SQLException using execSQL

, ,
1

We are using execSQL service of B2B server to access TN Translation Tables from Oracle. We are using Oracle JDBC driver. It throws an SQL Exception: Connection closed/Null PointerException for an incoming document from a partner. However when we submit the same document from TN again, it just goes through. We are not able to regenerate the error.

Moreove, there is another application accessing TN db thru Sequelink driver… it works fine.

N/w issues are not there as the webMethods server/TN and db are on the same machine.
We are using execSQL service multiple times in the application. Error occurs at least at 2 places

All we can think of is as reasons for hte db connection error are:

  • Is there any know issues with webMethods builtin java service execSQL?
  • Are there any steps to be performed while useing execSQL? I mean, do we need to specifically destroy/create some variables in the pipeline after accessing the db ( like results)?
  • Is this anything to do with connection pooling? we have nto enabled connection pooling for Oracle JDBC Server.

Valuable Suggestions are appreciated.

Thanks and Regards,

2

another thing I need to mention here is that we are using directly execSQL service without any preceding and succeding services like connect and close etc…

Is this necessary to first connect the dateabse and then close it afterwards with execSQL service. We assumed that execSQL handles open and close automatically.

3

Manish,

If you can maintain the connection pooling in your flow that will be best practice before and after invoking the pub.db.execSQL service.

Thanks

4

A few notes:

  1. execSQL will open a connection but never close it.
  2. You may want to do a close anytime you run an execSQL so you do not leave open connections around.
    3)If you plan on running multiple execSQL’s close you may want to open a connection to the DB, pass the DBConnection to the execSQL, and close the connection once your execSQL’s are completed.
5

Also pls check the Oracle database side and Tns files if any max and min connection pooling errors in the logs which might cause your connection problems from IS to Oracle and viceversa.

6

There ARE issues with the execSQL throwing null pointer exception (look at the message archives as well) which has been apparently addressed with 4.6SP2 and I assume 6.0.1 . We had the problem at random points (sometimes failed, sometimes worked) and when we invoked the process from a scheduled event mostly.

Will

7

thanks for your quick suggestions.

we can try connection pooling but will keep it as the last option… as it needs bouncing the server…

also, execSQL does not pass DBConnection object to the pipeline indicating it might be closing the connection after the execution.

another peculiar pattern i saw in using the execSQL service is that at all other places where we are using execSQL service with error handling it is working fine. But only in this place where the error handling is not done, it behaves unpredictably.

any suggestions on this line of thought.