[Gambas-user] Using dates in Gambas

Thu May 30 15:11:46 CEST 2019

Sorry I forgot a piece of code:
hResult = hConn.Find("users", "birth = &1", Date(1947, 4, 10))
Print hResult["id"];; hResult["name"]

Gianluigi

Il giorno gio 30 mag 2019 alle ore 14:24 Gianluigi <bagonergi at gmail.com> ha
scritto:

> 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/9a10f40f/attachment-0001.html>