Quick Database Question

[johnorourke1351]

Hi
I am wondering what is wrong with the syntax in this statement:
DatabaseUpdate(1, "INSERT INTO JobData VALUES ('" + GetGadgetText(#String_JobID) + "'" + GetGadgetText(#String_Job_Name) + "')")

I have previously opened the database I have values in the screen fields and I have successfully written test info to the database in this program

Could it be that these are string values coming from a form but the jobdata field jobid is defined as an integer in sqlite? Should I use some val() function to convert?

Thanks
John O'Rourke

[captain_skank]

I thing you need a comma between

Code: Select all

GetGadgetText(#String_JobID) + "'" + GetGadgetText(#String_Job_Name) + 

so it should read

Code: Select all

GetGadgetText(#String_JobID) + "','" + GetGadgetText(#String_Job_Name) + 

[johnorourke1351]

Hi Captain

Thanks for that. I put the comma in as you suggested but i am still not adding a record. As I said above, do I have a conversion problem? Should I put a val statement in for the numeric field( there are really more fields than this in the original one but I cut the statement down to keep it manageable for discussion purposes).

Thanks for your attention.

[johnorourke1351]

Even this statement without variable names is not working:
DatabaseUpdate(1, "INSERT INTO JobData VALUES ( '100' , 'sucky' , '12/30/2014' , '10:30', '10' , '100' , '400' , '8' , '30' , '0' , '0' , '0' , '0' ) ;" )
And that seems odd because other updates to tables in the same database do work. But those tables have all text fields. My database has a combination of integer, text, date, time, numeric fields defined.

Thanks again.

[johnorourke1351]

Hi,

Ok. Now I am VERY confused. I created a new table with no primary key and all text fields with the same names as those in JobData. I call the Table JobPata. Then I try to write a record to it in the program without using variable names:

DatabaseUpdate(1, "INSERT INTO JobPata VALUES ( '100' , 'sucky' , '12/30/2014' , '10:30', '10' , '100' , '400' , '8' , '30' , '0' , '0' , '0' , '0' ) ;" )

That doesn't execute and the program finishes without mentioning any error.

Hope someone can help.

Thanks

[infratec]

Hi,

without a working code it is difficult to help.
In general for an integer field '4' is wrong it is simply 4.

You should use something like that:

Code: Select all

If DatabaseUpdate(DB, SQL$) = 0
  Debug SQL$
  Debug DatabaseError()
EndIf

If I look in my crystal ball, I see something like that:

Code: Select all

DatabaseUpdate(1, "INSERT INTO JobPata VALUES ( 100 , 'sucky' , '12/30/2014' , '10:30', 10 , 100 , 400 , 8 , 30 , 0 , 0 , 0 , 0 ) ;" ) 

And this is only working if there are no other fields in the table, else you have to specify the fields after the table name.

Bernd

[johnorourke1351]

Voila. I had neglected to create one of the datafields in the table when I created it using the sqladmin program. Now the program works with the form fields and it works even though the fields are text and are being moved to numerics. But the date and time fields are still a problem. Thanks for the error checking idea. And thanks to anyone kind enough to help on this.

John

[infratec]

Hi,

for SQLite:

Code: Select all

UseSQLiteDatabase()

DB = OpenDatabase(#PB_Any, ":memory:", "", "", #PB_Database_SQLite)
If DB
  SQL$ = "PRAGMA encoding"
  If DatabaseQuery(DB, SQL$)
    While NextDatabaseRow(DB)
      Debug "Database encoding: " + GetDatabaseString(DB, 0)
    Wend
    FinishDatabaseQuery(DB)
  Else
    Debug SQL$
    Debug DatabaseError()
  EndIf
  
  SQL$ = "Create table records (id integer, te text, date text, time text, timestamp text)"
  If DatabaseUpdate(DB, SQL$)
    SQL$ = "INSERT INTO records VALUES (1, 'Test', date('2014-11-24'), time('19:06'), datetime('now'))"
    If DatabaseUpdate(DB, SQL$)
      Debug "Insert Ok"
     
      SQL$ = "SELECT id, te, date, time, timestamp FROM records"
      If DatabaseQuery(DB, SQL$)
        While NextDatabaseRow(DB)
          Debug "id  : " + GetDatabaseString(DB, 0)
          Debug "text: " + GetDatabaseString(DB, 1)
          Debug "date: " + GetDatabaseString(DB, 2)
          Debug "time: " + GetDatabaseString(DB, 3)
          Debug "timestamp: " + GetDatabaseString(DB, 4)
        Wend
        FinishDatabaseQuery(DB)
      Else
        Debug SQL$
        Debug DatabaseError()
      EndIf
     
    Else
      Debug SQL$
      Debug DatabaseError()
    EndIf
  Else
    Debug SQL$
    Debug DatabaseError()
  EndIf
  CloseDatabase(DB)
Else
  Debug DatabaseError()
EndIf

SQLite has no seperate datatypes for date or time,
they are stored as numeric.

https://www.sqlite.org/datatype3.html

Bernd

[johnorourke1351]

Thanks for such a detailed answer infratec.

I read that in the sqlite doc they had no date time format and yet sqladmin has date and time types when you define a table. But I will incorporate your coding suggestions into my program.

I really like this basic and the community that supports it. For years I have been looking for something that doesn't require a super long learning cycle and I have almost immediately been able to produce good working screen programs and now database appears to be within reach!

Thanks again.

[johnorourke1351]

Hi

on second thought I don't know if I do completely understand what you are saying in one of the statements. The latest rendition says:

SQL$ = "INSERT INTO records VALUES (1, 'Test', date('2014-11-24'), time('19:06'))"

the field names in the sqlite database are id, te, date, timestamp

the value fields are either literal or date and time. What is date and time here and how do I relate them to my form fields. Here the date is in yyyymmdd format but what is date and/or time?

Thanks

[infratec]

Hi,

date() and time() are functions of SQLite.

https://www.sqlite.org/lang_datefunc.html

You can change the format in PB or in SQLite.

PB example:

Code: Select all

"date('" + FormatDate("%yyyy-%mm-%dd", ParseDate("%mm/%dd/%yyyy", GetGadgetText(XYZ))) + "')"

SQLite example:

Code: Select all

"strftime('%d/%m/%Y', " + GetGadgetText(XYZ) + ")"

Bernd

[Shield]

Guys, you better don't forget escaping user input.

[johnorourke1351]

Once again I am in your debt.

Code: Select all

date=ParseDate("%mm/%dd/%yyyy",GetGadgetText(#String_Pour_Date))
time=ParseDate("%hh:%ii",GetGadgetText(#String_Pour_Start_Time))
     

DatabaseUpdate(1, "INSERT INTO JobData VALUES ('" + GetGadgetText(#String_JobID) + "','" + GetGadgetText(#String_Job_Name) + "','" + date +  "','" + time + "','" + GetGadgetText(#String_Load_Size) + "','" + GetGadgetText(#String_YPH_Ordered) + "','" + GetGadgetText(#String_Total_Yards_Ordered) + "','" + GetGadgetText(#String_Slab_Depth) + "','" + GetGadgetText(#String_One_Way_Travel) + "','" + GetGadgetText(#String_Added_Yard_Time) + "','" + GetGadgetText(#String_Added_Return_Time) + "','" + GetGadgetText(#String_Pump_Move_Time) + "','" + GetGadgetText(#String_Grab_Ass_Trucks) + "')")

Now this works and adds a record to the database. I had entered a date of 12/31/2014 and a time of 8:30. The date and time in the sqlite database is typed as numeric. When I examine the date in sqlite admin it appears as 1419984000 and the time as 30600. I guess I would have to reformat the dates myself before displaying them on the screen again and add the seconds to the date seconds since 1/1/1970.
The original date time stuff you gave me I am not sure how I would insert it into the above string. That is:
"date('" + FormatDate("%yyyy-%mm-%dd", ParseDate("%mm/%dd/%yyyy", GetGadgetText(XYZ))) + "')" this is the way you wrote it.
Anyways, I am slowly getting there

John

[Fangbeast]

Just to throw my oar in here, I read extensively in SQLite (and other) forums (by self professed experts) that if you are going to do any comparison testing on date fields (And I do), it's best to store the date as ISO text:

I.e YYYY-MM-DD format (2014-02-13)

Because of the way SQLite does greater/lesser comparisons. I actually found this way to be correct and working as they said and if I check my database using a third party tool, more readable than trying to read what the integer/numeric as a date was in text.

2014-02-13 is always going to be greater than 13-02-2014 etc.

Also, I don't know about SQLadmin or how good it is but another good, free one is SQLITEExpert personal. There is a paid version but the free one is pretty damned good.

http://www.sqliteexpert.com/download.html

Not sure if I went to the right page of sqladmin, that hasn't been updated in years and may have compatibility issues with newer versions of the SQLite data in PB whereas SQLiteExpert is constantly developed.

Was this the one you meant? http://sqliteadmin.orbmu2k.de/

[johnorourke1351]

The link you gave for sqladmin is the same one I used to download a copy of it. I will try the sql expert link you gave and see how it works. Also the sql admin is pretty basic. If you simply add a field to a table after you have data in it it won't automatically convert the old data. It creates a backup and you have to move the data via your own export import or create another query. Other databases like mysql do this automatically. Anyhow thanks for the suggestion and I think I prefer the idea of text based dates and times also. It is then easy to read it in one of these maintenance programs.

Thanks again