Jump to content
Sign in to follow this  
Kyan

SQL Database in autoit

Recommended Posts

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.


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

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


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

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


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

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

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


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

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?

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

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

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:

Posted Image

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


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

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


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

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.

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

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.

#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 :)

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

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.


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

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(...) ?


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

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.


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

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

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

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

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.


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

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


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

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.


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

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  

  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...