ASE ODBC driver related questions

[Former Member]

Hi Friends,

I am using ODBC driver (the framework over ODBC API) to connect and read data from ASE database server on Windows.

I've few questions about ASE ODBC driver. Can you please guide me.

1) ASE has a DB option called "textsize" which sets the limit on amount data we can fetch in select statement.

     This option can be set either in the DSN configuration (Advanced->Textsize text field) or by "set textsize <number>" for a session.

     --- While scanning BLOBs (image data types), I want to increase this limit to 512MB in my application. But, I don't want to override the limit if user has Text Size field in the DSN configuration.

    * how can we (programatically) find out the Text Size value set in the DSN configuration?

       --- Is it possible using some ODBC function?

2) What is the maximum limit on the data returned by SQLGetData function call at a time?

     --- Can we determine the size of the data stored in a column, especially the BLOB (or image) columns using ODBC API?

         or do we need to run the query "select datalength(ColumnName) from tablename" ?

3) Which the most stable and reliable ODBC Driver Manager for Unix and Linux OSs? Is it unixODBC?

Many thanks in advance for your clarifications and time.

Best Regards

[Former Member · Accepted solution]

Hi Ganesh,

For unixODBC, from the SDK 15.7 docs (Users Guide Adaptive Server Enterprise ODBC Driver by Sybase)

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20116.1570/html/aseodbc/aseodbc5.htm

For Linux - normally can use the unixODBC distributed with Red Hat and SuSE.  I've using 2.2.11, 2.2.14 and also 2.3.1 and all seem to work fine. (I install multiple versions of unixODBC depending on what customers are using for specific cases).

For UNIX you can use the Sybase iAnywhere ODBC Driver Manager included with the Driver installation or the recommened unixODBC version is 2.2.14 if you wish to install your own.

Definitely check the note about the 4-byte vs. 8-byte SQLLEN.  Since 15.7 ESD#4 we've included both the 4-byte and 8-byte SQLLEN ODBC Driver depending on your individual needs.

On Red Hat 6 and later, the unixODBC Driver manager uses the 8-byte SQLLEN. This would be with 2.2.14 and higher.

Hope this helps,

Cheers,

-Paul

[Former Member · Accepted solution]

I've one more question which is specific to Sybase ODBC driver.

I am using "select convert(binary(512), <columname>) from tablename" query to get the first 512 bytes of a BLOB (image, binary) columns.

According to http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.help.ase_15.0.blocks/html/blocks/block..., "The maximum allowable length for character and binary expression is 64K.".

However, I am able to use this only for length upto 128 bytes. If I try to use 256 or 512, ODBC driver returns indicator value of NULL.

Is 128 bytes the default to read using convert()? How can we read 512 bytes? Thanks

[Former Member · Accepted solution]

Hi Ganesh,

How are you doing this with the ODBC calls?

Is it using SQLGetData()? We have this fix coming out in 15.7 SP100 (should be this week or next week) CR 727861: ODBC Driver by Sybase: SQLGetData() API does not retrieve entire column data if buffer length is passed as 0.

I don't know if this fits your problem but provide the API details and I can take a look. The problem I mentioned had an issue where length was passed as 0, but the indicator was supposed to return the bytes read, and then call is repeated until the buffer containing the data was drained.

The data type used in SQLGetData was SQL_C_BINARY and we were seeing errors like "[Sybase][ODBC Driver]String data, right truncated (32007)".

Cheers,

-Paul

[Former Member · Accepted solution]

Thanks Paul.

This is not with SQLGetData. In fact, SQLGetData is working fine for me.

Getting this problem when executing query and accessing bound buffers as below (pseudo code):

// Set statement attribute to read by column, number of rows, static cursor and status pointer:

/*struct StmtAttrs

{

    SQLINTEGER attr;

    SQLPOINTER valptr;

    SQLINTEGER len;

};*/

    SQLHSTMT& stmt = m_stmtHdl.m_hdl;

    StmtAttrs parms[5] = \

    {{SQL_ATTR_ROW_BIND_TYPE, SQL_BIND_BY_COLUMN, 0},

    {SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0},

    {SQL_ATTR_ROW_ARRAY_SIZE, (SQLPOINTER)limit, 0},

    {SQL_ATTR_ROW_STATUS_PTR, &m_rowStatus[0], 0},

    {SQL_ATTR_ROWS_FETCHED_PTR, &m_rowsFetched, 0}};

    uint32_t i = 0;

    SQLINTEGER errcode;

    for (; i < 5; ++i)

    {

        bool ret = sqlSetStmtAttr(stmt, parms[i].attr, parms[i].valptr, parms[i].len);

          // error handling code

    }

//Bind the input buffers for number of columns using SQLBindCol:

//Prepare the query using SQLPrepare

// E.g.: select top 2039 numpk,convert(binary(256),ablob) from myccinfo.dbo.bigdata NOLOCK order by numpk asc ;

// Execute the query with SQLExecute(stmt);

// Fetch the data using SQLFetchScroll(stmt, SQL_FETCH_NEXT, 0);

// Iterate thru each column and the bound buffer to read the data

[Former Member · Accepted solution]

Hi Ganesh,

I haven't rtied this in test yet.  Is there any error returned when you do this?

Can you do this?

On your connection (Don't know if you are using DSN or DSN-less connection string)

We have a property, protocolcapture.

Set it to some path\binary

When you connect and run the test it'll produce some binary0.1234.tds file.

I think you can attach the file here - I am new to SCN (I am a former Sybase employee). I can translate it and look at what is happening with the call.

I don't think convert should have a limitation and if it did I would think 255.

However, get exact ASE version (select @@version) and let me know what charset is used in the application and ASE (On ASE you can get sp_helpsort) - just checking to see if using utf8.

Cheers,

-Paul

[Former Member · Accepted solution]

Paul,

I created a sample test program with the same code as in main app & captured the ODBC driver trace as well as protocolcapture (asper http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc20116.1570/html/aseodbc/a...) output for failing condition, i.e. when issuing "select convert(binary(512)...." command.

Since, editor does not allow attaching .tds files, I've changed the file name to "tds_capture0-tds.txt".

Attached files are:

tds_capture0-tds.txt:-              protocol capture output

odbc-drv.txt            :-              ODBC driver trace when program was run

ase-version-details.txt:-            output of "select @@version" and "sp_helpsort" commands, & working sample C++ program (on Windows 7, Visual Studio 2008, version 9)

Length values up to 255 provide good results. We get -1 indicator value when length is set to 256 and higher.

Do we need to set the correct char set?

Thanks

ase-version-details.txt.zip

tds_capture0-tds.txt.zip

odbc-drv.txt.zip

[Former Member · Accepted solution]

Hi Ganesh,

I'm tied up today so I will look at this tomorrow. I don't know yet if this is related to charset or not. Let me look at the logs and code to see what is going on.

Cheers,

-Paul

[Former Member · Accepted solution]

Hi Ganesh,

I can reproduce the behavior.  If 255 or less indicator returns the value, 256 and above it is -1, NULL.  I am not sure if this is a limitation in the driver or not.  I need to consult on this internally.  You might want to open a Tech Support case to have this investigated further. In the meantime, we have support with the ASE 15.7 for LOB Locator support that might help in this situation.  I haven't tried this sort of scheme yet but I'll include link to the docs where this is located:

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20116.1570/html/aseodbc/CIHGGHFJ.htm

For now, this looks like driver limitation - I can look in the TDS and all 512 bytes are retrieved.

Cheers,

-Paul

[Former Member · Accepted solution]

Hi Ganesh,

I just realized this is a specific problem.  For future postings it might be a good idea to keep these as separate postings so it's easier to search out the problem.  In the meantime we can keep this thread going. I'll check internally with this behaivor and get back.

Cheers,

-Paul

[Former Member · Accepted solution]

Hi Ganesh,

Can you try to comment out:

SQLSetStmtAttr(stmt, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER)SQL_CURSOR_STATIC, 0);

and see if this changes behavior.

Cheers,

-Paul

[Former Member · Accepted solution]

Hi Paul,

Yes, disabling this SQLSetStmtAttr resolves the problem.

Why is it so? Will the ODBC driver default to this cursor type if we don't set this attribute explicitly?

Thanks

[Former Member · Accepted solution]

Hi Ganesh,

By disabling the SQLSetStmtAttr for Cursor type, the ASE ODBC Driver defaults to FORWARD_ONLY cursor. I don't think this should impact things as it doesn't appear you are doing any sort of scrolling in the cursor. So by default the FORWARD_ONLY will retrieve the data and apparently handles the size correctly.

It looks like there is some sort of problem with the STATIC cursor. If you want to have this investigated you can open a Tech Support case and I can log this with Engineering to resolve in the future. If you're okay to use the FORWARD_ONLY cursor, go with that and I will log an internal CR for the STATIC cursor problem.  I think that setting should work anyway but at this time I haven't figured out why it isn't working. More likely it is due to problem in the ASE ODBC Driver code.

Cheers,

-Paul

[Former Member · Accepted solution]

Paul,

Many thanks for resolving this issue. As of now, I am ok using FORWARD_ONLY cursor.

I am not able to create a tech support case somehow. Kindly resolve it the way most appropriate to you.

(Just as a side note, I am curious to know, will changing to FWD_ONLY (from cursor_static) affect the behavior of other ODBC drivers, e.g. MSSQL or DB2. I am using the same ODBC code base for these other DBs also?)

[Former Member · Accepted solution]

Hi Ganesh,

I am not certain how use of Forward only will affect other drivers.  It depends on what the application does.  If it is just fetching the data and then using it separately from the ODBC Driver, that is the driver isn’t going to navigate around on the result set (I think you’d have to use scrollable cursor for this to happen anyway) if should be okay.

You could also,  inconveniently, create logic to set cursor type to fwd only for the ASE ODBC Driver.

All you need to do is test some selects on other databases to see what happens.  Typically, most drivers default cursor_type is forward_only, so they ought to work okay.

Cheers,

-Paul

[Former Member]

Hi Ganesh,

You can access the textsize for the session by checking the global variable @@textsize

select @@textsize

I don't think you can access it as a property (not sure you can do this anyway even if it were?) as it will just be set by the driver at connection time.

You could look at the connection string but only with via a higher level api (OdbcConnectionStringBuilder class for .NET).

SQLGetData can access the length if you set up a buffer to store it in, take a look at StrLen_or_IndPtr. You also have lob locators (others may be able to give you more on this) available from 15.7, take a look at:-

http://infocenter.sybase.com/help/topic/com.sybase.infocenter.dc20116.1570/html/aseodbc/CIHGAFCE.htm

Not sure I could recommend a driver manager, but if you are to use one on Linux\Unix, that's the only one I have used.

I'm sure others might have some more useful info in that regard.

HTH,

Simon

[Former Member]

Hi,

I will integrate a check in select query so that even if textsize is set within DSN config, it will be reflected as a session level global variable (textsize) after the connection is established.

I want to increase textsize to 512MB if it is less that 512MB, when reading blobs. So, I am planning to add following code to select query for blobs:

    declare @TXTSZ int

    select @TXTSZ = @@textsize

    if @TXTSZ < 524288000

    set textsize 524288000;

    select <blob_column> from table where pk = pk_value;

Can I combine these statements in this way?

[Former Member]

Hi,

Yep, if this is the way you want to approach it you can execute all the commands within a single statement object. Provided the statements are separated with a space it'll take of it as a single batch of statements. Even that final select could go in the same string, the textsize change will be active at execution time of the select statement.

Cheers,

Simon