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

asked 06 Dec '12, 17:11

warish's gravatar image

warish
1111
accept rate: 0%

edited 15 Mar '13, 19:44

Mark%20Culp's gravatar image

Mark Culp
24.9k10139297


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.

permanent link

answered 06 Dec '12, 23:45

Liam's gravatar image

Liam
36191118
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×125
×86
×39
×12

question asked: 06 Dec '12, 17:11

question was seen: 3,746 times

last updated: 15 Mar '13, 19:44