Re: Review of "SQLDA support for ECPG"

Robert Haas írta:
> On Thu, Oct 8, 2009 at 6:22 AM, Boszormenyi Zoltan <zb@cybertec.at> 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>