SQL Anywhere database - Binary Datatype

Author

Binary Datatype

Ashish Gupta

2005-07-07, 8:24 pm

I am running two databases on same box. I have triggers in
place to copy data from main database to backup database.
The copy operation is accomplished by inserting into remote
database using insert/update/delete statements inside the
triggers. The problem is with the binary fields. The binary
fields reconverts binary data as if the data is in string
format. I could not find a function to convert it back into
original format before issuing an insert statement from the
trigger.

Greg Fenton

2005-07-08, 3:23 am

Ashish Gupta wrote:
> The problem is with the binary fields. The binary
> fields reconverts binary data as if the data is in string
> format. I could not find a function to convert it back into
> original format before issuing an insert statement from the
> trigger.

Please always post the version and build number of SQLAnywhere that you
are using (e.g. for SQLAnywhere 9, use the command "dbeng9 -v")

Can you show us your trigger code and the remote server/proxy table
definitions?

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-07-08, 7:23 am

In addition to what Greg asked for (table definitions, trigger code),
please tell us how you know the binary data is being converted.

Breck

On 7 Jul 2005 16:52:28 -0700, Ashish Gupta wrote:

>I am running two databases on same box. I have triggers in
>place to copy data from main database to backup database.
>The copy operation is accomplished by inserting into remote
>database using insert/update/delete statements inside the
>triggers. The problem is with the binary fields. The binary
>fields reconverts binary data as if the data is in string
>format. I could not find a function to convert it back into
>original format before issuing an insert statement from the
>trigger.

--
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

Ashish Gupta

2005-07-08, 11:23 am

The databases are running on ASA 9.02.3044 version.
The table definition for both remote and local database is
create table person_image
(
person_image_id integer not
null ,
person_id integer,
patient_id char(36),
image long binary,
constraint PK_PERSON_IMAGE primary key (person_image_id)
);

The remote table is mapped as person_image_proxy on local
database.

The insert trigger looks like this

CREATE TRIGGER tI_person_image After INSERT on person_image
REFERENCING NEW As newrows
for each row
BEGIN

insert into person_image_proxy
(person_image_id,
person_id,
patient_id,
image)
values
(newrows.person_image_id,
newrows.person_id,
newrows.patient_id,
newrows.image);
END;

for testing I inserted '1234' as data value for image field.
The data was verified on both local and remote database. The
data stored in remote and local database is different. It
looks like the data is stored in hex format in both
databases and I expected it to be the same.

> In addition to what Greg asked for (table definitions,
> trigger code), please tell us how you know the binary data
> is being converted.
>
> Breck
>
> On 7 Jul 2005 16:52:28 -0700, Ashish Gupta wrote:
>
> in >place to copy data from main database to backup
> database. >The copy operation is accomplished by
> inserting into remote >database using insert/update/delete
> statements inside the >triggers. The problem is with the
> binary fields. The binary >fields reconverts binary data
> as if the data is in string >format. I could not find a
> function to convert it back into >original format before
> issuing an insert statement from the >trigger.
>
> --
> 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

Breck Carter [TeamSybase]

2005-07-08, 8:25 pm

Please answer the question: Exactly *how* do you know the data is
different?

How are you displaying the data? Are you using the same technique to
show the data in both databases?

For example, ISQL defaults to showing binary columns in hexadecimal
format, but that is ONLY the output display format. Binary and
character columns are stored in exactly the same way, inside the
database.

SELECT CAST ( "image" AS VARCHAR ) FROM person_image;

On 8 Jul 2005 09:47:36 -0700, Ashish Gupta wrote:
[color=darkred]
>The databases are running on ASA 9.02.3044 version.
>The table definition for both remote and local database is
>create table person_image
>(
> person_image_id integer not
>null ,
> person_id integer,
> patient_id char(36),
> image long binary,
> constraint PK_PERSON_IMAGE primary key (person_image_id)
> );
>
>The remote table is mapped as person_image_proxy on local
>database.
>
>The insert trigger looks like this
>
>CREATE TRIGGER tI_person_image After INSERT on person_image
>REFERENCING NEW As newrows
>for each row
>BEGIN
>
> insert into person_image_proxy
> (person_image_id,
> person_id,
> patient_id,
> image)
> values
> (newrows.person_image_id,
> newrows.person_id,
> newrows.patient_id,
> newrows.image);
>END;
>
>for testing I inserted '1234' as data value for image field.
>The data was verified on both local and remote database. The
>data stored in remote and local database is different. It
>looks like the data is stored in hex format in both
>databases and I expected it to be the same.
>
>
>http://www.amazon.com/exec/obidos/A...7/risingroad-20

--
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

Ashish Gupta

2005-07-11, 11:23 am

After executing the following insert statement

insert into person_image(person_
id, image) values(5,'1234')

The row in person_image at local database looks like this
person_image_id,pers
on_id,patient_id,ima
ge
18,5,,0x31323334

The row in person_image at remote table looks like this
person_image_id,pers
on_id,patient_id,ima
ge
18,5,,0x333133323333
3334

After execution of following statment on local and remote
database the results were different

SELECT CAST ( "image" AS VARCHAR ) FROM person_image where
person_image_id = 18;

On local database
image
'1234'

On remote database
image
'31323334'

It looks like it considered the hex value of '1234' as
string and converted it into hex on remote database.

I am doing this for testing but in actual database the
binary image will be stored at this location.

Thanks

-Ashish

> Please answer the question: Exactly *how* do you know the
> data is different?
>
> How are you displaying the data? Are you using the same
> technique to show the data in both databases?
>
> For example, ISQL defaults to showing binary columns in
> hexadecimal format, but that is ONLY the output display
> format. Binary and character columns are stored in exactly
> the same way, inside the database.
>
>
> SELECT CAST ( "image" AS VARCHAR ) FROM person_image;
>
>
> On 8 Jul 2005 09:47:36 -0700, Ashish Gupta wrote:
>
> is >create table person_image
> not >null ,
> (person_image_id) > );
> person_image >REFERENCING NEW As newrows
> field. >The data was verified on both local and remote
> database. The >data stored in remote and local database is
> different. It >looks like the data is stored in hex format
> in both >databases and I expected it to be the same.
> data >> is being converted.
> triggers >> in >place to copy data from main database to
> backup >> database. >The copy operation is accomplished
> by >> inserting into remote >database using
> insert/update/delete >> statements inside the >triggers.
> The problem is with the >> binary fields. The binary
> string >format. I could not find a >> function to convert
> it back into >original format before >> issuing an insert
> statement from the >trigger. >>
> oad-20 >> bcarter@risingroad.com
> Services >> www.risingroad.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

Breck Carter [TeamSybase]

2005-07-11, 1:23 pm

You are displaying the results via ISQL, correct?

Breck

On 11 Jul 2005 08:52:37 -0700, Ashish Gupta wrote:

>After executing the following insert statement
>
>insert into person_image(person_
id, image) values(5,'1234')
>
>
>The row in person_image at local database looks like this
> person_image_id,pers
on_id,patient_id,ima
ge
>18,5,,0x31323334
>
>The row in person_image at remote table looks like this
> person_image_id,pers
on_id,patient_id,ima
ge
> 18,5,,0x333133323333
3334
>
>
>After execution of following statment on local and remote
>database the results were different
>
>SELECT CAST ( "image" AS VARCHAR ) FROM person_image where
>person_image_id = 18;
>
>
>On local database
>image
>'1234'
>
>On remote database
>image
>'31323334'
>
>It looks like it considered the hex value of '1234' as
>string and converted it into hex on remote database.
>
>I am doing this for testing but in actual database the
>binary image will be stored at this location.
>
>
>Thanks
>
>-Ashish

--
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

Ashish Gupta

2005-07-11, 8:23 pm

Yes

> You are displaying the results via ISQL, correct?
>
> Breck
>
> On 11 Jul 2005 08:52:37 -0700, Ashish Gupta wrote:
>
> ,'1234') >
> where >person_image_id = 18;
>
> --
> 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-07-12, 9:23 am

Ashish Gupta wrote:[color=darkred
]
> Yes
>
>

Can you turn request-level logging on for both databases and see the
exact INSERT statement that is being passed to each engine?

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-07-12, 9:23 am

I cannot reproduce your symptom on 9.0.2.3124.

First of all, this statement gives "person_image_id cannot be NULL":

insert into person_image(person_
id, image) values(5,'1234')

But never mind that... the code shown below displays 0x31323334 for
all three tables: the base tables on both databases, and the proxy
table.

Breck

-----------------------------------------------------------------------
-- On database d2.

IF EXISTS ( SELECT *
FROM SYS.SYSTABLE
WHERE USER_NAME ( SYS.SYSTABLE.creator ) = CURRENT USER
AND SYS.SYSTABLE.table_name = 'person_image'
) THEN
DROP TABLE person_image;
END IF;

create table person_image
(
person_image_id integer not null ,
person_id integer,
patient_id char(36),
image long binary,
constraint PK_PERSON_IMAGE primary key (person_image_id)
);

-----------------------------------------------------------------------
-- On database d1.

IF EXISTS ( SELECT *
FROM SYSTABLE
WHERE USER_NAME ( SYSTABLE.creator ) = CURRENT USER
AND SYSTABLE.table_name =
'person_image_proxy'
) THEN
DROP TABLE person_image_proxy;
END IF;

IF EXISTS ( SELECT *
FROM SYS.SYSSERVERS,
SYSEXTERNLOGINS
WHERE SYSSERVERS.srvid =
SYSEXTERNLOGINS.srvid
AND SYSSERVERS.srvname = 'd2'
AND USER_NAME ( SYSEXTERNLOGINS.user_id ) = 'dba' )
THEN
DROP EXTERNLOGIN dba TO d2;
END IF;

IF EXISTS ( SELECT *
FROM SYS.SYSSERVERS
WHERE SYSSERVERS.srvname = 'd2' ) THEN
DROP SERVER d2;
END IF;

CREATE SERVER d2 CLASS 'ASAODBC'
USING 'DRIVER=Adaptive Server Anywhere 9.0;ENG=d2;DBN=d2';

CREATE EXTERNLOGIN dba TO d2
REMOTE LOGIN dba IDENTIFIED BY 'sql';

CREATE EXISTING TABLE person_image_proxy
AT 'd2..dba.person_image';

IF EXISTS ( SELECT *
FROM SYS.SYSTABLE
WHERE USER_NAME ( SYS.SYSTABLE.creator ) = CURRENT USER
AND SYS.SYSTABLE.table_name = 'person_image'
) THEN
DROP TABLE person_image;
END IF;

create table person_image
(
person_image_id integer not null ,
person_id integer,
patient_id char(36),
image long binary,
constraint PK_PERSON_IMAGE primary key (person_image_id)
);

CREATE TRIGGER tI_person_image After INSERT on person_image
REFERENCING NEW As newrows
for each row
BEGIN

insert into person_image_proxy
(person_image_id,
person_id,
patient_id,
image)
values
(newrows.person_image_id,
newrows.person_id,
newrows.patient_id,
newrows.image);
END;

/* Error: person_image_id cannot be NULL.
insert into person_image(person_
id, image) values(5,'1234')
*/

insert into person_image values(1,5,'x','1234
')
COMMIT;

BEGIN
SELECT * FROM person_image; -- 0x31323334
SELECT * FROM person_image_proxy; -- 0x31323334
END;

-----------------------------------------------------------------------
-- On database d2.

SELECT * FROM person_image; -- 0x31323334

/*
I am running two databases on same box. I have triggers in
place to copy data from main database to backup database.
The copy operation is accomplished by inserting into remote
database using insert/update/delete statements inside the
triggers. The problem is with the binary fields. The binary
fields reconverts binary data as if the data is in string
format. I could not find a function to convert it back into
original format before issuing an insert statement from the
trigger.

The databases are running on ASA 9.02.3044 version.
The table definition for both remote and local database is
create table person_image
(
person_image_id integer not
null ,
person_id integer,
patient_id char(36),
image long binary,
constraint PK_PERSON_IMAGE primary key (person_image_id)
);

The remote table is mapped as person_image_proxy on local
database.

The insert trigger looks like this

CREATE TRIGGER tI_person_image After INSERT on person_image
REFERENCING NEW As newrows
for each row
BEGIN

insert into person_image_proxy
(person_image_id,
person_id,
patient_id,
image)
values
(newrows.person_image_id,
newrows.person_id,
newrows.patient_id,
newrows.image);
END;

for testing I inserted '1234' as data value for image field.
The data was verified on both local and remote database. The
data stored in remote and local database is different. It
looks like the data is stored in hex format in both
databases and I expected it to be the same.

After executing the following insert statement

insert into person_image(person_
id, image) values(5,'1234')

The row in person_image at local database looks like this
person_image_id,pers
on_id,patient_id,ima
ge
18,5,,0x31323334

The row in person_image at remote table looks like this
person_image_id,pers
on_id,patient_id,ima
ge
18,5,,0x333133323333
3334

After execution of following statment on local and remote
database the results were different

SELECT CAST ( "image" AS VARCHAR ) FROM person_image where
person_image_id = 18;

On local database
image
'1234'

On remote database
image
'31323334'

It looks like it considered the hex value of '1234' as
string and converted it into hex on remote database.

I am doing this for testing but in actual database the
binary image will be stored at this location.

Thanks

-Ashish
*/
--
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

Ashish Gupta

2005-07-13, 1:23 pm

Breck,

The request level logging on first database is
"insert into person_image(person_
id, image) values(2,
'1234')"

The request level logging on backup database is
INSERT INTO fpcsupplemental.fpc_owner.person_image
(person_image_id, person_id, patient_id, image) VALUES (6 ,
2 , NULL , '31323334' );

Is it possible to upload my blank databases somewhere and
you can run the test using my databases. I will prefer to
send you the files via email or upload to some non-public
site. The compressed blank databases is 4.5 MB.

thanks

- Ashish
> I cannot reproduce your symptom on 9.0.2.3124.
>
> First of all, this statement gives "person_image_id cannot
> be NULL":
>
> insert into person_image(person_
id, image) values(5
> ,'1234')
>
> But never mind that... the code shown below displays
> 0x31323334 for all three tables: the base tables on both
> databases, and the proxy table.
>
> Breck
>
> ----------------------------------------------------------
> ------------- -- On database d2.
>
> IF EXISTS ( SELECT *
> FROM SYS.SYSTABLE
> WHERE USER_NAME ( SYS.SYSTABLE.creator ) =
> CURRENT USER
> AND SYS.SYSTABLE.table_name =
> 'person_image' ) THEN
> DROP TABLE person_image;
> END IF;
>
> create table person_image
> (
> person_image_id integer not null ,
> person_id integer,
> patient_id char(36),
> image long binary,
> constraint PK_PERSON_IMAGE primary key
> (person_image_id) );
>
> ----------------------------------------------------------
> ------------- -- On database d1.
>
> IF EXISTS ( SELECT *
> FROM SYSTABLE
> WHERE USER_NAME ( SYSTABLE.creator ) =
> CURRENT USER
> AND SYSTABLE.table_name =
> 'person_image_proxy'
) THEN
> DROP TABLE person_image_proxy;
> END IF;
>
> IF EXISTS ( SELECT *
> FROM SYS.SYSSERVERS,
> SYSEXTERNLOGINS
> WHERE SYSSERVERS.srvid =
> SYSEXTERNLOGINS.srvid
> AND SYSSERVERS.srvname =
> 'd2'
> AND USER_NAME ( SYSEXTERNLOGINS.user_id ) =
> 'dba' ) THEN
> DROP EXTERNLOGIN dba TO d2;
> END IF;
>
> IF EXISTS ( SELECT *
> FROM SYS.SYSSERVERS
> WHERE SYSSERVERS.srvname = 'd2' ) THEN
> DROP SERVER d2;
> END IF;
>
> CREATE SERVER d2 CLASS 'ASAODBC'
> USING 'DRIVER=Adaptive Server Anywhere 9.0;ENG=d2
> ;DBN=d2';
>
> CREATE EXTERNLOGIN dba TO d2
> REMOTE LOGIN dba IDENTIFIED BY 'sql';
>
> CREATE EXISTING TABLE person_image_proxy
> AT 'd2..dba.person_image';
>
> IF EXISTS ( SELECT *
> FROM SYS.SYSTABLE
> WHERE USER_NAME ( SYS.SYSTABLE.creator ) =
> CURRENT USER
> AND SYS.SYSTABLE.table_name =
> 'person_image' ) THEN
> DROP TABLE person_image;
> END IF;
>
> create table person_image
> (
> person_image_id integer not null ,
> person_id integer,
> patient_id char(36),
> image long binary,
> constraint PK_PERSON_IMAGE primary key
> (person_image_id) );
>
> CREATE TRIGGER tI_person_image After INSERT on
> person_image REFERENCING NEW As newrows
> for each row
> BEGIN
>
> insert into person_image_proxy
> (person_image_id,
> person_id,
> patient_id,
> image)
> values
> (newrows.person_image_id,
> newrows.person_id,
> newrows.patient_id,
> newrows.image);
> END;
>
> /* Error: person_image_id cannot be NULL.
> insert into person_image(person_
id, image) values(5
> ,'1234') */
>
> insert into person_image values(1,5,'x','1234
')
> COMMIT;
>
> BEGIN
> SELECT * FROM person_image; -- 0x31323334
> SELECT * FROM person_image_proxy; -- 0x31323334
> END;
>
> ----------------------------------------------------------
> ------------- -- On database d2.
>
> SELECT * FROM person_image; -- 0x31323334
>
>
> /*
> I am running two databases on same box. I have triggers in
> place to copy data from main database to backup database.
> The copy operation is accomplished by inserting into
> remote database using insert/update/delete statements
> inside the triggers. The problem is with the binary
> fields. The binary fields reconverts binary data as if the
> data is in string format. I could not find a function to
> convert it back into original format before issuing an
> insert statement from the trigger.
>
> The databases are running on ASA 9.02.3044 version.
> The table definition for both remote and local database is
> create table person_image
> (
> person_image_id integer
> not null ,
> person_id integer,
> patient_id char(36),
> image long binary,
> constraint PK_PERSON_IMAGE primary key
> (person_image_id) );
>
> The remote table is mapped as person_image_proxy on local
> database.
>
> The insert trigger looks like this
>
> CREATE TRIGGER tI_person_image After INSERT on
> person_image REFERENCING NEW As newrows
> for each row
> BEGIN
>
> insert into person_image_proxy
> (person_image_id,
> person_id,
> patient_id,
> image)
> values
> (newrows.person_image_id,
> newrows.person_id,
> newrows.patient_id,
> newrows.image);
> END;
>
> for testing I inserted '1234' as data value for image
> field. The data was verified on both local and remote
> database. The data stored in remote and local database is
> different. It looks like the data is stored in hex format
> in both databases and I expected it to be the same.
>
> After executing the following insert statement
>
> insert into person_image(person_
id, image) values(5
> ,'1234')
>
>
> The row in person_image at local database looks like this
> person_image_id,pers
on_id,patient_id,ima
ge
> 18,5,,0x31323334
>
> The row in person_image at remote table looks like this
> person_image_id,pers
on_id,patient_id,ima
ge
> 18,5,,0x333133323333
3334
>
>
> After execution of following statment on local and remote
> database the results were different
>
> SELECT CAST ( "image" AS VARCHAR ) FROM person_image where
> person_image_id = 18;
>
>
> On local database
> image
> '1234'
>
> On remote database
> image
> '31323334'
>
> It looks like it considered the hex value of '1234' as
> string and converted it into hex on remote database.
>
> I am doing this for testing but in actual database the
> binary image will be stored at this location.
>
>
> Thanks
>
> -Ashish
> */
> --
> 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

Breck Carter [TeamSybase]

2005-07-13, 8:23 pm

As long as you understand this forum is operated by *volunteers* and
there is no guaranteed response time (but there IS a money back
guarantee on all the free support :)...

Send your stuff to breck dot carter at gmail dot com.

Breck

On 13 Jul 2005 10:01:13 -0700, Ashish Gupta wrote:
[color=darkred]
>Breck,
>
>The request level logging on first database is
>"insert into person_image(person_
id, image) values(2,
>'1234')"
>
>
>The request level logging on backup database is
>INSERT INTO fpcsupplemental.fpc_owner.person_image
>(person_image_id, person_id, patient_id, image) VALUES (6 ,
>2 , NULL , '31323334' );
>
>Is it possible to upload my blank databases somewhere and
>you can run the test using my databases. I will prefer to
>send you the files via email or upload to some non-public
>site. The compressed blank databases is 4.5 MB.
>
>
>thanks
>
>-Ashish
>http://www.amazon.com/exec/obidos/A...7/risingroad-20

--
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

Breck Carter [TeamSybase]

2005-07-14, 9:23 am

Try using the ASA driver for the remote server definition instead of
the generic driver.

With CREATE SERVER this is CLASS 'ASAODBC' instead of 'ODBC'.

In Sybase Central this is fpcsupplemental Remote Server Properties -
Server class - Server type: "Sybase Adaptive Server Anywhere" instead
of "Generic".

With the Generic driver, it looks like a kind of double conversion is
happening; e.g., '1 is 0x31 in hex, and it becomes 0x3331 when sent
across to the proxy table.

This *might* have something to do with the 32K page size on the target
database, but the page size does not seem to matter when the ASA
driver is used.

Breck

--
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

Sponsored Links