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

3 posts in General Discussion Last posting was on 2003-10-31 15:27:22.0Z
M. Ravari Posted on 2003-10-30 23:04:24.0Z
From: "M. Ravari" <majid@lablogics.com>
Newsgroups: ianywhere.public.general
Subject: Calling stored procedure from EMBEDDED SQL
Lines: 37
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
X-Original-NNTP-Posting-Host: acac9084.ipt.aol.com
Message-ID: <3fa1a820$1@forums-2-dub>
X-Original-Trace: 30 Oct 2003 16:09:04 -0700, acac9084.ipt.aol.com
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 30 Oct 2003 15:56:38 -0700, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 30 Oct 2003 16:04:24 -0700
X-Trace: forums-1-dub 1067558664 10.22.108.75 (30 Oct 2003 16:04:24 -0700)
X-Original-Trace: 30 Oct 2003 16:04:24 -0700, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1978
Article PK: 17381

I am using DBserve7.

I am using the following EMBEDDED SQL routine to call a stored procedure:

int FAR PASCAL InvoiceBreakDown(long invid)
{
q_invid = invid;

EXEC SQL CALL sp_InvoiceBreakDown(:q_invid);

if (SQLCODE < 0)
return (SQLCODE) ;
return (DBOK) ;
}

however it does not do anything to a database table(which is what its
supposed to do).

However if i call the stored procedure from within DBISQL command it works
fine as follows:
sp_InvoiceBreakDown (326186)

WHAT AM I MISSING IN MY SQC FILE ?????

Few things to note:

1) SQLCODE above does not generate any errors
2) The stored procedure does not return any results.
3) The cpp file genereated by SQLPP.EXE compiles successfully
4) The stored procedure itself is working since it works within DBISQL
5) I think due to my old database engine, Object debugger is not supported
so please dont ask me to use it.

Thank you.


Nick Elson Posted on 2003-10-31 15:27:22.0Z
From: "Nick Elson" <no_spam_nicelson@sybase.com>
Newsgroups: ianywhere.public.general
References: <3fa1a820$1@forums-2-dub>
Subject: Re: Calling stored procedure from EMBEDDED SQL
Lines: 61
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: vpn-concord-030.sybase.com
X-Original-NNTP-Posting-Host: vpn-concord-030.sybase.com
Message-ID: <3fa27f5a$1@forums-1-dub>
Date: 31 Oct 2003 07:27:22 -0800
X-Trace: forums-1-dub 1067614042 158.159.8.30 (31 Oct 2003 07:27:22 -0800)
X-Original-Trace: 31 Oct 2003 07:27:22 -0800, vpn-concord-030.sybase.com
X-Authenticated-User: techsupp
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1982
Article PK: 4210

You should probably investigate this 3 ways:

1 - Verify that the stored procedure has been called.
If there is no other way to do this, try adding a message
statement to it. Profiling and debugging are other options.

2 - Verify that the value expected is actually being passed.
The server -zr sql switch might help here. So too
would a message statement.

3 - Given whatever you find out from the above 2 items
investigate you source code a bit. The FAR PASCAL
call conventions is antiquated and possibly suspect. You
also check to see that you have q_invid declared in your
SQL DECLARATION section (see sample files); also
being an automatic variable may be making this a little
more difficult to see what is happening.

Good luck there

"M. Ravari" <majid@lablogics.com> wrote in message
news:3fa1a820$1@forums-2-dub...
>
> I am using DBserve7.
>
> I am using the following EMBEDDED SQL routine to call a stored procedure:
>
> int FAR PASCAL InvoiceBreakDown(long invid)
> {
> q_invid = invid;
>
> EXEC SQL CALL sp_InvoiceBreakDown(:q_invid);
>
> if (SQLCODE < 0)
> return (SQLCODE) ;
> return (DBOK) ;
> }
>
> however it does not do anything to a database table(which is what its
> supposed to do).
>
> However if i call the stored procedure from within DBISQL command it works
> fine as follows:
> sp_InvoiceBreakDown (326186)
>
> WHAT AM I MISSING IN MY SQC FILE ?????
>
> Few things to note:
>
> 1) SQLCODE above does not generate any errors
> 2) The stored procedure does not return any results.
> 3) The cpp file genereated by SQLPP.EXE compiles successfully
> 4) The stored procedure itself is working since it works within DBISQL
> 5) I think due to my old database engine, Object debugger is not supported
> so please dont ask me to use it.
>
> Thank you.
>
>


Greg Fenton Posted on 2003-10-31 15:20:06.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: <3fa1a820$1@forums-2-dub>
In-Reply-To: <3fa1a820$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: <3fa27ec3$1@forums-2-dub>
X-Original-Trace: 31 Oct 2003 07:24:51 -0800, gfenton-xp.sybase.com
Lines: 34
X-Original-NNTP-Posting-Host: forums-2-dub.sybase.com
X-Original-Trace: 31 Oct 2003 07:12:17 -0800, forums-2-dub.sybase.com
NNTP-Posting-Host: forums-master.sybase.com
X-Original-NNTP-Posting-Host: forums-master.sybase.com
Date: 31 Oct 2003 07:20:06 -0800
X-Trace: forums-1-dub 1067613606 10.22.108.75 (31 Oct 2003 07:20:06 -0800)
X-Original-Trace: 31 Oct 2003 07:20:06 -0800, forums-master.sybase.com
X-Authenticated-User: ngsysop
Path: forums-1-dub!not-for-mail
Xref: forums-1-dub ianywhere.public.general:1981
Article PK: 4211


M. Ravari wrote:
> 5) I think due to my old database engine, Object debugger is not supported
> so please dont ask me to use it.
>

You've never indicated in any of your posts the version and build number
of ASA that you are using. Please do so in EVERY post you make so that
we have an idea on what features we can suggest or lookup any bug fixes
related to what you are using.

Have you tried adding MESSAGE statements to your stored procedure to
find out if the SP is being called by your application?

For example, if you add:

MESSAGE NOW() || ' invid is ' || @invid;

(assuming @invid is the name of your SP's parameter), then this message
will be printed to the database engine's console window whenever the SP
is called.

You can then see if the SP is actually being called by your ESQL program
and you can see the actual value being passed into the SP.

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/