esProc Accesses Databases: Updating Data

Database 783 0

Besides being used to query data in a database, SQL is also used to update them. In esProc, you can use the SQL commands directly to return a query result, or use update function to modify a database with the data from a table sequence or a sequence.

1. Update a database with SQL commands

In esProc, db.execute(sql) function can be used to execute the SQL commands in a database, so as to realize various update operations. For example:

  A B
1   /=demo.query(“select * from TEST”)
2 >demo.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”) =demo.query(“select * from TEST”)
3 >demo.execute(“insert into TEST values (1,’Tom Smith’,’Florida’)”)  
4 >demo.execute(“insert into TEST values (2,’Michael Jones’,’Montana’)”) =demo.query(“select * from TEST”)
5 >demo.execute(“update TEST set STATE=’New York’ where ID=1”) =demo.query(“select * from TEST”)
6 >demo.execute(“delete from TEST where STATE=’Montana'”) =demo.query(“select * from TEST”)
7 >demo.execute(“drop table TEST”) /=demo.query(“select * from TEST”)

In this cellset, SQL commands are used to create a new table – TEST, insert records into it, update records in it and delete records from it, and drop it finally. Column B provides the viewing of the data in TEST after each step is executed. In B2 you can see the structure of the table in which there are no records:

esProc_database_update_data_2

A3 and A4 insert a record respectively into the table using SQL commands. The result can be seen in B4:

esProc_database_update_data_3

A5 modifies a record and the result can be seen in B5:

esProc_database_update_data_4

It can be seen that the order of records in the database has been changed after A5 modified the state in which Tom Smith stays. In fact, the records stored in various databases are usually not in a fixed order, so the data updating will change the original order, which will bring some trouble.

A6 deletes the record of the state of Montana and the result is as follows:

esProc_database_update_data_5

A7 drops the table finally. Because query function cannot retrieve data before the table is created and after it is dropped, the program will report error when query commands are executed in B1 and B7.

Parameters can also be used when execute statement is used to perform the SQL commands. For example:

  A B
1   /=demo.query(“select * from TEST”)
2 >demo.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”) =demo.query(“select * from TEST”)
3 >demo.execute(“insert into TEST values (?,?,?)”,1,”Tom Smith”,”Florida”)  
4 >demo.execute(“insert into TEST values (?,’Michael Jones’,’Montana’)”,2) =demo.query(“select * from TEST”)
5 >demo.execute(“update TEST set STATE=’New York’ where ID=?”,1) =demo.query(“select * from TEST”)
6 >demo.execute(“delete from TEST where STATE=?”,”Montana”) =demo.query(“select * from TEST”)
7 >demo.execute(“drop table TEST”) /=demo.query(“select * from TEST”)

While adding parameters to an execute statement, the positions corresponding to the parameters should be replaced with question marks and the parameters will be entered after the SQL statement in order. As the code in A3 shows, for example, values to be inserted can be all replaced by parameters, or as the code in A4 shows, not all of them will be replaced. Please note that a string in esProc’s functions should be enclosed by double quotation marks (“…”), which is different from the syntax of SQL.

When an execute statement is executed, the result will be committed automatically to the database by default. Once an error occurs, the program will stop. For example:

  A B C
1 1 Tom Smith Florida
2 2 Michael Jones Montana
3 three William Bush New York
4 4 Violet Taylor Montana
5 >demo.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”)    
6 =create(Fd1,Fd2,Fd3).record(A1:C4)    
7 for A6 =demo.execute(“insert into TEST values (?,?,?)”,A7.Fd1,A7.Fd2,A7.Fd3)  
8   =demo.query(“select * from TEST”)  
9 =demo.execute(“drop table TEST”)    

The table sequence in A6 is as follows:

esProc_database_update_data_8

The code block in A7 performs loop on the above-mentioned table sequence, insert records into TEST of the database and query the result in B8. The program reports an error and stops while trying to insert the third record into the table, because three is not an integer and thus cannot be inserted. At this point, the data in TEST can be viewed in B8:

esProc_database_update_data_9

This means only some of the data have been inserted into the database and the code for dropping the table in A9 hasn’t been executed. Obviously, this is not the result we expected.

Therefore, we need to get the error code under control and commit the transaction using commands. To achieve the target, default database connection should be given up and connect@e() will be used to create a connection to the database instead; meanwhile execute@k will be used to execute the SQL command without committing the result automatically:

  A B C
1 1 Tom Smith Florida
2 2 Michael Jones Montana
3 three William Bush New York
4 4 Violet Taylor Montana
5 =connect@e(“demo”) >A5.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”)  
6 =create(Fd1,Fd2,Fd3).record([A1:C4])    
7 for A6 >A5.execute@k(“insert into TEST values (?,?,?)”,A7.Fd1,A7.Fd2,A7.Fd3)  
8 if A5.error()==0 >A5.commit()  
9 else >A5.rollback()  
10 =A5.query(“select * from TEST”) >A5.execute(“drop table TEST”) >A5.close()

In this cellset, @e option is used in connect function to connect to the database. In this way, when an error occurs during the loop in B7 for updating the database, it will be recorded instead of causing an interruption to the program. Finally, we can see in A8 if the error code is a zero:

esProc_database_update_data_11

The error code is not a zero, which means an error occurred during the execution. Since @k option is used in the execute statement in B7, the result won’t be committed automatically. Thus when the rollback in B9 is executed, we can see in B10 that all the records haven’t been committed as an error occurred:

"esProc_database_update_data_12

For details of the data connection control, please refer to esProc Accesses Databases: Data Connection Management.

2. Update a database with data from a table sequence or a sequence

In the above example, for statement for looping was used to update the data of a table sequence into another table sequence. While, in esProc, update function can be used to directly update the data from a sequence or a table sequence into another table sequence. For example:

  A B C
1 1 Tom Smith Florida
2 2 Michael Jones Montana
3 3 William Bush New York
4 4 Violet Taylor Montana
5 =connect@e(“demo”) >A5.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”)  
6 =create(Fd1,Fd2,Fd3).record([A1:C4])    
7 >A5.update(A6,TEST,ID:Fd1,FULLNAME:Fd2,STATE:Fd3;ID)    
8 =A5.query(“select * from TEST”) >A5.execute(“drop table TEST”) >A5.close()

The table sequence in A6 is as follows:

esProc_database_update_data_14

update function is used in A7 to update the data of the table sequence in A6 into table TEST in the database. The function first specifies a source table – A6 and a target table – TEST, then defines the correspondence between fields of the two tables and the primary key – ID – for updating the database. If the primary key is not specified, it will be got automatically from the database. The query result of A8 is as follows:

esProc_database_update_data_15

It can be seen that the result of updating a database with update function is the same as that using a for loop.

When the primary key for updating the database has been defined and there are already the data in the database that use the same primary key, what will we do then? Let’s look at the following example:

  A B C
1 1 Tom Smith Florida
2 2 Michael Jones Montana
3 3 William Bush New York
4 4 Violet Taylor Montana
5 =connect@e(“demo”) >A5.execute(“create table TEST(ID int,FULLNAME varchar(50), STATE varchar(20))”)  
6 >demo.execute(“insert into TEST values (1,’TOM SMITH’,’FL’)”) >demo.execute(“insert into TEST values (20,’MICHAEL’,’MT’)”) =demo.query(“select * from TEST”)
7 =create(Fd1,Fd2,Fd3).record([A1:C4])    
8 >A5.update(A7,TEST,ID:Fd1,FULLNAME:Fd2,STATE:Fd3;ID)    
9 =A5.query(“select * from TEST”) >A5.execute(“drop table TEST”) >A5.close()

First, A6 and B6 insert two records respectively into TEST. Then C6 selects all the data before the database was updated using update function from TEST, as shown below:

esProc_database_update_data_17

After that, update function is executed in A8 and the data in TEST can be viewed in A9:

esProc_database_update_data_18

As can be seen from the above, the records that use the same primary key have been updated, but the record – MICHAEL – which has a different primary key remain unchanged.

Some options can be used in update function to represent different operations: @u means updating the data without inserting new ones; @i means inserting data without updating the existing values; @a means deleting all the records from the target table before updating; @1 means that the first field is auto-increment and won’t get assigned during updating.

If we modify the statement in A8 in the previous example into =A5.update@u(A7,TEST,ID:Fd1,FULLNAME:Fd2,STATE:Fd3;ID), the query result in A9 will be as follows:

esProc_database_update_data_19

update@u in A8 is executed to only modify the existing records.

But if the statement in A8 in the previous example is modified into =A5.update@a(A7,TEST,ID:Fd1,FULLNAME:Fd2,STATE:Fd3;ID), the query result in A9 will be as follows:

esProc_database_update_data_20

In A8, the original records in the table will be deleted before update@a is executed. The use of update@a can ensure a consistency between the current data in the table and the source data used for updating.

Similar to the direct execution of a SQL statement, @k option can also be used in update function. In this case the data connection control should be considered. For more information, please refer to esProc Accesses Databases: Data Connection Management.

FAVOR (0)
Leave a Reply
Cancel
Icon

Hi,You need to fill in the Username and Email!

  • Username (*)
  • Email (*)
  • Website