Execute statement in stored procedure does not work in JDBC app any more

[Former Member]

I have a stored procedure named sp_WS_PODemandFulfillment it use to work from a java app using JDBC and dbisql using ODBC. The db was recreated via a third party software companies tools and now the stored procedure does not work with the java app with JDBC but still works with dbisql. I have narrowed it down to a EXECUTE IMMEDIATE statement. Here is the code:

sp_WS_PODemandFulfillment Code causing problem:

EXECUTE @status = sp_ExecuteImmediate @ExecString

sp_ExecuteImmediate Code:

ALTER PROCEDURE "somecompany"."sp_ExecuteImmediate"(in p_ExecuteString varchar(8192))
begin
  execute immediate p_ExecuteString
end

@status is an int variable and returns -6 when the java JDBC app fails. I have not been able to find out what -6 means.

p_ExecuteString = "insert into #RawReorderList ..."

RawReorderList is a temp table created in the original stored procedure.

Remember, The stored procedure still works fine in dbisql called using the same syntax as the JDBC app is using and called from the same server where the Java app resides. There are no parameter replacements going on in the JDBC call. The JDBC driver is Jconn2.jar and the SQL Anywhere version is 10.

And No, I can not go to the third party software company for support, they do not support the java app that is having the problem.

My best guess is that a setting got changed on the db by the software cos recreate db tools.

Any help with this issue will be greatly appreciated, especially what the return code -6 means.

Thanks,

Warren

[Former Member]

Dear Warren

I recall having had a similar problem a few years back. It's definitely to do with JConnect2. ( A looked up the old Change Request: "Another iteration of CR8857, but this time it is compatible with JAVA's jConnect JDBC drivers too!" )

Try this:

create or replace function sp_ExecuteImmediate(aString char(200)) returns char(200) begin declare @TEMP_VALUE numeric(18,6); execute immediate(aString); select @@rowcount into @TEMP_VALUE; return @TEMP_VALUE; end;

and then, to use it: select sp_ExecuteImmediate('insert into DEMO VALUES (1,''TEST'')')

For the life of me, I can't locate the root-cause as we've subsequently changed our version and change control systems, so the detail is lost. But if I do find the original explanation, I'll post it here.