Jump to content

..


69255
 Share

Recommended Posts

Your problem is that you use single quotes around column names. Single quotes are for enclosing string litterals.

Albeit SQLite does a smart job at silently correctly internally your create table statement, it can't do the same in the where clause, so that where clause simply compares both strings, which obviously fails.

Change your statements so that column names are enclosed in square brackets [column 1] or double quotes "column 2" or even grave accents `column 3`. You wouldn't need that if your column names didn't have white space or conflict with reserved keywords.

Use the same delimiters for any schema name (table, index, column, database alias, view, constraint, foreign key, ...)

EDIT: forgot to mention that enclosing schema names with grave accent is AFAIK an undocumented feature to ease porting to MySQL. Unlike square brackets and double quotes, grave accents seem to have issues in some constructs, so the best bet is to stick to [my column name is beautiful] or "আমার কলামের নাম সুন্দর" (this is the bengali translation of the same name: schema columns are Unicode aware and caseless for lower ASCII only)

Edited by jchd

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)

Link to comment
Share on other sites

Seems like column names should not contain spaces...

#include <array.au3>
#include <SQLite.au3>
#NoTrayIcon
_SQLite_Startup()
If @error Then Exit
_SQLite_Open("Test.db")
If @error Then Exit
_CreateDatabase()
; Query
Global $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Test;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
 _ArrayDisplay($aResult)
Else
 MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
_SQLite_Close()
_SQLite_Shutdown()
FileDelete(@ScriptDir & "Test.db")
Func _CreateDatabase()
 _SQLite_Exec(-1, "BEGIN;")
 _SQLite_Exec(-1, "CREATE TABLE Test ('Column1' TEXT, 'Column2' TEXT, 'Column3' TEXT);")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 1', 'Test 2', 'Test 3');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 4', 'Test 5', 'Test 6');")
 _SQLite_Exec(-1, "UPDATE Test SET Column2 = 'Update Test' WHERE Column1 = 'Test 1';")
 _SQLite_Exec(-1, "COMMIT;")
EndFunc   ;==>_CreateDatabase
Link to comment
Share on other sites

Yes you can!

See my previous post about how to.

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)

Link to comment
Share on other sites

...in square brackets [column 1]

New to me, I like it :)...

Func _CreateDatabase()
 _SQLite_Exec(-1, "BEGIN;")
 _SQLite_Exec(-1, "CREATE TABLE Test ([Column 1] TEXT, [Column 2] TEXT, [Column 3] TEXT);")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 1', 'Test 2', 'Test 3');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 4', 'Test 5', 'Test 6');")
 _SQLite_Exec(-1, "UPDATE Test SET [Column 2] = 'Update Test' WHERE [Column 1] = 'Test 1';")
 _SQLite_Exec(-1, "COMMIT;")
EndFunc   ;==>_CreateDatabase
Link to comment
Share on other sites

One way is to define the columns you want to be unique as UNIQUE :)... This way you get a non fatal error if you try to insert a duplicate row.

#AutoIt3Wrapper_Au3Check_Parameters=-q -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7
#include <array.au3>
#include <SQLite.au3>
#NoTrayIcon
_SQLite_Startup()
If @error Then Exit
_SQLite_Open("Test.db")
If @error Then Exit
_CreateDatabase()
; Query
Global $aResult, $iRows, $iColumns, $iRval
$iRval = _SQLite_GetTable2d(-1, "SELECT * FROM Test;", $aResult, $iRows, $iColumns)
If $iRval = $SQLITE_OK Then
 _ArrayDisplay($aResult)
Else
 MsgBox(16, "SQLite Error: " & $iRval, _SQLite_ErrMsg())
EndIf
_SQLite_Close()
_SQLite_Shutdown()
FileDelete(@ScriptDir & "Test.db")
Func _CreateDatabase()
 _SQLite_Exec(-1, "BEGIN;")
 _SQLite_Exec(-1, "CREATE TABLE Test ([Column 1] TEXT UNIQUE, [Column 2] TEXT UNIQUE, [Column 3] TEXT UNIQUE);")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 1', 'Test 2', 'Test 3');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 4', 'Test 5', 'Test 6');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 1', 'Test 22', 'Test 33');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 12', 'Test 2', 'Test 33');")
 _SQLite_Exec(-1, "INSERT INTO Test VALUES ('Test 32', 'Test 32', 'Test 3');")
 _SQLite_Exec(-1, "UPDATE Test SET [Column 2] = 'Update Test' WHERE [Column 1] = 'Test 1';")
 _SQLite_Exec(-1, "COMMIT;")
EndFunc   ;==>_CreateDatabase
Link to comment
Share on other sites

You don't explicitly state a primary key for this table, so SQLite creates one for you. Its name is rowid or oid and it's defined as integer primary key autoincrement (with integer spelled in full, INT wouldn't do the exact same thing).

You can create an explicit alias for this column e.g. create table test (id integer primary key autoincrement, col1 text, col2 blob, ...);

If you need unicity in one or several columns, you can create a unique index on this/those column(s). You can as well declare a unique constraint on the column(s). Doing both is useless overkill. If you don't need to detect an error when trying to insert a duplicate, you can use the on conflict ignore clause.

OTOH you can also use insert or replace (or more simply replace) but you need to understand that in case the row already exists, then SQLite will first delete the old row, then only insert the new (same) one. This is by design: the rowid will change!

OTOH you can as well use insert or ignore into test (columns...) values (...);

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)

Link to comment
Share on other sites

From what I understand, you want a table having an ID text column with unique constraint and a counter column which holds the occurence count (i.e. insert count) for it.

If that's what you need, you can put SQLite to work on it instead of having to use complex insert statements everytime.

Look, you can achieve the same result by using a once-for-all defined trigger.

Personally I prefer to have the actual DDL (creation statements) separated from the initial data loading, if any.

Try this example (I may have changed some names):

#AutoIt3Wrapper_Au3Check_Parameters=-q -d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6 -w 7
#include "SQLite.au3"
#NoTrayIcon
_SQLite_Startup()
If @error Then Exit
OnAutoItExitRegister("_SQLite_Shutdown")
Global $hDb = _SQLite_Open("TestAutoInc.db")
If @error Then Exit
OnAutoItExitRegister("_CloseAllDBs")
_CreateDatabase($hDb)
_LoadDB($hDb)

Func _CloseAllDBs()
_SQLite_Close($hDb)
EndFunc
Func _LoadDb($hDb)
    Local $s_ID = "A'A"
    _SQLite_Exec($hDb, "BEGIN;")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X($s_ID) & ");")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X($s_ID) & ");")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X('why not ?') & ");")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X($s_ID) & ");")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X('Something else') & ");")
    _SQLite_Exec($hDb, "INSERT INTO Test (id) VALUES (" & X($s_ID) & ");")
    _SQLite_Exec($hDb, "COMMIT;")
EndFunc
Func X($s)
Return("'" & StringReplace($s, "'", "''") & "'")
EndFunc
Func _CreateDatabase($hDb)
    _SQLite_Exec($hDb,  "CREATE TABLE if not exists Test (" & _
      "  ID CHAR NOT NULL, " & _
      "  Counter INTEGER DEFAULT 1, " & _
      "  CONSTRAINT pk PRIMARY KEY (ID COLLATE NOCASE) ON CONFLICT IGNORE" & _
      ");" & _
      "CREATE TRIGGER if not exists trIncOccurCount BEFORE INSERT ON Test " & _
      "WHEN exists (select 1 from Test where id = new.id) " & _
      "BEGIN " & _
      "  update Test set counter = counter + 1 where id = new.id; " & _
      "END;")
; deal with (unlikely) errors here
EndFunc

Come back if something isn't clear.

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)

Link to comment
Share on other sites

All fine. I've place a collate nocase clause on your PK (primary key) to ease queries (only works well for lower ASCII letters (cf. below).

Other general remarks which may help you someday: avoid using SELECT * FROM ... and prefer explicitely naming of columns. That way if ever you need to refactor your DB and for instance insert a new column in the middle, your statement will keep their meaning.

In your first post I see you followed (probably) the bad advice given in some examples of the help file, by using *_Query, *_Fetch*, *_Finalize. Unless you have very specific need for that (and most of the times you don't) use _SQLite_GetTable[2d] or *_QuerySingleRow instead.

It's fairly common to have large SQL statements and this shouldn't frighten you. I do have some spread on circa 100 lines and this pretty normal.

If you have need to handle collation (string comparison/ordering) on non-english text, just chime and I'll give you something useful to handle generic intl Unicode collation.

I've posted an SQLite backup UDF in the example forum, making it easy to backup a DB to/from memory towards memory/disk even if the DB is in use in case you need it someday.

Have fun with SQLite, it's an incredibly powerful engine.

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)

Link to comment
Share on other sites

Perfect.

Good luck with your project and you know where to chime if ever you need.

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)

Link to comment
Share on other sites

Thanks ! You're making me :)

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)

Link to comment
Share on other sites

... and caseless for lower ASCII only)

What do you main? ... Or did you switch ANSI for ASCII? As ASCII is the lower subset of ANSI.

"Straight_and_Crooked_Thinking" : A "classic guide to ferreting out untruths, half-truths, and other distortions of facts in political and social discussions."
"The Secrets of Quantum Physics" : New and excellent 2 part documentary on Quantum Physics by Jim Al-Khalili. (Dec 2014)

"Believing what you know ain't so" ...

Knock Knock ...
 

Link to comment
Share on other sites

No, things are clear to me but too many people confuse ASCII and ANSI so that I rather restrict this way.

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)

Link to comment
Share on other sites

No, things are clear to me but too many people confuse ASCII and ANSI so that I rather restrict this way.

o.O

... I'm obviously missing something simple. But its good to hear things are clear to you. ...

(not sure though that clarity at your end always translates to clarity at the readers end.)

Anyyyyway, Works for me.

[+quote]

Edited by MvGulik

"Straight_and_Crooked_Thinking" : A "classic guide to ferreting out untruths, half-truths, and other distortions of facts in political and social discussions."
"The Secrets of Quantum Physics" : New and excellent 2 part documentary on Quantum Physics by Jim Al-Khalili. (Dec 2014)

"Believing what you know ain't so" ...

Knock Knock ...
 

Link to comment
Share on other sites

You can still use _SQLiteGetTable if you insist on a 1D array. But realize that the UDF has no insight on the structure of the SQL statement.

The general case for results is a 2D array anyway.

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)

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...