FAQ
hi,

i can execute my query in MS sql query analyzer fine. when i run it in my
script, i get errors.

script:
# connect to database
$dbh = DBI->connect("dbi:ODBC:$dbname", $user, $passwd) or die
$dbh->errstr;

# set data size and ok to truncate
$dbh->{LongReadLen} = 10000;
$dbh->{LongTruncOk} = 1;

$sth = $dbh->prepare("
use ecmaster
declare @type varchar(50)
declare @type1 varchar(50)
declare @from datetime
declare @to datetime

set @type1 = 'rpt'
set @type = 'c0.asc'
SET @from = getdate()-1
SET @to = getdate()+1

SELECT distinct filename, status
FROM MASTERFILE MF JOIN MASTERFILEHISTORY MFH ON MF.masterfileid
=mfh.masterfileid
WHERE
( message like '%Error running TranslationScript%' or message like
'%Error running InitialRoutingScript:%' or message like 'Error importing
report data%')
and FILENAME NOT LIKE '%TST-%' and createdtime >= @from and createdtime
<= @to
and (filename like '%' + @type + '' or filename like '%' + @type1 + '')
and ascii(substring(filename,Len(filename),1)) = 116
");

$sth->execute;

while(@now = $sth->fetchrow_array)
{
$file_name = $now[0];
$status = $now[1];
print "$file_name\t$status\n";
}


######### disconnect

$sth->finish;
$dbh->disconnect;


error:
C:\>perl\scripts\collab_crash.pl
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Lin
e 3: Incorrect syntax near '('. (SQL-37000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
keyword
'and'. (SQL-37000)
[Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
prepare
d. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at
C:\perl\scripts\collab_cras
h.pl line 39.
DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
executi
ng err=-1) at C:\perl\scripts\collab_crash.pl line 41.


can anyone tell me why this is happening? and how it can be fixed?

thanks!
-adam


__________________________________________________
Do you Yahoo!?
HotJobs - Search new jobs daily now
http://hotjobs.yahoo.com/

Search Discussions

  • Brian McCain at Oct 31, 2002 at 8:30 pm
    What exactly are the errors you get?

    I don't do a whole lot with MS SQL (or DBI:ODBC for that matter), as my work
    with DBI is confined mostly to MySQL, so I could be way off on this, but you
    may find that you have better luck if you break up that statement you're
    preparing into multiple queries. In any case (and this is just a general
    statement for anyone getting errors), post up those errors, it'll help us to
    more easily give you accurate debugging information.

    Brian McCain
    PageMasters Internet Group

    ----- Original Message -----
    From: "Adam Peterson" <laca_adam@yahoo.com>
    To: <dbi-users@perl.org>
    Sent: Thursday, October 31, 2002 12:18 PM
    Subject: DBI MS SQL Server Question

    hi,

    i can execute my query in MS sql query analyzer fine. when i run it in my
    script, i get errors.

    script:
    # connect to database
    $dbh = DBI->connect("dbi:ODBC:$dbname", $user, $passwd) or die
    $dbh->errstr;

    # set data size and ok to truncate
    $dbh->{LongReadLen} = 10000;
    $dbh->{LongTruncOk} = 1;

    $sth = $dbh->prepare("
    use ecmaster
    declare @type varchar(50)
    declare @type1 varchar(50)
    declare @from datetime
    declare @to datetime

    set @type1 = 'rpt'
    set @type = 'c0.asc'
    SET @from = getdate()-1
    SET @to = getdate()+1

    SELECT distinct filename, status
    FROM MASTERFILE MF JOIN MASTERFILEHISTORY MFH ON MF.masterfileid
    =mfh.masterfileid
    WHERE
    ( message like '%Error running TranslationScript%' or message like
    '%Error running InitialRoutingScript:%' or message like 'Error importing
    report data%')
    and FILENAME NOT LIKE '%TST-%' and createdtime >= @from and createdtime
    <= @to
    and (filename like '%' + @type + '' or filename like '%' + @type1 + '')
    and ascii(substring(filename,Len(filename),1)) = 116
    ");

    $sth->execute;

    while(@now = $sth->fetchrow_array)
    {
    $file_name = $now[0];
    $status = $now[1];
    print "$file_name\t$status\n";
    }


    ######### disconnect

    $sth->finish;
    $dbh->disconnect;


    error:
    C:\>perl\scripts\collab_crash.pl
    DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
    Server]Lin
    e 3: Incorrect syntax near '('. (SQL-37000)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
    keyword
    'and'. (SQL-37000)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
    prepare
    d. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at
    C:\perl\scripts\collab_cras
    h.pl line 39.
    DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
    executi
    ng err=-1) at C:\perl\scripts\collab_crash.pl line 41.


    can anyone tell me why this is happening? and how it can be fixed?

    thanks!
    -adam


    __________________________________________________
    Do you Yahoo!?
    HotJobs - Search new jobs daily now
    http://hotjobs.yahoo.com/
  • Joshua Caesar at Oct 31, 2002 at 9:03 pm
    Adam,

    I think you are having a problem where perl is trying to interpolate
    your string. You are using sql variables ( which use @ ) , and perl
    thinks they are variable markers for arrays.

    Try the code I am pasting below.
    (hopefully I am not too far off base on what your problem is :)

    -Josh

    ---------------------
    # connect to database
    $dbh = DBI->connect("dbi:ODBC:$dbname", $user, $passwd) or die
    $dbh->errstr;

    # set data size and ok to truncate
    $dbh->{LongReadLen} = 10000;
    $dbh->{LongTruncOk} = 1;

    my $type1 = 'rpt';
    my $type = 'c0.asc';
    my $from = getdate()-1;
    my $to = getdate()+1;

    my $sql;
    $sql = "SELECT distinct filename, status ";
    $sql .= "FROM MASTERFILE MF JOIN MASTERFILEHISTORY MFH ON
    MF.masterfileid ";
    $sql .= "=mfh.masterfileid WHERE ";
    $sql .= "( message like '%Error running TranslationScript%' or message
    like ";
    $sql .= "'%Error running InitialRoutingScript:%' or message like 'Error
    importing ";
    $sql .= "report data%') ";
    $sql .= "and FILENAME NOT LIKE '%TST-%' and createdtime >= $from and
    createdtime ";
    $sql .= "<= $to ";
    $sql .= "and (filename like '%' + $type + '' or filename like '%' +
    $type1 + '') ";
    $sql .= "and ascii(substring(filename,Len(filename),1)) = 116";

    $sth = $dbh->prepare($sql);
    $sth->execute;

    while(@now = $sth->fetchrow_array)
    {
    $file_name = $now[0];
    $status = $now[1];
    print "$file_name\t$status\n";
    }

    ######### disconnect

    $sth->finish;
    $dbh->disconnect;

    -----Original Message-----
    From: Brian McCain
    Sent: Thursday, October 31, 2002 3:30 PM
    To: Adam Peterson; dbi-users@perl.org
    Subject: Re: DBI MS SQL Server Question


    What exactly are the errors you get?

    I don't do a whole lot with MS SQL (or DBI:ODBC for that matter), as my
    work with DBI is confined mostly to MySQL, so I could be way off on
    this, but you may find that you have better luck if you break up that
    statement you're preparing into multiple queries. In any case (and this
    is just a general statement for anyone getting errors), post up those
    errors, it'll help us to more easily give you accurate debugging
    information.

    Brian McCain
    PageMasters Internet Group

    ----- Original Message -----
    From: "Adam Peterson" <laca_adam@yahoo.com>
    To: <dbi-users@perl.org>
    Sent: Thursday, October 31, 2002 12:18 PM
    Subject: DBI MS SQL Server Question

    hi,

    i can execute my query in MS sql query analyzer fine. when i run it in
    my script, i get errors.

    script:
    # connect to database
    $dbh = DBI->connect("dbi:ODBC:$dbname", $user, $passwd) or die
    $dbh->errstr;

    # set data size and ok to truncate
    $dbh->{LongReadLen} = 10000;
    $dbh->{LongTruncOk} = 1;

    $sth = $dbh->prepare("
    use ecmaster
    declare @type varchar(50)
    declare @type1 varchar(50)
    declare @from datetime
    declare @to datetime

    set @type1 = 'rpt'
    set @type = 'c0.asc'
    SET @from = getdate()-1
    SET @to = getdate()+1

    SELECT distinct filename, status
    FROM MASTERFILE MF JOIN MASTERFILEHISTORY MFH ON MF.masterfileid
    =mfh.masterfileid WHERE
    ( message like '%Error running TranslationScript%' or message like
    '%Error running InitialRoutingScript:%' or message like 'Error importing
    report data%')
    and FILENAME NOT LIKE '%TST-%' and createdtime >= @from and
    createdtime
    <= @to
    and (filename like '%' + @type + '' or filename like '%' + @type1 + '')
    and ascii(substring(filename,Len(filename),1)) = 116
    ");

    $sth->execute;

    while(@now = $sth->fetchrow_array)
    {
    $file_name = $now[0];
    $status = $now[1];
    print "$file_name\t$status\n";
    }


    ######### disconnect

    $sth->finish;
    $dbh->disconnect;


    error:
    C:\>perl\scripts\collab_crash.pl
    DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
    Server]Lin e 3: Incorrect syntax near '('. (SQL-37000)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the
    keyword
    'and'. (SQL-37000)
    [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
    prepare
    d. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) at
    C:\perl\scripts\collab_cras
    h.pl line 39.
    DBD::ODBC::st fetchrow_array failed: (DBD: no select statement currently
    executi
    ng err=-1) at C:\perl\scripts\collab_crash.pl line 41.


    can anyone tell me why this is happening? and how it can be fixed?

    thanks!
    -adam


    __________________________________________________
    Do you Yahoo!?
    HotJobs - Search new jobs daily now
    http://hotjobs.yahoo.com/

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
groupdbi-users @
categoriesperl
postedOct 31, '02 at 8:18p
activeOct 31, '02 at 9:03p
posts3
users3
websitedbi.perl.org

People

Translate

site design / logo © 2022 Grokbase