1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>30.8.Using SQL Descriptor Areas</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="ecpg.html" title="Chapter30.ECPG - Embedded SQL in C">
<link rel="prev" href="ecpg-dynamic.html" title="30.7.Dynamic SQL">
<link rel="next" href="ecpg-errors.html" title="30.9.Error Handling">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="ecpg-descriptors"></a>30.8.Using SQL Descriptor Areas</h2></div></div></div>
<p> An SQL descriptor area is a more sophisticated method for
processing the result of a <code class="command">SELECT</code> or
<code class="command">FETCH</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 may
not be known ahead of time.
</p>
<p> An 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">variable name</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 DESCRIPTOR mydesc;</pre>
<p>
</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>
<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> ?
</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>
</div></body>
</html>
|
