Getting Started with a Database

[Kay C Lan]

On 2/15/06, David Burgun <dburgun at dsl.pipex.com> wrote:
>
> I have now looked that three database solutions and none of them seem
> to work and there seems to be very little "getting-started"
> information supplied with them.
>

Just confirm that you have a copy of the MySQL reference manual. I can't
remember if this is part of the 'full download', if not you can download it
here:

http://dev.mysql.com/doc/

I use the pdf version as Preview makes it extremely fast (after a slow
opening as the file is HUGE) to "find" terms.

Chapter 3 is a very straight forward 'Tutorial' which you can do through the
Terminal, although as previously posted CocoaMySQL is your friend and I
can't emphasis enough how easy this makes life.

If you are having troubles right from the start of the Tutorial then back
track to Chapter 2 which covers installation and a few possible problems
that you may encounter.

Once you have a functional installation, and confirmed that you can connect
locally, with CocoaMySQL the learning curve needed to create, add, update
and query the database that you describe will be minor.

For your initial setup of CocoaMySQL the enteries will be:
Host: localhost
User: root
Password: [yourpassword]

NOTE: the installation instruction for MySQL make it clear that after a
virgin install the root account (which is the MySQL root account NOT the OSX
root account so the password can be different) has no password but your
first action should be to allocate a password for the account.

Once connected you will have only one DB to look at, the mysql DB which you
shouldn't touch for a while until you understand what it does - but
basically it keeps a DB on all the users and DBs you create with MySQL.

>From here you are a DB Demi-god, you can use the CocoaMySQL GUI to create,
in your case:
1 x db
1 x table
4 or 5 fields (you might consider an Id field unless the Name field can be
guaranteed to be unique in all cases)

My guess would be that apart from your Id and Size field ( which would be
unsigned INTegers) your fields would be VARCHAR with sizes varying from 4
for the Type, to 256 for the ImageFilePathName (although this could be
limiting if the path names are very very long - so you might need a TEXT
field).

Initially, to make troubleshooting easy, I'd suggest you allow all fields to
be NULL.

Once you have CocoaMySQL (or Terminal) and MySQL talking to each other and
you have created your DB, the I recommend you use Rev WITHOUT Trevor's libDB
to confirm that you can use the Rev DB calls (not the DB Query Builder) to
communicate with MySQL. This should be a quick simple stack with 4 or 5
fields a button called something appropriate like 'Add Data'. In your button
you should have these calls:

-- this assumes that your db is called myTestDb, your MySQL root account
password is myTestDbPassword and your stack is called MyStack
-- this is a 9 line script so be carefull of where line breaks have occured

on mouseUp
if (the propDbRef of stack "MyStack" = "") then
 set the propDbRef of stack "MyStack" to
revOpenDatabase("MySQL",,"myTestDb","root","myTestDbPassword")
end if

put "INSERT INTO myTestDB (ID,Name,Type,SizeImageFilePathName) VALUES (" &
quote & field "ID" of stack "MyStack" & quote & comma & quote & field "Name"
of stack "MyStack" & quote & comma & quote & field "Size" of stack "MyStack"
& quote & comma & quote & field "ImageFilePathName" of stack "MyStack" &
quote & ")"

revExecuteSQL the propDbRef of stack "MyStack",tMyDbQuery
on mouseUp

Use the debugger and Variable Watcher to step through the script to see what
tMyDbQuery looks like.

You should then be able to change all the data in the fields and press the
"Add Data" button at your hearts content and then confirm the data entered
your Db by using the 'Content' pane of CocoaMySQL. Note that to see the
updates you will need to deselect your Test DB and select it again to get an
updated view of the Db.

Having achieved this you can then go ahead an use Trevor's excellent libDb
knowing that MySQL works, you have a Db that has data, and Rev can
communicate with the Db. From here on any failures to add,alter or retrieve
data will be a failure in your own code - reassuring isn't it;-)


HTH