How Can I Save Changes To SQLite Database?

[Knightlite]

Hi,

I am still having problems trying to achieve what I would consider should be a very simple task. I have looked at the documentation and articles on the web and I still cannot find a clear-cut way to simply save a change in a sqlite database using LiveCode. I'm sorry, I just don't get it.

I have a table field name called XX that is stored in a table called "Questions". The user navigates to a record in the table, which displays data from data field XX in a text box called lblX. A change is made in the text box lblX.

I just need to know how to save this data change back to the table.

When I use RealBasic the code is simple:

Code: Select all

  rs.Edit

  rs.field("XX").StringValue=trim(lblX.Text)

  rs.Update
  Fire2.Commit  // Fire2 is the name of the database - Fire2.sqlite

I am just trying to find out what the equivalent is in LiveCode.

The examples in the revLessons show how to add controls, connect to a database, add a table, insert new data, and to retrieve and display data, BUT nothing on updating current records in the database.

Any help with this would be greatly appreciated.

Jim

[Klaus]

Hi Knightlite,

Code: Select all

rs.Edit
rs.field("XX").StringValue=trim(lblX.Text)
rs.Update
Fire2.Commit  // Fire2 is the name of the database - Fire2.sqlite

well, like all the other db commands you also need to create the appropriate SQL command for this,
just like the other examples in the lessons you mentioned.

Like this:
...
UPDATE "table_name"
SET column1=value, column2=value2,...
WHERE some_column=some_value
...

Check this page, got me started with SQL, too:
http://www.w3schools.com/sql/default.asp

There is also a database example stack in LiveCode!
In LiveCode: Menu: Help: Example Stacks and Resources: Folder "Examples" -> SQLite Sampler.rev
There are examples of how to UPDATE records and more!


Best

Klaus

[Knightlite]

Hi Klaus,

Thank you very much for the information and the link. It was very helpful. But you have to understand...I am an idiot and have a hard time understanding things.

This is code I came up with based on the example, but it does not work. Can you see what I am doing wrong?

Code: Select all

  put "X" into Field "lblX"
      put fld "lblX" into lblX
      put fld "txtID" into txtID
      put "UPDATE Questions SET XX = '" & lblX  & "' WHERE _id='" & txtID & "'" into tSQLQuery
      

I appreciate you taking the time to help.

Jim

[Klaus]

Hi Jim,

given that:
lblX = a valid column name
txtID = a valid ID
and your database field ID is really named: _id

Then this should work:
UPDATE Questions SET Column_name = 'content_of_fld_lblx' WHERE _id='1'

Add an ANSWER to check your SQL string!
...
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
ANSWER tSQLQuery
...

I'm not sure if table and column names are case sensitive in SQLite, so please check this, too.


Best

Klaus

[Knightlite]

Hi Klaus,

Thank you so much again for trying to help me out. I am sorry I am being so dense about this. I'm still having problems getting this code to work.

given that:
lblX = a valid column name
txtID = a valid ID
and your database field ID is really named: _id

I am not using columns. The IU is a basically in a Form layout with the database fields connected to TextBoxes rather than columns.

lbLX is the name of the Textbox. It connects with the database field XX.
txtID displays the primary key of the database file which is called _id.

I am basically trying to put the contents of the Textbox lblX into the database field XX and save it.

I entered the code:

Code: Select all

UPDATE Questions SET Column_name = 'content_of_fld_lblX' WHERE _id='1'

It returns an error that says:

Compilation error at line 40 (Commands: missing ',') near "SET", char 14

Code: Select all

  put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
      ANSWER tSQLQuery

When I run the code above by itself it returns a message box that reads:

UPDATE Questions Set XX = 'X' WHERE _id = '12078'

This looks all correct. Thats what it should do, but in the end it does not actually save anything. When you refresh the database the record is empty. Sorry to be such a pain in the butt, but can you see anything that's might be the matter here?

Thanks again.

Jim

[Adrian]

If the SQL statement looks correct to you (it shows the correct table name, column names, etc.) then what does your script do next? Perhaps there is a fault in the next lines that actually perform the databse transaction?

Cheers,

Adrian

[Knightlite]

If the SQL statement looks correct to you (it shows the correct table name, column names, etc.) then what does your script do next? Perhaps there is a fault in the next lines that actually perform the databse transaction?

Cheers,

Adrian

Hi Adrian,

I think that is the point. What should come next to make the database actually update? As I said in my original post, in RealBasic the line would be:


rs.Update
Fire2.Commit // Fire2 is the name of the database - Fire2.sqlite


What is the equivenlent in LiveCode? That is what I am missing.

Jim

[Klaus]

Forget RealBasic, there is no equivalent to these commands in Livecode!

Stupid question: Did you ever EXECUTE tSQLQuery?

[Knightlite]

Forget RealBasic, there is no equivalent to these commands in Livecode!

Stupid question: Did you ever EXECUTE tSQLQuery?

Not a stupid question at all. I don't think that I am doing that correctly. Here is the code I have.

Code: Select all

      put "X" into Field "lblX"
      put fld "lblX" into lblX
      put fld "txtID" into txtID
      put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
      ANSWER tSQLQuery

    EXECUTE tSQLQuery //Tried it with this and ...
  revExecuteSQL sDatabaseID, tSQL  //Tired it with this.

It still won't update the record. I just can't believe it is so difficult to do a simple update of a record. After spending two days on trying to figure out how to write one line of code, it is getting frustrating.

Jim

[Klaus]

Hi Jim,

you obviously did not take a look at UPDATE example in the "SQLite Sampler.rev" stack,
where you can see a working example of the UPDATE syntax.

Hint: "revdb_execute" is a function!
...
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
put revdb_execute(gConID,tSQL) into tResult
## Now check tResult, it should be a number = the number of records that have been updated, so it should be 1 in your case.
...

Best

Klaus

[Knightlite]

Hi Klaus,

you obviously did not take a look at UPDATE example in the "SQLite Sampler.rev" stack,
where you can see a working example of the UPDATE syntax.

Well, I actually did check this sample out and I found it difficult to follow. I would also say, its not exactly a "working example" since the app crashes after you try to update a record.

For me, it is also difficult to learn from an example that uses a table, when my app is using a form layout. It is more difficult for me to follow. I guess I think an example should come down to the lowest common denominator. The simpler the example, the easier it is to learn. I am sure many people here think it is a great example, and I am sure it works for lots of people. It's just not working for me.

A good example would be a single textbox connected to a database field. Make a change in the text box, click an update button and the change is made. That is a good example ...simple and directly to the point.

Hint: "revdb_execute" is a function!
...
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
put revdb_execute(gConID,tSQL) into tResult
## Now check tResult, it should be a number = the number of records that have been updated, so it should be 1 in your case.
...

If I am suppose to copy the code above and put it into the app, that does not work either. Nothing is saved. I don't get it. What is "gConID"? How am I suppose to "Check tResult"??? I am sorry to be so stupid, but I just don't get it, and I don't see why it is so difficult.

You say I should forget about RealBasic. Maybe so, but it is 2 simple lines of code to save data there. In Visual Basic it is 1 line of code. So I don't know what the super secret code is for updating databases in LiveCode, when every single line of code I have seen in every single example does not work for me. Sorry I still don't get it.

I do however appreciate your help in making me try to understand this.

Jim

[Klaus]

Hi Jim,

Hi Klaus,

you obviously did not take a look at UPDATE example in the "SQLite Sampler.rev" stack,
where you can see a working example of the UPDATE syntax.

Well, I actually did check this sample out and I found it difficult to follow. I would also say, its not exactly a "working example" since the app crashes after you try to update a record.

Sorry, if the stack does not work for you, but I meant the SYNTAX that you should look at.

For me, it is also difficult to learn from an example that uses a table, when my app is using a form layout. It is more difficult for me to follow.

These are just WORDS!
A table is a part of a database file where you save our data in, so you ARE in fact dealing with a table, no matter how you call your layout!
And the entries in a table are called columns of fields

Example:

Code: Select all

TABLE: addresses
  Columns/fields:
     id
     firstname
     name
     city
     zipcode
     etc...

A good example would be a single textbox connected to a database field. Make a change in the text box, click an update button and the change is made. That is a good example ...simple and directly to the point.

Well create this example and we will create the UDPATE part together!

Hint: "revdb_execute" is a function!
...
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
put revdb_execute(gConID,tSQL) into tResult
## Now check tResult, it should be a number = the number of records that have been updated, so it should be 1 in your case.
...

If I am supposed to copy the code above and put it into the app, that does not work either.

Come on Jim, you are of course NOT supposed to copy and paste this little snippet into your app and hope it will work!?
You should look at the snytax and try to understand what's going on and modify it to your needs (e.g. enter YOUR connection ID and variables names!

What is "gConID"?

When you connect to a database (with "revopendatabase(...)" you get a connction ID and you need to provide this ID
with every database command so LiveCode knows what database you are working with, even if you only work with ONE database.
In my example "gConID" IS this ID.

How am I suppose to "Check tResult"???

What about:

Code: Select all

...
if tResult is not a number then
  answer "an error has occured"
  exit HANDLERNAME
end if
...

You say I should forget about RealBasic. Maybe so, but it is 2 simple lines of code to save data there. In Visual Basic it is 1 line of code. So I don't know what the super secret code is for updating databases in LiveCode, when every single line of code I have seen in every single example does not work for me. Sorry I still don't get it.

Well obviously XBasic supplies a build-in database functionality with non-verbise syntax, which makes it easy to deal with databases.
But Livecode does not, it is more "do it yourself" in this respect, so please get used to it


Best

Klaus

[phaworth]

You're putting the SQL into a variable named tSQLQuery but the variable you name in the revdb_execute is tSQL, different (and empty!) variable. Thus no SQL command is executed.
Pete

[Klaus]

Hi Pete,

You're putting the SQL into a variable named tSQLQuery but the variable you name in the revdb_execute is tSQL, different (and empty!) variable. Thus no SQL command is executed.
Pete

yep, that was my fault, a quick and dirty copy/past error
Should of course read:
...
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
put revdb_execute(gConID,tSQLQuery) into tResult
...


Best

Klaus

[Knightlite]

Hello,

I am still trying to get how I update a record in a SQLite database, and I am getting nowhere. When I try to update a record, I get an "invalid connection ID error.

I open the card with this code:

Code: Select all

local sDatabaseID, sRecordSetID

I use this code to open the database and save the connection ID:

Code: Select all

 ## Connect to the database
   ## Save the connection id
   put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
   
   clearStuff

   
   if the environment is "mobile" then
            put specialFolderPath("engine") & "/Fire2.sqlite" into tDatabasePath
            put specialFolderPath("documents") & "/Fire2.sqlite" into dataBaseFilePath
                  if there is not a file documentFilePath then
                           put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
                           put URL ("binfile:" & engineFilePath) into URL ("binfile:" & dataBaseFilePath)
                  end if
      else
            put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
      end if
   

   
   put revOpenDatabase("sqlite",tDatabasePath,,,,) into sDatabaseID

I create a query using this code and thus create a recordset:

Code: Select all

put "SELECT * FROM Questions WHERE Chapter='" & txtK & "'" into tSQLQuery

put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   

I then make a change in one of the fields and attempt to save the data change:

Code: Select all

 put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
      ANSWER tSQLQuery

I then try to finalize the change. I have tried these 2 lines of code below … Separately

Code: Select all

put revdb_execute(sDatabaseID,tSQLQuery) into tResult 

put revdb_execute(sRecordSetID,tSQLQuery) into tResult 

This is where I get an "invalid connection ID error.

I have looked at the famous SQLite Sampler and when you try to do anything with the "Update" section, it crashes.

There is nothing I can find on the Internet nor in any Live Code documentation that describes how to update a simple SQL record, nor an example project that actually works.

If anyone can help me with this, I would greatly appreciate it. I have been working on this simple but seemly impossible task for a long time and not getting anywhere.

Sincerely,

Jim