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.

Calling Stored Procedure from Embedded SQL

4 posts in General Discussion Last posting was on 2003-10-29 20:38:34.0Z
M. Ravari Posted on 2003-10-29 01:24:05.0Z
From: "M. Ravari" <majid@lablogics.com>
Newsgroups: ianywhere.public.general
Subject: Calling Stored Procedure from Embedded SQL
Lines: 14
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 5.50.4922.1500
X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4925.2800
NNTP-Posting-Host: acc44259.ipt.aol.com
X-Original-NNTP-Posting-Host: acc44259.ipt.aol.com
Message-ID: <3f9f24c5@forums-1-dub>
Date: 28 Oct 2003 18:24:05 -0700
X-Trace: forums-1-dub 1067394245 172.196.66.89 (28 Oct 2003 18:24:05 -0700)
X-Original-Trace: 28 Oct 2003 18:24:05 -0700, acc44259.ipt.aol.com
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1946
Article PK: 4188

the following EMBEDDED SQL statement to call stored procedure does not work
from within my SQC file:

EXEC SQL CALL sp_invoiceBreakDown(:q_invID);

where it returns no results and q_invID is a long holding the invoice id.

however, when I run it directly from dbisql command it works.

What am I missing in my SQC file?
Thank you,


"Bruce Hay" Posted on 2003-10-29 13:39:35.0Z
From: "Bruce Hay" <hay at sybase dot com>
Newsgroups: ianywhere.public.general
References: <3f9f24c5@forums-1-dub>
Subject: Re: Calling Stored Procedure from Embedded SQL
Lines: 27
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1106
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1106
NNTP-Posting-Host: hay-xp.sybase.com
X-Original-NNTP-Posting-Host: hay-xp.sybase.com
Message-ID: <3f9fd127$1@forums-1-dub>
Date: 29 Oct 2003 06:39:35 -0700
X-Trace: forums-1-dub 1067438375 172.31.142.57 (29 Oct 2003 06:39:35 -0700)
X-Original-Trace: 29 Oct 2003 06:39:35 -0700, hay-xp.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1947
Article PK: 4184

Does the procedure return a result set? If so, you'll need to open a cursor
on the CALL rather than just executing it. If you check the value of SQLCODE
after the call, you're probably getting a 111 warning ("Statement cannot be
executed").

Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
Developer Community at http://www.ianywhere.com/developer

"M. Ravari" <majid@lablogics.com> wrote in message
news:3f9f24c5@forums-1-dub...
> the following EMBEDDED SQL statement to call stored procedure does not
work
> from within my SQC file:
>
> EXEC SQL CALL sp_invoiceBreakDown(:q_invID);
>
> where it returns no results and q_invID is a long holding the invoice id.
>
> however, when I run it directly from dbisql command it works.
>
> What am I missing in my SQC file?
> Thank you,
>
>
>


M. Ravari Posted on 2003-10-29 18:56:57.0Z
From: "M. Ravari" <majid@lablogics.com>
Newsgroups: ianywhere.public.general
References: <3f9f24c5@forums-1-dub> <3f9fd127$1@forums-1-dub>
Subject: Re: Calling Stored Procedure from Embedded SQL
Lines: 46
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: adsl-67-127-109-33.dsl.irvnca.pacbell.net
Message-ID: <3fa01c9e$1@forums-2-dub>
X-Original-Trace: 29 Oct 2003 12:01:34 -0700, adsl-67-127-109-33.dsl.irvnca.pacbell.net
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Oct 2003 11:49:18 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Oct 2003 11:56:57 -0700
X-Trace: forums-1-dub 1067457417 10.22.108.75 (29 Oct 2003 11:56:57 -0700)
X-Original-Trace: 29 Oct 2003 11:56:57 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1951
Article PK: 4190

Thanks Bruce for your response, no it does not return any result sets. The
stored procedure only performs an internal function within the database.

If I run the same stored procedure in Dbisql, it runs just fine. Running it
from embedded sql, however, does not perform any actions in the database.

Thank you again,

M. Ravari

"Bruce Hay" <hay at sybase dot com> wrote in message
news:3f9fd127$1@forums-1-dub...
> Does the procedure return a result set? If so, you'll need to open a
cursor
> on the CALL rather than just executing it. If you check the value of
SQLCODE
> after the call, you're probably getting a 111 warning ("Statement cannot
be
> executed").
>
> Whitepapers, TechDocs, bug fixes are all available through the iAnywhere
> Developer Community at http://www.ianywhere.com/developer
>
> "M. Ravari" <majid@lablogics.com> wrote in message
> news:3f9f24c5@forums-1-dub...
> > the following EMBEDDED SQL statement to call stored procedure does not
> work
> > from within my SQC file:
> >
> > EXEC SQL CALL sp_invoiceBreakDown(:q_invID);
> >
> > where it returns no results and q_invID is a long holding the invoice
id.
> >
> > however, when I run it directly from dbisql command it works.
> >
> > What am I missing in my SQC file?
> > Thank you,
> >
> >
> >
>
>


Greg Fenton Posted on 2003-10-29 20:38:34.0Z
From: Greg Fenton <greg.fenton_NOSPAM_@ianywhere.com>
Organization: iAnywhere Solutions Inc.
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.4; MultiZilla v1.4.0.4A) Gecko/20030624
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: ianywhere.public.general
Subject: Re: Calling Stored Procedure from Embedded SQL
References: <3f9f24c5@forums-1-dub> <3f9fd127$1@forums-1-dub> <3fa01c9e$1@forums-2-dub>
In-Reply-To: <3fa01c9e$1@forums-2-dub>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Original-NNTP-Posting-Host: gfenton-xp.sybase.com
Message-ID: <3fa03470$1@forums-2-dub>
X-Original-Trace: 29 Oct 2003 13:43:12 -0700, gfenton-xp.sybase.com
Lines: 24
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 29 Oct 2003 13:30:54 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 29 Oct 2003 13:38:34 -0700
X-Trace: forums-1-dub 1067463514 10.22.108.75 (29 Oct 2003 13:38:34 -0700)
X-Original-Trace: 29 Oct 2003 13:38:34 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1955
Article PK: 4192


M. Ravari wrote:
>
> If I run the same stored procedure in Dbisql, it runs just fine. Running it
> from embedded sql, however, does not perform any actions in the database.
>

Please, always include the version and BUILD number of ASA that you are
using in every post (e.g. for ASA 9.0, use the command "dbeng9 -v")

You might try connecting to the database using the database object
debugger or putting some MESSAGE statements in your stored procedure to
track what is happening (is the procedure running? is it getting an
invalid parameter value? etc...)

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/