How Can I Save Changes To SQLite Database?

[Klaus]

Hi Jim,

is this script snippet:

I then make a change in one of the fields and attempt to save the data change:
put "UPDATE Questions SET XX = '" & lblX & "' WHERE _id='" & txtID & "'" into tSQLQuery
ANSWER tSQLQuery
put revdb_execute(sDatabaseID,tSQLQuery) into tResult

somewhere in the card script?
If not, then "sDatabaseID" is not known at this place, unles you declare it GLOBAL or store it in a custom property!


Best

Klaus

[sturgis]

Seems that there are issues with some of your code, mainly confusion as to which variable means what, plus some redundant code. As it currently sits you are attempting to open tDatabasePath which on mobile will end up pointing to the file in the engine rather than in documents and this won't work. You can't write to the engine folder.


Here is a simplified and commented version of your script.

Code: Select all

 ## Connect to the database
   ## Save the connection id
 
   clearStuff
### There was no need for the first line setting the database path for use in the desktop, its covered in the if/else
### so I removed it.

### Other extraneous lines were removed, the enginefilepath only needs to be set once with a single variable name
### Same for tDatabasePath. Since you use tDatabasePath for your revopendatabase it must contain the path to
### a writable sqlite file  Other than the connect id these should be the only vars needed here
   
   if the environment is "mobile" then

### if its mobile then set up the engine path to your template file
### and the database path to where the actual working db file will be.
            put specialFolderPath("engine") & "/Fire2.sqlite" into engineFilePath
            put specialFolderPath("documents") & "/Fire2.sqlite" into tDatabasePath

### Since tDatabasePath points to where it should be it can be used directly for the file check
                  if there is not a file tDatabasePath then
### This should work dandy. Watch out though to make sure you don't have
### a file already there that is invalid due to previous programming tests. 
### if the file exists (because it was opened, opening will create the file)
### then you could have a database file there with no tables. Not sure
### if you're testing on a live device, or simulator that you set to refresh each time or what
### so this might not apply.
                           put URL ("binfile:" & engineFilePath) into URL ("binfile:" & tDatabasePath)
                  end if
      else
### this correctly handles testing on the desktop, also using tDatabasePath since this is the var used
### with the revopendatabase
            put specialFolderPath("desktop") & "/Fire1/Fire2.sqlite" into tDatabasePath
      end if
   

### If you use "get" instead of just putting the database id directly into your variable it makes it easier to 
### check for errors. the special variable "it" will either contain an integer or an error message.
   get revOpenDatabase("sqlite",tDatabasePath,,,,)

### so if it is an integer, put it into sDatabaseId (which should be declared as a script local variable at the top of the script
### otherwise sDatabaseId will be transient and you won't be able to interact with the database.
	if it is an integer then
		put it into sDatabaseId
	else
### If "it" wasn't an integer that means there was an error, so display it. 
		answer information "There was an error: " & it
	end if

Since it appears that your initial query works (does it? You can actually cycle through the data rows and see whats there?) if you ARE able to select, then the most likely issue is that you can't update due to the fact you were pointing towards the engine sqlite file rather than the documents sqlite file when you did the revopen. If you can't actually retrieve data, same thing, but possibly the database is never validly opened the first time. (if you aren't getting an error with the revquerydatabase though this part seems unlikely) Well with the exception you don't check the variable "it" or the other special variable "the result" so its hard to tell if there was a problem with your revquerydatabase call.


Also, as far as the sqlite sampler stack, last time I went through that there were some typos and inconsistencies. Even to the point where the documentation code didn't match the underlying code, and vice versa. There were a few things that just didn't work, but that aren't too difficult to ferret out. If you want, I can grab the stack again, fix it and post a link to the working stack here so that you can look it over.

[Knightlite]

Dear Klaus and sturgis,

Thank you so much for trying to help me. I have spent a week trying to figure out things that you guys posted and I'm still having problems.

Klaus, you said the following:

somewhere in the card script?
If not, then "sDatabaseID" is not known at this place, unless you declare it GLOBAL or store it in a custom property!

The code is in the card script, but it still does not work.

and sturgis, thanks so much for your detailed explanation. I appreciate you taking so much time to write such a commented explanation.

The whole thing is still not clicking, and I was thinking about something Klaus said earlier in this thread challenging me to write a simple example app and that he would help code it. The existing example app does not seem to be working correctly for some reason, and I thought if I created a super-simple database example app, that other novices could learn from it as well.

So I gave it a try. It is simple sqlite database stack that contains an names and address database. The user can navigate between names with navigational buttons, and create new names, update names and delete names.

The project can be found at:

http://www.redway.net/livecode

The navigation buttons DO work on the IOS simulator, and in the IDE. The New, Update, and Delete buttons do not work, because the code is not there.

I think if we can just get this working, it would greatly help me, as well as new users to Livecode.

I would appreciate if you take a look at it.

Thanks again.

Jim

[mwieder]

Just had a quick look at this. Several problems:

Your "updateRecord" command in the card script will never execute. You're using variables that don't exist: txtName, txtAddress, txtEmail.
I think here you mean field "txtName", field "txtAddress", field "txtEmail".
You're also overwriting your variable tSQLQuery each time you put something into it, so only the last one (txtEmail) will be affected.

I don't understand what you're trying to do with your sql update commands. An update of name x where name=x doesn't make sense, etc.

You have two "end preOpenCard" statements.
I'm not sure what the compiler will do here, but my guess is that everything after the first one will just be orphaned and never executed.

Edit:

And lastly, how in the world did you get the text formatted so weirdly?
If you're not aware of it, pressing the tab key will format the current handler "properly", meaning in a way that makes syntactic sense to the compiler.

[Knightlite]

Hi mwleder,

Thank you for your message.

What I am trying to do is to write some code that would allow the user to make a change in one of those 3 fields and then when the user clicks the Update button, the changes are saved to the database.

I just don't know what the code is for doing that, and this example is all my unsuccessful attempts to simplify it and make it happen.

This whole thread was posted to try to understand what the code is necessary for updating a record in a sqlite table.

Perhaps the code should be something like this??????

Code: Select all

  put "UPDATE Info SET Name WHERE Name='" &  Field "txtName" & "'" into tSQLQuery

Sincerely,

Jim

[mwieder]

If you've already got the database record you're interested in (from a previous query) then you should have the recordset id. In that case you'd want something more like

Code: Select all

  put "UPDATE Info SET Name =" && field "txtName" && "WHERE id='" &  tSavedID & "'" into tSQLQuery

or you could try

Code: Select all

  put "UPDATE Info SET Email =" & field "txtEmail" && "WHERE Name='" & field "txtName" & "'" into tSQLQuery

That any help?

[Adrian]

First, let's presume you are only allowing updates here (not additions). If so, your updateRecord handler isn't a million miles off, but there's a few things to sort out.

1. Can the user change the name (the content of your txtName field)? If not, then this field should be locked, but if they can, then you need to use the "id" in the SQL Where clause (I would also presume that the field txtPrimaryKey is hidden and locked).

2. Whenever you put something into something, you replace the contents. I think part of the problem with the first part of updateRecord is that after the first line you should use put something after something.

So perhaps you need something like this:

Put "UPDATE info SET name='" & the text of field "txtName" & "',address='" & the text of field "txtAddress" into tSQLQuery
Put "',email='" & the text of field "txtEmail" & "' WHERE id='" & the text of field txtPrimaryKey & "'" after tSQLQuery

3. I can't see where you are setting sDatabaseID, but perhaps that's because I'm currently not at a computer with LiveCde, so I can't check it out properly. Are your databse calls working at all? Have you put in some basic checks, such as using "answer" in apporpriate places?

Hope this helps.

Cheers,

Adrian

[Knightlite]

Hi mwleder and Adrian,

Thank you again for your help. I really appreciate it.

I think Adrian has the solution here, yet it still does not save. I think tho code will work, but I still think the final execute line may not be right.

Code: Select all

Put "UPDATE info SET name='" & the text of field "txtName" & "',address='" & the text of field "txtAddress" into tSQLQuery
Put "',email='" & the text of field "txtEmail" & "' WHERE id='" & the text of field txtPrimaryKey & "'" after tSQLQuery

Here is all the code for the card:

Code: Select all

local sDatabaseID, sRecordSetID
 on preopencard
   local tDatabasePath, tSQLQuery
   
### There was no need for the first line setting the database path for use in the desktop, its covered in the if/else
### so I removed it.

### Other extraneous lines were removed, the enginefilepath only needs to be set once with a single variable name
### Same for tDatabasePath. Since you use tDatabasePath for your revopendatabase it must contain the path to
### a writable sqlite file  Other than the connect id these should be the only vars needed here
   
   if the environment is "mobile" then

### if its mobile then set up the engine path to your template file
### and the database path to where the actual working db file will be.  There is a copy of the Names.sqlite in the documents folder
            put specialFolderPath("engine") & "/Names.sqlite" into engineFilePath
            put specialFolderPath("documents") & "Names.sqlite" into tDatabasePath

### Since tDatabasePath points to where it should be it can be used directly for the file check
                  if there is not a file tDatabasePath then
### This should work dandy. Watch out though to make sure you don't have
### a file already there that is invalid due to previous programming tests. 
### if the file exists (because it was opened, opening will create the file)
### then you could have a database file there with no tables. Not sure
### if you're testing on a live device, or simulator that you set to refresh each time or what
### so this might not apply.
                           put URL ("binfile:" & engineFilePath) into URL ("binfile:" & tDatabasePath)
                  end if
      else
### this correctly handles testing on the desktop, also using tDatabasePath since this is the var used
### with the revopendatabase
            put specialFolderPath("desktop") & "/Names.sqlite" into tDatabasePath
      end if
   

### If you use "get" instead of just putting the database id directly into your variable it makes it easier to 
### check for errors. the special variable "it" will either contain an integer or an error message.
   get revOpenDatabase("sqlite",tDatabasePath,,,,)

### so if it is an integer, put it into sDatabaseId (which should be declared as a script local variable at the top of the script
### otherwise sDatabaseId will be transient and you won't be able to interact with the database.
   if it is an integer then
      put it into sDatabaseId
   else
### If "it" wasn't an integer that means there was an error, so display it. 
      answer information "There was an error: " & it
   end if
   
   //get revOpenDatabase("sqlite",tDatabasePath,,,,)
   
   ## Query all details
   ## Save the recordset id
   put "SELECT * from Info" into tSQLQuery
  put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   

   ## Query all details
   ## Put the result into the field
   put "SELECT * from Info" into tSQLQuery

put revQueryDatabase(sDatabaseID,tSQLQuery) into sRecordSetID   

//Counts the number of records in the recordset
put revNumberOfRecords(sRecordSetID)  into field "txtCount" 
   
      ## Display the first record
      revMoveToFirstRecord sRecordSetID
   displayRecord
end preopencard
   
   on showFirstRecord
   revMoveToFirstRecord sRecordSetID
    displayRecord
end showFirstRecord

on showLastRecord
   revMoveToLastRecord sRecordSetID
   displayRecord
end showLastRecord


on showNextRecord
   ## Move to the next record in the record set id and show it
   revMoveToNextRecord sRecordSetID
   displayRecord
end showNextRecord

on showPreviousRecord
   ## Move to the previous record in the record set id and show it
   revMoveToPreviousRecord sRecordSetID
   displayRecord
end showPreviousRecord


on updateRecord
 
     Put "UPDATE info SET name='" & the text of field "txtName" & "',address='" & the text of field "txtAddress" into tSQLQuery
Put "',email='" & the text of field "txtEmail" & "' WHERE id='" & the text of field txtPrimaryKey & "'" after tSQLQuery
  
      ANSWER tSQLQuery

put revdb_execute(sDatabaseID,tSQLQuery) into tResult 

if tResult is not a number then
  answer "an error has occured"

end if
end upDateRecord

   
   
on displayRecord
   
   put revDatabaseColumnNamed(sRecordSetID,"id") into field "txtPrimaryKey"
   put revDatabaseColumnNamed(sRecordSetID,"Name") into field "txtName"
   put revDatabaseColumnNamed(sRecordSetID,"Address") into field "txtAddress"
      put revDatabaseColumnNamed(sRecordSetID,"Email") into field "txtEmail"
   
end displayRecord

The following line may not be correct, so that is why it does not make the save.

Code: Select all

put revdb_execute(sDatabaseID,tSQLQuery) into tResult 

I think the solution is almost there, but not quite.

Jim

[Knightlite]

This is actually the line I was looking for:

Code: Select all

     Put "UPDATE info SET name='" & the text of field "txtName" & "',address='" & the text of field "txtAddress" into tSQLQuery
   Put "',email='" & the text of field "txtEmail" & "' WHERE id='" & the text of field txtPrimaryKey & "'" after tSQLQuery
   
// This line makes it save
   revExecuteSQL sDatabaseID, tSQLQuerry

I just have to figure out how to bookmark the record, because you can't see the change in the recordset until you reload the recordset. Then you need to navigate back to the last edited record so it looks lie the update was instant.

Jim

[mwieder]

// This line makes it save
revExecuteSQL sDatabaseID, tSQLQuerry

No unless you spell "tSQLQuery" correctly.

Otherwise your sql statement looks to be correct. I assume when you look at it in the answer dialog it looks like what you expect.

[Knightlite]

Hi mwleder,

Oops, yes I did get spell the tSQLQuery right. Typing too fast.

Can I just ask you one more question? ..... for now at least.

In that example lets say I make a change in one of the fields like the first name, how can I make the recordset display the change without having to load the entire recordset again?

In other words, I make a change from Jim to Scott and click the update button. The update is saved. But when I click a button to move to the next record, and click the "Back" button to move back one record, the change does not show up. The old record is displayed. I have to reload the recordset from the start to see the change, and when I do that, it automatically shows me the first record, and not the one I was editing.

Is there an easy way to automatically navigate to the last record edited so it looks seamless?

Again I appreciate all the help I am receiving on this forum and I really think this example app could help other people grasp using databases.

Jim

[Adrian]

I've no idea if this is the best way, but one way is not to use a record set (cursor). Instead, put the result of the query in an appropriate variable, such as an array and use that as the source for the next/back.

When the user updates a record, update the variable contents after successfully updating the database.

Cheers,

Adrian

[marksmithhfx]

Hi mwleder,

Oops, yes I did get spell the tSQLQuery right. Typing too fast.

Can I just ask you one more question? ..... for now at least.

In that example lets say I make a change in one of the fields like the first name, how can I make the recordset display the change without having to load the entire recordset again?

In other words, I make a change from Jim to Scott and click the update button. The update is saved. But when I click a button to move to the next record, and click the "Back" button to move back one record, the change does not show up. The old record is displayed. I have to reload the recordset from the start to see the change, and when I do that, it automatically shows me the first record, and not the one I was editing.

Is there an easy way to automatically navigate to the last record edited so it looks seamless?

Again I appreciate all the help I am receiving on this forum and I really think this example app could help other people grasp using databases.

Jim

Hi Jim, I edited your example database. I don't know if this is helpful or not. It should do most of what you are looking for. I used some existing code I had created for another database so there are some assumptions. The two primary display and "update" routines are called displayRecord and UpdateRecord. Both of these routines loop through all of the fields on your card and read or write them to fields in the database that have the same name ie. if you have a database field called "address" then the field on the card has to be called "address" as well. If you don't want a field saved to the database (like your txtcount field) then put a custom property in the field called cStoreMe and sets its value to "N"... see the custom property for the txtcount field for an example.

Also, I did not implement the "delete" function like you might expect. Its complicated, but I put comments in the Delete button and in the deleteRecord function to explain why, and if you need additional functionality you'll need to think of a way to get around SQL's problems with having missing primary keys in the database. Actually it is not so much SQL's problem, as it is my assumptions in writing the code.

Anyway, this should give you a start. BTW, if you want to work with your existing NAMES database, put it into your DOCUMENTS folder before running the program. Otherwise, a new one will be created in DOCUMENTS which is empty. I have not tested this on iOS but I am assuming it should run as is.

Have fun.

BTW, nice job on the buttons. How come mine never look that good?

[Knightlite]

Hi Adrian,

Thank you for the tip. I just don't know how to do that. I am very new to Livecode and as my posts show, I am struggling with just about everything.

I will try to look up what you are talking about it.

Thanks.

Jim

[Knightlite]

Hi marksmithhfx,

Thanks so much for taking the time to code that project. I really appreciate the time you put into it.

Its really different than what I was doing, so it looks like I was doing everything wrong. It will take me a few days to figure out what you did, but I really appreciate the help.

Thanks very much again. Livecode is not as easy as I thought.

Jim