HomeActive Query BuilderHow Do I Parse and Then Change the Where Clause of the Statement

How Do I Parse and Then Change the Where Clause of the Statement


How can I parse out just the where clause of a statement and then change the parameters of the where clause?

Example:
Select * from Customers where CustomerId between 'AB001' and 'BR002';

Pull the where clause portion and change the values used.

I use the VCL version.

Thanks.
Bill Brittain
Bill Brittain | email
November 26 2007, 12:56 PM
Dear Bill,

There are two ways to to that: First, you may find the right sub-query and pass through the list of expressions of this query; find and replace criteria for these items. The programming interface to do that is organized exactly as you see the query expressions in the columns pane: list of expressions, each expression may have one or more criteria expressions. A short example is below, to find more detailed example of parsing SQL query, please refer to the Non-visual demo that included in the installation package since version 1.8.

===========
var q: TacSubQuery;
usq: TacUnionSubQuery;
ci: TacSelectItem;

acQueryBuilder1.SQL := 'Select * From EMPLOYEE Where Name = ''Smith'' And Phone = ''555-1234''';

q := acQueryBuilder1.Query;
// q represents the whole query

// if query contains only a single query - no unions, union groups, etc.
if (q.Count = 1) and (q.Items[0] is TacUnionSubQuery)
then begin

usq := TacUnionSubQuery(q.Items[0]);
// usq is the only single query in the given SQL statement

ci := usq.SelectList.Items[0];
// ci represents the first row in the grid

// now the value of ci.Expression is "Name";
// the value of ci.ConditionString[0] is "= 'Smith'"
ShowMessage(ci.ExpressionString + ' ' + ci.ConditionString[0]);

ci := usq.SelectList.Items[1];
// ci represents the second row in the grid

// now the value of ci.Expression is "Phone";
// the value of ci.ConditionString[0] is "= '555-1234'"
ShowMessage(ci.ExpressionString + ' ' + ci.ConditionString[0]);

end;
===========

Another way to do that is to change the WHERE clause completely, but we do not recommend you to follow this way, because it may lead to SQL parsing errors in case of assignment of invalid value.

// where replacement example
// ====================
// qb is the TacQueryBuilder component
var
i:integer;
lCTE,lFromObj:TObjectList;
q:TSQLSelectStatement;
logicalExpression:TSQLExpressionItem;
select:TSQLSubQuerySelectExpression;
newSQL:WideString; // the new WHERE clause string

begin
// make a copy from QueryBuilder
q:=qb.ResultQueryAST.Clone(qb.SQLContext) as TSQLSelectStatement;
try
// refixup cloned query
q.RestoreColumnPrefixRecursive(true);
lCTE:=TObjectList.Create(false);
try
lFromObj:=TObjectList.Create(false);
try
q.PrepareAndFixupRecursive(lCTE,lFromObj);
finally
lFromObj.Free;
end;
finally
lCTE.Free;
end;

// in this simple example, we replace WHERE clause
// only if query is a simple single-SELECT statement
// without unions (but subqueries are possible)
if (q.Count>0)and(q[0] is TSQLSubQuerySelectExpression)
then begin
// prepare new expression
logicalExpression:=qb.SQLContext.ParseLogicalExpression(
'Orders.OrderId>100');

// delete old expression
select:=q[0] as TSQLSubQuerySelectExpression;
select.Where.Free;
select.Where:=nil;

// assign new expression
select.Where:=logicalExpression;

// refixup new query
q.RestoreColumnPrefixRecursive(true);
lCTE:=TObjectList.Create(false);
try
lFromObj:=TObjectList.Create(false);
try
q.PrepareAndFixupRecursive(lCTE,lFromObj);
finally
lFromObj.Free;
end;
finally
lCTE.Free;
end;

// display results
newSQL:=q.SimpleSQL(
qb.SQLContext.SQLBuilderExpressionForServer);
ShowMessage(newSQL);
end;

finally
q.Free;
end;
end;
===============

Please do not hesitate to contact us with any additional questions.

Sincerely
Sergey Smagin
Sergey Smagin | email
16 hours, 1 min since original post
Is it possible to integrate u r query builder with web application.
balaji | email
16 hours, 44 mins since original post
Yes, it is possible with the ActiveX Edition of Active Query Builder. You may find an example here: http://www.activequerybuilder.com/livedemo.html

The ASP.NET Edition will be available in a half of year.

Sergey
Sergey Smagin | email
16 hours, 49 mins since original post
This topic is closed