Quick Database Question

[johnorourke1351]

Yes that sqliteexpert.com site is fabulous. The freeware version is much better than sqliteadmin.

Nice suggestion.

John

[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.

[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

[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

[infratec]

And ?

Is it working or not?
If not:

show us the result of

Code: Select all

Debug SQL$
Debug DatabaseError()

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

[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

[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

[Fangbeast]

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.

[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.