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.

In reply to SOLVED: DBI Problem by Zarquav

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.