HostMySite.Com is sponsoring this tutorial, please visit their site today!

Creating, Altering and Deleting database tables with ColdFusion. by: Pablo Varando

[Download in PDF Format] [Download in FlashPaper Format]

Creating, Deleting and modifying database tables on the fly with ColdFusion.

This tutorial will demonstrate how to create tables in your database using your ColdFusion applications! Let's begin:

the Create table statement creates a new database table in the specify datasource.
(Please note : syntax varies from database to database. [ This code has been tested in Access and SQL Server 2000 ])

<!--- This will create the tables on the database --->
<cfquery name="qCreateTable"  datasource="MyDSN">
    CREATE TABLE Members (
                            member_id counter Primary Key,
                            member_fname varchar(255),
                            member_lname varchar(255),
                            member_age integer,
                            member_visits numeric,
                            CONSTRAINT ID PRIMARY KEY(ID)
                            )
</cfquery>

The next thing you need to learn is how to delete a table on the fly, you do this with the "DROP TABLE" command. The Drop statement deletes an existing table (including all data) in the specify datasource.

<!--- This will completely delete a table and all items in it --->
<cfquery name="qDeleteTable" datasource="MyDSN">
        DROP TABLE Members 
</cfquery>

The next step is to modify fields in a table with the ALTER TABLE statement:

<!--- This will alter a column in the table --->
<cfquery name="qAlterTable" datasource="MyDSN">
        ALTER TABLE Members
        ALTER COLUMN member_visits Long
</cfquery>

<!--- This will alter a table by deleting the column --->
<cfquery name="qDeleteFieldFromTable" datasource="MyDSN">
        ALTER TABLE Members
        DROP COLUMN member_visits
</cfquery>

Question? Comments? Email me....

HostMySite.Com is sponsoring this tutorial, please visit their site today!

Please rate this tutorial:

Post a new comment on this tutorial
post a new comment on this particular tutorial
Your Name:
Your Email:
Comment Title:
Comments:
Key Phrase: