FreeTDS Development Group

["Anton van Straaten" <anton AT appsolutions.com>]

Bruce R. Lewis wrote:
> You're going against 6.5 and still aren't in the habit of putting "set
> nocount on" at the beginning of every stored procedure?  I can hardly
> get anything to work without it.

Thanks.  I should have mentioned in my initial report that the "set nocount
on" statement was removed to achieve a minimal test case, because it has no
effect on this problem.  The real procedures that this test is based on all
contain that as their first statement.

Just to be sure, I added nocount statements to my copy of the unit test
which Andreas put in the jtds CVS (freetds.AsTest), and tried all four
possible combinations of "nocount on" statements in the two procedures, but
none of them change the result of the test.

I am still just speculating, but based on what Andreas has said about stored
procedures working like batches, it seems to me as though calling "execute"
in any form that doesn't return a resultset to the client - like
"insert...execute" - still puts something into the protocol stream to the
client, which the FreeTDS JDBC driver doesn't recognize.  I've even tried
using a macro in the inner stored procedure, spTestExec2, to insert records
into a table whose name is passed through as a parameter:

        execute ('insert ' + @table + ' select...etc...')

Again, presumbably because the EXECUTE doesn't return a resultset, this
fails in exactly the same way.

Failing some easy SQL rearrangement which can realistically be performed on
the actual stored procedures, I guess the next step would be to analyze the
protocol stream to see what's being sent in these cases, and figure out how
to reliably skip the markers that are being inserted.  Since there are time
constraints, as always, my client is probably going to give up and use a
commercial, closed source JDBC driver for this project until they can
upgrade their database.

Anton