SQL Anywhere Mobile - upload data and Java sync scripts

Author

upload data and Java sync scripts

Brent Williams

2005-05-11, 11:24 am

I'm running V9.0.2.3044

Here's the deal...I want to take data uploaded for a particular table
and insert it into 2 tables. I thought the easiest way would be to
write a java upload script for that table and insert into one table
using java and return the sql string to allow mobilink to do the other
insert. The problem I'm having is that I can't figure out how to get
hold of the data being upload on a per row basis. I can't use a trigger
because I only want to do this on an upload from mobilink. Any ideas???

Thanks

Reg Domaratzki $iAnywhere Solutions$

2005-05-11, 1:24 pm

You don't have access to per-row values in the Java upload scripts.

Your best bet at this point would be to use forced conflicts on that table
to insert the new_row and old_row values uploaded into a holding table. In
the end_upload table script, call a stored procedure that does whatever you
want with the data. For example, instead of :

create table t1 (pkey bigint defaut global autoincrement primary key, c1
varchar(20));
call ml_add_table_script(
'v1','t1','upload_in
sert','insert into t1 values
( ?,? )');
call ml_add_table_script(
'v1','t1','upload_up
date','update t1 set c1=?
where pk=?');
call ml_add_table_script(
'v1','t1','upload_de
lete','delete from t1 where
pk=?');

You could do :

create table t1 (pkey bigint defaut global autoincrement primary key, c1
varchar(20));
create global temporary table gtt_t1 (pkey bigint, row_type char(1), c1
varchar(20), primary key (pkey,row_type));
call ml_add_table_script(
'v1', 't1', 'upload_old_row_inse
rt', 'insert into
gtt_t1 values ( ?, ''O'', ? )' );
call ml_add_table_script(
'v1', 't1', 'upload_new_row_inse
rt', 'insert into
gtt_t1 values ( ?, ''N'', ? )' );
call ml_add_table_script(
'v1', 't1', 'end_upload', 'call end_upload_t1()');

create procedure end_upload_t1 ()
begin
DECLARE CurInsert CURSOR FOR
SELECT pkey,c1 FROM gtt_t1
WHERE row_type = 'N'
AND pkey NOT IN (SELECT pkey FROM gtt_t1 WHERE row_type = 'O');
DECLARE CurUpdate CURSOR FOR
SELECT pkey,c1 FROM gtt_t1
WHERE row_type = 'N'
AND pkey IN (SELECT pkey FROM gtt_t1 WHERE row_type = 'O');
DECLARE CurDelete CURSOR FOR
SELECT pkey,c1 FROM gtt_t1
WHERE row_type = 'O'
AND pkey NOT IN (SELECT pkey FROM gtt_t1 WHERE row_type = 'N');
DECLARE cpkey INTEGER;
DECLARE cpkey_old INTEGER;
DECLARE cc1 VARCHAR(20);
DECLARE cc1_old VARCHAR(20);
OPEN CurInsert;
FETCH FIRST CurInsert INTO cpkey, cc1;
WHILE ( sqlcode = 0 ) LOOP
// Do whatever you want with the
FETCH NEXT CurInsert INTO cpkey,cc1;
END LOOP;
CLOSE CurInsert;
OPEN CurUpdate;
FETCH FIRST CurUpdate INTO cpkey, cc1;
WHILE ( sqlcode = 0 ) LOOP
SELECT pkey,c1 INTO cpkey_old, cc1_old
FROM gtt_t1 WHERE pkey=cpkey AND row_type = 'O';
// Do what you want with the updates, you have the old and new values.
FETCH NEXT CurUpdate INTO cpkey,cc1;
END LOOP;
CLOSE CurUpdate;
OPEN CurDelete;
FETCH FIRST CurDelete INTO cpkey, cc1;
WHILE ( sqlcode = 0 ) LOOP
// Do what you want with the deletes
FETCH NEXT CurDelete INTO cpkey,cc1;
END LOOP;
CLOSE CurDelete;
END;

I haven't checked the above for typos and syntax, but you should get the
general idea. Just make sure you understand how the three cursors in the
end_upload_t1 stored procedure gather the information you need to re-create
the inserts, updates and deletes.

--
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

"Brent Williams" < brentwilliams@bcbsal
.org> wrote in message
news:428235ef$1@foru
ms-1-dub...
> I'm running V9.0.2.3044
>
> Here's the deal...I want to take data uploaded for a particular table
> and insert it into 2 tables. I thought the easiest way would be to
> write a java upload script for that table and insert into one table
> using java and return the sql string to allow mobilink to do the other
> insert. The problem I'm having is that I can't figure out how to get
> hold of the data being upload on a per row basis. I can't use a trigger
> because I only want to do this on an upload from mobilink. Any ideas???
>
> Thanks

David Fishburn

2005-05-13, 3:24 am

"Reg Domaratzki $iAnywhere Solutions$"
< Spam_bad_rdomarat@ia
nywhere.com> wrote in news:428247b7@forums
-2-dub of
sybase.public.sqlanywhere.mobilink:

> You don't have access to per-row values in the Java upload scripts.
>
> Your best bet at this point would be to use forced conflicts on that
> table to insert the new_row and old_row values uploaded into a holding
> table. In the end_upload table script, call a stored procedure that
> does whatever you want with the data. For example, instead of :

Why not just call a stored procedure in the upload_insert (or
upload_update) event.

The stored procedure can then perform the insert into both tables.

No need for forced conflicts or Java logic.

--
David Fishburn
Certified ASA Developer Version 8
iAnywhere Solutions - Sybase
Professional Services
Please only post to the newsgroup
Please ALWAYS include version and MORE importantly BUILD number with
EACH post (dbeng9 -v).

EBFs and Maintenance Releases
http://downloads.sybase.com/swx/sdmain.stm

Developer Community / Whitepapers
http://www.ianywhere.com/developer

CaseXpress - to report bugs
http://casexpress.sybase.com

CodeXchange - Free samples
[url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]

Reg Domaratzki $iAnywhere Solutions$

2005-05-13, 11:24 am

Yes, of course Dave's idea is much simpler. It was likely the cold
medication convincing me that forced conflicts were the way to go.

--
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

"David Fishburn" <fishburn_spam@off.ianywhere.com> wrote in message
news:Xns9654ECF678B5
1fishburnsybasecom@1
27.0.0.1...
> "Reg Domaratzki $iAnywhere Solutions$"
> < Spam_bad_rdomarat@ia
nywhere.com> wrote in news:428247b7@forums
-2-dub of
> sybase.public.sqlanywhere.mobilink:
>
>
> Why not just call a stored procedure in the upload_insert (or
> upload_update) event.
>
> The stored procedure can then perform the insert into both tables.
>
> No need for forced conflicts or Java logic.
>
> --
> David Fishburn
> Certified ASA Developer Version 8
> iAnywhere Solutions - Sybase
> Professional Services
> Please only post to the newsgroup
> Please ALWAYS include version and MORE importantly BUILD number with
> EACH post (dbeng9 -v).
>
> EBFs and Maintenance Releases
> http://downloads.sybase.com/swx/sdmain.stm
>
> Developer Community / Whitepapers
> http://www.ianywhere.com/developer
>
> CaseXpress - to report bugs
> http://casexpress.sybase.com
>
> CodeXchange - Free samples
> [url]http://ianywhere.codexchange.sybase.com/servlets/ ProjectDocumentList[
/url]
>

David Fishburn

2005-05-16, 7:24 am

"Reg Domaratzki $iAnywhere Solutions$" < Spam_bad_rdomarat@ia
nywhere.com>
wrote in news:4284c47e$1@foru
ms-1-dub
of sybase.public.sqlanywhere.mobilink:

RD> Yes, of course Dave's idea is much simpler. It was likely the cold
RD> medication convincing me that forced conflicts were the way to go.

Sounds like some good stuff, can I borrow some :-)

Sponsored Links