Jump to content
Sign in to follow this  
tylerdurden78

adodb how to extract values

Recommended Posts

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.

Share this post


Link to post
Share on other sites
amokoura

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

Edited by amokoura

Share this post


Link to post
Share on other sites
tylerdurden78

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

still getting -1 error

Share this post


Link to post
Share on other sites
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 by amokoura

Share this post


Link to post
Share on other sites
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 by tylerdurden78

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by tylerdurden78

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×