data grids and sqlite

[KennyR]

Can someone point me in the right direction for documentation on populating a data grid with entries made in a sqlite database? The Lessons section covers some of this, but I cannot seem to figure out how to insert saved data from my database and display it allowing the user to delete data as they wish. Most of what I have read in the lessons section explains how to manually create arrays and rows of content, but maybe I am missing something here...BTW, I am a rookie at this and attempting to learn about sqlite, so please understand that....

[townsend]

I think this sample code will help you get started:

[jalz]

Hi townsend,

Sorry dont want to hijack this thread, but I might be having the same problem as OP. Your CRUD file is an awesome stack by the way, Ive learned so much from it. How did you learn/get started with all the stuff in here. I've been disecting your example and understand most of it myself now, which is great and a real timesaver, however the one I am stuck on is populating the datagrid property.

I seem to follow your populate function, and its great if my dg column names are the same as the ones in my table however what I cant figure out is I've got a table names which dont tie up to my column names so will be trying to work out how to do this later on this evening

[townsend]

How did you learn/get started with all the stuff in here.

You may want to review the original thread, here.
You can see the step by step, how the CRUD example evolved, with all the help I received.

however what I cant figure out is I've got a table names which dont tie up to my column names so will be trying to work out how to do this later on this evening

That was a big issue for me as well. Since these routines were going to be my model. Where I'd copy and paste them into all my other applications. I didn't want the column names to be hard coded either. Look over the first revQueryDatabase call in the doRead handler, located in the Stack code area. You'll see how the field names are extracted from the DB and then used as the column headings.

Code: Select all

on doRead ----------------------------------------------------------
     if not Connected() then exit to top
     put revQueryDatabase( conID, "SELECT * FROM control") into theCursor
     put the result into temp  --for debugging
     if not (the result >0) then
          answer "ERROR=" & the result
          exit to top
     end if
     put theCursor  into field rCounter -- displays the number of lines
     put revDatabaseColumnNames(theCursor) into theFields -- get field names from table
     put the result into temp -- for debuging
     if the " revdberr" is in the result then
          return "ERROR=" & the result
     end if
     replace "," with CR in theFields
     set the dgProp["columns"] of group myGrid to theFields
     put "SELECT * FROM control" into tSQL
     put revdb_querylist(,,conID,tSQL) into ptext
     -- put revQueryDatabase(conID,tSQL) into ptext --doesn't work
     put the result into temp -- for debuging
     if "revdberr" is in the result then
          return "ERROR=" & the result
     end if
     put the number of lines in pText into field rCounter -- displays the # of lines
     set the dgText of group myGrid to ptext
end doRead

PS: One thing I never did get around to.
The column widths are still hard coded in the, doSetColumnWidth handler.

[jalz]

Thanks townsend, the thread is an interesting read. Especially the line which puts the result into temp. I was wondering how that was working, as you don't put contents of tCursor into results - I'm assuming from your explanation in the other thread that whenever you run a command on the db whether its a select or whatever else, "0" gets populated into the results variable if its successful or you get a string with an error message populated in the results variable.

Its all a great learning experience - wish I was a sponge and absorb this stuff in more quicker

[townsend]

whenever you run a command on the db whether its a select or whatever else, "0" gets populated into the results variable if its successful or you get a string with an error message populated in the results variable

Actually NOT. The return value varies, depending on which DB function you use.
The best thing to do, is ALWAYS check the Dictionary before using a new DB function.

For instance, this is from the Dictionary:

The revExecuteSQL command places a return value into the result, to indicate the outcome of the query.

For successful queries, the revExecuteSQL command returns the number of rows affected for INSERT, UPDATE and DELETE statements. For all other statements, 0 is returned.

For unsuccessful queries, an error string is returned, describing the problem.

[jalz]

Thanks again Townsend.

Ive found another resource which also looks quite useful. I'll be trying to disect this as well before I build my own one - if the OP is interested its found here http://www.quartam.com/ under the Resources/Downloads section. There is a link called desktop databases which looks good, but the code is much more difficult to understand as well as find (he's used multiple cards in his stacks and Im still finding it difficult to find which cards contain which scripts)

[YellowStone]

Wow, thanks townsend for this great example! Nice work

Just a question about LiveCode itself. Your example stack works great, but when I save it as a Mac standalone application, the button which activates the doRead handler doesn't do anything, so I can't populate the datagrid. Is this possible or will it only work as a stack in the LiveCode engine?

[townsend]

Before you save as an executable, it make sure to press the button Clear Datagrid,
otherwise that screen data will be compiled into your executable.

Keep in mind, if the executable is in a different folder than the the
.livecode source code, then a new db will be created.

Or, where ever it is run, just go through the process of starting a new db.

• Connect
  Delete SQLite Table
  Create SQLite Table
  Populate Table
  Read into DataGrid
  Set Column Widths

Also make sure you have the most current version.

CRUD_SQLite_example_v4.4.zip

(11.81 KiB) Downloaded 463 times

[YellowStone]

Right on, works like a charm!
I see that I had downloaded an old copy (3.2).

This helps so much -- really appreciate this work you did.

[phaworth]

Hi,
Sounds like the folks on the forum have given you lots of great sources for how to do this.



I hope it's OK to shamelessly plug a couple of my products.

Take a look at my web site www.lcSQL.com and read the page on LiquidSQL. It's a product I'm working on which should go into beta in about a month. It will take care of just about everything you're trying to do, and more, without you needing to write s single line of code. Amongst other things, it will also automatically generate a stack containing a datagrid and various controls that will provide you with all the CRUD functionality for one or more tables.

If you're looking for an sqlite database admin tool, download a demo of mySQLiteAdmin program and see if it helps.

End of self promotion!

Pete