Skip to Content.
Sympa Menu

freetds - Re: [freetds] stored procedure call with OUTPUT params not working using ruby-odbc/unixodbc/freetds/mssql

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Frediano Ziglio <freddy77 AT gmail.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] stored procedure call with OUTPUT params not working using ruby-odbc/unixodbc/freetds/mssql
  • Date: Mon, 23 Mar 2009 21:34:17 +0100

Il giorno lun, 23/03/2009 alle 16.16 -0400, Chris Hall ha scritto:
> I have a test SP I am trying to run on SQLServerExpress:
>
> # CREATE PROCEDURE dbo.up_TestSP4(@NotUsed INT, @ReturnVar INT OUTPUT)
> # AS
> # SET @ReturnVar = 666
> # /*
> # DECLARE @P2 INT
> # EXEC dbo.up_TestSP4 10, @P2 OUTPUT
> # SELECT @P2
> # */
>
> works in tsql no problems.
>
> ruby script (per example found at
> http://www.ch-werner.de/rubyodbc/odbc.html#stmt_param_iotype):
>
> begin
> conn = ODBC::Database.connect("DSN", "xxx", "xxx")
> stmt = conn.prepare("exec dbo.up_TestSP4 ?, ?")

I think here the call syntax would help, try

stmt = conn.prepare("{call dbo.up_TestSP4(?,?)}")

this is also faster cause it don't have to issue a prepare in the
network layer

> stmt.param_iotype(1, ODBC::SQL_PARAM_OUTPUT)
> stmt.param_output_type(1, ODBC::SQL_INTEGER)
> stmt.param_output_size(1, 4)
> stmt.execute(1, nil)
> res = stmt.fetch_all
> out_value = stmt.param_output_value(1)
> puts "#{out_value}"
> rescue Exception => e
> puts e
> puts e.backtrace.join("\n")
> ensure
> stmt.cancel if stmt
> conn.disconnect if conn && conn.connected?
> end
>
> however, when running this script, i get:
>
> # 2000 (8162) [FreeTDS][SQL Server]The formal parameter "@P2" was not
> # declared as an OUTPUT parameter, but the actual parameter passed in
> # requested output.
>
> installed tools/libs:
>
> freetds-0.82 (--with-unixodbc=/usr/local --with-tdsver=8.0
> --enable-msdblib --enable-sybase-compat)
> unixodbc-2.2.14 (--enable-gui=no)
> ruby-odbc 0.9996
>
> I'm wondering if anyone can tell from the dump if I am missing
> something? I can see there is a call to SQLBindParameter which looks
> correct to me, yet I'm getting the above error. Thanks in advance.
>
...

freddy77






Archive powered by MHonArc 2.6.24.

Top of Page