Database Tutorial for Beginners

[Rponzo]

As an experienced FileMaker developer (15 years & DevCon presenter), I was hoping to step up to Live Code. However, the underlying prevasive theme throughout this forum, and a personal experience of mine, is the real lack of a complete, cohesive and well thought out tutorial for database development (mySQL and MS-SQL). I can not for the life of me understand why you have yet to address this after so many years, especially looking at the number of requests posted. It should take a newbie to LC 4 days of trying to connect LiveCode while hating for a response on the forum.

LC is aggressively going after this market share, specially with the FM interface products recently released, yet there is no single reference to take us through the entire CRUM process. I spent 5 nights of extensive googling only to discover single posts/webpages that do not guide you down a straight path. This does little to win the confidence of potential customers who are on the fence and require a single reference to quickly learn the process and get on with the business of the day to keep revenue coming in.

Tutorials of birds flying around the screen, while appealing, does not address real world business cases that drive commerces to solve real issues and challenges companies face everyday. That requires a multi-user databases. Can you please commit to doing us all a favor and once and for all create a short e-book (not a 2 page post) and a series of videos. We can then become advocates by singing your praise. Until then, many of us will just come and go without investing in your technology and company.

[ghettocottage]

I hear your pain, but you might want to know that this is a user-forum rather than a direct communication line to the LiveCode Team. You might consider writing them an email.

As someone who started with Filemaker Databases several years ago, I can tell you I had a similar feeling of frustration when trying to find an alternative; however, now that I have a broader understanding of how various database-related things interconnect, I can see why there is not one particular tutorial on how to do a database project in LC: it is because there are so many ways of going about it, and each way has pros and cons...it all really depends on the particular project you are working on.

My current approach, that I have been doing for a few small businesses, involves setting up a VPS in DigitalOcean or Linode and using that as a MariaDB/Apache/LC Server/VPN Server.

I use MySQL Workbench for developing my database structures, which is an entirely different topic.

Once I have that set up I use Livecode to create a database front-end that can send queries and edits and such to the server (through the VPN only).
It works really well, security is tight, and I have a nice separation of interface and data.



Having said all that, some people use SQLite rather than setting up a server, and some people have existing odd server setups that they need to work with. Still others need a database that is open to the general public and have to come up with a login/password system or other type of security.

LC can handle the various database scenarios, and is quite easy to code in, but the actual database management part of things wouldn't even be in the LC Tutorials and needs to be considered beforehand.

This forum is a really great place to get in-depth help in getting started. If you describe the scenario for your project, there are some pros here that will point you in the right direction so you can everything set up first.

[Pistris]

Am a very inexperienced livecode developer and I can tell you by just reading the brief tutorial on databases
you can do anything
http://lessons.livecode.com/m/4071/l/11 ... a-database

the only other thing you need to know is SQL and you already have that part covered

[FourthWorld]

"CRUM"?

[Pistris]

LOL what I get from google is

"CRUM" Computational Representational Understanding Of Mind.

[bogs]

Erm, no, it would more likely be -
Create, Read, Update and Delete

I also believe there is already one just like that either in the user samples (forgot who wrote it) or available directly in the IDE itself.

[Pistris]

I learned something new

[bogs]

Heh, glad I could throw a new bone your way

I should have also mentioned that the dictionary actually has a fairly complete listing of the database functions and handlers, I'd start with 'revOpenDatabase' and move on to the other handlers listed in the 'related' section.

Aside from that, there is the lesson pointed out above, the samples I mentioned, and Max's excellent Wiki (type "database" into the search).

As well, there is the BYU Database section, the 'Super site' which has this section

LiveCode and Databases
How do I Create and set up a Database?
Converting a Database Cursor to a Data Grid Array
Connecting to a MySQL database
How to connect Database using ODBC?
How to create and use an SQLite database?

and many MANY more sources. It really does just take some looking.

[Rponzo]

Glad to see so much interest in my post so far. Thank you all.

My primary NEED is CONNECTING LC to back-end DB. I am not asking about creating the backend SQL DBS. I am experienced with MS-SQL and MySQL. To the post that there is so many ways to do something which is why it is difficult for LC to create a good tutorial does not make sense. I can do something the same thing in many ways in other languages, but there are still tutorials. Like any math class you can the learn long way of doing before the formula is revealed. Both work.

As for posting here versus emailing LC directly, the latter is a single voice. In the past it didn't seem to make a big enough impression to warrant them creating a coherent database guide. If enough people get behind the chase maybe they will take notice.

[ghettocottage]

Ok. I am just going to go pop myself some popcorn. Be right back

[Pistris]

@Rponzo
If you check out the tutorial you will see that there is really not much else that
you can write about connecting to mysql. in the tutorial there is a line of code

put revDataFromQuery(tab, cr, gConnectionID, tSQL) into tData

everything boils down to that line

on tSQL you are going to place your query

and the dataset its gonna be put back into tData

you can do with tData as you please

there is really not much else to it

Sorry if am not getting your message but it is really that simple

The hard part you already know it ( writing sql and administering the actual database)

Cheers

[dunbarx]

What pistris is saying, and you may think it glib, is that the LC component is SO high level that you might not trust it.

There was a comparison on an early LC website showing the difference in bringing the contents of a webpage into ones computer. The LC version was one or two lines, completely readable, and the other (I am not sure in which language, but "mainstream" and widely used) was much longer, and in Martian.

In fact, these fourth generation languages seem impossibly simple, if not simplistic, implying they cannot be serious, never mind full featured.

Never mind astonishingly powerful and accessible.

I do not use SQL gadgetry. But I bet the above is true.

Craig Newman

[jacque]

The LC Lessons cover just about everything. I found the following by searching for "database":
http://lessons.livecode.com/m/4071/l/70 ... l-database

There are only three lines of script required: open the database, send a query, close the database. There's a fourth step if you want to update the database while it's open.

The team adds new lessons regularly.

[golife]

I think most of the LiveCode database documentation is good for starting, but it very often lacks details that are even important first place. For example, in my eyes, it is absolutely essential that text is always encoded and decoded (see dictionary) when working with databases. This has not always been documented since many users still rely on ASCII.

The most complete documentation for database applications and tools to develop with LiveCode you find under https://github.com/trevordevore/sql-yoga from Trevor Devore. This is a highly professional approach. Trevor is very precise in defining and his framework and it is very well thought through. And even if you would not use the SQL Yoga API, you would understand much better going into the details of his scripts.

Another easier but very nice and well-organized approach is dbLib from Andre Garcia: https://andregarzia.com/livecode/. Following it gives a pretty clear understanding of how databases can be used from within LiveCode.

There may be others I have not seen or looked into yet.

Of course, it is desirable if there were more books and documentation and cooking recipes regarding setting up real-life applications from less complicated to large systems created with LiveCode and the most popular databases, either locally or using server-side management systems, including using the FileMaker database.

Business almost entirely depends on database-driven applications. And FileMaker derives its success from addressing this market where money can be found.

To have LiveCode do something similar compared to what Filemaker is doing now depends on someone doing it. -:)

golife

[bogs]

I finally remembered where I saw that CRUD examplel

Don't be thrown off by the 'SQLite title, except for the actual connection string, I'm pretty sure the rest is the same as any other db.