What causes Data Loss when Selecting Data from Sql Anywhere Proxy Tables?

[Former Member]

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.

[MarkCulp]

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.

[Former Member]

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

1. insert into test (col1,col2,col3,col4,col5) select repeat('a',1024), repeat('a',1023), repeat('a',1023), repeat('a',1024), repeat('a',1023); (repeat and vary for other rows)

The Proxy Table creation SQL in the Target DB is

1. CREATE server "remote_test" class 'saodbc' using 'DRIVER=SQL Anywhere Native;Server=testsrv2'; (passwords are the same of course)

2. create existing table test_et at 'remote_test...test';

3. select * from test_et

Column 1 and column 4 were blank in the proxy table select.

I hope this is sufficient.

[Former Member]

Mark, Just want to add that I appreciate your quick response to my initial query. Don't want to seem ungrateful. 🙂

[Former Member]

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

PropName    Value
AccentSensitive Off
AuditingTypes   all
Authenticated   Yes
BlankPadding    On
CaseSensitive   Off
CatalogCollation    UCA
CharSet UTF-8
Checksum    On
Collation   UCA
ConnsDisabled   Off
DatabaseCleaner On
Encryption  None
EncryptionScope None
HasCollationTailoring   Off
HasEndianSwapFix    On
HasNCHARLegacyCollationFix  On
HasTornWriteFix Off
IQStore Off
MultiByteCharSet    On
NcharCharSet    UTF-8
NcharCollation  UCA
PageSize    8192
ProcedureProfiling  Off
ReadOnly    Off
SnapshotIsolationState  Off
SynchronizationSchemaChangeActive   Off
WriteChecksum   On

[MarkCulp]

FYI: I have reproduced a problem similar to yours (but not exactly) and will investigate further.

[MarkCulp]

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.

[Former Member]

Thanks Mark! I suspected as much.

[Former Member]

Hi Mark,

Couldn't find v12 build 4294 online, the latest build available is 4278.

thanks