| Previous Chapter: ODBC |
Ovrimos offers a programming interface for C, C++,
Perl and Java programmers. The
following sections briefly describe this interface (with emphasis on the
C interface, which is entirely built by Ovrimos, since C has no native
interface for databases the way, Perl does, for example).
Scheme programmers may also find interface to Scheme in the HTTP
Server: Scheme - Script mechanisms.
The mechanism for stored procedures, which is also a strong feature of Ovrimos is briefly described toward the end of this chapter. A few simple examples are provided there too.
C and C++ programmers are able to write applications that connect to
Ovrimos databases. They only need the client library and the sqlcli.h
definition file.
To find out how to make and use a C client library, users may wish
to see the readme file in libsrc directory.
Central point to the information exchange between a client application and Ovrimos is the connection handle SQLH and the statements within each connection. Every application requests a connection to the database, which, if successful, returns a session handle, SQLH. Each connection (session handle) may place a request for a number of statement handles, which may be open at the same time. So, it is possible to retain several statement handles, SQLS, within a connection. API statements are execution channels for SQL statements.
Several simultaneous connections can be held to one or many disparate
databases.
Statement allocation and deallocation is allowed any time during the
session. Statements are subordinate notions to the session.
Connections and statements may have a number of options. Options given
to the connection are passed to each of its statements.
It is possible to have either a synchronous execution of an SQL statement, in which case the statement which initiated the request must wait until the end of the execution, or an asynchronous execution (non-blocking request) in which case the statement receives an answer if the request was successfully passed to a thread. The results will be available to the statement later, after the completion of the command execution.
Connection and statement initialization functions
bool sqlConnect (const char *server, const char *db, const char *username, const char *passwd, SQLH *session);
Arguments:
const char *server: The computer host name where the database
resides.
const char *db: The database name. In Unix, this is a string
representing the SQL Port number. In Win 32 one can use a string representation
of the SQL Port number prefixed by '#', or the database name. In the latter
case, the host computer's Registry must be visible to other machines.
const char *username: The user's name.
const char *passwd: The user's password.
SQLH *session: The session handle. It is returned by the sqlcore
to be used for all other communication with the database. The value of
session is undefined if the connection call failed.
Action:
Connects the user program to the database, returning a connection (session)
handle when the connection is successful.
Return values:
bool: true if connection succeeded, false if connection failed.
c_outcome sqlConnectOutcome ()
Arguments: None
Action:
Returns the outcome of the connection attempt.
Return values:
c_outcome: c_ok if the connection was successful, c_conn_failed
if the host was unreachable, c_trans_failed if the given port was not a
database sqlport, c_auth_failed if the user was not allowed to connect.
In the special case where c_outcome = c_ok but the connection has failed,
it is possible that the database configuration could not be read. (This
case arises in Win32)
Arguments:
SQLH: The connection handle.
Action:
Disconnects from the database and closes the connection.
Return values:
bool: true if the call was successful, false if the call failed.
bool sqlAllocStmt (SQLH, SQLS *);
Arguments:
SQLH: The session handle.
SQLS *: The returned statement handle.
Action:
Opens a new statement within an existing connection.
Return values:
bool: True if the system was able to open a new statement,
false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Frees a previously allocated statement, releasing its handle.
Return values:
bool:True if the command was successfully executed, false
otherwise.
bool sqlSetConnIntOption (SQLH, uint16 option, uint32 value);
Arguments:
SQLH: The connection handle.
uint16 option: The connection option (synchronous/asynchronous)
uint32 value: Value of the given option.
Action:
Sets the connection option.
The connection option supported may be either synchronous or asynchronous.
If the connection handle is asynchronous, then all statements of the connection
are also asynchronous. The situation is analogous for synchronous connections.
Return values:
bool: True if the connection option was successfully set,
false otherwise.
bool sqlGetConnIntOption (SQLH, uint16 option, uint32 *value);
Arguments:
SQLH: The session handle.
uint 16 option: The desired connection option.
uint32 *value: The value of the desired connection option.
Action:
The function returns the connection option, inside the argument value.
Return values:
bool: True if the connection option was successfully retrieved,
false otherwise.
bool sqlSetStmtIntOption (SQLS, uint16 option, uint32 value);
Arguments:
SQLS: The statement handle.
uint 16 option: The statement option to be set.
uint 32 value: The value of the statement option.
Action:
Sets the statement option. See also OPTIONS_DEFINED.
Return values:
bool: True if the statement option was successfully set, false
otherwise.
bool sqlGetStmtIntOption (SQLS, uint16 option, uint32 *value);
Arguments:
SQLS: The statement handle.
uint 16 option: The desired statement option.
uint 32 *value: The value of the desired statement option.
Action:
The function returns the statement option, inside the argument value.
Return values:
bool: True if the statement option was successfully retrieved,
false otherwise.
sqlSetRowsetSize (SQLS, uint32 sz);
Arguments:
SQLS: The statement handle.
uint32 sz: The rowset size to be set.
Action:
Sets the size of the returned Rowset size. It is used for the returned
results of queries. For efficiency reasons, the programmer is advised to
use a large rowset number. It is not always possible, however, to achieve
the required rowset size, especially when this is too large, therefore,
programmers are urged to use function sqlGetRowsetSize after setting
the size. This function returns the real rowset size set, which is the
one that will be used for the statement for the next cursor.
Return values:
bool: True if the rowset size was set, false otherwise. A
returned value of true does not mean that the requested size was allocated.
It merely means that the execution of the command was successful. Users
still need to use the sqlGetRowsetSize to learn the allocated
size.
sqlGetRowsetSize (SQLS, uint32 *psz);
Arguments:
SQLS: The statement handle.
unit32 *psz: The rowset size returned.
Action:
Returns the rowset size as an integer number. Most of the time it is
used in combination with sqlSetRowsetSize.
Return values:
bool: True if the function call succeeded, false otherwise.
Command execution functions
bool sqlExecDirect (SQLS, const char *cmd);
Arguments:
SQLS: The statement handle.
const char *cmd: The SQL command to be executed.
Action:
Executes directly an SQL command. Preparation and execution are done
one after another by the same command.
Return values:
bool: For synchronous statement handles, true if the execution
was successful, false otherwise. For asynchronous statement handles, true
if the execution has started and the handle was unblocked, false otherwise.
bool sqlPrepare (SQLS, const char *cmd);
Arguments:
SQLS: The statement handle.
const char *cmd: The SQL command to be executed.
Action:
Prepares an execution plan for an SQL command. The prepared command
may be executed later as many times as needed.
Return values:
bool: True if the command was successfully prepared, false
otherwise.
Arguments:
SQLS: The statement handle.
Action:
Executes a previously prepared SQL statement. An SQL statement may
be executed several times, while prepared only once.
Return values:
bool: For synchronous statement handles, true if the execution
was successful, false otherwise. For asynchronous statement handles, true
if the execution has started and the handle was freed, false otherwise.
The following two functions sqlBulkAdd and sqlBulkExec
add efficiency to any program making mass updates to the database. Instead
of giving the commands one by one and interacting with the database for
each one of them, it is possible to use these two "bulk" commands, where
the statements to be executed are written into an internal buffer via the
sqlBulk Add command and later sent for execution via the sqlBulkExec command.
Warning! The total space in the internal buffer is 64KB, therefore
programmers should take care not to exceed this limit when adding commands.
bool sqlBulkAdd (SQLS, const char *cmd);
Arguments:
SQLS: The statement handle.
const char *cmd: The SQL command as a character string.
Action:
Adds a command to an internal buffer. The command will be executed
later together with other commands. Of course only update commands (i.e.
insert, delete, update) may be processed through this mechanism.
Return values:
bool: True if the addition was successful, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Sends the previously filled buffer for bulk execution. As mentioned
above, the bulk execution makes sense only for update commands.
Return values:
bool: True if the execution was successful, false otherwise.
Regardless of the asynchronous option set for the statement handle, this
is a blocking request and has to be finished before a message is returned
and the statement handle is freed.
bool sqlCall (SQLS, const char *cmd);
Arguments:
SQLS: The statement handle.
const char *cmd: The call to be executed.
Action:
Calls and executes a stored procedure. See also AGI-Stored
Procedures.
Return values:
bool: True if the call was successfully executed, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Closes the cursor used to fetch the select data. Closing the cursor
does not close the statement.
Return values:
bool: True if the cursor was closed, false otherwise.
bool sqlAsyncFinished (SQLS, int *retcode);
Arguments:
SQLS: The statement handle.
int *retcode: The value indicating if the statement was finished
or not.
Action:
Examines if the asynchronous execution has finished.
Return values:
bool: True if probed successfully, false otherwise.
Note! The return value true or false does not indicate whether the
execution has finished. The value indicating the completion of the executed
statement is in retcode.
Arguments:
SQLS: The statement handle.
Action:
Cancels asynchronous execution.
Return values:
bool: True if execution was canceled, false otherwise.
bool sqlSetCursorName (SQLS, const char *name);
Arguments:
SQLS: The statement handle.
const char *name: The name of the cursor.
Action:
Sets a name for a cursor. Cursors are used to traverse the result set
returned by a select expression. Naming the cursor is an action that normally
is done before submitting an SQL select command for execution either to
sqlExec or to sqlExecDirect. The name of the cursor is used for an update
where the current of the cursor is.
Return values:
bool: True if the cursor was named, false otherwise.
bool sqlGetCursorName (SQLS, char *name);
Arguments:
SQLS: The statement handle.
char *name: The cursor name.
Action:
Returns into the argument "name" the name of the current cursor.
Return values:
bool: True if the cursor name was returned, false otherwise.
Transaction functions
Transactions are supported and nesting
is also possible within each statement. As soon as the statement begins,
all requests are executed within the context of an enveloping transaction.
No nesting command is necessary for the beginning of the first transaction.
If a new, nested transaction is necessary, then nesting is requested. To
make the updates of the innermost transaction permanent a commit statement
is required, while rollback cancels all updates of the current transaction.
If a commit is done during a statement, the current transaction is
finished and another transaction begins. Thus, a rollback always returns
the database to the state of the last commit if no nesting is done or to
the state before the nest command.
bool sqlNest (SQLS);
Arguments:
SQLS: The statement handle.
Action:
Starts a nesting transaction.
Return values:
bool: True if the new nested transaction is begun, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Makes the updates (insertions, deletions, etc.) of the transaction permanent
to the database. If the transaction was nested, the commit function closes
this level of nesting and returns to the previous. If the transaction was
not nested, it makes the updates up to this execution point permanent.
Return values:
bool: True if the transaction committed, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Rolls back the updates of the current transaction. All modifications
made from the beginning of the transaction are canceled.
Return values:
bool: True if the transaction is rolled back, false otherwise.
Messages and diagnostics
There is a number of messages and diagnostics in case of a problem.
Arguments:
SQLH: The session (connection) handle.
Action:
Returns the number of errors of the connection.
Return values:
int: The number of error messages. 0 if no pending messages
exist.
Arguments:
SQLS: The statement handle.
Action:
Returns the number of errors of the statement.
Return values:
int: The number of error messages. 0 if no pending messages
exist.
bool sqlGetConnDiagnostics (SQLH, uint16 maxlen, char *buffer);
Arguments:
SQLH: The connection (session) handle.
uint16 maxlen: The maximum return buffer size.
char *buffer: The buffer within which the error message will
be returned.
Action:
Returns one connection error/diagnostic message at a time.
Return values:
bool: True if a message was returned, false otherwise.
bool sqlGetStmtDiagnostics (SQLS, uint16 maxlen, char *buffer);
Arguments:
SQLS: The statement handle.
Action:
Returns one statement error/diagnostic message at a time.
Return values:
bool: True if a message was returned, false otherwise.
General information functions
Several functions are described below, which basically return some
information about the execution plan, the number of rows returned, or the
number, types, etc. of columns returned by a query.
bool sqlGetExecutionPlan (SQLS, char *buffer);
Arguments:
SQLS: The statement handle.
char *buffer: The buffer into which the execution plan will
be placed.
Action:
Returns the execution plan in a form that can be read by the user.
Return values:
bool: True if the execution plan is returned, false otherwise.
bool sqlGetNativeQuery (SQLS, char *buffer);
Arguments:
SQLS: The statement handle.
char *buffer: The buffer into which the native query will
be returned.
Action:
Returns the native query (i.e. the simplified query) of a select statement.
Return values:
bool: True if the native query was successfully returned,
false otherwise.
bool sqlGetRowCount (SQLS, uint32 *p);
Arguments:
SQLS: The statement handle.
uint 32 *p: The number of rows returned by an executed select
statement.
Action:
Returns the number of rows corresponding to an executed select command.
Return values:
bool: True if the number was successfully returned, false
otherwise.
The following functions are used to retrieve fetched column information and data.
bool sqlGetOutputColDescr (SQLS);
Arguments:
SQLS: The statement handle.
Action:
The function is used to fill the fetched column descriptions into an
internal buffer. From there on there is a number of functions that do not
access the database but can retrieve column information for every column.
The rest of the column functions do not access the database. They merely
access the buffer, so they are very fast.
Return values:
bool: True if the description was retrieved, false otherwise.
Arguments
SQLS: The statement handle.
Action
It returns the number of columns per row fetched by the executed select
statement.
Return values
int: The number of rows as an integer number. Zero means that
no columns were fetched.
const char* sqlGetOutputColName (SQLS, int );
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns the given column name as a string.
Return values:
const char *: The column name.
int sqlGetOutputColType (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns the column type as an integer number.
Return values:
int: The column type. Data types are defined in the sql
client include file.
int sqlGetOutputColLength (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns the column length.
Return values:
int: The length of the column is returned as a vector, i.e.
scalar types have length 1, while composite types (strings etc.) have the
number of elements making the total size of the column. Lengths, therefore,
are meaningful when the data type is also known.
int sqlGetOutputColPrecision (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns the column precision for numeric columns.
Return values:
int: The precision of column as an integer number.
int sqlGetOutputColScale (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns the column scale as an integer number.
Return values:
int: The scale of column as an integer number.
int sqlGetOutputColNullable (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The column number (zero based).
Action:
Returns information whether the column is nullable (i.e. accepts null
values) or not.
Return values:
int: Return values are false, true and unknown.
Parameter functions
Arguments:
SQLS: The statement handle.
Action:
Retrieves the parameter information and fills an internal buffer with
them. The rest of the parameter functions do not have to access the database.
They merely access the buffer, so they are very fast.
Return values:
bool: True if the parameter description has filled the buffer,
null otherwise.
Arguments:
SQLS: The statement handle.
Action:
Returns the number of parameters.
Return values:
int: The number of parameters.
int sqlGetParamType (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The parameters within the row as an integer (zero based).
Action:
It returns the parameter type as an integer.
Return values:
int: The parameter type. Data types are defined in the sql
client include file.
int sqlGetParamLength (SQLS, int);
Arguments:
SQLS: The statement handle.
Action:
It returns the length of the parameter.
Return values:
int: The length of the parameter is returned as a vector i.e.
scalar types have length 1, while composite types (strings etc.) have the
number of bytes making the total size of the parameter. Sizes, therefore,
are meaningful when the data type is also known.
int sqlGetParamPrecision (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The parameter number (zero based).
Action:
Returns the column precision.
Return values:
int: The parameter precision as an integer number.
int sqlGetParamScale (SQLS, int);
Arguments:
SQLS: The statement handle.
int: The parameter number (zero based).
Action:
Returns the parameter scale.
Return values:
int: The parameter scale as an integer number.
bool sqlPutParam (SQLS, int iparam, const char *, int sz);
Arguments:
SQLS: The statement handle.
int iparam: The parameter number (zero based)
const char *: The value of the parameter as a byte vector.
int sz: The size of the parameter.
Action:
Puts a value to a parameter.
Return values:
bool: True if the assignment was successful, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Resets the previously set parameters.
Return values:
bool: True if the operation was successful, false otherwise.
Cursor functions
Cursors are used for row sequence traversal. Every select statement may result into the selection of a large number of rows. This is the result set. The entire sequence of fetched rows is traversed by the cursor, while the row set defines a 'window' of results within the sequence.
bool sqlGotoRow (SQLS, int which_row);
Arguments:
SQLS: The statement handle.
int which_row: The row within the rowset (zero based).
Action:
Goes to the indicated row within the rowset. Moving through the rowset
affects many cursor functions. The function merely moves the cursor. It
does not fetch any rows.
Return values:
bool: True if the function was successful, false otherwise.
Arguments:
SQLS: The statement handle.
Action:
Fetches the rowset from the point on where the cursor is. For example,
if the rowset size is 100 (0 to 99) and the cursor is at point 40, then
function sqlCursorThis will fetch the next 100 rows of the result set.
Return values:
bool: True if the function is completed, false otherwise.
In the following functions, a parameter called irow is also used. This is an integer number indicating a displacement before fetching the rowset that the function indicates.
bool sqlCursorFirst (SQLS, int irow);
Arguments:
SQLS: The statement handle.
int irow: Displacement within the result set. irow=0 means
no displacement.
Action:
Fetches the first rowset (from the beginning of the result set) after
the displacement point.
Example: If the rowset size is 100 (0 to 99) and irow = 5, then rows
5 to 104 of the result set will be fetched.
Return values:
bool: True if the requested rowset is fetched, false otherwise.
bool sqlCursorNext (SQLS, int irow);
Arguments:
SQLS: The statement handle.
int irow: Displacement within the result set. irow=0 means
no displacement from the next row.
Action:
Fetches a row set from the position next to the irow displacement..
Example: If the rowset is 100 (0 to 99) and irow = 10, and the current
of the cursor is in position 25, then sqlCursorNext (SQLS, 10) fetches
100 rows after row 35 (i.e. 36 to 135).
Return values:
bool: True if the requested rowset is fetched, false otherwise.
bool sqlCursorLast (SQLS, int irow);
Arguments:
SQLS: The statement handle.
int irow: Displacement within the rowset. irow=0 means no
displacement.
Action:
Fetches a rowset that ends to position irow before the last row of
the result set.
Example: If the result set size is 544, the rowset size is 100 and
irow is 5, then sqlCursorLast (SQLS, 5) will fetch the rowset from row
440 to row 539.
Return values:
bool: True if the requested rowset is fetched, false otherwise.
bool sqlCursorPrev (SQLS, int irow);
Arguments:
SQLS: The statement handle.
int irow: Displacement within the rowset. irow=0 means no
displacement from the previous row.
Action:
Fetches a rowset that ends to the previous row from the position irow
before the current of the cursor.
Example: If the result set is 200, the rowset size is 100, irow is
5 and the current of the cursor is in position 125, then sqlCursorPrevious
(SQLS, 5) will fetch the rowset from row 20 to row 119.
Return values:
bool: True if the requested rowset is fetched, false otherwise.
bool sqlCursorBookmark (SQLS, uint32 bm);
Arguments:
SQLS: The statement handle.
uint32 bm: The bookmark value.
Action:
Fetches the rowset from where the bookmark was set.
Return values:
bool: True if the function executed successfully, false otherwise.
bool sqlCursorGetBookmark (SQLS, uint32 *pbm);
Arguments:
SQLS: The statement handle.
uint32 *pbm: The bookmark of the cursor.
Action:
Returns a bookmark for the current cursor position.
Return values:
bool: True if the bookmark was returned, false otherwise.
sint16 sqlRowState (SQLS, int which_row);
Arguments:
SQLS: The statement handle.
int which_row: The row number within the rowset.
Action:
Returns the state of the given row.
Return values:
sint16: May take values: ROW_OK, meaning that the requested
row is fetched and existent, ROW_NONEXISTENT, meaning that the requested
row does not exist in the rowset, ROW_ERROR, meaning that for some reason
this row could not be properly fetched.
Note! Sometimes if the rowset is large, the cursor may run out
of data without filling in the rowset. For example, if the rowset size
is 100 and the returned rows are only 10, then all rows from 10 on are
non-existent. Since it would be a waste of space to fill in buffers with
non-existent data, as soon as a non-existent row is found, the filling
of the cursor is stopped. It is thereby advisable to programmers to examine
the validity of the rows requested from beginning to end and not the other
way around.
uint32 sqlRowBookmark (SQLS, int which_row);
Arguments:
SQLS: The statement handle.
int which_row: The row number within the rowset (zero based).
Action:
Sets a bookmark onto the indicated row. This action is independent
of whether a bookmark is set on each row as a statement option.
Return values:
uint32: The bookmark number.
bool sqlColIsNull (SQLS, int icol, int which_row);
Arguments:
SQLS: The statement handle.
int icol: The column number (zero based).
int which_row: The row number within the rowset (zero based).
Action:
Returns whether a returned column is NULL.
Return values:
bool: True if the column is NULL, false otherwise.
const char *sqlColValue (SQLS, int icol, int which_row);
Arguments:
SQLS: The statement handle.
int icol: The column number (zero based).
int which_row: The row number within the rowset (zero based).
Action:
Returns the value data of a string column.
Return values:
const char *: The string where the column value is returned.
The rest of the functions return the value of a column put into the rowset by a select command. All the functions work in the same way, i.e. they accept the same arguments and return the column value. It is the programmer's responsibility to make sure that the function used is appropriate for the type of column returned. Otherwise, a meaningless result may be returned.
SQLS: The statement handle.
int icol: The column within the returned column set. The column
order is zero based.
int which_row: The row within the current rowset. The row
order is zero based.
init8 sqlColValueUint8 (SQLS, int icol, int which_row);
sint8 sqlColValueSint8 (SQLS, int icol, int which_row);
uint16 sqlColValueUint16 (SQLS, int icol, int which_row);
sint16 sqlColValueSint16 (SQLS, int icol, int which_row);
uint32 sqlColValueUint32 (SQLS, int icol, int which_row);
sint32 sqlColValueSint32 (SQLS, int icol, int which_row);
uint64 sqlColValueUint64 (SQLS, int icol, int which_row);
sint64 sqlColValueSint64 (SQLS, int icol, int which_row);
float sqlColValueFloat (SQLS, int icol, int which_row);
double sqlColValueDouble (SQLS, int icol, int which_row);
const char *sqlColValueString (SQLS, int icol, int which_row);
The following code is an example of a C program, making a connection
to the database and requesting data from the tables books.
#include <stdlib.h>
#include "sqlcli.h"
int main() {
SQLH conn;
SQLS stmt;
/* Establish connection */
if(!sqlConnect("localhost","3000","admin","pegasus",&conn)) {
char *msg;
/* Determine the reason of failure */
c_outcome outc=sqlConnectOutcome();
switch(outc) {
case c_conn_failed: msg="Host unreachable\n"; break;
case c_trans_failed: msg="Negotiation with host failed\n"; break;
case c_auth_failed: msg="Authentication failed or license exceeded\n"; break;
case c_ok: msg="Database configuration could not be read"; break; /*NT*/
}
puts(msg);
exit(1);
}
if(sqlAllocStmt(conn,&stmt)) {
if(sqlExecDirect(stmt,"select isbn,title,price from books")) {
sqlGetOutputColDescr(stmt);
sqlSetRowsetSize(stmt,1);
while(sqlCursorNext(stmt,0)) {
const char *isbn=sqlColValueString(stmt,0,0);
const char *title=sqlColValueString(stmt,1,0);
double price=sqlColValueDouble(stmt,2,0);
printf("isbn=\"%s\", title=\"%s\", price=%lf\n",isbn,title,price);
}
} else {
puts("Statement could not be executed");
}
sqlFreeStmt(stmt);
} else {
puts("Statement could not be allocated");
}
sqlDisConnect(conn);
exit(0);
}
Perl
Perl already contains an interface for database connection. All that is needed to work with Perl is to have the DBD-Ovrimos-0.10 driver (provided in file DBD-Ovrimos-0.10.tar.gz) and to install the driver, with the following steps:
perl Makefile.PL Run the Perl interpreter for the
makefile
make
make install
Only users with root privileges may install.
A readme file in the form of Perl doc page is provided in "Perldoc DBD::Ovrimos", offering a brief description of Perl usage.
A simple example is provided here. In the Perl source code, the user
program connects to the database and retrieves information from table books.
use DBI;
$dbh=DBI->connect('dbi:Ovrimos:localhost:3000','admin','pegasus')
or die("connect");
$sth=$dbh->prepare('select isbn,title,price from books') or die($dbh->errstr);
$sth->execute() or die($sth->errstr);
my ($isbn,$title,$price);
$sth->bind_columns(undef,\($isbn,$title,$price));
while($sth->fetch) {
print "isbn=\"$isbn\", title=\"$title\", price=$price\n";
}
$sth->finish() or die($sth->errstr);
$dbh->disconnect() or die($dbh->errstr);
Programmers will have to use DBI (Database Interface). The database handle
(dbh) is returned after a successful connection. Necessary connection parameters
are the database host, localhost, the port (sqlport), 3000
and the username, admin, and password, pegasus.
The statement handle (sth) is returned after a successful preparation
of the select command.
The columns are bound and as long as data may be retrieved from the
table, the results are printed.
In Unix systems no .cmd file is required. It is necessary however to have a first line as the one that follows to make the stored procedure executable.
#! /usr/bin/perl
use DBD::Ovrimos;
$dbh=DBD::Ovrimos::AGIdb();
$dbh->do('select * from books');
exit;
Java
The following is a simple Java application, connecting to the Ovrimos
database with port number 3000. The program selects data from table "books"
and prints them.
import java.sql.*;
import java.util.Properties;
import ovjdbc.*;
class j_sample {
public static void main(String args[]) throws SQLException
{
OvrimosDriver dr=new OvrimosDriver();
Connection c=null;
try {
c=dr.connect("jdbc:ovrimos://localhost:3000;password=pegasus;user=admin",
new Properties());
if(c==null)
{
System.exit(-1);
}
PreparedStatement
pst=c.prepareStatement("select
isbn,title,price from books");
pst.execute();
printDiags(pst.getWarnings());
ResultSet
rs=pst.getResultSet();
ResultSetMetaData md=rs.getMetaData();
while(rs.next())
{
System.out.println("isbn=\""+rs.getString(1)
+"\", title=\""+rs.getString(2)+"\", price="
+rs.getString(3));
}
}
catch(SQLException e) {
printDiags(e);
}
finally {
if(c!=null)
{
c.close();
}
}
}
static void printDiags(SQLException e) {
while(e!=null) {
System.out.println(e.getSQLState()+"
"+e.getMessage());
e=e.getNextException();
}
}
};
Note! The binary use of stdin/stdout in Java stored procedures was somewhat unsuccessful. Nevertheless, an example of a stored procedure in Java is included at the end of this section.
The AGI (Another Gateway Interface) is one more tool to allow as many
user categories as possible interface to Ovrimos. Stored procedures, written
in the programmer's favorite language may be called at any point during
the SQL command execution.
Stored procedures are either code made into executable programs, or
scripts, stored in the AGI directory of each database. They are to be invoked
from the SQL Terminal or sqlapp4 command line, as well as through calls
from other API programs, e.g. C programs.
At the physical level, the difference between the API and AGI is that while API communicates with the database through sockets, AGI stored procedures communicate through pipes. As soon as a call to a stored procedure is made, the SQL Server activates an AGI process, which uses two pipes to communicate to the database. The AGI program uses stdin/stdout.
Picture AGI-1 shows physical interconnection between Ovrimos and AGI programs, while picture AGI-2 shows logical interconnection.
When a client program asks for an AGI program (stored procedure), then communication between the client and Ovrimos SQL Server is momentarily suspended. The AGI program is executed and talks to the database as a surrogate of the client.
Stored procedures are invoked via the call command. The call executes the stored procedure and continues.
call create xx as select ...
The application program assumes that the first name after a call, i.e. create at this example is an executable file. Executable files require no extension to run as stored procedures, and in fact they must not have one (even in Win 32).
An example of a C stored procedure is presented in C - Stored Procedure.
In case of script files (which are not directly executable in systems such as Win 32), the script must be called by another script named <name>.cmd, e.g. in the case of the create above it must be a script file called create.cmd. The script file must contain the full path name of an executable file plus any parameters (if needed to run this file). All these are put together by AGI, which finds the file and executes the code as before.
Example: A script file called script1.cmd, which eventually leads to the execution of a Perl script through the Perl interpreter, will be called:
call script1 'abc' 123
Here script1 is a script file, with full name script1.cmd.
Script1.cmd contains the full path name of the perl interpreter and
the required parameters, e.g.
c:\perl5\bin\perl.exe -Ic:\alpha c:\scripts\script1.pl
The contents of script1 are embedded in the call command, thus call
is executed as if the command had been:
call c:\perl5\bin\perl.exe -Ic:\alpha c:\scripts\script1.pl 'abc'
123
At this point, mechanisms for communicating with stored procedures in
C, Perl and Java are implemented. It is important to note at this point
that the same libraries used for the API are also used for AGI, simplifying
thus very much communication and making a seamless programming environment.
C, C++ Stored
Procedures
The following is a small example of a stored procedure, which makes
a selection from table books.
#include <stdlib.h>
#include "sqlcli.h"
int main(int argc, char **argv) {
SQLH conn;
SQLS stmt;
if(sqlConnect("AGI",0,0,0,&conn)) {
if(sqlAllocStmt(conn,&stmt)) {
sqlExecDirect(stmt,"select * from books");
sqlFreeStmt(stmt);
}
sqlDisConnect(conn);}
exit(0);
}
Programmers should notice that connection statement is different from the one of the C API. Here, the host is indicated as AGI and the rest of the parameters (dbase, userid, password) are 0.
A more substantial example of a stored procedure is the following, where
a table is created dynamically as a result from a select statement.
The file is called create.c and the call is made as follows:
call create <tablename> as <select stmt>
e.g.
call create tnames as select table_name from sys.tables;
#include <string.h>
#include "sqlcli.h"
static void make_type_name(SQLS stmt, int icol, char *type_name);
int main(int argc, char **argv) {
SQLH conn;
SQLS stmt;
char create_buffer[1024];
char select_buffer[1024];
char insert_buffer[1024];
char *p;
char *table_name;
if(argc<6) {
exit(1);
}
table_name=argv[1];
if(stricmp(argv[2],"as")!=0) {
exit(1);
}
argv+=2;
*select_buffer=0;
p=select_buffer;
while(*++argv) {
p=strchr(p,0);
sprintf(p,"%s ",*argv);
}
if(sqlConnect("AGI",0,0,0,&conn)) {
if(sqlAllocStmt(conn,&stmt)) {
if(sqlPrepare(stmt,select_buffer)) {
int i;
int colnb;
int ucount=0;
char unnamed[20];
sqlGetOutputColDescr(stmt);
colnb=sqlGetOutputColNb(stmt);
sprintf(create_buffer,"create table %s(",table_name);
p=strchr(create_buffer,0);
sprintf(unnamed,"%d\n",colnb);
for(i=0; i<colnb; i++) {
const char *cname;
if(i!=0) {
sprintf(p,", "); p=strchr(p,0);
}
cname=sqlGetOutputColName(stmt,i);
if(cname[0]==0) {
sprintf(unnamed,"unnamed%d",ucount++);
cname=unnamed;
}
sprintf(p,"%s ",cname); p=strchr(p,0);
make_type_name(stmt,i,p); p=strchr(p,0);
if(sqlGetOutputColNullable(stmt,i)==false) {
sprintf(p,"not null"); p=strchr(p,0);
}
}
sprintf(p,")");
if(sqlExecDirect(stmt,create_buffer)) {
sprintf(insert_buffer,"insert into %s %s",table_name,select_buffer);
sqlExecDirect(stmt,insert_buffer);
}
}
sqlFreeStmt(stmt);
}
sqlDisConnect(conn);
}
exit(0);
}
static void make_type_name(SQLS stmt, int icol, char *type_name) {
int t,len,prec,scale;
t=sqlGetOutputColType(stmt,icol);
len=sqlGetOutputColLength(stmt,icol);
prec=sqlGetOutputColPrecision(stmt,icol);
scale=sqlGetOutputColScale(stmt,icol);
switch(t) {
case T_INTEGER:
strcpy(type_name,"integer"); break;
case T_SMALLINT:
strcpy(type_name,"smallint"); break;
case T_TINYINT:
strcpy(type_name,"tinyint"); break;
case T_BIT:
strcpy(type_name,"bit"); break;
case T_BIGINT:
strcpy(type_name,"bigint"); break;
case T_UINTEGER:
strcpy(type_name,"unsigned integer"); break;
case T_USMALLINT:
strcpy(type_name,"unsigned smallint"); break;
case T_UTINYINT:
strcpy(type_name,"unsigned tinyint"); break;
case T_UBIGINT:
strcpy(type_name,"unsigned bigint"); break;
case T_FLOAT:
strcpy(type_name,"float"); break;
case T_REAL:
strcpy(type_name,"real"); break;
case T_DOUBLE:
strcpy(type_name,"double"); break;
case T_DECIMAL:
sprintf(type_name,"decimal(%d,%d)",prec,scale); break;
case T_NUMERIC:
sprintf(type_name,"numeric(%d,%d)",prec,scale); break;
case T_CHAR:
sprintf(type_name,"char(%d)",len); break;
case T_VARCHAR:
sprintf(type_name,"varchar(%d)",len); break;
case T_UNI_CHAR:
sprintf(type_name,"unicode char(%d)",len); break;
case T_UNI_VARCHAR:
sprintf(type_name,"unicode varchar(%d)",len); break;
case T_BINARY:
sprintf(type_name,"binary(%d)",len); break;
case T_VARBINARY:
sprintf(type_name,"varbinary(%d)",len); break;
case T_DATE:
sprintf(type_name,"date"); break;
case T_TIME:
sprintf(type_name,"time"); break;
case T_TIMESTAMP:
sprintf(type_name,"timestamp"); break;
case T_LONGVARBINARY:
sprintf(type_name,"long varbinary"); break;
case T_LONGVARCHAR:
sprintf(type_name,"long varchar"); break;
default:
strcpy(type_name,"<Unknown type>");
}
}
Perl Stored Procedures
The following is a small example of a stored procedure, which makes a selection from table books.
The stored procedure will be as follows for Win NT:
use DBD::Ovrimos;
$dbh=DBD::Ovrimos::AGIdb();
$dbh->do('select * from books');
exit;
For Windows NT a .cmd file is also required. It will be of the following form:
d:\per15\bin\perl.exe c:\dbm\server\agi\p_agi.pl
The following is a small example of a stored procedure, which makes a selection from table books.
The stored procedure will be as follows for Win NT:
use DBD::Ovrimos;
$dbh=DBD::Ovrimos::AGIdb();
$dbh->do('select * from books');
exit;
For Windows NT a .cmd file is also required. It will be of the following form:
d:\per15\bin\perl.exe c:\dbm\server\agi\p_agi.pl
Note! The Java example seems not to be able to synchronize I/O. This
may be due to our using it for binary data. It is included here with reservations,
because it may work correctly with different versions/implementations of
the JDK.
import java.sql.*;
import ovjdbc.*;
public class javatest {
public static void main(java.lang.String[] args) throws SQLException {
Connection co=OvrimosDriver.connectAGI(true);
PreparedStatement st=co.prepareStatement("select * from books");
st.execute();
}
}
In all cases, the programmer should notice the special procedure AGIdb()
which is used for connection. No connection arguments are used.
| Previous Chapter: ODBC |