PostGres SQL Open a query

[slaughter]

Stil working on PostGres SQL

I dont have any problems with execute but when i nead to return data from database i get some problems!

This explame is what i have working fine for now:

Func postgres($query)
$var = "server.cfg"
$pg_host = IniRead($var, "Server", "1", "Klaida")
$pg_db = IniRead($var, "Server", "2", "Klaida")
$pg_usr = IniRead($var, "Server", "3", "Klaida")
$pg_pas = IniRead($var, "Server", "4", "Klaida")
$oConn = ObjCreate("ADODB.Connection")
$line2 =    "DRIVER={PostgreSQL ANSI};DATABASE="& $pg_db &";" & _
            "SERVER="& $pg_host &";PORT=5432;Uid="& $pg_usr &";" & _
            "Pwd="& $pg_pas &";" & _ 
            "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)
If IsObj($oConn) Then
        Return $oConn.execute ($query)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf
$oConn.Close
EndFunc

Here is what is not working as I espected.

expandcollapse popup

Func postgres_query($query)
$var = "server.cfg"
$pg_host = IniRead($var, "Server", "1", "Klaida")
$pg_db = IniRead($var, "Server", "2", "Klaida")
$pg_usr = IniRead($var, "Server", "3", "Klaida")
$pg_pas = IniRead($var, "Server", "4", "Klaida")
$oConn = ObjCreate("ADODB.Connection")
$oRS = ObjCreate("ADODB.Recordset")
;~ $cmd = ObjCreate("ADODB.Command")
$line2 =    "DRIVER={PostgreSQL ANSI};DATABASE="& $pg_db &";" & _
            "SERVER="& $pg_host &";PORT=5432;Uid="& $pg_usr &";" & _
            "Pwd="& $pg_pas &";" & _ 
            "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)
If IsObj($oConn) Then
        Return $oRS.Open ($query,$oConn)
    EndIf
    If @error Then
        SetError(1)
        Return 0
    EndIf
$oConn.Close
EndFunc

func get_id($what,$data)
if $what = "area" Then
Dim $data[5]
$sql_x = "SELECT * FROM area_codes WHERE destination LIKE '"& $data &"' LIMIT 1;"
$aQuery = postgres_query($sql_x)
With $aQuery                                                      <--- sems error in here
While NOT .EOF
$data[1] = .Fields("code").value 
$data[2] = .Fields("destination").value 
$data[3] = .Fields("comments").value
$data[4] = .Fields("is_bad_code").value
.MoveNext
WEnd
EndWith
_ArrayDisplay($data)
EndIf

get_id("area", "Afganistan")

And get error like:

Only Object-type variables allowed in an "With" statement.

There is documentation for languages:

# Example based Mini-Howto on Accessing PostgreSQL from C#

# Example based Mini-Howto on Accessing PostgreSQL from Ch

# Using large objects in Microsoft Access (notes from the original psqlODBC docs)

# Example based Mini-Howto on using Microsoft Access VBA with PostgreSQL

# Example based Mini-Howto on Accessing PostgreSQL from Visual Basic <-- Posted this

# Example based Mini-Howto on Pgsql Large Objects Interface and Visual Basic

# PostgreSQL and Business Objects

I suply you with:

Accessing PostgreSQL from Visual Basic

expandcollapse popup

# 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

Maybe some one culd help me?

I am using as samples MYSQL UDF but stuck with this thing.

If you nead more documentation or explame or you have idea why it is not working.

Some more info what I tryed by myself.

if using:
Return $oRS.Open ($query)
as found in mysql explame gives me error like :
err.description is:     The connection cannot be used to perform this operation. It is either closed or invalid in this context.
err.windescription: 
err.number is:  80020009
err.lastdllerror is:    0
err.scriptline is:  161
err.source is:  ADODB.Recordset
err.helpfile is:    C:\WINDOWS\HELP\ADO270.CHM
err.helpcontext is:     1240658
We intercepted a COM Error !

So now i think that

Return $oRS.Open ($query,$oConn)

dosent return me an object. So what it returns? I'm confused.

[slaughter]

come on neead some help plz