Lazarus

Programming => Databases => Topic started by: mpknap on January 19, 2020, 01:00:18 pm

Title: how to detect if there is a syntax error in SQL code?
Post by: mpknap on January 19, 2020, 01:00:18 pm
It's a piece of code that loads data into DBGRID1.
Works well.

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 :=
  7.     'SELECT  user_id, device_id ,timestamp, frame_content FROM (SELECT  *  ,(Timestamp) - LAG(Timestamp ,1,2)   OVER (PARTITION BY device_id ORDER BY Timestamp) diff    FROM        detections) d Where  diff < 3500';
  8.  
  9.   SQLQuery1.Close;
  10.   SQLQuery1.Open;
  11.   DataSource1.DataSet := SQLQuery1;
  12.   DBGrid1.DataSource := DataSource1;
  13.   DBGrid1.AutoFillColumns := True;  

But I want to do MEMO1, where the user will write SQL code and run the code.

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.  
  7.    SQLQuery1.SQL.Text :=   memo1.Lines.Text ;
  8.  
  9.   SQLQuery1.Close;
  10.   SQLQuery1.Open;
  11.   DataSource1.DataSet := SQLQuery1;
  12.   DBGrid1.DataSource := DataSource1;
  13.   DBGrid1.AutoFillColumns := True;  


Everything ok as long as the SQL is correct.
How can you check if the SQL code entered by User is good and if  not inform before running (or not runnig) the script?
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: af0815 on January 19, 2020, 02:04:31 pm
maybe a starting point in fpc sources in fc-db examples sqlparser.

my opinion is not to accept sql from a unsecure source like a user 😊
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mpknap on January 19, 2020, 02:19:16 pm
this is a small offline tool for browsing the database file on my hard drive, basically for me only.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: MarkMLl on January 19, 2020, 03:11:29 pm
Quote from: mpknap on January 19, 2020, 01:00:18 pm
How can you check if the SQL code entered by User is good and if  not inform before running (or not runnig) the script?

Does your server of choice have anything similar to PostgreSQL's "explain" command?

Perhaps another question to be asked is "how can one check whether SQL code entered by the user has unanticipated side effects?"

https://www.xkcd.com/327/

MarkMLl
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mangakissa on January 20, 2020, 09:12:47 am
I think try ...  except  ... end wil do the trick.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: MarkMLl on January 20, 2020, 10:14:28 am
Quote from: mangakissa on January 20, 2020, 09:12:47 am
I think try ...  except  ... end wil do the trick.

Not helpful: specific question was how to check validity of the SQL before attempting to run it.

MarkMLl
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: rvk on January 20, 2020, 11:37:44 am
Quote from: MarkMLl on January 20, 2020, 10:14:28 am
Quote from: mangakissa on January 20, 2020, 09:12:47 am
I think try ...  except  ... end wil do the trick.
Not helpful: specific question was how to check validity of the SQL before attempting to run it.
Yes, that was the question. But maybe TS can still use this method anyway. It gives an error if the SQL is incorrect and from the fail message you can pinpoint the exact error in the SQL.

I use it too. When the user presses Execute, it gives a result-grid if all goes well. Otherwise it gives the error en points the cursor to the given location.

Quote from: mpknap on January 19, 2020, 01:00:18 pm
... and if  not inform before running (or not runnig) the script?

Title: Re: how to detect if there is a syntax error in SQL code?
Post by: Zvoni on January 20, 2020, 11:59:12 am
Since it's SQLite, there is always the "Prepare"-Function.
https://www.sqlite.org/c3ref/prepare.html
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: sash on January 20, 2020, 02:38:13 pm
Since it is sqldb, there is always the
Code: Pascal  [Select][+][-]
  1. procedure TSQLQuery.Prepare;
function.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mpknap on January 20, 2020, 08:25:33 pm
Once again because maybe I didn't write clearly.
Two screens in the attachment.

in the good.jpg file the SQL line is correctly written and I can press the RUN button and execute the command.

In the bad.jpg file I intentionally added the word SOMETHING (marked in yellow). After pressing RUN the OK / ABORT window is displayed.

My question is whether it is possible that after pressing RUN you can check the syntax if it is correct or not, and if not, do not try to execute the SQL line and avoid displaying the window with OK / ABORT.

This is a SQLITE file viewer (offline) that allows me to view the frame_content content graphically (in BASE64). Something like DBBrowser for Windows.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: rvk on January 20, 2020, 08:33:23 pm
The answer is already (partially) given. You can wrap the execute/execsql/open/prepare in a try/except. In the exception handler you can display a correct message.

Do note that running in the IDE with the debugger on you'll also get the exception screen but outside the IDE or without debugger (shift+ctrl+f8) you'll only get your message.

(You can't do it without an exception handler because the server needs to evaluate the sql and only gives an exception back (even prepare does it like this).
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: HeavyUser on January 20, 2020, 08:47:44 pm
In addition to rvk's info you can change the exception dialog type to a message box with a less scary message by setting the Application.ExceptionDialog property to aedOkMessageBox.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mangakissa on January 21, 2020, 08:33:02 am
Quote

Quote
I think try ...  except  ... end wil do the trick.
/quote]
 Not helpful: specific question was how to check validity of the SQL before attempting to run it.
.
There's no way to check the query before running it. Even other SQL editors runs or prepares the query and handles an exception if the query isn't right.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: Thaddy on January 21, 2020, 08:46:15 am
Quote from: mangakissa on January 21, 2020, 08:33:02 am
There's no way to check the query before running it. Even other SQL editors runs or prepares the query and handles an exception if the query isn't right.
Of course there is: the fcl-db package contains a full SQL parser/scanner/syntaxtree that can validate the SQL without executing it. It can parse almost all Firebird SQL.
It can be adapted for other dialects.

So syntax checking is not that difficult at all.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: rvk on January 21, 2020, 11:29:29 am
Quote from: Thaddy on January 21, 2020, 08:46:15 am
Quote from: mangakissa on January 21, 2020, 08:33:02 am
There's no way to check the query before running it. Even other SQL editors runs or prepares the query and handles an exception if the query isn't right.
Of course there is: the fcl-db package contains a full SQL parser/scanner/syntaxtree that can validate the SQL without executing it. It can parse almost all Firebird SQL.
Without using try/except? Really?

Yes it can validate the SQL without executing it, but I think the parser will also throws an exception which you need to handle.

But ok, you could overload the Error() handler and handle this yourself.
But just doing an Execute to the database is much easier. Especially because TS wants to run the query anyway.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mpknap 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....
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: rvk on January 23, 2020, 11:04:29 am
Quote from: mpknap 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....
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.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: Zvoni 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.
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mpknap 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') ???
Title: Re: how to detect if there is a syntax error in SQL code?
Post by: rvk 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).

Quote from: mpknap on January 23, 2020, 01:03:43 pm
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;

Title: Re: how to detect if there is a syntax error in SQL code?
Post by: mpknap on January 23, 2020, 07:41:53 pm
  ;)
TinyPortal © 2005-2018