Recent

Author Topic: how to detect if there is a syntax error in SQL code?  (Read 5425 times)

mpknap

  • Full Member
  • ***
  • Posts: 155
Re: how to detect if there is a syntax error in SQL code?
« Reply #15 on: January 22, 2020, 08:39:17 pm »
Thanks. Not very important. I am giving up this problem, the more that I do not understand your solutions. ;)
im beginer....

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: how to detect if there is a syntax error in SQL code?
« Reply #16 on: January 23, 2020, 11:04:29 am »
Thanks. Not very important. I am giving up this problem, the more that I do not understand your solutions. ;)
im beginer....
Not really wise to give up, even if you are a beginner. How are you going to learn then?

What we were talking about is catching that exception dialog. This is easy to do with try and except.
(also see https://www.freepascal.org/docs-html/ref/refse113.html)

From your example:
Code: Pascal  [Select][+][-]
  1. SQLite3Connection1.DatabaseName := 'b.sqlite';
  2. SQLite3Connection1.Connected := True;
  3. SQLTransaction1.DataBase := SQLite3Connection1;
  4. SQLQuery1.DataBase := SQLite3Connection1;
  5. SQLTransaction1.Active := True;
  6. SQLQuery1.SQL.Text :=   memo1.Lines.Text ;
  7. try
  8.   SQLQuery1.Open;
  9.   DataSource1.DataSet := SQLQuery1;
  10.   DBGrid1.DataSource := DataSource1;
  11.   DBGrid1.AutoFillColumns := True;
  12. except
  13.   on E: Exception do
  14.     Showmessage('Error ' + E.Message);
  15. end;

Do note that when you run this in the IDE with debugging on (F9) you will still get a dialog from the debugger. But if you click continue, you'll get your own dialog. And if you run without debugger (Shift+Ctrl+F9) or outside the IDE, you'll ONLY get your own dialog, like intended.

Zvoni

  • Hero Member
  • *****
  • Posts: 2300
Re: how to detect if there is a syntax error in SQL code?
« Reply #17 on: January 23, 2020, 12:00:56 pm »
Another thing, what i, sash and Thaddy tried to tell you:
1) Me (with the Link to the sqlite-doc's): That is what sqlite is actually doing BEFORE executing a SQL-Statement. It prepares the statement, and if it is a faulty SQL-Statement you receive an error-code back.
2) Sash: That's what the "Prepare"-Method is actually for (without having checked the source-code, i think it actually calls the function i provided in my link).
3) Thaddy: Use the SQL-Parser he mentioned.
Bottom Line: the easiest method to check if you have a faulty SQL-Statement is to actually run the statement, and rvk showed you how to catch it.
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad

mpknap

  • Full Member
  • ***
  • Posts: 155
Re: how to detect if there is a syntax error in SQL code?
« Reply #18 on: January 23, 2020, 01:03:43 pm »
Ok RVK, it works. Instead of SHOWMESSAGE I made in memo1 the text is red :)

I have another little problem. When loading different SQLITE files, each one has a FRAME_CONTENT column, but once it is column 3, in another file column 5.

How to find a column with this content and return to the integer variable.
something like
x: = dbgrid.findcolumn ('Frame_content') ???

rvk

  • Hero Member
  • *****
  • Posts: 6056
Re: how to detect if there is a syntax error in SQL code?
« Reply #19 on: January 23, 2020, 01:14:35 pm »
Attached is an example of my application.
You'll see a Line and column number.
When you get an error you could put the cursor on the place of the error (after parsing the line and column numbers).

I have another little problem. When loading different SQLITE files, each one has a FRAME_CONTENT column, but once it is column 3, in another file column 5.
How to find a column with this content and return to the integer variable.
something like
x: = dbgrid.findcolumn ('Frame_content') ???
You probably mean
Code: Pascal  [Select][+][-]
  1. X := SQLQuery1.FieldByName('FRAME_CONTENT').FieldNo;
  2. // or
  3. X := DBGrid1.Columns.ColumnByFieldname('FRAME_CONTENT').Index;


mpknap

  • Full Member
  • ***
  • Posts: 155
Re: how to detect if there is a syntax error in SQL code?
« Reply #20 on: January 23, 2020, 07:41:53 pm »
  ;)

 

TinyPortal © 2005-2018