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

The problem still occurs after making the suggested changed. I'm pretty sure
that the problem isn't with the connection script as a similar script works
when I run it on the same box under windows scripting host. As you can see
below, both script use the same connection string.

Daniel.

-----Original Message-----
From: pgsql-odbc-owner(at)postgresql(dot)org
[mailto:pgsql-odbc-owner(at)postgresql(dot)org] On Behalf Of Chris Gamache
Sent: Friday, 11 April 2003 10:39 PM
To: Daniel F Garcia; pgsql-odbc(at)postgresql(dot)org
Subject: Re: [ODBC] 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=ui
> d;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

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faqs/FAQ.html