<!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="4. Sample ODBC & JDBC Applications" />
<meta name="dc.subject" content="4. Sample ODBC & JDBC Applications" />
<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="sampleapps.html" title="Chapter Contents" />
<link rel="prev" href="binsrcloc.html" title="Binary & Source File Locations" />
<link rel="next" href="jdbcdemos.html" title="Sample JDBC Applications & Applets" />
<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>4. Sample ODBC & JDBC Applications</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="sampleodbcapps" />
<img src="../images/misc/logo.jpg" alt="" />
<h1>4. Sample ODBC & JDBC Applications</h1>
</div>
<div id="navbartop">
<div>
<a class="link" href="sampleapps.html">Chapter Contents</a> | <a class="link" href="binsrcloc.html" title="Binary & Source File Locations">Prev</a> | <a class="link" href="jdbcdemos.html" title="Sample JDBC Applications & Applets">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="sampleapps.html">Sample ODBC & JDBC Applications</a>
</div>
<br />
<div>
<a href="binsrcloc.html">Binary & Source File Locations</a>
</div>
<div class="selected">
<a href="sampleodbcapps.html">Sample ODBC Applications</a>
<div>
<a href="#macosxsamples" title="Mac OS X">Mac OS X</a>
<a href="#winodbcsamples" title="Windows 95/98/NT/2000">Windows 95/98/NT/2000</a>
<a href="#unixodbcsamp" title="Linux & UNIX">Linux & UNIX</a>
<a href="#MSDTCsample" title=" MS DTC ODBC Sample Application "> MS DTC ODBC Sample Application </a>
<a href="#MSDTCsample2" title=" MS DTC OLE DB Sample Application "> MS DTC OLE DB Sample Application </a>
</div>
</div>
<div>
<a href="jdbcdemos.html">Sample JDBC Applications & Applets</a>
</div>
<br />
</div>
<div id="text">
<a name="sampleodbcapps" />
<h2>4.2. Sample ODBC Applications</h2>
<a name="macosxsamples" />
<h3>4.2.1. Mac OS X</h3>
<a name="odbctestmac" />
<h4>4.2.1.1. ODBCTEST:</h4>
<p>This is a simple 'C' based and ODBC compliant Interactive SQL processor. </p>
<ol>
<li>
<p>Open a Terminal session, and start ODBCTEST by executing the following command:</p>
<div>
<pre class="programlisting">/Library/iodbc/bin/odbctest</pre>
</div>
</li>
<li>
<p>At the SQL command prompt enter "?" for a list of ODBC
DSNs on your machine or enter a valid ODBC Connect String.
If you have a DSN named "Marketing" you would enter:</p>
<div>
<pre class="programlisting">DSN=Marketing;UID=username;PWD=password</pre>
</div>
<p>Note: If there is no password, you must include a semicolon at the end:</p>
<div>
<pre class="programlisting">DSN=Marketing;UID=sa;PWD=;</pre>
</div>
</li>
<li>
<p>Any valid SQL or ODBC command may be executed through this interface. The following example
shows a connection to Microsoft SQL Server 2000, making a simple query against the sample Northwind database:</p>
<div>
<pre class="programlisting">
[localhost:~] openlink% /Library/iodbc/bin/odbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Enter ODBC connect string (? shows list, or DSN=...): DSN=user_tthib_sql2k
SQL>select au_lname, au_fname, state from authors where au_id < '333-33-3333'
au_lname |au_fname |state
----------------------------------------+--------------------+-----
White |Johnson |CA
Green |Marjorie |CA
Carson |Cheryl |CA
O'Leary |Michael |CA
Straight |Dean |CA
5 row(s) fetched.
SQL>quit
Again (y/n) ? n
Have a nice day.
[localhost:~] openlink%
</pre>
</div>
</li>
</ol>
<br />
<br />
<a name="winodbcsamples" />
<h3>4.2.2. Windows 95/98/NT/2000</h3>
<a name="cppdemo32" />
<h4>4.2.2.1. C++ Demo</h4>
<ol>
<li>
<p>Go to the Virtuoso "Start Menu" item, then click on
the "C++ Demo 32 Bit" menu item.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="C++ Demo" src="../images/sampl001.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.1.1. C++ Demo</td>
</tr>
</table>
</li>
<li>
<p>Follow the Environment->Open Connection menu path. Selecting the
"Open Connection" menu item results in the ODBC Driver Manager presenting you
with a list of ODBC DSNs on your machine as depicted by the screen capture below:</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="C++ Demo" src="../images/sampl004.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.1.1. C++ Demo</td>
</tr>
</table>
</li>
<li>
<p>Select the ODBC DSN that you want to be connecting to, in this case
"Local Virtuoso Demo" has been chosen since this will connect you to a sample
Virtuoso database that has been populated with data. </p>
</li>
<li>
<p>You are then presented with a Login Dialog by the Virtuoso driver
for ODBC, enter a valid user name and password (default being user: demo and password:
demo) into the appropriate fields.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="C++ Demo" src="../images/sampl003.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.1.1. C++ Demo</td>
</tr>
</table>
</li>
<li>
<p>At this point you will be connected to the Virtuoso demonstration
database, you can now use the SQL-->Execute SQL menu path to open up the Interactive
SQL input dialog. Enter a valid SQL statement (see example in screen shot) and then click
on the "OK" button.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="C++ Demo" src="../images/sampl006.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.1.1. C++ Demo</td>
</tr>
</table>
</li>
<li>
<p>You will be presented with the results of your query.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="C++ Demo" src="../images/sampl007.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.1.1. C++ Demo</td>
</tr>
</table>
</li>
<li>
<p>You exit this demo by following the Environment-->Close Connection menu path.</p>
</li>
</ol>
<br />
<a name="odbcbench32" />
<h4>4.2.2.2. ODBC Bench Test 32</h4>
<ol>
<li>
<p>Go to the Virtuoso "Start Menu" item, then click on the "ODBC Bench
Test 32 Bit" menu item. You will be presented with the "Bench Test"
interface.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl008.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
</li>
<li>
<p>Follow the File-Connect menu path which initializes the ODBC Driver Manager which in
turn presents you with a list of ODBC DSN's installed on your machine. Select the DSN that
you want to benchmark, remember that by benchmarking a DSN you are benchmarking the ODBC
Driver that serves the DSN in question and the backend database engine that serves the
ODBC Driver. Choose the "Local Virtuoso Demo" DSN if you want to benchmark
Virtuoso.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl009.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
</li>
<li>
<p>You will then be presented with a Login Dialog by the Virtuoso driver for ODBC, enter a
valid user name and password (default being user: demo and password: demo for the Demo
database) into the appropriate fields.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl010.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
</li>
<li>
<p>Now follow the Bench-->Load Tables menu path and you will be presented with a dialog
that enables you to configure key elements of your benchmark. Click the
"Execute" button to commence the process of setting up your database for the
benchmark tests.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl011.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
</li>
<li>
<p>As the process of loading data occurs, all the way up to completion, the benchmark
program will provide status information into the benchmark output pane as shown below:</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl012.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
</li>
<li>
<p>Now that all the benchmark data has been loaded into your database, follow the
Bench-->Run Benchmark menu path and then configure your actual benchmark session
parameters:</p>
<p>These benchmark parameters fall into 3 categories, Timing Options, SQL Options, and
Execution Options.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl013.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
<p>
<strong>Timing Options:</strong>
These setting allow you to configure the duration related aspects of this benchmark
program</p>
<p>
Minutes - this is the duration of each benchmark run</p>
<p>
Runs - this controls how many iterations of the benchmarks you actually run (the default
is one benchmark iteration with a duration of 5 minutes)</p>
<p>
<strong>SQL Options:</strong>
These setting allow you to configure how your benchmark's SQL instructions are actually
handled.</p>
<p>ExecDirect with SQL Text - this means that no form of repetitive SQL execution
optimization is being applied (SQL statements are prepared and executed repetitively)</p>
<p>Prepare/Execute Bound Params - this means that the Parameter Binding SQL execution
optimization is being applied (SQL is prepared once but executed many times without the
overhead of re-preparing statements prior to execution)</p>
<p>Use Stored Procedures - this means that the Stored Procedure SQL optimization is being
applied (benchmark instructions are stored within database being benchmarked)</p>
<p>
<strong>Execution Options:</strong>
These setting allow you to configure the tone of your benchmark, for instance it could
have Transaction scoping and a mix of record retrieval queries, or it could simply be
input and update intensive with a minimal amount of record retrieval queries (the case
when the 100 row query checkbox is unchecked a typical OLTP scenario) </p>
<p>Asynchronous - execute the benchmark instructions asynchronously</p>
<p>Use Transactions - make the benchmark use transaction control (instructions are scoped to
transaction blocks)</p>
<p>Do 100 row Query - perform a simulation of a 100 record retrieval as part of the benchmark
activity.</p>
</li>
</ol>
<ol>
<li>
<p>Click on the "Run All" button if you would like all the different benchmark
type combinations to be performed.</p>
</li>
<li>
<p>When benchmark run complete benchmark data is written to the benchmark program's output
pane.</p>
<table class="figure" border="0" cellpadding="0" cellspacing="0">
<tr>
<td>
<img alt="ODBC Bench" src="../images/sampl014.gif" />
</td>
</tr>
<tr>
<td>Figure: 4.2.2.2.1. ODBC Bench</td>
</tr>
</table>
<p>The key pieces of benchmark data that you need to look out for are:</p>
<p>
<strong>Total Transactions: </strong> total number of transactions completed during the
benchmark run</p>
<p>
<strong>Transactions Per Second</strong>
number of transaction completed per second for
the benchmark run</p>
<p>Information from this benchmark is automatically written to an Excel format CSV
(the file odbcbnch.csv) which makes it easy for you to graph
and pivot data collated from several benchmark runs. A later version of this demo will
actually write the benchmark data into an ODBC DSN that you provide thereby offering even
more flexibility and accessibility to benchmark data.</p>
</li>
</ol>
<br />
<br />
<a name="unixodbcsamp" />
<h3>4.2.3. Linux & UNIX</h3>
<a name="odbctestnix" />
<h4>4.2.3.1. ODBCTEST:</h4>
<p>This is a simple 'C' based and ODBC compliant Interactive SQL processor. </p>
<ol>
<li>
<p>Run the script virtuoso-enterprise.sh to set up your environment:</p>
<div>
<pre class="programlisting">. virtuoso-enterprise.sh</pre>
</div>
</li>
<li>
<p>Start ODBCTEST by executing the following command:</p>
<div>
<pre class="programlisting">odbctest</pre>
</div>
</li>
<li>
<p>At the SQL command prompt enter "?" for a list of ODBC
DSNs on your machine or enter a valid ODBC Connect String.
If you have a DSN named "Marketing" you would enter:</p>
<div>
<pre class="programlisting">DSN=Marketing;UID=username;PWD=password</pre>
</div>
<p>Note: If there is no password, you must include a semicolon at the end:</p>
<div>
<pre class="programlisting">DSN=Marketing;UID=sa;PWD=;</pre>
</div>
</li>
<li>
<p>Any valid SQL or ODBC command may be executed through this interface. The following example
shows a connection to Microsoft SQL Server 2000, making a simple query against the sample Northwind database:</p>
<div>
<pre class="programlisting">
[localhost:~] openlink% odbctest
iODBC Demonstration program
This program shows an interactive SQL processor
Enter ODBC connect string (? shows list, or DSN=...): DSN=test_sql2k
SQL>select au_lname, au_fname, state from authors where au_id < '333-33-3333'
au_lname |au_fname |state
----------------------------------------+--------------------+-----
White |Johnson |CA
Green |Marjorie |CA
Carson |Cheryl |CA
O'Leary |Michael |CA
Straight |Dean |CA
5 row(s) fetched.
SQL>quit
Again (y/n) ? n
Have a nice day.
[localhost:~] openlink%
</pre>
</div>
</li>
</ol>
<br />
<br />
<a name="MSDTCsample" />
<h3>4.2.4. MS DTC ODBC Sample Application </h3>
<p>The MS DTC demo is located in the
<span class="computeroutput"><VIRTUOSO_INSTALLATION_DIRECTORY>\samples\odbc\MSDTCdemo1</span>
folder. This demo shows usage of distributed transactions driven by MS DTC through the ODBC API.</p>
<a name="MSDTCdemo1Setup" />
<h4>4.2.4.1. Setup </h4>
<p> The sample works with two instances of Virtuoso server. The running MS DTC service is needed.
The servers must be started with MS DTC support (see Virtuoso documentation). </p>
<p>
First of all, edit the virt.odbc file. By default this file contains two connection strings of local Virtuoso servers running on port 1111 and port 1112,
Each line begins with connection string to appropriate Virtuoso server.
Initially this file contains the following lines: </p>
<div>
<pre class="programlisting">
1111 dba dba 00.sql
1112 dba dba 01.sql
</pre>
</div>
<p> where 1111, 1112 are ports of two Virtuoso servers. "dba dba" - user and password. </p>
<br />
<a name="MSDTCdemo1Init" />
<h4>4.2.4.2. Initialization </h4>
<p> Start <span class="computeroutput"> mtstest.exe +load </span> in the <span class="computeroutput"><VIRTUOSO_INSTALLATION_DIRECTORY>\samples\odbc\MSDTCdemo1</span>
folder. This must check whether all needed servers are running, create and initialize tables, procedures, etc.</p>
<br />
<a name="MSDTCdemo1Start" />
<h4>4.2.4.3. Test </h4>
<p> Run the command in the demo's working directory: </p>
<div>
<pre class="programlisting">
mtstest.exe +exec N
mtstest.exe +exec 0
</pre>
</div>
<p> where N - is a number of test iterations. The second command checks logical consistency of the tables.</p>
<br />
<a name="MSDTCdemo1Description" />
<h4>4.2.4.4. Description </h4>
<p> The demo source is <span class="computeroutput">start.c</span> file in the <span class="computeroutput"><VIRTUOSO_INSTALLATION_DIRECTORY>\samples\odbc\MSDTCdemo1</span>
directory. Several highlights of the most significant
parts of code are presented below: </p>
<div>
<pre class="programlisting">
ITransactionDispenser *disp;
ITransaction *transaction;
...
HRESULT hr =
DtcGetTransactionManagerC (0, 0, &IID_ITransactionDispenser, 0, 0, 0,
&, disp);
</pre>
</div>
<p>
The code above creates Dispenser object which represents the local instance of MS DTC. If MS DTC is not available
<span class="computeroutput">DtcGetTransactionManagerC</span>
fails. The Dispenser is needed to create distributed transaction objects later.
</p>
<p>
Begin new distributed transaction: </p>
<div>
<pre class="programlisting">
disp->lpVtbl->BeginTransaction (disp, 0, ISOLATIONLEVEL_ISOLATED,
0, 0, &transaction);
</pre>
</div>
<p> Enlist connection in the transaction: </p>
<div>
<pre class="programlisting">
SQLSetConnectOption (hdbc, SQL_COPT_SS_ENLIST_IN_DTC,
(DWORD) transaction);
</pre>
</div>
<p> And, finally, commit the transaction: </p>
<div>
<pre class="programlisting">
transaction->lpVtbl->Commit (tran, 0, 0, 0);
</pre>
</div>
<br />
<br />
<a name="MSDTCsample2" />
<h3>4.2.5. MS DTC OLE DB Sample Application </h3>
<p>The MS DTC OLE DB demo is located in the
<span class="computeroutput"><VIRTUOSO_INSTALLATION_DIRECTORY>\samples\odbc\MSDTCdemo2</span>
folder. This demo shows usage of distributed transactions driven by MS DTC through OLE DB.</p>
<a name="MSDTCdemo2Setup" />
<h4>4.2.5.1. Setup </h4>
<p> The sample works with two instances of Virtuoso server. Running MS DTC service is needed.
The servers must be started with MS DTC support (see Virtuoso documentation). </p>
<p>
The test needs two Virtuoso server instances running on ports 1111 and 1112
</p>
<br />
<a name="MSDTCdemoStart" />
<h4>4.2.5.2. Run </h4>
<p> Run the command in the demo's working directory: </p>
<div>
<pre class="programlisting">
voledbtest.exe
</pre>
</div>
<br />
<a name="MSDTCdemoDescription" />
<h4>4.2.5.3. Description </h4>
<p> The demo source is <span class="computeroutput">voledbtest.cs</span> file in the <span class="computeroutput"><VIRTUOSO_INSTALLATION_DIRECTORY>\samples\odbc\MSDTCdemo</span>
directory. Several highlights of the most significant
parts of code are presented below: </p>
<div>
<pre class="programlisting">
[TransactionAttribute(TransactionOption.Required)]
</pre>
</div>
<p>
It is significant to set this attribute of class to enable automatic transaction initialization.
</p>
<p> Create connections to appropriate servers: </p>
<div>
<pre class="programlisting">
string strConn = "Provider=VIRTOLEDB;Data Source=" + dsn1
+ ";User Id=dba;Password=dba;Initial Catalog=Demo;Prompt=NoPrompt;";
string strConn2 = "Provider=VIRTOLEDB;Data Source=" + dsn2 +
";User Id=dba;Password=dba;Initial Catalog=Demo;Prompt=NoPrompt;";
obj_conn = new OleDbConnection(strConn);
obj_conn2 = new OleDbConnection(strConn2);
</pre>
</div>
<p> And, finally, execute the SQL code in the context of distributed transaction: </p>
<div>
<pre class="programlisting">
OleDbCommand sqlc = new OleDbCommand ("ODBC_BENCHMARK(" + idx + ",1,1,12.00,\'noone\')");
sqlc.Connection = obj_conn;
sqlc.ExecuteNonQuery();
sqlc = new OleDbCommand ("ODBC_BENCHMARK(" + idx + ",1,1,-12.00,\'noone\')");
sqlc.Connection = obj_conn2;
sqlc.ExecuteNonQuery();
</pre>
</div>
<br />
<br />
<table border="0" width="90%" id="navbarbottom">
<tr>
<td align="left" width="33%">
<a href="binsrcloc.html" title="Binary & Source File Locations">Previous</a>
<br />Binary & Source File Locations</td>
<td align="center" width="34%">
<a href="sampleapps.html">Chapter Contents</a>
</td>
<td align="right" width="33%">
<a href="jdbcdemos.html" title="Sample JDBC Applications & Applets">Next</a>
<br />Sample JDBC Applications & Applets</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
>
871
