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)
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
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”)
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)
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”)
here’s what I’ve found.