SQLite adding fields

[SkinnyKenny]

Hello all....I am attempting to follow the tutorial on creating tables and fields for SQLite and am running into some issues. Firstly, I can't seem to find anything that shows what types of values to use for creating fields ie: "char(50), text, bool etc. Secondly, using the tutorial I am able to create a new database that saves to my "documents" which creates 2 fields, but as soon as I attempt to add more than 2 or 3, it refuses to do so. FYI I create a new database each time I attempt to do this so it's not trying to add fields to the existing database that I had already been created previously. Below is the snippet of code for creating the table and fields. Can someone point me in the right direction regarding making tables and configuring the field values LC accepts? I appreciate the help in advance.

Code: Select all

on databaseCreateTables
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE Daily_002 (Date char(50), Time char(50), Supervisor char(50), 5Q12 char(50), 5Q14 char(50))" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseCreateTables

[Klaus]

Hi Kenny,

welcome to the forum!

When it comes to SQL I always visit this site and refresh my poor knowledge.
Here the page for "alter table add column", which is what you are looking for:
https://www.w3schools.com/sql/sql_alter.asp

Best

Klaus

[SkinnyKenny]

Klaus,
Thank you for the response! I was wondering if the command structures that you showed me would work in LC. I will take a look today and see what I can do with them. Does LC recognize "date" "datetime", "time" "float" etc? I have been searching the forum for examples of how people have set up their tables but I haven't had much luck. I'm very sorry for the stupid questions.

[Klaus]

Hi Kenny,

most developers, including me, use a dedicated DB application to create the DB!
Like this one: https://sqlitebrowser.org. Way too much typing otherwise!

Does LC recognize "date" "datetime", "time" "float" etc?

Well, we are talking about SQL, right?
LC has nothing to do with that, LC ist just the "messenger".


Best

Klaus

[SkinnyKenny]

Yes we are! Thank you again for the link...I am checking it out now!

[Klaus]

You're welcome!
Drop a line if you need more help.

[bogs]

I don't know if you saw this or not SkinnyKenny, but it gives a pretty good basic outline of using databases in Lc.

[SkinnyKenny]

Thank you Bogs! I'm going to look that over as well. I appreciate the help.

[SkinnyKenny]

I'm not sure why but I am absolutely unable to create a table in the new database I create. I don't understand because it looks like my command structure for creating the fields is solid.

Code: Select all

on databaseCreateTables
   ## Add a contact_details table to the database
   put getDatabaseID() into tDatabaseID
   put "CREATE TABLE DailyLogDatagrid (Date date, Time time, Supervisor varchar(50), 5Q12 int(12), 5Q14 int(12), 5Q15 int(12), 5Q16 int(12), 5Q17 int(12), 5Q18 int(12), 5Q19 int(12), 5Q20 int(12), 5Q23 int(12), 5Q101 int(12), Comments text(255))" into tSQL
   revExecuteSQL tDatabaseID, tSQL
end databaseCreateTables

[AxWald]

Hi,

[...]
put "CREATE TABLE DailyLogDatagrid (Date date, Time time, Supervisor varchar(50), 5Q12 int(12), 5Q14 int(12), 5Q15 int(12), 5Q16 int(12), 5Q17 int(12), 5Q18 int(12), 5Q19 int(12), 5Q20 int(12), 5Q23 int(12), 5Q101 int(12), Comments text(255))" into tSQL
revExecuteSQL tDatabaseID, tSQL
[...]

Have you checked "the result"?
Have you checked this SQL statement in an SQL Manager, running it in your DB?
Have you created a similar table in your SQL Manager, and analyzed the resulting "CREATE TABLE ..." string?

No? That I'd do next, then.

Else I'd:

1. I'd think about using indices - this is a database, not a spreadsheet!
2. I'd quote my table & field names (single quote)
3. ... especially if I'd use reserved words ('Date') as field names (which I'd avoid!)
4. I'd drop all the length indications - SQLite handles this for itself
5. ... especially for INT: "The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes [...]"

What you may not have realized yet:

• When working with SQL databases, you use a second "programming language" - SQL.
• All that LC does is:
  1. connecting you to the database (revdb_Connect/ RevOpenDatabase),
  2. let you send SQL statements to the DB (revdb_Execute/ RevExecuteSQL and others),
  3. and disconnecting again (revdb_Disconnect/ RevCloseDatabase).
• For each SQL statement sent to the DB LC transmits the "answer" from the DB, in "the result" (for commands) or as function results.
• LC offers 2 different sets of tools for this:
  1. "revdb_*": A set of functions that directly returns the result from the DB
  2. "Rev*": The same, only some are functions, some are commands - be sure to look at the right place for your result!
• You can do anything in your database work with only the 3 functions mentioned above (Connect, Execute, Disconnect). Anything else are helper functions where LC tries to be nice - for instance translating arrays, offering shortcuts etc.

And, once again, the basic recommendation for database work:
1. ALWAYS check your result!
2. If there's problems, take your SQL statement (as you'd send it to the DB) and run it in your DB manager - this way you'll find your mistakes more quickly!

Have fun!

[SkinnyKenny]

AxWald,
Thank you for all the valuable information. I took a look and it has helped. Also, I was not aware that you could take the SQL command into the DB manager and execute the script to see if it passes. (I'm still learning how this all works). NOW, the results of my tinkering around found that for some reason when I use numeric's like "5Q12" to name the columns, it throws a syntax error when it sees the "5". I'm not exactly sure why this causes an issue but there it is. After renaming the fields, everything seems to work correctly. I just wanted to update you all. If anyone has an answer as to why this would happen, feel free to chime in.

[AxWald]

Hi,

quite sure it's a quoting issue. I made a table:

Code: Select all

CREATE TABLE "t_test" ("id" INTEGER PRIMARY KEY  AUTOINCREMENT  NOT NULL , "1" INTEGER, "2" VARCHAR)

(This is how SQLiteManager shows the create statement)

and did an insert:

Code: Select all

INSERT INTO `t_test` (`1`, `2`) VALUES (1, 'aText');

(Sent from LC)
This works.

You notice the graves (`) that I use for field and table names?
Using this for field & table names (whenever there's a possibility the db engine doesn't recognize it itself!), and single quotes for string literals (= anything but safe numeric values) is a good way to avoid a lot of trouble. Works in mySQL/ MariaDB too.
More about keywords & quoting in SQLite here.

An advanced text editor with macros, brackets evaluation, visualization for tabs, spaces and line breaks, & good replace functions is invaluable for writing SQL statements - write the statements there, test it in your SQLite Manager, format it & paste the final "put [...] into StrSQL" into the LC script editor. Can save hours of hair pulling ;-)

Have fun!

[SkinnyKenny]

First off, thank you all for the help. I feel like I have a small grasp on how it woks now. I do have a question though. How can I use a variable with the INSERT INTO command? I have local variables that I would like to use. Here is my code....

Code: Select all

   put "INSERT INTO dailyLog (d_field, t_field, Supervisor, Q12, Q14, Q15, Q16, Q17, Q18, Q19, Q20, Q23, Q101, Comments) VALUES ('"&& vDate &&"', '"&& vTime &&"', '"&& vSup &&"', '"&& v12 &&"', '"&& v14 &&"', '"&& v15 &&"','"&& v16 &&"','"&& v17 &&"','"&& v18 &&"','"&& v19 &&"','"&& v20 &&"','"&& v23 &&"','"&& v101 &&"','"&& vComments &&"')" into tSQL

[AxWald]

Hi,

Code: Select all

replace "&&" with "&" in tSQL

You don't want extra spaces in your data. Then:

Code: Select all

set the clipboardData["text"] to tSQL

before you send the statement to SQLite - you (and we :) )want to know what your above code results in!
Finally:

Code: Select all

if the result is not empty then put the result

(assuming you use "revExecuteSQL" (= command).
When "revdb_Execute" (= function) then the result is in the variable you put it in.

The resulting actual tSQL statement allows looking for mistakes then.
And "the result" may contain hints what happened ;-)

Without these there's not much I can do.

Have fun!

[SkinnyKenny]

AxWald,
Thanks again for the support! After some modifications to the code to pass the variables into the "INSERT" command, it works just fine. What I did not pay attention to was I was connecting to an old database that I created for testing and not the one I was attempting to write to. Needless to say I spent the weekend being frustrated as to why it wasn't writing to the database I wanted. So now, my final step will be retrieving that data from the database and writing it to the data grid to keep it updated periodically. Wish me luck!