Sqlanywhere Stored Procedures

--------------DA6BDD57292073C8536A1EC1
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

> Hello
>     We are having great difficulty obtaining results from stored procedures
> from a sqlanywhere ver 5.5.01.  The error we recieve on the fetch statment
> is "CURSOR not Open".   The procdure script is
>
> drop procedure insert_new_applicant_proc;
> Create Procedure insert_new_applicant_proc
> ( OUT reslt1 numeric)
> begin
> set reslt1  = 5;
>
> END ;;
>
>
> The script in the SQLPreview event is as follows:
>
>
> string ls_msg, ls_error
> string ls_literal_iuserid
> long ll_literal_reslt, ll_org,ll_sdbeinind
> date  ld_sdbapprecvdt
> string ls_sdbbusnm, ls_sdbcertfrid, ls_sdbeinnmb, ls_sdbid
> // IF no row THEN discontinue, this also happens on the select statement
> IF row = 0 THEN Return 2
>   // Insert Statement
>   IF sqltype = PreviewInsert! THEN
>
>    DECLARE Insert_proc Procedure FOR insert_new_applicant_proc
>    reslt1 = :ll_literal_reslt USING SQLCA;
>         EXECUTE  Insert_proc;
>
>     il_trcknmb = ll_literal_reslt
>
>       ls_msg = 'Insert error using Stored Procedure
> insert_new_applicant_proc'
>   ELSE
>     // Update Statement
>    Return -1 // Not using stored procedures for Updates
>   END IF
> // Do not check for a Return code of 0 here since the procedures return a
> code
> // of 100 when they execute properly and have no result sets. -1 always
> // indicates that there was an error.
> IF sqlca.SqlCode = -1 THEN
>  ls_error = "Error during " + ls_msg + "~r~n~r~n"
>  ls_error = ls_error + "Transaction Error Code : " + String ( sqlca.SqlCode)
> + "~r~n"
>  ls_error = ls_error + "Database Error Code    : " + String
>  sqlca.SqldbCode) + "~r~n"
>  ls_error = ls_error + "DBMS                   : " + sqlca.DBMS      +
> "~r~n"
>  ls_error = ls_error + "Database               : " + sqlca.DataBase     +
> "~r~n"
>  ls_error = ls_error + "User ID                : " + sqlca.UserId     +
> "~r~n"
>  ls_error = ls_error + "DBParm                 : " + sqlca.dbParm     +
> "~r~n"
>  ls_error = ls_error + "Login ID               : " + sqlca.LogId      +
> "~r~n"
>  ls_error = ls_error + "ServerName             : " + sqlca.ServerName    + "
> ~r~n"
>  IF sqlca.AutoCommit THEN
>   ls_error = ls_error + "AutoCommit             :True~r~n"
>  ELSE
>   ls_error = ls_error + "AutoCommit             :False~r~n"
>  END IF
>  ls_error = ls_error + "Database Error Message : " + sqlca.SqlErrText +
> "~r~n"
>  Messagebox ("Insert Error", ls_error )
> // This will cause the update procedure to Return a -1
>  Return -1
> END IF
>
> fetch Insert_proc into :ll_literal_reslt;
> //
> IF sqlca.SqlCode = -1 THEN
>  ls_error = "Error during " + ls_msg + "~r~n~r~n"
>  ls_error = ls_error + "Transaction Error Code : " + String ( sqlca.SqlCode)
> + "~r~n"
>  ls_error = ls_error + "Database Error Code    : " + String
>  sqlca.SqldbCode) + "~r~n"
>  ls_error = ls_error + "DBMS                   : " + sqlca.DBMS      +
> "~r~n"
>  ls_error = ls_error + "Database               : " + sqlca.DataBase     +
> "~r~n"
>  ls_error = ls_error + "User ID                : " + sqlca.UserId     +
> "~r~n"
>  ls_error = ls_error + "DBParm                 : " + sqlca.dbParm     +
> "~r~n"
>  ls_error = ls_error + "Login ID               : " + sqlca.LogId      +
> "~r~n"
>  ls_error = ls_error + "ServerName             : " + sqlca.ServerName    +
> "~r~n"
>  IF sqlca.AutoCommit THEN
>   ls_error = ls_error + "AutoCommit             :True~r~n"
>  ELSE
>   ls_error = ls_error + "AutoCommit             :False~r~n"
>  END IF
>  ls_error = ls_error + "Database Error Message : " + sqlca.SqlErrText +
> "~r~n"
>  Messagebox ("Insert Fetch Error", sqlca.SqlErrText )
> // This will cause the update procedure to Return a -1
>  Return -1
> END IF
> close insert_proc;
>  il_trcknmb = ll_literal_reslt
> // Tell datawindow to NOT perform the default SQL statement
> Return 2
>
>
>
> Any help will be appreciated
>
>
>
> Dwayne.Hayes@sba.gov
>
> thanks
>


--------------DA6BDD57292073C8536A1EC1
Content-Type: text/html; charset=us-ascii
Content-Transfer-Encoding: 7bit

<HTML>

<BLOCKQUOTE TYPE=CITE>
<PRE>Hello
&nbsp;&nbsp;&nbsp; We are having great difficulty obtaining results from stored procedures
from a sqlanywhere ver 5.5.01.&nbsp; The error we recieve on the fetch statment
is "CURSOR not Open".&nbsp;&nbsp; The procdure script is

drop procedure insert_new_applicant_proc;
Create Procedure insert_new_applicant_proc
( OUT reslt1 numeric)
begin
set reslt1&nbsp; = 5;

END ;;


The script in the SQLPreview event is as follows:


string ls_msg, ls_error
string ls_literal_iuserid
long ll_literal_reslt, ll_org,ll_sdbeinind
date&nbsp; ld_sdbapprecvdt
string ls_sdbbusnm, ls_sdbcertfrid, ls_sdbeinnmb, ls_sdbid
// IF no row THEN discontinue, this also happens on the select statement
IF row = 0 THEN Return 2
&nbsp; // Insert Statement
&nbsp; IF sqltype = PreviewInsert! THEN

&nbsp;&nbsp; DECLARE Insert_proc Procedure FOR insert_new_applicant_proc
&nbsp;&nbsp; reslt1 = :ll_literal_reslt USING SQLCA;
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; EXECUTE&nbsp; Insert_proc;

&nbsp;&nbsp;&nbsp; il_trcknmb = ll_literal_reslt

&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; ls_msg = 'Insert error using Stored Procedure
insert_new_applicant_proc'
&nbsp; ELSE
&nbsp;&nbsp;&nbsp; // Update Statement
&nbsp;&nbsp; Return -1 // Not using stored procedures for Updates
&nbsp; END IF
// Do not check for a Return code of 0 here since the procedures return a
code
// of 100 when they execute properly and have no result sets. -1 always
// indicates that there was an error.
IF sqlca.SqlCode = -1 THEN
&nbsp;ls_error = "Error during " + ls_msg + "~r~n~r~n"
&nbsp;ls_error = ls_error + "Transaction Error Code : " + String ( sqlca.SqlCode)
+ "~r~n"
&nbsp;ls_error = ls_error + "Database Error Code&nbsp;&nbsp;&nbsp; : " + String
&nbsp;sqlca.SqldbCode) + "~r~n"
&nbsp;ls_error = ls_error + "DBMS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.DBMS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "Database&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.DataBase&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "User ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.UserId&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "DBParm&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.dbParm&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "Login ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.LogId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "ServerName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.ServerName&nbsp;&nbsp;&nbsp; + "
~r~n"
&nbsp;IF sqlca.AutoCommit THEN
&nbsp; ls_error = ls_error + "AutoCommit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :True~r~n"
&nbsp;ELSE
&nbsp; ls_error = ls_error + "AutoCommit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :False~r~n"
&nbsp;END IF
&nbsp;ls_error = ls_error + "Database Error Message : " + sqlca.SqlErrText +
"~r~n"
&nbsp;Messagebox ("Insert Error", ls_error )
// This will cause the update procedure to Return a -1
&nbsp;Return -1
END IF

fetch Insert_proc into :ll_literal_reslt;
//
IF sqlca.SqlCode = -1 THEN
&nbsp;ls_error = "Error during " + ls_msg + "~r~n~r~n"
&nbsp;ls_error = ls_error + "Transaction Error Code : " + String ( sqlca.SqlCode)
+ "~r~n"
&nbsp;ls_error = ls_error + "Database Error Code&nbsp;&nbsp;&nbsp; : " + String
&nbsp;sqlca.SqldbCode) + "~r~n"
&nbsp;ls_error = ls_error + "DBMS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.DBMS&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "Database&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.DataBase&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "User ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.UserId&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "DBParm&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.dbParm&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "Login ID&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.LogId&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;ls_error = ls_error + "ServerName&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; : " + sqlca.ServerName&nbsp;&nbsp;&nbsp; +
"~r~n"
&nbsp;IF sqlca.AutoCommit THEN
&nbsp; ls_error = ls_error + "AutoCommit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :True~r~n"
&nbsp;ELSE
&nbsp; ls_error = ls_error + "AutoCommit&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; :False~r~n"
&nbsp;END IF
&nbsp;ls_error = ls_error + "Database Error Message : " + sqlca.SqlErrText +
"~r~n"
&nbsp;Messagebox ("Insert Fetch Error", sqlca.SqlErrText )
// This will cause the update procedure to Return a -1
&nbsp;Return -1
END IF
close insert_proc;
&nbsp;il_trcknmb = ll_literal_reslt
// Tell datawindow to NOT perform the default SQL statement
Return 2



Any help will be appreciated



Dwayne.Hayes@sba.gov

thanks</PRE>
</BLOCKQUOTE>
&nbsp;</HTML>

--------------DA6BDD57292073C8536A1EC1--

0
Ashish
11/24/1997 6:57:54 PM
sybase.powerbuilder.general 62418 articles. 18 followers. Follow

1 Replies
504 Views

Similar Articles

[PageSpeed] 40

Easy.
You need to
declare a variable result
and then
SELECT @result

If you use output, you must use rpc in transaction object.


0
Victor
11/26/1997 1:57:14 AM
Reply:

Similar Artilces:

Calling a stored procedure inside another stored procedure (or "nested stored procedures")
Hi all - I'm trying to optimized my stored procedures to be a bit easier to maintain, and am sure this is possible, not am very unclear on the syntax to doing this correctly.  For example, I have a simple stored procedure that takes a string as a parameter, and returns its resolved index that corresponds to a record in my database. ie exec dbo.DeriveStatusID 'Created' returns an int value as 1 (performed by "SELECT statusID FROM statusList WHERE statusName= 'Created')  but I also have a second stored procedure that needs to make reference to&n...

DB2 V4 STORED STORED PROCEDURE AND POWERBUILDER
I have finally been able to call a stored procedure written in cobol from powerbuilder 6. But I still got some problem . The call will work only if I initialize the output parameter to space (80) because the output parameter of the stored procedure is char(80) . Here is a sample that works. string ls_literal_OUTMSGF string ls_literal_RQT Ls_literal_RQT = 'I' ls_literal_OUTMSGF = space(80) SQLCA.TESTPROC (ls_literal_RQT, ls_literal_OUTMSGF ) If I initialize the output variable like the following statements. It bombs.(communication error) setnull ( ls_literal_OUTMSGF...

Execute stored procedure from stored procedure
Hello,i need to execute a stored procedure from another stored procedure. For example:StoredProcedure1: Returns a table with columns "year" and "value".StoredProcedure2: Also returns a table with columns "year" and "value".In Stored Procedure3: I would like to use the results from StoredProcedure1 and StoredProcedure2 and join them. Can somebody give me an example how to execute a stored procedure from another one and use the result table for a new command?Kind regards and thanks,carsaw hi,first off all let me clarify here - you cannot return ta...

Calling a Stored Procedure from a Stored Procedure
Can you do this? I have a sp that returns a list of eligible accounts and then I use that list in other sp but I don't want to keep copying the code, I would like to be able to use it as a sub query in other sp but I don't know if this is possible or what the syntax would be. Thanks, Aaron You cannot do that directly. You can: 1. Use User defined functions which can return a resultset 2. Use a local #temp table which can hold the resultset of the called stored procedure and use this #temp table in your subsequent processing INSERT #tempTable EXEC s...

calling a stored procedure from a stored procedure
Hi Group! I'm on ASA 8.02 Build 4411 writing sprocs in WATCOM SQL. I have a sproc which breaks a given string-parameter into pieces. It's result set contains the pieces, e.g. last line of sproc: "select item from mytable;". This sproc works fine. Now I need to call this procedure from another procedure which has a variable result set. I tried the following: <snip... set ll_retval = 0; call sp_breakstring(as_bzp,','); BreakLoop: while ll_retval <> 100 and not ll_retval < 0 loop set li_zaehler = li_zaehler + 1; fetch next sp_breakstrin...

Call as stored procedure from another stored procedure
I am using a stored procedure (SP_A) to insert rows to a table. This is done by the cursor within the procedure. Value for one of the column is provided by SP_B. My problem is that when i call SP_B in SP_A it does not return any value (rather i do not know how to capture it from SP_A). If executed individually it returns a value. Please help with example if something specific needs to be taken care. Suresh wrote: > > Please help with example if something specific needs to be > taken care. Please always post the version and build number of ASA that you are using (e....

Calling a stored procedure from another stored procedure
Hello, I am now using Interbase again for a project and I am trying to do this: begin /* Procedure Text */ /* Steps: - Verify if protocol exists and fetch protocol id - add activity - add document - add activity protocol reeturn document id */ select id from protocols where protocols.protname = :protocol into :protid; /* protocol exists? Gooooood! */ if ( :protid is not null ) then begin execute addactivity(:userid,'New document') returning_values :actid; execute adddocument( :docname,:docpath...

how to call stored procedure from another stored procedure?
is it possible to call a stored procedure from another stored procedure?-keeara g------------------ Inside your first Stored Procedure: exec <name of SP to run> <Eventual Parameters this SP requires> Here is a link to all you want to know about executing SPs from other SPs. You can even Execute SPs on other SQL Servers.RegardsAndre Colbiornsen ---------------------------------Seventh DayRåbygatan 1A,SE-223 61 LundSwedenMob.: +46-(0)708-97 78 79Mail: info@seventhday.se--------------------------------...

Calling Stored Procedure fromanother Stored Procedure
Hi,I am getting error when I try to call a stored procedure from another. I would appreciate if someone could give some example.My first Stored Procedure has the following input output parameters:ALTER PROCEDURE dbo.FixedCharges @InvoiceNo int,@InvoiceDate smalldatetime,@TotalOut decimal(8,2) outputAS .... I have tried using the following statement to call it from another stored procedure within the same SQLExpress database. It is giving me error near CALL.CALL FixedCharges (@InvoiceNo,@InvoiceDate,@TotalOut )Many thanks in advanceJames I believe you want to use 'EXEC' abad...

Running stored procedure within a stored procedure
I have a custom built users table for storing some values and I am also utilizing the aspnet_Users table. I want to delete a user from my users tables then execute the aspnet_Users_DeleteUser sproc and pass into the stored procedure the username of the user to delete because the DeleteUser method requires this. When I execute the command from within my asp.net web application I get the exception below. Both values are being obtained from the asp.net application and are represented in my DAL that is also below. Any thoughts as to why I am receiving this exception? Thanks. Procedure or functi...

Stored procedure in powerbuilder
I'm having trouble using a stored procedure in Powerbuilder. I'm trying to create a datawindow based on the stored procedure sa_conn_info, but when I select the stored procedure initially in the design of the DW the database-server crashes. Any ideas? -- I'm using Powerbuilder 5.0.02 32bit Database: Sql Anywhere 5.5 OS: Windows 95 Jan Erik Hermansen Norway -- ------ I'm using Powerbuilder 5.0.02 32bit Database: Sql Anywhere 5.5 OS: Windows 95 Jan Erik Hermansen Norway Jan Erik Hermansen wrote: > I'm having trouble using a stored proc...

Call stored procedure from another stored procedure?
How do I call this stored procedure: CREATE Procedure hentSpecialPris ( @varenr int, @cvr int, @pris decimal(7,2) OUTPUT ) AS DECLARE @antal int SET @antal = 0 SET @antal = (SELECT COUNT(*) FROM rabat WHERE varenr=@varenr and cvr = @cvr) IF @antal < 1 BEGIN SET @pris = (SELECT pris FROM vare WHERE varenr=@varenr) END ELSE BEGIN SET @pris = (SELECT pris FROM rabat WHERE varenr=@varenr and cvr = @cvr) END GO from another stored procedure?? I want to get the output value of the stored procedure? ...

Calling stored procedure from another stored procedure
Is it possible to call one sp from another sp?I've been hunting around for an example to do this and just can't seem to find one.Anyone have a link for this or a sample?Thanks all,Zath Yes, you can. Just use EXEC usp_secondStoredProc @params inside your first SP.Nick...

Running a stored procedure within a stored procedure
I have a stored procedure (A), which I need to call from another stored procedure (B). The data pulled from (B) is then inserted into a temporary table. The problem I am having is the stored procedure is in a loop, and it only runs once. I need it to run each time the statement loops. For example: In the cursor I have, there are 3 rows, so the statement should return 3 rows. However, only 1 row is returned, it looks like the stored procedure is only one once. What do I have to do to get it to loop?? DECLARE dCURSOR CURSOR FOR select ID, value1, value2 from table; OPEN dCU...

Web resources about - Sqlanywhere Stored Procedures - sybase.powerbuilder.general

IBM Tivoli Storage Manager - Wikipedia, the free encyclopedia
IBM Tivoli Storage Manager ( TSM or ITSM ) is a centralized, policy-based, enterprise class, data backup and recovery package. The software enables ...

Archives - Caelum's Blog
Caelum's Blog Random Stuff Navigation Home - Articles Tags 256colors 64 64bit 8 activeperl activestate advent ajax alsa amd64 asa asus automation ...

keynote bingo - Google Search
Search Images Maps Play YouTube News Gmail Drive More Calendar Translate Mobile Books Wallet Shopping Blogger Finance Photos Videos Even more ...

Mobile and Wireless Partners - Partners - Sybase Inc
Thanks for visiting the Partners section of Sybase.com. Here you will find information about Mobile and Wireless Partners - Partners. For more ...

Datensynchronisierung - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Browse file extension list beginning with letter A
Browse file extension list beginning with letter A - File-Extensions.org search page

OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads
OpenLink ODBC Adapter for Ruby on Rails: OpenLink ODBC Adapter for Ruby on Rails: Downloads

Developer Edition - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

IBM - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Datenaustausch - sqlanywhere
„Good Partner - quick and reliable answers! Fast "delivery" by Email. Everybody can count on them." Tímea Steigervald, Product Manager Kvazar-Micro ...

Resources last updated: 1/20/2016 8:24:02 AM