PostgreSQL Connection

am 10.10.2004 02:30:59 von Popeye

Hi,

I am trying to connect to a PostgreSQL database using asp. The code is as
follows:

<%
dim rec1
dim strsearch
dim strConnect

strConnect="DRIVER={PostgreSQL};SERVER=localhost;DATABASE=SI C;UID=sa;PWD=**********;Socket=4096" set rec1=server.createobject("adodb.recordset") strsearch = "SELECT SIC, Description FROM tblSIC" rec1.open strsearch,strconnect,adopenforwardonly,adlockreadonly response.write "

Description" while not rec1.eof response.write "" Response.Write "" Response.Write "" Response.Write "" x = x + 1 rec1.movenext wend Response.Write "

SIC
" & rec1("SIC") & "	" & rec1("Description") & "
" if x = 0 then Response.Write "No records were found" elseif x = 1 then Response.Write x & " record found" else Response.Write x & " records found" end if Response.Write "

" rec1.close set rec1=nothing%>When I open the page I get the following error message.Microsoft OLE DB Provider for ODBC Drivers error '80004005'ERROR: relation "tblsic" does not exist/dev.asp, line 20I have checked everything out and all seems correct. The connection stringworks fine in a UDL file but it doesn't seem to work in asp. I can evencreate a dsn and export data from access to tblSIC with no problems. A pointto note is that somehow the asp code is looking for tblsic instead oftblSIC.Has anybody else got any experience of connecting to PostgreSQL and if socould they supply any pointers in trying to resolve this issue.RegardsPaul

Re: PostgreSQL Connection

am 10.10.2004 02:48:28 von Popeye

Sorry about the formatting, not sure what happened.

"Paul" wrote in message
news:7p%9d.5737$xb.2867@text.news.blueyonder.co.uk...
> Hi,
>
> I am trying to connect to a PostgreSQL database using asp. The code is as
> follows:
>
> <%
> dim rec1
> dim strsearch
> dim strConnect
>
> strConnect="DRIVER={PostgreSQL};SERVER=localhost;DATABASE=SI C;UID=sa;PWD=**********;Socket=4096"
> set rec1=server.createobject("adodb.recordset") strsearch = "SELECT SIC,
> Description FROM tblSIC" rec1.open
> strsearch,strconnect,adopenforwardonly,adlockreadonly response.write
> "

Description" while
> not rec1.eof response.write "" Response.Write "" Response.Write "" Response.Write "" x = x + 1
> rec1.movenext wend Response.Write "

> width=120>SIC
" & > rec1("SIC") & "	" & > rec1("Description") & "
" if x > = 0 then Response.Write "No records were found" elseif x = 1 then > Response.Write x & " record found" else Response.Write x & " records > found" end if Response.Write "

" rec1.close set
> rec1=nothing%>When I open the page I get the following error
> message.Microsoft OLE DB Provider for ODBC Drivers error '80004005'ERROR:
> relation "tblsic" does not exist/dev.asp, line 20I have checked everything
> out and all seems correct. The connection stringworks fine in a UDL file
> but it doesn't seem to work in asp. I can evencreate a dsn and export data
> from access to tblSIC with no problems. A pointto note is that somehow the
> asp code is looking for tblsic instead oftblSIC.Has anybody else got any
> experience of connecting to PostgreSQL and if socould they supply any
> pointers in trying to resolve this issue.RegardsPaul
>

Re: PostgreSQL Connection

am 10.10.2004 14:24:42 von reb01501

Paul wrote:
> Hi,
>
> I am trying to connect to a PostgreSQL database using asp. The code
> is as follows:

I am not familiar with PostgreSQL, but i do have a couple comments.
>
> <%
> dim rec1
> dim strsearch
> dim strConnect
>
> strConnect="DRIVER={PostgreSQL};

Is there a native OLEDB provider for PostgreSQL that you could try instead
of ODBC? If so, I would try that first.

> SERVER=localhost;DATABASE=SIC;UID=sa;PWD=**********;Socket=4 096"

You should not use your system administration account for your applications.

> set rec1=server.createobject("adodb.recordset") strsearch = "SELECT
> SIC, Description FROM tblSIC" rec1.open
> strsearch,strconnect,adopenforwardonly,adlockreadonly response.write
> "

> width=120>SICDescription"
> while not rec1.eof response.write "" Response.Write " > align=center>" & rec1("SIC") & "" Response.Write " > align=center>" & rec1("Description") & "" Response.Write
> "" x = x + 1 rec1.movenext wend Response.Write " > colspan=4 align=left>
" if x = 0 then Response.Write "No records
> were found" elseif x = 1 then Response.Write x & " record found"
> else Response.Write x & " records found" end if Response.Write
> "

" rec1.close set rec1=nothing%>When I open the
> page I get the following error message.Microsoft OLE DB Provider for
> ODBC Drivers error '80004005'ERROR: relation "tblsic" does not
> exist/dev.asp, line 20I have checked everything out and all seems
> correct. The connection stringworks fine in a UDL file but it doesn't
> seem to work in asp.

The connection string does not appear to be the problem. You aren't getting
a connection faliure are you? You're getting an error after the connection
has been established. Does PostgreSQLhave any database mangement tools to
allow you to view connections or trace commands sent to it? If so, I would
use them to determine what is going on.

> I can evencreate a dsn and export data from
> access to tblSIC with no problems. A pointto note is that somehow the
> asp code is looking for tblsic instead of
>tblSIC.

Most databases I've worked with are case-insensitive. Is
PostgreSQLdifferent?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: PostgreSQL Connection

am 11.10.2004 00:32:10 von Popeye

"Bob Barrows [MVP]" wrote in message
news:uwWTfQsrEHA.3288@TK2MSFTNGP12.phx.gbl...
> Paul wrote:
>> Hi,
>>
>> I am trying to connect to a PostgreSQL database using asp. The code
>> is as follows:
>
> I am not familiar with PostgreSQL, but i do have a couple comments.
>>
>> <%
>> dim rec1
>> dim strsearch
>> dim strConnect
>>
>> strConnect="DRIVER={PostgreSQL};
>
> Is there a native OLEDB provider for PostgreSQL that you could try instead
> of ODBC? If so, I would try that first.
>
>> SERVER=localhost;DATABASE=SIC;UID=sa;PWD=**********;Socket=4 096"
>
> You should not use your system administration account for your
> applications.
>
>> set rec1=server.createobject("adodb.recordset") strsearch = "SELECT
>> SIC, Description FROM tblSIC" rec1.open
>> strsearch,strconnect,adopenforwardonly,adlockreadonly response.write
>> "

>> width=120>SICDescription"
>> while not rec1.eof response.write "" Response.Write " >> align=center>" & rec1("SIC") & "" Response.Write " >> align=center>" & rec1("Description") & "" Response.Write
>> "" x = x + 1 rec1.movenext wend Response.Write " >> colspan=4 align=left>
" if x = 0 then Response.Write "No records
>> were found" elseif x = 1 then Response.Write x & " record found"
>> else Response.Write x & " records found" end if Response.Write
>> "

" rec1.close set rec1=nothing%>When I open the
>> page I get the following error message.Microsoft OLE DB Provider for
>> ODBC Drivers error '80004005'ERROR: relation "tblsic" does not
>> exist/dev.asp, line 20I have checked everything out and all seems
>> correct. The connection stringworks fine in a UDL file but it doesn't
>> seem to work in asp.
>
> The connection string does not appear to be the problem. You aren't
> getting a connection faliure are you? You're getting an error after the
> connection has been established. Does PostgreSQLhave any database
> mangement tools to allow you to view connections or trace commands sent to
> it? If so, I would use them to determine what is going on.
>
>> I can evencreate a dsn and export data from
>> access to tblSIC with no problems. A pointto note is that somehow the
>> asp code is looking for tblsic instead of
>>tblSIC.
>
> Most databases I've worked with are case-insensitive. Is
> PostgreSQLdifferent?
>
> Bob Barrows
>
> --
> Microsoft MVP - ASP/ASP.NET
> Please reply to the newsgroup. This email account is my spam trap so I
> don't check it very often. If you must reply off-line, then remove the
> "NO SPAM"
>

Thanks for the pointers, I did manage to find an OLEDB driver and it
connected OK although it is very slow. The drivers are open source
development ones so I may have to look at using another DB platform.

The sa account is not a true sa account. The default sy admin account for
PostgreSQL is actually called postgres. I just created the sa account for
that specific database.

Thnaks for your help.

Paul