|
   
4.
Executing SQL Statements
4.1
The SQL Language
4.2
Executing SQL Statements With ODBC
4.3
Parameters
4.4
Entering Large Data
4.5
Get Options of Statement Handle
4. Executing SQL Statements
This chapter
describes in detail how to use ODBC functions to execute the SQL statements
supported by DBMaker. It provides a brief introduction to the SQL query
language, and then illustrates how to:
allocate and free statement handles by using the functions SQLAllocStmt
and SQLFreeStmt.
execute an SQL statement directly by using the function SQLExecDirect,
and prepare an SQL statement for execution and execute the prepared
statement by using the functions SQLPrepare and SQLExecute.
return the number of rows affected by UPDATE, INSERT or DELETE statements
using the function SQLRowCount.
use parameters to pass a data value to an SQL command at execution time,
instead of preparation time.
return the number of parameters in an SQL statement by using the function
SQLNumParams, and return a description of a parameter marker
associated with a prepared SQL statement by using the function SQLDescribeParam.
bind a buffer to a parameter marker in an SQL statement by using the
function SQLBindParameter, and input large data items in smaller
pieces by using the functions SQLPutData and SQLParamData.
return the current setting of a statement option by using the function
SQLGetStmtOption, and cancel statement processing by using the
function SQLCancel.
Note:
You allocate statement handles differently using DBMaker 3.5 (ODBC 3.0)
from what is described in this chapter. Refer to Appendix E, What's
New in DBMaker 3.5 for more information.
The following
diagram shows the topics of Sections 4-3 ~ 4-6 and their relation to
the state transitions that occur when writing an application that uses
ODBC to access a database.
Figure
4-1: Topics in this chapter and their relation to state transitions
4.1 The SQL Language
Structured
Query Language (SQL) is the industry standard query language used
for defining, organizing, managing, and retrieving data stored in relational
databases. Unlike traditional procedural programming languages such
as C and Pascal, you do not need to explicitly define how to perform
a database operation. You can simply enter a request to the database
using the English-like SQL syntax, and the database will determine the
best method to process the request and return the results to you when
it is finished.
The functions
provided by SQL go beyond simple data retrieval, although that is still
one of its most important functions. SQL is actually divided into three
parts, known as Data Definition Language (DDL), Data Manipulation
Language (DML), and Data Control Language (DCL). Each of
these performs a specific role, and together you can use them to perform
all functions a DBMS provides, including:
Data definition - lets you define the structure and organization
of data and the relationships between data.
Data manipulation -allows you to retrieve existing data from the
database and update the database by adding new data, deleting old data,
and modifying previously stored data.
Data control -allows you to protect data against unauthorized access,
and define integrity constraints to protect data from corruption.
This section
provides a brief overview of SQL. For more detailed information, please
see the SQL Command and Function Reference.
The Role of SQL with ODBC
As shown
in Figure 4-2, when the ODBC driver
gets an SQL statement, it passes the SQL request to the database engine.
The database engine structures, stores and retrieves the data on disk
according to the SQL statement.
Figure
4-2: The role of SQL when using ODBC
Basic SQL Statements
SQL statements
can be divided into DDL, DML, and DCL statements. Only a few of the
basic SQL statements will be discussed in this chapter. For a more detailed
discussion of the entire SQL language and SQL syntax supported by DBMaker,
see the SQL Command and Function Reference.
Data Definition Language (DDL)
The schema
of a database is handled by a set of SQL statements called the SQL Data
Definition Language (DDL). DDL makes use of the CREATE, DROP or
ALTER commands to define, remove or modify the definition of a database
object. We will briefly explain the CREATE TABLE statement.
The CREATE TABLE Command
A database
contains many tables, and each table in a database stores information.
Tables are composed of rows (records) and columns (fields).
You can use the CREATE TABLE statement to create a new table in a database.
The basic syntax of the CREATE TABLE statement is:
CREATE TABLE table-name (column-name data-type, .... )
|
Data Types
The ANSI/ISO
SQL standard specifies a minimal set of data types that a DBMS should
support. Almost all commercial SQL products support these data types
or provide similar data types that have equivalent functionality. The
data types supported by DBMaker are listed in Table
4-1. Below is an example of how the CREATE TABLE command is
used:
CREATE TABLE account (
no serial, /* account number */
lname name, /* account last name */
fname name, /* account first name */
branch integer, /* belong to branch */
balance money, /* account balance */
altno char(12),
stamp image, /* account's stamp image */
photo image, /* account's photo image */
memo text /* account's memo */
);
|
Data Manipulation Language (DML)
Retrieving
or manipulating the data in a database is handled by a set of SQL statements
called the SQL Data Manipulation Language, or DML. The basic DML statements
are SELECT, INSERT, DELETE and UPDATE.
Retrieving Data from the Database (SELECT)
You can
use the SELECT statement to retrieve data from a database and return
a result set to the user. The basic syntax of the SELECT statement is:
SELECT item_list FROM table_list WHERE search_condition;
|
|
Data
Type
|
Description
|
|
CHAR(len)
|
Fixed-length
character string
|
|
VARCHAR(len)
|
Variable-length
character string
|
|
BINARY(len)
|
Binary
data
|
|
OID
|
Object
ID
|
|
FILE
|
BLOB
object (file)
|
|
LONG
VARCHAR
|
BLOB
object (text)
|
|
LONG
VARBINARY
|
BLOB
object (binary)
|
|
SERIAL
[(integer)]
|
Auto-increment
integer
|
|
SMALLINT
|
Small
integer number
|
|
INTEGER
[INT]
|
Integer
number
|
|
FLOAT
|
Low-precision
floating point number
|
|
DOUBLE
|
High-precision
floating point number
|
|
DECIMAL
[DEC] DECIMAL(precision,scale)
|
Decimal
numbers (use default precision and scale) Default precision is
17 and default scale is 6
|
|
DATE
|
Date
|
|
TIME
|
Time
|
|
TIMESTAMP
|
Timestamp
|
|
other
|
domain
|
Table
4-1: Data types in DBMaker
The basic
SELECT statement is made up of three components: SELECT, FROM and WHERE.
The functions of each of these components is listed below:
SELECT - specifies the columns or calculated columns to be retrieved
by the query.
FROM - specifies the tables that contain the items in the SELECT list.
WHERE - specifies the search condition that must be met to select a
row.
The WHERE
clause may contain multiple search conditions. The search conditions
that can be included in the WHERE clause are shown in the following
list. They can include:
Comparison operators (=, >, <, >=, <=, <>, !=)
Ranges (BETWEEN and NOT BETWEEN)
Lists (IN and NOT IN)
String matches (LIKE and NOT LIKE)
BLOB matches (MATCH and NOT MATCH)
Unknown values (IS NULL and IS NOT NULL)
Logical combinations (AND, OR)
Negations (NOT)
For example,
to perform a query to find all customers whose account balance is greater
than $10,000, you would use the following select statement:
SELECT lname, fname, balance FROM account WHERE balance > 10000
|
Modifying Data in the Database
The data
contained in a database can be modified by adding, deleting or updating
rows. For each of these operation, the DBMS will return how many rows
are affected.
Adding Data to the Database
The INSERT
statement is used to add a new row to a table. The basic syntax of the
INSERT statement is:
INSERT INTO table_name(column_names) VALUES value_list
|
The INSERT
statement is made up of two components: INSERT INTO and VALUES. The
function of these components is listed below:
INSERT INTO - specifies the table you want to insert a row into. It
can optionally contain a column list to specify that data should only
be inserted into those columns. Columns not in this list will be inserted
with NULL values.
VALUES - specifies the data value you want to insert. You can insert
values by using constants or parameters.
As stated
above, the value list may contain constants or parameters.
A constant is any numeric, text or date value that can be expressed
in text form, such as `John', `Monday', 123, 54.823, etc. An example
of the INSERT command using constants is shown below. This example adds
a new account for John Smith to the database:
INSERT INTO account (lname, fname, branch, balance)
VALUES ('john', 'smith', 101, 10000)
|
Parameter
data is represented by a question mark (?) in the value list, and values
can be inserted later. Parameters can be used when the data values are
unknown at preparation time, or when you want to save preparation time.
An example of the INSERT command using parameters is shown below. This
example is used to insert rows into the database, but the values are
not currently known. The actual values to be inserted can be bound before
execution:
INSERT INTO account (lname, fname, branch) VALUES (?,?,?)
|
Note:
To learn how to prepare statements and bind parameters to ODBC functions,
please refer to Section 4 of this chapter.
Deleting Data from the Database
The DELETE
statement deletes one or more rows from a table. The basic syntax of
the DELETE statement is:
DELETE FROM table_name WHERE search_condition
|
The DELETE
statement is made up of two components: DELETE FROM and WHERE. The function
of these components is listed below:
DELETE FROM - specifies the table you want to delete rows from.
WHERE - specifies the search conditions that must be met to delete a
row.
The WHERE
clause may contain multiple search conditions. For a list of search
conditions that can be included in the WHERE clause, refer to "Retrieving
Data from the Database".
The following
example will delete the account for John Smith from the database:
DELETE FROM account WHERE fname = 'john' AND lname = 'smith'
|
Updating the Data in the Database
The UPDATE
statement changes data in existing rows in a table. The basic syntax
of the UPDATE statement is:
UPDATE table_name SET column_names expression WHERE search_condition
|
The UPDATE
statement is made up of three components: UPDATE, SET and WHERE. The
function of these components is listed below:
UPDATE - specifies the table you want to update rows in.
SET - specifies the columns you want to change and an expression that
defines the changes to be made for each column.
WHERE - specifies the search conditions that must be met to update a
row.
The WHERE
clause may contain multiple search conditions. For a list of search
conditions that can be included in the WHERE clause, see the SQL Command
and Function Reference.
The following
example will add 6% interest to all accounts with a balance greater
than $1000.
UPDATE account SET balance = balance * 1.06 WHERE balance > 1000
|
Note:
To find out how many rows have been inserted, deleted or updated,refer
to the information about the SQLRowCount function.
4.2 Executing SQL Statements With ODBC
This section
will serve as a guide for writing a simple ODBC program. As the previous
section mentioned, every SQL statement can be executed via ODBC in a
program. For example, suppose we have connected to a database successfully
and want to use the following SQL statements to construct a simple table:
dmSQL> CREATE TABLE account (lname CHAR(15), fname CHAR(10),
2> branch INTEGER);
dmSQL> INSERT INTO account VALUES('Mulder', 'Fox', 11240);
|
Then the
corresponding ODBC statements would be:
retcode = SQLAllocStmt(hdbc, &htmt);
retcode = SQLExecDirect(hstmt,"CREATE TABLE account (lname CHAR(15),
fname CHAR(10), branch integer)", SQL_NTS);
retcode = SQLExecDirect(hstmt,"INSERT INTO account VALUES('Mulder',
'Fox', 11240)", SQL_NTS);
|
SQLAllocStmt
All ODBC
functions that handle SQL statements need a statement handle. A statement
handle is a pointer to a location where all information about an SQL
statement resides. So, before executing an SQL statement via SQLExecDirect,
we need to use SQLAllocStmt to allocate a statement handle.
The prototype
for SQLAllocStmt is:
RETCODE SQLAllocStmt(
HDBC hdbc,
HSTMT FAR *phstmt
);
|
If the
return code is SQL_SUCCESS, you have successfully allocated a valid
statement handle from the driver. You can then proceed to the next ODBC
function, SQLExecDirect.
SQLExecDirect
SQLExecDirect
is used to execute an SQL statement directly. Many ODBC books call this
direct execution, as opposed to another method of execution known
as prepared execution. Prepared execution will be explained later.
The prototype
for SQLExecDirect is:
RETCODE SQLExecDirect(
HSTMT hstmt,
UCHAR FAR *szSqlStr,
SWORD cbSqlStr
);
|
The first
argument, hstmt, is a valid statement handle, the second argument
is the SQL statement string to be executed, and the last argument is
the string length of the SQL statement or SQL_NTS if szSqlStr
points to a null terminated string.
The execution
of SQLExecDirect is performed in two parts. First it compiles
(prepares) the SQL statement by checking referenced object names and
grammar, choosing an access plan and converting the statement into an
internal executable form. Then in the second phase, it executes the
executable form to actually access the database.
If the
SQL statement is a query like SELECT * FROM account, then a result set
of selected rows is produced and you need to use SQLFetch to
get the returned data row by row from the result set (See Chapter
5). If the SQL statement is an INSERT, DELETE, or UPDATE statement,
you can use SQLRowCount to see how many rows were affected.
SQLRowCount
This function
returns the number of rows affected by INSERT, DELETE or UPDATE statements
executed in the statement handle.
The prototype
for SQLRowCount is:
RETCODE SQLRowCount(
HSTMT hstmt,
SDWORD FAR *pcrow
);
|
If hstmt
is associated with an UPDATE statement, the pcrow will return the number
of updated rows after executing the UPDATE statement. For example:
SDWORD count;
SDWORD retcode;
retcode = SQLAllocStmt(hdbc, &hstmt);
retcode = SQLExecDirect(hstmt,"CREATE TABLE account (lname name,
fname name, branch integer, balance money)",
SQL_NTS);
/* insert three records into account table */
retcode = SQLExecDirect(hstmt, "INSERT INTO account VALUES(`Mulder',
`Fox', 11240, 10000.00)", SQL_NTS);
retcode = SQLExecDirect(hstmt, "INSERT INTO account VALUES(`Scully',
`Dana', 11330, 20000.00)", SQL_NTS);
retcode = SQLExecDirect(hstmt, "INSERT INTO account VALUES(`Skinner',
`Walter', 11240, 30000.00)", SQL_NTS);
/* if branch is 11240, add 1000 to balance */
retcode = SQLExecDirect(hstmt, "UPDATE account SET balance = balance
+ 1000.00 WHERE branch = 11240", SQL_NTS);
/* get the number of updated rows from count in the example. */
/* Count will be two. */
retcode = SQLRowCount(hstmt, &count);
|
Example
4-1: Using SQLRowCount
If hstmt
is not associated with an INSERT, DELETE or UPDATE statement, the row
count will be -1 for DBMaker.
SQLFreeStmt
You can
use SQLFreeStmt to close or drop a statement handle.
The prototype
for SQLFreeStmt is:
RETCODE SQLFreeStmt (
HSTMT hstmt,
UWORD fOption
);
|
The first
argument (hstmt) is a valid statement handle and the second is
an option that specifies how the statement handle is freed. Two commonly
used options for freeing the statement handle are SQL_CLOSE and SQL_DROP.
If the
statement is not a select statement, a statement handle can be reused.
For example:
SQLExecDirect(hstmt1, "INSERT ...");
SQLExecDirect(hstmt1, "CREATE ...");
SQLExecDirect(hstmt1, "INSERT ...");
|
If the
statement is a select statement, you need to close the statement handle
before using it again. For example:
retcode = SQLExecDirect(hstmt,"SELECT * FROM account", SQL_NTS);
...
retcode = SQLFreeStmt(htmt,SQL_CLOSE);
retcode = SQLExecDirect(hstmt,"INSERT INTO account VALUES('Mulder',
'Fox', 11240)", SQL_NTS);
|
By using
SQL_CLOSE to close and reuse a statement handle, you don't have to allocate
a new statement handle every time you want to execute a statement after
a selection. However, if in doubt, you can use the SQL_DROP option to
drop the statement handle and allocate a new one. Drop will release
all resources associated with the statement handle. After a drop, you
can NOT reuse the handle.
Prepared Execution: SQLPrepare and SQLExecute
As described
in the section on SQLExecDirect, direct execution is performed
in two parts: preparation and execution. If you want to execute a statement
repeatedly, you can use prepared execution to improve performance.
Prepared
execution divides the execution life of a statement into two parts using
ODBC function calls: preparation (SQLPrepare) and execution (SQLExecute).
The idea is to prepare the statement into executable form only once
and then execute it many times. The prototypes of these two functions
are:
RETCODE SQLPrepare(
HSTMT hstmt,
UCHAR *szSqlStr,
UDWORD cbSqlStr
);
|
and
RETCODE SQLExecute(HSTMT hstmt);
|
In SQLPrepare,
hstmt is a valid statement handle, szSqlStr is the SQL
statement string to be executed and cbSqlStr is the length of
the string szSqlStr or SQL_NTS if the string is null-terminated.
Prepared execution is most useful when combined with parameters, which
is the topic of the next section.
4.3 Parameters
In this
section we will introduce parameters, which are used in SQL statements
to pass a data value to an SQL command at execution time in an ODBC
application program. The concept is similar to the host variables used
in embedded SQL.
What is a Parameter?
A parameter
is used in a SQL statement when:
values of the parameters are unknown at preparation time
applications need to execute the same SQL statment several times with
different parameter values
applications need to convert the parameter values between different
data types
For example,
an application wants to insert five rows into a table named account.
INSERT INTO account (lname, fname, branch) VALUES (?,?,?)
|
In this
statement, ? is the parameter marker. By using parameters, the application
only needs to prepare this statement once, and then execute the prepared
statement five times with different parameter values to insert five
rows into the account table.
Parameter Functions
There are
three ODBC functions for dealing with parameters: SQLBindParameter,
SQLDescribeParam, SQLNumParams.
SQLBindParameter is used to bind a storage location to a parameter
marker and specify the data type, precision and scale of the storage
location.
SQLNumParams is used by an application to get the number of parameters
in an SQL statement. It is especially useful for an interactive dynamic
SQL application.
SQLDescribeParam is used to describe the attributes of a specified
parameter, like length or precision. It is also used for dynamic SQL
applications.
SQLBindParameter
The prototype
for SQLBindParameter is:
RETCODE SQLBindParameter(
HSTMT hstmt,
UWORD ipar,
SWORD fParamType,
SWORD fCType,
SWORD fSqlType,
UDWORD cbColDef,
SWORD ibScale,
PTR rgbValue,
SDWORD cbValueMax,
SDWORD FAR *pcbValue
);
|
An application
needs to pass the following information to SQLBindParameter:
hstmt - statement handle
ipar - ith parameter
fParamType - parameter type, input/output
fCType - parameter host language type
fSqlType - SQL column type
cbColDef - precision of the column
ibScale - scale of the column
rgbValue - address of the storage
cbValueMax - not used in DBMaker
pcbValue - length of the parameter in rgbValue
The following
example is used to illustrate how several rows of data with different
values can be inserted into a database by using SQLBindParameter
to bind the row values to parameters before SQLExecute is called.
Note that when SQLExecute is called for the third time, a NULL
will be inserted into the branch column by setting pcbValue
in SQLBindParameter to SQL_NULL_DATA.
#define LENGTH 18
UCHAR lname[LENGTH], fname[LENGTH];
UDWORD branch_no;
SDWORD retcode, cblname, cbfname, cbbranch;
retcode = SQLPrepare(hstmt,"INSERT INTO account (lname,fname,branch)
VALUES (?,?,?)",SQL_NTS);
err_exit(hstmt, retcode); /* exit if error */
cblname = SQL_NTS; /* null terminated string */
cbfname = 0;
cgbranch = 0;
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, lname, 0, &cblname);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, fname, 0, &cbfname);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, branch_no, 0, &cbbranch);
strcpy(lname, "Mulder");
cblname = strlen(lname);
strcpy(fname, "Fox");
cbfname = strlen(fname);
branch_no = 11240;
retcode = SQLExecute(hstmt);
strcpy(lname, "Scully");
cblname = strlen(lname);
strcpy(fname, "Dana");
cbfname = strlen(fname);
branch_no = 11251;
retcode = SQLExecute(hstmt);
/* insert a NULL data to branch column for the third customer whose */
/* branch number is unknown */
strcpy(lname, "Angus");
cblname = strlen(lname);
strcpy(fname, "MacGyver");
cbfname = strlen(fname);
cbbranch = SQL_NULL_DATA; /* indicate NULL for branch column */
retcode = SQLExecute(hstmt);
|
Example
4-2: Using SQLBindParameter
To set
a parameter value, an application should perform the following steps:
Call SQLBindParameter to bind a storage location to a parameter
marker.
Put the parameters value in the storage location.
The first
step can be done before or after calling SQLPrepare, but should
be done before calling SQLExecute. The second step should be
done before SQLExecute since the driver needs the parameter values
to execute the SQL statement.
There are
three parameter types that can be used for fParamType in SQLBindParameter:
SQL_PARAM_INPUT
SQL_PARAM_INPUT_OUTPUT
SQL_PARAM_OUTPUT
Note:
Currently DBMaker only supporst SQL_PARAM_INPUT and SQL_PARAM_INPUT_OUTPUT.
Both are used for input.
The parameter
is stored in the storage location rgbValue. When putting the
parameter values into rgbValue, you should use the C data types
specified in the fCType argument of SQLBindParameter.
The pcbValue
argument in SQLBindParameter is a pointer to a buffer that contains
the parameter length, but it can also be used for several different
purposes. Some of the possible values stored in pcbValue before
calling SQLExecute or SQLExecDirect are:
the length of the parameter, only useful for character or binary C data.
SQL_NTS
to indicate that the parameter value is a null-terminated string.
SQL_NULL_DATA
to indicate that the parameter value is NULL, as shown in the previous
code example.
SQL_DEFAULT_PARAM
to indicate the default value of the column will be used
SQL_DATA_AT_EXEC
or SQL_LEN_DATA_AT_EXEC to indicate the data for the parameter will
be sent with SQLPutData. This will be covered in detail in Section
4.4.
SQLNumParameter
The prototype
for SQLNumParams is:
RETCODE SQLNumParams(
HSTMT hstmt,
SWORD FAR *pcpar
);
|
When this
function is called, the driver will put the number of parameters in
the SQL statement in the buffer pcpar. This number will be zero
if the SQL statement contains no parameters. Note that this function
can only be called after the SQL statement is prepared (i.e. SQLPrepare
has been called).
SQLDescribeParam
The prototype
for SQLDescribeParam is:
RETCODE SQLDescribeParam(
HSTMT hstmt,
UWORD ipar,
SWORD FAR *pfSqlType,
UDWORD FAR *pcbColDef,
SWORD FAR *pibScale,
SWORD FAR *pfNullable
);
|
If an application
has all the information necessary for SQLBindParameter, then
it can call SQLBindParameter directly as shown in Example
4-2. In other cases, applications may lack detailed information
about the parameters before calling SQLBindParameter to set them.
In this situation, SQLDescribeParam can be used to get the information.
For example, in dynamic SQL applications such as a graphical query tool,
the SQL statements to be used are undetermined until run time. This
kind of application needs to obtain detailed information about parameters
at tun time so that they can bind the parameters.
SQLDescribeParam
returns the description of the specified parameter marker associated
with a perpared SQL statement. Figure 4-3
shows the general calling flow of SQLDescribeParam, SQLNumParams
and SQLBindParameter.
Figure
4-3: Program flow when using parameters.
The following
example illustrates the use of SQLDescribeParam, SQLNumParams
and SQLBindParameter in a dynamic SQL application. Note that
the parameter marker number is ordered sequentially from left to right,
starting at 1.
#define BUFFER_LEN 256 /* length of the SQL string buffer */
#define MAX_PARAMS 32 /* maximum number of parameters */
UCHAR str[BUFFER_LEN];
SDWORD retcode;
SWORD i, nparam;
SWORD partype[MAX_PARAMS], parscale[MAX_PARAMS], parnull[MAX_PARAMS];
SWORD parCtype[MAX_PARAMS];
UDWORD parlen[MAX_PARAMS], outlen[MAX_PARAMS];
char *parbuf[MAX_PARAMS];
BEGIN: /* begin label */
getSQLString(&str); /* get SQL statement string */
retcode = SQLPrepare(hstmt,str,SQL_NTS); /* prepare the SQL string */
err_exit(hstmt, retcode); /* exit if error */
retcode = SQLNumParams(cmdp,&nparam); /* get number of parameters */
err_exit(hstmt, retcode); /* exit if error */
if (nparam > 0) /* string has parameters? */
{
printf("There are %d parameters \n",nparam);
for (i = 0; i < nparam; i++) /* describe/set parameters */
{
retcode = SQLDescribeParam(cmdp, i+1, &partype[i], &parlen[i],
&parscale[i], &parnull[i]);
err_exit(hstmt, retcode); /* exit if error */
/* allocate storage location for the parameter according to */
/* the parameter type, length, and scale. Reuse the storage */
/* if possible */
allocParamStorage(partype[i], parlen[i], parscale[i],
&parbuf[i]);
/*get C type corresponding to SQL type */
getSQLCtype(partype, &parCtype);
/* bind the parameter to storage location */
retcode = SQLBindParameter(cmdp, i+1, parCtype[i], partype[i],
parlen[i], parscale[i], parbuf[i],
BUFFER_LEN, &outlen[i]);
err_exit(hstmt, retcode); /* exit if error */
}
for (i = 0; i < nparam; i++)
{
/* get parameter values and store them in bound storage */
getParamValue(nparam, parCtype[i], partype[i], parlen[i],
parscale[i], &parnull[i], parbuf[i]);
}
} /* end of if statement */
retcode = SQLExecute(hstmt); /* execute prepared stmt */
err_exit(hstmt, retcode);
if (user_Quit()) /* user wants to quit */
return;
else
goto BEGIN; /* go to BEGIN */
|
Example
4-3: Using parameters in a dynamic SQL application
When to Use Parameters
Here we
use some examples to explain when applications need to use parameters.
As described in previous sections, an application might use parameters
when:
values of the parameters are unknown at preparation time (like the dynamic
SQL code in Example 4-3.).
applications need to execute the same SQL statement several times with
different parameter values (like Example 4-2
which used the same parameters to insert three rows into a database).
applications need to convert the parameter values between different
data types. For example, an application may need to use character strings
to get all input values for convenience reasons, then insert these values
into tables in the database. In this case, it can use parameter markers
to accept all values, then convert these values to their corresponding
column types so the driver can insert them into the database correctly.
Notice
that when an SQL statement to be executed contains parameters, the parameter
values should be set before each call of SQLExecute like Example
4-2 and Example 4-3.
Using Parameters in SQLExecDirect
As stated
before, when an application wants to execute an SQL statement more than
once, it may call SQLPrepare first, then call SQLExecute
several times to save the effort of preparing the same SQL statement
for each execution and improve performance.
Parameters
can also be used in an SQL statement that is executed only once by using
SQLExecDirect. However, you must bind the parameters and set
the parameter values before calling SQLExecDirect anyway, so
you lose all of the advantages you get when using parameters with SQLPrepare
and SQLExecute.
In general,
there is no need to use parameters with SQLExecDirect unless
the data you want to input is a special case, such as a BLOB. In the
case of a BLOB, you must use the SQL_DATA_AT_EXEC or the SQL_LEN_DATA_AT_EXEC
options to bind the parameter at execution time, but the data values
are not set until after the statement is executed. (Also refer to Section
4.4 for more information on entering
large data items.)
#define LENGTH 18
UCHAR lname[LENGTH], fname[LENGTH];
UDWORD branch_no;
SDWORD retcode, cblname, cbfname, cbbranch;
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, lname, 0, &cblname);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, fname, 0, &cbfname);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, branch_no, 0, &cbbranch);
strcpy(lname, "Bill");
cblname = strlen(lname);
strcpy(fname, "Skinner");
cblname = strlen(fname);
branch_no = 11243;
retcode = SQLExecDirect(hstmt, "INSERT INTO account (lname, fname,
branch) VALUES (?,?,?)", SQL_NTS);
err_exit(hstmt, retcode);
|
Example
4-4: Using parameters with SQLExecDirect
Clearing Bound Parameters
After a
storage location is bound by calling SQLBindParameter, it can
be reused again and again, like Example 4-4.
In the example, three storage locations are bound to the three parameter
markers in the INSERT statement, and remain bound until they are explicitly
unbound.
The storage
locations are unbound when the application calls SQLFreeStmt
with the SQL_RESET_PARAMS option or the SQL_DROP option. Notice that
the three storage locations belong to the same statement handle, and
when SQLFreeStmt is called, all the storage locations bound in
the statement handle will be unbound. If the application uses the SQL_RESET_PARAMS
option, it can reset the statement handle and bind a different storage
location.
#define LENGTH 18
UCHAR lname [length], fname[length];
UDWORD branch_no;
SDWORD retcode, cblname, cbfname, cbbranch;
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, lname, 0, &cblname);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_CHAR, LENGTH, 0, fname, 0, &cbfname);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, branch_no, 0, &cbbranch);
... (use the three parameters to execute some SQL commands)
/* reset the parameters */
retcode = SQLFreeStmt(hstmt, SQL_RESET_PARAMS);
|
Example
4-5: Clearing bound parameters
If the
application uses the SQL_DROP option in SQLFreeStmt, then the
statement handle is released and becomes invalid. This means the applications
cannot use that handle anymore, or it may cause some unpredictable errors.
4.4 Entering Large Data
DBMaker
provides two methods to input BLOB data into a database. One method
uses a small, fixed size buffer to read a portion of the BLOB data and
enter it into the database. By repeating this process several times,
the entire BLOB can be input without having to use a single large buffer.
The second method is even more convenient. DBMaker provides a File Object
data type to allow users to store their BLOB data in an external file.
How to Enter Large Data
When you
need to enter large chunks of data into a long varchar or long varbinary
column, you can use the SQLPutData and SQLParamData functions
to enter the data in smaller pieces. This way you don't need a large
buffer to store the whole chunk of data as you would if you entered
it all at once.
The prototype
for SQLParamData is:
RETCODE SQLParamData(
HSTMT hstmt,
PTR FAR *prgbValue
);
|
The prototype
for SQLPutData is:
RETCODE SQLPutData(
HSTMT hstmt,
PTR rgbValue,
SDWORD cbValue
);
|
SQLParamData
is used to check whether there are any parameters that require data,
and the data is entered using SQLPutData. This process continues
until data has been entered for all of the parameters in the SQL statement.
The process flow is shown in Figure 4-4.
The detailed
steps for using SQLParamData and SQLPutData to enter large
data objects into a database are:
Bind the parameters - Set the pcbValue argument in the SQLBindParameter
function to either SQL_DATA_AT_EXEC or SQL_LEN_DATA_AT_EXEC. This lets
the ODBC driver know that you will provide values for this parameter
at execution time with SQLPutData.
Execute the SQL command - Execute the SQL statement with SQLExecDirect
or SQLExecute. When there are parameters that are expecting data
at execution time, the call will return SQL_NEED_DATA.
Find the first parameter expecting data at execution time - Call SQLParamData
to indicate that the first parameter expecting data at execution time
should start to receive data.
Figure
4-4: Process flow for entering large data.
Call SQLPutData - Prepare your next piece of data in the buffer
and call SQLPutData to send it to the database for the parameter
currently waiting for data. Repeat this step until all of the data for
this parameter is sent.
Call SQLParamData - If the return code is SQL_NEED_DATA, the
next parameter that is expecting data at execution time is ready to
receive data, and you should go back to Step 4. If the return code is
SQL_SUCCESS or SQL_SUCCESS_WITH_INFO, then all data for all parameters
expecting data at execution time has been sent and the SQL statement
has completed its execution.
In the
following example, we will insert a record into the account table.
InitUserData() opens a customers data file which includes the
customers photograph, signature and a memo field
to be entered into the account table. GetUserData() retrieves
the next MAX_DATA_SIZE bytes of data from the users data file into the
data buffer InputData until all data has been read. SQLPutData
sends data from the buffer to the database.
#define MAX_DATA_SIZE 2048
SDWORD cbPhoto, cbStamp, cbMemo;
SDWORD DataLen;
PTR pParm, DataFile;
UCHAR InputData[MAX_DATA_SIZE];
SDWORD retcode;
retcode = SQLPrepare(hstmt, (UCHAR *)"INSERT INTO account VALUES(`Mark',
,'Greene',2, 40000.00, 'xxxx', ?, ?, ?)",SQL_NTS);
if (retcode == SQL_SUCCESS)
{
/* Bind the parameters. Set cbPhoto, cbStamp and cbMemo by */
/* SQL_DATA_AT_EXEC to let ODBC know that we will be providing */
/* values of these parameters at statement executing time */
cbPhoto = cbStamp = cbMemo = SQL_DATA_AT_EXEC;
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBIANRY, 0, 0, NULL, 0,
&cbPhoto);
retcode = SQLBindParameter(hstmt, 2, SQL_PARAM_INPUT, SQL_C_BINARY,
SQL_LONGVARBIANRY, 0, 0, NULL, 0,
&cbStamp);
retcode = SQLBindParameter(hstmt, 3, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_LONGVARCHAR, 0, 0, NULL, 0,
&cbMemo);
retcode = SQLExecute(hstmt);
if (retcode == SQL_NEED_DATA) /* any large data ? */
{
Parm = 0;
while ((retcode = SQLParamData(hstmt, &pAddr)) == SQL_NEED_DATA)
{ /* need to put large data for this column */
Parm++; /* in loop to get/put data in blob */
InitUserData(Parm, DataFile);
while (GetUserData(Parm, DataFile, InputData, &DataLen))
retcode = SQLPutData(hstmt, InputData, DataLen);
}
if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO)
printf("insert a record of account table success !! \n");
}
}
|
Example
4-6: Inserting data with SQLParamData and SQLPutData
Cancelling the Execution of SQLPutData
If an error
occurs while entering data into the database or we decide not to continue
entering data, we can cancel the process by using the SQLCancel
function.
The prototype
for SQLCancel is:
RETCODE SQLCancel(HSTMT hstmt);
|
You can
call SQLCancel and the whole statement will be aborted. After
canceling the current execution of a statement, you can call SQLExecute
or SQLExecDirect again.
Placing Large Data in a File Object
A File
Object (FO) is a powerful large object data type supported by DBMaker.
A FO is similar to LONG VARCHAR or LONG VARBINARY data, but it is stored
as an external file on your host file system. A FO column is created
by defining the column with the FILE data type. In the following example,
the photograph column is a FO column.
create table student (name char(20), photograph file)
|
Since a
FO is treated as BLOB data, you can use all methods of BLOB insertion
to insert data into FO columns. By setting fSQLType to SQL_LONGVARCHAR
or SQL_LONGVARBINARY, DBMaker will create a new file for each FO column
and copy data from the input buffer (when fCType is SQL_C_CHAR)
or from a file on the client side (when fCType is SQL_C_FILE).
Instead
of creating another file, you may want to link a FO column to an existing
file on the server side, such as a file on a CD-ROM. When you want to
link a server side file, set fCType to SQL_C_CHAR and fSqlType
to SQL_FILE. When using this method, you need to copy the file name
into the buffer before calling SQLExecute.
|
fSqlType
|
FO
Column
|
fCType
|
Data
Source
|
|
SQL_LONGVARCHAR
or SQL_LONGVARBINARY
(same
as BLOB)
|
Create
a new file on the server side.
|
SQL_C_FILE
|
Copy
data from a file on the client side.
|
|
|
|
SQL_C_CHAR
SQL_C_BINARY
|
Copy
data from the input buffer.
|
|
SQL_FILE
|
Link
to an existing file on the server side.
|
SQL_CHAR
|
Get
file name from the input buffer.
|
In the
following example a new record is inserted for a customer named Mary,
along with her photo which is stored as a File Object. The file already
exists on the server side, so we link this file into the database.
UCHAR pPhotoFlName[80];
SDWORD retcode;
retcode = SQLPrepare(hstmt, "INSERT INTO student VALUES('Mary', ?)",
SQL_NTS);
retcode = SQLBindParameter(hstmt, 1, SQL_PARAM_INPUT, SQL_C_CHAR,
SQL_FILE, 80, 0, pPhotoFlName, 0, SQL_NTS);
strcpy(pPhotoFlName, "/disk1/sys/fo/photo"); /* pass the file name */
retcode = SQLExecute(hstmt);
|
Example
4-7: Placing large data in a File Object
Since a
FO is stored as an external file, it has the advantage that applications
used to edit the file can still work on the file directly.
4.5 Get Options of Statement Handle
The current
setting of a statement option in a statement handle can be obtained
by using the SQLGetStmtOption function.
The prototype
for SQLGetStmtOption is:
RETCODE SQLGetStmtOption(
HSTMT hstmt,
UWORD fOption,
PTR pvParam
);
|
where hstmt
is a valid statement handle, fOption is the option to be retrieved
and pvParam is the value associated with fOption. Depending
on the value of fOption, a 32-bit integer value or a pointer
to a null-terminated character string will be returned in pvParam.
In DBMaker,
this function is used for some special purposes. There are several extended
statement options in DBMaker:
SQL_GET_INCREMENT_VALUE
- used to get the value of a SERIAL column
SQL_GET_CURRENT_OID
- used to get the OID of the most recently inserted/fetched tuple
SQL_GET_BACKUP_ID
- used to get the backup id
SQL_DUMP_PLAN
- used to get/set dump plan options
SQL_PLAN
- used to get the plan length
SQL_PLAN_LEN
- used to get the plan length
In the
following example, the table account contains a SERIAL data type
column. The value of a SERIAL column is incremented automatically, and
users don't need to explicitly specify it. However, after inserting
a record, users may want to know the value of the SERIAL column just
inserted. This value can be obtained by calling SQLGetStmtOption
with fOption SQL_GET_INCREMENT_VALUE.
For example:
/* insert a record into table account where the value of each field */
/* is it's default value */
SDWORD val;
SDWORD retcode;
retcode = SQLExecDirect(hstmt, "INSERT INTO account VALUES()", SQL_NTS);
/* get the serial number that was just inserted */
retcode = SQLGetStmtOption(hstmt, SQL_GET_INCREMENT_VALUE, &val);
|
Example
4-8: Getting the value of a SERIAL column using SQLGetStmtOption
In this
example, the variable val will be the value of the SERIAL column
which is just inserted into the table account in the previous INSERT
statement. For the definition and the use of the SERIAL data type, please
refer to the SQL Command and Function Reference.
Another
special use of SQLGetStmtOption is to get the OID of the most
recently inserted or fetched tuple. Continuing with the previous example,
you can submit SQLGetStmtOption with fOption SQL_GET_CURRENT_OID
to get the OID of the record just inserted into the database.
For example:
UCHAR oid[8];SDWORD retcode;
/* insert a recored into account table */
retcode = SQLExecDirect(hstmt, "INSERT INTO account VALUES()", SQL_NTS);
/* get the OID of the record just inserted */
retcode = SQLGetStmtOption(hstmt, SQL_GET_CURRENT_OID, &oid);
|
Example
4-9: Getting the current object's OID using SQLGetStmtOption
An OID
is an unique ID for an object in DBMaker. You can use the OID to uniquely
specify an object in database. For example, you can use OID in the WHERE
clause of a query:
SELECT * FROM account WHERE OID = ?
|
For more
detailed information on OID data type, please refer to the SQL Command
and Function Reference or the Database Administrator's Reference.
The extended
statement options SQL_DUMP_PLAN, SQL_PLAN_LEN and SQL_PLAN are used
to get the query plan generated by the DBMaker Query Optimizer for a
prepared SQL statement.
To get
the plan of a prepared SQL statement, use the following procedure:
Turn on the SQL_DUMP_PLAN option by calling SQLSetStmtOption.
Get the length of the plan string by calling SQLGetStmtOption
with fOption SQL_PLAN_LEN
Allocate a buffer according to the plan string and then call SQLGetStmtOption
with fOption SQL_PLAN to get the plan string.
For example:
SDWORD planlen;
UCHAR *planstr;
SDWORD retcode;
/* turn on the dump plan option */
retcode = SQLSetStmtOption(hstmt, SQL_DUMP_PLAN, SQL_DUMP_PLAN_ON);
/* prepare an SQL JOIN statement */
retcode = SQLPrepare(hstmt, "SELECT * FROM account, branch WHERE
account.branchId = branch.branchId", SQL_NTS);
/* get the plan string length */
retcode = SQLGetStmtOption(hstmt, SQL_PLAN_LEN, &planlen);
/* allocate a buffer for the plan string according to the plan */
/* string length */
planstr = malloc(planlen);
/* get the plan string */
retcode = SQLGetStmtOption(hstmt, SQL_PLAN, planstr);
|
Example
4-10: Getting an SQL statement's query plan using SQLGetStmtOption
|
