docs.roxen.comView this page in a printer friendly mode
DocsRoxen2.1TutorialsDatabase Tutorial
Copyright  2001, Roxen Internet Software
Suggestions, comments & compliments

Installing MySQL
Building a Sample Database
The query() function
The big_query() function
SQL Syntax
Features Missing from MySQL
Insertion Syntax
The tablify Container
The Business Graphics Module
The emit and sqlquery Tags
Database Creation
Creating Tables

Database Tutorial

This section of the manual deals with how Roxen and Pike can connect to SQL databases, retrieve data and modify the data stored there. It doesn't aim at teaching SQL or how to design a database, save for very simple cases, which are not surprisingly the most common in normal Web-related programming tasks. So you won't find references to triggers, stored procedures, referential integrity or complex privileges management here: they CAN be used from Pike, but they're more of an SQL matter, which is out of the scope of this manual.

Pike and Roxen offer an uniform layer to access all the supported databases. However such a layer does not cover anything but issuing queries and retrieving data. SQL is unfortunately another matter: it is an ANSI standard, but just about every SQL server has its own dialect, which may be a subset or a superset of the standard. You'll need to check your server of choice's documentation about its version of SQL.

This section of the manual tries to be a reference for both Roxen and Pike programmers. To do so, most examples will be available in two versions, a Pike snippet of code, and RXML code.


The RXML <sqloutput>, <sqltable> and <sqlquery> are slowly being phased out in favour of the new <emit> container, with "sql" as data-source. In this manual the 'old' tags are used, but the new tag is briefly introduced on the The emit and sqlquery Tags page.


Introduction to MySQL

MySQL by TcX AB is a simple SQL server, very popular among web-designers. It is a relatively simple and lightweight server, which aims at being very fast, but is not fully ANSI-SQL compliant, as it doesn't support features such as triggers or sophisticated access control.

Since MySQL is so popular among web-developers, it was chosen as the reference RDBMS for Roxen. This chapter will introduce you to it, and to some of the pitfalls most easily encountered when using it. The examples shown are however as cross-platform as they could be: they should work with any SQL server which claims at least a partial degree of ANSI-SQL compliance.


Querying a server is by far the most used DB-related functionality. Almost everything (in some cases, plain everything) you'll do when interacting with an SQL server goes through specifying correctly-formed SQL queries, sending them to a server and then interpreting the results the server sends back.

RXML offers three different ways to query a server, Pike offers two. This is needed to fit all situations: a query may yield results, or it might not, and the only way to tell the difference is by looking at the SQL code being executed by the server.

It would seem that programs (or RXML pages) accessing SQL resources are difficult and cryptic because the results queries can return are inherently dynamic in number and structure. Fortunately, very few programs need to handle the full range of possible outcomes from a query. In fact, most SQL queries are either non-interactive, or are parametric. This means they have a fixed structure where a few values (or no value if the query is non-interactive) change on each execution. This ensures that the results (or lack thereof) can be predicted accurately if not in number, at least in structure.

It is best to see SQL statements not as a foreign plug-in into a program's execution flow, but as an integral part of it. Whenever the data storage structure changes, the program must be changed according to it (this is why database design is such an important matter: a wrong database-design decision might end requiring an application rewrite almost from scratch).

Data Extraction

In this chapter we'll introduce how to perform data-extraction queries. We'll introduce the SQL syntax for data-extraction, and provide a few examples, both in RXML and in Pike.

Data Insertion

In this chapter we'll introduce how to insert data into a database.

Notice that data insertion and modification are two different operations, using two different SQL commands.

Using RXML Features with SQl Databases

In this chapter we'll examine how to exploit some RXML features when working with SQL databases.

The examples here contained are geared towards SQL-driven data-sources, but it is not of course the only use for them.

Database Maintainance

Up to this point we have assumed the databases to be already present for us. But this of course isn't the case in real-world situations.

Designing a database is a very complex task for nontrivial cases. It is also a very delicate operation: when dealing with data-storage-related applications, usually the application is built around the data, and not the other way around. So a bad data storage design will snowball, leading to a bad application design, which is very expensive to fix, going as far as a rewrite from scratch.

So for the umpteenth time we'll remark that if an application uses non-trivially organized data, the best solution is to hire someone to design the database.

In this chapter we'll examine how to build and delete a database, how to set the tables and indices up or remove them. We'll assume that the databse structure is so simple to be self-evident (which is often the case for web-related systems), database design won't be taken into account.

Also, the examples will be in pike-only: these activities are meant to be used only once at database-creation, and are really not suited for a web-based application.