Re: 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]

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

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: Microsoft ODBC SQL Server DriverInvalid character value for cast specification (SQL-22018) at D:\Bheema\kc_tlog_loaderSQL.pl line 997. Thanks	[reply]
Re^3: DBD::ODBC not support by ikegami (Patriarch) on May 12, 2010 at 13:53 UTC
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]
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]
Re^3: DBD::ODBC not support by mje (Curate) on May 12, 2010 at 10:09 UTC
Can we see the SQL you are running now, the way you've bound the parameters and the values of the parameters?	[reply]


Just another Perl shrine
	PerlMonks