[Gambas-devel] SHOW STOPPER: GB3, sqlite3 and date fields

[Benoît Minisini]

> sqlite3 correctly displays dates:
> 
> sqlite> select LogdtTimestamp from log limit 10;
> 9/7/2008 20:22:04
> 9/7/2008 20:26:03
> 9/7/2008 20:41:29
> 9/7/2008 20:59:48
> 9/7/2008 21:35:02
> 9/7/2008 21:37:37
> 9/7/2008 22:02:28
> 9/7/2008 22:26:49
> 9/7/2008 22:29:20
> 9/7/2008 22:38:10
> 
> Attachment Screenshot-1.png shows the date as Gambas3 interprets it,
> which is plainly wrong.
> 
> I have checked the data with sqlite3 and with sqlitebrowser, and in
> both cases the dates are displayed correctly.
> 
> Also attached is a database and supporting code showing the problem.
> In short, the dates returned by Gambas are gobbledegook.
> 
> I have confirmed that the problem also exists in gb2.
> 
> Environment is Ubuntu 9.10.
> 

The date datatype does not exist in SQLite, it is stored as a string.

So it has to be emulated by Gambas.

It was decided to store dates in ISO-8601 format, the format used by date and 
time functions of SQLite. As soon as you write the data with Gambas, and read 
it back, there is no problem. 

But if you write data in a different format, Gambas does not recognize it and 
returns garbage. It should return an error anyway instead of garbage, but the 
guy who wrote the code didn't do that.

I can modify the SQLite drivers to accept the american format, but I can't add 
all date format of the planet easily...

So you have four solutions:

- Writing dates in the database by using the ISO-8601 format.

- Writing dates in the database from Gambas.

- Using a string field instead of a date field in the SQLite database. It is 
not a problem, as SQLite stores everything as strings (It is more complex, but 
I simplify...)

- Waiting for me to update the SQLite drivers.

And, no, crying, howling and moaning like a child is not a solution. Just 
explain your problem like an adult, and you will get the answer as well. 

Now there is one more people that won't talk to you anymore. When nobody won't 
talk to you at all (even my own patience has its limit), you will have to find 
another language. 

Did you try to get the UTF-8 output of an external running process on 
Microsoft .Net? You will have a lot of reason to howl there too, but there is 
no mailing-list where some kind people will explain you what to do, and which 
undocumented function to use to make it work, for free.

-- 
Benoît Minisini