Partial SQL filter method/componet/library
lazarus / 16-Databases

----------------------------------------------------------------------
vladvons · 2013-10-21 08:37

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 · 2013-10-21 10:19

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 · 2013-10-21 10:25

Quote from: Groffy on October 21, 2013, 12:19:09 pm01.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 · 2013-10-21 10:45

Quote from: BigChimp on October 21, 2013, 12:25:32 pmAnything 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 · 2013-10-21 10:49

Ok, that's cleared things up, thanks.