|
Using MobiLink and UltraLiteJ, is there a requirement that the schema on the remote database exactly match the schema on the consolidated database? I have a case where the tables being synchronized have the same columns but are in a different order; and I'm using the scripts generated by the Synchronization Model Wizard. The synchronization fails because MobiLink tries to insert the column values in the wrong order. Here are the details: Consolidated table definition: CREATE TABLE "DBA"."BAL_ReportDataTypes" (
"ReportDataTypeID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"Name" VARCHAR(255) NOT NULL,
"Category" VARCHAR(255) NOT NULL,
"DisplayType" VARCHAR(255) NOT NULL,
"Unit" VARCHAR(255) NULL,
"ThresholdValue" FLOAT NULL,
"DeviationValue" FLOAT NULL,
"ThresholdType" VARCHAR(255) NULL,
"SiteTabletID" INTEGER NOT NULL,
PRIMARY KEY ( "ReportDataTypeID", "SiteTabletID" )
);
UltraLiteJ table definition: CREATE OR REPLACE TABLE "BAL_ReportDataTypes" (
"ReportDataTypeID" INTEGER NOT NULL DEFAULT AUTOINCREMENT,
"SiteTabletID" INTEGER NOT NULL,
"Name" VARCHAR(255) NOT NULL,
"Category" VARCHAR(255) NOT NULL,
"DisplayType" VARCHAR(255) NOT NULL,
"Unit" VARCHAR(255),
"ThresholdValue" REAL,
"DeviationValue" REAL,
"ThresholdType" VARCHAR(255),
PRIMARY KEY ("ReportDataTypeID", "SiteTabletID"),
);
Note that the column "SiteTabletID" is second in the UltraLiteJ database and last in the consolidated database.
The 'upload_insert' script is: BAL_ReportDataTypes (DBA): upload_insert​
/ Insert the row into the consolidated database. /
INSERT INTO "DBA"."BAL_ReportDataTypes" ( "ReportDataTypeID", "Name", "Category", "DisplayType", "Unit", "ThresholdValue", "DeviationValue", "ThresholdType", "SiteTabletID" )
VALUES ( {ml r."ReportDataTypeID"}, {ml r."Name"}, {ml r."Category"}, {ml r."DisplayType"}, {ml r."Unit"}, {ml r."ThresholdValue"}, {ml r."DeviationValue"}, {ml r."ThresholdType"}, {ml r."SiteTabletID"} )
The error occurs when 'uploading' the following row from the UltraLiteJ table: 1, 1, 'Hand_Strength_Left', 'STRENGTH', 'VALUE_ONLY', 'KILOGRAMS', 0, 0, null The MobiLink log contains the following: ...
17:36:37 upload_insert BAL_ReportDataTypes
17:36:37 / Insert the row into the consolidated database. /
17:36:37 INSERT INTO "DBA"."BAL_ReportDataTypes" ( "ReportDataTypeID", "Name", "Category", "DisplayType", "Unit", "ThresholdValue", "DeviationValue", "ThresholdType", "SiteTabletID" )
17:36:37 VALUES ( {ml r."ReportDataTypeID"}, {ml r."Name"}, {ml r."Category"}, {ml r."DisplayType"}, {ml r."Unit"}, {ml r."ThresholdValue"}, {ml r."DeviationValue"}, {ml r."ThresholdType"}, {ml r."SiteTabletID"} )
17:36:37 Translated SQL:
17:36:37 INSERT INTO "DBA"."BAL_ReportDataTypes" ( "ReportDataTypeID", "Name", "Category", "DisplayType", "Unit", "ThresholdValue", "DeviationValue", "ThresholdType", "SiteTabletID" )
17:36:37 VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ? )
17:36:37 Insert row [BAL_ReportDataTypes]:
17:36:37 1
17:36:37 1
17:36:37 Hand_Strength_Left
17:36:37 STRENGTH
17:36:37 VALUE_ONLY
17:36:37 KILOGRAMS
17:36:37 0
17:36:37 0
17:36:37 NULL
17:36:37 [-10002] Consolidated database server or ODBC error: ODBC: [SAP][ODBC Driver][SQL Anywhere]Cannot convert 'KILOGRAMS' to double (ODBC State = 07006, Native error code = -157)
17:36:37 [-10072] Unable to insert into table 'BAL_ReportDataTypes' using upload_insert
...
As you can see, while the 'upload_insert' script specifies the columns in the consolidated database order, the generated VALUES are in the UltraLiteJ database order.
I should note that the failure only occurs because of a data-type mismatch. If not for that, the data in the consolidated database table would have been silently corrupted :-( Is this a bug? Is the workaround to manually change the generated 'upload_insert' script in some way? What about the other scripts such as 'upload_update' and 'upload_delete'? Terry. PS: using MobiLink (17.0.0.1211) under Windows Server 2012, and UltraLiteJNI17.jar on an Android from that same distro. |
|
A similar discussion took place on the SAP SCN recently. I would suggest reading over that thread, and then testing whether removing all the rows from the ml_column table resolves the issue. Reg Reg, that reference was helpful, but your comment: Please keep in mind that if you ever decide you want to download data to the remote databases, this environment you have with different ordering of columns from remote to remote is GUARANTEED to cause data integrity issues. was scary! I seem to be missing something, but why does it need to care at all about column order? The generated event scripts specify the columns being processed, by name. Does the client not upload the column names as well as the data? My use-case is a SA17 consolidated database with UltraLiteJ clients on Android tablets. Does this mean that, whenever there is a schema change to one of the synchronized tables, both the consolidated database and all the UltraLiteJ databases need to be modified simultaneously?
(16 Dec '15, 14:55)
Terry Wilkinson
Replies hidden
My scary quote was mostly meant for the distinct situation that the customer was running into, and would only work for them because they were not downloading data to the remote database. If you follow this simple rule, the scary comment can be ignored : When you define a MobiLink script version, all the remote databases using that script version must have the EXACT same schema, including the order of the columns in the tables. The remote database does upload the column names and order as part of the upload, but if you have populated the ml_column table (the Synchronization Model does this), then the column names and order in the upload are ignored, and the MobiLink Server assumes that the information in the ml_column table is correct. If you make a schema change to the consolidated and/or remote database, you should create a new MobiLink script version. Reg
(16 Dec '15, 15:30)
Reg Domaratzki
Using different versions sounds good - hadn't thought of that (I'm pretty new to MobiLink). However, will the old scripts continue to work even though I have changed the consolidated database schema? I know that if I try to setup a synchronization mapping that does not use all the columns in the consolidated database, I get a message saying "If you plan to use UltraLite remote databases, then you must include all columns for synchronized tables. Excluding a column will prevent you from using UltraLite remote databases." Terry
(16 Dec '15, 15:51)
Terry Wilkinson
No, that's what different script versions are for. Besides that, you should be fine to add (non-synchronized) columns to synchronized tables in the consolidated database without impacting the sync design - as long as these new columns are not used in ml scripts. CAVEAT: IHMO that's true for normal ML setups with full SQL Anywhere remotes. I'm not fully sure whether it is true for UltraLite remotes but I guess it is as you are quite free to have different schemas on the consolidated and remote site - see that doc topic. A relevant restriction for UL clients is that they cannot synchronize only a subset of the columns of a published table, to cite:
(17 Dec '15, 03:54)
Volker Barth
Thanks for the reference to use MobiLink scripts to resolve those differences. The warning message I referenced in another comment, led me to think that there was no way to resolve them. I'm getting a better understanding now (dangerous assumption I know!). So ... I assume I can proceed as follows:
Now I just need to verify all these assumptions! Thanks to everyone for all your help.
(17 Dec '15, 11:22)
Terry Wilkinson
Regarding your point 3: current clients send a hash of the schema with every sync, but older clients would send the actual schema with every sync. The column and table names can be large, so by default the old clients didn't send them, and instead relied on the ml_column table. With modern clients that send a hash of the schema there's no real cost to sending the column names, so they always do and the ml_column table is redundant, but it had to stick around to support older clients that don't send the column names. With version 17 clients, we will always get the column names, so it should be safe to not have anything in the ml_column table.
(17 Dec '15, 14:10)
Bill Somers
EDIT: and make sure you restart the MobiLink Server service after deleting the contents of 'ml_column', and in fact after any deployment!
(17 Dec '15, 15:12)
Terry Wilkinson
> why does it need to care at all about column order? In theory, if MobiLink was implemented differently, it could download rows as SQL INSERT/UPDATE statements that would be immune to differences in column ordering. It would NOT be immune to other differences, however, such as column name differences, dropped columns, type differences and so on. The most likely reason that MobiLink does NOT do that is efficiency... and the fact that AFAIK 99.999% of the use cases preserve column order among remotes.
(17 Dec '15, 16:15)
Breck Carter
|
|
Hi Terry, A few questions:
Thanks for the reply. 1) restarting MobiLink server had no effect. 2) I have worked around the problem temporarily by making the column order the same on both tables, but that will not work as a long-term solution. 3) there are lots of rows in the 'ml_column' table, put there (I assume) by the 'deploy' operation. I've not looked at this table before, but I see an 'idx' column which may be relevant. If it would help, I could revert my workaround and then look at 'ml_column' more closely? 4) I'm not sure I understand the last comment in your answer. Do you mean I should arbitrarily delete the contents of 'ml_column' after deploying the ML project? Thanks, Terry
(16 Dec '15, 14:27)
Terry Wilkinson
|
