[Gambas-user] Using dates in Gambas

[Gianluigi]

Something about the type of Find [0] also exists in VB, and you should know
it.
[0] http://gambaswiki.org/wiki/comp/gb.db/_connection/find

As explained to you by Minisini, Gambas uses dates as a datetime regardless
of which database you use.
Gambas takes care of managing everything under the covers.

In my example it changes Find like this:
hResult = hConn.Find("users", "birth", Date(1947, 4, 10))
Print hResult["id"];; hResult["name"]

Regards
Gianluigi

Il giorno gio 30 mag 2019 alle ore 10:55 David Silverwood via User <
user at lists.gambas-basic.org> ha scritto:

> 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 ]----
>
> ----[ 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/e24d4ac4/attachment.html>