Partial SQL filter method/componet/library

[vladvons]

Is there some library or component for generating SQL text using input search conditions?
Something similar to DataSet.Filter property.

for example:

Code: [Select]

SomeSQLGenerator.ConditionAdd('Name', tEqual, 'John');
Some.SQLGenerator.ConditionAdd('DayOfBith', tLess, '01.01.1980');

String1 := 'SELECT * FROM Users WHERE ' + SomeSQLGenerator.GetText();

so, we should get in String1 something like:

Code: [Select]

SELECT
  *
FROM
  Users
WHERE
  Name = 'John' AND DayOfBith < '01.01.1980'

Im not lazy, just dont wanna envent a wheel

[Groffy]

Hello,

your requirements are low, in my eyes not worth to develop any control or class for this, just to avoid typing "where" and "and". How should the parameter be for checking a range like

01.01.2013 < DayOfBirth < 31.12.2013 ?

The SQL builder class should also know the database fieldtype for generating the where - statement correctly. Date and string literals need additional apostrophe, numerical values not.

Are you used to build your SQL statements static? I suggest to use parameters for changing your filter criteria values more easily. With prepared parametrized queries you don't need to assign your static SQL statements again and again. ZSQLProcessor control from ZEOS library can help you in this case.

Just my personal suggestions...

[BigChimp]

01.01.2013 < DayOfBirth < 31.12.2013 ?

Anything wrong with ISO formatted dates ?

I'm having trouble understanding the rest of your answer - especially the part about parameterized queries - you can't really add/remove where clauses when using parameterized queries in sqldb without having to prepare the query again.

Are you saying ZSQLProcessor provides a solution like vladvons asked? In other words, use Zeos? I'd be very surprised if Zeos could keep one single prepared query statement with varying parameters in the WHERE clause but who knows...

@vladvons: there was a thread with a visual query builder tool which might be useful if you want to provide the ability (to e.g. end users) to dynamically generate SQL. Don't know if it covers where statements, but might be worth looking up.

[Groffy]

Anything wrong with ISO formatted dates ?

Not, just did not think about format while typing in ;-)

Of course parametrized queries only making sense when type and number of parameters not changing in between.

I have to confess, that I'm not using ZEOS at all. Just saw this component and where playing around a bit. When typing in a SQL statement the component seems to parse for parameters...

I know, there are some visual SQL builders around, but I thought that vladvons wanted the statement creation by code during runtime.

[BigChimp]

Ok, that's cleared things up, thanks.