AutoItObject SQLite Text Formating

[Yoriz]

Wanted to share my first playing around with the great AutoItObject UDF, what this does is adds formating to make sqlite command entrys easier, im proabably not making best use of AutoItObject. I have no experience of OOP, got to start somewhere , feel free to comment on how it could be improved.

expandcollapse popup

#include <sqlite.au3>
#include <sqlite.dll.au3>
#include <Array.au3>
#include "AutoitObject.au3"

_AutoItObject_Startup()

Func _oSQLText()
    Local $self = _AutoItObject_Create()
    ;Propertys Public
    _AutoItObject_AddProperty($self, "TableName",$ELSCOPE_PUBLIC ,"")
    _AutoItObject_AddProperty($self, "Delimiter",$ELSCOPE_PUBLIC ,"|")
    _AutoItObject_AddProperty($self, "ColumnDef",$ELSCOPE_PUBLIC ,"")
    _AutoItObject_AddProperty($self, "Column",$ELSCOPE_PUBLIC ,"")
    _AutoItObject_AddProperty($self, "Data",$ELSCOPE_PUBLIC ,"")
    _AutoItObject_AddProperty($self, "Where",$ELSCOPE_PUBLIC ,"")

    ;Propertys Private
    _AutoItObject_AddProperty($self, "Header",$ELSCOPE_PRIVATE ,".header OFF")

    ;Methods
    _AutoItObject_AddMethod($self, "SQLText", "_ReturnValue")
    _AutoItObject_AddMethod($self, "CreateTable", "_oSQLTextCreateTable")
    _AutoItObject_AddMethod($self, "HeaderOff", "_oSQLTextHeaderOff")
    _AutoItObject_AddMethod($self, "HeaderOn", "_oSQLTextHeaderOn")
    _AutoItObject_AddMethod($self, "Insert", "_oSQLTextInsert")
    _AutoItObject_AddMethod($self, "SelectColumns", "_oSQLTextSelectColumns")
    _AutoItObject_AddMethod($self, "SelectAll", "_oSQLTextSelectAll")
    _AutoItObject_AddMethod($self, "Update", "_oSQLTextUpdate")
    _AutoItObject_AddMethod($self, "Delete", "_oSQLTextDelete")
    Return $self
EndFunc

; _oSQLText Methods
Func _oSQLTextCreateTable($oSelf)
    Local $sSqlInput, $aColumn, $aComlumndef
    $sSqlInput = "CREATE TABLE " & $oSelf.TableName & " ("
    $aColumn = StringSplit($oSelf.Column,$oSelf.Delimiter)
    $aComlumndef = StringSplit($oSelf.ColumnDef,$oSelf.Delimiter)
    For $i = 1 To $aColumn[0]
        $sSqlInput = $sSqlInput & $aColumn[$i] & " " & $aComlumndef[$i] & ","
    Next
    $sSqlInput = StringTrimRight($sSqlInput,1) & ");"
    Return $sSqlInput
EndFunc

Func _oSQLTextHeaderOn($oSelf)
    $oSelf.Header = ".header ON"
    Return $oSelf
EndFunc

Func _oSQLTextHeaderOff($oSelf)
    $oSelf.Header = ".header OFF"
    Return $oSelf
EndFunc

Func _oSQLTextInsert($oSelf)
    Local $sSqlInput, $aSplit
    $sSqlInput = "INSERT INTO " & $oSelf.TableName & " " & "("
    $aColumn = StringSplit($oSelf.Column,$oSelf.Delimiter)
    For $i = 1 To $aColumn[0]
        $sSqlInput = $sSqlInput & $aColumn[$i] & ","
    Next
    $sSqlInput = StringTrimRight($sSqlInput,1)
    $sSqlInput = $sSqlInput & ") VALUES ('"
    $aSplit = StringSplit($oSelf.Data, $oSelf.Delimiter)
    For $i = 1 To $aSplit[0] Step 1
        $sSqlInput = $sSqlInput & $aSplit[$i] & "','"
    Next
    $sSqlInput = StringTrimRight($sSqlInput, 2)
    $sSqlInput = $sSqlInput & ");"
    Return $sSqlInput
EndFunc

Func _oSQLTextSelectAll($oSelf)
    Local $sSqlInput, $aColumn
    $sSqlInput = "SELECT * FROM " & $oSelf.TableName & " "
    If $oSelf.Where <> "" Then
        $sSqlInput = $sSqlInput & "WHERE " & $oSelf.Where & " "
    EndIf
    $sSqlInput = $sSqlInput & ";"
    Return $sSqlInput
EndFunc

Func _oSQLTextSelectColumns($oSelf)
    Local $sSqlInput, $aColumn
    $sSqlInput = "SELECT "
    $aColumn = StringSplit($oSelf.Column,$oSelf.Delimiter)
    For $i = 1 To $aColumn[0]
        $sSqlInput = $sSqlInput & $aColumn[$i] & ","
    Next
    $sSqlInput = StringTrimRight($sSqlInput,1)
    $sSqlInput = $sSqlInput & " FROM " & $oSelf.TableName & " "
    If $oSelf.Where <> "" Then
        $sSqlInput = $sSqlInput & "WHERE " & $oSelf.Where & " "
    EndIf
    $sSqlInput = $sSqlInput & ";"
    Return $sSqlInput
EndFunc

Func _oSQLTextUpdate($oSelf)
    Local $sSqlInput, $aColumn, $aSplitData
    $sSqlInput = "UPDATE " & $oSelf.TableName & " SET "
    $aColumn = StringSplit($oSelf.Column, $oSelf.Delimiter)
    $aSplitData = StringSplit($oSelf.Data, $oSelf.Delimiter)
    For $i = 1 To $aColumn[0] Step 1
        $sSqlInput = $sSqlInput & $aColumn[$i] & " = '" & $aSplitData[$i] & "',"
    Next
    $sSqlInput = StringTrimRight($sSqlInput, 1)
    $sSqlInput = $sSqlInput & " "
    If $oSelf.Where <> "" Then
        $sSqlInput = $sSqlInput & "WHERE " & $oSelf.Where & " "
    EndIf
    $sSqlInput = $sSqlInput & ";"
    Return $sSqlInput
EndFunc

Func _oSQLTextDelete($oSelf)
    Local $sSqlInput
    $sSqlInput = "DELETE FROM " & $oSelf.TableName & " "
    $sSqlInput = $sSqlInput & "WHERE " & $oSelf.Where & " ;"
    Return $sSqlInput
EndFunc

$sSQLText = _oSQLText()
$sSQLText.TableName = "Testing"
$sSQLText.Delimiter = "|"
$sSQLText.Column = "a|b|c"
$sSQLText.ColumnDef = "NUMERIC|NUMERIC|NUMERIC"
$sSQLText.Data = "1|2|3"

Local $aResult, $iRows, $iColumns
_SQLite_Startup()
_SQLite_Open () ; Open a :memory: database
MsgBox(0,"",$sSQLText.CreateTable)
_SQLite_Exec (-1, $sSQLText.CreateTable)
MsgBox(0,"",$sSQLText.Insert)
_SQLite_Exec (-1, $sSQLText.Insert)
MsgBox(0,"",$sSQLText.SelectColumns)
_SQLite_GetTable2d (-1, $sSQLText.SelectColumns, $aResult, $iRows, $iColumns)
_SQLite_Display2DResult($aResult)
_ArrayDisplay($aResult)
MsgBox(0,"",$sSQLText.SelectAll)
$sSQLText.Data = "4|5|6"
MsgBox(0,"",$sSQLText.Insert)
_SQLite_Exec (-1, $sSQLText.Insert)
_SQLite_GetTable2d (-1, $sSQLText.SelectAll, $aResult, $iRows, $iColumns)
_SQLite_Display2DResult($aResult)
_ArrayDisplay($aResult)
$sSQLText.Where = "a = 1"
MsgBox(0,"",$sSQLText.SelectAll)
MsgBox(0,"",$sSQLText.Update)
MsgBox(0,"",$sSQLText.Delete)
_SQLite_Close ()
_SQLite_Shutdown ()
$sSQLText = 0
_AutoItObject_Shutdown()
Exit

Edited February 24, 2010 by Yoriz

[lsakizada]

Hi, I looked for some ideas to use SQLite and you introduced me to the

AutoitObject UDF for the first time !!!

You script is a nice example too.

Thanks.