Jump to content

This site uses cookies. By continuing to browse the site you are agreeing to our use of cookies. Find out more here. X
X


Photo

..


  • Please log in to reply
17 replies to this topic

#1 69255

69255

    Adventurer

  • Active Members
  • PipPip
  • 131 posts

Posted 20 February 2012 - 01:31 PM

Posted Image

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








#2 Th3Gam3

Th3Gam3

    Seeker

  • Normal Members
  • 4 posts

Posted 20 February 2012 - 01:48 PM

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

no ' needed i think (:

#3 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 20 February 2012 - 01:50 PM

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#4 KaFu

KaFu

    Hey, it's just me, KhaFoo...

  • MVPs
  • 3,382 posts

Posted 20 February 2012 - 01:56 PM

Seems like column names should not contain spaces...

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


#5 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 20 February 2012 - 01:58 PM

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#6 KaFu

KaFu

    Hey, it's just me, KhaFoo...

  • MVPs
  • 3,382 posts

Posted 20 February 2012 - 02:00 PM

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


#7 KaFu

KaFu

    Hey, it's just me, KhaFoo...

  • MVPs
  • 3,382 posts

Posted 20 February 2012 - 02:40 PM

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


#8 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 20 February 2012 - 02:50 PM

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#9 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 21 February 2012 - 02:43 PM

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#10 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 22 February 2012 - 07:21 AM

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.

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#11 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 22 February 2012 - 08:07 AM

Perfect.

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#12 KaFu

KaFu

    Hey, it's just me, KhaFoo...

  • MVPs
  • 3,382 posts

Posted 22 February 2012 - 08:39 AM

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

#13 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 22 February 2012 - 09:54 AM

Thanks ! You're making me :)

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#14 MvGulik

MvGulik

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 2,800 posts

Posted 22 February 2012 - 10:56 AM

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

#15 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 22 February 2012 - 11:39 AM

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

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.


#16 MvGulik

MvGulik

    Universalist

  • Active Members
  • PipPipPipPipPipPip
  • 2,800 posts

Posted 22 February 2012 - 12:32 PM

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.


#17 KaFu

KaFu

    Hey, it's just me, KhaFoo...

  • MVPs
  • 3,382 posts

Posted 22 February 2012 - 12:48 PM

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


#18 jchd

jchd

    Whatever your capacity, resistance is futile.

  • MVPs
  • 4,924 posts

Posted 22 February 2012 - 01:24 PM

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.

SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!

SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)

An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.

 

SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.

 

PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

RegExp tutorial: enough to get started

Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.





0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users