subject:"\[sqlite\] UPDATE of field deletes record"

Re: [sqlite] UPDATE of field deletes record

2011-09-17 Thread Igor Tandetnik

Jim Michaels 
 
wrote:
> 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).

Should it be? In which standard is this feature mandated?
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-17 Thread Jim Michaels


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="_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.

--
j...@antichoc.net

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

#include 
#include 
#include 
#include 


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 jmich...@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 
jmich...@yahoo.com")

_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 
jmich...@yahoo.com")

_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 
jmich...@yahoo.com")

_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 jmich...@yahoo.com")

case $err=1
msgbox(0,"OOPS",  "SELECT problem0:Error calling SQLite API 
'sqlite3_prepare'. report to author jmich...@yahoo.com")

case $err=2
msgbox(0,"OOPS",  "SELECT problem0: call prevented by safe 
mode. report to author jmich...@yahoo.com")

endselect

While

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Jean-Christophe Deschamps




> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012

  Are you sure it is gone?

  I'm not familiar with this API, but you appear to be finalizing the
  query BEFORE you extract rows from it.  In the native API, that's not
  valid, making the code report no rows, when it is really trying to
  report an API error.  Depending on how this API maps to the native
  API, the issue may be the SELECT, not the UPDATE.


Jay, you are of course right about pointing out the actual issue the OP 
has: finalizing a prepared statement before retrieving the 
resultset.  BTW the code in the ticket report has _many_ errors (AutoIt 
specific syntax, SQLite [SQL] syntax, missing bracket[s], a.s.o.).


This has nothing to do with SQLite itself, nor even the AutoIt bug 
report tracker, but only the AutoIt support forum where several 
contributors are more than willing to put posters on the right track.


Note: I'm the unofficial maintainer of the current SQLite support for 
AutoIt.



--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Jay A. Kreibich

On Wed, Sep 14, 2011 at 04:28:55PM -0700, Jim Michaels scratched on the wall:
> 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="_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

  Are you sure it is gone?

  I'm not familiar with this API, but you appear to be finalizing the
  query BEFORE you extract rows from it.  In the native API, that's not
  valid, making the code report no rows, when it is really trying to
  report an API error.  Depending on how this API maps to the native
  API, the issue may be the SELECT, not the UPDATE.

   -j

-- 
Jay A. Kreibich < J A Y  @  K R E I B I.C H >

"Intelligence is like underwear: it is important that you have it,
 but showing it to the wrong people has the tendency to make them
 feel uncomfortable." -- Angela Johnson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Jean-Christophe Deschamps



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="_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.

--
j...@antichoc.net  


___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Black, Michael (IS)

May I suggest you print out the SQL that you are building in your script for 
every statement?  I assume you can log it or display it to screen somehow? Then 
you'll see what your problem is and can try and execute those statements in the 
shell...plus you can show us.

And your error checking is not working because your statements have errors, as 
Igor pointed out, but you claims you get no errors.  Plus, you don't check the 
$err return from your statements.

Your create statement errors out but apparently your "if $SQLITE_OK <> ..." 
isn't working.  That's not SQlite's problem.

sqlite> CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT 
entry_id_c PRIMARY KEY AUTOINCREMENT, shortdesc TEXT NOT NULL DEFAULT , 
longdesc TEXT N
OT 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;
Error: near ",": syntax error


Michael D. Black
Senior Scientist
NG Information Systems
Advanced Analytics Directorate




From: sqlite-users-boun...@sqlite.org [sqlite-users-boun...@sqlite.org] on 
behalf of Jim Michaels [j...@jimscomputerrepairandwebdesign.com]
Sent: Wednesday, September 14, 2011 6:28 PM
To: sqlite-users@sqlite.org
Subject: EXT :[sqlite] UPDATE of field deletes record


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="_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.

--
Jim Michaels
j...@jimscomputerrepairandwebdesign.com
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Igor Tandetnik

Jim Michaels 
 
wrote:
> the test code can be seen at
> http://www.autoitscript.com/trac/autoit/ticket/2012

Here's a CREATE TABLE statement from this sample:

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

It's invalid - DEFAULT keyword must be followed by an expression. Let's assume 
you meant DEFAULT '' .

Your example creates a table named "todolist", then purports to create a bunch 
of indexes on a non-existent table "events" (with no error checking). All these 
statements fail.

Anyway, here's a transcript of a SQLite session that attempts to reproduce your 
example to the extent possible:

SQLite version 3.7.2
Enter ".help" for instructions
Enter SQL statements terminated with a ";"
sqlite> CREATE TABLE IF NOT EXISTS todolist (entry_id INTEGER CONSTRAINT entry_i
d_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 '');
sqlite>
sqlite> INSERT INTO todolist(priority,state,shortdesc,startdt,completeddt,duedt,
longdesc) VALUES('0', '0', 'get hp50g calculator batteries', '', '', '', '');
sqlite>
sqlite> UPDATE todolist SET shortdesc='calc batteries' WHERE shortdesc='get hp50
g calculator batteries';
sqlite>
sqlite> SELECT DISTINCT shortdesc FROM todolist ORDER BY shortdesc ASC;
calc batteries
sqlite>

Looks OK to me.
-- 
Igor Tandetnik

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Simon Slavin

On 15 Sep 2011, at 12:28am, Jim Michaels 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="_escape("get calculator batteries")&" WHERE 
> fieldname=sqlite_escape("get hp50g calc cells")&";"
> this code actually deletes the record.  it should not.

An understatement worthy of an Englishman.

What API are you using ?  The function 'sqlite_escape' is not part of SQLite.

SQLite uses single quotes for quoting strings, but I'm not sure you're actually 
quoting strings in the above.

Can you retype your command again ?  You can't have got it right because the 
brackets don't match.

Also, can you test the command using the sqlite3 shell tool, show us exactly 
what you type and tell us what the result is ?

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

2011-09-15 Thread Stephan Beal

On Thu, Sep 15, 2011 at 1:28 AM, Jim Michaels <
j...@jimscomputerrepairandwebdesign.com> wrote:

>  sqlite_exec("UPDATE database SET fieldname="_escape("get calculator
> batteries")&" WHERE fieldname=sqlite_escape("get hp50g calc cells")&";"
>

That code can't possibly evaluate to valid SQL, i think: there's a missing
"& pair before the 2nd sqlite_escape() call.

-- 
- stephan beal
http://wanderinghorse.net/home/stephan/
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] UPDATE of field deletes record

2011-09-15 Thread Jim Michaels

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="_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.


--
Jim Michaels
j...@jimscomputerrepairandwebdesign.com
http://JimsComputerRepairandWebDesign.com
http://JesusnJim.com

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

Re: [sqlite] UPDATE of field deletes record

[sqlite] UPDATE of field deletes record

10 matches

Site Navigation

Mail list logo

Footer information