SQL Command Execution

For BBj-specific SQL Select information, see the SQL SELECT Modifications.

Once a database is selected, a list of available tables can be acquired by using the SQLTABLES() function. SQL commands are executed in two steps. First, an SQL command must be run through SQLPREP. SQLPREP checks the legality of the command and prepares an execution strategy. Second, using SQLEXEC. SQLEXEC executes the command that was prepared by SQLPREP.

If the SQL command (or commands) contains replaceable arguments, then they can be adjusted using the SQLARG verb or, optionally, using the SQLEXEC verb.

Reading Data from SQL

Once the SQL command has been executed using the SQLEXEC verb, the data may be returned to the programmer from the SQLFETCH() function. When all of the data from the SQL command is returned, the SQLFETCH() function will return an !ERROR=2. If any other errors are reported by either the SQLPREP or SQLEXEC verbs, then an explanation of the error is available in the SQLERR() function documentation.

You may terminate a database connection with the SQLCLOSE verb.

As an example, consider accessing the database "Chile Company", available in TAOS/Views. In that demonstration database there are several tables available, and the following program shows the selection of data from one of them.

Attach to a database. Because the database name is known, bypass the SQLLIST() function and directly attach to the database:

0010 SQLOPEN(1)"Chile Company"
Execute the desired command. In this case a selection of the customers with a current balance:

0020 SQLPREP(1)"select * from CUSTOMER where CURRENT_BAL>0"
Format a string to receive the rows of data generated by the SQL select:

0030 DIM CUST$:SQLTMPL(1)
Execute the command set up via SQLPREP():

0040 SQLEXEC(1)
Since the select command is not ordering the data much, you will likely be able to start receiving data immediately, so you execute:

0050 CUST$=SQLFETCH(1,ERR=90)
The error branch will handle the end of the select set (similar to the "end of file" condition encountered when reading data from a file). If the error branch is not taken, the variable "cust$" will receive the first/next row from the table selected by the SQL select command. You can do something with it and go to retrieve another:

0060 PRINT CUST.CUST_NUM$," ",CUST.COMPANY$," ",CUST.CURRENT_BAL
0070 GOTO 50
The error branch should handle the expected case of the "end of file," and generate error diagnostics for the other, unexpected, conditions:

0080 IF ERR=2 THEN GOTO 0110
0090 PRINT "error",ERR,"encountered, sql error text
0090:is:"
0100 PRINT SQLERR(1,ERR=0110); GOTO 0100
0110 END

The SQLFETCH() function will retrieve any data available as a result of the SQLPREP and SQLEXEC commands. If no data is available as a result of the SQL command, or if all data made available as a result of the executed command has already been retrieved, SQLFETCH() will return an !ERROR=2. If a command is executed that prepares a list of rows to be returned, a new command is prepared and executed, then the old list of rows is discarded without complaint. Additionally, a query can be re-started by simply doing another SQLEXEC, without performing a new SQLPREP. This is good practice because SQLPREP can be time consuming.

Inserting a Row in a Table

To insert a row in a database, use the following commands:

0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP (1)"insert into
0020:CUSTOMER(CUST_NUM,FIRST_NAME,LAST_NAME)
0020:VALUES(6,'Ralph','Hammerschmidt')"
0030 SQLEXEC(1)

Modifying a Row in a Table

The SQLPREP will insert into the CUSTOMER table a new row with the information given in the proper columns, and all unspecified columns containing default data for that column. To modify data in a particular customer's record:

0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP(1)"update CUSTOMER set CREDIT_CODE='01'
0020:where CUST_NUM=10"
0030 SQLEXEC(1)

Removing a Row in a Table

To remove a row:

0010 SQLOPEN(1)"Chile Company"
0020 SQLPREP(1)"delete from CUSTOMER where
0020:CUST_NUM=10"
0030 SQLEXEC(1)

Multiple SQLPREP Commands

The above examples create a new connection and compile a new command for each operation desired. In practice it is generally easier to maintain multiple connections to the database, each with previously compiled commands with settable arguments, to select the row on which to operate. It would be more effective for many of the above operations to perform like the following:

0010 DATABASE$="Chile Company"
0020 GETREC=SQLUNT; SQLOPEN(GETREC)DATABASE$
0021 SQLPREP(GETREC)"select * from CUSTOMER where
0021:CUST_NUM=?"
0030 DELREC=SQLUNT; SQLOPEN(DELREC)DATABASE$
0031 SQLPREP(DELREC)"delete from CUSTOMER where
0031:CUST_NUM=?"
0040 DIM CUST$:SQLTMPL(GETREC)
0050 INPUT "customer number: ",CUST:("end"=1000,9999)
0060 SQLEXEC (GETREC)CUST
0070 CUST$=SQLFETCH(GETREC,ERR=0200)
0080 PRINT CUST.CUST_NUM," ",CUST.COMPANY$
0090 INPUT (0,ERR=0090)"delete? (y/n) ",
0090:ANSWER$:("Y"=100,"y"=100,"n"=50,"N"=50)
0100 SQLEXEC(DELREC)CUST
0110 PRINT CUST.CUST_NUM," deleted"
0120 GOTO 0050
0200 PRINT "record not found"; GOTO 0050

SQL Date Columns in Data Aware Grids

When a SELECT statement on an SQL channel contains a date column, the developer has two basic options:

1. The date displayed as a Julian number (default), or

2. First convert the date to a formatted date string.

For example, if the following SELECT statement is used in a Data Aware Grid, the date would be displayed as a Julian number:

SELECT order_date as ORDER_DATE,

invoice_date as INVOICE_DATE

FROM order

The template generated would be:

ORDER_DATE:I(4),INVOICE_DATE:I(4)

However, the second option is to convert the date value to a formatted string and return the string representation of the date. The following SELECT statement will format the date values as mm/dd/yyyy:

SELECT date(order_date, '%Mz/%Dz/%Yd') as ORDER_DATE,

date(invoice_date, '%Mz/%Dz/%Yd') as INVOICE_DATE

FROM order

The template generated for this statement would be:

ORDER_DATE:C(10),INVOICE_DATE:C(10)

See the DATE() function documentation for a description of constructing the format string.