I am using python 2.7 and the cx_Oracle module. When I try to run the following query:
UPDATE bs_cart
SET qty = qty + :moreBooks
WHERE userid = :userID
AND isbn = :bookNumber;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO bs_cart
(userid,isbn)
VALUES
(:userID,:bookNumber)
using Cursor.execute()
from cx_Oracle
I get the following error:
DatabaseError: ORA-00911: invalid character
When I put it in SQL plus it says:
SP2-0734: unknown command beginning "IF SQL%ROW..." - rest of line ignored.
I am trying to make the cart UPDATE
if a user already has the selected book in the cart and INSERT
if there are no current copies of the book they want in the cart.
The execute method looks like:
ds.execute(sql,moreBooks=howMany,userID=userLoggedIn,bookNumber=booksToBuy)
and each of the parameters are user generated using rawinput()
and then checked against a regular expression.
You need to enclose your statements in begin
/end
Oracle blocks.
Something like:
BEGIN
UPDATE bs_cart
SET qty = qty + :moreBooks
WHERE userid = :userID
AND isbn = :bookNumber;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO bs_cart (userid,isbn)
VALUES (:userID,:bookNumber);
END;