This chapter lists code examples of dealing with databases, including Retrieve data from database as table sequence via SQL, Return single value result of SQL computation, Use database stored procedure to return one or multiple table sequences, Run SQL statement over a database to modify data, Use program code to connect to and disconnect from database, Manage transaction commit automatically by program code, Get database error messages, Use cursor to fetch big data in batches, and Write a table sequence/record sequence into database.
|
|
A |
|
|
1 |
=demo.query("select * from EMPLOYEE") |
|
|
2 |
=demo.query("select * from EMPLOYEE where EID=?",1) |
|
|
|
A |
|
|
1 |
=demo.query@1("select count(*) from EMPLOYEE") |
|
|
2 |
=demo.query@1("select count(*) from EMPLOYEE where SALARY>?",10000) |
|
|
|
A |
|
|
1 |
=db.proc("{call proc1(?,?)}",:101: "o":a,:101:"o":b) |
Execute the stored procedure and return 2 table sequences |
|
2 |
=A1(1) |
The first table sequence |
|
3 |
=A1(2) |
The second table sequence |
|
4 |
=a |
Use a variable name to access the first table sequence |
|
|
A |
|
|
1 |
>demo.execute("update SCORES set SCORE=? where STUDENTID=10",90) |
Update |
|
2 |
=create(wineID,wineName,wineType,wineProduction,wineStock).record([100,"Amaretto","Liqueur","France",2130,200,"Tia Maria","Liqueur","Jamaica",1570]) |
|
|
3 |
>demo.execute(A2,"update LIQUORS set STOCK=? where NAME=?", wineStock, wineName) |
Update in batches |
|
4 |
>demo.execute([1,3,5],"delete from ADVENTURE where AID=?",~) |
Delete in batches |
|
5 |
>demo.execute(A2,"insert into LIQUORS (LID, NAME, TYPE, PRODUCTION, STOCK) values (?,?,?,?,?)",wineID, wineName, wineType, wineProduction, wineStock) |
Insert in batches |
|
|
A |
|
|
1 |
=connect("demo") |
Connect to a database |
|
2 |
>A1.close() |
Close the connection |
|
|
A |
B |
|
|
1 |
=connect@e("demo") |
|
Establish a connection |
|
2 |
=A1.execute@k(.…) |
|
|
|
3 |
=A1.error() |
|
Read the error message generated by the previous database transaction execution |
|
4 |
if A3==0 |
>A1.commit() |
Commit if there are no errors |
|
5 |
else |
>A1.rollback() |
Roll back if there are errors |
|
6 |
>A1.close() |
|
Close the connection |
|
|
A |
|
|
1 |
=connect@e("demo") |
|
|
2 |
… |
|
|
3 |
=A1.error() |
Error code |
|
4 |
=A1.error@m() |
Error messages |
|
|
A |
B |
C |
|
|
1 |
=demo.cursor("select * from STOCKRECORDS") |
|
|
|
|
2 |
for |
|
|
|
|
3 |
|
=A1.fetch(1000) |
|
Fetch 1,000 records and return them as a table sequence |
|
4 |
|
if B3==null |
break |
Break the loop when the data retrieving is finished |
|
5 |
|
… |
|
|
|
|
A |
|
|
1 |
=demo.query("select ID, NAME,GENDER,AGE from STUDENTS") |
|
|
2 |
=A1.keys(ID) |
|
|
3 |
=demo.update(A1,STUDENTS1,ID, NAME) |
|
|
4 |
=demo.update@u(A1,STUDENTS1,ID, NAME) |
Generate "update" only |
|
5 |
=demo.update@i(A1,STUDENTS1, ID, NAME) |
Generate "insert" only |
|
6 |
=demo.update@a(A1,STUDENTS1, ID,NAME) |
Empty the target table before inserting data |