How to avoid ODBC parameterization for the question mark character ? within literals and comments?

Question

I'm running into an issue where I have question mark characters '?' within my SQL scripts inside comments is causing problems. When I run these statements through an ODBC connection using the Visual FoxPro SQLEXEC function these characters are being seen as parameters and VFP prompts for values.

What are my options for dealing with question mark characters within comments when using SQLEXEC? I'm hoping that I may be either to some how turn off parameterization or alternatively to some how escape these characters before passing the strings as commands. So far I haven't found any evidence of such a solution so it looks like I may have to strip out all comments which is undesirable because they are generally create statements for stored procedures and we would like to preserve these comments. I'm hoping somebody might have a more pleasing solution than stripping out the comments completely.

Answer 1

It appears that it may be a known issue that SQLEXEC has no regard for comments in the SQL command when encountering question mark characters ? which normally represent parameters in ODBC and likewise in VFP.

Just a warning - VFP's parser will catch any question mark in the SQL string, even if it's within a SQL comment (i.e. on a line beginning with -- or bracketed between /* */) and will error out if there's nothing there that can be a variable. If it can be a variable but you don't have it defined at runtime, you get an ugly inputbox to enter it. Worse, your user gets it.

So watch out. -- Dragan Nedeljkovich

Answer 2

have you tried replacing it with the ascii code? Something like: '+chr(63)+'