[SparForte][Banner]
[Top Main Menu] Intro | Tutorials | Reference | Packages | Examples | Contributors   [Back Page]      [Next Page]  

Command Prompt Tutorial 3: Working with Databases

In order to work with database engines, you will need to install database software on your computer if you do not have one installed already. The engine is a program that stores, sorts and retrieves data. SparForte supports the PostgreSQL 7 or newer and MySQL 4 or newer.  (When you build SparForte from sources, you can select which database engines SparForte will support.) This tutorial uses the PostgreSQL database. To use MySQL in these examples, use the "mysql." prefix instead of "db.".

Database engines use a language called SQL.  Since most software applications don't understand SQL, a special client program or library is often provided with the database.  The MySQL database has a client program called mysql.  The PostgreSQL database has a program called psql.  However, SparForte doesn't require a client program: it understands basic SQL commands and the built-in database package provides most of the additional features of a client program.  (Note: Each engine uses a unique version of SQL.  To create complex SQL queries, consult your database documentation.)

Before you can use a database, connect to software using the connect procedure.  Connecting is similar to logging in: you will need the name of the database, a username and a password.  Some of this information may be optional.  In the simplest example, PostgreSQL will allow you to connect to an existing database with your login name by providing only the database name.

=> db.connect( "ken" )
=> ? db.is_connected
true
 

Example: Connecting to a Database

db.connect is a built-in AdaScript procedure in the db database package.  It is not an operating command: you cannot use Bourne parameters or redirect the output.

Database client programs do more than just run SQL commands.  They also know how to display information about the database, its users and its tables.  These commands are not a part of the SQL language.  SparForte's db package contains db,databases, db.list, db.users and db.schema procedures to display this kind of information.

The db.list procedure will list all the tables in a database.

=> db.list
 
 Schema | Name      | Type  | Owner
--------+-----------+-------+-------
 public | guestbook | table | root
 public | users     | table | ken
 2 Rows and 4 Columns
 

Example: Listing Tables in a Database

The db.schema procedure will list the contents of a table.

=> db.schema( "users" )
 
 Column | Type                  | Not Null | Default
--------+-----------------------+----------+---------
 name   | character varying(32) |          |
 age    | integer               |          |
 2 Rows and 5 Columns
 

Example: Listing Columns in a Database Table

To create a new table, use the db.prepare and db.execute procedures.  Use db.prepare to begin building a SQL command.  Additional lines can be added with db.append, db.append_line and db.append_quoted.  When the SQL command is finished, run the command with db.execute.

Before we begin, we'll start a transaction.

=> db.begin_work
=> db.prepare( "create table test (name varchar(20), age integer )" )
=> db.execute
=> db.list
 Schema | Name      | Type  | Owner
--------+-----------+-------+-------
 public | guestbook | table | root
 public | test      | table | ken
 public | users     | table | ken
 2 Rows and 4 Columns
 

Example: Creating a Table within a Transaction

The SQL command created a new table.  You can use db.prepare and db.execute to run most SQL commands.  For convenience, SparForte recognizes several basic SQL commands directly on the command prompt: alter (change a table's structure), insert (add a row to a table), delete (remove rows from a table), select (view rows) and update (change rows).

=> db.begin_work
=> insert into test values ('bob', 16 )
=> insert into test values ('joe', 78 )
=> select * from test
 
 name | age
------+------
 bob  | 16
 joe  | 78
 2 Rows and 2 Columns
 

Example: Inserting Rows into a Table

The new rows were added to the empty table.

The format of the SQL command, including how quoting is used, is determined by the database being used.  SparForte will not perform file globbing (otherwise select count(*) would give very strange results!).  SparForte will substitute variables with dollar sign expansion and the output from SQL commands can be redirected like an operating system command.  For this reason you'll have to escape characters like ">" with a backslash used with SQL on the command line.

=> select * from test > temp.txt
=> cat temp.txt
 
 name | age
------+------
 bob  | 16
 joe  | 78
 2 Rows and 2 Columns
 

=> select * from test where name \> 'c' > temp.txt
 
 name | age
------+------
 joe  | 78
 1 Row and 2 Columns
 

Example: Writing Rows from a Table to a File

In the case of the where clause, the ">" must be escaped to prevent SparForte from redirecting the output to a file called "c".

Since these command are in a transaction, you can discard the table by rolling back with db.rollback_work, or commit the changes using db.commit_work.

=> db.rollback_work

Database errors are reported in the same way as a SparForte error.

=> select * from test
select * from test;
                  ^ ERROR:  relation "test" does not exist

There's a lot more that the db package can do.  Read the Packages section for a full description.

Securing Your Database

Security is one of the biggest challenges, especially for web sites.

SparForte's strong typing can help ensure that your website is protected. For example, create new string types called "unvalidated_string" and "validated_string".

Then create your own versions of the database procedures like "append" that build a query string. In your version, require a validated_string parameter. Now SparForte's type system can check that only validated strings are used in database queries.

There is an example of how to do this with HTML content in the Basic Templates tutorial.

Study Questions

  1. Can you run a query for two different databases engines at the same time?
  2. What does db.schema do?
  3. How can you use SparForte variables in a SQL query?
 
[Right Submenu]

 Command Prompt Tutorial 1: SparForte as a Calculator

 Command Prompt Tutorial 2: Basic Shell Commands

 Command Prompt Tutorial 3: Working with Databases

 Script Tutorial 1: Basic Commands Scripts

 Script Tutorial 2: Intermediate Program Scripts

 Script Tutorial 3: Data Types

 Template Tutorial 1: Basic Templates

 Template Tutorial 2: Intermediate Templates

 GCC Tutorial: Compiling SparForte Scripts

 Debugging Tutorial - Using the SparForte Debugger

 Creating a Profile Script

 Calling SparForte from C: A Tutorial

 SparForte For PHP Developers

 SparForte Best Practices

[Back to Top] Back To Top [Small Forte Symbol]