5 replies to this topic

[ptrex]

Disconnected Recordset.

I will never get famous for writing nice code !!
But I definitely get there, because of the nice intro's I make

How much more minimalistic can we get !!
Creating a Database and not having to use a DB file or an ODBC, or DNS less connection, or Queries.

This means there is no dependency on any external DLL's or what so ever.

I have used the technique of a Disconnected Recordset.

For those who don' t know what I mean :

Two of the most exciting features of ActiveX Data Objects (ADO) are disconnected recordsets
and saved recordsets. Disconnected recordsets allow you to work with a recordset that is no longer connected to a data source.
A saved recordset is saved to a file that can be closed and reopened without an active connection.

You should use a disconnected recordset when the application needs to drop a connection to the
data source and still retain the ability to view or manipulate the data.

A saved or persisted recordset is data that is saved to a file. You can close and reopen the file
later without an active connection. For example, an application may need to download data to a laptop computer that updates the data while disconnected from the network. When you reconnect the
laptop to the network, the application then updates the shared network database with the additions
and changes that have been made.

AutoIt        expandcollapse  popup

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

Dim $oMyError, $ador
Local $listview, $Items, $item1, $item2
Const $adVarChar = 200
Const $MaxCharacters = 255
Const $field1 = "Name"
Const $Field2 = "Memo"

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

$ador = ObjCreate( "ADOR.Recordset" ) ; Create a disconnected ADOR Object with the Beta version

    With $ador
        .Fields.append ($field1, $adVarChar, $MaxCharacters)
            .Fields.append ($Field2, $adVarChar, $MaxCharacters)
        ;.CursorType = "adOpenDynamic"
        ;.CursorLocation = "AdUseClient"
            .Open
    
    EndWith

GUICreate("No ODBC no DNS no DB no Query 1.0", 600, 400, (@DesktopWidth/2)-300, _
(@DesktopHeight/2)-350, $WS_OVERLAPPEDWINDOW + $WS_VISIBLE + $WS_CLIPSIBLINGS)
$listview = GUICtrlCreateListView ("Names |Comments ",10,10,200,350)
$btn_1 = GUICtrlCreateButton ("Build the DB", 10, 365, 110, 20)
$btn_2 = GUICtrlCreateButton ("Refresh", 130, 365, 110, 20)
GUISetState()
    With $ador  
            .AddNew
            .Fields(0).Value = "AutoIT"
            $item1= .Fields(0).Value
            .Fields(1).Value = "Isn't it great !!"
            $item2= .Fields(1).Value
            $items=GUICtrlCreateListViewItem($item1&"|"&$item2,$listview)
            .AddNew
            .Fields(0).Value = "ptrex"
            $item1= .Fields(0).Value
            .Fields(1).Value = "Or am I great ?"
            $item2= .Fields(1).Value
            $items=GUICtrlCreateListViewItem($item1&"|"&$item2,$listview)
    EndWith

While 1
    $msg = GuiGetMsg()
    Select
        Case $msg = $GUI_EVENT_CLOSE
            ExitLoop
    Case $msg = $btn_1
        _Build_DB()
    Case $msg = $btn_2
        _Refresh()
    EndSelect
WEnd
Exit

Func _Build_DB ()
    If not IsObj($ador) Then
    MsgBox(0,"Error","Cannot create Object")
EndIf
    With $ador
        For $i = 0 To 49
            .AddNew
            .Fields(0).Value = "Name" & $i+1
        $item1= .Fields(0).Value
            .Fields(1).Value = "Added some more"
        $item2= .Fields(1).Value
            $items=GUICtrlCreateListViewItem($item1&"|"&$item2,$listview)
        Next
    EndWith
EndFunc

Func _Refresh()
    _GUICtrlListViewDeleteAllItems ($listview)
    _Build_DB()
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

 ADOR_example.au3   6.81K   391 downloads

And for those who thought who that DNS less connections was the ultimate

Enjoy !!

Edited by ptrex, 14 September 2012 - 09:38 AM.

[jpam]

how write the databasefile to harddisk
and load it when needed?

jpam

btw; nice job!

[ptrex]

@jpam

You can write the data using the following command ;

$ador.Save ("C:\Temp\Test.xml", 1)

Regards,

ptrex

[jpam]

thanks petrex

try it tonight

[Dschingis]

Ptrex,
i've tried your example but i always get the error 80020009 while saving the data which means the database is closed.

if i compare your article with another source for ador i get the answer i try to access a closed dataconnection.

The file test.xml will be created but always only with the first two records - immediately followed by this error. Until now i haven't got a workaround for that. Setting the database connection to nothings does not work as i haven't created one. So i see no way to save and reload the data to modify it.

maybe you have a working example ? Is there some other users which ha ve found a solution ?

[ptrex]

@Dschingis

The reason why you get the error is not that the DB is closed but because the file already exists.

Note. OK, with one small caution: make sure that the file Test.xml does not exist before calling the Save method. Suppose Test.xml already exists and you run this script; when you call the Save method the script will fail. However, suppose Test.xml does not exist and you run the script. Inside this one script you can call the Save method (thus creating the file) and then, later on in the same script, you can call the Save method again. That’s OK: because the Save method does not close the file you can continue writing to the XML file as long as the file is open. You’ll encounter a “file exists” error only if you close the XML file and then try overwriting it.

You can always use this function to check and delete the file before writing.

 FileExists()

Regards,

ptrex