[jruby-user] Stored Procedure problem

[jqm]

Hello Lords of jRuby,

I want to submit something nice :

I'm currently trying to use MS SQL Server 2005 and rails 2.1 to play
together nicely (using jRuby 1.1.3 and the lastest sqljdbc.jar). So far I've
been quite confident I wouldn't have any problem until I hit those 'stored
procedures'.

Imagine this SP which basically gets a int as parameter an returns the
double of its value (basic, right?).
---------------------
CREATE PROCEDURE [dbo].[sp_test]
@myParam int
AS
select @myParam*2;
---------------------
This works perfectly in a SQL Management Studio : Exec sp_test 2; ==> gives
4.. Great!

Let's move to Rails now...in a console (jruby script/console) in my actual
project.
doing this : puts ActiveRecord.connection.select_value("Use MyDevDB; Exec
sp_test 2;")

AR returns an error: ActiveRecord::StatementInvalid:
ActiveRecord::ActiveRecordError: A result set was generated for update.

I tried connection.execute, connect.select_all but they returns same
error...

but what's the deal here? I'm not (at any moment) trying to open a recordset
for any update, it's a dawn simple Stored Procedure giving me back a simple
integer!

I've been looking on all SQL forums, on MSDN web site, etc. it seems this
error message comes from the SQL Server itself , but i guess that at any
moment AR is not giving right infos to SQL who believes I try to open this
recordset for update, right?

Anyone with a solution there?

Thanks!
Fred.

EDIT :
I just tried same situation using ruby...
this works with
"ActiveRecord::Base.connection.instance_variable_get("@connection")["AutoCommit"]
= false"
so wtf isn't that correctly working with jRuby and sqljdbc.jar??? (rem :
this autocommit is not available with this jdbc adapter)

[Mike Dietz]

Fred,

I've been looking for an answer to the same question... Since none of the
"Lords of jRuby" have answered ;-) here's what I've found.

It looks like the "_execute" method in jdbc_mssql.rb wasn't set up to handle
stored procedures. That method looks for sql keywords (insert, select,
etc), and falls through to the "update" case if it doesn't find any of the
explicitly listed keywords (and "exec" is not listed). That explains the
error you're seeing ("A result set was generated for update") -- the code is
expecting "update" sql, which shouldn't produce a result set, but your
stored proc is returning one.

If you're still looking for a workaround, this seems to work:

connection.raw_connection.execute_query(sql) # where sql is the
stored proc "exec"

There's probably a better way to do that (besides fixing the jdbc-related
code), so if anyone has suggestions, it would be appreciated...

Hope this helps.

Take care,

Mike

Post by jqm
Hello Lords of jRuby,
I'm currently trying to use MS SQL Server 2005 and rails 2.1 to play
together nicely (using jRuby 1.1.3 and the lastest sqljdbc.jar). So far
I've been quite confident I wouldn't have any problem until I hit those
'stored procedures'.
Imagine this SP which basically gets a int as parameter an returns the
double of its value (basic, right?).
---------------------
CREATE PROCEDURE [dbo].[sp_test]
@myParam int
AS
---------------------
This works perfectly in a SQL Management Studio : Exec sp_test 2; ==>
gives 4.. Great!
Let's move to Rails now...in a console (jruby script/console) in my actual
project.
doing this : puts ActiveRecord.connection.select_value("Use MyDevDB; Exec
sp_test 2;")
ActiveRecord::ActiveRecordError: A result set was generated for update.
I tried connection.execute, connect.select_all but they returns same
error...
but what's the deal here? I'm not (at any moment) trying to open a
recordset for any update, it's a dawn simple Stored Procedure giving me
back a simple integer!
I've been looking on all SQL forums, on MSDN web site, etc. it seems this
error message comes from the SQL Server itself , but i guess that at any
moment AR is not giving right infos to SQL who believes I try to open this
recordset for update, right?
Anyone with a solution there?
Thanks!
Fred.
I just tried same situation using ruby...
this works with
= false"
this autocommit is not available with this jdbc adapter)
there is well a setAutoCommit method available in the sqljdbc , but I have
no clue how to use it... please help

[jqm]

Thanks a lot Mike for this explanation!
I switched back to Ruby for this project and had the work done.
Anyway, I appreciate your help allowing me to have a better understanding of
this error.

Hopes this will help other people around the place.
I'll keep this post in mind for my next project.

For curiosity: did you get back to jRuby sources to point out this issue?
can you tell me where (in which file?)

Thanks again for your help and for your time,

Fred

Post by Mike Dietz
Fred,
I've been looking for an answer to the same question... Since none of the
"Lords of jRuby" have answered ;-) here's what I've found.
It looks like the "_execute" method in jdbc_mssql.rb wasn't set up to
handle stored procedures. That method looks for sql keywords (insert,
select, etc), and falls through to the "update" case if it doesn't find
any of the explicitly listed keywords (and "exec" is not listed). That
explains the error you're seeing ("A result set was generated for update")
-- the code is expecting "update" sql, which shouldn't produce a result
set, but your stored proc is returning one.
connection.raw_connection.execute_query(sql) # where sql is the
stored proc "exec"
There's probably a better way to do that (besides fixing the jdbc-related
code), so if anyone has suggestions, it would be appreciated...
Hope this helps.
Take care,
Mike

Post by jqm
Hello Lords of jRuby,
I'm currently trying to use MS SQL Server 2005 and rails 2.1 to play
together nicely (using jRuby 1.1.3 and the lastest sqljdbc.jar). So far
I've been quite confident I wouldn't have any problem until I hit those
'stored procedures'.
Imagine this SP which basically gets a int as parameter an returns the
double of its value (basic, right?).
---------------------
CREATE PROCEDURE [dbo].[sp_test]
@myParam int
AS
---------------------
This works perfectly in a SQL Management Studio : Exec sp_test 2; ==>
gives 4.. Great!
Let's move to Rails now...in a console (jruby script/console) in my
actual project.
doing this : puts ActiveRecord.connection.select_value("Use MyDevDB;
Exec sp_test 2;")
ActiveRecord::ActiveRecordError: A result set was generated for update.
I tried connection.execute, connect.select_all but they returns same
error...
but what's the deal here? I'm not (at any moment) trying to open a
recordset for any update, it's a dawn simple Stored Procedure giving me
back a simple integer!
I've been looking on all SQL forums, on MSDN web site, etc. it seems
this error message comes from the SQL Server itself , but i guess that at
any moment AR is not giving right infos to SQL who believes I try to open
this recordset for update, right?
Anyone with a solution there?
Thanks!
Fred.
I just tried same situation using ruby...
this works with
= false"
this autocommit is not available with this jdbc adapter)
there is well a setAutoCommit method available in the sqljdbc , but I
have no clue how to use it... please help

[PrasadH]

Hi,
Sure, you are right. The last select statement value will be returned.

But if the stored procedure is having OUTPUT parameter defined, how can I
catch the value returned. If I use the method
‘ActiveRecord::Base.connection.raw_connection.execute_query(stored_procedure)
I get the error message saying ‘Procedure or Function ‘XXXXX’ expects
parameter ‘@aaaa’, which was not supplied. (Here XXXXX is the stored
procedure name and @aaaa is OUTPUT variable defined inside stored procedure)
Any help is greatly appreciated.

Thanks and regards

PRasad Hande

Post by Mike Dietz
Fred,
I've been looking for an answer to the same question... Since none of the
"Lords of jRuby" have answered ;-) here's what I've found.
It looks like the "_execute" method in jdbc_mssql.rb wasn't set up to
handle stored procedures. That method looks for sql keywords (insert,
select, etc), and falls through to the "update" case if it doesn't find
any of the explicitly listed keywords (and "exec" is not listed). That
explains the error you're seeing ("A result set was generated for update")
-- the code is expecting "update" sql, which shouldn't produce a result
set, but your stored proc is returning one.
connection.raw_connection.execute_query(sql) # where sql is the
stored proc "exec"
There's probably a better way to do that (besides fixing the jdbc-related
code), so if anyone has suggestions, it would be appreciated...
Hope this helps.
Take care,
Mike

Post by jqm
Hello Lords of jRuby,
I'm currently trying to use MS SQL Server 2005 and rails 2.1 to play
together nicely (using jRuby 1.1.3 and the lastest sqljdbc.jar). So far
I've been quite confident I wouldn't have any problem until I hit those
'stored procedures'.
Imagine this SP which basically gets a int as parameter an returns the
double of its value (basic, right?).
---------------------
CREATE PROCEDURE [dbo].[sp_test]
@myParam int
AS
---------------------
This works perfectly in a SQL Management Studio : Exec sp_test 2; ==>
gives 4.. Great!
Let's move to Rails now...in a console (jruby script/console) in my
actual project.
doing this : puts ActiveRecord.connection.select_value("Use MyDevDB;
Exec sp_test 2;")
ActiveRecord::ActiveRecordError: A result set was generated for update.
I tried connection.execute, connect.select_all but they returns same
error...
but what's the deal here? I'm not (at any moment) trying to open a
recordset for any update, it's a dawn simple Stored Procedure giving me
back a simple integer!
I've been looking on all SQL forums, on MSDN web site, etc. it seems
this error message comes from the SQL Server itself , but i guess that at
any moment AR is not giving right infos to SQL who believes I try to open
this recordset for update, right?
Anyone with a solution there?
Thanks!
Fred.
I just tried same situation using ruby...
this works with
= false"
this autocommit is not available with this jdbc adapter)
there is well a setAutoCommit method available in the sqljdbc , but I
have no clue how to use it... please help

[Nick Sieger]

Post by jqm
Hello Lords of jRuby,
I'm currently trying to use MS SQL Server 2005 and rails 2.1 to play
together nicely (using jRuby 1.1.3 and the lastest sqljdbc.jar). So far I've
been quite confident I wouldn't have any problem until I hit those 'stored
procedures'.
Imagine this SP which basically gets a int as parameter an returns the
double of its value (basic, right?).
---------------------
CREATE PROCEDURE [dbo].[sp_test]
AS
---------------------
This works perfectly in a SQL Management Studio :  Exec sp_test 2; ==> gives
4.. Great!
Let's move to Rails now...in a console (jruby script/console) in my actual
project.
doing this : puts ActiveRecord.connection.select_value("Use MyDevDB;  Exec
sp_test 2;")
ActiveRecord::ActiveRecordError: A result set was generated for update.
I tried connection.execute, connect.select_all but they returns same
error...

Sorry about this. Mike's response is correct. Although in the most
recent version of ar-jdbc (0.9.3), #select_* will always pass through
to the JDBC executeQuery, which should work better for you. So if you
can try 0.9.3 and your above storedproc query should hopefully fare
better.

/Nick

Post by jqm
but what's the deal here? I'm not (at any moment) trying to open a recordset
for any update, it's a dawn simple Stored Procedure giving me back a simple
integer!
I've been looking on all SQL forums, on MSDN web site, etc.  it seems this
error message comes from the SQL Server itself , but i guess that at any
moment AR is not giving right infos to SQL who believes I try to open this
recordset for update, right?
Anyone with a solution there?
Thanks!
Fred.
I just tried same situation using ruby...
this works with
= false"
this autocommit is not available with this jdbc adapter)
--
View this message in context: http://www.nabble.com/Stored-Procedure-problem-tp18628248p18628248.html
Sent from the JRuby - User mailing list archive at Nabble.com.
---------------------------------------------------------------------
   http://xircles.codehaus.org/manage_email

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Nick Sieger]

Post by Nick Sieger

Post by jqm
ActiveRecord::ActiveRecordError: A result set was generated for update.
I tried connection.execute, connect.select_all but they returns same
error...

Sorry about this. Mike's response is correct. Although in the most
recent version of ar-jdbc (0.9.3), #select_* will always pass through
to the JDBC executeQuery, which should work better for you. So if you
can try 0.9.3 and your above storedproc query should hopefully fare
better.
/Nick

Sorry to respond to my own reply but I just realized I referenced
0.9.3 when in fact the latest release is 0.9.4. So try 0.9.4 if you
can.

/Nick

---------------------------------------------------------------------
To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

[Prajapati Viral]

i have store procedure in sql server but when i call it it return -1
insead of record

[Christian MICHON]

How do you call the procedure?

Do you have a code snippet to share?

Post by Prajapati Viral
i have store procedure in sql server but when i call it it return -1
insead of record
--
Posted via http://www.ruby-forum.com/.
---------------------------------------------------------------------
http://xircles.codehaus.org/manage_email