Andrew -- I've done this sort of thing before, though not with DB2. Still, the approach should be the same and follows along the lines you've already hinted at.
Fundamentally, you have to have some way of accessing DB2. If you don't have a native driver for DB2, then you can still make it work as long as you can (1) connect CF to Access and (2) connect Access to DB2. All you have to do is set up an Access database whose tables are nothing but "links" to the real DB2 tables (File, Get External Data, Link Tables, select File Type "ODBC Data Sources", select the data source you've set up to connect to the DB2 database, then select the tables). Then set up a datasource so that CF can talk to that Access database. At that point you can execute a CFQUERY against that datasource, and the SQL you specify will be interpreted by the Access "Jet" engine but the tables the SQL is operating on will actually be the DB2 tables. The other way you can do this, without hardcoding the linked tables into the Access database, is to simply send to the Access database a SQL command with the variant tablename syntax that includes all the ODBC parameters you need to drill through to the datasource you're interested in. Something like this: insert into [ODBC;DSN=datasourcename;SERVER=dbmsservername;UID=userid;PWD=password;DATABASE=dbname].tablename values(1,2,3) More recently, I've had to figure out how to get from my CF Pro (Linux) installation to a Sybase database without either upgrading to Enterprise Edition or buying the pricey Merant drivers. A bit of a challenge since I only had Sybase's Open Client for the Windows platform and CF Pro for Linux only comes with ODBC drivers for SQL Server (our primary database), mySQL, dBase/FoxPro, and postgreSQL, but I eventually figured out how to use SQL Server's "Linked Server" capability (sort of akin to Access' "linked tables" feature) to make it all come together. Have fun! -- LBA Andrew Peterson wrote: > Well, we just received our version of CF MX - only our purchasing dept > screwed up and got us the Pro version instead of the Enterprise version. > According to the docs, you can connect to DB2 using native drivers in the > Enterprise version, but there is no mention of connecting to DB2 using the > Pro version. I was wondering if there was a way to connect to DB2 using Pro > by accessing it in some way other than the native drivers found in > Enterprise. I figure there must be a way, since our internal users can > connect to DB2 from Access using DB2's Client Access Enabler and ODBC. > Forgive my ignorance in this area, but I could have sworn we did something > like this with CF 3.1 - without Enterprise. Any ideas? > > Thanks in Advance, > Andrew > > ______________________________________________________________________ This list and all House of Fusion resources hosted by CFHosting.com. The place for dependable ColdFusion Hosting. FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
