In this section: Example: |
The API provides two methods for submitting an SQL request. EDASQL passes the assembled SQL statement to the server and the server parses the statement; if no errors are found, it executes the statement directly, potentially returning a result set to the client. This method is appropriate for processing multiple different SQL statements.
In the second method, the application program begins by submitting an SQL statement to the server using the EDAPREPARE method. The server parses the statement, and if no problems are found, retains it for future use. It does not return a result set to the client. The application program is then expected to execute the prepared SQL statement one or more times by invoking the EDAEXECUTE method.
In the first method, an SQL string must be transmitted, parsed, and validated every time it is used. In the second method, the system need only prepare an SQL string once-and can then execute it repeatedly. Whenever an SQL request is invoked repeatedly, the second method may be more efficient.
A statement submitted using EDAPREPARE might contain one or more embedded parameter markers. Before the server executes the statement, a parameter value is substituted for each embedded marker. Parameter values are passed into the API through EDAUSING.
"SELECT name FROM syscolum WHERE tbname = ?;"
The question mark denotes a parameter marker, which will be substituted for a table name value at execution. This notation is the standard way of representing a parameter insertion point in an SQL request. The following is sample code:
char statement [50] = "SELECT name FROM syscolum WHERE tbname = ?;"
long slength = 45; /* Length of SQL string */
char vlu[30]; /* Table name string */
long vlulen; /* Length of name string. */
char data_area[30]; /* Column name string. */
long len = 30; /* Length of result field.*/
long sth; /* Statement identifier */
long dtype = EDA_A_TYPE_CODE; /* Parameter data type. */
long len = 30; /* Parameter length. */
long scale = 0; /* Parameter scale. */
long zero=0L;
int browsing = TRUE; /* TRUE =>continue. */
...
/* Prepare the query for execution. */
EDAPREPARE(&scb1, &sth, statement, &slength);
if (status != 0) process_the_error();
EDATEST(&scb1, NULL);
if (scb1.status != 0) process_the_error();
...
/* Map the format of the result set. */
for (column_nbr = 1; column_nbr <= scb1.nbrcols; column_nbr++)
{
EDAINFO(&scb1, &column_nbr, &work_area);
if (scb1.status != 0) process_the_error();
...
}
/* Loop until the user decides to quit. */
while (browsing)
{
/* Prompt the user for a table name, T. Store T in
vlu and its length in vlulength. */
...
/* Set up the single-element parameter list and
execute the query. */
EDAUSING(&scb1, &sth, &ONE, vlu, vlulen,
&dtype, &len, &scale, ONE);
if (scb1.status != 0) process_the_error();
EDAEXECUTE(&scb,&sth,&zero,&zero,NULL,&zero);
if (scb1.status != 0) process_the_error();
EDAINFO(&scb1, &ONE, &work_area);
if (scb1.status != 0) process_the_error();
/* The query executed. Scan the result set. */
while(1)
{
EDANEXT(&scb1);
if (scb1.status==EDA_END_OF_SET) break;
if (scb1.status != 0) process_the_error();
EDAFIELD(&scb1, &ONE, data_area, &len,
&fmt);
if (scb1.status != 0) process_the_error();
/* Add the column name to the display list. */
...
}
/* Display the result. Ask whether operator wishes to
continue. */
...
}
...
The program shown previously demonstrates the typical pattern of logic for two-stage query execution under the API. When you prepare a statement and invoke it any number of times, each invocation entails a call to EDAUSING (to set up a parameter list) and a subsequent call to EDAEXECUTE. This code is marginally more complex but, potentially more efficient than the equivalent single-stage EDASQL formulation.
The API permits batches of parameters to be applied to SQL requests. An example, in which the application program accepts several new customer records before inserting them into the database, is shown in the following figure.
A skeletal version of this program, written in the C language, follows:
/* The SQL INSERT statement with parameter markers for
company name, customer number and sales rep number. */
char insert_stmt[] =
"INSERT INTO CUST(COMPANY, CNBR, SALESREP) VALUES (?, ?, ?)"
...
/* Prepare the SQL INSERT statement. */
EDAPREPARE(&scb1, &sth, statement, &slength);
...
/* Data entry loop. */
while (accepting_input)
{
printf("\nCompany Name: ");
scanf("%s", vlu);
vlulen = strlen(vlu);
EDAUSING(&scb1, &sth, &ONE, &vlu, &vlulen, &dtype,
&len, &scale, &ZERO);
if (scb1.status != 0) process_the_error();
printf("\nCustomer Number: ");
scanf("%s", vlu);
vlulen = strlen(cnum);
EDAUSING(&scb1, &sth, &TWO, &vlu, &vlulen, &dtype,
&len, &scale, &ZERO);
if (scb1.status != 0) process_the_error();
printf("\nCustomer Rep Number: ");
scanf("%s", vlu);
vlulen = strlen(vlu);
EDAUSING(&scb1, &sth, &THREE, &vlu, &vlulen,
&integer,&parm_len, &scale, &ONE);
if (scb1.status != 0) process_the_error();
printf("\nMore (Y|N)? ");
...
}
...
/* At least one parameter list has been set up. Execute the
SQL INSERT request once for every list. Then COMMIT the
transaction unless one of the operations fails. Note that
EDAEXECUTE is followed below by calls to EDATEST. The EDATEST
call waits for the server to respond
to the EDAEXECUTE command and the EDATEST call reveals
whether all of the insertions were made correctly. */
yes_no = YES;
EDAEXECUTE(&scb,&sth,&zero,&zero,NULL,&zero);
if (scb1.status != 0) process_the_error();
EDATEST(&scb1, NULL);
if (scb1.status != 0)
{
yes_no = NO;
process_the_error();
}
EDACOMMIT(&eid, &yn, &status);
if (scb1.status != 0) process_the_error();
...
Note: Prepared statements are not valid in a transaction. A commit or a rollback will cause the prepare to be dropped from the server.
| iWay Software |