SQL Anywhere Mobile - Download_delete

Author

Download_delete_cursor script question

Kenny Cheang

2005-05-13, 1:23 pm

Hi,

I am running ASA/ML 9.0.1.1862. I have a question about
download_delete_curs
or script.

I have a table which is cleared and rebuilt every night on the
consolidated database server. During the day, the data in this table is
never changed. Therefore, the requirements for synchronizing this table
using Mobilink are:

1. During the first synchronization on each day, download_delete_curs
or
script will clear the entire table. And download_cursor script will
refresh the entire table.

2. If the users sync more than once a day, the download_delete_curs
or
should not clear the table except the first sync on each day.

I understand that I can use "select NULL, NULL, ..." to clear the table
in download_delete_curs
or script. However, how can I achieve requirement #2?

Any help is greatly appreciated!

Thank you!

Kenny

Reg Domaratzki $iAnywhere Solutions$

2005-05-13, 1:23 pm

Your download_delete_curs
or could be a call to stored procedure, which only
returns NULL if the last_download timestamp passed in is not from today.
For example, assuming the table t1 has a single column primary key (not
checked for syntax) :

call ml_add_table_script ( 'v1', 't1', 'download_delete_cur
sor', '{call
t1_ddc( ?, ? )}' );

create procedure t1_ddc ( in last_download timestamp, in mluser
varchar(128) )
result ( pkey bigint )
begin
if datediff( day, last_download, CURRENT TIMESTAMP ) > 0 then
select NULL from dummy;
end if;
end;

You might need to do something fancy when daylight savings time comes
around, but I'll leave that as an exercise for the reader.

--
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 "Platform Preview" and "Time Frame" to ALL

"Kenny Cheang" <kcheang@k-swiss.com> wrote in message
news:4284dd90$1@foru
ms-2-dub...
> Hi,
>
> I am running ASA/ML 9.0.1.1862. I have a question about
> download_delete_curs
or script.
>
> I have a table which is cleared and rebuilt every night on the
> consolidated database server. During the day, the data in this table is
> never changed. Therefore, the requirements for synchronizing this table
> using Mobilink are:
>
> 1. During the first synchronization on each day, download_delete_curs
or
> script will clear the entire table. And download_cursor script will
> refresh the entire table.
>
> 2. If the users sync more than once a day, the download_delete_curs
or
> should not clear the table except the first sync on each day.
>
> I understand that I can use "select NULL, NULL, ..." to clear the table
> in download_delete_curs
or script. However, how can I achieve requirement
#2?
>
> Any help is greatly appreciated!
>
> Thank you!
>
> Kenny

Kenny Cheang

2005-05-17, 8:24 pm

Hi Reg,

Thanks so much for your reply. Your suggestion makes a lot of sense.
However, after I tried it, I got Invalid Cursor State error.

Here is my store procedure:

CREATE PROCEDURE customerhistory_down
load_delete_cursor_h
elper(
IN @last_download TIMESTAMP,
IN @ml_username VARCHAR(128) )
RESULT ( Ord varchar(1),
Customer varchar(12),
Type varchar(12),
No1 varchar(12),
Season varchar(20),
No2 varchar(12),
Description varchar(50) )
BEGIN
if datediff( day, @last_download, CURRENT TIMESTAMP ) > 0 then
select NULL, NULL, NULL, NULL, NULL, NULL, NULL;
end if;
END;

I only get the error when I try to sync for the second time on the same
day (when the if statement in the store procedure fails). Here is the
error message from Mobilink log:

I. 05/17 14:48:53. <1.5> [kcheang]: begin_download_delet
es
CustomerHistory (no script)
I. 05/17 14:48:53. <1.5> [kcheang]: Cached ODBC statement:
{call customerhistory_down
load_delete_cursor_h
elper(
?, ? )}
E. 05/17 14:48:53. <1.5> [kcheang]: Error: [-10002] ODBC: [Sybase][ODBC
Driver]Invalid cursor state (ODBC State = 24000, Native error code = 0)
I. 05/17 14:48:53. <1.5> [kcheang]: Error Context:
I. 05/17 14:48:53. <1.5> [kcheang]: User Name: kcheang
I. 05/17 14:48:53. <1.5> [kcheang]: Modified User Name: kcheang
I. 05/17 14:48:53. <1.5> [kcheang]: Transaction: download
I. 05/17 14:48:53. <1.5> [kcheang]: Table Name: CustomerHistory
I. 05/17 14:48:53. <1.5> [kcheang]: Script Version: NULL
I. 05/17 14:48:53. <1.5> [kcheang]: Script: {call
customerhistory_down
load_delete_cursor_h
elper( ?, ? )}
I. 05/17 14:48:53. <1.5> [kcheang]: End of Error Context
W. 05/17 14:48:53. <1.5> [kcheang]: Warning: [10010] No handle_error
script is defined. The default action code (3000) will decide the error
behavior

Any idea what caused the error?

Thanks in advance for your help again!

Kenny

Reg Domaratzki (iAnywhere Solutions) wrote:
> Your download_delete_curs
or could be a call to stored procedure, which only
> returns NULL if the last_download timestamp passed in is not from today.
> For example, assuming the table t1 has a single column primary key (not
> checked for syntax) :
>
> call ml_add_table_script ( 'v1', 't1', 'download_delete_cur
sor', '{call
> t1_ddc( ?, ? )}' );
>
> create procedure t1_ddc ( in last_download timestamp, in mluser
> varchar(128) )
> result ( pkey bigint )
> begin
> if datediff( day, last_download, CURRENT TIMESTAMP ) > 0 then
> select NULL from dummy;
> end if;
> end;
>
> You might need to do something fancy when daylight savings time comes
> around, but I'll leave that as an exercise for the reader.
>

David Fishburn

2005-05-18, 3:24 am

Kenny Cheang <kcheang@k-swiss.com> wrote in news:428a6c9c$1@foru
ms-2-dub
of sybase.public.sqlanywhere.mobilink:

....
KC> CREATE PROCEDURE customerhistory_down
load_delete_cursor_h
elper(
KC> IN @last_download TIMESTAMP,
KC> IN @ml_username VARCHAR(128) )
KC> RESULT ( Ord varchar(1),
KC> Customer varchar(12),
KC> Type varchar(12),
KC> No1 varchar(12),
KC> Season varchar(20),
KC> No2 varchar(12),
KC> Description varchar(50) )
KC> BEGIN
KC> if datediff( day, @last_download, CURRENT TIMESTAMP ) > 0 then
KC> select NULL, NULL, NULL, NULL, NULL, NULL, NULL;
KC> end if;
KC> END;
....
KC> E. 05/17 14:48:53. <1.5> [kcheang]: Error: [-10002] ODBC:
KC> [Sybase][ODBC Driver]Invalid cursor state (ODBC State = 24000, Native
KC> error code = 0) I. 05/17 14:48:53. <1.5> [kcheang]: Error Context:

What is the primary key for this table?
Is it really 7 columns?
--
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]

Kenny Cheang

2005-05-18, 11:23 am

Yes. I don't think this is related to Primary Key. The invalid cursor
state error only happens when the if statement in the procedure fails, a
condition where no download_delete_curs
or script is supposed to run.

David Fishburn wrote:
> Kenny Cheang <kcheang@k-swiss.com> wrote in news:428a6c9c$1@foru
ms-2-dub
> of sybase.public.sqlanywhere.mobilink:
>
> ...
> KC> CREATE PROCEDURE customerhistory_down
load_delete_cursor_h
elper(
> KC> IN @last_download TIMESTAMP,
> KC> IN @ml_username VARCHAR(128) )
> KC> RESULT ( Ord varchar(1),
> KC> Customer varchar(12),
> KC> Type varchar(12),
> KC> No1 varchar(12),
> KC> Season varchar(20),
> KC> No2 varchar(12),
> KC> Description varchar(50) )
> KC> BEGIN
> KC> if datediff( day, @last_download, CURRENT TIMESTAMP ) > 0 then
> KC> select NULL, NULL, NULL, NULL, NULL, NULL, NULL;
> KC> end if;
> KC> END;
> ...
> KC> E. 05/17 14:48:53. <1.5> [kcheang]: Error: [-10002] ODBC:
> KC> [Sybase][ODBC Driver]Invalid cursor state (ODBC State = 24000, Native
> KC> error code = 0) I. 05/17 14:48:53. <1.5> [kcheang]: Error Context:
>
> What is the primary key for this table?
> Is it really 7 columns?

Reg Domaratzki $iAnywhere Solutions$

2005-05-18, 11:23 am

Maybe you need to still return an empty result set (not a result set with
NULLS) when the condition is not met. Does adding the following help?

if datediff( day, @last_download, CURRENT TIMESTAMP ) > 0 then
select NULL, NULL, NULL, NULL, NULL, NULL, NULL;
else
select 0,0,0,0,0,0 from dummy where 1=0;
end if;

--
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 "Platform Preview" and "Time Frame" to ALL

"Kenny Cheang" <kcheang@k-swiss.com> wrote in message
news:428b5fc5$1@foru
ms-1-dub...[color=darkred]
> Yes. I don't think this is related to Primary Key. The invalid cursor
> state error only happens when the if statement in the procedure fails, a
> condition where no download_delete_curs
or script is supposed to run.
>
> David Fishburn wrote:
Native[color=darkred
]

Kenny Cheang

2005-05-18, 1:24 pm

Reg,

It works now. This is great. Thanks so much for your help.

Kenny

Reg Domaratzki (iAnywhere Solutions) wrote:
> Maybe you need to still return an empty result set (not a result set with
> NULLS) when the condition is not met. Does adding the following help?
>
> if datediff( day, @last_download, CURRENT TIMESTAMP ) > 0 then
> select NULL, NULL, NULL, NULL, NULL, NULL, NULL;
> else
> select 0,0,0,0,0,0 from dummy where 1=0;
> end if;
>
>

Sponsored Links