Database

Read(239) Label: database,

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.

Retrieve data from database as table sequence via SQL

 

A

 

1

=demo.query("select * from EMPLOYEE")

 

2

=demo.query("select * from EMPLOYEE where EID=?",1)

 

 

Return single value result of SQL computation

 

A

 

1

=demo.query@1("select count(*) from EMPLOYEE")

 

2

=demo.query@1("select count(*) from EMPLOYEE where SALARY>?",10000)

 

 

Use database stored procedure to return one or multiple table sequence s

 

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

 

Run SQL statement over database to modify data

 

A

 

1

>demo.execute("update SCORES set SCORE=? where STUDENTID=10",90)

Update

2

=demo.query("selct * from LIQUORSNEW")

 

3

>demo.execute(A2,"update LIQUORS set STOCK=? where NAME=?", wineStock, wineName)

Update in batches

4

>demo.execute([1,3,5],"delete from product where productnumber=?",~)

Delete in batches

5

>demo.execute(A2,"insert into LIQUORS (LID, NAME, TYPE, PRODUCTION, STOCK) values (?,?)",wineID, wineName, wineType, wineProduction, wineStock)

Insert in batches

 

Use program code to connect to and disconnect from database

 

A

 

1

=connect("demo")

Connect to a database

2

>A1.close() 

Close the connection

 

Use program code to connect to and disconnect from database

 

A

 

1

=connect("demo")

Connect to a database

2

>A1.close() 

Close the connection

 

Manage transaction commit automatically by program code

 

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

 

Manage transaction commit automatically by program code

 

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

 

Get database error messages

 

A

 

1

=connect@e("demo")

 

2

 

3

=A1.error()

Error code

4

=A1.error@m()

Error messages

 

Get database error messages

 

A

 

1

=connect@e("demo")

 

2

 

3

=A1.error()

Error code

4

=A1.error@m()

Error messages

 

Use cursor to fetch big data in batches

 

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

 

 

 

 

Use cursor to fetch big data in batches

 

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

 

 

 

 

Write a table sequence/ record sequence into database

 

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

 

Write a table sequence/ record sequence into database

 

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