Friends,

I wrote a set of SPL functions for a client for IDS v11.50UC1 on Linux
2.6.18 using dynamic SQL - a very cool feature even with the one bug I've
already reported but I'll post about that separately once I have a bug # for
you all. Here's my problem:

There is one function that builds SQL from user parameters and stores it in
a table and another that fetches the SQL string from the table, executes it,
and returns the data. When I run the generated SQL string manually in
dbaccess it works. When I call the execute function from dbaccess either as
myself or as the client's web user it works, however, when the client's
application connects remotely from a PC via .NET and executes the function
the query fails. Turning trace on in the execute function I see that for
the .NET connection there was a -217 error (unknown column or local
variable) from the cursor OPEN. Looking at the OPEN I notice that in this
user's trace file all of the literal strings in filters have had their
quotes stripped from them, though they were there in the original SQL string
and are shown in the version of the statement shown for the PREPARE
statement. So things like '...AND table.column = "astring" ...' become
'...AND table.column = astring ...' and the engine is complaining that
column 'astring' does not exist.

Anyone seen this before? Could it be a locale problem? I'm trying to
change the build function to generate the SQL with single quotes instead of
double quotes, but the client has gone home and won't test that workaround
attempt tonight. I'd like an answer when they get back in the AM instead
of waiting for them to try it and hope it doesn't fail.

Can anyone help an old furry faced coder?

Art S. Kagel
Oninit

On May 15, 6:20 pm, "Art Kagel" <art.ka...@gmail.com> wrote:
> Friends,
>
> I wrote a set of SPL functions for a client for IDS v11.50UC1 on Linux
> 2.6.18 using dynamic SQL - a very cool feature even with the one bug I've
> already reported but I'll post about that separately once I have a bug # for
> you all. Here's my problem:
>
> There is one function that builds SQL from user parameters and stores it in
> a table and another that fetches the SQL string from the table, executes it,
> and returns the data. When I run the generated SQL string manually in
> dbaccess it works. When I call the execute function from dbaccess either as
> myself or as the client's web user it works, however, when the client's
> application connects remotely from a PC via .NET and executes the function
> the query fails. Turning trace on in the execute function I see that for
> the .NET connection there was a -217 error (unknown column or local
> variable) from the cursor OPEN. Looking at the OPEN I notice that in this
> user's trace file all of the literal strings in filters have had their
> quotes stripped from them, though they were there in the original SQL string
> and are shown in the version of the statement shown for the PREPARE
> statement. So things like '...AND table.column = "astring" ...' become
> '...AND table.column = astring ...' and the engine is complaining that
> column 'astring' does not exist.
>
> Anyone seen this before? Could it be a locale problem? I'm trying to
> change the build function to generate the SQL with single quotes instead of
> double quotes, but the client has gone home and won't test that workaround
> attempt tonight. I'd like an answer when they get back in the AM instead
> of waiting for them to try it and hope it doesn't fail.
>
> Can anyone help an old furry faced coder?
>
> Art S. Kagel
> Oninit

Is this a feature of .NET to strip the quotes?
Try putting the quotes as """ or something similar.