JDBC Adapter results to Flatfile

We are using JDBC adapter with dynamic query option. The results of the query are huge (more than 2 lakh records). We save the results to a specified file on the hard disk.

We are looping through the records and appending to a string variable, which we are writing to a file. However looping through the huge list of records is terminating the session of the developer. Is there a way to spool the results of the JDBC adapter service results to a file on to hard disk directly such as the SPOOL command in SQL.

Request help in this regard
Thanks in advance
Sudha

Hi, i dont know about spooling, but i cant understand why you want the developer to be open when u are running the service.
If you want you can either schedule the service or simply invoke it from the webmethods administrators console, package management page.
This way u can execute the service and at the same time continue doing other work in Developer.

If this helps then let me know. Till then i will try to find another solution for ur problem.

Regards,
Ramnish.

Hi Ramnish,

Thanks a lot for the reply.
More than the expiration of the session, we are looking for a solution where we can avoid looping through the results from the adapter as this could actually lead to memory problems when scheduled as in the debug mode itself, while testing this is closing the session - we are still in the testing stage.

It would be of great help if you could suggest a way to get to write the results to a flat file without looping through the results array.

Thanks and regards,
Sudha.

Hi Sudha, here are a couple of solutions to your problem:-

1:- you can write a “Stored Procedure” which will return you the data directly in a String Format and then you simply write the data to a file in your Integration Server. Call this Stored Procedure from Integration Server.

2:- Or instead of Adapter Service , you can write a Java Service which will make the Database call and will give you back a result set. Then in the Java Service itself you Loop over the Result Set to write the each row to the required file. Executing the Java Service will be more faster than Adapter Service which will save some time.

I dont know whether you have time to try this out, but i guess either of the 2 solutions will be more efficient than trying by Adapter Service.

Let me know if this helps. Also in mean time i will think of some other solution.

Regards,
Ramnish.

Hi Sudha,

Did you find out the solution??, I am also running on same senariou, but here my record count is huge, its about 54 Million record count.

Please help me on this.

Thanks & Regards,
Shami