Integrating MySQL Access and coldfusion 1

[chidori]

Hi,

I've developed several coldfusion pages so far using Access. However because of size limitations I have decided to migrate to MYSQL.

I am a little confused about how everything works now. When I write SQL in the coldfusion webpages to search the access database, is Access actually doing anything? Or is the query actually being run by Coldfusion? Finally if and when I link Coldfusion to a mySQL database, do I have to be running mysql server in the background so that the queries can be executed?

Sorry if my questions are a little confusing, any comments would be appreciated.
I am basically trying to convert multiple access databases each with 300,000 records into one HUGE 1,000,000+ record database.

If I ever do get MYSQL to work, how do I get the database to register with the cold fusion ODBC data source...


Thanks in advance!

 

[pcorreia]

A little about how ColdFusion executes queries...

When you do a

<cfquery>

, ColdFusion takes the text of the query, maybe does some initial processing to format the query in the language that the target database understands, and passes the query (that is, the SQL text) to the database server (be it MySQL, SQL Server, Oracle, or Access) either through a native interface that connects ColdFusion directly to the database or (more commonly) using one or more transport services like ODBC and/or OLEDB. It's the database server that actually &quot;executes&quot; the query, meaning it interprets the language of the SQL query and uses it to find a set of rows that match the criteria. Then, the database server returns that data back through the interface (native, ODBC, etc.) to ColdFusion, which provides the data to you through the <cfoutput> tag. The only responsibility of ColdFusion in the whole process is to know how to communicate the SQL query to the database server, and to know what to do with the data the server returns. The actual mapping of a SQL statement to a result set is the sole responsibility of the database server. This is confused by the fact that ColdFusion versions 5 and up can execute queries against query result sets in memory (this is called Query of Queries or CFSQL). In that case (and only that case), ColdFusion is actually acting like a database server, and can understand SQL syntax and map a SQL query to a result set. Any time you're querying an external database, it's the database server that's doing the work of processing the SQL query.

So, when you query an Access database, it's Access that's doing the work. And when you switch to mySQL, it'll be MySQL doing the work. There will be a migration period, because the syntax of the SQL statement varies from database server to database server.

Notice the whole time that I've been calling Access a &quot;database server&quot;. This is just a convention that makes it easier to understand how the transaction works -- as you know, Access is not truly a &quot;server&quot; that runs in the background listening for requests. However, MySQL is a true &quot;server&quot; which must be running and actively listening for requests when you want to query one of its databases.

Unfortunately, I can't tell you much about setting up an ODBC connection to MySQL. The Macromedia documentation says that ColdFusionMX comes with built-in drivers for MySQL. Is there anyone else that can specifically address the setup issues involved?