Chapter 46 – SQL/CLI: desc Functions¶
Note
You are reading a digital copy of SQL-99 Complete, Really, a book that documents the SQL-99 standard.
For more information specific to CrateDB, check out the CrateDB Reference.
In this chapter, we’ll describe the fourth essential CLI resource: the
desc
. We have much to say about:
What is in a
desc
– fields of the header and the “item descriptor areas”.How to make a
desc
, or how to use an automatically-madedesc
.Similarities and differences of ARDs, APDs, IRDs and IPDs.
Functions which attack
desc
s directly:SQLAllocHandle
,SQLGetDescField
,SQLSetDescField
,SQLGetDescRec
,SQLSetDescRec
,SQLCopyDesc
and others.Functions which attack descs indirectly:
SQLBindParameter
,SQLBindCol
,SQLColAttribute
,SQLDescribeCol
and others.
Table of Contents
-
-
SQLBindParameter Effect on IPD.IDA Fields for Numeric <data type>s
SQLBindParameter Effect IPD.IDA Fields for Datetime <data type>s
SQLBindParameter Effect on IPD.IDA Fields for Interval Year/Month <data type>s
SQLBindParameter Effect on IPD.IDA Fields for Interval Day/Time <data type>s with No SECONDs
SQLBindParameter Effect on IPD.IDA Fields for Interval Day/Time <data type>s with SECONDs
SQLBindParameter Effect on IPD.IDA Fields for Other <data type>s
Descriptor Areas¶
Here is a piece of CLI code:
SQLExecDirect(hstmt,"SELECT col_1,col_2 FROM T WHERE col_1=?",SQL_NTS);
SQLFetch(hstmt);
The SELECT
statement in this example contains a question mark: it
represents a parameter marker – that is, it means “there is an input parameter
here”. (An input parameter is a host variable value which travels from the
application to the DBMS. For some CALL
statements a parameter may be an
output parameter, but the normal case is that parameters are input parameters.)
The input will happen during SQLExecDirect
.
The SELECT
statement also contains a two-Column select list
(col_1,col_2
). The existence of a select list implies “there are output
rows here”. (An output row consists of one or more Columns in a result set,
whose values travel from the DBMS to the application.) The output will happen
during SQLFetch
.
What are the addresses of the host variables? How will NULL
values be
flagged? What is already known, or can be specified, about the characteristics
of each value: <data type>, size, precision, scale, Character set, etc.?
Descriptor areas – or desc
s – are available to handle such questions,
for both parameters and row Columns.
Automatic descs¶
There are four automatic desc
s; here’s a brief description of each:
IRD, or Implementation Row Descriptor. Its usual use: to find out what the DBMS knows about a result set. The IRD is filled in when you call
SQLPrepare
for aSELECT
statement.ARD, or Application Row Descriptor. Its usual use: to tell the DBMS how to transfer a result set to the host. The important fields of the ARD must be filled in by the host program.
IPD, or Implementation Parameter Descriptor. Its usual use: to provide information about the DBMS’s view of parameters. The DBMS may be capable of “populating” the IPD fields; otherwise, the programmer must take some responsibility for this job.
APD, or Application Parameter Descriptor. Its usual use: to the tell the DBMS how to transfer an input parameter. (Parameters are marked in an SQL statement with “?” parameter markers.)
Here’s a pseudo struc
declaration, for future reference in this chapter’s
examples.
desc struc {
SQL_DESC_ALLOC_TYPE smallint, /* header fields ... */
SQL_DESC_COUNT smallint,
SQL_DESC_DYNAMIC_FUNCTION char[],
SQL_DESC_DYNAMIC_FUNCTION_CODE integer,
SQL_DESC_KEY_TYPE smallint,
IDA struc occurs n times {
SQL_DESC_CHARACTER_SET_CATALOG varchar[], /* IDA[n] fields ... */
SQL_DESC_CHARACTER_SET_SCHEMA varchar[],
SQL_DESC_CHARACTER_SET_NAME varchar[],
SQL_DESC_COLLATION_CATALOG varchar[],
SQL_DESC_COLLATION_SCHEMA varchar[],
SQL_DESC_COLLATION_NAME varchar[],
SQL_DESC_DATA_POINTER void*,
SQL_DESC_DATETIME_INTERVAL_CODE smallint,
SQL_DESC_DATETIME_INTERVAL_PRECISION smallint,
SQL_DESC_INDICATOR_POINTER integer*,
SQL_DESC_KEY_MEMBER smallint,
SQL_DESC_LENGTH integer,
SQL_DESC_NAME varchar[],
SQL_DESC_NULLABLE smallint,
SQL_DESC_OCTET_LENGTH integer,
SQL_DESC_OCTET_LENGTH_POINTER integer*,
SQL_DESC_PARAMETER_ORDINAL_POSITION smallint,
SQL_DESC_PARAMETER_SPECIFIC_CATALOG varchar[],
SQL_DESC_PARAMETER_SPECIFIC_SCHEMA varchar[],
SQL_DESC_PARAMETER_SPECIFIC_NAME varchar[],
SQL_DESC_PRECISION smallint,
SQL_DESC_SCALE smallint,
SQL_DESC_TYPE smallint,
SQL_DESC_UDT_CATALOG varchar[],
SQL_DESC_UDT_SCHEMA varchar[],
SQL_DESC_UDT_NAME varchar[],
SQL_DESC_UNNAMED smallint
}
}
APD = desc;
IPD = desc;
ARD = desc;
IRD = desc;
Throughout this chapter, we’ll replace long-winded references using
program-like conventions. For example, it is often necessary to make statements
like this: “Within the Application Parameter Descriptor, in the nth
occurrence of the Item Descriptor Area, set the SCALE
attribute to 5
.”
We will make such statements this way:
"Set APD.IDA[n].SQL_DESC_SCALE = 5"
For a programmer, the second statement is shorter, clearer and better. We’ll
use such statements as convenient conventions, without implying that all DBMSs
store desc
information with this structure.
The desc Fields¶
On first reading, we suggest that you skip quickly through this section. But
bookmark this page – you’ll have to refer to the desc
field descriptions
many times.
The first entries in a descriptor area are the desc
“header” fields. Then
come the fields of the desc
“item descriptor area” (IDA), which is
multiple-occurrence. In our discussions of each group, entries are in
alphabetical order. Each entry begins with a field identifier (which is the
code used for identification), a <data type> and an indicator of the field’s
importance. For example:
SQL_DESC_ALLOC_TYPE 1099
Type: SMALLINT. Importance: Low.
This description should be read as follows: The SQL descriptor field is
identified by the code SQL_DESC_ALLOC_TYPE
, which is a number. The
sqlcli.h
line for this field is:
#define SQL_DESC_ALLOC_TYPE 1099
This book uses SQL_DESC_ALLOC_TYPE
as both a field identifier and a field
name. The field’s <data type> is SMALLINT
(short signed integer).
Relatively speaking, the field is of low importance (this is our subjective
estimate).
Each field entry contains some text description, accompanied by examples if the field is of high importance. Finally, each entry ends with a small chart. For example:
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
This chart should be read as follows: When this field is in the ARD, it may be
gotten by the user (presumably with the SQLGetDescField
function) and it
may be set by the SQLGetData
function or by the user (presumably with the
SQLSetDescField
function). And so on for the IRD, APD and IPD. In the
chart, the symbol [I]
means “this function is applicable only when the
AUTO POPULATE
flag is on” – which usually it isn’t, as you may recall from
the discussion of SQLSetEnvAttr
. The symbol [P]
means “this function is
legal only if the statement is prepared”. The chart does not show functions
which are, in functional effect, containers of other functions. For example,
many fields may be affected by SQLExecDirect
, but we won’t show that; we
only show SQLPrepare
and/or SQLExecute
. If either “May be Gotten By” or
“May be Set by” is blank for a particular automatic desc
, that means the
field is not affected by any “get” or “set” function (as appropriate) for that
desc
.
The desc Header Fields¶
There are five desc
header fields: SQL_DESC_ALLOC_TYPE
,
SQL_DESC_COUNT
, SQL_DESC_DYNAMIC_FUNCTION
,
SQL_DESC_DYNAMIC_FUNCTION_CODE
and SQL_DESC_KEY_TYPE
. Their
descriptions follow.
SQL_DESC_ALLOC_TYPE
SQL_DESC_ALLOC_TYPE 1099
Type: SMALLINT
. Importance: Low.
Possible values: SQL_DESC_ALLOC_AUTO (1)
or SQL_DESC_ALLOC_USER
(2)
. The value is set permanently when the desc
is created. Those
desc
s which are made implicitly by a call to
SQLAllocHandle(SQL_HANDLE_STMT,...)
are automatic desc
s; they will
have SQL_DESC_ALLOC_AUTO
in this field. Those desc
s which are made
explicitly by a call to SQLAllocHandle(SQL_HANDLE_DESC,...)
are user
desc
s; they will have SQL_DESC_ALLOC_USER
in this field.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_COUNT
SQL_DESC_COUNT 1001
Type: SMALLINT
. Importance: High
Provides the number of item descriptor areas in the desc
. Conceptually,
there is a correspondence between the value in SQL_DESC_COUNT
and the
number of actual items – the “number of parameters” (for IPDs and APDs) or
“the number of Columns” (for IRDs and ARDs). However, this correspondence is
not automatically true. You have to make it so.
Initially, the field value is 0
. The SQLPrepare
function will set
IRD.SQL_DESC_COUNT =
<the number of Columns in the select list>. If
“populate IPD” is true, the SQLPrepare
function will set
IPD.SQL_DESC_COUNT =
<the number of parameter markers in SQL statement>.
If you call a function which effectively creates a new IDA, then the COUNT
field value may go up. For example, if ARD.SQL_DESC_COUNT=0
and you call
SQLBindCol
for COLUMN_1
, then ARD.SQL_DESC_COUNT
is set to 1
.
In ODBC, the value goes down if you “unbind” the last IDA (by setting
desc.IDA[n].SQL_DATA_POINTER=0)
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
SQLGetData ,SQLGetDescRec , user |
SQLBindCol ,SQLSetDescRec , user |
IRD |
SQLGetDescRec[P] ,user[P] ,SQLNumResultCols[P] |
SQLPrepare |
APD |
SQLExecute ,SQLGetDescRec ,SQLGetParamData ,SQLParamData , user |
SQLBindParameter, SQLSetDescRec , user |
IPD |
SQLExecute ,SQLGetDescRec , user |
SQLBindParameter ,SQLPrepare[I] ,SQLSetDescRec , user |
SQL_DESC_DYNAMIC_FUNCTION
SQL_DESC_DYNAMIC_FUNCTION 1031
Type: VARCHAR
. Importance: Low.
This is an SQL3 field only; it’s not in ODBC. It provides an SQL_TEXT
string containing a copy of the prepared statement. For example, after:
SQLPrepare(hstmt,"INSERT INTO Table_1 VALUES (5)",SQL_NTS);
IRD.SQL_DESC_DYNAMIC_FUNCTION
will contain 'INSERT INTO Table_1 VALUES
(5)'
. You can get the same information with the SQLGetDiagField
function.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
|
|
SQL_DESC_DYNAMIC_FUNCTION_CODE
SQL_DESC_DYNAMIC_FUNCTION_CODE 1032
Data type: INTEGER
. Importance: Low.
This is an SQL3 field only; it’s not in ODBC. It provides a numeric code for the prepared statement. For example, after:
SQLPrepare(hstmt,"INSERT INTO Table_1 VALUES (5)",SQL_NTS);
IRD.SQL_DESC_DYNAMIC_FUNCTION_CODE
will contain 50
(you can find the
list of possible codes in our chapter on SQL/CLI diagnostics). You can get the
same information with the SQLGetDiagField
function.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
|
|
SQL_DESC_KEY_TYPE
SQL_DESC_KEY_TYPE 1029
Type: SMALLINT
. Importance: Low.
This is an SQL3 field only; it’s not in ODBC. If a SELECT
statement’s
select list contains all the Columns of the Table’s primary key, the value is
2
. If it contains all the Columns of one of the Table’s preferred candidate
keys, the value is 1
. Otherwise, the value is 0
. For example, suppose
that TABLE_1
has two Columns, COL_1
and COL_2
, and that
TABLE_1
's primary key is (COL_1)
. In that case:
SQLPrepare(hstmt,"SELECT * FROM Table_1",SQL_NTS);
will set IRD.SQL_DESC_KEY_TYPE = 1
, and:
SQLPrepare(hstmt,"SELECT COUNT(*) FROM Table_1",SQL_NTS);
will set IRD.SQL_DESC_KEY_TYPE = 0
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
The desc Item Descriptor Area (IDA) Fields¶
There are 28 desc
IDA fields:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
These fields are also known as the “Item Fields”, the “fields of the Descriptor Record” (an ODBC term) and as the “detail records”. Their descriptions follow.
Character Set Fields¶
SQL_DESC_CHARACTER_SET_CATALOG 1019
Type: VARCHAR
. Importance: Low.
SQL_DESC_CHARACTER_SET_SCHEMA 1020
Type: VARCHAR
. Importance: Low.
SQL_DESC_CHARACTER_SET_NAME 1021
Type: VARCHAR
. Importance: Low.
These three fields are SQL3 fields only; they’re not in ODBC. Together, they
make up the qualified name of a Character set and are meaningful only if the
item’s <data type> is CHAR
, VARCHAR
or CLOB
. Some example values
are: 'OCELOT'
, 'INFORMATION_SCHEMA'
and 'ISO8BIT'
(for the three
fields, respectively).
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
Collation Fields¶
SQL_DESC_COLLATION_CATALOG 1016
Type: VARCHAR
. Importance: Low.
SQL_DESC_COLLATION_SCHEMA 1017
Type: VARCHAR
. Importance: Low.
SQL_DESC_COLLATION 1018
Type: VARCHAR
. Importance: Low.
These three fields are ANSI SQL3 fields only; they’re not in ISO SQL3 or ODBC.
Together, they make up the qualified name of a Collation and are meaningful
only if the item’s <data type> is CHAR
, VARCHAR
or CLOB
. The
Standard says, ambiguously, that this is “the Character set’s Collation”.
Presumably it is, in fact, the item’s Collation.
For a select list, a standard DBMS may set these fields to a <Collation name>
– for example, 'OCELOT'
, 'INFORMATION_SCHEMA'
and 'POLISH'
(for
the three fields, respectively). You can change the fields, but it does not
matter – the DBMS never reads them.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_DATA_POINTER
SQL_DESC_DATA_POINTER 1010
Type: POINTER
. Importance: High.
Provides the address of a host variable. Meaningful in the APD (where it points to an input parameter), or in the ARD (where it points to a target for the result-set Column).
The initial value is 0
. When this field is set to a non-zero value, the
item is considered to be “bound” – a “bound parameter”, a “bound target”.
Application programmers must ensure that the address is valid.
This field is reset to 0
if a change is made to a non-pointer field in the
same IDA using the SQLSetDescField
function. For example: if, using
SQLSetDescField
, you set IPD.IDA[4].SQL_DESC_DATETIME_INTERVAL_CODE =
1
, the side effect is that IPD.IDA[4].SQL_DESC_DATA_POINTER = 0
. Moral:
change this field last.
In ODBC: the name is SQL_DESC_DATA_PTR
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
||
APD |
|
|
IPD |
|
SQL_DESC_DATETIME_INTERVAL_CODE
SQL_DESC_DATETIME_INTERVAL_CODE 1007
Type: SMALLINT
. Importance: Medium.
This field will only be meaningful if the SQL_DESC_TYPE field
is 9
(SQL_DATETIME)
or 10 (SQL_INTERVAL)
. It will contain a datetime subtype
(a number between 1
and 5
) or an interval subtype (a number between
1
and 13
). For example: if SQL_DESC_TYPE = 9
and
SQL_DESC_DATETIME_INTERVAL_CODE = 5
, then the item’s precise <data type> is
known to be TIMESTAMP WITH TIME ZONE
. If you change the SQL_DESC_TYPE
field to 9
or 10
, you must change SQL_DESC_DATETIME_INTERVAL_CODE
too.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_DATETIME_INTERVAL_PRECISION
SQL_DESC_DATETIME_INTERVAL_PRECISION 1014
Type: SMALLINT
. Importance: Medium.
This field will only be meaningful if the SQL_DESC_TYPE
field is 9
(SQL_DATETIME)
or 10 (SQL_INTERVAL)
. This is the precision of the leading
datetime field – not the fractional-seconds precision. For example, a DATE
value has three fields: YEAR
, MONTH
and DAY
. The first (“leading”)
datetime field in a DATE
is YEAR
, which always has four positions:
yyyy
. Therefore, desc.IDA[n].SQL_DESC_DATETIME_INTERVAL_PRECISION = 4
.
In ODBC, SQL_DESC_DATETIME_INTERVAL_PRECISION
is 26
rather than
1014
and <data type> is INTEGER
rather than SMALLINT
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_INDICATOR_POINTER
SQL_DESC_INDICATOR_POINTER 1009
Type: POINTER TO INTEGER
. Importance: Medium
Provides the address of an indicator. This field is used together with
SQL_DESC_DATA_POINTER
. For example, suppose that you set
ARD.IDA[n].SQL_DESC_INDICATOR_POINTER = &indicator
(where &indicator
means “the address of a variable named indicator
in the host program”). If
SQLGetData
retrieves a null value for the nth Column in the select list,
indicator
is set to SQL_NULL_DATA (-1)
. The field’s initial value is
0
.
In ODBC, the name is SQL_DESC_INDICATOR_PTR
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
||
APD |
|
|
IPD |
SQL_DESC_KEY_MEMBER
SQL_DESC_KEY_MEMBER 1030
Type: INTEGER
. Importance: Low.
This field is in SQL3 only; it’s not in ODBC. If a fetched item is from a
Column of the selected Table’s primary key or a preferred candidate key, then
the value is 1
(true); otherwise it’s 0
(false). For example:
SQLPrepare(hstmt,"SELECT 5 FROM t",SQL_NTS);
will set IRD.IDA[1].SQL_DESC_KEY_MEMBER = 0
. (See also the header field
SQL_DESC_KEY_TYPE
.)
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
SQL_DESC_LENGTH
SQL_DESC_LENGTH 1003
Data type: INTEGER
. Importance: Medium.
For all character string <data type>s, this is the defined length in characters
– for example, it equals 12
for a Column defined as VARCHAR(12)
. For
all bit string <data type>s, this is the defined length in bits. For all
temporal <data type>s, this is the defined length in positions – for example,
it equals 10
for a Column defined as a DATE
(because 2000-01-01 is 10
positions). For a BLOB
<data type>, this is the defined length in octets.
In ANSI SQL, SQL_DESC_LENGTH
may be changed with the SQLSetDescField
function. In ISO SQL, it may not be.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_NAME
SQL_DESC_NAME 1011
Type: VARCHAR
. Importance: Low.
This provides the derived Column name if there’s a select list. For example, after:
SQLPrepare(hstmt,"SELECT col_1 FROM Table_1",SQL_NTS);
the DBMS sets IRD.IDA[n].SQL_DESC_NAME = "Col_1"
. In ANSI SQL,
SQL_DESC_NAME
may be changed with the SQLSetDescField
function. In ISO
SQL, it may not be. In ODBC, named parameters are supported.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_NULLABLE
SQL_DESC_NULLABLE 1008
Type: SMALLINT
. Importance: Medium.
This field is 1
(true) (SQL_NULLABLE
) if the value might be NULL
;
otherwise it’s 0
(false) (SQL_NO_NULLS
). For a populated IPD,
SQL_DESC_NULLABLE
is 1
. In ANSI SQL, SQL_DESC_NULLABLE
may be
changed with the SQLSetDescField
function. In ISO SQL, it may not be. In
ODBC, the value might also be 2
(SQL_NULLABLE_UNKNOWN
).
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_OCTET_LENGTH
SQL_DESC_OCTET_LENGTH 1013
Type: INTEGER
. Importance: Medium.
For any character string, bit string or BLOB
<data type>, this is the
item’s maximum length in octets.
This field matters for “output”: if a CHAR
Column is fetched, the number of
octets transferred will be <= SQL_DESC_OCTET_LENGTH
. For example, if you
intend to fetch into a C host variable defined as "char[20]"
, then you
should set ARD.IDA[n].SQL_DESC_OCTET_LENGTH = 20
. For “input”
(SQL_PARAM_MODE_IN
parameters), SQL_DESC_OCTET_LENGTH
is not relevant.
When the DBMS inputs parameters, it uses the length pointed to by the
SQL_DESC_OCTET_LENGTH_POINTER
field.
Despite some contradictions in other documents, we believe that datetime or interval transfers are not affected by the value in this field.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_OCTET_LENGTH_POINTER
SQL_DESC_OCTET_LENGTH_POINTER 1004
Data type: POINTER TO INTEGER
. Importance: Medium.
This field provides the address of a length in octets. Its initial value is
0
(ARD/APD). For VARCHAR
or BIT VARYING
or BLOB
<data type>s,
this length is the actual length, which may be less than the defined length.
For other character string and bit string <data type>s, the actual and maximum
lengths are the same because size is fixed. The size of the \0
terminator
is not included in the octet length. For other <data type>s, the value is
implementation-defined.
This field and the SQL_DESC_INDICATOR_POINTER
field may point to the same
place. In ISO SQL, IPD.IDA[n].SQL_DESC_LENGTH
may be changed with the
SQLSetDescField
function. In ANSI SQL, it may not be. In ODBC, the name is
SQL_DESC_OCTET_LENGTH_PTR
.
[Obscure Rule] The rules change if SQL_DESC_OCTET_LENGTH_POINTER
and
SQL_DESC_INDICATOR_POINTER
contain the same address value (this is actually
imprecise: the rules don’t really change, but octet length is always set after
indicator, and we stop if indicator == SQL_NULL_DATA
).
If they’re separate:
On Output (taking SQLFetch for our example):
If fetched value IS NULL:
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = SQL_NULL_DATA (-1)
SQLFetch does not set *SQL_DESC_OCTET_LENGTH_POINTER
If fetched value IS NOT NULL:
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = 0
SQLFetch sets *SQL_DESC_OCTET_LENGTH_POINTER = string size in octets
On Input:
*SQL_DESC_INDICATOR_POINTER should be either 0 or -1.
*SQL_DESC_OCTET_LENGTH_POINTER can be SQL_NTS, SQL_DATA_AT_EXEC or length.
If they’re the same:
On Output (taking SQLFetch for our example):
If fetched value IS NULL:
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = SQL_NULL_DATA (-1)
If fetched value IS NOT NULL:
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = 0
/* We've proposed a change to the ISO committee; the following is an assumption that they'll adopt it: */
() if CHAR or BLOB:
*SQL_DESC_OCTET_LENGTH_POINTER = length
() otherwise:
"implementation-dependent", but assume it's like ODBC:
*SQL_DESC_OCTET_LENGTH_POINTER = length
... This "overrides the setting of *SQL_DESC_INDICATOR_POINTER=0.
... So "strlen_or_ind" is a misnomer; it's "strlen_and_ind"
If (CHAR or BLOB)
If (truncation would occur):
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = length
If (truncation would not occur):
SQLFetch sets *SQL_DESC_INDICATOR_POINTER = 0
On Input:
*SQL_DESC_INDICATOR_POINTER may be 0,-1,SQL_NTS,SQL_DATA_AT_EXEC, or length.
The rules are a little confusing, but that’s the price we have to pay for backward compatibility – some of the older CLI functions allow for only one pointer variable which serves for both “indicator” and “string length” information.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
||
APD |
|
|
IPD |
|
SQL_DESC_PARAMETER_MODE
SQL_DESC_PARAMETER_MODE 1021
Data type: SMALLINT
. Importance: Low.
This field is in SQL3 only; it’s not in ODBC. Its possible values are: 1
(SQL_PARAM_MODE_IN
), 2
(SQL_PARAM_MODE_INOUT
) and 4
(SQL_PARAM_MODE_OUT
). If the “populate IPD” flag is true, and the SQL
statement is "CALL ..."
, and the first parameter of the called routine is
input, the DBMS sets IPD.IDA[1].SQL_DESC_PARAMETER_MODE=1
.
In ANSI SQL, users may only change this field in the IPD (with the
SQLSetDescField
function). In ISO SQL, users may change this field in the
IPD, the APD and the ARD. In ODBC, a field named
IPD.IDA[n].SQL_DESC_PARAMETER_TYPE
can be set to SQL_PARAM_MODE_IN
,
SQL_PARAM_MODE_INOUT
or SQL_PARAM_MODE_OUT
. The default value is
SQL_PARAM_MODE_INPUT
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
|
|
IPD |
|
SQL_DESC_PARAMETER_ORDINAL_POSITION
SQL_DESC_PARAMETER_ORDINAL_POSITION 1022
Type: SMALLINT
. Importance: Low.
This field is in SQL3 only; it’s not in ODBC. It provides an ordinal number,
for SQL routine parameters. If this item corresponds to the first parameter in
an SQL "CALL ..."
statement, the DBMS sets
IPD.IDA[n].SQL_DESC_PARAMETER_ORDINAL_POSITION=1
. SQLPrepare
sets IPD
if “populate IPD” is true.
In ANSI SQL, users may only change this field in the IPD (with the
SQLSetDescField
function). In ISO SQL, users may change this field in the
IPD, the APD and the ARD.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
|
Parameter Fields¶
SQL_DESC_PARAMETER_SPECIFIC_CATALOG 1023
Type: VARCHAR
. Importance: Low.
SQL_DESC_PARAMETER_SPECIFIC_SCHEMA 1024
Type: VARCHAR
. Importance: Low.
SQL_DESC_PARAMETER_SPECIFIC_NAME 1025
Type: VARCHAR
. Importance: Low.
These three fields are SQL3 fields only; they’re not in ODBC. Together, they
make up the qualified name of a parameter in an SQL "CALL ..."
statement.
SQLPrepare
sets IPD if “populate IPD” is true.
In ANSI SQL, users may only change these fields in the IPD (with the
SQLSetDescField
function). In ISO SQL, users may change these fields in the
IPD, the APD and the ARD.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
||
IRD |
|
|
APD |
||
IPD |
|
SQL_DESC_PRECISION
SQL_DESC_PRECISION 1005
Type: SMALLINT
. Importance: Medium.
For all numeric <data type>s, this is a precision – that is, for DECIMAL
and NUMERIC
, it’s the number of digits both before and after the decimal
point; for INTEGER
and SMALLINT
, it’s the fixed implementation-defined
number of decimal or binary digits; for REAL
, FLOAT
and DOUBLE
PRECISION
, it’s the number of bits or digits in the mantissa. For all
temporal <data type>s, this is the fractional seconds precision – that is, the
number of digits after the decimal point in the SECOND
datetime component.
The default value in each case is what you’d get if you used the <data type>
with its default value in a CREATE TABLE
statement.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_SCALE
SQL_DESC_SCALE 1006
Type: SMALLINT
. Importance: Medium.
For DECIMAL
or NUMERIC
<data type>s, SQL_DESC_SCALE
is the number of digits
after the decimal point. The default value is zero – that is, if you change
the SQL_DESC_TYPE
to SQL_DECIMAL
or SQL_NUMERIC
, then the value in
SQL_DESC_SCALE
is implicitly set to 0
. For SMALLINT
or INTEGER
<data type>s,
the DBMS will set IRD.IDA[n].SQL_DESC_SCALE = 0
during SQLPrepare
, and ignore
the field on all other occasions. For all other <data type>s, the value of
SQL_DESC_SCALE
is irrelevant. The field’s initial value is 0
.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_TYPE
SQL_DESC_TYPE 1002
Type: SMALLINT
. Importance: High.
This field provides the item’s <data type>. Its possible values are:
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
For datetime and interval items, the subtype is in the
SQL_DESC_DATETIME_INTERVAL_CODE
field.
You may set ARD.IDA[n].SQL_DESC_TYPE = SQL_C_DEFAULT
. By setting SQL_DESC_TYPE
with SQLSetDescField
, you cause all other fields of the IDA to be reset
to implementation-dependent values. Moral: always set SQL_DESC_TYPE
first.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
UDT Fields¶
SQL_DESC_UDT_CATALOG 1026
Type: VARCHAR
. Importance: Low.
SQL_DESC_UDT_SCHEMA 1027
Type: VARCHAR
. Importance: Low.
SQL_DESC_UDT_NAME 1028
Type: VARCHAR
. Importance: Low.
These three fields are SQL3 fields only; they’re not in ODBC. Together, they make up the qualified name of the item’s user-defined type, if it has one.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
SQL_DESC_UNNAMED
SQL_DESC_UNNAMED 1012
Type: SMALLINT
. Importance: Low.
This field has two possible values: 1
(true) (SQL_UNNAMED
) or 0
(false) (SQL_NAMED
).
In the IRD: if the select list contains an initially-unnamed Column, then the
DBMS makes up a name, returns the made-up name to the SQL_DESC_NAME
field
and sets the SQL_DESC_UNNAMED
field to SQL_UNNAMED
. For example, after:
SQLPrepare(hstmt,"SELECT 5+1 FROM Table_1",SQL_NTS);
the DBMS might set IRD.IDA[n].SQL_DESC_NAME = "expr1"
(this is the value an
Access clone would give), and then set IRD.IDA[n].SQL_DESC_UNNAMED = 1
(SQL_UNNAMED
).
In the IPD: if “auto-populate” happens, then the DBMS sets
IPD.IDA[n].SQL_DESC_UNNAMED = 1
(SQL_UNNAMED
).
In ANSI SQL, users may change this field (with the SQLSetDescField
function). In ISO SQL, they may not.
May be Gotten by … |
May be Set by … |
|
---|---|---|
ARD |
|
|
IRD |
|
|
APD |
|
|
IPD |
|
|
The desc Functions¶
We are now ready to describe the individual desc
functions. There is a good
deal of redundancy here, because several functions are available which do the
same thing. If you’re a beginner, we suggest that you pay particular attention
to SQLSetDescField
and SQLGetDescField
, because it is possible to do
nearly everything with those two low-level functions alone.
There are 14 desc
functions. Their descriptions follow.
SQLAllocHandle(SQL_HANDLE_DESC,…)¶
Function Prototype:
SQLRETURN SQLAllocHandle(
SQLSMALLINT HandleType, /* 16-bit input = SQL_HANDLE_DESC */
SQLINTEGER InputHandle, /* 32-bit input, must be a hdbc */
SQLINTEGER *OutputHandle /* 32-bit output, a hdesc */
);
Job: Allocate a user desc
. (Note: User desc
s are unimportant. We
start off with this function for symmetry reasons.)
Algorithm:
If (HandleType == SQL_HANDLE_DESC)
If (InputHandle is not a valid handle of a dbc)
return error: CLI-specific condition-invalid handle
Empty the dbc's diagnostics area.
If (dbc is not connected)
Set *OutputHandle = 0
return error: connection exception-connection does not exist
If (the maximum number of descs has already been allocated)
Set *OutputHandle = 0
/* The maximum number of descs is implementor-defined */
return error: HY014 CLI-specific condition-limit on number of handles exceeded
If (there's not enough memory)
Set *OutputHandle = 0
return error: HY001 CLI-specific condition-memory allocation error
Allocate a new desc, associated with the dbc.
Set desc.SQL_ALLOC_TYPE = 2 i.e. SQL_DESC_ALLOC_USER.
/* Thus this desc is marked as a user desc, not an automatic desc. */
*OutputHandle = handle of new desc.
Notes:
In early prototypes of the CLI there were separate calls for different resource types (see
SQLAllocEnv
,SQLAllocConnect
,SQLAllocStmt
). Eventually people realized that the number of handle types might grow indefinitely, soSQLAllocHandle
was defined as a generalized “allocate handle” function for all current and future resource types.Call this function after you have allocated a
dbc
and after you have connected, since theInputHandle
parameter is ahdbc
.This function is only for user
desc
s. There are two kinds ofdesc
s: automaticdesc
s and userdesc
s. The DBMS implicitly sets up 4 automaticdesc
s (ARD, APD, IRD, IPD) whenSQLAllocHandle(SQL_HANDLE_STMT,...)
is called, and associates them with thestmt
. You explicitly set up userdesc
s withSQLAllocHandle(SQL_HANDLE_DESC,...)
, and they are associated with thedbc
.Using another function (
SQLSetStmtAttr
), you can replace one of the automaticdesc
s with a userdesc
. Suchdesc
s can be shared among multiplestmt
s.Using another function (
SQLCopyDescRec
), you can save the contents of an automaticdesc
in a userdesc
, for backup purposes.
Example:
#include "sqlcli.h"
...
SQLHDBC hdbc;
SQLHDESC hdesc;
...
SQLConnect(hdbc,...);
...
SQLAllocHandle(SQL_HANDLE_DESC,hdbc,&hdesc);
ODBC: The SQLAllocHandle
function is new in ODBC 3.5; in ODBC 2.0 the
desc
resource could not be explicitly allocated.
SQLFreeHandle(SQL_HANDLE_DESC,…)¶
Function Prototype:
SQLRETURN SQLFreeHandle( /* function returns SMALLINT */
SQLSMALLINT HandleType, /* 16-bit input, = SQL_HANDLE_HDESC */
SQLINTEGER Handle /* 32-bit input, must be a hdesc */
);
Job: Destroy a user desc
. (Remember that the SQLFreeHandle
function
can be used to destroy any resource: an env
, a dbc
, a stmt
or a
desc
. We are treating the four variants as four separate functions. In this
section, our sole concern is desc
.)
Algorithm:
If (HandleType == SQL_HANDLE_DESC)
If (Handle is not really a handle of a desc)
return error: CLI-specific condition-invalid handle
Empty the desc's diagnostics area.
The desc's dbc becomes the "current dbc".
If (there is a deferred parameter number)
return error: HY010 CLI-specific condition-function sequence error
If (desc.SQL_DESC_ALLOC_TYPE == SQL_DESC_ALLOC_AUTO)
/* You can't free any of the four descs (ARD IRD APD IPD) that
were automatically allocated when you made a stmt. They stay
around till you free the stmt. You can only free a "user"desc --
a desc which you allocated explicitly with SQLAllocHandle. */
return error: HY017 CLI-specific condition-invalid use of automatically-allocated descriptor
/* The following cancels the effects of any SQLSetStmtAttr calls which
might have made the user desc into an ARD or APD, in place of the
automatic ARD or APD desc. */
For (each stmt associated with dbc)
If (stmt is associated with the desc)
If (the desc is currently the stmt's ARD)
Re-associate stmt with the automatically-allocated ARD
If (the desc is currently the stmt's APD)
Re-associate stmt with the automatically-allocated APD
Deallocate desc.
The handle becomes invalid.
Notes:
If
SQLFreeHandle
returnsSQL_ERROR
, then the handle is still live and you can get diagnostics.This function is the reverse of the
SQLAllocHandle(SQL_HANDLE_DESC,...)
function. Only userdesc
s are destructible withSQLFreeHandle(SQL_HANDLE_DESC,...)
.The
SQLDisconnect
function will automatically destroy alldesc
s which are associated with adbc
. So technically you don’t need to callSQLFreeHandle(SQL_HANDLE_DESC...)
if you are about to disconnect.
Example:
#include "sqlcli.h"
SQLHDESC hdesc;
...
SQLFreeHandle(SQL_HANDLE_DESC,hdesc);
ODBC: SQLFreeHandle(SQL_HANDLE_DESC,...)
is new in ODBC 3.0.
SQLGetDescField¶
Function Prototype:
SQLRETURN SQLGetDescField (
SQLHDESC hdesc, /* 32-bit input */
SQLSMALLINT RecordNumber, /* 16-bit input */
SQLSMALLINT FieldIdentifier, /* 16-bit input */
SQLPOINTER Value, /* VOID* output */
SQLINTEGER BufferLength, /* 32-bit input */
SQLINTEGER *StringLength /* 32-bit output */
);
Job: Get one field from a desc
.
Algorithm:
If (FieldIdentifier<> one of the FieldIdentifier codes in "The Desc Fields")
return error: HY091 CLI-specific condition-invalid descriptor field identifier.
If (FieldIdentifier is the code for one of the IDA fields)
/* The RecordNumber parameter would be irrelevant for a "header field",
but IDA is multiple-occurrence so we need a valid index for IDA fields */
If (RecordNumber < 1)
return error: '07009': Dynamic SQL error-invalid descriptor index.
If (RecordNumber > SQL_DESC_COUNT)
/* Example: if the desc is an IRD, and the only SQL statement so far is
INSERT, then SQL_DESC_COUNT is zero. If now you pass SQL_DESC_TYPE
as the FieldIdentifier parameter and 5 as the RecordNumber parameter,
then the DBMS returns with return code = SQL_NO_DATA. */
Return with warning: '02000': No data.
If (FieldIdentifier is only applicable for a Prepared Statement, and there is no Prepared Statement)
/* Example: if the desc is an IRD, and no SQLPrepare or SQLExecDirect
has happened for the stmt associated with the desc, then a request
for SQL_DESC_TYPE would make no sense. */
Return error: HY007 CLI-specific condition-associated statement is not prepared.
If (FieldIdentifier is not applicable for this type of desc)
/* Example: if the desc is an IPD, then a request for
SQL_DESC_INDICATOR_POINTER would make no sense. */
Return with error HY091 CLI-specific condition-invalid descriptor field identifier.
If (the field is in its initially-undefined state)
/* Example: if the desc is an IPD, and the DBMS doesn't "automatically
populate" the IPD, then most fields stay in their "undefined" state. */
Return with error HY091 CLI-specific condition-invalid descriptor field identifier.
Retrieve the value of the field indicated by the FieldIdentifier parameter,
and put it in the place pointed to by the Value parameter. Notice that the
value might be a smallint, or it might be an integer, or it might be a
character string. In the latter case, the rules of Character String
Retrieval apply.
Notes:
SQLGetDescField
is a fundamentaldesc
routine. There are several other routines which are, conceptually, wrappers for one or moreSQLGetDescField
calls. For example, theSQLColAttribute
function will implicitly callSQLGetDescField
after finding the IRD;SQLGetDescRec
will retrieve sevendesc
fields at once (name, type, subtype, length, precision, scale and nullable).In our descriptions of the
desc
fields, the included charts of functions that affect the field use the word “user” to identify those cases whereSQLGetDescField
may be called to retrieve a single value after explicitly passing the field’s numeric identifier.The first
SQLGetDescField
parameter is ahdesc
. To get this handle, save the handle when you callSQLAllocHandle
(if it’s a userdesc
) and callSQLGetStmtAttr
(if it’s an automaticdesc
).The second parameter –
FieldIdentifier
– is unnecessary for a header field. For an IDA, it’s an index to the multiple-occurrence structure and should contain a value between 1 and “count”.
Example: Assume you have just called:
SQLExecDirect(hstmt,"SELECT * FROM Table_1",SQL_NTS);
Since SQLExecDirect
implies a SQLPrepare
phase, the DBMS has filled in
some of the IRD fields, as follows:
---------------------------------------------------------------------------
-
--------------------
--SQL_DESC_COUNT -
- ------------------ << picture of IRD after "SELECT * FROM Table_1" >>
- | 00003 |
- ------------------
-
-------------------------------------------------------------------
- - SQL_DESC_NAME | SQL_DESC_PRECISION | SQL_DESC_TYPE | ... |
-------------------------------------------------------------------
- 1-'Col_1' | 00005 | 00002 | ... |
- 2-'Col_2' | 00004 | 00003 | ... |
- 3-'Col_3' | ?? | 00001 | ... |
- ------------------------------------------------------------------
-
---------------------------------------------------------------------------
For space reasons, we’ve shown only a few fields in this diagram. But there’s
enough to make it clear that: (a) the DBMS found three Columns in the result
set (as indicated by SQL_DESC_COUNT == 3
), (b) the first Column is named
COL_1
, its <data type> is NUMERIC
(as indicated by SQL_DESC_TYPE ==
2
) and its precision is 5
, (c) the second Column is named COL_2
,
its <data type> is DECIMAL
(as indicated by SQL_DESC_TYPE == 3
) and its
precision is 4
, and (d) the third Column is named COL_3
, its <data
type> is CHAR
(as indicated by SQL_DESC_TYPE == 1
) and its precision is
unset because CHAR
Columns have no precision (they have a length instead).
Here are some SQLGetDescField
calls – and what will be put into the
Value
variable:
SQLGetDescField(hdesc,NULL,SQL_DESC_COUNT,&value,NULL,NULL);
– gets 3
SQLGetDescField(hdesc,1,SQL_DESC_TYPE,&value,NULL,NULL);
– gets 2
SQLGetDescField(hdesc,3,SQL_DESC_PRECISION,&value,NULL,NULL);
– gets 4
And here is a larger code snippet, which finds out the hdesc
value (a
necessary preliminary!), then displays the name of every Column in TABLE_1
:
#include "sqlcli.h"
...
SQLHSTMT hstmt; /* handle of stmt */
SQLHDESC hdesc; /* handle of IRD */
SQLSMALLINT i,col_count; /* used for a loop counter */
SQLCHAR *col_name[128+1];
...
SQLExecDirect(hstmt,"SELECT * FROM Table_1",SQL_NTS);
SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_ROW_DESC,&hdesc,NULL,NULL);
SQLGetDescField(hdesc,SQL_DESC_COUNT,&col_count,NULL,NULL);
printf("The Columns of Table_1 are:\n");
for (i=1; i<=col_count; ++i) {
SQLGetDescField(hdesc,i,SQL_DESC_NAME,col_name,sizeof(col_name),NULL);
printf("%s\n",col_name); }
...
ODBC: The SQLGetDescField
function is new in ODBC 3.5. There are some
implementation-defined additional fields. The expected behaviour is somewhat
different if you ask for a field which has no defined value: a
standard-conformant DBMS would return SQL_ERROR
, an ODBC-conformant DBMS
would return SQL_SUCCESS
and an undefined value.
SQLSetDescField¶
Function Prototype:
SQLRETURN SQLSetDescField(
SQLHDESC hdesc, /* 32-bit input */
SQLSMALLINT RecordNumber, /* 16-bit input */
SQLSMALLINT FieldIdentifier, /* 16-bit input */
SQLPOINTER Value, /* ANY* input */
SQLINTEGER BufferLength /* 32-bit input */
);
Job: Assign a value to one field in a desc
.
Algorithm:
If (desc is associated with a "deferred parameter")
return error: HY010 CLI-specific condition-function sequence error
If (desc is an IRD)
/* This error will appear only for ISO SQL3: */
return error: HY016 CLI-specific condition-cannot modify an implementation row descriptor
If (FieldIdentifier is not a code used in "The Desc Fields" list)
return error: HY091 CLI-specific condition-invalid descriptor field identifier
If (this field cannot be set by the user)
return error: HY091 CLI-specific condition-invalid descriptor field identifier
If (FieldIdentifier == SQL_DESC_COUNT)
Set desc.SQL_DESC_COUNT = Value
If (this is an IDA field i.e. not a header field)
If (RecordNumber < 1)
return error: 07009 Dynamic SQL error-invalid descriptor index
/* We have: a particular field in a particular IDA of a desc
identified by FieldIdentifier/RecordNumber/hdesc respectively).
We have: a new value for that field (in Value/BufferLength). */
If (FieldIdentifier == SQL_DESC_OCTET_LENGTH_POINTER)
Set desc.IDA[RecordNumber].SQL_DESC_OCTET_LENGTH_POINTER = Value
If (FieldIdentifier == SQL_DESC_INDICATOR_POINTER)
Set desc.IDA[RecordNumber].SQL_DESC_INDICATOR_POINTER = Value
If (FieldIdentifier == SQL_DESC_CHARACTER_SET_CATALOG (or SCHEMA) (or NAME))
/* Value points to a string, BufferLength is string size, possibly
BufferLength == SQL_NTS */
Trim lead and trail spaces from the string.
Ensure that the string is a valid identifier.
/* Valid identifiers may include introducers. */
Copy string to desc.IDA[RecordNumber].SQL_DESC_CHARACTER_SET_CATALOG (or
SCHEMA) (or NAME)
If (RecordNumber > desc.SQL_DESC_COUNT)
/* Change SQL_DESC_COUNT so it equals the maximum IDA index number */
Set desc.SQL_DESC_COUNT = RecordNumber
If (FieldIdentifier <> SQL_DESC_COUNT)
If (FieldIdentifier not
SQL_DESC_OCTET_LENGTH_POINTER|SQL_DESC_DATA_POINTER|INDICATOR_POINTER)
Set desc.IDA[RecordNumber].SQL_DESC_DATA_POINTER = 0
If (FieldIdentifier == SQL_DESC_DATA_POINTER)
Set desc.IDA[RecordNumber].SQL_DESC_DATA_POINTER = Value
If (Value <> 0)
/* The "Consistency check" is described later in this chapter.
Basically, it just checks that field values aren't absurd. */
If ("Consistency check" failure)
/* SQL_DESC_DATA_POINTER field might be changed despite the error */
return error: HY021 CLI-specific condition-inconsistent descriptor information
If (FieldIdentifier == SQL_DESC_TYPE)
If (Value is not a valid data type, for example SQL_NUMERIC)
return error: HY004 CLI-specific condition-invalid data type
Set desc.IDA[RecordNumber].SQL_DESC_TYPE = Value
/* See the "Default values" chart in this section */
Set other fields to their "default values"
If (FieldIdentifier == SQL_DATETIME_INTERVAL_CODE)
If (desc.IDA[RecordNumber].SQL_DESC_TYPE is datetime (9))
If (Value=1 or 2 or 4: i.e.: date or time or time with time zone)
Set desc.IDA[RecordNumber].SQL_DESC_PRECISION = 0
If (Value=3 or 5: i.e.: timestamp or timestamp with time zone)
Set desc.IDA[RecordNumber].SQL_DESC_PRECISION = 6
Set other fields to implementation-dependent values
If (desc.ida[RecordNumber].SQL_DESC_TYPE is interval)
Set desc.IDA[RecordNumber].SQL_DESC_DATETIME_INTERVAL_PRECISION = 2
If (Value is for an interval that ends with SECOND)
Set desc.IDA[RecordNumber].SQL_DESC_PRECISION = 6
Else
Set desc.ida[RecordNumber].SQL_DESC_PRECISION = 0
/* For other FieldIdentifier values, there is no standard
procedure. A reasonable assumption is that the DBMS would
simply copy Value to the field indicated by FieldIdentifier. */
Notes:
Sometimes
Value
is a pointer; sometimes it’s an integer; sometimes it’s a smallint; depending onFieldIdentifier
.If you must change multiple fields in one IDA, do them in this order:
Change the
SQL_DESC_TYPE field
first.Change the
SQL_DESC_DATETIME_INTERVAL_CODE
field second.Change the other fields (except for
SQL_DESC_DATA_POINTER
) in any order.Change the
SQL_DESC_DATA_POINTER
field last.
If you don’t set fields in this order, you’ll get errors. It’s deliberate.
If an error happens, the value of the field may be changed anyway.
If you change a header field, you should pass
RecordNumber = 0
(otherwise, in theory, you could inadvertently changed theSQL_DESC_COUNT
field).The <data type> of
Value
should correspond to the <data type> of the field being changed. For example, if changing theSQL_DESC_SCALE
field,Value
should contain aSMALLINT
value. For a list of the <data type> correspondences between the SQL predefined <data type>s and host variables, see our chapter on embedded SQL. Because there are some differences between embedded SQL and the SQL/CLI, here is a concise summary for the SQL/C <data type> correspondences, with some adjustments and notes that are specific to SQL/CLI:SQL
C
Notes
ARRAY
-
ARRAY LOCATOR
long
BIT (length)
char[length/8]
Assumes 8-bit chars, rounded up
BIT VARYING(length)
-
You can cast to
BIT
BLOB (length)
char[length]
Assumes 8-bit chars
BLOB(length)
long
BOOLEAN
long
Standard is not explicit here
CHAR or CLOB
char[length+1]
CLOB(length)
long
CLOB(length)
long
DATE
-
ODBC defines a struc for this
DECIMAL(p,s)
-
Cast to char or (rarely) to float
DOUBLE PRECISION
double
FLOAT (p)
-
if p<=23:float, if p>23:double
INTEGER
long
INTERVAL(q)
-
NUMERIC(p,s)
-
Cast to char or (rarely) to float
REAL
float
REF
char[L]
L is implementation-defined
SMALLINT
short
TIME(t)
-
ODBC defines a struc for this
TIMESTAMP(t)
-
ODBC defines a struc for this
UDT
-
UDT LOCATOR
long
Note: The symbol “-” means there is no official correspondence according to the Standard, but some DBMSs will try to do an implicit cast.
Note: In a similar list for Delphi, we would find that SQL’s
REAL
corresponds to Delphi’sFloat
(not Delphi’sReal
), and that SQL’sCHAR
corresponds to Delphi’sPchar
(not Delphi’s packed array of char).The
SQLSetDescField
function can only set one field at a time. Therefore, it is a “fundamental” function. There are other functions which can be used to change multiple fields (SQLSetDescRec
,SQLBindParameter
andSQLBindCol
).Default Values – A change to
SQL_DESC_TYPE
will cause other IDA fields to be reset to their “default values”. This chart shows what the changes are. Any fields not shown are reset to implementation-dependent values.If
SQL_DESC_TYPE
is changed to …… Then these fields change too
SQL_CHAR
,SQL_VARCHAR
,SQL_CLOB
SQL_DESC_CATALOG etc.: default set
SQL_LENGTH: maximum length [Note 1]
SQL_BIT
,SQL_BIT_VARYING
,SQL_BLOB
SQL_LENGTH: maximum length
SQL_DATETIME
SQL_PRECISION: 0
SQL_INTERVAL
SQL_DATETIME_INTERVAL_PRECISION: 2
SQL_DECIMAL
,SQL_NUMERIC
SQL_PRECISION: maximum precision [Note 2]
SQL_SCALE: 0
SQL_FLOAT
SQL_PRECISION: default precision [Note 2]
[Note 1]
The “maximum length” is not the usual default value for aCHAR
<data type>. If you sayCREATE TABLE Table_1 (col_1 CHAR);
, the default length is1
. That is why, in ODBC, if you setSQL_DESC_TYPE
toSQL_CHAR
, theSQL_LENGTH
field changes to1
. But in standard SQL, it becomes a character string <data type>’s “maximum possible length”, which is an implementation-defined size.[Note 2]
The “default precision” ofSQL_DECIMAL
,SQL_NUMERIC
andSQL_FLOAT
<data type>s is implementation-defined.The Standard has omitted mention of all other <data type>s in relation to
SQLSetDescField
, and ends with a note that says an error will be returned for any type not shown in the chart. In effect, this means that all <data types> other thanSQL_CHAR
,SQL_VARCHAR
,SQL_CLOB
,SQL_BIT
,SQL_BIT_VARYING
,SQL_BLOB
,SQL_DATETIME
,SQL_INTERVAL
,SQL_DECIMAL
,SQL_NUMERIC
andSQL_FLOAT
are technically illegal here. The wise programmer will assume that these matters will be resolved by the DBMS vendor – not being able to useSQL_INTEGER
, for example, seems too severe a restriction to be followed.Consistency check – While you’re changing descriptor fields, you might cause temporary inconstancy. Here’s how:
[Step 1] You change
SQL_DESC_TYPE
toSQL_DECIMAL
. Changing the <data type> triggers a wholesale resetting of all other IDA fields to default values. For example, theSQL_DESC_PRECISION
field becomes1
(an implementation-defined default), theSQL_DESC_SCALE
field becomes0
and theSQL_DESC_DATA_POINTER
becomes a null pointer.[Step 2] You change
SQL_DESC_SCALE
to5
. Now the scale is greater than the precision. That is inconsistent – aDECIMAL(1,5)
definition is illegal. But don’t worry – the DBMS won’t reject your change.[Step 3] You change
SQL_DESC_PRECISION
to6
. Now the fields are consistent again.[Step 4] You change
SQL_DESC_DATA_POINTER
to a host-variable address. Changing the data pointer triggers a consistency check. The rationale for delaying the consistency check until you change the data pointer is that temporary inconsistencies are inevitable if you change one field at a time, but don’t matter as long as the data pointer is a null pointer. When you set the data pointer, you “bind the Column” to the host variable. At that point the DBMS cannot allow any further inconsistency. If you’ve read our chapters about the various SQL predefined <data type>s, you’ll find that the consistency check is merely an enforcement of the rules you already know. And there are no **GOTCHAs because the DBMS only checks what’s relevant. It looks for these things:SQL_DESC_PRECISION
,SQL_DESC_SCALE
,SQL_DESC_LENGTH
,SQL_DESC_DESC_DATETIME_INTERVAL_CODE
andSQL_DESC_DATETIME_INTERVAL_PRECISION
must be valid if it’s possible to specify precision, scale, length and/or specific datetime or interval leading-field precision when you’re defining a Column of the given <data type>. For example,FLOAT(precision)
is legal in definitions, thereforeSQL_DESC_PRECISION
must have a valid value ifSQL_DESC_TYPE = SQL_FLOAT
. On the other hand, precision is not specifiable forREAL
Columns, so there is no check ofSQL_DESC_PRECISION
ifSQL_DESC_TYPE = SQL_REAL
. IfSQL_DESC_TYPE
isNUMERIC
orDECIMAL
, then scale and precision must be valid for the <data type> (e.g. scale cannot be greater than precision). (Warning: this description is of the SQL Standard “Consistency Check” definition. The ODBC “Consistency Check” is more picky.)For IDAs within application descriptors (ARDs or APDs),
SQL_DESC_TYPE
must be a <data type> that’s representable in the host language. In our charts of <data type> correspondences (see our chapter on embedded SQL), we showed you that the SQLINTEGER
<data type> translates directly to a C data type: long. Easy times. Now what about theNUMERIC
<data type>? Well you’d have toCAST
it to something that C can handle: either a floating-point or a character-string. Casting is easy – just change the <data type> as we described in each <data type> chapter.If the DBMS detects an inconsistency during execution of
SQLSetDescField
orSQLSetDescRec
, theSQLSTATE
error return isHY021 "CLI-specific condition-inconsistent descriptor information."
Tip
Even though you never need an
SQL_DESC_DATA_POINTER
value in an IPD IDA, set it anyway. That will force a consistency check. It’s better to check for inconsistency in advance, rather than waiting for the consistency error to happen when you try to execute the statement.
The DBMS may also detect inconsistency during execution of
SQLBindParameter
orSQLBindCol
, but usually there is a final Consistency Check duringSQLExecute
. If the DBMS detects an inconsistency during execution ofSQLExecute
, theSQLSTATE
error return is either07001 "Dynamic SQL error-using clause does not match dynamic parameters."
or07002 "Dynamic SQL error-using clause does not match target specifications."
(There appears to be an error in the Standard; this is what we believe is the intended meaning.)
Example: You have an SQL statement which uses an integer input parameter. You want to tell the DBMS about it, by setting certain fields of the APD. Assume that the “auto-populate IPD” flag is off, so you’ll have to set some fields in the IPD too. Here’s how.
#include "sqlcli.h"
SQLHSTMT hstmt;
SQLHDESC hapd,hipd; /* hapd="handle of APD"; hipd="handle of IPD" */
SQLINTEGER input_variable; /* this has the value we pass */
...
SQLPrepare(hstmt,"INSERT INTO Table_1 VALUES (?)",SQL_NTS);
SQLGetStmtAttr(hstmt,SQL_ATTR_AUTO_APD,
SQLGetStmtAttr(hstmt, SQL_ATTR_APP_PARAM_DESC, &hapd, NULL, NULL);
SQLSetDescField(hapd,1,SQL_DESC_TYPE,SQL_INTEGER,NULL);
SQLSetDescField(hapd,1,SQL_DESC_DATA_POINTER,&input_variable,NULL);
SQLGetStmtAttr(hstmt, SQL_ATTR_IMP_PARAM_DESC, &hipd, NULL, NULL);
SQLSetDescField(hipd,1,SQL_DESC_TYPE,SQL_INTEGER,NULL);
SQLSetDescField(hipd,1,SQL_DESC_DATA_POINTER,&input_variable,NULL);
input_variable = 55;
SQLExecute(hstmt);
...
Here is a picture of the APD after the SQLSetDescField
calls are done:
- ----------------------- -------------------
--SQL_DESC_ALLOC_TYPE- - SQL_DESC_COUNT -
- ----------------------- ------------------
- | 00001 | | 00001 |
- ----------------------- ------------------
-
----------------------------------------------------
-- SQL_DESC_DATA_POINTER | SQL_DESC_TYPE | ........ |
-----------------------------------------------------
- 1-&input_variable | 00004 | ........ |
- ----------------------------------------------------
We will revisit “parameter passing” again after discussing the SQLBindCol
function.
SQLGetDescRec¶
Function Prototype:
SQLRETURN SQLGetDescRec(
SQLHDESC hdesc, /* 32-bit input */
SQLSMALLINT RecordNumber, /* 16-bit input */
SQLCHAR *Name, /* CHAR* output */
SQLSMALLINT BufferLength, /* 16-bit input */
SQLSMALLINT *NameLength, /* CHAR* output */
SQLSMALLINT *Type, /* 16-bit output */
SQLSMALLINT *SubType, /* 16-bit output */
SQLINTEGER *Length, /* 32-bit output */
SQLSMALLINT *Precision, /* 16-bit output */
SQLSMALLINT *Scale, /* 16-bit output */
SQLSMALLINT *Nullable /* 16-bit output */
);
Job: Retrieve the values of several fields from one Item Descriptor Area of
a desc
.
Algorithm:
/* hdesc refers to a desc. */
/* RecordNumber n refers to IDA[n] within the desc. */
If (RecordNumber < 1)
return error: 07009 dynamic SQL error-invalid descriptor index
If (RecordNumber > desc.SQL_DESC_COUNT)
return warning: 02000 no data -
If (desc is an IRD and associated statement is not prepared)
return error: HY007 CLI-specific condition-associated statement is not prepared
/* Retrieve into *Name,*Type,*Subtype,etc. ... If any parameter
is a null pointer (0000:0000), just ignore it. */
Set *Name = desc.IDA[RecordNumber].SQL_DESC_NAME (use the
usual Character String Retrieval method).
Set *NameLength = desc.IDA[RecordNumber].SQL_DESC_NAME.
Set *Type = desc.IDA[RecordNumber].SQL_DESC_TYPE
Set *SubType = desc.IDA[RecordNumber].SQL_DESC_DATETIME_INTERVAL_CODE
Set *Length = desc.IDA[RecordNumber].SQL_DESC_OCTET_LENGTH
Set *Precision = desc.IDA[RecordNumber].SQL_DESC_PRECISION.
Set *Scale = desc.IDA[RecordNumber].SQL_DESC_SCALE.
Set *Nullable = desc.IDA[RecordNumber].SQL_DESC_NULLABLE field.
Notes:
In effect, calling
SQLGetDescRec
is equivalent to callingSQLGetDescField
several times, with different field identifiers:SQL_DESC_NAME
,SQL_DESC_TYPE
,SQL_DESC_DATETIME_INTERVAL_CODE
,SQL_DESC_OCTET_LENGTH
,SQL_DESC_PRECISION
,SQL_DESC_SCALE
andSCALE_NULLABLE
.Regarding the value of
*Length
, what the Standard actually says is that it should be set to “the length (in octets or positions, as appropriate)” … which is ambiguous. We have taken the ODBC specification as our guide here – it says*Length
should be set toSQL_DESC_OCTET_LENGTH
. Probably you should use another function if the setting of Length is important to you.For fields which are “not applicable”, pass null parameters. For example, the
SQL_DESC_NAME
field is usually meaningless within an ARD or an APD. So, for*Name
,BufferLength
and*Namelength
, pass null if you’re retrieving from an ARD or APD. By passing null pointers, you can limit the values you get, to fill only the fields you really want.SQLGetDescRec
'sBufferLength
parameter is defined asSMALLINT
(16-bit).SQLGetDescField
'sBufferLength
parameter in defined asINTEGER
(32-bit). However, the apparent inconsistency causes no problems: the length of a Name string is typically only a few octets.
Example: The following code snippet is an exact copy of the example used
for the SQLGetDescField
function, with only one line changed:
SQLGetDescField(hdesc,SQL_DESC_NAME,col_name,sizeof(col_name),NULL);
is replaced by a call to SQLGetDescRec
which gets only the name.
#include "sqlcli.h"
...
SQLHSTMT hstmt; /* handle of stmt */
SQLHDESC hdesc; /* handle of IRD */
SQLSMALLINT i,col_count; /* used for a loop counter */
SQLCHAR *col_name[128+1];
...
SQLExecDirect(hstmt,"SELECT * FROM Table_1",SQL_NTS);
SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_ROW_DESC,&hdesc,NULL,NULL);
SQLGetDescField(hdesc,SQL_DESC_COUNT,&col_count,NULL,NULL);
printf("The Columns of Table Table_1 are:\n");
for (i=1; i<=col_count; ++i) {
SQLGetDescRec(
hdesc,i,col_name,sizeof(col_name),NULL,NULL,NULL,NULL,NULL,NULL,NULL);
printf("%s\n",col_name); }
...
ODBC: The SQLGetDescRec
function is new in ODBC 3.5. (In ODBC 2.0 the
desc
fields were always retrieved via other functions, such as
SQLDescribeCol
.) SQLGetDescRec
is apparently short for “Get Descriptor
Record”. “Descriptor record” is ODBC jargon; “Item Descriptor Area” (IDA) is
the standard term. The fact that the name is SQLGetDescRec
, rather than
SQLGetIDA
, illustrates the influence of ODBC over the Standard.
SQLSetDescRec¶
Function Prototype:
SQLRETURN SQLSetDescRec(
SQLHDESC hdesc, /* 32-bit input */
SQLSMALLINT RecordNumber, /* 16-bit input */
SQLSMALLINT Type, /* 16-bit input */
SQLSMALLINT SubType, /* 16-bit input */
SQLINTEGER Length, /* 32-bit input */
SQLSMALLINT Precision, /* 16-bit input */
SQLSMALLINT Scale, /* 16-bit input */
SQLPOINTER Data, /* ANY* input */
SQLINTEGER *StringLength, /* 32-bit output */
SQLINTEGER *Indicator /* 32-bit output */
);
Job: Set the values for several fields in one Item Descriptor Area of a
desc
.
Algorithm:
If (desc is associated with a "deferred parameter")
return error: HY010 CLI-specific condition-function sequence error
If (RecordNumber < 1)
return error: 07009 dynamic SQL error-invalid descriptor index
If (desc is an IRD)
/* This error will appear only for ISO SQL3: */
return error: HY016 CLI-specific condition-cannot modify an implementation row descriptor
Set desc.IDA[RecordNumber].SQL_DESC_TYPE = Type
Set desc.IDA[RecordNumber].SQL_DESC_PRECISION = Precision
Set desc.IDA[RecordNumber].SQL_DESC_SCALE = Scale
Set desc.IDA[RecordNumber].SQL_DESC_DATETIME_INTERVAL_CODE = SubType
if (desc is an IPD)
/* for IPD: this is the length in characters / bits / positions
Set desc.IDA[RecordNumber].SQL_DESC_LENGTH=Length
Else
/* for APD or ARD: this is the length in octets */
Set desc.IDA[RecordNumber].SQL_DESC_OCTET_LENGTH=Length
if (StringLength is not a null pointer)
Set desc.IDA[RecordNumber].SQL_DESC_OCTET_LENGTH_POINTER=StringLength
Set desc.IDA[RecordNumber].SQL_DESC_DATA = Data
If (Indicator is not a null pointer)
Set desc.IDA[RecordNumber].SQL_DESC_INDICATOR_POINTER=Indicator
If (Data is not a null pointer)
If ("Consistency Check" fails)
return error: HY021 CLI-specific condition-inconsistent descriptor information
If (RecordNumber > desc.SQL_DESC_COUNT)
Set desc.SQL_DESC_COUNT = RecordNumber
/* If any errors occur, then desc.SQL_DESC_COUNT will not be changed,
but the other fields mentioned in this description may be. */
Notes:
In effect, calling
SQLSetDescRec
is equivalent to callingSQLSetDescField
several times, with different field identifiers:SQL_DESC_TYPE
,SQL_DESC_PRECISION
,SQL_DESC_SCALE
,SQL_DESC_DATETIME_INTERVAL_CODE
,SQL_DESC_LENGTH
orSQL_DESC_OCTET_LENGTH
,SQL_DESC_OCTET_LENGTH_POINTER
,SQL_DESC_DATA_POINTER
andSQL_DESC_DATA_POINTER
.Using
SQLSetDescRec
on an ARD is somewhat similar to usingSQLBindCol
on thestmt
which contains the ARD.Using
SQLSetDescRec
on an APD is somewhat similar to usingSQLBindParameter
on thestmt
which contains the APD.The parameters of
SQLGetDescRec
do not correspond to the parameters ofSQLGetDescRec
. For example, there is no way to set theSQL_DESC_NAME
field.Because of the peculiar algorithm logic, it is impossible to set
SQL_DESC_OCTET_LENGTH_POINTER
orSQL_DESC_INDICATOR_POINTER
to null values.A Consistency Check always happens. Therefore, if there are other fields that you want to set, you should call
SQLSetDescField
before you callSQLSetDescRec
.
Example: This is an efficient way to execute an SQL statement many times,
varying only the input parameter (represented in the statement by “?”). The
code will insert 50 rows, with values between 1
and 50
. We have
deliberately varied some names and parameter-declarations, to show a style
preferred by other programmers.
#include "sqlcli.h"
SQLHSTMT hstmt;
SQLHDESC hdesc1,hdesc2; /* hdesc1 is APD, hdesc2 is IPD */
SQLINTEGER i; /* the loop counter, and the input value */
...
/* prepare the statement -- outside the loop */
SQLPrepare(hstmt, "INSERT INTO Table_1 VALUES (?)", SQL_NTS);
/* Associate i with APD */
SQLGetStmtAttr(hstmt,SQL_ATTR_APP_PARAM_DESC,&hdesc1,0L,(SQLINTEGER *)NULL);
SQLSetDescRec(
hdesc1,1,SQL_INTEGER,0,0L,0,0,(SQLPOINTER)&i,(SQLINTEGER *)NULL,
(SQLINTEGER *)NULL);
/* Associate parameter marker with IPD */
SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_PARAM_DESC,&hdesc2,0L,(SQLINTEGER *)NULL);
SQLSetDescRec(
hdesc2,1,SQL_INTEGER,0,0L,0,0,(SQLPOINTER)NULL,(SQLINTEGER *)NULL,
(SQLINTEGER *)NULL);
for (i=1; i<50; ++i)
{
/* execute the statement -- inside the loop */
SQLExecute(hstmt);
}
...
Warning
This code is “efficient” because it calls SQLPrepare
and the
SQLSetDescRec
functions only once, for an SQL statement that is executed
50 times. Usually, taking code out of loops is the right thing to do. But
for at least one DBMS, it’s better to bind i
(as a deferred parameter)
after the call to SQLExecute
.
ODBC: SQLSetDescRec
is new in ODBC 3.0. You can set
SQL_DESC_OCTET_LENGTH_POINTER
or SQL_DESC_INDICATOR_POINTER
to null, by
passing null pointers in the StringLength
or Indicator
parameters. In
standard SQL, the DBMS ignores null pointers in those parameters.
SQLCopyDesc¶
Function Prototype:
SQLRETURN SQLCopyDesc(
SQLHDESC source_hdesc, /* 32-bit input */
SQLHDESC target_hdesc /* 32-bit input */
);
Job: Copy a source desc
to a target desc
.
Algorithm:
If (source_hdesc is not a hdesc)
return error: CLI-specific condition-invalid handle
If (target_hdesc is not a hdesc)
return error: CLI-specific condition-invalid handle
The target desc's diagnostics area is emptied.
If (source_desc is associated with a "deferred parameter")
return error: HY010 CLI-specific condition-function sequence error
If (target_desc is associated with a "deferred parameter")
return error: HY010 CLI-specific condition-function sequence error
If (Target Desc is an IRD)
return error: HY016 CLI-specific condition-cannot modify an implementation row descriptor.
If (Source Desc is an IRD)
If (associated statement not prepared)
return error: HY007 CLI-specific condition-associated statement is not prepared)
Copy the contents of every field in source desc to target desc -- with
the exception of the SQL_DESC_ALLOC_TYPE field. SQL_DESC_ALLOC_TYPE keeps its
original value).
Notes:
There are other ways to copy an entire
desc
. For example, you could call theSQLGetDescField
andSQLSetDescField
functions repeatedly. ButSQLCopyDesc
is the easy way.If you’re thinking of copying
stmt
#1’s APD tostmt
#2’s APD, there’s an alternative: you can allocate a userdesc
and share it. Here’s how:Make a user
desc
:SQLAllocHandle(SQL_HANDLE_DESC,hdbc,&hdesc_user);
Say that
stmt
#1’s APD is the userdesc
:SQLSetStmtAttr(hstmt1,SQL_ATTR_APP_ROW_DESC,&hdesc_user,NULL);
Fill in the fields of
stmt
#1’s APD (usingSQLSetDescRec
etc.)Say that
stmt
#2’s APD is the userdesc
:SQLSetStmtAttr(hstmt2,SQL_ATTR_APP_ROW_DESC,&hdesc_user,NULL);
Now there’s no need to copy, because
stmt
#2’s APD isstmt
#1’s APD.
One possible use of
SQLCopyDesc
is to copy an IRD to an ARD. The point is: the IRD and the IRD fields are supposed to be similar; the IRD is set up automatically; so after the copy the ARD will have the values that the DBMS thinks should be there. If you want to change these values slightly, you can fine-tune usingSQLSetDescField
.Another possible use of
SQLCopyDesc
is to savedesc
information – if, for example, you have a small number of queries that are executed repeatedly using the samestmt
. Here’s how:Allocate a user descriptor with
SQLAllocHandle(SQL_HANDLE_DESC,...).
Copy an automatic
desc
to the userdesc
, withSQLCopyDesc
.Change the automatic
desc
for some temporary purpose.Copy the user
desc
back to the automaticdesc
, withSQLCopyDesc
. This is like “pushing” all thedesc
fields to a stack, making changes, then “popping” to restore the original values.
Some DBMSs will perform a consistency check on the target desc
's IDAs if
any target_desc.IDA[n].SQL_DESC_DATA_POINTER
is not a null pointer.
The copy is possible even if the source and target desc
s are in
different connections.
Example: This shows how to copy values from one Table to another. The trick works like this:
Step 1: Prepare a
SELECT
statement from the source Table. This yields the IRD of the source. Bind the result set withSQLSetDescRec
. This yields the ARD of the source.Step 2: Prepare an
INSERT
statement on the target Table. This yields the IPD of the target.Step 3: Copy the source RDs to the target PDs.
The trick would work for any pair of Tables, provided Columns have compatible <data type>s.
#include "sqlcli.h"
SQLCHAR szCol_1[6];/* sz is "string, zero terminated" */
SQLINTEGER cbCol_1; /* col_1's indicator */
SQLHSTMT hstmt_source, hstmt_target;
SQLHDESC hard_source, hird_source; /* source's ARD+IRD handles */
SQLHDESC hapd_target, hipd_target; /* target's APD+IPD handles */
...
/* Get the handles of each desc that we'll use */
/* SELECT from the source. */
SQLExecDirect(hstmt_source,"SELECT col_1 FROM Sources;",SQL_NTS);
SQLGetStmtAttr(hstmt_source,SQL_ATTR_APP_ROW_DESC,&hard_source,0,NULL);
SQLGetStmtAttr(hstmt_target,SQL_ATTR_APP_PARAM_DESC,&hapd_target,0,NULL);
/* Bind source Column #1. This changes the source's ARD. */
SQLSetDescRec(
hard_source,1,SQL_CHAR,NULL,NULL,NULL,NULL,NULL,szCol_1,&6,&cbCol_1);
/* Copy source's ARD to target's APD */
SQLCopyDesc(hard_source,hapd_target);
/* Copy source's IRD to target's IPD */
SQLGetStmtAttr(hstmt_source,SQL_ATTR_IMP_ROW_DESC,&hird_source,0,NULL);
SQLGetStmtAttr(hstmt_target,SQL_ATTR_IMP_PARAM_DESC,&hipd_target,0,NULL);
SQLCopyDesc(hIrd_source, hipd_target);
/* Prepare to INSERT in the target. */
/* Once again, we assume "auto-populate IPD" is not true. If it were
true, SQLPrepare would overwrite what we've just copied to the IPD. */
SQLPrepare(hstmt_target,"INSERT INTO Targets VALUES (?)", SQL_NTS);
/* Fetch loop */
for (;;) {
sqlreturn = SQLFetchScroll(hstmt0, SQL_FETCH_NEXT, 0);
if (sqlreturn <> SQL_SUCCESS && sqlreturn <> SQL_SUCCESS_WITH_INFO) break;
/* According to the row descriptors of the SELECT: we fetched into
szCol_1 and cbCol_1. According to the parameter descriptors of
INSERT: we'll get our values from szCol_1 and cbCol_1. Thus the
fetch's "output" is the insert's "input". */
SQLExecute(hstmt_target); }
SQLCloseCursor(hstmt_source);
...
ODBC: The SQLCopyDesc
function arrived with ODBC 3.0. ODBC’s Driver
Manager will handle copying if the source and target handles are associated
with different drivers.
SQLBindCol¶
Function Prototype:
SQLRETURN SQLBindCol(
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT ColumnNumber, /* 16-bit input */
SQLSMALLINT BufferType, /* 16-bit input */
SQLPOINTER Data, /* ANY* input */
SQLINTEGER BufferLength, /* 32-bit input */
SQLINTEGER *StrLen_or_Ind /* 32-bit pointer to output */
);
Job: Bind a Column to a host variable by setting fields in the ARD.
Algorithm:
If (ColumnNumber < 1)
return error: 07009 Dynamic SQL error-invalid descriptor index
If (BufferType <> SQL_C_DEFAULT and BufferType is not a valid type code)
/* A "valid type code" could be one of: 1 (SQL_CHAR), 2 (SQL_NUMERIC), 3
(SQL_DECIMAL), 4 (SQL_INTEGER), 5 (SQL_SMALLINT), 6 (SQL_FLOAT), 7 (SQL_REAL),
8 (SQL_DOUBLE), 18 (SQL_UDT_LOCATOR), 20 (SQL_REF), 30 (SQL_BLOB), 31
(SQL_BLOB_LOCATOR), 40 (SQL_CLOB), 41 (SQL_CLOB_LOCATOR), etc. But no host
language has corresponding <data type>s for all of those; see the data type
correspondences lists in our chapter on embedded SQL. */
return error: HY003 CLI-specific condition-invalid data type in application descriptor
If (BufferLength <= 0)
return error: HY090 CLI-specific condition-invalid string length or buffer length
Set ARD.IDA[ColumnNumber].SQL_DESC_TYPE = BufferType
Set ARD.IDA[ColumnNumber].SQL_DESC_OCTET_LENGTH = BufferLength
Set ARD.IDA[ColumnNumber].SQL_DESC_LENGTH = maximum for this data type
Set ARD.IDA[ColumnNumber].SQL_DESC_DATA_POINTER = Data
Set ARD.IDA[ColumnNumber].SQL_DESC_OCTET_LENGTH_POINTER = StrLen_or_Ind
Set ARD.IDA[ColumnNumber].SQL_DESC_INDICATOR_POINTER = StrLen_or_Ind
If (ColumnNumber > ARD.SQL_DESC_COUNT)
Set ARD.SQL_DESC_COUNT = ColumnNumber
/* If an error occurs: the DBMS leaves SQL_DESC_COUNT unchanged, but may
set IDA fields to implementation-dependent values. */
Notes:
Technically, a host variable is “bound” when it is associated with a host-variable address. Since
SQLBindCol
causes a setting ofARD.IDA[ColumnNumber].SQL_DESC_DATA_POINTER
, we can describe it thus: “SQLBindCol
performs a binding of an output host variable for a result-set Column; specifically the result-set Column indicated by theColumnNumber
parameter.”Technically, this host variable is called a “target specification”.
SQLBindCol
is for values that flow out from the DBMS to a host variable.The usual idea is that what you set up with
SQLBindCol
will later be used bySQLFetch
orSQLFetchScroll
. Alternatively, you could bind after fetching, using theSQLGetData
function.You need a valid
hstmt
, but you don’t need a result set yet. That is, you canSELECT
either before or after you callSQLBindCol
.Calling
SQLBindCol
for astmt
is conceptually similar to callingSQLSetDescRec
for thestmt
's ARD. In fact, everything that you can do withSQLBindCol
, and more, can be done withSQLSetDescRec
. However,SQLBindCol
is seen far more often thanSQLSetDescRec
.Warning
You are passing addresses. The DBMS will write to those addresses. So there are two easy ways to cause GPFs:
Pass the address of a local variable, exit from the procedure that the local variable is defined in, then call
SQLFetch
.Pass a buffer which is too small to hold the result. For
CHAR
andBIT
<data type>s there is a guard against this (you passBufferLength
to tell the DBMS when it must stop). For numeric <data type>s there is also a guard against this (if you say thatBufferType
isSQL_SMALLINT
the DBMS won’t move a 32-bit value to it). But if you enter the wrong value forDataType
… Ka-Boom! As a safeguard, some programmers deliberately “unbind” when they finish fetching – see the description ofSQLFreeStmt(...SQL_UNBIND)
.
SQLBindCol
does not set every defined field in the ARD. You might have to follow up with a call toSQLSetDescField
if for some reason you have to change a low-importance field, likeSQL_DESC_DATETIME_INTERVAL_PRECISION
.The value of
BufferLength
is irrelevant for non-string <data type>s. Nevertheless, you must always pass a value greater than zero.
Example:
/* This example shows a retrieval of a CHAR string. */
#include "sqlcli.h"
SQLHSTMT hstmt;
SQLCHAR value[128];
SQLINTEGER value_indicator;
...
SQLBindCol(hstmt,1,SQL_CHAR,value,sizeof(value),&value_indicator);
SQLExecDirect(stmt,"SELECT 'ABCDE' FROM t",SQL_NTS);
SQLFetch(hstmt);
/* At this point, value has "ABCDE\0", value_indicator has zero. */
...
/* This example shows a retrieval of a floating-point variable. The C data
type is float, but we use SQL_REAL -- for explanation, see the list of data
correspondences. We'd like to pass NULL as the BufferLength parameter -- it's
irrelevant -- but we can't. */
#include "sqlcli.h"
#include <math.h>
SQLHSTMT hstmt;
SQLREAL value; /* sqlcli.h contains the line "typedef float SQLREAL" */
SQLINTEGER value_indicator;
...
SQLBindCol(hstmt,1,SQL_REAL,&value,sizeof(value),&value_indicator);
SQLExecDirect(stmt,"SELECT 1.5E5 FROM t",SQL_NTS);
SQLFetch(hstmt);
/* At this point, value has 1.5E5, value_indicator has zero. */
...
/* This example shows a retrieval of two Columns from the first row of
TABLE_1. It displays their values, or displays "NULL". */
#include "sqlcli.h"
SQLINTEGER col_1;
SQLCHAR col_2[128];
SQLINTEGER col_1_ind,col_2_ind;
SQLHSTMT hstmt;
SQLHDESC hdesc;
...
SQLBindCol(hstmt,1,SQL_INTEGER,&col_1,sizeof(col_1),&col_1_ind);
SQLBindCol(hstmt,2,SQL_CHAR,col_2,sizeof(col_2),&col_2_ind);
SQLExecDirect(hstmt,"SELECT col_1,col_2 FROM Table_1",SQL_NTS);
SQLFetch(hstmt);
if (col_1_ind == SQL_NULL_DATA) printf("NULL\n");
else printf("%ld.\n",col_1);
if (col_2_ind == SQL_NULL_DATA) printf("NULL\n");
else printf("%s.\n",col_2);
...
Here is a picture of the ARD after the SQLSetDescField
call is done:
- ----------------------- -------------------
--SQL_DESC_ALLOC_TYPE- - SQL_DESC_COUNT -
- ----------------------- ------------------
- | 00001 | | 00002 |
- ----------------------- ------------------
-
-----------------------------------------------------
-- SQL_DESC_DATA_POINTER | SQL_DESC_TYPE | ........ |
-----------------------------------------------------
- 1-&col_1 | 00004 | ........ |
-----------------------------------------------------
- 2-col_2 (address) | 00001 | ........ |
-----------------------------------------------------
Here is a picture of the IRD after the SQLExecDirect
call is done:
- ----------------------- -------------------
--SQL_DESC_ALLOC_TYPE- - SQL_DESC_COUNT -
- ----------------------- ------------------
- | 00001 | | 00002 |
- ----------------------- ------------------
-
-----------------------------------------------------
-- SQL_DESC_NAME | SQL_DESC_TYPE | ........ |
-----------------------------------------------------
- 1-'Col_1' | 00005 | ........ |
- ----------------------------------------------------
- 2-'Col_2' | 00001 | ........ |
-----------------------------------------------------
[Obscure Rule] In this example, COL_1
is actually a SMALLINT
(SQL_DESC_TYPE == 5
), but it’s being transferred to an INTEGER
(SQL_DESC_TYPE == 4
). That is not a problem – the DBMS will automatically
“CAST
(<smallint Column value> TO INTEGER
)”. In fact, the DBMS will
automatically cast from the IRD type to the ARD type. If the cast is
syntactically impossible, an SQLSTATE
error appears: 07006 "Dynamic SQL
error-restricted data type attribute violation."
ODBC: SQLBindCol
always triggers a consistency check. The value of
BufferLength
must be >= 0
(standard SQL says the value of
BufferLength
must be > 0
). The difference looks trivial, but the
majority of ODBC application programs would collapse tomorrow if a DBMS vendor
decided to enforce the standard SQL rule.
SQL_C_DEFAULT¶
There is a lazy way to bind numbers. Instead of passing a real <data type>,
pass 99
(SQL_C_DEFAULT
). Here is an example using SQLBindCol
–
notice that the third parameter is SQL_C_DEFAULT
, so, after SQLBindCol
,
the value in ARD.IDA[n].SQL_DESC_TYPE == SQL_C_DEFAULT
. When SQLFetch
occurs, it handles the default request like this:
Set
ARD.IDA[n].SQL_DESC_TYPE = IRD.IDA[n].SQL_DESC_TYPE
.Set
ARD.IDA[n].SQL_DESC_PRECISION = IRD.IDA[n].SQL_DESC_PRECISION
.Set
ARD.IDA[n].SQL_DESC_SCALE = IRD.IDA[n].SQL_DESC_SCALE
. (These are temporary settings.SQLFetch
never makes permanent changes to anydesc
fields.) Now there is enough information to continue the fetch:
#include "sqlcli.h"
SQLHSTMT hstmt;
SQLSMALLINT col_1;
...
SQLExecDirect(hstmt,"SELECT col_1 FROM Table_1",SQL_NTS;
SQLBindCol(hstmt,1,SQL_C_DEFAULT,&col_1,1,NULL);
SQLFetch(hstmt);
And now, a few words of urgent warning:
The only fields involved are type, precision and scale – not length or octet_length.
There is no check for possible overflow of the target output.
The ODBC specification does not agree with the Standard about what the default <data type> codes should be.
Retrieving Column Values¶
Column retrieval involves executing a SELECT
statement, then fetching from
the result set to variables defined in your host program. The Columns in the
select list must be “bound” to the host-variable addresses. The problems you
must solve – with the DBMS’s help – are:
The impedance mismatch between SQL <data type>s and host language types.
Telling the DBMS where to put the data.
Telling the DBMS how null values should be signalled to the host.
Ensuring that the DBMS does not go beyond the host-variable buffers.
There are two solutions to these problems:
Call
SQLBindCol
,SQLSetDescField
orSQLSetDescRec
, then fetch usingSQLFetch
orSQLFetchScroll
.Fetch using
SQLFetch
orSQLFetchScroll
, then callSQLGetData
.
The most popular option is to call SQLBindCol
, then SQLFetch
.
There are two desc
s involved here – the IRD (which the DBMS sets up when
the SELECT
statement is executed), and the ARD (which the host program sets
up at any time before the fetch). The programmer’s job, then, can be seen as
making sure that the ARD matches the IRD, and setting up appropriate buffers
for the DBMS to fetch data into.
Fetch Loops Revisited¶
Once more into the “fetch”, dear friends! Now that you know about binding, you
can fetch INTO
something. Here is a program which does so. Most functions
are shown in skeletal form. There is a test for truncation.
#include "sqlcli.h"
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLCHAR char_string[128];
SQLINTEGER char_string_ind;
SQLRETURN sqlreturn;
void main ()
{
SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);
SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
SQLConnect(hdbc,...,SQL_NTS);
SQLAllocHandle(SQL_HANDLE_HSTMT,hdbc,&hstmt);
SQLExecDirect(hstmt,"SELECT ...",SQL_NTS);
SQLBindCol(
hstmt,1,SQL_CHAR,char_string,sizeof(char_string),&char_string_ind);
for (;;) {
sqlreturn = SQLFetch(hstmt);
if (sqlreturn == SQL_NO_DATA) break;
if (sqlreturn == SQL_ERROR) {
printf("Error ... aborting\n");
break; }
if (sqlreturn == SQL_SUCCESS_WITH_WARNING) {
/* We could call SQLGetDiagnostics to find out if sqlstate == '01004'
(string data right truncation), diagnostics is a later chapter.
Instead, we'll see if see if the DBMS had more data than it sent. */
if (char_string_ind >= sizeof(char_string) printf("[Truncated!]"); }
if (char_string_ind==SQL_NULL_DATA) printf("NULL\n");
else printf("%s.\n",char_string); }
SQLCloseCursor(hstmt);
SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
SQLFreeHandle(SQL_HANDLE_ENV,henv); }
SQLGetData¶
Function Prototype:
SQLRETURN SQLGetData(
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT ColumnNumber, /* 16-bit input -- index to IDA */
SQLSMALLINT TargetType, /* 16-bit input. Concise. */
SQLPOINTER TargetValue, /* VOID* output */
SQLINTEGER BufferLength, /* 32-bit input */
SQLINTEGER *StrLen_or_Ind /* 32-bit output */
);
Job: Get a value from one Column of a result set. Call SQLGetData
after
calling SQLFetch
or SQLFetchScroll
.
Algorithm:
If (there is no fetched row associated with stmt)
/* Looks like somebody forgot to call SQLFetch or SQLFetchScroll */
return error: HY010 CLI-specific condition-function sequence error
If (the fetched row is "empty")
/* Looks like somebody DELETEd the row */
return warning: 02000 No data
If (ColumnNumber < 1 or ColumnNumber > IRD.SQL_DESC_COUNT)
/* IRD.SQL_DESC_COUNT is the number of Columns in the select list */
return error: 07009 Dynamic SQL error-invalid descriptor index
If (ARD.IDA[ColumnNumber].SQL_DESC_DATA_POINTER <> 0)
/* It's illegal to call SQLGetdata for a "bound" Column */
return error: 07009 dynamic SQL error-invalid descriptor index
/* Following check is implementation-defined -- it depends whether
the DBMS supports SQLGetData extensions -- see SQLGetInfo */
If (ARD.IDA[x].SQL_DESC_DATA_POINTER==0 for any IDA before ColumnNumber)
return error: 07009 dynamic SQL error-invalid descriptor index
/* Following check is implementation-defined -- it depends whether
the DBMS supports SQLGetData Extensions -- see SQLGetInfo */
If (ARD.IDA[x].SQL_DESC_DATA_POINTER<>0 for any IDA after ColumnNumber)
return error: 07009 dynamic SQL error-invalid descriptor index
/* The rest of the SQLGetData algorithm is the same as the algorithm
for fetching a "bound" Column -- see SQLBindCol for details. */
Notes:
SQLGetData
does not make permanent changes to the ARD. But it can be thought of as a function which makes a temporary binding for a specified Column in a result set, and transferring the Column value to the bound target variable.It looks like there are two possible strategies for retrieving data – with
SQLBindCol
or withSQLGetData
. Let’s compare the two:The
SQLBindCol
strategy:SQLExecDirect(...,"SELECT ...",...); SQLBindCol(...,1,&var_1,...); SQLBindCol(...,2,&var_2,...); for (;;) { if (SQLFetch(...)==100) break; } SQLGetData(...,2,...,&var_2,...); }
The
SQLGetData
strategy:SQLExecDirect(...,"SELECT ...",...); for (;;) { if (SQLFetch(...)==100) break; SQLGetData(...,1,...,&var_1,...);
Look hard at where the loop is. The
SQLBindCol
strategy is apparently more efficient, because the binding happens only once. If you useSQLGetData
, you’ll have to use it for every iteration of theSQLFetch
loop. On the other hand, maybe that’s exactly what you want to do. There are times when you have to be flexible, and change some factor (such as the variable’s address) while inside the loop. ThenSQLGetData
is the choice.Suppose you bind Column #1 (using
SQLBindCol
), then you fetch, then you get Column #2 (usingSQLGetData
). That’s legal. But it might not be legal to skip Columns, to get Columns out of order or to get Columns twice. The ability to get Columns in any order is called the “SQLGetData
Extensions” feature. You can check whether your DBMS supports it (you’ll see how when we describe theSQLGetInfo
function). But usually there’s nothing difficult about getting Columns in ascending Column-number order.*StrLen_or_Ind
points to a 32-bit integer, not a 16-bit integer. Thus the final three parameters –*TargetValue
,BufferLength
,*StrLen_or_Ind
– do not form a typical example of Character String Retrieval parameters.Since
SQLGetData
cannot take advantage of the full set of ARD fields, it only gets used for simple situations.
Example:
#include "sqlcli.h"
#define CHAR_LENGTH 1000
SQLHSTMT hstmt;
SQLRETURN sqlreturn;
SQLINTEGER sIntegerColumn;
SQLINTEGER IntegerIndicator;
SQLCHAR szCharColumn[CHAR_LENGTH];
SQLINTEGER IntegerIndicator;
...
SQLExecDirect(hstmt,"SELECT col_1,col_2 FROM Table_1",SQL_NTS);
for (;;) {
sqlreturn = SQLFetch(hstmt);
if (sqlreturn != SQL_SUCCESS && sqlreturn != SQL_SUCCESS_WITH_INFO) {
break; }
SQLGetData(hstmt,1,SQL_INTEGER,&sIntegerColumn,NULL,&IntegerIndicator);
if (IntegerIndicator != SQL_NULL_DATA) {
if (sIntegerColumn > 0) {
SQLGetData(hstmt,2,SQL_CHAR,szCharColumn,CHAR_LENGTH,&CharIndicator);
if (CharIndicator == SQL_NULL_DATA) strcpy(szCharColumn,"");
printf("%s.\n",szCharColumn); } } }
SQLCloseCursor(hstmt);
...
ODBC: SQLGetData
has been around since ODBC version 1.0. SQLGetData
can be used, with char or bit <data type>s, to get data in pieces. This is done
by calling SQLGetData
with BufferLength == 1000
(to ask for the first
1000 octets), calling again – for the same Column number with BufferLength
== 1000
(to ask for the next 1000 octets), and so on. In the days of 16-bit
operating systems, this was a useful feature.
SQLBindParameter¶
Function Prototype:
SQLRETURN SQLBindParameter (
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT ParameterNumber, /* 16-bit input: must be > 0 */
SQLSMALLINT InputOutputMode, /* 16-bit input: must be one of:
1 SQL_PARAM_MODE_IN,
2 SQL_PARAM_MODE_INOUT,
4 SQL_PARAM_MODE_OUT */
SQLSMALLINT ValueType, /* 16-bit input ... for the APD
must be in table of host/SQL <data type>
correspondences */
SQLSMALLINT ParameterType, /* 16-bit input ... for the IPD
must be in table of concise types */
SQLINTEGER Columnsize, /* 32-bit input */
SQLSMALLINT DecimalDigits, /* 16-bit input */
SQLPOINTER ParameterValue, /* DEF */
SQLINTEGER BufferLength, /* 32-bit input */
SQLINTEGER *StrLen_or_Ind /* DEF */
);
Job: Describe one “dynamic parameter specification” (in the IPD), and its
host variable (in the APD). SQLBindParameter
is useful if you pass
parameters from the application to the DBMS. Such parameters are marked by
parameter markers (question marks) in SQL statements.
Algorithm:
The algorithm is to complex to show in the usual manner. For this function, we will have to make heavy use of texts and charts. What happens –
If the function fails and returns
-1
(SQL_ERROR
): see07009
,HY015
andHY090
in our chapter on SQL/CLI diagnostics. (Note: If errors happen, some of the IPD and APD fields may be garbaged, althoughSQL_DESC_COUNT
won’t change.)If the function succeeds: some IPD and APD fields are set. The precise setting depends on a combination of factors, but in general we can say that:
hstmt
designates whichstmt
. The affecteddesc
s are thestmt
's IPD and APD.ParameterNumber
designates which item descriptor area, within adesc
. IfParameterNumber
isn
, then the affected IDAs areIPD.IDA[n]
andAPD.IDA[n]
.ParameterType
,Columnsize
andDecimalDigits
affectIPD.IDA[n]
.ValueType
,BufferLength
,ParameterValue
andStrlen_Or_Ind
affectAPD.IDA[n]
.If
ParameterNumber
is greater than an APD or IPD’sSQL_DESC_COUNT
field, then thatSQL_DESC_COUNT
field gets the value inParameterNumber
.Sometimes passed values are ignored.
Values should be consistent, but some DBMSs won’t perform a consistency check until
SQLExecute
happens.
In the charts which follow, we show the effects on particular APD or IPD fields. Notice that the usual effect is that the field simply receives the value of a parameter that you pass, but that sometimes special calculations are necessary. Particularly complex are “datetime” and “interval” settings.
SQLBindParameter effect on APD Fields¶
Field |
Gets |
---|---|
|
|
|
|
|
|
|
|
|
|
|
|
For example, if you call SQLBindParameter
with ParameterNumber=2
,
InputOutputMode = OUT
, ValueType=1
(the code for CHAR
<data type>),
BufferLength=5
, ParameterValue =
address of x_char_buffer
and
Strlen_Or_Ind =
address of x_char_buffer_indicator
, the result is that,
in the APD.IDA[2]
(i.e.: the second item descriptor area in the application
parameter desc
), the settings are as follows: SQL_DESC_PARAMETER_MODE =
OUT
, SQL_DESC_TYPE = 1
, SQL_DESC_OCTET_LENGTH = BufferLength
,
SQL_DESC_DATA_POINTER = x_char_buffer
address, SQL_DESC_INDICATOR_POINTER
= x_char_buffer_indicator
address and SQL_DESC_OCTET_LENGTH_POINTER =
x_char_buffer_indicator
address.
SQLBindParameter Effect on IPD.IDA Fields for Numeric <data type>s¶
Field |
Gets |
---|---|
|
|
|
|
|
|
This chart shows the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 2
or 3
or 4
or 5
or 6
or 7
or
8
(SQL_NUMERIC
or SQL_DECIMAL
or SQL_INTEGER
or
SQL_SMALLINT
or SQL_FLOAT
or SQL_REAL
or SQL_DOUBLE
). For
example, if you call SQLBindParameter
with ParameterNumber = 1
,
ParameterType = 3
, Columnsize=5
and DecimalDigits=4
, the result is
that, in the IPD.IDA[1]
(i.e.: the first item descriptor area in the
Implementation Parameter Desc), the settings are as follows:
SQL_DESC_TYPE=3
, SQL_DESC_PRECISION=5
and SQL_DESC_SCALE=4
.
SQLBindParameter Effect IPD.IDA Fields for Datetime <data type>s¶
Field |
Gets for … |
||
---|---|---|---|
91 (date) |
92 (time) |
93 (timestamp) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
This chart shows the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 91
or 92
or 93
(date or time or timestamp).
SQL_DESC_LENGTH
is the length in “positions”. SQL_DESC_PRECISION
is the
“fractional-seconds” precision. For example, if you call SQLBindParameter
with ParameterNumber = 5
, ParameterType=92
, Columnsize=8
and
DecimalDigits=0
, the result is that, in the IPD.IDA[5]
(i.e.: the fifth
item descriptor area in the Implementation Parameter Desc), the settings are as
follows: SQL_DESC_TYPE=9
, SQL_DESC_DATETIME_INTERVAL_CODE=2
,
SQL_DESC_LENGTH=8
and SQL_DESC_PRECISION=0
.
SQLBindParameter Effect on IPD.IDA Fields for Interval Year/Month <data type>s¶
Field |
Gets for … |
|
---|---|---|
101 (year) |
102 (month) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
---|---|
107 (year to month) |
|
|
|
|
|
|
|
|
|
|
|
The charts show the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 101
or 102
or 107
(interval year or interval
month or interval year-to-month). SQL_DESC_LENGTH
is the length in
“positions”. SQL_DESC_PRECISION
is the “fractional-seconds” precision.
SQL_DESC_DATETIME_INTERVAL_PRECISION
is the “leading field” precision. For
example, if you call SQLBindParameter
with ParameterNumber = 2
,
ParameterType=107
, Columnsize=8
and DecimalDigits=0
, the result is
that, in the IPD.IDA[2]
(i.e.: the second item descriptor area in the
Implementation Parameter Desc), the settings are as follows:
SQL_DESC_TYPE=10
, SQL_DESC_DATETIME_INTERVAL_CODE=7
,
SQL_DESC_LENGTH=8
, SQL_DESC_PRECISION=0
and
SQL_DESC_DATETIME_INTERVAL_PRECISION=(8-4)=4
.
SQLBindParameter Effect on IPD.IDA Fields for Interval Day/Time <data type>s with No SECONDs¶
Field |
Gets for … |
|
---|---|---|
103 (day) |
104 (hour) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
||
---|---|---|---|
105 (minute) |
108 (day to hour) |
109 (day to minute) |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
---|---|
111 (hour to minute) |
|
|
|
|
|
|
|
|
|
|
|
The charts show the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 103
or 104
or 105
or 108
or 109
or
111
(interval day or interval hour or interval minute or interval day to
hour or interval day to minute or interval hour to minute). SQL_DESC_LENGTH
is the length in “positions”. SQL_DESC_PRECISION
is the
“fractional-seconds” precision. SQL_DESC_DATETIME_INTERVAL_PRECISION
is the
“leading field” precision. For example, if you call SQLBindParameter
with
ParameterNumber = 2
, ParameterType=104
, Columnsize=3
and
DecimalDigits=0
, the result is that, in the IPD.IDA[2]
(i.e.: the
second item descriptor area in the Implementation Parameter Desc), the settings
are as follows: SQL_DESC_TYPE=10
, SQL_DESC_DATETIME_INTERVAL_CODE=4
,
SQL_DESC_LENGTH=3
, SQL_DESC_PRECISION=0
and
SQL_DESC_DATETIME_INTERVAL_PRECISION=(3-1)=2
.
SQLBindParameter Effect on IPD.IDA Fields for Interval Day/Time <data type>s with SECONDs¶
Field |
Gets for … |
---|---|
106 (second) |
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
---|---|
110 (day to second) |
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
---|---|
112 (hour to second) |
|
|
|
|
|
|
|
|
|
|
|
Field |
Gets for … |
---|---|
113 (minute to second) |
|
|
|
|
|
|
|
|
|
|
|
The charts show the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 106
or 110
or 112
or 113
(interval second
or interval day-to-second or interval hour-to-second or interval
minute-to-second). SQL_DESC_LENGTH
is the length in “positions”.
SQL_DESC_PRECISION
is the “fractional-seconds” precision.
SQL_DESC_DATETIME_INTERVAL_PRECISION
is the “leading field” precision. The
Column size must include one position for a “.” if there is a
fractional-seconds amount – that is, INTERVAL '+5.00' SECOND
has a length
of 5
(Columnsize=5
) and a fractional seconds precision of 2
(DecimalDigits=2
), so the precision of the leading field is
(Columnsize-DecimalDigits-2) = (5-2-2) = 1
. For example, if you call
SQLBindParameter
with ParameterNumber = 1
, ParameterType=110
,
Columnsize=12
and DecimalDigits=0
, the result is that, in the
IPD.IDA[1]
(i.e.: the first item descriptor area in the Implementation
Parameter Desc), the settings are as follows: SQL_DESC_TYPE=10
,
SQL_DESC_DATETIME_INTERVAL_CODE=10
, SQL_DESC_LENGTH=12
,
SQL_DESC_PRECISION=0
and
SQL_DESC_DATETIME_INTERVAL_PRECISION=(12-10)=2
.
SQLBindParameter Effect on IPD.IDA Fields for Other <data type>s¶
Field |
Gets … |
---|---|
|
|
|
|
The chart shows the effect on IPD.IDA[n]
fields if the value of the passed
ParameterType
is 1
or 12
or 14
or 15
or 30
or 40
(CHAR
or CHAR VARYING
or BIT
or BIT VARYING
or BLOB
or
CLOB
). For example, if you call SQLBindParameter
with ParameterNumber
= 8
, ParameterType = 1
and Columnsize = 50
, the result is that, in
the IPD.IDA[8]
(i.e.: the eighth item descriptor area in the Implementation
Parameter Desc), the settings are as follows: SQL_DESC_TYPE = 1
and
SQL_DESC_LENGTH = 50
.
Notes:
ValueType
might beSQL_C_DEFAULT
.*StrLen_or_Ind
might beSQL_DATA_AT_EXEC
.*StrLen_or_Ind
might beSQL_DATA_NULL
.Datetime codings are an occasional source of confusion, because the specification was changed a few years ago. Make sure, especially, that
ParameterType
is one of the “Concise Codes” for datetimes.The DBMS does not have to perform a consistency check at this time. Contrast
SQLSetDescField
andSQLSetDescRec
, which require a consistency check as soon asSQL_DATA_POINTER
contents change to a non-zero value.Beware if you prepare before you bind. For example:
SQLPrepare(hstmt,"UPDATE Table_1 SET int_column =?+5;",SQL_NTS); SQLBindParameter(hstmt,...); SQLExecute(hstmt); ...
This sequence is okay according to standard SQL rules. But a DBMS exists which evaluates input parameters while processing
SQLPrepare
, instead of waiting and evaluating parameters while processingSQLExecute
. In that case, the above example won’t work. Or – even worse – it will appear to work becauseSQLPrepare
picks up an input parameter that you made for a completely different statement! It’s difficult to fix this problem by changing the order of the statements, but you could at least get rid of leftover bound parameters by callingSQLFreeStmt(...SQL_RESET_PARAMS)
.
Example:
...
SQLHSTMT hstmt;
SQLCHAR c[6];
...
SQLBindParameter(
hstmt,1,SQL_PARAM_MODE_INPUT,SQL_C_CHAR,SQL_CHAR,5,NULL,c,NULL,NULL);
SQLExecDirect(hstmt,"UPDATE Table_1 SET col_1 = ?;",-3);
This example shows how to bind a character parameter. Here, SQLExecDirect
contains an SQL statement with a single parameter marker (?
is a parameter
marker). Before executing the SQL statement, we must bind that parameter. Let’s
look at the SQLBindParameter
arguments, in order:
[hstmt]
is thestmt
handle – same as in theSQLExecDirect
function.[1]
isParameterNumber
– it’s 1 because we’re binding parameter number 1.[SQL_PARAM_MODE_INPUT]
isInputOutputMode
– this is “input” (from host to DBMS).[SQL_C_CHAR]
isValueType -- SQL_C_CHAR = 1
, that is, the input is a hostchar
field.[SQL_CHAR]
isParameterType -- SQL_CHAR = 1
, that is, the input is an SQLCHAR
field.[5]
isColumnsize
– the input is 5 characters long.[NULL]
isDecimalDigits
– the value here doesn’t matter because we’re dealing with achar
field.[c]
isParameterValue
– what we’re actually passing here is the address ofc
.[NULL]
isBufferLength
– the value here doesn’t matter. (Note: Many programmers would pass[6]
here – i.e.: the number of octets inc
. We deplore this misleading practice. If the parameter mode isSQL_PARAM_MODE_INPUT
, the “octet length” is not determined by what is passed forBufferLength
.)[NULL]
is*StrLen_or_Ind
– in this case we won’t supply an indicator pointer.... SQLHSTMT hstmt; SQLCHAR date [] = "1994-01-31"; ... SQLBindParameter( hstmt,1,SQL_PARAM_MODE_INPUT,SQL_C_CHAR,SQL_TYPE_DATE,10,0,date,11,0); SQLExecDirect(hstmt,"SELECT * FROM Table_1 WHERE date_field = ?;",SQL_NTS);
This example shows how to bind a date parameter. This is much like the previous
example, but this time ParameterType
is SQL_TYPE_DATE
. (Note:
ParameterType
is SQL_TYPE_DATE (91)
– not SQL_DATE (9)
.
Columnsize
is 10
, because the number of positions in a date is 10:
'yyyy-mm-dd'
.) In such bindings, there is an implicit CAST
to DATE
.
One of the strengths of SQLBindParameter
is that it makes implicit casts
easy. So here’s what most programmers do:
Store everything in the host program as character strings.
Pass
ValueType = SQL_C_CHAR
for everySQLBindParameter
call.Set
ParameterType =
desired SQL <data type>, forcing the DBMS to perform appropriate conversions.
...
SQLHSTMT hstmt;
SQLINTEGER i; /* sqlcli.h has "typedef long SQLINTEGER" */
SQLINTEGER i_indicator;
...
SQLPrepare(hstmt,"INSERT INTO Table_1 VALUES (?);",SQL_NTS);
SQLBindParameter(
hstmt, /* hstmt = "handle of stmt" */
1, /* ParameterNumber = "parameter #1" */
SQL_PARAM_MODE_INPUT, /* InputOutputType = "input" */
SQL_C_LONG, /* ValueType = "long int" (as seen from C) */
SQL_INTEGER, /* ParameterType = "int" (as seen from SQL) */
NULL, /* Columnsize "don't care" */
NULL, /* DecimalDigits "don't care" */
&i, /* ParameterValue "address of input data" */
NULL, /* BufferLength "don't care" */
&i_indicator); /* *StrLen_or_Ind "address of indicator" */
i_indicator=SQL_NULL_DATA; /* sqlcli.h has "#define SQL_NULL_DATA -1" */
SQLExecute(hstmt);
This example shows how to bind a nullable integer parameter. Here, the value of
i
does not matter because the indicator variable’s value is -1. Ultimately,
what gets inserted is a NULL
value.
#include "sqlcli.h"
#include <math.h>
#include <stdio.h>
input_string char[20];
SQLHENV henv;
SQLHDBC hdbc;
SQLHSTMT hstmt;
SQLSMALLINT experiment_number;
SQLREAL measurement;
void main ()
{
SQLAllocHandle(SQL_HANDLE_ENV,NULL,&henv);
SQLAllocHandle(SQL_HANDLE_DBC,henv,&hdbc);
SQLConnect(hdbc,"Exp",SQL_NTS);
SQLAllocHandle(SQL_HANDLE_HSTMT,hdbc,&hstmt);
SQLExecDirect(
hstmt,"CREATE TABLE Experiments(no INT,measurement REAL);",SQL_NTS);
SQLPrepare(hstmt,"INSERT INTO Experiments VALUES (?,?);",SQL_NTS);
SQLBindParameter(
hstmt,1,SQL_PARAM_MODE_INPUT,SQL_C_SHORT,SQL_SMALLINT,0,0,
&experiment_no,0,0);
SQLBindParameter(
hstmt,2,SQL_PARAM_MODE_INPUT,SQL_C_FLOAT,SQL_REAL,24,0,&measurement,0,0);
for (experiment_no=0; experiment_no<10; ++experiment_no) {
printf("Enter measurement:\n");
gets(input_string);
measurement=atof(input_string);
SQLExecute(hstmt); }
SQLEndTran(SQL_HANDLE_DBC,hdbc,SQL_COMMIT);
SQLFreeHandle(SQL_HANDLE_STMT,hstmt);
SQLDisconnect(hdbc);
SQLFreeHandle(SQL_HANDLE_DBC,hdbc);
SQLFreeHandle(SQL_HANDLE_ENV,henv);
}
This example shows how to bind two parameters in a loop: it contains calls to
SQLPrepare
and SQLBindParameter
before the loop starts. Note that, for
the Columnsize
of the measurement parameter, we used 24. In fact, the
precision of a REAL
is implementation-defined; for some DBMSs, the
appropriate value here is 7.
ODBC: The SQLBindParameter
function is new in ODBC 3.0. An ODBC 2.0
function named SQLSetParam
was pretty well the same except that it had no
InputOutputMode
parameter. ODBC uses the abbreviation SQL_PARAM_INPUT
rather than SQL_PARAM_MODE_INPUT
. ODBC, in addition to the actions
described for standard SQL, will change ARD.IDA[n].SQL_DESC_SCALE
and
ARD.IDA[n].SQL_DESC_PRECISION
to “default” values if
ARD.IDA[n].SQL_DESC_TYPE
becomes SQL_NUMERIC
.
Who should populate the IPD?¶
We’ve had to repeat, tiresomely, the phrase “if your DBMS supports auto-population of the IPD …” because this is a major factor of the CLI that’s implementation-defined. Let’s have a last look at what it means.
If the DBMS supports auto-population of the IPD, then:
You can find this out by calling
SQLGetConnectAttr
.You can’t change the setting by calling
SQLSetConnectAttr
.The IPD IDA fields will be populated by
SQLPrepare
.
The action is analogous to what SQLPrepare
does to an IRD (for a SELECT
statement), so what’s the problem? Why don’t all DBMSs support auto-population?
After all, in an SQL statement like this:
UPDATE Table_1 SET col_1 = 5.1E4 + ?
the DBMS must know the definition of the parameter (represented by ?
),
because it “knows” the <data type> of both TABLE_1.COL_1
and of the
<literal>. Well, to answer that, we must remind you that a DBMS is often two
quite different programs: the client (driver) and the server (data source).
Now, it’s true that the server can figure out what a parameter must be. But the
client knows nothing about TABLE_1.COL_1
unless the server tells it. As
Microsoft puts it: “… most data sources do not provide a way for the driver
to discover parameter metadata.” What it comes down to, then, is: Yes, the DBMS
should populate the IPD. But it won’t, so you should. Luckily, since you’re
setting up the host variables anyway, you usually have all the information you
need to set up the IPD at the time you write your application.
SQLColAttribute¶
Function Prototype:
SQLRETURN SQLColAttribute(
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT ColumnNumber, /* 16-bit input, base 1, = Column # in result data corresponds to IRD.IDA[n]
SQLSMALLINT FieldIdentifier, /* 16-bit input */
SQLCHAR *CharacterAttribute, /* to char[L], where L = max VARCHAR length. field value ret'd to here if CHAR, else ignored */
SQLSMALLINT BufferLength, /* 16-bit input = sizeof(CharacterAttribute) ignored if non-CHAR */
SQLSMALLINT *StringLength, /* 16-bit output */
SQLINTEGER *NumericAttribute /* 32-bit output */
);
Job: Get one field value from an IRD.
Algorithm:
If (it's an "ITEM")
/* There must be an open Cursor */
If (FieldIdentifier is ..._CATALOG or ..._SCHEMA or ..._NAME)
/* The return is a character string, it goes to CharacterAttribute */
SQLGetDescField (<descriptor handle>,ColumnNumber,FieldIdentifier,
CharacterAttribute,BufferLength,&StringLength);
Else If (FieldIdentifier is ..._TYPE)
/* The return is an integer, it goes to NumericAttribute */
If (datetime)
NumericAttribute = "concise code value"
If (interval)
NumericAttribute = "concise code value"
Else
NumericAttribute = <data type>
Else
SQLGetDescField(
<descriptor handle>,ColumnNumber,FieldIdentifier,&NumericAttribute,
BufferLength,&StringLength);
Notes:
SQLColAttribute
stands for “[get] Column Attribute”. The word “attribute” here means “a field in the Implementation Row Descriptor (IRD)”.The IRD is populated by preparing or executing a
SELECT
statement. For example, when displaying on the screen, it is useful to know the <Column name> and the <data type>. Those are two of the pieces of information thatSQLColAttribute
will provide you.All of the information that
SQLColAttribute
returns can also be found via theSQLGetDescField
function.With some DBMSs, it is a bad idea to retrieve IRD fields after
SQLPrepare
, but beforeSQLExecute
. The reason, once again, is that the driver may not have an easy time querying the data source for such “metadata” information. In this case, the driver may actually execute a dummy SQL statement merely in order to find out what fields the data source returns. Such a process is inefficient. Therefore, it is commonly recommended that the IRD-information functions –SQLColAttribute
,SQLDescribeCol
and sometimesSQLGetDescField
orSQLGetDescRec
– should be deferred until the SQL statement is executed. If theSQLExecute
function call is in a loop, then you should set flags appropriately so that the IRD-information function is only called once.
Example:
#include "sqlcli.h"
...
name char[10];
SQLSMALLINT name_length;
SQLRETURN sqlreturn;
...
SQLExecDirect("SELECT x AS column_name FROM Table_1");
...
/* We'd like to know the name. */
sqlreturn=SQLColAttribute (
StatementHandle,
1, /* the Column number */
SQL_DESC_NAME, /* the field identifier, = 1011 */
name, /* this is where the name will go */
10, /* BufferLength -- too small! */
*name_length,
0); /* NumericAttribute doesn't matter */
/* The result is: sqlreturn==SQL_SUCCESS_WITH_INFO, and if we now got
the diagnostics we would see sqlstate='01004' (truncation). The
value in name is "column_NA\0". The value in name_length is 11. */
ODBC: SQLColAttribute
arrived with ODBC 3.0. In ODBC 2.x there was a
similar function, SQLColAttributes
, which is now deprecated. Syntactically,
ODBC 3.0’s SQLColAttribute
function is nearly standard CLI. But ODBC
supports only 14 of the standard FieldIdentifier
values (1001
through
1013
and 1099
). ODBC also has 15 non-standard values in the
“implementation-defined” range, for items like “the name of the Base table” or
“whether the Column is case sensitive”. In standard SQL, the fields of an IRD
are still valid even after a Cursor is closed – but this is not the case with
ODBC. In earlier versions, ODBC and the standard CLI had different type
specifications for the BufferLength
and StringLength
parameters. These
differences have now been resolved.
SQLDescribeCol¶
Function Prototype:
SQLRETURN SQLDescribeCol(
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT ColumnNumber, /* 16-bit input */
SQLCHAR *ColumnName, /* CHAR* output */
SQLSMALLINT BufferLength, /* 16-bit input */
SQLSMALLINT *NameLength, /* 16-bit output */
SQLSMALLINT *DataType, /* 16-bit output */
SQLINTEGER *Columnsize, /* 32-bit output */
SQLSMALLINT *DecimalDigits, /* 16-bit output */
SQLSMALLINT *Nullable); /* 16-bit output */
Job: Get the following information about one Column in a result set: the <Column name>, the <data type>, the Column size, the scale and whether the Column might contain nulls.
Algorithm:
If (no statement was prepared with hstmt)
return error: HY010 CLI-specific condition-function sequence error
Find the IRD for hstmt.
If (IRD.SQL_DESC_COUNT == 0)
/* there are no "result set" Columns so last statement must have
been a non-query */
return error: 07005 Dynamic SQL error-prepared statement not a Cursor specification
If (ColumnNumber < 1)
/* in some non-standard implementations, ColumnNumber can be 0 */
return error: 07009 dynamic SQL error-invalid descriptor index
If (ColumnNumber > IRD.SQL_DESC_COUNT)
return error: 07009 dynamic SQL error-invalid descriptor index
/* Use the Character String Retrieval routine for the following: */
Copy IRD.IDA[ColumnNumber].SQL_DESC_NAME to ColumnName,NameLength
Switch (IRD.IDA[ColumnNumber].SQL_DESC_TYPE)
case: == SQL_DATETIME
/* if type = 9 and subtype = 1, return 91. and so on */
Set *data type = 91, 92, 93, 94 or 95, depending on whether
IRD.IDA[ColumnNumber].SQL_DESC_DATETIME_INTERVAL_CODE is 1, 2, 3, 4
or 5, respectively.
case: == SQL_INTERVAL
/* if type = 10 and subtype = 1, return 101, and so on */
Set *data type = 101, 102, 103, 104, 105, 106, 107, 108, 109, 110,
111, 112 or 113, depending on whether
IRD.IDA[ColumnNumber].SQL_DESC_DATETIME_INTERVAL_CODE is 1, 2, 3, 4,
5, 6, 7, 8, 9, 10, 11, 12 or 13, respectively. */
default:
Set *DataType = IRD.IDA[ColumnNumber].SQL_DESC_TYPE
Switch (IRD.IDA[ColumnNumber].SQL_DESC_TYPE)
case: == any "char" <data type> code
Set *Columnsize = IRD.IDA[ColumnNumber].SQL_DESC_OCTET_LENGTH
case: == any "numeric" <data type> code
Set *Columnsize = the maximum length in decimal digits
case: == any "bit" or "datetime" or "interval" <data type> code
Set *Columnsize = IRD.IDA[ColumnNumber].SQL_DESC_LENGTH
Switch (IRD.IDA[ColumnNumber].SQL_DESC_TYPE)
case: == any "exact numeric" <data type> code
Set *DecimalDigits = IRD.IDA[ColumnNumber].SQL_DESC_SCALE
case: == any "datetime" or "interval" <data type> code
Set *DecimalDigits = IRD.IDA[ColumnNumber].SQL_DESC_PRECISION
default:
Set *DecimalDigits = some implementation-dependent value
Set *Nullable = IRD.IDA[ColumnNumber].SQL_DESC_NULLABLE
Notes:
Most of the information that
SQLDescribeCol
returns can also be found via theSQLGetDescField
function, or via theSQLColAttribute
function. SoSQLDescribeCol
is a redundancy. It continues to be popular because it’s a handy wrapper: the information thatSQLDescribeCol
returns is what’s commonly needed for simple applications.The SQL Standard does not provide for the possibility that people may wish to pass null pointers for unwanted parameters.
Sometimes
SQLDescribeCol
is called in order to provide information for a reporting program. In that case,ColumnName
andNameLength
are used for the report-Column headers,DataType
is used to determine whether right justification is needed,ColumnLength+2
is the maximum Column width,DecimalDigits
helps COBOL programs decide what the totallers’ PICs are andNullable
, if true, may cause creation of a separate “null?” Column.Sometimes
SQLDescribeCol
is called in order to provide information that can be used bySQLBindCol
. In that case,DataType
can be passed directly,ColumnLength
can be passed directly,ColumnLength
can be used to decide how big a buffer must be malloc’d,DecimalDigits
can be passed directly andNullable
can be used to decide whether an indicator is necessary. However, such a scheme cannot be carried out without some advance checking. For example, not all <data type>s can be passed directly – some must beCAST
to a “char” with length equal toColumnLength
(usually).The source field for
Columnsize
will depend on the <data type>. This chart shows what the effects are of the calculation in the “Algorithm” section:<data type>
field
example definition
example Columnsize
CHAR
,VARCHAR
,BLOB
SQL_DESC_OCTET_LENGTH
CHAR(18)
18
DECIMAL
,NUMERIC
SQL_DESC_PRECISION
DECIMAL(5,3)
5
SMALLINT
SQL_DESC_PRECISION
[1]SMALLINT
5
INTEGER
SQL_DESC_PRECISION
[1]INTEGER
10
FLOAT
SQL_DESC_PRECISION
[1]FLOAT(53)
15
REAL
SQL_DESC_PRECISION
[1]REAL
7
DOUBLE PRECISION
SQL_DESC_PRECISION
[1]DOUBLE PRECISION
15
BIT
,BIT VARYING
SQL_DESC_LENGTH
BIT(6)
6
DATE
,TIME
,TIMESTAMP
SQL_DESC_LENGTH
DATE
10
INTERVAL
SQL_DESC_LENGTH
INTERVAL SECOND(1)
4
[2]Note 1: This <data type> usually has a binary-radix precision, so the value in
SQL_DESC_PRECISION
is in bits. When this is the case, the DBMS converts to the number of decimal digits that would be needed to represent the <data type>’s largest literal (not including space for sign, decimal point or exponent).Note 2: The
INTERVAL
example is based on an assumption that the leading field precision is 2; the specified fractional precision is 1, so a typical <literal> would beINTERVAL '-33.5' SECOND
.Old-timers may recognize that the fields retrieved by
SQLDescribeCol
are analogous to the fields of IBM DB2’s SQLDA (SQL descriptor area), used for embedded SQLDESCRIBE
statements in days of yore.
Example:
#include "sqlcli.h"
#include stdlib.h
SQLHSTMT hstmt;
SQLCHAR column_name[128+1];
SQLSMALLINT column_name_length;
SQLSMALLINT data_type;
SQLINTEGER column_size;
SQLSMALLINT decimal_digits;
SQLSMALLINT nullable;
SQLCHAR *lpBuffer;
...
SQLExecDirect("SELECT col_1 FROM Table_1",SQL_NTS);
...
SQLDescribeCol(
hstmt, /* handle of stmt */
1, /* Column number */
column_name, /* where to put Column name */
sizeof(column_name), /* = 128+1 ... allow for \0 */
&column_name_length, /* where to put name length */
&data_type, /* where to put <data type> */
&column_size, /* where to put Column size */
&decimal_digits, /* where to put scale/frac precision */
&nullable); /* where to put null/not-null flag */
/* Allocate a buffer that we will fetch into. */
switch (data_type) {
case SQL_BIT: lpBuffer = malloc(column_size/8+1);
case SQL_REAL: lpBuffer = malloc(column_size+6+1);
case SQL_DOUBLE_PRECISION: lpBuffer = malloc(column_size+7+1);
case SQL_CHAR: lpBuffer = malloc(column_size+1);
case SQL_DECIMAL: lpBuffer = malloc(column_size+2+1);
case SQL_INTEGER: lpBuffer = malloc(column_size+1+1);
...
}
ODBC: The SQLDescribeCol
function has been around since ODBC 1.0. The
differences between the ODBC and Standard’s specifications are only minor.
Unlike Standard-conformant drivers, ODBC drivers can accept 0 for a Column
number, can return a blank string in ColumnName
, can return
SQL_NULLABLE_UNKNOWN
in Nullable
and return SQL_DESC_OCTET_LENGTH
for ColumnLength
of datetime, interval or bit <data type>s.
SQLNumResultCols¶
Function Prototype:
SQLRETURN SQLNumResultCols(
SQLHSTMT hstmt, /* 32-bit input */
SQLSMALLINT *ColumnCount); /* pointer to 16-bit output */
);
Job: Find out how many Columns are in a result set.
Algorithm:
If (there is no prepared statement associated with StatementHandle)
return error: HY010 CLI-specific condition-function sequence error
Set *ColumnCount = IRD.SQL_DESC_COUNT
Notes:
All this function does is retrieve the “count” field in the IRD, which is zero if the last prepared/executed SQL statement was a non-query, or – if the last prepared/executed SQL statement was a query – is the number of Columns in the select list.
SQLNumResultCols(hstmt,&column_count)
is effectively the same as:SQLGetStmtAttr(hstmt,SQL_ATTR_IMP_ROW_DESC,&hdesc,NULL,NULL); SQLGetDescField(hdesc,NULL,SQL_DESC_COUNT,&column_count,NULL,NULL);
Some applications call
SQLNumResultCols
in order to find out whether the last executed statement was a query. If it was something else –INSERT
, for instance – then*ColumnCount
would be 0. This is a reliable test, but SQL3 programmers can usedesc.SQL_DESC_DYNAMIC_FUNCTION_CODE
for a slightly more precise answer.
Example:
#include "sqlcli.h"
SQLSMALLINT column_count;
...
SQLPrepare(hstmt,...);
if (SQLNumResultCols(hstmt,&column_count)<0) {
... invalid handle, statement not prepared, etc. }
if (column_count==0) {
... it wasn't a query expression }
if (column_count>0) {
... now we know how many Columns we must bind }
ODBC: The SQLNumResultCols
function has been around since ODBC 1.0.
SQLGetParamData¶
Function Prototype:
SQLRETURN SQLGetParamData(
SQLHSTMT StatementHandle, /* 32-bit input */
SQLSMALLINT ParameterNumber, /* 16-bit input */
SQLSMALLINT TargetType, /* 16-bit input */
SQLPOINTER TargetValue, /* ANY* output */
SQLINTEGER BufferLength, /* 32-bit input */
SQLINTEGER *StrLen_or_Ind /* 32-bit output */
);
Job: Get the value of an unbound <output parameter. SQLGetParamData
is
rare. You only need it if all these things are true:
You have executed an SQL statement which begins with the word
CALL
.The called procedure has “output parameters” (direction of flow is from DBMS to host-language buffers).
You did not bind the output parameters in advance with
SQLBindParameter
,SQLSetDescRec
orSQLSetDescField
.
Algorithm:
If the last statement for hstmt was not a CALL statement:
return error: HY010 CLI-specific condition-function sequence error
If the parameter referred to by ParameterNumber doesn't exist, or was bound as
"input" parameter, or (implementation-defined restriction) has already been
transferred:
return error: 07009 dynamic SQL error-invalid descriptor index
Transfer data from DBMS internal buffers to variables in the host-language
program. The algorithm is the same as the SQLGetData algorithm; the only
important difference is that the desc is an APD rather than an ARD.
Note:
It is implementation-defined whether the parameters must be accessed in ascending parameter-number order. You can call
SQLGetInfo(...SQL_GETPARAMDATA_EXTENSIONS...)
to find out whether your DBMS supports getting any parameter in any order.The “source” is already described in the IPD; you only need to pass a description of the “target”.
The possible values of TargetType are:
SQL_C_CHARACTER 1 SQL_C_INTEGER 4 SQL_C_SMALLINT 5 SQL_C_REAL 7 SQL_C_DOUBLE 8 SQL_C_DEFAULT 99 (use IPD <data type>, precision, scale) SQL_APD_TYPE -99 (APD specifies <data type> already)
We recommend against using SQL_C_DEFAULT
because “type, precision, scale”
is often insufficient information.
Example:
Scenario: You have a procedure named Withdraw. It takes five parameters. The
first three parameters are input parameters. The fourth parameter is an output
parameter which is pre-bound with SQLBindParameter
. The fifth parameter is an
output parameter which you will pick up with SQLGetParamData
.
SQLCHAR amount[10];
SQLINTEGER teller_id;
SQLINTEGER customer_id;
SQLCHAR message1[101];
SQLCHAR message2[101];
SQLINTEGER message2_indicator;
...
SQLBindParameter(
hstmt,1,SQL_PARAM_MODE_INPUT,SQL_C_CHAR,SQL_DECIMAL,6,2,amount,0,NULL);
SQLBindParameter(
hstmt,2,SQL_PARAM_MODE_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&teller_id,0,NULL);
SQLBindParameter(
hstmt,3,SQL_PARAM_MODE_INPUT,SQL_C_LONG,SQL_INTEGER,0,0,&customer_id,0,NULL);
SQLBindParameter(
hstmt,4,SQL_PARAM_MODE_OUTPUT,SQL_CHAR,SQL_C_CHAR,100,0,message1,0,NULL);
strcpy(amount,"15.33");
teller_id = 44;
customer_id = 90182;
SQLExecDirect(hstmt,"CALL Withdraw (?,?,?,?,?);",24);
SQLGetParamData(hstmt,5,
SQL_C_DEFAULT, /* TargetType */
message2, /* TargetValue */
100, /* BufferLength */
&message2_indicator); /* *StrLen_or_Ind */
A possible result from this code would be: message2
contains the
null-terminated string "abc"
and message2_indicator
contains 3.
ODBC: The SQLGetParamData
function is not part of ODBC 3.0.
And that’s it for the desc
functions. In the next chapter, we’ll take a
look at the diagnostic functions.