This chapter is intended to be an introduction to Database Programming - not a comprehensive tutorial.
The Easy Database command set is a library included with the Emergence BASIC development environment. Allowing connection to any database format with an ODBC driver, the command set provides a robust solution for enabling SQL access to any application.
A database is a structure that comes in two flavors: a flat database and a relational database. A relational database is much more oriented to the human mind and is often preferred over the gabble-de-gook flat database that are just stored on hard drives like a text file. MySQL is a relational database and is the database that we will use with Ebasic.
In a relational structured database there are tables that store data. A table has columns and rows.
Columns define which kinds of information will be stored in the table. An individual column must be created for each type of data you wish to store (i.e. Age, Weight, Height).
A row contains the actual values for these specified columns. Each row will have 1 value for each and every column. For example a table with columns (Name, Age, Weight-lbs) could have a row with the values (Bob, 65, 165).
I suggest that you find one of the many MySql Tutorials that are on-line if you wish to really understand databases and structures.
This is the example database program included with Ebasic
A database program needs to have the following functions:
Before any SQL queries, updates, insertions, etc. can be performed on a database you must first establish a connection to it. The connection is created using either the dbConnect connect function or the dbConnectDSN function.
The following code will open or create the database.
The connect method you will usually use is dbConnect. It connects to a local database on you computer.
In our program we use:
It is important to note that not every database type is discreet file based. In cases where a database is stored in a directory such as text format CSV files you supply the path to the directory in the filename parameter.
Some drivers just use the options parameter and a keyword to connect. Consult the documentation for your database.
The second connect method, Data Source Names (DSN) is the traditional method for connecting to a database through ODBC. While it may be traditional it is not the most convenient method unless you have a number of applications that will be using a database and don't want to worry about where the database is located. A DSN is created using the "Data Sources" control panel applet. After a DSN is created, and points to a valid database, connection to it is as easy as supplying the name and options.
Your program must disconnect from the database when you have completed your operations, to disconnect issue a dbDisconnect command before exiting. Simply pass the pointer returned by dbConnect or dbConnectDSN.
Before we can connect we need to know the layout of our database. The map of the layout is called the schema.
Our database has one table with several fields. The table is named 'addresses' and contains the fields id, fname, lname, street, street2, city, state, zipcode, phone, and email. This is our schema.
In our example, if the open fails (returns a NULL), we create the database and tables. To create the database we use the dbCreateMDB command.
The GETSTARTPATH tells us that the database is in the current directory and the name of the database is addressbook.mdb.
Once we have successfully created the database we add the table and fields.
All calls to the database to work with data use the dbExeccSQL call. Your command to the database is a string containing the command you wish to execute.
The string data tells the database manager to create a table called addresses with fields id, fname, lname, street, and street2. The command also contains information about the type and sizes of the fields. For further information about the details of the command you should refer to one of the many MySQL tutorials and reference manuals that are available on-line.
One important aspect to notice is that the return handle of the db calls is cleared after each use. To do this use the dbFreeSQL(<handle>) command. This is very important since you will find that you often reuse the variable for subsequent commands.
At this point we have our database open and are ready to add our first record. To do this we need subroutines to create the new record and update the data in that record. You could use a single subroutine for both but it makes it more usable to create 2 subroutines. The first is the 'DoNew' subroutine to create the new record. The second is the 'DoUpdate" subroutine to do the work of adding data.
The 'DoNew' subroutine inserts a blank record into the database. Once the blank record is created we set up the screen to collect the data to be stored in the record.
Now that we have a blank database record to store the data in and we have entered the data into the screen fields we read the data from the screen into variables that we can store in the database. We call another subroutine 'CopyData' to make this happen.
The subroutine is written to both copy data to the variables from the screen and from the screen to the variables. This time we are reading from the screen to the variables so we call 'CopyData' with an argument of '0'.
Once we have the data we wish to store in the database we need to tell the database where and how to store it.
The combination of the 'dbPrepare' and the 'dbBindParameter' comprise our command to the database to update the current record with the data from the fields. You should always check the return code to ensure that your action actually happens. In this example the assumption that everything worked is being made.
To change a record we can use the screen function, the CopyData and DoUpdate subroutines. We do so by selecting the change option in the menu and updating the data on the screen then a call to CopyData with a 0 argument is made to copy the data to the variables. Once that is complete, call the DoUpdate subroutine and the data is changed.
The delete subroutine 'DoDelete' will delete the record currently in the record buffer. This will be the last record that was selected. You must take care to ensure that the record you currently have selected is the record you want to delete. The delete routine will issue a delete command to the database manager then read the first record in the database into the current record buffer.
You may wish to modify this code to be more clever about which record is read in after a delete.
This has been a brief introduction to Database Programming. A future Ebook will cover Database Programming in more detail. I would suggest that you play with this example and expand on it. The best way to learn is by doing.