I regularly use the Sybase Central Database Migration Tool to move tables between SQL Anywhere databases. I recently investigated a missing data case and found that selects from proxy tables can return a zero length string in certain situations. My current workaround is to use the INPUT USING command to migrate data as this avoids the data loss. To Recreate I did the following. I created a table with 5 columns each defined as nvarchar(1024). I then inserted 4 rows filled to the lengths below. COL1 COL2 COL3 COL4 COL5 1024 1023 1023 1024 1023 1024 1023 1024 1024 1023 1024 102 102 1024 1023 1024 102 102 102 1023 When I ran a "select *" from this table the following columns were blank - row 1 column 4 , row 2 columns 3 and 4, row 3 column 4 (all should be in bold). I'm using SQL Anywhere 12 version 12.0.1.4155 on Windows 7 (this issue occurs on other windows versions). Both databases use UCA collation and UTF8 charset. The ODBC Driver is also version 12.0.1.4155. |
Your problem ended up being a bug in the way nvarchar columns were being handled in SQLA's CIS/OMNI layer. The bug has been fixed in v12 build 4294 and v16 build 2156. Thanks Mark! I suspected as much.
(14 Jul '15, 05:00)
write_conrad
Hi Mark, Couldn't find v12 build 4294 online, the latest build available is 4278. thanks
(15 Jul '15, 04:45)
write_conrad
|
You should never have any data loss! What you are seeing should not happen.
Can you show us the exact SQL that you used in your example - the dbinit command line, the create table, inserts, creating the proxy tables, and finally the select statement. This will help us/others see exactly what you are doing. Also please describe where the two databases reside (two different computers, same lan or across a wan?) and the connection string(s) used.
The server was started using dbsrv12.exe -x tcpip <db_filename> from Sybase Central. The Target and Remote database servers are on the same machine.
The source table creation SQL in the Remote Server is 1. create table test (col1 nvarchar(1024), col2 nvarchar(1024), col3 nvarchar(1024), col4 nvarchar(1024), col5 nvarchar(1024));
The Proxy Table creation SQL in the Target DB is
CREATE server "remote_test" class 'saodbc' using 'DRIVER=SQL Anywhere Native;Server=testsrv2'; (passwords are the same of course)
create existing table test_et at 'remote_test...test';
select * from test_et
Column 1 and column 4 were blank in the proxy table select.
I hope this is sufficient.
Mark, Just want to add that I appreciate your quick response to my initial query. Don't want to seem ungrateful. :)
Mark,
Realised I hadn't answered your dbinit question. I use a standard database template so haven't got that, below is some of the output from sa_db_properties (the next best thing)
cheers...
FYI: I have reproduced a problem similar to yours (but not exactly) and will investigate further.