Using ColdFusion to Create a Data Source

The MERANT ODBC drivers that ship with all UNIX versions of ColdFusion include a FoxPro 2.5/dBase driver you can use to create a database file in a CFQUERY with standard SQL syntax even if you don't have an Oracle, Informix, Sybase, or DB2 database.

Note See Appendix A in the MERANT DataDirect ODBC Drivers manual for details about SQL statements used for flat file drivers.

The following example creates tables in a data source called newtable. This example uses a coffee inventory model as a theme for the data. Before you can run this code, you need to create the newtable data source in the ColdFusion Administrator, specifying the MERANT dBase/FoxPro ODBC driver. If you don't create the data source, you'll receive an error when you try to execute this page. This example generates the following tables in the newtable data source.

Fields created in the Beans1 table
Field Data type
Bean_ID numeric
Name char
Price char
Date date
Descript char

<HTML>
<HEAD>
    <TITLE>DBASE Table Setup</TITLE>
</HEAD>
<BODY>

<!---        
Before running this code, you need to create the 
newtable data source in the ColdFusion Administrator, 
specifying the Merant dBase/FoxPro ODBC driver. 
--->

<CFQUERY NAME=xs DATASOURCE="newtable"> 
    CREATE TABLE Beans1 ( 
        Bean_ID numeric(6), 
        Name char(50), 
        Price char(50), 
        Date date,</P> 
        Descript char(254))
</CFQUERY>

<CFQUERY NAME=xs DATASOURCE="newtable">
    INSERT INTO Beans1 VALUES ( 
        1,</P> 
        'Kenya', 
        '33', 
        {ts '1999-08-01 00:00:00.000000'}, 
        'Round, rich roast')
</CFQUERY>

<CFQUERY NAME=xs DATASOURCE="newtable"> 
    INSERT INTO Beans1 VALUES ( 
        2, 'Sumatra', 
        '21', 
        {ts '1999-08-01 00:00:00.000000'}, 
        'Complex flavor, medium-bodied')
</CFQUERY>

<CFQUERY NAME=xs DATASOURCE="newtable"> 
    INSERT INTO Beans1 VALUES ( 
        3, 'Colombia', 
        '89', 
        {ts '1999-08-01 00:00:00.000000'}, 
        'Deep rich, high-altitude flavor')
</CFQUERY> 

<CFQUERY NAME=xs DATASOURCE="newtable"> 
    INSERT INTO Beans1 VALUES ( 
        4,</P> 
        'Guatamala', 
        '15', 
        {ts '1999-08-01 00:00:00.000000'}, 
        'Organically grown')
</CFQUERY> 

<CFQUERY NAME=xs DATASOURCE="newtable"> 
    CREATE UNIQUE INDEX Bean_ID on Beans1 (Bean_ID)
</CFQUERY>

<CFQUERY NAME=""QueryTest2"" DATASOURCE="newtable"> 
    SELECT * FROM Beans
</CFQUERY>

<CFOUTPUT QUERY=""QueryTest2""> 
    #Bean_ID# #Name#<br>
</CFOUTPUT>

</BODY>
</HTML>