Creating SQL Tables

Here's a code sample that shows how to create new Tables:

Creating an SQL Table using dbupdate
<dbupdate> CREATE TABLE tablename (Guest_Name char(50), Email char(50), Address char(60), Country char(60), Comment char(250), Password char(61)) </dbupdate>

As you can see, the dbupdate Tag is an Environment Tag, the body of which contains an SQL command. In this case the command is CREATE TABLE tablename, where tablename is any valid name accepted by your particular SQL software. Also, a list of Data Fields is declared, each field of which is composed of a Field Name, its corresponding Data Type, and an integer representing the maximum number of spaces or characters reserved for the field.

The above code fragment contains Field Names that might appear in a simple Guestbook application. It should prove to be compatible with virtually any SQL software package, as it strictly adheres to the SQL standard. Although it is beyond the scope of this text to discuss the differences between various SQL packages, we will point out that cross-platform applications can easily be developed with heitml.

Many vendors add their own extensions to SQL, either as a means of filling what they perceive to be gaps in the standard SQL functionality, or simply as a means of differentiating their product from those of other vendors. Although many of these extensions prove to be quite useful, they can also pose problems to developers. In order to help you avoid such problems, we have provided an example below which shows how to use the if Tag in conjunction with the Contains() function and the SrvDbsys Server Variable to exploit (or conform to) the capabilities of some popular UNIX and Windows SQL packages.

Creating an SQL Table: A cross-platform example.

<if contains(SrvDbsys,"ADABAS"); dbupdate> create table guestbook (Guest_Name varchar(50), Email varchar(50), Address varchar(60), Country varchar(60), Comment varchar(250), Password char(61) ) </dbupdate; dbupdate> create unique index guestindex on guestbook (Guest_Name) </dbupdate; elsif contains(SrvDbsys,"POSTGRES"); dbupdate> create table guestbook (Guest_Name char(50), Email text, Address text, Country text, Comment text, Password char(61) ) </dbupdate dbupdate> create index guestindex on guestbook using btree (Guest_Name char16_ops) </dbupdate; elsif SrvDbsys=="MSQL"; dbupdate> create table guestbook (Guest_Name char(50) primary key, Email char(50), Address char(60), Country char(60), Comment char(250), Password char(61) ) </dbupdate elsif SrvDbsys=="MSQL2"; dbupdate> create table guestbook (Guest_Name char(50), Email char(50), Address char(60), Country char(60), Comment text(50), Password char(61) ) </dbupdate dbupdate> create index guestindex on guestbook ( Guest_Name ) </dbupdate; else dbupdate> create table guestbook (Guest_Name char(50), Email char(50), Address char(60), Country char(60), Comment char(250), Password char(61) ) </dbupdate /if > Guestbook Table Created. <br>

Notice that the ADABAS database package not only supports the char Data Type, but also varchar , a more efficient Data Type that stores data in variable length fields, thus saving storage that would otherwise be filled with blank spaces or null characters. We have also created an index table on the Guest_Name data field, which means that we can access records more quickly, without having to read through the table sequentially to find specific records.

The PostgreSQL package supports an even more efficient indexing system based on the concept of Binary Trees, so our index table was modified to make use of it.

MSQL is widely popular on UNIX systems, but it is a very limited, non-standard implementation which supports only a single key field, which must be indicated at the time the file is created. MSQL2 is a more recent and complete implementation, as well as a more standard one.

The final "else" statement is executed only when none of the above-mentioned SQL packages are found. As you can see, this definition is the same as the one we shown at the beginning of this discussion, and it should work with any package, including MS Access, dBASE, or Paradox on Windows systems, provided that you are using the appropriate ODBC drivers.