We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you two Citrix podcasts. Learn about 2020 trends and get answers to your biggest Citrix questions!Listen Now

x

Postgres VB6 connection

Medium Priority
3,536 Views
Last Modified: 2012-05-06
I need a sample vb6 code to see how to connect to a postgres database, or other useful resources to help me with that problem.
Comment
Watch Question

I understand this isn't a complete answer but...

When I installed Postgres on my Windows machine, it created a "system dsn"

Control Panel-->Administrative Tools-->Data Sources  Then click on the system DSN tab.  If there isn't a data source defined for your database, you can click "ADD" and add one.  Once you have a DSN defined that is half the battle.  I'll try to post a link or two to tutorials later tonight (my time) unless someone beats me to it.

Regards,
Eric
hi cristi
try this code
strSQL = Driver={PostgreSQL ANSI}; Server=localhost; PORT=5432; " _ 
      & "Database=" & gstrPGDB & "; UID=user; PWD=pass"
debug.print strSQL
connPG.Open strSQL  ' <----- Add a break point here, and verify that strSQL contains 
                    '        the exact database name. 
...
Set rsPractice = New ADODB.Recordset 'Practice file
rsPractice.Cursorlocation = adUseServer
strSQL = "SELECT * FROM public.practice"
rsPractice.Open strSQL, connPG, adOpenStatic, adLockPessimistic
debug.print "rsPractice.EOF = " & rsPractice.EOF
debug.print rsPractice.Fields!address1

Open in new window

BTW. IF you log on as postgres (the default superuser logon,) you can drop the practice database.

Author

Commented:
I'm not able to find any documentation for the functions exposed by pgoledb.dll (i don't know what methods and what properties have the objects).

I would like a complete example to indicate me what references should i add in my VB6 project.

I have installed Postgres 8.3 and i use Visual Basic 6.

I realized that the ODBC connection is extremely slow for databases having the size of 2GB. I need to replace MS Access, but the ODBC connection is out of discution. I practically need a substitute for DAO 3.6.

Ideal would be to connect to a Postgres database located on a Linux Server with a VB6 program using its IP and port  (so preferable without installing Samba), but no way through ODBC.

Thank you.
I realize that ODBC can be slower for large databases but I'm not sure what the difference would be on a database that is well-indexed.  It might be worth the time to benchmark it just to see.

I got the code below from the PostgreSQL forums.  (http://www.postgresqlforums.com)  As for connecting to a database on a Linux box I'm not sure the syntax but I would first try using the IP address of it instead of "localhost"  (see below).  You will also have to check the PORT to make sure it is running on that port for Linux.
    Attribute VB_Name = "Module1"
    Sub Main()
    Dim cn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
 
      'Open the connection
      cn.Open "Driver={PostgreSQL ANSI};" & _
              "Server=111.111.111.111;" & _
              "Port=5432;" & _
              "UID=<Your Username>;" & _
              "PWD=<Your Password>;" & _
              "Database=test"
 
      'Open cursor
      rs.Open "SELECT field1, field2, field3 FROM mytable ", cn, adOpenForwardOnly, adLockReadOnly
 
      'Loop though the recordset and print the results
      While Not rs.EOF
        Debug.Print rs!field1 & "," & rs!field2 & "," & rs!field3
        rs.MoveNext
      Wend
 
      'Cleanup
      If rs.State <> adStateClosed Then rs.Close
      Set rs = Nothing
      If cn.State <> adStateClosed Then cn.Close
      Set cn = Nothing
    End Sub

Open in new window

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.