database SQL types: date, time, datetime

[muellerto]

I made a post here more than a year ago viewing some issues related to database types handling date and time.

I tried the new 1.4.0 now and I see: even POCO 1.4.0 doesn't handle date, time and datetime values correctly. I tried MySQL and SQLite databases. I made a table like this (MySQL):

Code: Select all

create table MyTable (t datetime primary key not null, v double);
insert into MyTable values (now(), 5.0);
insert into myTable values (now(), 6.0);


Then I wrote a piece of code requesting these two rows:

Code: Select all

Statement select(*m_pSession);
select << "select t,v from MyTable order by t;";
select.execute();

RecordSet rs(select);
if (rs.moveFirst())
{
   do {
      cout << rs.value(0).convert<string>() << ", " << rs.value(1).convert<string>() << endl;
   } while (rs.moveNext());
}


This code still throws exceptions containing the message "Not Found.". I can even avoid the explicit evaluation of rs.value(0) - as long as the result contains a column of a date or time SQL type POCO throws truely. If I change the type of the t column in the SQL table to char(19) or something other everything works fine.

Last time somebody told me to use char(19) because I did ask more or less relating SQLite. Meanwhile my code runs on both SQLite and MySQL very well. But using char(19) as timestamp on a true database server is somehow foolish, especially because a server forbids using SQL functions like now() and date/time arithmetics on simple char-columns. It's a pity.

[guenter]

You'll have to wait until 1.6 for DateTime support. Or you could use the Data library from the SVN trunk, which has DateTime support. You'll have to make some changes to Foundation though. The plan is to start integrating the current Trunk version with 1.4 in the spring. This will be done in the 1.5 branch. Once this is ready for release, the result will be released as 1.6.