Robert Haas írta:
> On Thu, Oct 8, 2009 at 6:22 AM, Boszormenyi Zoltan <[email protected]> wrote:
>
>> Noah Misch írta:
>>
>>> I will report on the sqlda patch in more detail on
>>> 2009-10-10.
>>>
>> I am attaching a new one, please review this. Changes:
>> - set sqllen, sqlind, sqlilen and sqlitype per-field properties
>> - SQLINT8 and SQLSERIAL8 are ECPGt_long on 64-bit, not ECPGt_long_long
>>
>>
>>> The one concern that's clear now is a lack of documentation update.
>>>
>>>
>> That will come in a separate patch.
>>
>
> What's the point of that? It can't be applied without documentation,
> and it just makes life more complicated to have two separate patch
> files floating around.
>
> ...Robert
>
It's easier to write the documentation for all changes at once.
I would have the same situation that happened with the code,
the patches with the documentation added would strictly depend
on each other again. Also, Michael Meskes applied the "string"
pseudo-type patch without the documentation, despite the patch
had it, maybe at an improper place. With a tongue-in-cheek
"no comment" ;-) I point to this paragraph in the ECPG part of
the documentation:
"This documentation is quite incomplete. But since this interface is
standardized,
additional information can be found in many resources about SQL."
The doc patch is attached. Details:
- documented "string" pseudo-type
- documented two more statements under section
"Additional embedded SQL statements" which is now called
"Additional/missing embedded SQL statements"
This is about the current state of ECPG, no code changed.
- document that INTO and USING are interchangeable if
the target is a Descriptor Areas
- document that the SQL keyword is not optional for DAs
- document SQLDA
- document DESCRIBE in section "Using SQL Descriptor Areas"
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.doc/doc/src/sgml/ecpg.sgml.old 2009-10-07 12:05:28.000000000 +0200
--- pgsql.doc/doc/src/sgml/ecpg.sgml 2009-10-08 12:55:15.785009096 +0200
***************
*** 2420,2428 ****
</para>
<sect2>
! <title>Additional embedded SQL statements</title>
<para>
<variablelist>
<varlistentry>
<term><literal>CLOSE DATABASE</></term>
<listitem>
--- 2420,2445 ----
</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>
***************
*** 2436,2446 ****
--- 2453,2755 ----
</para>
</listitem>
</varlistentry>
+
+ <varlistentry>
+ <term><literal>FREE cursor_name</></term>
+ <listitem>
+ <para>
+ Due to the differences how ECPG works (i.e. which steps are purely grammar transformations
+ and which steps rely on the underlying runtime library compared to Informix's ESQL/C)
+ there is no <literal>FREE cursor_name</> statement in ECPG. This is because in ECPG,
+ <literal>DECLARE CURSOR</literal> previously didn't translate to a function call into
+ the runtime library at all. Now it does, just to simply reset SQLCA to follow Informix
+ behaviour. But it's still true 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>Additional syntax forms</title>
+ <para>
+ <variablelist>
+
+ <varlistentry>
+ <term>Interchangeable <literal>USING</> and <literal>INTO</></term>
+ <listitem>
+ <para>
+ In <literal>DECRIBE</> and <literal>FETCH</> statements, the <literal>USING</>
+ and <literal>INTO</> keywords are interchangeable if the target is a Descriptor Area.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry>
+ <term>Non-optional <literal>SQL</> keyword for Descriptor Areas</term>
+ <listitem>
+ <para>
+ In <literal>DECRIBE</> and <literal>FETCH</> statements using Descriptor Areas,
+ the <literal>SQL</> keyword is not optional. <literal>DESCRIPTOR</> means an
+ SQLDA Descriptor Area, <literal>SQL DESCRIPTOR</> means a named SQL Descriptor Area.
+ </para>
+ </listitem>
+ </varlistentry>
+
</variablelist>
</para>
</sect2>
<sect2>
+ <title>Additional SQL descriptor type: SQLDA C-structure</title>
+ <para>
+ Besides the named SQL descriptors, ECPG now supports Informix-special SQLDA descriptors
+ for accessing fields properties and data from low-level C code. 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 structure, unused, contains NULL.
+ </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;
+
+ pg_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>
+
+ <sect2>
<title>Additional functions</title>
<para>
<variablelist>
***************
*** 3696,3705 ****
<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>
--- 4005,4028 ----
<para>
To use a descriptor area, specify it as the storage target in an
! <literal>INTO</literal> clause in a <literal>DESCRIBE [OUTPUT]</literal> statement:
! <programlisting>
! EXEC SQL BEGIN DECLARE SECTION;
! char *stmt = "SELECT * FROM table1";
! EXEC SQL END DECLARE SECTION;
!
! EXEC SQL PREPARE stmt1 FROM :stmt;
! EXEC SQL DESCRIBE stmt1 INTO DESCRIPTOR mydesc;
! EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; /* the SQL keyword is optional */
! </programlisting>
! or a <literal>FETCH</literal> statement, instead of listing host variables:
<programlisting>
EXEC SQL FETCH NEXT FROM mycursor INTO DESCRIPTOR mydesc;
+ EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc /* the SQL keyword is optional */;
</programlisting>
+ The difference between <literal>DESCRIBE</literal> and <literal>FETCH</literal>
+ statements above is that <literal>DESCRIBE</literal> on an empty resultset
+ gives the field metadata, e.g. its name, while <literal>FETCH</literal> doesn't.
</para>
<para>
--
Sent via pgsql-hackers mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
