Error in Prepare SQL query with params in UDF function call

Elephant · Post by **Elephant** » Sat 06 May 2006 11:06

I have a Delphi project. To connect to MSSQL I use ADO. I decided to see, what kind of alternative variants were and I looked at SDAC. Unfortunately, I find the problem which I don't know how to manage.

I have a Query: TMSQuery. There is a query inside:
select Val1, Val2 from dbo.MyFunction(:Param1, :Param2, :Param3) order by Val1
When I try to call Query.Prepare I get Exception with error message about syntax error or illegal cross of access right. Everything works in ADO correctly. Couldn't I hear comments
of specialist about my problem?

jfpicard · Post by **jfpicard** » Mon 08 May 2006 12:59

When i've done my conversion from BDE to SDAC, all the Query.Prepare method was troublesome. I've taken the lasiest way to solve that problem; I've removed all the Query.Prepare. The project worked right, but I don't know if we lose perfomance by doing it.

JFPicard.

Jackson · Post by **Jackson** » Wed 10 May 2006 12:01

This is OLE DB problem.
To workaround this problem set parameters information before calling Prepare method.
For example:
MSQuery1.SQL.Text := 'select Val1, Val2 from dbo.MyFunction(:Param1,:Param2, :Param3) order by Val1';
MSQuery1.ParamByName('Param1').AsInteger := 10;
MSQuery1.ParamByName('Param2').AsInteger := 20;
MSQuery1.ParamByName('Param3').AsInteger := 30;
MSQuery1.Prepare;
Please download the latest SDAC version to use this workaround.