app-cli

Languages - Definitions - Manual Pages

Previous Chapter: Appendix - Special Issues

Next Chapter: Appendix - Previous version

C Client Program Interface

Users who wish to make a C SQL client library and use C as API, must refer to the readme file in libsrc directory. See Files for the location of this directory.

The SQLCLIENT prototype for C and C++

/* SQLCLI.H */ #ifndef _SQLCLI_H #define _SQLCLI_H #include "machine.h" #include "bigint.h" #ifndef C_DEFINED #define C_DEFINED typedef enum { c_ok=0, c_conn_failed=1, c_trans_failed=2, c_auth_failed } c_outcome; #endif #ifndef BOOL_DEFINED # define BOOL_DEFINED typedef int bool; enum { false, true }; #endif #ifndef SQLTRUTH_DEFINED # define SQLTRUTH_DEFINED typedef int sqltruth; enum { unknown=2 }; #endif /* Definition of connection and statement handles */ #ifndef SQLH_DEFINED #define SQLH_DEFINED typedef struct _sql_handle *SQLH; #endif #ifndef SQLS_DEFINED #define SQLS_DEFINED typedef struct _sqls_handle *SQLS; #endif

/* The following definitions are used for connection and statement options. */ #ifndef OPTIONS_DEFINED #define OPTIONS_DEFINED enum { OPTION_ASYNC, OPTION_SEND_BOOKMARKS, OPTION_ISOLATION /* others ...*/ }; #endif /* OPTION_ASYNC A statement may be either synchronous or asynchronous, i.e. it may wait on SQL statement execution (blocking request) or set the request and continue, without waiting for an answer (non-blocking request). This option may also be set for the entire connection, in which case all sessions belonging to this connection "inherit" by default the option, unless explicitly stated otherwise. Connections and statements are by default synchronous, unless the option is set. OPTION_SEND_BOOKMARKS A statement may set bookmarks on every row fetched. This option complies with the ODBC requirements, it is however very space-consuming. OPTION_ISOLATION The isolation level of the statement. See below. */ #ifndef ISOLEVEL_DEFINED #define ISOLEVEL_DEFINED enum { ISO_READ_UNCOMMITTED=1, ISO_READ_COMMITTED=2, ISO_REPEATABLE_READ=3, ISO_SERIALIZABLE=4 }; #endif /* The state of a returned row in a cursor*/ #ifndef ROW_STATES_DEFINED #define ROW_STATES_DEFINED enum { ROW_OK, ROW_NONEXISTENT, ROW_ERROR }; #endif #ifndef RET_CODES_DEFINED #define RET_CODES_DEFINED enum { RET_OK, RET_STILL_EXEC, RET_ERROR }; #endif

/*Data types */ /* The supported types by Ovrimos. The numbers and definitions are copied from ODBC for compatibility. The underlying implementation of each type is presented in the right as a comment. */ #define T_CHAR 1 /* String */ #define T_VARCHAR 12 /* String */ #define T_LONGVARCHAR (-1) /* BLOB (Special handling) */ #define T_DECIMAL 3 /* double */ #define T_NUMERIC 2 /* double */ #define T_SMALLINT 5 /* sint16 */ #define T_INTEGER 4 /* sint32 */ #define T_REAL 7 /* float */ #define T_FLOAT 6 /* double */ #define T_DOUBLE 8 /* double */ #define T_BIT (-7) /* uint8 */ #define T_TINYINT (-6) /* sint8 */ #define T_BIGINT (-5) /* sint64 */ #define T_BINARY (-2) /* Raw bytes */ #define T_VARBINARY (-3) /* Bytes, prefixed by sint16 length */ #define T_LONGVARBINARY (-4) /* BLOB (Special handling) */ #define T_DATE 9 /* uint32 */ #define T_TIME 10 /* uint32 */ #define T_TIMESTAMP 11 /* 2x uint32 */ #define T_USMALLINT 20 /* uint16 */ #define T_UINTEGER 21 /* uint32 */ #define T_UTINYINT 22 /* uint8 */ #define T_UBIGINT 23 /* uint64 */ #define T_UNI_CHAR (-95) /* Unicode string */ #define T_UNI_VARCHAR (-96) /* Unicode string */ int type_size(int type_id); int type_overhead(int type);

bool sqlConnect(const char *server, const char *db, const char *username,const char *passwd, SQLH *session);
c_outcome sqlConnectOutcome();
bool sqlDisConnect (SQLH);
bool sqlAllocStmt (SQLH, SQLS*);
bool sqlFreeStmt (SQLS);

bool sqlSetConnIntOption (SQLH, unit16 option, unit32 value);
bool sqlGetConnIntOption (SQLH, unit16 option, unit32 *value);
bool sqlSetStmtIntOption (SQLS, unit16 option, unit32 value);
bool sqlGetStmtIntOption (SQLS, unit16 option, unit32 *value);
bool sqlSetRowsetSize (SQLS, unit32 sz);
bool sqlGetRowsetSize (SQLS, unit32 *psz);

bool sqlExecDirect (SQLS, const char *cmd);
bool sqlPrepare (SQLS, const char *cmd);
bool sqlExec (SQLS);
bool sqlBulkAdd (SQLS, const char *cmd);
bool sqlBulkExec (SQLS);
bool sqlCall (SQLS, const char *cmd);
bool sqlCloseCursor (SQLS);
bool sqlAsyncFinished (SQLS, int *retcode);
bool sqlCancel (SQLS);
bool sqlSetCursorName (SQLS, const char *);
bool sqlGetCursorName (SQLS, char *);

bool sqlNest (SQLS);
bool sqlCommit (SQLS);
bool sqlRollback (SQLS);

int sqlGetConnPending (SQLH);
int sqlGetStmtPending (SQLS);

bool sqlGetConnDiagnostics (SQLH, unit16 maxlen, char *buffer);
bool sqlGetStmtDiagnostics (SQLS, unit16 maxlen, char *buffer);

bool sqlGetExecutionPlan (SQLS, char *buffer);
bool sqlGetNativeQuery (SQLS, char *buffer);
bool sqlGetRowCount (SQLS, uint32 *p);
bool sqlGetOutputColDescr (SQLS);
int sqlGetOutputColNb (SQLS);
const char *sqlGetOutputColName (SQLS, int);
int sqlGetOutputColType (SQLS, int);
int sqlGetOutputColLength (SQLS, int);
int sqlGetOutputColPrecision (SQLS, int);
int sqlGetOutoutColScale (SQLS, int);
int sqlGetOutputColNullable (SQLS, int);

bool sqlGetParamDescr (SQLS);
int sqlGetParamNb (SQLS);
int sqlGetParamType (SQLS, int);
int sqlGetParamLength (SQLS, int);
int sqlGetParamPrecision (SQLS, int);
int sqlGetParamScale (SQLS, int);

bool sqlPutParam (SQLS, int iparam, const char *, int sz);
bool sqlResetParams (SQLS);

bool sqlCursorThis (SQLS);
bool sqlCursorFirst (SQLS, int irow);
bool sqlCursorNext (SQLS, int irow);
bool sqlCursorLast (SQLS, int irow);
bool sqlCursorPrev (SQLS, int irow);
bool sqlCursorBookmark (SQLS, unit32 bm);
bool sqlCursorGetBookmark (SQLS, unit32 *pbm);

bool sqlGotoRow (SQLS, int which_row);
sint16 sqlRowState (SQLS, int which_row);
unit32 sqlRowBookmark (SQLS, int which_row);
bool sqlColIsNUll (SQLS, int icol, int which_row);

const char *sqlColValue (SQLS, int icol, int which_row);

#endif

Perl Manual Page

Perl has a standard interface for connection to the database, therefore no functions are implemented or explained here. Some instructions are provided to allow the programmer the usage of Perl as API.

site_perl::DBD::User Distributed Perl Documentation::DBD::Ovrimos(3)

NAME
DBD::Ovrimos - DBI Driver for Ovrimos

SYNOPSIS
            use DBI;
            my $dbh=DBI->connect(
                 "dbi:Ovrimos:some.host.com:2500",
                 "user",
                 "passwd")
                 or die "Cannot connect\n";
            # more DBI calls...

DESCRIPTION
       DBI driver for Ovrimos (See the DBI(3) manpage
       for details). This driver is essentially a rename of
       DBD::Altera. Since DBI is a moving target at the time of
       this writing, this driver should only be assumed to work
       with DBI 0.93. A standard notice in DBD drivers' man
       pages is that, since the DBI is not yet stable, any DBD
       driver should be considered ALPHA software. So be it. We
       will try to keep up with the changes, stay tuned at
       <http://www.ovrimos.com/download.html> which is the primary
       download site for this driver.

CURRENT VERSION
       Release 0.12 Name change... Previous release were:
       Release 0.11 Essentially a bug-fix. Release 0.10
       (one hair short of 1.00). Main difference
       from previous version 0.09 is minor alterations to permit
       use for AGI (Another Gateway Interface). In other words,
       how can one use the same module to write both DBI programs
       and stored procedures for Ovrimos. Also,
       stored procedures are now supported, using the pseudo-SQL
       "call xxx ..." statement. See the documentation of Ovrimos
       for details.

DRIVER SPECIFIC BEHAVIOR DATA SOURCE NAME

The dsn string passed to DBI->connect must be of the
following form:

dbi:Ovrimos:host:port

       where host is a TCP/IP address in human-readable or
       dotted-decimal format, and port is the TCP/IP port number
       to use (Ovrimos SQLPORT configuration parameter).

CONNECTIONS

       One can have multiple connections to an Ovrimos
       database, up to the limit specified by one's User License.
       Keep in mind that what the License calls 'sessions' amount
       to what are called separate statements in DBI. Underlying
       the DBI is a protocol using the ODBC-equivalent
       'connections' and 'statements'. Sessions are kept live
       until commit/rollback, and that can result in denial of
       service if you reach the License limit. The database
       handle will reuse an inactive statement handle, so finish often.

       Commit/rollback finishes implicitly all open cursors
       (that's the answer one asks ODBC with
       SQL_CURSOR_COMMIT_BEHAVIOR and
       SQL_CURSOR_ROLLBACK_BEHAVIOR).

       Cached statements are not available. In the near future it
       is planned to cache SQL statements internally at the SQL
       Server, so preparing the same SQL statement as some time
       before will return a new $sth but without the cost
       associated with preparing from scratch.

DATA TYPES

       All ODBC 2.0 data types are supported. The format of
       time/date values is as per the SQL/2 Standard, i.e.:
       'DATE YYYY-MM-DD', 'TIME HH:MM:SS' and
       'TIMESTAMP YYYY-MM-DD HH:MM:SS'.

Ovrimos supports some additional types that
are given below alongside their numerical value:

UNSIGNED SMALLINT = 20

UNSIGNED INTEGER = 21

UNSIGNED TINYINT = 22

UNSIGNED BIGINT = 23

ERROR HANDLING

       As it stands, the DBI does not support the notion of
       warnings. Consequently, there are no diagnostics for
       successful calls. There is no obstacle in adding this,
       but since perl code using DBI will not check $h->errstr
       for successful operations, there is not much incentive to
       actually do it. Diagnostics for failed calls are inspected
       with the usual DBI calls. Do not pay any attention to
       $h->err; it is dummy. Ovrimos returns Standard
       SQL SQLSTATES and assorted messages, modeled principally
       after ODBC use. Since many diagnostics can be accumulated
       by one call, the diagnostics are merged, separated with
       newline. In that way, only the first SQLSTATE in the queue
       is visible using $h->state. One has to parse $h->errstr to
       find out the rest.

BLOBS

       BLOBs are supported via the SQL2 types LONG VARCHAR and
       LONG VARBINARY. These are not fetched with SQL queries and
       the LongReadLen and LongTruncOk attributes are not
       honored. Instead, Ovrimos presents a HTTP
       interface for retrieving BLOBS. Every BLOB has a Uniform
       Resource Identifier that can be found using the built-in
       URI function. This makes for easy retrieval of BLOBs in
       CGI scripts, where the URI can be embedded in HTML
       constructs like this:

my ($name,$uri1,$uri2);
$sth->bind_columns(undef,\($name,$uri1,$uri2));

$sth->prepare('select name,uri(blob1),uri(blob2) from blobtest');
$sth->execute;

            while($sth->fetch) {
                 print '<A HREF="' . $uri1 .'">Click here!<A> ';
                 print '<IMG SRC="' . $uri2 . '" ALT="Image"><BR>', "\n";
            }

       BLOBs are MIME-typed so the HTTP browser knows how to
       handle them. If one needs to retrieve a BLOB in an
       arbitrary script, one can use HTTP facilities like those
       in the libwww bundle (see CPAN,
       <http://cpan.perl.org/CPAN.html#libwww>). Or, one can just
       lead a simple life and do

            require 5.002;
            use strict;
            use IO::Socket;
            my $host;
            my $file;
            my $port=80;
            if($uri =~ m[^http://(.*):(\d*)/(.*)]) {
                 ($host,$port,$file)=($1,$2,$3);
            } elsif($uri =~ m[^http://(.*)/(.*)]) {
                 ($host,$file)=($1,$2);
            } else {
                 die "horribly";
            }
            my $so=IO::Socket::INET->new( Proto=>"tcp", PeerAddr=>$host,
                 PeerPort=>$port) or die "in pain";
            print $so "GET /$file HTTP/1.0\r\n\r\n";
            $so->flush() or die "in agony";

       One can then proceed to read from $so after skipping the
       reply header. If the MIME type is required, it can be
       found in the 'Content-type:' attribute of the reply
       header.

Maybe in a later release this functionality will be
included in the driver.

DRIVER SPECIFIC ATTRIBUTES

There are some additional attributes that the user can
query a $sth for:

       TYPE (also ovrimos_column_type)
           Reference to an array of column types as per ODBC,
           plus the Ovrimos extended types. TYPE is in capitals
           because the values returned conform to approved
           standards (ODBC, X/Open).

       ovrimos_column_precision
           Reference to an array of column precisions. Has
           meaning only for vector types (*CHAR, *BINARY) and
           NUMERIC/DECIMAL

       ovrimos_column_scale
           Reference to an array of column scales. Has meaning
           only for NUMERIC/DECIMAL.

       ovrimos_execution_plan
           It is a high-level explanation of the execution plan
           for the statement. The format is highly version-
           dependent and not to be dependent upon, but a human
           reader should be able to understand the access path
           for every range variable used, the order of range
           variables, the indices used, which temporary tables
           have been created etc.

       ovrimos_native_query
           The query submitted, but in the form retained by the
           SQL Server. The SQL Server applies transformations to
           the SQL source and disambiguates certain constructs.
           The modified source can also be found in the execution
           plan (see above).

LOW LEVEL LIBRARY

       The entire low-level library that implements the Ovrimos
       protocol is included. The DBI driver is based
       on this library, but one could conceivably use the library
       on its own. It is the only way, for the time being, to use
       scrollable cursors and bookmarks, since the DBI does not
       support them (yet?). See the package
       DBD::Ovrimos::lowlevel in Ovrimos.pm. No documentation is
       provided in this version about the low-level library.

       CONFORMANCE
       There is a particularity concerning transactions: see the
       section on CONNECTIONS, SESSIONS AND TRANSACTIONS.

       Cached statements don't exist. Not even the function
       prepare_cached exists. Do not use it! You won't find any
       relevant attribute either.

KNOWN BUGS
There are no known bugs in the DBD Driver.

       ACKNOWLEDGEMENTS
       I would like to thank all the people on the DBI-DEV
       mailing list that helped clear some misunderstandings.

SEE ALSO DBI(3)

AUTHOR
Dimitrios Souflis dsouflis@altera.gr,

COPYRIGHT

(c) ALTERA EPE , Greece http://www.ovrimos.com

       Permission is granted to use this software library
       according to the GNU Library General Public License (see
       <http://www.gnu.org>).

Previous Chapter: Appendix - Special Issues

Next Chapter: Appendix - Previous version