CGI/Perl Guide | Learning Center | Forums | Advertise | Login
Site Search: in

  Main Index MAIN
INDEX
Search Posts SEARCH
POSTS
Who's Online WHO'S
ONLINE
Log in LOG
IN

Home: Perl Programming Help: Advanced:
Proper date format for Perl ODBC connection to MS Access

 



PapaGeek
User

Jun 22, 2014, 5:24 AM

Post #1 of 6 (37842 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 Id try to get the answer here first.

Im working on a 401K investment tracking program written in Perl using an ODBC connection to a MS Access database. Im 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:


Code
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.


Code
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:




Code
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.


Code
 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;
}



FishMonger
Veteran / Moderator

Jun 22, 2014, 9:04 AM

Post #2 of 6 (37708 views)
Re: [PapaGeek] Proper date format for Perl ODBC connection to MS Access [In reply to] Can't Post

It's been 20+ years since I used MS Access and never from within a Perl script, but I have a couple suggestions.

Using a bare #21-Jun-2014# in the statement would/should be wrong when using DBI/DBD::ODBC. At the very least it should be quoted i.e. '#21-Jun-2014#'.

However, I suspect that simply simply quoting it would still fail. You probably need to use #21-Jun-2014# as an arg to one of Access's functions, such as the format(...) function.
http://office.microsoft.com/en-us/access-help/format-function-HA001228839.aspx
http://www.techonthenet.com/access/functions/date/format.php


PapaGeek
User

Jun 22, 2014, 12:52 PM

Post #3 of 6 (37568 views)
Re: [FishMonger] Proper date format for Perl ODBC connection to MS Access [In reply to] Can't Post

Finally found a reference that talked about field names in MS Access. Since my field is called Date, for some reason it works fine in the select statement and the update statement, but the insert statement requires the field name to be bracketed in [] braces.

The code now reads:

Code
    $SQL  = "INSERT INTO Investments (Account,Ticker,Amount,[Date])"; 
$SQL .= "VALUES ($account, '$ticker', $amount, #$date#);";


And the select and insert statement traces are:


Code
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#);


The square brackets made it work!


(This post was edited by PapaGeek on Jun 22, 2014, 6:39 PM)


Laurent_R
Veteran / Moderator

Jun 22, 2014, 11:27 PM

Post #4 of 6 (37148 views)
Re: [FishMonger] Proper date format for Perl ODBC connection to MS Access [In reply to] Can't Post

I do not know for Access, but in most database engines I have used, you need to convert date strings into actual internal date formats before storing data into a database (for example a "to_date()" function). But it depends on the format of your data column and there may be shortcuts that I am not aware of in Access.


PapaGeek
User

Jun 23, 2014, 5:45 AM

Post #5 of 6 (36907 views)
Re: [Laurent_R] Proper date format for Perl ODBC connection to MS Access [In reply to] Can't Post

I am familiar with the date string issues in database engines. As I was doing the research on this problem I found that this or a similar problem was posted on many boards and I followed all of the different suggestions, hence the list of various formats that I used, and what I posted was only a partial list that I remembered!

As I see it now, there seems to be a glitch in the ODBC SQL preparation to the MS Access database. The proper format for a date is, or at least includes, #21-Jun-2014#, but the problem was that I called the field a reserved name, Date, and that field name does not cause a problem in a SELECT or UPDATE $dbh->prepare($SQL) statement, but does cause the given errors when Date is used in an INSERT prepare statement.

Im updating this post here so that others might find it and realize the in some cases there are reserved words that act differently in different situations.


Laurent_R
Veteran / Moderator

Jun 23, 2014, 10:12 AM

Post #6 of 6 (36746 views)
Re: [PapaGeek] Proper date format for Perl ODBC connection to MS Access [In reply to] Can't Post

Be it in Perl or in a database or more generally, it is a relatively bad idea to call a variable or column indentifier "date", because it does not say enough about the content. I would sugest that you use name such as start_date, end_date, termination_date, next_bill_date, last_invoice_date, birth_date, action_date, match_date, etc. Not only is this much clearer, but the chances of accidental collision with a key or reserved word are considerably smaller.

 
 


Search for (options) Powered by Gossamer Forum v.1.2.0

Web Applications & Managed Hosting Powered by Gossamer Threads
Visit our Mailing List Archives