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

I'm having some odd problems with DBI and I'm hoping someone can shed some light on what's going on here. Here's a snippet of code: 
my $insertMACDDataSQL = "INSERT INTO `%s` (date, stockId, macdSignal, 
+macd, macdHistogram, macdIsAsOfClose) VALUES ('%s', %s, %s, %s, %s, %
+s)
ON DUPLICATE KEY UPDATE macdSignal = VALUES(macdSignal), macd = VALUES
+(macd), macdHistogram = VALUES(macdHistogram), macdIsAsOfClose = VALU
+ES(macdIsAsOfClose);";

...


my $sql = sprintf($insertMACDDataSQL, $stock, $MACDDate, $stockId, $MA
+CDSignal, $MACD, $MACDHist, $MACDIsAsOfClose);
print $sql."\n";
my $result = $dbHandle->do($sql);
print Data::Dumper::Dumper($result)."\n";

[download]
The code "works", as in there are no errors. The console output ends up looking like this: 
INSERT INTO `TSE:AC` (date, stockId, macdSignal, macd, macdHistogram, 
+macdIsAsOfClose) VALUES ('2007-01-09', 2191, -0.0991, 0.0500, 0.1491,
+ 1)
ON DUPLICATE KEY UPDATE macdSignal = VALUES(macdSignal), macd = VALUES
+(macd), macdHistogram = VALUES(macdHistogram), macdIsAsOfClose = VALU
+ES(macdIsAsOfClose);
$VAR1 = 1;

[download]

So the # rows affected returned by the DBI's do call is printed out as 1, suggesting it all worked just as expected. However, the database table is still empty.

Other DBI calls I've made to the same database have worked, so I know I'm connecting properly and talking to the right database. Although I did have a similar problem to this recently that was somehow solved by changing the order of the SQL queries I was running (which also made no sense to me)

Furthermore, I can copy/paste the printed out SQL query to a SQL console and it executes perfectly AND actually adds (or updates) the row to the table.

The questions.. Why is this not working through DBI? And why is DBI's return value suggesting it is working if it's not? My only theory is that DBI is handling the back ticks poorly on the table name in the query. Since back ticks are special characters in perl.. But it's in a string, so it really shouldn't cause a problem I don't think.. Either way, it seems like there's a bug in DBI.. It shouldn't report 1 row affected if it didn't do anything or if it failed.

Update - Just eliminated my theory that the back ticks were the problem by trying it on tables without special characters in the name and removing the back ticks from the insert query.

Update 2 - Thanks everyone! Responses guided me to the fact I didn't have autocommit set on connecting to the DB, and I wasn't explicitly calling commit. I'm still a bit surprised that my previous scripts connecting the same way did commit, but I'm guessing that maybe table creates/drops are not transactionable, so those calls were forcing a commit anyways.

Replies are listed 'Best First'.
Re: DBI Problem
by Corion (Patriarch) on Jan 29, 2018 at 19:43 UTC

    Did you create your database handle with RaiseError => 1 ?

    Otherwise, DBI has very little in ways of communicating to you what might be going wrong.

    I don't really see why/where your INSERT statement would go wrong, but maybe it has something to do with the credentials you use in your Perl script as opposed tothe credentials of whatever other tool you use.

[reply]
[d/l]
[select]
      Yes, I did. Here's the DBI connect line: 
      my $dbHandle = DBI->connect($dbSource, $dbUserName, $dbPassword, {Rais
+eError => 1, AutoCommit => 0});

      [download]
      As for credentials, I'm connecting to the DB both in code and through the SQL console with 'root' user.. So unless there's some other specific credentials being used by the SQL console.... But.. if it was a credentials issue, shouldn't DBI be returning an error, not suggesting that 1 row was affected?
[reply]
[d/l]

        AutoCommit => 0

        means you have to explicitly call $dbh->commit or your changes will be rolled back.

[reply]
[d/l]
[select]
Re: DBI Problem
by thanos1983 (Parson) on Jan 29, 2018 at 20:02 UTC

    Hello Zarquav,

    Welcome to the Monastery. As fellow Monk Corion connect to your DB like this (pseudo code):

    DBI->connect_cached( $dsn, $username, $auth, {
    PrintError => 1,
    RaiseError => 1,
    AutoInactiveDestroy => 1})or die "Could not connect to: " $DBI::er
+rstr ."\n";

    [download]

    You can read in the documentation about those errors DBI/AutoInactiveDestroy. After setting the errors and connecting to your DB prepare you select statement like this (pseudo code):

    my $checkExist = $dbh->prepare("INSERT IGNORE INTO your statement here
+");

if (!$checkExist->execute()) {
    die "Error: ". $checkExist->errstr ."\n";
}

    [download]

    Or you can do it as recommended from the documentation DBI/RaiseError:

    use Try::Tiny;

try {
    ...
    $sth->execute();
    ...
} catch {
    # $sth->err and $DBI::err will be true if error was from DBI
    warn $_; # print the error (which Try::Tiny puts into $_)
    ... # do whatever you need to deal with the error
};

    [download]

    Hope this helps. Let us know if it resolved your problem or guided you to your solution, BR.

    Seeking for Perl wisdom...on the process of learning...not there...yet!
[reply]
[d/l]
[select]

Back to Seekers of Perl Wisdom