Neuron ESB User Network

The Service Bus for the Connected Business

Publishing events from Oracle updates

I am trying to publish a message whenever a particular column in a table is changed. I  was first going to use Oracle's Advanced Queue (AQ) which is similar to MS SQL's SQL Service Broker (SSB). Upon finding there to be no Oracle specific adapters, I've had to improvise.

I've created a table and trigger in Oracle to be used as a queue. When the column is changed, a row is inserted into the queue table with the ID, old value and new value.

I'm trying to configure an ODBC adapter in Neuron to poll this table, but keep running into difficulties. I cannot seem to find any documentation on setting up and ODBC adapter.

I have created an Adapter Registration called "ODBC Adapter". I have created an ODBC DSN for my database. I have created an Adapter Endpoint using the ODBC Adapter in Publish mode. I am able to test the connection successfully in the adapter connection properties dialog.

With my current configuration, I get the following in my logs. The table exists and can be queried from SQL*PLUS using the same credentials assigned to the ODBC DSN.

2018-07-06 10:32:04.359-06:00 [148] DEBUG - Creating the '******ChangeEndpoint' Adapter Endpoint ...2018-07-06 10:32:04.359-06:00 [148] DEBUG - Preparing to run the '******ChangeEndpoint' Adapter Endpoint under the current service identity '' ...2018-07-06 10:32:04.359-06:00 [148] DEBUG - Loading the 'ODBC Adapter' Adapter assembly for the '******ChangeEndpoint' Adapter Endpoint ...
2018-07-06 10:32:04.374-06:00 [148] DEBUG - Configuring the '******ChangeEndpoint' Adapter Endpoint for MODE 'Publish' ...

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ConnectionString = *******************

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: VerifyConnect = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: Transactional = None

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: IsolationLevelString = Serializable

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: TransactionTimeout = 60

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ConnectionTimeout = 15

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: CommandTimeout = 30

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: EnforceTypedParams = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SqlEnabled = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SqlCheckingEnabled = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SendTopic = ******Change

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: PollingInterval = 1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: OdbcCommandType = TableDirect

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SelectStatement = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: SelectParameters = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: UpdateDeleteStatement = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: UpdateDeleteParameters = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: TableDirectName = *****_QUEUE

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ForXmlClause = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RootNodeName = ******ChangeMessage

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RootNodeNameSpace = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RowNodeName = ******Change

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: GenerateSchema = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: LimitRows = -1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: DeleteTable = True

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: PublishOnNoData = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: RenameSourceFileExtension = dbq

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ErrorMode = Error

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: ErrorHandling = StopPollingOnError

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRootNodeName = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRowNodeName = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryRootNodeNameSpace = 

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryGenerateSchema = False

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: QueryLimitRows = -1

2018-07-06 10:32:04.374-06:00 [148] INFO  - Property: BatchSize = -1

2018-07-06 10:32:04.374-06:00 [148] DEBUG - Connecting the '******ChangeEndpoint' Adapter Endpoint using '******ChangePublisher' Party...

2018-07-06 10:32:06.374-06:00 [287] ERROR - A general publishing error occurred in the ODBC Adapter.



THE ADAPTER WILL NOW BE DISCONNECTED.

Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.



Stack Trace:

   at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable)

   at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Common.DbCommandBuilder.GetDeleteCommand(DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Odbc.OdbcCommandBuilder.GetDeleteCommand()

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()



System.InvalidOperationException: Dynamic SQL generation is not supported against a SelectCommand that does not return any base table information.

   at System.Data.Common.DbCommandBuilder.BuildInformation(DataTable schemaTable)

   at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Common.DbCommandBuilder.GetDeleteCommand(DataRow dataRow, Boolean useColumnsForParameterNames)

   at System.Data.Odbc.OdbcCommandBuilder.GetDeleteCommand()

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()

I've tried setting the Delete Table property to false, which results in the following log:

2018-07-06 10:30:09.260-06:00 [291] ERROR - A general publishing error occurred in the ODBC Adapter.THE ADAPTER WILL NOW BE DISCONNECTED.
ERROR [42S02] [Oracle][ODBC][Ora]ORA-00903: invalid table name





Stack Trace:

   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()



System.Data.Odbc.OdbcException (0x80131937): ERROR [42S02] [Oracle][ODBC][Ora]ORA-00903: invalid table name



   at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXMLAndDelete(OdbcCommand command, OdbcCommand deleteCommand, String rootName, String rootNamespace, Boolean generateSchema, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByTableDirect()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()

Tags: ODBC Adapter, TableDirect

Views: 319

Attachments:

▶ Reply to This

Replies to This Discussion

Permalink Reply by Martin Wasznicky on July 6, 2018 at 10:57am

you can find docs for the adapter here...a bit dated...but they work.  https://www.neuronesb.com/neuron/Help3/Development/Developing_Neuro...

you have to set sql enabled to true if you are using dynamic sql rather than running stored procs

Permalink Reply by Philip Saxton on July 6, 2018 at 11:12am

Thank you for your reply Marty,

Unfortunately the documentation is light on details about what properties need to be set for TableDirect OdbcCommandType and so I find myself trying to fiddle my way through the settings.

I have tried setting the SqlEnabled property to true as well as the SqlCheckingEnabled property. Any combination of those two properties results in the similar logs as above depending on whether the DeleteTable property is true or false.

I've tried changing the OdbcCommandType to Text and setting the SelectStatement to SELECT * FROM ******_QUEUE. I get a different log when I try that method:

2018-07-06 12:09:45.681-06:00 [332] ERROR - A general publishing error occurred in the ODBC Adapter.THE ADAPTER WILL NOW BE DISCONNECTED.
Arithmetic operation resulted in an overflow.



Stack Trace:

   at System.Data.Odbc.OdbcDataReader.FirstResult()

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXML(OdbcCommand command, String rootName, String rootNamespace, Boolean generateSchema, String rowName, Int32 limitRowsForTable, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByText()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()



System.OverflowException: Arithmetic operation resulted in an overflow.

   at System.Data.Odbc.OdbcDataReader.FirstResult()

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)

   at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)

   at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)

   at System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables, Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior behavior)

   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

   at Neuron.Esb.Adapters.OdbcAdapter.ProcessAdapter(DataTable dt, OdbcDataAdapter adapter, String rootNamespace, Int32 limitRowsForOutput, Boolean generateSchema, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.GetXML(OdbcCommand command, String rootName, String rootNamespace, Boolean generateSchema, String rowName, Int32 limitRowsForTable, List`1 properties, Int32& rowsReturned)

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveByText()

   at Neuron.Esb.Adapters.OdbcAdapter.ReceiveFromDatasource()

   at Neuron.Esb.Adapters.OdbcAdapter.TryReceive()
Permalink Reply by Martin Wasznicky on July 6, 2018 at 11:17am

what build/version of neuron are you using?

Permalink Reply by Philip Saxton on July 6, 2018 at 11:18am

Neuron ESB 3.5.4.1038

Permalink Reply by Martin Wasznicky on July 6, 2018 at 11:20am

ok....I just googled the error.  looks like this is a bug with the Oracle ODBC driver you're using :  https://blogs.msdn.microsoft.com/mariae/2012/07/05/arithmetic-opera...

you may need to update your driver

Permalink Reply by Philip Saxton on July 6, 2018 at 11:24am

Lovely.

Thanks for your attention to this. I will try updating the Oracle drivers and update when I've tested it.

Permalink Reply by Martin Wasznicky on July 6, 2018 at 11:26am

I've had issues with Oracle ODBC drivers in the past.  its one of the reasons we also provide an X86 install of Neuron....specifically for older ODBC drivers...

Permalink Reply by Philip Saxton on July 6, 2018 at 1:46pm

So, after updating to the Oracle ODBC driver to 11.02.00.04, I am able to get some data back from Oracle using OdbcCommandType = Text as above.

There are presently 10 rows in the table, but I am only receiving 2 rows when SelectStatement = SELECT * FROM ******_QUEUE and BatchSize = -1 and I'm not certain how to only remove the rows I've read in the UpdateDeleteStatement to move past those first two rows.

I am still receiving the invalid table name error when trying to use OdbcCommandType = TableDirect. I am just putting the table name in the TableDirectName field, should this be quoted or qualified in some way? It would really be useful to be able to see the SQL being generated.

Permalink Reply by Martin Wasznicky on July 6, 2018 at 2:09pm

no...the ODBC driver may not support direct table name usage though.  I think this was specifically added to support things like sql.

so generally, the update/delete statement can be useful if you know the criteria that you can use that was also used to download in the first place....usually this would be the where clause in the select statement.

if that's not doable, than you should use a stored proc instead....that way you can do the pull and delete in the same transaction...

Permalink Reply by Philip Saxton on July 9, 2018 at 11:18am

So, I've created a stored procedure with an OUT SYS_REFCURSOR parameter. Is there any documentation or examples of how to use this refcursor in Neuron?

Permalink Reply by Martin Wasznicky on July 9, 2018 at 11:28am

we had a consultant do this once, returning a ref_cursor.  you may have to change to ref_cursor if the sys_refcursor doesn't work.   there's nothing special on the neuron side...just follow the standard ODBC call syntax.  Here's what the consultant's stored proc looked like:

This is the syntax of the Oracle stored procedure that returns a ref_cursor:

 

PROCEDURE GET_ROUTES(p_branch in varchar2,

p_error_code in out varchar2,

p_error_message in out varchar2,

cur_return out R_CURSOR) is

 

BEGIN

p_error_code := 0;

p_error_message := null;

--

 

OPEN cur_return FOR

select route_number,route_id from lfcx_route_definitions

where branch_number=p_branch

ORDER BY route_number;

END GET_ROUTES;

and here's the screen shot of the adapter configuration:

Permalink Reply by Philip Saxton on July 9, 2018 at 1:29pm

Thank you for the reply, Marty.

I was able to finally get this all connected. The secret appears to be that the OUT REF_CURSOR needs to not be included in the call or parameter collection.

Thanks,

--Phil

RSS

Neuron ESB Product Support Forums and Communities

Latest Activity

Anupama Nair posted a discussion

Marketo Adapter Invalid Token

Hi,We are using the Marketo adapter to push account updates to Marketo. It works well for some time then starts failing with Invalid Token unless restarted. Is there a configuration that can be done so it can auto refresh the token when required?Thanks!See More
Nov 6, 2023
1 Comment
Sayantini Basak posted a discussion

Maximum payload size(REST API) for requests interfacing to NeuronESB

I am new to Neuron ESB and in our current scenario,We need to process batch transactions comprising of ~1000 records and send them to Neuron ESB for further processing. I would like to understand what is the maximum size of payload that can be transferred using REST interface to Neuron ESB.See More
Jul 22, 2022
0 Comments
Profile IconRobert E Dunie and Sayantini Basak joined Neuron ESB User Network
Apr 28, 2022
Profile IconDayanand, Frederic C, Steffen Greve-Oksfeldt and 1 more joined Neuron ESB User Network
Mar 16, 2022
Profile IconCam Vong and Mitja Luznar joined Neuron ESB User Network
Jan 27, 2022
Profile IconWill Hitzges, Chad Parsons, michael larsen and 4 more joined Neuron ESB User Network
Jun 11, 2021
Anupama Nair posted a discussion

ODBC stored proc polling with temporary tables

We have set up an ODBC adapter to poll a stored proc.We found that if the stored proc has a temporary table defined the rows returned are always 0.Any idea why this would be and what we can do to get around it?See More
Dec 14, 2020
3 Comments
Prasanth Kharade is now a member of Neuron ESB User Network
Dec 30, 2019
Welcome Them!

Badge

Loading…

Get Badge

© 2024   Created by Neuron Admin.   Powered by

Badges  |  Report an Issue  |  Terms of Service

Sign in to chat!