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 SQLBindParamete r() 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]

Re: Inserting empty values into NOT NULL columns via ODBC

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" <idea_vortex@ya hoo.com> wrote in message
news:64748609.0 309120738.22e23 cea@posting.goo gle.com...[color=blue]
> 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 SQLBindParamete r() 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.[/color]

[Erland Sommarskog]

Re: Inserting empty values into NOT NULL columns via ODBC

FizzBin (idea_vortex@ya hoo.com) writes:[color=blue]
> 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 SQLBindParamete r() 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.[/color]

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
SQLBindParamete r 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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp