My effort is to create a script in which I can vary my Selection Queries.  I find that there are some cases in which queries which run successfully elsewhere fail when the string is introduced to the script via an input parameter.  The error found is with quoted numbers, a "700" error.  In this example, quoted numbers such as the ''00'' near the end of the statement is specified as the cause of the error.

I have shown a script which illustrates this issue.  The script is set up so that the query can be run either as an internal variable (passes) or as a parameter (fails).  The internal variable will be used if the input parameter is empty.

What suggestions do you have?

SCRIPT (
in inScript   varchar
)
BEGIN
declare aCursor CURSOR with return for aStmt;
declare testScript  varchar;
declare aScript     varchar;

-- if the contents of this string are entered as a parameter, the Prepare statement fails.
-- if the same statement is used internally, then there is no failure.
set testScript = 'select zone from districts group by zone order by right(''00''+zone, 3)';

if length(inScript) = 0 then
set aScript = testScript;
else
set aScript = inScript;
end if;

prepare aStmt from aScript;  
open aCursor;

END

David


I hesitate to suggest this one - is someone entering the parameter using double quotes or with two single quotes? If so and without testing an entered string of

select zone from districts group by zone order by right(''00''+zone, 3)

is wrong and it should be

select zone from districts group by zone order by right('00'+zone, 3)

I think. If I'm wrong (very probable) I'll try and create a script here.

Roy Lambert

Roy:

The selection script (SS) was entered with two single quotes.

Note that the same SS was provided two ways, once as an internal string (works) and once as an input parameter (fails).  What I did to generate the input parameter was to copy and paste the contents of the internal string into the slot provided for inputting parameters, so I know that they are identical up until then.

I've done a few experiments since I created my post.  The lengths of the two strings, internal source and parameter source are different (69 vs 71 respectively).  On investigation as to where the extra two "chars" or bytes are added, it seems to be in the area of the ''00''.  Could it be that the ''00'' was changed to a different string format by the input parameter process?

David

Luckily I found the solution.

During the process of inputting the parameter, an invisible char seems to be included immediately before the double single quotes.  If I replace the double single quotes with a '#' in the query script and then do a search and replace of the "#" with '''' (four single quotes) in the managing script, the query script works.  On inspection of the resulting query script, I only see the "00" encased in single quotes.

The line for the search and replace in my managing script is where aScript is the input parameter script.
set aScript = replace('#' with '''' in aScript);

Original query script
select zone from districts group by zone order by right(''00''+zone, 3)

Modified input query script
select zone from districts group by zone order by right(#00#+zone, 3)

Version of the query script used in the Prepare statement.
select zone from districts group by zone order by right('00'+zone, 3)

Hopefully this is clear.  Thanks for your help.