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), Guest_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,"POSTGRES"); dbupdate> create table guestbook (Guest_Name char(50), Email text, Address text, Country text, Guest_Comment text, Password char(61) ) </dbupdate dbupdate> create index guestindex on guestbook using btree (Guest_Name char16_ops) </dbupdate; else dbupdate> create table guestbook (Guest_Name char(50), Email char(50), Address char(60), Country char(60), Guest_Comment char(250), Password char(61) ) </dbupdate /if > Guestbook Table Created. <br>

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

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 provided that you are using the appropriate ODBC drivers.