Using ColdFusion to Create a Data Source (UNIX only)

The MERANT ODBC drivers that ship with all UNIX versions of ColdFusion include a FoxPro 2.5/dBASE driver. You can use the FoxPro 2.5/dBASE driver to create a database file in a cfquery with standard SQL syntax even if you do not have an Oracle, Informix, Sybase, or DB2 database.

Note

See the MERANT DataDirect ODBC Reference for details about SQL statements used for flat-file drivers. The default location of this reference on UNIX machines is: <installdir>/coldfusion/odbc/doc/odbcref.pdf. On Win32 machines, the default location is: <installdir>/cfusion/bin/odbcref.pdf.

You need to create tables in a data source called newtable.

To create a table in the data source:

Create the newtable data source in the ColdFusion Administrator, specifying the MERANT dBASE/FoxPro ODBC driver.
If you do not create the data source, you receive an error when you try to execute this page.

Use the following code to generate these fields in the newtable data source:


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>

Advanced ColdFusion Administration
Advanced Data Source Management