You are interpolating scalars containing ':' into your SQL and ':' is the introducer for named parameters. As a result DBD::ODBC thinks your SQL contains some named parameters and obviously some of those :xxx strings occur more than once. If you are not using named parameters (which you do not appear to be) and for some strange reason cannot use placeholders for the parameters you need to look at setting odbc_ignore_named_placeholders. However, I would just use bound parameters if I were you - much safer.

i am running query like this

  my $SQL = "UPDATE kcrei_batch_job_history SET
                            rows_added = ?,
                            status = 'C',
                            stop_datetime = getdate(),
                            stop_tran_seq_nbr = ?,
                            elapsed_time_hh = datediff(hh, convert(dat
+etime,?), getdate()),
                            elapsed_time_mm = datediff(mi, convert(dat
+etime,?), getdate()),
                            elapsed_time_ss = datediff(ss, convert(dat
+etime,?), getdate())
                        WHERE
                            master_job_no = ? AND
                            master_job_sub_no = ?";
[download]

my $sth = $dbh->prepare( $SQL )
        or die "Couldn't prepare statement: " . $dbh->errstr;
 
    eval{
        $sth->execute($_[1],
        $hdr_tran_seq_nbr,
        $loader_start_time,
        $loader_start_time,
        $loader_start_time, 
        $master_job_no,
        $_[0]
        );
        $sth->finish();
    };

    if( $@ ) {
     
        outputErrorMessage( "$@" );
        outputErrorMessage( "$sth->{Statement}" );
    }
[download]

hi mje in this way i am running my query

But what are the values of the parameters passed to execute?

I have been trying to understand this for some time now. The ':' in the strings I was using for time parameters was in fact the problem.

my $stime = '2010-01-01 00:00:00'
my $qstring = qq{select * from mytable where start > $stime
return};
[download]

which returned the error "DBD::ODBC does not yet support binding a named parameter more than once". After reading this, I made things work by (abbreviatedj example)...

my $stime = '2010-01-01 00:00:00'
my $qstring = qq{select * from mytable where start > '$stime'
return};
[download]

..which made all work just fine. Apparently those ':' create the problem.

Thanks for pointing that out!

Just goes to show; elephants don't bite, mosquitoes do!

• ...the majority is always wrong, and always the last to know about it...
• The Spice must flow...
• ..by my will, and by will alone.. I set my mind in motion

In DBI, the : introduces a named parameter into the SQL. The convention in the ODBC world is ? introduces a parameter but as DBI dictates :my_param is also valid DBD::ODBC has to parse the SQL to check for named parameters. If you create SQL containing an unquoted : it looks like a parameter. In your first example this is exactly what you did and because it was 00:00:00 it looks like you have 2 named parameters called "00". DBD::ODBC does not support binding the same named parameter more than once (Using the same placeholder more than once) and hence the message you got.

If you absolutely have to include a string in your SQL instead of using a parameter marker (?) and binding the parameter you need to quote it and DBI has a quote method for just that (don't just use single quotes as it can differ between SQL engines).

You can stop DBD::ODBC looking for named parameters by setting odbc_ignore_named_placeholders. In fact, I only wish the default could be this as it wastes time parsing the SQL if you are not using named parameters but DBI says it has to support them.

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]

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

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]

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