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.

asked 08 Jul '15, 05:27

write_conrad's gravatar image

write_conrad
41111
accept rate: 0%

1

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.

(08 Jul '15, 08:10) Mark Culp

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.

(08 Jul '15, 12:47) write_conrad

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

(09 Jul '15, 07:14) write_conrad

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

(10 Jul '15, 05:12) write_conrad
1

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

(10 Jul '15, 13:19) Mark Culp

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.

permanent link

answered 13 Jul '15, 16:18

Mark%20Culp's gravatar image

Mark Culp
23.0k9130270
accept rate: 40%

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
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×63
×49
×45

question asked: 08 Jul '15, 05:27

question was seen: 849 times

last updated: 15 Jul '15, 04:45