Ovrimos is an open-ended SQL Server, allowing connection and data handling from several different software products. See 3.1.(Ovrimos at a glance). The present chapter refers to ODBC, JDBC and DBI, as well as Stored Procedures.
You may find ovodbc.zip either in the Ovrimos CD or you may download it from http://www.ovrimos.com/download.html. You may also check this URL for updated driver versions.
Warning! Do not use an ovodbc driver with version lower than 1.00.0500 for Ovrimos 2.6 and/or higher or vice versa. Use the old ODBC drivers for Ovrimos versions 2.5 and lower and the new ODBC drivers for Ovrimos versions 2.6 and higher.
To install the Ovrimos ODBC driver, the following simple steps are required:
The Ovrimos ODBC driver installation is now complete. Next step will be to Create a New Data Source for each database you wish to access through ODBC compatible products.
Go to the Control Panel
Choose ODBC
When the ODBC Data Source Administrator window appears, choose the User DSN tab and click on the Add... button.
A window with all available ODBC drivers will appear as shown in capture 10.1.
Choose Ovrimos ODBC Driver and click on the Finish button.
The Ovrimos ODBC Setup
configuration dialog box of caption 10.2 will pop on screen. The four blank fields
that appear in this box must be filled in with the following information:
Data Source Name: The name through which the database will be known to
ODBC.
Description: A short description text about the data source.
Host Name: The name of the computer, where the database server resides.
Database Name: The name of the Ovrimos database or the port number.
When you type all information, click on the OK button.
The data source name will now appear in the list of ODBC Data Source Administrator.
Repeat the above steps for each database you wish to access through ODBC.
The JDBC driver , called ovjdbc.jar, is copied by the installation program in the shared directory ($HTTPSHAREDDIR). It is a type 4 native protocol all-java technology enabled driver, conformant to the JDBC 1.1 specification.
You may also check http://www.ovrimos.com/download.html for any updates of the driver.
The ovjdbc.jar can be used by an applet as follows:
<applet code=class_name ... archive="ovjdbc.jar">
Some technical information about the Ovrimos JDBC driver is provided below:
The Ovrimos JDBC driver connects to Ovrimos using the native Ovrimos RPC protocol
over TCP/IP . No middleware is necessary to connect to an Ovrimos Database using JDBC.
URL
The URL syntax recognized by the Ovrimos JDBC driver is:
jdbc:ovrimos://<host>:<dbase>;user=<username>;password=<hispassword>
dbase is either the $SQLPORT or the database name. It may be obtained from the Database Manager.
Any of the properties user or password or both, may be missing from the URL, and supplied to DriverManager.getConnection. If both are missing, the semicolon after <dbase> must be omitted too.
DatabaseMetaData
The function getBestRowIdentifier is, at present, unimplemented and returns null.
Statement
The function Statement.cancel does nothing.
ResultSet
The ResultSets returned belong to the class
OvrimosResultSet. If downcast, the user has
access to extended functionality:
Method isNull (int columnIndex) permits to inquire about the null state of a
column without get()ing it first.
PreparedStatement
Class OvrimosPreparedStatement offers methods to inquire about the parameters of the
statement:
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.
| Java example: Database connection and data selection |
![\includegraphics[width=12cm]{odbc-1}](img47.gif)
![\includegraphics[width=12cm]{odbc-2}](img48.gif)
/** j_sample.java
*/
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();
}
}
}
Perl Perl already defines an interface for database connectivity. To work with Perl you need to have DBI installed. Ovrimos S.A. provides you with a DBD driver, DBD-Ovrimos-0.13.tar.gz, which you will have to install.
You may find the DBD-Ovrimos-0.13.tar.gz in the shared directory ($HTTPSHAREDDIR).
You may also check http://www.ovrimos.com/download.html, for any updated version of the DBI driver.
The following steps are required to install the driver:
perl Makefile.PL
make
make install
In U*ix only users with root privileges may install.
A readme file in the form of Perldoc page is provided with the driver, offering a brief description of the driver.
In the simple Perl example below, the user program connects to the database and retrieves information from table Books.
| Perl example: Database connection and data selection |
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 include the use DBI statement. The database handle (dbh) is returned after a successful connection. Necessary connection parameters are the database host localhost, the $SQLPORT, 3000 and the username (in the above example, username is admin with password pegasus).
The statement handle (sth) is returned after a successful preparation of the select command. The columns are binded and as long as data may be retrieved from the table, the results are printed.
The contents of the PHP driver's directory are:
| Filename | Description |
|---|---|
| readme | Instructions for the Ovrimos PHP interface |
| php_ovrimos.c | The C source for the driver |
| *.php | Samples to test the API |
| php_ovrimos.mak | Sample customizable makefile to create the driver |
The driver is a single shared object, php_ovrimos.so, that is loaded in PHP using the dl command, like this:
dl("php\_ovrimos.so");
To create the driver one must have already built the Ovrimos C library, SQLCLI. Directions on doing that can be found in every Ovrimos distribution in the subdirectory named libsrc. The instructions therein guide in building a static library, libsqlcli.a which is statically linked with php_ovrimos.so in the provided makefile, but one could very well choose to build SQLCLI as a shared object, and modify php_ovrimos.mak accordingly.
Customization of php_ovrimos.mak is needed to tailor it to the specific machine and platform.
The API is modelled after that for ODBC. It should be immediately comprehensible to anyone having experience with the ODBC interface. One difference from ODBC is that autocommit cannot be specified at all (whereas in ODBC, it is the default).
The AGI (Another Gateway Interface) is one more tool to allow as many user categories as possible to interface to Ovrimos. Stored procedures, written in one of the languages supported by Ovrimos may be called at any point during the transaction.
Stored procedures are either executable programs or scripts, stored in the AGI directory of each database. They may be invoked from the SQL Terminal or the SQL Query Tool 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 with the database. The AGI program uses stdin/stdout.
Picture 10.3 shows physical interconnection between Ovrimos and AGI programs, while picture 10.4 shows logical interconnection.
When a client program asks for an AGI program (stored procedure), then communication between the client and Ovrimos is momentarily suspended. The AGI program is executed and communicates with 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 newcreate xx AS SELECT ...
The application program assumes that the first name after a call, i.e. newcreate in this example, is an executable file. Executable files require no extension to run as stored procedures, and in fact they must not have one.
In case of script files (which are not directly executable in systems such as Win32), the script must be called by another script named <name>.cmd, e.g. in the case of newcreate above it must be a script file called newcreate.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
For an example of C stored procedures, see E.2.1(C, C++ 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:
![\includegraphics[width=12cm]{agi-1}](img49.gif)
![\includegraphics[width=12cm]{agi-2}](img50.gif)
use DBD::Ovrimos;
$dbh=DBD::Ovrimos::AGIdb();
$dbh->do('select * from books');
exit;
For Windows NT a .cmd file is also required. The command to make the stored procedure will be of the following form:
d:\per15\bin\perl.exe c:\dbm\server\agi\p_agi.pl
| Java Stored Procedure |
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 connectAGI() which is used for connection. No connection arguments are used.