cancel
Showing results for 
Search instead for 
Did you mean: 

MobiLink .NET API - Binary Data in Sql Server

Former Member
4,181

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
Product and Topic Expert
Product and Topic Expert
0 Kudos

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

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

regdomaratzki
Product and Topic Expert
Product and Topic Expert
0 Kudos

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
Product and Topic Expert
Product and Topic Expert

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

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

Accepted Solutions (0)

Answers (2)

Answers (2)

regdomaratzki
Product and Topic Expert
Product and Topic Expert

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

Former Member

Verified in 3757

t1950
Participant
0 Kudos

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

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.