SQL Anywhere Mobile - How can I output to file during dbmlsync?

Author

How can I output to file during dbmlsync?

Andy

2005-07-18, 11:24 am

Hello,
I am working on a script to upgrade about 1700 ASA7.0.4.3498 databases to
ASA9.0.2.3044. To minimize the risk, I'm verifying that each step of the
upgrade process works before either going to the next step or failing over
to a stable situation on the old database. Part of the upgrade script
includes synchronizing the ASA7 database. In order to verify that this step
of my batch file worked, I was thinking that I could execute dbmlsync with
the "-i <script>" switch. I believe the script will only execute if
synchronization is successful. In this sql script I would like to output to
a file so that I can test the existence of this file. The problem is, the
output command won't work because this isn't running through dbisql. I
tried xp_write_file, but I can't get it to create a file during
synchronization. I believe I have the syntax correct because it does work
from dbisqlc and synchronization does succeed whereas synchronization failed
every time I had a syntax error in the -i script. Here's the relevant code
to better explain what I have tried. Keep in mind that I don't particularly
care what's in the SyncWorked.txt file, I just want to create it using
dbmlsync -i so that I have a good indicator that synchronization worked. Am
I making this more complicated than it is and going about this all wrong?

ASA7toASA9.bat:
<snip>
rem Synchronize with the consolidated database.
echo Beginning synchronization. >> c:\trace\ASA9Upgrade
.log
%ASANY7%\win32\dbmls
ync.exe -k -q -n GENSYNC -a -o
c:\trace\lastsync.txt -i c:\temp\VerifySync.sql
if exist c:\temp\SyncWorked.txt echo Synchronization successful. >>
c:\trace\ASA9Upgrade
.log
if not exist c:\temp\SyncWorked.txt call c:\temp\ASA9BackoutP
lan.bat
</snip>

VerifySync.sql version 1
--This of course doesn't work because the output command only works from
dbisql.
--It causes synchronization to fail if I run this from dbmlsync -i.
select * from dba.sync_log where sync_log_id = (select max(sync_log_id)
from dba.sync_log);
output to 'c:\temp\SyncWorked.txt';

VerifySync.sql version 2
--This creates a file if I run it from dbisqlc. The contents are not
what I intended that doesn't matter.
--However it doesn't create a file when run from dbmlsync -i.
select xp_write_file( 'c:\temp\SyncWorked.txt', max(sync_log_id)) from
dba.sync_log;

Thanks,
Andy Marter

Greg Fenton

2005-07-18, 11:24 am

Andy wrote:
> Hello,
> I am working on a script to upgrade about 1700 ASA7.0.4.3498 databases to
> ASA9.0.2.3044. To minimize the risk, I'm verifying that each step of the
> upgrade process works before either going to the next step or failing over
> to a stable situation on the old database. Part of the upgrade script
> includes synchronizing the ASA7 database. In order to verify that this step
> of my batch file worked, I was thinking that I could execute dbmlsync with
> the "-i <script>" switch. I believe the script will only execute if
> synchronization is successful.

Have you checked to see if dbmlsync from 7.0.4.3498 will exit with an
appropriate error code/level? If so, then you could simply check the
success of the sync with the "if errorlevel" construct in NT's command
language.

I am just not sure if the exit code is properly set at that build or
not. I know that at one point there was a dialog about earlier builds
not setting the return code correctly.

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

Andy

2005-07-18, 1:24 pm

I just gave it a try. echo %errorlevel% evaluated to 0 even though
synchronization failed so it doesn't appear to update it correctly in this
build.

We do write useful information about the success or failure to a table we
call sync_log. I think that I may just have to query that using dbisql
right after synchronization. I should only get a file created from the
following statement if a synchronization finished the download phase within
the last minute. Although there may be room for error, I think this may
have to be close enough.

if exists (Select 1 from sync_log where sync_log_id = (select
max(sync_log_id)
from sync_log where sync_def_name = 'GENSYNC' and sync_end >=
dateadd(minute, -1, NOW()))
and download_end is not null) then
Select * from sync_log where sync_log_id = (select max(sync_log_id)
from sync_log where sync_def_name = 'GENSYNC' and sync_end >=
dateadd(minute, -1, NOW()))
and download_end is not null;
end if;
output to c:\temp\SyncWorked.txt;

Thanks for the reply.
- Andy

"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:42dbde5a$1@foru
ms-1-dub...
> Andy wrote:
>
>
> Have you checked to see if dbmlsync from 7.0.4.3498 will exit with an
> appropriate error code/level? If so, then you could simply check the
> success of the sync with the "if errorlevel" construct in NT's command
> language.
>
> I am just not sure if the exit code is properly set at that build or not.
> I know that at one point there was a dialog about earlier builds not
> setting the return code correctly.
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/

David Fishburn

2005-07-20, 11:23 am

"Andy" <retrama@lanetsaf.moc> wrote in news:42dbebad$1@foru
ms-1-dub of
sybase.public.sqlanywhere.mobilink:

A> I just gave it a try. echo %errorlevel% evaluated to 0 even though
A> synchronization failed so it doesn't appear to update it correctly in
A> this build.

I believe this was fixed/enhanced in some version of 8.x.

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

Pavel Karady

2005-10-27, 8:21 am

Hello,

have you tried to call the xp_write_file system procedure with double
backslashes in outfile path?

EXAMPLE xp_write_file( 'c:\\temp\\SyncWorke
d.txt', max(sync_log_id))

This should have it done. I've burnt my hands on this for a long time, since
I've figured out, why the files are not created :) If this still won't work,
exclude the path, leave the filename only:

EXAMPLE xp_write_file( 'SyncWorked.txt', max(sync_log_id))

This should always work. The problem is, file is saved then to fancy places
(e.g. C:\Windows\system32)
but it IS CREATED.

I hope this helps,
Pavel

On 18 Jul 2005 09:03:15 -0700,
in sybase.public.sqlanywhere.mobilink
Andy <retrama@lanetsaf.moc> wrote:
>Hello,
>I am working on a script to upgrade about 1700 ASA7.0.4.3498 databases to
>ASA9.0.2.3044. To minimize the risk, I'm verifying that each step of the
>upgrade process works before either going to the next step or failing over
>to a stable situation on the old database. Part of the upgrade script
>includes synchronizing the ASA7 database. In order to verify that this step
>of my batch file worked, I was thinking that I could execute dbmlsync with
>the "-i <script>" switch. I believe the script will only execute if
>synchronization is successful. In this sql script I would like to output to
>a file so that I can test the existence of this file. The problem is, the
>output command won't work because this isn't running through dbisql. I
>tried xp_write_file, but I can't get it to create a file during
>synchronization. I believe I have the syntax correct because it does work
>from dbisqlc and synchronization does succeed whereas synchronization failed
>every time I had a syntax error in the -i script. Here's the relevant code
>to better explain what I have tried. Keep in mind that I don't particularly
>care what's in the SyncWorked.txt file, I just want to create it using
>dbmlsync -i so that I have a good indicator that synchronization worked. Am
>I making this more complicated than it is and going about this all wrong?
>
>ASA7toASA9.bat:
><snip>
> rem Synchronize with the consolidated database.
> echo Beginning synchronization. >> c:\trace\ASA9Upgrade
.log
> %ASANY7%\win32\dbmls
ync.exe -k -q -n GENSYNC -a -o
>c:\trace\lastsync.txt -i c:\temp\VerifySync.sql
> if exist c:\temp\SyncWorked.txt echo Synchronization successful. >>
> c:\trace\ASA9Upgrade
.log
> if not exist c:\temp\SyncWorked.txt call c:\temp\ASA9BackoutP
lan.bat
></snip>
>
>VerifySync.sql version 1
> --This of course doesn't work because the output command only works from
>dbisql.
> --It causes synchronization to fail if I run this from dbmlsync -i.
> select * from dba.sync_log where sync_log_id = (select max(sync_log_id)
>from dba.sync_log);
> output to 'c:\temp\SyncWorked.txt';
>
>VerifySync.sql version 2
> --This creates a file if I run it from dbisqlc. The contents are not
>what I intended that doesn't matter.
> --However it doesn't create a file when run from dbmlsync -i.
> select xp_write_file( 'c:\temp\SyncWorked.txt', max(sync_log_id)) from
>dba.sync_log;
>
>
>Thanks,
>Andy Marter
>
>

David Fishburn

2005-10-27, 8:21 am

"Pavel Karady" <spachty@seznam.cz> wrote in news:435e1c2e$1@foru
ms-2-dub
of sybase.public.sqlanywhere.mobilink:

PK> If this still won't work, exclude the path, leave the filename only:
PK>
PK> EXAMPLE xp_write_file( 'SyncWorked.txt', max(sync_log_id))
PK>
PK> This should always work. The problem is, file is saved then to fancy
PK> places (e.g. C:\Windows\system32)
but it IS CREATED.

It is saved to the directory where the engine was launched.
So if you started the engine as a windows service, c:\windows\system32
would be it.

cd c:\my\location
dbeng9 dave.db

Now it would be written to c:\my\location.

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