Hello!

I've got a problem with passing parameters into subquery. Example code:

lQuery.SQL.Text := 'SELECT * FROM ( SELECT * FROM :TableNameParam ) tempTable WHERE (ID > 180) AND (ID < 220)';  // This query is just
example to show that I can't pass parameters to subquery
//    lQuery. ParamCheck := True; // ParamCheck is true when I check while debugging
lQuery.ParamByName('TableNameParam').AsString := 'TableName';
lQuery.Prepare;
lQuery.ExecSQL;

The exception raised in Borland Studio is:
Project * raised exception class EDatabaseError with message 'Parameter 'TableNameParam' not found'.

My question is, if is it possible to pass parameters into subquery? If so, how? My elevateDB version is 2.03 b6

Best regards
--
Tymoteusz Paszun
Axence Software Inc.

Tymoteusz


>lQuery.SQL.Text := 'SELECT * FROM ( SELECT * FROM :TableNameParam ) tempTable WHERE (ID > 180) AND (ID < 220)';

>My question is, if is it possible to pass parameters into subquery? If so, how? My elevateDB version is 2.03 b6

The answer is not yet even if it wasn't a subquery. You can't use parameters to specify tables or fieldnames. Try in EDBManager and you'll get an error like

ElevateDB Error #700 An error was found in the statement at line 1 and column 13 (Expected FROM but instead found ?)

when you try and prepare the query

Roy Lambert [Team Elevate]

OK, now I understand, that I can't pass parameters as an identifier, but still I'm getting errors when trying to pass parameters as values. Example:

with FQuery do
try
  SQL.Clear;
  SQL.Add('SELECT * FROM ( SELECT * FROM MyTableName WHERE ID > :SubQueryMoreThan ) tempTable WHERE tempTable.ID <
:QueryLessThan');
  // Prepare; // If I use Prepare; exception is raised here, If I don't use it exception is raised on Open;
  ParamByName('SubQueryMoreThan').AsInteger := 180;
  ParamByName('QueryLessThan').AsInteger := 220;
  Open;

Exception:
Project * raised exception class EEDBException with message 'ElevateDB Error #700 An error was found in the statement at line 1 and column 48
(Invalid expression ? found, dynamic parameter references not allowed)'.

Best Regards
--
Tymoteusz Paszun
  

Tymoteusz,

<< OK, now I understand, that I can't pass parameters as an identifier, but
still I'm getting errors when trying to pass parameters as values. >>

2.03 Build 7 was just released, and one of the sneak features in it
(reserved for inclusion in the 2.04 feature set) is the ability to use
parameters with derived tables.  So, if you download and install 2.03 B7,
you'll be all set.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tymoteusz


>Exception:
>Project * raised exception class EEDBException with message 'ElevateDB Error #700 An error was found in the statement at line 1 and column 48
>(Invalid expression ? found, dynamic parameter references not allowed)'.

As you've found that isn't implemented yet. Tim has it on his todo list but for now you'll have to build the query fully before trying to run it.

Roy Lambert [Team Elevate]

Tim

>2.03 Build 7 was just released, and one of the sneak features in it
>(reserved for inclusion in the 2.04 feature set) is the ability to use
>parameters with derived tables. So, if you download and install 2.03 B7,
>you'll be all set.

Shouldn't even sneak features be mentioned in the release notes, maybe with a suitable warning?

Roy Lambert

Roy,

<< Shouldn't even sneak features be mentioned in the release notes, maybe
with a suitable warning? >>

Certain features I don't want anyone to know about until the minor version.
So, in general, I don't mention anything that has been snuck in unless
someone absolutely needs it.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>Certain features I don't want anyone to know about until the minor version.

I guessed that, and I think you're being mean and nasty

Roy Lambert

Great thanks for the answers!

Best regards
--
Tymoteusz Paszun