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:
A3 and A4 insert a record respectively into the table using SQL commands. The result can be seen in B4:
A5 modifies a record and the result can be seen in B5:
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:
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:
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:
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:
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:
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:
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:
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:
After that, update function is executed in A8 and the data in TEST can be viewed in A9:
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:
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:
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.