diff options
Diffstat (limited to 'doc/src/sgml/html/ecpg-descriptors.html')
| -rw-r--r-- | doc/src/sgml/html/ecpg-descriptors.html | 710 |
1 files changed, 710 insertions, 0 deletions
diff --git a/doc/src/sgml/html/ecpg-descriptors.html b/doc/src/sgml/html/ecpg-descriptors.html new file mode 100644 index 0000000..a93f57e --- /dev/null +++ b/doc/src/sgml/html/ecpg-descriptors.html @@ -0,0 +1,710 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 15.5 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</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> + An SQL descriptor area is a more sophisticated method for processing + the result of a <code class="command">SELECT</code>, <code class="command">FETCH</code> or + a <code class="command">DESCRIBE</code> statement. An SQL descriptor area groups + the data of one row of data together with metadata items into one + data structure. The metadata is particularly useful when executing + dynamic SQL statements, where the nature of the result columns might + not be known ahead of time. PostgreSQL provides two ways to use + Descriptor Areas: the named SQL Descriptor Areas and the C-structure + SQLDAs. + </p><div class="sect2" id="ECPG-NAMED-DESCRIPTORS"><div class="titlepage"><div><div><h3 class="title">36.7.1. Named SQL Descriptor Areas</h3></div></div></div><p> + A named SQL descriptor area consists of a header, which contains + information concerning the entire descriptor, and one or more item + descriptor areas, which basically each describe one column in the + result row. + </p><p> + Before you can use an SQL descriptor area, you need to allocate one: +</p><pre class="programlisting"> +EXEC SQL ALLOCATE DESCRIPTOR <em class="replaceable"><code>identifier</code></em>; +</pre><p> + The identifier serves as the <span class="quote">“<span class="quote">variable name</span>”</span> of the + descriptor area. + When you don't need the descriptor anymore, you should deallocate + it: +</p><pre class="programlisting"> +EXEC SQL DEALLOCATE DESCRIPTOR <em class="replaceable"><code>identifier</code></em>; +</pre><p> + </p><p> + To use a descriptor area, specify it as the storage target in an + <code class="literal">INTO</code> clause, instead of listing host variables: +</p><pre class="programlisting"> +EXEC SQL FETCH NEXT FROM mycursor INTO SQL DESCRIPTOR mydesc; +</pre><p> + If the result set is empty, the Descriptor Area will still contain + the metadata from the query, i.e., the field names. + </p><p> + For not yet executed prepared queries, the <code class="command">DESCRIBE</code> + statement can be used to get the metadata of the result set: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +char *sql_stmt = "SELECT * FROM table1"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE stmt1 FROM :sql_stmt; +EXEC SQL DESCRIBE stmt1 INTO SQL DESCRIPTOR mydesc; +</pre><p> + </p><p> + Before PostgreSQL 9.0, the <code class="literal">SQL</code> keyword was optional, + so using <code class="literal">DESCRIPTOR</code> and <code class="literal">SQL DESCRIPTOR</code> + produced named SQL Descriptor Areas. Now it is mandatory, omitting + the <code class="literal">SQL</code> keyword produces SQLDA Descriptor Areas, + see <a class="xref" href="ecpg-descriptors.html#ECPG-SQLDA-DESCRIPTORS" title="36.7.2. SQLDA Descriptor Areas">Section 36.7.2</a>. + </p><p> + In <code class="command">DESCRIBE</code> and <code class="command">FETCH</code> statements, + the <code class="literal">INTO</code> and <code class="literal">USING</code> keywords can be + used to similarly: they produce the result set and the metadata in a + Descriptor Area. + </p><p> + Now how do you get the data out of the descriptor area? You can + think of the descriptor area as a structure with named fields. To + retrieve the value of a field from the header and store it into a + host variable, use the following command: +</p><pre class="programlisting"> +EXEC SQL GET DESCRIPTOR <em class="replaceable"><code>name</code></em> :<em class="replaceable"><code>hostvar</code></em> = <em class="replaceable"><code>field</code></em>; +</pre><p> + Currently, there is only one header field defined: + <em class="replaceable"><code>COUNT</code></em>, which tells how many item + descriptor areas exist (that is, how many columns are contained in + the result). The host variable needs to be of an integer type. To + get a field from the item descriptor area, use the following + command: +</p><pre class="programlisting"> +EXEC 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>; +</pre><p> + <em class="replaceable"><code>num</code></em> can be a literal integer or a host + variable containing an integer. Possible fields are: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">CARDINALITY</code> (integer)</span></dt><dd><p> + number of rows in the result set + </p></dd><dt><span class="term"><code class="literal">DATA</code></span></dt><dd><p> + actual data item (therefore, the data type of this field + depends on the query) + </p></dd><dt><span class="term"><code class="literal">DATETIME_INTERVAL_CODE</code> (integer)</span></dt><dd><p> + When <code class="literal">TYPE</code> is <code class="literal">9</code>, + <code class="literal">DATETIME_INTERVAL_CODE</code> will have a value of + <code class="literal">1</code> for <code class="literal">DATE</code>, + <code class="literal">2</code> for <code class="literal">TIME</code>, + <code class="literal">3</code> for <code class="literal">TIMESTAMP</code>, + <code class="literal">4</code> for <code class="literal">TIME WITH TIME ZONE</code>, or + <code class="literal">5</code> for <code class="literal">TIMESTAMP WITH TIME ZONE</code>. + </p></dd><dt><span class="term"><code class="literal">DATETIME_INTERVAL_PRECISION</code> (integer)</span></dt><dd><p> + not implemented + </p></dd><dt><span class="term"><code class="literal">INDICATOR</code> (integer)</span></dt><dd><p> + the indicator (indicating a null value or a value truncation) + </p></dd><dt><span class="term"><code class="literal">KEY_MEMBER</code> (integer)</span></dt><dd><p> + not implemented + </p></dd><dt><span class="term"><code class="literal">LENGTH</code> (integer)</span></dt><dd><p> + length of the datum in characters + </p></dd><dt><span class="term"><code class="literal">NAME</code> (string)</span></dt><dd><p> + name of the column + </p></dd><dt><span class="term"><code class="literal">NULLABLE</code> (integer)</span></dt><dd><p> + not implemented + </p></dd><dt><span class="term"><code class="literal">OCTET_LENGTH</code> (integer)</span></dt><dd><p> + length of the character representation of the datum in bytes + </p></dd><dt><span class="term"><code class="literal">PRECISION</code> (integer)</span></dt><dd><p> + precision (for type <code class="type">numeric</code>) + </p></dd><dt><span class="term"><code class="literal">RETURNED_LENGTH</code> (integer)</span></dt><dd><p> + length of the datum in characters + </p></dd><dt><span class="term"><code class="literal">RETURNED_OCTET_LENGTH</code> (integer)</span></dt><dd><p> + length of the character representation of the datum in bytes + </p></dd><dt><span class="term"><code class="literal">SCALE</code> (integer)</span></dt><dd><p> + scale (for type <code class="type">numeric</code>) + </p></dd><dt><span class="term"><code class="literal">TYPE</code> (integer)</span></dt><dd><p> + numeric code of the data type of the column + </p></dd></dl></div><p> + </p><p> + In <code class="command">EXECUTE</code>, <code class="command">DECLARE</code> and <code class="command">OPEN</code> + statements, the effect of the <code class="literal">INTO</code> and <code class="literal">USING</code> + keywords are different. A Descriptor Area can also be manually built to + provide the input parameters for a query or a cursor and + <code class="literal">USING SQL DESCRIPTOR <em class="replaceable"><code>name</code></em></code> + is the way to pass the input parameters into a parameterized query. The statement + to build a named SQL Descriptor Area is below: +</p><pre class="programlisting"> +EXEC 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>; +</pre><p> + </p><p> + PostgreSQL supports retrieving more that one record in one <code class="command">FETCH</code> + statement and storing the data in host variables in this case assumes that the + variable is an array. E.g.: +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +int id[5]; +EXEC SQL END DECLARE SECTION; + +EXEC SQL FETCH 5 FROM mycursor INTO SQL DESCRIPTOR mydesc; + +EXEC SQL GET DESCRIPTOR mydesc VALUE 1 :id = DATA; +</pre><p> + + </p></div><div class="sect2" id="ECPG-SQLDA-DESCRIPTORS"><div class="titlepage"><div><div><h3 class="title">36.7.2. SQLDA Descriptor Areas</h3></div></div></div><p> + An SQLDA Descriptor Area is a C language structure which can be also used + to get the result set and the metadata of a query. One structure stores one + record from the result set. +</p><pre class="programlisting"> +EXEC SQL include sqlda.h; +sqlda_t *mysqlda; + +EXEC SQL FETCH 3 FROM mycursor INTO DESCRIPTOR mysqlda; +</pre><p> + Note that the <code class="literal">SQL</code> keyword is omitted. The paragraphs about + the use cases of the <code class="literal">INTO</code> and <code class="literal">USING</code> + 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. + In a <code class="command">DESCRIBE</code> statement the <code class="literal">DESCRIPTOR</code> + keyword can be completely omitted if the <code class="literal">INTO</code> keyword is used: +</p><pre class="programlisting"> +EXEC SQL DESCRIBE prepared_statement INTO mysqlda; +</pre><p> + </p><div class="procedure"><p> + The general flow of a program that uses SQLDA is: + </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="id-1.7.5.13.4.4"><div class="titlepage"><div><div><h4 class="title">36.7.2.1. SQLDA Data Structure</h4></div></div></div><p> + SQLDA uses three data structure + types: <code class="type">sqlda_t</code>, <code class="type">sqlvar_t</code>, + and <code class="type">struct sqlname</code>. + </p><div class="tip"><h3 class="title">Tip</h3><p> + PostgreSQL's SQLDA has a similar data structure to the one in + IBM DB2 Universal Database, so some technical information on + DB2's SQLDA could help understanding PostgreSQL's one better. + </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</h5></div></div></div><p> + The structure type <code class="type">sqlda_t</code> is the type of the + actual SQLDA. It holds one record. And two or + more <code class="type">sqlda_t</code> structures can be connected in a + linked list with the pointer in + the <code class="structfield">desc_next</code> field, thus + representing an ordered collection of rows. So, when two or + more rows are fetched, the application can read them by + following the <code class="structfield">desc_next</code> pointer in + each <code class="type">sqlda_t</code> node. + </p><p> + The definition of <code class="type">sqlda_t</code> is: +</p><pre class="programlisting"> +struct sqlda_struct +{ + char sqldaid[8]; + long sqldabc; + short sqln; + short sqld; + struct sqlda_struct *desc_next; + struct sqlvar_struct sqlvar[1]; +}; + +typedef struct sqlda_struct sqlda_t; +</pre><p> + + The meaning of the fields is: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">sqldaid</code></span></dt><dd><p> + It contains the literal string <code class="literal">"SQLDA "</code>. + </p></dd><dt><span class="term"><code class="literal">sqldabc</code></span></dt><dd><p> + It contains the size of the allocated space in bytes. + </p></dd><dt><span class="term"><code class="literal">sqln</code></span></dt><dd><p> + It contains the number of input parameters for a parameterized query in + case it's passed into <code class="command">OPEN</code>, <code class="command">DECLARE</code> or + <code class="command">EXECUTE</code> statements using the <code class="literal">USING</code> + keyword. In case it's used as output of <code class="command">SELECT</code>, + <code class="command">EXECUTE</code> or <code class="command">FETCH</code> statements, + its value is the same as <code class="literal">sqld</code> + statement + </p></dd><dt><span class="term"><code class="literal">sqld</code></span></dt><dd><p> + It contains the number of fields in a result set. + </p></dd><dt><span class="term"><code class="literal">desc_next</code></span></dt><dd><p> + If the query returns more than one record, multiple linked + SQLDA structures are returned, and <code class="literal">desc_next</code> holds + a pointer to the next entry in the list. + </p></dd><dt><span class="term"><code class="literal">sqlvar</code></span></dt><dd><p> + This is the array of the columns in the result set. + </p></dd></dl></div><p> + </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</h5></div></div></div><p> + The structure type <code class="type">sqlvar_t</code> holds a column value + and metadata such as type and length. The definition of the type + is: + +</p><pre class="programlisting"> +struct sqlvar_struct +{ + short sqltype; + short sqllen; + char *sqldata; + short *sqlind; + struct sqlname sqlname; +}; + +typedef struct sqlvar_struct sqlvar_t; +</pre><p> + + The meaning of the fields is: + + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">sqltype</code></span></dt><dd><p> + Contains the type identifier of the field. For values, + see <code class="literal">enum ECPGttype</code> in <code class="literal">ecpgtype.h</code>. + </p></dd><dt><span class="term"><code class="literal">sqllen</code></span></dt><dd><p> + Contains the binary length of the field. e.g., 4 bytes for <code class="type">ECPGt_int</code>. + </p></dd><dt><span class="term"><code class="literal">sqldata</code></span></dt><dd><p> + Points to the data. The format of the data is described + in <a class="xref" href="ecpg-variables.html#ECPG-VARIABLES-TYPE-MAPPING" title="36.4.4. Type Mapping">Section 36.4.4</a>. + </p></dd><dt><span class="term"><code class="literal">sqlind</code></span></dt><dd><p> + Points to the null indicator. 0 means not null, -1 means + null. + </p></dd><dt><span class="term"><code class="literal">sqlname</code></span></dt><dd><p> + The name of the field. + </p></dd></dl></div><p> + </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</h5></div></div></div><p> + A <code class="type">struct sqlname</code> structure holds a column name. It + is used as a member of the <code class="type">sqlvar_t</code> structure. The + definition of the structure is: +</p><pre class="programlisting"> +#define NAMEDATALEN 64 + +struct sqlname +{ + short length; + char data[NAMEDATALEN]; +}; +</pre><p> + The meaning of the fields is: + </p><div class="variablelist"><dl class="variablelist"><dt><span class="term"><code class="literal">length</code></span></dt><dd><p> + Contains the length of the field name. + </p></dd><dt><span class="term"><code class="literal">data</code></span></dt><dd><p> + Contains the actual field name. + </p></dd></dl></div><p> + </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</h4></div></div></div><div class="procedure"><p> + The general steps to retrieve a query result set through an + SQLDA are: + </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> + Here is an example retrieving a result set through an SQLDA. + </p><p> + First, declare a <code class="type">sqlda_t</code> structure to receive the result set. +</p><pre class="programlisting"> +sqlda_t *sqlda1; +</pre><p> + </p><p> + Next, specify the SQLDA in a command. This is + a <code class="command">FETCH</code> command example. +</p><pre class="programlisting"> +EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; +</pre><p> + </p><p> + Run a loop following the linked list to retrieve the rows. +</p><pre class="programlisting"> +sqlda_t *cur_sqlda; + +for (cur_sqlda = sqlda1; + cur_sqlda != NULL; + cur_sqlda = cur_sqlda->desc_next) +{ + ... +} +</pre><p> + </p><p> + Inside the loop, run another loop to retrieve each column data + (<code class="type">sqlvar_t</code> structure) of the row. +</p><pre class="programlisting"> +for (i = 0; i < cur_sqlda->sqld; i++) +{ + sqlvar_t v = cur_sqlda->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + ... +} +</pre><p> + </p><p> + To get a column value, check the <code class="structfield">sqltype</code> value, + a member of the <code class="type">sqlvar_t</code> structure. Then, switch + to an appropriate way, depending on the column type, to copy + data from the <code class="structfield">sqlvar</code> field to a host variable. +</p><pre class="programlisting"> +char var_buf[1024]; + +switch (v.sqltype) +{ + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf) - 1 : sqllen)); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + ... +} +</pre><p> + </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</h4></div></div></div><div class="procedure"><p> + The general steps to use an SQLDA to pass input + parameters to a prepared query are: + </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> + Here is an example. + </p><p> + First, create a prepared statement. +</p><pre class="programlisting"> +EXEC SQL BEGIN DECLARE SECTION; +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 = ?)"; +EXEC SQL END DECLARE SECTION; + +EXEC SQL PREPARE stmt1 FROM :query; +</pre><p> + </p><p> + Next, allocate memory for an SQLDA, and set the number of input + parameters in <code class="structfield">sqln</code>, a member variable of + the <code class="type">sqlda_t</code> structure. When two or more input + parameters are required for the prepared query, the application + has to allocate additional memory space which is calculated by + (nr. of params - 1) * sizeof(sqlvar_t). The example shown here + allocates memory space for two input parameters. +</p><pre class="programlisting"> +sqlda_t *sqlda2; + +sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); +memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + +sqlda2->sqln = 2; /* number of input variables */ +</pre><p> + </p><p> + After memory allocation, store the parameter values into the + <code class="literal">sqlvar[]</code> array. (This is same array used for + retrieving column values when the SQLDA is receiving a result + set.) In this example, the input parameters + are <code class="literal">"postgres"</code>, having a string type, + and <code class="literal">1</code>, having an integer type. +</p><pre class="programlisting"> +sqlda2->sqlvar[0].sqltype = ECPGt_char; +sqlda2->sqlvar[0].sqldata = "postgres"; +sqlda2->sqlvar[0].sqllen = 8; + +int intval = 1; +sqlda2->sqlvar[1].sqltype = ECPGt_int; +sqlda2->sqlvar[1].sqldata = (char *) &intval; +sqlda2->sqlvar[1].sqllen = sizeof(intval); +</pre><p> + </p><p> + By opening a cursor and specifying the SQLDA that was set up + beforehand, the input parameters are passed to the prepared + statement. +</p><pre class="programlisting"> +EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; +</pre><p> + </p><p> + Finally, after using input SQLDAs, the allocated memory space + must be freed explicitly, unlike SQLDAs used for receiving query + results. +</p><pre class="programlisting"> +free(sqlda2); +</pre><p> + </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</h4></div></div></div><p> + Here is an example program, which describes how to fetch access + statistics of the databases, specified by the input parameters, + from the system catalogs. + </p><p> + This application joins two system tables, pg_database and + pg_stat_database on the database OID, and also fetches and shows + the database statistics which are retrieved by two input + parameters (a database <code class="literal">postgres</code>, and OID <code class="literal">1</code>). + </p><p> + First, declare an SQLDA for input and an SQLDA for output. +</p><pre class="programlisting"> +EXEC SQL include sqlda.h; + +sqlda_t *sqlda1; /* an output descriptor */ +sqlda_t *sqlda2; /* an input descriptor */ +</pre><p> + </p><p> + Next, connect to the database, prepare a statement, and declare a + cursor for the prepared statement. +</p><pre class="programlisting"> +int +main(void) +{ + EXEC SQL BEGIN DECLARE SECTION; + 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=? )"; + EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO testdb AS con1 USER testuser; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + EXEC SQL PREPARE stmt1 FROM :query; + EXEC SQL DECLARE cur1 CURSOR FOR stmt1; +</pre><p> + </p><p> + Next, put some values in the input SQLDA for the input + parameters. Allocate memory for the input SQLDA, and set the + number of input parameters to <code class="literal">sqln</code>. Store + type, value, and value length into <code class="literal">sqltype</code>, + <code class="literal">sqldata</code>, and <code class="literal">sqllen</code> in the + <code class="literal">sqlvar</code> structure. + +</p><pre class="programlisting"> + /* Create SQLDA structure for input parameters. */ + sqlda2 = (sqlda_t *) malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); + memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + sqlda2->sqln = 2; /* number of input variables */ + + sqlda2->sqlvar[0].sqltype = ECPGt_char; + sqlda2->sqlvar[0].sqldata = "postgres"; + sqlda2->sqlvar[0].sqllen = 8; + + intval = 1; + sqlda2->sqlvar[1].sqltype = ECPGt_int; + sqlda2->sqlvar[1].sqldata = (char *)&intval; + sqlda2->sqlvar[1].sqllen = sizeof(intval); +</pre><p> + </p><p> + After setting up the input SQLDA, open a cursor with the input + SQLDA. + +</p><pre class="programlisting"> + /* Open a cursor with input parameters. */ + EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; +</pre><p> + </p><p> + Fetch rows into the output SQLDA from the opened cursor. + (Generally, you have to call <code class="command">FETCH</code> repeatedly + in the loop, to fetch all rows in the result set.) +</p><pre class="programlisting"> + while (1) + { + sqlda_t *cur_sqlda; + + /* Assign descriptor to the cursor */ + EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; +</pre><p> + </p><p> + Next, retrieve the fetched records from the SQLDA, by following + the linked list of the <code class="type">sqlda_t</code> structure. +</p><pre class="programlisting"> + for (cur_sqlda = sqlda1 ; + cur_sqlda != NULL ; + cur_sqlda = cur_sqlda->desc_next) + { + ... +</pre><p> + </p><p> + Read each columns in the first record. The number of columns is + stored in <code class="structfield">sqld</code>, the actual data of the first + column is stored in <code class="literal">sqlvar[0]</code>, both members of + the <code class="type">sqlda_t</code> structure. + +</p><pre class="programlisting"> + /* Print every column in a row. */ + for (i = 0; i < sqlda1->sqld; i++) + { + sqlvar_t v = sqlda1->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + + strncpy(name_buf, v.sqlname.data, v.sqlname.length); + name_buf[v.sqlname.length] = '\0'; +</pre><p> + </p><p> + Now, the column data is stored in the variable <code class="varname">v</code>. + Copy every datum into host variables, looking + at <code class="literal">v.sqltype</code> for the type of the column. +</p><pre class="programlisting"> + switch (v.sqltype) { + int intval; + double doubleval; + unsigned long long int longlongval; + + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf) <= sqllen ? sizeof(var_buf)-1 : sqllen)); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + ... + + default: + ... + } + + printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); + } +</pre><p> + </p><p> + Close the cursor after processing all of records, and disconnect + from the database. +</p><pre class="programlisting"> + EXEC SQL CLOSE cur1; + EXEC SQL COMMIT; + + EXEC SQL DISCONNECT ALL; +</pre><p> + </p><p> + The whole program is shown + in <a class="xref" href="ecpg-descriptors.html#ECPG-SQLDA-EXAMPLE-EXAMPLE" title="Example 36.1. Example SQLDA Program">Example 36.1</a>. + </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"> +#include <stdlib.h> +#include <string.h> +#include <stdlib.h> +#include <stdio.h> +#include <unistd.h> + +EXEC SQL include sqlda.h; + +sqlda_t *sqlda1; /* descriptor for output */ +sqlda_t *sqlda2; /* descriptor for input */ + +EXEC SQL WHENEVER NOT FOUND DO BREAK; +EXEC SQL WHENEVER SQLERROR STOP; + +int +main(void) +{ + EXEC SQL BEGIN DECLARE SECTION; + 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=? )"; + + int intval; + unsigned long long int longlongval; + EXEC SQL END DECLARE SECTION; + + EXEC SQL CONNECT TO uptimedb AS con1 USER uptime; + EXEC SQL SELECT pg_catalog.set_config('search_path', '', false); EXEC SQL COMMIT; + + EXEC SQL PREPARE stmt1 FROM :query; + EXEC SQL DECLARE cur1 CURSOR FOR stmt1; + + /* Create an SQLDA structure for an input parameter */ + sqlda2 = (sqlda_t *)malloc(sizeof(sqlda_t) + sizeof(sqlvar_t)); + memset(sqlda2, 0, sizeof(sqlda_t) + sizeof(sqlvar_t)); + sqlda2->sqln = 2; /* a number of input variables */ + + sqlda2->sqlvar[0].sqltype = ECPGt_char; + sqlda2->sqlvar[0].sqldata = "postgres"; + sqlda2->sqlvar[0].sqllen = 8; + + intval = 1; + sqlda2->sqlvar[1].sqltype = ECPGt_int; + sqlda2->sqlvar[1].sqldata = (char *) &intval; + sqlda2->sqlvar[1].sqllen = sizeof(intval); + + /* Open a cursor with input parameters. */ + EXEC SQL OPEN cur1 USING DESCRIPTOR sqlda2; + + while (1) + { + sqlda_t *cur_sqlda; + + /* Assign descriptor to the cursor */ + EXEC SQL FETCH NEXT FROM cur1 INTO DESCRIPTOR sqlda1; + + for (cur_sqlda = sqlda1 ; + cur_sqlda != NULL ; + cur_sqlda = cur_sqlda->desc_next) + { + int i; + char name_buf[1024]; + char var_buf[1024]; + + /* Print every column in a row. */ + for (i=0 ; i<cur_sqlda->sqld ; i++) + { + sqlvar_t v = cur_sqlda->sqlvar[i]; + char *sqldata = v.sqldata; + short sqllen = v.sqllen; + + strncpy(name_buf, v.sqlname.data, v.sqlname.length); + name_buf[v.sqlname.length] = '\0'; + + switch (v.sqltype) + { + case ECPGt_char: + memset(&var_buf, 0, sizeof(var_buf)); + memcpy(&var_buf, sqldata, (sizeof(var_buf)<=sqllen ? sizeof(var_buf)-1 : sqllen) ); + break; + + case ECPGt_int: /* integer */ + memcpy(&intval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%d", intval); + break; + + case ECPGt_long_long: /* bigint */ + memcpy(&longlongval, sqldata, sqllen); + snprintf(var_buf, sizeof(var_buf), "%lld", longlongval); + break; + + default: + { + int i; + memset(var_buf, 0, sizeof(var_buf)); + for (i = 0; i < sqllen; i++) + { + char tmpbuf[16]; + snprintf(tmpbuf, sizeof(tmpbuf), "%02x ", (unsigned char) sqldata[i]); + strncat(var_buf, tmpbuf, sizeof(var_buf)); + } + } + break; + } + + printf("%s = %s (type: %d)\n", name_buf, var_buf, v.sqltype); + } + + printf("\n"); + } + } + + EXEC SQL CLOSE cur1; + EXEC SQL COMMIT; + + EXEC SQL DISCONNECT ALL; + + return 0; +} +</pre><p> + The output of this example should look something like the + following (some numbers will vary). + </p><pre class="screen"> +oid = 1 (type: 1) +datname = template1 (type: 1) +datdba = 10 (type: 1) +encoding = 0 (type: 5) +datistemplate = t (type: 1) +datallowconn = t (type: 1) +datconnlimit = -1 (type: 5) +datfrozenxid = 379 (type: 1) +dattablespace = 1663 (type: 1) +datconfig = (type: 1) +datacl = {=c/uptime,uptime=CTc/uptime} (type: 1) +datid = 1 (type: 1) +datname = template1 (type: 1) +numbackends = 0 (type: 5) +xact_commit = 113606 (type: 9) +xact_rollback = 0 (type: 9) +blks_read = 130 (type: 9) +blks_hit = 7341714 (type: 9) +tup_returned = 38262679 (type: 9) +tup_fetched = 1836281 (type: 9) +tup_inserted = 0 (type: 9) +tup_updated = 0 (type: 9) +tup_deleted = 0 (type: 9) + +oid = 11511 (type: 1) +datname = postgres (type: 1) +datdba = 10 (type: 1) +encoding = 0 (type: 5) +datistemplate = f (type: 1) +datallowconn = t (type: 1) +datconnlimit = -1 (type: 5) +datfrozenxid = 379 (type: 1) +dattablespace = 1663 (type: 1) +datconfig = (type: 1) +datacl = (type: 1) +datid = 11511 (type: 1) +datname = postgres (type: 1) +numbackends = 0 (type: 5) +xact_commit = 221069 (type: 9) +xact_rollback = 18 (type: 9) +blks_read = 1176 (type: 9) +blks_hit = 13943750 (type: 9) +tup_returned = 77410091 (type: 9) +tup_fetched = 3253694 (type: 9) +tup_inserted = 0 (type: 9) +tup_updated = 0 (type: 9) +tup_deleted = 0 (type: 9) +</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 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> 36.8. Error Handling</td></tr></table></div></body></html>
\ No newline at end of file |