![]() | ![]() | |||||||||||||||
|
Command Prompt Tutorial 3: Working with DatabasesIn 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" )
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
Example: Listing Tables in a Database
The db.schema procedure will list the contents of a table. => db.schema( "users" )
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
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
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 => select * from test where name \> 'c' > temp.txt
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 There's a lot more that the db package can do. Read the Packages section for a full description. Securing Your DatabaseSecurity 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
|
![]() 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 Template Tutorial 1: Basic Templates Template Tutorial 2: Intermediate Templates GCC Tutorial: Compiling SparForte Scripts Debugging Tutorial - Using the SparForte Debugger |
![]() |
![]() |