ADO, read ALIASES from derived table

[redsleeves]

I can't read the ALIAS values from a query.

The query returns the data correctly, I've checked, but it creates a derived table with ALIASES that I can't get the values from.

Here is what I have:

expandcollapse popup

Dim $My_Database = "Dummy.mdb"
$oConn = ObjCreate("ADODB.Connection")
            $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$My_Database&"")
            $oRS_recordcount = ObjCreate("ADODB.Recordset")
            $oRS_recordcount.Open("Select username FROM pt_users", $oConn, 1,3)
            While not $oRS_recordcount.EOF
                $oRS_recordcount.MoveNext
            WEnd
            $numusers = $oRS_recordcount.RecordCount
            $oRS_recordcount.Close
            Dim $MyArray[$numusers+1]
            $MyArray[0] = $numusers
$oRS = ObjCreate("ADODB.Recordset")
            $SQL = ""
            $SQL &= "Select Max(pt_records.recordstatus) As USERSTATUS, "
            $SQL &= "Max(pt_records.recordaddon) As ADDDATE, "
            $SQL &= "pt_users.userlastname As LASTNAME, "
            $SQL &= "Max(pt_users.userfirstname) As FIRSTNAME "
            $SQL &= "FROM pt_records "
            $SQL &= "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username "
            $SQL &= "GROUP By pt_users.userlastname"
        ;MsgBox(0,"debug",""""& $SQL & """")
            $oRS.Open(""& $SQL & "", $oConn, 1,3)
                For $l = 1 to $numusers
                    $UserLastName = $oRS.Fields( "userlastname" ).value
                    $UserFirstName = $oRS.Fields( "userfirstname" ).value
                    $UserRecordStatus = $oRS.Fields( "recordstatus" ).value
                    $UserRecordAddon = $oRS.Fields( "recordaddon" ).value
                    $MyArray[$l] = $UserLastName & ", " &  $UserFirstName & "|" & $UserRecordStatus & "|" & $UserRecordAddon 
                    $oRS.MoveNext
                Next
            $oRS.Close
            $oConn.Close
                GuiCreate("Who", 420, 600, -1, -1, $WS_POPUPWINDOW, $WS_EX_TOOLWINDOW + $WS_EX_TOPMOST)
                GUISetState (@SW_SHOW)
                $listview = GUICtrlCreateListView ("",10, 30, 400, 500,$LVS_REPORT)
                GUICtrlSetBkColor($listview,0xece9d8)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 0, "Who", 130)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 1, "Status", 78)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 2, "Time", 75)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 3, "Date", 90)
                
                For $w = 1 To $numusers
                $item1=GUICtrlCreateListViewItem("|||",$listview)
                GUICtrlSetData($item1,_ArrayPop ( $MyArray ))
                Next
                
                $okay_button = GuiCtrlCreateButton("Okay", 170, 550, 80, 30)
                    While 1
                            $guimsg = GUIGetMsg()
                            Select
                                Case $guimsg = $okay_button 
                                    GUIDelete("Who")
                                    ExitLoop
                            EndSelect
                    WEnd

I have also tried using the aliases like this:

$UserLastName = $oRS.Fields( "LASTNAME" ).value
                    $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value
                    $UserRecordStatus = $oRS.Fields( "USERSTATUS" ).value
                    $UserRecordAddon = $oRS.Fields( "ADDDATE" ).value

What I would like to do is read the values from the aliases. Do I need to open another recordset to access the derived table?

Any ideas or suggestions would be welcome. Also, I can provide the dummy database that I am using.

Thanks in advance.

[ptrex]

@redsleeves

Maybe this can give you some tips.

Aliases 

When joining Tables you can give a table an alias name which you can then use throughout the SQL statement. This is another way of reducing the size on an SQL statement that a join.

When joining the Employee and Training Table instead of typing the whole table name in front of every column, you could alias Employee as E and Training as T Which would give you:

Select E.EMPLOYEENAME, T.DATE, T.DESCRIPTION From EMPLOYEE E, TRAINING T where E.EMPLOYEENO = T.EMPLOYEENO

SQL For Access

regards

ptrex

[redsleeves]

Thank you for the tip.

I can access the tables and fields with the following code but the returned data is not sorted correctly:

$oRS = ObjCreate("ADODB.Recordset")
$SQL = ""
$SQL &= "SELECT pt_records.recordstatus, "
$SQL &= "pt_records.recordaddon, "
$SQL &= "pt_users.userlastname, "
$SQL &= "pt_users.userfirstname "
$SQL &= "FROM pt_records "
$SQL &= "INNER JOIN pt_users On pt_records.recordid = pt_users.userid "
$oRS.Open(""& $SQL & "", $oConn, 3,3)

As soon as I add the 'GROUP By' function (code below) everything falls apart.

This is the error I get: $oRS.Open(""& $SQL & "", $oConn, 3,3)^ ERROR

Error: The requested action with this object has failed.

$oRS = ObjCreate("ADODB.Recordset")
$SQL = ""
$SQL &= "SELECT pt_records.recordstatus, "
$SQL &= "pt_records.recordaddon, "
$SQL &= "pt_users.userlastname, "
$SQL &= "pt_users.userfirstname "
$SQL &= "FROM pt_records "
$SQL &= "INNER JOIN pt_users On pt_records.recordid = pt_users.userid "
$SQL &= "GROUP By pt_users.userlastname"   < ---------------------- this breaks me 
$oRS.Open(""& $SQL & "", $oConn, 3,3)

I have tested the SQL above with the 'GROUP By' inside of Access and it works fine.

But using the 'GROUP By' creates a new temporary recordset and I haven't been able to get to that.

I was ALIASING the fields earlier because they show up in the derived table as Expr1000, Expr1001, Expr1002, etc., and aliasing them makes the recordset easier to read.

I was also aliasing the tables within the SQL statement itself but I have not found a way to declare that the derived recordset should be called 'MyNewTable,' or something similar, and then connect to it directly.

I have also looked at CursorType and LockType to see if I am just not 'seeing' the derived recordset.

I am using CursorType adOpenStatic, 3, which should allow me to process a local cached version of the database. I don't care about any changes being made while I am running my query so my LockType is also 3, adLockOptimistic, which allows the database to be updated while I'm running my query.

I am trying to find some vbscript that uses ADO with a group like this so that I can take it part. I haven't found anything useful so far.

Any ideas or suggestions are welcome.

[redsleeves]

I wrote this in vbscript and it worked fine. The only difference was in the vbscript code I echoed the data out onto the screen instead of pumping it into an array. So I tried this with AutoIt as well and it, too, worked.

So, something is going on with my array when I add the GROUP function to my SQL query.

This works,

For $l = 1 to $numusers
                    $UserLastName = $oRS.Fields( "LASTNAME" ).value
                    $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value
                    $UserRecordStatus = $oRS.Fields( "STATUS" ).value
                    $UserRecordAddon = $oRS.Fields( "ADDON" ).value
                    _GetLoginDate($UserRecordAddon)
                    MsgBox(0,"",$UserLastName &", "&$UserFirstName &" "&$UserRecordStatus &" "&$RecordDate)
                    $oRS.MoveNext
                Next

...but this does not:

For $l = 1 to $numusers
                    $UserLastName = $oRS.Fields( "LASTNAME" ).value
                    $UserFirstName = $oRS.Fields( "FIRSTNAME" ).value
                    $UserRecordStatus = $oRS.Fields( "STATUS" ).value
                    $UserRecordAddon = $oRS.Fields( "ADDON" ).value
                    _GetLoginDate($UserRecordAddon)
                    $MyArray[$l] = $UserLastName & ", " &  $UserFirstName & "|" & $UserRecordStatus & "|" & $RecordDate 
                    $oRS.MoveNext
                Next

Any ideas? Possibly timing?

I was using the array to populate a listview by popping each element off but I could use the string directly by calling a function from within the for loop as I run my query. I'll play with that for awhile.

Here is the vbscript for anyone who may be interested:

Dim my_database
my_database = "DLECINOUT.mdb"                
Dim QueryString

            QueryString = ""
            QueryString = QueryString &  "SELECT MAX(pt_records.recordstatus) AS STATUS, "
            QueryString = QueryString &  "MAX(pt_records.recordaddon) AS ADDON, "
            QueryString = QueryString &  "pt_users.userlastname AS LASTNAME, "
            QueryString = QueryString &  "MAX(pt_users.userfirstname) AS FIRSTNAME "
            QueryString = QueryString &  "FROM pt_records "
            QueryString = QueryString &  "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username "
            QueryString = QueryString &  "GROUP By pt_users.userlastname"     
          
Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")
objConnection.Open "Provider = Microsoft.Jet.OLEDB.4.0; " & "Data Source = " & my_database
objRecordSet.Open QueryString, objConnection, 3, 3

            While not objRecordSet.EOF
            WScript.Echo objRecordSet.Fields( "LASTNAME" ).Value & " " &_
            objRecordSet.Fields( "FIRSTNAME" ).Value & " " &_
            objRecordSet.Fields( "STATUS" ).Value & " " &_
            objRecordSet.Fields( "ADDON" ).value
            objRecordSet.MoveNext
            WEnd

objRecordSet.Close
objConnection.Close

[redsleeves]

I got this working. Finally. Probably not the most elegant way to achieve this but it does work.

I just dumped all of the returned fields into a string, appended an @CR onto the end of each recursion then StringSplit the whole thing into an array. That worked.

Here is the chunk of code that I am using. This is actually a part of something much larger.

expandcollapse popup

#include <GUIConstants.au3>
#Include <GuiListView.au3>
#include <Array.au3>

Dim $My_Database = "dummy.mdb"
Dim $MyString = ""
If FileExists($My_Database) Then
            $oConn = ObjCreate("ADODB.Connection")
            $oConn.Open("Driver={Microsoft Access Driver (*.mdb)};Dbq="&$My_Database&"")
    If $oConn.State = 1 Then 
    ;MsgBox(0,"","Connection successfully opened.")
            $oRS = ObjCreate("ADODB.Recordset")
            $SQL = ""
            $SQL &= "SELECT MAX(pt_records.recordstatus) AS STATUS, "
            $SQL &= "MAX(pt_records.recordaddon) AS ADDON, "
            $SQL &= "pt_users.userlastname AS LASTNAME, "
            $SQL &= "MAX(pt_users.userfirstname) AS FIRSTNAME "
            $SQL &= "FROM pt_records "
            $SQL &= "INNER JOIN pt_users On pt_records.recordaddby = pt_users.username "
            $SQL &= "GROUP By pt_users.userlastname"
            $oRS.Open(""& $SQL & "", $oConn,2,3)
                    While Not $oRS.EOF
                            $MyString &= $oRS.Fields( "LASTNAME" ).value & ", "
                            $MyString &= $oRS.Fields( "FIRSTNAME" ).value & "|"
                            $MyString &= $oRS.Fields( "STATUS" ).value & "|"
                            $MyString &= $oRS.Fields( "ADDON" ).value & @CR
                            $oRS.MoveNext
                    WEnd
                    $MyArray = StringSplit($MyString, @CR, 0)
            $oRS.Close
            $oConn.Close
            _ArrayDelete( $MyArray,0)
                GuiCreate("Who", 420, 600, -1, -1, $WS_POPUPWINDOW, $WS_EX_TOOLWINDOW + $WS_EX_TOPMOST)
                GUISetState (@SW_SHOW)
                $listview = GUICtrlCreateListView ("",10, 30, 400, 500,$LVS_REPORT)
                GUICtrlSetBkColor($listview,0xece9d8)
                GUICtrlSetState($listview,$GUI_FOCUS) 
                _GUICtrlListView_InsertColumn ($LISTVIEW, 0, "Who", 130)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 1, "Status", 78)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 2, "Time", 75)
                _GUICtrlListView_InsertColumn ($LISTVIEW, 3, "Date", 90)
                For $w = 1 To UBound($MyArray)
                $item1=GUICtrlCreateListViewItem("|||",$listview)
                GUICtrlSetData($item1,_ArrayPop ( $MyArray ))
                Next
                $okay_button = GuiCtrlCreateButton("Okay", 170, 550, 80, 30)
                    While 1
                            $guimsg = GUIGetMsg()
                            Select
                                Case $guimsg = $okay_button 
                                    GUIDelete("Who")
                                    ExitLoop
                            EndSelect
                        WEnd
    Else 
        MsgBox(0,"",$oConn.State)
    EndIf
EndIf

[ResNullius]

I got this working. Finally. Probably not the most elegant way to achieve this but it does work.

I just dumped all of the returned fields into a string, appended an @CR onto the end of each recursion then StringSplit the whole thing into an array. That worked.

redsleeves,

Might also want to look at the ADO Getrows and Getstring function.

The Getrows will return an array with out the need for creating a string and splitting it.

So replace your String creation/spilt code with

$oRS.Open(""& $SQL & "", $oConn,2,3)
                    $MyArray = $oRS.GetRows
            $oRS.Close
            $oConn.Close

Should work the same...

[redsleeves]

Thank you ResNullius.

Your example is much quicker than what I had been using. I am using it like this:

$oRS.Open(""& $SQL & "", $oConn,2,3)
            Dim $GetRows[4]
                $GetRows[0] = "userlastname"
                $GetRows[1] = "userfirstname"
                $GetRows[2] = "recordstatus"
                $GetRows[3] = "recordaddon"
                $MyArray = $oRS.GetRows(-1,0,$GetRows)
            $oRS.Close

For anyone who happens to stumble across this in the future, the '-1' is 'adGetRowsRest' which retrieves all records, '0' is the starting position and '$GetRows' is an array of the fields I want to retrieve in the order I want them in. I am connecting to a database that lives on a remote machine and this ended up being much quicker than retrieving all records and processing the data client side or running recursive queries with ugly, fat SQL on the server. (The connection is slow.)