PapaGeek
User
Jun 22, 2014, 5:24 AM
Post #1 of 6
(39387 views)
|
|
Proper date format for Perl ODBC connection to MS Access
|
Can't Post
|
|
I hope this is the right place to post this question. It deals more with MS Access than with Perl, but the code is written in Perl, so I thought I’d try to get the answer here first.
I’m working on a 401K investment tracking program written in Perl using an ODBC connection to a MS Access database. I’m having trouble inserting records that include dates. The actual code is at the end of this post, the date has been hard coded in the INSERT statement for testing purposes. The sub selects a record from the table with the requested date and if found UPDATEs the record, if not found INSERTs the record. This logic is working fine with the date format as shown in the SELECT statement below:
SELECT * FROM Investments WHERE Date = #21-Jun-2014# AND Account = 2 AND Ticker = 'FSCRX';
INSERT INTO Investments (Account,Ticker,Amount)VALUES (2, 'FSCRX', 0);
The code was changed in the previous example to not inset the date. This insert statement works fine, but of course the record is inserted with a blank date.
I ran the code using a variety of hard coded date formats for the INSERT statement as shown below, each line is a separate run of the test program.
SELECT * FROM Investments WHERE Date = #21-Jun-2014# AND Account = 2 AND Ticker = 'FSCRX';
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, #21-Jun-2014#);
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, '21-Jun-2014');
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, #6-21-2014#);
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, #'6-21-2014'#);
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, #06-21-14#);
INSERT INTO Investments (Account,Ticker,Amount,Date)VALUES (2, 'FSCRX', 0, #06/21/14#);
Each of above INSERT statements give the same error when run with an inserted date:
DBD::ODBC::db prepare failed: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in INSERT INTO statement. (SQL-42000) at C:/…/Investments/DBaccess.pm line 245.
Tk::Error: DBD::ODBC::db prepare failed: [Microsoft][ODBC Microsoft Access Driver]
Syntax error in INSERT INTO statement. (SQL-42000) at C:/…/Investments/DBaccess.pm line 245.
[\&main::AmountChanged,3]
(in validation command executed by entry)
Does anyone know the proper date format for a SQL statement to INSERT a record in a MS Access table?
Here is the actual code with the wanted INSERT date format commented and one of the test formats coded in its place.
ub updateInvestment
{
my ($account, $ticker, $amount, $date) = @_;
my $SQL = "SELECT * FROM Investments";
$SQL .= " WHERE Date = #$date# AND Account = $account AND Ticker = '$ticker';";
say $SQL;
my $sth = $dbh->prepare($SQL);
$sth->execute;
if ($sth->fetchrow_hashref)
{
$SQL = "UPDATE Investments";
$SQL .= " SET Amount = $amount";
$SQL .= " WHERE Date = #$date# AND Account = $account AND Ticker = '$ticker';";
}
else
{
$SQL = "INSERT INTO Investments (Account,Ticker,Amount,Date)";
# $SQL .= "VALUES ($account, '$ticker', $amount, #$date#);";
$SQL .= "VALUES ($account, '$ticker', $amount, '21-Jun-2014');";
}
say $SQL;
my $sth2 = $dbh->prepare($SQL);
$sth2->execute;
} |
MAIN
SEARCH
WHO'S 
LOG 