Re^3: DBD::ODBC not support

I haven't done much SQL, and I haven't dealt with passing dates. Perhaps you need to specify the type of the argument.

use DBI qw( :sql_types );

my $sth = $dbh->prepare('
    ...
');

$sth->bind_param(1, $_[1]);
$sth->bind_param(2, $hdr_tran_seq_nbr);
$sth->bind_param(3, $loader_start_time, SQL_DATETIME);
$sth->bind_param(4, $loader_start_time, SQL_DATETIME);
$sth->bind_param(5, $loader_start_time, SQL_DATETIME);
$sth->bind_param(6, $master_job_no);
$sth->bind_param(7, $_[0]);

$sth->execute();
[download]

Comment on Re^3: DBD::ODBC not support Download Code

Replies are listed 'Best First'.
Re^4: DBD::ODBC not support by mje (Curate) on May 12, 2010 at 15:11 UTC
The best way to pass datetimes to SQLServer via ODBC is using the ODBC syntax: `$sth->bind_param($param_n, q/{ts '1998-05-11 00:00:00'}/);` [download] In your case you have an additional problem since the datetime parameters occur in a function so some MS SQL Server versions will be unable to describe the parameter correctly. As a result, if the parameter occurs in a function you might have to do the following to be sure it works: `$sth->bind_param($param_n, q/{ts '1998-05-11 00:00:00'}/, SQL_VARCHAR) +;` [download] See ODBC Datetime Format. I could go in to why using SQL_DATETIME is not a good idea but it would be rather lengthy. Perhaps I should write a FAQ on this for DBD::ODBC.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re^4: DBD::ODBC not support
by mje (Curate) on May 12, 2010 at 15:11 UTC

The best way to pass datetimes to SQLServer via ODBC is using the ODBC syntax:

$sth->bind_param($param_n, q/{ts '1998-05-11 00:00:00'}/);
[download]

In your case you have an additional problem since the datetime parameters occur in a function so some MS SQL Server versions will be unable to describe the parameter correctly. As a result, if the parameter occurs in a function you might have to do the following to be sure it works:

$sth->bind_param($param_n, q/{ts '1998-05-11 00:00:00'}/, SQL_VARCHAR)
+;
[download]

See ODBC Datetime Format.

I could go in to why using SQL_DATETIME is not a good idea but it would be rather lengthy. Perhaps I should write a FAQ on this for DBD::ODBC.

[reply]
[d/l]
[select]