DBI Oracle insert not working

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

Hey Perl Monks! I have a piece of code which is running across some data and placing it within an Oracle database. The bug which I have found is that 2 of my SQL statements don't work by executing them in perl, but they do work in Oracle SQL Developer:

    my $query = $self->{dbConn}->prepare("INSERT INTO repository(rname
+) VALUES('". $repository->getName ."')");
    my $affected = $query->execute;
    $self->{dbConn}->commit;
    $logger->debug($DBI::errstr || 'no errors found, ' . $affected . '
+ affected');
[download]

This piece of code places a new repository within the database. This generates a log like:

2009/04/02 11:15:15 DEBUG> ResultSQL.pm:62 ResultSQL::prvInsertRepo - 
+Reporting repository
2009/04/02 11:15:15 DEBUG> ResultSQL.pm:65 ResultSQL::prvInsertRepo - 
+INSERT INTO repository(rname) VALUES('testSQL')
2009/04/02 11:15:15 DEBUG> ResultSQL.pm:69 ResultSQL::prvInsertRepo - 
+no errors found, 1 affected
[download]

This next example of code is where the bug is:

  $logger->debug("INSERT INTO script(repoid, sname) SELECT repoid, '" 
+. $scriptN . "' as sname FROM repository WHERE rname='" . $repository
+->getName . "'");
        my $query = $self->{dbConn}->prepare("INSERT INTO script(repoi
+d, sname) SELECT repoid, '" . $scriptN . "' as sname FROM repository 
+WHERE rname='" . $scriptN . "'");
        my $affected = $query->execute;
        $self->{dbConn}->commit;
        $logger->debug($DBI::errstr || 'no errors found, ' . $affected
+ . ' affected');
[download]

This generates this log:

2009/04/02 11:15:15 DEBUG> ResultSQL.pm:78 ResultSQL::prvInsertScripts
+ - Reporting scripts
2009/04/02 11:15:15 DEBUG> ResultSQL.pm:81 ResultSQL::prvInsertScripts
+ - INSERT INTO script(repoid, sname) SELECT repoid, 'tableName' as sn
+ame FROM repository WHERE rname='testSQL'
2009/04/02 11:15:15 DEBUG> ResultSQL.pm:85 ResultSQL::prvInsertScripts
+ - no errors found, 0E0 affected
[download]

There is another method which gets called after this, but it pretty much does the same job but if a different set of data. When I check the database out I would have an entry in the repository table, but nothing else. As I said before, if I take the SQL from my log, paste it into SQL Developer, it will execute as expected.. Any ideas? I am totally stuck.. Cheers in advance ..

Comment on DBI Oracle insert not working Select or Download Code

Replies are listed 'Best First'.
Re: DBI Oracle insert not working by tilly (Archbishop) on Apr 02, 2009 at 12:13 UTC
In your logging statement you make rname `$repository->getName` but in your insert you have `$scripN` instead. So the query that is logged is not the query that is executed. A second error you make is making several calls to DBI without checking for errors and then only checking `$DBI::errstr` at the end. Unfortunately that variable is reset when entering most API calls, so an error in your prepare or execute will no longer be available to see after your commit. This is one reason that I prefer setting RaiseError=>1 on my connect calls. To avoid these problems in the future you could use the following coding pattern: `my $sql = qq{ INSERT INTO script (repoid, sname) SELECT repoid, '$scriptN' as sname FROM repository WHERE rname = '} . $repository->getName . qq{' }; $logger->debug($sql); my $query = $self->{dbConn}->prepare($sql) or die "Can't prepare: $DBI::errstr"; my $affected = $query->execute() or die "Can't execute: $DBI::errstr"; $self->{dbConn}->commit;` [download] In this sample I have not used bind parameters. Bind parameters are important if you do not absolutely trust your incoming data. There are also performance benefits. However when you use them it is slightly easier to log one thing and do another, which is a mistake you just made. So the lesson I chose to focus on is the value of not repeating yourself. Even if you're just saying it twice, say it once, stick it in a variable, and then use that variable twice. This guarantees that the statement logged is the statement actually prepared.	[reply] [d/l] [select]
Re^2: DBI Oracle insert not working by smoky (Novice) on Apr 02, 2009 at 12:32 UTC
All fixed, cheers for the help .. It was not using bound parameters which caused the issue ..	[reply]
Re^3: DBI Oracle insert not working by tilly (Archbishop) on Apr 02, 2009 at 12:56 UTC
You've drawn the wrong lesson. The issue is that you logged one thing and did another, then got confused when the two didn't match. Now you've rewritten it and were lucky enough to rewrite it correctly. But it wasn't the interpolation technique that you wrote it in which made the difference, it is that you didn't make a mistake this time.	[reply]
Re: DBI Oracle insert not working by mje (Curate) on Apr 02, 2009 at 10:44 UTC
There are things missing from this example like the fact you have called commit but we don't know if you disabled AutoCommit or if you are really in a transaction. However, don't concatenate strings into sql, use bound parameters instead and I'd look carefully at your strings and column types as you are using some with leading and trailing white space.	[reply]
Re^2: DBI Oracle insert not working by smoky (Novice) on Apr 02, 2009 at 11:12 UTC
Hey .. Sorry that I didn't meantion about the commits.. I have tried both auto commit and manual commit without any luck :( I shall try bounding the parameters now	[reply]
Re^3: DBI Oracle insert not working by mje (Curate) on Apr 02, 2009 at 12:19 UTC
You should always use bound parameters and not concatenate strings - there are multiple reasons why, commented on perl monks many times before and in the DBI documentation too. The only thing I can think of from the code snippets you provide is that the insert into repository is rolled back or not in the same connection and not committed or in different schemas as the insert for 'testSQL' seems to match the select for 'testSQL'.	[reply]