Pos, SplitString

[Henry Olive 5]

I wish everyone a heathy day.

I have a Memo.Text, if this memo.text starts with SELECT then

I'll open myquery like below

MyQuery.OPEN

if this memo.text starts  INSERT or UPDATE or DELETE or CREATE or ALTER then

I'll make ExecSql like below

MyQuery.EXECSQL;

 

I wrote below code

if Pos('SELECT', UpperCase(Trim(Memo1.Text))) > 0 then

MyQuery.Open else

MyQuery.ExecSql;

 

but if a procedure  has FOR SELECT then naturally i'm getting error msg.

because the query looks for EXECSQL but finds OPEN
Any advice ?

Thank You


 

 

 

 

 

[David Heffernan 2345]

Your Pos statement will find FOR SELECT so will call Open. But you say ExecSql is called. What you describe happening doesn't match the code therefore. I am confused.

 

Also SQL is case insensitive but Pos is not. 

[Lars Fosdal 1792]

How about testing for 'FOR SELECT' first?

Then again - what if there are two spaces between FOR and SELECT ? 

If you want to "trim" all spaces, you still have to avoid doing so inside string literals.

Parsing can be complicated.

 

I hope you don't plan to expose this to users, because they could wreak havoc on your database.

[Henry Olive 5]

Thank you so much David, Lars

David i'm so sorry  instead off OPEN i mistakely wrote EXECSQL
 

 

[aehimself 396]

This won't work, you have to parse at least a little bit. E.g.:

-- This is a very good query
/* Just for extra complexity */ SELECT * FROM MyTable

is a valid SQL query.

Zeos has a SmartOpen property, so you can call .Open even on queries with no resultset returned - although I never tested this.

 

My personal solution is:

Function IsOpenQuery(Const inSQL: String): Boolean;
Var
 fword: String;
 tokenizer: TZTokenizer;
 token: TZToken;
Begin
 tokenizer := TZTokenizer.Create;
 fword := '';
 Try
  For token In tokenizer.TokenizeBuffer(inSQL, [toSkipUnknown, toSkipWhitespaces, toSkipComments, toSkipEOF, toUnifyNumbers]) Do
   If token.TokenType = ttWord Then Begin
                                    fword := Copy(token.P, 0, token.L).ToLower;
                                    Break;
                                    End;
 Finally
  FreeAndNil(tokenizer);
 End;

Result := (fword = 'select') Or (fword = 'show') { Or fword.IsEmpty };
End;

It uses Zeos's tokenizer for parsing so I don't have to worry about comments and stuff.

 

Although it works, it is still vulnerable to the fact that (probably) there are a lot more statements than SELECT and SHOW, which will return a resultset.

[Henry Olive 5]

Thank you so much aehimself.
I think i need to find the FIRST WORD of the memo1.text  The other words are not important.

Because all commands starts with SELECT, CREATE,ALTER,INSERT,DELETE,UPDATE....

May be i should have asked the question like below

How can i get THE FIRST WORD of a memo1.text ?

Thank you again.
 

[aehimself 396]

4 minutes ago, Henry Olive said:

How can i get THE FIRST WORD of a memo1.text ?

Var firstword = String(Memo1.Text).Trim.Substring(0, String(Memo1.Text).Trim.IndexOf(' '));

Ineffective, but will yield you results 🙂

But as I mentioned, this will work only in some cases. I'd recommend against using such a solution anywhere near production.

[Uwe Raabe 2057]

1 hour ago, Henry Olive said:

I have a Memo.Text, if this memo.text starts with SELECT then

I'll open myquery like below

MyQuery.OPEN

if this memo.text starts  INSERT or UPDATE or DELETE or CREATE or ALTER then

I'll make ExecSql like below

MyQuery.EXECSQL;

 

uses
  System.StrUtils;
var
  S: string;
begin
  if Memo.Text = '' then
    raise Exception.Create('no SQL found');

  S := SplitString(Memo.Text, ' ')[0];
  case IndexText(S, ['SELECT', 'INSERT', 'UPDATE', 'DELETE', 'CREATE', 'ALTER']) of
    0: MyQuery.OPEN;
    1, 2, 3, 4, 5: MyQuery.EXECSQL;
  else
    raise Exception.Create('unhandled SQL command');
  end;
end;

 

[Fr0sT.Brutal 900]

function TIBDataSet.PSGetCommandType: TPSCommandType;
begin
  if not Prepared then
    Prepare;
  case SQLType of
    SQLSelect, SQLSelectForUpdate : Result := ctSelect;
    SQLInsert : Result := ctInsert;
    SQLUpdate : Result := ctUpdate;
    SQLDelete : Result := ctDelete;
    SQLDDL, SQLSetGenerator : Result := ctDDL;
    SQLExecProcedure : Result := ctStoredProc;
    else
      Result := ctUnknown;
  end;
end;

 

[Stano 143]

Quote

Because all commands starts with SELECT, CREATE, ALTER, INSERT, DELETE, UPDATE ....

What happens if you add over time WITH XX AS (... ?

[haentschman 92]

Hi...

Quote

I have a Memo.Text

I hope this SQL is only for you in the development?

 

For editing SQL by users...have you heard of SQL Injection? https://en.wikipedia.org/wiki/SQL_injection

[Henry Olive 5]

Thank you so much  aehimself, Uwe, Frost.Brutal, Stano, haentschman
 

I'll use this SQL routine in my development not for user.

I Think for me the most important thing is if memo1.text starts with SELECT or not

if it starts with  SELECT then i know it is a Regular Query ( Query.OPEN )  other wise i'll need to use EXECSQL

so if it is CTE query or ALTER or CREATE, or DELETE ......  then i'll use EXECSQL

My Code will be like below

Str := UpperCase(SplitString(Memo1.Text, ' ')[0]);

if Str ='SELECT' then Query1.Open else Query1.ExecSql;

 

 

 

 

 

[Lajos Juhász 293]

In case of the FireDAC there is already OpenOrExecute. 

[Fr0sT.Brutal 900]

Isn't Query.Active doing what is needed? I can't check now myself - busy with other projects

[Guest]

3 hours ago, Lajos Juhász said:

In case of the FireDAC there is already OpenOrExecute. 

I don't know shit about FireDAC.

But i am intimately knowledgeable about how this problem should be solved and i am SURE FireDAC can provide (as any full-blown DAC - Data Access Component - should).

The statement above is key.

Any serious DAC have loads of SQL parsing code in order to "pre-empt" the RDBMS API calls.

Use it instead of trying to create your own, that code is tested in a way a single "deveoper" cannot do.

 

And, as a last recommendation, never ever use DAC libs (or any other) without sources. If you do, all the fancy stuff will be hidden and you will re-invent lots of wheels.

[Remy Lebeau 1394]

10 hours ago, Henry Olive said:

I wrote below code

if Pos('SELECT', UpperCase(Trim(Memo1.Text))) > 0 then

MyQuery.Open else

MyQuery.ExecSql;

What about this?

uses
  ..., StrUtils;
  
if StartsText('SELECT ', TrimLeft(Memo1.Text)) then
  MyQuery.Open
else
  MyQuery.ExecSql;

Or:

if Memo1.Text.TrimLeft.StartsWith('SELECT ', True) then
  MyQuery.Open
else
  MyQuery.ExecSql;

 

[Lajos Juhász 293]

4 hours ago, Dany Marmur said:

Any serious DAC have loads of SQL parsing code in order to "pre-empt" the RDBMS API calls.

Of course you can parse the statements, however for a stored procedure call you must have a list of procedures that  returns cursors.

[Henry Olive 5]

Thank you so much FrostBrutal, Lajos, Remy

 

[Fr0sT.Brutal 900]

12 hours ago, Lajos Juhász said:

Of course you can parse the statements, however for a stored procedure call you must have a list of procedures that  returns cursors.

Generally speaking, only DB engine can reliably say what the query type is.

[mvanrijnen 123]

Would the query component know what type after a .Prepare statement ?

 

[Fr0sT.Brutal 900]

32 minutes ago, mvanrijnen said:

Would the query component know what type after a .Prepare statement ?

 

Depends on implementation. IBX likely will as the snippet I posted shows

[Guest]

4 hours ago, mvanrijnen said:

Would the query component know what type after a .Prepare statement ?

IBO does, the parsing code is almost on par with the server(s) themselves. CTEs, subselects, singletons, windowing, BLR, returning handling, SUID, parameter types, it knows most of all that stuff, some of it even before preparing (can be a huge performance boon not to have to query the server for everything). FireDAC really really should (no sources, so dunno).

[mvanrijnen 123]

Yes, most DB components do, i did abuse (years ago) with some specific software to calculcate expressions, i just send them to the dbserver, and that did the calculations for me.

SELECT (1+4)*5 AS result 
  FROM dummytable
  ROWS 1 TO 1

 

now days i opt for a different solution 😉

 

[Guest]

select [expression]

from rdb$database

 

Is not an antipattern IMHO, let's say you just need one of those session/attachment variables.

(rdb$database is the dummytabe of the database for FB.)

Agreed, that if the result is the same in OP/C#/Clientside/anything it's wasteful.

 

Neat, esp. before the admin tools could do proper trigger/sp debugging.