http://qs321.pair.com?node_id=665968

AKSHUN has asked for the wisdom of the Perl Monks concerning the following question:

I'm trying to do a database deletion from an Oracle database. I'm using DBI and the Oracle DBD. I can do inserts with placeholders, but I do not know how to do deletes.

I declare my variable like this.

my $deleteString = qq{ delete from my_table where id = ?};


I originally tried using prepare and execute statements, but to no avail.

my $var = "12345"; $dbh = DBI->connect ("dbi:Oracle:$database",$username,$password) or die $DBI::errstr; if(!$dbh){ print $DBI::errstr; exit 1;} $sth1 = $dbh->prepare($deleteString); $sth1->execute($var) or die "FAILED DELETION";
Then I thought that I should use a "$dbh->do" call, but I don't know how to make it work with placeholders.

Can anyone assist?

UPDATE:
Corion prompted me to check the type of the column that I was using in the where clause. It was a CHAR(6). I was looking for a 5 character string. I padded the string with a trailing space and everything worked properly.

Gentlemen(and/or ladies), thank you for your help.

AKSHUN

Replies are listed 'Best First'.
Re: DBI Deletes with placeholders
by talexb (Chancellor) on Feb 04, 2008 at 14:34 UTC

    Regarding your post, it's customary to wrap code sections in <code> tags. If you could update your post, that would be appreciated.

    Regarding your DBI question, why don't you ask DBI what the problem is by outputting DBI->errstr in your error message, rather than a generic message? Also, I typically make a connection with RaiseError => 1

    Another thing to check is whether the user you're connecting to the database as, has sufficient privilege to delete table rows.

    Alex / talexb / Toronto

    "Groklaw is the open-source mentality applied to legal research" ~ Linus Torvalds

      DBI doesn't return any error. The code runs successfully, but the data remains in the database. I connect manually using SQLPLUS and I can see that the data is still there. I have the necessary privs on the database because I can delete them manually with a query.

      I believe the problem is that I am not properly using placeholders.

        In principle your code and SQL should work. One more thing you can try is to change your SQL from using DELETE to using SELECT - my guess is that your value in $var is wrong.

        Maybe the id column is a CHAR column instead of a number? Is the code you posted the actual code you're running? Maybe you can show us the real, actual code (minus the login to Oracle) you're using?

Re: DBI Deletes with placeholders
by olus (Curate) on Feb 04, 2008 at 15:27 UTC

    Deletes with placeholders are possible with prepare/execute and do ways.

    Like talexb said, you should get the error message from the driver, and with that you could get a better hint on what went wrong.

    my $qry = "delete from my_table where id=?"; my $sth = $dbh->prepare($qry); $sth->execute($value_to_delete) or die DBI->errstr; print "deleted rows = ".$sth->rows; $sth->finish;

    To do the same but using bind values with the do method you can

    my $qry = "delete from my_table where id=?"; $dbh->do($qry, undef, $value_to_delete);
    Also, id is an Oracle reserved word and you should avoid using it.
Re: DBI Deletes with placeholders
by Thilosophy (Curate) on Feb 05, 2008 at 06:00 UTC
    Corion prompted me to check the type of the column that I was using in the where clause. It was a CHAR(6). I was looking for a 5 character string. I padded the string with a trailing space and everything worked properly.

    Yeah, that is a well-known issue on Oracle, making use of the CHAR type rather error-prone.

    CHAR is a fixed length type, and all data gets padded with spaces. If you want to query the table, you also have to pad the constants in your WHERE clause accordingly, or they will not match. When directly interpolating into the SQL string (which is discouraged), Oracle will add the padding automatically, but when using bind variables, it will not. Unless your data is really fixed (and maybe even then) you should consider VARCHAR2.

      Unfortunately, or fortunately depending on one's perspective, I don't get to make any design decisions on this database or table. Basically, it is how it is and I have to work with it. But again, I appreciate everyone's help.

      AKSHUN