SQL Anywhere Mobile - Integrity violations

Author

Integrity violations

MJN

2005-08-24, 11:25 am

When a referential integrity violation is found how can u flag the row
instead of deleting it??
I am getting allot of these integrity violation issues and am trying to
found out how and why these are happening...

ASA 9.02

MJN

2005-08-24, 11:25 am

Forgot to mention another point and that is on the remotes no row is ever
deleted by the user only flagged as being deleted..
Thus can't understand why I am getting these errors as when sync started all
db's were identical with the same set of data..

"MJN" < SpamFilterOnlychuck1
rar@yahoo.com> wrote in message
news:430c8cbd$1@foru
ms-1-dub...
> When a referential integrity violation is found how can u flag the row
> instead of deleting it??
> I am getting allot of these integrity violation issues and am trying to
> found out how and why these are happening...
>
> ASA 9.02
>

Greg Fenton

2005-08-25, 3:37 am

MJN wrote:
> When a referential integrity violation is found how can u flag the row
> instead of deleting it??
> I am getting allot of these integrity violation issues and am trying to
> found out how and why these are happening...
>

Where are you seeing these errors, on the client or the server?
Can you show us the log file containing this type of error message?

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

MJN

2005-08-25, 3:37 am

>
> Where are you seeing these errors, on the client or the server?
> Can you show us the log file containing this type of error message?

Get these errors on the remote.
Have added the relevant portion below.
I had tought it must have been a problem with my altered pfc_delete function
but when I got this error I realised it could not be so as no user is
allowed to delete a customer row.

Also in response to the other thread, maybe I am wrong in my assumption but
that SP I had included gets called when integrity violations gets resolved
and thus inserts into the log table. So assumed this was the problem as it
only happened after I added the SP.

Thanks

I. 08/25 01:32:02. # rows inserted/updated into table CURRENT_COURSE_DETAI
LS
: 62
I. 08/25 01:32:02. # rows deleted in table CURRENT_COURSE_DETAI
LS : 0
I. 08/25 01:32:02. Setting last download time to 2005-08-25 01:31:11.757.
I. 08/25 01:32:02. Resolving referential integrity violations on table
COMMENTS, role CUSTOMER
I. 08/25 01:32:02. Opening connection to remote database to call error/log
hook.
I. 08/25 01:32:02. insert into #hook_dict values( 'MobiLink
user','ML_Nathess1' );
I. 08/25 01:32:02. insert into #hook_dict values( 'Foreign key
table','COMMENTS' );
I. 08/25 01:32:02. insert into #hook_dict values( 'Primary key
table','CUSTOMER' );
I. 08/25 01:32:02. insert into #hook_dict values( 'Role name','CUSTOMER' );
I. 08/25 01:32:02. insert into #hook_dict values( 'script version','v1' );
I. 08/25 01:32:02. insert into #hook_dict values(
'publication_0','pub
_nathess' );
I. 08/25 01:32:02. execute "DBA". sp_hook_dbmlsync_dow
nload_log_ri_violati
on
E. 08/25 01:32:02. SQL statement failed: (-157) Cannot convert ML_Nathess1
to a timestamp
E. 08/25 01:32:02. Error while executing hook procedure
sp_hook_dbmlsync_dow
nload_log_ri_violati
on.
I. 08/25 01:32:03. ROLLBACK
I. 08/25 01:32:03. Closing connection for error/log hooks.
I. 08/25 01:32:03. ROLLBACK
I. 08/25 01:32:03. Download stream processing failed

Greg Fenton

2005-08-26, 3:24 am

MJN wrote:
> I. 08/25 01:32:02. insert into #hook_dict values( 'MobiLink
> user','ML_Nathess1' );
> I. 08/25 01:32:02. insert into #hook_dict values( 'Foreign key
> table','COMMENTS' );
> I. 08/25 01:32:02. insert into #hook_dict values( 'Primary key
> table','CUSTOMER' );
> I. 08/25 01:32:02. insert into #hook_dict values( 'Role name','CUSTOMER' );
> I. 08/25 01:32:02. insert into #hook_dict values( 'script version','v1' );
> I. 08/25 01:32:02. insert into #hook_dict values(
> 'publication_0','pub
_nathess' );
> I. 08/25 01:32:02. execute "DBA". sp_hook_dbmlsync_dow
nload_log_ri_violati
on
> E. 08/25 01:32:02. SQL statement failed: (-157) Cannot convert ML_Nathess1
> to a timestamp

It does indeed appear that this error is related to your stored
procedure. From your previous thread:

> CREATE TABLE "DBA"." LogRIErrorTable "
> (
> "id" int PRIMARY KEY NOT NULL,
> "name" varchar(255) NOT NULL,
> "value" timestamp NOT NULL
> )
>
> ALTER PROCEDURE "DBA"." sp_hook_dbmlsync_dow
nload_log_ri_violati
on"()
> begin
> [...]
> insert into LogRIErrorTable( id,name,value)
> select nextid,#hook_dict.name,#hook_dict.value from
> #hook_dict
>

There are many rows in the #hook_dict table. Each of them have a name
and a value field. Look at the above log entries and I see that the
table has:

NAME VALUE
-------------------------------------
MobiLink User ML_Nathess1
Foreign key table COMMENTS
Primary key table CUSTOMER
Role name CUSTOMER
script version v1
publication_0 pub_nathess

The INSERT/SELECT statement above is indeed trying to insert
'ML_Nathess1' into the "value" column of your table which is a
timestamp. So the error message is bang-on.

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