Team Ai
Datasetpublic

codekingpro/portable-devtools

sourceHugging Faceupdated 5mo agoView on Hugging Face
1likes15kdownloads
ecpg-descriptors.html710 linesDownload Raw Back to html
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>36.7. Using Descriptor Areas</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="ecpg-pgtypes.html" title="36.6. pgtypes Library" /><link rel="next" href="ecpg-errors.html" title="36.8. Error Handling" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">36.7. Using Descriptor Areas</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><th width="60%" align="center">Chapter 36. <span class="application">ECPG</span> — Embedded <acronym class="acronym">SQL</acronym> in C</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="ecpg-errors.html" title="36.8. Error Handling">Next</a></td></tr></table><hr /></div><div class="sect1" id="ECPG-DESCRIPTORS"><div class="titlepage"><div><div><h2 class="title" style="clear: both">36.7. Using Descriptor Areas <a href="#ECPG-DESCRIPTORS" class="id_link">#</a></h2></div></div></div><div class="toc"><dl class="toc"><dt><span class="sect2"><a href="ecpg-descriptors.html#ECPG-NAMED-DESCRIPTORS">36.7.1. Named SQL Descriptor Areas</a></span></dt><dt><span class="sect2"><a href="ecpg-descriptors.html#ECPG-SQLDA-DESCRIPTORS">36.7.2. SQLDA Descriptor Areas</a></span></dt></dl></div><p>3   An SQL descriptor area is a more sophisticated method for processing4   the result of a <code class="command">SELECT</code>, <code class="command">FETCH</code> or5   a <code class="command">DESCRIBE</code> statement. An SQL descriptor area groups6   the data of one row of data together with metadata items into one7   data structure.  The metadata is particularly useful when executing8   dynamic SQL statements, where the nature of the result columns might9   not be known ahead of time. PostgreSQL provides two ways to use10   Descriptor Areas: the named SQL Descriptor Areas and the C-structure11   SQLDAs.12  </p><div class="sect2" id="ECPG-NAMED-DESCRIPTORS"><div class="titlepage"><div><div><h3 class="title">36.7.1. Named SQL Descriptor Areas <a href="#ECPG-NAMED-DESCRIPTORS" class="id_link">#</a></h3></div></div></div><p>13    A named SQL descriptor area consists of a header, which contains14    information concerning the entire descriptor, and one or more item15    descriptor areas, which basically each describe one column in the16    result row.17   </p><p>18    Before you can use an SQL descriptor area, you need to allocate one:19</p><pre class="programlisting">20EXEC SQL ALLOCATE DESCRIPTOR <em class="replaceable"><code>identifier</code></em>;21</pre><p>22    The identifier serves as the <span class="quote">“<span class="quote">variable name</span>”</span> of the23    descriptor area.  24    When you don't need the descriptor anymore, you should deallocate25    it:26</p><pre class="programlisting">27EXEC SQL DEALLOCATE DESCRIPTOR <em class="replaceable"><code>identifier</code></em>;28</pre><p>29   </p><p>30    To use a descriptor area, specify it as the storage target in an31    <code class="literal">INTO</code> clause, instead of listing host variables:32</p><pre class="programlisting">33EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc;34</pre><p>35    If the result set is empty, the Descriptor Area will still contain36    the metadata from the query, i.e., the field names.37   </p><p>38    For not yet executed prepared queries, the <code class="command">DESCRIBE</code>39    statement can be used to get the metadata of the result set:40</p><pre class="programlisting">41EXEC SQL BEGIN DECLARE SECTION;42char *sql_stmt = "SELECT * FROM table1";43EXEC SQL END DECLARE SECTION;44 45EXEC SQL PREPARE stmt1 FROM :sql_stmt;46EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc;47</pre><p>48   </p><p>49    Before PostgreSQL 9.0, the <code class="literal">SQL</code> keyword was optional,50    so using <code class="literal">DESCRIPTOR</code> and <code class="literal">SQL DESCRIPTOR</code>51    produced named SQL Descriptor Areas. Now it is mandatory, omitting52    the <code class="literal">SQL</code> keyword produces SQLDA Descriptor Areas,53    see <a class="xref" href="ecpg-descriptors.html#ECPG-SQLDA-DESCRIPTORS" title="36.7.2. SQLDA Descriptor Areas">Section 36.7.2</a>.54   </p><p>55    In <code class="command">DESCRIBE</code> and <code class="command">FETCH</code> statements,56    the <code class="literal">INTO</code> and <code class="literal">USING</code> keywords can be57    used to similarly: they produce the result set and the metadata in a58    Descriptor Area.59   </p><p>60    Now how do you get the data out of the descriptor area?  You can61    think of the descriptor area as a structure with named fields.  To62    retrieve the value of a field from the header and store it into a63    host variable, use the following command:64</p><pre class="programlisting">65EXEC SQL GET DESCRIPTOR <em class="replaceable"><code>name</code></em> :<em class="replaceable"><code>hostvar</code></em> = <em class="replaceable"><code>field</code></em>;66</pre><p>67    Currently, there is only one header field defined:68    <em class="replaceable"><code>COUNT</code></em>, which tells how many item69    descriptor areas exist (that is, how many columns are contained in70    the result).  The host variable needs to be of an integer type.  To71    get a field from the item descriptor area, use the following72    command:73</p><pre class="programlisting">74EXEC SQL GET DESCRIPTOR <em class="replaceable"><code>name</code></em> VALUE <em class="replaceable"><code>num</code></em> :<em class="replaceable"><code>hostvar</code></em> = <em class="replaceable"><code>field</code></em>;75</pre><p>76    <em class="replaceable"><code>num</code></em> can be a literal integer or a host77    variable containing an integer. Possible fields are:78 79    </p><div class="variablelist"><dl class="variablelist"><dt id="ECPG-NAMED-DESCRIPTORS-CARDINALITY"><span class="term"><code class="literal">CARDINALITY</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-CARDINALITY" class="id_link">#</a></dt><dd><p>80        number of rows in the result set81       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-DATA"><span class="term"><code class="literal">DATA</code></span> <a href="#ECPG-NAMED-DESCRIPTORS-DATA" class="id_link">#</a></dt><dd><p>82        actual data item (therefore, the data type of this field83        depends on the query)84       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-DATETIME-INTERVAL-CODE"><span class="term"><code class="literal">DATETIME_INTERVAL_CODE</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-DATETIME-INTERVAL-CODE" class="id_link">#</a></dt><dd><p>85        When <code class="literal">TYPE</code> is <code class="literal">9</code>,86        <code class="literal">DATETIME_INTERVAL_CODE</code> will have a value of87        <code class="literal">1</code> for <code class="literal">DATE</code>,88        <code class="literal">2</code> for <code class="literal">TIME</code>,89        <code class="literal">3</code> for <code class="literal">TIMESTAMP</code>,90        <code class="literal">4</code> for <code class="literal">TIME WITH TIME ZONE</code>, or91        <code class="literal">5</code> for <code class="literal">TIMESTAMP WITH TIME ZONE</code>.92       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-DATETIME-INTERVAL-PRECISION"><span class="term"><code class="literal">DATETIME_INTERVAL_PRECISION</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-DATETIME-INTERVAL-PRECISION" class="id_link">#</a></dt><dd><p>93        not implemented94       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-INDICATOR"><span class="term"><code class="literal">INDICATOR</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-INDICATOR" class="id_link">#</a></dt><dd><p>95        the indicator (indicating a null value or a value truncation)96       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-KEY-MEMBER"><span class="term"><code class="literal">KEY_MEMBER</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-KEY-MEMBER" class="id_link">#</a></dt><dd><p>97        not implemented98       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-LENGTH"><span class="term"><code class="literal">LENGTH</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-LENGTH" class="id_link">#</a></dt><dd><p>99        length of the datum in characters100       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-NAME"><span class="term"><code class="literal">NAME</code> (string)</span> <a href="#ECPG-NAMED-DESCRIPTORS-NAME" class="id_link">#</a></dt><dd><p>101        name of the column102       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-NULLABLE"><span class="term"><code class="literal">NULLABLE</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-NULLABLE" class="id_link">#</a></dt><dd><p>103        not implemented104       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-OCTET-LENGTH"><span class="term"><code class="literal">OCTET_LENGTH</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-OCTET-LENGTH" class="id_link">#</a></dt><dd><p>105        length of the character representation of the datum in bytes106       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-PRECISION"><span class="term"><code class="literal">PRECISION</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-PRECISION" class="id_link">#</a></dt><dd><p>107        precision (for type <code class="type">numeric</code>)108       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-RETURNED-LENGTH"><span class="term"><code class="literal">RETURNED_LENGTH</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-RETURNED-LENGTH" class="id_link">#</a></dt><dd><p>109        length of the datum in characters110       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-RETURNED-OCTET-LENGTH"><span class="term"><code class="literal">RETURNED_OCTET_LENGTH</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-RETURNED-OCTET-LENGTH" class="id_link">#</a></dt><dd><p>111        length of the character representation of the datum in bytes112       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-SCALE"><span class="term"><code class="literal">SCALE</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-SCALE" class="id_link">#</a></dt><dd><p>113        scale (for type <code class="type">numeric</code>)114       </p></dd><dt id="ECPG-NAMED-DESCRIPTORS-TYPE"><span class="term"><code class="literal">TYPE</code> (integer)</span> <a href="#ECPG-NAMED-DESCRIPTORS-TYPE" class="id_link">#</a></dt><dd><p>115        numeric code of the data type of the column116       </p></dd></dl></div><p>117   </p><p>118    In <code class="command">EXECUTE</code>, <code class="command">DECLARE</code> and <code class="command">OPEN</code>119    statements, the effect of the <code class="literal">INTO</code> and <code class="literal">USING</code>120    keywords are different. A Descriptor Area can also be manually built to121    provide the input parameters for a query or a cursor and122    <code class="literal">USING SQL DESCRIPTOR <em class="replaceable"><code>name</code></em></code>123    is the way to pass the input parameters into a parameterized query. The statement124    to build a named SQL Descriptor Area is below:125</p><pre class="programlisting">126EXEC SQL SET DESCRIPTOR <em class="replaceable"><code>name</code></em> VALUE <em class="replaceable"><code>num</code></em> <em class="replaceable"><code>field</code></em> = :<em class="replaceable"><code>hostvar</code></em>;127</pre><p>128   </p><p>129    PostgreSQL supports retrieving more that one record in one <code class="command">FETCH</code>130    statement and storing the data in host variables in this case assumes that the131    variable is an array. E.g.:132</p><pre class="programlisting">133EXEC SQL BEGIN DECLARE SECTION;134int id[5];135EXEC SQL END DECLARE SECTION;136 137EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc;138 139EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA;140</pre><p>141 142   </p></div><div class="sect2" id="ECPG-SQLDA-DESCRIPTORS"><div class="titlepage"><div><div><h3 class="title">36.7.2. SQLDA Descriptor Areas <a href="#ECPG-SQLDA-DESCRIPTORS" class="id_link">#</a></h3></div></div></div><p>143    An SQLDA Descriptor Area is a C language structure which can be also used144    to get the result set and the metadata of a query. One structure stores one145    record from the result set.146</p><pre class="programlisting">147EXEC SQL include sqlda.h;148sqlda_t         *mysqlda;149 150EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda;151</pre><p>152    Note that the <code class="literal">SQL</code> keyword is omitted. The paragraphs about153    the use cases of the <code class="literal">INTO</code> and <code class="literal">USING</code>154    keywords in <a class="xref" href="ecpg-descriptors.html#ECPG-NAMED-DESCRIPTORS" title="36.7.1. Named SQL Descriptor Areas">Section 36.7.1</a> also apply here with an addition.155    In a <code class="command">DESCRIBE</code> statement the <code class="literal">DESCRIPTOR</code>156    keyword can be completely omitted if the <code class="literal">INTO</code> keyword is used:157</p><pre class="programlisting">158EXEC SQL DESCRIBE prepared_statement INTO mysqlda;159</pre><p>160   </p><div class="procedure"><p>161      The general flow of a program that uses SQLDA is:162     </p><ol class="procedure" type="1"><li class="step"><p>Prepare a query, and declare a cursor for it.</p></li><li class="step"><p>Declare an SQLDA for the result rows.</p></li><li class="step"><p>Declare an SQLDA for the input parameters, and initialize them (memory allocation, parameter settings).</p></li><li class="step"><p>Open a cursor with the input SQLDA.</p></li><li class="step"><p>Fetch rows from the cursor, and store them into an output SQLDA.</p></li><li class="step"><p>Read values from the output SQLDA into the host variables (with conversion if necessary).</p></li><li class="step"><p>Close the cursor.</p></li><li class="step"><p>Free the memory area allocated for the input SQLDA.</p></li></ol></div><div class="sect3" id="ECPG-SQLDA-DESCRIPTORS-SQLDA"><div class="titlepage"><div><div><h4 class="title">36.7.2.1. SQLDA Data Structure <a href="#ECPG-SQLDA-DESCRIPTORS-SQLDA" class="id_link">#</a></h4></div></div></div><p>163     SQLDA uses three data structure164     types: <code class="type">sqlda_t</code>, <code class="type">sqlvar_t</code>,165     and <code class="type">struct sqlname</code>.166    </p><div class="tip"><h3 class="title">Tip</h3><p>167      PostgreSQL's SQLDA has a similar data structure to the one in168      IBM DB2 Universal Database, so some technical information on169      DB2's SQLDA could help understanding PostgreSQL's one better.170     </p></div><div class="sect4" id="ECPG-SQLDA-SQLDA"><div class="titlepage"><div><div><h5 class="title">36.7.2.1.1. sqlda_t Structure <a href="#ECPG-SQLDA-SQLDA" class="id_link">#</a></h5></div></div></div><p>171      The structure type <code class="type">sqlda_t</code> is the type of the172      actual SQLDA.  It holds one record.  And two or173      more <code class="type">sqlda_t</code> structures can be connected in a174      linked list with the pointer in175      the <code class="structfield">desc_next</code> field, thus176      representing an ordered collection of rows.  So, when two or177      more rows are fetched, the application can read them by178      following the <code class="structfield">desc_next</code> pointer in179      each <code class="type">sqlda_t</code> node.180     </p><p>181      The definition of <code class="type">sqlda_t</code> is:182</p><pre class="programlisting">183struct sqlda_struct184{185    char            sqldaid[8];186    long            sqldabc;187    short           sqln;188    short           sqld;189    struct sqlda_struct *desc_next;190    struct sqlvar_struct sqlvar[1];191};192 193typedef struct sqlda_struct sqlda_t;194</pre><p>195 196      The meaning of the fields is:197 198    </p><div class="variablelist"><dl class="variablelist"><dt id="ECPG-SQLDA-SQLDA-SQLDAID"><span class="term"><code class="literal">sqldaid</code></span> <a href="#ECPG-SQLDA-SQLDA-SQLDAID" class="id_link">#</a></dt><dd><p>199        It contains the literal string <code class="literal">"SQLDA  "</code>.200       </p></dd><dt id="ECPG-SQLDA-SQLDA-SQLDABC"><span class="term"><code class="literal">sqldabc</code></span> <a href="#ECPG-SQLDA-SQLDA-SQLDABC" class="id_link">#</a></dt><dd><p>201        It contains the size of the allocated space in bytes.202       </p></dd><dt id="ECPG-SQLDA-SQLDA-SQLN"><span class="term"><code class="literal">sqln</code></span> <a href="#ECPG-SQLDA-SQLDA-SQLN" class="id_link">#</a></dt><dd><p>203        It contains the number of input parameters for a parameterized query in204        case it's passed into <code class="command">OPEN</code>, <code class="command">DECLARE</code> or205        <code class="command">EXECUTE</code> statements using the <code class="literal">USING</code>206        keyword. In case it's used as output of <code class="command">SELECT</code>,207        <code class="command">EXECUTE</code> or <code class="command">FETCH</code> statements,208        its value is the same as <code class="literal">sqld</code>209        statement210       </p></dd><dt id="ECPG-SQLDA-SQLDA-SQLD"><span class="term"><code class="literal">sqld</code></span> <a href="#ECPG-SQLDA-SQLDA-SQLD" class="id_link">#</a></dt><dd><p>211        It contains the number of fields in a result set.212       </p></dd><dt id="ECPG-SQLDA-SQLDA-DESC-NEXT"><span class="term"><code class="literal">desc_next</code></span> <a href="#ECPG-SQLDA-SQLDA-DESC-NEXT" class="id_link">#</a></dt><dd><p>213        If the query returns more than one record, multiple linked214        SQLDA structures are returned, and <code class="literal">desc_next</code> holds215        a pointer to the next entry in the list.216       </p></dd><dt id="ECPG-SQLDA-SQLDA-SQLVAR"><span class="term"><code class="literal">sqlvar</code></span> <a href="#ECPG-SQLDA-SQLDA-SQLVAR" class="id_link">#</a></dt><dd><p>217        This is the array of the columns in the result set.218       </p></dd></dl></div><p>219     </p></div><div class="sect4" id="ECPG-SQLDA-SQLVAR"><div class="titlepage"><div><div><h5 class="title">36.7.2.1.2. sqlvar_t Structure <a href="#ECPG-SQLDA-SQLVAR" class="id_link">#</a></h5></div></div></div><p>220      The structure type <code class="type">sqlvar_t</code> holds a column value221      and metadata such as type and length. The definition of the type222      is:223 224</p><pre class="programlisting">225struct sqlvar_struct226{227    short          sqltype;228    short          sqllen;229    char          *sqldata;230    short         *sqlind;231    struct sqlname sqlname;232};233 234typedef struct sqlvar_struct sqlvar_t;235</pre><p>236 237      The meaning of the fields is:238 239        </p><div class="variablelist"><dl class="variablelist"><dt id="ECPG-SQLDA-SQLVAR-SQLTYPE"><span class="term"><code class="literal">sqltype</code></span> <a href="#ECPG-SQLDA-SQLVAR-SQLTYPE" class="id_link">#</a></dt><dd><p>240            Contains the type identifier of the field. For values,241            see <code class="literal">enum ECPGttype</code> in <code class="literal">ecpgtype.h</code>.242           </p></dd><dt id="ECPG-SQLDA-SQLVAR-SQLLEN"><span class="term"><code class="literal">sqllen</code></span> <a href="#ECPG-SQLDA-SQLVAR-SQLLEN" class="id_link">#</a></dt><dd><p>243            Contains the binary length of the field. e.g., 4 bytes for <code class="type">ECPGt_int</code>.244           </p></dd><dt id="ECPG-SQLDA-SQLVAR-SQLDATA"><span class="term"><code class="literal">sqldata</code></span> <a href="#ECPG-SQLDA-SQLVAR-SQLDATA" class="id_link">#</a></dt><dd><p>245            Points to the data.  The format of the data is described246            in <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING" title="36.4.4. Type Mapping">Section 36.4.4</a>.247           </p></dd><dt id="ECPG-SQLDA-SQLVAR-SQLIND"><span class="term"><code class="literal">sqlind</code></span> <a href="#ECPG-SQLDA-SQLVAR-SQLIND" class="id_link">#</a></dt><dd><p>248            Points to the null indicator.  0 means not null, -1 means249            null.250           </p></dd><dt id="ECPG-SQLDA-SQLVAR-SQLNAME"><span class="term"><code class="literal">sqlname</code></span> <a href="#ECPG-SQLDA-SQLVAR-SQLNAME" class="id_link">#</a></dt><dd><p>251            The name of the field.252           </p></dd></dl></div><p>253     </p></div><div class="sect4" id="ECPG-SQLDA-SQLNAME"><div class="titlepage"><div><div><h5 class="title">36.7.2.1.3. struct sqlname Structure <a href="#ECPG-SQLDA-SQLNAME" class="id_link">#</a></h5></div></div></div><p>254      A <code class="type">struct sqlname</code> structure holds a column name.  It255      is used as a member of the <code class="type">sqlvar_t</code> structure.  The256      definition of the structure is:257</p><pre class="programlisting">258#define NAMEDATALEN 64259 260struct sqlname261{262        short           length;263        char            data[NAMEDATALEN];264};265</pre><p>266      The meaning of the fields is:267            </p><div class="variablelist"><dl class="variablelist"><dt id="ECPG-SQLDA-SQLNAME-LENGTH"><span class="term"><code class="literal">length</code></span> <a href="#ECPG-SQLDA-SQLNAME-LENGTH" class="id_link">#</a></dt><dd><p>268                 Contains the length of the field name.269                </p></dd><dt id="ECPG-SQLDA-SQLNAME-DATA"><span class="term"><code class="literal">data</code></span> <a href="#ECPG-SQLDA-SQLNAME-DATA" class="id_link">#</a></dt><dd><p>270                 Contains the actual field name.271                </p></dd></dl></div><p>272     </p></div></div><div class="sect3" id="ECPG-SQLDA-OUTPUT"><div class="titlepage"><div><div><h4 class="title">36.7.2.2. Retrieving a Result Set Using an SQLDA <a href="#ECPG-SQLDA-OUTPUT" class="id_link">#</a></h4></div></div></div><div class="procedure"><p>273      The general steps to retrieve a query result set through an274      SQLDA are:275     </p><ol class="procedure" type="1"><li class="step"><p>Declare an <code class="type">sqlda_t</code> structure to receive the result set.</p></li><li class="step"><p>Execute <code class="command">FETCH</code>/<code class="command">EXECUTE</code>/<code class="command">DESCRIBE</code> commands to process a query specifying the declared SQLDA.</p></li><li class="step"><p>Check the number of records in the result set by looking at <code class="structfield">sqln</code>, a member of the <code class="type">sqlda_t</code> structure.</p></li><li class="step"><p>Get the values of each column from <code class="literal">sqlvar[0]</code>, <code class="literal">sqlvar[1]</code>, etc., members of the <code class="type">sqlda_t</code> structure.</p></li><li class="step"><p>Go to next row (<code class="type">sqlda_t</code> structure) by following the <code class="structfield">desc_next</code> pointer, a member of the <code class="type">sqlda_t</code> structure.</p></li><li class="step"><p>Repeat above as you need.</p></li></ol></div><p>276     Here is an example retrieving a result set through an SQLDA.277    </p><p>278     First, declare a <code class="type">sqlda_t</code> structure to receive the result set.279</p><pre class="programlisting">280sqlda_t *sqlda1;281</pre><p>282    </p><p>283     Next, specify the SQLDA in a command.  This is284     a <code class="command">FETCH</code> command example.285</p><pre class="programlisting">286EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;287</pre><p>288    </p><p>289     Run a loop following the linked list to retrieve the rows.290</p><pre class="programlisting">291sqlda_t *cur_sqlda;292 293for (cur_sqlda = sqlda1;294     cur_sqlda != NULL;295     cur_sqlda = cur_sqlda-&gt;desc_next)296{297    ...298}299</pre><p>300    </p><p>301     Inside the loop, run another loop to retrieve each column data302     (<code class="type">sqlvar_t</code> structure) of the row.303</p><pre class="programlisting">304for (i = 0; i &lt; cur_sqlda-&gt;sqld; i++)305{306    sqlvar_t v = cur_sqlda-&gt;sqlvar[i];307    char *sqldata = v.sqldata;308    short sqllen  = v.sqllen;309    ...310}311</pre><p>312    </p><p>313     To get a column value, check the <code class="structfield">sqltype</code> value,314     a member of the <code class="type">sqlvar_t</code> structure.  Then, switch315     to an appropriate way, depending on the column type, to copy316     data from the <code class="structfield">sqlvar</code> field to a host variable.317</p><pre class="programlisting">318char var_buf[1024];319 320switch (v.sqltype)321{322    case ECPGt_char:323        memset(&amp;var_buf, 0, sizeof(var_buf));324        memcpy(&amp;var_buf, sqldata, (sizeof(var_buf) &lt;= sqllen ? sizeof(var_buf) - 1 : sqllen));325        break;326 327    case ECPGt_int: /* integer */328        memcpy(&amp;intval, sqldata, sqllen);329        snprintf(var_buf, sizeof(var_buf), "%d", intval);330        break;331 332    ...333}334</pre><p>335    </p></div><div class="sect3" id="ECPG-SQLDA-INPUT"><div class="titlepage"><div><div><h4 class="title">36.7.2.3. Passing Query Parameters Using an SQLDA <a href="#ECPG-SQLDA-INPUT" class="id_link">#</a></h4></div></div></div><div class="procedure"><p>336      The general steps to use an SQLDA to pass input337      parameters to a prepared query are:338     </p><ol class="procedure" type="1"><li class="step"><p>Create a prepared query (prepared statement)</p></li><li class="step"><p>Declare an sqlda_t structure as an input SQLDA.</p></li><li class="step"><p>Allocate memory area (as sqlda_t structure) for the input SQLDA.</p></li><li class="step"><p>Set (copy) input values in the allocated memory.</p></li><li class="step"><p>Open a cursor with specifying the input SQLDA.</p></li></ol></div><p>339     Here is an example.340    </p><p>341     First, create a prepared statement.342</p><pre class="programlisting">343EXEC SQL BEGIN DECLARE SECTION;344char query[1024] = "SELECT d.oid, * FROM pg_database d, pg_stat_database s WHERE d.oid = s.datid AND (d.datname = ? OR d.oid = ?)";345EXEC SQL END DECLARE SECTION;346 347EXEC SQL PREPARE stmt1 FROM :query;348</pre><p>349    </p><p>350     Next, allocate memory for an SQLDA, and set the number of input351     parameters in <code class="structfield">sqln</code>, a member variable of352     the <code class="type">sqlda_t</code> structure.  When two or more input353     parameters are required for the prepared query, the application354     has to allocate additional memory space which is calculated by355     (nr. of params - 1) * sizeof(sqlvar_t).  The example shown here356     allocates memory space for two input parameters.357</p><pre class="programlisting">358sqlda_t *sqlda2;359 360sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));361memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));362 363sqlda2-&gt;sqln = 2; /* number of input variables */364</pre><p>365    </p><p>366     After memory allocation, store the parameter values into the367     <code class="literal">sqlvar[]</code> array.  (This is same array used for368     retrieving column values when the SQLDA is receiving a result369     set.)  In this example, the input parameters370     are <code class="literal">"postgres"</code>, having a string type,371     and <code class="literal">1</code>, having an integer type.372</p><pre class="programlisting">373sqlda2-&gt;sqlvar[0].sqltype = ECPGt_char;374sqlda2-&gt;sqlvar[0].sqldata = "postgres";375sqlda2-&gt;sqlvar[0].sqllen  = 8;376 377int intval = 1;378sqlda2-&gt;sqlvar[1].sqltype = ECPGt_int;379sqlda2-&gt;sqlvar[1].sqldata = (char *) &amp;intval;380sqlda2-&gt;sqlvar[1].sqllen  = sizeof(intval);381</pre><p>382    </p><p>383     By opening a cursor and specifying the SQLDA that was set up384     beforehand, the input parameters are passed to the prepared385     statement.386</p><pre class="programlisting">387EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;388</pre><p>389    </p><p>390     Finally, after using input SQLDAs, the allocated memory space391     must be freed explicitly, unlike SQLDAs used for receiving query392     results.393</p><pre class="programlisting">394free(sqlda2);395</pre><p>396    </p></div><div class="sect3" id="ECPG-SQLDA-EXAMPLE"><div class="titlepage"><div><div><h4 class="title">36.7.2.4. A Sample Application Using SQLDA <a href="#ECPG-SQLDA-EXAMPLE" class="id_link">#</a></h4></div></div></div><p>397     Here is an example program, which describes how to fetch access398     statistics of the databases, specified by the input parameters,399     from the system catalogs.400    </p><p>401     This application joins two system tables, pg_database and402     pg_stat_database on the database OID, and also fetches and shows403     the database statistics which are retrieved by two input404     parameters (a database <code class="literal">postgres</code>, and OID <code class="literal">1</code>).405    </p><p>406     First, declare an SQLDA for input and an SQLDA for output.407</p><pre class="programlisting">408EXEC SQL include sqlda.h;409 410sqlda_t *sqlda1; /* an output descriptor */411sqlda_t *sqlda2; /* an input descriptor  */412</pre><p>413    </p><p>414     Next, connect to the database, prepare a statement, and declare a415     cursor for the prepared statement.416</p><pre class="programlisting">417int418main(void)419{420    EXEC SQL BEGIN DECLARE SECTION;421    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";422    EXEC SQL END DECLARE SECTION;423 424    EXEC SQL CONNECT TO testdb AS con1 USER testuser;425    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;426 427    EXEC SQL PREPARE stmt1 FROM :query;428    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;429</pre><p>430    </p><p>431     Next, put some values in the input SQLDA for the input432     parameters.  Allocate memory for the input SQLDA, and set the433     number of input parameters to <code class="literal">sqln</code>.  Store434     type, value, and value length into <code class="literal">sqltype</code>,435     <code class="literal">sqldata</code>, and <code class="literal">sqllen</code> in the436     <code class="literal">sqlvar</code> structure.437 438</p><pre class="programlisting">439    /* Create SQLDA structure for input parameters. */440    sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));441    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));442    sqlda2-&gt;sqln = 2; /* number of input variables */443 444    sqlda2-&gt;sqlvar[0].sqltype = ECPGt_char;445    sqlda2-&gt;sqlvar[0].sqldata = "postgres";446    sqlda2-&gt;sqlvar[0].sqllen  = 8;447 448    intval = 1;449    sqlda2-&gt;sqlvar[1].sqltype = ECPGt_int;450    sqlda2-&gt;sqlvar[1].sqldata = (char *)&amp;intval;451    sqlda2-&gt;sqlvar[1].sqllen  = sizeof(intval);452</pre><p>453    </p><p>454     After setting up the input SQLDA, open a cursor with the input455     SQLDA.456 457</p><pre class="programlisting">458    /* Open a cursor with input parameters. */459    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;460</pre><p>461    </p><p>462     Fetch rows into the output SQLDA from the opened cursor.463     (Generally, you have to call <code class="command">FETCH</code> repeatedly464     in the loop, to fetch all rows in the result set.)465</p><pre class="programlisting">466    while (1)467    {468        sqlda_t *cur_sqlda;469 470        /* Assign descriptor to the cursor  */471        EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;472</pre><p>473    </p><p>474     Next, retrieve the fetched records from the SQLDA, by following475     the linked list of the <code class="type">sqlda_t</code> structure.476</p><pre class="programlisting">477    for (cur_sqlda = sqlda1 ;478         cur_sqlda != NULL ;479         cur_sqlda = cur_sqlda-&gt;desc_next)480    {481        ...482</pre><p>483    </p><p>484     Read each columns in the first record.  The number of columns is485     stored in <code class="structfield">sqld</code>, the actual data of the first486     column is stored in <code class="literal">sqlvar[0]</code>, both members of487     the <code class="type">sqlda_t</code> structure.488 489</p><pre class="programlisting">490        /* Print every column in a row. */491        for (i = 0; i &lt; sqlda1-&gt;sqld; i++)492        {493            sqlvar_t v = sqlda1-&gt;sqlvar[i];494            char *sqldata = v.sqldata;495            short sqllen  = v.sqllen;496 497            strncpy(name_buf, v.sqlname.data, v.sqlname.length);498            name_buf[v.sqlname.length] = '\0';499</pre><p>500    </p><p>501     Now, the column data is stored in the variable <code class="varname">v</code>.502     Copy every datum into host variables, looking503     at <code class="literal">v.sqltype</code> for the type of the column.504</p><pre class="programlisting">505            switch (v.sqltype) {506                int intval;507                double doubleval;508                unsigned long long int longlongval;509 510                case ECPGt_char:511                    memset(&amp;var_buf, 0, sizeof(var_buf));512                    memcpy(&amp;var_buf, sqldata, (sizeof(var_buf) &lt;= sqllen ? sizeof(var_buf)-1 : sqllen));513                    break;514 515                case ECPGt_int: /* integer */516                    memcpy(&amp;intval, sqldata, sqllen);517                    snprintf(var_buf, sizeof(var_buf), "%d", intval);518                    break;519 520                ...521 522                default:523                    ...524            }525 526            printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);527        }528</pre><p>529    </p><p>530     Close the cursor after processing all of records, and disconnect531     from the database.532</p><pre class="programlisting">533    EXEC SQL CLOSE cur1;534    EXEC SQL COMMIT;535 536    EXEC SQL DISCONNECT ALL;537</pre><p>538    </p><p>539     The whole program is shown540     in <a class="xref" href="ecpg-descriptors.html#ECPG-SQLDA-EXAMPLE-EXAMPLE" title="Example 36.1. Example SQLDA Program">Example 36.1</a>.541    </p><div class="example" id="ECPG-SQLDA-EXAMPLE-EXAMPLE"><p class="title"><strong>Example 36.1. Example SQLDA Program</strong></p><div class="example-contents"><pre class="programlisting">542#include &lt;stdlib.h&gt;543#include &lt;string.h&gt;544#include &lt;stdlib.h&gt;545#include &lt;stdio.h&gt;546#include &lt;unistd.h&gt;547 548EXEC SQL include sqlda.h;549 550sqlda_t *sqlda1; /* descriptor for output */551sqlda_t *sqlda2; /* descriptor for input */552 553EXEC SQL WHENEVER NOT FOUND DO BREAK;554EXEC SQL WHENEVER SQLERROR STOP;555 556int557main(void)558{559    EXEC SQL BEGIN DECLARE SECTION;560    char query[1024] = "SELECT d.oid,* FROM pg_database d, pg_stat_database s WHERE d.oid=s.datid AND ( d.datname=? OR d.oid=? )";561 562    int intval;563    unsigned long long int longlongval;564    EXEC SQL END DECLARE SECTION;565 566    EXEC SQL CONNECT TO uptimedb AS con1 USER uptime;567    EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT;568 569    EXEC SQL PREPARE stmt1 FROM :query;570    EXEC SQL DECLARE cur1 CURSOR FOR stmt1;571 572    /* Create an SQLDA structure for an input parameter */573    sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t));574    memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t));575    sqlda2-&gt;sqln = 2; /* a number of input variables */576 577    sqlda2-&gt;sqlvar[0].sqltype = ECPGt_char;578    sqlda2-&gt;sqlvar[0].sqldata = "postgres";579    sqlda2-&gt;sqlvar[0].sqllen  = 8;580 581    intval = 1;582    sqlda2-&gt;sqlvar[1].sqltype = ECPGt_int;583    sqlda2-&gt;sqlvar[1].sqldata = (char *) &amp;intval;584    sqlda2-&gt;sqlvar[1].sqllen  = sizeof(intval);585 586    /* Open a cursor with input parameters. */587    EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2;588 589    while (1)590    {591        sqlda_t *cur_sqlda;592 593        /* Assign descriptor to the cursor  */594        EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1;595 596        for (cur_sqlda = sqlda1 ;597             cur_sqlda != NULL ;598             cur_sqlda = cur_sqlda-&gt;desc_next)599        {600            int i;601            char name_buf[1024];602            char var_buf[1024];603 604            /* Print every column in a row. */605            for (i=0 ; i&lt;cur_sqlda-&gt;sqld ; i++)606            {607                sqlvar_t v = cur_sqlda-&gt;sqlvar[i];608                char *sqldata = v.sqldata;609                short sqllen  = v.sqllen;610 611                strncpy(name_buf, v.sqlname.data, v.sqlname.length);612                name_buf[v.sqlname.length] = '\0';613 614                switch (v.sqltype)615                {616                    case ECPGt_char:617                        memset(&amp;var_buf, 0, sizeof(var_buf));618                        memcpy(&amp;var_buf, sqldata, (sizeof(var_buf)&lt;=sqllen ? sizeof(var_buf)-1 : sqllen) );619                        break;620 621                    case ECPGt_int: /* integer */622                        memcpy(&amp;intval, sqldata, sqllen);623                        snprintf(var_buf, sizeof(var_buf), "%d", intval);624                        break;625 626                    case ECPGt_long_long: /* bigint */627                        memcpy(&amp;longlongval, sqldata, sqllen);628                        snprintf(var_buf, sizeof(var_buf), "%lld", longlongval);629                        break;630 631                    default:632                    {633                        int i;634                        memset(var_buf, 0, sizeof(var_buf));635                        for (i = 0; i &lt; sqllen; i++)636                        {637                            char tmpbuf[16];638                            snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]);639                            strncat(var_buf, tmpbuf, sizeof(var_buf));640                        }641                    }642                        break;643                }644 645                printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype);646            }647 648            printf("\n");649        }650    }651 652    EXEC SQL CLOSE cur1;653    EXEC SQL COMMIT;654 655    EXEC SQL DISCONNECT ALL;656 657    return 0;658}659</pre><p>660      The output of this example should look something like the661      following (some numbers will vary).662     </p><pre class="screen">663oid = 1 (type: 1)664datname = template1 (type: 1)665datdba = 10 (type: 1)666encoding = 0 (type: 5)667datistemplate = t (type: 1)668datallowconn = t (type: 1)669datconnlimit = -1 (type: 5)670datfrozenxid = 379 (type: 1)671dattablespace = 1663 (type: 1)672datconfig =  (type: 1)673datacl = {=c/uptime,uptime=CTc/uptime} (type: 1)674datid = 1 (type: 1)675datname = template1 (type: 1)676numbackends = 0 (type: 5)677xact_commit = 113606 (type: 9)678xact_rollback = 0 (type: 9)679blks_read = 130 (type: 9)680blks_hit = 7341714 (type: 9)681tup_returned = 38262679 (type: 9)682tup_fetched = 1836281 (type: 9)683tup_inserted = 0 (type: 9)684tup_updated = 0 (type: 9)685tup_deleted = 0 (type: 9)686 687oid = 11511 (type: 1)688datname = postgres (type: 1)689datdba = 10 (type: 1)690encoding = 0 (type: 5)691datistemplate = f (type: 1)692datallowconn = t (type: 1)693datconnlimit = -1 (type: 5)694datfrozenxid = 379 (type: 1)695dattablespace = 1663 (type: 1)696datconfig =  (type: 1)697datacl =  (type: 1)698datid = 11511 (type: 1)699datname = postgres (type: 1)700numbackends = 0 (type: 5)701xact_commit = 221069 (type: 9)702xact_rollback = 18 (type: 9)703blks_read = 1176 (type: 9)704blks_hit = 13943750 (type: 9)705tup_returned = 77410091 (type: 9)706tup_fetched = 3253694 (type: 9)707tup_inserted = 0 (type: 9)708tup_updated = 0 (type: 9)709tup_deleted = 0 (type: 9)710</pre></div></div><br class="example-break" /></div></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="ecpg-pgtypes.html" title="36.6. pgtypes Library">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="ecpg.html" title="Chapter 36. ECPG — Embedded SQL in C">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="ecpg-errors.html" title="36.8. Error Handling">Next</a></td></tr><tr><td width="40%" align="left" valign="top">36.6. pgtypes Library </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.8. Error Handling</td></tr></table></div></body></html>
codekingpro/portable-devtools · Team Ai