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

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