> I am having problems with a db2 stored procedure and I am
> guessing the problem is on the cf server side though I am
> not sure.
>
> The first time the stored procedure is run, it brings back
> the right result set. After the procedure is run the first
> time though, it takes the first results from the first time
> it was run, and them combines it with the next newest set
> of returned data. It continues to do this for each time that
> the procedure is ran. If I allow the cfserver to sit a
> couple of minutes, and then rerun my app, I get the following
> error message:
...
> After getting this message, then running the procedure again,
> it brings back the correct records the first time, then it goes
> back to adding the results of each subsequent running to it.
> What it seems to me is that it is caching the results somewhere
> and then adding to this cache. The stored procedure, is a Cobol
> program that is indexed by db2. The Cobol program creates a
> temporary table that the results are stored in, before passing
> them back to coldfusion. The person working on the db2 side of
> things has tested the procedure by using a batch file to return
> the results and is unable to duplicate the errors I am getting
> which leads me to believe that it is a caching problem on the
> server, but if so, don't know what to do about it?
I'm no DB2 expert (to say nothing about my COBOL skills), but I think the
problem is with DB2, not CF. Specifically, it sounds like the stored
procedure is reusing the temporary table, instead of dropping that table at
the end of the stored proc execution. That would explain why you get the
cumulative records each time it's run. Does the COBOL program explicitly
drop the table?
CF isn't caching the stored procedure recordset, either. It probably is
reusing the database connection, however, and I wouldn't be surprised if
that's why multiple queries use the same temporary table. As a test, you can
go into the CF Administrator and configure your datasource so that it
doesn't maintain the database connection. You'll have to cycle the CF server
after this to get your change to take effect.
Dave Watts, CTO, Fig Leaf Software
http://www.figleaf.com/
voice: (202) 797-5496
fax: (202) 797-5444
------------------------------------------------------------------------------------------------
Archives: http://www.mail-archive.com/[email protected]/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists or send a message
with 'unsubscribe' in the body to [EMAIL PROTECTED]
