Getting Started with SQLIO
To explain the process of of transitioning a program that uses flat files to a database, let's use an
example program. The program we will use the the "Addrform" program found in the Sunbelt\Plbwin.95\Demo folder.
The requirements for SQLIO are very straight forward. You must enable SQLIO by a simple modification to the
PLB runtime configuration file. There must be a mechanism that understands that
a file in the PL/B language should relate to a table within the database. Further the fields within
the records of a flat file correspond to columns in rows within the database. Other entities such as
the database configuration and indexes must also be defined. These relational definitions are
implemented in a schema database and easily maintained using Sunbelt's SchemaEditor. Once these definitions are
in place, a simple modification to the PL/B Open or Prepare statements triggers the SQLIO functions.
All IO normally destined for the disk file will be performed by the database.
The key to implementing SQLIO is the creation of the schema file. A tool released by Sunbelt and
available in the Sunbelt\Plbwin.95\Code folder is the SchemaEditor. Originally designed to support the "named IO"
feature implemented in the PL/B language, this tool has been enhanced to support the creation and
maintenance of the SQLIO schema databases. Though the schema file may be modified using any
database administration tool, the SchemaEditor provides a
graphic representation of the database and a set of simple functions to maintain it. The SchemaEditor
also includes a feature to import data definitions from a PL/B source file.
Let's understand the Address demo program as it was originally released. Open the file "addrform.pls"
using your favorite editor and let's examine how it works. If you'd like to use the Sunbelt IDE, create
a new project in the Sunbelt\Demo folder and add this program to the project.
This application's purpose is to maintain name and address information in a file named "address".
It does this by employing a form (addrform.plf) as its user interface. Most of the program's
IO routines are implemented in the code section of the form but a few are in the host program (addrform.pls).
These routines are triggered by user actions on the form such as clicking a button. To view the code
contained within the form, open it using the Designer. If you are using the IDE Studio, simply double
click on "addrform.plf" in the Source Map and the Designer will launch and open the form. The code contained
within the form can be viewed by pressing the F3 key or by selecting "Code" from the "Window" menu.
Let's first modify out runtime configuration to support SQLIO. Add the following two lines to bottom of
the "plbwin.ini" file found in your "sunbelt\plbwin.95\code" folder:
PLB_SQLIO_HOST=c:\sunbelt\plbwin.95\code\sunschema.db
PLB_SQLIO_DRIVER=1
The first line identifies the name and location of our schema database. The second line identifies the
database as SQLite.
Next we need to create the schema database. In this example, we will store the address data in a SQLite database.
By examining the host program (address.pls),we see the defintion for the ISAM file. The file contains records
and each record has a name, company, address, city, and phone field with each being fifty characters long.
Start the SchemaEditor. A menu selection on the Tools menu of the IDE provides access to the program
that is released in the "sunbelt\plbwin.95\code" directory.
Once the SchemaEditor is running, we need to create a schema database. Select "File" followed by "New" and navigate
the save dialog to your "sunbelt\plbwin.95\code" folder. You will notice that the file name is "sunschema.db".
That is the schema database name we used when we modified the runtime's configuration file. Click "Save" and
the database will be created along with all the necessary tables for both "Named IO" and "SQLIO". In this example,
we are working with SQLIO so we need to switch the SchemaEditor into that mode. Click the "SQLIO" tab. You will
note that the second set of tabs changes to reflect the available tables for SQLIO.
Once the SchemaEditor is in SQLIO mode, we need to define our file. From the SQL Files menu, select "Add" or right
click on the File panel and choose "Add". The "File Editor" is then displayed. Enter "address.txt" for the file name
and "address" for the table name. You may provide a description if you wish. Click the "Import PLB" button. We will
use the definitions found in our source program to create the column definitions in our schema. From "addrform.pls",
copy the following lines to the clipboard:
NAME DIM 50
COMPANY DIM 50
ADDRESS DIM 50
CITY DIM 50
PHONE DIM 50
Paste the lines into the "PLB Source" area of the import form and click "Import". The SchemaEditor will create
column definitions based on the source code provided. Now click the "Create" button and the file and column defintions
will be complete.
The address data file has one index that is based on the customer name field. We need to add that definition to the
schema. Click the "Indexes" tab. From the Indexes menu, click "Add" or right click on the Indexes panel and choose
"Add" and the Index Editor will be displayed. Enter "address.isi" for the ISI file name, "address_index" for the SQL
Index Name, and "name" for the index column. Once these values are defined, click the "Add" button.
The final step in configuring the schema database is to define the data source. Click on the Databases tab and let's
define the database where the address information will be stored. Begin by adding a name for the database such as
"Example". We will specify this name when we modify the program's OPEN and PREPARE statements in the next section.
Our database will be a SQLite database so the default driver and language settings are correct. In the "DBFILE parameters"
section, we need to tell SQLIO where the actual database resides. Provide a database file name such as "c:\temp\address.db"
and then click "Add".
Our schema database is now complete. The final step in implementing SQLIO is to modify the OPEN and PREPARE statements of
our sample application. In the code of the "addrform.plf" form file locate the OPEN statement and modify it as follows:
OPEN ADDRESS,"ADDRESS<sql Database=Example>"
Locate and modify the PREPARE statement as well.
PREP ADDRESS,"ADDRESS","ADDRESS<sql Database=Example>","50","250"
Note that the name specified in the <sql> tag matches the name we defined in the Databases section of the SchemaEditor.
That's it! All the modifications are complete. Compile and execute the program to test the functions. The database that
contains the actual data will be created if it doesn't already exist. If you want to verify
that the database is actually being used by the application, run the DBExplorer, open the database (c:\temp\address.db),
and view the data.
Let's now appreciate what you've done.
- The runtime modification is only done once.
- Defining files and indexes are done once for each file that SQLIO will manage and the SchemaEditor greatly simplifies that process.
- The change to the actual program was very minor. We did not have have to examine the logic of the program. SQLIO does all the work
of translating instructions such as READ, READS, WRITE, UPDATE, and DELETE for us.
With SQLIO, transisitioning your applications from flat files to a database has never been easier.
|
|
