Inserting NULL via parameters
lazarus / 16-Databases

----------------------------------------------------------------------
Arbee · 2011-01-05 09:48

Hello,
I can't imagine I'm the first to ask, but I couldn't find it on the
forum ....

I have a program that inserts data in a MySQL database, the value of
which comes from a text file.  A simple LOAD is not possible as a lot
of pre-processing needs to be done on the data from the file before it
can be inserted.  That is the whole purpose of this program.

One of the columns is a DATE field. The data is inserted in the SQL
statement via a ParamByName call, like:

Code: [Select]TheSQLQuery.ParamByName('someDate').AsDate := aDate;

where aDate is a TDate (and thus a double), converted from the
contents in the text file (that obviously was a string).

But if the value of that date in the file is blank - no date available
- I would like to insert NULL in the table.  I can't do that by
setting the TDate to zero as that will insert the 30th of December
1899 as date in the table.

So how do I go about inserting a NULL value into the table?

----------------------------------------------------------------------
Arbee · 2011-01-05 10:13

I already found a solution which works (for Zeoslib at least)
Just don't fill the parameter,  something like:

Code: [Select]if DateAvailable then
   TheSQLQuery.ParamByName('someDate').AsDate := aDate;

Where DateAvailable is a boolean that true or false depending on
whether the file contained a valid date.

I just gave it a try, convinced as I was that it would return me a
message like "no value given for parameter :someDate" or something to
that extent.

But lo and behold, it inserted a NULL instead.

----------------------------------------------------------------------
SteveF · 2011-01-05 21:21

Yes, that makes sense -- as long as the column definition in MySQL
allows nulls, building an Insert SQL statement that does not supply a
value for that column should result in the column being set to null in
that row.

Steve

----------------------------------------------------------------------
José Mejuto · 2011-01-06 19:21

Quote from: Arbee on January 05, 2011, 10:48:42 amHello,
I can't imagine I'm the first to ask, but I couldn't find it on the
forum ....
Code: [Select]TheSQLQuery.ParamByName('someDate').AsDate := aDate;

Hello,

Code: [Select]TheSQLQuery.ParamByName('someDate').Value := NULL;

----------------------------------------------------------------------
clauslack · 2011-01-07 13:33

I think that this work too

TheSQLQuery.ParamByName('someDate').Clear;

Regards

----------------------------------------------------------------------
Arbee · 2011-01-07 15:52

Thanks folks ... I'm spoilt for choice now