[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post found bugs and possible workarounds.
madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 10 Oct 2012, 17:25

hi,

I'm searching the character who block the interpretation by TD.

Example : i call a pl/sql procedure but one of my param is a query.
call SqlPLSQLCommand (hSql, 'package.procedure(param1, param2, 'select year, month from my_table', param4)')

TD says "String is not terminated: select year..."
I don't want the coma between year and month to be interpreted.

Thanks

Jeff Luther

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by Jeff Luther » 10 Oct 2012, 22:35

TD supports single- and double-quotes, as long as they are paired.

So, 2 ways to do what you want -- assuming I understand the problem. This is a TD compile error, right?
1 - outside has double quotes: call SqlPLSQLCommand (hSql, "package.procedure(param1, param2, 'select year, month from my_table', param4)" )

2 -- 'escape' inner quotes with backslashes: call SqlPLSQLCommand (hSql, 'package.procedure(param1, param2, \'select year, month from my_table\', param4)')

Hmmm, try 1 first. Maybe that's what you want?

madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 11 Oct 2012, 09:58

It's not a compile error.
I have an EXE with this command :
Return SqlPLSQLCommand (hSqliUsed, spRequete)

spRequete is read into a database in a parameter table. In this table i have the column with the value : GCI0.GCI0_Export_SQL('020','SELECT DISTINCT YEAR , MONTH FROM My_table',99)

020 is my first parameter,
SELECT DISTINCT YEAR , MONTH FROM My_table is the second
and 99 is the third.

If i remove ", MONTH" it works (SELECT DISTINCT YEAR FROM My_table)

So i can't do the first solution
I tried the second like this GCI0.GCI0_Export_SQL('020',\'SELECT DISTINCT YEAR , MONTH FROM My_table\',99)
and i also tried GCI0.GCI0_Export_SQL('020','SELECT DISTINCT YEAR \, MONTH FROM My_table',99)

nothing works

Thx

Jeff Luther

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by Jeff Luther » 12 Oct 2012, 00:07

Not sure what to suggest.

Sounds like whatever GCI0.GCI0_Export_SQL() is, it doesn't support > 1 column name in the select column list. What I see below are pieces of this and that, but no TD context. I was assuming that a TD string passing itself in a literal was the issue, which is why I suggested double quotes on the outside of a string literal you wanted to pass as is.

It is this original call you had in your first msg. that I was replying to:
SqlPLSQLCommand (hSql, 'package.procedure(param1, param2, 'select year, month from my_table', param4)')
and to pass the 'select...' as a string correctly in TD you would arrange like this to surround entire string in double quotes so inner 'select...' string could be passed with single quotes (as I wrote earlier)
SqlPLSQLCommand (hSql, "package.procedure(param1, param2, 'select year, month from my_table', param4)" )

And you had originally written:
I don't want the coma between year and month to be interpreted.
and it won't be by TD at all if outer quotes are double. TD will pass that entire string, AS IS, to the DB. TD doesn't parse out a string literal. My guess is that <something> outside TD -- router and/or DB server -- is parsing out that string and does not like there being 2 parameters in the SELECT string.


Now, however , you have a different SqlPLSQLCommand(). And GCIO object/class does not like that comma -- that's how I interpret this.

madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 12 Oct 2012, 13:56

Hi,
Sorry if I wasn't clear on my explications at the first time.

I tried a simple case, in Oracle SQL*PLUS :

create table test
(text varchar2(50));

create or replace procedure proc_test(p_text in varchar2)
is
begin
insert into test (text) values (p_text);
end;

SQL> execute proc_test('my test,text');
PL/SQL procedure successfully completed.
SQL> select * from test;

TEXT
------------
my test,text

THEN TRY IT WITH TD6 :

Call SqlPLSQLCommand (hSqliUsed, "proc_test('my TD test,text')")

SQL WINDOWS ERROR...
String is not terminated: my TD test...


Can you reproduce the same ?

I think this is a problem with SqlPLSQLCommand.

madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 17 Oct 2012, 10:15

Jeff, have you try to reproduce my sample ?

Jeff Luther

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by Jeff Luther » 17 Oct 2012, 16:23

have you try to reproduce my sample ?
No, what I would need is a complete, running small TD test case showing how you have coded this. That way I can try it myself.

madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 07 Nov 2012, 17:25

Hi, I wrote a simple test case so you can try it.
Thanks.
You do not have the required permissions to view the files attached to this post.

Jeff Luther

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by Jeff Luther » 07 Nov 2012, 20:19

Odd. According to online help: http://support.guptatechnologies.com/Do ... racle.html
SqlPLSQLCommand does support > 1 parameter, separated by the ',' char, OK. Code sample is in the page section titled "Executing the sample stored procedure".

I can reproduce your issue with a comma embedded in a string and see from the TD error msg. what looks to be the problem: "String is not terminated..."

Thus, looks like TD is parsing that string, ignoring that it is inside a string, and is finding the ',' char. and generating the error because it sees as what it thinks is the end of the parameter and expects to have seen the string terminating char. Like you with '\\' I tried a couple ways to fool TD, but no luck. dash, period, semi-colon work OK as you would expect but not that ',' char.

I even tried passing this string: "proc_test('Hello' || chr(44) || ' My Name Is Bob')"
to see if I could fool TD but no luck.

I will open a TD/router defect for this. Added TD-18632

P.S. Note that I changed this topic title to better describe what the thread is reporting.

madmax92

[DEFECT ADDED] SqlPLSQLCommand parses comma in string wrong

Post by madmax92 » 08 Nov 2012, 09:42

ok thanks Jeff

Return to “Bug Reports”

Who is online

Users browsing this forum: [Ccbot] and 0 guests