Mapping UNIQUEIDENTIFIER column in HANA

[former_member183029]

Hi,

I am using Mobilink to synchronize data between SQLA and HANA. I have UNIQUEIDENTIFIER as one of the columns in SQLA. What is the recomendation for Mapping in that case? If I use Varchar then from HANA, insert into SQLA is not possible. Is there any recomendation of usage of data type in that case?

Regards,

Subhankar

[former_member188493 · Accepted solution]

The MobiLink rules for mapping data types say that GUID in SQL Anywhere 16  corresponds to VARCHAR ( 40 ) in HANA.

That is a bit confusing since "GUID" is not a data type in SQL Anywhere. The suggestion of VARCHAR ( 40 ) is interesting since VARCHAR ( 36 ) is recommended for synchronizing UNIQUEIDENTIFIER with Oracle and IBM DB2.

Suggestion: Modify the Hello World test to upload and download a SQL Anywhere 16 UNIQUEIDENTIFIER column with a HANA VARCHAR ( 40 ) and verify that everything works OK.

[former_member183029 · Accepted solution]

Hi Breck,

I see that when I have UNIQUEIDENTIFIER as one column in SQLA and Varchar(36) as the corresponding column in HANA, it fails to upload the new row from SQLA to HANA.

It shows this error in the log.

E. 2014-11-25 10:02:53. Error code from MobiLink server: -10002

E. 2014-11-25 10:02:53. Server error: Message: ODBC: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;1033 error while parsing protocol: no such data type (ODBC State = S1000, Native error code = 1033).  Table Name: TEST_CHAR_TYPES_TABLE

My upload_insert script is the following :

CALL ml_add_table_script  ( 'v1', 'TEST_CHAR_TYPES_TABLE', 'upload_insert', '' );

CALL ml_add_table_script  ( 'v1', 'TEST_CHAR_TYPES_TABLE', 'upload_insert', '

INSERT INTO TEST_CHAR_TYPES_TABLE

       ( pkey,  col27 )

VALUES ( {ml r.pkey}, {ml r.col27} )' );

col27 is the uniqueidentifier column in question here. I am inserting the value on SQLA side using NEWID() method. I guess that is fine.

My assumption is that the upload_insert script will not be the simple one and need to do some data conversion before the data can be uploaded to HANA DB.

Any help on this will be helpful.

Regards,

Subhankar

[jeff_albion · Accepted solution]

Hi Subhankar,

I'm not sure about the specific HANA error you're encountering, but I can state that UNIQUEIDENTIFIER is a recognized and understood data type within MobiLink and uploads correctly to HANA within the following test:

-------------------

rem.sql (on SQL Anywhere)

create sequence seqGuidtest minvalue 2000000000 maxvalue 2999999999;

create table guidtest (

    pk integer primary key default (seqGuidtest.nextval),

    c1 uniqueidentifier);

create publication p1 ( table guidtest );

insert into guidtest (c1) values (newid());
insert into guidtest (c1) values (newid());
insert into guidtest (c1) values (newid());
insert into guidtest (c1) values (newid());
insert into guidtest (c1) values (newid());
commit;

-------------------

cons.sql (on HANA)

create column table "ML_USER"."guidtest" (
  "pk" integer primary key,
  "c1" VARCHAR(36)
);

call ml_add_table_script( 'v1', 'guidtest', 'upload_insert',
'insert into "ML_USER"."guidtest"
  ("pk", "c1")
   values ({ml r.pk}, {ml r.c1})'
);

call ml_add_table_script( 'v1', 'guidtest', 'upload_update',
'--{ml_ignore}'
);

call ml_add_table_script( 'v1', 'guidtest', 'upload_delete',
  '--{ml_ignore}'
);

call ml_add_table_script( 'v1', 'guidtest', 'download_cursor',
  'select "pk", "c1" FROM "ML_USER"."guidtest"'
);
call ml_add_table_script( 'v1', 'guidtest', 'download_delete_cursor',
  '--{ml_ignore}'
);

commit;

-------------------

The result: (the SQL Anywhere remote - dbmlsync log)

I. 2014-11-25 17:09:16. Table Upload Order: guidtest

I. 2014-11-25 17:09:16. Uploading table operations

I. 2014-11-25 17:09:16. Upload operations on table 'guidtest'

I. 2014-11-25 17:09:16. Insert row:

I. 2014-11-25 17:09:16.   <pk>: 2000000001

I. 2014-11-25 17:09:16.   <c1>: e309076e-63f6-4d2c-9623-56b8810ff1ae

I. 2014-11-25 17:09:16. Insert row:

I. 2014-11-25 17:09:16.   <pk>: 2000000002

I. 2014-11-25 17:09:16.   <c1>: ab73dddd-5628-42cb-ab1a-c16460ceda5b

I. 2014-11-25 17:09:16. Insert row:

I. 2014-11-25 17:09:16.   <pk>: 2000000003

I. 2014-11-25 17:09:16.   <c1>: 9c46303c-b004-4c2b-bd99-30f4fb7a317a

I. 2014-11-25 17:09:16. Insert row:

I. 2014-11-25 17:09:16.   <pk>: 2000000004

I. 2014-11-25 17:09:16.   <c1>: be0109ad-f40f-44de-814c-5e5976ab41c8

I. 2014-11-25 17:09:16. Insert row:

I. 2014-11-25 17:09:16.   <pk>: 2000000005

I. 2014-11-25 17:09:16.   <c1>: d36b3035-2116-4958-8143-9a90af96b854

(the MobiLink server - HANA server)

I. 2014-11-25 17:09:16. <1> upload_insert guidtest
                        insert into "ML_USER"."guidtest"

                          ("pk", "c1")

                           values ({ml r.pk}, {ml r.c1})
I. 2014-11-25 17:09:16. <1> Translated SQL:
                        insert into "ML_USER"."guidtest"

                          ("pk", "c1")

                           values ( ?,  ?)
I. 2014-11-25 17:09:16. <1> Insert row [guidtest]:
I. 2014-11-25 17:09:16. <1>   2000000001
I. 2014-11-25 17:09:16. <1>   e309076e63f64d2c962356b8810ff1ae
I. 2014-11-25 17:09:16. <1> Insert row [guidtest]:
I. 2014-11-25 17:09:16. <1>   2000000002
I. 2014-11-25 17:09:16. <1>   ab73dddd562842cbab1ac16460ceda5b
I. 2014-11-25 17:09:16. <1> Insert row [guidtest]:
I. 2014-11-25 17:09:16. <1>   2000000003
I. 2014-11-25 17:09:16. <1>   9c46303cb0044c2bbd9930f4fb7a317a
I. 2014-11-25 17:09:16. <1> Insert row [guidtest]:
I. 2014-11-25 17:09:16. <1>   2000000004
I. 2014-11-25 17:09:16. <1>   be0109adf40f44de814c5e5976ab41c8
I. 2014-11-25 17:09:16. <1> Insert row [guidtest]:
I. 2014-11-25 17:09:16. <1>   2000000005
I. 2014-11-25 17:09:16. <1>   d36b30352116495881439a90af96b854

----

Note the conversion between the SQL Anywhere GUID format with hyphens and the format in HANA without.

Some questions:

1. Can you provide some of the lines above the error you're seeing in the MobiLink console log to provide better context for the error?
2. Can you provide the table definitions for both the remote and consolidated?
3. Are you using the same version of SQL Anywhere for the remote database and the MobiLink server?
4. Which version of the HANA ODBC driver are you using?

Regards,

Jeff Albion

SAP Active Global Support

[former_member183029 · Accepted solution]

Hi Jeff,

Thank you for your support. It works now and upload happens both ways. However if I insert a string in HANA side which is less than 36 characters then it throws this error.

E. 2014-11-26 09:48:41. Error code from MobiLink server: -10216

E. 2014-11-26 09:48:41. Server error: Message: A downloaded value for table 'TEST_CHAR_TYPES_TABLE' (column #2) was an invalid unique identifier string.  Table Name: TEST_CHAR_TYPES_TABLE

E. 2014-11-26 09:48:41. Error code from MobiLink server: -10216

E. 2014-11-26 09:48:41. Server error: Message: A downloaded value for table 'TEST_CHAR_TYPES_TABLE' (column #2) was an invalid unique identifier string.  Table Name: TEST_CHAR_TYPES_TABLE

Which I guess is expected. If I insert 36 length string in HANA, everything works.

Regards,

Subhankar

[jeff_albion]

Hello,

If you upgrade the MobiLink server to be past CR #748725 ( http://search.sybase.com/kbx/changerequests?bug_id=748725 ) which is version 16.0.0.1699, you can use VARCHAR(36) in HANA to map UNIQUEIDENTIFIER instead. VARCHAR(40) is required for all builds below that version.

*Note: Not VARCHAR(32) like the description says - that is a typo.

Regards,

Jeff Albion

SAP Active Global Support

[former_member183029]

Hi Jeff,

My MobiLink Server Version is 16.0.0.1948 so Varchar(36) should be enough I guess. But still I am facing some problem while uploading data to HANA. Please see my reply to Breck. Any help on this will be helpful.

Regards,

Subhankar