Easysoft XML-ODBC Server User Guide - Client Applications

Developing Client Applications for the Easysoft XML-ODBC Server

This section includes useful information for use when creating client applications to connect to the Easysoft XML-ODBC Server.

See Sample Client Applications for sample client application classes and objects for use in connecting to the Easysoft XML-ODBC Server.

Chapter Guide

Requirements

In order to communicate with the Easysoft XML-ODBC Server, a client application needs to:

1.  connect a socket to port 8895 (the default) on the remote server.

2.  send an XML request to the server.

3.  read an XML response from the server (where "</Result>" signals the last line of XML).

4.  go back to step 2 or continue on to step 5.

5.  close the socket.

Any number of requests and responses may be transmitted once a socket has been opened and it is only necessary to close the socket once all operations have been completed.

A streaming XML text protocol is used between the client and server, allowing client applications to be easily written in any language.

Fully functional examples are distributed with the Easysoft XML-ODBC Server and documented in Sample Client Applications.

Example SQL XML

Sample XML code is supplied with the Easysoft XML-ODBC Server distribution for the various SQL requests and responses sent to and from the Easysoft XML-ODBC Server.

There are three types of XML SQL request which can be made to the Easysoft XML-ODBC Server, each of which uses the <Format> element of the XML request to defines the type of output required:

There are three corresponding XML formats for responses from the Easysoft XML-ODBC Server to SQL requests which are successful:

There is an additional XML error response format for failed SQL requests to the Easysoft XML-ODBC Server:

SQL requests

See "sql_request.dtd" for the SQL request DTD layout and detailed information of all the possible fields and elements which XML SQL request code may include.

A SQL request to return XML in Long, Short, Standard or TableTags format:

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

<Request>

<ConnectionString>

DSN=demo;UID=demo;PWD=easysoft;

</ConnectionString>

<Sql>select * from oobdist_contact</Sql>

<Format>

<Long/>OR <Short/>OR <Standard/>OR <TableTags/>

</Format>

</Request>

<ConnectionString> is ODBC defined, containing attributes and values, separated by semicolons, which are to be used on the Easysoft XML-ODBC Server to connect to your database.

This example connects to DSN called "demo"using database authentication consisting of a user name of "demo" and a password of "easysoft".

<Sql> is any SQL statement that your database accepts and which you want to execute on the Easysoft XML-ODBC Server.

This example requests all the rows to be returned from a table called "oobdist_contact".

<Format> must contain ONE of <Long>, <Short>, <Standard> and <TableTags>, where <Standard> is currently the same as <Long>. The output format defaults to <Long> if <Format> is not defined.


NB

This code is available in <InstallDir>/easysoft/xml-odbc/documentation/dtdson Unix, or <InstallDir>\Easysoft\XML-ODBC Server\Documentation\dtds on Windows as sql_request_long.xml, sql_request_short.xml or sql_request_tabletags.xml


Long SQL response

This is the default output format, producing a larger output for a given result set, because each <Column> in a <Row> contains a "Name" attribute to which is assigned the column name.

All the sample client applications distributed with the Easysoft XML-ODBC Server issue requests for the default format and expect <Long> format output (see Sample Client Applications) .

<Result State="00000">

<Row>

<Column Name="column1name">column 1 data</Column>

<Column Name="column2name">column 2 data</Column>

</Row>

</Result>

See "sql_response_long.dtd" for the Long SQL response DTD layout.

Short SQL response

This format firstly defines the column names in the <Columns> element.

Each <Row> element then contains multiple <Column> elements (one for each column in a result set row), each containing data for the columns which have been defined.

<Result State="00000">

<Columns>

<Column>column 1 name</Column>

<Column>column 2 name</Column>

</Columns>

<Row>

<Column>column 1 data</Column>

<Column>column 2 data</Column>

</Row>

</Result>

See "sql_response_short.dtd" for the Short SQL response DTD layout.

TableTags SQL response

This is the shortest output format, where the elements used to describe the result set are based on the ODBC driver cursor name and the column names.

<CursorName> will be the result of calling SQLGetCursorNameW().

You can use this output format to import data directly into Microsoft Office applications.

<Result>

<CursorName>

<Column1Name>column 1 data</Column1Name>

<Column2Name>column 2 data</Column2Name>

</CursorName>

</Result>

See "sql_response_tabletags.dtd" for the TableTags SQL response DTD layout.

SQL Error response

If a SQL request in any format is unsuccessful, then a response is returned containing diagnostic information rather than data (the diagnostic format is the same, irrespective of the request format).

An error can be distinguished by examining the <State> attribute in the <Request> element, which will contain either "00000" for successful requests, or some other state (defined by ODBC) for unsuccessful requests.

This will then be followed in the XML output by one or more <Diagnostic>elements.

e.g.

<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>

See "SQL" for response DTD layouts, showing the <Diagnostic> field and its elements.

Binary columns

The Easysoft XML-ODBC Server currently handles binary columns in one of three ways specified by the <Binary>element "Output" attribute (see "sql_request.dtd"):

This is the default (when no <Binary> element is specified in the request).

The Easysoft XML-ODBC Server requests binary columns from the ODBC driver as SQL_C_CHAR, producing two digit hex output (i.e. each 8 bit character in the binary column is output as two hex digits).

Here binary columns are ignored and are omitted from the result set.


NB

This means that if you issue a query which includes a binary column, it will be omitted from the XML result. This is the one case where the XML result does not match your SQL query.


Binary columns are output as base64.

Column data output formats

Column data may be output as either PCDATA or CDATA (see "sql_request.dtd").

By default the Easysoft XML-ODBC Server outputs column data as PCDATA.

This is specified with:

<Data Type="pcdata">

Column data output as PCDATA follows these rules:

1.  Characters with the eighth bit clear are output without change (for UTF-8).

2.  Characters with the eighth bit set are output as character references

 e.g.

 &#xXX; (where "XX" is the value in hex)

3.  Characters with values greater than "0xff" are output as two byte character references

 e.g.

 &#xXXXX;

4.  Entity references are output for "<", ">", "&", "'" and """, as follows:

> &gt;

< &lt;

' &apos;

" &quot;

& &amp;

<Data Type="cdata">

If you request column data as CDATA then these rules are followed :

1.  Column data in enclosed in a CDATA section:

 <![CDATA[column data here]]>

2.  Character references and character entities are not used.

3.  The UTF-8 output is pure UTF-8.

Truncating character columns

Fixed length SQL_CHAR columns are returned by an ODBC driver with all the trailing spaces.

You can override this behavior in the Easysoft XML-ODBC Server by using the <TruncateTrailingBlanks> element in the <Format> element of the request (see "sql_request.dtd").

e.g.

<Format>

<Long/>

<TruncateTrailingBlanks/>

</Format>

Column names

Column names are used as elements and element attributes in the various output formats, but some queries may produce a result set where the ODBC driver will not name some of the columns.

e.g.

select 1*5,column from table"

If the ODBC driver does not produce a name for the column the Easysoft XML-ODBC Server will use <ColumnN> (where "N" is the column number) as the column name.

This behaviour can be overridden by using the "as" SQL clause.

e.g

"select 1*5 as number,column from table"

Style Sheets

Microsoft Internet Explorer uses a default style sheet if one is not specified in an XML file with an XML Processing Instruction.

Mozilla by default removes the XML tags.

You can add a style sheet reference to the output XML using the <StyleSheet> element (see Adding Style Sheet references).

Adding Style Sheet references

By default the Easysoft XML-ODBC Server adds an XML version definition and an encoding definition, but no style sheet reference, to the start of the output XML.

A style sheet name can be specified and a request typed in by using the <StyleSheet> element:

<StyleSheet>

<Name>stylesheet name</Name>

<Type>stylesheet type</Name>

</StyleSheet>

e.g.

<StyleSheet>

<Name>test.xsl</Name>

<Type>text/xsl</Name>

</StyleSheet>

This results in the following XML Processing Instruction at the top of the XML output:

<?xml-stylesheet href="test.xsl" type="text/xsl"?>

This is useful if you define a style sheet template and want your browser to apply the template to the XML.

Transaction support

The Easysoft XML-ODBC Server can accept requests containing multiple SQL statements to be executed as a single transaction, which may be required if multiple updates, inserts or deletes are being performed as part of a single operation.

e.g.

Suppose users are allowed to register on a web site and have a checkbox which they can check to receive a newsletter.

When a user registers, one entry should be inserted into a registered users table and another entry into a table listing users who want to receive the newsletter.

There is no point in adding a entry to the newsletter table if the insertion into the registered users table fails, so either both insert statements should work or neither of them should.

The Easysoft XML-ODBC Server can achieve this by enclosing multiple <Sql> elements within a <Transaction> element.

e.g.

<Request>

<ConnectionString>xxx</ConnectionString>

<Transaction>

<Sql>first SQL statement in the transaction</Sql>

<Sql>second SQL statement in the transaction</Sql>

.

</Transaction>

</Request>

When the Easysoft XML-ODBC Server sees a transaction, it turns off AUTOCOMMIT in the ODBC driver and starts issuing the SQL statements one at a time.

If any of the SQL statements fail, an SQLEndTrans (SQL_ROLLBACK) statement is issued to roll back the transaction.

If all of the SQL statements succeed it issues a SQLCommit() statement to commit the transaction.