id	summary	reporter	owner	description	type	status	milestone	component	version	severity	resolution	keywords	cc
2012	sqlite3 UPDATE changing value deletes it with UNIQUE index!	jmichae3@…		"I don't ever remember seeing this happen before with a database.
what it's doing is when I have an existing record that has a UNIQUE INDEX on the column, I try to UPDATE database SET fieldname=newvalue WHERE fieldname=oldvalue;
but this is not working.  it actually deletes the entry.  and this is consistent.  as a result, I cannot write my to-do list program.  should I submit the bug report to sqlite folk, or is that something you will do?


$PROGRAM_NAME=""to-do-list""
$PROGRAM_VERSION=""1.0""
$PROGRAM_TITLE+""To-Do List""

#include <Constants.au3>
#include <SQLite.au3>
#include <SQLite.dll.au3>

global $dbdir, $dbpath,$hDB, $hQuery,$hquery,$result,$err


$dbdir=@AppDataCommonDir&""\JimMichaels\""&$PROGRAM_NAME
DirCreate($dbdir)
$dbpath=$dbdir&""\""&$PROGRAM_NAME&"".sqlitedb""
;you can use a header like this in your examples if you want.

_SQLite_Startup ()
If @error > 0 Then
    MsgBox(16, ""SQLite Error"", ""SQLite.dll Can't be Loaded!""&"". report to author jmichae3@yahoo.com"")
    Exit - 1
EndIf
$hDB=_SQLite_Open($dbpath); Open a :memory: database
If @error > 0 Then
    MsgBox(16, ""SQLite Error"", ""Can't Load Database!""&"". report to author jmichae3@yahoo.com"")
	_SQLite_Shutdown()
    Exit - 1
EndIf
If $SQLITE_OK <> _SQLite_Exec($hDB,""CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_id_c PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT '', longdesc TEXT NOT NULL DEFAULT '', priority INTEGER(8) NOT NULL DEFAULT '0', state INTEGER(8) NOT NULL DEFAULT '0', startdt VARCHAR(25) NOT NULL DEFAULT '', duedt VARCHAR(25) NOT NULL DEFAULT '', completeddt VARCHAR(25) NOT NULL DEFAULT '');"") Then
    MsgBox(0,""SQLite Error"",""Error Code1: "" & _SQLite_ErrCode() & @CR & ""Error Message: "" & _SQLite_ErrMsg()&"". report to author jmichae3@yahoo.com"")
	_SQLite_Close($hDB)
	_SQLite_Shutdown()
	exit -1
endif
_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS startdt_idx ON events(startdt);"")
_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS duedt_idx ON events(duedt);"")
_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS completeddt_idx ON events(completeddt);"")
_SQLite_Exec($hDB,""CREATE UNIQUE INDEX IF NOT EXISTS shortdesc_idx ON events(shortdesc);"")
_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS priority_idx ON events(priority);"")
_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS state_idx ON events(state);"")
;_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS _idx ON events(taskname_id);"")
;_SQLite_Exec($hDB,""CREATE INDEX IF NOT EXISTS _idx ON events(taskname_id);"")

	$err=_SQLite_Exec($hDB, ""INSERT INTO todolist(priority,state,shortdesc,startdt,completeddt,duedt,longdesc) VALUES(""&_SQLite_Escape(0)&"",""&_SQLite_Escape(0)&"",""&_SQLite_Escape(""get hp50g calculator batteries"")&"",""&_SQLite_Escape("""")&"",""&_SQLite_Escape("""")&"",""&_SQLite_Escape("""")&"",""&_SQLite_Escape("""")&"");"")

	$err=_SQLite_Exec($hDB, ""UPDATE todolist SET shortdesc=""&_SQLite_Escape(""calc batteries"")&"" WHERE shortdesc=""&_SQLite_Escape(""get hp50g calculator batteries"")&"";"")

local $row[1]
	$err=_SQLite_Query ( $hDB, ""SELECT DISTINCT shortdesc FROM todolist ORDER BY shortdesc ASC;"", $hQuery )
	_SQLite_QueryFinalize ($hQuery)
	While $SQLITE_OK==_SQLite_FetchData ($hQuery, $row)
	     msgbox(0,""out"",$row[0])
	WEnd


"	Bug	closed		AutoIt	3.3.6.1	None	No Bug