On 02-Feb-2004 Tim Howell wrote:
> I've got a script that runs a query that uses a user defined function. The
> query looks something like 'SELECT * FROM dbo.f_myfunction(?, ?, ?);'
>
> My problem is that prepare fails with a syntax error indicating that there is
> a problem near @P1. I'm using DBD::ODBC to connect to MS SQL Server 2000
> through DBD::Proxy, but I've also tried it directly from DBD::ODBC with the
> same results. Any ideas?
>
> Thanks! =)
>
> --TWH
>
> DBD::Proxy::st execute failed: Server returned error: Failed to execute
> method CallMethod: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver][SQL Server]Line 2: Incorrect syntax near '@P1'. (SQL-42000)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
> prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at
> C:/Perl/site/lib/DBI/ProxyServer.pm line 344.
> DBD::Proxy::st execute failed: Server returned error: Failed to execute
> method CallMethod: DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server
> Driver][SQL Server]Line 2: Incorrect syntax near '@P1'. (SQL-42000)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Statement(s) could not be
> prepared. (SQL-42000)(DBD: st_execute/SQLExecute err=-1) at
> C:/Perl/site/lib/DBI/ProxyServer.pm line 344.
A simple case seems to work for me:
create function mjef( @p1 integer) returns table as return (select * from
bench_char where f1 = @p1)
#!/usr/bin/perl -w
use DBI;
my $dbh = DBI->connect('dbi:ODBC:test', "Martin_Evans", "easysoft");
my $sth = $dbh->prepare("select * from mjef(?)");
$sth->execute("1");
while(@row = $sth->fetchrow_array) {
print join(",", @row);
}
$dbh->disconnect;
$ ./x.pl mHowell->
1,The quick brown fox jumps over the lazy dog
Have you tried doing this from odbctest or the query analyser?
Martin
--
Martin J. Evans
Easysoft Ltd, UK
Development
