LiveCode Forums.

Posted: **Wed May 25, 2011 4:48 pm**

Hi There
I'm relatively new to Livecode and need some pointers towards how to read and write data to and from sqlite databases. In what I want to do, the sqlite3 databases are already written and Livecode doesn't need to create anything. Ultimately, the user fills in many different fields, each field corresponding to predetermined parts in an sqlite table. Then, I would have a 'refresh', 'update' and 'save' button. To select which of the inputted data entries you want, you select from a list field containing all the database entries. Once you select from the list field, all the data fields populate automatically from data from the database allowing you to edit or read etc.

My problem is that I've looked at as many examples I can find and can't find any which assume you don't want to create tables in sqlite. Therefore, I'm getting pretty lost. I also managed to get the database query built into Livecode to work but then read on this forum to avoid it at all costs!! Aarggh.

What I was wondering is if anyone knows of an example stack or tutorial online that could help?

Thanks in advance

Posted: **Wed May 25, 2011 5:22 pm**

http://forums.runrev.com/viewtopic.php? ... 67&start=0

There's a stack in that thread that might help you.

Posted: **Wed May 25, 2011 5:24 pm**

Thank you so much. Will take a look.

Posted: **Wed May 25, 2011 6:39 pm**

Sorry, I'm still a little lost with this.
I can roughly see from this example how to connect to the database which is fine. However, rather than create random data in a list field, I need to pair text input fields in livecode with entries in a sqlite table. There is quite a few of them. Then, the refresh, update and save buttons need to write to and from all these fields. The list field in my interface needs to display a few table entries to identify the input line so that when you click select it, all the fields populate with the rest of the data.

I think where I'm stuck is understanding how the databinding happens between the many text input fields in the interface and the sqlite database.

Sorry for being a bit slow on this.

Posted: **Wed May 25, 2011 6:46 pm**

You need to create code that does what you want. Only experience and therefore only experimentation will bring you closer to that goal.

a way to write data from a field into a database is to us "update" in sql. which you can use in livecode via the revExecuteSQL statement.
a way to read data from a database and put it into a field is the revDataFromQuery function.

If you could rephrase your problem as a single, specific question, maybe you will be able to get a specific answer?

Posted: **Wed May 25, 2011 7:01 pm**

Sorry BvG, you're right.
What I need to know is:
a) If I have 50 input fields in the interface which needs to pair with entries into an sqlite database, do I need to write individual revExecuteSQL statements for each field? All fields have been individually named for easy identification already.
b) For the update, create and save buttons, do I need to list all the fields for each one?
c) Is it possible to have a listfield which has 3 or 4 selected columns from the database to allow the user to identify the entry they want so they can click in the list field which then causes all the fields to update with the entries for that entry? So imagine the list field has John, Mark and Graham. When you click on Mark, all the text fields immediately update to show all the data for Mark, allowing you to add, update and save the data.

I know this is basic stuff but I just can't find the tutorials or help which can show me examples of what I need to do in an easy and straightforward way.

Thanks
Clive

Posted: **Wed May 25, 2011 7:10 pm**

a) If I have 50 input fields in the interface which needs to pair with entries into an sqlite database, do I need to write individual revExecuteSQL statements for each field? All fields have been individually named for easy identification already.

No, there's no need for that. Yet again, I can't give the antwort, because that's a question about "what is this 'programming' thing, and can it be used for anything?" and not "how do i use this language to achieve x". One approach is to use a repeat. look up repeat in the dictionary. A usage example could be:

Code: Select all

repeat for the number of fields --all on current card
add one to x
put myFakeFunction(the short name of field x) & return after myFakeResult
end repeat

b) For the update, create and save buttons, do I need to list all the fields for each one?

probably, unless you assemble the list automatically, in which case there'd be a list, but not made by you, but by the wonders of machination.

c) Is it possible to have a listfield which has 3 or 4 selected columns from the database to allow the user to identify the entry they want so they can click in the list field which then causes all the fields to update with the entries for that entry? So imagine the list field has John, Mark and Graham. When you click on Mark, all the text fields immediately update to show all the data for Mark, allowing you to add, update and save the data.

yes. for listfields i normally use the selectionChanged message. For example.

Code: Select all

on selectionChanged
  put the selectedText into myVariable
  answer myVariable
end selectionChanged

Sorry for not taking your questions super serious, but I still feel like being asked "how do i avoid learning stuff" instead of "how do I achieve my goals".

Posted: **Wed May 25, 2011 7:52 pm**

BvG, in order to say "how do I use this language to achieve X", wouldn't you say it's useful to have an example somewhere of how to do this so that I know what the language for it is? I have looked and found nothing that answers the point of data from multiple fields going to an sqlite database and visa versa. Lots of examples of other ways but none seem applicable and I do not want to waste time going the wrong way.
So since I was not asking for a "programming thing to do anything", I was hoping to find a helpful nudge in the right direction via a tutorial or example stack which would make sense to someone who is starting out at this and is faced with no tutorials and no online examples to help.

Posted: **Wed May 25, 2011 9:10 pm**

Ah, i see. I don't have any examples for you, but "multiple" always means repeat, somewhere you repeat over fields, and create the sqlQuery that does what you need. Then you do the inverse for going the other way. To learn how to do that, every sql-using tutorial is helpful. for info about repeat, the dictionary entry is all encompassing. The documentation pdf has information on both topics for you, it's in the help menu.

No tutorial will ever cover exactly what you want. You will need to try and test. If the result is weird, and bad code, you'll laugh about your silliness in a few years. But right now you'll be glad you got something working.

Posted: **Fri May 27, 2011 3:33 pm**

A bit of progress.
I managed to get my button to connect to the sqlite database which is fine. I now have a script on an 'update' button to write data to the database which is as follows:

Code: Select all

on mouseUp 
   global gConnectionID 
   if gConnectionID is not a number 
      then
answer error "Please connect to the database first."
exit to top 
end if

put "project_settings" into tTableName 
put "project_name, working_title, production_no, type" into tFields
put the text of fld "project_projectnameInput" into tProjectName 
put the text of fld "project_workingtitleInput" into tWorkingTitle
put the text of fld "project_prodnumInput" into tProdNo
put the text of fld "project_typeInput" into tProdType

put "INSERT INTO " & tTableName & " (" & tFields & ") VALUES (:1, :2, :3, :4)" into tSQL 

-- send the SQL to the database, filling in the placeholders with data from variables

revExecuteSQL gConnectionID, tSQL, "tProjectName", "tWorkingTitle", "tProdNo", "tProdType"

if the result is a number then 
put "Database Updated" into fld "status" 
else
   put "Error Updating Database" into fld "status"
   end if
end mouseUp

However, it seems to only update the first three fields (I think) and no more. In other words, the fourth entry for 'Type' is not being committed to the database as each time I reopen, it isn't there. The reason I say 'I think" data is being written to the database is that when I open the sqlite database to see whether anything is written to it, I don't see anything. Is this actually working? It says its connecting and writing without errors and indeed, something is being written to the sqlite database according to its folder 'date modified' time.

Posted: **Fri May 27, 2011 3:53 pm**

It looks like you are executing an insert instead of an update statement.

Your update statement should like this:

UPDATE table_name SET field_name1 = '" & tValue1& "', field_name2 = '" & tValue2 & "' WHERE theUniqueKey = '" & tUK & "'"

jharris

Posted: **Fri May 27, 2011 7:38 pm**

Thanks jharris but I keep getting an error on the line. I've adapted it to my script:

put "UPDATE tTableName SET project_name = '" & tProjectName & "', working_title = '" & tWorkingTitle & "' WHERE theUniqueKey = '" & tUK & "'" into tSQL

Is this right? I've tried in many different ways but get errors or nothing is going through. Is it the quotation marks or the 'put' command at the start? If I don't put 'put' in I get errors too.

Posted: **Fri May 27, 2011 8:39 pm**

CliveC wrote: put "UPDATE tTableName SET project_name = '" & tProjectName & "', working_title = '" & tWorkingTitle & "' WHERE theUniqueKey = ' " & tUK & " ' " into tSQL

Is this right? .

No, It cant' work.

Because you use a variable (tTableName) as a litteral inside tSQL ! Your table is not "tTableName" but rather "project_settings".

I remind you some basic rules :

-keep it simple (why do you try to put the table name in a variable ?)

-before to use the "variable" form (with :1, :2 etc) use instead the direct form.

-eventually, after the query is created, put it inside a text field so you'll be able to read it like it will be sent to the SQL engine. Easier to debug. Easier to spot any mistake or mis spelling in the query.

So regarding your query, try :

Code: Select all

put "UPDATE project_settings SET project_name = ' " & tProjectName & " ', working_title = ' " & tWorkingTitle & " ' WHERE theUniqueKey = ' " & tUK & " ' " into tSQL
revExecuteSQL gConnectionID, tSQL

Posted: **Sat May 28, 2011 3:35 am**

Hi CliveC,

Here is a bit of code for an update example that I copied out of one of my apps:

Code: Select all

            
           put "UPDATE clients SET " & \
                 "name = '" & tName & "', " & \
                 "address = '" & tAddress & "' " & \
                 "WHERE pk = '" & tId & "'" into tSql

          revExecuteSql tCon, tSql
          if the result is not a number then
               answer error the result
          else
              -- Do What Ever
          end if
          revCloseDatabase tCon

You should test the result of the revExecuteSql to see if there was an error. Many times the error message will give you a clue as to what went wrong.
As bangkok stated you can always put your sql statement into a text field or answer dialog to examine the statement. You will usually find your errors
that way a lot quicker than you will looking at your code. I do it all the time.

bangkok has some very good advice.

I would also suggest:
1. If you are new to SQL, read some of the many tutorials on the Internet. I still refer to them when needed.
2. Download a database management tool where you can execute SQL statements against your database for testing/learning SQL.
I use Sqlite Expert, but there are many out there.

bangkok's sql statement should work with the exception of:

Code: Select all

' WHERE theUniqueKey = ' " & tUK & " ' "

I think I added that portion and theUniqueKey is probably not a field in your table. If you don't provide a WHERE clause, your statement will update the entire table. (That may be what you wanted.)

Good Luck,
jharris

Posted: **Mon May 30, 2011 3:28 pm**

Thanks for all your help guys. Really appreciate it.
The update script now works and updates the database.
What really confuses me is that when trying to adapt this update script to now INSERT INTO for a 'save' button, doesn't work. I have consequently gone through so many permutations of INSERT INTO and VALUES to get it writing to the database and nothing works. Why can't you write?

Code: Select all

           put "INSERT INTO project_settings VALUES " & \
                 "project_name = '" & tProjName & "', " & \
                 "working_title = '" & tWorkingTitle & "' " & \
                 "WHERE project_id = '" & tProjID & "'" into tSql
          revExecuteSql tCon, tSql

The only explanation I can think of is the WHERE statement?

LiveCode Forums.

SQlite newbie question

SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question

Re: SQlite newbie question