Problem with NULL values using Sybase ODBC driver in Linux

[Former Member]

Hi,

I am accessing a Sybase ASE 15.7 database using Sybase ASE 16.0 ODBC driver on Ubuntu 14.04.

I have a strange problem with fetching NULL values. At first I noticed the problem with a Python script,

but the problem remains the same when using the isql command line tool that comes with unixodbc

package. The isql command line tool can be used to access the Sybase ASE database via ODBC driver

and test if the ODBC driver configuration is valid.

The problem can be tested with a simple select statement with a column which does not have a value

i.e. is NULL.

As you can see the ISNULL function is used here to make sure the column customer_segment is NULL:

SQL> select ISNULL(customer_segment, 'has no value') from test_table where id = 50

+-------------+

|             |

+-------------+

| has no value|

+-------------+

SQLRowCount returns -1

1 rows fetched

SQL> select language, customer_segment from test_table where id = 50

+---------+-----------------+

| language| customer_segment|

+---------+-----------------+

| FI      | FI              |

+---------+-----------------+

SQLRowCount returns -1

1 rows fetched

SQL>

Has anyone of you seen this before? Is this a known problem?

Any help would be appreciated.

Thanks is advance,

Kari

[ChrisBaker]

The documentation for 'isnull' states:

If expression1 parameter is a char datatype and expression2 is a literal parameter, the results from your select statement that includes isnull differ based on whether you enable literal autoparameterization. To avoid this situation, do not autoparameterize char datatype literals within isnull().

You should also check the setting of the 'ansinull' connection property.  As you are running from ODBC, the setting may be different than with OpenClient and affecting the behaviour of the 'isnull' function.

Chris

[Former Member]

Hi Chris,

I quess you missed my point. The problem I have has nothing to do with ISNULL function.

I just used it to show that the column really is NULL.

The problem is that for the NULL columns, the value of the previous column in the SELECT statement is returned, but I need it to remain NULL.

Kari

[ChrisBaker]

Sorry.  It was not stated that way.

I would to 2 things -

First - ensure that you have applied the SPs that come with the ODBC driver to the ASE 15.7.  They are usually located in a folder under the driver installation and contain scripts that install/patch the metadata for the driver on the ASE side.  Just quickly looking at my Windows installation, there is an '$SYBASE/DataAccess64/ODBC/sp/install_odbc_sprocs.bat' script that will determine which actual SQL script to install on an ASE for my 16.0 ODBC drivers.  In my case, there is an sp_drv_common_1570020.sql script that would install on an ASE 15.7 server, as opposed to the sp_drv_common_1600000.sql script that would install on an ASE 16.0.  You can also just install these scripts via the SDK isql tool as 'sa' as well.

This is required when you upgrade or patch drivers (ODBC, JDBC, etc.) on the client side.  Installmaster will install the most recent versions of the drivers (see 'sp_version' output) at that ASE/SDK release version, when you upgrade ASE, but as you are using 16.0 against 15.7, you probably need to install the newer metadata to match your highest level ODBC driver.  Drivers are actually in 2 parts (client-side and server-side) to keep them thinner on the client.

If this doesn't solve your issue, try installing the latest SP of the driver with the server-side pieces updated as above.

Second - if nothing works, please log an incident with SAP.

Chris

[ChrisBaker]

One last thing, and it may not relate to this, and was more to do with using the IQ ODBC driver on Linux with 3rd-party driver managers (unixODBC and Datadirect) -

You may need to test adding the following to your DSN definition:

DriverUnicodeType=1

Charset=utf8

With IQ drivers, both settings were needed for Datadirect.  The second only may be needed for unixODBC, although this was commented out in the doc I have.

As I said, this was for IQ drivers on Linux/Unix, but this may relate.  The driver could be returning all results as utf8, regardless of the character set of the server, to work with multilingual applications better.  I think these settings tell the driver manager how to translate from the driver to the application.  Under the covers, the driver will take care of the actual character set translation to the server anway.

Chris

[Former Member]

Hi Chris,

Thank you for your suggestions.

I installed the patches and tried also parameters DriverUnicodeType and Charset in the DSN definitions, but they did not help.

I tested the ODBC driver on a fresh install of Sybase ASE 16.0 SP2 on an Ubuntu 14.04 virtual machine. The Sybase ODBC driver behaves in the same way i.e. for NULL value column I get the value of the previous column in the SELECT statement.

What is strange is that I have no problems accessing the Sybase ASE database from a Windows 7 PC using the ODBC driver from Sybase ASE 16.0 installation.

Regards,

Kari

[ChrisBaker]

The best thing I can suggest then is to open an incident with SAP.  Most likely there is a difference between the ODBC Driver Managers.

Chris