MobiLink .NET API - Binary Data in Sql Server

[Former Member]

We are currently working on implementing synchronization using the MobiLink (v12) .NET direct row handling API for multiple platforms, and there is an issue that we are experiencing with SQL server. When handling varbinary(n) columns, the data returned from the provided DBConnection appears to always be a byte array of size n, despite the actual size of the value in the consolidated database. The varbinary(max) columns appear to be working, as long they are at the end of the select statement (see related post).

One specific example we are seeing involves a column defined as varbinary(255), which contains a value that is actually 20 bytes in length. When we query the consolidated using the MobiLink .NET API, we get back an array of 255 bytes. The first 20 bytes are the actual bytes stored in the database, and the remaining bytes in the array are all zeros. If I query the data using an ADO.NET OdbcConnection pointed at the same DSN, I get the expected 20 bytes.

Is this a bug with the ODBC wrapper within the MobiLink .NET direct row handling API, or is there some way to avoid having the binary data padded with zeros?

EDIT

After futher testing, it appears this may be a problem on all three platforms that we are targeting (SQL Anywhere, Oracle, and MS SQL Server)

[regdomaratzki]

Just to be clear, this is data that you are pulling out of a varbinary(255) column in the consolidated database, and not data in the upload stream that you are accessing through the UploadedTableData interface?

[Former Member]

That is correct, we are downloading data from the consolidated.

[regdomaratzki]

Sorry Dan, I haven't had a chance to get back to this yet since you answered my question. I hope to have a look again early next week. If this is a time sensitive issue, you can always open a support case.

[regdomaratzki]

Dan, I believe I've reproduced your issue, and we're looking into it. Can I assume that your code to add the varbinary(255) column to your download stream looks similar to :

public void handleDownload ( ) {
  DownloadData dd = _context.GetDownloadData();
  DownloadTableData dtdAdmin  = dd.GetDownloadTableByName( "Admin" );
  DBCommand dataAdmin = _conn.CreateCommand();
  dataAdmin.CommandText = "select admin_id, data from Admin";
  DBRowReader AdminRowReader = dataAdmin.ExecuteReader();
  IDbCommand downCurAdmin = dtdAdmin.GetUpsertCommand();
  object[] AdminValues = AdminRowReader.NextRow();
  while( AdminValues != null ) {
    ((IDataParameter)(downCurAdmin.Parameters[0])).Value = (Int64)AdminValues[0];
    ((IDataParameter)(downCurAdmin.Parameters[1])).Value = (byte[])AdminValues[1];
    downCurAdmin.ExecuteNonQuery();
    AdminValues = AdminRowReader.NextRow();
  }
  AdminRowReader.Close();
  dataAdmin.Close();
}

[Former Member]

Yes, while there are some minor differences, what we are doing is very similar.

[regdomaratzki]

This has now been fixed. The fix should be available in v1201 build 3752 or greater.

[Former Member]

Verified in 3757

[t1950]

i'm not sure why you're seeing 0's instead of spaces, but MS SQL, using ODBC, always returns a fixed length string.
try adding this to you dbparm connection string >>> TrimSpaces=1 if that doesn't work try adding >>> TrimSpaces='Yes'

[Former Member]

Hello, and thank you for the quick response. We are actually querying binary data, not string data, so we are getting byte arrays back in .NET, not fixed length strings.