Re: Connection to posgresql database works under windows scripting host but not IIS/ASP

First, to create a connection string, create a file with a udl extension. An
easy way to do this is right-click and create a new text document, then rename
it. Click on the icon and the universal data link applet will appear. Create a
link to PostgreSQL. Close the applet, then open the document in notepad. You'll
find a full-blown connection string pre-made for you. I usually remove the DSN
reference and user DRIVER={drivername} instead. You might have to add a
PWD=password in the extended properties too...

I've found that if I pass "pointers" (if you can call a variant reference to
another variant a pointer!) to recordsets and connection objects within
functions I get unpredictable results (memory leaks, crashes, etc.). If you're
going to use "conn" or "rs" outside your function you might be better off to
declare the objects in the main scope of the script. "Option Explicit" is also
a good idea. It helps by forcing you to declare all variables.

Here's a revised function that should work for you (with conn and rs only
within the scope of your script):

Function nugget_forum(column)
start_nugget column,"::Rumour Mill"

'Server.createobject is better suited to all COM+
'references in ASP. I think this is your main problem...
Set conn = Server.CreateObject("adodb.connection")

' Here's a plain connection string. modify to suit...
connstr = "Provider=MSDASQL.1;" & _
"Persist Security Info=False;Extended " & _
"Properties=""DRIVER={PostgreSQL+ (Beta)};" & _
"DATABASE=whatever;SERVER=10.11.2.5;PORT=5432;" & _
"UID=uid;PWD=pwd;ReadOnly=0;Protocol=6.4;FakeOidIndex=0;" & _
"ShowOidColumn=0;RowVersioning=0;ShowSystemTables=0;" & _
"ConnSettings=;Fetch=100;Socket=4096;UnknownSizes=0;" & _
"MaxVarcharSize=254;MaxLongVarcharSize=8190;Debug=0;" & _
"CommLog=0;Optimizer=1;Ksqo=1;UseDeclareFetch=0;" & _
"TextAsLongVarchar=1;UnknownsAsLongVarchar=0;" & _
"BoolsAsChar=1;Parse=0;CancelAsFreeStmt=0;" & _
"ExtraSysTablePrefixes=dd_;;LFConversion=1;" & _
"UpdatableCursors=1;DisallowPremature=0;TrueIsMinus1=0;BI=0"""
conn.Open connstr
'Set rs = server.CreateObject("adodb.recordset")
'querystr = "SELECT * FROM ib_forum_posts;"
'rs.open querystr, conn,3,3
Conn.close
end_nugget(column)
end Function

One more tip: concatenation (string & string & string) and rs.movenext will
give you a huge performance hit. Consider using a complex postgresql query and
rs.getstring if you're just outputting a table of data. If you HAVE to munch
each row, use rs.getrows, which returns an array of the table. Much faster.

CG

--- Daniel F Garcia <dgarcia(at)ballsolutions(dot)com> wrote:
> Environment:
> =========
> Windows 2000 server running IIS. This server is a domain controller. This
> server is runnning version 7.2.5 of the postgres odbc driver.
> Debian Linux server running a postgres database
>
> ASP script:
> ========
>
> Function nugget_forum(column)
> start_nugget column,"::Rumour Mill"
> Set conn = CreateObject("adodb.connection")
> conn.ConnectionString =
> "DRIVER={PostgreSQL};sERVER=10.11.2.5;port=5432;DATABASE=dbname;UID=uid;PWD=
> pwd;"
> conn.Open
> 'Set rs = CreateObject("adodb.recordset")
> 'querystr = "SELECT * FROM ib_forum_posts;"
> 'rs.open querystr, conn,3,3
> Conn.close
> end_nugget(column)
> end Function
>
> script run under windows scripting host
> ==========================
>
> When I run this script under windows scripting host I get a connection and
> am able to retrieve data.
>
> Set conn = CreateObject("adodb.connection")
> conn.ConnectionString =
> "DRIVER={PostgreSQL};sERVER=10.11.2.5;port=5432;DATABASE=dbname;UID=uid;PWD=
> pwd;"
> conn.Open
> Set rs = CreateObject("adodb.recordset")
> querystr = "SELECT * FROM ib_forum_posts;"
> rs.open querystr, conn,3,3
> do while not rs.eof
> wscript.echo rs.Fields(0).value & ":" & rs.Fields(1).value & ":" &
> rs.Fields(12).value
> rs.MoveNext
> loop
> Conn.close
>
> The error
> =======
>
> When I run the script in an ASP I get the following error.
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> [Microsoft][ODBC Driver Manager] Data source name not found and no default
> driver specified
> /intranet/default.asp, line 35
>
> Line 35 is the conn.open statement
>
>
> I initally thought that this might be a permissions problem. I added
> IUSR_SRV-AMB-DB temporarily to the domain admin group and restarted IIS, but
> it didn't make a difference.
>
> I'm at a loss. Can someone please help me.
>
> Best Regards,
>
> Daniel F Garcia.
>

__________________________________________________
Do you Yahoo!?
Yahoo! Tax Center - File online, calculators, forms, and more
http://tax.yahoo.com