>>Hi everybody,
>>
>>We have queries like this:
>>
>>
>> text to lcSql textmerge noshow pretext 3
>> select ri.*, rt.Descrip AS type_descrip, rt.maxdays, rt.maxrents, rt.shortdesc,
>> rt.showinfo, rt.SigRequire
>> from r_invent ri inner join r_types rt on ri.rentaltype = rt.typeid
>> where ri.equip_tag = ?pnEquiptag
>> ENDTEXT
>>
>>The equip_tag column is defined as decimal(17,0) in SQL Server. In SQL Trace I see the following query being executed:
>>
>>
>>exec sp_executesql N'select form_no from r_header where rental_no = @P1 ',N'@P1 float',719646112000
(for a similar query).
>>
>>Note, that the parameter is passed as float. The question is - how to pass this parameter as decimal(17,0) instead? Is there any way without changing the current code?
>>
>>May be I should put CAST on the right side, e.g.
>>
>> where ri.equip_tag = CAST(?pnEquiptag as decimal(17,0))
>>
>
>Yup, that would be the solution. Because there's nothing much else you can do fox-side. Fox parses the sql statement and if it encounters a question mark, it will repackage it as "exec sp_executesql" with parameters. Those parameters are fox variables, and fox really doesn't have any idea what they should be sql-side. The parser isn't too smart, which you'll discover as soon as you insert a comment containing a question mark in the sql statement - you get a bug which you can't really explain...
Thanks, Dragan. I'll plan to implement that solution then as I'm going to work on changes to this app soon.
If it's not broken, fix it until it is.
My Blog