On 9/15/2011 6:09 AM, Jean-Christophe Deschamps wrote:
with an existing record, and a fieldname that is in a column that has
a UNIQUE INDEX, if I do sqlite_exec("UPDATE database SET
fieldname="&sqlite_escape("get calculator batteries")&" WHERE
fieldname=sqlite_escape("get hp50g calc cells")&";"
this code actually deletes the record. it should not. I don't ever
remember seeing this happen in any database like pgsql or mysql. I
can test again to see for sure.
the test code can be seen at
http://www.autoitscript.com/trac/autoit/ticket/2012
I used escape on the strings. existing examples do not put single
quotes on the strings. if single quotes are required to mske strings
work as I think they do, please tell me. I am not getting errors.
Could you please post your question on the AutoIt help forum? It's
there for a reason.
BTW yes you're getting errors but you don't test for them.
More on the AutoIt help forum rather than on this list.
--
<mailto:[email protected]>[email protected]
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 <[email protected]>
; 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 [email protected]")
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
[email protected]")
_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
[email protected]")
_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
[email protected]")
_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 [email protected]")
case $err=1
msgbox(0,"OOPS", "SELECT problem0:Error calling SQLite API
'sqlite3_prepare'. report to author [email protected]")
case $err=2
msgbox(0,"OOPS", "SELECT problem0: call prevented by safe
mode. report to author [email protected]")
endselect
While $SQLITE_OK==_SQLite_FetchData ($hQuery, $aRow)
$s&=""""&$aRow[0]&""""&@crlf
WEnd
_SQLite_QueryFinalize ($hQuery)
msgbox(0,"dumpfield:"&$fieldname,$s)
endfunc
--
Jim Michaels
[email protected]
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
