17 replies to this topic

[69255]

Edited by 69255, 23 March 2012 - 04:44 PM.

[Th3Gam3]

UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value

no ' needed i think (:

[jchd]

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, 20 February 2012 - 02:04 PM.

[KaFu]

Seems like column names should not contain spaces...

AutoIt        expandcollapse  popup

#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

[jchd]

Yes you can!
See my previous post about how to.

[KaFu]

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

[KaFu]

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.

AutoIt        expandcollapse  popup

#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

[jchd]

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

[jchd]

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

AutoIt        expandcollapse  popup

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

[jchd]

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.

[jchd]

Perfect.

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

[KaFu]

It's always a pleasure to read jchd's posts on SQLite ...

[jchd]

Thanks ! You're making me

[MvGulik]

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

[jchd]

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

[MvGulik]

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, 22 February 2012 - 12:34 PM.

[KaFu]

The function you use to fetch the results is called _SQLite_GetTable2d ...

    For $i = 1 To $i_Rows
        ConsoleWrite($a_Table[$i][0] & @CRLF)
    Next

[jchd]

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.