SQLite3connection: near "(": Syntax error in Lazarus

Question

I have a SQL deviation statement, but I found the error when running. I have researched many related topics to come up with solutions, but I have not yet solved it.

SQLite3connection: near "(": Syntax error

I hope to get help from everyone.

strSQL:='SELECT Mathang.Stt
         ,Mathang.Mahang
         , Mathang.Tenhang
         ,SUM(CASE 
                WHEN Xuatnhap.Loaiphieu="N" 
                     AND SQLQuery1.FieldByName("Ngay").asString <= StrToDate(Edit1.Text) 
                THEN Xuatnhap.Soluong  
                ELSE 0  
              END) AS Tongnhap
         ,SUM(CASE
                WHEN Xuatnhap.Loaiphieu="X" 
                     AND SQLQuery1.FieldByName("Ngay").asString <= StrToDate(Edit1.Text) 
                THEN Xuatnhap.Soluong   
              ELSE 0  
           END) AS Tongxuat
          ,SUM(CASE 
                WHEN Xuatnhap.Loaiphieu="N" 
                     AND SQLQuery1.FieldByName("Ngay").asString <= StrToDate(Edit1.Text) 
                THEN Xuatnhap.Soluong 
               ELSE 0 
               END) - SUM(CASE 
                            WHEN Xuatnhap.Loaiphieu="X" 
                                 AND SQLQuery1.FieldByName("Ngay").asString <= StrToDate(Edit1.Text) 
                            THEN Xuatnhap.Soluong 
                            ELSE 0  
                          END) AS Tongton 
         FROM Mathang INNER JOIN Xuatnhap 
                    ON Mathang.Mahang=Xuatnhap.Mahang 
                   GROUP BY  Mathang.Stt,Mathang.Mahang, Mathang.Tenhang';
`

Answer 1

There are a couple of problems with your q;

Firstly, although the code you initially posted as syntactically correct, someone has edited it, I assume to improve its legibility, so that the RHS of the assignment is on several lines, and that has made it syntactically incorrect. The reason it is incorrect is that in Pascal the opening quote ' of a string expression must occur on the same line as its closing quote: the example below shows how to work around this.

Secondly, I assume you intend to assign the value of your strSQL variable to the SQL property of your SQLQuery1 but, if so, there are several problems with it.

It would have helped if you had tried iteratively commenting out all except one of the SUM clauses and all except one of the expressions in it to locate exactly where the error is occurring.

However, most likely the error is in the expression

SQLQuery1.FieldByName("Ngay").asString <= StrToDate(Edit1.Text)

which occurs in 4 places. The reason is that the SQL string you construct is handed over to the Sqlite engine (contained in the file Sqlite3.Dll) for execution and this has no access to what is contained in the properties of components in your application. So, it is invalid for the SQL to contain references to the contents of the SQLQuery1 component's fields, the contents of Edit1's Text field and the Lazarus StrToDate function. You will need to replace them by values which are valid in a SQL expression, such as values in the columns of the joined tables, constants or the values of parameters in the query.

The next problem is that even if the above problems are corrected, your code would be vulnerable to SQL injection - see https://en.wikipedia.org/wiki/SQL_injection. To avoid this, rather than allow the contents of Edit1.Text to be included in the SQL sent to the server, you should parameterize the SQL with a date parameter and use the contents of Edit1.Text to supply the value of the parameter.

So, it would be better to write your strSQL assignment more like this (I will use a ver much simplified version)

strSQL :=
  'Select ' +
  '  * ' +
  'from ' +
  '  MyTable ' +
  'where ' +
    'Date <= :Date ';  //  :Date is the parameter value for the Date column of MyTable

Then, you could use this SQL to execute the query

if SqlQuery1.Active then
  SqlQuery1.Close;
SqlQuery1.Params.ParamByName('Date').Value := StrToDate(Edit1.Text);

SqlQuery1.Prepare;
SqlQuery1.Open;