Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: Use of double quotes in a dynamic sql statement


This question is not answered. Helpful answers available: 2. Correct answers available: 1.


Permlink Replies: 3 - Last Post: Jul 20, 2014 10:42 PM Last Post By: Dmitry Arefiev
Paulo Mariano

Posts: 29
Registered: 11/25/12
Use of double quotes in a dynamic sql statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 18, 2014 1:56 PM
Hello.

An application is being converted from Delphi XE5 to XE6.
In XE5, the following queries are successfully executed:

eQuery.Connection := FDManager.Connections[0];
eQuery.SQL.Text := 'select admin_name from table1';
eQuery.Open;
s1 := eQuery.Fields[0].AsString;
if s1 = '' then
begin
  eQuery.SQL.Text := Format('select decrypt_char(admin_acct, "%s") from table2', [s1]);
  eQuery.Open;
end;


In XE6, the following exception is raised when the second query is executed:
Project app.exe raised exception class EInfxNativeException with message '[FireDAC][Phys][ODBC][Informix][Informix ODBC Driver][Informix]Column (Column1) not found in any table in the query (or SLV is undefined).'.

When each double-quote character is replaced with two single-quote characters, the problem is solved.
Would it be possible to solve the problem without replacing the double-quote characters? The application has several dynamic statements built this way.

Thank you in advance.
quinn wildman

Posts: 856
Registered: 12/2/99
Re: Use of double quotes in a dynamic sql statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 18, 2014 2:40 PM   in response to: Paulo Mariano in response to: Paulo Mariano
While I am not certain it applies here, the SQL standard generally
states that in a SQL statement if you put double quotes around something
it means you are using delimited identifiers and are specifying an
object. Typically delimited identifiers also means case sensitivity is
required. So, I would suggest the problem is you have specified the
wrong case for the column being used. Most likely, when using delimited
identifiers the column name needs to be upper case.

Paulo Mariano wrote:
Hello.

An application is being converted from Delphi XE5 to XE6.
In XE5, the following queries are successfully executed:

eQuery.Connection := FDManager.Connections[0];
eQuery.SQL.Text := 'select admin_name from table1';
eQuery.Open;
s1 := eQuery.Fields[0].AsString;
if s1 = '' then
begin
   eQuery.SQL.Text := Format('select decrypt_char(admin_acct, "%s") from table2', [s1]);
   eQuery.Open;
end;


In XE6, the following exception is raised when the second query is executed:
Project app.exe raised exception class EInfxNativeException with message '[FireDAC][Phys][ODBC][Informix][Informix ODBC Driver][Informix]Column (Column1) not found in any table in the query (or SLV is undefined).'.

When each double-quote character is replaced with two single-quote characters, the problem is solved.
Would it be possible to solve the problem without replacing the double-quote characters? The application has several dynamic statements built this way.

Thank you in advance.
Linden ROTH

Posts: 467
Registered: 11/3/11
Re: Use of double quotes in a dynamic sql statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 18, 2014 4:11 PM   in response to: quinn wildman in response to: quinn wildman
Paulo Mariano wrote:
Hello.

An application is being converted from Delphi XE5 to XE6.
In XE5, the following queries are successfully executed:

if s1 = '' then
begin
   eQuery.SQL.Text := Format('select decrypt_char(admin_acct, "%s") from table2', [s1]);


I'm guessing its just a typo but the test and use of s1 seems a bit silly in this example (can't really be uppercase '' )

--
Linden
"Mango" was Cool but "Wasabi" was Hotter but remember it's all in the "source"
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: Use of double quotes in a dynamic sql statement  
Click to report abuse...   Click to reply to this thread Reply
  Posted: Jul 20, 2014 10:42 PM   in response to: Paulo Mariano in response to: Paulo Mariano
Would it be possible to solve the problem without replacing the double-quote characters? The application has several dynamic statements built this way.

Try to specify in your Infx connection parameters:
ODBCAdvanced=CURB=1;DLMT=n


--
With best regards,
Dmitry Arefiev / FireDAC Architect
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02