Friday, June 30, 2006

ASP and PostgreSQL quick snippet

Hi, here is a small snippet to connect to PostgreSQL from an ASP page and run a select query.

  1. dim adoConn
  2. dim adoRS
  3. dim counter
  4. set adoConn = Server.CreateObject("ADODB.Connection")
  5. set adoRS = Server.CreateObject("ADODB.Recordset")
  6. strConn = "Provider=PostgreSQL.1;Data Source=localhost;location=postgres;" & _
  7. "User Id=login_role;password=login_role_password;"
  8. adoConn.Open strConn
  9. adoRS.ActiveConnection = adoConn
  10. if adoConn.errors.count = 0 then

  11. adoRS.Open "select * from phpbb.phpbb_users"
  12. while not adoRS.EOF
  13. response.write adoRS.fields(0).value
  14. adoRS.MoveNext
  15. wend
  16. else
  17. response.write "ERROR: Couldn't connect to database"
  18. end if

Probably the most interesting thing is the connectionstring where:
Localhost is the server
postgres is the database
login_role is ... the login role AKA username
and in the query phpbb is the schema.
To avoid access denied errors you'll have to grant proper rights to the phpbb schema to user login_role, like :

GRANT ALL privileges ON schema phpbb TO login_role;

No comments: