[Gambas-user] Using dates in Gambas

[David Silverwood]

 Hi. Me again
So this is my code...
Public Sub btnUpdate_Click()        $Sql = "UPDATE cashup SET ("    $Sql = $Sql & "date = '" & edtDate.Text & "', "    $Sql = $Sql & "ref = '" & edtTransNo.Text & "', "    $Sql = $Sql & "amount = " & ValueBox1.Text & ", "    $Sql = $Sql & "comment = '" & edtDescription.Text & "', "    $Sql = $Sql & "WHERE date = '" & edtDate.Text & "';"        Print $Sql        Try    conMod.$Con.Exec($Sql)    Catch    objErr.dhError()        'error handler in objErr mod    End
It seems to me there is another way of doing this, but I haven't exactly been able to figure it out yet. It will however explain why sqlite refuses to accept a date from this code, no matter how I tried to format it.
I am reading the wiki and came across the Connection.Edit(gb.db) page. So I tried this...
Public Sub btnUpdate_Click()        '$Sql = "UPDATE cashup SET ("    '$Sql = $Sql & "date = '" & edtDate.Text & "', "    '$Sql = $Sql & "ref = '" & edtTransNo.Text & "', "   ' $Sql = $Sql & "amount = " & ValueBox1.Text & ", "    '$Sql = $Sql & "comment = '" & edtDescription.Text & "', "    '$Sql = $Sql & "WHERE date = '" & edtDate.Text & "');"        Result = conMod.$Con.Edit("cashup", "date = &2", edtDate.Text)    Result!date = edtDate.Text                    'note, no formats etc    Result!ref = edtTransNo.Text    Result!amount = ValueBox1.Text    Result!comment = edtDescription.Text        'update the values    Result.update    conMod.$Con.Commit    Catch    objErr.dhError()    End
Now I get "Invalid Assignment in FCashup...", referring to "Result = conMod.$Con.Edit(...)
So 'cashup" is the table, right?
'date = &2' refers to the date column which is the second col in the table (the SQL WHERE clause without the WHERE as the manual so aptly puts it), I assume.
I'm using this to 'update' the database. Is it correct and where is the error in my syntax?
Thanks for all your patience
David





     On Thursday, May 23, 2019, 9:11:08 AM GMT+2, Jorge Carrión <shordi at gmail.com> wrote:  
 
 In fact in Sqlite all data types are stored as characters and defined by "data affinity".
Very well explained here:https://www.sqlite.org/datatype3.html
Regards

El mié., 22 may. 2019 a las 15:37, Cedron Dawg (<cedron at exede.net>) escribió:

Hi Benoit,

I think you should have said: "In Gambas, dates are NOT strings", because in SQLite they are stored as such, which can make your proclamation confusing.

In my own case, I haven't started using dates yet.  I was/am assuming that the Gambas' Result class would take care of any converstions:

I think this is true:

  1) Gambas stores dates in its own internal format

  2) Databases store dates in their own (inconsistent across different DB types) formats.

  3) The Gambas database objects will convert for you under the covers.

When you say "Gambas has to create the date fields", is that short hand for "In database with more than one DATE type, Gambas will only work with one."?


Now, for the local vs UTC mess.  I got bit early on by some functions being UTC aware and others not.

Perhaps there should be two Date types in Gambas.  The regular Date meaning local time, i.e. locale unaware, and a UtcDate which is locale aware.  That way it would be really clear which one you are holding and the rules for display and database storage could be defined inpependently for both.

Just a thought.

Ced

P.S.  I've found working with databases delightfully easy in Gambas.

P.P.S  I haven't done any yet, but I presume that Gambas also handles boolean value storage conversions as well.


----- Original Message -----
From: "Benoît Minisini" <g4mba5 at gmail.com>

OK, you read it, but I'm not sure you understood it then.

"Dates are NOT strings" is the name of the first paragraph. Look at your 
words, you are talking about sending / receiving dates as strings from 
sqlite.

On the other hand, I didn't write the database-specific paragraph, so I 
will explain you:

1) You must send to the database and receive from it dates, not strings.

2) You have to create the database from Gambas, by using Date fields.

3) The gambas database component will take care of using the accurate 
date/time SQL type that works with your specific database.

4) To write a date to the database, you use: 'Result!Field = SomeDate'.

5) 'SomeDate' must be a date. If you use a string, you deal with 
implicit conversion.

6) To read the date back, you use 'SomeDate = Result!Field'.

7) 'SomeDate' is still a date. Now if you want to show the date to the 
user, (or to you), you have to use PRINT, Str(), or Format().

8) SQL date/time field types are a mess (as everything in SQL). The 
gambas database component will store the date in the database IN LOCAL 
TIME. Which is a stupid thing as soon as your database is accessed from 
different timezones. You have to manually convert the dates from the 
timezone of the server that writes to the database to the timezone of 
the client that reads it. An option for automatically doing that must be 
added to the Gambas database driver!

Are things more clear now?

-- 
Benoît Minisini

----[ Gambas mailing-list is hosted by https://www.hostsharing.net ]----

----[ Gambas mailing-list is hosted by https://www.hostsharing.net ]----


----[ Gambas mailing-list is hosted by https://www.hostsharing.net ]----
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <https://lists.gambas-basic.org/pipermail/user/attachments/20190530/c5f39f96/attachment.html>