codekingpro/portable-devtools
115k
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->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 < cur_sqlda->sqld; i++)305{306 sqlvar_t v = cur_sqlda->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(&var_buf, 0, sizeof(var_buf));324 memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen));325 break;326 327 case ECPGt_int: /* integer */328 memcpy(&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->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->sqlvar[0].sqltype = ECPGt_char;374sqlda2->sqlvar[0].sqldata = "postgres";375sqlda2->sqlvar[0].sqllen = 8;376 377int intval = 1;378sqlda2->sqlvar[1].sqltype = ECPGt_int;379sqlda2->sqlvar[1].sqldata = (char *) &intval;380sqlda2->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->sqln = 2; /* number of input variables */443 444 sqlda2->sqlvar[0].sqltype = ECPGt_char;445 sqlda2->sqlvar[0].sqldata = "postgres";446 sqlda2->sqlvar[0].sqllen = 8;447 448 intval = 1;449 sqlda2->sqlvar[1].sqltype = ECPGt_int;450 sqlda2->sqlvar[1].sqldata = (char *)&intval;451 sqlda2->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->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 < sqlda1->sqld; i++)492 {493 sqlvar_t v = sqlda1->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(&var_buf, 0, sizeof(var_buf));512 memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen));513 break;514 515 case ECPGt_int: /* integer */516 memcpy(&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 <stdlib.h>543#include <string.h>544#include <stdlib.h>545#include <stdio.h>546#include <unistd.h>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->sqln = 2; /* a number of input variables */576 577 sqlda2->sqlvar[0].sqltype = ECPGt_char;578 sqlda2->sqlvar[0].sqldata = "postgres";579 sqlda2->sqlvar[0].sqllen = 8;580 581 intval = 1;582 sqlda2->sqlvar[1].sqltype = ECPGt_int;583 sqlda2->sqlvar[1].sqldata = (char *) &intval;584 sqlda2->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->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<cur_sqlda->sqld ; i++)606 {607 sqlvar_t v = cur_sqlda->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(&var_buf, 0, sizeof(var_buf));618 memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) );619 break;620 621 case ECPGt_int: /* integer */622 memcpy(&intval, sqldata, sqllen);623 snprintf(var_buf, sizeof(var_buf), "%d", intval);624 break;625 626 case ECPGt_long_long: /* bigint */627 memcpy(&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 < 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>