SQL Anywhere Mobile - modify_last_download

Author

modify_last_download_timestamp not changing

Tim Irwin

2005-06-21, 8:24 pm

We are in the processing of moving our SQL Remote on ASE to MobiLink on SQL
Server. Right now, I am working on my modify_last_download
_timestamp script
following, but slightly changing (see below), the example (in
http://www.sybase.com/content/10341...to-mobilink.pdf on page 38).
However, when I include the DELETE it does not update the @ldts value. If I
take that line out, it works just fine. It is deleting the row from the
table, but no other statements get called after that point which I found out
by putting a PRINT statement after the delete. If I remove the DELETE
statement and put two PRINT statements in, the first one comes out but the
second one is ignored. Any ideas how I can get this to work?

Note: For simplicity, we made the address the same as the remote user when
we deployed SQL Remote which has come in handy now. I am keeping my
last_download_timest
amp and next_last_download_t
imestamp on UTC to avoid
problems with daylight savings. I plan to add entries to sr_remoteuser in
case I need to restore a remote database or add a new one (copied from
somewhere else) to be able to pick up from a specified point in time.

Thanks for your help.

Tim

CREATE PROCEDURE sp_mldt( @ldts DATETIME OUTPUT, @ml_user
VARCHAR(128) )ASBEGIN IF (EXISTS (SELECT 1 FROM sr_remoteuser WHERE address
= @ml_user)) BEGIN SELECT @ldts = DATEADD(hour, DATEDIFF(hour,
GETDATE(), GETUTCDATE()), time_sent) FROM sr_remoteuser WHERE address =
@ml_user DELETE FROM sr_remoteuser WHERE address = @ml_user ENDEND;

Tim Irwin

2005-06-21, 8:24 pm

I'm sorry, I should have included that we are using Adaptive Server Anywhere
MobiLink Version 9.0.1.1964 on Windows XP Professional SP2 with SQL Server
2000 on Windows 2000.

"Tim Irwin" <tirwin@twr.org> wrote in message
news:<42b875e1@forums-1-dub>...

> We are in the processing of moving our SQL Remote on ASE to MobiLink on
> SQL

> Server. Right now, I am working on my modify_last_download
_timestamp
> script

> following, but slightly changing (see below), the example (in

> http://www.sybase.com/content/10341...to-mobilink.pdf on page
> 38).

> However, when I include the DELETE it does not update the @ldts value. If
> I

> take that line out, it works just fine. It is deleting the row from the

> table, but no other statements get called after that point which I found
> out

> by putting a PRINT statement after the delete. If I remove the DELETE

> statement and put two PRINT statements in, the first one comes out but the

> second one is ignored. Any ideas how I can get this to work?

>

> Note: For simplicity, we made the address the same as the remote user when

> we deployed SQL Remote which has come in handy now. I am keeping my

> last_download_timest
amp and next_last_download_t
imestamp on UTC to avoid

> problems with daylight savings. I plan to add entries to sr_remoteuser in

> case I need to restore a remote database or add a new one (copied from

> somewhere else) to be able to pick up from a specified point in time.

>

> Thanks for your help.

>

> Tim

>

>

CREATE PROCEDURE sp_mldt(

@ldts DATETIME OUTPUT,

@ml_user VARCHAR(128) )

AS

BEGIN

IF (EXISTS (SELECT 1 FROM sr_remoteuser WHERE address = @ml_user))

BEGIN

SELECT @ldts = DATEADD(hour, DATEDIFF(hour, GETDATE(), GETUTCDATE()),
time_sent) FROM sr_remoteuser WHERE address = @ml_user

DELETE FROM sr_remoteuser WHERE address = @ml_user

END

END;

David Fishburn

2005-06-29, 11:24 am

"Tim Irwin" <tirwin@twr.org> wrote in news:42b8798e$1@foru
ms-2-dub of
sybase.public.sqlanywhere.mobilink:

TI> I'm sorry, I should have included that we are using Adaptive Server
TI> Anywhere MobiLink Version 9.0.1.1964 on Windows XP Professional SP2
TI> with SQL Server 2000 on Windows 2000.
....
TI>> However, when I include the DELETE it does not update the @ldts
TI>> value. If I
TI>
TI>> take that line out, it works just fine. It is deleting the row from
TI>> the
TI>
TI>> table, but no other statements get called after that point which I
TI>> found out
TI>
TI>> by putting a PRINT statement after the delete. If I remove the
DELETE
TI>
TI>> statement and put two PRINT statements in, the first one comes out
TI>> but the
TI>
TI>> second one is ignored. Any ideas how I can get this to work?
TI>
....
TI>
TI> CREATE PROCEDURE sp_mldt(
TI>
TI> @ldts DATETIME OUTPUT,
TI>
TI> @ml_user VARCHAR(128) )
TI>
TI> AS
TI>
TI> BEGIN
TI>
TI> IF (EXISTS (SELECT 1 FROM sr_remoteuser WHERE address = @ml_user))
TI>
TI> BEGIN
TI>
TI> SELECT @ldts = DATEADD(hour, DATEDIFF(hour, GETDATE(),
TI> GETUTCDATE()),
TI> time_sent) FROM sr_remoteuser WHERE address = @ml_user
TI>
TI> DELETE FROM sr_remoteuser WHERE address = @ml_user
TI>
TI> END
TI>
TI> END;
TI>
TI>
TI>

Did you manage to get this working?

I do not see anything wrong with the procedure. The print statements
were a
good idea.

Can you show the procedure with your print statements as well, plus the
output
you are seeing.
--
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]

Tim Irwin

2005-07-05, 8:24 pm

> Did you manage to get this working?

Unfortunately, I have not.

> Can you show the procedure with your print statements as
well, plus the output you are seeing.

I ran the procedure outside of MobiLink and I get the
correct datetime returned and the row is deleted. That
would seem to imply MobiLink is not accepting the result
based upon procedure output messages??

Here is the PRINT statement version:
CREATE PROCEDURE sp_mldt(
@ldts DATETIME OUTPUT,
@ml_user VARCHAR(128) )
AS
BEGIN
IF (EXISTS (SELECT 1 FROM sr_remoteuser WHERE address =
@ml_user))
BEGIN
SELECT @ldts = DATEADD(hour, DATEDIFF(hour, GETDATE(),
GETUTCDATE()), time_sent) FROM sr_remoteuser WHERE address =
@ml_user

PRINT 'before delete'

DELETE FROM sr_remoteuser WHERE address = @ml_user

PRINT 'after delete'
END
END;

Here is the output from procedure as it shows up on the
MobiLink log:
I. 07/05 16:33:30. <1.5> [mb_tim]:
modify_last_download
_timestamp <connection>
EXEC twrsp_ModifyLastDown
loadTimestamp
?, ?
I. 07/05 16:33:30. <1.5> [mb_tim]: Translated SQL:
EXEC twrsp_ModifyLastDown
loadTimestamp
?, ?
W. 07/05 16:33:30. <1.5> [mb_tim]: Warning: [10050] ODBC:
[Microsoft][ODBC SQL Server Driver][SQL Server]before delete
(ODBC State = 01000, Native error code = 0)
I. 07/05 16:33:30. <1.5> [mb_tim]: COMMIT Transaction:
prepare_for_download

I. 07/05 16:33:30. <1.5> [mb_tim]: begin_download
<connection> (no script)

If I change the procedure to be this:
CREATE PROCEDURE sp_mldt(
@ldts DATETIME OUTPUT,
@ml_user VARCHAR(128) )
AS
BEGIN
IF (EXISTS (SELECT 1 FROM sr_remoteuser WHERE address =
@ml_user))
BEGIN
SELECT @ldts = DATEADD(hour, DATEDIFF(hour, GETDATE(),
GETUTCDATE()), time_sent) FROM sr_remoteuser WHERE address =
@ml_user

DELETE FROM sr_remoteuser WHERE address = @ml_user

PRINT 'after delete'
END
END;

The result I get is:
I. 07/05 16:41:50. <1.5> [mb_tim]:
modify_last_download
_timestamp <connection>
EXEC twrsp_ModifyLastDown
loadTimestamp
?, ?
I. 07/05 16:41:50. <1.5> [mb_tim]: Translated SQL:
EXEC twrsp_ModifyLastDown
loadTimestamp
?, ?
I. 07/05 16:41:50. <1.5> [mb_tim]: COMMIT Transaction:
prepare_for_download

I. 07/05 16:41:50. <1.5> [mb_tim]: begin_download
<connection> (no script)

David Fishburn

2005-08-17, 1:24 pm

Tim Irwin wrote in news:42caf0e1.64ea.1681692777@sybase.com
of sybase.public.sqlanywhere.mobilink:

TI>> Did you manage to get this working?
TI>
TI> Unfortunately, I have not.
TI>
TI> I. 07/05 16:33:30. <1.5> [mb_tim]:
TI> modify_last_download
_timestamp <connection>
TI> EXEC twrsp_ModifyLastDown
loadTimestamp ?, ?

I would change the above call to:
{ call twrsp_ModifyLastDown
loadTimestamp( ?, ? ) }

And see if you get different results.

This was based on:

MobiLink Administration Guide
MobiLink Consolidated Databases
Microsoft SQL Server consolidated database

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