Datetime fields return value in different format?

Hi,

I get newly different displays of datetime values using MySQL database. The
Data Viewer shows the values correctly, not so the DateTimePicker and
DBGrid.

The value format is correct when fresh entered in the DateTimePicker,
changes after saving and reloading - in the Data Viewer doesn't change
anything. Is this related to DB-settings (which ones)? It seems that the DB
reports them well (Data Viewer).

Also there is an issue saving the data: sometimes it works, mostly not,
without any Firebug error output.

Changes made recently: Upgrade of MySQL to version 5.1.

Thanks for any information!

John

> I get newly different displays of datetime values using MySQL database. The
> Data Viewer shows the values correctly, not so the DateTimePicker and
> DBGrid.
>
> The value format is correct when fresh entered in the DateTimePicker,
> changes after saving and reloading - in the Data Viewer doesn't change
> anything. Is this related to DB-settings (which ones)? It seems that the DB
> reports them well (Data Viewer).
>
> Also there is an issue saving the data: sometimes it works, mostly not,
> without any Firebug error output.
>
> Changes made recently: Upgrade of MySQL to version 5.1.

You may need to do some coercion of the date data in your select query from MySQL as the DBGrid in particular relies upon string data and has no autoconversion of datatypes. The Datetime picker can have a multitude of datetime formats which are set as a property. I recommend you trackl down the source code or rather the documentation for that control from the original developer. The PDF doc does not come with the VCL for PHP.

http://www.dynarch.com/projects/calendar/old/

Conversely when saving the data you may have to cast the data or format the date data but this depends entirely on whether you are using tables views or stored procedures to update the data.

If you need to debug SQL issues like failed updates and incorrect parameters or mangled SQL and you dont have a SQL monitor like Toad for MySQL then turn on debuging in the Database component property "Debug". I doubt Firebug will help you.

These kinds of value/property issues should be readily solvable by setting breakpoints in the PHP debugger when you are making assignments to and from the control display value. Have you not tried that approach?

Hi Steve,

Thank you for answering.

> You may need to do some coercion of the date data in your select query
> from MySQL as the DBGrid in particular relies upon string data and has no
> autoconversion of datatypes. The Datetime picker can have a multitude of
> datetime formats which are set as a property. I recommend you trackl down
> the source code or rather the documentation for that control from the
> original developer. The PDF doc does not come with the VCL for PHP.

I have used the value property in the JS. If I select a date the
DateTimePicker schows it correctly, thus the format setting is OK. Also the
datavieweer shows the date values correct. Thus the DB data format set is
OK. I set e.g. '22.06.2009' and after saving I get '22-06-2009' in the
controls only, not in the dataviewer, there I get '22.06.2009'. The OS data
format is set to '22.06.2009'.

> Conversely when saving the data you may have to cast the data or format
> the date data but this depends entirely on whether you are using tables
> views or stored procedures to update the data.

In the SQLs I just use the PHP-variable with quotes around and escaped. I
use the same SQL in SQL-scripts to preload the tables and it's working.
Therfore I guess it has to do with the date format changed by some process
during saving. I'm not very expert configuring MySQL.

> If you need to debug SQL issues like failed updates and incorrect
> parameters or mangled SQL and you dont have a SQL monitor like Toad for
> MySQL then turn on debuging in the Database component property "Debug". I
> doubt Firebug will help you.

I have set it and I'll try.

> These kinds of value/property issues should be readily solvable by setting
> breakpoints in the PHP debugger when you are making assignments to and
> from the control display value. Have you not tried that approach?

Unfortunately I use JS eventhandlers with ajax-calls to handle data. It's
faster. Besides, if setting breakpoints, the process slows down until
timeout many times or just not to load any more (worse with Firefox, some
better with IE).

John

>
> Unfortunately I use JS eventhandlers with ajax-calls to handle data. It's
> faster. Besides, if setting breakpoints, the process slows down until
> timeout many times or just not to load any more (worse with Firefox, some
> better with IE).
>
> John

Well we all use AJAX hopefully but at the end of the day AJAX is just calling PHP in the background on an asynchronous thread (this is also why you need a PHP debugger doing AJAX as Firebug is not enough). This is where your insert is happening (the MYSQL library is a PHP dll after all) not in the client side javascript. Connection timeouts can be easily upped on the database component or in PHP ini or is maybe a property of the connnectionor a settable default in MySQL. For myself I have patched the VCL
for PHP to use the current build of Xajax which has a more effective/working oncomplete event you can trap in javascript. Also ADODB has moved on in versions and may have improved date handling in MySQL. You should be able to get the current version of that and basically substitrute it for the VCL for PHP version without needing any edits in the core VCL components.

If the Delphi for PHP debugger is driving you nuts, shell out for VS.PHP. It uses Xdebug and is very fast and slices readily through code. You can edit and design in D4PHP and debug in VS.PHP quite happily at the same time, as VS.PHP has a separate PHP/Apache subsystem. There are other options for debugging as well if you dont want to spend money. You can try the eclipse based Aptana or the Eclipse PHP Development Tools (PDT)altyhough these are complex to use and configure.

Also Firebug has had a lot of problems with Ajax and only the most recent builds of Firefox (3.5RC) with Firebug 1.4x are actually working properly again. IE8 now has pretty usable debugger built in as well although not as well integrated as Firebug.

[405hp]

Also there is an issue saving the data: sometimes it works, mostly not,

John - are you using table/query components or direct mysql access for the postings? With direct you can just trap the result failures and look up what the error is.

PHP Code:

function sqlErrors($query)
               {
                       //debug log
               $regdate=date('Y/m/d H:i:s');
               $message  = $regdate.' <b>'.$_SERVER['PHP_SELF'].' - Invalid query: '.mysql_errno().'</b><br>' . mysql_error() ." ip ".$_SERVER["REMOTE_ADDR"]. '<br><br>';
               $message .= '<b>Whole query:</b><br>' . $query .'<br>'.json_encode($_REQUEST).'<br>___________________________________<br>';
               $file="errors.html";
               $handle = fopen($file, "a");
               fwrite($handle, $message);
               fclose($handle);
               }
             $result=mysql_query($sql);
               if (!$result) sqlErrors($sql); 


I've never tried this but, you may be able to check the errors in adodb with just adding mysql_errno().'</b><br>' . mysql_error() to a test label value to see if anything is happening that you can check.

from the docs MySQL :: MySQL 5.1 Reference Manual :: 10.3 Date and Time Types

Although MySQL tries to interpret values in several formats, dates always must be given in year-month-day order (for example, '98-09-04'), rather than in the month-day-year or day-month-year orders commonly used elsewhere (for example, '09-04-98', '04-09-98').

Steve - I've not had any problem with viewing ajax responses with firebug, from old versions to new ones they are always visible in the console. A quick look there has always told me if I had a problem.

Thanks, this helped so far!


"Delphi-PHP Forums" <405hp.3u9i0z@no-mx.delphi-php.net> wrote in message
news:130959@forums.codegear.com...
>> Also there is an issue saving the data: sometimes it works, mostly not,
> John - are you using table/query components or direct mysql access for
> the postings? With direct you can just trap the result failures and look
> up what the error is.
>
> PHP code:
> --------------------
>
> function sqlErrors($query)
> {
> //debug log
> $regdate=date('Y/m/d H:i:s');
> $message = $regdate.' <b>'.$_SERVER['PHP_SELF'].' - Invalid query:
> '.mysql_errno().'</b><br>' . mysql_error() ." ip
> ".$_SERVER["REMOTE_ADDR"]. '<br><br>';
> $message .= '<b>Whole query:</b><br>' . $query
> .'<br>'.json_encode($_REQUEST).'<br>______________ _____________________<br>';
> $file="errors.html";
> $handle = fopen($file, "a");
> fwrite($handle, $message);
> fclose($handle);
> }
> $result=mysql_query($sql);
> if (!$result) sqlErrors($sql);
>
> --------------------
>
>
> I've never tried this but, you may be able to check the errors in adodb
> with just adding mysql_errno().'</b><br>' . mysql_error() to a test
> label value to see if anything is happening that you can check.
>
> from the docs 'MySQL :: MySQL 5.1 Reference Manual :: 10.3 Date and
> Time Types'
> (http://dev.mysql.com/doc/refman/5.1/...ime-types.html)
>> Although MySQL tries to interpret values in several formats, dates
>> always must be given in year-month-day order (for example, '98-09-04'),
>> rather than in the month-day-year or day-month-year orders commonly used
>> elsewhere (for example, '09-04-98', '04-09-98').
>
>
> Steve - I've not had any problem with viewing ajax responses with
> firebug, from old versions to new ones they are always visible in the
> console. A quick look there has always told me if I had a problem.
>
>
> --
> 405hp
> ------------------------------------------------------------------------
> 405hp's Profile: 1089
> View this thread: Datetime fields return value in different format?