I really do not know where the problem was, but changing the upper(name) to
LIKE name
caused the delete to occur. AutoCommit was found to be on, and single quotes
around the
name also did not help. I then went back to what I *thought* was my original
statement,
and that is working now too. There must have been leading or trailing blanks
in the
search target due to my coding, but I did not see it before changing it to use
LIKE. Thanks for all
the suggestions and samples – they are what let me to actually get it to work.
This list
certainly is much better and responsive that any other technical support I have
used in the past.
From: hakan
Sent: Thursday, January 03, 2013 4:37 AM
To: Open Object Rexx Users
Subject: Re: [Oorexx-users] Must be something wrong - can't delete a row in
ooSQLite - ooSQLite3 works okay
Art
You can also use the LIKE op
DELETE FROM table WHERE name LIKE 'SoMe NamE';
LIKE is case insensitive by default, but only works for LATIN1 characters.
/hex
----- Ursprungligt Meddelande -----
Från: Mark Miesfeld mailto:miesf...@gmail.com
Till: Open Object Rexx Users mailto:oorexx-users@lists.sourceforge.net
Kopia:
Datum: torsdag, 03 januari 2013 04:42
Ämne: Re: [Oorexx-users] Must be something wrong - can't delete a row in
ooSQLite - ooSQLite3 works okay
On Wed, Jan 2, 2013 at 7:01 PM, Art Heimsoth <artst...@artheimsoth.com> wrote:
Perhaps, but I think autoCommit is the default with ooSQLite and
so far I have not found the syntax for Rollback or Commit to
try that - are they implemented in ooSQLite? or must the hooks
be used? However, in playing with the sample programs included
with ooSQLite, it appears that the DELETE is working there - so it
must be something else in my program.
Art,
Yes auto commit should be the default. It is something else. It could be your
quoting:
DELETE FROM table WHERE upper(name) = "SOME NAME"
It is hard to say what your actually Rexx code is from that. But, I think
SOME NAME needs to be in single quotes.
Here is a test program that works. You can run it using the ooFoods.rxdb that
is shipped with ooSQLite.
It goes to some lengths to check that the row for Cinnamon Bobka exists and
to add it back in if the delete is successful. But, it works. Comment out the
last bit of code that adds the row back in after the delete and you can see for
sure it works.
You'll need to fix up the lines that wrap, they should be easy to spot. Note
that I use single quotes for Cinnamon Bobka.
/* deleteTest.rex */
db = .ooSQLiteConnection~new('ooFoods.rdbx')
if db~initCode <> db~ok then do
-- report error
return 99
end
say 'Checking for Cinnamon Bobka'
stmt = .ooSQLiteStmt~new(db, "SELECT * FROM foods WHERE name='Cinnamon
Bobka';")
if stmt~initCode <> db~ok then do
say 'Line:' .line 'Statement error - error msg:' stmt~lastErrMsg
stmt~finalize
db~close
return 99
end
i = 0
do while stmt~step == stmt~ROW
i += 1
say 'Got a row ('i')'
say stmt~columnText(1)~left(10) || stmt~columnText(2)~left(15) ||
stmt~columnText(3)~left(15)
end
stmt~finalize
say
if i == 0 then do
say 'No row for Cinnamon Bobka. Going to add that row.'
stmt = .ooSQLiteStmt~new(db, "INSERT INTO foods (name, type_id) VALUES
('Cinnamon Bobka', 1);")
ret = stmt~step
if ret <> db~done then do
say 'Line:' .line 'Step return error:' ret
stmt~finalize
db~close
return 99
end
stmt~finalize
end
stmt = .ooSQLiteStmt~new(db, "DELETE FROM foods WHERE name='Cinnamon Bobka';")
if stmt~initCode <> db~ok then do
say 'Line:' .line 'Statement error - error msg:' stmt~lastErrMsg
stmt~finalize
db~close
return 99
end
ret = stmt~step
if ret <> db~done then do
say 'Line:' .line 'Step return error:' ret
stmt~finalize
db~close
return 99
end
stmt~finalize
say 'Checking that Cinnamon Bobka row was deleted'
stmt = .ooSQLiteStmt~new(db, "SELECT * FROM foods WHERE name='Cinnamon
Bobka';")
if stmt~initCode <> db~ok then do
say 'Line:' .line 'Statement error - error msg:' stmt~lastErrMsg
stmt~finalize
db~close
return 99
end
i = 0
do while stmt~step == stmt~ROW
i += 1
say 'Got a row ('i')'
say stmt~columnText(1)~left(25) || stmt~columnText(2)~left(25) ||
stmt~columnText(3)~left(25)
say 'Delete failed.'
end
stmt~finalize
say
if i == 0 then do
say 'No row for Cinnamon Bobka. Delete was successful.'
say 'Going to add that row back in.'
stmt = .ooSQLiteStmt~new(db, "INSERT INTO foods (name, type_id) VALUES
('Cinnamon Bobka', 1);")
ret = stmt~step
if ret <> db~done then do
say 'Line:' .line 'Step return error:' ret
stmt~finalize
db~close
return 99
end
stmt~finalize
end
ret = db~close
return ret
::requires 'ooSQLite.cls'
--
Mark Miesfeld
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
Oorexx-users mailing list
Oorexx-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-users
--------------------------------------------------------------------------------
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
--------------------------------------------------------------------------------
_______________________________________________
Oorexx-users mailing list
Oorexx-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-users
------------------------------------------------------------------------------
Master Visual Studio, SharePoint, SQL, ASP.NET, C# 2012, HTML5, CSS,
MVC, Windows 8 Apps, JavaScript and much more. Keep your skills current
with LearnDevNow - 3,200 step-by-step video tutorials by Microsoft
MVPs and experts. ON SALE this month only -- learn more at:
http://p.sf.net/sfu/learnmore_122712
_______________________________________________
Oorexx-users mailing list
Oorexx-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/oorexx-users
