Re: Different empty_string/NULL handling under ODBC and OLE DB?
- From: Erland Sommarskog <esquel@xxxxxxxxxxxxx>
- Date: Thu, 21 Aug 2008 22:03:23 +0000 (UTC)
sepe (sebastian.peters@xxxxxxxxx) 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@xxxxxxxxxxxxx
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/prodinfo/previousversions/books.mspx
.
- References:
- Prev by Date: Re: SYSTEM_USER automatic RTRIM of trailing spaces in 2000, but not 2005?
- Next by Date: Re: How can I produce multiple output flat files from a single SQL file with multiple SQL statements?
- Previous by thread: Re: Different empty_string/NULL handling under ODBC and OLE DB?
- Next by thread: SYSTEM_USER automatic RTRIM of trailing spaces in 2000, but not 2005?
- Index(es):
Relevant Pages
|
