Hi,
here's the documentation patch for the new ECPG features.
- I changed the order of sections "Using Descriptor Areas" and
"Informix compatibility mode"
- split the "Using Descriptor Areas", so it now have two subsections:
"Named SQL Descriptor Areas" and "SQLDA Descriptor Areas".
The second one talks about the native mode SQLDA only.
- Documented DESCRIBE and the USING/INTO quirks.
- Documented the "string" pseudo-type in compat mode
- Modified the section name "Additional embedded SQL statements",
it now reads "Additional/missing embedded SQL statements" and
documented the lack of "FREE cursor_name" statement and
the behaviour of "FREE statement_name" statement.
- Documented the Informix-compatible SQLDA under the
"Informix compatibility mode" section.
Best regards,
Zoltán Böszörményi
--
Bible has answers for everything. Proof:
"But let your communication be, Yea, yea; Nay, nay: for whatsoever is more
than these cometh of evil." (Matthew 5:37) - basics of digital technology.
"May your kingdom come" - superficial description of plate tectonics
----------------------------------
Zoltán Böszörményi
Cybertec Schönig & Schönig GmbH
http://www.postgresql.at/
*** pgsql.orig/doc/src/sgml/ecpg.sgml 2009-12-08 09:23:19.000000000 +0100
--- pgsql.doc/doc/src/sgml/ecpg.sgml 2010-01-15 19:07:01.000000000 +0100
***************
*** 2376,2381 ****
--- 2376,2853 ----
</sect2>
</sect1>
+ <sect1 id="ecpg-descriptors">
+ <title>Using Descriptor Areas</title>
+
+ <para>
+ An SQL descriptor area is a more sophisticated method for processing
+ the result of a <command>SELECT</command>, <command>FETCH</command> or
+ a <command>DESCRIBE</command> statement. An SQL descriptor area groups
+ the data of one row of data together with metadata items into one
+ data structure. The metadata is particularly useful when executing
+ dynamic SQL statements, where the nature of the result columns might
+ not be known ahead of time. PostgreSQL provides two ways to use
+ Descriptor Areas: the named SQL Descriptor Areas and the C-structure
+ SQLDAs.
+ </para>
+
+ <sect2 id="ecpg-named-descriptors">
+ <title>Named SQL Descriptor Areas</title>
+
+ <para>
+ A named SQL descriptor area consists of a header, which contains
+ information concerning the entire descriptor, and one or more item
+ descriptor areas, which basically each describe one column in the
+ result row.
+ </para>
+
+ <para>
+ Before you can use an SQL descriptor area, you need to allocate one:
+ <programlisting>
+ EXEC SQL ALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
+ </programlisting>
+ The identifier serves as the <quote>variable name</quote> of the
+ descriptor area. <remark>The scope of the allocated descriptor is WHAT?.</remark>
+ When you don't need the descriptor anymore, you should deallocate
+ it:
+ <programlisting>
+ EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
+ </programlisting>
+ </para>
+
+ <para>
+ To use a descriptor area, specify it as the storage target in an
+ <literal>INTO</literal> clause, instead of listing host variables:
+ <programlisting>
+ EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;
+ </programlisting>
+ If the resultset is empty, the Descriptor Area will still contain
+ the metadata from the query, i.e. the field names.
+ </para>
+
+ <para>
+ For not yet executed prepared queries, the <command>DESCRIBE</command>
+ statement can be used to get the metadata of the resultset:
+ <programlisting>
+ EXEC SQL BEGIN DECLARE SECTION;
+ char *sql_stmt = "SELECT * FROM table1";
+ EXEC SQL END DECLARE SECTION;
+
+ EXEC SQL PREPARE stmt1 FROM :sql_stmt;
+ EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;
+ </programlisting>
+ </para>
+
+ <para>
+ Before PostgreSQL 8.5, the <literal>SQL</literal> keyword was optional,
+ so using <literal>DESCRIPTOR</literal> and <literal>SQL DESCRIPTOR</literal>
+ produced named SQL Descriptor Areas. Now it is mandatory, omitting
+ the <literal>SQL</literal> keyword produces SQLDA Descriptor Areas,
+ see <xref linkend="ecpg-sqlda-descriptors">.
+ </para>
+
+ <para>
+ In <command>DESCRIBE</command> and <command>FETCH</command> statements,
+ the <literal>INTO</literal> and <literal>USING</literal> keywords can be
+ used to similarly: they produce the resultset and the metadata in a
+ Descriptor Area.
+ </para>
+
+ <para>
+ Now how do you get the data out of the descriptor area? You can
+ think of the descriptor area as a structure with named fields. To
+ retrieve the value of a field from the header and store it into a
+ host variable, use the following command:
+ <programlisting>
+ EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
+ </programlisting>
+ Currently, there is only one header field defined:
+ <replaceable>COUNT</replaceable>, which tells how many item
+ descriptor areas exist (that is, how many columns are contained in
+ the result). The host variable needs to be of an integer type. To
+ get a field from the item descriptor area, use the following
+ command:
+ <programlisting>
+ EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
+ </programlisting>
+ <replaceable>num</replaceable> can be a literal integer or a host
+ variable containing an integer. Possible fields are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>CARDINALITY</literal> (integer)</term>
+ <listitem>
+ <para>
+ number of rows in the result set
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DATA</literal></term>
+ <listitem>
+ <para>
+ actual data item (therefore, the data type of this field
+ depends on the query)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term>
+ <listitem>
+ <para>
+ ?
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term>
+ <listitem>
+ <para>
+ not implemented
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>INDICATOR</literal> (integer)</term>
+ <listitem>
+ <para>
+ the indicator (indicating a null value or a value truncation)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>KEY_MEMBER</literal> (integer)</term>
+ <listitem>
+ <para>
+ not implemented
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>LENGTH</literal> (integer)</term>
+ <listitem>
+ <para>
+ length of the datum in characters
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NAME</literal> (string)</term>
+ <listitem>
+ <para>
+ name of the column
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>NULLABLE</literal> (integer)</term>
+ <listitem>
+ <para>
+ not implemented
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>OCTET_LENGTH</literal> (integer)</term>
+ <listitem>
+ <para>
+ length of the character representation of the datum in bytes
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>PRECISION</literal> (integer)</term>
+ <listitem>
+ <para>
+ precision (for type <type>numeric</type>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RETURNED_LENGTH</literal> (integer)</term>
+ <listitem>
+ <para>
+ length of the datum in characters
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term>
+ <listitem>
+ <para>
+ length of the character representation of the datum in bytes
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>SCALE</literal> (integer)</term>
+ <listitem>
+ <para>
+ scale (for type <type>numeric</type>)
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>TYPE</literal> (integer)</term>
+ <listitem>
+ <para>
+ numeric code of the data type of the column
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ <para>
+ In <command>EXECUTE</command>, <command>DECLARE</command> and <command>OPEN</command>
+ statements, the effect of the <literal>INTO</literal> and <literal>USING</literal>
+ keywords are different. A Descriptor Area can also be manually built to
+ provide the input parameters for a query or a cursor and
+ <literal>USING SQL DESCRIPTOR <replaceable>name</replaceable></literal>
+ is the way to pass the input parameters into a parametrized query. The statement
+ to build a named SQL Descriptor Area is below:
+ <programlisting>
+ EXEC SQL SET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> <replaceable>field</replaceable> = :<replaceable>hostvar</replaceable>;
+ </programlisting>
+ </para>
+
+ <para>
+ PostgreSQL supports retrieving more that one record in one <command>FETCH</command>
+ statement and storing the data in host variables in this case assumes that the
+ variable is an array. E.g.:
+ <programlisting>
+ EXEC SQL BEGIN DECLARE SECTION;
+ int id[5];
+ EXEC SQL END DECLARE SECTION;
+
+ EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;
+
+ EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;
+ </programlisting>
+
+ </para>
+
+ </sect2>
+
+ <sect2 id="ecpg-sqlda-descriptors">
+ <title>SQLDA Descriptor Areas</title>
+
+ <para>
+ An SQLDA Descriptor Area is a C language structure which can be also used
+ to get the resultset and the metadata of a query. One structure stores one
+ record from the resultset.
+ <programlisting>
+ EXEC SQL include sqlda.h;
+ sqlda_t *mysqlda;
+
+ EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;
+ </programlisting>
+ Note that the <literal>SQL</literal> keyword is omitted. The paragraphs about
+ the use cases of the <literal>INTO</literal> and <literal>USING</literal>
+ keywords in <xref linkend="ecpg-named-descriptors"> also apply here with an addition.
+ In a <command>DESCRIBE</command> statement the <literal>DESCRIPTOR</literal>
+ keyword can be completely omitted if the <literal>INTO</literal> keyword is used:
+ <programlisting>
+ EXEC SQL DESCRIBE prepared_statement INTO mysqlda;
+ </programlisting>
+ </para>
+
+ <para>
+ The structure of SQLDA is:
+ <programlisting>
+ #define NAMEDATALEN 64
+
+ struct sqlname
+ {
+ short length;
+ char data[NAMEDATALEN];
+ };
+
+ struct sqlvar_struct
+ {
+ short sqltype;
+ short sqllen;
+ char *sqldata;
+ short *sqlind;
+ struct sqlname sqlname;
+ };
+
+ struct sqlda_struct
+ {
+ char sqldaid[8];
+ long sqldabc;
+ short sqln;
+ short sqld;
+ struct sqlda_struct *desc_next;
+ struct sqlvar_struct sqlvar[1];
+ };
+
+ typedef struct sqlvar_struct sqlvar_t;
+ typedef struct sqlda_struct sqlda_t;
+ </programlisting>
+ </para>
+
+ <para>
+ The allocated data for an SQLDA structure is variable as it depends on the
+ number of fields in a resultset and also depends on the length of the string
+ data values in a record. The individual fields of the <literal>SQLDA</literal>
+ structure are:
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>sqldaid</></term>
+ <listitem>
+ <para>
+ It contains the "<literal>SQLDA </literal>" literal string.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>sqldabc</></term>
+ <listitem>
+ <para>
+ It contains the size of the allocated space in bytes.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>sqln</></term>
+ <listitem>
+ <para>
+ It contains the number of input parameters for a parametrized query
+ case it's passed into <command>OPEN</command>, <command>DECLARE</command> or
+ <command>EXECUTE</command> statements using the <literal>USING</literal>
+ keyword. In case it's used as output of <command>SELECT</command>,
+ <command>EXECUTE</command> or <command>FETCH</command> statements,
+ its value is the same as <literal>sqld</literal>
+ statement
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>sqld</></term>
+ <listitem>
+ <para>
+ It contains the number of fields in a resultset.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>desc_next</></term>
+ <listitem>
+ <para>
+ If the query returns more than one records, multiple linked SQLDA structures
+ are returned, the first record is stored in the SQLDA returned in the
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>sqlvar</></term>
+ <listitem>
+ <para>
+ This is the array of the fields in the resultset. The fields are:
+
+ <variablelist>
+
+ <varlistentry>
+ <term><literal>sqltype</></term>
+ <listitem>
+ <para>
+ It contains the type identifier of the field. For values,
+ see <literal>enum ECPGttype</literal> in <literal>ecpgtype.h</literal>.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqllen</></term>
+ <listitem>
+ <para>
+ It contains the binary length of the field. E.g. 4 bytes for ECPGt_int.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqldata</></term>
+ <listitem>
+ <para>
+ <literal>(char *)sqldata</literal> points to the data.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlind</></term>
+ <listitem>
+ <para>
+ <literal>(char *)sqlind</literal> points to the NULL indicator for data.
+ 0 means NOT NULL, -1 means NULL.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlname</></term>
+ <listitem>
+ <para>
+ <literal>struct sqlname sqlname</literal> contains the name of the field
+ in a structure:
+ <programlisting>
+ struct sqlname
+ {
+ short length;
+ char data[NAMEDATALEN];
+ };
+ </programlisting>
+
+ <variablelist>
+ <varlistentry>
+ <term><literal>length</></term>
+ <listitem>
+ <para>
+ <literal>sqlname.length</literal> contains the length of the field name.
+ </para>
+ </listitem>
+ </varlistentry>
+ <varlistentry>
+ <term><literal>data</></term>
+ <listitem>
+ <para>
+ <literal>sqlname.data</literal> contains the actual field name.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+
+ </variablelist>
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+
+ </sect2>
+
+ </sect1>
+
<sect1 id="ecpg-informix-compat">
<title><productname>Informix</productname> compatibility mode</title>
<para>
***************
*** 2385,2442 ****
the dollar sign instead of the <literal>EXEC SQL</> primitive to introduce
embedded SQL commands.:
<programlisting>
! $int j = 3;
! $CONNECT TO :dbname;
! $CREATE TABLE test(i INT PRIMARY KEY, j INT);
! $INSERT INTO test(i, j) VALUES (7, :j);
! $COMMIT;
</programlisting>
! </para>
! <para>
! There are two compatiblity modes: INFORMIX, INFORMIX_SE
! </para>
! <para>
! When linking programs that use this compatibility mode, remember to link
! against <literal>libcompat</> that is shipped with ecpg.
! </para>
! <para>
! Besides the previously explained syntactic sugar, the <productname>Informix</productname> compatibility
! mode ports some functions for input, output and transformation of data as
! well as embedded SQL statements known from E/SQL to ecpg.
! </para>
! <para>
! <productname>Informix</productname> compatibility mode is closely connected to the pgtypeslib library
! of ecpg. pgtypeslib maps SQL data types to data types within the C host
! program and most of the additional functions of the <productname>Informix</productname> compatibility
! mode allow you to operate on those C host program types. Note however that
! the extent of the compatibility is limited. It does not try to copy <productname>Informix</productname>
! behaviour; it allows you to do more or less the same operations and gives
! you functions that have the same name and the same basic behavior but it is
! no drop-in replacement if you are using <productname>Informix</productname> at the moment. Moreover,
! some of the data types are different. For example,
! <productname>PostgreSQL's</productname> datetime and interval types do not
! know about ranges like for example <literal>YEAR TO MINUTE</> so you won't
! find support in ecpg for that either.
! </para>
- <sect2>
- <title>Additional embedded SQL statements</title>
- <para>
- <variablelist>
<varlistentry>
! <term><literal>CLOSE DATABASE</></term>
<listitem>
<para>
! This statement closes the current connection. In fact, this is a
! synonym for ecpg's <literal>DISCONNECT CURRENT</>.:
<programlisting>
! $CLOSE DATABASE; /* close the current connection */
! EXEC SQL CLOSE DATABASE;
</programlisting>
</para>
</listitem>
</varlistentry>
</variablelist>
</para>
</sect2>
--- 2857,3229 ----
the dollar sign instead of the <literal>EXEC SQL</> primitive to introduce
embedded SQL commands.:
<programlisting>
! $int j = 3;
! $CONNECT TO :dbname;
! $CREATE TABLE test(i INT PRIMARY KEY, j INT);
! $INSERT INTO test(i, j) VALUES (7, :j);
! $COMMIT;
! </programlisting>
! </para>
! <para>
! There are two compatiblity modes: INFORMIX, INFORMIX_SE
! </para>
! <para>
! When linking programs that use this compatibility mode, remember to link
! against <literal>libcompat</> that is shipped with ecpg.
! </para>
! <para>
! Besides the previously explained syntactic sugar, the <productname>Informix</productname> compatibility
! mode ports some functions for input, output and transformation of data as
! well as embedded SQL statements known from E/SQL to ecpg.
! </para>
! <para>
! <productname>Informix</productname> compatibility mode is closely connected to the pgtypeslib library
! of ecpg. pgtypeslib maps SQL data types to data types within the C host
! program and most of the additional functions of the <productname>Informix</productname> compatibility
! mode allow you to operate on those C host program types. Note however that
! the extent of the compatibility is limited. It does not try to copy <productname>Informix</productname>
! behaviour; it allows you to do more or less the same operations and gives
! you functions that have the same name and the same basic behavior but it is
! no drop-in replacement if you are using <productname>Informix</productname> at the moment. Moreover,
! some of the data types are different. For example,
! <productname>PostgreSQL's</productname> datetime and interval types do not
! know about ranges like for example <literal>YEAR TO MINUTE</> so you won't
! find support in ecpg for that either.
! </para>
!
! <sect2>
! <title>Additional types</title>
! <para>
! The Informix-special "string" pseudo-type for storing right-trimmed character string data is now
! supported in Informix-mode without using <literal>typedef</literal>. In fact, in Informix-mode,
! ECPG refuses to process source files that contain <literal>typedef sometype string;</literal>
! <programlisting>
! EXEC SQL BEGIN DECLARE SECTION;
! string userid; /* this variable will contain trimmed data */
! EXEC SQL END DECLARE SECTION;
!
! EXEC SQL FETCH MYCUR INTO :userid;
! </programlisting>
! </para>
! </sect2>
!
! <sect2>
! <title>Additional/missing embedded SQL statements</title>
! <para>
! <variablelist>
! <varlistentry>
! <term><literal>CLOSE DATABASE</></term>
! <listitem>
! <para>
! This statement closes the current connection. In fact, this is a
! synonym for ecpg's <literal>DISCONNECT CURRENT</>.:
! <programlisting>
! $CLOSE DATABASE; /* close the current connection */
! EXEC SQL CLOSE DATABASE;
! </programlisting>
! </para>
! </listitem>
! </varlistentry>
! <varlistentry>
! <term><literal>FREE cursor_name</></term>
! <listitem>
! <para>
! Due to the differences how ECPG works compared to Informix's ESQL/C (i.e. which steps
! are purely grammar transformations and which steps rely on the underlying runtime library)
! there is no <literal>FREE cursor_name</> statement in ECPG. This is because in ECPG,
! <literal>DECLARE CURSOR</literal> doesn't translate to a function call into
! the runtime library that uses to the cursor name. This means that there's no runtime
! bookkeeping of SQL cursors in the ECPG runtime library, only in the PostgreSQL server.
! </para>
! </listitem>
! </varlistentry>
! <varlistentry>
! <term><literal>FREE statement_name</></term>
! <listitem>
! <para>
! <literal>FREE statement_name</> is a synonym for <literal>DEALLOCATE PREPARE statement_name</>.
! </para>
! </listitem>
! </varlistentry>
! </variablelist>
! </para>
! </sect2>
!
! <sect2>
! <title>Informix-compatible SQLDA Descriptor Areas</title>
! <para>
! Informix-compatible mode supports a different structure than the one described in
! <xref linkend="ecpg-sqlda-descriptors">. See below:
! <programlisting>
! struct sqlvar_compat
! {
! short sqltype;
! int sqllen;
! char *sqldata;
! short *sqlind;
! char *sqlname;
! char *sqlformat;
! short sqlitype;
! short sqlilen;
! char *sqlidata;
! int sqlxid;
! char *sqltypename;
! short sqltypelen;
! short sqlownerlen;
! short sqlsourcetype;
! char *sqlownername;
! int sqlsourceid;
!
! char *sqlilongdata;
! int sqlflags;
! void *sqlreserved;
! };
!
! struct sqlda_compat
! {
! short sqld;
! struct sqlvar_compat *sqlvar;
! char desc_name[19];
! short desc_occ;
! struct sqlda_compat *desc_next;
! void *reserved;
! };
!
! typedef struct sqlvar_compat sqlvar_t;
! typedef struct sqlda_compat sqlda_t;
! </programlisting>
! </para>
!
! <para>
! The global properties are:
! <variablelist>
!
! <varlistentry>
! <term><literal>sqld</></term>
! <listitem>
! <para>
! The number of fields in the <literal>SQLDA</> descriptor.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>sqlvar</></term>
! <listitem>
! <para>
! Pointer to the per-field properties.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>desc_name</></term>
! <listitem>
! <para>
! Unused, filled with zerobytes.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>desc_occ</></term>
! <listitem>
! <para>
! Size of the allocated structure.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>desc_next</></term>
! <listitem>
! <para>
! Pointer to the next SQLDA structure if the resultset contains more than one records.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>reserved</></term>
! <listitem>
! <para>
! Unused pointer, contains NULL. Kept for Informix-compatibility.
! </para>
! </listitem>
! </varlistentry>
!
! </variablelist>
!
! The per-field properties are below, they are stored in the <literal>sqlvar</literal> array:
!
! <variablelist>
!
! <varlistentry>
! <term><literal>sqltype</></term>
! <listitem>
! <para>
! Type of the field. Constants are in <literal>sqltypes.h</literal>
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>sqllen</></term>
! <listitem>
! <para>
! Length of the field data.
! </para>
! </listitem>
! </varlistentry>
!
! <varlistentry>
! <term><literal>sqldata</></term>
! <listitem>
! <para>
! Pointer to the field data. The pointer is of <literal>char *</literal> type,
! the data pointed by it is in a binary format. Example:
! <programlisting>
! int intval;
!
! switch (sqldata->sqlvar[i].sqltype)
! {
! case SQLINTEGER:
! intval = *(int *)sqldata->sqlvar[i].sqldata;
! break;
! ...
! }
</programlisting>
! </para>
! </listitem>
! </varlistentry>
<varlistentry>
! <term><literal>sqlind</></term>
<listitem>
<para>
! Pointer to the NULL indicator. If returned by DESCRIBE or FETCH then it's always a valid pointer.
! If used as input for <literal>EXECUTE ... USING sqlda;</literal> then NULL-pointer value means
! that the value for this field is non-NULL. Otherwise a valid pointer and <literal>sqlitype</literal>
! has to be properly set. Example:
<programlisting>
! if (*(int2 *)sqldata->sqlvar[i].sqlind != 0)
! printf("value is NULL\n");
</programlisting>
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlname</></term>
+ <listitem>
+ <para>
+ Name of the field. 0-terminated string.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlformat</></term>
+ <listitem>
+ <para>
+ Reserved in Informix, value of PQfformat() for the field.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlitype</></term>
+ <listitem>
+ <para>
+ Type of the NULL indicator data. It's always SQLSMINT when returning data from the server.
+ When the <literal>SQLDA</literal> is used for a parametrized query, the data is treated
+ according to the set type.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlilen</></term>
+ <listitem>
+ <para>
+ Length of the NULL indicator data.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlxid</></term>
+ <listitem>
+ <para>
+ Extended type of the field, result of PQftype().
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqltypename</></term>
+ <term><literal>sqltypelen</></term>
+ <term><literal>sqlownerlen</></term>
+ <term><literal>sqlsourcetype</></term>
+ <term><literal>sqlownername</></term>
+ <term><literal>sqlsourceid</></term>
+ <term><literal>sqlflags</></term>
+ <term><literal>sqlreserved</></term>
+ <listitem>
+ <para>
+ Unused.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term><literal>sqlilongdata</></term>
+ <listitem>
+ <para>
+ It equals to <literal>sqldata</literal> if <literal>sqllen</literal> is larger than 32KB.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
+
+ Example:
+ <programlisting>
+ EXEC SQL INCLUDE sqlda.h;
+
+ sqlda_t *sqlda; /* This doesn't need to be under embedded DECLARE SECTION */
+
+ EXEC SQL BEGIN DECLARE SECTION;
+ char *prep_stmt = "select * from table1";
+ int i;
+ EXEC SQL END DECLARE SECTION;
+
+ ...
+
+ EXEC SQL PREPARE mystmt FROM :prep_stmt;
+
+ EXEC SQL DESCRIBE mystmt INTO sqlda;
+
+ printf("# of fields: %d\n", sqlda->sqld);
+ for (i = 0; i < sqlda->sqld; i++)
+ printf("field %d: \"%s\"\n", sqlda->sqlvar[i]->sqlname);
+
+ EXEC SQL DECLARE mycursor CURSOR FOR mystmt;
+ EXEC SQL OPEN mycursor;
+ EXEC SQL WHENEVER NOT FOUND GOTO out;
+
+ while (1)
+ {
+ EXEC SQL FETCH mycursor USING sqlda;
+ }
+
+ EXEC SQL CLOSE mycursor;
+
+ free(sqlda); /* The main structure is all to be free(),
+ * sqlda and sqlda->sqlvar is in one allocated area */
+ </programlisting>
+ For more information, see the <literal>sqlda.h</> header and the
+ <literal>src/interfaces/ecpg/test/compat_informix/sqlda.pgc</literal> regression test.
</para>
</sect2>
***************
*** 3660,3867 ****
</sect2>
</sect1>
- <sect1 id="ecpg-descriptors">
- <title>Using SQL Descriptor Areas</title>
-
- <para>
- An SQL descriptor area is a more sophisticated method for
- processing the result of a <command>SELECT</command> or
- <command>FETCH</command> statement. An SQL descriptor area groups
- the data of one row of data together with metadata items into one
- data structure. The metadata is particularly useful when executing
- dynamic SQL statements, where the nature of the result columns might
- not be known ahead of time.
- </para>
-
- <para>
- An SQL descriptor area consists of a header, which contains
- information concerning the entire descriptor, and one or more item
- descriptor areas, which basically each describe one column in the
- result row.
- </para>
-
- <para>
- Before you can use an SQL descriptor area, you need to allocate one:
- <programlisting>
- EXEC SQL ALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
- </programlisting>
- The identifier serves as the <quote>variable name</quote> of the
- descriptor area. <remark>The scope of the allocated descriptor is WHAT?.</remark>
- When you don't need the descriptor anymore, you should deallocate
- it:
- <programlisting>
- EXEC SQL DEALLOCATE DESCRIPTOR <replaceable>identifier</replaceable>;
- </programlisting>
- </para>
-
- <para>
- To use a descriptor area, specify it as the storage target in an
- <literal>INTO</literal> clause, instead of listing host variables:
- <programlisting>
- EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
- </programlisting>
- </para>
-
- <para>
- Now how do you get the data out of the descriptor area? You can
- think of the descriptor area as a structure with named fields. To
- retrieve the value of a field from the header and store it into a
- host variable, use the following command:
- <programlisting>
- EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
- </programlisting>
- Currently, there is only one header field defined:
- <replaceable>COUNT</replaceable>, which tells how many item
- descriptor areas exist (that is, how many columns are contained in
- the result). The host variable needs to be of an integer type. To
- get a field from the item descriptor area, use the following
- command:
- <programlisting>
- EXEC SQL GET DESCRIPTOR <replaceable>name</replaceable> VALUE <replaceable>num</replaceable> :<replaceable>hostvar</replaceable> = <replaceable>field</replaceable>;
- </programlisting>
- <replaceable>num</replaceable> can be a literal integer or a host
- variable containing an integer. Possible fields are:
-
- <variablelist>
- <varlistentry>
- <term><literal>CARDINALITY</literal> (integer)</term>
- <listitem>
- <para>
- number of rows in the result set
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>DATA</literal></term>
- <listitem>
- <para>
- actual data item (therefore, the data type of this field
- depends on the query)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>DATETIME_INTERVAL_CODE</literal> (integer)</term>
- <listitem>
- <para>
- ?
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>DATETIME_INTERVAL_PRECISION</literal> (integer)</term>
- <listitem>
- <para>
- not implemented
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>INDICATOR</literal> (integer)</term>
- <listitem>
- <para>
- the indicator (indicating a null value or a value truncation)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>KEY_MEMBER</literal> (integer)</term>
- <listitem>
- <para>
- not implemented
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>LENGTH</literal> (integer)</term>
- <listitem>
- <para>
- length of the datum in characters
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>NAME</literal> (string)</term>
- <listitem>
- <para>
- name of the column
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>NULLABLE</literal> (integer)</term>
- <listitem>
- <para>
- not implemented
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>OCTET_LENGTH</literal> (integer)</term>
- <listitem>
- <para>
- length of the character representation of the datum in bytes
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>PRECISION</literal> (integer)</term>
- <listitem>
- <para>
- precision (for type <type>numeric</type>)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>RETURNED_LENGTH</literal> (integer)</term>
- <listitem>
- <para>
- length of the datum in characters
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>RETURNED_OCTET_LENGTH</literal> (integer)</term>
- <listitem>
- <para>
- length of the character representation of the datum in bytes
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>SCALE</literal> (integer)</term>
- <listitem>
- <para>
- scale (for type <type>numeric</type>)
- </para>
- </listitem>
- </varlistentry>
-
- <varlistentry>
- <term><literal>TYPE</literal> (integer)</term>
- <listitem>
- <para>
- numeric code of the data type of the column
- </para>
- </listitem>
- </varlistentry>
- </variablelist>
- </para>
- </sect1>
-
<sect1 id="ecpg-errors">
<title>Error Handling</title>
--- 4447,4452 ----
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
