in reply to DBD::ODBC not support

You didn't properly turn the contents of $loader_start_time into a literal. The easiest solution is to use placeholders: 
my $sth = $dbh->prepare('
    UPDATE kcrei_batch_job_history
       SET rows_added      = ?,
           status          = 'C',
           stop_datetime   = getdate(),
           stop_tran_seq_nbr = ?,
           elapsed_time_hh = datediff(hh, convert(datetime,?), getdate
+()),
           elapsed_time_mm = datediff(mi, convert(datetime,?), getdate
+()),
           elapsed_time_ss = datediff(ss, convert(datetime,?), getdate
+())
     WHERE master_job_no     = ?
       AND master_job_sub_no = ?
');

$sth->execute(
    $_[1],
    $hdr_tran_seq_nbr,
    $loader_start_time,
    $loader_start_time,
    $loader_start_time,
    $master_job_no
    $_[0],
);

[download]

Replies are listed 'Best First'.
Re^2: DBD::ODBC not support
by Bheema_Tyco (Novice) on May 12, 2010 at 07:13 UTC

    Hi iskegami i had made the changes wat you have told

    but its giving error like this

    Revision : 1 Wed May 12 12:32:13 2010 DBD::ODBC::st execute failed: MicrosoftODBC SQL Server DriverInvalid character value for cast specification (SQL-22018) at D:\Bheema\kc_tlog_loaderSQL.pl line 997.

    Thanks

[reply]
      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]
[reply]
[d/l]

        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]

      Can we see the SQL you are running now, the way you've bound the parameters and the values of the parameters?

[reply]

In Section Seekers of Perl Wisdom