REF ODBC                                            John Gibson Aug 1996

        COPYRIGHT University of Sussex 1996. All Rights Reserved.

>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<                              >>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<     POPLOG INTERFACE TO      >>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<  OPEN DATABASE CONNECTIVITY  >>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<                              >>>>>>>>>>>>>>>>>>>>>
<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<

         CONTENTS - (Use <ENTER> g to access required sections)

  1   Introduction

  2   Differences from the C Interface
      2.1   Procedures
      2.2   INCLUDE ODBC
      2.3   SQL Data Types
      2.4   Pop Data Types
      2.5   Environment, Connection & Statement Handles

  3   Data Input/Output Modes
      3.1   false (Pop Value Mode)
      3.2   3-Element Vector (Byte Buffer Mode)
            ... Input
            ... Output
      3.3   Procedure (Byte Stream Mode)
            ... Input
            ... Output
      3.4   Special Data Values
            ... NULL Values
            ... Default Parameters in SQL Procedure Calls
      3.5   Extended Fetch and Update
            ... Pop Value mode
            ... Byte Buffer mode
            ... Byte Stream mode
            ... Unfetched Rows

  4   Connecting to a Data Source

  5   Driver/Data Source Information

  6   Driver Options

  7   Preparing SQL Requests

  8   Submitting Requests

  9   Retrieving Results & Result Information

 10   Data Source System Table Information

 11   Terminating Statements & Connections

 12   Asynchronous Statement Processing

 13   Examples
      13.1  Static SQL
      13.2  Ad Hoc Query
            ... Using sql_app_result_set



---------------
1  Introduction
---------------

LIB * ODBC is  a Pop-11  interface to  Microsoft's ODBC  (Open  DataBase
Connectivity), a  C language  API which  allows applications  to  access
database management systems using Structured Query Language (SQL).

Before using any procedures, the library must be loaded with

    uses odbc;

The  include   file  INCLUDE * ODBC defines  various   integer constants
and macros for use with these procedures:

    include odbc.ph;

LIB ODBC  assumes Extension  Level 1  conformance from  all drivers.  It
implements all Core and Level  1 functions, and (currently) all  Level 2
functions except the following:

    SQLParamOptions
    SQLMoreResults
    SQLProcedures
    SQLProcedureColumns

This file describes only the Pop interface to ODBC, and does not attempt
to provide a tutorial  on ODBC in general.  The description of LIB  ODBC
procedures  should   therefore  be   read  in   conjunction  with   ODBC
documentation for the C interface.

In all cases, the  descriptions of the  Pop procedures indicate  which C
functions they call.  Where a Pop  procedure is dependent  on a  Level 2
function, * sql_connect_functions  can  be  used  to  test  whether  the
particular driver supports that function or not.




-----------------------------------
2  Differences from the C Interface
-----------------------------------

2.1  Procedures
---------------
LIB ODBC is  a Pop-paradigm  implementation of the  C interface,  rather
than a literal one. Thus while the Pop procedures are roughly comparable
to the C ones, they are by no means identical.

For this reason,  the Pop procedures  have all-lowercase names  starting
with sql_, e.g.  sql_bind_parameter rather  than SQLBindParameter.  (The
Pop names are also generally  cleaner and more explicit. In  particular,
the names of all  procedures that return data  by creating a result  set
begin with sql_select_.)

Unlike the C  functions they  call, LIB  ODBC procedures  do not  return
status codes: they either return nothing, or (where appropriate)  return
data values directly.

Instead, when a LIB ODBC procedure  calls a C function which returns  an
error (return code  SQL_ERROR), the  Pop procedure will  raise a  mishap
with sys_raise_exception. If  a function returns  SQL_SUCCESS_WITH_INFO,
the Pop procedure will raise a warning and continue. In both cases,  the
exception message text is obtained from SQLError, and the exception  has
an id-string of the form

        odbc-funcname:exsys-odbc-sqlstate

where funcname is the name of  the C function being called and  sqlstate
is the SQLSTATE returned by SQLError.

All other C  return codes are  handled automatically by  LIB ODBC.  (The
only  procedure  which  effectively  hands  on  a  C  status  result  is
sql_fetch,     whose      true/false     result      corresponds      to
SQL_SUCCESS/SQL_NO_DATA_FOUND from SQLFetch.)

The following table summarises  the LIB ODBC  equivalent of each  ODBC C
function:

    C Interface             LIB ODBC
    -----------             --------
    SQLAllocConnect         Called automatically by * sql_connect.

    SQLAllocEnv             * sql_environment if  required,  but  called
                            automatically by * sql_connect.

    SQLAllocStmt            * sql_alloc_statement.

    SQLBindCol              * sql_bind_column.

    SQLBindParameter        * sql_bind_parameter.

    SQLBrowseConnect        * sql_connect.

    SQLCancel               Assigning false to * sql_statement_busy  for
                            a statement.

    SQLColAttributes        * sql_column_attribute.

    SQLColumnPrivileges     * sql_select_column_privileges.

    SQLColumns              * sql_select_columns.

    SQLConnect              * sql_connect.

    SQLDataSources          * sql_data_source_repeater.

    SQLDescribeCol          * sql_describe_column.

    SQLDescribeParam        * sql_describe_parameter.

    SQLDisconnect           * sql_disconnect (or GC destroy action).

    SQLDriverConnect        * sql_connect.

    SQLDrivers              * sql_driver_repeater.

    SQLError                Called  automatically  for  message  strings
                            when an exception is raised.

    SQLExecDirect           * sql_execute with optional SQL string.

    SQLExecute              * sql_execute.

    SQLExtendedFetch        * sql_extended_fetch.

    SQLFetch                * sql_fetch.

    SQLForeignKeys          * sql_select_foreign_keys.

    SQLFreeConnect          * sql_disconnect (or GC destroy action).

    SQLFreeEnv              GC destroy action.

    SQLFreeStmt             * sql_free_statement (or GC destroy action).

    SQLGetConnectOption     * sql_connect_option.

    SQLGetCursorName        * sql_cursor_name.

    SQLGetData              Called  automatically  by  * sql_fetch   and
                            * sql_extended_fetch.

    SQLGetFunctions         * sql_connect_functions.

    SQLGetInfo              * sql_connect_attribute.

    SQLGetStmtOption        * sql_statement_option.

    SQLGetTypeInfo          * sql_select_types.

    SQLNativeSql            * sql_native_sql.

    SQLNumParams            * sql_num_parameters.

    SQLNumResultCols        * sql_num_result_columns.

    SQLParamData            Called automatically  by  * sql_execute  and
                            * sql_set_position when necessary.

    SQLPrepare              * sql_prepare.

    SQLPrimaryKeys          * sql_select_primary_keys.

    SQLPutData              Called automatically  by  * sql_execute  and
                            * sql_set_position when necessary.

    SQLRowCount             * sql_row_count.

    SQLSetConnectOption     * sql_connect_option updater.

    SQLSetCursorName        * sql_cursor_name updater.

    SQLSetStmtOption        * sql_statement_option updater.

    SQLSetPos               * sql_set_position.

    SQLSpecialColumns       * sql_select_special_columns.

    SQLStatistics           * sql_select_statistics.

    SQLTablePrivileges      * sql_select_table_privileges.

    SQLTables               * sql_select_tables.

    SQLTransact             * sql_transact.




2.2  INCLUDE ODBC
-----------------
The file  INCLUDE * ODBC defines  a similar  set of  macros for  integer
constants as the corresponding C header file 'sql.h'.

However, whereas in C all these  macros have names beginning with  SQL_,
in LIB ODBC  some begin with  Sql_. In  all cases, Sql_  values are  the
corresponding SQL_ values shifted left  16 bits, with extra  information
encoded in the bottom part.

(If required, the inline macro SQL(Sql_value) can be applied to an  Sql_
value to get the SQL_ value, e.g.

        SQL(Sql_INTEGER)

is SQL_INTEGER. Note that  in general, INCLUDE  ODBC defines either  the
Sql_ or the SQL_ value, but not both.)

Most  of  the  Sql_  values  are  input  arguments  to  the   procedures
sql_connect_attribute,  sql_connect_option,   sql_statement_option   and
sql_column_attribute, where  they  specify  a  particular  attribute  or
option (and  where  the bottom  16  bits encode  the  data type  of  the
corresponding attribute/option value).


2.3  SQL Data Types
-------------------
ODBC SQL data types describe the represention of data by the driver.  In
LIB ODBC, they are Sql_  values in which the  bottom 16 bits encode  the
precision and  the  scale  of  the type,  and  additionally,  a  bit  to
differentiate between signed and unsigned for integer types.

The LIB ODBC SQL types are listed below, together with the corresponding
default Pop type for each (see next section).

For types whose precision and scale are variable, the Sql_ definition is
not a  single  value,  but  an  inline  macro  that  takes  one  or  two
parameters. These are

        SQL Type                            Default Pop Type
        --------                            ----------------
        Sql_CHAR(nbytes)                    Sql_P_string
        Sql_VARCHAR(max_nbytes)             Sql_P_string
        Sql_BINARY(nbytes)                  Sql_P_binary
        Sql_VARBINARY(max_nbytes)           Sql_P_binary
        Sql_NUMERIC(precision, scale)       Sql_P_string
        Sql_DECIMAL(precision, scale)       Sql_P_string

The non-parameterised types are

        SQL Type                            Default Pop Type
        --------                            ----------------
        Sql_BIT                             Sql_P_boolean
        Sql_TINYINT                         Sql_P_sbyte
        Sql_SMALLINT                        Sql_P_short
        Sql_INTEGER                         Sql_P_int
        Sql_BIGINT                          Sql_P_longlong
        Sql_REAL                            Sql_P_sfloat
        Sql_FLOAT                           Sql_P_dfloat
        Sql_DOUBLE                          Sql_P_dfloat
        Sql_DATE                            Sql_P_date
        Sql_TIME                            Sql_P_time
        Sql_TIMESTAMP                       Sql_P_timestamp
        Sql_LONGVARBINARY                   Sql_P_binary
        Sql_LONGVARCHAR                     Sql_P_string

The types Sql_TINYINT, Sql_SMALLINT, Sql_INTEGER and Sql_BIGINT indicate
signed integers, and each has a corresponding unsigned version:

        SQL Type                            Default Pop Type
        --------                            ----------------
        Sql_UTINYINT                        Sql_P_byte
        Sql_USMALLINT                       Sql_P_ushort
        Sql_UINTEGER                        Sql_P_uint
        Sql_UBIGINT                         Sql_P_ulonglong

The   above   SQL   type   encoding   simplifies   the   arguments    to
sql_bind_parameter,  by   condensing   three   of   the   arguments   to
SQLBindParameter into one (also three SQLDescribeColumn results into one
sql_describe_column result, etc).

The signed/unsigned distinction for  integer types makes it  unnecessary
to specify an  explicit Pop  type to sql_bind_parameter  merely for  the
purpose of indicating signed or unsigned.

Note that  the  parameters  to  types like  Sql_CHAR  are  evaluated  at
compile-time,  and  cannot  be   run-time  expressions.  The   following
procedures may be used to manipulate SQL type descriptors at run-time:


sql_cons_sql_type(stype, precision, scale, unsigned)         [procedure]
                                            -> sql_type
        Constructs a LIB ODBC SQL type descriptor sql_type (an integer).

        stype is the basic  ODBC SQL type; precision  and scale are  the
        precision and scale of the type. precision must range from 0  to
        255, and scale must range from 0 to precision.

        unsigned is a boolean  which if true  indicates that an  integer
        type (i.e.  stype =  SQL_TINYINT, SQL_SMALLINT,  SQL_INTEGER  or
        SQL_BIGINT) should be  considered unsigned  rather than  signed.
        (unsigned is ignored for other types.)

        Note that an sql_type descriptor  for a long data type  (stype =
        SQL_LONGVARCHAR or  SQL_LONGVARBINARY)  does not  represent  the
        maximum number  of bytes  allowed for  the type  (since this  is
        likely to be  a large  number such  as 2**31-1).  Hence in  this
        case, precision is ignored and set to 0 (and scale must be 0).


sql_dest_sql_type(sql_type)                                  [procedure]
                    -> (stype, precision, scale, unsigned)
        Given a  LIB  ODBC SQL  type  descriptor sql_type,  returns  the
        four component fields of the type.


The following  code fragment  illustrates the  use of  sql_dest_sql_type
(and the SQL inline  macro) to perform  a go_on on  the basic type  of a
given sql_type descriptor:

    sql_dest_sql_type(sql_type) -> (stype, precision, scale, unsigned);

    go_on stype to SType:
        SType SQL(Sql_CHAR(1)):
        SType SQL(Sql_VARCHAR(1)):
        SType SQL(Sql_BINARY(1)):
        SType SQL(Sql_VARBINARY(1)):
            <some code>
            goto SType end;

        SType SQL(Sql_TINYINT):
        SType SQL(Sql_SMALLINT):
        SType SQL(Sql_INTEGER):
        SType SQL(Sql_BIGINT):
            <some code>
            goto SType end;

        SType SQL(Sql_REAL):
        SType SQL(Sql_FLOAT):
        SType SQL(Sql_DOUBLE):
        SType SQL(Sql_DECIMAL(1,0)):
        SType SQL(Sql_NUMERIC(1,0)):
            <some code>
    endgo_on;

(Note that for the above, the parameterised Sql_ types can use any legal
parameter values, since the basic stype is not affected.)


2.4  Pop Data Types
-------------------
In the C interface, the ODBC C data types specify the representation  of
data required in the application program; when data is transferred,  the
driver converts  data  between  this representation  and  the  SQL  type
representation. Each SQL type has a corresponding default C type.

In LIB ODBC, the role played by  ODBC C data types is played instead  by
Sql_P_ Pop data types (the  SQL() values of which  are the C types  they
correspond to). Each SQL type has a corresponding default Pop type  (see
previous section).

Unlike the C interface, LIB ODBC provides three different mechanisms for
actually accessing data -- see Data Input/Output Modes below.

The Pop data types are

        Sql_P_boolean
        Sql_P_bit

        Sql_P_sbyte
        Sql_P_byte
        Sql_P_short
        Sql_P_ushort
        Sql_P_int
        Sql_P_uint
        Sql_P_longlong
        Sql_P_ulonglong
        Sql_P_sfloat
        Sql_P_dfloat

        Sql_P_bookmark

        Sql_P_date
        Sql_P_time
        Sql_P_timestamp

        Sql_P_string
        Sql_P_binary

The types Sql_P_sbyte through  Sql_P_dfloat represent storage  locations
as for  a  Pop  record  field  of  the  corresponding  type  (see  Field
Specifiers for Poplog Data in REF * KEYS).

The type  Sql_P_bit is  the same  as Sql_P_byte,  except that  only  the
bottom bit  is  significant; Sql_P_boolean  is  the same  as  Sql_P_bit,
except that the bit  value is converted to/from  a boolean in Pop  value
mode (see Data Input/Output Modes below).

Sql_P_bookmark is the same as Sql_P_uint (note that this is the  default
Pop type for * sql_bind_column on column number 0).

Sql_P_date, Sql_P_time and  Sql_P_timestamp represent storage  locations
for the  i_typespec  structures  sql_date,  sql_time  and  sql_timestamp
defined in INCLUDE * ODBC.

The types Sql_P_string and Sql_P_binary both represent storage locations
for data as a sequence  of bytes. (In the  C interface, these two  types
must be distinguished, because of  null-termination for strings. In  the
Pop interface this does not apply, and the only difference between  them
is that for input to non-LONG parameters (i.e. excluding Sql_LONGVARCHAR
or Sql_LONGVARBINARY parameters), Sql_P_string implies a maximum  length
of 254 bytes rather than 255 for Sql_P_binary.)


2.5  Environment, Connection & Statement Handles
------------------------------------------------
In LIB ODBC,  the C-interface handles  for the environment,  connections
and statements  are packaged  as external-pointer  class records  (whose
contents are otherwise opaque to the user).

Each record is given a garbage  collector destroy action, which will  be
called when the record becomes garbage.

Since there is  only one  environment handle, this  is never  explicitly
required as an argument to any procedure. It is created automatically by
* sql_connect if necessary, but may be explicitly created by referencing
the active variable * sql_environment.




--------------------------
3  Data Input/Output Modes
--------------------------

The DATA_BUFF argument  to sql_bind_parameter specifies  how data  for a
parameter is input when  sql_execute is called.  (It also specifies  how
data is output for an output parameter in an SQL procedure call, i.e. an
SQL call statement that calls a  named executable object defined by  the
data source.)

Similarily, the  DATA_BUFF  argument to  sql_bind_column  specifies  how
data for a column is output when sql_fetch is called.

In both  cases,  DATA_BUFF represents  either  one or  two  arguments: a
single identifier record main_id, optionally  followed by a second  item
mode.

The mode item controls the mode in which data is transferred, and may be

    ¤ false (the default if omitted);
    ¤ a 3-element vector;
    ¤ a procedure.

Alternatively, mode may be another  identifier whose idval will  contain
one of those  three at  the time of  the sql_execute  or sql_fetch.  (In
other words, mode may be supplied as a bind-time constant, or through an
identifier    whose    value    can    change    between    calls     to
sql_execute/sql_fetch.)

The different modes (in order of increasing complexity) are as follows:


3.1  false (Pop Value Mode)
---------------------------
When mode  is false  (or omitted),  the data  value is  simply a  Poplog
object input from, or output to, the idval of main_id.

The Pop types Sql_P_sbyte through Sql_P_dfloat accept as input any  item
that can be assigned into a Pop  record field of that type, and  produce
as output the result of accessing such a field.

An input  value for  Sql_P_bit must  be either  0 or  1, with  the  same
produced on output; for Sql_P_boolean,  the truthvalue of an input  item
is converted to 0 or  1 (0 for false, 1  for anything else), and  output
produces false or true.

The types Sql_P_string and Sql_P_binary both accept or produce a string.

The  Pop  value   corresponding  to  the   Sql_P_date,  Sql_P_time   and
Sql_P_timestamp types is a full vector  whose first element is the  word
"sql_date", "sql_time" or "sql_timestamp", and whose remaining  elements
are integers:

        Type                Vector
        ----                ------
        Sql_P_date          {sql_date year month day}
        Sql_P_time          {sql_time hour minute second}
        Sql_P_timestamp     {sql_timestamp year month day
                                           hour minute second fraction}


3.2  3-Element Vector (Byte Buffer Mode)
----------------------------------------
When mode is a 3-element vector, it specifies a byte buffer in the form

        {start_bsub bytestruct max_nbytes}

where bytestruct is  any structure suitable  for use with  * move_bytes.
The buffer area starts at  byte subscript start_bsub within  bytestruct,
and has a maximum length of max_nbytes bytes.

...  Input
----------
For input parameter  data, you  must set  main_id to  the actual  number
nbytes of data bytes to be transferred before each call to  sql_execute.
The data is then taken from the buffer with

        move_bytes(start_bsub, bytestruct, ..., nbytes);

For all Pop types except Sql_P_string or Sql_P_binary, it is an error if
nbytes is  not the  exact number  of bytes  required for  the type.  For
Sql_P_string/Sql_P_binary, nbytes must be less than or equal to  254/255
unless  the   SQL  type   of  the   parameter  is   Sql_LONGVARCHAR   or
Sql_LONGVARBINARY.

...  Output
-----------
For output column data, each call of sql_fetch will transfer data to the
buffer with

        move_bytes(..., start_bsub, bytestruct, nbytes);

and set main_id  to the number  nbytes of bytes  transferred. (It is  an
error if the buffer is not large enough, i.e. if max_nbytes is less than
nbytes.) Similarily for output parameters from sql_execute.


3.3  Procedure (Byte Stream Mode)
---------------------------------
If mode  is a  procedure, mode  or  its updater  will be  called  inside
sql_execute or sql_fetch to transfer the data.

...  Input
----------
For input parameters, you must set main_id to the total number nbytes of
data bytes to be  transferred before each call  to sql_execute. (If  the
total is not known  in advance you  can set it  false; however, this  is
only allowable if the * sql_connect_attribute Sql_NEED_LONG_DATA_LEN  is
false.)

The updater of mode will then  be called inside sql_execute with  nbytes
and a procedure write_p as arguments:

        -> mode(nbytes, write_p)

The updater of mode must then call the procedure write_p to write chunks
of data as input, i.e.

        write_p(start_bsub, bytestruct, nb)

meaning take  nb  bytes  from  bytestruct  starting  at  byte  subscript
start_bsub (where bytestruct is any structure allowed by  * move_bytes).
The updater of mode may call write_p as many times as necessary to  pass
all the data for the parameter.

...  Output
-----------
For output column data, sql_fetch will  set main_id to the total  number
nbytes of data bytes  (or false if  this is not  known in advance),  and
then call the base procedure of mode with main_id and a procedure read_p
as arguments:

        mode(main_id, read_p)

mode must then  call the procedure  read_p to receive  chunks of  output
data, i.e.

        read_p(start_bsub, bytestruct, max_nb) -> nb

meaning read  at most  max_nb  bytes into  bytestruct starting  at  byte
subscript start_bsub (returning the actual  number of bytes read).  mode
must repeatedly call read_p to fetch all the data for the column; read_p
will return less than max_nb only at end-of-stream, and 0 when the  data
is exhausted.

(Note that mode  being called with  main_id as argument  gives mode  the
option of creating a structure for  the output data and assigning it  to
main_id. This is potentially useful with * sql_app_result_set, where the
main_id   identifiers   are   created   internally   and    dereferenced
automatically after calling sql_fetch.)

The situation is identical for  sql_execute output parameters, i.e.  the
base procedure of mode is called to read the data. (For parameters  that
are both input and output, the updater is called first to handle  input,
then the base procedure to handle output.)


3.4  Special Data Values
------------------------

...  NULL Values
----------------
NULL data values are represented in  the Pop interface by undef  records
(e.g. pop_undef).

To specify  that  the  value  of an  input  parameter  is  NULL,  assign
pop_undef to the value of main_id. The mode argument is then ignored.

When an  output column  or  parameter value  is  NULL, the  system  sets
main_id to pop_undef, and ignores mode.


...  Default Parameters in SQL Procedure Calls
----------------------------------------------
To specify that an SQL procedure  call should use the default value  for
an input parameter, set the value of main_id to the word "default". mode
is then ignored for input.



3.5  Extended Fetch and Update
------------------------------
The Level  2 procedure  sql_extended_fetch allows  multiple rows  to  be
fetched from a result set.  The number of rows  to be fetched is  called
the  rowset  size  (and  is  determined  by  the  * sql_statement_option
Sql_ROWSET_SIZE).

In  addition,  following  a  call  to  sql_extended_fetch,  the  Level 2
procedure sql_set_position may be  called (with operation =  SQL_UPDATE)
to update one or all of the fetched rows. (It may also be called to  add
new rows.)

To cope with multiple rows in  each column, the first DATA_BUFF item  to
sql_bind_column must be a full vector main_vec rather than an identifier
main_id. The R-th element  of main_vec is then  used instead of  main_id
for the R-th row of the rowset.  The length of the vector bound to  each
column must be  at least as  large as the  rowset size (identifiers  may
only be used with sql_extended_fetch if the rowset size is 1).

When main_vec is specified, the mode argument is similar, except that it
now applies to any row in the rowset instead of just one:

...  Pop Value mode
-------------------
The value item for  the R-th row  of the column is  assigned to (or  for
updating, taken from) main_vec(R).

...  Byte Buffer mode
---------------------
When mode  is a  3-element  vector {start_bsub  bytestruct  max_nbytes},
max_nbytes specifies the size of each  row part within the column.  Thus
the data area for the R-th row is assumed to start at

        start_bsub + (R-1)*max_nbytes

The actual number of bytes  nbytes for the R-th  row is assigned to  (or
for updating, taken from) main_vec(R).

...  Byte Stream mode
---------------------
When mode is a procedure, the base procedure is called once for each row
R within the column, with the  total number of bytes nbytes assigned  to
main_vec(R). mode is called with the row number R as an additional first
argument:

        mode(R, main_vec, read_p)

For updating, main_vec(R) must be set to the total number nbytes of data
bytes  to   be   transferred  for   each   row  before   the   call   to
sql_set_position. The updater of mode is  then called once for each  row
within the column with the row number R as an additional first argument:

        -> mode(R, nbytes, write_p)

...  Unfetched Rows
-------------------
When the actual  number of  rows fetched is  less than  the rowset  size
(e.g. at the end of the result  set), main_vec(R) is set to termin  (and
mode ignored) for any rows not  fetched. This also happens for any  rows
for  which   an   error   occurred,   or   which   were   deleted   (see
sql_extended_fetch).

For updating, any row for which main_vec(R) is termin will be ignored.




------------------------------
4  Connecting to a Data Source
------------------------------

sql_environment -> henv                                [active variable]
        This active variable returns  the current environment handle  if
        one  exists,  or  calls  SQLAllocEnv  to  allocate  a  new   one
        otherwise.

        Once created, sql_environment holds  on to the environment  only
        weakly via  a  temporary  property  entry,  which  means  that a
        garbage collection will reclaim it (and call SQLFreeEnv)  unless
        other references to it remain.

        Since the environment is not required as an argument by any  LIB
        ODBC procedure calls, there are  only two reasons for  accessing
        this variable explicitly, viz

          ¤ To force the environment to be created at a particular  time
            (e.g. on program startup -- note that SQLAllocEnv reads  the
            file 'odbc.ini');

          ¤ To save the environment in  your own variable to prevent  it
            being garbaged (you should always do this if you access it).

        Note that  all  connection  and  statement  handles  maintain a
        pointer to the environment, so  merely saving one of these  will
        also prevent the environment being garbaged.


sql_connect(conn_str, changes_p, retries, conn_mode) -> hdbc [procedure]
        Establishes  a  connection  to  a  data  source  and   returns a
        connection handle hdbc.

        This procedure provides  an interface  to both  SQLDriverConnect
        and SQLBrowseConnect (as well as the equivalent of  SQLConnect).
        It  performs   SQLAllocConnect   automatically   (and   accesses
        * sql_environment for the environment handle).

        Any of the  changes_p, retries  and conn_mode  arguments may  be
        omitted;  only  the  conn_str  argument  is  mandatory.   Unless
        conn_mode is  supplied  and  is a  reference  (see  below),  the
        connection is made using SQLDriverConnect.

        conn_str
            This is  the only  required argument,  and may  be a  single
            string, or a 0- to 3-element list of strings:

                [ ]
                [ data_source_name ]
                [ data_source_name user_name ]
                [ data_source_name user_name password ]

            The list elements  are converted  to a  string suitable  for
            SQLDriverConnect/SQLBrowseConnect, i.e.

                ''
                'DSN=data_source_name;'
                'DSN=data_source_name; UID=user_name;'
                'DSN=data_source_name; UID=user_name; PWD=password;'

            If conn_str  is  a single  string,  it is  interpreted  as a
            data_source_name (i.e.  equivalent  to  a  1-element  list),
            unless it contains a semicolon: in this case, it is  assumed
            to be a valid connection string for SQLDriverConnect, and is
            used unchanged.

        changes_p
            An   optional   procedure   argument   which   enables   any
            pre-connection setting of options with * sql_connect_option.
            If supplied, it is called on the newly-allocated  connection
            handle hdbc before making the actual connection, i.e.

                    changes_p(hdbc)

            (Note that this is only time user code is given access to an
            unconnected connection handle.)

        retries
            An optional  integer  retry count  for  connection  attempts
            (defaults to 5  if omitted). Connection  attempts that  fail
            because of communication-link  errors will  be retried  this
            many times.

        conn_mode
            This  optional  argument  is   either  a  pair   (specifying
            additional arguments for  SQLDriverConnect), or a  reference
            containing a  procedure  (specifying  that  SQLBrowseConnect
            should be used instead of SQLDriverConnect).

            If conn_mode  is a  pair,  the front  must contain  an  item
            suitable for the window handle argument to SQLDriverConnect,
            and the back  an integer  for the  'driver completion  flag'
            argument.   The    latter    is    one    of    the    codes
            SQL_DRIVER_NOPROMPT, SQL_DRIVER_COMPLETE,  SQL_DRIVER_PROMPT
            or SQL_DRIVER_COMPLETE_REQUIRED (defined in INCLUDE * ODBC),
            and defaults to SQL_DRIVER_NOPROMPT if conn_mode is omitted.

            To specify that SQLBrowseConnect  should be used,  conn_mode
            must be reference containing a procedure, i.e.

                    consref(browse_p)

            where browse_p is a procedure of the form

                browse_p(result_string, sqlstate_or_false)
                                            -> next_conn_string

            SQLBrowseConnect is  then called  repeatedly (starting  with
            the initial  conn_str  argument)  until  the  connection  is
            established. Each time it returns SQL_NEED_DATA  (indicating
            that more connection information  is required), browse_p  is
            called to supply the additional information.

            The two arguments  passed to  browse_p each  time depend  on
            whether the previous SQLBrowseConnect call had a recoverable
            error or not. If there was an error, the second argument  is
            the SQLSTATE string for the error, and result_string is  the
            associated error message obtained from SQLError.  Otherwise,
            the second  argument  is  false, and  result_string  is  the
            browse result connection string.

            In  either  case,  browse_p   must  return  an   appropriate
            next_conn_string  as   argument  for   the  next   call   to
            SQLBrowseConnect.




---------------------------------
5  Driver/Data Source Information
---------------------------------

sql_data_source_repeater() -> ds_rep                         [procedure]
        Returns  a   repeater   procedure   ds_rep   for   data   source
        specifications from the 'odbc.ini' file or registry.

        Each time ds_rep is called, it uses SQLDataSources to fetch  the
        next data source specification  as a pair  of strings, the  data
        source name and its associated driver description. That is,

            ds_rep() -> pair_or_termin

        where each pair has the form

            conspair(data_source_name, driver_description)

        ds_rep returns termin at end of stream.


sql_driver_repeater() -> dr_rep                              [procedure]
        Returns a repeater  procedure dr_rep  for driver  specifications
        from the odbc.ini file.

        Each time dr_rep is called, it uses SQLDrivers to fetch the next
        driver specification as a list of strings. The first element  of
        the list is the driver  description, and any remaining  elements
        are attributes in the form 'keyword=value'. That is,

            dr_rep() -> list_or_termin

        where each list has the form

            [ driver_description attribute1 attribute2 ... ]

        dr_rep returns termin at end of stream.


sql_connect_attribute(hdbc, conn_attr) -> value              [procedure]
        Calls SQLGetInfo to return the value of a specified attribute of
        the driver/data source associated with the connection hdbc.

        conn_attr specifies the attribute, and  must be one of the  Sql_
        descriptors listed below (defined in INCLUDE * ODBC).

        The table below indicates  the data type  of the value  returned
        for each attribute. Note that

          ¤ type code means one of  a mutually exclusive set of  integer
            values defined  as  SQL_  constants by  INCLUDE  ODBC  (e.g.
            Sql_IDENTIFIER_CASE    returns    one    of    SQL_IC_UPPER,
            SQL_IC_LOWER, SQL_IC_SENSITIVE or SQL_IC_MIXED);

          ¤ type bitmask means an  integer OR'ed from  a set of  bitmask
            values defined  as  SQL_  constants by  INCLUDE  ODBC  (e.g.
            Sql_ALTER_TABLE   has   zero   or    more   of   the    bits
            SQL_AT_ADD_COLUMN, SQL_AT_DROP_COLUMN set).

          ¤ attributes for  which SQLGetInfo  returns a  Y/N string  are
            returned as booleans (true/false);

        See SQLGetInfo for a description of each attribute.

            conn_attr                           Type
            ---------                           ----
            Sql_ACCESSIBLE_PROCEDURES           boolean
            Sql_ACCESSIBLE_TABLES               boolean
            Sql_ACTIVE_CONNECTIONS              integer
            Sql_ACTIVE_STATEMENTS               integer
            Sql_ALTER_TABLE                     bitmask
            Sql_BOOKMARK_PERSISTENCE            bitmask
            Sql_COLUMN_ALIAS                    boolean
            Sql_CONCAT_NULL_BEHAVIOR            code
            Sql_CONVERT_BIGINT                  bitmask
            Sql_CONVERT_BINARY                  bitmask
            Sql_CONVERT_BIT                     bitmask
            Sql_CONVERT_CHAR                    bitmask
            Sql_CONVERT_DATE                    bitmask
            Sql_CONVERT_DECIMAL                 bitmask
            Sql_CONVERT_DOUBLE                  bitmask
            Sql_CONVERT_FLOAT                   bitmask
            Sql_CONVERT_FUNCTIONS               bitmask
            Sql_CONVERT_INTEGER                 bitmask
            Sql_CONVERT_LONGVARBINARY           bitmask
            Sql_CONVERT_LONGVARCHAR             bitmask
            Sql_CONVERT_NUMERIC                 bitmask
            Sql_CONVERT_REAL                    bitmask
            Sql_CONVERT_SMALLINT                bitmask
            Sql_CONVERT_TIME                    bitmask
            Sql_CONVERT_TIMESTAMP               bitmask
            Sql_CONVERT_TINYINT                 bitmask
            Sql_CONVERT_VARBINARY               bitmask
            Sql_CONVERT_VARCHAR                 bitmask
            Sql_CORRELATION_NAME                code
            Sql_CURSOR_COMMIT_BEHAVIOR          code
            Sql_CURSOR_ROLLBACK_BEHAVIOR        code
            Sql_DATA_SOURCE_NAME                string
            Sql_DATA_SOURCE_READ_ONLY           boolean
            Sql_DBMS_NAME                       string
            Sql_DBMS_VER                        string
            Sql_DEFAULT_TXN_ISOLATION           code
            Sql_DRIVER_NAME                     string
            Sql_DRIVER_ODBC_VER                 string
            Sql_DRIVER_VER                      string
            Sql_EXPRESSIONS_IN_ORDERBY          boolean
            Sql_FETCH_DIRECTION                 bitmask
            Sql_FILE_USAGE                      code
            Sql_GETDATA_EXTENSIONS              bitmask
            Sql_GROUP_BY                        code
            Sql_IDENTIFIER_CASE                 code
            Sql_IDENTIFIER_QUOTE_CHAR           string
            Sql_KEYWORDS                        string
            Sql_LIKE_ESCAPE_CLAUSE              boolean
            Sql_LOCK_TYPES                      bitmask
            Sql_MAX_BINARY_LITERAL_LEN          integer
            Sql_MAX_CHAR_LITERAL_LEN            integer
            Sql_MAX_COLUMNS_IN_GROUP_BY         integer
            Sql_MAX_COLUMNS_IN_INDEX            integer
            Sql_MAX_COLUMNS_IN_ORDER_BY         integer
            Sql_MAX_COLUMNS_IN_SELECT           integer
            Sql_MAX_COLUMNS_IN_TABLE            integer
            Sql_MAX_COLUMN_NAME_LEN             integer
            Sql_MAX_CURSOR_NAME_LEN             integer
            Sql_MAX_INDEX_SIZE                  integer
            Sql_MAX_OWNER_NAME_LEN              integer
            Sql_MAX_PROCEDURE_NAME_LEN          integer
            Sql_MAX_QUALIFIER_NAME_LEN          integer
            Sql_MAX_ROW_SIZE                    integer
            Sql_MAX_ROW_SIZE_INCLUDES_LONG      boolean
            Sql_MAX_STATEMENT_LEN               integer
            Sql_MAX_TABLES_IN_SELECT            integer
            Sql_MAX_TABLE_NAME_LEN              integer
            Sql_MAX_USER_NAME_LEN               integer
            Sql_MULTIPLE_ACTIVE_TXN             boolean
            Sql_MULT_RESULT_SETS                boolean
            Sql_NEED_LONG_DATA_LEN              boolean
            Sql_NON_NULLABLE_COLUMNS            boolean
            Sql_NULL_COLLATION                  code
            Sql_NUMERIC_FUNCTIONS               bitmask
            Sql_ODBC_API_CONFORMANCE            code
            Sql_ODBC_SAG_CLI_CONFORMANCE        code
            Sql_ODBC_SQL_CONFORMANCE            code
            Sql_ODBC_SQL_OPT_IEF                boolean
            Sql_ODBC_VER                        string
            Sql_OJ_CAPABILITIES                 bitmask
            Sql_ORDER_BY_COLUMNS_IN_SELECT      boolean
            Sql_OUTER_JOINS                     boolean
            Sql_OWNER_TERM                      string
            Sql_OWNER_USAGE                     bitmask
            Sql_POSITIONED_STATEMENTS           bitmask
            Sql_POS_OPERATIONS                  bitmask
            Sql_PROCEDURES                      boolean
            Sql_PROCEDURE_TERM                  string
            Sql_QUALIFIER_LOCATION              code
            Sql_QUALIFIER_NAME_SEPARATOR        string
            Sql_QUALIFIER_TERM                  string
            Sql_QUALIFIER_USAGE                 bitmask
            Sql_QUOTED_IDENTIFIER_CASE          code
            Sql_ROW_UPDATES                     boolean
            Sql_SCROLL_CONCURRENCY              bitmask
            Sql_SCROLL_OPTIONS                  bitmask
            Sql_SEARCH_PATTERN_ESCAPE           string
            Sql_SERVER_NAME                     string
            Sql_SPECIAL_CHARACTERS              string
            Sql_STATIC_SENSITIVITY              bitmask
            Sql_STRING_FUNCTIONS                bitmask
            Sql_SUBQUERIES                      bitmask
            Sql_SYSTEM_FUNCTIONS                bitmask
            Sql_TABLE_TERM                      string
            Sql_TIMEDATE_ADD_INTERVALS          bitmask
            Sql_TIMEDATE_DIFF_INTERVALS         bitmask
            Sql_TIMEDATE_FUNCTIONS              bitmask
            Sql_TXN_CAPABLE                     code
            Sql_TXN_ISOLATION_OPTION            bitmask
            Sql_UNION                           bitmask
            Sql_USER_NAME                       string


sql_connect_functions(hdbc) -> bitmask                       [procedure]
sql_connect_functions(hdbc, func_number) -> bool
        Calls  SQLGetFunctions  to  return  information  on  which  ODBC
        functions are supported by the driver on the connection hdbc.

        Each function is identified by a number from 1 to 100; constants
        for these numbers have  names of the form  SQL_API_functionname.
        Note that INCLUDE * ODBC defines these names only for  Extension
        Level 2 functions, since LIB ODBC assumes Level 1  functionality
        is always present.

        In the first  form, sql_connect_functions calls  SQLGetFunctions
        to get information  about all functions,  and returns this  as a
        biginteger bitmask in which bit N is set if the driver  supports
        function number N. bitmask can therefore be used with

            testbit(bitmask, func_number)

        to test for  an individual func_number.  (Note that the  bitmask
        result is  cached  inside  hdbc  so  that  subsequent  calls  to
        sql_connect_functions do not have to reconstruct it.)

        In the second form, sql_connect_functions returns a boolean bool
        pertaining to the individual function given by func_number.


sql_select_types(hstmt)                                      [procedure]
sql_select_types(hstmt, sql_type)
        Calls SQLGetTypeInfo to create a result set describing the  data
        types supported by a  data source. hstmt  is a statement  handle
        for the result set.

        If sql_type  is  supplied,  it  must be  a  LIB  ODBC  SQL  type
        descriptor (see SQL Data Types above); in this case, the  result
        set has rows only for  those data source types which  correspond
        to sql_type. If sql_type is omitted, all possible data types are
        enumerated.

        See SQLGetTypeInfo for details of the columns in each row of the
        result set.




-----------------
6  Driver Options
-----------------

sql_connect_option(hdbc, conn_option) -> value               [procedure]
value -> sql_connect_option(hdbc, conn_option)
        Calls  SQLGetConnectOption/SQLSetConnectOption   to  access   or
        update  the  value  of  a  specified  option  setting  for   the
        connection hdbc.

        conn_option specifies the option,  and must be  one of the  Sql_
        descriptors listed below  (and defined  in INCLUDE * ODBC).  For
        the updater only, conn_option  may also be  any valid option  to
        sql_statement_option (i.e.  to set  a statement  option for  all
        subsequently-created statements on hdbc).

        The table below indicates  the data type  of the value  returned
        for each option.  Note that type  code means one  of a  mutually
        exclusive set of  integer values  defined as  SQL_ constants  by
        INCLUDE   ODBC    (e.g.   Sql_ACCESS_MODE    returns   one    of
        SQL_MODE_READ_WRITE or SQL_MODE_READ_ONLY).

        Certain options  also have  a default  value defined  for  them:
        these are shown in the table. For any option whose value has not
        previously been set, sql_connect_option will return the  default
        if the option has one,  or pop_undef otherwise. In addition,  an
        option with  a default  value  may be  set  to that  default  by
        assigning the word "default" to the updater.

        See SQLSetConnectOption for a description of each option.

            conn_option             Type        Default
            -----------             ----        -------
            Sql_ACCESS_MODE         code        SQL_MODE_READ_WRITE
            Sql_AUTOCOMMIT          boolean     true
            Sql_CURRENT_QUALIFIER   string
            Sql_LOGIN_TIMEOUT       integer     15
            Sql_ODBC_CURSORS        code        SQL_CUR_USE_DRIVER
            Sql_OPT_TRACE           boolean     false
            Sql_OPT_TRACEFILE       string
            Sql_PACKET_SIZE         integer
            Sql_TRANSLATE_DLL       string
            Sql_TRANSLATE_OPTION    integer
            Sql_TXN_ISOLATION       code


sql_statement_option(hstmt, stmt_option) -> value            [procedure]
value -> sql_statement_option(hstmt, stmt_option)
        Calls SQLGetStmtOption/SQLSetStmtOption to access or update  the
        value of a specified option setting for the statement hstmt.

        stmt_option specifies the option,  and must be  one of the  Sql_
        descriptors listed below (and defined in INCLUDE * ODBC).

        The table below indicates  the data type  of the value  returned
        for each option.  Note that type  code means one  of a  mutually
        exclusive set of  integer values  defined as  SQL_ constants  by
        INCLUDE   ODBC    (e.g.   Sql_ACCESS_MODE    returns   one    of
        SQL_MODE_READ_WRITE or SQL_MODE_READ_ONLY).

        Certain options  also have  a default  value defined  for  them:
        these are shown in the table. For any option whose value has not
        previously  been  set,  sql_statement_option  will  return   the
        default if  the  option  has one,  or  pop_undef  otherwise.  In
        addition, an option  with a  default value  may be  set to  that
        default by assigning the word "default" to the updater.

        Note that the Sql_ASYNC_ENABLE option is special insofar as  its
        value is  false  or  a  procedure.  See  Asynchronous  Statement
        Processing below.

        See SQLSetStmtOption for a description of each option.

            stmt_option             Type        Default
            -----------             ----        -------
            Sql_ASYNC_ENABLE        boolean     false
            Sql_CONCURRENCY         code        SQL_CONCUR_READ_ONLY
            Sql_CURSOR_TYPE         code        SQL_CURSOR_FORWARD_ONLY
            Sql_KEYSET_SIZE         integer     0
            Sql_MAX_LENGTH          integer     0
            Sql_MAX_ROWS            integer     0
            Sql_NOSCAN              boolean     false
            Sql_QUERY_TIMEOUT       integer     0
            Sql_RETRIEVE_DATA       boolean     true
            Sql_ROWSET_SIZE         integer     1
            Sql_SIMULATE_CURSOR     code
            Sql_USE_BOOKMARKS       boolean     false

        The following options  are read-only (i.e.  may be accessed  but
        not set, see SQLGetStmtOption):

            Sql_GET_BOOKMARK        integer
            Sql_ROW_NUMBER          integer

        There is also the following option, which is special to LIB ODBC
        (and is  used by  * sql_extended_fetch when  Sql_ROWSET_SIZE  is
        greater than 1):

            Sql_LONG_DATA_MAX       integer     2047




-------------------------
7  Preparing SQL Requests
-------------------------

sql_alloc_statement(hdbc) -> hstmt                           [procedure]
        Calls SQLAllocStmt to allocate a  new statement handle hstmt  on
        the connection hdbc.


sql_prepare(hstmt, sql_string)                               [procedure]
        Calls SQLPrepare  to  prepare  the  SQL  string  sql_string  for
        execution on the statement hstmt. All parameters and columns  on
        hstmt are unbound first.


sql_num_parameters(hstmt) -> num_parameters                  [procedure]
        (Extension Level  2) Returns  the number  of parameters  in  the
        prepared statement hstmt (by calling SQLNumParams).


sql_describe_parameter(hstmt, N) -> (sql_type, nullable)     [procedure]
sql_describe_parameter(hstmt, N, unsigned)
                                 -> (sql_type, nullable)
        (Extension Level 2) Returns  the LIB ODBC SQL  type of the  N-th
        parameter  in   the  prepared   statement  hstmt   (by   calling
        SQLDescribeParam).

        The  nullable  result  is   one  of  the  values   SQL_NO_NULLS,
        SQL_NULLABLE     or     SQL_NULLABLE_UNKNOWN     (defined     in
        INCLUDE * ODBC).

        Because SQLDescribeParam  does  not provide  any  indication  of
        whether whether  an  integer type  is  signed or  unsigned,  the
        optional boolean  argument unsigned  may  be supplied:  this  is
        passed as  the last  argument  to * sql_cons_sql_type  when  the
        sql_type result is constructed (and defaults to false).



sql_bind_parameter(hstmt, N, DATA_BUFF, sql_type)            [procedure]
sql_bind_parameter(hstmt, N, DATA_BUFF, sql_type, pop_type)
sql_bind_parameter(hstmt, N, DATA_BUFF, sql_type, pop_type,
                                                param_type)
        Binds a  buffer for  the N-th  parameter marker  in a  statement
        hstmt (where N >= 1). This procedure calls SQLBindParameter.

        DATA_BUFF specifies the buffer mechanism, and represents  either
        one or two arguments. These  are described fully in the  section
        Data Input/Output Modes above.

        sql_type specifies the LIB ODBC  SQL type of the parameter,  and
        must be one of the Sql_ types listed under SQL Data Types above.

        pop_type is  an optional  Pop data  type for  the parameter;  if
        supplied, it must be  one of the Sql_P_  types listed under  Pop
        Data Types above.  If pop_type  is omitted, it  defaults to  the
        default Pop type for sql_type (see SQL Data Types).

        param_type is an optional parameter type, and must be one of the
        constants     SQL_PARAM_INPUT,     SQL_PARAM_INPUT_OUTPUT     or
        SQL_PARAM_OUTPUT  defined  in  INCLUDE * ODBC.  If  omitted,  it
        defaults  to  SQL_PARAM_INPUT.  (Note  that  if  param_type   is
        supplied, pop_type is mandatory; however, in this case  pop_type
        may be false to mean use the default Pop type for sql_type.)

        The form

                sql_bind_parameter(hstmt, "*", false);

        may also be used to unbind all parameters on hstmt (there is  no
        mechanism for unbinding a single parameter).


sql_cursor_name(hstmt) -> string_or_false                    [procedure]
string_or_false -> sql_cursor_name(hstmt)
        Returns or updates the  name of the  cursor associated with  the
        statement hstmt.

        The base procedure  call SQLGetCursorName to  access the  cursor
        name. false is returned if there is no open cursor on hstmt (and
        the updater has not been called to set an explicit name).

        If the updater is given  a string, it calls SQLSetCursorName  to
        associate a cursor name with hstmt (this may only be done before
        the cursor is opened, i.e. before the statement is executed).

        If the updater  is given  false, it calls  SQLFreeStmt with  the
        SQL_CLOSE option to close the cursor. (However, note that if  an
        explicit cursor name has been set, this remains associated  with
        hstmt even  though  there is  no  open cursor;  hence  the  base
        procedure will continue to return that name rather than false.)




----------------------
8  Submitting Requests
----------------------

sql_execute(hstmt)                                           [procedure]
sql_execute(hstmt, sql_string)
        In the first form, calls SQLExecute to execute an SQL  statement
        hstmt already prepared with  * sql_prepare. In the second  form,
        calls SQLExecDirect to execute the SQL statement sql_string.

        In both cases, the current  values of any parameters bound  with
        * sql_bind_parameter are  used for  the corresponding  parameter
        markers in the statement.


sql_native_sql(hdbc, sql_string) -> native_sql_string        [procedure]
        (Extension  Level  2)  Given  a  connection  hdbc,  returns  the
        driver's translation of the SQL statement string sql_string.




------------------------------------------
9  Retrieving Results & Result Information
------------------------------------------

sql_row_count(hstmt) -> row_count                            [procedure]
        Calls SQLRowCount to return  the number of  rows affected by  an
        UPDATE, INSERT or DELETE statement previously executed on  hstmt
        (or alternatively,  by  an SQL_UPDATE,  SQL_ADD,  or  SQL_DELETE
        operation with * sql_set_position).


sql_num_result_columns(hstmt) -> num_columns                 [procedure]
        Calls SQLNumResultCols to  return the number  of columns in  the
        result set associated with the (prepared or executed)  statement
        hstmt.

        0 is returned  if hstmt  is a  statement that  did not  create a
        result set.


sql_describe_column(hstmt, N) -> (name, sql_type, nullable)  [procedure]
        Returns the column name and LIB ODBC SQL type of the N-th column
        in  the  result   set  on  the   statement  hstmt  (by   calling
        SQLDescribeCol).

        The  nullable  result  is   one  of  the  values   SQL_NO_NULLS,
        SQL_NULLABLE     or     SQL_NULLABLE_UNKNOWN     (defined     in
        INCLUDE * ODBC).

        For integer types,  this procedure calls  * sql_column_attribute
        to determine the  Sql_COLUMN_UNSIGNED attribute  of the  column,
        which is then included in the sql_type result.


sql_column_attribute(hstmt, N, col_attr) -> value            [procedure]
        Calls SQLColAttributes  to  return  the  value  of  a  specified
        attribute of the N-th column in a result set.

        col_attr specifies the attribute,  and must be  one of the  Sql_
        descriptors listed below (and defined in INCLUDE * ODBC).

        The table below indicates  the data type  of the value  returned
        for each attribute. Note that type code means one of a  mutually
        exclusive set of  integer values  defined as  SQL_ constants  by
        INCLUDE  ODBC   (e.g.   Sql_COLUMN_NULLABLE   returns   one   of
        SQL_NO_NULLS, SQL_NULLABLE or SQL_NULLABLE_UNKNOWN).

        See SQLColAttributes for a description of each attribute.

            col_attr                        Type
            --------                        ----
            Sql_COLUMN_AUTO_INCREMENT       boolean
            Sql_COLUMN_CASE_SENSITIVE       boolean
            Sql_COLUMN_COUNT                integer
            Sql_COLUMN_DISPLAY_SIZE         integer
            Sql_COLUMN_LABEL                string
            Sql_COLUMN_LENGTH               integer
            Sql_COLUMN_MONEY                boolean
            Sql_COLUMN_NAME                 string
            Sql_COLUMN_NULLABLE             code
            Sql_COLUMN_OWNER_NAME           string
            Sql_COLUMN_PRECISION            integer
            Sql_COLUMN_QUALIFIER_NAME       string
            Sql_COLUMN_SCALE                integer
            Sql_COLUMN_SEARCHABLE           code
            Sql_COLUMN_TABLE_NAME           string
            Sql_COLUMN_TYPE                 integer
            Sql_COLUMN_TYPE_NAME            string
            Sql_COLUMN_UNSIGNED             boolean
            Sql_COLUMN_UPDATABLE            code


sql_bind_column(hstmt, N, DATA_BUFF)                         [procedure]
sql_bind_column(hstmt, N, DATA_BUFF, pop_type)
        Binds a buffer  for the  N-th column of  the result  set on  the
        statement hstmt (where N >= 0).

        DATA_BUFF specifies the buffer mechanism, and represents  either
        one or two arguments. These  are described fully in the  section
        Data Input/Output Modes above.

        pop_type is  an  optional  Pop  data type  for  the  column;  if
        supplied, it must be  one of the Sql_P_  types listed under  Pop
        Data Types above.  If pop_type  is omitted, it  defaults to  the
        default Pop type for  the SQL type of  the column (see SQL  Data
        Types). For N = 0  (i.e. binding the bookmark column),  pop_type
        defaults to Sql_P_bookmark.

        The forms

                sql_bind_column(hstmt, N, false);
                sql_bind_column(hstmt, "*", false);

        can also be used to unbind either a single column or all columns
        on hstmt.

        (N.B.  In  general,  sql_bind_column  does  not  actually   call
        SQLBindCol -- this is because sql_fetch uses SQLGetData to fetch
        the data  for  all  columns.  Hence any  errors  that  might  be
        expected from SQLBindCol  will instead  occur during  sql_fetch.
        The same is true for * sql_extended_fetch with a rowset size  of
        1; however, sql_extended_fetch with a rowset size greater than 1
        does call SQLBindCol.)


sql_fetch(hstmt) -> data_found                               [procedure]
        Calls SQLFetch to fetch the next row of data from the result set
        on the statement hstmt.

        If a row was fetched,  any columns bound with  * sql_bind_column
        have data transferred to their buffers (by calls to SQLGetData),
        and true is returned.

        false is returned when there are no rows left.


sql_app_result_set(hstmt, col_select, app_p)                 [procedure]
sql_app_result_set(hstmt, col_select, init_p, app_p)
        A convenience procedure that enables processing of a result  set
        without explicit  use  of  sql_bind_column  and  sql_fetch.  The
        procedure app_p is applied to every row of the result set on the
        statement hstmt.

        The  col_select  argument  selects  the  column  numbers  to  be
        processed,     which     can     range     between     1     and
        sql_num_result_columns(hstmt) inclusive. col_select is a list of
        column specifiers,

                [ col_spec1 col_spec2 ... col_specM ]

        where each  col_spec  is  either  a  col_num_spec  or  a  2-  or
        3-element full vector whose first element is a col_num_spec:

                { col_num_spec 1_or_2_bind_args }

        A col_num_spec is either

              ¤ a single column number;

              ¤ a 2-element list  [first_col last_col], meaning  columns
                first_col to last_col inclusive;

              ¤ the word "*", meaning all remaining columns.

        (Note   that   column   numbers   outside   the   range   1   to
        sql_num_result_columns(hstmt) are ignored.)

        Every column  selected is  bound with  sql_bind_column. In  each
        case, the main_id  identifier for  the first  DATA_BUFF item  to
        sql_bind_column is generated automatically; if the col_spec that
        selected a column is a vector, the remaining one or two elements
        of  col_spec  are   passed  as  additional   arguments  to   the
        sql_bind_column  call  for  that  column.  These  elements  thus
        represent  the  mode  part  of  DATA_BUFF  and/or  the  pop_type
        arguments to sql_bind_column.

        For example, the col_select list

                [ 1 [3 5] {[7 10] ^Sql_P_int} {* ^Sql_P_string} ]

        selects columns 1,  3-5, 7-10  with pop_type  Sql_P_int, and  11
        onwards with pop_type Sql_P_string. (With all columns  returning
        data in Pop value mode.)

        sql_fetch is then called  to fetch each row  of the result  set.
        For each row,  app_p is  applied to the  set of  values for  the
        selected columns, together with the number of columns  selected.
        That is, if column numbers c1, ..., cN are selected, then  app_p
        is called as

            app_p(c1_val, c2_val, ..., cN_val, N);

        where each cI_val  is the  idval of the  main_id identifier  for
        that column.

        init_p Argument
        The optional  procedure  argument  init_p  allows  some  initial
        processing to be  done for  each selected  column. If  supplied,
        then before fetching any  rows, init_p is  called once for  each
        column c1,  ..., cN,  with hstmt  and the  column number  cI  as
        arguments:

                init_p(hstmt, cI) -> cI_results

        Any results returned by init_p for  column cI will be saved  and
        added as additional  (constant) arguments  following the  cI_val
        argument to every call of app_p.  That is, app_p will be  called
        as

            app_p(c1_val, c1_results, c2_val, c2_results, ...,
                                        cN_val, cN_results, nargs);

        where nargs is the total number of arguments.

        In addition, init_p is called an extra (N+1)-th time with  false
        for the column number (which call must not return any  results).
        This allows any final processing  to take place before  fetching
        the row data.


sql_extended_fetch(hstmt, fetch_type, row_num,               [procedure]
                        row_status_vec)  -> nrows_or_false
        (Extension Level 2)  Calls SQLExtendedFetch  to fetch  specified
        row(s) from the result set on the statement hstmt.

        The number of rows to be fetched is called the rowset size,  and
        is determined by the * sql_statement_option Sql_ROWSET_SIZE.

        Binding Buffers
        For each row fetched,  any columns bound with  * sql_bind_column
        have data transferred to  their buffers. If  the rowset size  is
        greater than 1, the DATA_BUFF arguments to sql_bind_column  must
        have supplied  full vectors  of appropriate  length rather  than
        identifiers -- see Extended Fetch and Update above.

        With a  rowset  size  of 1,  sql_extended_fetch  uses  the  same
        mechanism as  sql_fetch  for fetching  data  (i.e.  SQLGetData).
        However, with a rowset  size greater than  1, this mechanism  is
        problematical  and   so   (currently),   the   first   call   of
        sql_extended_fetch on an hstmt will cause all columns to  become
        bound  with   SQLBindCol   instead.  The   latter   necessitates
        allocating an internal buffer of sufficient size to hold all the
        data for each column, and forces sql_extended_fetch to  impose a
        limit on columns with LONG data types (i.e. Sql_LONGVARCHAR  and
        Sql_LONGVARBINARY). This  limit  is  specified  by  the  special
        * sql_statement_option  Sql_LONG_DATA_MAX,  and  represents  the
        maximum number of bytes that should be allowed for a LONG column
        of data  in a  single row.  Sql_LONG_DATA_MAX defaults  to  2047
        bytes.

        (Note: The row-wise binding facility of SQLExtendedFetch is  not
        supported by sql_extended_fetch.)

        fetch_type and row_num Arguments
        The position  of the  rowset fetched  within the  result set  is
        determined by  the  fetch_type  argument, either  alone,  or  in
        conjunction with  the current  cursor  position or  the  row_num
        argument, as shown below. (After a rowset is fetched, the cursor
        is positioned on the first row of the rowset.)

            fetch_type              Action
            ----------              ------
            SQL_FETCH_NEXT          Fetches  the  next  rowset.  If  the
                                    cursor  is  positioned  before   the
                                    start  of   the   result   set   (as
                                    initially),  this  is  the  same  as
                                    SQL_FETCH_FIRST.

            SQL_FETCH_PRIOR         Fetches the previous rowset. If  the
                                    cursor is positioned  after the  end
                                    of the result set, this is the  same
                                    as SQL_FETCH_LAST.

            SQL_FETCH_RELATIVE      Fetches the rowset row_num rows from
                                    the start of the current rowset;  if
                                    row_num is 0, the current rowset  is
                                    re-fetched.   If   the   cursor   is
                                    positioned before the  start of  the
                                    result set  and row_num  is  greater
                                    than  0,   or  if   the  cursor   is
                                    positioned  after  the  end  of  the
                                    result set and row_num is less  than
                                    0,   this    is    the    same    as
                                    SQL_FETCH_ABSOLUTE.

            SQL_FETCH_FIRST         Fetches  the  first  rowset  in  the
                                    result set.

            SQL_FETCH_LAST          Fetches the last complete rowset  in
                                    the result set.

            SQL_FETCH_ABSOLUTE      If  row_num  is   greater  than   0,
                                    fetches the rowset  starting at  row
                                    row_num; if row_num equals 0,  false
                                    is  returned  (and  the  cursor   is
                                    positioned before the  start of  the
                                    result set); if row_num is less than
                                    0, fetches  the rowset  starting  at
                                    row L+row_num+1, where L is the last
                                    row in the result set.

            SQL_FETCH_BOOKMARK      Fetches the rowset starting with the
                                    row whose bookmark is row_num.

        The row_num argument is ignored  for those values of  fetch_type
        that do  not require  it, and  may be  omitted in  these  cases;
        fetch_type itself may  be omitted (together  with row_num)  when
        the fetch type required is SQL_FETCH_NEXT.

        row_status_vec Argument
        The row_status_vec argument is optional; if supplied, it must be
        a full vector  of length  greater than  or equal  to the  rowset
        size. The elements  of this vector  are then set  to one of  the
        following integer status values for each row fetched:

                SQL_ROW_SUCCESS
                SQL_ROW_DELETED
                SQL_ROW_UPDATED
                SQL_ROW_ADDED
                SQL_ROW_ERROR

        Any remaining elements of row_status_vec for unfetched rows  (up
        to the rowset size) are  set to SQL_ROW_NOROW. (As mentioned  in
        Extended Fetch and Update above, rows with status SQL_ROW_NOROW,
        SQL_ROW_DELETED or  SQL_ROW_ERROR  have  their  main_id/main_vec
        column buffer entries set to termin.)

        Note that  row_status_vec  is saved  inside  hstmt so  that  its
        elements   may   be   modified   by   a   subsequent   call   to
        * sql_set_position.

        Result
        sql_extended_fetch returns the actual number of rows fetched, or
        false at end-of-stream in either direction.


sql_set_position(hstmt, row_num, operation, lock)            [procedure]
        (Extension Level 2) Following a call to * sql_extended_fetch  to
        fetch a rowset from  the statement hstmt, sql_set_position  sets
        the cursor position within the rowset, and allows an application
        to refresh,  update, delete  or add  data to  the rowset.  (This
        procedure calls SQLSetPos.)

        The row_num argument specifies the row number within the  rowset
        on which to  perform the  operation, or  is 0  if the  operation
        should be performed on  all rows in the  rowset (0 is not  valid
        for operation = SQL_POSITION).  Except for operation =  SQL_ADD,
        row_num must be less than or equal to the rowset size.

        The operation argument specifies the operation to be  performed,
        as follows:

            operation       Action
            ---------       ------
            SQL_POSITION    Sets the cursor on row number row_num.

            SQL_REFRESH     If row_num is  nonzero, sets  the cursor  on
                            row number row_num, and re-fetches the  data
                            for that row;  if row_num  is 0,  re-fetches
                            the data  for all  rows in  the rowset  (and
                            leaves the cursor position unchanged).

            SQL_UPDATE      If row_num is  nonzero, sets  the cursor  on
                            row number row_num, and then updates the row
                            from the column  buffers; if  row_num is  0,
                            updates all rows in  the rowset (and  leaves
                            the cursor  position unchanged).  Note  that
                            columns   whose   main_id/main_vec    buffer
                            entries for  the updated  row(s) are  termin
                            will be  ignored  (see  Extended  Fetch  and
                            Update above).

            SQL_DELETE      If row_num is  nonzero, sets  the cursor  on
                            row number  row_num,  and then  deletes  the
                            underlying row; if row_num is 0, deletes all
                            rows in the  rowset (and  leaves the  cursor
                            position unchanged).

            SQL_ADD         If row_num  is nonzero,  adds a  new row  of
                            data to the  data source,  using the  column
                            buffer entries  for  row_num (which  may  be
                            greater than the rowset size, providing  all
                            main_vec  buffers   have   a   corresponding
                            element). If row_num is 0, adds  rowset-size
                            new  rows.  Unbound   columns  (or   columns
                            containing  termin  for  a  row)  will   use
                            default or NULL values in the new row(s).

        The operation argument may be omitted provided the lock argument
        is also; in this case,  operation defaults to SQL_POSITION  (and
        lock to SQL_LOCK_NO_CHANGE).

        Note that if a row_status_vec was supplied to the preceding call
        of sql_extended_fetch,  all operations  except SQL_POSITION  may
        modify the elements of row_status_vec to reflect the new  status
        of row(s) affected by the operation.

        The lock argument is  optional; if supplied, it  must be one  of
        SQL_LOCK_NO_CHANGE   (the   default),   SQL_LOCK_EXCLUSIVE    or
        SQL_LOCK_UNLOCK. See SQLSetPos for more details.




----------------------------------------
10  Data Source System Table Information
----------------------------------------

sql_select_columns(hstmt, tab_qual, tab_owner, tab_name,     [procedure]
                                                col_name)
        Calls SQLColumns to create a  result set on the statement  hstmt
        describing the columns in specified tables on the data source.

        tab_qual, tab_owner, tab_name and col_name are respectively  the
        table qualifier name, table owner  name, table name, and  column
        name arguments to SQLColumns, and may each be a string or  false
        (the latter meaning pass a NULL pointer for the argument).

        See SQLColumns for more details.


sql_select_special_columns(hstmt, col_type, tab_qual,        [procedure]
                    tab_owner, tab_name, scope, nullable)
        Calls SQLSpecialColumns to create a result set on the  statement
        hstmt describing special columns in specified tables on the data
        source.

        col_type must be either SQL_BEST_ROWID or SQL_ROWVER.

        tab_qual, tab_owner  and  tab_name are  respectively  the  table
        qualifier name, table  owner name, and  table name arguments  to
        SQLSpecialColumns, and may each be a string or false (the latter
        meaning pass a NULL pointer for the argument).

        scope  is  one  of  SQL_SCOPE_CURROW,  SQL_SCOPE_TRANSACTION  or
        SQL_SCOPE_SESSION;   nullable   is   either   SQL_NO_NULLS    or
        SQL_NULLABLE.

        See SQLSpecialColumns for more details.


sql_select_statistics(hstmt, tab_qual, tab_owner, tab_name,  [procedure]
                                        unique, accuracy)
        Calls SQLStatistics  to create  a result  set on  the  statement
        hstmt describing statistics  and indexes for  a single table  on
        the data source.

        tab_qual, tab_owner,  and tab_name  are respectively  the  table
        qualifier name, table  owner name, and  table name arguments  to
        SQLStatistics. tab_qual and tab_owner may  be a string or  false
        (the latter  meaning  pass a  NULL  pointer for  the  argument);
        tab_name must be string.

        unique is either SQL_INDEX_UNIQUE or SQL_INDEX_ALL; accuracy  is
        either SQL_ENSURE or SQL_QUICK.

        See SQLStatistics for more details.


sql_select_tables(hstmt, tab_qual, tab_owner, tab_name,      [procedure]
                                                tab_type)
        Calls SQLTables to create  a result set  on the statement  hstmt
        listing the tables available on the data source.

        tab_qual, tab_owner, tab_name and tab_type are respectively  the
        table qualifier name,  table owner name,  table name, and  table
        type arguments to SQLTables, and may  each be a string or  false
        (the latter meaning pass a NULL pointer for the argument).

        See SQLTables for more details.


sql_select_column_privileges(hstmt, tab_qual, tab_owner,    [procedure]
                                    tab_name, col_name)
        (Extension Level 2) Calls SQLColumnPrivileges to create a result
        set on the statement hstmt describing the associated  privileges
        of columns in specified tables on the data source.

        tab_qual, tab_owner, tab_name and col_name are respectively  the
        table qualifier name, table owner  name, table name, and  column
        name arguments to SQLColumnPrivileges, and may each be a  string
        or false  (the  latter  meaning  pass a  NULL  pointer  for  the
        argument).

        See SQLColumnPrivileges for more details.


sql_select_table_privileges(hstmt, tab_qual, tab_owner,      [procedure]
                                                tab_name)
        (Extension Level 2) Calls SQLTablePrivileges to create a  result
        set on  the  statement hstmt  describing  privilege  information
        about tables on the data source.

        tab_qual, tab_owner  and  tab_name are  respectively  the  table
        qualifier name, table  owner name  and table  name arguments  to
        SQLTablePrivileges, and  may  each be  a  string or  false  (the
        latter meaning pass a NULL pointer for the argument).

        See SQLTablePrivileges for more details.


sql_select_primary_keys(hstmt, tab_qual, tab_owner,          [procedure]
                                                tab_name)
        (Extension Level 2) Calls SQLPrimaryKeys to create a result  set
        on the statement  hstmt listing the  column names that  comprise
        the primary key for a specified table.

        tab_qual, tab_owner,  and tab_name  are respectively  the  table
        qualifier name, table  owner name, and  table name arguments  to
        SQLPrimaryKeys . tab_qual and tab_owner may be a string or false
        (the latter  meaning  pass a  NULL  pointer for  the  argument);
        tab_name must be string.

        See SQLPrimaryKeys for more details.


sql_select_foreign_keys(hstmt,                               [procedure]
                        pktab_qual, pktab_owner, pktab_name,
                        fktab_qual, fktab_owner, fktab_name)
        (Extension Level 2) Calls SQLForeignKeys to create a result  set
        on the  statement  hstmt listing  foreign  keys in  a  specified
        table.

        pktab_qual, pktab_owner  and  pktab_name  are  respectively  the
        primary key table  qualifier name,  table owner  name and  table
        name arguments  to SQLForeignKeys;  fktab_qual, fktab_owner  and
        fktab_name are  respectively  the foreign  key  table  qualifier
        name,  table   owner   name   and  table   name   arguments   to
        SQLForeignKeys.

        All these arguments may be a string or false (the latter meaning
        pass a  NULL  pointer  for the  argument),  except  that  either
        pktab_name or fktab_name (or both) must be a string.

        See SQLForeignKeys for more details.




----------------------------------------
11  Terminating Statements & Connections
----------------------------------------

sql_transact(hdbc)                                           [procedure]
sql_transact(hdbc, commit)
        Calls SQLTransact to commit or rollback all active operations on
        all statements associated with the connection hdbc.

        The optional boolean argument commit  is true for commit,  false
        for rollback, and defaults to true if omitted.

        The form

                sql_transact(false, commit);

        may also be used to  perform a commit/rollback operation on  all
        current connections.

        Note that  the * sql_connect_option  SQL_AUTOCOMMIT is  true  by
        default on any  connection, and  causes all  transactions to  be
        committed automatically. See SQLTransact for further details.


sql_free_statement(hstmt)                                    [procedure]
        Calls  SQLFreeStmt  (with  operation   SQL_DROP)  to  free   the
        statement hstmt  and  release all  associated  resources.  (This
        procedure is the * sys_process_destroy_action of every hstmt.)


sql_disconnect(hdbc)                                         [procedure]
        Calls SQLDisconnect to close the connection hdbc and release all
        associated     resources.     (This     procedure     is     the
        * sys_process_destroy_action of every hdbc.)




-------------------------------------
12  Asynchronous Statement Processing
-------------------------------------

Providing a given  driver supports  it, the  Sql_ASYNC_ENABLE option  to
* sql_statement_option enables  processing  on  a  statement  to  happen
asynchronously.

In this mode, calls to certain ODBC C functions (such as SQLExecute  and
SQLFetch) initiate  an operation  on  a statement  and then  return  the
status code  SQL_STILL_EXECUTING.  The  function  must  then  be  called
repeatedly to  determine whether  processing  has finished  (i.e.  until
something other than SQL_STILL_EXECUTING is returned).

The corresponding LIB ODBC procedures handle this by repeatedly  calling
the underlying  C function  in a  loop, with  a 'wait'  procedure  being
called each time the function returns SQL_STILL_EXECUTING.

The wait procedure used is the value of the Sql_ASYNC_ENABLE option  for
the hstmt.  If true  is  assigned to  the  option value,  the  procedure
defaults to syssleep(%1%), but any suitable procedure can be used.

In particular,  either  an individual  LIB  ODBC procedure  or  a  whole
application can be  run as a  process (see REF * PROCESS),  with a  wait
procedure that suspends the process. A timer can then resume the process
at appropriate intervals, or (more efficiently), use  sql_statement_busy
to test  if the  statement has  finished executing  before resuming  the
process.


sql_statement_busy(hstmt) -> busy                            [procedure]
false -> sql_statement_busy(hstmt)
        The base procedure returns true if the statement hstmt is  busy,
        i.e. has an asynchronous operation  still in progress. false  is
        returned otherwise.

        The  updater  is  an  interface  to  SQLCancel,  and  allows  an
        asynchronous operation  on hstmt  to be  cancelled by  assigning
        false (any other value is ignored).


The LIB ODBC procedures that can  call a wait procedure if  asynchronous
processing has been enabled on their hstmt argument are listed below.

    * sql_app_result_set
    * sql_column_attribute
    * sql_describe_column
    * sql_describe_parameter
    * sql_execute
    * sql_extended_fetch
    * sql_fetch
    * sql_num_parameters
    * sql_num_result_columns
    * sql_prepare
    * sql_select_column_privileges
    * sql_select_columns
    * sql_select_foreign_keys
    * sql_select_primary_keys
    * sql_select_special_columns
    * sql_select_statistics
    * sql_select_table_privileges
    * sql_select_tables
    * sql_select_types
    * sql_set_position




------------
13  Examples
------------

The following two examples are Pop-11 translations of the the C examples
given in Chapter 10 of ODBC SDK Programmer's Reference.


13.1  Static SQL
----------------

include odbc.ph;

define example1(conn_str);
    lvars   ID, Name, hdbc, hstmt;

    ;;; Connect to conn_str (which may be a string in DriverConnect
    ;;; format, or a list [^server ^user ^password] etc).

    sql_connect(conn_str) -> hdbc;

    ;;; allocate a statement handle

    sql_alloc_statement(hdbc) -> hstmt;

    ;;; EXEC SQL CREATE TABLE NAMEID (ID integer, NAME varchar(50));
    ;;; Execute the SQL statement

    sql_execute(hstmt,
                  'CREATE TABLE NAMEID (ID INTEGER, NAME VARCHAR(50))');

    ;;; EXEC SQL COMMIT WORK;
    ;;; Commit the table creation.
    ;;; (Note that the default transaction mode is auto-commit
    ;;; and sql_transact has no effect.)

    sql_transact(hdbc);

    ;;; EXEC SQL INSERT INTO NAMEID VALUES (:id, :name);
    ;;; Show the use of the sql_prepare/sql_execute method:
    ;;; Prepare the insertion and bind parameters;
    ;;; Assign parameter values;

    sql_prepare(hstmt, 'INSERT INTO NAMEID VALUES (?, ?)');
    sql_bind_parameter(hstmt, 1, ident ID,   Sql_INTEGER);
    sql_bind_parameter(hstmt, 2, ident Name, Sql_VARCHAR(50));

    ;;; Execute the insertions.

    (500, 'Babbage') -> (ID, Name);
    sql_execute(hstmt);
    (600, 'Turing') -> (ID, Name);
    sql_execute(hstmt);

    ;;; EXEC SQL COMMIT WORK;
    ;;; Commit the insertions

    sql_transact(hdbc);

    ;;; EXEC SQL DECLARE c1 CURSOR FOR SELECT ID, NAME FROM NAMEID;
    ;;; EXEC SQL OPEN c1;
    ;;; Show the use of the sql_execute direct method.
    ;;; Execute the selection.
    ;;; (Note that the application does not declare a cursor name.)

    sql_execute(hstmt, 'SELECT ID, NAME FROM NAMEID');

    ;;; EXEC SQL FETCH c1 INTO :id, :name;
    ;;; Bind the columns of the result set with sql_bind_column.
    ;;; Fetch and print all rows.

    sql_bind_column(hstmt, 1, ident ID);
    sql_bind_column(hstmt, 2, ident Name);

    while sql_fetch(hstmt) do
        [ID ^ID Name ^Name] ==>
    endwhile;

    sql_transact(hdbc);
    sql_disconnect(hdbc);
enddefine;


13.2  Ad Hoc Query
------------------

include odbc.ph;

define example2(conn_str, sql_string);
    lvars   nresultcols, rowcount, N, id, name, dsize, col_list, col,
            hdbc, hstmt;

    sql_connect(conn_str) -> hdbc;
    sql_alloc_statement(hdbc) -> hstmt;

    ;;; Execute the given SQL statement

    sql_execute(hstmt, sql_string);

    ;;; See what kind of statement it was. If there are no result
    ;;; columns, the statement is not a SELECT. If the number of
    ;;; affected rows is greater than 0, the statement was probably an
    ;;; UPDATE, INSERT or DELETE, so print the number of affected rows.
    ;;; If the  number of affected rows is 0, print that the operation
    ;;; was successful and commit it.

    sql_num_result_columns(hstmt) -> nresultcols;

    if nresultcols == 0 then
        sql_row_count(hstmt) -> rowcount;
        if rowcount == 0 then
            printf('%p rows affected.\n', [^rowcount])
        else
            printf('Operation successful.\n')
        endif;

    ;;; Otherwise, use sql_column_attribute to get and display the
    ;;; column names of the result set, and also to find the display
    ;;; size needed for each column. Then bind each column (specifying
    ;;; that all data should be converted to string format), and make a
    ;;; list of columns in which each element contains the id and the
    ;;; display size for that column. Finally, fetch and print each row.

    else
        [%  for N to nresultcols do
                sql_column_attribute(hstmt, N, Sql_COLUMN_NAME) -> name;
                sql_column_attribute(hstmt, N, Sql_COLUMN_DISPLAY_SIZE)
                                                    -> dsize;
                max(dsize, datalength(name)) + 1 -> dsize;
                pr_field(name, dsize, false, `\s`);
                consident(0, 0, "lex") -> id;
                sql_bind_column(hstmt, N, id, Sql_P_string);
                [^id ^dsize]
            endfor
        %] -> col_list;
        cucharout(`\n`);

        while sql_fetch(hstmt) do
            for col in col_list do
                dl(col) -> (id, dsize);
                pr_field(idval(id), dsize, false, `\s`);
            endfor;
            cucharout(`\n`)
        endwhile
    endif;

    sql_transact(hdbc);
    sql_disconnect(hdbc)
enddefine;


...  Using sql_app_result_set
-----------------------------
Another version of example2, but using * sql_app_result_set.


define example2(conn_str, sql_string);
    lvars   nresultcols, rowcount, hdbc, hstmt;

    sql_connect(conn_str) -> hdbc;
    sql_alloc_statement(hdbc) -> hstmt;
    sql_execute(hstmt, sql_string);
    sql_num_result_columns(hstmt) -> nresultcols;

    if nresultcols == 0 then
        sql_row_count(hstmt) -> rowcount;
        if rowcount == 0 then
            printf('%p rows affected.\n', [^rowcount])
        else
            printf('Operation successful.\n')
        endif;

    else
        define init_col(hstmt, N);
            lvars name, dsize;
            if N then
                sql_column_attribute(hstmt, N, Sql_COLUMN_NAME) -> name;
                sql_column_attribute(hstmt, N, Sql_COLUMN_DISPLAY_SIZE)
                                                -> dsize;
                max(dsize, datalength(name)) + 1 -> dsize;
                pr_field(name, dsize, false, `\s`);
                dsize;      ;;; return dsize
            else
                cucharout(`\n`)
            endif
        enddefine;

        ;;; For each row, this procedure will be given nresultcols * 2
        ;;; arguments, the column value and dsize for each column
        define pr_col(nargs);
            lvars i;
            for i from nargs by -2 to 2 do
                pr_field(subscr_stack(i), subscr_stack(i), false, `\s`)
            endfor;
            erasenum(nargs);
            cucharout(`\n`)
        enddefine;

        sql_app_result_set(hstmt, [{* ^Sql_P_string}], init_col,
                                                            pr_col);
    endif;

    sql_transact(hdbc);
    sql_disconnect(hdbc)
enddefine;




+-+ C.all/ref/odbc
+-+ Copyright University of Sussex 1996. All rights reserved.

SourceForge.net Logo