SQL Anywhere Mobile - Help with Mobilink and ASA

Author

Help with Mobilink and ASA

Andrea Whittaker

2005-03-30, 9:43 am

I am very new at the Sybase collection of software products.
I have inherited a project that needs modification.
Written in VB6 with MobilVB, it is looking for a .udb
database. I only have an ASA .db I am trying to use
Mobilink to create a snapshot of the .db so I can continue
with the project in VB6.

In Sybase Central, I have connected to the .db and created
multiple synch table SQL scripts for each table in the db
under Mobil Synchronization.

I have the Publication named and identified tables within,
ODBC connection named, and the conduit as well.

How do I execute it? Is there a better way of doing this
than from within Sybase Central?

Thank you!

Greg Fenton

2005-03-30, 9:43 am

Andrea Whittaker wrote:
>
> How do I execute it? Is there a better way of doing this
> than from within Sybase Central?
>

Hi Andrea,

Well there are a few things to say about this.

First, you can have MobiLink generate "default" scripts for you in your
consolidated database so that your only job is then to make
modifications to those scripts based on your business needs. To do that:

- start the ML server connecting to your consolidated database and use
the "-za" and "-zu+" option:

dbmlsrv9 -c dsn=MY_CONS_DSN -za -zu+

- in your UL application's synchronization parameters, set the
SendColumnNames option to TRUE (I believe you are using MobileVB) and
specify the ScriptVersion value (otherwise it defaults to 'ul_default'
which is not a good practice):

mySyncParms.SendColumnNames = true;
mySyncParms.Version = 'Andrea_1.0';

- synchronize

If the Version (e.g. Andrea_1.0) does not already exist in the
consolidated, ML will generate scripts for that Version based on the
tables (and columns) sent up *by the remote UL database*.

These default scripts are a good starting point (for starters, they work).

Now the way that people in our team work is, once we have the scripts in
an ASA consolidated we extract those scripts into a .SQL file and work
with them in our favourite text editor(s).

Sybase Central is another valid option for working with the scripts, but
the benefit of having the scripts in a .SQL file is that we can check
that file into our version control system and track changes(!!).

Once you've made changes to the .SQL, you can simply run it against the
consolidated database and the changes will be applied. There is no need
to restart the MobiLink server as it will detect the changed scripts and
run the newer version.

To extract the scripts from an ASA consolidated, you can use the
following code (borrowed from previous posts by David Fishburn):

*************
UNLOAD
SELECT 'call ml_add_lang_table_sc
ript' ||
'( ''' ||v.name||
''', ''' ||t.name||
''', ''' ||ts.event||
''', ''' ||s.script_language||
''', ''\x0D\x0A' ||replace(s.script, '''', '''''')||
'\x0D\x0A'' );\x0D\x0A'
FROM ml_table t,
ml_table_script ts,
ml_script s,
ml_script_version v
WHERE ts.version_id = v.version_id
AND t.table_id = ts.table_id
AND ts.script_id = s.script_id

UNION ALL

SELECT 'call ml_add_lang_connecti
on_script' ||
'( ''' ||v.name||
''', ''' ||cs.event||
''', ''' ||s.script_language||
''', ''\x0D\x0A' || replace(s.script,'''', '''''')||
'\x0D\x0A'' );\x0D\x0A'
FROM ml_script_version v
KEY JOIN ml_connection_script
cs
KEY JOIN ml_script s

to 'C:\ML_scripts.sql'
quotes off
escapes off
delimited by '';
*************

The above code will generate the file C:\ML_scripts.sql. Edit
ML_scripts.sql and add what ever you want using your favourite
editor.

Typically we add the following code to the *top* of ML_scripts.sql to
empty out the ML tables before rerunning our tests. This effectively
resets the ML server, so any remote database would need to be reset as
well (you might decide not to reset the ML tables, but typically in a
development environment resetting everything is better than trying to
change synchronization logic with a bunch of remotes already having
synch'ed with the previous logic).

****************
DELETE FROM ml_table_script
WHERE version_id IN (
SELECT version_id
FROM ml_script_version
WHERE name = 'v1.0'
);

DELETE FROM ml_connection_script

WHERE version_id IN (
SELECT version_id
FROM ml_script_version
WHERE name = 'v1.0'
);

DELETE FROM ml_script
WHERE script_id NOT IN (
SELECT script_id
FROM ml_table_script
UNION ALL
SELECT script_id
FROM ml_connection_script

);

DELETE FROM ml_script_version
WHERE version_id IN (
SELECT version_id
FROM ml_script_version
WHERE name = 'v1.0'
);

COMMIT;
****************

Once you've got the ML_scripts.sql file all set up, you can run it
against your consolidated database with the command:

dbisql -c dsn=MY_CONS_DSN read ML_scripts.sql

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/

Andrea Whittaker

2005-03-30, 9:43 am

Greg,
First off, thank you for your quick response.

Second, when you say that MobiLink will generate "default"
scripts in consolidated database, where will they be
located? How do I find them? Do you mean I can see them in
Sybase Central?

I believe I did that last night, from fumbling through doco.
In Sybase Central I first right clicked on the Mobil
Synchronization Folder and connected to the consolidated
database.

Under that connection I saw that , I have table scripts
under the table folder under each table but I don't know how
to extract them. I also set up a connection script,
(ul_default) but I didn't know which event to attach to it,
so I choose begin_connection.

I did not set up any service.

Inside Sybase Central, I went to ODBC Connection and with
the wizard I have identified my ODBC connection as
GladesODBC.

I also set up under the publication folder a publication
named GladesCropCarePub which names all of the tables to be
included in the download.
Last night I also ran the

dbmlsync -c GladesODBC;user:syst
em; password:system
-n GladesCropCarePub
then I ran the

dbmlsrv9 -c dsn=GladesODBC -za

Questions on your response:

Should I run the dbmlsrv9 again with GladesODBC or did I do
that correctly last night? (the attachment shows you the
Sybase Central view of what I described above)

dbmlsrv9 -c dsn=GladesODBC -za -zu+

When you say in your "UL application", you mean VBasic w/
MobilVB correct?
I should also have the database server up as well correct?
Is there a minimum cache size I should set up?

Where do I run the SQL scripts? What I am trying to do is
not really sync with any remote right now. I'm just trying
to create a duplicate of my .db in a .udb format so I can
continue with my changes and enhancements of the application
in VBasic. I want to test the application as-is against the
.udb, so I don't care about the remotes right now. The
publication I created in Sybase Central was an 'additional'
publication to the one normally used for the remotes.

After I run the extracted SQL, it will build a data-full
duplicate of my .db in a .udb?

Thanks again. I'm getting right to work on your
instructions, and waiting anxiously for your response to
this note.

Sincerely, Andrea

Andrea Whittaker

2005-03-30, 9:43 am

Greg

I sent the attachment in the wrong format. Here it is in a
jpg.

Thanks, Andrea

Greg Fenton

2005-03-30, 9:43 am

Andrea Whittaker wrote:
> Second, when you say that MobiLink will generate "default"
> scripts in consolidated database, where will they be
> located? How do I find them? Do you mean I can see them in
> Sybase Central?

The scripts are stored in the consolidated database itself. There are a
bunch of tables (by default owned by the "DBA" user in an ASA db) that
are named "ml_*" (e.g. ml_scripts, ml_users, ...)

> Under that connection I saw that , I have table scripts
> under the table folder under each table but I don't know how
> to extract them.

See the SELECT statement from my previous post. You run that (e.g. via
DBISQL) against your consolidated database and it pulls the data (the
synch scripts) out of the ML tables.

> I also set up a connection script,
> (ul_default) but I didn't know which event to attach to it,
> so I choose begin_connection.

The event you choose depends on what the code is doing and when it needs
to run during the synchronization process. You need to have an
understanding of that process.

For example, begin_connection fires once for each database connection
that ML creates (when it connects to the database). Typically, this is
only once from the time ML starts up until it shuts down (though various
circumstances may have ML drop and reconnect to the database, thereby
re-executing that script).

> I did not set up any service.

You only need to set up a service if you want ML to automatically start
and be maintained by the OS. During a development cycle, this is
something I rarely do. I would rather start and stop the ML server via
the command line (or by writing a .BAT file and double-clicking it).

>
> I also set up under the publication folder a publication
> named GladesCropCarePub which names all of the tables to be
> included in the download.

You do *not* need a publication in your *consolidated*. Publications
are used in a *reference* database (since you are using ulinit you have
a reference database) to identify the tables you want to have in your UL
database. It is possible to use a single ASA db as both a consolidated
and a reference during development, but often this way of doing things
confuses people who are just getting started (the line between
consolidated and reference is fairly blurry to begin with).

> Last night I also ran the
>
> dbmlsync -c GladesODBC;user:syst
em; password:system
> -n GladesCropCarePub

You don't want to run dbmlsync for an UL application. Dbmlsync is the
*remote client* application to allow a *remote ASA* database to
synchronize to the ML server. Since you are building a UL application,
you won't use dbmlsync. Instead your UL application will call the
Synchronize() function within the UL library.

>
> Should I run the dbmlsrv9 again with GladesODBC or did I do
> that correctly last night?

The ML server can be kept up and running continuously. There is no need
to stop it unless you decide to shut down your consolidated. (This is
another area of confusion for people new to all of this as the ML
windows looks an awful lot like the dbmlsync window...)

> When you say in your "UL application", you mean VBasic w/
> MobilVB correct?

Yes.

> I should also have the database server up as well correct?

The database server needs to be up for the ML server to connect to it.
To run your UL/MobileVB app, there is no need to have ASA up (until it
is time to synchronize).

> Is there a minimum cache size I should set up?

ASA has dynamic cache sizing. It will grow its cache as needed. Once
you are close to production and you have an idea of how large the
database will be, the types of queries you'll be throwing at it, etc...
you might consider modifying its cache size. I know a lot of people who
always throw a "-c20m" into their ASA startup parameters just because
the default (2MB) is not sufficient for a typical database (though ASA
will grow the cache size as needed, it is more efficient to have ASA
start off with a decent cache size).

> Where do I run the SQL scripts? What I am trying to do is
> not really sync with any remote right now.

You would run the SQL scripts against the consolidated database (likely
your ASA database).

Your remote is the UL database. So you are indeed trying to sync with a
remote :-)

> I want to test the application as-is against the
> udb, so I don't care about the remotes right now.

But you likely want to have data in your .udb and the most effective way
to get there is to use synchronization (which is quite simple to set up
too).

> The
> publication I created in Sybase Central was an 'additional'
> publication to the one normally used for the remotes.

Again, your remote is you UL database.

> After I run the extracted SQL, it will build a data-full
> duplicate of my .db in a .udb?

Are you talking about the SQL file you have from my post? If so, then
no it will only contain the statements necessary to add/update the ML
synchronization scripts in a consolidated database.

> Thanks again. I'm getting right to work on your
> instructions, and waiting anxiously for your response to
> this note.

BTW: if it would be of benefit, our team offers ASA, ML and UL training,
knowledge transfer, design review and supplemental development. If you
would be at all interested, consider contacting us:

http://www.ianywhere.com/support/services.html

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