Database-Inbound/Outbound Protocols

[Jerry Tilsley]

Anybody out there using the Database Protocols that shipped with 6.0?  Trying to use SQLite with it and everything works fine in the test tool, but then when you try and use it on a thread it errs out stating that the Input to Method was “”.  Any help would be appreciated.

Thanks,

Jerry

Topic

[Max Drown (Infor)]

In 6.0.1, the SQLite and MySQL databases will work much better with CIS. For now, they are buggy and unsupported (uncertified). If you can hang in there for a little but, I think you will be very pleased with the support for these two databases.

-- Max Drown (Infor)

[Jerry Tilsley]

What is the time frame for 6.0.1 to be released?  I’m pretty disappointed thus far with these protocols and am really hoping for a major improvement.

[Max Drown (Infor)]

I’m not sure yet. It’s in beta right now.

I’ve been very happy with the database protocols!! We’ve done some amazing stuff with some complicated queries. These protocols will see some dramatic improvements over the next couple of years. If you have any suggestions, please submit suggestions and enhancement requests.

That being said, I would encourage you to always seeks to use stored procedures instead of direct queries inside of CIS. You’ll always be better off managing the queries on the database side and returning only what you need to CIS.

-- Max Drown (Infor)

[Jim Kosloskey]

jerry,

I have not played with the 6.0 JDBC imbedded but it does not seem to support the use of Stored Procedures the way we would like it.

Because all of our DB work involves the invocation of Stored Procedures we will probably continue to use the Data Integrator (DataDirect ODBC) drivers for our DB work for some time but watch the evolution of the JDBC imbedded protocol.

So while I agree whole heartedly with Max vis-a-vis using Stored Procedures I do not agree the current Database protocol is the way to go for us.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Max Drown (Infor)]

What sort of problems are you having with stored procedures? Are you using the database-inbound/outbound protocols?

-- Max Drown (Infor)

[Jerry Tilsley]

Firstly for me, I use several SQLite databases which does not support stored procuedres, thus having no other option but to use SQL commands.  Though the use of triggers can aid with inserts/updates/deletes.

What I would like to figure out regardless of the database type is who to configure these connections to do the following:

On a database-inbound thread when a record is read, can you submit an update statement so you can mark a column indicating so?  Or can you perform other actions per-record on the items received?  It would be nice to have a UPoC there which would let you take action at the record level before sending it on as a message.

[Jim Kosloskey]

Max,

No as I said I have not yet begun to use the 6.0 protocols.

However at the Chicago road show I had a discussion with Rob Abbott about what we expect and he indicated we would not see that currently.

My reading in the doc seems to confirm what Rob told me.

What we expect is to use one Tcl proc for all of our DB exchange; invoke a Stored Procedure with two parameters.

The first parameter is an output parameter to the Stored Procedure and is where the Stored Procedure (not SQL) informs us of success or failure within the Stored Procedure with the error code returned to the Stored Procedure and the text associated with that code.

The second parameter is an inbound parameter to the Stored procedure and contains the message in whatever format has been agreed upon (HL/7, X12, XML, HRL set, etc.).

We use the feedback from the Stored Procedure just as one would use an acknowledgment reply. We check it and if there is failure, we evaluate whether messages need to stop or can continue; and also evaluate the level of alerting needed to communicate the error.

Most of the time we stop the flow of messages and email and/or page an alert with some general text identifying site, thread, date/time, etc. as well as the code and text we received in the first parameter.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jim Kosloskey]

If anyone cares to learn more about how we do DB exchanges (and there is more) I will be presenting at the Progress Software Exchange Conference – Boston, Mass. sometime in September this year.

That presentation will not go into excruciating detail but will delve deeper than the explanation given earlier.

I will also talk about our philosophy and architecture which allows us to use the ODBC drivers with minimal maintenance and high configurability.

Our design also allows the receiving/sending system the ability to layer their exchange more into a service, isolate Cloverleaf from the actual target DB interaction, and provide the sending/receiving systems with transaction tracking.

Our current new direction is to use XML Data type for the second parameter to allow the use of the natural parsing ability of the various DB that suppor the XML Data Type rather than having to construct custom parsing inside a Stored Procedure.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Max Drown (Infor)]

Jerry, understood. 6.0.1 should provide what you need.

-- Max Drown (Infor)

[Jim Kosloskey]

Max,

I will await 6.0.1 with great interest regarding DB exchange.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jerry Tilsley]

Max,

I appreciate your replies.  One last question though, does the current version of the Database-Outbound have the ability to send a reply message on success or failure?

Thanks,

Jerry

[Peter Heggie]

I would like to see an optional ‘keep connected’ or ‘keep connected until x seconds of inactivity’ attribute for database updates, if this makes sense.

We have an outbound thread receiving ADT and orders from our HIS, and use a TCL that invokes ODBC to perform inserts at the rate of 1-2 per second. The code has a subroutine that initializes and connects, while the main routine performs the insert. The initialize and connect subroutine is called from the START mode, and is also called from the main routine if the connection is lost (just be fore the insert, another call is made to check connectivity). This maximizes performance and eliminates costly build-up and tear-down of connections for each message.

Can this be done for database protocol?

Peter Heggie

[Jerry Tilsley]

From what I see there is an option to “connect as needed” which to me indicates if this is off the connection is left open and if turned on would build a new connection per message.  Could be useful for low activity db threads.   Just my 2 cents.

[Peter Heggie]

sorry I missed the connect as needed – where is that?

Peter Heggie

[Jim Kosloskey]

I still think you need to sccomodaste the situation where the DB has disconnected.

email: jim.kosloskey@jim-kosloskey.com 29+ years Cloverleaf, 59 years IT - old fart.

[Jerry Tilsley]

On the Database-Inbound protocol the “connect as needed” option is available once you activate the “Advanced Scheduling”.  I just looked for it on the Database-Outbound but evidently that doesn’t have one.

[Max Drown (Infor)]

I’ll be out of the office next week. I’ll try to assist more when I return to work. Ping me if you don’t hear from my earlier next week.

-- Max Drown (Infor)

[Max Drown (Infor)]

Jerry Tilsley wrote:

Max,

I appreciate your replies.

-- Max Drown (Infor)

[Jerry Tilsley]

All,

Max has confirmed with development that the output parameter of a query/stored procedure does not return as a reply in the database-outbound protocol.  He is working with them to try and get this added as an ability.  Some uses for this would be to send some bits of data to the database-outbound protocol which would be used for the parameters to the query/stored procedure, in return you would receive the reply that would include the data you wanted from the query/stored procedure that you could use to create/update another message before sending it to a receiving system.  I have several items that this would be useful for in which would make things a little cleaner and provide the recovery db options to.

I will follow-up with everyone as these items progress and I learn more details.

Thanks Max for working with me on this issue.

Jerry

[Anonymous]

hi， Jerry

Is there any news about adding ability to database outbound connection to returning data from query/stored procedure? I think it’s quite meaningful to DB based query service.

Thanks and regards

[Max Drown (Infor)]

I would recommend that you all submit enhancement requests via Infor Xtreme. Please include as much detail and example use cases as you can. R&D really appreciates customer feedback.

-- Max Drown (Infor)

[Author]

Replies