Remote procedure with output parms

This is a multi-part message in MIME format.
--------------000007090100080001060201
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

Hi all!


I'm using SQLAnywhere 11 and MSSQL 2000, and need to call a remote 
procedure with output parameters. So far, I've got declared the remote 
procedure correctly, and when I call it from Interactive SQL says it was 
executed correctly. How can I catch the output values?

I've Attached the sample I'm using.

Thanks in advance.

--------------000007090100080001060201
Content-Type: text/plain;
 name="remote procedure SA11.txt"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="remote procedure SA11.txt"

Original Procedure
**********************************************************************
Create PROCEDURE dbo.sp_get_remote_test
	@ai_ubicacion	smallint	= null out,
	@as_error   	varchar(100)    = null out,
	@ai_cliente	smallint        = null,
	@ai_producto	smallint        = null
as
begin
	Declare @i_rc           tinyint
	select @i_rc = 0, @as_error = ''
	
	if @ai_cliente = null or @ai_producto = null
	Begin
		Print 'valores nulos!'
		select @i_rc = 1, @as_error =  '(0010) SP: Existen datos incorrectos para ejecutar el SP.', 
			@ai_ubicacion = null
	End
	else
	Begin
		Print 'valores default!'
		select @i_rc = 0, @as_error =  '', @ai_ubicacion = 9999  
	End
end   
return @i_rc


Remote Procedure
************************************************************************
ALTER PROCEDURE "dba"."pxy_get_remote_test"( out ai_ubicacion smallint,out as_error char(100),in ai_cliente smallint,in ai_producto smallint ) 
result( int_return smallint,achar_error char(100),aint_ubicacion smallint ) 
at 'My_Server;MYDB;dbo;sp_get_remote_test;1'


Call from ISQL
************************************************************************
CALL "dba"."pxy_get_mejor_ubicacion"( "ai_cliente" = 0, "ai_producto" = 0)

--------------000007090100080001060201--
0
Jose
1/11/2011 4:30:37 PM
sybase.sqlanywhere.general 32637 articles. 4 followers. Follow

3 Replies
714 Views

Similar Articles

[PageSpeed] 26

There are a number of ways you can accomplish what you want, but the 
simplest would be to use variables. Try the following:

CREATE VARIABLE ai_ubicacion smallint;
CREATE VARIABLE as_error varchar(100);
CALL "dba"."pxy_get_mejor_ubicacion"( ai_ubicacion, as_error, 0, 0);

When the call finishes, the output values should be in the ai_ubicacion 
and as_error variables. You can then query the variables as in:

SELECT ai_ubicacion, as_error FROM SYS.DUMMY

or you can use the variables in another procedure call etc.

Note that when you use CREATE VARIABLE, you create a variable that lasts 
for the duration of the connection or until a DROP VARIABLE statement is 
executed. If you would rather have variables stick around for a shorter 
period of time, you might want to consider using DECLARE VARIABLE 
instead and wrap your remote procedure call within a local stored procedure.

HTH,
Karim

On 1/11/2011 11:30 AM, Jose Manuel Espinoza wrote:
> Hi all!
>
>
> I'm using SQLAnywhere 11 and MSSQL 2000, and need to call a remote
> procedure with output parameters. So far, I've got declared the remote
> procedure correctly, and when I call it from Interactive SQL says it was
> executed correctly. How can I catch the output values?
>
> I've Attached the sample I'm using.
>
> Thanks in advance.

0
Karim
1/11/2011 6:56:50 PM
So thats why!

Sorry, I didn't read about Create Variable statement. I tried with the 
same syntax used in ASE, using Declare:


Declare @ai_ubicacion smallint, @as_error char(100)

Call pxy_get.....

Select @ai_ubicacion, @as_error


but there was an error just before the "Call".

Thanks, I'll review  the help files.



El 11/01/2011 12:56 p.m., Karim Khamis [Sybase iAnywhere] escribi�:
> There are a number of ways you can accomplish what you want, but the
> simplest would be to use variables. Try the following:
>
> CREATE VARIABLE ai_ubicacion smallint;
> CREATE VARIABLE as_error varchar(100);
> CALL "dba"."pxy_get_mejor_ubicacion"( ai_ubicacion, as_error, 0, 0);
>
> When the call finishes, the output values should be in the ai_ubicacion
> and as_error variables. You can then query the variables as in:
>
> SELECT ai_ubicacion, as_error FROM SYS.DUMMY
>
> or you can use the variables in another procedure call etc.
>
> Note that when you use CREATE VARIABLE, you create a variable that lasts
> for the duration of the connection or until a DROP VARIABLE statement is
> executed. If you would rather have variables stick around for a shorter
> period of time, you might want to consider using DECLARE VARIABLE
> instead and wrap your remote procedure call within a local stored
> procedure.
>
> HTH,
> Karim
>
> On 1/11/2011 11:30 AM, Jose Manuel Espinoza wrote:
>> Hi all!
>>
>>
>> I'm using SQLAnywhere 11 and MSSQL 2000, and need to call a remote
>> procedure with output parameters. So far, I've got declared the remote
>> procedure correctly, and when I call it from Interactive SQL says it was
>> executed correctly. How can I catch the output values?
>>
>> I've Attached the sample I'm using.
>>
>> Thanks in advance.
>

0
Jose
1/11/2011 10:25:43 PM
AFAIK DECLARE is for usage within a block (BEGIN ... END) or stored 
procedure. So this should work:

BEGIN
  Declare @ai_ubicacion smallint;
  Declare @as_error char(100);
  Call ...
END

Reimer


Jose Manuel Espinoza wrote:
> So thats why!
>
> Sorry, I didn't read about Create Variable statement. I tried with the
> same syntax used in ASE, using Declare:
>
>
> Declare @ai_ubicacion smallint, @as_error char(100)
>
> Call pxy_get.....
>
> Select @ai_ubicacion, @as_error
>
>
> but there was an error just before the "Call".
>
> Thanks, I'll review the help files.
>
>
>
> El 11/01/2011 12:56 p.m., Karim Khamis [Sybase iAnywhere] escribi�:
>> There are a number of ways you can accomplish what you want, but the
>> simplest would be to use variables. Try the following:
>>
>> CREATE VARIABLE ai_ubicacion smallint;
>> CREATE VARIABLE as_error varchar(100);
>> CALL "dba"."pxy_get_mejor_ubicacion"( ai_ubicacion, as_error, 0, 0);
>>
>> When the call finishes, the output values should be in the ai_ubicacion
>> and as_error variables. You can then query the variables as in:
>>
>> SELECT ai_ubicacion, as_error FROM SYS.DUMMY
>>
>> or you can use the variables in another procedure call etc.
>>
>> Note that when you use CREATE VARIABLE, you create a variable that lasts
>> for the duration of the connection or until a DROP VARIABLE statement is
>> executed. If you would rather have variables stick around for a shorter
>> period of time, you might want to consider using DECLARE VARIABLE
>> instead and wrap your remote procedure call within a local stored
>> procedure.
>>
>> HTH,
>> Karim
>>
>> On 1/11/2011 11:30 AM, Jose Manuel Espinoza wrote:
>>> Hi all!
>>>
>>>
>>> I'm using SQLAnywhere 11 and MSSQL 2000, and need to call a remote
>>> procedure with output parameters. So far, I've got declared the remote
>>> procedure correctly, and when I call it from Interactive SQL says it was
>>> executed correctly. How can I catch the output values?
>>>
>>> I've Attached the sample I'm using.
>>>
>>> Thanks in advance.
>>
>

0
R
1/12/2011 10:27:22 AM
Reply:

Similar Artilces:

How to get output parm in the client procedure
Hi,every one: I know how to get output parm of the procedure in sql statment at server side, but do't know hot get it in powerscript at client side. I define a procedure in database: CREATE procedure up_test @arg_out char(40) AS select @arg_out = 'ABC' I declare and exec procedure in powerscript: string ls_out declare lp_out procedure for up_test @arg_out = :ls_out out //(or: @arg_out = :ls_out output) using sqlca; exec lp_out; All is executed successfully , but ls_out didn't get 'ABC',WHY??? My code is not right ? I searched in PB Help and foun...

Stored Procedure Update Output Parms
I am working against ASE 11.9.2 and using Stored Procedure Update properties in PB 7.0.3, Build 10047. I can only receive Output parms from the Stored Proc if I set values into the Output Parms prior to Update, either through script or key entry. What is even stranger is that Output is truncated based on the length of value I put in. For example on a Varchar(70) field, if I only type a single character in the field then I will only get the first character back as set by the stored proc. At this point I am doing a work-around by initializing these output parms to spaces in PB....

SQLAnywhere stored procedure output parameters?
Hello. Is there any way to get the value of output parameters for stored procedures in SQL Anywhere in PowerBuilder? I know the output parameters are supported by the DBMS, because I can get their values in Delphi. Thanks in advance. Francisco Leong Hi, Thanks. I wish I could do something like DECLARE PROCEDURE... with the OUT specifier. Also I need to call stored procedures with output parameters in a dynamic way. I don't know exactly the name of the stored procedure until run-time, but I know about the number of arguments and their types. Is it possible? (Not only for...

DB2 Stored Procedure via ODBC with output parms
PB5.0.02 and DB2.1.2 We have a DB2 stored procedure that is defined in C syntax as: smallint PalletProc ( parm2 char(9), // input parm3 char(9), // output parm4 char(7), // output parm5 char(7), // output parm6 char(7), // output parm7 char(17), // output parm8 char(9), // output parm9 char(14), // output parm10 char(31), // output parm11 char(17), // output parm12 char(3), // output parm13 c...

Remote Procedure Calls to a Stored Procedure
Remote procedure calls to stored procedures don't seem to work on Jaguar. I can set them up in my components like I used to do in client/server, but they don't execute when I make the call. They don't fail either, which is interesting. Is this a Jaguar issue, or an ODBC issue, or what? We are using ODBC to connect to Informix 7.32 from E.A. Server 3.0.1. Thanks in advance for any input you have. Karl Karl Werner wrote: > Remote procedure calls to stored procedures don't seem to work on Jaguar. I can > set them up in my components like I used to do in...

How to call one procedure output in another procedure
hi, this is my procedure,DELIMITER $$DROP PROCEDURE IF EXISTS `ctsdb`.`LastInsertedId`$$CREATE DEFINER=`karthik`@`%` PROCEDURE `LastInsertedId`( in strTable varchar(50),in fieldanme varchar(50))BEGINSET @dyn_sql=CONCAT('Select max(', fieldanme, ') from ',strTable); PREPARE stmt FROM @dyn_sql;EXECUTE stmt;END$$DELIMITER ;this will give output as max of value. so i would like to use this procedure output in another procedure, i don't know how to use please can anybody give some ida with example making impossible into possible is possible when we have determination,dedica...

how to fill a DataTable from a stored procedure that outputs a cursor as output
I have a procedure that query the database and outputs result as a cursor output as follows: create or replace procedure Ihale_AktifSirketler2(ihlkodu in ihale_katilimcilar.ihlkodu%TYPE, ihlaltktgkodu in ihale_katilimcilar.ihlaltktgkodu%TYPE, cur out sys_refcursor)isBEGIN OPEN cur FOR select max(decode(rn,1,teklif)) teklif,max(decode(rn,1,tarih)) tarih,max(decode(rn,1,ihlkodu)) ihlkodu,max(decode(rn,1,ihlaltktgkodu)) ihlaltktgkodu,kuladi, max(decode(rn,1,ad)) adfrom (select teklif, tarih, ihlkodu, ihlaltktgkodu, kuladi, ad,row_number() over (partition by kuladi order by to_date(tarih,'...

Remote-Server
Hello ASA 9.02 - Build 3124 i have following problem with remote-servers: in my first db i have stored all users, that are in the application work's - all these users are in the group "AppUser"; now i have add a external login for the group "appUser" to the remote-server. if i select with the appUser-Login, i can access the tables and procedures from the remote-server - BUT if i connect with a normal user from the group "AppUser" ---> i cant' access any object. is it possible to create for every user in the first DB - an external login...

Remote Procedures
We are currently evaluating version 12 (from 10)of asa, the reason we would upgrade is the ability to perform remote procedures with parameters from a sql server. I'm still getting unsupported data type errors from the procedure even though the parameters are all supported. Bring in a new remote procedure it doesn't have the parameters. Any ideas? Thanks We need some more information before we can help you. Do you know which side is giving the unsupported data type error (i.e. is it the local SA server or the remote MS SQL Server)? Also what does "bring in new rem...

OUTPUT in procedures
I get a syntax error when running this. What could be wrong? alter procedure smsdba.EXPORT_ESCROW(in COMP_NAME varchar(1),in ESCROW_NUM varchar(20),in FFILE varchar(1000)) begin call xp_write_file(FFILE || 'reload.sql','test;test2;test3;'); select* from SMSDBA.ADJUSTMENTS where ESCROW_NO = ESCROW_NUM and COMPANY = COMP_NAME; OUTPUT TO FFILE DELIMITED BY '^'; --syntax error here end OUTPUT is a DBISQL only command. In the database, you can use UNLOAD TABLE, or in 7.0 UNLOAD 'Select stmt' -- Jason Hinsperger International and Sustai...

What generals generally do
Reading a McChrystal thread elsewhere, I came across someone quoting a US general http://en.wikipedia.org/wiki/Smedley_Butler > I spent 33 years and four months in active military service and > during that period I spent most of my time as a high class thug for > Big Business, for Wall Street and the bankers. In short, I was a > racketeer, a gangster for capitalism. I helped make Mexico and > especially Tampico safe for American oil interests in 1914. I helped > make Haiti and Cuba a decent place for the National City Bank boys to > collect revenues in. I ...

how to get stored procedure identity or procedure output from asp.net
hi guys i have problem to retrieve the output of my procedure in asp.net c# please help me:( this is procedure CREATE PROCEDURE [dbo].[ekleleman] (@ad varchar(50), @bolum varchar(50), @maas varchar(50), @sicilno int output) AS BEGIN insert into eleman (ad,bolum,maas) values(@ad,@bolum,@maas ) SET @sicilno = @@identity END and this is c# code protected void Button1_Click(object sender, EventArgs e) { SqlConnection baglanti = new SqlConnection("Data Source=FIRATAK-PC\\SQLEXPRESS;Initial Catalog=deneme;Persist Security Info=True;User ID=s...

Call stored procedure with output variable shows Return parameters output
I have an sql script that calls a stored procedure with an output variable. The script is called through isql and has an output file. Every time the stored procedure is called it writes: "Return parameters:" and the output parameters value. The stored procedure is called in a cursor and runs millions of times, I would like to turn off this "Return parameters" output. Does anyone know a way to do this? Is there an isql parameter? More recent versions of isql offer: set proc_return_status on/off set proc_output_params on/off <michael.spivack@us.ing....

Using ADO.NET Execute Second Stored Procedure Depending of Output of First Stored Procedure
When a user logs onto the app, I capture their UserId, today's date and thier WINNT using ADO.NET calling a stored procedue-no problem. Now, once the above mention fields have been populated, when the same user logs on again and this time click their daily attendance time, I need to execute and update stored procedure-no problem. Each time the user logs onto the attendance page, they need to be able to view the previuos time stamps, on page load.- no problem The problem is that I cannot get all three to work in concert, although they will work independently, somewhat. Sotred proc...

Return value from a MSSQL Server procedure using a remote procedure call (RPC) fails in pb 7
Hi all, After upgrading to pb 7 (c4 release) I get a zero return value from a remote procedure call to a sql server 7.0 stored procedure. The same application works great on PB 6.0 . Any ideas?. The procedure is used to generate a new key value and is used throughout the application. (Return (max(some value ) + 1) the rpc function is defined to accept a return value of long the call to the RPC is long ll_ret ll_ret = sqlca.of_getsystemnumber() This returns the value in 6.0 but returns 0 in 7.0 Thanks, Jude ...

a question about procedure parm ?
hello everyone! I want to know which procedure parm is output type ? how I do? I know syscolumns have procedure parm formation , but I don't know which parm is output type ? wait for your reply! In ASE 12.5 the syscolumns.status2 field indicates whether a parameter is input, output, or input/output. (0x1,0x2, 0x4, respectively). In prior versions of ASE there was no easy way to tell if a parameter was declared for input or output, you had to read the contents of syscomments (or sp_helptext output) to find out. -bret devid wrote: > > hello every...

Remote stored procedures
Hi, I have a problem with the access to the Paste special -> SQL - > Remote stored procedures. It's disabled and I'm not allowed to choose it. How can I enable the Remot stored procedures. Thanks Allan Allan, Not sure BUT, I declare my RPCs as local external function in the Transaction object (using the RPCFUNC keyword) this allows me to put all declation for RPCs in one place. Now, when I enable AutoScripting, I get the list of my RPCs in the AutoScript window (I name them using the prefix "rpc_").. Didn't quite answer your question, but I...

Execute procedure remote
Hello, I am creating a website for work. We have MS SQL server 2005 running on a server.Several times a month we need to run a couple of stored procedures (we have 1 SP where all calls are grouped in), and it takes about 1 to 2 hours to complete.Now my question is: how can I call a stored procedure remote, meaning: when I close my webpage it wont stop running.Further, if I am working at home and log in again after 30min, I can check the status of my SP (I populate a table whenever I call a sub SP). Can somebody help me? Forgive me for my bad english :/ thanx in advance A...

Remote Procedure Call
Am trying to execute an RPC which keeps failing. Please see message below: 1> GLUE...sp_who 2> go Msg 7211, Level 18, State 1: Server 'MAXDB1P_SQL', Line 1: Can't open a connection to site 'GLUE' because 'set up channel' message was not acknowledged. The interfaces file on both local and remote servers are set up correctly and the "timeouts" option on the local server has been set to "false". All other checks suggested by the Sybase guides have been done but the problem persists. Any help is appreciated. Thank...

Remote Procedure Calls
Folks, Is there any way to determine the identity (hopefully the name) of the server that initiated a remote procedure call when the procedure is being executed on the remote server? e.g. on SV1 do exec SV2.db1.dbo.proc1 and in proc1 do something like select @@rpcsourceservername and return SV1. There is nothing in SV2's sysprocesses that indicates that the connection is being made as an RPC. If there was some way to match the RPC's connection to the site handler's connection I could find it out that way but nothing obvious presents itself. ASE...

ORACLE REMOTE PROCEDURE
Hi There, it is me again, with another issue in ORACLE. This time is the turn of REMOTE procedure in ORACLE 10g called from Sybase 9.0.2.3609 . I am calling a remote procedure to RETRIVE some data from oracle and I have tested it with both ODBC Drivers: - I Anywhere 9 : version 10.00.01.3605 - I Anywhere 10 : version 5.10.00.124 At the end of the email you can find a test case to run in ORACLE and in Sybase. The error I get is CASE A) a randomic value for the return parameter in ODBC driver version 9. The documentation says the CHAR Type can host a Unicode charset (eventu...

Remote Stored Procedure
I have a Remote Stored Procedure in Oracle that recibes a blob by reference. In Oracle the declaration of the Procedure is: create or replace function sfg(ab_comprimir NUMBER, as_procedimiento STRING, al_entity_id NUMBER, as_blob OUT RAW) RETURN NUMBER; In Power Builder the definition of the stored Procedure is: function double SFG(double AB_COMPRIMIR,string AS_PROCEDIMIENTO,double AL_ENTITY_ID,ref blob AS_BLOB) RPCFUNC ALIAS FOR "DBA1.SFG" Why if the blob is OUT Power Builder pass to Oracle the size total of the blob? I.e. : If I a have a declaration of blob in...

Output from Stored Procedure
Hi, Can someone remind me how to peform a similar operation to OUTPUT from a stored procedure? ASA 7.04, Windows thanks Ray Norrish wrote: > Hi, > > Can someone remind me how to peform a similar operation to OUTPUT from a > stored procedure? > ASA 7.04, Windows > > thanks > > Have a look at the UNLOAD command, which is an engine command used to dump result sets to text files. OUTPUT is a DBISQL command and cannot be used in stored procedures. -- Reg Domaratzki, Sybase iAnywhere Solutions Please reply only to the newsgrou...

Remote Procedure Call
I migrated an application from PB5 to PB6. However, when trying to call a stored procedure (SQLAnywhere) using the format SQLCA.p_xxx() I receive the following error: Error: Database interface does not support Remote Procedure Calls I'm sure that this isn't a feature that's been lost with PB6; so can anyone tell me why I'm now getting this error? --Dave Check that DisableBind=0 and also that PBSupportDBBind='YES' in the SQLAnywhere section of the PBODB60.INI file. That file must be in the SAME directory as the PBODB60.DLL that is lo...

Web resources about - Remote procedure with output parms - sybase.sqlanywhere.general

Federal Rules of Bankruptcy Procedure - Wikipedia, the free encyclopedia
The Federal Rules of Bankruptcy Procedure (abbreviated Fed. R. Bankr. P. or FRBP ) are a set of rules promulgated by the Supreme Court of the ...

Eye tattoos: Tattooist Luna Cobra defends practice following calls to ban procedure
IT SOUNDS like the really gross scene in a horror movie.

Rushed cosmetic procedures a 'recipe for disaster'
&#8203;When Chanelle O'Hare went searching online for a deal on potential cosmetic procedures, she could not have imagined that what she ended ...

Jetstar procedures under investigation after planes took off too heavy
Jetstar's procedures for calculating the weight of its aircraft are under review by the Australian Transport Safety Bureau after two of its planes ...

Survivors of female genital mutilation outraged by 'compromise' which could legalise procedure
Survivors of female genital mutilation are fighting back against a so-called compromise, which could let a modified form of the procedure continue. ...

Doctors Perform Medical Procedure On Wrong Newborn
The procedure was meant for another child.

Old Lady Lawyer: Uncivil Procedure
What is some of the worst behavior you've witnesses by attorneys?

NFL Announces Changes to Officiating Procedures for Playoffs - Bleacher Report
The NFL formally approved changes to its postseason officiating procedures to allow referees the opportunity to consult Vice President of Officiating ...

We need to reform the culture of law enforcement, not just the procedures
We need to reform the culture of law enforcement, not just the procedures by digby I have a new piece up at Salon this morning about police ...

Rescued tiger cub "Himmel" dies during medical procedure - CBS News 8 - San Diego, CA News Station ...
Alpine animal rescue sanctuary Lions, Tigers & Bears is sadly reporting Himmel, the tiger cub found roaming the streets of Hemet and rescued ...

Resources last updated: 2/29/2016 12:34:02 AM