How Can I Save Changes To SQLite Database?

[marksmithhfx]

Hi marksmithhfx,
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.
Jim

Hi Jim,

I would not say that what you were doing is wrong. THe update and display routines are very different from what you were doing because... I am not giving you code to do it. I am giving you code that writes the code to do it. That is a very different approach. Background: I learned very quickly that I hated writing code to write LC fields to database fields. Its messy, it is full of weird complexity, and it is prone to error. I was spending more time debugging the stuff I was writing than writing it. I confess, it is one of my weaknesses (ask anyone here!): I am terrible at syntax. So what I did is write some simple little 16 line routines that will write out all the LC/SQL code I need to read and write fields to/from databases. Basically the "update" routine is my "write2db" code, and the "display" routine is my "read_from_db" routine. I actually take a slightly different tack in the read-from code and put the record into a recordset just so I can take advantage of reading the values out of the record set by field name (hence the requirement of correspondence between field names on the card and field names in the database). If you would like to see how the write routine is writing the sql code there is a line you can uncomment that will put the sql out to the msg box:

-- put return & tCmd after msg -- uncomment this line if you want to see what tCmd looks like

Now, this is just one approach. Basically I've done it this way to avoid having to hand code, as much as possible, any SQL. THere are many other perfectly legitimate approaches to doing this. However, even if this does not become your preferred approach you'll probably pick up a trick or two by reading and understanding this approach. Also, the approach I took is not perfect... I had trouble seeing how I could delete records and still satisfy your need to move backwards and forwards in the file. I would need to rethink how to do that in order to properly delete records. So, ideas offered by others here might provide a better solution. At the very least, it should run reliably and should help get you started. You can tinker with it and modify it from its current form and refine it until you get it working the way you want.

If I can answer any questions, don't hesitate to ask.

-- Mark

PS I don't know if RR archives the RevUP newsletters but if you can get your hands on issues 131, 132 and 133 I wrote some articles explaining the approach and providing sample code. The first article (131) describes in graphic detail some of the nightmares I was facing that led me to create this approach. Like I said, it is probably not for everyone but the articles might help to explain it a bit more.

[sturgis]

http://www.runrev.com/developers/resources/newsletters/ for revup stuff

[marksmithhfx]

Hi Jim, something else I forgot to mention is that I use Andre Garzia's DBLib a lot these days (http://www.andregarzia.com/page/dblib). Its really solid and really simplifies reading and writing to SQL databases. Its definitely worth checking out. He's got a nice little vimeo video on his site that walks you through using dbLib. I think it would make an excellent project to convert your test stack from its current working condition to use DBLib routines.

Just a thought.

-- Mark

PS thanks Sturgis (just saw your post)

[Knightlite]

Hi Mark,

Thank you so much for your message. I will check out your newsletters, and again, I appreciate your help.

I actually have Andre's DBLib and I am trying to figure that out as well.

There are two problems I am having with his DBLib and I know its all me. I am sure there is a way of doing it.

1) I was messing around with his example stack, and it does not write to the iPad device. I can get it to work in the simulator, but not save information on the device itself. I get the error message :

"attempt to write a readonly database"

I guess I am still having problems knowing where to put the sqlite file. Should it be in documents, the desktop, the project folder, and which one gets sent to the actual device. I also try adding the file under Standalone Application Settings (Copy Files), but I don't know which sqlite file should be added. I have added the aagDBLib.livecode in that Copy Files Section also.

2) The second problem I am having is that in his example app, he writes all the data to a grid. I see how that works, but I don't know how to display data to individual textboxes like that Test example I created. So when you load the database the first record appears in the text boxes like the Test app.

The thing is, I created this little test app so I can see the big picture in a simple database example, which is turning out to be not that simple … at least for me. My actual project is a "multiple choice test" app. I create study software that helps Firefighters, EMTs and Paramedics study for their certification exams. I am more of firefighter than a programer I guess, but the software helps make better firefighters, and that help save more lives, which is very important to me. I have a history with VB and RealStudio, but I am new to LiveCode. That may explain why I can't grasp this as fast as others who read and write to this great forum.

I think Andre's DBLib is the way to go too. I'm just not sure how to do the above tasks.

Again thank you for your help. I deeply appreciate it.

Jim

[sturgis]

This is untested but should be close..

To get andres sample stack working on mobile, first in the standalone settings for the sample stack, go to the "copy files" pane and add the contacts.sqlite file so that it is packaged up when you build for your mobile device.

Then, open both the sample stack and andres dblib stack and set the mainstack of the aagdblib stack to the name of the sample stack. This will make the aagdblib stack become a substack of the sampler stack.

Save the demo stack.

Change the stack script of the demo stack to

Code: Select all

on preOpenStack

-- use switch with "the environment" property to determine if the app is running on mobile or in development
   switch the environment

-- if its development, do pretty much what andre was already doing
      case "development"
         -- set default folder
         set the itemdel to "/"
         set the defaultfolder to item 1 to -2 of the effective filename of this stack
         put "contacts.sqlite" into tDatabase -- using a variable to store the path to the database file rather than using the path directly in revopendatabase
         break
      case "mobile"
   -- if its mobile, point to where the database should be.
   -- the documents folder is readable and writable.
   -- the engine folder path is only readable.
   -- when the file was included using the "copy files" pane
   -- of the standalone settings, it will end up in the engine folder, next to the executable
   -- but since the engine folder isn't writable you have to copy it to the right place if its not already there.
         put specialfolderpath("documents") & "/contacts.sqlite" into tDatabase 

  -- see if the database is already in the documents folder
  -- if not, copy it there from the engine folder
         if there is not a file tDatabase then
            put URL ("binfile:" & specialfolderpath("engine") & "/contacts.sqlite") into URL ("binfile:" & tDatabase)
         end if
         break
   end switch

-- since aagDBLib should now be a substack you don't hvae to specify a 
-- path to the file. Just the stack name.
   start using stack "aagDBLib"

-- open the database using the variable tDatabase to point to the file
-- if its on mobile the tdatabase will point to the file in specialfolderpath(documents) 
-- otherwise it will point to a file sitting next to the demo stack.
   get revOpenDatabase("sqlite",tDatabase,,,)

-- catch any errors as andre already did. 
   if it is a number then
      dbSetDefaultConnectionID it
   else
      answer error it
   end if 
end preOpenStack

Again, this is untested but it should be pretty close to what you need to get the test stack working on mobile.

One thing to watch for is that during testing an empty contacts.sqlite file might have been accidently created. If you have trouble, you can get rid of the "if" statement that checks if the file exists, and overwrite with the copy of the file in the engine folder.

[marksmithhfx]

Hi Mark,
me. My actual project is a "multiple choice test" app. I create study software that helps Firefighters, EMTs and Paramedics study for their certification exams. I Jim

Hi Jim, there is quite a bit of LC material on the Brigham Young University site: http://revolution.byu.edu/indexgeneric.php
including some material that focuses on building quizzes and flash cards:

Creating Instructional Applications: Example Projects
Labeled Picture Task
Annotated Readings
Flash Card Stack
Audio Dialog Stack
Creating Quizzes 1: Multiple Choice
Creating Quizzes 2: Short Answer
Creating Quizzes 3: Drag and Drop Matching

I have not worked through the material myself but Devin Asay who created the material is well known in HC circles and the course has had 1,000's of students pass through it over the years.

Despite what many say Livecode has a pretty steep learning curve, depending on your background. I came from a procedural fortran/basic/pascal/dbase/c type background and had a horrible time making the transition. Its easier now but the language is very large... perhaps thousands of functions and commands so it can be difficult and take quite a bit of time to grasp it all. Play with the stack I sent you and some of the stuff Devin has online and see if that doesn't help you get over some of your hurdles. If I have some time this weekend I may try and flesh out a "shell" multiple choice application that reads the questions and answers from a database. Do you need to store individual responses as well? or just a name and final score?

-- Mark

[Knightlite]

Hi Mark,

Thank you for your message. I actually have the multiple choice testing GUI functioning the way I want it. Almost everything works except for:

Being able to save data in the iPad simulator and on the actual iPad device.

I would also like to be able to use Andre's DBLib to update the database, add new records and delete records, and to do some sql queries.

Is there anyway I can contact you directly through email so I can give you more details? There seems no way to send a private message in this forum.

Although my business is one step up from non-profit, I would not mind paying you to help me get the few code elements missing from my gui working. I would appreciate if you contact me at cherry@redway.com if you wouldn't mine.

In the meantime I will work on the information sturgis sent me and the other information you provided.

Thanks again.

Jim

[marksmithhfx]

Hi Mark,
Is there anyway I can contact you directly through email so I can give you more details? There seems no way to send a private message in this forum.
Jim

I'll send you a private email. -- Mark