Sign in to follow this  
Followers 0
Yoriz

AutoItObject SQLite Text Formating

2 posts in this topic

#1 ·  Posted (edited)

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.

#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 by Yoriz

GDIPlusDispose - A modified version of GDIPlus that auto disposes of its own objects before shutdown of the Dll using the same function Syntax as the original.EzMySql UDF - Use MySql Databases with autoit with syntax similar to SQLite UDF.

Share this post


Link to post
Share on other sites



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.


Be Green Now or Never (BGNN)!

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now
Sign in to follow this  
Followers 0