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@…")
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@…")

_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@…")

_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

[anonymous]

new information: the code I posted did not create indexes for the database. I just fixed that in my program, but not here.

regardless, even though a block of code did not create indexes, the update still deleted my records consistently.

[anonymous]

I found a bug in my program where it is taking the new value from a blank textbox. but this should not make any difference. the codeexample I have here is incorrect for the problem. the new value should be set to an empty string for it to delete the record. this is improper behavior.

[anonymous]

btw, there is still a bug in sqlite3.

[jchd]

You shouldn't be posting supposed "bugs" there without checking with others users in the help forum first.
Then your post doesn't follow ticket posting guidelines and your code has numerous errors.

[Valik]

Closing this ticket. Seems to be a long way from actually have a bug to report. I'm not interested in bugs filed against 3.3.6.1 anyway since we are deep into beta territory.

[anonymous]

cancel the bug report. further testing revealed that it does NOT exhibit a bug, except for the fact that I can't do multirow INSERTs (that, unfortunately is not in the manual, and should be a standard feature). there is usually a limit as to how many rows you can do at once.
BTW, I have fixed my original program and test code and released my to-do-list program as of today.

the new test code looks like this:
obviously, the author of autoit (autoitscript.com) has made the language automatically insert single quotes for us, because his example code shows no single quotes, and this code works, even with a single quote.

I also had a bug in my debug output routine where I wasn't concatenating strings.

;===============================================================================
;
; Program Name: to-do-list
; Description: to-do list
; Requirement(s): None
; Return Value(s): None
; Author(s): Jim Michaels <jmichae3@…>
; Create Date: 9/16/2011
; Current Date: 9/16/2011
;
;the test will be to see if SQLITE3 will delete the record that contains "that's it" by an UPDATE (no, it does not)
;
;===============================================================================

$PROGRAM_NAME="sqlite-bug-test"
$PROGRAM_TITLE="SQLIte3 Bug Test"
$PROGRAM_VERSION="1.1"

#AutoIt3Wrapper_au3check_parameters=-d -w 1 -w 2 -w 3 -w 4 -w 5 -w 6
#include <WindowsConstants.au3>
#include <GUIConstantsEx.au3>

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

Global $dbfilename, $hDB, $hQuery, $hquery, $aRow

$dbdir=@AppDataCommonDir&"\JimMichaels\"&$PROGRAM_NAME
DirCreate($dbdir)
$dbpath=$dbdir&"\"&$PROGRAM_NAME&".sqlitedb"

_SQLite_Startup ()
If @error > 0 Then

MsgBox(16, "SQLite Error", "SQLite.dll Can't be Loaded!. report to author jmichae3@…")
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@…")
_SQLite_Shutdown()
Exit - 1

EndIf
If $SQLITE_OK <> _SQLite_Exec($hDB,"CREATE TABLE IF NOT EXISTS test (" _

& "entry_id INTEGER CONSTRAINT entry_id_c PRIMARY KEY AUTOINCREMENT, " _
& "shortdesc TEXT NOT NULL DEFAULT " _
& ");") Then
MsgBox(0,"SQLite Error","Error Code1: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()&". report to author jmichae3@…")
_SQLite_Close($hDB)
_SQLite_Shutdown()
exit -1

endif
;_SQLite_Exec($hDB,"CREATE UNIQUE INDEX IF NOT EXISTS shortdesc_idx ON todolist(shortdesc);")

_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (1,"&_SQLite_Escape("that's it")&");")
_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (2,'b');")
_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (3,'c');")
_SQLite_Exec($hDB,"INSERT INTO test(entry_id,shortdesc) VALUES (4,'d');")

If $SQLITE_OK <> _SQLite_Exec($hDB,"UPDATE test SET shortdesc="&_SQLite_Escape("")&" WHERE shortdesc="&_SQLite_Escape("that's it")&";") Then

MsgBox(0,"SQLite Error","Error Code1: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()&". report to author jmichae3@…")
_SQLite_Close($hDB)
_SQLite_Shutdown()
exit -1

endif

;output shortdesc column to messagebox
dumpfieldname("test","shortdesc")

;output db column plainly to messagebox
func dumpfieldname($tablename,$fieldname)

local $s=""
$err=_SQLite_Query ( $hDB, "SELECT "&$fieldname&" FROM "&$tablename&";", $hQuery )
select
case $err==$SQLITE_OK
case $err==-1

msgbox(0,"OOPS", "SELECT prob0: Error Code: " & _SQLite_ErrCode() & @CRLF & "Error Message: " & _SQLite_ErrMsg()&". report to author jmichae3@…")

case $err=1

msgbox(0,"OOPS", "SELECT problem0:Error calling SQLite API 'sqlite3_prepare'. report to author jmichae3@…")

case $err=2

msgbox(0,"OOPS", "SELECT problem0: call prevented by safe mode. report to author jmichae3@…")

endselect

While $SQLITE_OK==_SQLite_FetchData ($hQuery, $aRow)

$s&=""""&$aRow[0]&""""&@crlf

WEnd
_SQLite_QueryFinalize ($hQuery)
msgbox(0,"dumpfield:"&$fieldname,$s)

endfunc

[jmichae3@…]

sorry, the forum links to here.