await reply and resend if necessary for database protocol

[Lawrence Williams]

I configured a database protocol outbound thread that does a simply INSERT into an Oracle database.  I currently have it setup to not Await replies, but I need to configure it to wait for a reply and queue messages if necessary if the Oracle database connection is unavailable for any reason.  

Is there a Cloverleaf provided tclproc I can reference as a template ?  

Am I overthinking this….will Timeout and Timeout Handling (with Resend OB message) handle this appropriately for an outbound db connection ?

Topic

[Jim Kosloskey]

This is a general response and not specifically related to the DB Protocol.

I suspect the only response you might receive is something equivalent to a SQL_SUCCESS.

My understanding is all that will tell you is the Insert command (in this case) was successfully executed. As far as I know it does NOT return any of the SQL errors which could occur if the Insert itself fails for any reason (including the DB not responding).

Personally I would only consider interacting with a DB through a Stored Procedure. I would require that Stored Procedure to return me (via a parameter) a success/fail code where the code in case of fail is the actual SQL code returned to the SP. I would also expect the SP to get the text associated with the code and return that as well. Now I can at least alert everyone needed of a failure and stop the flow of messages if needed.

I have heard the most current release of CL has improved the interaction of the DB Protocol with SP. If indeed that is true and exchange can be made with the SP such that it can communicate back success/fail with text then that is the way I would go.

You would need some Tcl I would think to interrogate the return parameter and take whatever actions are needed.

I have a lot of other ideas (which I used successfully with the Data Direct Driver add-on) as to how a integration with a DB should be done. If you would like to discuss those ideas, feel free to email me.

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

[Jim Kosloskey]

This is a general response and not specifically related to the DB Protocol.

I suspect the only response you might receive is something equivalent to a SQL_SUCCESS.

My understanding is all that will tell you is the Insert command (in this case) was successfully executed. As far as I know it does NOT return any of the SQL errors which could occur if the Insert itself fails for any reason (including the DB not responding).

Personally I would only consider interacting with a DB through a Stored Procedure. I would require that Stored Procedure to return me (via a parameter) a success/fail code where the code in case of fail is the actual SQL code returned to the SP. I would also expect the SP to get the text associated with the code and return that as well. Now I can at least alert everyone needed of a failure and stop the flow of messages if needed.

I have heard the most current release of CL has improved the interaction of the DB Protocol with SP. If indeed that is true and exchange can be made with the SP such that it can communicate back success/fail with text then that is the way I would go.

You would need some Tcl I would think to interrogate the return parameter and take whatever actions are needed.

I have a lot of other ideas (which I used successfully with the Data Direct Driver add-on) as to how a integration with a DB should be done. If you would like to discuss those ideas, feel free to email me.

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

[Author]

Replies