Sybase (SAP) ASE Ado.Net way around 16384 character limit?

Viewed 493

I have this test case:

internal class LongString {
    const string CommandText = @"
create table #temp (p text)
insert into #temp(p) values(@p)
select p from #temp
drop table #temp";

    public static void Issue() {
        Console.WriteLine("character length limit, expect:");
        Console.WriteLine(16384);
        Console.WriteLine(16385);
        Console.WriteLine("---------");
        Console.WriteLine("Actual:");

        try {
            Console.WriteLine(Run(new string('A', 16384)));
        } catch (Exception ex) {
            Console.WriteLine(ex);
        }
        try {
            Console.WriteLine(Run(new string('A', 16385)));
        } catch (Exception ex) {
            Console.WriteLine(ex);
        }
        Console.WriteLine("---------");
        Console.WriteLine();
    }

    private static int Run(string s) {
        using (var conn = new AseConnection(Globals.ConnectionString)) {
            conn.Open();
            using (var cmd = conn.CreateCommand()) {
                cmd.CommandText = CommandText;
                cmd.Parameters.Add(new AseParameter {ParameterName = "@p", AseDbType = AseDbType.Text, Value = s});
                using (var r = cmd.ExecuteReader()) {
                    while (r.Read()) {
                        return ((string) r["p"]).Length;
                    }
                }
            }
        }
        throw new InvalidOperationException("cannot get here");
    }
}

I have to insert data into text columns in various tables and have hit this roadblock. Output for the testcase is:

character length limit, expect:
16384
16385
---------
Actual:
16384
16384
---------

currently using the driver Sybase.AdoNet4.AseClient.dll 16.0.3.0 and testing against various versions of the database ranging from 15.7 through a current version of 16. Previous tested versions of the driver failed at 16360 characters.

1 Answers

While the TEXT datatype might support storage of up to 2GB, the server will place a limit (typically 16KB) on how much you can select back in one go. Bytes beyond this limit don't get transmitted.

There are a couple of ways to bump the limit:

  1. At the start of your query, use SET TEXTSIZE some_value
    • some_value is the number of bytes you want to bump the limit to.
    • So, if you know that you'll be selecting up to 128000 bytes from a text column, then put SET TEXTSIZE 128000 at the start of your query.
  2. Use the TextSize=some_value; connection string parameter. Much like the above method, you would specify an appropriate value for some_value.
    • Under-the-hood, I believe the driver actually just uses this value to do a SET TEXTSIZE call, when you call Open().
Related