Page 2 of 2
Re: Quick Database Question
Posted: Tue Nov 25, 2014 2:20 am
by johnorourke1351
Yes that sqliteexpert.com site is fabulous. The freeware version is much better than sqliteadmin.
Nice suggestion.
John
Re: Quick Database Question
Posted: Tue Nov 25, 2014 2:51 am
by Fangbeast
The link you gave for sqladmin is the same one I used to download a copy of it.
Whew! Glad I got it right. That one hasn't been updated in absolute years.
Thanks again
No worries. Pretty much everything I do these days has a database backend. Not that I know what I am doing (grin), but they all work and no database expert has ever told me off (yet) for bad practices.
Re: Quick Database Question
Posted: Tue Nov 25, 2014 7:58 am
by infratec
I changed my example on page one:
Changed the date/time fields to text
Added a timestamp field
Show the database encoding
Other free tools:
http://sqlitebrowser.org/ (active developed)
http://www.singular.gr/sqlite/ (latest version from 2010)
Bernd
Re: Quick Database Question
Posted: Tue Nov 25, 2014 10:19 pm
by johnorourke1351
Hi
I have settled on a final handling of this database insert issue. I have made the date and time fields in the data base into text fields. I have made the insert logic like this:
Code: Select all
SQL$=""
SQL$ = "INSERT INTO JobData VALUES ('" +
SQL$ + GetGadgetText(#String_JobID) + "','" +
SQL$ + GetGadgetText(#String_Job_Name) + "','" +
SQL$ + GetGadgetText(#String_Pour_Date) + "','" +
SQL$ + GetGadgetText(#String_Pour_Start_Time) + "','" +
SQL$ + GetGadgetText(#String_Load_Size) + "','" +
SQL$ + GetGadgetText(#String_YPH_Ordered) + "','" +
SQL$ + GetGadgetText(#String_Total_Yards_Ordered) + "','" +
SQL$ + GetGadgetText(#String_Slab_Depth) + "','" +
SQL$ + GetGadgetText(#String_One_Way_Travel) + "','" +
SQL$ + GetGadgetText(#String_Added_Yard_Time) + "','" +
SQL$ + GetGadgetText(#String_Added_Return_Time) + "','" +
SQL$ + GetGadgetText(#String_Pump_Move_Time) + "','" +
SQL$ + GetGadgetText(#String_Grab_Ass_Trucks) + "')"
If DatabaseUpdate(DB,SQL$)
MessageRequester("Database" , "Record Added", #PB_MessageRequester_Ok)
Else
MessageRequester("Database", "Insert Failed" + SQL$ + "Error:" + DatabaseError(), #PB_MessageRequester_Ok)
EndIf
JobId and the fields after time are defined as integer and numeric respectively.
All the help I received is appreciated. I hope I can contribute to this forum.
John
Re: Quick Database Question
Posted: Tue Nov 25, 2014 11:11 pm
by infratec
And ?
Is it working or not?
If not:
show us the result of
Or the text in the MessageRequester.
I think JobID is an integer, so you can not use '123' you have to use simply 123.
Or are all your table fileds now of type Text?
That wouldn't be a good idea, since at least the primary key should be a 'serial' unique integer.
Bernd
Re: Quick Database Question
Posted: Tue Nov 25, 2014 11:54 pm
by johnorourke1351
Hi
Here is the result of looking at debug SQL$ INSERT INTO JobData VALUES ('9422','ABC','12/02/2014','00:30','10','120','1200','8','45','0','0','0','0')
it sends text but it apparently converts correctly into a JobID because the record is added. I validate each fields before posting to make sure they have the right datatype and are not null.
Apparently there is no database error because the debug screen doesn't show a result for it.
The proof is that the record is added and I guess the affinity feature maybe converts it for me.
the primary key is type integer and name date and time are the only text fields. The other fields are defined as numeric.
John
Re: Quick Database Question
Posted: Wed Nov 26, 2014 7:38 am
by infratec
Ok,
it's working. That's the main point.
In my opinion it should look like this:
Code: Select all
INSERT INTO JobData VALUES (9422,'ABC','12/02/2014','00:30',10,120,1200,8,45,0,0,0,0)
Bernd
Re: Quick Database Question
Posted: Wed Nov 26, 2014 9:42 am
by Fangbeast
infratec wrote:Ok,
it's working. That's the main point.
In my opinion it should look like this:
Code: Select all
INSERT INTO JobData VALUES (9422,'ABC','12/02/2014','00:30',10,120,1200,8,45,0,0,0,0)
Bernd
Bernd, whether a number is text format or decimal and whatever the type, sqlite always seems to store it as text (SQLite site mentions it somewhere) so I developed the habit of always passing numbers as quoted text and dealt with it accordingly when reading back and it's worked for years but I do it to leave me unconfused.
I have seen that quoted or not, it takes the same number of bytes to store a value so there is no advantage to quoting one and not the other and I got used to doing it that way because of my absolutely shocking eyesight.
INSERT INTO JobData VALUES ('9422', 'ABC', '12/02/2014', '00:30', '10', '120', '1200', '8', '45', '0', '0', '0', '0')
But for dates, i'd still pass them as ISO format: 2013-02-12 instead of 12/02/2014 for greater than/less than comparison purposes etc.
Re: Quick Database Question
Posted: Wed Nov 26, 2014 6:52 pm
by johnorourke1351
Thanks people.
I think we have pretty well discussed this area. I am now curious about how to read previous in sqlite. I know you can't do it with PreviousDatbaseRow. I will create a new topic for discussing this area if I can't really get a solution today.