Your prepare cannot be a multi-statement operation... And should be like
so..
if (!($sth1 = $dbh->prepare('exec spsomeproc_or_sqlstatement ?,?,?')))
{
print "Failed preparing sql call\n".$dbh->errstr."\n";
return 0;
}
Or in your example...
$MinsUsedInRangeQuery1
= $DBH->prepare('SELECT SUM(DATEDIFF(mi,StartTime,EndTime))'
. ' AS MinsUsed FROM Sessions'
. ' WHERE AuthSource = ? AND UserID = ?')
|| die 'Unable to prepare MinsUsedInRange query 1';
Now of course, that obviously cuts out your start and stop times..
But, it's the root cause of your problem. You'd need to pass start and
stop times to each prepare.. While also cutting out the SQL variable
declarations in the process. I'd suggest also procedurizing this SQL
call.
Steven
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of
Lyle Kopnicky
Sent: Wednesday, November 23, 2005 11:41 AM
To: perl-win32-users
Subject: SQL problem
Hi folks,
Perhaps I should send this question to a SQL Server forum, but someone
here might be able to spot the problem.
I am accessing SQL Server through Perl and DBI. I have written a number
of queries. I prepare all the queries once at the start of the program,
and then execute them as needed.
I do not get an error when preparing any of the queries. However, when
I attempt to execute this query, called $MinsUsedInRangeQuery1, I get
the error
DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver][SQL
Server]Line 1: Incorrect syntax near ','. (SQL-42000) [Microsoft][ODBC
SQL Server Driver][SQL Server]Statement(s) could not be prepared.
(SQL-42000)(DBD: st_execute/SQLExecute err=-1) at
server/V-ASC-UsageDB.pl line 333.
How strange: It said the statement could not be prepared. But
supposedly it was already prepared! As for the syntax error, does
anybody have an idea what is wrong in the SQL syntax? Here's the code:
### when program starts
$MinsUsedInRangeQuery1
= $DBH->prepare('DECLARE @StartRange datetime, @EndRange datetime'
. ' SET @StartRange = ?, @EndRange = ?'
. ' SELECT SUM(DATEDIFF(mi,StartTime,EndTime))'
. ' AS MinsUsed FROM Sessions'
. ' WHERE AuthSource = ? AND UserID = ?'
. ' AND StartTime BETWEEN @StartRange AND @EndRange'
. ' AND EndTime BETWEEN @StartRange AND @EndRange')
|| die 'Unable to prepare MinsUsedInRange query 1';
### later on...
LogText(1, "Executing MinsUsedInRange1 query with parameters "
. "($StartRange, $EndRange, patron, $UserID)"); if
(!$MinsUsedInRangeQuery1->execute($StartRange, $EndRange,
'patron', $UserID)) {
$MinsUsedInRangeQuery1->finish;
die("Unable to execute MinsUsedInRange query 1:"
. " $DBI::errstr");
}
$Row = $MinsUsedInRangeQuery1->fetchrow_hashref;
if (!$Row) {
$MinsUsedInRangeQuery1->finish;
die "No row returned from MinsUsedInRange query 1"; }
$MinsUsedInRange += $Row->{'MinsUsed'}; $MinsUsedInRangeQuery1->finish;
### end code segment
If you can suggest another forum I should use, please do. Thanks!
--
Lyle Kopnicky
Software Project Engineer
Veicon Technology, Inc.
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
_______________________________________________
Perl-Win32-Users mailing list
Perl-Win32-Users@listserv.ActiveState.com
To unsubscribe: http://listserv.ActiveState.com/mailman/mysubs
