Writing an API Application to Process SQL Statements
Contents
Writing an API application that processes SQL statements enables a developer to code directly to InterBase at a low level, while presenting end users a familiar SQL interface. API SQL applications are especially useful when any of the following are not known until runtime:
- The text of the SQL statement
- The number of host variables
- The datatypes of host variables
- References to database objects
Writing an API DSQL application is more complex than programming embedded SQL applications with regular SQL because for most DSQL operations, the application needs explicitly to allocate and process an extended SQL descriptor area (XSQLDA) data structure to pass data to and from the database.
To use the API to process a DSQL statement, follow these basic steps:
- Determine if API calls can process the SQL statement.
- Represent the SQL statement as a character string in the application.
- If necessary, allocate one or more XSQLDAs for input parameters and return values.
- Use appropriate API programming methods to process the SQL statement.
Determine if API Calls Can Process a SQL Statement
Except as noted earlier in this chapter, DSQL functions can process most SQL statements. For example, DSQL can process data manipulation statements such as DELETE and INSERT, data definition statements such as ALTER TABLE and CREATE INDEX, and SELECT statements.
The following is a list of SQL statements that cannot be processed by DSQL functions:
- CLOSE
- DECLARE CURSOR
- DESCRIBE
- EXECUTE
- EXECUTE IMMEDIATE
- FETCH
- OPEN
- PREPARE
These statements are used to process DSQL requests or to handle SQL cursors, which must always be specified when an application is written. Attempting to use them with DSQL results in run-time errors.
Representing a SQL Statement as a Character String
Within a DSQL application, a SQL statement can come from different sources. It might come directly from a user who enters a statement at a prompt, as does isql. Or it might be generated by the application in response to user interaction. Whatever the source of the SQL statement, it must be represented as a SQL statement string, a character string that is passed to DSQL for processing.
SQL statement strings do not begin with the EXEC SQL prefix or end with a semicolon (;) as they do in typical embedded applications. For example, the following host-language variable declaration is a valid SQL statement string:
- char *str = "DELETE FROM CUSTOMER WHERE CUST_NO = 256";
Note: The semicolon that appears at the end of this char declaration is a C terminator, and not part of the SQL statement string.
Specifying Parameters in SQL Statement Strings
SQL statement strings often include value parameters, expressions that evaluate to a single numeric or character value. Parameters can be used anywhere in statement strings where SQL expects a value that is not the name of a database object.
A value parameter in a statement string can be passed as a constant, or passed as a placeholder at runtime. For example, the following statement string passes 256 as a constant:
- char *str = "DELETE FROM CUSTOMER WHERE CUST_NO = 256";
It is also possible to build strings at runtime from a combination of constants. This method is useful for statements where the variable is not a true constant, or it is a table or column name, and where the statement is executed only once in the application.
To pass a parameter as a placeholder, the value is passed as a question mark (?) embedded within the statement string:
- char *str = "DELETE FROM CUSTOMER WHERE CUST_NO = ?";
When a DSQL function processes a statement containing a placeholder, it replaces the question mark with a value supplied in an extended SQL descriptor area (XSQLDA) previously declared and populated in the application. Use placeholders in statements that are prepared once, but executed many times with different parameter values.
Replaceable value parameters are often used to supply values in SQL SELECT statement WHERE clause comparisons and in the UPDATE statement SET clause.