Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

We are in the process of adding more flexibility to our data partitioning on a working synchronization scheme. Our first step is converting our normal SQL scripts to run under the DirectRow handling for each table and populating the download stream through the .NET API. It all seems to be working fine until we try reading from tables that contain a long varchar or xml type column. For these we have receive either Out of Memory exceptions or Protected Memory Access exceptions.

Versions: SA (& 3519, latest EBF), .NET 4, Consolidated db running under SA 12.0.1, OS Win 7 x64

SQL is running under the iAnywhere reader, and the exception happens on the first call to NextRow() before we have any chance to access the data in our code. Code snippet with the key elements of the data access is below.

Are there known limitations when dealing with long data types? We have several long binary types in other tables which do download just fine. As long as we skip tables with the long character types everything else succeeds (with a couple workarounds for other small problems).


using System.Data;
using iAnywhere.MobiLink.Script;


// called from handle_download: loop through download tables
// connection is the return from DBConnectionContext.GetConnection()
// downloadCommand is the return from a call to DownloadTableData.GetUpsertCommand()
private void executeDownload(string sqlStatement, DBConnection connection, IDbCommand downloadCommand)
    var selectCommand = connection.CreateCommand();
    selectCommand.CommandText = sqlStatement;

    var tableUpsertParameters = downloadCommand.Parameters;
    var reader = selectCommand.ExecuteReader();

object[] row;
    // memory access exception happens in call to reader.NextRow()
    while ((row = reader.NextRow()) != null)
        //add row to download stream here

asked 04 Jan '12, 15:45

Ron%20Emmert's gravatar image

Ron Emmert
accept rate: 12%

edited 04 Jan '12, 17:20

How long are your long columns?

(04 Jan '12, 16:44) Bill Somers
Replies hidden

Data type of the column is "long varchar". Actual contents vary from less than 100 characters to around 100k depending on what the column contains.

It's hard to know what order the rows were actually retrieved in, but on the first failure, there would not have been more than 60K in that particular row.

Note that the same script runs just fine with the same contents using a normal download SQL statement.

(04 Jan '12, 17:00) Ron Emmert

I'm asking because we allocate a single contiguous chunk to return the value, so if the column was 1GB long, I would expect out of memory errors. 100K or so is not unreasonable, and I would expect it to succeed.

(04 Jan '12, 17:08) Bill Somers

The whole test consolidated db, indices and all, is well under 500MB . It's possible that an occasional single row could exceed 1MB, but I would be surprised to find that condition in this test case.

(04 Jan '12, 18:57) Ron Emmert

Some more information when running against other consolidated db platforms.

We don't get the exceptions when running against SQL Server (2008 R2), but then all the returned values for long type (varbinary(max) or varchar(max)) columns are truncated to zero length.

When the consolidated db is Oracle (11g) we get the same behavior. Mostly protected memory access exceptions.

In addition, on some tables when we close the reader, we also get protected memory access exceptions. In our tests, the tables that do that are consistent and repeatable, but there is no particular pattern of column types that we can identify yet causing this one.

The patterns suggest a couple questions to me:

Using SQL scripts we've always just included the long types in the SELECT clause and ML handles it. Do we need to treat long values differently in DirectRow handling?

Is the DirectRow handling really designed for handling the bulk of data from the consolidated database that SQL Script handling is? Most of the documentation refers to using DirectRow handling for interfacing with web services or some other non-db or external db data source.

(05 Jan '12, 14:18) Ron Emmert
Replies hidden

The DBConnection and other related interfaces make a rather simple .NET-ODBC bridge, so that NextRow call is doing a bunch of ODBC work under the hood. Some ODBC drivers have restrictions on when you can fetch long columns, which explains the behaviour you're seeing with Microsoft SQL Server. It's probable that the server itself does something smarter against MSS to hide this.

Typically customers use the direct row API for only a few tables, but it's not unheard of for it to be used for everything.

I'll look into your crashes when closing the reader.

(05 Jan '12, 17:29) Bill Somers
showing 2 of 6 show all flat view

I've reproduced the exceptions you reported in the question and have a fix. I'll update this answer tomorrow with a CR# you can use to request an EBF.

permanent link

answered 05 Jan '12, 17:11

Bill%20Somers's gravatar image

Bill Somers
accept rate: 40%

While updating a test for this change, I ran into what I think is your DataReader crash and some other issues. I'll update this post when I've got everything fixed up.

(06 Jan '12, 17:56) Bill Somers

My fixes are submitted. The CR# is 695370. You'll have to get in touch with support to kick off the EBF process.

(12 Jan '12, 13:33) Bill Somers
Replies hidden

Thanks, we'll pick up the EBF & test.

(13 Jan '12, 17:57) Ron Emmert

Got an early release and this fixes it.

(30 Jan '12, 17:22) Ron Emmert
Your answer
toggle preview

Follow this question

By Email:

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



Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text]( "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:


question asked: 04 Jan '12, 15:45

question was seen: 2,634 times

last updated: 30 Jan '12, 17:22