Stored procedure not working as required

[Former Member]

Hello Everyone..

I have developed a scenario where i need to execute a stored procedure using the JDBC receiver adapter.

Below is the stored procedure that i am executing

CREATE PROCEDURE usp_select

@name char(10) ,

@company char(10)

AS

Begin

Create Table

Rahul1 (

Name varchar(10) primary key,

Company varchar(10) );

INSERT INTO Rahul1 (Name, Company)

values (@name,@company);

SELECT * from Rahul where Name = @name and Company = @company;

End;

GO

Problem is that only the first query(creating a table) gets executed and not the remaining two.

When i check this stored procedure using the query analyzer, it gets executed perfectly.

Please suggest me what can be wrong.

Thanks and Regards

Rahul Nawale

[bhavesh_kantilal]

Rahul,

<i>Problem is that only the first query(creating a table) gets executed and not the remaining two.

When i check this stored procedure using the query analyzer, it gets executed perfectly.</i>

Well, <b>the first time the Table is created and commited by the JDBC adapter</b> and when trying to create a table with the same name the next time for the next inout from the JDBC adapter, it will not be able to do so as such a table already exists.

You want a table to be created for every call to the stored procedure? Am not sure, if you have your Stored Procedure correct. Why do you want to create a atble for every query from the JDBC adapter>?

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

There are 3 queries in the stored procedure

Create

Insert

Select

I want these 3 queries to get executed when i execute the stored procedure.

Atleast for the 1st time this procedure shd work fine.

Thanks and Regards

Rahul Nawale

[bhavesh_kantilal]

Hi rahul,

It will work fine the first time only if the table has not already been created.

Try deleting the table, commiting the DB and then check for only one query by calling the Stored Porcedure from the JDBC adapter.

Hopefully it should work,

Regards,

Bhavesh

[Former Member]

Hi Bhavesh,

It is not working.

I have executed many stored procedures which include just one query and they all work fine.

My requirement is that i want to execute all the 3 queries.

Thanks and Regards

Rahul

[Former Member]

Rahul, What is the DB you are executing your query against?

If it is sybase, please check this weblog from Naveen. This weblog address a scenario when stored procedure has multiple steps and all are not executed.

/people/sap.user72/blog/2006/02/22/xi-recent-enhancements-to-jdbc-adapter

[Former Member]

I am executing my query on MSSQL...

Thanks and Regards

Rahul

[Former Member]

As per the blog, could you please set the following property in your connection string and see if it resolves your problem

IGNORE_DONE_IN_PROC=true

[Former Member]

The problem is still not resolved..

connection string is

jdbc:microsoft:sqlserver://INLD50046976A.dhcp.blrl.sap.corp:1433;user=rahul;password=rahul123;databaseName=Test_Rahul;SelectMethod=Cursor;IGNORE_DONE_IN_PROC=true

Thanks and Regards

Rahul