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.
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?
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)
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:
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.I don't want the coma between year and month to be interpreted.
Now, however , you have a different SqlPLSQLCommand(). And GCIO object/class does not like that comma -- that's how I interpret this.
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
create or replace procedure proc_test(p_text in varchar2)
insert into test (text) values (p_text);
SQL> execute proc_test('my test,text');
PL/SQL procedure successfully completed.
SQL> select * from test;
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.
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.have you try to reproduce my sample ?
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.
Who is online
Users browsing this forum: [Ccbot] and 0 guests