on delete cascade oracle perldbi

[xhoster]

Hi
I get message 'Child record found' when deleting parents in Oracle.
How can I from perl dbi instruct Oracle to delete also child when I
delete parents?

The same way you would do it were you not using Perl.

Xho

 

[ngoc]

Hi
I get message 'Child record found' when deleting parents in Oracle.
How can I from perl dbi instruct Oracle to delete also child when I
delete parents?
Thanks

 

[Paul Lalli]

I get message 'Child record found' when deleting parents in Oracle.
How can I from perl dbi instruct Oracle to delete also child when I
delete parents?

my $sql = "WHATEVER COMMAND DOES THIS IN ORACLE";
$dbh->do($sql) or die "SQL Command failed: " . $dbh->errstr;


Paul Lalli

 

[Brian Wakem]

my $sql = "WHATEVER COMMAND DOES THIS IN ORACLE";
$dbh->do($sql) or die "SQL Command failed: " . $dbh->errstr;

I don't think you can 'do' a delete command, you'd have to prepare and
execute it.

 

[Paul Lalli]

I don't think you can 'do' a delete command, you'd have to prepare and
execute it.

..... which is precisely what do() does. From perldoc DBI:
==================================================
do

$rows = $dbh->do($statement) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr) or die $dbh->errstr;
$rows = $dbh->do($statement, \%attr, @bind_values) or die ...

Prepare and execute a single statement. Returns the number of rows
affected or undef on error. A return value of -1 means the number of
rows is not known, not applicable, or not available.
This method is typically most useful for non-SELECT statements that
either cannot be prepared in advance (due to a limitation of the
driver) or do not need to be executed repeatedly. It should not be used
for SELECT statements because it does not return a statement handle (so
you can't fetch any data).

The default do method is logically similar to:

sub do {
my($dbh, $statement, $attr, @bind_values) = @_;
my $sth = $dbh->prepare($statement, $attr) or return undef;
$sth->execute(@bind_values) or return undef;
my $rows = $sth->rows;
($rows == 0) ? "0E0" : $rows; # always return true if no error
}

For example:

my $rows_deleted = $dbh->do(q{
DELETE FROM table
WHERE status = ?
}, undef, 'DONE') or die $dbh->errstr;
==================================================

Paul Lalli

 

[J. Gleixner]

Hi
I get message 'Child record found' when deleting parents in Oracle.
How can I from perl dbi instruct Oracle to delete also child when I
delete parents?

You'd handle it just like you'd handle other error conditions, by using
eval.

Take a look at the documentation for eval (perldoc -f eval) and for DBI
as to how to handle error conditions. Show your code if you're unable to
get this to work.

Straight out of the documentation for DBI:

eval {
...
$sth->execute();
...
};
if ($@) {
# $sth->err and $DBI::err will be true if error was from DBI
warn $@; # print the error
... # do whatever you need to deal with the error
}


You could check $@ to see if it contains 'Child record found'.