Thread: ColdFusion Remote PostgreSQL
Hi all, On a project at work, we are trying to go with PostgreSQL as our database instead of our original choice of Oracle (because Oracle on Linux is just too buggy). Our set up has the database on a separate server. Its running slackware Linux with PostgreSQL (OBDC enabled, along with TCL, perl, OpenSSL, Java and Syslog). Installation is done, the first database created and everything is working great on that end. Our other server, for the actual web side of things, is running Win2K with ColdFusion 5. I installed the PostgreSQL drivers and was able to add it to the system DNS with no problem. However, the ColdFusion Administrator doesn't recognize offer the PostgreSQL driver as an option in the datasource area. Now, when we put in the PostgreSQL datasource it replaced the testing one of Access we had been using, so CF can see it as a datasource, lists PostgreSQL as its driver, but is unable to connect to the datasource. How can I get ColdFusion to recognize the Postgre DNS connection and properly utilize it? I've searched the net but so far the only answers I've found have been for CF Linux on the same server as Postgre. Any help appreciated, since I'm quickly becoming a fan of Postgre and would love to use it for this project, Summer S. Wilson ICQ 26835530 Programmer/Analyst I, EIT TCE Webmaster, An Eclectic World http://eclectic-world.com
Summer S. Wilson wrote: > > Our other server, for the actual web side of things, is running Win2K with > ColdFusion 5. I installed the PostgreSQL drivers and was able to add it to > the system DNS with no problem. However, the ColdFusion Administrator > doesn't recognize offer the PostgreSQL driver as an option in the datasource > area. Now, when we put in the PostgreSQL datasource it replaced the testing > one of Access we had been using, so CF can see it as a datasource, lists > PostgreSQL as its driver, but is unable to connect to the datasource. How > can I get ColdFusion to recognize the Postgre DNS connection and properly > utilize it? I've searched the net but so far the only answers I've found > have been for CF Linux on the same server as Postgre. We are running production with CF 4.5.1 SP2 on Windows NT4 SP6a connected to PostgreSQL 7.1 by the 7.06 ODBC driver, and it runs like a dream (except for bind variables using <cfqueryparam>, I recommend thorough testing of them, we abolished them in the end). We have tested quite a bit with CF5 on W2KS, but haven't finalized yet. What you should do is create entirely new datasources for each PostgreSQL database you want to use. I have attached a cfml template that works for us to create new datasources. Just fill out everything you need in the first section and it should work. If you want a choice as to the name of the datasource you need to modify the template, currently the name of the datasource will be the same as the name of the database. After creation validation using CF will fail since that doesn't use a password. You should test using a real query so you can supply a password in the cfquery attributes or set a default attribute in CF. Let me know if you run into any problems. Jochem van Dieten Team Macromedia <cfset variables.servername = "tweetie.oli.tudelft.nl"> <cfset variables.name = "database/datasource name"> <cfset variables.driver = "C:\WINNT\System32\psqlodbc.dll"> <CFSET constCF_TO_ODBC_DriverName = "PostgreSQL"> <CFSET constODBCDriverName = "PostgreSQL"> <CFREGISTRY ACTION=SET BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\ODBC Data Sources" TYPE="STRING" ENTRY="#variables.name#"VALUE="#constODBCDriverName#"> <CFREGISTRY ACTION=SET BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI" TYPE="KEY" ENTRY="#variables.name#"> <CFSET BRANCH = "HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="ConnSettings" VALUE="" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Database" VALUE="#variables.name#" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Description" VALUE="PostgreSQL database connection" TYPE="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Driver" VALUE="#variables.driver#" TYPE="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="FakeOidIndex" VALUE="0" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Password" VALUE="" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Port" VALUE="5432" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Protocol" VALUE="6.4" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="ReadOnly" VALUE="0" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="RowVersioning" VALUE="0" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Servername" VALUE="#variables.servername#" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="ShowOidColumn" VALUE="0" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="ShowSystemTables" VALUE="0" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET ENTRY="Username" VALUE="" type="STRING" branch="#branch#"> <CFREGISTRY ACTION=SET TYPE="KEY" BRANCH="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources" ENTRY="#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="AllowAllSQL" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DisableConnections" VALUE="0" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="MaintainConnect" VALUE="0" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="AllowIUActions" VALUE="0" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DISABLE_SELECT" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DISABLE_UPDATE" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DISABLE_INSERT" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DISABLE_DELETE" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="DISABLE_{CALL" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="EnableUnboundText" VALUE="1" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="ConnectionTimeout" VALUE="30" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="LoginTimeout" VALUE="30" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="ConnectionPeriod" VALUE="" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="TextBufferSize" VALUE="65000" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="LimitConnections" VALUE="0" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="MaxConnections" VALUE="" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="UserID" VALUE="" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <CFREGISTRY ACTION=SET ENTRY="Password" VALUE="" TYPE="STRING" BRANCH ="HKEY_LOCAL_MACHINE\SOFTWARE\Allaire\ColdFusion\CurrentVersion\DataSources\#variables.name#"> <cfset temp = CFusion_Settings_Refresh()>