Database Examples wanted Please

[mhoneywill]

Hi,

I'm interested in using LiveCode to connect to and Create databases on my ISP's MYSQL server.

I find that looking at existing applications allows me to understand how to do things, Having just bought LiveCode I was hoping there might be some examples available that would get me started, the few examples supplied with the install do not seem to include this

In Googling around I found this article http://livecodejournal.com/interviews/r ... rview.html written by Richard Gaskin which included the following comment "you wrote the employee database stack/tutorial" I could not find this?

LiveCodeJournal: Among your attributes, I would list the fact that you are very active in the Revolution user community. To cite a few examples, you wrote the employee database stack/tutorial that ships with the product, you host the Revolution Embassy (http://www.fourthworld.com/rev/index.html), you?re a regular contributor to the Rev mailing lists, and, most recently, you?ve built RevNet, a stack that serves as sort of a web browser dedicated to Revolution resources. Why so generous with your time?

Richard if you're reading this is this available?, Also if anyone else knows of any other database stacks could they post links as replies to this. I'm looking for a simple contact type database that has the ability to store information about different individuals, along with a notes field and photo of the contact.

Cheers

Martin

[townsend]

Interesting. That Richard Gaskin interview talks about Rev 2.0.
This must be a couple years old.

I myself, was surprised to find that the LiveCode User Guide
only had 4 Pages (265-268) on "Working With Databases".

I have read many times, that native drivers for both mySQL and Postgres are included-- so to me that says-- LiveCode should be just as capable as any other development environment, even if it's not widely used that way.

None the less, the short coverage, in the User Guide, and the lack of activity the Database section of the Forum, made me wonder. Then I found the SQL Yoga product. Which was developed by Blue Mango Learning and seems to offer a whole suite of tools for making database access as simple as everything else in LiveCode, which is just what I need.

I'm only one week into LC-- so I'm not even ready to do a proper evaluation of the SQL Yoga product. There so many more elementary concepts I need to grasp first. Buy from what I've read, the fact that Blue Mango, is and has been, very closely aligned with RunRev give me some confidence about this product.

Also, I noticed there was an update recently, so it not old technology.
I've sure, when I get to this eval, I'll have lots of questions.

[mhoneywill]

Hi Townsend,

You echo my thoughts, I was expecting many more examples with Livecode. I've looked in RevOnline and there is 1 demo that's not even using SQL.

I am 2 days into owning Livecode and I too have got a licence for SQL Yoda (lucky I got it with the Mega Bundle), I hope it does what I want. I've not had a chance to try it yet. All I want to do is create multi user database applications that will use an online shared database (MySQL). What would be really cool is if the application could use SQLIte when not connected to the internet, then synchronise with the MySQL database when connected. But lets learn to walk before learning to Run.

It just seems that Livecode is crying out to be a tool to create Database front ends, and as you say its mentioned quite a lot. Then when you get the product, you can't find any examples. A simple contacts database would be good for new users

After quite some searching I have found http://runrev.com/developers/lessons-an ... databases/ and http://lessons.runrev.com/spaces/lesson ... g=database but they weren't easy to find. This stuff should be supplied with the product in my opinion. Also a lot of the examples in RevOnline do seem to be quite dated.

Cggod luck and keep me updated with your progress, looks like we are both on the same path.

Cheers

Martin

[townsend]

Thanks for those links Martin! I too picked up that Mega Bundle.

Odd-- I've been here a week, and I'm still not allowed to post links.
You're here two days, and posting links already. Hmm. Anyway.

LiveCode looks like the perfect SaaS solution. (software as a service)

I was using MySQL with Real Studio, but now that MySQL owned by Sun, they have more stringent restrictions on commercial use-- so I've switched to Postgre SQL.

You mention synchronization. This is something on my to-do list, as well. All records in my remote database will have a timestamp field, which will make downloading only new records clear cut. Now that I know LC can easily save the contents on any DataGrid with just a few lines of code, this seems more plausible.

It's still too early for me to know what will be the best route. I still have many questions. For instance.

I know an array can be bound to a DataGrid. Can an array be bound to DB? SQLite or remote?
Can a DataGrid be bound to a remote DB? Can a SQLite DB be bound to a DataGrid.
I'm guessing there are a lot of ways to deal with DB data.

Anyway, make sure pick up a copy of SQLiteMan, which is a FREE SQLite DB Manager.
It's great. From: sqliteman_com

[bangkok]

It just seems that Livecode is crying out to be a tool to create Database front ends, and as you say its mentioned quite a lot. Then when you get the product, you can't find any examples. A simple contacts database would be good for new users

You are right : "crying out"... LC is just that. The perfect tool.

And because of that, databases are nothing special.

Just a source of data for LC.

I use it, itensively, with MySQL server, local and remote, without any problem.

Basically : 4 things to know.

-open the connexion [revOpenDatabase]

-send the SQL query : if to read : 2 ways, take the data [revDataFromQuery] or create a cursor [revQueryDatabase]
if to execute (update, delete,insert) [revExecuteSQL]

-manage possible error message returned by LC or the MySQL server.

-close the connexion

At the beginning, I felt a little bit overwhelmed too (I had zero experience with SQL db). And then, everything flows naturally.

Now I can say that LC with DB is nothing special.

What is special is the way you design your DB and you SQL queries and the way you design your app in order to display /handle the data etc.

Check the example.

[mhoneywill]

Thanks Bangkok, you give me hope .

One of the things I like with tools is a wealth of examples, it gives me as a newbie courage that different things can be achieved (even if I don't know how). I think this is one thing that RunRev could put some more effort into, more examples of how brilliant LC is and what can be achieved with it (Some sort of Showcase).

It looks like my first test application will be some sort of contacts database and this will prove to be a good testing ground to see how easy LC is to use with Databases.

Looking at your your example DB1.rev is trying to access 127.0.0.1 are you running a local copy of MySQL possibly using WAMP http://www.wampserver.com/en/ I will look at installing this server for testing.

I think one of the things that RunRev could do is create a tutorial on databases for beginners, that takes you through the differences between SQLite (local) and MySQL (remote / Multi-user) databases. Specifically how to create the databases and the Tables, before using LC to manipulate them. I know this may sound trivial to some users but RunRev does target LC at non programmers and hand holding at the start really helps. Also if LC is not the correct tool to manage databases then what are good examples of tools.

Townsend: I too could not post links, its to do with stopping spamming, once you have posted 5 or so posts then you will be allowed to post links, so post away , I've tried to download SQLiteMan from here http://sqliteman.com/page/4.html but for some reason it downloads the Os2 version??

[bangkok]

Looking at your your example DB1.rev is trying to access 127.0.0.1 are you running a local copy of MySQL possibly using WAMP http://www.wampserver.com/en/ I will look at installing this server for testing.

I think one of the things that RunRev could do is create a tutorial on databases for beginners, that takes you through the differences between SQLite (local) and MySQL (remote / Multi-user) databases. Specifically how to create the databases and the Tables, before using LC to manipulate them.?

Yes 127.0.0.1 is for localhost.

No need apache and PHP. Go there to download MySQL for Windows :
http://dev.mysql.com/downloads/mysql/

Installation is very easy. Work like a charm as an application or a service.

After that I advise you to install a very good SQL client : HeidiSQL.
Free, a perfect front end for MySQL.

http://www.heidisql.com/

Connect, create a DB, a table with a few columns. And voila !

And then you can start working with LC.

As for the tutorials, again, there is nothing special. For instance, the question you ask regarding MySQL and SQLite, and the ways to handle single or multi user DB... are not related to LC.

You'll find on the web TONS of tutorials and infos about SQL, MySQL, SQLlite, database design, management etc.

You have to learn SQL, the magic of the multi user DB, etc...

LC will just be a nice (and easy, and efficient, and quiet) "wrapper".

Good luck !

[townsend]

Townsend: I've tried to download SQLiteMan from here but for some reason it downloads the Os2 version?

Looks like there's a new version
I've been using the previous 1.2.1 version which can be downloaded for windows here:

sourceforge_net/projects/sqliteman/files/sqliteman/1.2.1/
Note: replace underscore above, with period.

[townsend]

Hey-- I've got link posting privileged now.
So here's that link that works:
http://sourceforge.net/projects/sqlitem ... man/1.2.1/

SQLite is a a completely FREE utility.
One of those top notch open source projects.

One really cool feature-- once you create a table--
SQLite will populate that table with test data--
with as many records as you ask for.

And of course, it generates a SQL CREATE statement of your final database design.
You paste that into your script. That way your app can create a new database from scratch, if needed.

[PeterG]

Hi Bankok
Would it be possible to see an example of putting the cursor into a Datagrid of a query ?

Many thanks
Peter G

[quailcreek]

gagsoft,
There are plenty of example of this on the forum. Also, check the dictionary for "revDataFromQuery".

Here is one way:

Code: Select all

  put "SELECT FigureID, Condition, theDescription, bCodeNum FROM theTableName" into tSQLStatement
  put revDataFromQuery(tab,cr, (the uDatabaseID of this stack),tSQLStatement) into tData
  put "ID" & tab &"Condition" & tab & "Name" & tab & "bCode" & cr into TheTextData
  set the dgText [true] of grp "myDataGridName" to TheTextData & tData

[PeterG]

Hi thanks for the pointer

many thanks
Peter G

[FourthWorld]

Have you seen the examples included in the install?

[bogs]

There is also quite a few example stacks made about the dg and sql, such as
Ben Beaumont's Data Grids Tour and SQLite-Sampler.

[PeterG]

Thank you Richard, quailcreek, Bogs for all the support.
I am inspired and excited by the hi-spirited responses and debates on the forums.

Thanks
Peter G