[ Pobierz całość w formacie PDF ]
.Using host-language facilities, such as datatypes and macros, to provide input and outputareas for passing statements and parameters at runtime.Programming methods that use these statements and facilities to process SQL statementsat runtime.API GUIDE 77CHAPTER 6 WORKING WITH DYNAMIC SQLThese steps are described in detail throughout this chapter.DSQL API limitationsAlthough DSQL offers many advantages, it also has the following limitations:Dynamic transaction processing is not permitted; all named transactions must bedeclared at compile time.Dynamic access to Blob and array data is not supported; Blob and array data can beaccessed, but only through standard, statically processed SQL statements, or throughlow-level API calls.Database creation is restricted to CREATE DATABASE statements executed within the contextof EXECUTE IMMEDIATE.For more information about database access in DSQL, see  Accessing databases onpage 78.For more information about handling transactions in DSQL applications, see Handling transactions on page 79.For more information about working with Blobdata in DSQL, see  Processing Blob data on page 81.For more information abouthandling array data in DSQL, see  Processing array data on page 81.For moreinformation about dynamic creation of databases, see  Creating a database onpage 80.Accessing databasesThe InterBase API permits applications to attach to multiple databases simultaneouslyusing database handles.Database handles must be declared and initialized when anapplication is compiled.Separate database handles should be supplied and initialized foreach database accessed simultaneously.For example, the following code creates a singlehandle, db1, and initializes it to zero:#includeisc_db_handle db1;.db1 = 0L;Once declared and initialized, a database handle can be assigned dynamically to adatabase at runtime as follows:#include.char dbname[129];78 INTERBASE 5DSQL API LIMITATIONSISC_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 previouslyattached database is first detached with isc_detach_database(), which automatically setsdatabase handles to NULL.The following statements detach from a database, set thedatabase handle to zero, and attach to a new database:isc_detach_database(status_vector, &db1);isc_attach_database(status_vector, 0, "employee.gdb", &db1, NULL,NULL);For more information about API function calls for databases, see Chapter 4,  Workingwith Databases.Handling transactionsInterBase requires that all transaction handles be declared when an application iscompiled.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 processSQL 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(), andisc_dsql_prepare(), support the inclusion of a transaction handle parameter.The SQLstatements processed by these functions cannot pass transaction handles even if the SQLsyntax 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.Thefollowing code declares, initializes, and uses a transaction handle in an API call thatallocates and prepares an SQL statement for execution:#include.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.*/API GUIDE 79CHAPTER 6 WORKING WITH DYNAMIC SQL/* This code assumes both 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(), butit can be processed with isc_dsql_execute_immediate() if:1.Previous transactions are first committed or rolled back.2 [ Pobierz całość w formacie PDF ]