Skip to Content.
Sympa Menu

freetds - [freetds] sending NULL to non-null column with BCP

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: jklowden AT schemamania.org
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: [freetds] sending NULL to non-null column with BCP
  • Date: Thu, 29 Sep 2011 15:28:22 -0400

In CVS HEAD I just committed a change to how the bcp functions deal with
missing
data in the client. If you run Sybase, I would be very much obliged if you
would test this change briefly with freebcp to confirm it works with TDS
5.0.

Just to clear a little terminological hurdle: to "send NULL data" is a
misnomer,
because what's sent isn't data, isn't "a NULL", but metadata stating the data
are missing. But we say "send a NULL" because it's easier.

OK, at least that's behind us.

The target table of bcp_sendrow has columns, each of which may or may not
accept NULL. The client-side data to be sent may be missing for a column
declared NOT NULL. Until today, the bcp library checked for that situation
and raised SYBEBCNN, saying you can't send NULL to a non-NULL column.

But: the column may have a default declared, and the server will provide that
default even for a bcp load. In fact, Microsoft's bcp.exe has an option to
force a NULL to be kept, so to speak, even if a default is declared. (Why
you'd
declare a default for a NULLable column is a discussion for another day.) I
suspect you can use the KEEPNULL hint in freebcp to effect that, but I
haven't tested it. Absent that option, the server routinely provides the
default in place of missing data for both INSERT and BCP.

My change is to remove the persnicketiness from the client. If the data are
missing on the client's end, it sends a NULL regardless of whether or not the
column accepts NULL. If a default is declared, the server can apply it; if
not, the server will raise an error.

I tested this on SQL Server 2008. If you see or know about other behavior on
other servers, please say so.

--jkl



  • [freetds] sending NULL to non-null column with BCP, jklowden, 09/29/2011

Archive powered by MHonArc 2.6.24.

Top of Page