Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Unable to get output parameter

3 posts in General Discussion Last posting was on 2004-03-10 17:41:58.0Z
Nagendra Makineni Posted on 2004-03-02 20:24:43.0Z
From: "Nagendra Makineni" <nagendra.makineni@brinksinc.com>
Newsgroups: ianywhere.public.general
Subject: Unable to get output parameter
Lines: 15
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
NNTP-Posting-Host: 12.172.207.3
X-Original-NNTP-Posting-Host: 12.172.207.3
Message-ID: <4044ed8b$1@forums-1-dub>
Date: 2 Mar 2004 12:24:43 -0800
X-Trace: forums-1-dub 1078259083 12.172.207.3 (2 Mar 2004 12:24:43 -0800)
X-Original-Trace: 2 Mar 2004 12:24:43 -0800, 12.172.207.3
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2689
Article PK: 6162

Dear Friends

I have VB.NET application running against ASA8. I got a stored procedure
that got one OUTPUT parameter and a Result Set. I am using
'iAnywhere.Data.AsaClient' class and its objects to connect to database and
execute the stored procedure. But in my VB.NET I could not see the OUTPUT
parameter value. If I remove the Result Set from the stored procedure, I
could see the OUTPUT parameter. Any idea what is going on?

Any input from you is greatly appreciated.

Thanks


Breck Carter [TeamSybase] Posted on 2004-03-05 15:34:52.0Z
From: "Breck Carter [TeamSybase]" <NOSPAM__bcarter@risingroad.com>
Newsgroups: ianywhere.public.general
Subject: Re: Unable to get output parameter
Organization: RisingRoad Professional Services
Reply-To: NOSPAM__bcarter@risingroad.com
Message-ID: <c27h40t7fi686f8i5qb3pn3ubnbl3p9iqv@4ax.com>
References: <4044ed8b$1@forums-1-dub>
X-Newsreader: Forte Agent 2.0/32.640
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
NNTP-Posting-Host: bcarter.sentex.ca
X-Original-NNTP-Posting-Host: bcarter.sentex.ca
Date: 5 Mar 2004 07:34:52 -0800
X-Trace: forums-1-dub 1078500892 64.7.134.118 (5 Mar 2004 07:34:52 -0800)
X-Original-Trace: 5 Mar 2004 07:34:52 -0800, bcarter.sentex.ca
Lines: 30
X-Authenticated-User: TeamPS
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2726
Article PK: 6198

I don't know the answer, but this Google Web search got a lot of
hits...

vb.net stored procedure result set output parameter

Breck
On 2 Mar 2004 12:24:43 -0800, "Nagendra Makineni"

<nagendra.makineni@brinksinc.com> wrote:

>
>Dear Friends
>
>I have VB.NET application running against ASA8. I got a stored procedure
>that got one OUTPUT parameter and a Result Set. I am using
>'iAnywhere.Data.AsaClient' class and its objects to connect to database and
>execute the stored procedure. But in my VB.NET I could not see the OUTPUT
>parameter value. If I remove the Result Set from the stored procedure, I
>could see the OUTPUT parameter. Any idea what is going on?
>
>Any input from you is greatly appreciated.
>
>Thanks
>

--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/ASIN/1556225067/risingroad-20
bcarter@risingroad.com
Mobile and Distributed Enterprise Database Applications
www.risingroad.com


Juergen Frerking Posted on 2004-03-10 17:41:58.0Z
From: "Juergen Frerking" <Juergen.Frerking@t-online.de>
Newsgroups: ianywhere.public.general
References: <4044ed8b$1@forums-1-dub>
Subject: Re: Unable to get output parameter
Lines: 118
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1158
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1165
X-Original-NNTP-Posting-Host: p50861b25.dip.t-dialin.net
Message-ID: <404f5419$1@forums-2-dub>
X-Original-Trace: 10 Mar 2004 09:44:57 -0800, p50861b25.dip.t-dialin.net
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 10 Mar 2004 09:41:50 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 10 Mar 2004 09:41:58 -0800
X-Trace: forums-1-dub 1078940518 10.22.108.75 (10 Mar 2004 09:41:58 -0800)
X-Original-Trace: 10 Mar 2004 09:41:58 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:2765
Article PK: 6235

Hello,

we had the same problem using ASA6.03 and ODBC. We have been told by
technical support that it is impossible to get both, a result set and output
parameters.
Instead you have to do the call like you would do it using isql:
First execute a create variable @outvar.
Then excute call sp_proc(@outvar).
Then fetch the result set (As far as I know, your have to fetch all rows,
before doing the next step).
Then execute select @outvar and get the value.

The folling is the original answer from technical support:

> FR: gfarrenb
>
> Dear Mr. Frerking,
>
> sorry for the delay.
> The problem you described is known and is currently not implemented in
ASA.
> Your reference to the Microsoft web site didn't say it should work but it
said it should work on a MS SQL Server.
>
> Here's a detailed description and possible workaround from our
engineering:
>
> **************
> The problem you describe is not an ODBC problem, but a deficiency in our

database engine. The engine does not allow you to have output parameters (or
a > > > return value) that uses host variables and also return a result set.
The only way to do this is with variables. The reason for this is that there
is no way to get the > > > result back to the program when the cursor is
closed.
> To get around the problem, you need to do something like the following:
> SQLExecDirect( stmt2, "create variable @res char(50)" );
> SQLExecDirect( stmt1, "call sp_test( @res )" );
> SQLFetch( stmt1);
> SQLFreeStmt( stmt1, SQL_CLOSE );
> SQLExecDirect( stmt2, "select @res" );
> SQLBindCol( stmt2, SQL_CHAR, &my_buffer );
> SQLFetch( stmt2 )
> More info:
> Currently, stored procedures cannot return a result set as well as output
variables or return value. The reason is that there is no place to put those
values when a > result set is being returned. We would have to change or
enhance the CALL and EXECUTE statements and probably the CLOSE statement to
allow for the > > > returning of these values separately from the result
set(s). This is being considered for a future release.
> Note that this difference in behaviour comes about partially because of a
significant feature of SQL Anywhere, namely bi-directional, scrollable
cursors. In SQL > Server, when you hit the end of a cursor, about the only
thing you can do is close it. In SQL Anywhere, you could conceivably start
scrolling upwards again. This > is why, in ISQL, multiple result sets
require the RESUME statement between them. It indicates that you are really
done with this result set and can start > > > > > processing the next one.
At the ODBC level, the ODBC driver can issue the RESUME for you
automatically (SQLMoreResults). At the Embedded SQL level, > you can issue
your own RESUME.
> Currently, the only way to get return values and output variables with a
result set is through variables. For example,
> create proc test( out @outvar int ) as
> select * from some_table
> select @outvar = 1
> return 2
> create variable @retvar int
> create variable @parm int
> execute @retvar = test( @parm )
> resume
> select @retvar, @parm
> At this point, a program could use the last select statement and do a
fetch into any host variables.
> I understand that this is not the most desireable answer for the customer.
We are considering this requested feature in a future release, but we don't
have any > > > dates or versions in mind.
> To help support our position, it is important to remember that our
Transact-SQL compatibility is very good, but it is not perfect. Our view is
that writing a > > > > procedure in the Transact-SQL dialect of SQL Anywhere
will virtually guarantee portability to SQL Server's Transact-SQL, but
coming to SQL Anywhere from > SQL Server is more likely to result in things
that don't work exactly the same. However, we feel that our compatibility is
well over 90%.
> Also, please note that SQL Anywhere does support multiple result sets. It
was indicated above that it did not, but that was an old issue, and the
feature was > > > added after that.
> **********
> Since there's not much left I can do I kindly ask if it's ok to close this
case.
> Regards,
>
> Guido Farrenberg
> CS&S Germany
> S Y B A S E
>

Juergen Frerking


"Nagendra Makineni" <nagendra.makineni@brinksinc.com> schrieb im Newsbeitrag
news:4044ed8b$1@forums-1-dub...
>
> Dear Friends
>
> I have VB.NET application running against ASA8. I got a stored procedure
> that got one OUTPUT parameter and a Result Set. I am using
> 'iAnywhere.Data.AsaClient' class and its objects to connect to database
and
> execute the stored procedure. But in my VB.NET I could not see the OUTPUT
> parameter value. If I remove the Result Set from the stored procedure, I
> could see the OUTPUT parameter. Any idea what is going on?
>
> Any input from you is greatly appreciated.
>
> Thanks
>
>