Stored Procedure problem

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)

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.


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

Fred,

I’ve been looking for an answer to the same question… Since none of
the
“Lords of jRuby” have answered :wink: 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

jqm wrote:

Imagine this SP which basically gets a int as parameter an returns the
Let’s move to Rails now…in a console (jruby script/console) in my actual
but what’s the deal here? I’m not (at any moment) trying to open a
Thanks!
EDIT 2:
there is well a setAutoCommit method available in the sqljdbc , but I have
no clue how to use it… please help


View this message in context:
http://www.nabble.com/Stored-Procedure-problem-tp18628248p20113237.html
Sent from the JRuby - User mailing list archive at Nabble.com.


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

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

Mike Dietz wrote:

explains the error you’re seeing (“A result set was generated for update”)

CREATE PROCEDURE [dbo].[sp_test]
Exec sp_test 2;")

EDIT :


View this message in context:
http://www.nabble.com/Stored-Procedure-problem-tp18628248p20125712.html
Sent from the JRuby - User mailing list archive at Nabble.com.


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

On Thu, Jul 24, 2008 at 4:24 AM, jqm [email protected] wrote:

Imagine this SP which basically gets a int as parameter an returns the
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…

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

Anyone with a solution there?
this autocommit is not available with this jdbc adapter)


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

On Fri, Apr 16, 2010 at 10:12 AM, Nick S. [email protected]
wrote:

On Thu, Jul 24, 2008 at 4:24 AM, jqm [email protected] wrote:

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

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

ActiveRecord::Base.connection.execute(“EXEC dbo.SpData 1”)

How do you call the procedure?

Do you have a code snippet to share?

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

Mike Dietz wrote:

explains the error you’re seeing (“A result set was generated for update”)

CREATE PROCEDURE [dbo].[sp_test]
Exec sp_test 2;")

EDIT :


View this message in context:
http://old.nabble.com/Stored-Procedure-problem-tp18628248p28263757.html
Sent from the JRuby - User mailing list archive at Nabble.com.


To unsubscribe from this list, please visit:

http://xircles.codehaus.org/manage_email

This forum is not affiliated to the Ruby language, Ruby on Rails framework, nor any Ruby applications discussed here.

| Privacy Policy | Terms of Service | Remote Ruby Jobs