Jump to content
Sign in to follow this  
Kyan

SQL Database in autoit

Recommended Posts

Kyan

Hi, I'm trying to create a sql db, with a "table" (is called like that?) with some data, the table I want to create is like this one:

Posted Image

Can some one explain how do I insert data in DB as well as locate for example a number and corresp. the name?

thanks in advance

EDIT:The example from help section of this function

_SQLite_FetchNames()
gives a empty array $aNames and a error of course..., can some explain from where this "%-10s" come from? Edited by DiOgO

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
kylomas

DiOgO,

This example code is completely self-contained (assuming that you have the sqlite module). It is intended as an example for a previoous thread. The data was not normalized nor was the schema created with any optimization in mind. It should give you something to build off of, however.

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***

#include <sqlite.au3>

#AutoIt3Wrapper_Add_Constants=n

local $Parts_fl = @scriptdir & '\Parts.txt'
local $Parts_DB = @scriptdir & '\Parts.DB3'

;-------------------------------------------------------------------------------------
; generate test file of 5,000 comma delimited items, 3 entries (cols) per line (row)
;-------------------------------------------------------------------------------------

local $refresh = false

if $refresh then filedelete($Parts_fl)

if not fileexists($Parts_fl) then

    local $hfl = fileopen($Parts_fl,2)
    if $hfl = -1 then
        ConsoleWrite('File open failed' & @LF)
        Exit
    endif

    local $str_out

    for $1 = 1 to 5000
        $str_out &= stringformat('%05i,M%05s,V%05s\n',$1,$1 & '-' & random(1,999,1),$1 & '-' & random(1,999,1))
    Next

    filewrite($hfl,$str_out)
    fileclose($hfl)
    $hfl = 0

endif

;---------------------------------------------------------------------------------------
; initialize SQLite and open Parts DB
;---------------------------------------------------------------------------------------

local $sqlstrt = _SQLite_Startup(), $st = timerinit()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

local $hmemDB = _sqlite_open($Parts_DB)

if @error then
    ConsoleWrite('Unable to open DB' & @LF)
    _Exit()
EndIf

if $refresh then _reload()

func _reload()

    if _sqlite_exec(-1,'drop table if exists parts;') <> $sqlite_ok then
        ConsoleWrite('Drop table failed' & @LF)
        _exit()
    Else
        ConsoleWrite('Parts table dropped for refresh' & @LF)
    endif

    if _sqlite_exec(-1,'create table parts (SKU, Model, Version);') <> $sqlite_ok then
        ConsoleWrite('Create Table Failed' & @LF)
        _exit()
    endif

    local $fl_array
    _filereadtoarray($Parts_fl,$fl_array)

    switch @error
        case 1
            ConsoleWrite('Input file failed to open' & @LF)
            _exit()
        case 2
            ConsoleWrite('Unable to split file' & @LF)
            _exit()
    EndSwitch

    local $aLine, $sql

    ProgressOn('Loading Parts Table','Please Wait')

    _SQLite_Exec(-1, "begin immediate;")

    for $1 = 1 to $fl_array[0]
        progressset(($1/$fl_array[0])*100)
        $aLine = stringsplit($fl_array[$1],',')
        $sql = 'insert into parts values ('
        for $2 = 1 to $aLine[0]
            $sql &= '"' & $aLine[$2] & '",'
        next
        $sql = stringtrimright($sql,1)
        $sql &= ');'

        if _sqlite_exec(-1,$sql) <> $sqlite_ok Then
            ConsoleWrite('Table insert failed STMT = ' & $sql & @LF)
            _exit()
        endif
    next

    _SQLite_Exec(-1, "commit;")

    progressoff()

    ConsoleWrite('Table loaded with ' & ubound($fl_array)- 1 & ' records in ' & round(timerdiff($st)/1000,3) & ' seconds' & @LF)

endfunc

;---------------------------------------------------------------------------------------
; display SKU query dialaog
;---------------------------------------------------------------------------------------

local $gui010 = guicreate('SKU Query Mini-APP Using SQLITE',300,170)
local $aSize  = wingetclientsize($gui010)
                guictrlcreatelabel('Enter SKU for Query',40,20,150,20)
                guictrlsetfont(-1,10,600)
                GUICtrlSetColor(-1,0xaa0000)
local $inp010 = guictrlcreateinput('',190,20,40,20)
local $lbl010 = guictrlcreatelabel('',70,60,250,50)
                guictrlsetfont(-1,10,600,-1,'Courier New')
                guictrlsetcolor(-1,0x000099)
local $btn010 = guictrlcreatebutton('Submit Query',10,$aSize[1]-30,$aSize[0]-20,20)
                guictrlsetfont(-1,9,600)
local $dmy010 = GUICtrlCreateDummy()
                guisetstate()

local $aAccelKeys[1][2] = [["{ENTER}", $dmy010]]
GUISetAccelerators($aAccelKeys)

local $aRow, $ret

while 1
    switch guigetmsg()
        case $gui_event_close
            _exit()
        case $btn010, $dmy010
            _disp()
    endswitch
wend

func _disp()

    $ret = _SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow)
    if $ret = $sqlite_ok then
        guictrlsetdata($lbl010,stringformat('%-10s%5s\n%-10s%5s\n%-10s%5s','SKU',$aRow[0],'Model',$aRow[1],'Version',$aRow[2]))
    Else
        guictrlsetdata($lbl010,guictrlread($inp010) & ' Not Found')
    endif
    guictrlsetstate($inp010,$gui_focus)

endfunc

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

kylomas

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Kyan

DiOgO,

This example code is completely self-contained (assuming that you have the sqlite module). It is intended as an example for a previoous thread. The data was not normalized nor was the schema created with any optimization in mind. It should give you something to build off of, however.

; *** Start added by AutoIt3Wrapper ***
#include <GUIConstantsEx.au3>
; *** End added by AutoIt3Wrapper ***

#include <sqlite.au3>

#AutoIt3Wrapper_Add_Constants=n

local $Parts_fl = @scriptdir & '\Parts.txt'
local $Parts_DB = @scriptdir & '\Parts.DB3'

;-------------------------------------------------------------------------------------
; generate test file of 5,000 comma delimited items, 3 entries (cols) per line (row)
;-------------------------------------------------------------------------------------

local $refresh = false

if $refresh then filedelete($Parts_fl)

if not fileexists($Parts_fl) then

    local $hfl = fileopen($Parts_fl,2)
    if $hfl = -1 then
        ConsoleWrite('File open failed' & @LF)
        Exit
    endif

    local $str_out

    for $1 = 1 to 5000
        $str_out &= stringformat('%05i,M%05s,V%05s\n',$1,$1 & '-' & random(1,999,1),$1 & '-' & random(1,999,1))
    Next

    filewrite($hfl,$str_out)
    fileclose($hfl)
    $hfl = 0

endif

;---------------------------------------------------------------------------------------
; initialize SQLite and open Parts DB
;---------------------------------------------------------------------------------------

local $sqlstrt = _SQLite_Startup(), $st = timerinit()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

local $hmemDB = _sqlite_open($Parts_DB)

if @error then
    ConsoleWrite('Unable to open DB' & @LF)
    _Exit()
EndIf

if $refresh then _reload()

func _reload()

    if _sqlite_exec(-1,'drop table if exists parts;') <> $sqlite_ok then
        ConsoleWrite('Drop table failed' & @LF)
        _exit()
    Else
        ConsoleWrite('Parts table dropped for refresh' & @LF)
    endif

    if _sqlite_exec(-1,'create table parts (SKU, Model, Version);') <> $sqlite_ok then
        ConsoleWrite('Create Table Failed' & @LF)
        _exit()
    endif

    local $fl_array
    _filereadtoarray($Parts_fl,$fl_array)

    switch @error
        case 1
            ConsoleWrite('Input file failed to open' & @LF)
            _exit()
        case 2
            ConsoleWrite('Unable to split file' & @LF)
            _exit()
    EndSwitch

    local $aLine, $sql

    ProgressOn('Loading Parts Table','Please Wait')

    _SQLite_Exec(-1, "begin immediate;")

    for $1 = 1 to $fl_array[0]
        progressset(($1/$fl_array[0])*100)
        $aLine = stringsplit($fl_array[$1],',')
        $sql = 'insert into parts values ('
        for $2 = 1 to $aLine[0]
            $sql &= '"' & $aLine[$2] & '",'
        next
        $sql = stringtrimright($sql,1)
        $sql &= ');'

        if _sqlite_exec(-1,$sql) <> $sqlite_ok Then
            ConsoleWrite('Table insert failed STMT = ' & $sql & @LF)
            _exit()
        endif
    next

    _SQLite_Exec(-1, "commit;")

    progressoff()

    ConsoleWrite('Table loaded with ' & ubound($fl_array)- 1 & ' records in ' & round(timerdiff($st)/1000,3) & ' seconds' & @LF)

endfunc

;---------------------------------------------------------------------------------------
; display SKU query dialaog
;---------------------------------------------------------------------------------------

local $gui010 = guicreate('SKU Query Mini-APP Using SQLITE',300,170)
local $aSize = wingetclientsize($gui010)
guictrlcreatelabel('Enter SKU for Query',40,20,150,20)
                guictrlsetfont(-1,10,600)
                GUICtrlSetColor(-1,0xaa0000)
local $inp010 = guictrlcreateinput('',190,20,40,20)
local $lbl010 = guictrlcreatelabel('',70,60,250,50)
                guictrlsetfont(-1,10,600,-1,'Courier New')
                guictrlsetcolor(-1,0x000099)
local $btn010 = guictrlcreatebutton('Submit Query',10,$aSize[1]-30,$aSize[0]-20,20)
                guictrlsetfont(-1,9,600)
local $dmy010 = GUICtrlCreateDummy()
                guisetstate()

local $aAccelKeys[1][2] = [["{ENTER}", $dmy010]]
GUISetAccelerators($aAccelKeys)

local $aRow, $ret

while 1
    switch guigetmsg()
        case $gui_event_close
            _exit()
        case $btn010, $dmy010
            _disp()
    endswitch
wend

func _disp()

    $ret = _SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow)
    if $ret = $sqlite_ok then
        guictrlsetdata($lbl010,stringformat('%-10s%5s\n%-10s%5s\n%-10s%5s','SKU',$aRow[0],'Model',$aRow[1],'Version',$aRow[2]))
    Else
        guictrlsetdata($lbl010,guictrlread($inp010) & ' Not Found')
    endif
    guictrlsetstate($inp010,$gui_focus)

endfunc

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

kylomas

thanks, I didn't understand the logic of a sql db, that why I don't know from where to start, when I run your code on console appears "Unable to open DB", is because I don't have the sql module you told?

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
kylomas

Yes, you can download it from sqlite.org. Save it in either the scriptdir or a system library.

kylomas

edit: download it from here

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Kyan

Yes, you can download it from sqlite.org. Save it in either the scriptdir or a system library.

kylomas

edit: download it from here

I downloaded: SQLite.dll.au3 plus SQLite.dll

Now it works, can you explain this: %05i,M%05s,V%05sn (%05 seems to be "-" and n "@CRLF" and sqlite_exec(-1,'create table parts (SKU, Model, Version);') >create a table with first column "SKU", 2nd Model, 3rd "version", parts = rows?

SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow) ; looks for SKU value and outputs the corresponding row?

Sorry for asking basic questions about sql but I never see it in my life (not literally speaking), can you give a simple example? like 2nd table with creation and row read? :)

EDIT: when I done all this stuff of table creation and data insert, displaying part of the that in a listview is a good ideia? (In terms of gui speed)

Edited by DiOgO

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
kylomas

%05i,M%05s,V%05sn

This is part of "stringformat()". See the help file.

sqlite_exec(-1,'create table parts (SKU, Model, Version);') >create a table with first column "SKU", 2nd Model, 3rd "version", parts = rows?

Yes

SQLite_QuerySingleRow(-1,'select SKU,Model,Version from parts where SKU = "' & stringformat('%05s',guictrlread($inp010)) & '";', $aRow) ; looks for SKU value and outputs the corresponding row?

Yes

EDIT: when I done all this stuff of table creation and data insert, displaying part of the that in a listview is a good ideia? (In terms of gui speed)

If that is what you need...

Sorry for asking basic questions about sql but I never see it in my life (not literally speaking), can you give a simple example? like 2nd table with creation and row read?

I'm not trying to be a smart ass, but, from an sQLite perspective this is about as simple as it gets...

Work through it, piece by piece and it will make sense...

I think Im' about done for the night, my mouse is taking a shit!

Good Luck,

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Kyan

This is part of "stringformat()". See the help file.

sorry, not big deal then :)

If that is what you need...

yap, but could be faster ways of doing it

I'm not trying to be a smart ass, but, from an sQLite perspective this is about as simple as it gets...

Work through it, piece by piece and it will make sense...

I think Im' about done for the night, my mouse is taking a shit!

Good Luck,

kylomas

in sql commands seems easy to who's used to work with it, but for who started sql 90min ago, is not that easy

thank you for all the help given


Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
kylomas

DiOgO,

in sql commands seems easy to who's used to work with it, but for who started sql 90min ago, is not that easy

I agree, but you asked for something simpler and this is as simple as it gets, at least the SQLite part of it. I'm not an expert either, however, if you get stuck there are lot's of REAL sqlite experts here. Have patience and don't be afraid to ask after you've tried to get something going.

Good Luck,

kylomas

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
kylomas

DiOgO,

I created a different example. This one populates an in-memory DB. This example does not have a bunch of AutoIT stuff going on (like the previous example) and illustrates getting a 2D table (one of your earlier questions).

#include <array.au3>
#include <sqlite.au3>

; create SQLITE DB of Names and Addresses

local $sqlstrt = _SQLite_Startup()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

; create an in-memory DB

local $hmemDB = _sqlite_open()

; create a table named "NAMES" with columns "NAME", "ADDR1", "ADDR2", "CITY" and "STATE"

if _sqlite_exec(-1,'create table NAMES (NAME, ADDR1, ADDR2, CITY, STATE);') <> $sqlite_ok then
    ConsoleWrite('Create Table Failed' & @LF)
    _exit()
endif

; insert some data into the "NAMES" table as constant data within the function

if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Tommy Turkey","111 W Nowhere ST", "", "Buzzard", "Arizona");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed' & @LF)
    _exit()
endif
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Alex Xela","123N 779W", "", "North Pole", "NoWhere");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("John Q Public","9999 Paying ST", "Department - BendOver", "TaxDebt", "USA");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = '  & @LF)
    _exit()
endif

; insert some data into the "NAMES" table using an sql statment stored in a variable ($sql)

local $sql = 'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Lindsey Lohan","000 Zoned ST", "ATTN:ANYMAN", "Loose Canyon", "Mars");'

if _sqlite_exec(-1, $sql) <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif

; insert some names and states as constant data within the function

if _sqlite_exec(-1,'insert into NAMES (NAME, STATE) values("Alvin Dark","Wisconsin");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = '  & @LF)
    _exit()
endif

; create an array of names, city and state and populat the DB from the array

local $aNames[5][3] =   [ _
                        ["A. J. Smith    ", "Ruthrford", "New Jersey "], _
                        ["Bo Didley      ", "Wendover ", "Utah-Nevada"], _
                        ["kylomas        ", "Milwaukee", "Wisconsin  "], _
                        ["Shirley U. Jest", "HollyWood", "California "], _
                        ["Obama          ", "?????????", "Secret     "] _
                        ]

for $1 = 0 to ubound($aNames,1) - 1
    $sql = 'insert into NAMES (NAME, CITY, STATE) Values ('
    for $2 = 0 to ubound($aNames,2) - 1
        $sql &=  _SQLite_FastEscape($aNames[$1][$2]) & ','
    Next
    $sql = stringtrimright($sql,1)
    $sql &= ');'

    if _sqlite_exec(-1, $sql) <> $sqlite_ok Then
        ConsoleWrite('Table insert failed STMT = ' & @LF)
        _exit()
    endif
next

; Get a 2D array of all rows in the table

local $rows, $nbrows, $nbcols
local $ret = _SQLite_GetTable2d(-1, "select * from NAMES;", $rows, $nbrows, $nbcols)

; display the array (NAMES table)

_arraydisplay($rows)

; cleanup and exit

_exit()

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

kylomas

edit : added various examples of how to populate a table

Edited by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
jchd

A very good SQL tutorial can be found here. It isn't specifically SQLite-dedicated but it gives you solid SQL grounds. SQLite covers most of SQL92 standard so you shouldn't be lost.

Don't spend useless time on premature optimization.

Favor use of _SQLite_GetTable[2d] or _SQLite_QuerySingleRow. Completely forget *_Query, *_FetchData and *_Finalize.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Melba23

Hi,

I thoroughly endorse the site jchd recommended. I have been learning how to use SQLite over the past few days and found it an excellent introduction - it even has an interactive section where you can run SQL commands on a mini database to see what you get back. ;)

M23


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites
jchd

As an useful complement I strongly recommend downloading the freeware version of SQlite Expert. This is the best free 3rd-party SQLite DB manager ever and since it relies on the same library as you can use with AutoIt (albeit not exactly the latest version) you know that if schema and queries work well in Expert, it will work as well in your AutoIt application. This tool will saves you really many hours of painful coding.

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Kyan

DiOgO,

I created a different example. This one populates an in-memory DB. This example does not have a bunch of AutoIT stuff going on (like the previous example) and illustrates getting a 2D table (one of your earlier questions).

#include <array.au3>
#include <sqlite.au3>

; create SQLITE DB of Names and Addresses

local $sqlstrt = _SQLite_Startup()

if @error then
    ConsoleWrite('error loading sqlite.dll' & @LF)
    Exit
EndIf

; create an in-memory DB

local $hmemDB = _sqlite_open()

; create a table named "NAMES" with columns "NAME", "ADDR1", "ADDR2", "CITY" and "STATE"

if _sqlite_exec(-1,'create table NAMES (NAME, ADDR1, ADDR2, CITY, STATE);') <> $sqlite_ok then
    ConsoleWrite('Create Table Failed' & @LF)
    _exit()
endif

; insert some data into the "NAMES" table as constant data within the function

if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Tommy Turkey","111 W Nowhere ST", "", "Buzzard", "Arizona");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed' & @LF)
    _exit()
endif
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Alex Xela","123N 779W", "", "North Pole", "NoWhere");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("John Q Public","9999 Paying ST", "Department - BendOver", "TaxDebt", "USA");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif

; insert some data into the "NAMES" table using an sql statment stored in a variable ($sql)

local $sql = 'insert into NAMES (NAME, ADDR1, ADDR2, CITY, STATE) values("Lindsey Lohan","000 Zoned ST", "ATTN:ANYMAN", "Loose Canyon", "Mars");'

if _sqlite_exec(-1, $sql) <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif

; insert some names and states as constant data within the function

if _sqlite_exec(-1,'insert into NAMES (NAME, STATE) values("Alvin Dark","Wisconsin");') <> $sqlite_ok Then
    ConsoleWrite('Table insert failed STMT = ' & @LF)
    _exit()
endif

; create an array of names, city and state and populat the DB from the array

local $aNames[5][3] =   [ _
                        ["A. J. Smith ", "Ruthrford", "New Jersey "], _
                        ["Bo Didley ", "Wendover ", "Utah-Nevada"], _
                        ["kylomas ", "Milwaukee", "Wisconsin "], _
                        ["Shirley U. Jest", "HollyWood", "California "], _
                        ["Obama ", "?????????", "Secret "] _
                        ]

for $1 = 0 to ubound($aNames,1) - 1
    $sql = 'insert into NAMES (NAME, CITY, STATE) Values ('
    for $2 = 0 to ubound($aNames,2) - 1
        $sql &= _SQLite_FastEscape($aNames[$1][$2]) & ','
    Next
    $sql = stringtrimright($sql,1)
    $sql &= ');'

    if _sqlite_exec(-1, $sql) <> $sqlite_ok Then
        ConsoleWrite('Table insert failed STMT = ' & @LF)
        _exit()
    endif
next

; Get a 2D array of all rows in the table

local $rows, $nbrows, $nbcols
local $ret = _SQLite_GetTable2d(-1, "select * from NAMES;", $rows, $nbrows, $nbcols)

; display the array (NAMES table)

_arraydisplay($rows)

; cleanup and exit

_exit()

func _exit()
    _SQLite_Close()
    _SQLite_Shutdown()
    exit
endfunc

kylomas

edit : added various examples of how to populate a table

wow, really nice example, thanks a lot :)

do I need allways escape all the elements? (_SQLite_FastEscape)

A very good SQL tutorial can be found here. It isn't specifically SQLite-dedicated but it gives you solid SQL grounds. SQLite covers most of SQL92 standard so you shouldn't be lost.

Don't spend useless time on premature optimization.

Favor use of _SQLite_GetTable[2d] or _SQLite_QuerySingleRow. Completely forget *_Query, *_FetchData and *_Finalize.

seems to be pretty good to learn sql commands and how they actually work, thanks :)

About your last sentence, the first 2 autoit commands is the one's I know to work with, should the other be used instead?

Hi,

I thoroughly endorse the site jchd recommended. I have been learning how to use SQLite over the past few days and found it an excellent introduction - it even has an interactive section where you can run SQL commands on a mini database to see what you get back. ;)

M23

nice :D

As an useful complement I strongly recommend downloading the freeware version of SQlite Expert. This is the best free 3rd-party SQLite DB manager ever and since it relies on the same library as you can use with AutoIt (albeit not exactly the latest version) you know that if schema and queries work well in Expert, it will work as well in your AutoIt application. This tool will saves you really many hours of painful coding.

I already downloaded SQLlite Expert (I saw you recommend it a few days ago with a devices db), I don't know how to use it yet (probably because I don't know well how a sql db is made) but just a simple question

- I created 3 fields (I think every field is like a column, I'm wrong?)

- Next, I created a index "1" linked to those 3fields and hit apply

- Now selected Data tab and the table shows up, but with a additional column "RecNo"

EDIT: For using sql commands in a compiled script do I need to incorporate SQLite.dll?

Edited by DiOgO

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
kylomas

DiOgO,

do I need allways escape all the elements? (_SQLite_FastEscape)

Technically, no, however it makes handling quotes easier and prevents something called an "sql injection attack". Do NOT know all of the details but it was recommended to me by jchd to use this technique.

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Kyan

DiOgO,

Technically, no, however it makes handling quotes easier and prevents something called an "sql injection attack". Do NOT know all of the details but it was recommended to me by jchd to use this technique.

kylomas

this is not for a web db is just for storing data rather than using a excel sheet, but I'll keep that in mind in case of someday create a web db

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
jchd

As I said, "Completely forget *_Query, *_FetchData and *_Finalize." These function are lower-level and only useful in special applications: they are harder to get right and need more complex code. The higher-level commands do use them internally but why make your code more complex to follow/debug than it needs be?

Yes, a "field" is a column. Don't create indices unless you're certain they will speed up the precise queries you're going to use in practice. Any SELECT can only make use of a single index and doing so is only beneficial if a favorable conditions exists within indexed values and time required to search the index. Most people create SQL indices only based on wild guesses and they often prove to be detrimental.

About escaping string litterals: since the SQLite DBs are simple files, it's generally hard to avoid a rogue user messing with it. SQL injections are a more sophisticated way to mess with a DB but this is not the primary concern with SQLite DBs: after all, they are local to the PC running the processes and intercepting the SQL statements to hack/destroy the DB is harder than hacking the file directly or thru another rogue process.

The main reason why escaping string literals is always a good idea is that if ever one of them contains a single quote (like in "O'Connor") then the statement gets invalid. I agree that you can skip escaping if you may escape data before it reaches the actual SQL statement, but that requires escaping anyway, unless you're 100% sure that single quotes can never occur in that literal or variable. As the saying goes: better safe than sorry!

Last point: Expert shows a row sequence number by default; that is merely for your own visual convenience. You can get rid of that virtual column by selecting Tools > Options > Data > Show record numbers


This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Kyan

As I said, "Completely forget *_Query, *_FetchData and *_Finalize." These function are lower-level and only useful in special applications: they are harder to get right and need more complex code. The higher-level commands do use them internally but why make your code more complex to follow/debug than it needs be?

Yes, a "field" is a column. Don't create indices unless you're certain they will speed up the precise queries you're going to use in practice. Any SELECT can only make use of a single index and doing so is only beneficial if a favorable conditions exists within indexed values and time required to search the index. Most people create SQL indices only based on wild guesses and they often prove to be detrimental.

About escaping string litterals: since the SQLite DBs are simple files, it's generally hard to avoid a rogue user messing with it. SQL injections are a more sophisticated way to mess with a DB but this is not the primary concern with SQLite DBs: after all, they are local to the PC running the processes and intercepting the SQL statements to hack/destroy the DB is harder than hacking the file directly or thru another rogue process.

The main reason why escaping string literals is always a good idea is that if ever one of them contains a single quote (like in "O'Connor") then the statement gets invalid. I agree that you can skip escaping if you may escape data before it reaches the actual SQL statement, but that requires escaping anyway, unless you're 100% sure that single quotes can never occur in that literal or variable. As the saying goes: better safe than sorry!

Last point: Expert shows a row sequence number by default; that is merely for your own visual convenience. You can get rid of that virtual column by selecting Tools > Options > Data > Show record numbers

okey

better escape than have inssues later

if is a virtual column no problem then

thanks to all of you, really, now I have lot of work to do :)


Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
Kyan

how do I check if a table exists?

I tried this:

If _SQLite_Query(-1,'select * from NAMES;',$existTbl) <> $sqlite_ok Then MsBox(0,'',"Prob")

in console appeared this

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

! SQLite.au3 Error

--> Function: _SQLite_Query

--> Query: select * from NAMES;

--> Error: no such table: NAMES

results:

$existTbl = 0

queryOutPut = 0

is this right? because the console error fired up :o

EDIT: I checked sqlite.au3 error codes, and find out 0 = $sqlite_ok and 1 = error

Edited by DiOgO

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

Share this post


Link to post
Share on other sites
jchd

Why so? You yourself should be able to know if a table you created actually exists, don't you?

Well, there are ways to do that. If you whish to ascertain that table mytable exists without massaging error returns, do this:

_SQLite_QuerySingleRow($hDB, "select count(*) from sqlite_master where type = 'table' and tbl_name like 'mytable'", $row)

You'll get $row[0] = 0 or 1

If you whish to create a table only if necessary, still avoiding errors, do that:

_SQLite_Exec($hDB, "create table if not exists mytable ....")

  • Like 1

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Share this post


Link to post
Share on other sites
Kyan

Why so? You yourself should be able to know if a table you created actually exists, don't you?

Well, there are ways to do that. If you whish to ascertain that table mytable exists without massaging error returns, do this:

_SQLite_QuerySingleRow($hDB, "select count(*) from sqlite_master where type = 'table' and tbl_name like 'mytable'", $row)

You'll get $row[0] = 0 or 1

If you whish to create a table only if necessary, still avoiding errors, do that:

_SQLite_Exec($hDB, "create table if not exists mytable ....")

every time my program needs to start db work, it does this stuff

startup

openDB (db file could exist or not, since _sqlite_open can create or open a db file)

createtable (if not exists)

wait for commands (insert, delete, etc)

shutdown

this is the easy one: _SQLite_Exec($hDB, "create table if not exists mytable ....")

thank you :)

EDIT: no error displayed on console ;D

Edited by DiOgO

Heroes, there is no such thing

One day I'll discover what IE.au3 has of special for so many users using it.
C'mon there's InetRead and WinHTTP, way better
happy.png

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  

×