Page 1 of 1

Database to Data Grid and back again - best methods

Posted: Wed Aug 28, 2013 5:35 pm
by Quentin Brown
I am slowly, ever so slowly, wading my way through to an understanding of how I might manage to get my information in from my database and into a Data Grid for display and editing then back into the Database. It seems to involve a lot of stages of data translation but also there seems to be another method using callbacks to make a direct connection.

This is all buried deep in the documentation and examples spread throughout the various Livecode resources and documents which I find can be a bit too deep for the beginner. The documents either give a single example with code and only a brief explanation of the reasoning behind how it was done with no other examples to guide you as to how you might apply that to slightly different situations or requirements or they just list off functions and descriptions with the odd explanation thrown in and often that is obfuscated by terms and language used to describe things lat lead you on a merry chase to learn what they mean. I really feel that Livecode would benefit from a more structure approach to learning with theory and basic principles being explored first, progression from there in logical steps to discussing specific applications and those being explained with multiple examples and possible ways of solving the problems and other possible & related problems and solutions being discussed with a discussion and explanation of best practices along the way.

This is really what I want for Databases and Data Grids (Well apart from Filemaker like integration into a native control so we don't have to code so much basic functionality just to get us to the basics from which we can build the unique functionality of our app - New Multiplatform Database Layer feature please!)

Can anyone explain the very various methods and stages of getting Data from a Database into and out of a Data Grid and updating the changes made in the database along with their relative merits?

I'm thinking along the lines of "callbacks - requires only this many steps.... Because data is not stored temporarily in the datagrid array you loose access to these important functions that would allow you to do this and that but you do gain speed and simplicity and won't need to worry about data not being written back into the database if the program crashes etc."

"The full standard path - requires this many steps... Gives you these extra functions which you can use to do these things..... "

"Callback method steps are.... (with details, hows? Whys? and explanations of terminology)"
"Full standard path steps are = connect to database, create database cursor (which is?), read in query, convert to array (steps & reasons?), map to data grid columns (how?), register changes/edits made by the user (How?), export these changes to internal data format ready for writing to Database (How? which? why?), Map data into database fields and records and update those in Database."

I hope this isn't too demanding but I do feel this kind of approach to explaining and teaching would be much more constructive to my learning style and most likely help others significantly too.

Quentin.

Re: Database to Data Grid and back again - best methods

Posted: Wed Aug 28, 2013 8:48 pm
by Simon
Hi Quentin,
Thanks for putting the time in to write this post and please don't think I am in any way putting you down, but I think this falls into:
"Learning to walk before you run"
"Jumping in at the deep end"
Now I'm not saying that a new user could not grasp the concepts of mastering a DG/DB connection but it will be difficult.

LC used to have topics broken up into Beginner-Intermediate-Advanced which in a way you are asking for. I'd put the DG/DB thing into intermediate-advanced. If DG's were labeled "Advanced Topic - Implementing DataGrids" would you have written this post?

Trust me there are very few people (maybe 3 or 4(Really!)) who actually know all the workings of a DG, most of us are just blissfully ignorant and happy that it does work. I have been using LC for years and only just started working with a DG because it's such a beast.

I really don't want to rain on your parade.
But I think you may have bitten off more than you can chew <-- sorry about that one, just wanted to see how many cliche's I could get in :D

Simon

Re: Database to Data Grid and back again - best methods

Posted: Wed Aug 28, 2013 11:09 pm
by bangkok
Like someone said, DGs are "beast". I used myself this word when i started to digg in.

There is a learning curve, but afterwards it's pretty straightforward, especially for applications Database + Datagrids (a winner solution with Livecode).

Here are a few steps :

-I would advise you to forget "cursor". Prefer the direct revDataFromQuery

1-DG
-create a DG, name it "myDatagrid", create as many columns as you have columns in your SELECT SQL query.

2- CONNEXION TO DB

Code: Select all

put revOpenDatabase("mysql",ServerIPAddress,yourDBName,yourLogin,yourPWD)  into dbID

--dbID will contains the ID connexion, always integer
--here i use "mysql", but you can use "sqlite", "odbc" or "postgresql"

if dbID is not a number then
answer warning "Problem :"&dbID
exit to top
end if
3- READ THE DATA FROM THE DB

Code: Select all

put "select col1,col2,col3 from mytable" into dbSQL
   put revDataFromQuery(,,dbID,dbSQL) into theData  

--by default, columns will be separated by TAB, and record by CR (the first 2 commas in revDataFromQuery)
--but you could use other separators : put revDataFromQuery("|","*",dbID,dbSQL) into theData  

if theData begins with "revdberr" then
answer warning "Problem :"&theData
exit to top
end if

revCloseDatabase dbID
--close the connexion to the DB server
4-PUT DATA INTO DG

Code: Select all

   put false into pFirstLineContainsHeaders 
   set the dgText [ pFirstLineContainsHeaders  ] of group "myDatagrid" to theData  
Voila ! It's not complicated.

-The issue with DG at the begining is not to get "overwhelmed" by all their goodies and possibilties. To focus on a very simple application like DB->DG is the best way to start.

-Once you can see that it's working fine, you can attack the other way around : to modify a value in the datagrid, and then update the DB accordingly, or insert another line etc. It's a little bit more complicated, and there are several ways to achieve this (i will try to put a stack here as an example).

-And then you can go upscale with issue like : characters encoding (hum... your database stores chinese word in UT8, how to display those correctly in the DG, etc.)
-Ways to change dynamically the columns labels in the grid, for different type of SELECT query (so you can have only 1 grid to display several set of data from the DB)
-add icons on each line of the grid (to trigger actions)
-change the behaviour of the grid when user clicks, doubleclicks on a cell, change the color of some lines linked to the value of a certain cell (great to give to the user another layer of "visual reading" on the data) etc. etc.

-Last thing : you can't complain that LiveCode offers many, many different ways to achieve one task. That's precisely the beauty of such tool ! :) It gives total freedom.

Re: Database to Data Grid and back again - best methods

Posted: Wed Aug 28, 2013 11:30 pm
by EOTR
Quentin Brown wrote:I really feel that Livecode would benefit from a more structure approach to learning with theory and basic principles being explored first, progression from there in logical steps to discussing specific applications and those being explained with multiple examples and possible ways of solving the problems and other possible & related problems and solutions being discussed with a discussion and explanation of best practices along the way.
Coincidently I had been thinking about the same thing last night. Over the course of the last couple of weeks I've been poking around somewhat randomly at all the resources. I'm slowly beginning to find answers and more importantly WHERE to find answers. This lead me to think something like a syllabus would be helpful.

For instance, if I was to go back now and re-learn everything from Lesson 1 I would put together a list of links to the various resources that go over all the concepts that were involved in Lesson 1. There are parts of the LiveCode University that are probably important to go through, as well as some of the lessons in the App Academy. There are also tutorials on the LC site as well as the Mobgui site. There are key terminologies to be familiar with that can be found in the Dictionary.

It would have supplemented Lesson 1 really well if the pertinent areas of all these links/resources could have been pointed out in week 1.

Re: Database to Data Grid and back again - best methods

Posted: Thu Aug 29, 2013 10:34 am
by elanorb
Hi EOTR

This is a really good suggestion, we will start adding links to additional resources to the document that accompanies the video each week.

Thanks for pointing out how useful this would be.

Kind regards

Elanor

Re: Database to Data Grid and back again - best methods

Posted: Thu Aug 29, 2013 8:00 pm
by walkerl26
Quentin:

I went through a similar experience about 9 months ago, trying the reduce the "get data from a database into a datagrid" problem to its simplest and easiest form.

I found that the best way (for me anyway) was to try to hide/ignore all the revDBxxx calls. I started with Andre Garzia's DBLib package (available at http://andregarzia.com/pages/en/dblib/). I then wrote some scripts that used DBLib to automatically generate a card containing either a form (a page full of label:text-field pairs, one for each column in a chosen database table) or a datagrid (one DG column for each database column). It does this automatically, by reading the DBMS's system tables, which define the contents of each user-defined table. The scripts populate the resulting card, including Next and Prev record, plus Delete and Insert functions.

I gave a presentation on this at RunRevLive 13, and would be happy to send you a copy of the presentation if you're interested...

Even if you don't have an interest in the auto-generated-forms stuff, I still strongly recommend DBLib: a single functions call to DBLib will auto-fill fields or datagrids directly from the results returned from a query, no need to reference each item by name. And DBLib queries are MUCH, MUCH easier to code than the traditional approach described in Lesson 3. No fooling around with breaking SQL WHERE clauses into little quote-wrapped-chunklets-mixed-with-LC-variables!

I'm a huge fan of abstracting all the annoying details away, and of not making app developers struggle with SQL syntax. Andre has done a magnificent job of doing just that...

Larry

Re: Database to Data Grid and back again - best methods

Posted: Thu Aug 29, 2013 10:29 pm
by EOTR
elanorb wrote: we will start adding links to additional resources to the document that accompanies the video each week.
Thanks Elanor!! :D

Re: Database to Data Grid and back again - best methods

Posted: Fri Aug 30, 2013 1:32 am
by Quentin Brown
Thanks Bangkok,

That's a helpful start.

What it still leaves me wondering is how the columns match up - I presume this simple method relies on the number and order of the DG columns matching those returned by the SQL Query?

Larry,

Your RunRevLive 13 presentation sounds like it would be immensley helpful if you can send me a copy that would be fantastic. Thanks also for the link to Andre Garzia's DBLib package. It looks like an interesting solution and it's great to be made aware of these things that are available to help. It would be so great if things like this were all collated with the Livecode tutorials & documentation.

It takes hours of searching through multiple resources, extreme determination and blind luck to eventually find answers like this at the moment. That seems to be where the forum picks up the slack through experienced and knowledgeable users who have done the legwork willingly sharing their aquired knowledge. Thanks to all who have helped so far.

And Simon, I appreciate what you are saying but I don't think it needs to be this way and it could be possible to lead someone through the steps so they can eventually learn to run with DB + DG solutions, yes there is a lot to cover along the way but I'm sure many coming to Livecode have the kind of functionality enabled by the combination of the two in mind for their planned apps when they first come to the Livecode platform, just like I did. It would also be possible for future iterations of the DG control to integrate basic Database connection functionality - it seems like they are designed as optimum partners just the code is missing to link them up. As I am coming from filemaker development this is frustrating to me - my app which I am porting to Livecode has been developed with this basic functionality sitting in the background and just a few sections of code to join the dots and add the unique functionality I require and now I find I have to code that basic functionality from the ground up and climb a massively steep learning curve to do so before I can even begin a the point I began at in Filemaker. No doubt many people are in a similar situation and moving from Filemaker to Livecode due to the combination of much greater flexibility and less restrictive licensing model in terms of costs and seats. There is also the fmpro migrator plugin that draws Filemaker developers attention to Livecode. I see this incoming market as a good reason to offer well structured training from the ground up into the use of DB + DG and a good reason for adding that functionality into the DG control itself down the line. As it is I'm wondering if I'll ever have time to code my app in Livecode alongside my day job, redecorating the house and my complicated family life and if signing up to the Summer School has been a costly misadventure, but I'm going to see it through and learn what I can along the way, hopefully with help from those on the forums, the course tutors and a lot of perseverance and my own legwork I'll get there, if not I'll end up going bac to filemaker and making it work as far as I can there...

Cheers,

Q.

Re: Database to Data Grid and back again - best methods

Posted: Fri Aug 30, 2013 4:45 am
by cavenewt
Quentin Brown wrote:Thanks Bangkok,
As I am coming from filemaker development this is frustrating to me - my app which I am porting to Livecode has been developed with this basic functionality sitting in the background and just a few sections of code to join the dots and add the unique functionality I require and now I find I have to code that basic functionality from the ground up and climb a massively steep learning curve to do so before I can even begin a the point I began at in Filemaker. No doubt many people are in a similar situation and moving from Filemaker to Livecode due to the combination of much greater flexibility and less restrictive licensing model in terms of costs and seats.
I am in the same position. If anyone knows of any resources aimed more directly at those coming from a FileMaker background, don't hesitate to point them out, please!

walkerl26 Larry, if you wouldn't mind sharing your presentation with more people, I'd sure like to see it.

—Colleen

Re: Database to Data Grid and back again - best methods

Posted: Fri Aug 30, 2013 5:48 am
by bangkok
Quentin Brown wrote: No doubt many people are in a similar situation and moving from Filemaker to Livecode due to the combination of much greater flexibility and less restrictive licensing model in terms of costs and seats.
I've followed exactly the same path ! Was a fan of FM (client + server), since version 7. Did some nice applications for my company (production management).

But... no more.

Costs issues. It was really frustrating to see the editor... creating one release per year... with each time only a few add ons, or things that users were asking for years... and each and every time to pay upgrade licence (and even full licence) for the clients, for the server... blablabla.

Enough was enough ! It's a racket business. End of rant.

So Livecode as (very) smart front end for databases, that's the killing app. Will never go back to FM.

Bon courage for your endeavour. it's worth it.

Re: Database to Data Grid and back again - best methods

Posted: Fri Sep 06, 2013 12:25 pm
by Quentin Brown
Hi bangkok,

I really appreciate you taking the time to help me get the basics down on this. I was just working through your first reply on this thread again and it really helps. Breaking it down into simple stages and not getting too carried away with all the many possibilities of DataGrids is definitely the way to go.

Although I am making steady progress I would really appreciate it if you could share the example stack you mentioned especially if it helps me learn how to achieve any of that exponentially spiralling list of possible functions you might want to add down the line to a DB-DG based project which you wrote towards the end of your post.

Many Thanks,

Quentin.

Re: Database to Data Grid and back again - best methods

Posted: Fri Sep 06, 2013 2:29 pm
by bangkok
OK to make you wait, you can have a look at :

http://www.forums.runrev.com/viewtopic. ... rid#p78299

This stack contains several tips and tricks for DG.

Re: Database to Data Grid and back again - best methods

Posted: Tue Sep 10, 2013 4:29 pm
by cavenewt
walkerl26 wrote:Quentin:

I went through a similar experience about 9 months ago, trying the reduce the "get data from a database into a datagrid" problem to its simplest and easiest form.

I found that the best way (for me anyway) was to try to hide/ignore all the revDBxxx calls. I started with Andre Garzia's DBLib package (available at http://andregarzia.com/pages/en/dblib/). I then wrote some scripts that used DBLib to automatically generate a card containing either a form (a page full of label:text-field pairs, one for each column in a chosen database table) or a datagrid (one DG column for each database column). It does this automatically, by reading the DBMS's system tables, which define the contents of each user-defined table. The scripts populate the resulting card, including Next and Prev record, plus Delete and Insert functions.

I gave a presentation on this at RunRevLive 13, and would be happy to send you a copy of the presentation if you're interested...

Even if you don't have an interest in the auto-generated-forms stuff, I still strongly recommend DBLib: a single functions call to DBLib will auto-fill fields or datagrids directly from the results returned from a query, no need to reference each item by name. And DBLib queries are MUCH, MUCH easier to code than the traditional approach described in Lesson 3. No fooling around with breaking SQL WHERE clauses into little quote-wrapped-chunklets-mixed-with-LC-variables!

I'm a huge fan of abstracting all the annoying details away, and of not making app developers struggle with SQL syntax. Andre has done a magnificent job of doing just that...

Larry
Noob question: What is the danger, if any, of future updates to LC breaking something like DBLib?

Re: Database to Data Grid and back again - best methods

Posted: Mon Sep 16, 2013 4:10 pm
by walkerl26
CaveNewt: Yes, I do worry a bit about using 3rd-party packages. But in this case, I know that Andre (the author of DBLIB) is a long-time, active, dedicated LiveCode developer. So I'm gambling that he will update the package if a future LC release breaks it...

Quentin, CaveNewt: I will attempt to attach my presentation to this post.

Grrr: the #$%^&* forum first said I couldn't upload a file > 2Mb, so I dropped the resolution a bit; then it said No PDFs Allowed; so I zipped the file, and now it tells me that files > 256 KiB (sic) are not allowed.

If you email me, I'l be happy to send the presentation along to you...

Larry@walkerenergysystems.com

Larry

Re: Database to Data Grid and back again - best methods

Posted: Sun Mar 27, 2016 3:23 pm
by bbhank
I agree with Quentin. The complexity of the datagrid makes it where it needs excellent documentation for the beginner and the advanced user, for the free AND the premium, IF there is to be new blood into the life of LiveCode. The documentation for LiveCode is terrible, especially for the beginner and can stop many in their tracks. This has little to do with its complexity, but more the lack of writing skills of the producers. Not a cut or negative but a fact of life. Manuals are difficult to produce. Good manuals even harder. One has to have LOTS of composition all through school even to start a manual. Bad documentation can ruin any effort. I have been in technology for 30+ years and have seen few good instruction sets for ANY program. I have Never been asked, not even once, what my writing skills were before being assigned a manual to produce. I came from grade school through college writing papers, some of them prize winners, judged by communities of peers. It has been said that "people who can't write, write manuals." I don't find the lack of quality documentation defensible. If there are complaints, fix it!