Skip to Content.
Sympa Menu

freetds - Re: [freetds] Not getting return value andoutputparameterfromstoredprocedure.

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Not getting return value andoutputparameterfromstoredprocedure.
  • Date: Tue, 9 Oct 2007 13:39:36 +0200

>
> 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
>




Archive powered by MHonArc 2.6.24.

Top of Page