ODBC Pathway Protocol Version 2.0

This protocol is a backwards compatible extension of the protocol used by ODBC Socket Server written by Team fXML, details of which can be found elsewhere.

To simplify determining the CLIENT version, I have designated the old (fXML) protocol as Protocol Version 1 (V1). My extensions are designated Protocol Version 2 (V2). The server can tell which version the client supports by looking for the version="x.x" attribute at various places in the request. The client can query the server to find out which protocol it supports by sending a V2 version request (see below) and testing for a successful response - a V1 server will not understand the query and will send an error response.

The V1 protocol used a simple XML encoding to send a DSN and query to the V1 server.

The XML is shown "pretty-printed" here but, as with all XML, the exact formatting and layout of whitespace should not be counted upon.

V1 Query Request

<?xml version="1.0"?>
<request>
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <sql>SELECT * FROM XXX_my_table_or_view_XXX</sql>
</request>

V1 Success Response

<?xml version="1.0"?>
<result state="success">
 <row>
  <column name="XXX_my_column_1_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_2_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_3_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_4_XXX">XXX_DATA_XXX</column>
 </row>
 <row>
  <column name="XXX_my_column_1_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_2_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_3_XXX">XXX_DATA_XXX</column>
  <column name="XXX_my_column_4_XXX">XXX_DATA_XXX</column>
 </row>
</result>

Note:

In the TEAM fXML server, the name attribute on the <column> element may or may not be present in the second and subsiquent <row> element. Their presence depends on a server configuration variable. In the HaqaSoft V2 protocol they are ALWAYS PRESENT in EVERY row.

V1 Failure Response

<?xml version="1.0"?>
<result state="failure">
 <error>XXX_system_error_message_goes_here_XX</error>
</result>

Note:

In cases of V1 protocol violation, the V1 server does not always return a failure response. The V2 server will always return either a success or a failure response.

The V2 protocol builds on this solid foundation by adding the ability to retreive a list of tables, the structure of a table and to execute multiple queries within a single transaction.

V2 Query Request

<?xml version="1.0"?>
<request version="2.0">
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <sql>SELECT * FROM XXX_my_table_or_view_XXX</sql>
</request>

All of the following requests are only valid with the version="2.0" attribute included, both servers will produce an error if this is omitted. Depending on the configuration of the server, any of the responses shown here may be compressed.

V2 Version Request

<?xml version="1.0"?>
<request version="2.0">
 <version />
</request>

V2 Version Response

<?xml version="1.0"?>
<result state="success" version="2.0" />

V1 Version Response

Note:

A request not matching the V1 Query Request (Above) will always get an empty response from a V1 server.

V2 Catalog Request

<?xml version="1.0"?>
<request version="2.0">
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <catalog />
</request>

V2 Catalog Response

<?xml version="1.0"?>
<result state="success" version="2.0">
 <table>XXX_table_name_1_XXX</table>
 <table>XXX_table_name_2_XXX</table>
 <table>XXX_table_name_3_XXX</table>
</result>

Note:

The V2 server makes no destinction between different catalog object types - everything is considered to be a table.

V2 Multiple Request

<?xml version="1.0"?>
<request multiple="3" version="2.0">
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <subrequest id="1">
  <sql>SELECT * FROM Table1</sql>
 </subrequest>
 <subrequest id="2">
  <sql>SELECT * FROM Table2</sql>
 </subrequest>
 <subrequest id="3">
  <connectionstring>DSN=SomeOtherDSN</connectionstring>
  <catalog />
 </subrequest>
</request>

V2 Multiple Response

<?xml version="1.0"?>
<result multiple="3" version="2.0">
 <subresult id="1" state="success">
  <schema table="XXX_table_name_1_XXX">
  <column name="XXX_my_column_1_XXX">XXX</column>
  <column name="XXX_my_column_2_XXX">XXX</column>
  <column name="XXX_my_column_3_XXX">XXX</column>
  <column name="XXX_my_column_4_XXX">XXX</column>
  </schema>
 </subresult>
 <subresult id="2" state="success">
  <column name="XXX_my_column_1_XXX">XXX</column>
  <column name="XXX_my_column_2_XXX">XXX</column>
  <column name="XXX_my_column_3_XXX">XXX</column>
  <column name="XXX_my_column_4_XXX">XXX</column>
 </subresult>
 <subresult id="3" state="failure">
  <error>XXX_system_error_message_goes_here_XX</error>
 </subresult>
</result>

Note 1:

The multiple attribute of the request element is set to a count of the number of subrequests. This may be zero or any positive integer.

Note 2:

Each subrequest has an id attribute, which allows the client to match subresults to subrequest.

Note 3:

The connectionstring may exist in the request element, and/or in the subrequest elments.

Note 4:

It is perfectly possible (As shown) for one subrequest in a V2 Multiple Request to fail. This should not affect the other subrequests in the set.

V2 Schema Request (One Object)

<?xml version="1.0"?>
<request version="2.0">
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <schema>XXX_table_name_XXX</schema>
</request>

V2 Schema Response (One Object)

<?xml version="1.0"?>
<result state="success" version="2.0">
 <schema table="XXX_table_name_1_XXX">
  <column name="XXX_my_column_1_XXX" />
  <column name="XXX_my_column_2_XXX" />
  <column name="XXX_my_column_3_XXX" />
  <column name="XXX_my_column_4_XXX" />
 </schema>
</result>

V2 Schema Request (All Objects) !!!

<?xml version="1.0"?>
<request version="2.0">
 <connectionstring>DSN=XXX_my_dsn_XXX</connectionstring>
 <schema />
</request>

V2 Schema Response (All Objects)

<?xml version="1.0"?>
<result multiple="2" version="2.0">
 <subresult id="1" state="success">
  <schema table="XXX_table_name_1_XXX">
   <column name="XXX_my_column_1_XXX" />
   <column name="XXX_my_column_2_XXX" />
   <column name="XXX_my_column_3_XXX" />
   <column name="XXX_my_column_4_XXX" />
  </schema>
 </subresult>
 <subresult id="2" state="success">
  <schema table="XXX_table_name_1_XXX">
   <column name="XXX_my_column_1_XXX" />
   <column name="XXX_my_column_2_XXX" />
   <column name="XXX_my_column_3_XXX" />
   <column name="XXX_my_column_4_XXX" />
  </schema>
 </subresult>
</result>

Note 1:

Although the request is not an explicit V2 Multiple Request, it will generate a V2 Multiple Response. The id attributes are auto generated for the individual subresult elements.

Note 2:

For a database with many tables or tables with many columns, this could generate A LOT of data!