SQL Adapter

Overview

The SQL adapter will allow Neuron to send data to or retrieve data from MS SQL server.

Requirements

Microsoft SQL Server 2005 or later is required to use this adapter.

Adapter Modes

The adapter supports the following modes of operation.

Mode	Description
Subscribe	The adapter receives a SQL 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	The adapter receives a SQL adapter XML formatted message from Neuron and executes a user-defined stored procedure. No results are to be returned.
Query	The adapter received a SQL adapter XML formatted message from Neuron and executes a user-defined stored procedure. The results are published back to Neuron.

Subscribe Mode

When the adapter receives a SQL 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 ‘Max Insert Batch Count’ 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 ‘Poll Interval’ property.  The results of the stored procedure or select command are published to Neuron.

Calling a Stored Procedure

To configure the adapter to call a stored procedure, the ‘Use Stored Procedure’ property must be set, and the ‘Stored Procedure Name’ property must be the name of a valid stored procedure. 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.

 In publish mode, when the ‘Use Stored Procedure’ property is set, the value of the ‘Database Table’ property is ignored.

Selecting From a Table

To configure the adapter to select from a table directly, the ‘Use Stored Procedure’ property must be false, and the ‘Database Table’ property must be the name of a valid database table.

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.

Once the message has been successfully published to Neuron the rows will be deleted from the table.

In publish mode, when the ‘Use Stored Procedure’ property is false, the value of the ‘Stored Procedure Name’ property is ignored.

Any message received from Neuron will be discarded when the adapter is in publish mode.

Execute Mode

When the adapter receives a SQL 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 a SQL 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.

Adapter Properties

Property Name	Description
General Properties
Connection String	Sets the connection string to the MS SQL database.
Isolation Level	The transaction isolation level.
Transactional	Sets the option to require all database operations to take place within a transaction.
Publish Mode Properties
Database Table	Sets the name of the database to select from when in Publish mode.
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.
Polling Interval	Publish mode only. Sets the interval on which the database is queried for data. This value is specified in seconds.
Publish Topic	Publish mode only. Sets the topic to publish the message to.
Stored Procedure Name	Publish mode only. Sets the stored procedure name to call. The property “Use Stored Procedure” must be set when calling a stored procedure.
Use Stored Procedure	Publish Mode only. Sets the option to use a stored procedure.
For XML Clause	If the FOR XML clause is used on the select statement in the stored procedure then this option should be set to true.
Subscribe Mode Properties
Max INSERT Count Per Batch	Subscribe mode only. Sets the maximum number of SQL INSERT commands to execute in a single batch.

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.

Subscribe Mode

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

<Data>
  <Table name="table1">
    <Row>
      <Column name="columnName1">columnValue1</Column>
    </Row>
  </Table>
</Data>

Example:

<Data>
  <Table name="Orders">
    <Row>
      <Column name="Id">12345</Column>
      <Column name="Item">Xbox 360 Elite</Column>
    </Row>
  </Table>
  <Table name="NewCustomer">
    <Row>
      <Column name="FirstName">John</Column>
      <Column name="LastName">Doe</Column>
    </Row>
    <Row>
      <Column name="FirstName">Jane</Column>
      <Column name="LastName">Doe</Column>
    </Row>
  </Table>
</Data>

For reference, the example listed above would translate into the following SQL command:

INSERT INTO Orders (Id, Item) VALUES (‘12345’, ‘Xbox 360 Elite’)

INSERT INTO NewCustomer (FirstName, LastName) VALUES (‘John’, ‘Doe’)

INSERT INTO NewCustomer (FirstName, LastName) VALUES (‘Jane’, ‘Doe’)

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>

Execute Mode

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

This is the XML format of the incoming Neuron message. The sp attribute specifies the name of the stored procedure. If there are any parameters, a <Parameters> element should be used and defines each parameter.

<Execute sp="spName">
  <Parameters>
    <Parameter name="@paramName1" value="value1"   />
  </Parameters>
</Execute>

Example:

<Execute sp="InsertOrder">
  <Parameters>
    <Parameter name="@OrderId" value="AB123"   />
    <Parameter name="@OrderItem" value="XBox   360 Elite" />
  </Parameters>
</Execute>

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 SQL adapter XML format. The XML describes the stored procedure 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. The sp attribute specifies the name of the stored procedure. If there are any parameters, a <Parameters> element should be used and defines each parameter.

<Execute sp="spName">
  <Parameters>
    <Parameter name="@paramName1" value="value1"   />
  </Parameters>
</Execute>

Example:

<Execute sp="InsertOrder">
  <Parameters>
    <Parameter name="@OrderId" value="AB123"   />
    <Parameter name="@OrderItem" value="XBox   360 Elite" />
  </Parameters>
</Execute>

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>