Forum: JRuby Stored Procedure problem

A651f36e82176a7e5c875c3906c5f0c2?d=identicon&s=25 Frederic Jacquemin (jqm)
on 2008-07-24 11:27
(Received via mailing list)
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-tp1...
Sent from the JRuby - User mailing list archive at Nabble.com.


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

    http://xircles.codehaus.org/manage_email
Fb9e48795ad706d5638ed00e7b3c9226?d=identicon&s=25 Mike Dietz (Guest)
on 2008-10-22 17:27
(Received via mailing list)
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


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-tp1...
Sent from the JRuby - User mailing list archive at Nabble.com.


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

    http://xircles.codehaus.org/manage_email
A651f36e82176a7e5c875c3906c5f0c2?d=identicon&s=25 Frederic Jacquemin (jqm)
on 2008-10-23 09:26
(Received via mailing list)
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-tp1...
Sent from the JRuby - User mailing list archive at Nabble.com.


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

    http://xircles.codehaus.org/manage_email
642c81a3cf3c02ce22e5989cb7487772?d=identicon&s=25 PrasadH (Guest)
on 2010-04-16 09:08
(Received via mailing list)
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-tp1...
Sent from the JRuby - User mailing list archive at Nabble.com.


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

    http://xircles.codehaus.org/manage_email
526d60de6472502bb570a9df2842b33b?d=identicon&s=25 Nick Sieger (Guest)
on 2010-04-16 17:13
(Received via mailing list)
On Thu, Jul 24, 2008 at 4:24 AM, jqm <jqm@synergy.be> 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
526d60de6472502bb570a9df2842b33b?d=identicon&s=25 Nick Sieger (Guest)
on 2010-04-16 21:01
(Received via mailing list)
On Fri, Apr 16, 2010 at 10:12 AM, Nick Sieger <nicksieger@gmail.com>
wrote:
> On Thu, Jul 24, 2008 at 4:24 AM, jqm <jqm@synergy.be> 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
9b6bb7b5e5693e870cb1fd20b3721b69?d=identicon&s=25 Prajapati Viral (prajapati_v)
on 2014-07-17 13:32
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")
2c0c4cf3ccc8da22f7c3b9586ce1cd70?d=identicon&s=25 Christian MICHON (Guest)
on 2014-07-17 13:39
(Received via mailing list)
How do you call the procedure?

Do you have a code snippet to share?
Please log in before posting. Registration is free and takes only a minute.
Existing account

NEW: Do you have a Google/GoogleMail, Yahoo or Facebook account? No registration required!
Log in with Google account | Log in with Yahoo account | Log in with Facebook account
No account? Register here.