SQL Anywhere Mobile - How i may use ml_user in upload

Author

How i may use ml_user in upload_update ?

Fantom

2005-11-16, 7:25 am

Hi
How i may use ml_user in upload_update ?
Username is not in parameters in this event.

Fantom

Reg Domaratzki

2005-11-16, 9:24 am

You need to create a variable in the begin_connection event, populate it in
the being_synchronizatio
n, and now it can be referenced during the rest of
the synchronization, since it's all on the same connection.

call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
@mlu varchar(128)');
call ml_add_connection_sc
ript( 'v1', 'begin_synchronizati
on', 'set @mlu
=?');

call ml_add_table_script(
'v1', 't1', 'upload_update', 'update t1 set c1=?,
ml_user=@mlu where pkey = ?');

My answer assumes you are using ASA as the consolidated. There are
different techniques for different consolidated databases.

--
Reg Domaratzki, Sybase iAnywhere Solutions
Certified SQL Anywhere Associate
Please reply only to the newsgroup

ASA Patches and EBFs : http://downloads.sybase.com/swx/sdmain.stm
-> Choose SQL Anywhere Studio
-> Set "Platform Preview" and "Time Frame" to ALL and click "GO"

"Fantom" <szczukot@skasujto.poczta.onet.pl> wrote in message
news:437b2c5b$1@foru
ms-2-dub...
> Hi
> How i may use ml_user in upload_update ?
> Username is not in parameters in this event.
>
> Fantom

Fantom

2005-11-16, 9:24 am

> You need to create a variable in the begin_connection event, populate it
in
> the being_synchronizatio
n, and now it can be referenced during the rest of
> the synchronization, since it's all on the same connection.
>
> call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
> @mlu varchar(128)');
> call ml_add_connection_sc
ript( 'v1', 'begin_synchronizati
on', 'set @mlu
> =?');
>
> call ml_add_table_script(
'v1', 't1', 'upload_update', 'update t1 set
c1=?,
> ml_user=@mlu where pkey = ?');

Thanks. This is not 'easy' but is work ok.

Fantom

Fantom

2005-11-16, 9:24 am

> call ml_add_table_script(
'v1', 't1', 'upload_update', 'update t1 set
c1=?,
> ml_user=@mlu where pkey = ?');

Can i use this variable in WHERE ?
'update t1 set c1=?, where pkey = ? and @mlu = 111' - i get error
"Column not found: Column '@mlu' not found (ODBC State = 42S22, Native error
code = -143)"

Fantom

David Fishburn

2005-11-16, 1:24 pm

"Fantom" <szczukot@skasujto.poczta.onet.pl> wrote in
news:437b39a2@forums
-2-dub of sybase.public.sqlanywhere.mobilink:

F> Can i use this variable in WHERE ?
F> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - i get error
F> "Column not found: Column '@mlu' not found (ODBC State = 42S22, Native
F> error code = -143)"

I think you meant (or at least should mean):

update t1
set c1 = ?,
where pkey = ?
and your_column_which_ho
lds_the_ml_user_name
= @mlu

@mlu is a value, it is not a column name like you are trying to use it.

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

Fantom

2005-11-17, 3:25 am

> F> Can i use this variable in WHERE ?
> F> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - i get error
> F> "Column not found: Column '@mlu' not found (ODBC State = 42S22, Native
> F> error code = -143)"
>
> I think you meant (or at least should mean):
>
> update t1
> set c1 = ?,
> where pkey = ?
> and your_column_which_ho
lds_the_ml_user_name
= @mlu
>
> @mlu is a value, it is not a column name like you are trying to use it.

But i want use this variable not with column name - only with another
variable :
'update t1 set c1=?, where pkey = ? and @mlu = 111' - this update is only
aplly from user name = 111

Fantom

David Fishburn

2005-11-17, 9:24 am

"Fantom" <szczukot@skasujto.poczta.onet.pl> wrote in
news:437c2eee$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:

F>> F> Can i use this variable in WHERE ?
F>> F> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - i get error
F>> F> "Column not found: Column '@mlu' not found (ODBC State = 42S22,
F>> Native F> error code = -143)"
F>>
F>> I think you meant (or at least should mean):
F>>
F>> update t1
F>> set c1 = ?,
F>> where pkey = ?
F>> and your_column_which_ho
lds_the_ml_user_name
= @mlu
F>>
F>> @mlu is a value, it is not a column name like you are trying to use
F>> it.
F>
F> But i want use this variable not with column name - only with another
F> variable :
F> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - this update is
F> only aplly from user name = 111

Based on these sync scripts:

call ml_add_connection_sc
ript( 'v1', 'begin_connection', '
create variable @mlu varchar(128)
');
call ml_add_connection_sc
ript( 'v1', 'begin_synchronizati
on', '
set @mlu = ?
');

@mlu was already set to 111, assuming that was the synchronization user
name supplied by the remote.

So essentially you are saying:
set @mlu = 111
which is really:
set 111 = 111

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

Fantom

2005-11-17, 9:24 am

> F> But i want use this variable not with column name - only with another
> F> variable :
> F> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - this update is
> F> only aplly from user name = 111
>
> Based on these sync scripts:
>
> call ml_add_connection_sc
ript( 'v1', 'begin_connection', '
> create variable @mlu varchar(128)
> ');
> call ml_add_connection_sc
ript( 'v1', 'begin_synchronizati
on', '
> set @mlu = ?
> ');
>
> @mlu was already set to 111, assuming that was the synchronization user
> name supplied by the remote.
>
> So essentially you are saying:
> set @mlu = 111
> which is really:
> set 111 = 111

Not always. Because for example synchronization user is 222. And for this
user i dont want update record. I want skip this row. and i must uset @mlu =
111 in WHERE clausule. But this is not work.

Fantom

Reg Domaratzki $iAnywhere Solutions$

2005-11-17, 9:24 am

I think Dave and I are having trouble answering your questions without
knowing the exact schema we're dealing with.

I'll assume the following schema on the remote :

create table t1 (
pkey bigint default autoincrement,
c1 integer,
primary key(pkey)
);

and I'll assume the following schema on the consolidated :

create table t1 (
pkey bigint,
c1 integer,
ml_user varchar(128),
primary key( pkey, owner )
);

The main reason I see for wanting access to the MobiLink user in the
upload_* scripts is so that you can define a two-column primary key on your
table in the consolidated, but a single column primary key on the remote.
We've essentially guaranteed primary key uniqueness on the consolidated
without making a schema change on the remote, and without having the remote
have to worry about primary key uniqueness. Given this setup, the
synchronization scripts on the consolidated will look like :

call ml_add_connection_sc
ript( 'v1', 'begin_connection', 'create variable
@mlu varchar(128)');
call ml_add_connection_sc
ript( 'v1', 'begin_synchronizati
on', 'set @mlu=?');

call ml_add_table_script(
'v1','t1','upload_in
sert','insert into t1 values
(?,?,@mlu)' );
call ml_add_table_script(
'v1','t1','upload_up
date','update t1 set c1=? where
pkey=? and ml_user=@mlu' );
call ml_add_table_script(
'v1','t1','upload_de
lete','delete from t1 where
pkey=? and ml_user=@mlu');
call ml_add_table_script(
'v1','t1','download_
cursor','select pkey,c1 from t1
where ml_user=@mlu');

Your suggestion about putting where @mlu=111 doesn't make sense at all. The
variable @mlu will have been set to the value "111" in the
begin_synchronizatio
n script, so when you put "@mlu=111" in the where clause
of the update statement, you're saying "where 111=111", which will always be
true. The upload_update I wrote above will only update the rows owned by
the MobiLink user that is synchronizing, which I think is what you're trying
to accomplish. If this isn't what you are trying to accomplish, please use
the schema above to explain what you're trying to do, or post your own
schema and explain what you're trying to do.

--
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 filter to "Display ALL platforms IN ALL MONTHS"

"Fantom" <szczukot@skasujto.poczta.onet.pl> wrote in message
news:437c2eee$1@foru
ms-2-dub...
Native[color=darkred
]
>
> But i want use this variable not with column name - only with another
> variable :
> 'update t1 set c1=?, where pkey = ? and @mlu = 111' - this update is only
> aplly from user name = 111
>
> Fantom
>

Fantom

2005-11-18, 3:24 am

> and I'll assume the following schema on the consolidated :
>
> create table t1 (
> pkey bigint,
> c1 integer,
> ml_user varchar(128),
> primary key( pkey, owner )
> );

Owner or ml_user in primary_key ?

> Your suggestion about putting where @mlu=111 doesn't make sense at all.
The
> variable @mlu will have been set to the value "111" in the
> begin_synchronizatio
n script, so when you put "@mlu=111" in the where
clause
> of the update statement, you're saying "where 111=111", which will always
be
> true.

I understand what You 'give' me, but you dont understand what i want. My
english is very poor :(
For example i have 2 remote database. i first user name is 111, and in
second user name is 222.
And "where @mlu=111" is not always true, because sometimes is 222=111 (this
is false) ! upload_update script is this same form user 111 and 222.

Fantom

David Fishburn

2005-11-18, 9:24 am

"Fantom" <szczukot@skasujto.poczta.onet.pl> wrote in
news:437d8ff5$1@foru
ms-2-dub of sybase.public.sqlanywhere.mobilink:

F> For example i have 2 remote database. i first user name is 111, and in
F> second user name is 222.
F> And "where @mlu=111" is not always true, because sometimes is 222=111
F> (this is false) ! upload_update script is this same form user 111 and
F> 222.

Why would you want to "hard code" a value in your synchronization
scripts? If you do that, you have to start creating different script
versions for every user. This is a maintenance nightmare and defeats
the performance caching MobiLink does.

I think you need to come up with a sample of rows in the consolidated,
rows in 2 remotes. Show us what actions the remote's make and show us
what action you want the consolidated to take and WHY it should make
that choice.

Once we have a better picture, we could offer better suggestions.

Just remember, make sure you read this section in the Help file:

MobiLink Administration Guide
Synchronization Techniques
Maintaining unique primary keys

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

Sponsored Links