|
Home > Archive > SQL Anywhere Mobile > August 2005 > uniqueidentifier upload_insert problem
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
uniqueidentifier upload_insert problem
|
|
| Bob Piskac 2005-06-03, 1:23 pm |
| SQL Anywhere V9.0.2.3124
ASA for Pocket PC V9.0.2.3044
MobiLink Server V9.0.2.3124
When I preform a upload_insert with a table that have a uniqueidentifier as
the primary key. The key value that is in the cosilidated database is
different then the one in the remote database.
Actual key value: 5e57bbbc-aa59-4755-88f2-98de2b4a3bcf
From MobiLink Server log
Wrong key: 35653537-6262-6263-2d61-6135392d3437
Why is the upload process changing the key value? The insert script is a
simple sql statement
call ml_add_table_script(
'msys 1.0', 'xxtable', 'upload_insert',
'insert into dba.xxtable (column1, column2, column3)
values(?, ?, ?')
| |
| Greg Fenton 2005-06-03, 8:24 pm |
| Bob Piskac wrote:
>
> Actual key value: 5e57bbbc-aa59-4755-88f2-98de2b4a3bcf
>
> From MobiLink Server log
> Wrong key: 35653537-6262-6263-2d61-6135392d3437
>
What is the column definition in both the remote and the consolidated?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Breck Carter 2005-06-03, 8:24 pm |
| It almost sounds like the consolidated primary key has
DEFAULT NEWID() and the column isn't named in the
upload_insert INSERT column name list.
Please show us the CREATE TABLE. If table schemas are
different, please show both.
Please show us the actual the actual upload_insert script.
Does the upload_insert script name the primary key column,
and does the INSERT column name list match the order of "?"
values coming from the remote database?
Breck
> SQL Anywhere V9.0.2.3124
> ASA for Pocket PC V9.0.2.3044
> MobiLink Server V9.0.2.3124
>
> When I preform a upload_insert with a table that have a
> uniqueidentifier as the primary key. The key value that
> is in the cosilidated database is different then the one
> in the remote database.
>
> Actual key value: 5e57bbbc-aa59-4755-88f2-98de2b4a3bcf
>
> From MobiLink Server log
> Wrong key: 35653537-6262-6263-2d61-6135392d3437
>
> Why is the upload process changing the key value? The
> insert script is a simple sql statement
>
> call ml_add_table_script(
'msys 1.0', 'xxtable',
> 'upload_insert', 'insert into dba.xxtable (column1,
> column2, column3)
> values(?, ?, ?')
>
>
| |
| Bob Piskac 2005-06-09, 7:24 am |
| Is it safe to assume that if the value is in the inserted log then it's
being used in the insert statement?
<Breck Carter> wrote in message news:42a0c442.781c.1681692777@sybase.com...
> It almost sounds like the consolidated primary key has
> DEFAULT NEWID() and the column isn't named in the
> upload_insert INSERT column name list.
>
> Please show us the CREATE TABLE. If table schemas are
> different, please show both.
>
> Please show us the actual the actual upload_insert script.
>
> Does the upload_insert script name the primary key column,
> and does the INSERT column name list match the order of "?"
> values coming from the remote database?
>
> Breck
>
| |
| Breck Carter [TeamSybase] 2005-06-09, 7:24 am |
| What do you mean by "inserted log"? The column value output produced
by dbmlsrv9 -v+, or the translated transaction log (dbtran)?
In both cases I think the answer is yes.
Breck
On 9 Jun 2005 04:52:31 -0700, "Bob Piskac" <Bob@pbsoftware.com> wrote:
>Is it safe to assume that if the value is in the inserted log then it's
>being used in the insert statement?
>
><Breck Carter> wrote in message news:42a0c442.781c.1681692777@sybase.com...
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Greg Fenton 2005-06-09, 9:24 am |
| Breck Carter [TeamSybase] wrote:
>
> In both cases I think the answer is yes.
>
Unless the "insert" is done in conjunction with a prior "delete" of a
row that already existed in the remote at the end of the previous
synchronization. Then the "insert" will be uploaded as an update.
Or am I misreading what you are asking (I also don't know what the
"insert log" refers to).
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Bob Piskac 2005-06-09, 11:24 am |
| I was talking about the -v + option on the mobilink server. I see the
uniqueidentifier being displayed with the incorrect value in the mobilink
server window.
"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:42a851fb$1@foru
ms-1-dub...
> Breck Carter [TeamSybase] wrote:
>
> Unless the "insert" is done in conjunction with a prior "delete" of a row
> that already existed in the remote at the end of the previous
> synchronization. Then the "insert" will be uploaded as an update.
>
> Or am I misreading what you are asking (I also don't know what the "insert
> log" refers to).
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Greg Fenton 2005-06-09, 1:23 pm |
| Bob Piskac wrote:
> I was talking about the -v + option on the mobilink server. I see the
> uniqueidentifier being displayed with the incorrect value in the mobilink
> server window.
>
Can you show use the CREATE TABLE and the CREATE PUBLICATION definitions
from the *remote* database for the table/publication in question?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Bob Piskac 2005-06-09, 8:24 pm |
|
create table hospital_rounds(
practice_id smallint not null,
provider_id smallint not null,
service_facility_id smallint not null,
line_no smallint not null,
guarantor_id integer null,
patient_no smallint null,
room_no varchar(6) null,
admit_date date null,
"name" varchar(37) null,
phone varchar(20) null,
note varchar(256) null,
discharge_date date null,
discharged char(1) null,
unit smallint null,
shift smallint null,
referring_doctor smallint null,
consulting_doctor smallint null,
temp_patient_id smallint null,
pda_patient_id uniqueidentifier null,
last_modified timestamp not null default timestamp,
hospital_rounds_id uniqueidentifier not null,
primary key(practice_id, provider_id, service_facility_id,
line_no,
hospital_rounds_id)
)
table hospital_rounds (practice_id, provider_id, service_facility_id,
line_no, guarantor_id, patient_no, room_no,
admit_date, "name", phone, note, discharge_date, discharged, unit,
shift, referring_doctor,
consulting_doctor, temp_patient_id, pda_patient_id, last_modified,
hospital_rounds_id),
call ml_add_table_script(
'msys 1.0', 'hospital_rounds', 'upload_insert',
'insert into dba.hospital_rounds (practice_id, provider_id,
service_facility_id,
line_no, guarantor_id, patient_no, room_no,
admit_date, "name", phone, note, discharge_date, discharged, unit,
shift, referring_doctor,
consulting_doctor, temp_patient_id, pda_patient_id, last_modified,
hospital_rounds_id)
values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
?)')
"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
news:42a87b60@forums
-1-dub...
> Bob Piskac wrote:
>
> Can you show use the CREATE TABLE and the CREATE PUBLICATION definitions
> from the *remote* database for the table/publication in question?
>
> greg.fenton
> --
> Greg Fenton
> Consultant, Solution Services, iAnywhere Solutions
> --------
> Visit the iAnywhere Solutions Developer Community
> Whitepapers, TechDocs, Downloads
> http://www.ianywhere.com/developer/
| |
| Breck Carter [TeamSybase] 2005-06-09, 8:24 pm |
| Are you saying the uploaded value is different from the value that is
in that row on the remote database?
Are you *sure* the uploaded value is different from the remote column
value *at the point in time* the sync started?
Is it possible some piece of code changed the value afterwards, like
an event or trigger?
This is unusual behaviour, never before noted IMO.
Breck
On 9 Jun 2005 13:02:59 -0700, "Bob Piskac" <Bob@pbsoftware.com> wrote:
>
>create table hospital_rounds(
> practice_id smallint not null,
> provider_id smallint not null,
> service_facility_id smallint not null,
> line_no smallint not null,
> guarantor_id integer null,
> patient_no smallint null,
> room_no varchar(6) null,
> admit_date date null,
> "name" varchar(37) null,
> phone varchar(20) null,
> note varchar(256) null,
> discharge_date date null,
> discharged char(1) null,
> unit smallint null,
> shift smallint null,
> referring_doctor smallint null,
> consulting_doctor smallint null,
> temp_patient_id smallint null,
> pda_patient_id uniqueidentifier null,
> last_modified timestamp not null default timestamp,
> hospital_rounds_id uniqueidentifier not null,
> primary key(practice_id, provider_id, service_facility_id,
line_no,
>hospital_rounds_id)
> )
>
> table hospital_rounds (practice_id, provider_id, service_facility_id,
>line_no, guarantor_id, patient_no, room_no,
> admit_date, "name", phone, note, discharge_date, discharged, unit,
>shift, referring_doctor,
> consulting_doctor, temp_patient_id, pda_patient_id, last_modified,
> hospital_rounds_id),
>
>call ml_add_table_script(
'msys 1.0', 'hospital_rounds', 'upload_insert',
>'insert into dba.hospital_rounds (practice_id, provider_id,
> service_facility_id,
line_no, guarantor_id, patient_no, room_no,
> admit_date, "name", phone, note, discharge_date, discharged, unit,
>shift, referring_doctor,
> consulting_doctor, temp_patient_id, pda_patient_id, last_modified,
>hospital_rounds_id)
> values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
>?)')
>
>"Greg Fenton" <greg. fenton_NOSPAM_@ianyw
here.com> wrote in message
> news:42a87b60@forums
-1-dub...
>
--
SQL Anywhere Studio 9 Developer's Guide
Buy the book: http://www.amazon.com/exec/obidos/A...7/risingroad-20
bcarter@risingroad.com
RisingRoad SQL Anywhere and MobiLink Professional Services
www.risingroad.com
| |
| Bob Piskac 2005-06-09, 8:24 pm |
|
"Breck Carter [TeamSybase]" < NOSPAM__bcarter@risi
ngroad.com> wrote in
message news:3qbha1dltqhj79c
6lfucabmqc6hm5let8t@
4ax.com...
> Are you saying the uploaded value is different from the value that is
> in that row on the remote database?
>
Yes.
> Are you *sure* the uploaded value is different from the remote column
> value *at the point in time* the sync started?
>
This happen on Pocket PC and PocketBuilder developer environment on win32
desktop.
1) I add record in ASA database using PocketBuilder.
2) Run Sybase central and look at new record
Actual key value: 5e57bbbc-aa59-4755-88f2-98de2b4a3bcf
3) Run sync and look at log
From MobiLink Server log
Wrong key: 35653537-6262-6263-2d61-6135392d3437
4) Look at consolidated database and new record with wrong key is there.
> Is it possible some piece of code changed the value afterwards, like
> an event or trigger?
>
I don't beleive that MobiLink server runs triggers on values that appear in
it's log.
> This is unusual behaviour, never before noted IMO.
>
Thats why I am here. I can run a webex session and show you.
| |
| Greg Fenton 2005-06-10, 3:24 am |
| Bob Piskac wrote:[color=darkred
]
> create table hospital_rounds(
> practice_id smallint not null,
> provider_id smallint not null,
> service_facility_id smallint not null,
> line_no smallint not null,
> guarantor_id integer null,
>
> ...
>
> hospital_rounds_id uniqueidentifier not null,
> primary key(practice_id, provider_id, service_facility_id,
line_no,
> hospital_rounds_id)[
/color]
One possible issue, though I cannot be 100% sure, is with the column
order of your table. Since you have the columns that comprise the
primary key in a different order than the actual definition of the PK,
it is possible that the values are being uploaded in a different order
than might be derived from the CREATE TABLE statement.
How did you derive the upload_insert script? Is this one originally
generated by MobiLink?
Can you sync with the SendColumnNames extended option enabled and
compare the column order (displayed in the ML log at the start of the
synchronization session if running with -v+) to the order of the
parameters in your upload_insert script?
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
| |
| Bob Piskac 2005-08-09, 8:26 pm |
| I finally figured it out. I had two different databases with two different
Capability ID's. Once I unloaded the database and reloaded it, it worked
fine.
What was the difference you ask.....
The database were different in the system tables of sysdomain and
sysusertype. They had different entries for uniqueidentifier.
| |
| Greg Fenton 2005-08-10, 11:26 am |
| Bob Piskac wrote:
>
> What was the difference you ask.....
>
> The database were different in the system tables of sysdomain and
> sysusertype. They had different entries for uniqueidentifier.
>
Thank you for posting back and letting us know this is resolved!
greg.fenton
--
Greg Fenton
Consultant, Solution Services, iAnywhere Solutions
--------
Visit the iAnywhere Solutions Developer Community
Whitepapers, TechDocs, Downloads
http://www.ianywhere.com/developer/
|
|
|
|
|