[fpc-devel] RFC: sqlDB extend TStatementType

[michael.vancanneyt at wisa.be]

On Thu, 19 Apr 2012, LacaK wrote:

> Hi ,
> now we have in sqlDB.pp:
>
> TStatementType = (stNone, stSelect, stInsert, stUpdate, stDelete,
>   stDDL, stGetSegment, stPutSegment, stExecProcedure,
>   stStartTrans, stCommit, stRollback, stSelectForUpd);
>
> Statement type is initialy determined by parsing SQL.Text in function 
> TCustomSQLQuery.SQLParser and function TSQLConnection.StrToStatementType 
> based on first word (token) of SQL.Text.
> (i.e. 'SELECT ...' -> stSelect, 'INSERT ...' -> stInsert, 'CREATE ...' -> 
> stDDL, etc.)
> As you can see there is very limited count of sql statements which are 
> recognized (but this is not problem ;-)).
>
> Statement types stSelect and stExecProcedure have special meaning, because 
> only these two types are allowed when using Open method
> (i.e. only these two types are expecting, that return any data rows; see 
> procedure TCustomSQLQuery.InternalOpen; )

In fact, this is not quite correct.

Insert into table (a,b,c) returning values (x,y,z)

can also be done with an Open, yet it will probably get stInsert as type.

>
> Due to this fact also other sql statements like SHOW, PRAGMA, TRANSFORM, 
> MySQL Admin.statements (CHECK TABLE, REPAIR TABLE etc.) which return data are 
> mapped to stSelect (and others ATM unhandled like CTEs 'WITH ...' and 'PIVOT 
> ...' etc. must in future also map to stSelect).
> This is OK as far as we do not expect, that stSelect = 'SELECT ...' , which 
> is NON-intuitive assumption.
>
> So I am thinking about adding new statement type stQuery and map all other 
> than 'SELECT ...' statements that return dataset to this general statement 
> type.
> ( See also http://docwiki.embarcadero.com/VCL/en/DB.TPSCommandType and 
> http://docwiki.embarcadero.com/VCL/en/IBSQL.TIBSQLTypes )
>
> But because this change is not unavoidable I am not sure if go this way or 
> leave it as is and definitely abandon that stSelect is 'SELECT ...' ?
> What do you think ?

It is not clear to me what are you trying to accomplish with this change ?

Michael.