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 <miesf...@gmail.com>
Till: Open Object Rexx Users <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
