SQL Database in autoit

[jchd]

Take some time to browse the SQLite docs (see my .sig), especially the SQL syntax as understood by SQLite part (the railroad diagrams). This is the reference for what can make a statement.

[Kyan]

Take some time to browse the SQLite docs (see my .sig), especially the SQL syntax as understood by SQLite part (the railroad diagrams). This is the reference for what can make a statement.

When I'm with syntax doubts I go to this site: http://www.w3schools.com/sql/sql_default.asp

think who recommended to go on that website was you

[jchd]

W3school is certainly a good SQL primer, but the SQlite doc is obviously the reference point for SQLite.

[Kyan]

W3school is certainly a good SQL primer, but the SQlite doc is obviously the reference point for SQLite.

course

I cannot set a default to all parts? like create table if not exists NAMES (Name, City, Addr1) Default '';? (then I don't need to replace a null var by '')

EDIT: Still without manage to set a default value to all table parts, is this command correct: IF NOT EXISTS (SELECT NAME FROM NAMES WHERE NUM='"&_SQLite_FastEscape($rdn)&"' AND SELECT NUM FROM NAMES WHERE NAME='"&_SQLite_FastEscape($rdname)&"') insert into NAMES values(...); ?

EDIT2: IF NOT EXISTS (SELECT NAME FROM NAMES WHERE NUM="&_SQLite_FastEscape($rdn)&") insert into NAMES values(...)"; [_SQLite_FastEscape puts single quotes already]

EDIT3: Now when I use the command of "EDIT2:" this error shows up, I don't know what is wrong near of "IF" statement

! SQLite.au3 Error

--> Function: _SQLite_Exec

--> Query: IF NOT EXISTS(SELECT * FROM NAMES WHERE NUM='7') Begin insert into NAMES values('7','A','','','','//','','','','-','','','') END;

--> Error: near "IF": syntax error

EDIT4: Now I find out that mysql is <> sqlite syntax, I tried this: insert into NAMES values(...) where NOT EXISTS (SELECT * FROM NAMES WHERE NUM="&_SQLite_FastEscape($rdn)&") ;"

but still got the a error: --> Error: near "where": syntax error

EDIT5: insert into NAMES values(...) where (SELECT NUM FROM NAMES WHERE NUM="&_SQLite_FastEscape($rdn)&") NOT LIKE "&_SQLite_FastEscape($rdn)&";"

edit5 don't work either, my head hurts

Edited February 9, 2013 by DiOgO

[kylomas]

DiOgO,

Did you install SQLite Expert?

EDIT4: Now I find out that mysql is <> sqlite syntax, I tried this: insert into NAMES values(...) where NOT EXISTS (SELECT * FROM NAMES WHERE NUM="&_SQLite_FastEscape($rdn)&") ;"

This is why jchd pointed you at the SQLite Doc. Each flavor of SQL has differences in the syntax.

I cannot set a default to all parts? like create table if not exists NAMES (Name, City, Addr1) Default '';? (then I don't need to replace a null var by '')

You can set a default value ach column. Look at the table definition DDL in the SQLite doc.

kylomas

[Kyan]

Hi kylomas

DiOgO,

Did you install SQLite Expert?

yap

This is why jchd pointed you at the SQLite Doc. Each flavor of SQL has differences in the syntax.

since I didn't fnd anything for this kind of clause, i look at http://www.sqlite.org/, but there's no example, just diagrams without referring code

You can set a default value ach column. Look at the table definition DDL in the SQLite doc.

DDL?

[kylomas]

DiOgO,

The diagrams ARE the code. All syntax is described but it is a little hard to get used to as it is not presented like other references, W3C for example.

Using SQLite Expert I created a test table to play with. Then displayed the resulting DDL:

CREATE TABLE [tb1] (
[col001] NOT NULL DEFAULT 0,
[col002] TIMESTAMP DEFAULT (now()),
[col003] ,
[col004] );

This is ONE of the strengths of using this tool.

kylomas

edit: you can link directly to SQLite help from SQLite Expert.

Edited February 9, 2013 by kylomas

[Kyan]

DiOgO,

The diagrams ARE the code. All syntax is described but it is a little hard to get used to as it is not presented like other references, W3C for example.

Using SQLite Expert I created a test table to play with. Then displayed the resulting DDL:

CREATE TABLE [tb1] (
[col001] NOT NULL DEFAULT 0,
[col002] TIMESTAMP DEFAULT (now()),
[col003] ,
[col004] );

This is ONE of the strengths of using this tool.

kylomas

edit: you can link directly to SQLite help from SQLite Expert.

but the words described are not like in the code

for insert command:

really weird :s, in the last part (after table-name) there isn't a box for insert data if "this value" does not exist

[kylomas]

If what value does not exist?

[Kyan]

If what value does not exist?

the one returned from this command: (SELECT * FROM NAMES WHERE NUM="&_SQLite_FastEscape($rdn)&")

$rdn = inputed number to be added in the DB

[kylomas]

DiGiO,

In the following example I've created the "CITY" column with a default value. If no value is supplied then the default is used.

expandcollapse popup

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

local $sqlstrt = _SQLite_Startup()

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

_sqlite_open()

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

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

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


; city is not specified for this insert stmt so the default value is used
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, STATE) values("Tommy Turkey","111 W Nowhere ST", "", "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", "Some State");') <> $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


; 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 :posted before I saw your last response

Edited February 9, 2013 by kylomas

[Kyan]

DiGiO,

In the following example I've created the "CITY" column with a default value. If no value is supplied then the default is used.

expandcollapse popup

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

local $sqlstrt = _SQLite_Startup()

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

_sqlite_open()

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

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

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


; city is not specified for this insert stmt so the default value is used
if _sqlite_exec(-1,'insert into NAMES (NAME, ADDR1, ADDR2, STATE) values("Tommy Turkey","111 W Nowhere ST", "", "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", "Some State");') <> $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


; 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 :posted before I saw your last response

no problem, I set a default value for each column with not null Default '' and its ok, no more null values shown

[jchd]

Thanks kylomas for running the shop while I was distracted.

Now that the optional DEFAULT clause has been correctly put back in the CREATE table statement, let me point out that while the railroad diagrams are fine and carefully designed, they are no magic bullets.

There exist both valid SQLite statements that can't be represented by the diagrams and the following diagrams blindly may produce invalid statements. Nonetheless those cases are highly marginal and only occur in complex statements. Always read the text documentation for correct description of subtlelities.

[Kyan]

Thanks kylomas for running the shop while I was distracted.

Now that the optional DEFAULT clause has been correctly put back in the CREATE table statement, let me point out that while the railroad diagrams are fine and carefully designed, they are no magic bullets.

There exist both valid SQLite statements that can't be represented by the diagrams and the following diagrams blindly may produce invalid statements. Nonetheless those cases are highly marginal and only occur in complex statements. Always read the text documentation for correct description of subtlelities.

My native language is not english, its a bit tough to read and understand all in the right way

btw, could you tell me how do I write this in sqlite: IF NOT EXISTS (SELECT NAME FROM NAMES WHERE NUM='"&_SQLite_FastEscape($rdn)&"' AND SELECT NUM FROM NAMES WHERE NAME='"&_SQLite_FastEscape($rdname)&"') insert into NAMES values(...) ?

[jchd]

My lative language isn't english either!

Can you write down in plain english or google translate what you want exactly?

SQL is not a general programming language and lacks control structures like IF condition THEN this ELSE that. There is no provision to perform an insert depending on some condition.

You seem to want something like the following pseudo-code mixing AutoIt and SQL:

if <there exist row in DB satisfying NUM = $rdn or NAME = $rdname> then

_SQLite_Exec($hdb, "insert ...")

endif

The part in green is not a direct condition. It is the result of something like:

_SQLite_QuerySingleRow($hdb, "select count(*) from names where num = " & $rdn & " or name = " & _SQLite_FastEscape($rdname), $row)

If again row[0] = 0 then no such row exist and you can proceed with the insertion.

[Kyan]

My lative language isn't english either!

Can you write down in plain english or google translate what you want exactly?

SQL is not a general programming language and lacks control structures like IF condition THEN this ELSE that. There is no provision to perform an insert depending on some condition.

You seem to want something like the following pseudo-code mixing AutoIt and SQL:

if <there exist row in DB satisfying NUM = $rdn or NAME = $rdname> then

_SQLite_Exec($hdb, "insert ...")

endif

The part in green is not a direct condition. It is the result of something like:

_SQLite_QuerySingleRow($hdb, "select count(*) from names where num = " & $rdn & " or name = " & _SQLite_FastEscape($rdname), $row)

If again row[0] = 0 then no such row exist and you can proceed with the insertion.

yes, that is what I'm trying to do in sql

So what I'm trying to do is: check if $rdn (integer number) already exists in the table (column NUM), if exist I cannot write since that number already exists and to avoid search colisions (I'm thinking like hash algo..) it cannot be wrote, so I display a msgbox error saying that number already exists

Edited February 9, 2013 by DiOgO

[Kyan]

I follow your _SQLite_QuerySingleRow sugestion, and it does not returns a array and sets @error=2 (2 - Call prevented by SafeMode), what is wrong now?

I wrote it like this: _SQLite_QuerySingleRow(1,"select count(*) from NAMES where NUM="&$rdn,$found)

EDIT: Fixed, some idiot set db handle as 1 in stead of -1, btw, i found how to avoid repeated numbers :

CREATE TABLE NAMES (NUM int,NAME not null default '', UNIQUE (NUM));, now if I input a row with a existent NUM in the table, it wont adds to DB

Edited February 10, 2013 by DiOgO

[jchd]

You can make that:

CREATE TABLE NAMES (NUM integer primary key, NAME not null default '')

Now NUM is an alias to the automatically created rowid. Here, the type integer (in full) is important and has distinct semantic from int albeit being both integers.

An integer primary key (autoincrement or not) is different from a unique constraint. The latter will create a new unique index on this column, while primary keys indices are inherently unique and don't need a separate index.

Remember you can always use an ON CONFLICT clause to exactly specify what SQLite should do in case you try to insert (or update to) an already existing value for that column.

[Kyan]

You can make that:

CREATE TABLE NAMES (NUM integer primary key, NAME not null default '')

Now NUM is an alias to the automatically created rowid. Here, the type integer (in full) is important and has distinct semantic from int albeit being both integers.

An integer primary key (autoincrement or not) is different from a unique constraint. The latter will create a new unique index on this column, while primary keys indices are inherently unique and don't need a separate index.

Remember you can always use an ON CONFLICT clause to exactly specify what SQLite should do in case you try to insert (or update to) an already existing value for that column.

I do not follow this topic and since I wasn't especting more comments I do not saw no more it.

Hi jchd,

so using unique() function it creates a index associated to the column I requested to be unique, and using integer primary key doesn't need a separate index to be unique (I presume, less space used by DB and less type to process select commands, right?) from w3school, each table can have only one primary key, here's a quote:

The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns

Not realise the difference between integer and int (I though it was a shortening), in w3school for SQL Server Data Types, appears to integer numbers as int: http://www.w3schools.com/sql/sql_datatypes.asp

[jchd]

Every SQL engine is special and to complicate matters more, there are way too many SQL "standards" to choose from.

For SQLite, a primary key having INTEGER type becomes an alias to the implicit rowid column that exists if you don't specify an INTEGER PRIMARY KEY explicitely. Making that INT PRIMARY KEY spoils this magical behavior and the implicit rowid is created as a distinct column and index. That's how SQLite works and this particular detail is unique to SQLite.