Setting Remote Procedure Call
Tags:
This doc talk about how to set up RPC to other server, when RPC is embedded with in begin … end block. When Remote procedure is
created, engine has no knowledge of what Remote Procedure does and therefore It cannot implicitly define result set for Remote Procedure.
If Remote Procedure is executed using "call foo(...)" in DBISQL or dbisqlc, the application will open a cursor on the call statement
and the proper "cursor describe stuff" will automatically happen. BUT, if you attempt to execute a "call foo(...)" within a procedure
or batch, the engine does not automatically open a cursor on the call. Instead, it is up to the user to explicitly force a cursor to be opened either by
declaring a cursor within the stored procedure or batch or using the RPC in the from clause of a select statement. Furthermore, if the RPC is going
to be used in the from clause, then the RPC *MUST* be created with a RESULT clause in order to allow the engine to predefine the schema
of the expected result set.
STEPS:
Add source and target SAP Sybase IQ server entries in the interfaces. Add $ODBCINI and $ODBCHOME environment variables in $SYBASE/IQ-15_4/IQ-15_4.csh and then source to setup environment variables
- Create remote server
CREATE SERVER "iqdemo1543" CLASS 'SAODBC' USING 'dsn=iqdemo1543';
2. Create Externlogin
CREATE EXTERNLOGIN "DBA" TO "iqdemo1543" REMOTE LOGIN "DBA" IDENTIFIED BY 'sql';
3. Create Remote Procedure:
create "DBA"."remwho"()
result(
ConnHandle unsigned bigint,
IQconnID unsigned bigint,
Userid varchar(255),
ReqType varchar(255),
IQCmdType char(32),
BlockedOn unsigned bigint,
BlockUserid varchar(255),
IQCursors unsigned integer,
IQThreads unsigned integer,
IQIdle integer,
SAIdle integer,
TempTableSpaceKB unsigned bigint,
TempWorkSpaceKB unsigned bigint )
at 'iqdemo1543...sp_iqwho'
4. Execute Remote Procedure
begin select * from remwho() end
ConnHandle IQconnID Userid
ReqType
IQCmdType BlockedOn BlockUserid
IQCursors
IQThreads IQIdle SAIdle TempTableSpaceKB TempWorkSpaceKB
--------------------------------------------------------------------------------
-----------------------------------------------------------------------
23 5625 DBA
OPEN
IQUTILITYOPENCURSOR 0 (NULL)
0
1
0 0 0 0
(1 rows)
Execution time: 0.025 seconds