SQL Command Execution
Description
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 ENDThe 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:
|
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:
|
Removing a Row in a Table
To remove a row:
|
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:
|
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:
-
The date displayed as a Julian number (default), or
-
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:
|
The template generated would be:
|
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:
|
The template generated for this statement would be:
|
See the DATE() function documentation for a description of constructing the format string.