Hi all,
I am using services in WmDB package to update around 1400 records to DB. I am using the same connection object to do this and somewhere in between its throwing a null pointer exception. The execSQL service is throwing this exception. I am using “connect” and then “execSQL” in a loop and then “disconnect”. Please let me know if I have to set any DBConnection object timeout parameter. My guess is that the connection is timing out and hence the execSQL is throwing an error. By the way, I am not using any pooling. Please give your valuable suggestions.

Thanks,
Hari

Hi Hari,

If you are sure it is because of timeout, you can set this information when you define the database alias. You have an option called ‘Expiration Time (ms)’ as a part of database configuration. Change this and check if this solves your problem.

-Rajesh Rao

Ji Rajesh,
I went through the documentation and found that the “'Expiration Time (ms)” is valid only when we are using connection pooling. I did not enable connection pooling in my case, hence I don’t think it will work.
Let me know if there might be any other reason for that null pointer exception. its being throw by execSQL service.

thanks,
hari

Hi Hari,

   You call connect service before loop.It creates connection object 

before loop.Call close service (disconnect) after loop.If you are updating than call startTransaction after connect service.After loop
call clearTransaction,Commit,close.Dont drop connection object in main flow.Call clearTransaction,rollOut,Close in catch block also.If
Transaction fails it jumps to catch block do the rollOut,Clear,Close.
Make sure that you have to take Connection object in execSQl
service.Don’t drop connection object here also.

Thanks,
SriniK

Hi Hari,

 "Make sure that you have to take Connection object in execSQl  

service.Don’t drop connection object here also".This means If you
created execSQL then invoking this service into your main flow
take $dbConnection object as input parameter.Earlier in your post
you mention you are calling connect service inside loop.It opens
connection objects,later you are calling execSQL then close.It loops
1400 times.It has to hit to open 1400 connection objects,1400 connection close objects.I think in wmDB has bug some times it
not closes database connection properly.

SriniK

Hi Srini,
thanks for the reply. I am not using connect in a loop. I am using connect once and then execSQL in a loop and then disconnect.
I will try doing this in a transaction and let u know the result.

Thanks,
Hari

Hari,

   Make sure all 1400 records you are passing to exSql service  

to update has values.It(NullPointerException) seems to database connection problem.It is data problem.Suppose if you are updating
record with null value or if you update null value to updated record has null value.Make sure it.Generally for updates,SP,inserts we use strartTransaction service.That i have mention in my earlier post.
I think you will aware of this

Thanks,
sriniK

Hi srini,
thanks for the comments. I am sure that I am passing valid SQL strings to execSQL service and none of those strings is null. as far as I know, If the SQL string is not valid then DB should throw exception, not execSQL. and if the SQL string is null then execSQL throws an exception saying that SQL statement is null.
I wrote a custom java service to do the same and it seems to work fine for more that 50000 records. But i would prefer using execSQL if it works. also, let me know how to turn of audit logging on execSQL service. I don’t want to turn of audit logging completely. Its consuming a lot of time in my case.

Thanks,
Hari

Hi Hari,

   If you are using 6.0 version there is a Audit button in developer 

Clack on that button you have to make settings.If you want to refer
information for settings pl refer 7th chapter page 130 onwards.There lot of description for flat file,database Audit log settings.

Thanks,
SriniK

hi srini,
thanks for the information.

hari.

hi srini,
thanks for the information.

hari.

Hi Hari,

I am also facing the same problem.

Could you let me know how did you go about solving this.

Any suggestion is highly appreciated.

Thanks in advance,
Neelima

hi neelima,
I couldn’t really find a solution to this problem. I wrote my own java services to connect to database and update records. that aproach worked fine for me. The error that i used to get is a null pointer exception from execSQL service. and it usually occurs only when the same connection object is being used for a long time, say to insert/update more that 20,000(approx) records. let me know if you find any solution to this.

thanks,
hari.

It could be that webMethods is finding an invalid JDBC connection when tries to execute the SQL (This could be a JDBC session timeout or a firewall closing the database TCP connection)

I haven’t used it myself, but WM support told me about this directive you can put in server.cnf to ensure a JDBC connection is actually valid before executing an SQL over it. Here are the details:
> > e.g. watt.server.db.testSQL=select * from dual
> > where “select * from dual” can be replaced by any sql statement
> > that’ll execute successfully if the connection was active.

So just set this directive to any small SQL statement that you know always succeeds. When it finds an invalid connection, this directive will cause a new connection to be setup and used instead.

With regards,
Sonam Chauhan

Hi Sonam,
Thanks a lot for the information. That would solve some of my problems. One other problem that i have is regarding the usage of temporary tables. I connect to database using “connect” method and I use connection object to craete a temp table and update it and retrieve results from it. During this process, if the connection is lost then i would lose all the data in my temporary tables too. Using the approach u mentioned, we are sure that a valid connection object always exists before excuting queries but in my case, i might lose temporary data when connection object changes. please let me know your thots on this. I guess I have to change my approach of creating temporary tables.

Thanks a lot,
hari

Sorry Hari - I don’t know how temp tables will be handled. WM support should be in a position to tell you what will happen. Else, you will have to stress test your application and find out.

Hi Sonam,

When I try to append this key in server.cnf,it gets appended as

watt.server.db.testSQL=select\ *\ from\ dual

I do not know from where does this (backslash) come from.

Any suggestion is highly appreciated.

Regards,
Neelima

This looks like some kind of an “escape” sequence for space character

Sorry Neelima - I have no first hand experience with this directive. If you haven’t done so already, try directly editing server.cnf (on disk). I am told this directive works too:
watt.server.db.testSQL=select 1 from dual
(may not need the escaping that “select *” needs)

> looks like some kind of an “escape” sequence for space character

Hmm, you’re right Vikram. IIRC server.cnf does that, so it should be working Neelima.

Hi Sonam,

Is it necessary for me to go for Connection pooiling if I need to use the directive you have proposed.

Neelima

I am not sure Neelima - that directive should work with or without Connection pooling.

This problem does not affect either of the two types of database connections I use at work:

1. TN<–>Merant<–>Oracle
   This does not seem at all affected by firewall timeouts.

2. IS<–>Oracle
   This connection isn’t affected either.
   I assume that is because this connection is low volume
   and the flow open and close a new connection for each request.

Other applications in the same network have been affected.

Neelima/Sonam/Srini,
We are experiencing a new problem with our database connections. We are using connection pooling and we have ‘watt.server.db.testSQL’ directive specified in server.cnf. Once we enabled connection pooling and enabled the directive mentioned before, our null pointer exceptions were gone and all the SQL services started running fine. The new problem that we are facing is that after 1 or 2 hours of continuous testing, none of the services are able to get connections. The number of active connections/available connections (using wm.server.db.getConnectionInfo method) is always greater than one. number of active connections are not even close to max pool size.
We double checked to make sure that we are explicitly closing the connections in our code.
To describe about the services, we open the connection using connect first, then we have try catch sequence blocks, in the try block we do some processing which might take more than 1 or 2 minutes in some cases and there might be some execSQL statements in between. once all the processing is done we close the connection. we also use close method in the catch block if dbConnection object is not null.

during processing, there are chances that firewall might timeout our initial connection object. but i guess since we are using watt.server.db.testSQL directive we get a new connection in our try block. what do u think might happen to the connection that was dropped by the firewall? Would IS return the connection to the pool?

any thots on this will be very helpful

Thanks in advance
Hari

Hari

<my2cents>

Maybe not very relevant to your problem, but I wish to point out two things that you should try experimenting

1. we had noticed that connect and close connection when used with execSql made it very slow. when the connect and close connection was removed the execution time dropped to fraction of what it took before.

2. If its inserting 10000’s of records that you do in a loop it can be done without a loop. You just need to create a document list that exactly matches the table structure with all the records and just call pub.db:insert once to insert all the records. I had only tried “inserting” using this technic and am not sure if it would work with other things as well.

</my2cents>

Thahir

Hari - If you are using connection pooling, you need to be very careful. Every time you use wm.db:call or pub.db:execSQL you must make an explicit call to pub.db:call. If you do not the connection will never be made availible to the DB Pool. Hence, you will eventually run out of connection in the pool, despite what your active connections say. You may want to check all code that is running on that server to make sure you are closing all connection. Even on failures.