Alex,
What type of DB (Oracle, SQL Server, etc)?
Do you plan on connecting and disconnecting the DB with each message or once at startup?
If you are using an Xlate you will need an Xltp type proc which will acquire the resources, Connect to the DB (if connecting/disconnecting with each message) prototype the Stored Procedure Call, invoke the Stored Procedure, check for valid completion of the Stored Procedure, then release resources and disconnect (if connecting and disconnecting with each message).
I prefer the use of Stored Procedures as you are doing but there are some caveats.
When you invoke the Stored Procedure you will get an SQL return. Getting and SQL_SUCCESS here does NOT mean the Stored Procedure completed succesfully it only means the Stored Procedure was sucessfully sttarted by the DB.
I prefer to use 3 parameters with Stored Procedures. One parameter is an inbound to the SP which is the field value you want, one of the parameters is a return code (outbound) from the SP indicating if it detected any issues while executing (more on that later), the last parameter is for the value you want from the SP (assuming a single value is to be returned).
In order for the Stored Procedure to return the return code it will need to do ‘try’ on all of its pertinent commands catching the return code and getting any text associated with that code should it not be a successful return. Then the SP needs to populate the parameter with the code received and the text associated with it.
This is so your proc can know if the SP worked and if it dod not have information to ‘externalize’ (send to humanoinds to understand what happened).
A big decision is what do you do if the SP has an iissue? You have a message in flight inside the Xlate. Do you just keep translating, error the message to the Error DB, use a default value (maybe COPY the inbound value to the outbound)? How do you let the receiving systeem and anyone else important know there was an issue. You have the information so you could email it, etc.
One more thing to keep in mind. Thiis may be changed on later versions of SQL Server but the parameters you want to get back from the SP won’t be usable until you clearr the recordset. I know in the design laid out above there is no mention of a recordset but it turns out SQL Server always builds a recordset and it needs to be cleared before the parameters from the SP are available to your proc.
For connectivity I like to use connect time parameters to modify the default ODBC.ini file entry rather than modifying the ODBC.ini file. I keep thos Connect Strings in a Cloverleaf Lookup Table – easy to maintain and refresh. Using Conectt Strings among other things assures the ODBC.ini file does not get hosed up.
So in your Xlatee, transform and when you ge to the field in question, invoke your proc (either via COPY or CALL). The result of that proc then would be placed in whatever field, component, sub-component you want. Finish your transform and move on.
There is probably more but that is what I recall off the top of my head.
email: jim.kosloskey@jim-kosloskey.com 30+ years Cloverleaf, 60 years IT – old fart.
.
