Error passing string date from Perl to MSSQL

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

Using Perl I am parsing an fixed width file and fetching few fields that are being pushed to SQL DB later.

my $Date = 20170121;     # Parsed from file
my $file = Testfilename; # Parsed from file
[download]

Iam then making a connection to the DB and then the below lines of code follows

my $sql = "INSERT INTO TableName(Date,FileName) VALUES($Date,$file)";
my $stmt = $settle_dbh->prepare($sql);
$stmt->execute();
[download]

Getting an error stating

DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Inval
+id precision value (SQL-HY104)
[download]

Hence I tried to check what's passing in the $sql, the single quotes seem to be missing. Which I feel might be the issue.

print $sql;
INSERT INTO TableName(Date,FileName) VALUES(20170121,Testfilename)
[download]

Can you please help me on how to get this resolved, may be in two ways which I could think of

1. Convert the date in Perl to format 2017-01-21 00:00:00.000

2. Passing the values in single quotes to SQL DB

Comment on Error passing string date from Perl to MSSQL Select or Download Code

Replies are listed 'Best First'.
Re: Error passing string date from Perl to MSSQL by Corion (Patriarch) on Apr 20, 2017 at 07:53 UTC
What's wrong with simply adding the required single quotes? `my $sql = "INSERT INTO TableName(Date,FileName) VALUES('$Date',$file)" +;` [download] Or even better, use DBI placeholders: `my $sql = 'INSERT INTO TableName(Date,FileName) VALUES(?,?)'; my $stmt = $settle_dbh->prepare($sql); $stmt->execute($Date, $file); # no quoting needed` [download]	[reply] [d/l] [select]
Re^2: Error passing string date from Perl to MSSQL by JobyJ (Initiate) on Apr 20, 2017 at 09:03 UTC
Awesome the my $sql = "INSERT INTO TableName(Date,FileName) VALUES('$Date',$file)"; , solved the issue. Also can you let me know how the convert the date into the format 2017-01-21 00:00:00.000 before passing it to SQL.	[reply]
Re^3: Error passing string date from Perl to MSSQL by Corion (Patriarch) on Apr 20, 2017 at 09:05 UTC
My approach to splitting up a date into its constituent parts is a regular expression: `if( $Date !~ /^(20\d\d)([01]\d)([0123]\d)$/ ) { die "Invalid date '$Date'; } $Date = "$1-$2-$3 00:00:00.000";` [download]	[reply] [d/l]
Re^3: Error passing string date from Perl to MSSQL by MidLifeXis (Monsignor) on Apr 20, 2017 at 17:28 UTC
Depending on the lineage of the data, I would highly recommend looking at the placeholder information, or heavily validating that the data is as expected. Inadvertent or intentionally bad data can break things otherwise (see bobby tables, for example). While not a panacea, both placeholders and heavy data validation of content originating from potentially hostile sources are very good tools to know how to use. --MidLifeXis	[reply]
Re^3: Error passing string date from Perl to MSSQL by chacham (Prior) on Apr 20, 2017 at 18:34 UTC
Please try Corion's second suggestion to use placeholders. The query as it is currently written uses variables in the actual statement, which is known as dynamic sql. Amongst the many issues with dynamic sql is that it is not secure. Using placeholders will make it secure, and can end up helping with other issues as well.	[reply]
Re^3: Error passing string date from Perl to MSSQL by cormanaz (Deacon) on Apr 20, 2017 at 18:01 UTC
You can use Date::Manip to parse the date, and use the UnixDate function to format it.	[reply]