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.
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.
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.
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.
"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.
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.
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
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.
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.
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.
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.
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.
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.
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)