SQL Anywhere Mobile - execution of store procedure in events of the mobilink 9.0.2 using consolidated database Oracle 9i

Author

execution of store procedure in events of the mobilink 9.0.2 using consolidated database Oracle 9i

Luis Ponce Ibarra - Hotmail

2005-07-22, 9:26 am

I am using oracle 9i as consolidated database and sybase anywhere 9.0.2.
I have some very big sentences that cannot be recorded in the events
donwload_cursor. I have read that one can make sending the sentences select
to store procedure using packages and some things but. I have carried out it
using sql server like consolidated database and I have not had problem but
one doesn't eat to make it with Oracle.
This is also due to my little experience with Oracle.
Somebody could give the instructions of as making him and if it was not so
much nuisance an example of a small chart of 2 fields of as managing it with
store procedure he/she would thank it

Reg Domaratzki $iAnywhere Solutions$

2005-07-22, 11:27 am

Here's a very simple example that uses a stored procedure call in Oracle as
a download cursor. It is CRITICAL that you modify your DSN an make sure
that the "Procedure Returns Result Sets" check box is selected on the
Advanced tab of the ODBC DSN setup dialog. Also note the ODBC calling
convention for stored procedures when I define the download cursor.

CREATE TABLE t2 (
pkey NUMBER(16) PRIMARY KEY,
c1 DATE
)
/

CREATE OR REPLACE PACKAGE t2_pkg
AS
Type t2_rec is record (
r_pkey NUMBER(16),
r_c1 DATE
);
TYPE t2_cursor IS REF CURSOR RETURN t2_rec;
END t2_pkg;
/

CREATE OR REPLACE PROCEDURE t2_download_cursor (
inout_t2_cursor IN OUT t2_pkg.t2_cursor,
lst_download_timesta
mp IN date,
user_id IN varchar
)
AS
BEGIN
OPEN inout_t2_cursor for select pkey,c1 from t2;
END;
/

call ml_add_table_script(
'v2','t2','download_
cursor',
'{call t2_download_cursor( ?,? )}'
)
/

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in message
news:42e0fe52@forums
-2-dub...
> I am using oracle 9i as consolidated database and sybase anywhere 9.0.2.
> I have some very big sentences that cannot be recorded in the events
> donwload_cursor. I have read that one can make sending the sentences
select
> to store procedure using packages and some things but. I have carried out
it
> using sql server like consolidated database and I have not had problem but
> one doesn't eat to make it with Oracle.
> This is also due to my little experience with Oracle.
> Somebody could give the instructions of as making him and if it was not so
> much nuisance an example of a small chart of 2 fields of as managing it
with
> store procedure he/she would thank it
>
>
>

Luis Ponce Ibarra - Hotmail

2005-07-22, 8:24 pm

I have executed that indicated and it leaves me the following error:

E. 07/22 20:31:28. <1.4> [OMTECH0001]: Error: [-10002] ODBC:
[DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1,
column 8:
PLS-00306: wrong number or types of arguments in call to
'RTRACK_DC_BANCO'
ORA-06550: line 1, column 8:
PL/SQL: Statement ignored (ODBC State = HY000, Native
error code = 6550)
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Error Context:
I. 07/22 20:31:28. <1.4> [OMTECH0001]: User Name: OMTECH0001
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Modified User Name: OMTECH0001
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Transaction: download
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Table Name: BANM_ENTIDAD
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Script Version: default
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Script: {call RTRACK_DC_BANCO (?, ?)}

I. 07/22 20:31:28. <1.4> [OMTECH0001]: End of Error Context
I. 07/22 20:31:28. <1.4> [OMTECH0001]: ROLLBACK Transaction: end_download
I. 07/22 20:31:28. <1.4> [OMTECH0001]: Synchronization failed

----------------------------------------------

what carries out is the following thing:

CREATE TABLE BANM_ENTIDAD
(
GENCIA_codigo char(4) NOT NULL,
BANENT_tipo char(2) NOT NULL,
BANENT_codigo char(3) NOT NULL,
BANENT_nombre varchar2(100) NOT NULL,
CONSTRAINT PK_BANM_ENTIDAD PRIMARY KEY(GENCIA_codigo, BANENT_tipo,
BANENT_codigo)
)
/

create or replace package PCK_DC_BANCO as
Type DCBANCO_REC is record (
R_GENCIA_codigo CHAR(4),
R_BANENT_tipo CHAR(2),
R_BANENT_codigo CHAR (3),
R_BANENT_nombre VARCHAR(100)
);
TYPE DCBANCO_CURSOR IS REF CURSOR RETURN DCBANCO_REC;
End PCK_DC_BANCO;
/

CREATE OR REPLACE PROCEDURE RTRACK_DC_BANCO (
rset IN OUT PCK_DC_BANCO.DCBANCO_CURSOR, lastdate IN date, idpalm IN
varchar ) IS
BEGIN

open rset for
SELECT GENCIA_codigo, BANENT_tipo, BANENT_codigo, BANENT_nombre FROM
BANM_ENTIDAD
where --GENCIA_codigo = @gencia_codigo AND
BANENT_estado = 'A'
AND BANENT_tipo = 'BC';

End RTRACK_DC_BANCO;
/

THE ONLY THING DIFFERENT THAT CARRIES OUT IT WAS THE DEFINITION OF THE
EVENT. I MADE IT FOR THE SYBASE CENTRAL ADDING THE TABLE, THE VERSION AND
THE EVENT AND WRITING

{call RTRACK_DC_BANCO (?, ?)}

I proved it with key -{}- and without key and I obtained the same result
I don't believe that it affects the one that has not created it with the
sentence that you/they gave me.

you could help me indicating because it leaves error

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
escribió en el mensaje news:42e10cca$1@foru
ms-2-dub...
> Here's a very simple example that uses a stored procedure call in Oracle
as
> a download cursor. It is CRITICAL that you modify your DSN an make sure
> that the "Procedure Returns Result Sets" check box is selected on the
> Advanced tab of the ODBC DSN setup dialog. Also note the ODBC calling
> convention for stored procedures when I define the download cursor.
>
> CREATE TABLE t2 (
> pkey NUMBER(16) PRIMARY KEY,
> c1 DATE
> )
> /
>
> CREATE OR REPLACE PACKAGE t2_pkg
> AS
> Type t2_rec is record (
> r_pkey NUMBER(16),
> r_c1 DATE
> );
> TYPE t2_cursor IS REF CURSOR RETURN t2_rec;
> END t2_pkg;
> /
>
> CREATE OR REPLACE PROCEDURE t2_download_cursor (
> inout_t2_cursor IN OUT t2_pkg.t2_cursor,
> lst_download_timesta
mp IN date,
> user_id IN varchar
> )
> AS
> BEGIN
> OPEN inout_t2_cursor for select pkey,c1 from t2;
> END;
> /
>
> call ml_add_table_script(
'v2','t2','download_
cursor',
> '{call t2_download_cursor( ?,? )}'
> )
> /
>
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in
message
> news:42e0fe52@forums
-2-dub...
> select
out[color=darkred]
> it
but[color=darkred]
so[color=darkred]
> with
>
>

Luis Ponce Ibarra - Hotmail

2005-07-22, 8:24 pm

if I use in the event download_cursor the same sentence of the cursor but
without point and coma -; - it works well, without problems
I attach it sentences:

SELECT GENCIA_codigo, BANENT_tipo, BANENT_codigo, BANENT_nombre FROM
BANM_ENTIDAD
where --GENCIA_codigo = @gencia_codigo AND
BANENT_estado = 'A'
AND BANENT_tipo = 'BC'

"Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> escribió en el
mensaje news:42e19f78$1@foru
ms-1-dub...
> I have executed that indicated and it leaves me the following error:
>
> E. 07/22 20:31:28. <1.4> [OMTECH0001]: Error: [-10002] ODBC:
> [DataDirect][ODBC Oracle Wire Protocol driver][Oracle]ORA-06550: line 1,
> column 8:
> PLS-00306: wrong number or types of arguments in call
to
> 'RTRACK_DC_BANCO'
> ORA-06550: line 1, column 8:
> PL/SQL: Statement ignored (ODBC State = HY000, Native
> error code = 6550)
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Error Context:
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: User Name: OMTECH0001
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Modified User Name: OMTECH0001
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Transaction: download
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Table Name: BANM_ENTIDAD
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Script Version: default
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Script: {call RTRACK_DC_BANCO (?,
?)}
>
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: End of Error Context
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: ROLLBACK Transaction: end_download
> I. 07/22 20:31:28. <1.4> [OMTECH0001]: Synchronization failed
>
> ----------------------------------------------
>
> what carries out is the following thing:
>
> CREATE TABLE BANM_ENTIDAD
> (
> GENCIA_codigo char(4) NOT NULL,
> BANENT_tipo char(2) NOT NULL,
> BANENT_codigo char(3) NOT NULL,
> BANENT_nombre varchar2(100) NOT NULL,
> CONSTRAINT PK_BANM_ENTIDAD PRIMARY KEY(GENCIA_codigo, BANENT_tipo,
> BANENT_codigo)
> )
> /
>
> create or replace package PCK_DC_BANCO as
> Type DCBANCO_REC is record (
> R_GENCIA_codigo CHAR(4),
> R_BANENT_tipo CHAR(2),
> R_BANENT_codigo CHAR (3),
> R_BANENT_nombre VARCHAR(100)
> );
> TYPE DCBANCO_CURSOR IS REF CURSOR RETURN DCBANCO_REC;
> End PCK_DC_BANCO;
> /
>
> CREATE OR REPLACE PROCEDURE RTRACK_DC_BANCO (
> rset IN OUT PCK_DC_BANCO.DCBANCO_CURSOR, lastdate IN date, idpalm IN
> varchar ) IS
> BEGIN
>
> open rset for
> SELECT GENCIA_codigo, BANENT_tipo, BANENT_codigo, BANENT_nombre FROM
> BANM_ENTIDAD
> where --GENCIA_codigo = @gencia_codigo AND
> BANENT_estado = 'A'
> AND BANENT_tipo = 'BC';
>
> End RTRACK_DC_BANCO;
> /
>
> THE ONLY THING DIFFERENT THAT CARRIES OUT IT WAS THE DEFINITION OF THE
> EVENT. I MADE IT FOR THE SYBASE CENTRAL ADDING THE TABLE, THE VERSION AND
> THE EVENT AND WRITING
>
> {call RTRACK_DC_BANCO (?, ?)}
>
>
> I proved it with key -{}- and without key and I obtained the same result
> I don't believe that it affects the one that has not created it with the
> sentence that you/they gave me.
>
> you could help me indicating because it leaves error
>
>
>
> "Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
> escribió en el mensaje news:42e10cca$1@foru
ms-2-dub...
> as
> http://www.ianywhere.com/developer/product_manuals
> message
9.0.2.[color=darkred]
> out
> but
not[color=darkred]
> so
it[color=darkred]
>
>

Reg Domaratzki $iAnywhere Solutions$

2005-07-25, 7:34 am

Did you check off the box in the ODBC DSN to allow the procedure to return
result sets?

> It is CRITICAL that you modify your DSN an make sure
> that the "Procedure Returns Result Sets" check box is selected on the
> Advanced tab of the ODBC DSN setup dialog.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Sybase Certified Professional - Sybase ASA Developer Version 8
Please reply only to the newsgroup

iAnywhere Developer Community : http://www.ianywhere.com/developer
iAnywhere Documentation : http://www.ianywhere.com/developer/product_manuals
ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL

"Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in message
news:42e1a1e3@forums
-1-dub...
> if I use in the event download_cursor the same sentence of the cursor but
> without point and coma -; - it works well, without problems
> I attach it sentences:
>
> SELECT GENCIA_codigo, BANENT_tipo, BANENT_codigo, BANENT_nombre FROM
> BANM_ENTIDAD
> where --GENCIA_codigo = @gencia_codigo AND
> BANENT_estado = 'A'
> AND BANENT_tipo = 'BC'
>
>
>
> "Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> escribió en el
> mensaje news:42e19f78$1@foru
ms-1-dub...
> to
> ?)}
end_download[color=d
arkred]
AND[color=darkred]
result[color=darkred
]
<FirstName.LastName@ianywhere.com>[color=darkred]
Oracle[color=darkred
]
sure[color=darkred]
> 9.0.2.
carried[color=darkre
d]
problem[color=darkre
d]
> not
> it
>
>

Luis Ponce Ibarra - Hotmail

2005-07-26, 8:25 pm

In the ODBC DSN the parameter "Procedure return result" I have it
fastened -ON-. Should this parameter be active or out?

"Reg Domaratzki (iAnywhere Solutions)" <FirstName.LastName@ianywhere.com>
escribió en el mensaje news:42e4de6b$1@foru
ms-1-dub...
> Did you check off the box in the ODBC DSN to allow the procedure to return
> result sets?
>
>
> --
> Reg Domaratzki, Sybase iAnywhere Solutions
> Sybase Certified Professional - Sybase ASA Developer Version 8
> Please reply only to the newsgroup
>
> iAnywhere Developer Community : http://www.ianywhere.com/developer
> iAnywhere Documentation :
http://www.ianywhere.com/developer/product_manuals
> ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
> -> Choose SQL Anywhere Studio
> -> Set "Platform Preview" and "Time Frame" to ALL
>
> "Luis Ponce Ibarra - Hotmail" < luisponceibarra@hotm
ail.com> wrote in
message
> news:42e1a1e3@forums
-1-dub...
but[color=darkred]
el[color=darkred]
1,[color=darkred]
call[color=darkred]
Native[color=darkred
]
(?,[color=darkred]
> end_download
FROM[color=darkred]
> AND
> result
the[color=darkred]
> <FirstName.LastName@ianywhere.com>
> Oracle
> sure
the[color=darkred]
calling[color=darkre
d]
events[color=darkred
]
sentences[color=dark
red]
> carried
> problem
was[color=darkred]
managing[color=darkr
ed]
>
>

Sponsored Links