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 [mailto:bmccain@;pagemasters.com]
Sent: Thursday, October 31, 2002 3:30 PM
To: Adam Peterson; [EMAIL PROTECTED]
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" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
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/
|
