SQLite adding fields

Deploying to Windows? Utilizing VB Script execution? This is the place to ask Windows-specific questions.

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

SQLite adding fields

Post by SkinnyKenny » Fri Jan 24, 2020 6:54 am

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
Posts: 11495
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite adding fields

Post by Klaus » Fri Jan 24, 2020 11:31 am

Hi Kenny,

welcome to the forum!

When it comes to SQL I always visit this site and refresh my poor knowledge. :D
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
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Fri Jan 24, 2020 11:43 am

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
Posts: 11495
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite adding fields

Post by Klaus » Fri Jan 24, 2020 12:09 pm

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! :D
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
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Fri Jan 24, 2020 12:12 pm

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

Klaus
Posts: 11495
Joined: Sat Apr 08, 2006 8:41 am
Location: Germany
Contact:

Re: SQLite adding fields

Post by Klaus » Fri Jan 24, 2020 12:15 pm

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

bogs
Posts: 4199
Joined: Sat Feb 25, 2017 10:45 pm

Re: SQLite adding fields

Post by bogs » Fri Jan 24, 2020 8:44 pm

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

SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Sat Jan 25, 2020 9:33 pm

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

SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Sat Jan 25, 2020 10:22 pm

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
Posts: 387
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQLite adding fields

Post by AxWald » Sun Jan 26, 2020 10:33 am

Hi,
SkinnyKenny wrote:
Sat Jan 25, 2020 10:22 pm
[...]
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!
Livecode programming until the cat hits the fan ...

SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Sun Jan 26, 2020 9:26 pm

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
Posts: 387
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQLite adding fields

Post by AxWald » Mon Jan 27, 2020 11:52 am

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!
Livecode programming until the cat hits the fan ...

SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Tue Jan 28, 2020 4:33 am

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
Posts: 387
Joined: Thu Mar 06, 2014 2:57 pm

Re: SQLite adding fields

Post by AxWald » Tue Jan 28, 2020 11:05 am

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!
Livecode programming until the cat hits the fan ...

SkinnyKenny
Posts: 23
Joined: Fri Jan 24, 2020 6:44 am

Re: SQLite adding fields

Post by SkinnyKenny » Wed Jan 29, 2020 3:52 am

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!

Post Reply

Return to “Windows”