Setting up and using a Database

bjb007 · Post by **bjb007** » Thu Mar 20, 2008 12:27 am

My next project will need a database.

Does anyone know where I can find
examples?

In some systems a database is a set
of tables although my experience has
been with systems which called a table
a database.

I'm familiar with SQL so setting up
the database/table is where I need some
help.

Does a database come into existence
by specifying a name (as with files) or
is there another method?

Any help appreciated.

BvG · Post by **BvG** » Thu Mar 20, 2008 12:59 am

Jan just today posted a few nice handlers that can get you started:

Post from today

bjb007 · Post by **bjb007** » Thu Mar 20, 2008 2:35 am

From the 2.9 docs....

"This topic does not include discussion of how to set
up and create a SQL database, which is beyond the
scope of the Revolution documentation."

No help there so where do I go next?

Janschenkel · Post by **Janschenkel** » Thu Mar 20, 2008 6:08 am

In the case of SQLite, you can create a new database file on your hard drive, automagically, by just "opening" the database. You can then use SQL DDL commands to create the tables.

Code: Select all

on mouseUp
  ask file "Create an SQLite database"
  if it is empty then exit mouseUp
  put it into tFilePath
  put revOpenDatabase("sqlite", tFilePath,,,,) into tConnectionId
  put "CREATE TABLE Customer (FirstName, LastName)" into tDDLquery
  revExecuteSQL tConnectionId, tDDLquery
  revCloseDatabase tConnectionId
end mouseUp

The above script creates a new sqlite database, and adds a new table Customer with two fields. I made no attempt to define a primary key or index - for more information on the commands for SQLite database schema manipulation, see http://www.sqlite.org.
As for other databases, check their documentation for more information on installation and configuration.

Hope this gets you started,

Jan Schenkel.

bjb007 · Post by **bjb007** » Fri Mar 21, 2008 7:33 am

Jan

I changed one line so that the file
extension is added...

put it & ".db3" into tFilePath

since I'm not in the habit of adding
the extension to new file names.

Of course if I did I'd have .db3.db3.

Do the Rev functions need .db3 to work?

Janschenkel · Post by **Janschenkel** » Sun Mar 23, 2008 10:55 am

The rev functions do not require an extension - the sqlite database driver will check for itself if the file is a valid SQLite database. Of course, it is "cleaner" to add the extension when it's missing.

Jan Schenkel.