DSQL API Limitations

Although DSQL offers many advantages, it also has the following limitations:

• Dynamic transaction processing is not permitted; all named transactions must be declared at compile time.
• Dynamic access to Blob and array data is not supported; Blob and array data can be accessed, but only through standard, statically processed SQL statements, or through low-level API calls.
• Database creation is restricted to CREATE DATABASE statements executed within the context of EXECUTE IMMEDIATE.

For more information on:

• database access in DSQL, see Accessing Databases.
• handling transactions in DSQL applications, see Handling Transactions.
• working with Blob data in DSQL, see Processing Blob Data.
• handling array data in DSQL, see Processing Array Data.
• dynamic creation of databases, see Creating a Database.

Accessing Databases

The InterBase API permits applications to attach to multiple databases simultaneously using database handles. Database handles must be declared and initialized when an application is compiled. Separate database handles should be supplied and initialized for each database accessed simultaneously. For example, the following code creates a single handle, db1, and initializes it to zero:

#include <ibase.h>

isc_db_handle db1;

. . .

db1 = 0L;

Once declared and initialized, a database handle can be assigned dynamically to a database at runtime as follows:

#include <ibase.h>

. . .

char dbname[129];

ISC_STATUS status_vector[20];

. . .

prompt_user("Name of database to open: ");

gets(dbname);

isc_attach_database(status_vector, 0, dbname, &db1, NULL, NULL);

A database handle can be used to attach to different databases as long as a previously attached database is first detached with isc_detach_database(), which automatically sets database handles to NULL. The following statements detach from a database, set the database handle to zero, and attach to a new database:

isc_detach_database(status_vector, &db1);

isc_attach_database(status_vector, 0, "employee.ib", &db1, NULL, NULL);

For more information about API function calls for databases, see Chapter 4: Working with Databases.

Handling Transactions

InterBase requires that all transaction handles be declared when an application is compiled. Once fixed at compile time, transaction handles cannot be changed at runtime, nor can new handles be declared dynamically at runtime. Most API functions that process SQL statements at runtime, such as isc_dsql_describe(), isc_dsql_describe_bind(), isc_dsql_execute(), isc_dsql_execute2(), isc_dsql_execute_immediate(), isc_dsql_exec_immed2(), and isc_dsql_prepare(), support the inclusion of a transaction handle parameter. The SQL statements processed by these functions cannot pass transaction handles even if the SQL syntax for the statement permits the use of a TRANSACTION clause.

Before a transaction handle can be used, it must be declared and initialized to zero. The following code declares, initializes, and uses a transaction handle in an API call that allocates and prepares a SQL statement for execution:

#include <ibase.h>

. . .

isc_tr_handle trans; /* Declare a transaction handle. */

isc_stmt_handle stmt; /* Declare a statement handle. */

char *sql_stmt = "SELECT * FROM EMPLOYEE";

isc_db_handle db1;

ISC_STATUS status_vector[20];

. . .

trans = 0L; /* Initialize the transaction handle to zero. */

stmt = NULL; /* Set handle to NULL before allocation. */

/* This code assumes that a database attachment is made,

* and a transaction is started here. */

. . .

/* Allocate the SQL statement handle. */

isc_dsql_allocate_statement(status_vector, &db1, &stmt);

/* Prepare the statement for execution. */

isc_dsql_prepare(status_vector, &trans, &stmt, 0, sql_stmt, 1, NULL);

Note: The SQL SET TRANSACTION statement cannot be prepared with isc_dsql_prepare(), but it can be processed with isc_dsql_execute_immediate() if:

1. Previous transactions are first committed or rolled back.
2. The transaction handle is set to NULL.

For more information about using SQL statements, see the Embedded SQL Guide. For more information about SQL statement syntax, see the Language Reference Guide.

Creating a Database

To create a new database in an API application:

1. Detach from any currently attached databases with isc_detach_database(). Detaching from a database automatically sets its database handle to NULL.
2. Build the CREATE DATABASE statement to process.
3. Execute the statement with isc_dsql_execute_immediate() or isc_dsql_exec_immed2().

For example, the following statements disconnect from any currently attached databases, and create a new database. Any existing database handles are set to NULL, so that they can be used to connect to the new database in future DSQL statements.

char *str = "CREATE DATABASE \"new_emp.ib\"";

. . .

isc_detach_database(status_vector, &db1);

isc_dsql_execute_immediate(status_vector, &db1, &trans, 0, str, 1, NULL);

Processing Blob Data

Blob processing is not directly supported using DSQL, nor are Blob cursors supported. Applications that process SQL statements can use API calls to handle Blob processing. For more information about processing Blob data, see [[Working with Blob Data]].

Processing Array Data

Array processing is not directly supported using DSQL. DSQL applications can use API calls to process array data. For more information about array calls, see Working with Array Data.

Advance to Next Section

• Writing an API Application to Process SQL Statements