cancel
Showing results for 
Search instead for 
Did you mean: 

Mobilink Synchronization for ST_GEOMETRY column between SQL Anywhere and HANA

former_member183029
Participant
0 Kudos

Hi,

I am trying to write synchronization script for ST_GEOMETRY column to synchronize between HANA and SQL Anywhere.

My download script is as follows :

col35 is the ST_GEOMETRY column.

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

CALL ml_add_table_script ( 'v1', 'TEST_CHAR_TYPES_TABLE', 'download_cursor', 'SELECT TEST_CHAR_TYPES_TABLE.pkey, TEST_CHAR_TYPES_TABLE.col35.ST_AsBinary(), TEST_CHAR_TYPES_TABLE.col35.ST_SRID()

FROM TEST_CHAR_TYPES_TABLE

INNER JOIN TEST_CHAR_TYPES_TABLE_upserted

ON TEST_CHAR_TYPES_TABLE_upserted.pkey

= TEST_CHAR_TYPES_TABLE.pkey

WHERE TEST_CHAR_TYPES_TABLE_upserted.upserted_timestamp

>= {ml s.last_table_download}' );

And download from HANA to SQLA works!!

But I couldnot write Upload script and make it work.

I tried with this :

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,

col35 )

VALUES ( {ml r.pkey},

ST_Geometry::ST_GeomFromBinary({ml r.col35:data},{ml r.col35:srid}) )' );

It does not work.

also INSERT INTO test VALUES( {ml r.c1}, GeometryFromWKB({ml r.c2:data},{ml r.c2:srid}) ) did not work. Can someone please help me out in this?

Do we have a document somewhere to write these scripts ?

Regards,

Subhankar

Accepted Solutions (1)

Accepted Solutions (1)

JasonHinsperger
Advisor
Advisor
0 Kudos

Bi-directional sync, of spatial data with HANA could not be supported by MobiLink prior to SPS 09.  Now that SPS09 is released, we are completing full QA testing of the spatial sync. components with HANA. If/when that completes successfully, we will be able to announce support for spatial sync with HANA, including examples.

That said, just because we haven't fully tested doesn't mean it doesn't actually work with SPS09.  The MobiLink upload insert script defines the process for inserting into the consolidated database, and so the SQL you write must use the consilidated database SQL syntax.  In your testing, you need to use the HANA spatial functions to convert the upload binary data to a HANA geometry type.

For HANA, the correct function to convert binary to geometry is ST_GeomFromWKB(), so I think the following should work for you in your test case:

INSERT INTO TEST_CHAR_TYPES_TABLE

( pkey,

col35 )

VALUES ( {ml r.pkey},

ST_GeomFromWKB({ml r.col35:data},{ml r.col35:srid}) )' );

One final note.  If the srid value is NULL, the INSERT statement into HANA will fail with an error.  If you expect this may occur, you should use a stored procedure rather than a direct INSERT statement as the upload script.  Then, in the procedure, you can do the appropriate checking for NULLs to ensure the insert is successful.

Hope this helps,

--Jason

former_member183029
Participant
0 Kudos

Hi Jason,

Thank you very much for your reply. I tried with using ST_GeomFromWKB() method but it fails with the following error in the log

E. 2014-11-26 23:00:36. Error code from MobiLink server: -10002

E. 2014-11-26 23:00:36. Server error: Message: ODBC: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10404 Conversion not supported for parameter/column (2), source type BINARY and target type STRING (ODBC State = S1000, Native error code = -10404).  Table Name: TEST_CHAR_TYPES_TABLE

E. 2014-11-26 23:00:36. Error code from MobiLink server: -10002

E. 2014-11-26 23:00:36. Server error: Message: ODBC: [SAP AG][LIBODBCHDB DLL][HDBODBC] General error;-10404 Conversion not supported for parameter/column (2), source type BINARY and target type STRING (ODBC State = S1000, Native error code = -10404).  Table Name: TEST_CHAR_TYPES_TABLE

I tried without giving the srid value but still the same result.

Regards,

Subhankar

former_member183029
Participant
0 Kudos

One observation on this,

I just checked my HANA version by

select * from "SYS"."M_DATABASE"

It shows 1.00.74.00.390550 as VERSION which means the HANA instance I am working on is SPS7. Is that the reason why upload script is not working but the download script works?

Regards,

Subhankar

JasonHinsperger
Advisor
Advisor
0 Kudos

Yes, you need to wait for SPS 09, which is due out any time now.

--Jason

former_member183029
Participant
0 Kudos

Hi Jason,

Just grabbed an SP9 system and checked. It works! Thanks for your help.

Regards,

Subhankar

JasonHinsperger
Advisor
Advisor
0 Kudos

Excellent! Good to hear.

--Jason

Answers (0)