44
Chapter 33. ECPG - Embedded SQL in C
EXEC SQL FETCH NEXT FROM foo_bar INTO :dboid, :dbname;
...
}
EXEC SQL CLOSE foo_bar;
When you don’t need the prepared statement anymore, you should deallocate it:
EXEC SQL DEALLOCATE PREPARE
name
;
For more details about
PREPARE
,see PREPARE. Also see Section 33.5 for more details about using
placeholders and input parameters.
33.4. Using Host Variables
In Section 33.3 you saw how you can execute SQL statements from an embedded SQL program.
Some of those statements only used fixed values and did not provide a way to insert user-supplied
values into statements or have the program process the values returned by the query. Those kinds of
statements are not really useful in real applications. This section explains in detail how you can pass
data between your C program and the embedded SQL statements using a simple mechanism called
host variables. In an embedded SQL program we consider the SQL statements to be guests in the C
program code which is the host language. Therefore the variables of the C program are called host
variables.
Another way to exchange values between PostgreSQL backends and ECPG applications is the use of
SQL descriptors, described in Section 33.7.
33.4.1. Overview
Passing data between the C program and the SQL statements is particularly simple inembedded SQL.
Instead of having the program paste the data into the statement, which entails various complications,
such as properly quoting the value, you can simply write the name of a C variable into the SQL
statement, prefixed by a colon. For example:
EXEC SQL INSERT INTO sometable VALUES (:v1, ’foo’, :v2);
This statements refers to two C variables named
v1
and
v2
and also uses a regular SQL string literal,
to illustrate that you are not restricted to use one kind of data or the other.
This style of inserting C variables in SQL statements works anywhere a value expression is expected
in an SQL statement.
33.4.2. Declare Sections
To pass data from the program to the database, for example as parameters in a query, or to pass data
from the database back to the program, the C variables that are intended to contain this data need to
be declaredin specially marked sections, so the embeddedSQL preprocessor is made aware of them.
This section starts with:
743
66
Chapter 33. ECPG - Embedded SQL in C
EXEC SQL BEGIN DECLARE SECTION;
and ends with:
EXEC SQL END DECLARE SECTION;
Between those lines, there must be normal C variable declarations, such as:
int
x = 4;
char
foo[16], bar[16];
As you can see, you can optionally assign an initial value to the variable. The variable’s scope is
determinedby the location of its declaring sectionwithinthe program. You canalso declare variables
with the following syntax which implicitly creates a declare section:
EXEC SQL int i = 4;
You can have as many declare sections in a program as youlike.
The declarations are also echoed tothe output file as normalC variables, sothere’s no needto declare
them again. Variables that are not intended to be used in SQL commands can be declared normally
outside these special sections.
The definition of a structure or union also must be listed inside a
DECLARE
section. Otherwise the
preprocessor cannot handle these types since it does not know the definition.
33.4.3. Retrieving Query Results
Now you should be able to pass data generated by your program into an SQL command. But how
do you retrieve the results of a query? For that purpose, embedded SQL provides special variants of
the usual commands
SELECT
and
FETCH
.These commands have a special
INTO
clause that specifies
which host variables the retrieved values are to be stored in.
SELECT
is used for a query that returns
only single row, and
FETCH
is used for a query that returns multiple rows, using a cursor.
Here is an example:
/
*
*
assume this table:
*
CREATE TABLE test1 (a int, b varchar(50));
*
/
EXEC SQL BEGIN DECLARE SECTION;
int v1;
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL SELECT a, b INTO :v1, :v2 FROM test;
So the
INTO
clause appears between the select list and the
FROM
clause. The number of elements in
the select list and the list after
INTO
(also called the target list) must be equal.
Here is an example using the command
FETCH
:
EXEC SQL BEGIN DECLARE SECTION;
int v1;
744
82
Chapter 33. ECPG - Embedded SQL in C
VARCHAR v2;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL DECLARE foo CURSOR FOR SELECT a, b FROM test;
...
do
{
...
EXEC SQL FETCH NEXT FROM foo INTO :v1, :v2;
...
} while (...);
Here the
INTO
clause appears after all the normal clauses.
33.4.4. Type Mapping
When ECPGapplications exchange values between thePostgreSQL server and theC application, such
as when retrieving query results from the server or executing SQL statements with input parameters,
the values need to be converted between PostgreSQL data types and host language variable types
(C language data types, concretely). One of the main points of ECPG is that it takes care of this
automatically in most cases.
In this respect, there are two kinds of data types: Some simple PostgreSQL data types, such as
integer
and
text
,can be read andwritten by the application directly. Other PostgreSQL data types,
such as
timestamp
and
numeric
can only be accessed through special libraryfunctions;see Section
33.4.4.2.
Table 33-1 shows which PostgreSQL data types correspond to which C data types. When you wish
to send or receive a value of a given PostgreSQL data type, you should declare a C variable of the
corresponding C data type in the declare section.
Table 33-1. Mapping Between PostgreSQL Data Types and C Variable Types
PostgreSQL data type
Host variable type
smallint
short
integer
int
bigint
long long int
decimal
decimal
a
numeric
numeric
a
real
float
double precision
double
smallserial
short
serial
int
bigserial
long long int
oid
unsigned int
character(
n
)
,
varchar(
n
)
,
text
char[
n
+1]
,
VARCHAR[
n
+1]
b
name
char[NAMEDATALEN]
745
97
Chapter 33. ECPG - Embedded SQL in C
PostgreSQL data type
Host variable type
timestamp
timestamp
a
interval
interval
a
date
date
a
boolean
bool
c
Notes:
a. This type can only be accessed through special library functions; see Section 33.4.4.2.
b. declaredin
ecpglib.h
c. declaredin
ecpglib.h
if not native
33.4.4.1. Handling Character Strings
To handle SQL character string data types, such as
varchar
and
text
,there are two possible ways
to declare the host variables.
One way is using
char[]
,an array of
char
,which is the mostcommon way to handle character data
in C.
EXEC SQL BEGIN DECLARE SECTION;
char str[50];
EXEC SQL END DECLARE SECTION;
Note that youhavetotakecare of the lengthyourself. If youusethishostvariableas the target variable
of a query which returns a string with more than 49 characters, a buffer overflow occurs.
The other way is using the
VARCHAR
type, which is a special type provided by ECPG. The definition
on anarrayof type
VARCHAR
is converted into anamed
struct
for everyvariable. A declaration like:
VARCHAR var[180];
is converted into:
struct varchar_var { int len; char arr[180]; } var;
The member
arr
hosts the string including a terminating zero byte. Thus, to store a string in a
VARCHAR
host variable, the host variable has to be declared with the length including the zero byte
terminator. The member
len
holds the length of the string stored in the
arr
without the terminating
zero byte. When a host variable is used as input for a query, if
strlen(arr)
and
len
are different,
the shorter one is used.
VARCHAR
can be written in upper or lower case, but not in mixed case.
char
and
VARCHAR
host variables can also hold values of other SQL types, which will be stored in
their string forms.
33.4.4.2. Accessing Special Data Types
ECPG contains some special types that help you to interact easily with some special data types from
the PostgreSQL server. In particular, it has implemented support for the
numeric
,
decimal
,
date
,
timestamp
,and
interval
types. These data types cannot usefully be mapped to primitive host
variable types (such as
int
,
long long int
,or
char[]
), because they have a complex internal
structure. Applications deal withthese types bydeclaringhostvariables in specialtypes andaccessing
them using functions in the pgtypes library. The pgtypes library, described in detail in Section 33.6
746
57
Chapter 33. ECPG - Embedded SQL in C
contains basic functions to deal with those types, such that you do not need to send a query to the
SQL server just for adding an interval to a time stampfor example.
The follow subsections describe these special data types. For more details about pgtypes library func-
tions, see Section 33.6.
33.4.4.2.1. timestamp, date
Here is a pattern for handling
timestamp
variables in the ECPG host application.
First, the program has to include the header file for the
timestamp
type:
#include <pgtypes_timestamp.h>
Next, declare a host variable as type
timestamp
in the declare section:
EXEC SQL BEGIN DECLARE SECTION;
timestamp ts;
EXEC SQL END DECLARE SECTION;
And after reading a value into the host variable, process it using pgtypes library functions.
In following example, the
timestamp
value is converted into text (ASCII) form with the
PGTYPEStimestamp_to_asc()
function:
EXEC SQL SELECT now()::timestamp INTO :ts;
printf("ts = %s\n", PGTYPEStimestamp_to_asc(ts));
This example will showsome result like following:
ts = 2010-06-27 18:03:56.949343
In addition, the DATE type can be handled in the same way. The program has to include
pgtypes_date.h
,declare a host variable as the date type and convert a DATE value into a text
form using
PGTYPESdate_to_asc()
function. For more details about the pgtypes libraryfunctions,
see Section 33.6.
33.4.4.2.2. interval
The handling of the
interval
type is also similar to the
timestamp
and
date
types. It is required,
however, to allocate memory for an
interval
type value explicitly. In other words, the memory
space for the variable has to be allocated in the heap memory, not in the stack memory.
Here is an example program:
#include <stdio.h>
#include <stdlib.h>
#include <pgtypes_interval.h>
int
main(void)
{
EXEC SQL BEGIN DECLARE SECTION;
747
Documents you may be interested
Documents you may be interested
