Jump to content
Henry Olive

Pos, SplitString

Recommended Posts

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


 

 

 

 

 

Share this post


Link to post

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. 

Share this post


Link to post

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.

Share this post


Link to post

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.

Share this post


Link to post

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.
 

Share this post


Link to post
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.

Share this post


Link to post
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;

 

Share this post


Link to post
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;

 

Share this post


Link to post
Quote

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


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

  • Like 2

Share this post


Link to post

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;

 

 

 

 

 

Share this post


Link to post
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.

Share this post


Link to post
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;

 

  • Like 1

Share this post


Link to post
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.

Share this post


Link to post
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.

Share this post


Link to post
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

Share this post


Link to post
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).

Share this post


Link to post

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 😉

 

Share this post


Link to post

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.

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×