SQL Anywhere Mobile - Uploading into temp table rather than original table?

Author

Uploading into temp table rather than original table?

Bob

2006-04-04, 9:32 am

I read a post from Greg Fenton in November 2005 regarding uploading into a
temp table rather than the original table. I have a table that I want any
new or modified rows in the remote database to be redirected to a temp table
for processing; I do not want these records in the remote database to be
inserted/updated in its respective consolidated table. I am fairly new to
mobilink and I do not know what combination of scripts need to be
modified/created in order to handle this.

TIA,

Bob

Reg Domaratzki $iAnywhere Solutions$

2006-04-04, 9:32 am

Here's a sample that uses forced conflicts to insert rows into a global
temporary table during upload, and then calls a stored procedure that opens
cursor on the rows in that table to distinguish between inserts, updates and
deletes from the remote. The stored procedure goes on to actually perform
the inserts, updates and deletes, but you can do whatever you want at that
point in the stored procedure.

==
== On the remote
==

create table Admin (
admin_id integer default global autoincrement primary key,
data varchar(30)
);
CREATE PUBLICATION "DBA"."p1" ( TABLE Admin);
CREATE SYNCHRONIZATION USER "rem1";
CREATE SYNCHRONIZATION SUBSCRIPTION TO "DBA"."p1"
FOR "rem1" TYPE 'TCPIP' ADDRESS 'host=localhost' OPTION SV='v1';

==
== On the consolidated
==

create table Admin (
admin_id integer default global autoincrement primary key,
data varchar(30),
last_modified timestamp default timestamp
);

create global temporary table Admin_Process (
admin_id integer,
data varchar(30),
row_type varchar(1) check ( @col in ('O', 'N') ),
primary key (admin_id, row_type)
);

call ml_add_table_script(
'sas918_16', 'Admin',
'upload_old_row_inse
rt',
'insert into Admin_Process values ( ?, ?, ''O'' )'
);

call ml_add_table_script(
'sas918_16', 'Admin',
'upload_new_row_inse
rt',
'insert into Admin_Process values ( ?, ?, ''N'' )'
);

call ml_add_table_script(
'sas918_16', 'Admin',
'end_upload',
'call ProcessAdmin()'
);

create procedure ProcessAdmin ()
begin
DECLARE CurInsert CURSOR FOR
SELECT admin_id, data
FROM Admin_Process
WHERE row_type = 'N'
AND admin_id NOT IN
(SELECT admin_id
FROM Admin_Process
WHERE row_type = 'O');
DECLARE CurUpdate CURSOR FOR
SELECT admin_id, data
FROM Admin_Process
WHERE row_type = 'N'
AND admin_id IN
(SELECT admin_id
FROM Admin_Process
WHERE row_type = 'O');
DECLARE CurDelete CURSOR FOR
SELECT admin_id, data
FROM Admin_Process
WHERE row_type = 'O'
AND admin_id NOT IN
(SELECT admin_id
FROM Admin_Process
WHERE row_type = 'N');
DECLARE cid INTEGER;
DECLARE cdata VARCHAR(30);
OPEN CurInsert;
FETCH FIRST CurInsert INTO cid, cdata;
WHILE ( sqlcode = 0 ) LOOP
INSERT INTO Admin VALUES (cid,cdata,DEFAULT);

FETCH NEXT CurInsert INTO cid,cdata;
END LOOP;
CLOSE CurInsert;
OPEN CurUpdate;
FETCH FIRST CurUpdate INTO cid, cdata;
WHILE ( sqlcode = 0 ) LOOP
UPDATE Admin SET data = cdata WHERE admin_id = cid;
FETCH NEXT CurUpdate INTO cid,cdata;
END LOOP;
CLOSE CurUpdate;
OPEN CurDelete;
FETCH FIRST CurDelete INTO cid, cdata;
WHILE ( sqlcode = 0 ) LOOP
DELETE FROM Admin WHERE admin_id = cid;
FETCH NEXT CurDelete INTO cid,cdata;
END LOOP;
CLOSE CurDelete;
END;

--
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/swd/base.do
-> Choose SQL Anywhere Studio
-> Set filter to "Display ALL platforms IN ALL MONTHS"

"Bob" <email@place.com> wrote in message news:443270a0$1@foru
ms-2-dub...
> I read a post from Greg Fenton in November 2005 regarding uploading into a
> temp table rather than the original table. I have a table that I want any
> new or modified rows in the remote database to be redirected to a temp
table

> for processing; I do not want these records in the remote database to be
> inserted/updated in its respective consolidated table. I am fairly new to
> mobilink and I do not know what combination of scripts need to be
> modified/created in order to handle this.
>
> TIA,
>
> Bob
>
>

Greg Fenton

2006-04-04, 9:32 am

Bob wrote:
> I read a post from Greg Fenton in November 2005 regarding uploading into a
> temp table rather than the original table.

Uh oh....I knew that history would eventually catch up to me...

> I have a table that I want any
> new or modified rows in the remote database to be redirected to a temp table
> for processing; I do not want these records in the remote database to be
> inserted/updated in its respective consolidated table.

The cool thing with MobiLink is that it makes no assumption about the
schema in the consolidated database. If you have a table "foo" in the
remote, then scripts will fire in ML for table "foo", but what those
scripts do is entirely up to you.

For example, the upload_insert script for table "foo" gets called when a
newly INSERTed row is uploaded. But nothing says that your script has
to INSERT that row, or that it has to operate on table "foo". You could
do anything you want to with those values.

CALL ml_add_table_script(
'v1', 'foo',
'upload_insert',
'INSERT INTO my_temp_table (id, c1, c2) VALUES (?, ?, ?)'
);

The only real difficult concept here is those pesky "?"s. The represent
the values of the columns for the row that caused this event to fire.
The order and number of the "?"s differs for different events, so you
need to read up on them. In the upcoming Jasper release (beta starting
real soon now), MobiLink has the concept of "named parameters" where you
can use names instead of ambiguous "?"s.

The main thing to keep in mind is that ML does not do any magic. It
offers a simple API for your scripts, calls them when the matching
operation from the remote is uploaded and passes the script the
appropriate data.

Hope this helps,
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/

Bob

2006-04-04, 1:32 pm

Thanks for the help; works very well.

Bob

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:44328d99$1@foru
ms-1-dub...
> Bob wrote:
>
> Uh oh....I knew that history would eventually catch up to me...
>
>
> The cool thing with MobiLink is that it makes no assumption about the
> schema in the consolidated database. If you have a table "foo" in the
> remote, then scripts will fire in ML for table "foo", but what those
> scripts do is entirely up to you.
>
> For example, the upload_insert script for table "foo" gets called when a
> newly INSERTed row is uploaded. But nothing says that your script has to
> INSERT that row, or that it has to operate on table "foo". You could do
> anything you want to with those values.
>
> CALL ml_add_table_script(
'v1', 'foo',
> 'upload_insert',
> 'INSERT INTO my_temp_table (id, c1, c2) VALUES (?, ?, ?)'
> );
>
> The only real difficult concept here is those pesky "?"s. The represent
> the values of the columns for the row that caused this event to fire. The
> order and number of the "?"s differs for different events, so you need to
> read up on them. In the upcoming Jasper release (beta starting real soon
> now), MobiLink has the concept of "named parameters" where you can use
> names instead of ambiguous "?"s.
>
>
> The main thing to keep in mind is that ML does not do any magic. It
> offers a simple API for your scripts, calls them when the matching
> operation from the remote is uploaded and passes the script the
> appropriate data.
>
>
> Hope this helps,
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

Sponsored Links