[freetds] Not getting return value andoutputparameterfromstoredprocedure.

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Tue Oct 9 07:39:36 EDT 2007


> 
> Hi,
> Thanks for your quick response. 
> 
> We have installed FreeTDS 0.64 now. Which version of unixODBC you are
> using. We are using unixODBC(2.2.12). 
> 
> Please refer to attached (our) trace file, search for
> "[ODBC][19497][SQLExecDirect.c][233]" it gives following error 
> 
> "DIAG [42000] [FreeTDS][SQL Server]Procedure 'TestParm' expects
> parameter '@OutParm2', which was not supplied." 
> 
> Please suggest.
> 

Mmm... I think that this

CREATE PROCEDURE TestParm @OutParm1 int OUTPUT AS
SELECT au_lname, au_fname FROM pubs.dbo.authors
SELECT @OutParm1 = 88
RETURN 99
GO

is not the store you are calling... Perhaps you suppose to be in a db
and you are not in that db. Try to use isql (cause it use the same
driver) and issue this query

select [text] from syscomments where [id] = (select top 1 [id] from
sysobjects where [name] = 'TestParm') order by number, colid

the result should be store procedure source (at least under mssql2k).

freddy77

> Thanks & best regards,
> Rajesh Akolu
> Project Manager
> Financial Technologies (India) Ltd.          
> Board No: 022-26831022     Extn: 254
> Fax: 022-26836999
> 
> rajesh.akolu at ftindia.com 
> 
> www.ftindia.com 
> 
> 
> 
> Disclaimer:
> 
> The information in this E-mail (which includes any files transmitted
> with it) is CONFIDENTIAL and may be legally PRIVILEGED. It is intended
> solely for the addressee and access to this email by anyone else is
> unauthorized. If you have received it in error, please destroy any
> copies of this message, including any attachments, and delete it from
> your system notifying the sender immediately. Any disclosure, copying,
> distribution, dissemination, forwarding, printing or any 
> action taken or
> omitted to be taken in reliance on it or utilising the same for any
> purpose other than what it is intended for, is prohibited and may be
> unlawful.
> 
> -----Original Message-----
> From: freetds-bounces at lists.ibiblio.org
> [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of ZIGLIO,
> Frediano, VF-IT
> Sent: Tuesday, October 09, 2007 2:02 PM
> To: FreeTDS Development Group
> Subject: Re: [freetds] Not getting return value and
> outputparameterfromstoredprocedure.
> 
> > 
> > Hi,
> > In continuation with last conversation:
> > 
> > Find attached for your reference:
> > 1. Trace file (strace.txt)
> > 2. Code which we are testing (code.cpp)
> > 3. Stored procedure (SP_TestParm.txt) 
> > 
> > We are not getting any error.
> > 
> > We have set TDS Version = 8.0 and 7.0 in freeTDS.conf
> > 
> 
> This is the result I got from both 0.64 and CVS HEAD versions (using
> protocol version 8.0)
> 
> Before result sets cleared: RetCode = 0, OutParm = 1.
> After result sets drained: RetCode = 99, OutParm = 88.
> 
> which seems correct to me...
> You are surely not using version 0.62... strace report clock_gettime
> which is quite new in FreeTDS code.
> 
> I noted also that you have some strange lines in your trace
> 
> open("/usr/X11R6/lib/tls/libm.so.6", O_RDONLY) = -1 ENOENT 
> (No such file
> or directory)
> open("/usr/X11R6/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No 
> such file or
> directory)
> open("-pthread/tls/i686/sse2/libm.so.6", O_RDONLY) = -1 
> ENOENT (No such
> file or directory)
> open("-pthread/tls/i686/libm.so.6", O_RDONLY) = -1 ENOENT (No 
> such file
> or directory)
> open("-pthread/tls/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No 
> such file
> or directory)
> open("-pthread/tls/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("-pthread/i686/sse2/libm.so.6", O_RDONLY) = -1 ENOENT 
> (No such file
> or directory)
> open("-pthread/i686/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("-pthread/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("-pthread/libm.so.6", O_RDONLY)    = -1 ENOENT (No such file or
> directory)
> open("/usr/lib/tls/i686/libm.so.6", O_RDONLY) = -1 ENOENT (No 
> such file
> or directory)
> open("/usr/lib/tls/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("/usr/lib/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("/usr/lib/libm.so.6", O_RDONLY)    = -1 ENOENT (No such file or
> directory)
> open("/usr/local/lib/tls/i686/sse2/libm.so.6", O_RDONLY) = -1 
> ENOENT (No
> such file or directory)
> stat64("/usr/local/lib/tls/i686/sse2", 0xbfebde58) = -1 
> ENOENT (No such
> file or directory)
> open("/usr/local/lib/tls/i686/libm.so.6", O_RDONLY) = -1 
> ENOENT (No such
> file or directory)
> stat64("/usr/local/lib/tls/i686", 0xbfebde58) = -1 ENOENT (No 
> such file
> or directory)
> open("/usr/local/lib/tls/sse2/libm.so.6", O_RDONLY) = -1 
> ENOENT (No such
> file or directory)
> stat64("/usr/local/lib/tls/sse2", 0xbfebde58) = -1 ENOENT (No 
> such file
> or directory)
> open("/usr/local/lib/tls/libm.so.6", O_RDONLY) = -1 ENOENT 
> (No such file
> or directory)
> stat64("/usr/local/lib/tls", 0xbfebde58) = -1 ENOENT (No such file or
> directory)
> open("/usr/local/lib/i686/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No
> such file or directory)
> stat64("/usr/local/lib/i686/sse2", 0xbfebde58) = -1 ENOENT 
> (No such file
> or directory)
> open("/usr/local/lib/i686/libm.so.6", O_RDONLY) = -1 ENOENT (No such
> file or directory)
> stat64("/usr/local/lib/i686", 0xbfebde58) = -1 ENOENT (No such file or
> directory)
> open("/usr/local/lib/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No such
> file or directory)
> stat64("/usr/local/lib/sse2", 0xbfebde58) = -1 ENOENT (No such file or
> directory)
> open("/usr/local/lib/libm.so.6", O_RDONLY) = -1 ENOENT (No 
> such file or
> directory)
> stat64("/usr/local/lib", {st_mode=S_IFDIR|0755, 
> st_size=12288, ...}) = 0
> open("tls/i686/sse2/libm.so.6", O_RDONLY) = -1 ENOENT (No such file or
> directory)
> open("tls/i686/libm.so.6", O_RDONLY)    = -1 ENOENT (No such file or
> directory)
> open("tls/sse2/libm.so.6", O_RDONLY)    = -1 ENOENT (No such file or
> directory)
> open("tls/libm.so.6", O_RDONLY)         = -1 ENOENT (No such file or
> directory)
> open("i686/sse2/libm.so.6", O_RDONLY)   = -1 ENOENT (No such file or
> directory)
> open("i686/libm.so.6", O_RDONLY)        = -1 ENOENT (No such file or
> directory)
> open("sse2/libm.so.6", O_RDONLY)        = -1 ENOENT (No such file or
> directory)
> open("libm.so.6", O_RDONLY)             = -1 ENOENT (No such file or
> directory)
> open("/etc/ld.so.cache", O_RDONLY)      = 3
> fstat64(3, {st_mode=S_IFREG|0644, st_size=106299, ...}) = 0
> old_mmap(NULL, 106299, PROT_READ, MAP_PRIVATE, 3, 0) = 0xb7f9b000
> close(3)                                = 0
> open("/lib/tls/libm.so.6", O_RDONLY)    = 3
> read(3, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\0\323z"...,
> 512) = 512
> 
> note that the system is looking at "-pthread" directory and currect
> directory for libraries... did you set some LD_* environment or used
> some strange link options??
> 
> This is my sql trace (using 0.64 version)
> 
> [ODBC][21084][__handles.c][444]
>                 Exit:[SQL_SUCCESS]
>                         Environment = 0x9fe1008
> [ODBC][21084][SQLSetEnvAttr.c][182]
>                 Entry:
>                         Environment = 0x9fe1008
>                         Attribute = SQL_ATTR_ODBC_VERSION
>                         Value = 0x3
>                         StrLen = -6
> [ODBC][21084][SQLSetEnvAttr.c][349]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLAllocHandle.c][345]
>                 Entry:
>                         Handle Type = 2
>                         Input Handle = 0x9fe1008
> [ODBC][21084][SQLAllocHandle.c][463]
>                 Exit:[SQL_SUCCESS]
>                         Output Handle = 0x9fe15e0
> [ODBC][21084][SQLConnect.c][3549]
>                 Entry:
>                         Connection = 0x9fe15e0
>                         Server Name = [fxs][length = 3 (SQL_NTS)]
>                         User Name = [sa][length = 2 (SQL_NTS)]
>                         Authentication = 
> [****************][length = 16
> (SQL_NTS)]
>                 UNICODE Using encoding ASCII 'ISO8859-1' and UNICODE
> 'UCS-2LE'
> 
> [ODBC][21084][SQLConnect.c][4123]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLAllocHandle.c][510]
>                 Entry:
>                         Handle Type = 3
>                         Input Handle = 0x9fe15e0
> [ODBC][21084][SQLAllocHandle.c][872]
>                 Exit:[SQL_SUCCESS]
>                         Output Handle = 0xa028948
> [ODBC][21084][SQLBindParameter.c][193]
>                 Entry:
>                         Statement = 0xa028948
>                         Param Number = 1
>                         Param Type = 4
>                         C Type = -15 SQL_C_SSHORT
>                         SQL Type = 4 SQL_INTEGER
>                         Col Def = 0
>                         Scale = 0
>                         Rgb Value = 0xbfda86d2
>                         Value Max = 0
>                         StrLen Or Ind = 0xbfda86cc
> [ODBC][21084][SQLBindParameter.c][339]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLBindParameter.c][193]
>                 Entry:
>                         Statement = 0xa028948
>                         Param Number = 2
>                         Param Type = 4
>                         C Type = -15 SQL_C_SSHORT
>                         SQL Type = 4 SQL_INTEGER
>                         Col Def = 0
>                         Scale = 0
>                         Rgb Value = 0xbfda86d0
>                         Value Max = 0
>                         StrLen Or Ind = 0xbfda86c8
> [ODBC][21084][SQLBindParameter.c][339]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLExecDirect.c][233]
>                 Entry:
>                         Statement = 0xa028948
>                         SQL = [{? = call TestParm(?)}][length = 22
> (SQL_NTS)]
> [ODBC][21084][SQLExecDirect.c][496]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLMoreResults.c][155]
>                 Entry:
>                         Statement = 0xa028948
> [ODBC][21084][SQLMoreResults.c][311]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLMoreResults.c][155]
>                 Entry:
>                         Statement = 0xa028948
> [ODBC][21084][SQLMoreResults.c][311]
>                 Exit:[SQL_NO_DATA]
> [ODBC][21084][SQLFreeHandle.c][365]
>                 Entry:
>                         Handle Type = 3
>                         Input Handle = 0xa028948
> [ODBC][21084][SQLFreeHandle.c][462]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLDisconnect.c][204]
>                 Entry:
>                         Connection = 0x9fe15e0
> [ODBC][21084][SQLDisconnect.c][341]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLFreeHandle.c][268]
>                 Entry:
>                         Handle Type = 2
>                         Input Handle = 0x9fe15e0
> [ODBC][21084][SQLFreeHandle.c][317]
>                 Exit:[SQL_SUCCESS]
> [ODBC][21084][SQLFreeHandle.c][203]
>                 Entry:
>                         Handle Type = 1
>                         Input Handle = 0x9fe1008
> 
> > 
> > Thanks & best regards,
> > 
> > Rajesh Akolu
> > Project Manager
> > 
> ....
> 
> bye
>   freddy77
> 
> > 
> > > 
> > > Hi,
> > > 
> > > We are using FreeTDS 0.65 (TDS version 8.0) and unixODBC 
> > > 2.2.12 on RH Linux 3.4.6. We are connecting to Microsoft SQL 
> > > Server 2000 from Linux. I am using C++ on Linux. 
> > > 
> > 
> > Is ebay selling time machine ??
> > I didn't note we release 0.65 :)
> > Don't mind, just kidding... I think you are using a snapshot which
> > report 0.65.
> > 
> > > 
> > > 
> > > Requirement: To get return value and output parameter after 
> > > calling a stored procedure.
> > > 
> > > 
> > > 
> > > We are calling Stored Procedure (SP) with input and output 
> > > parameter and we are also expecting return value as well. 
> > > 
> > > 
> > > 
> > > We are calling stored procedure as follows:
> > > 
> > > 
> > > 
> > > {? = CALL stp_returnRow(?, ?, ?)}
> > > 
> > > 
> > 
> > MMmm... a unixODBC trace would help. We support this type of 
> > call so it
> > should works... what's the problem?? Aren't datas readed 
> correct? Does
> > execute report an error/warning??
> > 
> > > 
> > > In above case we are expecting both return value and output 
> > > parameter. The first parameter is input and 2nd and 3rd 
> > > parameter is output parameter.
> > > 
> > > 
> > 
> > Remember that ODBC count also the result so you have 4 parameters.
> > 
> > > 
> > > We have written the above query to unixODBC and we were 
> > > informed that this problem is not in there Perview. This is 
> > > for your information.
> > > 
> > > 
> > > 
> > > Please suggest or give us solution if possible.
> > > 
> > > 
> > > 
> > > Thanks & best regards,
> > > 
> > > Rajesh Akolu
> > > 
> > 
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 


More information about the FreeTDS mailing list