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

Daniel F Garcia.

Phone: +61 7 5461 1223   Fax: +61 7 5461 1221   Mobile: 0438 670 947
Email: dgarcia@BallSolutions.com
Mail: Level 2, 15 Gordon St, Ipswich Qld 4305
Web: http://www.BallSolutions.com

"Not everything that can be counted counts, and not everything that counts can be counted"
--Albert Enstein

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@ballsolutions.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

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@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Chris Gamache
Sent: Friday, 11 April 2003 10:39 PM
To: Daniel F Garcia; pgsql-odbc@postgresql.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@ballsolutions.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

In that case...

Try logging in as the Anonymous Internet User and running the .vbs.

Check all your permissions and ownership and "run as user"'s. That means files,
directories, COM+ objects, --->REGISTRY ENTRIES<--- (use regedt32), your COM+
isolated applications in "Component Services", etc.

Also, do you have Crystal Reports installed on the box?

CG

--- Daniel F Garcia <dgarcia@ballsolutions.com> wrote:
> 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@postgresql.org
> [mailto:pgsql-odbc-owner@postgresql.org] On Behalf Of Chris Gamache
> Sent: Friday, 11 April 2003 10:39 PM
> To: Daniel F Garcia; pgsql-odbc@postgresql.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@ballsolutions.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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


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