Different empty_string/NULL handling under ODBC and OLE DB?

[sepe]

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

[nidaar]

On Aug 21, 11:42*am, sepe <[e-mail]:

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].[NullInsteadOfEmpty]
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

[Erland Sommarskog]

sepe (se**************@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, es****@sommarskog.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