VFP and MySQL null dates

[Richard Stecenko]

I need to detect an empty date field in a table in a MySQL database.

In MySQL, the date is .null. and I can detect that in my SQL stamen.

But if the date is a control source on a VFP form,
1) gets entered by the operator.
2) the record is saved.
3) later the operator wants to remove the date, he deletes it.
4) VFP tries to send { / /} to MySQL and MySQL rejects it.

I tried checking before the save:

if empty(Date)
replace date with .null.
endif
* do the tableupdates, etc.

But that doesn't seem to work either.

Any advice on how to write a null date?

Richard Stecenko
Interactive Computer Services Inc.
Winnipeg, Canada
204.453.2052

[Olaf Doschke]

> I tried checking before the save:
>
> if empty(Date)
> replace date with .null.
> endif
> * do the tableupdates, etc.

In principle this should work, but it will only correct the current record
of course.

Take at look at the options set in the ODBC driver.
And is the date filed nullable on the MySQL side?

Bye, Olaf.

[Demetrios Panayotakopoulos]

Hi Richard,

I tried the following (4 records):

Sql="Insert Into Test (Date) Value (NULL)"
SQLEXEC(hSQL, SQL)
Sql="Insert Into Test (Date) Value ('')"
SQLEXEC(hSQL, SQL)
Sql="Insert Into Test (Date) Value ('" + DTOC(CTOD('')) + "')"
? SQLEXEC(hSQL, SQL)
Sql="Insert Into Test (Date) Value ('"+DTOC({})+"')"
? SQLEXEC(hSQL, SQL)

Data in MySQL
-------------
(NULL)
0000-00-00
0000-00-00
0000-00-00

Sql="Select * from Test"
SQLEXEC(hSQL, SQL)

Data in VFP
-----------
.NULL.
.NULL.
.NULL.
.NULL.

Is the default value of date column NULL?
I am not sure what you are trying to do.

Demetrios, Greece

[Richard Stecenko]

Thanks, Demetrios.