SQL Database in autoit

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

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 February 6, 2013 by DiOgO

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

expandcollapse popup

; *** 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 February 6, 2013 by kylomas

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

expandcollapse popup

; *** 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?

[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 February 6, 2013 by kylomas

[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 February 6, 2013 by DiOgO

[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

[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

[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 February 6, 2013 by kylomas

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

expandcollapse popup

#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 February 6, 2013 by kylomas

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

[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

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

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

expandcollapse popup

#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

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 February 6, 2013 by DiOgO

[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

[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

[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

[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

[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

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

Edited February 9, 2013 by DiOgO

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

[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 February 9, 2013 by DiOgO