Recent

Author Topic: <SOLVED> Syntax for user input value in SQLiteQuery  (Read 251 times)

Bert_Plitt

  • Jr. Member
  • **
  • Posts: 62
<SOLVED> Syntax for user input value in SQLiteQuery
« on: June 13, 2022, 09:37:16 pm »
Hello, I need help with SQLite query syntax.  I wrote a demo project to show how to find the nearest of an array of ten points to a point entered by the user.  The ten points are included in a table named POINTS.DB, which has four columns: IDX, NAME, XCOORD, YCOORD.  The user point is input by two TEdit controls: named edtXCoord and edtYCoord.  When a search button is clicked, I want to display the point in the table nearest to the input point.

The following is the code I tried to do the search:

procedure TForm1.sbSearchClick(Sender: TObject);
var
  xVal, yVal: Extended;
begin
  xVal:=StrToFloat(edtXCoord.Text);
  yVal:=StrToFloat(edtYCoord.Text);
  qryPoints.Close;
  qryPoints.SQL.Text :=
    'select * FROM Points order by '+
    '((xVal-XCoord)*(xVal-XCoord) + (yVal-YCoord)*(yVal-YCoord)) LIMIT 1';
  qryPoints.Open;
end;

This code produces an error: 'no such column: xVal'

My question is what's the code needed to get the values of xVal and yVal included in the Query?
« Last Edit: June 13, 2022, 11:16:42 pm by Bert_Plitt »
Windows 10, Lazarus 2.2.2, FPC 3.2.2

dseligo

  • Hero Member
  • *****
  • Posts: 651
Re: Syntax for user input value in SQLiteQuery
« Reply #1 on: June 13, 2022, 10:18:38 pm »
My question is what's the code needed to get the values of xVal and yVal included in the Query?

You need parameters in query (they are preceded with colon sign):

Code: Pascal  [Select][+][-]
  1. procedure TForm1.sbSearchClick(Sender: TObject);
  2. var
  3.   xVal, yVal: Extended;
  4. begin
  5.   xVal:=StrToFloat(edtXCoord.Text);
  6.   yVal:=StrToFloat(edtYCoord.Text);
  7.   qryPoints.Close;
  8.   qryPoints.SQL.Text :=
  9.     'select * FROM Points order by '+
  10.     '((:xVal-XCoord)*(:xVal-XCoord) + (:yVal-YCoord)*(:yVal-YCoord)) LIMIT 1';
  11.   qryPoints.ParamByName('xVal').AsFloat := xVal;
  12.   qryPoints.ParamByName('yVal').AsFloat := yVal;
  13.   qryPoints.Open;
  14. end;

Bert_Plitt

  • Jr. Member
  • **
  • Posts: 62
Re: Syntax for user input value in SQLiteQuery
« Reply #2 on: June 13, 2022, 11:14:54 pm »
Thanks dseligo, works great.   I figured it was something simple!
Windows 10, Lazarus 2.2.2, FPC 3.2.2

 

TinyPortal © 2005-2018