PostGres SQL ODBC connector

[slaughter]

This is manual for VB

********************************************************************

psqlODBC HOWTO - Visual Basic

Author: Dave Page (dpage@postgresql.org)

Release Date: 13 November 2001

Description: Example based Mini-Howto on Accessing PostgreSQL from Visual Basic

This document provides some sample code to get you started with Visual Basic & PostgreSQL.

Requirements to get the subroutines to work:

* Visual Basic 5/6

* A reference in the VB project to Microsoft ActiveX Data Objects

* A PostgreSQL datasource.

The example code shown below may need some modification to make it actually work in your environment. There is one table used in the example:

CREATE TABLE vbtest(

id int4,

data text,

accessed timestamp

);

Code

Sub Main()

Dim cn as New ADODB.Connection

Dim rs as New ADODB.Recordset

'Open the connection

cn.Open "DSN=<MyDataSourceName>;" & _

"UID=<MyUsername>;" & _

"PWD=<MyPassword>;" & _

"Database=<MyDatabaseName>"

'Clear the table

cn.Execute "DELETE FROM vbtest;"

'For updateable recordsets we would typically open a Dynamic recordset.

'Forward Only recordsets are much faster but can only scroll forward and

'are read only. Snapshot recordsets are read only, but scroll in both

'directions.

rs.Open "SELECT id, data, accessed FROM vbtest", cn, adOpenDynamic, adLockOptimistic

'Loop though the recordset and print the results

'We will also update the accessed column, but this time access it through

'the Fields collection. ISO-8601 formatted dates/times are the safest IMHO.

While Not rs.EOF

Debug.Print rs!id & ": " & rs!data

rs.Fields("accessed") = Format(Now, "yyyy-MM-dd hh:mm:ss")

rs.Update

rs.MoveNext

Wend

'Add a new record to the recordset

rs.AddNew

rs!id = 76

rs!data = 'More random data'

rs!accessed = Format(Now, "yyyy-MM-dd hh:mm:ss")

rs.Update

'Insert a new record into the table

cn.Execute "INSERT INTO vbtest (id, data) VALUES (23, 'Some random data');"

'Refresh the recordset to get that last record...

rs.Requery

'Get the record count

rs.MoveLast

rs.MoveFirst

MsgBox rs.RecordCount & " Records are in the recordset!"

'Cleanup

If rs.State <> adStateClosed Then rs.Close

Set rs = Nothing

If cn.State <> adStateClosed Then cn.Close

Set cn = Nothing

End Sub

Useful Functions

' The escapeString function can be used to fix strings for us in INSERT and

' UPDATE SQL statements. It will take a value, and return it ready for

' use in your statement as NULL, or quoted with backslashes and single quotes

' escaped.

Function escapeString(vValue As Variant) As String

If IsNull(vValue) Then

escapeString = "NULL"

else

escapeString = "'" & Replace(Replace(vValue, "", ""), "'", "''") & "'"

end if

End Function

*****************************************************

My script is:

***************************************

$sql = "INSERT INTO Incoming_rates (Destination, Area Codes, Price, Valid From) VALUES ('Albania Mobile', '0035538, 0035567, 0035568, 0035569', '0,1684', '26.11.07');"
postgres_query($sql)


Func postgres_query($query)
$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$oConn.Open ("DSN={PostgreSQL};UID=odbc_usr;PWD=odbc_usr;Database=Incoming_rates")
;~ $oConn.Open ("ODBC;DRIVER={PostgreSQL};DATABASE=Incoming_rates;SERVER=localhost;PORT=5432;Uid=odbc_usr;Pwd=odb

c_usr;A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=

0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_")
;~ $oRS.Open ($query, $oConn, 1, 3)
$oConn.Execute ($query, $oConn, 1, 3)
$oConn.Close
$oConn = 0
EndFunc

The error witch i get is

~~_read.au3 (55) : ==> The requested action with this object has failed.:

$oConn.Open ("DSN={PostgreSQL};UID=odbc_usr;PWD=odbc_usr;Database=Incoming_rates")

$oConn.Open ("DSN={PostgreSQL};UID=odbc_usr;PWD=odbc_usr;Database=Incoming_rates")^ ERROR

Pleas help me to sort it out...

[ptrex]

@slaughter

Maybe have look here on how to set the connection string.

Postgress

I can't test it because I don't have a postgress DB around.

regards,

ptrex

[slaughter]

@slaughter

Maybe have look here on how to set the connection string.

Postgress

I can't test it because I don't have a postgress DB around.

regards,

ptrex

thanks when i will be at work pc i will try. Is this right?

tryed:

$oConn.Open ("ODBC;DRIVER={PostgreSQL};DATABASE=Incoming_rates;SERVER=localhost;PORT=5432;Uid=odbc_usr;Pwd=od

b

c_usr;A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=

0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_")

and

$oConn.Open ("ODBC;DRIVER={PostgreSQL};DATABASE=Incoming_rates;SERVER=localhost;PORT=5432;Uid=odbc_usr;Pwd=od

b

c_usr")

or i just have use?

$oConn.Open ("ODBC;DRIVER={PostgreSQL};HOST=myServerAddress;DB=myDataBase;UID=myUsername;PWD=myPassword;PORT=

2055;")

or

$oConn.Open ("DRIVER={PostgreSQL};HOST=myServerAddress;DB=myDataBase;UID=myUsername;PWD=myPassword;PORT=2055;

")

whre i can read more abuot using object? and this functions?

Thanks to all auto it members for helping each other!!!

I will setup remote access to DB

host: KSG.lt

port: 5432

USR & Pass :odbc_usr

just i don't know can you access from remote ip or is this setuped for local because now I cant get in to Database server just got to firewall and redirected port to postgres server. Try and let me know. By tomorrow morning i will get hands fisical on pc so can do what need.

and you provided with incorrect link

this is correct

http://www.connectionstrings.com/default.a...rier=postgresql

it not progress its postgresql

Edited January 10, 2008 by slaughter

[ptrex]

@slaughter

If you want to know how to use "ADODB.Connection" in AU3,

You can use the search on this forum like this :

ADODB.Connection

regards,

ptrex

[slaughter]

Thanks all for help or ideas or just saying "make it by your self"

Done

PostGres

Func postgres_query($query)
$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
$cmd = ObjCreate("ADODB.Command")

$line2 =    "DRIVER={PostgreSQL ANSI};DATABASE=Incoming_rates;" & _
            "SERVER=localhost;PORT=5432;Uid=odbc_usr;" & _
            "Pwd=odbc_usr;" & _ 
            "A0=0;A1=6.4;A2=0;A3=0;A4=0;A5=0;A6=;A7=100;A8=4096;A9=0;" & _
            "B0=254;B1=8190;B2=0;B3=0;B4=1;B5=1;B6=0;B7=1;B8=0;B9=1;C0=0;C1=0;C2=dd_"

$oConn.Open ($line2)
$oConn.execute ($query)
$oConn.Close

EndFunc

[ptrex]

@slaughter

Good to see you got if running.

Seems that very few people use a postgress DB.

Otherwise the repons would have been greater.

regards

ptrex

[slaughter]

@slaughter

Good to see you got if running.

Seems that very few people use a postgress DB.

Otherwise the repons would have been greater.

regards

ptrex

PostGres is not popular among simple ussers. But when your database goes up milion entries its becoming dificult to control it and under MySQL or MS acces

PostGress hve tons of tools and functions and is one of fastest SQL engines on my test MYSQL vas faster on short queris with not much returned object but post gres took over on higher level. PostGress was faster on long queris and was returing large objects faster.

PostGress ir more for busnies