Query between 2 Disconnected Recordsets

[BigDaddyO]

I found an old Post from ptrex that shows how to create a Disconnected Recordset using ADO but what I'm looking to do is basically create 2 separate tables that I can perform queries between to assist with some validations I'm doing.

1. Load specific Columns from delimited file 1 into Disconnected Record set 1
2. Load specific columns from delimited file 2 into Disconnected Record set 2
3. Perform lots of Queries between the two record sets in order to identify missing or incorrectly formatted data.

I have created the 2 disconnected recordsets but I can't figure out how to perform queries between them.  Is this possible? am I going about this the wrong way?  other ideas?

 

Thanks for any assistance you may be able to offer.

Mike

 

EDIT:  After several days of re-writing my script to use the Disconnected RecordSets to perform validations between two large data files, it turns out, it's no faster than just looping through Arrays.  So, yes this is possible, but it's not at all useful. 

 

 

Below is the POC I'm using that was built from ptrex's initial code.  

expandcollapse popup

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

Dim $oMyError, $ADOR, $ADOR2, $ADOR3

Const $adVarChar = 200
Const $MaxCharacters = 255
Const $Field1 = "Name"
Const $Field2 = "Desc"

; Initializes COM handler
$oMyError = ObjEvent("AutoIt.Error","MyErrFunc")

;Create the Recordset for the Main Data
$ADOR = ObjCreate("ADOR.Recordset")     ;Create a disconnected ADO Recordset Object
$ADOR.CursorLocation = 3                ;Ensure the system is using the local computer for all processing
With $ADOR                              ;Create the new fields in the recordset
    .Fields.append ($Field1, $adVarChar, $MaxCharacters)
    .Fields.append ($Field2, $adVarChar, $MaxCharacters)
    .Open
EndWith


GUICreate("No ODBC no DNS no DB no Query 1.0", 800, 400, (@DesktopWidth/2)-300, (@DesktopHeight/2)-350, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)

$listview = GUICtrlCreateListView ("Field1|Field2",10,10,200,350)
$btn_1 = GUICtrlCreateButton ("Build DB 1", 10, 365, 90, 20)
$btn_2 = GUICtrlCreateButton ("Save To XML", 110, 365, 100, 20)

$listview2 = GUICtrlCreateListView("Field1|Field2", 250, 10, 200, 350)
$btn_3 = GUICtrlCreateButton("Build DB2", 250, 365, 90, 20)

$listview3 = GUICtrlCreateListView("Field1|Field2", 500, 10, 200, 350)
$btn_4 = GUICtrlCreateButton("Query for Matches", 500, 365, 120, 20)


GUISetState()

While 1
    $msg = GuiGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop

        Case $msg = $btn_1
            _Build_DB()

        Case $msg = $btn_2
            _Save_DB()

        Case $msg = $btn_3
            _Build_DB_2()

        Case $msg = $btn_4
            _Query_RS()

    EndSelect
WEnd

Exit



Func _Query_RS()
    MsgBox(0, "Hmm", "How do I query table 2 looking for info from Table 1")
EndFunc


Func _Save_DB()
    If not IsObj($ADOR) Then
        MsgBox(0,"Error", "Object does not exist to be saved")
    EndIf

    $sLoc = FileSaveDialog("Save DB as", @ScriptDir, "XML (*.xml)")

    If FileExists($sLoc) Then
        If MsgBox(262196,"File Exists","File already exists, Overwrite?") = 6 Then
            FileDelete($sLoc)
        Else
            Return
        EndIf
    EndIf

    $ADOR.Save ($sLoc, 1)

EndFunc



Func _Build_DB_2()

    _GUICtrlListView_DeleteAllItems($listview2)

    $ADOR2 = ObjCreate("ADOR.Recordset")            ;Create a disconnected ADO Recordset Object
    $ADOR2.CursorLocation = 3                       ;Ensure the system is using the local computer for all processing
    With $ADOR2                                     ;Create the new fields in the recordset
        .Fields.append ($Field1, $adVarChar, $MaxCharacters)
        .Fields.append ($Field2, $adVarChar, $MaxCharacters)
        .Open
    EndWith

    If not IsObj($ADOR2) Then
        MsgBox(0, "Error", "Cannot create Object")
    EndIf

    With $ADOR2
        For $i = 0 To 49                            ;Loop to create 50 new items in the RecordSet
            .AddNew
            .Fields(0).Value = "Name " & $i + 1     ;Insert the data into the 1st field in the Recordset
            .Fields(1).Value = "Added some more"    ;Insert the data into the 2nd field in the Recordset

            $item1 = .Fields(0).Value               ;Read the data back out of the Recordset
            $item2 = .Fields(1).Value               ;Read the data back out of the Recordset
            $items = GUICtrlCreateListViewItem($item1 & "|" & $item2, $listview2)
        Next
    EndWith

EndFunc



Func _Build_DB ()

    _GUICtrlListView_DeleteAllItems($listview)

    If not IsObj($ADOR) Then
        MsgBox(0,"Error", "Cannot create Object")
    EndIf

    With $ADOR
        For $i = 0 To 55 Step 5                     ;Loop to create a few new items in the RecordSet
            .AddNew
            .Fields(0).Value = "Name " & $i + 1     ;Insert the data into the 1st field in the Recordset
            .Fields(1).Value = "Added some more"    ;Insert the data into the 2nd field in the Recordset

            $item1 = .Fields(0).Value               ;Read the data back out of the Recordset
            $item2 = .Fields(1).Value               ;Read the data back out of the Recordset
            $items = GUICtrlCreateListViewItem($item1 & "|" & $item2, $listview)
        Next
    EndWith

EndFunc


; This is Sven P's custom error handler added by ptrex
Func MyErrFunc()
    $HexNumber=hex($oMyError.number,8)
    Msgbox(0,"AutoItCOM Test","We intercepted a COM Error !" & @CRLF & @CRLF & _
             "err.description is: " & @TAB & $oMyError.description & @CRLF & _
             "err.windescription:" & @TAB & $oMyError.windescription & @CRLF & _
             "err.number is: " & @TAB & $HexNumber & @CRLF & _
             "err.lastdllerror is: " & @TAB & $oMyError.lastdllerror & @CRLF & _
             "err.scriptline is: " & @TAB & $oMyError.scriptline & @CRLF & _
             "err.source is: " & @TAB & $oMyError.source & @CRLF & _
             "err.helpfile is: " & @TAB & $oMyError.helpfile & @CRLF & _
             "err.helpcontext is: " & @TAB & $oMyError.helpcontext _
            )
    SetError(1) ; to check for after this function returns
Endfunc

 

Edited September 19, 2017 by BigDaddyO

[BigDaddyO]

I sorta figured out a solution but it's not going to be as fast as I was hoping.

 

Updated _Query_RS() function which uses .Filter to act as a type of Where clause.  I'll have to loop through the First RS performing Filters on the Second RS.

 

Func _Query_RS()
    ;loop through all of the RS1 and apply filter to RS2 returning data
    $ADOR.MoveFirst                                     ;Ensure we are at the first row in the RecordSet 1
    While Not $ADOR.EOF                                 

        With $ADOR2
            $ADOR2.Filter = 0                           ;Clear any existing filters From the RecordSet 2
            $ADOR2.MoveFirst                            ;Move back to the top of the RecordSet 2 after filter was cleared

            $ADOR2.Filter = $Field1 & " = '" & $ADOR.Fields(0).Value & "' AND " & $Field2 & " = '" & $ADOR.Fields(1).Value & "'"    ;Apply Filter to the RecordSet 2 only showing the data we want from RecordSet1

            While Not $ADOR2.EOF
                $item1 = .Fields(0).Value               ;Read the data from the RecordSet 2
                $item2 = .Fields(1).Value               ;Read the data from the RecordSet 2
                $items = GUICtrlCreateListViewItem($item1 & "|" & $item2, $listview3)   ;Add the data to the listview 3 for a visual confirmation
                $ADOR2.MoveNext
            WEnd

        EndWith

        $ADOR.MoveNext                                  ;Move to the next record in the RecordSet 1 to look for
    WEnd

EndFunc

 

[jchd]

I've no idea on the actual context you're working on, but I strongly suspect that you should consider using SQL only (not external, pedestrian AutoIt code) to perform the job.

Try to expose your real-world problem using understandable entities and post the tables' DDL (layout) and semantics. Then explain what you want to obtain as a result.

Burrying SQL / recordsets in GUI glue only makes the actual problem harder to grasp.