Sophie

Sophie

distrib > Mandriva > 2010.0 > i586 > media > contrib-release > by-pkgid > 415a9558429d583fc9b2617edd1d24a6 > files > 188

postgresql8.3-docs-8.3.8-1mdv2010.0.i586.rpm

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>Using Host Variables</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.8 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="ECPG - Embedded SQL in C"
HREF="ecpg.html"><LINK
REL="PREVIOUS"
TITLE="Choosing a Connection"
HREF="ecpg-set-connection.html"><LINK
REL="NEXT"
TITLE="Dynamic SQL"
HREF="ecpg-dynamic.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-09-04T05:13:45"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.8 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="ecpg-set-connection.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="ecpg.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 32. <SPAN
CLASS="APPLICATION"
>ECPG</SPAN
> - Embedded <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
> in C</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="ecpg.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="ecpg-dynamic.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="ECPG-VARIABLES"
>32.6. Using Host Variables</A
></H1
><P
>   In <A
HREF="ecpg-commands.html"
>Section 32.4</A
> 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
   <I
CLASS="FIRSTTERM"
>host variables</I
>. In an embedded SQL program  we
   consider the SQL statements to be <I
CLASS="FIRSTTERM"
>guests</I
> in the C
   program code which is the <I
CLASS="FIRSTTERM"
>host language</I
>. Therefore
   the variables of the C program are called <I
CLASS="FIRSTTERM"
>host
   variables</I
>.
  </P
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN33407"
>32.6.1. Overview</A
></H2
><P
>    Passing data between the C program and the SQL statements is
    particularly simple in embedded 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:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL INSERT INTO sometable VALUES (:v1, 'foo', :v2);</PRE
><P>
    This statements refers to two C variables named
    <TT
CLASS="VARNAME"
>v1</TT
> and <TT
CLASS="VARNAME"
>v2</TT
> and also uses a
    regular SQL string literal, to illustrate that you are not
    restricted to use one kind of data or the other.
   </P
><P
>    This style of inserting C variables in SQL statements works
    anywhere a value expression is expected in an SQL statement.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN33414"
>32.6.2. Declare Sections</A
></H2
><P
>    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 declared in specially marked sections, so the
    embedded SQL preprocessor is made aware of them.
   </P
><P
>    This section starts with:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;</PRE
><P>
    and ends with:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL END DECLARE SECTION;</PRE
><P>
    Between those lines, there must be normal C variable declarations,
    such as:
</P><PRE
CLASS="PROGRAMLISTING"
>int   x = 4;
char  foo[16], bar[16];</PRE
><P>
    As you can see, you can optionally assign an initial value to the variable.
    The variable's scope is determined by the location of its declaring
    section within the program.
    You can also declare variables with the following syntax which implicitly
    creates a declare section:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL int i = 4;</PRE
><P>
    You can have as many declare sections in a program as you like.
   </P
><P
>    The declarations are also echoed to the output file as normal C
    variables, so there's no need to declare them again.  Variables
    that are not intended to be used in SQL commands can be declared
    normally outside these special sections.
   </P
><P
>    The definition of a structure or union also must be listed inside
    a <TT
CLASS="LITERAL"
>DECLARE</TT
> section. Otherwise the preprocessor cannot
    handle these types since it does not know the definition.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN33425"
>32.6.3. Different types of host variables</A
></H2
><P
>    As a host variable you can also use arrays, typedefs, structs and
    pointers. Moreover there are special types of host variables that exist
    only in ECPG.
   </P
><P
>    A few examples on host variables:
    <P
></P
></P><DIV
CLASS="VARIABLELIST"
><DL
><DT
>Arrays</DT
><DD
><P
>       One of the most common uses of an array declaration is probably the
       allocation of a char array as in:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
    char str[50];
EXEC SQL END DECLARE SECTION;</PRE
><P>
       Note that you have to take care of the length for yourself. If you use
       this host variable as the target variable of a query which returns a
       string with more than 49 characters, a buffer overflow occurs.
      </P
></DD
><DT
>Typedefs</DT
><DD
><P
>      Use the <TT
CLASS="LITERAL"
>typedef</TT
> keyword to map new types to already
      existing types.
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
    typedef char mychartype[40];
    typedef long serial_t;
EXEC SQL END DECLARE SECTION;</PRE
><P>
       Note that you could also use:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL TYPE serial_t IS long;</PRE
><P>
       This declaration does not need to be part of a declare section.
      </P
></DD
><DT
>Pointers</DT
><DD
><P
>       You can declare pointers to the most common types. Note however that
       you cannot use pointers as target variables of queries without
       auto-allocation. See <A
HREF="ecpg-descriptors.html"
>Section 32.10</A
> for more
       information on auto-allocation.
      </P
><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
    int   *intp;
    char **charp;
EXEC SQL END DECLARE SECTION;</PRE
></DD
><DT
>Special types of variables</DT
><DD
><P
>        ECPG contains some special types that help you to interact easily with
        data from the SQL server. For example it has implemented support for
        the <TT
CLASS="TYPE"
>varchar</TT
>, <TT
CLASS="TYPE"
>numeric</TT
>, <TT
CLASS="TYPE"
>date</TT
>, <TT
CLASS="TYPE"
>timestamp</TT
>, and <TT
CLASS="TYPE"
>interval</TT
> types.
        <A
HREF="ecpg-pgtypes.html"
>Section 32.8</A
> 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 timestamp for example.
       </P
><P
>        The special type <TT
CLASS="TYPE"
>VARCHAR</TT
> 
        is converted into a named <TT
CLASS="TYPE"
>struct</TT
> for every variable. A
        declaration like:
</P><PRE
CLASS="PROGRAMLISTING"
>VARCHAR var[180];</PRE
><P>
         is converted into:
</P><PRE
CLASS="PROGRAMLISTING"
>struct varchar_var { int len; char arr[180]; } var;</PRE
><P>
        This structure is suitable for interfacing with SQL datums of type
        <TT
CLASS="TYPE"
>varchar</TT
>.
       </P
></DD
></DL
></DIV
><P>
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN33464"
>32.6.4. <TT
CLASS="COMMAND"
>SELECT INTO</TT
> and <TT
CLASS="COMMAND"
>FETCH INTO</TT
></A
></H2
><P
>    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 <TT
CLASS="COMMAND"
>SELECT</TT
> and
    <TT
CLASS="COMMAND"
>FETCH</TT
>.  These commands have a special
    <TT
CLASS="LITERAL"
>INTO</TT
> clause that specifies which host variables
    the retrieved values are to be stored in.
   </P
><P
>    Here is an example:
</P><PRE
CLASS="PROGRAMLISTING"
>/*
 * 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;</PRE
><P>
    So the <TT
CLASS="LITERAL"
>INTO</TT
> clause appears between the select
    list and the <TT
CLASS="LITERAL"
>FROM</TT
> clause.  The number of
    elements in the select list and the list after
    <TT
CLASS="LITERAL"
>INTO</TT
> (also called the target list) must be
    equal.
   </P
><P
>    Here is an example using the command <TT
CLASS="COMMAND"
>FETCH</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
int v1;
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 (...);</PRE
><P>
    Here the <TT
CLASS="LITERAL"
>INTO</TT
> clause appears after all the
    normal clauses.
   </P
><P
>    Both of these methods only allow retrieving one row at a time.  If
    you need to process result sets that potentially contain more than
    one row, you need to use a cursor, as shown in the second example.
   </P
></DIV
><DIV
CLASS="SECT2"
><H2
CLASS="SECT2"
><A
NAME="AEN33482"
>32.6.5. Indicators</A
></H2
><P
>    The examples above do not handle null values.  In fact, the
    retrieval examples will raise an error if they fetch a null value
    from the database.  To be able to pass null values to the database
    or retrieve null values from the database, you need to append a
    second host variable specification to each host variable that
    contains data.  This second host variable is called the
    <I
CLASS="FIRSTTERM"
>indicator</I
> and contains a flag that tells
    whether the datum is null, in which case the value of the real
    host variable is ignored.  Here is an example that handles the
    retrieval of null values correctly:
</P><PRE
CLASS="PROGRAMLISTING"
>EXEC SQL BEGIN DECLARE SECTION;
VARCHAR val;
int val_ind;
EXEC SQL END DECLARE SECTION:

 ...

EXEC SQL SELECT b INTO :val :val_ind FROM test1;</PRE
><P>
    The indicator variable <TT
CLASS="VARNAME"
>val_ind</TT
> will be zero if
    the value was not null, and it will be negative if the value was
    null.
   </P
><P
>    The indicator has another function: if the indicator value is
    positive, it means that the value is not null, but it was
    truncated when it was stored in the host variable.
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="ecpg-set-connection.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="ecpg-dynamic.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Choosing a Connection</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="ecpg.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Dynamic SQL</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>

Perl :: Catalyst :: PostgreSQL :: RPM Valid HTML 4.01 Transitional