Different empty_string/NULL handling under ODBC and OLE DB?

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • sepe
    #1

    Different empty_string/NULL handling under ODBC and OLE DB?

    Hello,

    I've a problem with SQL Server and OLE DB. SQL Server handles NULL as
    NULL whereas empty string is stored as a space. This is correct when
    using OLE DB but with ODBC an empty string gets converted to NULL with
    SQL Server.

    When migrating from ODBC to OLE DB we get errors when inserting an
    empty string into foreign key columns. Therefore I guess that ODBC
    drivers implicitly convert empty string to NULL. Does anyone know if
    there is any parameter to set this behaviour for OLE DB connections?

    Example:
    CREATE TABLE foo (id VARCHAR(25) PRIMARY KEY, data VARCHAR(10))
    CREATE TABLE bar (id VARCHAR(25) PRIMARY KEY, foo_id VARCHAR(25)
    CONSTRAINT foo_id FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE
    CASCADE)
    INSERT INTO foo VALUES ('1', 'data')
    INSERT INTO bar VALUES ('1', '1')
    INSERT INTO bar VALUES ('2', null)
    -- possible with ODBC connect, error with OLE DB (b/c no conversion)
    INSERT INTO bar VALUES ('3', '')

    I'm working with legacy code under Gupta/Unify Team Developer and need
    to migrate from ODBC to OLE DB, without changing all INSERTs from
    empty string to NULL.

    Regards
    Sebastian Peters
    Tags: None
    • nidaar
      #2
      Re: Different empty_string/NULL handling under ODBC and OLE DB?

      On Aug 21, 11:42 am, sepe <sebastian.pet. ..@gmail.comwro te:
      Hello,
      >
      I've a problem with SQL Server and OLE DB. SQL Server handles NULL as
      NULL whereas empty string is stored as a space. This is correct when
      using OLE DB but with ODBC an empty string gets converted to NULL with
      SQL Server.
      >
      When migrating from ODBC to OLE DB we get errors when inserting an
      empty string into foreign key columns. Therefore I guess that ODBC
      drivers implicitly convert empty string to NULL. Does anyone know if
      there is any parameter to set this behaviour for OLE DB connections?
      >
      Example:
      CREATE TABLE foo (id VARCHAR(25) PRIMARY KEY, data VARCHAR(10))
      CREATE TABLE bar (id VARCHAR(25) PRIMARY KEY, foo_id VARCHAR(25)
      CONSTRAINT foo_id FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE
      CASCADE)
      INSERT INTO foo VALUES ('1', 'data')
      INSERT INTO bar VALUES ('1', '1')
      INSERT INTO bar VALUES ('2', null)
      -- possible with ODBC connect, error with OLE DB (b/c no conversion)
      INSERT INTO bar VALUES ('3', '')
      >
      I'm working with legacy code under Gupta/Unify Team Developer and need
      to migrate from ODBC to OLE DB, without changing all INSERTs from
      empty string to NULL.
      >
      Regards
      Sebastian Peters
      An INSTEAD OF INSERT trigger could be a solution, like below:

      CREATE TRIGGER [dbo].[NullInsteadOfEm pty]
      ON [dbo].[bar]
      INSTEAD OF INSERT
      AS
      BEGIN

      INSERT INTO dbo.bar (id, foo_id)
      SELECT id, CASE WHEN foo_id <'' THEN foo_id END AS foo_id
      FROM INSERTED

      END

        Comment

        • Erland Sommarskog
          #3
          Re: Different empty_string/NULL handling under ODBC and OLE DB?

          sepe (sebastian.pete rs@gmail.com) writes:
          I've a problem with SQL Server and OLE DB. SQL Server handles NULL as
          NULL whereas empty string is stored as a space. This is correct when
          using OLE DB but with ODBC an empty string gets converted to NULL with
          SQL Server.
          >
          When migrating from ODBC to OLE DB we get errors when inserting an
          empty string into foreign key columns. Therefore I guess that ODBC
          drivers implicitly convert empty string to NULL. Does anyone know if
          there is any parameter to set this behaviour for OLE DB connections?
          >
          Example:
          CREATE TABLE foo (id VARCHAR(25) PRIMARY KEY, data VARCHAR(10))
          CREATE TABLE bar (id VARCHAR(25) PRIMARY KEY, foo_id VARCHAR(25)
          CONSTRAINT foo_id FOREIGN KEY(foo_id) REFERENCES foo(id) ON DELETE
          CASCADE)
          INSERT INTO foo VALUES ('1', 'data')
          INSERT INTO bar VALUES ('1', '1')
          INSERT INTO bar VALUES ('2', null)
          -- possible with ODBC connect, error with OLE DB (b/c no conversion)
          INSERT INTO bar VALUES ('3', '')
          >
          I'm working with legacy code under Gupta/Unify Team Developer and need
          to migrate from ODBC to OLE DB, without changing all INSERTs from
          empty string to NULL.
          I don't know what your code looks like, but if your code looks like
          the above, and some driver or tool changes '' to NULL, bad things are
          going on.

          Now, I would guess that the real code looks different, and it could
          help to see it. Maybe. I suspect that the answer lies within Gupta of
          which I know nothing.


          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

          Links for SQL Server Books Online:
          SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
          SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
          SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            Previous template Next
            Working...