Easysoft XML-ODBC Server User Guide - Sample Client Applications

Example Client Applications included with the Easysoft XML-ODBC Server

This section includes sample client application classes and objects for use in connecting to the Easysoft XML-ODBC Server.

All the files listed are distributed with Easysoft XML-ODBC Server and are located in the corresponding directories within the Clients subdirectory of the chosen installation path.

Chapter Guide

C

File List

A generic make file for building the sample C client.

Various other versions of the make file for different platforms (if a make file is not supplied for your platform, then use the generic version and edit it to reflect your required compiler and compiler options).

The xmlodbc_client program C source code.

A header file for xmlodbc_client.c containing various macros (this file may need to be edited if xmlodbc_client.c does not compile successfully on your platform).

The example xmlodbc_client program prebuilt for this platform distribution.

Building the example Client

You will need an ANSI C compiler and make (although it is possible to compile the client without make).

The Easysoft XML-ODBC Server distribution includes a generic make file (Makefile), which should suffice for most platforms, and also additional platform-specific make files named Makefile.<platform>.

If you see a make file for your platform use:

make -f Makefile.xxx

where xxx is the platform.

Use the generic make file if there is no make file for your platform:

make -f Makefile

If the code compiles, but fails to link then you may need to specify additional libraries on the link line, of which the most likely to be missing are the socket libraries (see the Makefile.sunos file for an example of a platform requiring additional link libraries).

If the code fails to compile then ensure that:

¯ OR ¯

Using the example Client

Prerequisites:

The xmlodbc_client program supports the following command line arguments:

-h hostname

The name of the server machine where the Easysoft XML-ODBC Server is running. This may be an IP address, host name or a fully qualified domain name, but in each case it is passed to the gethostbyname() function, so your client machine needs to be able to resolve this argument value into an IP address if a host name is specified.

-p port

This is the port number on which the Easysoft XML-ODBC Server listens for database requests. This setting is optional and the default port of 8895 is used if it is not specified. Two ports can be configured for the Easysoft XML-ODBC Server, one for database requests and one for configuration requests (usually ports 8895 and 8896). You should send database queries to the database port (8895) and configuration requests/operations to the control port (8896).

-c connection_string

The connection string to use on the Easysoft XML-ODBC Server to connect to your database. This is an ODBC connection string i.e. what is passed to the ODBC API SQLDriverconnect() function.

In its simplest form this requires a DSN name only.

e.g.

"DSN=mydsn;"

For databases with built in authentication you may need to add UID and PWD attributes for the database user name and password.

e.g.

"DSN=mydsn;UID=database_username;PWD=database_password;"

Data sources may require other attributes too. You should specify all these attributes in the connection string separating each one with a ';'.

e.g.

"ATTRIBUTE1=value;ATTRIBUTE2=value;"


NB

If you are still getting ODBC driver connection errors at this point, then consult your ODBC driver documentation.

-s query_string

This is the database query you want to execute on the server machine. This is usually a request for elements of a table.

e.g.

"select * from mytable"


NB

If you need to issue SQL containing XML reserved characters (e.g. "<"), then the C client will wrap your SQL in a CDATA section before sending it to the server. If you would rather do this yourself, you need to specify your SQL as follows:

-s '<![CDATA[your SQL statement]]>'

e.g.

-s '<![CDATA[select * from table where column < 2]]>'

The result of this query (the result set) will be returned in XML.

If, for example:

then the command line would be either:

./xmlodbc_client

-h myserver -p 8895

-c "DSN=mydsn;UID=dbuser;PWD=dbpass;"

-s "select * from mytable"

or, alternatively,

./xmlodbc_client

-h myserver -p 8895

-f file.xml

where file.xml contains:

<?xml version="1.0" encoding="UTF-8" ?>

<Request>

<ConnectionString>

DSN=mydsn;UID=dbuser;PWD=dbpass;

</ConnectionString>

<Sql>select * from mytable</Sql>

</Request>

The output should be the contents of the "mytable" table in XML. Any error that occurs is also output in XML, rather than as a result set, and both are described in the the "Documentation" subdirectory of the Easysoft XML-ODBC Server distribution.


NB

If you need to issue SQL containing XML escape characters (e.g. "<"), then the example Perl client will escape these for you. If you would rather control this yourself you can enter the SQL as XML CDATA:

e.g. -s '<![CDATA[select * from table where column < 2]]>'

Client Return Status and output

The return status of the xmlodbc_client program is "0" for success and any other value for an error (see the code for all the possible return codes).

The XML output (including XML output generated for an error occurring on the server) is always directed to stdout and errors occurring at the client end are written to stderr (see the code for examples).

As a result you can redirect the XML output to a file in most shells with something like:

./xmlodbc_client args 1> file.xml

Parsing the XML output

There are many XML parsers available on the net which have C APIs you could use with this example C client, such as Expat(http://expat.sourceforge.net), which requires very straight forward code to parse the XML output of this client, as illustrated in the accompanying examples.

Perl

File List

This is a small Perl script which allows a query to be executed on the specified Easysoft XML-ODBC Server with the results returned exactly as the server returns them (i.e. in XML).

You can also issue configuration requests or ask for statistics.

This script uses ESXMLODBC::XMLREQUEST and its methods, so the directory ESXMLODBC needs to be on your Perl INC path.

Usage:

xmlodbc_client.pl

[-V]

-h server [-p port]

[-t]

[-r]

[-f file-request] |

[-c connection_string

[-s sql_query | SQL_statements_in_a_transaction]]

where:

-h is the name of machine where the server is running

-p is the server port to connect to (usually 8895)

-c is the ODBC connection string

-s is the SQL to run

-t truncates trailing characters in SQL_CHAR columns (cannot be used with the -f option)

-r displays the XML request (cannot be used with the -f option)

-V prints the ESXMLODBC::XMLREQUEST version being used

e.g.

xmlodbc_client.pl

-h myserver.company.co.uk

-p 8895

-c "DSN=test;UID=dbuser;PWD=dbpassword;"

-s "select * from mytable"

where the Easysoft XML-ODBC Server on myserver.company.co.uk listens on port 8895 to connect to the test data source with a database user name and password of dbuser and dbpassword, executes the select statement and returns the results in XML.

e.g.

xmlodbc_client.pl

-h myserver.company.co.uk

-p 8895

-c "DSN=test;UID=dbuser;PWD=dbpassword;"

"insert into users values (1,'user@domain')"

"insert into mailing values ('user@domain', 'Yes')"

where the Easysoft XML-ODBC Server on myserver.company.co.uk listens on port 8895 to connect to the test data source with a database user name and password of dbuser and dbpassword, and executes the two insert statements. If both inserts work they are committed, but if either insert fails then the transaction is rolled back.

e.g.

xmlodbc_client.pl

-h myserver.company.co.uk

-p 8895

-f file.xml

This sends the SQL request in file.xml to the Easysoft XML-ODBC Server on myserver.company.co.uk listening on port 8895 and returns the result in XML. You can also use this method to send configuration requests or instructions (see "Example Configuration XML") and statistics requests (see "Example Statistics XML") to the Easysoft XML-ODBC Server control port.

A directory containing the Perl modules for the Easysoft XML-ODBC Server.

A Perl module containing the methods which query a particular Easysoft XML-ODBC Server (for examples of how to use this module see xmlodbc_client.pl and xmlodbc_example.pl):

XMLREQUEST methods and members

new

You must create a new $h before using any of the other methods.

$h = ESXMLODBC::XMLREQUEST->new();

Connect

Creates a connection between the client and server and requires two attributes to achieve this:

Server

The name or IP address of the machine where the Easysoft XML-ODBC Server is listening.

Port

The port on which the Easysoft XML-ODBC Server is listening. Port is optional and defaults to 8895.

You may independently set Port and Server before using Connect or pass a hash reference of connection attributes to Connect.

e.g.

$h->{Server} = "demo.easysoft.com";

$h->{Port} = "8895";

$h->Connect();

or

$h->Connect({Server=>"demo.easysoft.com", Port=>"8895"});

Connect returns true on successful connection.

Disconnect

Closes the connection between the client and the Easysoft XML-ODBC Server.

ExecuteFile

$h->ExecuteFile($filespec);

Allows you to pass an XML request residing on the specified file directly to the Easysoft XML-ODBC Server you are connected to.

For more details of the format of these XML requests, see "sql_request.dtd".

e.g.

a file called "/tmp/request.xml" containing the following data:

<Request>

<ConnectionString>DSN=demo;UID=demo;PWD=easysoft;

</ConnectionString>

<Sql>select * from oobdist_contact</Sql>

</Request>

can then be executed as follows:

$h->Execute(/tmp/request.xml);

Execute

$h->Execute();

$h->Execute($sql);

$h->Execute($sql, \%attr);

Allows you to send an SQL request to the Easysoft XML-ODBC Server and requires two attributes:

ConnectionString - an ODBC connection string defining the ODBC driver and database to which you want to connect.

Sql - passed either as the first argument to Execute or set as a reference to array of SQL statements you would like executed as part of a transaction (see Transactions).

You can define ConnectionString and Sql before calling Execute by setting them in the hash reference.

e.g.

$h->{ConnectionString} = "DSN=demo.easysoft.com";

$h->{Sql} =

["insert into table values (1)",

"update table set columna = 2 where columnb = 1"];

Do not set Sql in this way unless you are using transactions.

Instead pass the SQL as the first argument to Execute:

$h->Execute("select * from table");

There are various other execution attributes which may be set in the Execute call by providing a hash reference:

e.g.

$h->Execute("select * from table", {Parse=>"1"});

See Execution attributes.

First Next and Last

These methods may be used after using Execute or ExecuteFile.

All these methods return a reference to a hash containing the column information (with a key consisting of the column name) or undef.

$h->First - return the first row in the result set

$h->Next - return the next row in the result set

$h->Last - return the last row in the result set

Note that these methods may only be used if the Execution attribute <Parse> is set to 1 before the Execute method is used.

Note that you also need Expat and XML::Parser installed (see the XML_Parser.txt file included with the distribution in the Perl section of the Client subdirectory).

e.g.

$h->Execute("select * from table", {Parse=>"1"});

$hr = $xoh->First() || die "cannot get first row";

print join ("," keys %$hr), "\n"; # print column names

while($hr) {

my $col;

foreach $col (keys %$hr) {

print $hr->{$col}, " ";

}

print "\n";

$hr = $xoh->Next();

}

Connection attributes

These attributes may be set directly in the hash off $h or by passing a hash reference to the Connect method.

<ConnectionString> (string, read/write)

The ODBC connection string to be passed to the ODBC API SQLDriverConnectW function. It consists of a number of attribute=value pairs separated by semicolons.

At its simplest a <ConnectionString> is:

DSN=system_dsn_name;

where the DSN attribute names the System data source name known to the ODBC driver manager.

However, most ODBC drivers define a number of additional attributes

e.g.

UID

The user name for accessing the database.

PWD

The user name password for accessing the database.

The ODBC driver manager also accepts some specific attibutes like "Driver" and "FileDSN", which can be used to specify the ODBC driver or a data source defined in a file.

Consult a good ODBC reference and your ODBC driver's documentation for a list of all the possibilities.

Execution attributes

These attributes may be set directly in the hash off $h or by passing a hash reference to the Execute method.

Parse (boolean, read/write)

If set to true, causes the resulting XML to be parsed so the First, Next and Last methods may be used. It also sets NumRows, NumColumns and Rows (see Execution results).

Truncate

If set to true, causes the Truncate Format element to be sent to the Easysoft XML-ODBC Server, requesting the removal of trailing spaces from the end of SQL_CHAR columns.

Execution results

There are a number of execution result attributes which may be accessed off $h.

XML

The XML result returned from the Easysoft XML-ODBC Server.

NumRows

The number of rows in the result set. This is undefined unless Parse is set to 1 before execution.

Request

The XML request sent to the Easysoft XML-ODBC Server. This attribute is only defined if the Execute method is used.

NumColumns

The number of columns in a row in the result set. This attribute is undefined unless Parse is set to true before execution.

Rows

A reference to an array of hash references.

There is one hash reference for each row in the result set. The keys to the hash reference are the column names.

Rows->[0]->{Column1Name}

Rows->[1]->{Column1Name}

Rows->[2]->{Column1Name}

.

.

Rows[n]->{ColumnnName}

This attribute is only defined if the Execute method is used.

Error Handling

All methods return undef for error and set ErrorState and ErrorText when an error occurs.

ErrorState

A five character string identifying an error, similar to ODBC states, where the string "00000" represents success and any other error state string is an error.

ErrorText

A description of an error.

If Parse is set to false then <ErrorState> will always be "00000" if the request was sent to the server and a response was received, even though the XML response suggests the requested action could not be completed successfully.

In this case you need to actually examine the returned XML to identify the problem.

e.g.

With Parse set to false and executing this request:

<Request>

<ConnectionString>DSN=test;</ConnectionString>

<Sql><![CDATA[select * from tabledoesnotexist]]></Sql>

</Request>

The returned XML is probably something like this:

<Result State="S0002">

<Diagnostic Number="1">

<State>S0002</State>

<Native>208</Native>

<Text>[Microsoft][ODBC SQL Server Driver][SQL Server]

Invalid object name 'tabledoesnotexist'.

</Text>

</Diagnostic>

indicating the table did not exist, but the Execute method will return successfully and ErrorState will be "00000".

To get full ErrorState and ErrorText strings from the returned XML you need to set Parse to true. In this case ErrorState is set to either a client end error state (e.g. socket could not be created) or to the "State" attribute in the <Result> element of the result (this will always be "00000" for success or the State in the first ODBC diagnostic).

In this way you can tell if your SQL execution was successful by testing if ErrorState is equal to "00000".

A small Perl script serving as an example of how to use ESXMLODBC::XMLREQUEST.

This is a working example, but only if the constants defined for server, port, connection string and SQL are modified.

Using the example Client

You will need to obtain and install Perl (http://www.perl.com).

If you want to parse the output XML you will also need

and

If the Perl interpreter is not installed in /usr/bin you will need to either:

¯ OR ¯

On most types of Unix you can find out where your Perl interpreter is installed is by typing:

$ which perl

If, for example, the install directory is returned as

/usr/local/bin/perl

then the first line of each of the Perl sample clients would require amending to read

#!/usr/local/bin/perl

rather than the default

#!/usr/bin/perl

OR

add the prefix "perl" when running a sample client:

perl sample_client.pl arguments

Install the Easysoft XML-ODBC Server on a machine which has an ODBC driver for your database and create a System data source for your database (User data sources are not seen by the Easysoft XML-ODBC Server, as it runs as a service).

All the example Perl clients are similar in that they need to be passed a server host (where the Easysoft XML-ODBC Server is running), an ODBC connection string (describing the data source on the server you want to connect to), some SQL to execute and a port on which the Easysoft XML-ODBC Server is listening.

For example, if the Easysoft XML-ODBC Server is installed on a Windows machine called "mywinserver.company.com" which has an ODBC driver for Microsoft SQL Server and a SQL Server database called "mydatabase":

1.  Create a System DSN to describe the database:

2.  Once the data source has been created, test it (there is a test button in the Microsoft SQL Server ODBC driver dialog boxes and you can also test it, once created, with an ODBC application).

3.  On your client machine (where you want to issue the SQL and retrieve the results in XML) you need to install the sample Perl clients and (optionally) Expat and XML::Parser.

4.  Issue a SQL query using the xmlodbc_client.pl, a database user name and password of "dbuser" and "dbpass" for SQL Server authentication and a table name of "table".

e.g.

./xmlodbc_client.pl mywinserver.company.com 8895

"DSN=mydatasource;UID=dbuser;PWD=dbpass;"

'select * from table'


NB

If you need to issue SQL containing XML escape characters (e.g. "<"), then the example Perl client will escape these for you. If you would rather control this yourself you can enter the SQL as XML CDATA:

e.g. -s '<![CDATA[select * from table where column < 2]]>'


The result set generated by your query will be returned in XML to stdout, but you can use standard shell redirection to direct stdout to a file if you prefer.

The sample clients do not allow you to choose the format of the returned XML, because some of them are dependent on the default Easysoft XML-ODBC Server XML output format (see the "/Documentation" subdirectory for a description of the different output formats).

PHP

File List

Sample PHP code using the XMLODBC class with Expat to parse the XML result set, outputting it as HTML.

A PHP include file containing the XMLODBC class for use with PHP.

The XMLODBC class contains the following methods:

Connects to the Easysoft XML-ODBC Server.

¯ OR ¯

Hostname = "server";

The machine where the Easysoft XML-ODBC Server is located.

Port = 8895;

The port on which the Easysoft XML-ODBC Server is listening.

$x->Connect();

Executes either a single SQL statement or a transaction containing one or more SQL statements.

¯ OR ¯

ConnectionString = "connection string";

SQL = "SQL statement";

¯ OR ¯

SQL[0] = "insert/update/delete statement 1 of transaction";

SQL[1] = "insert/update/delete statement 2 of transaction";

Executes statements 1 and 2 together as one transaction.

Disconnects from the Easysoft XML-ODBC Server.

Defaults to false, but if set to true this modifies the request to the Easysoft XML-ODBC Server to request the truncation of all trailing blanks from SQL_CHAR columns.

The XML request issued by XMLODBC to the Easysoft XML-ODBC Server.

The XML returned from the Easysoft XML-ODBC Server.

If set to true causes the returned XML to be parsed with Expat (see http://expat.sourceforge.net).

This makes available the NumRows, NumColumns, First, Next, Last and Rows methods.

If the Parse method is set to true and in an executed state, then this is the number of rows in the result set.

If the Parse method is set to true and in an executed state, then this is the number of columns in each row in the result set.

If the Parse method is set to true and in an executed state, then this is an array of associated arrays. The first dimension is the rows in the result set.

The second dimension contains associative arrays when the column names are the keys.

e.g.

$p = $x->Row[1];

row 2 - this is an associative array

print_r(array_keys ($p));

prints the keys which are column names

If the Parse method is set to true and in an executed state, then this returns the first row of the result set (see Rows).

If the Parse method is set to true and in an executed state, then this returns the next row of the result set (see Rows).

If the Parse method is set to true and in an executed state, then this returns the last row of the result set (see Rows).

Using the Example Client

You need PHP and optionally Expat and XML support built in to PHP.

1.  Copy XMLODBC.inc and include that file into any PHP in which you intend using the class (the example xmlodbc.phtml file contains some sample code using the class).

2.  Install the Easysoft XML-ODBC Server on a machine which has an ODBC driver for your database.

3.  Create a System data source for your database (User data sources are not seen by the Easysoft XML-ODBC Server, as it runs as a service).

For example, if the Easysoft XML-ODBC Server is installed on a Windows machine called "mywinserver.company.com" which has an ODBC driver for Microsoft SQL Server and a SQL Server database called "mydatabase":

4.  Create a System DSN to describe the database:

5.  Once the data source has been created, test it (there is a test button in the Microsoft SQL Server ODBC driver dialog boxes and you can also test it, once created, with an ODBC application).

6.  On your client machine (where you want to issue the SQL and retrieve the results in XML):

To parse the returned XML you will also need Expat and XML built in to PHP.


NB

Much of this information does not apply if you are running PHP from the command line.

7.  As an example, you may now use xmlodbc.phtml to issue a SQL query with the Easysoft XML-ODBC Server on a machine called "mywinserver.company.com", an ODBC data source called "mydatasource", a database user name and password of "dbuser" and "dbpass" (assuming SQL Server authentication is in use) and a table called "table":

 <?

 $Test = new XMLODBC;

 $Test->Connect("mywinserver.company.com", "8895");

 $Test->Execute("DSN=mydatasource;UID=dbuser;PWD=dbpass;",

  "select * from table");

 print $Test->XML;

 // see xmlodbc.phtml for other methods.

 $Test->Disconnect();

 ?>

 


NB

If you need to issue SQL containing XML escape characters (e.g. "<"), then the example PHP client will escape these for you.

If you would rather control this yourself you can enter the SQL as XML CDATA:

e.g. -s '<![CDATA[select * from table where column < 2]]>'


The result set generated by your query will be returned in XML in $Result.

 


NB

The sample clients do not allow you to choose the format of the returned XML, because some of them are dependent on the default Easysoft XML-ODBC Server XML output format (see "Example SQL XML").


Useful references

http://www.wirelessdevnet.com/channels/wap/features/xmlcast_php.html

http://www.analysisandsolutions.com/code/phpxml.htm

Java

File List

The Java source for the client class

The compiled XmlOdbc.java file

The Java source for the example demo

The compiled XmlOdbcDemo.java file

Building the example Client

Running:

javac XmlOdbc.java

will produce XmlOdbc.class.

Running:

javac XmlOdbcDemo.java

will produce XmlOdbcDemo.class.

XmlOdbc class

XmlOdbc.class is a basic client which can be used to communicate with the Easysoft XML-ODBC Server and allows SQL statements to be sent to an ODBC driver on the same machine as the Easysoft XML-ODBC Server and results to be retrieved in XML.

The protocol used is simple and documented by the Document Type Definitions distributed with the Easysoft XML-ODBC Server (see "SQL").

It has a basic constructor and six methods as follows:

Constructor:

Methods:

public void connect(java.lang.String server)

throws java.net.UnknownHostException,

java.io.IOException

Establishes a socket connection to the Easysoft XML-ODBC Server using a default port of 8895.

Parameters:

server - the Easysoft XML-ODBC Server machine.

public void connect(java.lang.String server,

int port)

throws java.net.UnknownHostException,

java.io.IOException

Establishes a socket connection to the Easysoft XML-ODBC Server.

Parameters:

server - the Easysoft XML-ODBC Server machine.

port - the port on which the Easysoft XML-ODBC Server is listening.

public void sendRequest(java.lang.String sql,

java.lang.String odbcConnectionString)

throws java.lang.Exception

Constructs a request in XML and sends it to the Easysoft XML-ODBC Server.

Establishes a connection to an ODBC data source on the machine on which the Easysoft XML-ODBC Server is running using the connection string supplied and executes the given query.

The result set generated can be retrieved as a string using getStringResponse() or as an input stream using getStreamResponse().

Parameters:

sql - the SQL query to be sent to the Easysoft XML-ODBC Server.

odbcConnectionString - the connection string.

public java.lang.String getStringResponse()

throws java.lang.Exception

Gets an XML response from the Easysoft XML-ODBC Server as a string.

public java.io.DataInputStream getStreamResponse()

throws java.lang.Exception

Gets an XML response from the Easysoft XML-ODBC Server as an input stream that can be passed to a parser.

public void sendXml(java.lang.String xmlrequest)

throws java.lang.Exception

Sends an XML request to the Easysoft XML-ODBC Server given as a string parameter

Parameters:

xmlrequest - the XML request (see "sql_request.dtd").

Using the example Java Client

Prerequisites:

1.  The Easysoft XML-ODBC Server must have been installed in a location which is accessible from this machine.

2.  The name of the installation machine used in [1].

3.  The name of a System data source on the installation machine used in [1].

4.  The name of a table in the database referenced by the System data source on the installation machine used in [1].

The XmlOdbcDemo program supports the following command line arguments:

The name of the server machine on which the Easysoft XML-ODBC Server is running. This may be an IP address, a host name or a fully qualified domain name. In each case it is passed to gethostbyname(), so your client machine needs to be able to resolve this argument value into an IP address.

Optional. If not specified port 8895 is used. This is the port the XML-ODBC Server is listening on. The Easysoft XML-ODBC Server can be configured to listen on two ports, one for database requests and one for configuration requests (these are usually ports 8895 and 8896). You should obviously send database queries to the database port (8895) and configuration requests/operations to the control port (8896).

The connection string to use on the Easysoft XML-ODBC Server to connect to your database. This is an ODBC connection string i.e. the data passed to the ODBC API SQLDriverconnect() function.

In its simplest form this is a simple DSN name

e.g. "DSN=mydsn;".

For databases with built in authentication you may need to add UID and PWD attributes for the database user name and password

e.g.

"DSN=mydsn;UID=database_username;PWD=database_password;"

Data sources may require other attributes too, which should be specified in the connection string, separating each one with a ';'

e.g.

"ATTRIBUTE1=value;ATTRIBUTE2=value;"

The database query to be executed on the server machine.

This is usually a request for elements of a table.

e.g.

"select * from mytable"

The result of this query (the result set) will be returned in XML.

e.g.

Suppose you have installed the Easysoft XML-ODBC Server on "myserver", running in its default configuration (i.e. listening on ports 8895 for database requests and 8896 for configuration and control requests).

Suppose you have a Microsoft SQL Server database which contains a table called "mytable" on "myserver" with user authentication (a user name of "dbuser" and a password of "dbpass") and a data source called "mydsn" pointing to it:

java XmlOdbcDemo -h myserver -p 8895

-c "DSN=mydsn;UID=dbuser;PWD=dbpass;"

-s "select * from mytable"

The output should be the contents of the "mytable" table in XML and if an error occurs then the error is output in XML instead of as a result set.

Both are described in the "/Documentation" subdirectory of this distribution.

Client Return Status And Output

The return status of the XmlOdbc program is "0" for success and any other value for an error (see the code for all the possible return values).

As the XML output (including XML output generated for an error occurring on the server) is written to stdout and errors occurring at the client end are written to stderr (see the code for examples), XML output can be redirected to a file in most shells. e.g.:

java XmlOdbcDemo args 1> file.xml

Parsing The XML Output

There are many XML parsers available on the net for Java that you could use with this example client.

A good parser is Expat (http://expat.sourceforge.net) and James Clark has XP for Java at http://www.jclark.com/xml/xp.

The code for parsing the XML output of this client with XP is very simple, as can be seen by the many examples that XP contains.

Visual Basic

File List

An Active X DLL.

An example using xmlodbc.dll in Microsoft Excel.

xmlodbc.dll

This Active X DLL contains the following properties, collections and methods:

Properties and Collections:

The number of columns in a row of the result set. Only meaningful if Parse is used.

Contains Count (the number of columns in a row of the result set) and Name (the name of the nth column). Only meaningful if Parse is used.

States whether or not a Connect call succeeded and a connection has been made.

The ODBC connection string to be passed to the Easysoft XML-ODBC Server. It may also be passed to Connect as a parameter.

The index of the current row in the result set. Only meaningful if Parse is used.

The data for the specified column in the specified row. If the value of Row is omitted, it defaults to that of CurrentRow. Only meaningful if Parse is used.

Indicates whether CurrentRow now points past the end of the result set (i.e. Next has been called repeatedly to get to the end of Data). Only meaningful if Parse is used.

The state of the last Execute call. This mirrors ODBC status codes and is always "00000" for success and some other state (e.g. "HY000") for errors. It should be checked after calling Execute to ensure the specified SQL was executed correctly.

Contains descriptive error text if ErrorState is not equal to "00000". This may be the error text for a Winsock error, an internal Easysoft XML-ODBC Server error or an error returned from the ODBC driver.

The port number on which the Easysoft XML-ODBC Server is listening. Set this before calling Connect or pass as a parameter to Connect.

The name of the server machine where the Easysoft XML-ODBC Server is running. Set this before calling Connect or pass as a parameter to Connect.

The XML returned by the Easysoft XML-ODBC Server in response to the last Execute call.

If you do not want the XML parsed, so that you can access some of the xmlodbc.dll properties, you can use this yourself.

See "Document Type Definitions" for descriptions of the XML returned from the Easysoft XML-ODBC Server (note that this Active X DLL requests and receives all result sets using the Easysoft XML-ODBC Server Long format).

The number of rows in the result set. Only meaningful if Parse is used.

Methods:

Connects to an Easysoft XML-ODBC Server. ServerName and Port are optional, as they may be set using Serveror Port.

This method returns true if successful. If an error occurs use ErrorState or ErrorText to identify the problem.

Once all requests have been issued, use Disconnect.

Disconnects from an Easysoft XML-ODBC Server by closing the connection socket.

Requests the Easysoft XML-ODBC Server to which a connection has been made to access the ODBC data source defined in ConnectionStr and to issue the SQL in SQLStatement.

The value of ConnectionString may be set separately before calling Execute, rather than passing it as a parameter.

The Easysoft XML-ODBC Server will return either a result set or a status in XML to this client.

If Parse is used, ErrorState and ErrorText can be examined to see if the request was successful.

Parses the XML returned from the Easysoft XML-ODBC Server and initialises most of the xmlodbc.dll properties.

Sets CurrentRow to point to the first row in the result set.

Increments CurrentRow.

Decrements CurrentRow.

Sets CurrentRow to point to the last row in the result set.

xmlexample.exe

To start xmlexample.exe:

Register xmlodbc.dll at a DOS prompt by typing:

regsvr32 C:\WINNT\system32\xmlodbc.dll


NB

To unregister xmlodbc.dll, type:

regsvr32 -u full pathname/filename

To get help with registering DLLs, type:

regsvr32 -?

To access a sample table on the Easysoft demonstration database:

1.  Enter demo.easysoft.com into Server Name.

 This is the name of a server machine at Easysoft.

 See Server.

2.  Enter 8895 into Port.

 This is the number of the port on demo.easysoft.com which is listening for incoming connections.

 See Port.

3.  Click Connect.

 See Connect and Connected.

Figure 17: XML Sample Application connect error

Figure 18: XML Sample Application run-time error '429'

Figure 19: XML Sample Application run-time error '430'

4.  Enter DSN=pubs;UID=demo;PWD=easysoft; into Connection String.

 See ConnectionString.

5.  Enter select * from information_schema.tables into Query.


NB

If the contents of Server Name, Port, Connection String and Query are not left blank they are saved when the application exits and do not have to be retyped.

6.  Click Execute.

Parse is enabled.

 See Execute.

7.  Click Parse.

 See Parse.

8.  Click Firstto display the first record in the result set.

 See FirstRecord, CurrentRow and Data.

9.  Click Nextto display the next result set record.

 See NextRecord, CurrentRow, Data and Eof.

10.  Click Previousto display the previous result set record.

 See PreviousRecord, CurrentRow and Data.

11.  Click Lastto display the last record in the result set.

 See LastRecord, CurrentRow, Data and Eof.

No. Records displays the number of rows in the requested table (see RowCount) and No. Columns displays the number of columns in each row (see ColumnCount).


NB

If the XML is not parsed successfully, an error message is displayed in XML Result-set, State and Error Text (see ErrorState and ErrorText).

Figure 20: Easysoft Sample XML Application

netpipes

netpipes (http://web.purplefrog.com/~thoth/netpipes/ftp) is a useful program for connecting to the Easysoft XML-ODBC Server, issuing requests and obtaining the results.

The principle benefit of netpipes is the ability to make requests from a shell script.

Once built, the hose command can be used to send a request to the Easysoft XML-ODBC Server.

e.g.

hose server port -in -out sh -c

"(cat ./request.xml; sockdown) & cat > out.xml"

where:

server is the server machine where the Easysoft XML-ODBC Server is running

port is the port on server where the Easysoft XML-ODBC Server is listening for incoming connections

request.xml is a file containing an XML request

out.xml is the output file