Database Update

This section explains database upadate in esProc. You can choose to skip this section if you are not a professional programmer. It won’t affect your learning about the other contents of this Tutorial.

SQL statements can both query data in a database and update the database data. Besides using SQL commands directly to return a query result, esProc also provides update function to update a database using data from a table sequence or a sequence.

4.4.1 Database update through SQL commands

db.execute(sql) function is used to execute different SQL commands on a database to perform 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 a record in it, delete a record 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:

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

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

You see that the order of records in the database has changed after A5 modified the home state of Tom Smith. In fact, the records stored in various databases are usually not in a fixed order, and the data updating will change the original order, which will bring trouble in certain cases.

A6 deletes the record of the state of Montana and the table becomes this:

A7 drops the table finally. Because query function cannot retrieve data before the table is created and after it is dropped, errors will be reported when executing queries in B1 and B7.

Parameters can be used when performing SQL commands using esProc execute function:

	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")

When using parameters in the execute function, their positions in the SQL statement are held by question marks (?) and they are written in order after the statement. The values to be assigned by the SQL statement can be represented all or partly by parameters, as A3 and A4 respectively demonstrate. Note that a string in an esProc function should be enclosed by double quotation marks (in the form of "…"), which is different from the SQL syntax.

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:

A7’s code block loops through records of the above table sequence to insert each of them into the database table TEST and queries the result in B8. The program reports an error and stops when 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:

Only two records were written to the database and the code for dropping the source table in A9 hasn’t been executed yet. Obviously, this isn’t the expected result.

Therefore, we need to get the error code under control and handle the transaction using the command. To do this, default database connection should be given up and connect@e() will be used to create a connection to the database instead; and then 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. When an error occurs during executing B7 by loop to update the database, it will be recorded instead of causing an interruption to the program. Finally, check A8 to see if the error code is zero:

The error value isn’t zero, which means an error occurred during the execution. Since @k option is used in the execute function in B7, each inserting result won’t be committed automatically. Thus when the rollback in B9 is executed, you can see in A10 that all the records haven’t been committed as an error occurred:

For details of the data connection control, refer to Database Connection Management. 

4.4.2 Database update using a TSeq or a sequence

In the above example, for statement for performing loop operation was used to update a table sequence using the data of another table sequence. esProc offers the update function to directly update a table sequence with the data from a sequence or a 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:

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

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 is defined but there are already the data in the database that use the same primary key, what will happen? 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 retrieves data from TEST before updating the database using update function:

The update function is then executed in A8 and the data in TEST can be viewed in A9:

The record having the same primary key value has been updated, but the record of MICHAEL,which has a different primary key value, remains unchanged.

Different options can work with update function to perform different operations: @u means updating data without inserting new records; @i means inserting recrods without updating the existing values; @a means deleting all existing records from the target table before performing the update; and @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 example into =A5.update@u(A7,TEST,ID:Fd1,FULLNAME:Fd2,STATE:Fd3;ID)， the query result in A9 will be as follows:

A8 executes update@u to modify only the existing records.

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

A8 will delete the original records in the target table before executing update@a. The use of update@a ensures data consistency between the target table and the source data used for update.

Similar to the direct execution of a SQL statement, the update function can also use the @k option. In this case you should consider an appropriate way of connecting the database. For more information, refer to Database Connection Management.