Recent

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

mpknap

  • Full Member
  • ***
  • Posts: 101
how to detect if there is a syntax error in SQL code?
« 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?

af0815

  • Sr. Member
  • ****
  • Posts: 459
Re: how to detect if there is a syntax error in SQL code?
« Reply #1 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 😊
regards
Andreas

mpknap

  • Full Member
  • ***
  • Posts: 101
Re: how to detect if there is a syntax error in SQL code?
« Reply #2 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.

MarkMLl

  • Hero Member
  • *****
  • Posts: 622
Re: how to detect if there is a syntax error in SQL code?
« Reply #3 on: January 19, 2020, 03:11:29 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

mangakissa

  • Hero Member
  • *****
  • Posts: 970
Re: how to detect if there is a syntax error in SQL code?
« Reply #4 on: January 20, 2020, 09:12:47 am »
I think try ...  except  ... end wil do the trick.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

MarkMLl

  • Hero Member
  • *****
  • Posts: 622
Re: how to detect if there is a syntax error in SQL code?
« Reply #5 on: January 20, 2020, 10:14:28 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
Turbo Pascal v1 on CCP/M-86, multitasking with LAN and graphics in 128Kb.
Pet hate: people who boast about the size and sophistication of their computer.

rvk

  • Hero Member
  • *****
  • Posts: 3918
Re: how to detect if there is a syntax error in SQL code?
« Reply #6 on: January 20, 2020, 11:37:44 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.

... and if  not inform before running (or not runnig) the script?


Zvoni

  • Sr. Member
  • ****
  • Posts: 334
Re: how to detect if there is a syntax error in SQL code?
« Reply #7 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
One System to rule them all, One IDE to find them,
One Code to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------
People call me crazy, because i'm jumping out of perfectly fine aircrafts

sash

  • Sr. Member
  • ****
  • Posts: 334
Re: how to detect if there is a syntax error in SQL code?
« Reply #8 on: January 20, 2020, 02:38:13 pm »
Since it is sqldb, there is always the
Code: Pascal  [Select]
  1. procedure TSQLQuery.Prepare;
function.
Lazarus 2.0.6 FPC 3.0.4 x86_64-linux-gtk2 @ Ubuntu 19.10 XFCE

mpknap

  • Full Member
  • ***
  • Posts: 101
Re: how to detect if there is a syntax error in SQL code?
« Reply #9 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.

rvk

  • Hero Member
  • *****
  • Posts: 3918
Re: how to detect if there is a syntax error in SQL code?
« Reply #10 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).

HeavyUser

  • Sr. Member
  • ****
  • Posts: 308
Re: how to detect if there is a syntax error in SQL code?
« Reply #11 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.

mangakissa

  • Hero Member
  • *****
  • Posts: 970
Re: how to detect if there is a syntax error in SQL code?
« Reply #12 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.
Lazarus 1.84 (32b) / FPC 3.0.4
Windows 10

Thaddy

  • Hero Member
  • *****
  • Posts: 9637
Re: how to detect if there is a syntax error in SQL code?
« Reply #13 on: January 21, 2020, 08:46:15 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.
« Last Edit: January 21, 2020, 08:52:45 am by Thaddy »
I am more like donkey than shrek

rvk

  • Hero Member
  • *****
  • Posts: 3918
Re: how to detect if there is a syntax error in SQL code?
« Reply #14 on: January 21, 2020, 11:29:29 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.