<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html>
<head profile="http://internetalchemy.org/2003/02/profile">
<link rel="foaf" type="application/rdf+xml" title="FOAF" href="http://www.openlinksw.com/dataspace/uda/about.rdf" />
<link rel="schema.dc" href="http://purl.org/dc/elements/1.1/" />
<meta name="dc.title" content="7. Data Access Interfaces" />
<meta name="dc.subject" content="7. Data Access Interfaces" />
<meta name="dc.creator" content="OpenLink Software Documentation Team ; " />
<meta name="dc.copyright" content="OpenLink Software, 1999 - 2009" />
<link rel="top" href="index.html" title="OpenLink Virtuoso Universal Server: Documentation" />
<link rel="search" href="/doc/adv_search.vspx" title="Search OpenLink Virtuoso Universal Server: Documentation" />
<link rel="parent" href="accessinterfaces.html" title="Chapter Contents" />
<link rel="prev" href="isql.html" title="Interactive SQL Utility" />
<link rel="next" href="VirtuosoDriverJDBC.html" title="Virtuoso Driver for JDBC" />
<link rel="shortcut icon" href="../images/misc/favicon.ico" type="image/x-icon" />
<link rel="stylesheet" type="text/css" href="doc.css" />
<link rel="stylesheet" type="text/css" href="/doc/translation.css" />
<title>7. Data Access Interfaces</title>
<meta http-equiv="Content-Type" content="text/xhtml; charset=UTF-8" />
<meta name="author" content="OpenLink Software Documentation Team ; " />
<meta name="copyright" content="OpenLink Software, 1999 - 2009" />
<meta name="keywords" content="" />
<meta name="GENERATOR" content="OpenLink XSLT Team" />
</head>
<body>
<div id="header">
<a name="odbcimplementation" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>7. Data Access Interfaces</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="accessinterfaces.html">Chapter Contents</a> | <a class="link" href="isql.html" title="Interactive SQL Utility">Prev</a> | <a class="link" href="VirtuosoDriverJDBC.html" title="Virtuoso Driver for JDBC">Next</a>
</div>
</div>
<div id="currenttoc">
<form method="post" action="/doc/adv_search.vspx">
<div class="search">Keyword Search: <br />
<input type="text" name="q" /> <input type="submit" name="go" value="Go" />
</div>
</form>
<div>
<a href="http://www.openlinksw.com/">www.openlinksw.com</a>
</div>
<div>
<a href="http://docs.openlinksw.com/">docs.openlinksw.com</a>
</div>
<br />
<div>
<a href="index.html">Book Home</a>
</div>
<br />
<div>
<a href="contents.html">Contents</a>
</div>
<div>
<a href="preface.html">Preface</a>
</div>
<br />
<div class="selected">
<a href="accessinterfaces.html">Data Access Interfaces</a>
</div>
<br />
<div>
<a href="virtclientref.html">ADO.Net Data Provider</a>
</div>
<div>
<a href="isql.html">Interactive SQL Utility</a>
</div>
<div class="selected">
<a href="odbcimplementation.html">Virtuoso Driver for ODBC</a>
<div>
<a href="#virtdsnsetup" title="Windows ODBC Driver Configuration">Windows ODBC Driver Configuration</a>
<a href="#secureodbcx509" title="Using X509 Certificates With ODBC Connection">Using X509 Certificates With ODBC Connection</a>
<a href="#virtmanconfodbcdsnunix" title="Manually configuring a Virtuoso ODBC DSN on Unix">Manually configuring a Virtuoso ODBC DSN on Unix</a>
<a href="#odbccompliance" title="ODBC Compliance">ODBC Compliance</a>
<a href="#VScrlCURSORS" title="Virtuoso Scrollable Cursor Engine">Virtuoso Scrollable Cursor Engine</a>
<a href="#CONN_STMT_OPTIONS" title="Effect of Connection & Statement Options">Effect of Connection & Statement Options</a>
<a href="#EFFICIENTAPI" title="Efficient Use of API">Efficient Use of API</a>
<a href="#EXECPYTHONSCRIPT" title="Executing SQL from Python script">Executing SQL from Python script</a>
<a href="#odbcimplementationext" title="Extensions">Extensions</a>
</div>
</div>
<div>
<a href="VirtuosoDriverJDBC.html">Virtuoso Driver for JDBC</a>
</div>
<div>
<a href="virtoledb.html">OLE DB Provider for Virtuoso</a>
</div>
<div>
<a href="inprocess.html">Virtuoso In-Process Client</a>
</div>
<div>
<a href="accintudsockets.html">Unix Domain Socket Connections</a>
</div>
<div>
<a href="dataccessclientsconfailandbalance.html">Virtuoso Data Access Clients Connection Fail over and Load Balancing Support</a>
</div>
<br />
</div>
<div id="text">
<a name="odbcimplementation" />
<h2>7.3. Virtuoso Driver for ODBC</h2>
<a name="virtdsnsetup" />
<h3>7.3.1. Windows ODBC Driver Configuration</h3>
<p>At installation time two ODBC data source names (DSN's)
are created with default values by the Virtuoso installer, the first DSN named "Local
Virtuoso" provides a link to a local default Virtuoso database server instance, while
the other named "Local Virtuoso Demo" provides a link to a local Virtuoso server
for the Virtuoso demonstration database.</p>
<p>The process of creating additional ODBC DSN's for you Virtuoso drivers for ODBC
is explained in the steps that follow:</p>
<ol>
<li>
<p>Go to the Windows <strong>Control panel</strong>.</p>
</li>
<li>
<p>Double click on the <strong>ODBC Administrator</strong> applet.
On Windows 2000 / XP the ODBC Administrator applet may be called
<strong>Data Source (ODBC)</strong> and may be found under Administrative
tools icon of the Control Panel.</p>
</li>
<li>
<p>Once the ODBC Administrator has been loaded choose by selecting
the appropriate panel whether you want a new User or System Data Source. User
Data Sources will only be available to the user that created them. System Data
Sources will be available to all users and applications on the system.</p>
</li>
<li>
<p>Click on the <strong>Add</strong> Data Source Name button</p>
</li>
<li>
<p>Select the Driver named <strong>OpenLink Virtuoso Driver</strong>
</p>
</li>
<li>
<p>Enter values into the fields presented by the Virtuoso
Driver's DSN configuration dialog:</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="Virtuoso ODBC Driver Setup Dialogue for Windows" src="../images/virtdsn001.png" />
</td>
</tr>
<tr>
<td>Figure: 7.3.1.1. Virtuoso ODBC Driver Setup Dialogue for Windows</td>
</tr>
</table>
<p>
<strong>Name:</strong> provide a name that will act as a logical
reference to the Virtuoso database server that you will be connecting to.
Subsequent references to this database will be made to this value when ODBC
compliant applications interact with your Virtuoso driver.</p>
<p>
<strong>Description:</strong> allows you to provide a short
description about the nature of the connection. This is optional.</p>
<p>
<strong>Server:</strong> enter the hostname or IP address of the
machine hosting your Virtuoso server and enter the port number that Virtuoso is
listening at. This is configured in the <a href="databaseadmsrv.html#VIRTINI">Virtuoso ini</a>
file on the server.</p>
</li>
<li>
<p>Press the <strong>Next</strong> button to configure more
details about the connection</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="Virtuoso ODBC Driver Setup Dialogue for Windows" src="../images/virtdsn002.ong" />
</td>
</tr>
<tr>
<td>Figure: 7.3.1.1. Virtuoso ODBC Driver Setup Dialogue for Windows</td>
</tr>
</table>
<p>
<strong>Connect to the Virtuoso Servet to obtain default settings for the
additional configuration options:</strong> allows you to specify the default username
and password for the connection.</p>
<p>Press the <strong>Next</strong> button</p>
<p>
<strong>Database:</strong> allows you to select the default database
for the connection. You will need to check the box above and supply a password
to refresh this list. Objects created or selected without an explicit
catalogue/database qualifier will automatically be sought from this select database.</p>
<p>
<strong>Charset:</strong> lets you choose the default character set
for the connection.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="Virtuoso ODBC Driver Setup Dialogue for Windows" src="../images/virtdsn003.png" />
</td>
</tr>
<tr>
<td>Figure: 7.3.1.2. Virtuoso ODBC Driver Setup Dialogue for Windows</td>
</tr>
</table>
</li>
<li>
<p>When the configuration is complete, and indeed at any time
you are satisfied with the configuration press the <strong>Finish</strong>
button to save the DSN.</p>
</li>
</ol>
<br />
<a name="secureodbcx509" />
<h3>7.3.2. Using X509 Certificates With ODBC Connection</h3>
<p>Virtuoso can be configured to authenticate ODBC logins with a single
X.509 certificate. The certificate must be registered server side for this purpose
and may contain an additional custom attribute for the users SQL account name.
In this way all login information is held in the possibly password protected
certificate. The user specifies the certificate path in the place of the user name
and the certificate encryption password as the password. This works with the
login dialog of the ODBC driver or within a SQLDriverConnect login string.</p>
<p>It is also possible to set up the ODBC client to check for the server's
X.509 certificate by specifying a client side CA (Certificate Authority) list.</p>
<p>The X509 certificate can be used for authentication with the
Virtuoso server via SSL. In this case the ODBC client will use an X.509
certificate which contains a SQL username as an extension.
This extension is added when the certificate is issued. Since an object
ID (OID) of the username attribute can be used any valid object identifier that
does not conflict with existing OIDs (for example 2.16.840.1.NNNNNN).
It will be best to have one's own registered OID for that purpose.</p>
<a name="secureodbcx509foafsll" />
<h4>7.3.2.1. WebID Protocol ODBC Login</h4>
<p>Virtuoso supports WebID Protocol ODBC Login. Thus, the INI parameter X509ClientVerify can accept the following values:</p>
<ul>
<li>0 - do not require</li>
<li>1 - ask for trusted certificates</li>
<li>2 - optionally ask, if certificate is given will be verified</li>
<li>3 - accept optionally any certificate even self-signed</li>
</ul>
<p>If certificate contains WebID, the odbc login will use it. The WebID can be setup to particular account via Conductor's
users interface. </p>
<p>A typical [Parameters] INI section should contain:</p>
<div>
<pre class="programlisting">
SSLServerPort = 1113
SSLCertificate = keys/server.crt
SSLPrivateKey = keys/server.key
X509ClientVerify = 3
</pre>
</div>
<p>The client could connect in following way:</p>
<div>
<pre class="programlisting">
isql 1113 "" -X client.p12 -T server.crt
</pre>
</div>
<p>Note: The client certificate client.p12 contains WebID which is registered to some sql user account via Conductor.</p>
<br />
<a name="secureodbccertgen" />
<h4>7.3.2.2. ODBC Client Certificate Generation Using "openssl" Tool</h4>
<p>To generate X509 certificates one can use the "openssl" tool obtained
freely from <a href="http://www.openssl.org/">www.openssl.org</a>.
The <span class="computeroutput">openssl.cnf</span> configuration file
must be edited to include the new extension for sqlUserName, but first we
have to find the hexadecimal representation of the SQL Username. Then
you can proceed the use the openssl to create and confirm the
certificate. Follow the steps below:</p>
<ol>
<li>
<p>
<strong>Find the HEX representation of the SQL Username</strong>
</p>
<p>You can either work this out from ASCII codes or use a hexdump utility
found on most Unix platforms. The following command would be sufficient,
replacing <user_name> with the actual username:</p>
<div>
<pre class="programlisting">
$ echo -n "<user_name>" | hexdump -e '":" 1/1 "%02X"'
</pre>
</div>
<p>For example, the username <span class="computeroutput">'dba'</span>
would be <span class="computeroutput">:64:62:61</span>.</p>
</li>
<li>
<p>
<strong>Edit the OpenSSL config file: openssl.cnf</strong>
</p>
<p>Open the file in a text editor of your choosing and add the following:</p>
<div>
<pre class="programlisting">
[ new_oids ]
sqlUserName=2.16.840.1.NNNNNN.1
...
[ usr_cert ]
...
sqlUserName = DER:NN:NN:NN:NN:NN.....
</pre>
</div>
<p>replacing <span class="computeroutput">:NN:NN.....</span> with the
hexadecimal representation of the username we discovered in the
previous step.</p>
</li>
<li>
<p>
<strong>Make New Certificate Request</strong>
</p>
<p>Make new certificate request using the command:</p>
<div>
<pre class="programlisting">
$ openssl req -new
</pre>
</div>
<p>The tool will ask for certain details. Once completed it will
generate a private key also.</p>
</li>
<li>
<p>
<strong>Generate Certificate</strong>
</p>
<p>Assuming the role of CA, generate a certificate using the request
from the previous step, using the following command:</p>
<div>
<pre class="programlisting">
$ openssl ca -in <req_file>
</pre>
</div>
<p>The tool will print the details of request and will ask you to sign and
commit the certificate into the CA repository.</p>
</li>
<li>
<p>
<strong>Verify New Certificate</strong>
</p>
<p>Check that the certificate contains the right SQL account name; use
the following command to obtain a text dump of the certificate:</p>
<div>
<pre class="programlisting">
openssl x509 -in <certfile> -text -noout
</pre>
</div>
<p>Now we can scan the contents of the output for the extension entries
we added earlier:</p>
<div>
<pre class="programlisting">
>>>>>
X509v3 extensions:
....
2.16.840.1.NNNNNNN.1:
<SqlAccountName>
^^^^^^ this should match the new OID ^^^^^
>>>>>
</pre>
</div>
</li>
</ol>
<br />
<a name="secureodbcclisrvsetup" />
<h4>7.3.2.3. Virtuoso Server Configuration</h4>
<p>The Virtuoso server can work in two modes based on an
SSL connection:</p>
<ul>
<li>
<strong>basic</strong> - this is when only connection is
secured, no certificate verification. The client is not required to have its own
trusted certificate.</li>
<li>
<strong>trusted</strong> - additional mechanisms are
enabled to check client's certificate. in this case the client is required to have
a trusted certificate.</li>
</ul>
<p>To allow Virtuoso to check the client's certificates, the virtuoso.ini
file must contain the following entries in [Parameters] section:</p>
<div>
<pre class="programlisting">
; Basic session encryption only parameters
SSLServerPort = 1113
SSLCertificate = ./srv.cert.pem ; server's certificate
SSLPrivateKey = ./srv.key.pem ; server's private key, must match the certificate
; Trusted operation parameters
X509ClientVerify = 1
X509ClientVerifyCAFile = ./ca.pem ; CA list; file containing certificates of acceptable CA
X509ClientVerifyDepth = 1 ; dependent of type of certificate checking can be &gt;1
X509ExtensionOID = 2.16.840.1.NNNNNNN.1 ; the OID value, same as that used to make client's certificates
</pre>
</div>
<p>All certificate/key files need to be in PEM format.</p>
<p>The server needs an "SSLCertificate" and "SSLPrivateKey" to begin
listening on the "SSLServerPort". These are essential for the secure
operations. Furthermore the certificate must match the private key;
non-matching certificate and private keys will prevent server startup and
an error will be logged.
The private key is required to guarantee that the certificate's claim is true.
The server certificate is used by the client to identify the server. The
client can retrieve and verify this key and choose whether the server
an be trusted depending on circumstances. </p>
<div class="note">
<div class="notetitle">Note:</div>
<p>basic operation (SSL/TLS) encryption only cannot be used to
identify a client with certificate. </p>
</div>
<p>The "X509ClientVerifyCAFile" is a file containing a CA's (Certificate
Authority) certificates that the server can use to verify client certificates.
The client certificate verification in general depends on the "X509ClientVerify" flag,
which enables or disable this feature. The 'X509ClientVerifyDepth"
parameter is used to verify to what degree server will trust the client's
certificate. The lower the value, the higher the restriction levels, with a
minimum value of 1. This means that the server will look in CA's certificates
to find who has issued the client certificate. If there is no matching CA
entry the connection will be rejected; If there is a matching entry then
verify the issuer chain; If issuer chain is greater than "X509ClientVerifyDepth",
the connection will be rejected.</p>
<p>All file paths above must be either absolute or relative to the server working directory. </p>
<div class="note">
<div class="notetitle">Note:</div>
<p>If primary key file is encrypted then the server must be started
in foreground mode so that a password can be supplied in order to open the file.
</p>
</div>
<br />
<a name="secureodbcclisetup" />
<h4>7.3.2.4. Virtuoso Client Configuration</h4>
<p>The following connection options control the client's behavior regarding SSL:</p>
<ul>
<li>
<strong>Encrypt</strong> - specifies type of secure
connection to be used. </li>
<li>
<strong>ServerCert</strong> - (optional) to specify which
certificate(s) are to be used to verify server certificates.</li>
</ul>
<p>The <strong>Encrypt</strong> option can be set to '1' to specify
a basic secure connection; no server identity verification will be performed.
Note that this is only possible when the server is also set to make
basic SSL connections.</p>
<p>To ensure server's identity the <strong>Encrypt</strong> option
must be set to full or relative (to application working directory) path to the file
containing client's certificate and private key. This file can be encoded in
PKCS#12 or PEM format. The certificate and private key contained may be
generated using the steps outlined above.</p>
<p>When the Encrypt option is set to point to a certificate file, the
<strong>ServerCert</strong> option must be set to the full or
relative path to file containing list of CA certificates in PEM format. The content
of this file will be used to verify server's certificate.</p>
<p>When using these options the UID connection-option must be set to
an empty string to enable certificate authorization. The PWD option will be
used to open the private key.</p>
<p>Here is an example of an ODBC connect-string:</p>
<div>
<pre class="programlisting">
...
connectString =
"HOST=localhost:1113;UID=;PWD=keysecret;ENCRYPT=c:\certs\mycertwithkey.p12;SERVERCERT=c:\certs\ca.pem"
...
</pre>
</div>
<p>If client's certificate does not contains user name for SQL login then
the server will try matching the certificate fingerprint against registered
certificates. If any SQL account has such a certificate registered it
will be used for login. Otherwise login will be rejected.</p>
<br />
<a name="secureodbcregkeys" />
<h4>7.3.2.5. Registering Keys with Virtuoso</h4>
<p>To enable a client certificate to be used for authorization,
the DBA must register it's MD5 fingerprint (checksum) in the database.
Registered certificates can be removed from system. Two functions
exist for the purposes: <span class="computeroutput">USER_CERT_REGISTER()</span>,
and <span class="computeroutput">USER_CERT_UNREGISTER()</span>. Both functions
rely on the MD5 checksum of the certificates being registered or un-registered,
therefor you have the option of supplying these functions with the certificate
file or the MD5 checksum directly.</p>
<p>The functions are:</p>
<p>
<span class="computeroutput">USER_CERT_REGISTER (user_name, certificate, password, type);</span>
</p>
<p>
<span class="computeroutput">USER_CERT_UNREGISTER (user_name, certificate, password, type);</span>
</p>
<p>The registered certificate's fingerprints are kept in the DB.DBA.SYS_USERS table as
vectors of strings under the 'LOGIN_CERTIFICATES' user option value (U_OPTS column).
The data stored there can be retrieved using a third function: </p>
<p>
<span class="computeroutput">USER_GET_OPTION()</span>
</p>
<p>For example, one might invoke:</p>
<div>
<pre class="programlisting">
USER_CERT_REGISTER ('DBA', 'file:/dba.pem', '', 'PEM');
</pre>
</div>
<p>Note that 'file:' URL is needed to designate
certificate is in a file on file system. </p>
<p>The above action can be performed also without certificate supplied
if the MD5 fingerprint is known:</p>
<div>
<pre class="programlisting">
USER_CERT_REGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');
</pre>
</div>
<p>and</p>
<div>
<pre class="programlisting">
USER_CERT_UNREGISTER ('DBA', 'D9:6D:47:D7:67:47:D7:3C:2C:E0:89:91:F3:BC:E7:59');
</pre>
</div>
<br />
<br />
<a name="virtmanconfodbcdsnunix" />
<h3>7.3.3. Manually configuring a Virtuoso ODBC DSN on Unix</h3>
<p>If you have <a href="http://www.iodbc.org">iODBC</a> installed, you can configure Virtuoso data sources by
adding the following entry into the relevant .odbc.ini file. Usually it is the value of the ODBCINI
environment variable or $HOME/.odbc.ini:
</p>
<p>Sample DSN:
</p>
<div>
<pre class="programlisting">
[LocalVirt]
Driver=/usr/local/lib/virtodbc_32.so
# absolute path to the shared object
Address=localhost:1111
# host and port of the Virtuoso server
</pre>
</div>
<p>If the application that will load the ODBC driver is multithreaded, use the virtodbc32_r.so driver instead.
</p>
<a name="virtmanconfodbcdsnunixlink" />
<h4>7.3.3.1. Linking Client Applications</h4>
<p>
The isql and other utilities are linked directly with the Virtuoso client code. See the Makefiles for the
libraries used. These are identical in function with the ODBC driver but accept a host:port in the place of
a data-source name to be resolved from the odbc ini file.
</p>
<p>Generally applications should pass via ODBC. Directly linking with the ODBC driver shared object
is also possible.
</p>
<br />
<a name="virtmanconfodbcdsnunixjdbc" />
<h4>7.3.3.2. JDBC</h4>
<p>If you specified:</p>
<div>
<pre class="programlisting">
--with-jdbc3=<path of JDK>
</pre>
</div>
<p>
to the configure in the installation root directory, running make will produce the files
libsrc/JDBCDriverType4/virtjdbc3.jar and virtjdbc3ssl.jar. These can be placed on the
Java class path. See <a href="installwin32.html#VirtuosoDriverJDBC">Virtuoso JDBC Documentation</a> for URL formats etc.
</p>
<br />
<br />
<a name="odbccompliance" />
<h3>7.3.4. ODBC Compliance</h3>
<p>The Virtuoso Driver for ODBC conforms to both the ODBC
1.x,2.x,and 3.x versions of the ODBC specification, it implements Core, Level 1, Level 2,
and Extensions functionality. It also has a native support for the wide versions of the
ODBC API (e.g. SQLColumnsW) in Windows. This driver enables you to communicate with local or remote
Virtuoso servers across any combination of platforms supported by Virtuoso.</p>
<a name="odbccompliance" />
<h4>7.3.4.1. ODBC API implementation details</h4>
<a name="SQLAllocHandle" />
<h5>7.3.4.1.1. SQLAllocHandle</h5>
<p>Virtuoso ODBC driver does not allow allocation and usage of
explicitly allocated descriptor handles. That is why the
SQLAllocHandle (SQL_HANDLE_DESC) will return an error.</p>
<br />
<a name="SQLBulkOperations" />
<h5>7.3.4.1.2. SQLBulkOperations</h5>
<p>Only the SQL_ADD operation is supported.</p>
<br />
<a name="SQLColAttributes" />
<h5>7.3.4.1.3. SQLColAttributes</h5>
<p>The virtuoso ODBC driver does not return information for the
following attributes:</p>
<ul>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_TABLE_NAME</strong>
<p>Returns an empty string instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_OWNER_NAME</strong>
<p>Returns an empty string instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_QUALIFIER_NAME</strong>
<p>Returns an empty string instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_CASE_SENSITIVE</strong>
<p>Returns 1 instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_AUTO_INCREMENT</strong>
<p>Returns 0 instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_MONEY</strong>
<p>Returns 0 instead</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SQL_COLUMN_UNSIGNED</strong>
<p>Returns 0 instead</p>
</div>
</li>
</ul>
<br />
<a name="SQLDriverConnect" />
<h5>7.3.4.1.4. SQLDriverConnect</h5>
<p>The Virtuoso ODBC driver recognizes the following
SQLDriverConnect connection string keywords:</p>
<ul>
<li>
<div class="formalpara">
<strong>DSN</strong>
<p>The data source name.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>HOST</strong>
<p>The virtuoso server host specification (in the form : [<hostname>[:]][<portnumber>]</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>UID</strong>
<p>The virtuoso user ID used to connect</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>PWD</strong>
<p>The login password used to connect</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>DATABASE</strong>
<p>The qualifier to use when connected (overrides the user's default qualifier)</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>CHARSET</strong>
<p>The name of the character set to use for wide/narrow conversions</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>DAYLIGHT</strong>
<p>Boolean parameter (1/0). When ON (1) it takes into account the
client OS daylight savings settings.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>ENCRYPT</strong>
<p>String parameter (file name or "1"). Specifies how the ODBC
connection will be encrypted. see the <a href="">Using
SSL For Secure ODBC connections</a> for details.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>PWDCLEAR</strong>
<p>Integer parameter (default to 0). Specifies how the password
will be sent over the wire. 0 - send digest of the password,
1 - send password in cleartext, 2 - send password garbaled.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>SERVERCERT</strong>
<p>String parameter (file name). Specifies the path for the
CA list used to verify the server's certificate (in PEM format).
see the <a href="">Using
SSL For Secure ODBC connections</a> for details.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>FORCE_DBMS_NAME</strong>
<p>String parameter (default "OpenLink Virtuoso"). When set it
alters the result of SQLGetInfo (SQL_DBMS_NAME).</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>NoSystemTables</strong>
<p>Boolean parameter (1/0) (default 0). When set it
alters the result of SQLTables () so that it won't find or return
tables of type SYSTEM TABLE.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>IsolationLevel</strong>
<p>String parameter ("Read Uncommitted"/"Read Committed"/"Repeatable Read"/"Serializable").
When set it specifies the initial transaction isolation mode for that connection.</p>
</div>
</li>
</ul>
<br />
<a name="SQLGetEnvAttr" />
<h5>7.3.4.1.5. SQLGetEnvAttr</h5>
<p>The SQL_ATTR_OUTPUT_NTS does not have any effect on the
Virtuoso driver. It allows the value to be set and retrieved
but with no further effect.</p>
<br />
<a name="SQLNativeSql" />
<h5>7.3.4.1.6. SQLNativeSql</h5>
<p>All ODBC syntax is parsed server side. The native SQL syntax for
Virtuoso is the ODBC syntax.</p>
<br />
<a name="not_supp" />
<h5>7.3.4.1.7. Not Supported ODBC API functions</h5>
<p>Virtuoso ODBC driver does not support the following ODBC API
functions:</p>
<ul>
<li>
<p>SQLCopyDesc</p>
</li>
</ul>
<br />
<br />
<br />
<a name="VScrlCURSORS" />
<h3>7.3.5. Virtuoso Scrollable Cursor Engine</h3>
<p>Virtuoso implements server side scrollable cursors.
ODBC 2.0, ODBC 3.5 and JDBC 2.0 API's are supported.
</p>
<p>Cursor types include:</p>
<ul>
<li>
<div class="formalpara">
<strong>Forward only</strong>
<p>This is the default, non-scrollable cursor.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Static</strong>
<p>The cursor's evaluation is computed when the cursor statement is first executed.
Positioned operations are possible but their effect will not show nor will changes be detected.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Keyset</strong>
<p>When the cursor is opened a keyset is built. Rows within the keyset
can be fetched and modified by positioned operations. Changes will show when refreshing
data and changes by other transactions will be detected for update and delete.
Inserts by the same or different transaction will not appear.
A keyset cursor may have a finite keyset size. When scrolling outside of
the given keyset the keyset will shift to cover the new rows.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Dynamic</strong>
<p>A dynamic cursor will reflect all changes by the same and different transactions.
The dynamic cursor's evaluation is constructed as needed, hence it generally has
less overhead than other types of cursors.</p>
</div>
</li>
<li>
<div class="formalpara">
<strong>Mixed</strong>
<p>A mixed cursor is a combination of a keyset-driven cursor and a dynamic cursor.
It is used when the result set is too large to reasonably generate a keyset for the entire result set.
Mixed cursors use a keyset smaller than the entire result set but larger than the rowset.
</p>
<p>
While the application is scrolling within the keyset, the behavior is keyset-driven.
When the application scrolls beyond the keyset, the behavior becomes dynamic to fetch
the requested rows and generate a new keyset.
The behavior then reverts back to keyset-driven within that keyset, as before.
</p>
</div>
</li>
</ul>
<a name="FwdOnlyCursors" />
<h4>7.3.5.1. Forward Only Cursors</h4>
<p>
A forward only cursor is substantially more efficient than a scrollable
cursor. It however does not allow positioned operations (the WHERE CURRENT OF SQL phrase),
or SQLSetPos. The SQLExtendedFetch function is supported but only the SQL_FETCH_NEXT
fetch type is then allowed.
</p>
<br />
<a name="CursorOperation" />
<h4>7.3.5.2. Cursor Operations</h4>
<p>Virtuoso supports all ODBC scrollable cursor operations. These include
</p>
<div class="formalpara">
<strong>SQLExtendedFetch / SQLScrollFetch fetch type</strong>
<ul>
<li>
<p>SQL_FETCH_FIRST</p>
</li>
<li>
<p>SQL_FETCH_LAST</p>
</li>
<li>
<p>SQL_FETCH_NEXT</p>
</li>
<li>
<p>SQL_FETCH_PRIOR</p>
</li>
<li>
<p>SQL_FETCH_RELATIVE</p>
</li>
<li>
<p>SQL_FETCH_ABSOLUTE</p>
</li>
<li>
<p>SQL_FETCH_BOOKMARK</p>
</li>
</ul>
</div>
<div class="formalpara">
<strong>SQLSetPos operations</strong>
<ul>
<li>
<p>SQL_POSITION</p>
</li>
<li>
<p>SQL_REFRESH</p>
</li>
<li>
<p>SQL_ADD</p>
</li>
<li>
<p>SQL_UPDATE</p>
</li>
<li>
<p>SQL_DELETE</p>
</li>
</ul>
</div>
<p>
Positioned SQL statements, i.e. the WHERE CURRENT OF clause, is supported for
scrollable cursors.
</p>
<br />
<a name="CursorOptions" />
<h4>7.3.5.3. Cursor Options</h4>
<p>The cursor options
<ul>
<li>
<p>SQL_CURSOR_TYPE</p>
</li>
<li>
<p>SQL_CONCURRENCY</p>
</li>
<li>
<p>SQL_KEYSET_SIZE</p>
</li>
<li>
<p>Cursor name (SQLSetCursorName)</p>
</li>
</ul>
have to be set before a SQLPrepare or SQLExecDirect.
</p>
<p>
The SQL_ROWSET_SIZE can be varied while a cursor is open.
</p>
<br />
<a name="CursorsTransactions" />
<h4>7.3.5.4. Cursors and Transactions</h4>
<p>
All forward only or scrollable cursors survive committing or rolling back transactions.
A cursor maintains its position over a transaction's end. Hence the
next fetch operation will resume from the correct place. If a dynamic cursor's current row / rowset is
deleted, the cursor will continue from the next greater / lesser row in the order of the cursor's
ordering columns. This also applies to mixed mode (keyset with finite keyset size)
cursors scrolling outside of the keyset bounds. A forward only cursor will retain its logical position across commit/rollback.
</p>
<p>
The SQL_CONCURRENCY values of SQL_CONCUR_READ_ONLY and SQL_CONCUR_LOCK
cause Virtuoso to lock the rows in the keyset / rowset in shared or exclusive mode, respectively.
</p>
<br />
<a name="OptimisticConcurrency" />
<h4>7.3.5.5. Optimistic Concurrency Control</h4>
<p>
A scrollable cursor may have a SQL_CONCURRENCY setting of SQL_CONCUR_VALUES. This
enables optimistic concurrency control. This is a mechanism which will reflect an
update or delete of a row if the row has been modified by a third party after the time the
application last read the row.
</p>
<p>
A 'updated meanwhile' condition detected in this manner will prevent the operation and
return a SQL state of 01001 with SQL_SUCCESS_WITH_INFO from SQLSetPos.
</p>
<p>
The updates and deletes made through SQLSetPos are still subject to being committed or rolled
back by normal transaction control. The 01001 state does not prevent the current transaction
from committing.
</p>
<p>
The 01001 state is only detected if the update or delete is made by SQLSetPos and the row
at hand has been changed by any statement of any transaction. If the update of updated
operation is carried out by any other operation than SQLSetPos of the statement that last read
the value the condition cannot be detected.
</p>
<p>
Note that the time between the last read and the SQLSetPos update can be long
and can span multiple transactions.
</p>
<br />
<a name="CursorInfo" />
<h4>7.3.5.6. Cursor Information</h4>
<table class="data">
<caption>Table: 7.3.5.6.1. Cursor Support</caption>
<tr>
<th class="data" />
<th class="data">Static</th>
<th class="data">Keyset</th>
<th class="data">Dynamic</th>
</tr>
<tr>
<td class="data">SQLRowCount</td>
<td class="data">x</td>
<td class="data">x</td>
<td class="data" />
</tr>
<tr>
<td class="data">SQL_BOOKMARK</td>
<td class="data">x</td>
<td class="data">x</td>
<td class="data">x</td>
</tr>
<tr>
<td class="data">SQL_ROW_NUMBER</td>
<td class="data">x</td>
<td class="data">x</td>
<td class="data" />
</tr>
<tr>
<td class="data">reflect update</td>
<td class="data" />
<td class="data">x</td>
<td class="data">x</td>
</tr>
<tr>
<td class="data">reflect delete</td>
<td class="data" />
<td class="data" />
<td class="data">x</td>
</tr>
<tr>
<td class="data">reflect inx</td>
<td class="data" />
<td class="data" />
<td class="data">x</td>
</tr>
<tr>
<td class="data">Notice update</td>
<td class="data">x</td>
<td class="data">x</td>
<td class="data">x</td>
</tr>
<tr>
<td class="data">Notice delete</td>
<td class="data">x</td>
<td class="data">x</td>
<td class="data">x</td>
</tr>
</table>
<br />
<p>
SQLRowCount is set after the initial SQLExecute. This is the actual row count or the keyset size
for a mixed mode cursor. A dynamic cursor does not know this since it builds the evaluation as
needed.
</p>
<p>
All cursors support bookmarks.
</p>
<p>
The SQL_ROW_NUMBER is the ordinal number of the current row in the
cursor's evaluation. A dynamic cursor cannot know this, as the scrolling may
start from end and rows may appear on either side of the current row while the cursor is open.
</p>
<p>
'reflect' means that the new values or added / deleted rows
appear when re-scrolling over the rows. A deletion is reflected by
omitting the row.
</p>
<p>
'notice' means that the row is flagged by SQL_UPDATE, SQL_DELETED
or SQL_ADDED in the SQLExtendedFetch status array when the cursor re-scrolls over the
rows in question.
</p>
<br />
<a name="CursorsVDB" />
<h4>7.3.5.7. Cursors and Virtual Database</h4>
<p>
The cursor subsystem modifies the cursor's defining select statement to
make various backward and forward read statements, update and delete statements etc.
These transformations can be seen through the explain function.
</p>
<p>
Since cursors are implemented by generating SQL statements these work transparently
against remote database, independently of their possible native cursor support.
</p>
<br />
<a name="CursorSpecialCases" />
<h4>7.3.5.8. Cursor Special Cases</h4>
<p>
SQL SELECT statements fall in two categories: Simple, which consist of
one or more tables, an arbitrary WHERE clause and an optional ORDER BY, and Complex, which includes
all other SELECT's, e.g. ones with GROUP BY, derived tables, full outer joins, UNION's etc.
</p>
<p>
A simple statement can easily be modified to read backwards or forwards from a given point. Also,
each result row of a simple statement has a physical counterpart, or many physical counterparts
for a join. Having a physical counterpart makes it possible to define the meaning of a positioned
update or delete. A GROUP BY is a counter-example.
</p>
<p>
All complex SQL statements occurring as cursors are processed as static
cursors regardless of the declared cursor type.
</p>
<br />
<a name="CursorPerf" />
<h4>7.3.5.9. Cursors and Performance</h4>
<p>
If a cursor's ordering corresponds to an ordering in an index, dynamic cursors will generally perform
best. This is understandable since the engine can quickly locate
the current row based on an index and then just read backward or forward on that index.
On the other hand, if the result set is very sparsely spread over the table or if
there is no ordering index dynamic cursors will have a substantial penalty.
</p>
<p>
If used as a forward only cursor in a single transaction, a dynamic cursor is only some
30% slower than a forward only cursor.
</p>
<p>
A static or keyset cursor has a relatively long execute time since the initial execute
will make the entire keyset. The initial execute of a dynamic cursor is instantaneous
since the fetch operations will do the work as needed.
</p>
<p>
With most off the shelf applications, e.g. Microsoft ADO, static and keyset cursors
are preferable because the applications may rely on row counts and row numbers which
are not known for dynamic cursors.
</p>
<p>
Positioned operations are not affected by cursor type.
</p>
<br />
<br />
<a name="CONN_STMT_OPTIONS" />
<h3>7.3.6. Effect of Connection & Statement Options</h3>
<a name="ConnOpt" />
<h4>7.3.6.1. Connection Options</h4>
<a name="SQL_AUTOCOMMIT" />
<h5>7.3.6.1.1. SQL_AUTOCOMMIT</h5>
<p>
The autocommit option is set at the connection level and affects
every statement executed after it being set. Setting the option
does not communicate itself with the server and is therefore fast.
</p>
<p>
Autocommit is on by default. Autocommitting SELECT statements are executed
with read committed isolation. This is appropriate since any update
based on the autocommitting read would be in a different transaction and hence
would block to wait for access to the selected row. Also re-evaluating
a select in autocommit mode would read the data in a different transaction.
Hence there is no point in repeatable read isolation for autocommitting
cursors. Cursors inside procedures have the normal repeatable read isolation
regardless of whether the procedure was called in autocommit mode.
</p>
<p>
When an autocommitting statement starts it is executed in the transaction
That is the connection's current transaction when it is received. Before
starting the autocommitting statement sets the connection's current
transaction to a new one. In this manner a client can issue multiple
asynchronous autocommitting statements at the same time and the statements
will execute concurrently, each in its own transaction.
</p>
<p>
If array parameters are used in a statement on an autocommitting
connection each parameter row will be processed in its own transaction
in sequential order. Multiple Asynchronous statements must be used to
execute one client's statements in parallel.
</p>
<p>To commit or roll back a transaction in manual-commit mode, an
application should call SQLEndTran. Applications should not attempt
to commit or roll back transactions by executing COMMIT or ROLLBACK
statements with SQLExecute or SQLExecDirect. The effects of doing
this are undefined.</p>
<br />
<a name="SQL_TXN_ISO" />
<h5>7.3.6.1.2. SQL_TXN_ISOLATION</h5>
<p>
This option allows all the values defined in ODBC,
</p>
<p>
The isolation of an operation is the property of the operation rather than
of the transaction within which it takes place. Once an operation
has started, e.g. a cursor has been opened, its isolation cannot be changed.
</p>
<p>
The value of this option will affect any subsequently executed statement.
Note that setting this option to different values during a transaction will work,
thus a transaction can have cursors with different isolations although that
is presumably not the intention of the ODBC specification.
</p>
<p>
See the transaction model for a definition of the different isolation levels.
</p>
<p>
A statement in autocommit mode executes
in the same transaction as the previous statement. The transaction is
committed when the statement successfully completes. The next statement in
the connection will execute in the fresh transaction that was associated to
the connection at the start of the previous autocommitting statement.
</p>
<p>
As a consequence of this multiple concurrent autocommitting transactions may execute
on the same connection at the same time.
</p>
<p>
Statements executed with array parameters execute each set of
parameters as a separate transaction if the connection is in autocommit
mode.
</p>
<br />
<a name="SQL_ACCESS_MODE" />
<h5>7.3.6.1.3. SQL_ACCESS_MODE</h5>
<p>
This has the effect of reversing any SQL_CONCUR_LOCK concurrency to SQL_CONCUR_READ_ONLY.
The statement option's value is not changed though.
</p>
<br />
<a name="SQL_CURRENT_QUAL" />
<h5>7.3.6.1.4. SQL_CURRENT_QUALIFIER</h5>
<p>
This sets or gets the current qualifier. The initial
value is obtained from the server at connect time. The values reflect the
effects of any USE statements.
</p>
<br />
<a name="SQL_NO_CHAR_C_ESCAPE" />
<h5>7.3.6.1.5. SQL_NO_CHAR_C_ESCAPE (=5002)</h5>
<p>
This has the same effect as the NO_CHAR_C_ESCAPE option in the SET statement. It takes boolean int values (0/non-0)
</p>
<br />
<a name="SQL_CHARSET" />
<h5>7.3.6.1.6. SQL_CHARSET (=5003)</h5>
<p>
This has the same effect as the CHARSET option in SET statement. It takes string values (the name of the
character set to use).
</p>
<br />
<a name="SQL_ENCRYPT_CONNECTION" />
<h5>7.3.6.1.7. SQL_ENCRYPT_CONNECTION (=5004)</h5>
<p>
Usable only with the Virtuoso CLI (because the ODBC/iODBC driver manager does not pass-through the custom
options to the driver on SQLConnect/SQLDriverConnect). When set to the string "1" means use SSL but no
X509 certificates. Setting it to a NULL (default) means no encryption of the ODBC connection.
Any other string is treated as a file name of one PKCS12 package to get the data from for establishing an
encrypted SSL connection using X509 certificates (see the -E/-X ISQL options).
</p>
<br />
<a name="SQL_SHUTDOWN_ON_CONNECT" />
<h5>7.3.6.1.8. SQL_SHUTDOWN_ON_CONNECT (=5005)</h5>
<p>
Usable only with the Virtuoso CLI (because the ODBC/iODBC driver manager does not pass through the custom
options to the driver on SQLConnect/SQLDriverConnect). Shuts down the server on connection after authenticating
the DBA user (see the -K ISQL option).
</p>
<br />
<br />
<a name="SQLSetStmtOption" />
<h4>7.3.6.2. SQLSetStmtOption Statement Options</h4>
<p>
Virtuoso supports all ODBC 2.x and ODBC 3.x statement options.
The following options are mentioned below due to implementation specific
details.
</p>
<a name="SQLConcurrency" />
<h5>7.3.6.2.1. SQL_CONCURRENCY</h5>
<p>
The supported values are SQL_CONCUR_READ_ONLY, SQL_CONCUR_LOCK and SQL_CONCUR_VALUES, the
last option is only available for scrollable cursors. A select statement
with SQL_CONCUR_READ_ONLY will make shared locks when locking for repeatable
read or serializable transactions. The SQL_CONCUR_LOCK for a select statement
will cause it to make exclusive locks, as if it had the FOR UPDATE clause specified.
</p>
<p>
See the section on scrollable cursors for the effect of SQL_CONCUR_VALUES. For
all statements except scrollable cursors this value reverts to SQL_CONCUR_READ_ONLY.
</p>
<p>
Any searched update or delete statements will make exclusive locks in all cases.
</p>
<br />
<a name="SQLMaxRows" />
<h5>7.3.6.2.2. SQL_MAX_ROWS</h5>
<p>
This option only affects static cursors.
</p>
<br />
<a name="SQLTxnTimeout" />
<h5>7.3.6.2.3. SQL_TXN_TIMEOUT</h5>
<p>
This is an extension that allows setting a time limit on the current transaction.
The time limit starts at the execution of the statement specifying this option.
The transaction will terminate the indicated number of seconds after the execute
whether the statement has completed or not.</p>
<br />
<a name="SQLPrefetchSize" />
<h5>7.3.6.2.4. SQL_PREFETCH_SIZE</h5>
<p>
This is an extension option that controls how many rows of a forward only cursor are
prefetched by the execute and fetch calls. A high value is advantageous for
long consecutive reads since it cuts down on the number of client server messages
exchanged. On the other hand a large value can result in unnecessary data transfer
and locking if only the first few rows of a cursor are fetched. A value of -1 will
cause the entire rowset to be fetched at the execute, so that no matter the select size, only
one message is exchanged. The default value is 20. This can also be set in the virtuoso.ini file.
</p>
<p>
A select with array parameters will always work as with a SQL_PREFETCH_SIZE of -1, meaning that
all the result sets are computed and sent to the client by the SQLExecute
call that opens the cursor.
</p>
<br />
<a name="SQLCursorType" />
<h5>7.3.6.2.5. SQL_CURSOR_TYPE</h5>
<br />
<a name="KeySetSize" />
<h5>7.3.6.2.6. SQL_KEYSET_SIZE</h5>
<br />
<a name="SQLConcur" />
<h5>7.3.6.2.7. SQL_CONCURRENCY</h5>
<br />
<p>
The cursor type options should be set before preparing a statement.
Other options may be set at any time. The rowset and prefetch options should not be modified after executing a SELECT.
</p>
<a name="SQLGETLASTSERIAL" />
<h5>7.3.6.2.8. SQL_GETLASTSERIAL</h5>
<p>
This is an extension that returns the last assigned identity column
value. The return buffer pvParam is of type SQLINTEGER. For this to be
meaningful, the statement in question must be an INSERT into a table
that has an identity column. Note that if there are more than one
identity columns or if triggers make inserts with identity columns the
value will be undefined.
</p>
<br />
<br />
<br />
<a name="EFFICIENTAPI" />
<h3>7.3.7. Efficient Use of API</h3>
<p>
DO NOT USE SQLExecDirect. If a statement is executed more than once it
is much faster to prepare the statement with SQLPrepare and then use
SQLExecute repeatedly. The system only compiles the statement once,
therefore there is no parsing overhead for repeatedly compiling the
same text.
</p>
<p>
Array parameters for insert, update or single row selects are about
twice as fast as the same operations on a single parameter set.
</p>
<p>
The OR connective in SQL can result in slow queries with extensive
locking.
</p>
<p>
Autocommit should be used when possible, i.e. make the last statement of
a transaction autocommitting to avoid having to commit the transaction
as a separate operation.
</p>
<br />
<a name="EXECPYTHONSCRIPT" />
<h3>7.3.8. Executing SQL from Python script</h3>
<p>In order to execute SQL from Python script, you need to add the following lines to the /etc/odbc.ini
file:</p>
<div>
<pre class="programlisting">
[Local Virtuoso]
Driver = /PREFIX/lib/virtodbc_r.so
Address = localhost:1111
</pre>
</div>
<p>where PREFIX is replaced by the full path where Virtuoso is installed and also assuming that
is used let's say port 1111 in virtuoso.ini (which is set by default).
</p>
<p>Then you should be able to connect with:</p>
<div>
<pre class="programlisting">
c = pyodbc.connect('DSN=Local Virtuoso;UID=dba;PWD=dba')
</pre>
</div>
<br />
<a name="odbcimplementationext" />
<h3>7.3.9. Extensions</h3>
<a name="virtodbcsparql" />
<h4>7.3.9.1. Virtuoso ODBC RDF Extensions for SPASQL</h4>
<p>The Virtuoso ODBC Driver adds a number of defines to the ODBC API to allow an ODBC compliant
application to query meta information on SPASQL queries.</p>
<p>If the application uses the iODBC Driver Manager V3.52.7 or higher, it can simply include the
iodbcext.h header file, which contains information on extensions of many ODBC drivers like DB2, SQL
Server and Virtuoso.</p>
<p>If however the application is compiled against another Driver Manager, like the Microsoft
Driver Manager on Windows, the following construction should to be used:</p>
<div>
<pre class="programlisting">
#ifdef WIN32
# include <windows.h>
#endif
#include <sql.h>
#include <sqlext.h>
#if defined (HAVE_IODBC)
#include <iodbcext.h>
#endif
/*
* Include Virtuoso ODBC extensions for SPASQL result set
*/
#if !defined (SQL_DESC_COL_DV_TYPE)
/*
* ODBC extensions for SQLGetDescField
*/
# define SQL_DESC_COL_DV_TYPE 1057L
# define SQL_DESC_COL_DT_DT_TYPE 1058L
# define SQL_DESC_COL_LITERAL_ATTR 1059L
# define SQL_DESC_COL_BOX_FLAGS 1060L
# define SQL_DESC_COL_LITERAL_LANG 1061L
# define SQL_DESC_COL_LITERAL_TYPE 1062L
/*
* Virtuoso - ODBC SQL_DESC_COL_DV_TYPE
*/
# define VIRTUOSO_DV_DATE 129
# define VIRTUOSO_DV_DATETIME 211
# define VIRTUOSO_DV_DOUBLE_FLOAT 191
# define VIRTUOSO_DV_IRI_ID 243
# define VIRTUOSO_DV_LONG_INT 189
# define VIRTUOSO_DV_NUMERIC 219
# define VIRTUOSO_DV_RDF 246
# define VIRTUOSO_DV_SINGLE_FLOAT 190
# define VIRTUOSO_DV_STRING 182
# define VIRTUOSO_DV_TIME 210
# define VIRTUOSO_DV_TIMESTAMP 128
# define VIRTUOSO_DV_TIMESTAMP_OBJ 208
/*
* Virtuoso - ODBC SQL_DESC_COL_DT_DT_TYPE
*/
# define VIRTUOSO_DT_TYPE_DATETIME 1
# define VIRTUOSO_DT_TYPE_DATE 2
# define VIRTUOSO_DT_TYPE_TIME 3
/*
* Virtuoso - ODBC SQL_DESC_COL_BOX_FLAGS
*/
#define VIRTUOSO_BF_IRI 0x1
#define VIRTUOSO_BF_UTF8 0x2
#define VIRTUOSO_BF_DEFAULT_ENC 0x4
#endif
</pre>
</div>
<a name="virtodbcsparqlapi" />
<h5>7.3.9.1.1. API</h5>
<a name="virtodbcsparqlapisgdf" />
<h6>SQLGetDescField</h6>
<p>Before the application can retrieve the column meta data using <span class="computeroutput">SQLGetDescField</span>,
it first needs to retrieve the correct descriptor handle attached to the statement handle:</p>
<div>
<pre class="programlisting">
SQLHDESC hdesc = NULL;
SQLRETURN rc;
rc = SQLGetStmtAttr (hstmt, SQL_ATTR_IMP_ROW_DESC, &hdesc, SQL_IS_POINTER, NULL);
if (!SQL_SUCCEEDED(rc))
{
/* Handle error */
}
</pre>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_DV_TYPE</strong>
<p>Retrieves the datatype of a field.</p>
<div>
<pre class="programlisting">
SQLINTEGER dvtype;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_DV_TYPE, &dvtype, SQL_IS_INTEGER, NULL);
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span> or <span class="computeroutput">SQL_SUCCESS_WITH_INFO</span>, the dvtype variable will contain
the underlying Virtuoso datatype as indicated in the following table:</p>
<div>
<pre class="programlisting">
#define VIRTUOSO_DV_DATE 129
#define VIRTUOSO_DV_DATETIME 211
#define VIRTUOSO_DV_DOUBLE_FLOAT 191
#define VIRTUOSO_DV_IRI_ID 243
#define VIRTUOSO_DV_LONG_INT 189
#define VIRTUOSO_DV_NUMERIC 219
#define VIRTUOSO_DV_RDF 246
#define VIRTUOSO_DV_SINGLE_FLOAT 190
#define VIRTUOSO_DV_STRING 182
#define VIRTUOSO_DV_TIME 210
#define VIRTUOSO_DV_TIMESTAMP 128
#define VIRTUOSO_DV_TIMESTAMP_OBJ 208
</pre>
</div>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_DT_DT_TYPE</strong>
<p>Retrieves the date subtype of a field.</p>
<div>
<pre class="programlisting">
SQLINTEGER dv_dt_type;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_DT_DT_TYPE, &dv_dt_type, SQL_IS_INTEGER, NULL);
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span> or <span class="computeroutput">SQL_SUCCESS_WITH_INFO</span>, the dttype variable will contain the
underlying Virtuoso date subtype as indicated in the following table:</p>
<div>
<pre class="programlisting">
#define VIRTUOSO_DT_TYPE_DATETIME 1
#define VIRTUOSO_DT_TYPE_DATE 2
#define VIRTUOSO_DT_TYPE_TIME 3
</pre>
</div>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_LITERAL_ATTR</strong>
<p>Retrieves the literal attributes associated with the field.</p>
<div>
<pre class="programlisting">
SQLINTEGER littype;
SQLINTEGER lang, type;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_DT_DT_TYPE, &littype, SQL_IS_INTEGER, NULL);
lang = (littype >> 16) & 0xFFFF;
type = littype & 0xFFFF;
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span> or <span class="computeroutput">SQL_SUCCESS_WITH_INFO</span>,
the littype variable will contain the encoded language and rdf type information of the field.</p>
<p>These numbers are uniq to the database the client has connected to, and correspond to information
in the <span class="computeroutput">DB.DBA.RDF_LANGUAGE</span> and <span class="computeroutput">DB.DBA.RDF_DATATYPE</span>
tables:</p>
<div>
<pre class="programlisting">
select RL_ID from DB.DBA.RDF_LANGUAGE where RL_TWOBYTE = ?
</pre>
</div>
<p>and</p>
<div>
<pre class="programlisting">
select RDT_QNAME from DB.DBA.RDF_DATATYPE where RDT_TWOBYTE = ?
</pre>
</div>
<div class="note">
<div class="notetitle">Note:</div>
<p>This call is deprecated in favor of using the <span class="computeroutput">SQL_DESC_COL_LITERAL_LANG</span>
and <span class="computeroutput">SQL_DESC_LITERAL_TYPE</span> options of <span class="computeroutput">SQLGetDescField</span>
which caches these lookups to speed up describe operations.</p>
</div>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_BOX_FLAGS</strong>
<p>Retrieves the flags associated with the field:</p>
<div>
<pre class="programlisting">
SQLINTEGER flags;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_BOX_FLAGS, &flags, SQL_IS_INTEGER, NULL);
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span>
or <span class="computeroutput">SQL_SUCCESS_WITH_INFO</span>, the following
bitmasks can be used to determine the representation of the field:</p>
<div>
<pre class="programlisting">
#define VIRTUOSO_BF_IRI 0x1
#define VIRTUOSO_BF_UTF8 0x2
#define VIRTUOSO_BF_DEFAULT_ENC 0x4
for example:
flags description
0 field contains a Latin1 encoded literal string
1 field contains an IRI (always UTF-8 encoded)
2 field contains an UTF-8 encoded literal string
3 field contains an UTF-8 encoded IRI (should not happen)
</pre>
</div>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_LITERAL_LANG</strong>
<p>Retrieves the language string for this field:</p>
<div>
<pre class="programlisting">
SQLCHAR langbuf[100];
SQLINTEGER len1;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_LITERAL_LANG, langbuf, sizeof (langbuf), &len1);
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span> or
<span class="computeroutput">SQL_SUCCESS_WITH_INFO,</span>, the langbuf variable will contain
the language of the field.</p>
</div>
<div class="formalpara">
<strong>SQLGetDescField - SQL_DESC_COL_LITERAL_TYPE</strong>
<p>
Retrieves the data type string for this field:</p>
<div>
<pre class="programlisting">
SQLCHAR typebuf[100];
SQLINTEGER len2;
SQLRETURN rc;
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_LITERAL_TYPE, typebuf, sizeof (typebuf), &len2);
</pre>
</div>
<p>If this call returns <span class="computeroutput">SQL_SUCCESS</span> or
<span class="computeroutput">SQL_SUCCESS_WITH_INFO</span>, the typebuf variable will contain
the rdf type of the field.</p>
</div>
<br />
<br />
<a name="virtodbcsparqlevalnodes" />
<h5>7.3.9.1.2. Evaluating Nodes</h5>
<p>The following pseudo code evaluates the various type and flag information retrieved using
the above API calls and shows what kind of node a field is.</p>
<div>
<pre class="programlisting">
switch (dvtype)
{
case VIRTUOSO_DV_STRING:
{
if (flag == 1)
{
if (strncmp ((char *) data, "_:", 2) == 0)
{
/* node is a Turtle style named BNode */
}
else
{
/* node is an URI string */
}
}
else
{
if (strncmp ((char *) data, "nodeID://", 9) == 0)
{
/* node is a BNode */
}
else
{
/* node is a string literal */
}
}
break;
}
case VIRTUOSO_DV_RDF:
/* node is a typed literal with possible lang and type */
break;
case VIRTUOSO_DV_LONG_INT:
/* node is a literal http://www.w3.org/2001/XMLSchema#integer */
break;
case VIRTUOSO_DV_SINGLE_FLOAT:
/* node is a literal http://www.w3.org/2001/XMLSchema#float */
break;
case VIRTUOSO_DV_DOUBLE_FLOAT:
/* node is a literal http://www.w3.org/2001/XMLSchema#double */
break;
case VIRTUOSO_DV_NUMERIC:
/* node is a literal http://www.w3.org/2001/XMLSchema#decimal */
break;
case VIRTUOSO_DV_TIMESTAMP:
case VIRTUOSO_DV_DATE:
case VIRTUOSO_DV_TIME:
case VIRTUOSO_DV_DATETIME:
switch (dv_dt_type)
{
case VIRTUOSO_DT_TYPE_DATE:
/* node is a literal http://www.w3.org/2001/XMLSchema#date */
break;
case VIRTUOSO_DT_TYPE_TIME:
/* node is a literal http://www.w3.org/2001/XMLSchema#time */
break;
default:
/* node is a literal http://www.w3.org/2001/XMLSchema#dateTime */
break;
}
break;
case VIRTUOSO_DV_IRI_ID:
/*
* node is an IRI ID
*
* This type is only returned in output:valmode "LONG"
* It needs to be translated into a literal string using the
* ID_TO_IRI() function as the value is database specific.
*/
break;
default:
/* unhandled type */
return NULL;
}
</pre>
</div>
<br />
<a name="virtodbcsparqlexample" />
<h5>7.3.9.1.3. Examples</h5>
<p>The following program performs a SPARQL query against a Virtuoso Database using SPASQL. Note
that the connection parameters and the sparql query are compiled into the executable.</p>
<p>To compile it on Linux against iODBC the following command can be used:</p>
<div>
<pre class="programlisting">
gcc -o odbc_iri -I/usr/local/iODBC odbc_iri.c -L/usr/local/iODBC/lib -liodbc -ldl
</pre>
</div>
<p>It can then be called as:</p>
<div>
<pre class="programlisting">
./odbc_iri
</pre>
</div>
<p>which will print out the first 50 triples from the database in N3 format.</p>
<p>Here it is the source code:</p>
<div>
<pre class="programlisting">
/*
* odbc_iri.c
*
* This file is part of the OpenLink Software Virtuoso Open-Source (VOS)
* project.
*
* Copyright (C) 1998-2009 OpenLink Software
*
* This project is free software; you can redistribute it and/or modify it
* under the terms of the GNU General Public License as published by the
* Free Software Foundation; only version 2 of the License, dated June 1991.
*
* This program is distributed in the hope that it will be useful, but
* WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* General Public License for more details.
*
* You should have received a copy of the GNU General Public License along
* with this program; if not, write to the Free Software Foundation, Inc.,
* 51 Franklin St, Fifth Floor, Boston, MA 02110-1301 USA
*
*/
#include <stdio.h>
#include <string.h>
#ifdef WIN32
# include <windows.h>
#endif
#include <sql.h>
#include <sqlext.h>
#if defined (HAVE_IODBC)
#include <iodbcext.h>
#endif
/*
* Include Virtuoso ODBC extensions for SPASQL result set
*/
#if !defined (SQL_DESC_COL_DV_TYPE)
/*
* ODBC extensions for SQLGetDescField
*/
# define SQL_DESC_COL_DV_TYPE 1057L
# define SQL_DESC_COL_DT_DT_TYPE 1058L
# define SQL_DESC_COL_LITERAL_ATTR 1059L
# define SQL_DESC_COL_BOX_FLAGS 1060L
# define SQL_DESC_COL_LITERAL_LANG 1061L
# define SQL_DESC_COL_LITERAL_TYPE 1062L
/*
* Virtuoso - ODBC SQL_DESC_COL_DV_TYPE
*/
# define VIRTUOSO_DV_DATE 129
# define VIRTUOSO_DV_DATETIME 211
# define VIRTUOSO_DV_DOUBLE_FLOAT 191
# define VIRTUOSO_DV_IRI_ID 243
# define VIRTUOSO_DV_LONG_INT 189
# define VIRTUOSO_DV_NUMERIC 219
# define VIRTUOSO_DV_RDF 246
# define VIRTUOSO_DV_SINGLE_FLOAT 190
# define VIRTUOSO_DV_STRING 182
# define VIRTUOSO_DV_TIME 210
# define VIRTUOSO_DV_TIMESTAMP 128
# define VIRTUOSO_DV_TIMESTAMP_OBJ 208
/*
* Virtuoso - ODBC SQL_DESC_COL_DT_DT_TYPE
*/
# define VIRTUOSO_DT_TYPE_DATETIME 1
# define VIRTUOSO_DT_TYPE_DATE 2
# define VIRTUOSO_DT_TYPE_TIME 3
/*
* Virtuoso - ODBC SQL_DESC_COL_BOX_FLAGS
*/
#define VIRTUOSO_BF_IRI 0x1
#define VIRTUOSO_BF_UTF8 0x2
#define VIRTUOSO_BF_DEFAULT_ENC 0x4
#endif
SQLHANDLE henv = SQL_NULL_HANDLE;
SQLHANDLE hdbc = SQL_NULL_HANDLE;
SQLHANDLE hstmt = SQL_NULL_HANDLE;
#define MAXCOLS 25
int
ODBC_Errors (char *where)
{
unsigned char buf[250];
unsigned char sqlstate[15];
/*
* Get statement errors
*/
while (SQLError (henv, hdbc, hstmt, sqlstate, NULL, buf, sizeof (buf), NULL) == SQL_SUCCESS)
{
fprintf (stdout, "STMT: %s || %s, SQLSTATE=%s\n", where, buf, sqlstate);
}
/*
* Get connection errors
*/
while (SQLError (henv, hdbc, SQL_NULL_HSTMT, sqlstate, NULL, buf, sizeof (buf), NULL) == SQL_SUCCESS)
{
fprintf (stdout, "CONN:%s || %s, SQLSTATE=%s\n", where, buf, sqlstate);
}
/*
* Get environment errors
*/
while (SQLError (henv, SQL_NULL_HDBC, SQL_NULL_HSTMT, sqlstate, NULL, buf, sizeof (buf), NULL) == SQL_SUCCESS)
{
fprintf (stdout, "ENV:%s || %s, SQLSTATE=%s\n", where, buf, sqlstate);
}
return -1;
}
int
ODBC_Disconnect (void)
{
if (hstmt)
SQLFreeHandle (SQL_HANDLE_STMT, hstmt);
hstmt = SQL_NULL_HANDLE;
if (hdbc)
SQLDisconnect (hdbc);
if (hdbc)
SQLFreeHandle (SQL_HANDLE_DBC, hdbc);
hdbc = SQL_NULL_HANDLE;
if (henv)
SQLFreeHandle (SQL_HANDLE_ENV, henv);
henv = SQL_NULL_HANDLE;
return 0;
}
int
ODBC_Connect (char *dsn, char *usr, char *pwd)
{
SQLRETURN rc;
/* Allocate environment handle */
rc = SQLAllocHandle (SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
if (!SQL_SUCCEEDED (rc))
goto error;
/* Set the ODBC version environment attribute */
rc = SQLSetEnvAttr (henv, SQL_ATTR_ODBC_VERSION, (void *) SQL_OV_ODBC3, 0);
if (!SQL_SUCCEEDED (rc))
goto error;
/* Allocate connection handle */
rc = SQLAllocHandle (SQL_HANDLE_DBC, henv, &hdbc);
if (!SQL_SUCCEEDED (rc))
goto error;
/* Connect to data source */
rc = SQLConnect (hdbc, (SQLCHAR *) dsn, SQL_NTS, (SQLCHAR *) usr, SQL_NTS, (SQLCHAR *) pwd, SQL_NTS);
if (!SQL_SUCCEEDED (rc))
goto error;
/* Allocate statement handle */
rc = SQLAllocHandle (SQL_HANDLE_STMT, hdbc, &hstmt);
if (!SQL_SUCCEEDED (rc))
goto error;
/* Successful connection */
return 0;
error:
/* Failed connection */
ODBC_Errors ("ODBC_Connect");
ODBC_Disconnect ();
return -1;
}
int
ODBC_PrintResult ()
{
char fetchBuffer[1000];
short numCols = 0;
short colNum;
SDWORD colIndicator;
UDWORD totalRows;
UDWORD totalSets;
SQLHANDLE hdesc = SQL_NULL_HANDLE;
SQLRETURN rc;
totalSets = 0;
do
{
/*
* Get the number of result columns for this cursor.
* If it is 0, then the statement was probably not a select
*/
rc = SQLNumResultCols (hstmt, &numCols);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLNumResultCols");
goto endCursor;
}
if (numCols == 0)
{
printf ("Statement executed.\n");
goto endCursor;
}
if (numCols > MAXCOLS)
numCols = MAXCOLS;
/*
* Print all the fields
*/
totalRows = 0;
while (1)
{
/*
* Fetch next record
*/
rc = SQLFetch (hstmt);
if (rc == SQL_NO_DATA_FOUND)
break;
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("Fetch");
break;
}
for (colNum = 1; colNum <= numCols; colNum++)
{
char buf[1000];
SQLINTEGER len;
int flag, dvtype;
/*
* Fetch this column as character
*/
rc = SQLGetData (hstmt, colNum, SQL_C_CHAR, fetchBuffer, sizeof (fetchBuffer), &colIndicator);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetData");
goto endCursor;
}
/*
* Get descriptor handle for this statement
*/
rc = SQLGetStmtAttr (hstmt, SQL_ATTR_IMP_ROW_DESC, &hdesc, SQL_IS_POINTER, NULL);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetStmtAttr");
goto endCursor;
}
/*
* Get data type of column
*/
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_DV_TYPE, &dvtype, SQL_IS_INTEGER, NULL);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetDescField");
goto endCursor;
}
/*
* Get flags
*/
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_BOX_FLAGS, &flag, SQL_IS_INTEGER, NULL);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetDescField");
goto endCursor;
}
/*
* Show NULL fields as ****
*/
if (colIndicator == SQL_NULL_DATA)
{
printf ("NULL");
}
else
{
if (flag & VIRTUOSO_BF_IRI)
printf ("<%s>", fetchBuffer); /* IRI */
else if (dvtype == VIRTUOSO_DV_STRING || dvtype == VIRTUOSO_DV_RDF)
printf ("\"%s\"", fetchBuffer); /* literal string */
else
printf ("%s", fetchBuffer); /* value */
if (dvtype == VIRTUOSO_DV_RDF)
{
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_LITERAL_LANG, buf, sizeof (buf), &len);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetDescField");
goto endCursor;
}
if (len)
printf ("@%.*s", (int) len, buf);
rc = SQLGetDescField (hdesc, colNum, SQL_DESC_COL_LITERAL_TYPE, buf, sizeof (buf), &len);
if (!SQL_SUCCEEDED (rc))
{
ODBC_Errors ("SQLGetDescField");
goto endCursor;
}
if (len)
printf ("^^<%.*s>", (int) len, buf);
}
if (colNum < numCols)
putchar (' ');
}
}
printf (" .\n");
totalRows++;
}
printf ("\n\nStatement returned %lu rows.\n", totalRows);
totalSets++;
}
while (SQLMoreResults (hstmt) == SQL_SUCCESS);
endCursor:
SQLCloseCursor (hstmt);
return 0;
}
int
ODBC_Execute (char *qr)
{
int rc;
SQLCHAR *Statement = (SQLCHAR *) qr;
if ((rc = SQLExecDirect (hstmt, Statement, SQL_NTS)) != SQL_SUCCESS)
{
ODBC_Errors ("ODBC_Execute");
if (rc != SQL_SUCCESS_WITH_INFO)
return -1;
}
return 0;
}
char dsn[] = "Local Virtuoso";
char uid[] = "dba";
char pwd[] = "dba";
char txt[] = "sparql SELECT * WHERE {?s ?p ?o} LIMIT 50";
int
main (int argc, char *argv[])
{
if (ODBC_Connect (dsn, uid, pwd))
exit (1);
if (ODBC_Execute (txt) == 0)
ODBC_PrintResult ();
ODBC_Disconnect ();
exit (0);
}
</pre>
</div>
<div class="tip">
<div class="tiptitle">See Also:</div>
<ul>
<li>
<a href="rdfandsparql.html">RDF Data Access and Data Management</a>
</li>
</ul>
</div>
<br />
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="isql.html" title="Interactive SQL Utility">Previous</a>
<br />Interactive SQL Utility</td>
<td align="center" width="34%">
<a href="accessinterfaces.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="VirtuosoDriverJDBC.html" title="Virtuoso Driver for JDBC">Next</a>
<br />Virtuoso Driver for JDBC</td>
</tr>
</table>
</div>
<div id="footer">
<div>Copyright© 1999 - 2009 OpenLink Software All rights reserved.</div>
<div id="validation">
<a href="http://validator.w3.org/check/referer">
<img src="http://www.w3.org/Icons/valid-xhtml10" alt="Valid XHTML 1.0!" height="31" width="88" />
</a>
<a href="http://jigsaw.w3.org/css-validator/">
<img src="http://jigsaw.w3.org/css-validator/images/vcss" alt="Valid CSS!" height="31" width="88" />
</a>
</div>
</div>
</body>
</html>
distrib
>
Fedora
>
14
>
x86_64
>
media
>
updates
>
by-pkgid
>
71d40963b505df4524269198e237b3e3
>
files
>
819
