ODBC Adapter

Overview

The ODBC adapter will allow Neuron to send data to or retrieve data from any ODBC-accessible databases.

Requirements

An ODBC driver to the database you want to integrate with.

Adapter Modes

The adapter supports the following modes of operation.

Mode	Description
Execute	The adapter receives an Odbc adapter XML formatted message from Neuron and executes a user-defined stored procedure. No results are to be returned.
Query	The adapter received an Odbc adapter XML formatted message from Neuron and executes a user-defined stored procedure. The results are published back to Neuron.
Batch	The adapter receives an Odbc adapter XML formatted message from Neuron and inserts the data into SQL tables.
Publish	The adapter executes a user-defined stored procedure or selects from a table and the results are published to Neuron.

 

Execute Mode

When the adapter receives an Odbc adapter XML formatted message from Neuron, the data from the XML message is used to execute a stored procedure.

The stored procedure called is user-defined.

Query Mode

Query mode functions the same as execute mode, except that a result set is published to Neuron. When the adapter receives an Odbc adapter XML formatted message from Neuron, the data from the message is used to execute a stored procedure. The result set returned by the stored procedure is then published to Neuron. If the result set is empty, a message will still be published.

The stored procedure called is user-defined.

Batch Mode

When the adapter receives an Odbc adapter XML formatted message from Neuron, the data in the rows of the XML message are used to create a SQL INSERT commands and the commands are then executed. The ‘Batch Size’ property will allow one to limit the number of INSERT commands that are run in a single batch.

Publish Mode

The adapter will select results from the specified table or execute the specified stored procedure on an interval specified in the ‘Polling Interval’ property.  The results of the stored procedure or select command are published to Neuron.

Calling a Stored Procedure

To configure the adapter to poll by calling a stored procedure, the ‘Command Type’ property must be set to 'StoredProcedure', and the ‘Polling Statement’ property must be the name of a valid stored procedure. When calling a stored procedure, the 'Polling Statement' property must be in Odbc call syntax, i.e. {Call myStoredProcedure(?,?)}.  The results of the stored procedure are published to Neuron as XML. If the stored procedure returns an empty result set, then no message is published.

The stored procedure called is user-defined.

Selecting From a Table

To configure the adapter to poll by selecting data from a table directly, the ‘Command Type’ property must be set to 'Text', and the ‘Polling Statement’ property must be the SELECT statement to execute on each call. When executing a SELECT statement, the 'Polling Statement' property must be in Odbc call syntax, i.e. SELECT * FROM PhoneBook WHERE Id = ?..

The adapter will select the top number of rows where the number of rows is configured by the ‘Batch Size’ property. If the batch size is 1, then only the top 1 row will be selected from the table. If the batch size is 100, then only the top 100 rows will be selected. The results of the select will be published to Neuron as XML. If the selected result set is empty, then no message is published.

After the SELECT statement is executed, Neuron can optionally execute an UPDATE or DELETE statement.  To modify a set of records after the SELECT statement, set the 'UPDATE/DELETE Statement' property to the UPDATE or DELETE statement to execute.  The statement must be in Odbc Call syntax, i.e. UPDATE PhoneBook SET Status = ? WHERE Id = ?.

Adapter Properties

 

Property Name	Description
General Properties
Connection String	Sets the connection string to use for communicating with the Odbc data source.
Transactional	Sets the option to require all database operations to take place within a transaction.
Transaction Isolation Level	The transaction isolation level.
Transaction Timeout	Sets the number of seconds required for the Transaction to timeout.
Require Typed Parameters	Requires that all parameter elements include the type attribute with the appropriate Odbc Data type value.
Sql Enabled	Sets whether or not dynamic SQL can be passed to the adapter.  If set to false, only stored procedures are allowed.
Batch Mode Properties
Batch Size	Sets the number of insert statements per table to be sent to the server.
Publish Mode Properties
Publish Topic	The Neuron topic that messages will be published to.  Required for Publish mode.
Polling Interval	Sets the interval between polling executions. This value is specified in seconds.
Command Type	Indicates how the adapter will retrieve results when polling. 'Text' allow SQL (SELECT, UPDATE and DELETE) statements; 'StoredProcedure' allows a Stored Procedure to be used; 'TableDirect' allows for specifying the contents of a Table to be returned.
Polling Statement	Either a SELECT statement or Stored Procedure to execute on each poll.  The statement must be in Odbc Call syntax i.e. {Call myStoredProcedure(?,?)} or SELECT * FROM PhoneBook WHERE Id = ?.
Parameters	Name	Description
	Data Type	The ODBC command parameter data type.
	Name	An ODBC command parameter name.
	Value	Value for the ODBC command parameter.
Batch Size	Publish mode only. Sets the maximum number of rows to select when selecting directly from a database table. This property is not used when using a stored procedure.
Update/Delete Statement	UPDATE or DELETE Statement to execute after the SELECT Statement on each poll.  The statement must be in Odbc Call syntax i.e. {Call myStoredProcedure(?,?)} or SELECT * FROM PhoneBook WHERE Id = ?.
Parameters	Name	Description
	Data Type	The ODBC command parameter data type.
	Name	An ODBC command parameter name.
	Value	Value for the ODBC command parameter.
For XML Clause	Only used for 'SQL Server Native Client' ODBC Driver. Indicates if the stored procedure or select statement used for polling is using a  FOR XML clause.
Root Node Name	Root node name for the returned XML document.
Target Namespace	Target Namespace used for the returned XML document.
Row Node Name	The row node name for the returned XML document.
Generate Schema	Indicates whether an XSD Schema is generated and returned with the XML document.
Number of Rows	Indicates the maximum number of rows to return from the polling statement.  If set to -1, then all rows are returned.
Publish Empty Message	Indicates whether an empty message containing only the root node will be published if no results are returned from the polling statement.
Rename Source File Extension	Used to rename the ODBC source file specified within the 'dbq' keyword. If the file exists, the extension will be appended to the original file name, i.e. if the original file was 'somefile.txt' then renamed file would be 'somefile.txt.<extention>'.
Error Reporting	Determines how all errors are reported in the Event Log and Neuron Log files.  Errors can be reported as Errors, Warnings or Informational messages.
Error on Polling	Determines if polling of data source continues when an error is encountered and whether or not consecutive errors are reported.
Query Mode Properties
Root Node Name	Root node name for the returned XML document.
Row Node Name	The row node name for the returned XML document.
Target Namespace	Target Namespace used for the returned XML document.
Generate Schema	Indicates whether an XSD Schema is generated and returned with the XML document.
Number of Rows	Indicates the maximum number of rows to return from the polling statement.  If set to -1, then all rows are returned.

 

When specifying the publish topic, ensure that the configured party for the adapter endpoint has a publish subscription to the topic.

Adapter Metadata

This adapter does not use any metadata properties.

Message Format

The adapter uses a custom XML format in all modes. The body of the messages that are published to Neuron will be XML, and message bodies received from Neuron must be in the custom XML format. There are several formats of the XML depending on the mode.

Execute Mode

The adapter receives a message from Neuron and executes a stored procedure. The body of the Neuron ESBMessage must be the custom ODBC adapter XML format. The XML describes the stored procedure or SQL Statement called and parameters that are passed.

This is the XML format of the incoming Neuron message. In the <Statement> element, the type attribute specifies whether a stored procedure or SQL statement is executed. The sql attribute specifies the stored procedure or SQL statement to be executed. The sql attribute must be in the format using he ODBC call syntax.

If there are any parameters, a <Parameters> element should be used and defines each parameter.  The type attribute is optional, but most ODBC data sources need them.  When used, the type attribute provided must be an ODBC data type.

 

<Statement type="StoredProcedure" sql="{Call uspUpdatePhoneNumber(?,?)}">

    <Parameters>

        <Parameter type="int" name="@Id" value="3"/>

        <Parameter type="varchar" name="@PhoneNumber" value="11111"/>

    </Parameters>

</Statement>

 

<Statement type="Text" sql="INSERT INTO phonebook(LastName, FirstName,PhoneNumber) VALUES(?,?,?)">

    <Parameters>

        <Parameter type="varchar" name="@LastName" value="Wasznicky"/>

        <Parameter type="varchar" name="@FirstName" value="Todd"/>

        <Parameter type="varchar" name="@PhoneNumber" value="3109890000"/>

    </Parameters>

</Statement>

 

Query Mode

Query mode functions the same as execute mode, except that a result set from the stored procedure is published to Neuron. The adapter receives a message from Neuron and executes a stored procedure. The body of the Neuron ESBMessage must be the custom ODBC adapter XML format. The XML describes the stored procedure or SQL Statement called and parameters that are passed. The response XML message is published back to Neuron.

This is the XML format of the incoming Neuron message. In the <Statement> element, the type attribute specifies whether a stored procedure or SQL statement is executed. The sql attribute specifies the stored procedure or SQL statement to be executed. The sql attribute must be in the format using he ODBC call syntax.

If there are any parameters, a <Parameters> element should be used and defines each parameter.  The type attribute is optional, but most ODBC data sources need them.  When used, the type attribute provided must be an ODBC data type.

 

<Statement type="Text" sql="select * from PhoneBook where Id = ?">

    <Parameters>

        <Parameter type="int" name="@Id" value="2"/>

    </Parameters>

</Statement>

 

<Statement sql="{Call InsertPaymentRecord (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)}" type="StoredProcedure">

    <Parameters>

        <Parameter type="varchar" name="VENDOR" value="BIT" />

        <Parameter type="varchar" name="SOURCE_FILE_NAME" value="myfile" />

        <Parameter type="varchar" name="SOURCE_UPLOAD_DATE" value="09/01/2008 12:00:00 AM" />

        <Parameter type="int" name="CORRECTION" value="0" />

        <Parameter type="varchar" name="STUDY_NO" value="C10953/1086" />

        <Parameter type="varchar" name="COUNTRY_CODE" value="USA" />

        <Parameter type="int" name="SITE_NO" value="1" />

        <Parameter type="int" name="PI_REFERENCE_NO" value="3880" />

        <Parameter type="varchar" name="PI_LAST_NAME" value="Medlock" />

        <Parameter type="varchar" name="PI_FIRST_NAME" value="Matthew" />

        <Parameter type="varchar" name="SITE_NAME" value="PPD Development, LP" />

        <Parameter type="varchar" name="SITE_ADDRESS" value="7551 Metro Center Drive &#xD;&#xA;Suite 200" />

        <Parameter type="varchar" name="CITY" value="Austin" />

        <Parameter type="varchar" name="STATE" value="TX" />

        <Parameter type="varchar" name="ZIP" value="78744" />

        <Parameter type="varchar" name="PAYEE" value="PPD Development, LP " />

        <Parameter type="varchar" name="PAYMENT_TYPE" value="PV" />

        <Parameter type="decimal" name="AMOUNT" value="815200.05" />

        <Parameter type="varchar" name="RAISED_DATE" value="07/14/2011 06:00:00 PM" />

    </Parameters>

</Statement>

 

When calling in QUERY mode and using either ReturnValue or OutPut parameters to return a value from the Query call, the following format MUST be used.  Notice the new “direction” attribute.  This can be either ReturnValue or OutPut.  This attribute is ONLY REQUIRED when in QUERY mode AND they want to return the value using a ReturnValue or OutPut parameter:

<Statement sql="{? = Call CLOB_InsertPaymentRecord (?,?)} " type="Text">

    <Parameters>

        <Parameter type="Text" name="@RETURN_VALUE" size="1" direction="ReturnValue" value=""/>

        <Parameter type="String" name="RECORD_NO" value="C9722/2051"/>

        <Parameter type="String" name="DATA" value="19.99"/>

    </Parameters>

</Statement>

 

This is the XML format of the response message. The value used for the table name attribute will be the value of the adapter property ‘Database Name’. If there are 0 records in the result set, then the <Row> element will not be present.

<Data>

  <Table name="table1">

    <Row>

      <Column name="columnName1">columnValue1</Column>

    </Row>

  </Table>

</Data>

 

Publish Mode

The adapter executes a stored procedure or select SQL command and the result set is published to Neuron. The result set will be in the following XML format.

This is the XML format of the results published to Neuron when selecting on a table directly.

<Data>

  <Table name="[table1]">

    <Row>

      <Column name="columnName1">columnValue1</Column>

    </Row>

  </Table>

</Data>

 

This is the XML format of the results published to Neuron when using a stored procedure.

<Data>

  <StoredProc name="spName">

    <Row>

      <Column name="columnName1">columnValue1</Column>

    </Row>

  </StoredProc>

</Data>

 

Batch Mode

The adapter receives a message from Neuron and inserts records into SQL. The body of the Neuron ESBMessage must be in the custom ODBC adapter XML format. The XML describes the table and values that are to be inserted.

<Batch>

  <Statement name="TableName" type="Table">

    <Fields>

      <Field type="AnSqlType" name="ColumnName">8</Field>

      <Field type="nvarchar" name="NillableExample" nillable="true">null</Field>

    </Fields>

  </Statement>

</Batch>

 

Example:

<Batch>

  <Statement name="Sales.SalesOrderHeader" type="Table">

    <Fields>

      <Field type="tinyint" name="RevisionNumber">8</Field>

      <Field type="datetime" name="OrderDate">2011-05-31</Field>

      <Field type="datetime" name="DueDate">2011-06-12</Field>

      <Field type="datetime" name="ShipDate">2011-06-07</Field>

      <Field type="tinyint" name="Status">5</Field>

      <Field type="bit" name="OnlineOrderFlag">0</Field>

      <Field type="nvarchar" name="PurchaseOrderNumber">PO522145787</Field>

      <Field type="nvarchar" name="AccountNumber">10-4020-000676</Field>

      <Field type="int" name="CustomerID">29825</Field>

      <Field type="int" name="SalesPersonID">279</Field>

      <Field type="int" name="TerritoryID">5</Field>

      <Field type="int" name="BillToAddressID">985</Field>

      <Field type="int" name="ShipToAddressID">985</Field>

      <Field type="int" name="ShipMethodID">5</Field>

      <Field type="int" name="CreditCardID">16281</Field>

      <Field type="varchar" name="CreditCardApprovalCode">105041Vi84182</Field>

      <Field type="int" name="CurrencyRateID">4</Field>

      <Field type="decimal" name="SubTotal">20565.6206</Field>

      <Field type="decimal" name="TaxAmt">1971.5149</Field>

      <Field type="decimal" name="Freight">616.0984</Field>

      <Field type="nvarchar" name="Comment" nillable="true">null</Field>

    </Fields>

  </Statement>

</Batch>

Stored Procedure Example:

Note: This example can be used with the MessageData database found in many of the samples in the Samples and Walkthroughs section.

<Batch>

  <Statement name="CreateCustomer" type="StoredProcedure">

    <Fields>

      <Field type="int" name="@CustomerID">1001</Field>

      <Field type="nvarchar" name="@Name">Northwind Traders</Field>

      <Field type="nvarchar" name="@EmailAddress">orders@northwind.com</Field>

    </Fields>

  </Statement>

  <Statement name="CreateCustomer" type="StoredProcedure">

    <Fields>

      <Field type="int" name="@CustomerID">1002</Field>

      <Field type="nvarchar" name="@Name">LitWare Inc.</Field>

      <Field type="nvarchar" name="@EmailAddress">accounts@litware.com</Field>

    </Fields>

  </Statement>

  <Statement name="CreateCustomer" type="StoredProcedure">

    <Fields>

      <Field type="int" name="@CustomerID">1003</Field>

      <Field type="nvarchar" name="@Name">AdventureWorks Cycles</Field>

      <Field type="nvarchar" name="@EmailAddress">admin@adventureworks.com</Field>

    </Fields>

  </Statement>

</Batch>