How do you programmatically create an empty database?

[Bob Sneidar]

Actually, this is one of the things about SQL that you have to think about if you are going to use it. If you are not going to use a file based SQL like sqLite, you will need some kind of tool that can connect to an SQL server and create a database for you. You have to have at least one table with one field. Once you have that you can create more tables, add fields, the works. 

MySQL has some tools you can download for free, called MySqlWorkbench (Mac only I think and you can google to find it). There are other tools you can buy for not too much money, like Navicat. While you CAN get a shell, connect to the server and create an database using some commands, I would not recommend this method for anyone who was not already familiar with working with SQL databases in this fashion. 

The next thing you need to do is find some kind of SQL primer, so that you can begin to get familiar with syntax. No matter what you do, you are going to at least need to get some basics under your belt. After that I highly recommend Trevor Devore's sqlYoga, which is a utility which hides most of the real grunt work in connecting to and accessing SQL databases. Even that has a learning curve, but it shields you (for the most part) from the actual syntax, and I believe it "escapes" your data for you. 

Just a heads up on that, lets say you wanted to insert a record, and populate the columns with some data. The syntax might be:

INSERT INTO example (name, age) VALUES('Timmy Mellowman', '23')

But what if your data contained commas or carriage returns? You can see that the SQL syntax USES those things, so a comma in your query string in the wrong place would wreak havoc and you would get an error. Escaping the values replaces such reserved characters with something else, so that doesn't happen. SqlYoga does this for you (if I am not mistaken). 

Now sqLite is much easier. You just need to call revOpenDatabase, and specify the path to the database, and if it doesn't exist, it will create one for you: 

get revOpenDatabase("sqlite", "C:/testdbsqlite.db", , , , )

After that you can begin to add columns and what have you using some of the other commands available to you (see built in dictionary). It's easier, but there are some caveats to sqLite, such as the kind of columns and indexes you cannot add after the database is created. 


Bob


On Nov 23, 2010, at 1:46 PM, Jonathan Lynch wrote:

> Honestly, I am still just trying to get my head around using databases.
> 
> I use Rev for processing text in all sorts of creative ways at work - vastly
> increases our productivity - but, I know next to nothing about database use.
> 
> The logical process, to me, would be the following:
> 
> - Create database file
> - Populate it with columns
> - Set up my application to use an embedded database app to write and
> retrieve data as needed
> 
> Say, for example, if I wanted to experiment with Valentina (knowing that the
> demo version only lasts 10 minutes before the app has to be restarted). I
> could not fool around with it, without having an existing file to work with.
> 
> So, I thought to myself, my first step must be to create a database file. I
> am not committed to doing it in a script, I just don't know how else to
> access the embedded database driver functionalities.
> 
> Should I just find a blank database file somewhere on the web and download
> it?