Inserting empty values into NOT NULL columns via ODBC

[FizzBin]

We are writing a C application that is using ODBC to insert records
into a database. We have a NOT NULL column that can legitimately have
an empty value, i.e. we know the value and it is empty (i.e. a zero
length string).

We are using SQLBindParameter() to bind a variable to the
parameterized insert statement <<in the form: INSERT INTO table VALUES
(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.

We are running into the problem where ODBC is converts the empty (zero
length) string into a NULL value and this errors due to the fact that
the column is defined as NOT NULL.

We do not want to redefine the column as NULL, becasue my
understanding of the correct usage of a NULL column is to indicate
that a value is unknown or meaningless... in our case we know the
value (it is empty) and an empty value has meaning within our
application.

I'm sure that this issue has been seen and address thousands
(millions?) of times... any guidance would be appreciated.

[BJ Freeman]

NULL means that at the time the row is created that no data has to be
inserted, into that column and will not be checked for.
NOT NULL means there will be a check to make sure there is something other
than a null.
The best you can do is use a space for varchar and 0 or 0.0 for numeric.

"FizzBin" <id*********@yahoo.com> wrote in message
news:64**************************@posting.google.c om...

We are writing a C application that is using ODBC to insert records
into a database. We have a NOT NULL column that can legitimately have
an empty value, i.e. we know the value and it is empty (i.e. a zero
length string).

We are using SQLBindParameter() to bind a variable to the
parameterized insert statement <<in the form: INSERT INTO table VALUES
(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.

We are running into the problem where ODBC is converts the empty (zero
length) string into a NULL value and this errors due to the fact that
the column is defined as NOT NULL.

We do not want to redefine the column as NULL, becasue my
understanding of the correct usage of a NULL column is to indicate
that a value is unknown or meaningless... in our case we know the
value (it is empty) and an empty value has meaning within our
application.

I'm sure that this issue has been seen and address thousands
(millions?) of times... any guidance would be appreciated.

[Erland Sommarskog]

FizzBin (id*********@yahoo.com) writes:

We are writing a C application that is using ODBC to insert records
into a database. We have a NOT NULL column that can legitimately have
an empty value, i.e. we know the value and it is empty (i.e. a zero
length string).

We are using SQLBindParameter() to bind a variable to the
parameterized insert statement <<in the form: INSERT INTO table VALUES
(?, ?, ?)>>. We are using SQLExecDirect() to process the SQL.

We are running into the problem where ODBC is converts the empty (zero
length) string into a NULL value and this errors due to the fact that
the column is defined as NOT NULL.
...

I'm sure that this issue has been seen and address thousands
(millions?) of times... any guidance would be appreciated.

Nah, direct programming against the ODBC interface from C is not what
everyone does. :-)

It would probably help if you posted your code, both the call to
SQLBindParameter and SQLExecDirect. If you make a complete program
of it, I might even be able to play with. (OK, so I have never programmed
against the ODBC interface myself.)

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp