Re: ColdFusion Remote PostgreSQL - Mailing list pgsql-novice

From Jochem van Dieten
Subject Re: ColdFusion Remote PostgreSQL
Date
Msg-id 3BB0F562.6080305@oli.tudelft.nl
Whole thread Raw
In response to ColdFusion Remote PostgreSQL  ("Summer S. Wilson" <collectonian@eclectic-world.com>)
List pgsql-novice
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()>


pgsql-novice by date:

Previous
From: "Summer S. Wilson"
Date:
Subject: ColdFusion Remote PostgreSQL
Next
From: Yeo Eng Hee
Date:
Subject: Postgresql and OpenSSL 0.9.6x