MobiLink problem with data type conversion to Oracle

[Former Member]

Our client has a column defined in the Oracle database as SYS.XMLTYPE. That column is defined in our SQL Anywhere database as LONG VARCHAR.

An upload to Oracle produces the following error:

E. 2012-02-15 11:11:01. Error code from MobiLink server: -10002
E. 2012-02-15 11:11:01. Server error: Message: ODBC: [Sybase][iAnywhere Solutions - Oracle][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column
                         (ODBC State = HY000, Native error code = 1461).  Table Name: MEMO_SYNC_DETAILS.  Primary Key(s😞  ACKN  201
E. 2012-02-15 11:11:01. Error code from MobiLink server: -10002
E. 2012-02-15 11:11:01. Server error: Message: ODBC: [Sybase][iAnywhere Solutions - Oracle][Oracle]ORA-01461: can bind a LONG value only for insert into a LONG column
                         (ODBC State = HY000, Native error code = 1461).  Table Name: MEMO_SYNC_DETAILS.  Primary Key(s😞  ACKN  201

We do have a similar sync running with CLOB data type on Oracle, but our client prefers to keep this data type definition due to already completed programming on their end. Is there a solution short of changing the Oracle schema definition?

SQL Anywhere MobiLink Client Version 11.0.1.2652
MobiLink SQL Anywhere 11.0.1.2376 
Oracle 10G 10.2.0.1.0

Thanks, Bill

---

This should be the script corresponding to the error. (Several different versions have been tried).

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', NULL); 
CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', 
' INSERT INTO MEMO_SYNC_DETAILS ( ACTION_TYPE, MEMO_SYNC_SID, PAYLOAD, SEND_DATE, PROCESSED_DATE) 
SELECT 
{ml r.ACTION_TYPE}, 
{ml r.MEMO_SYNC_SID}, 
sys.xmltype.createxml({ml r.PAYLOAD}), 
{ml r.SEND_DATE}, 
{ml r.PROCESSED_DATE} 
from dual');

[Breck_Carter]

Please show us the MobiLink script.

[Breck_Carter]

Scroll down to "Example 4-5 Inserting into XMLTYpe Using the XMLType() Constructor" in http://docs.oracle.com/cd/B10500_01/appdev.920/a96620/xdb04cre.htm#1030582

An XMLType instance can be easily created from a VARCHAR or a Character Large Object (CLOB) by using the XMLType() constructor:

INSERT INTO warehouses VALUES ( 100, XMLType( '<warehouse whno="100"> <building>Owned</building> </warehouse>'), 'Tower Records', 1003);

This example creates an XMLType instance from a string literal. The input to createXML() can be any expression that returns a VARCHAR2 or CLOB. createXML() also checks that the input XML is well-formed.

[VolkerBarth]

Does the same problem happen when the SQL Anywhere database column is defined as LONG BINARY? (This is a very wild guess, particular as the SA XML data type seems to be more like a LONG VARCHAR...)

[Former Member]

This should be the script corresponding to the error. (Several different versions have been tried).

CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', NULL); CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', ' INSERT INTO MEMO_SYNC_DETAILS ( ACTION_TYPE, MEMO_SYNC_SID, PAYLOAD, SEND_DATE, PROCESSED_DATE) SELECT {ml r.ACTION_TYPE}, {ml r.MEMO_SYNC_SID}, sys.xmltype.createxml({ml r.PAYLOAD}), {ml r.SEND_DATE}, {ml r.PROCESSED_DATE} from dual');

[Breck_Carter]

Nothing appears wrong, except the part about it not working... 🙂

What versions of Oracle and SQL Anywhere?

Please show us the SQL Anywhere and Oracle tables, and that part of the publication.

Have you tried it with INSERT VALUES instead of SELECT?

Have you tried the abbreviated syntax XMLTYPE (...)?

Are you sure the column-to-column mapping is correct? (the error message doesn't name the offending column)

[Former Member]

SQL Anywhere MobiLink Client Version 11.0.1.2652 MobiLink SQL Anywhere 11.0.1.2376 Oracle 10G 10.2.0.1.0

Client-side table: CREATE TABLE app_owner.MEMO_SYNC_DETAILS ( -- 5 rows, 680k total = 4k table + 660k ext + 16k index, 139,264 bytes per row ACTION_TYPE / PK / VARCHAR ( 4 ) NOT NULL CONSTRAINT ASA947 CHECK ( ACTION_TYPE in( 'ACKN','SEND','TRSF' ) ), MEMO_SYNC_SID / PK FK / INTEGER NOT NULL, PAYLOAD LONG VARCHAR NULL, SEND_DATE TIMESTAMP NULL, PROCESSED_DATE TIMESTAMP NULL, CONSTRAINT ASA948 PRIMARY KEY ( -- 8k MEMO_SYNC_SID, ACTION_TYPE ) );

Part of the Publication: CREATE PUBLICATION "app_owner"."AGENCYTOMCFDPUBLICATION" (... TABLE "app_owner"."MEMO_SYNC_DETAILS" ("ACTION_TYPE","MEMO_SYNC_SID","PAYLOAD","SEND_DATE","PROCESSED_DATE") ...

[former_SQLA_member1694977]

Hi Breck, answers to your questions:

Versions: - Oracle 10g - SQL Anywhere MobiLink Client Version 11.0.1.2652 - MobiLink SQL Anywhere 11.0.1.2376

Tables: - by showing you the tables, what are you asking for? - The definition of the tables?

Variations used: 1) CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', ' INSERT INTO MEMO_SYNC_DETAILS ( ACTION_TYPE, MEMO_SYNC_SID, PAYLOAD, SEND_DATE, PROCESSED_DATE) VALUES ( {ml r.ACTION_TYPE}, {ml r.MEMO_SYNC_SID}, {ml r.PAYLOAD}, {ml r.SEND_DATE}, {ml r.PROCESSED_DATE})'); 2) CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', ' INSERT INTO MEMO_SYNC_DETAILS ( ACTION_TYPE, MEMO_SYNC_SID, PAYLOAD, SEND_DATE, PROCESSED_DATE) SELECT {ml r.ACTION_TYPE}, {ml r.MEMO_SYNC_SID}, TO_LOB({ml r.PAYLOAD}), {ml r.SEND_DATE}, {ml r.PROCESSED_DATE} from dual'); 3) CALL ml_add_table_script('ScriptVersion12.00.00', 'MEMO_SYNC_DETAILS', 'upload_insert', ' INSERT INTO MEMO_SYNC_DETAILS ( ACTION_TYPE, MEMO_SYNC_SID, PAYLOAD, SEND_DATE, PROCESSED_DATE) SELECT {ml r.ACTION_TYPE}, {ml r.MEMO_SYNC_SID}, sys.xmltype.createxml({ml r.PAYLOAD}), {ml r.SEND_DATE}, {ml r.PROCESSED_DATE} from dual');

Abbreviated syntax: - I will need to ask the developer before answering

Mapping: - I will ask the developer to confirm, then answer

[Former Member]

I am posting the info I have from the client side, someone else will post from the condsolidated side. And how the heck do I use the editor on comments to get the formatting right when pasting in content??

SQL Anywhere MobiLink Client Version 11.0.1.2652 MobiLink SQL Anywhere 11.0.1.2376 Oracle 10G 10.2.0.1.0

Client-side table: CREATE TABLE app_owner.MEMO_SYNC_DETAILS ( -- 5 rows, 680k total = 4k table + 660k ext + 16k index, 139,264 bytes per row ACTION_TYPE / PK / VARCHAR ( 4 ) NOT NULL CONSTRAINT ASA947 CHECK ( ACTION_TYPE in( 'ACKN','SEND','TRSF' ) ), MEMO_SYNC_SID / PK FK / INTEGER NOT NULL, PAYLOAD LONG VARCHAR NULL, SEND_DATE TIMESTAMP NULL, PROCESSED_DATE TIMESTAMP NULL, CONSTRAINT ASA948 PRIMARY KEY ( -- 8k MEMO_SYNC_SID, ACTION_TYPE ) );

Part of the Publication: CREATE PUBLICATION "app_owner"."AGENCYTOMCFDPUBLICATION" (... TABLE "app_owner"."MEMO_SYNC_DETAILS" ("ACTION_TYPE","MEMO_SYNC_SID","PAYLOAD","SEND_DATE","PROCESSED_DATE") ...

[Former Member]

I did some experiments and found it works, if we write the upload_insert and download_cursor scripts in the following way:

Assume we have a synchronization table, test that is defined as

CREATE TABLE test (
    pk integer   primary key not null,
    c1 integer,
    c2 long varchar )

in the remote databas, and

CREATE TABLE test (
    pk integer   primary key not null,
    c1 integer,
    c2 XMLType )

in the Oracle consolidated database.

Here are the upload_insert and download_cursor scripts for the test table

call ml_add_table_script( 'my_version', 'test', 'upload_insert',
    'declare p_pk integer; p_c1 integer; p_c2 clob; v_c2 xmltype; 
    begin 
    p_pk := {ml r.pk}; 
    p_c1 := {ml r.c1}; 
    p_c2 := {ml r.c2}; 
    v_c2 := XMLTYPE.createXML( p_c2 ); 
    insert into test values( p_pk, p_c1, v_c2 ); 
    end;' );

and

call ml_add_table_script( 'my_version', 'test', 'download_cursor',
    'select pk,c1,XMLSERIALIZE( content c2 ) from test' );

I also need to tell the MobiLink server the data types of the columns in test, because I am using named-parameters in the upload and download scripts. So calling the following SQL statements to add the column types of the test table into the ml_column table in Oracle

call ml_add_column( 'my_version', 'test', 'pk', 'INT' );
call ml_add_column( 'my_version', 'test', 'c1', 'INT' );
call ml_add_column( 'my_version', 'test', 'c2', 'CLOB' );

[Breck_Carter]

Wow! ...sometimes you need a BIG hammer! 🙂

[VolkerBarth]

...fitting the BIG IRON:)

[Former Member]

thanks Guys for your comprehensive, quick response. I tested this and it worked for all but one of our test cases. The test case that we're still failing on includes a very large payload (in excess of >33K). We're getting the following error:

E. 2012-02-16 12:20:57. <3126> [-10002] Consolidated database server or ODBC error: ODBC: [Sybase][iAnywhere Solutions - Oracle][Oracle]ORA-06550: line 1, column 37: PLS-00215: String length constraints must be in range (1 .. 32767)

Would you be willing to retest your posted solution with a very large payload.

[Former Member]

I did a test and tried to upload a 64K XML string and I got an error from Oracle, "ORA-01460: unimplemented or unreasonable conversion requested" We may need to find another Oracle conversion function. I am currently using SA12.0.1 and Oracle 11g.

What versions of SA and Oracle software are you using?

[Former Member]

Okay, here is a work around and the steps are:

1) Create a global temporary table in the Oracle consolidated database, as

create global temporary table my_temp (
    pk int not null primary key,
    c1 int not null,
    c2 clob );

we still use the same table definitions as described in my previous post.

2) Create an upload_insert script to upload the rows into the temporary table,

call ml_add_table_script( 'my_version', 'test', 'upload_insert', 'insert into my_temp values( ?,?,? )' );

3) Create an end_upload_rows script to convert the data from clob to XML,

call ml_add_table_script( 'my_version', 'test', 'end_upload_rows', 'insert into test (pk,c1,c2) (select pk,c1,XMLTYPE.createXML(c2) from my_temp' );

4) The previous download_cursor script works fine and it does not need to be changed.

This work-around works well on my system, when the data length of the xml column is less than 100 MB. But Oracle gives me an error, "ORA-27163: out of memory" when the MobiLink server was trying to execute the end_upload_rows script. If this problem occurs, you may need to increase the amoun of memory on your system.

[Former Member]

That's great. Thanks again.