[SOLVED] SQLite search
lazarus / 16-Databases

----------------------------------------------------------------------
Pe3s · 2023-06-11 10:35

Hello all forum users, I have such a problem, I want to make a search
engine with the option of choosing from ComboBox which column to
search for. My code only looks for one column, how can I fix it?
Regards

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Edit1Change(Sender: TObject);begin  with ZQuery1 dobegin 
SQL.Text := 'SELECT * FROM product WHERE Name LIKE :Name'; 
ParamByName('Name').AsString := Edit1.Text + '%';  Open;end;end;

----------------------------------------------------------------------
TRon · 2023-06-11 10:56

Untested and from memory:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //
initialize the combobox items "somewhere"procedure
TForm1.FormCreate(Sender: TObject);  begin  ComboBox1.Items.Clear; 
ComboBox1.Items.Add('NameOfColumn1'); 
ComboBox1.Items.Add('NameOfColumn2'); 
ComboBox1.Items.Add('NameOfColumnN');end; procedure
TForm1.Edit1Change(Sender: TObject);var  ColumnName: string;begin 
ColumnName := ComboBox1.Items[ComboBox1.ItemIndex];     with ZQuery1
do  begin    SQL.Text := 'SELECT * FROM product WHERE :ColumnName LIKE
:Name';    ParamByName('ColmunName').AsString := ColumnName;   
ParamByName('Name').AsString := Edit1.Text + '%';    Open;  end;end;

----------------------------------------------------------------------
paweld · 2023-06-11 11:23

@TRon: It won't work that way, because the column name will be
inserted as a text value and the query will look like this: 
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT *
FROM product WHERE 'NameOfColumn1' LIKE 'Edit1%'  
@Pe3s: as @TRon wrote, only the column name needs to be part of the
query, not passed as a parameter 
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //...  
SQL.Text := 'SELECT * FROM product WHERE ' + ColumnName + ' LIKE
:Name';    ParamByName('Name').AsString := Edit1.Text + '%';    Open;
 //...

----------------------------------------------------------------------
TRon · 2023-06-11 11:29

Quote from: paweld on June 11, 2023, 01:23:06 pm@TRon: It won't work
that way, because the column name will be inserted as a text value and
the query will look like this: 
Ah yes ofc. my bad. Thank you for the correction.

----------------------------------------------------------------------
Pe3s · 2023-06-11 14:08

@TRon, @paweld, Thank you

----------------------------------------------------------------------
Zvoni · 2023-06-12 07:06

Final note: Table-/Column-Names cannot be parameterized (as paweld
pointed out).
As Pawel said: Use String-Concatenation
OR
There is a workaround in SQLite to use Table-/Column-Names as
parameters, which involves a staging table.
If anyone's interested give a shout

----------------------------------------------------------------------
Pe3s · 2023-06-13 15:11

@Zvoni, Interesting topic, so how do you use a staging table?

----------------------------------------------------------------------
Zvoni · 2023-06-14 06:27

Quote from: Pe3s on June 13, 2023, 05:11:53 pm@Zvoni, Interesting
topic, so how do you use a staging table?

The trick is as follows:
You create a staging table like this
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE IF NOT EXISTS "tbl_staging" (    "TableName"    TEXT,   
"FieldName"    TEXT,    "FieldParam"    TEXT,          
"SQLSelectLike" TEXT GENERATED ALWAYS AS ('SELECT * FROM ' ||
"TableName" || ' WHERE ' || "FieldName" || ' LIKE ' || "FieldParam")
VIRTUAL,    "SQLSelectEqual" TEXT GENERATED ALWAYS AS ('SELECT * FROM
' || "TableName" || ' WHERE ' || "FieldName" || '=' || "FieldParam")
VIRTUAL);Note: Not sure if any of those SQLite-Admin-Tools (SQLite-
Browser etc.) support "visual" creation of computed columns
("GENERATED ALWAYS AS").
You'd have to use the SQL-Syntax as shown

Next: Make sure it's empty before you use it --> Just fire off a Code:
SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i DELETE FROM
tbl_staging
Next step:
Insert into that table the tableName from which you want to select,
the Field/ColumnName you want to use in your WHERE-Clause, and the
Parameter-Name (!!)
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i INSERT INTO
tbl_staging("TableName","FieldName","FieldParam")
VALUES('SomeTableName','SomeColumnName',':ParamName');Note: In the
VALUES-Section you can also use parameters.
Important: Note the double-colon in the third Value! I'm passing the
ParameterName completely

Next: Now Read the Column of the SELECT you want to use. I have two
columns: one for LIKE, one for equality
Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i SELECT
SQLSelectEqual FROM tbl_stagingThis returns someting like this

SELECT * FROM SomeTableName WHERE SomeColumnName=:ParamName

You assign THIS to your SQL.Text-Property of your TSQLQuery, and then
just use it as you're used to.

This technique can also be used for UPDATE, INSERT and DELETE

Now encapsulate all that into your own class, and it becomes basically
a shoot and forget.
Your call could look something like this
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i Var 
t:TSQLStage;Begin  t:=TSQLStage.Create('SomeTableName',
'SomeColumnName',':ParamName');
 //t:=TSQLStage.Create('SomeTableName', ColumnNameVar,':ParamName');
 YourQuery.SQL.text:=t.SQLSelectLike;  t.Free; 
YourQuery.ParamByName('ParamName').AsString:=Edit1.Text + '%'; 
YourQuery.Open;End; 
In a nutshell: You don't "construct" your SQL-String in your
(Frontend-) Code but inside the Database itself.
That way, if you have to change something later on, you change it in
the Database, and don't have to recompile your Frontend.
as long as the "Interface" of the Queries remain the same

----------------------------------------------------------------------
Pe3s · 2023-06-14 17:26

@Zvoni, Thank you for your valuable knowledge.