When the SAP ASE Wire Protocol driver establishes a connection, the driver sets the SAP ASE database option ansinull to on. Setting ansinull to on ensures that the driver is compliant with the ANSI SQL standard, which makes developing cross-database applications easier.

By default, SAP ASE does not evaluate NULL values in SQL equality (=), inequity (<>), or aggregate function comparisons in an ANSI SQL-compliant manner. For example, the ANSI SQL specification defines that col1=NULL always evaluates to false:

SELECT * FROM table WHERE col1 = NULL

Using the default database setting (ansinull=off), the same comparison evaluates to true instead of false.

Setting ansinull to on changes the default database behavior so that SQL statements must use IS NULL instead of =NULL. For example, using the SAP ASE Wire Protocol driver, if the value of col1 in the following statement is NULL, the comparison evaluates to true:

SELECT * FROM table WHERE col1 IS NULL

In your application, you can restore the default SAP ASE behavior for a connection in the following ways:

  • Use the Initialization String option to specify the SQL command set ANSINULL off. For example, the following connection string ensures that the handling of NULL values is restored to the SAP ASE default for the current connection:
    DSN=SYB TABLES;DB=PAYROLL;IS=set ANSINULL off
  • Explicitly execute the following statement after the connection is established:
    SET ANSINULL OFF