Recent

Author Topic: Partial SQL filter method/componet/library  (Read 2766 times)

vladvons

  • Jr. Member
  • **
  • Posts: 65
Partial SQL filter method/componet/library
« on: October 21, 2013, 10:37:56 am »
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
« Last Edit: October 21, 2013, 10:45:57 am by vladvons »
Windows 7, Ubuntu 12.04, Lazarus 1.2.2, FPC 2.6.4, PostgreSQL 9.2

Groffy

  • Full Member
  • ***
  • Posts: 210
Re: Partial SQL filter method/componet/library
« Reply #1 on: October 21, 2013, 12:19:09 pm »
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...

Linux Mint 18.3 - KDE / Windows 10 / Lazarus 2.0.6 / trunk -qt

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Partial SQL filter method/componet/library
« Reply #2 on: October 21, 2013, 12:25:32 pm »
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.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

Groffy

  • Full Member
  • ***
  • Posts: 210
Re: Partial SQL filter method/componet/library
« Reply #3 on: October 21, 2013, 12:45:01 pm »
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.

« Last Edit: October 21, 2013, 12:47:45 pm by Groffy »
Linux Mint 18.3 - KDE / Windows 10 / Lazarus 2.0.6 / trunk -qt

BigChimp

  • Hero Member
  • *****
  • Posts: 5740
  • Add to the wiki - it's free ;)
    • FPCUp, PaperTiger scanning and other open source projects
Re: Partial SQL filter method/componet/library
« Reply #4 on: October 21, 2013, 12:49:51 pm »
Ok, that's cleared things up, thanks.
Want quicker answers to your questions? Read http://wiki.lazarus.freepascal.org/Lazarus_Faq#What_is_the_correct_way_to_ask_questions_in_the_forum.3F

Open source including papertiger OCR/PDF scanning:
https://bitbucket.org/reiniero

Lazarus trunk+FPC trunk x86, Windows x64 unless otherwise specified

 

TinyPortal © 2005-2018