No ODBC no DNS no DB no Query - no DLL's required

[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.

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

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

Enjoy !!

Edited September 14, 2012 by ptrex

[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. Thats 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. Youll 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