adodb how to extract values

[tylerdurden78]

Hi,

i have a list of several select from where things, all of them should return one single String.

I used

Func _postgres_query()

$oConn = ObjCreate("ADODB.Connection")

$oRS = ObjCreate("ADODB.Recordset")

;~ set your database ip user & pass

$line2 = "DRIVER={PostgreSQL Unicode};DATABASE=XXX;" & _

"SERVER=localhost;PORT=5432;Uid=postgres;" & _

"Pwd=xyz;" & _

"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 ("SELECT COUNT(*) as result FROM game_players, players WHERE game_players.player_id = players.player_id AND screen_name like 'donkey'")

$oConn.Close

Return $oRS

EndFunc

Now i have the problem how i access the values in the recordset because im not familiar with adodb. I found some "With" Codes and .Fields().value but it doesnt work? The select delivers an 1x1 table.

[amokoura]

$foo = $oRS.Fields("somecolumn").Value

Edited July 23, 2008 by amokoura

[tylerdurden78]

$foo = $oRS.Fields("somecolumn").Value

still getting -1 error

[amokoura]

Oops maybe you shouldn't close the connection before reading recordset data.

I mean: don't use this too early:

$oConn.Close

It might be possible to close connection early and read data afterwards with a specific cursor type (client side). I'm not sure...

Please share your solution, if you find one muttley

Edited July 23, 2008 by amokoura

[tylerdurden78]

Oops maybe you shouldn't close the connection before reading recordset data.

I mean: don't use this too early:

$oConn.Close

It might be possible to close connection early and read data afterwards with a specific cursor type (client side). I'm not sure...

Please share your solution, if you find one muttley

this doesnt seem to be the main problem, i changed it but it still doesnt work. Maybe you could provide me with an example code that is working for you? Thank you.

Edited July 23, 2008 by tylerdurden78

[amokoura]

Unfortunately all my adodb code is at work so let's do this on the fly muttley

I noticed $oRS is not associated to the query in any way.

If you use $oConn.Execute() method, you should save the result to $oRS. Maybe..

Other option is to:

$oConn.Open($connstring)

$oRS.Open($sql, $oConn)

; Do some recordset operations here

$oRS.Close

$oConn.Close

Final hint:

Check the manual for obj/com reference. And there "Error handling" or something. Try getting out the COM error description. It usually tells what's wrong instead of "ERROR -1".

Search forum for "ADODB" and you should find methods how people have used the Connection and Recordset objects together.

[tylerdurden78]

ok i got it working, thank you.

Func _postgres_query()

    $oConn = ObjCreate("ADODB.Connection")
    $oRS = ObjCreate("ADODB.Recordset")

    $db_Settings = "DRIVER={PostgreSQL Unicode};DATABASE=bla;" & _
            "SERVER=localhost;PORT=5432;Uid=postgres;" & _
            "Pwd=bla;" & _
            "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($db_Settings)
    $oRS.Open("SELECT (case when (count(*)!=0) then ( cast(sum(pre_flop_raise_n) as real) / cast(count(*) as real) ) else (-1) end) as result FROM game_players, players WHERE game_players.player_id = players.player_id AND screen_name like 'donkey'", $oConn, 1, 3)
    $oRS.MoveFirst

    For $iIndex = 1 To $oRS.RecordCount
        $pt_pfr = $oRS.Fields("result" ).value
        $oRS.MoveNext
    Next

    $oConn.Close
    Return $pt_pfr

EndFunc  ;==>_postgres_query

$var = _postgres_query()
MsgBox(0, "player pfr", $var)

Edited July 23, 2008 by tylerdurden78