CODEWITHSUNDEEP
phpdatetimeadovisual-foxprofoxpro
ScottF
ScottF

Reputation: 571

ADODB Update Method updating FoxPro Date/Datetime fields

I'm using ADODB to interface between my php code and my FoxPro .dbf tables. Here is the connection string and the opening of the RecordSet I'm using to update a table

$this->dbConnection->Open('Provider=VFPOLEDB.1;CursorType=2;Data Source="{path}";');
$this->recordSet->Open($sqlStr, $this->dbConnection, 3);

After opening the recordSet, and selecting some records, I need to update the values in the RecordSet then save those changes back to the table but I can't figure out the format/type to use when using Ado's Recordset 'Update' method. I don't want to use an sql UPDATE statement for a number of reasons - in this case to take advantage of batch updating.

The line that doesn't work whenever the FoxPro field data type is DateTime/Date/Time is this: 

$this->recordSet->Update('fieldName', $value);

I've tried it with the 'value' variable equal to:

"{//::}" or 'CTOT("{//::}")'

and neither of those work. I just get the error "Multiple-step operation generated errors. Check each status value." at:

com->Update('tcdate', '{//::}')

Any ideas on how to format the DateTime/Date value in php so that the Ado connection accepts it? Thanks!

UPDATE

After further testing, this seems to only be an issue with blank/empty date values. When using the update method, it works fine as long as the value doesn't include the curly braces around it like it needs to in an actual SQL statement. i.e. This works:

$this->recordSet->Update('dateField', '2016-01-21 02:10:48 PM');

Also, to add a new record, a value for all fields has to be specified so it isn't possible to add a new record with a blank date value via AddNew as far as I can tell. The following fails if there is also a Date type field in that record.

Upvotes: 1

Views: 1069

Answers (2)

Cetin Basoz
Cetin Basoz

Reputation: 23797

ADO is ANSI based and there is no "empty date" concept there. Instead, in your table set your date/datetime fields to accept nulls and simply do not send any value for those fields or explicitly send null. Another alternative would be, to execute "SET NULL OFF" on the same connection. When you do that, any fields that you do not pass are filled with their "default blank" values (0 for numeric, {} for date etc).

Updating a field with '0000-01-01' is simply asking for trouble (recently I had to deal with a customer's table giving headache and it turned out that someone did the same you did, putting table in an unusable state - hard to catch and fix).

Upvotes: 3

ScottF
ScottF

Reputation: 571

So I think I figured out how to do this. I had tried using the following line which didn't work and just gave an error.

$this->recordSet->Update('dateField', '0000-00-00 00:00:00 AM');

However, the following lines do work for datetime and date fields respectively, and result in an "empty" field in the table. 

$this->recordSet->Update('dateField', '0000-01-01 00:00:00 AM');
$this->recordSet->Update('dateField', '0000-01-01');

Upvotes: 0

Related Questions