471,066 Members | 1,302 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,066 software developers and data experts.

Inserting empty values into NOT NULL columns via ODBC

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.
Jul 20 '05 #1
2 8619
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.

Jul 20 '05 #2
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
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by Wayno | last post: by
3 posts views Thread by tornado | last post: by
reply views Thread by GreenFROG Design | last post: by
reply views Thread by Marko Poutiainen | last post: by
16 posts views Thread by bill | last post: by
9 posts views Thread by Andrew Banks | last post: by
3 posts views Thread by rcoco | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.