Support for dynamic datasources was dropped on ColdFusion MX (it had been introduced in ColdFusion 5), and this has made it difficult to connect to data files without having to define a datasource. I was asked about this several times while in Japan, and promised to respond via e-mail with details on a workaround. Having just done so, I thought it worthwhile to share the info with others too. The trick is the use of the non-standard SQL keyword IN (not to be confused with the IN operator used in WHERE clauses) which allows a physical file name to be passed in a SQL statement. Macromedia TechNote http://www.macromedia.com/support/coldfusion/ts/documents/dynamic_dsn_cfmx.htm explains the workaround, and Microsoft page http://msdn.microsoft.com/library/default.asp?url=/library/en-us/off2000/html/acsqlINX.asp lists the exact syntax to be used for Access, Excel, DBF (dBase etc.), and Paradox.
Using SQL "IN" In Lieu of Dynamic Datasources
One response to “Using SQL "IN" In Lieu of Dynamic Datasources”
-
Ben,
Ok well this does work very well to pull data from excel. While I will to try not to make this a tech support question it deals directly with the subject. When using this method to get data from excel only cells that do not start with numerical characters are returned. It works on my local machine but not on the server. I was wondering if you could explain this. I am sure others will have this question.
Thanks,
Jason
Leave a Reply