[fpc-devel] RFC: sqlDB extend TStatementType
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; ) 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 ? TIA -Laco. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] RFC: sqlDB extend TStatementType
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. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] RFC: sqlDB extend TStatementType
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. This case is handled in PrepareStatement phase. In case of IBConnection is StatementType re-mapped to stExecProcedure and in case of PQConnection is re-maped to stSelect. 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 ? Hm, good point ;-) Answer depends on answer of question what is purpose of StatementType ? What kind of information do we expect ? How detailed ? In my mind is strange if stSelect is once true 'SELECT ' then 'INSERT INTO ... RETURNING ' then 'SHOW TABLES' then 'REPAIR TABLES' etc. I would expect, that stSelect is always only 'SELECT' and other sql statements, that may return data map to any other StatementType ... may be, we can all other map to stExecProcedure (if we do not want introduce new statement type)? (like it is done internaly by fbclient for ...returning statements) There is no technical problem only in my mind current situation is bit strange (may be I can say uncomplete or unintuitive or unclear). But if nobody else see it as I do, then I can live with this ;-) Looking at sources it seems, that property StatementType is protected so it is used mostly for internal purposes. Note, that there is in db.pas also: TPSCommandType = ( ctUnknown, ctQuery, --- ctTable, ctStoredProc, ctSelect, --- ctInsert, ctUpdate, ctDelete, ctDDL ); and virtual method of TDataSet.PSGetCommandType So in future in TCustomSQLQuery somebody may want override PSGetCommandType and do maping from StatementType to CommandType (this is other reason why I am thinking about new stQuery) L. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] RFC: sqlDB extend TStatementType
On Thu, 19 Apr 2012, LacaK wrote: 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. This case is handled in PrepareStatement phase. In case of IBConnection is StatementType re-mapped to stExecProcedure and in case of PQConnection is re-maped to stSelect. That should obviously be changed to return the same type :-) 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 ? Hm, good point ;-) Answer depends on answer of question what is purpose of StatementType ? What kind of information do we expect ? How detailed ? In my mind is strange if stSelect is once true 'SELECT ' then 'INSERT INTO ... RETURNING ' then 'SHOW TABLES' then 'REPAIR TABLES' etc. I would expect, that stSelect is always only 'SELECT' and other sql statements, that may return data map to any other StatementType ... may be, we can all other map to stExecProcedure (if we do not want introduce new statement type)? (like it is done internaly by fbclient for ...returning statements) As far as I know, the statementtype is mostly (if not only) used to determine whether Open or ExecSQL must be used. For this purpose, no new statement type is needed IMHO. If you want more detailed information, then the new statement type can be introduced. But then one could argue that a lot of new types can be introduced: stAlterObject or stDDL and so on. One would also need to see to it that stQuery can be used both in Open and ExecSQL, since you don't know in detail what the statement does. Maybe Joost can comment on this. Michael. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
Re: [fpc-devel] RFC: sqlDB extend TStatementType
This case is handled in PrepareStatement phase. In case of IBConnection is StatementType re-mapped to stExecProcedure and in case of PQConnection is re-maped to stSelect. That should obviously be changed to return the same type :-) Yes any rules will be welcomed. 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 ? Hm, good point ;-) Answer depends on answer of question what is purpose of StatementType ? What kind of information do we expect ? How detailed ? In my mind is strange if stSelect is once true 'SELECT ' then 'INSERT INTO ... RETURNING ' then 'SHOW TABLES' then 'REPAIR TABLES' etc. I would expect, that stSelect is always only 'SELECT' and other sql statements, that may return data map to any other StatementType ... may be, we can all other map to stExecProcedure (if we do not want introduce new statement type)? (like it is done internaly by fbclient for ...returning statements) As far as I know, the statementtype is mostly (if not only) used to determine whether Open or ExecSQL must be used. For this purpose, no new statement type is needed IMHO. If you want more detailed information, then the new statement type can be introduced. Personaly I do not need it. I only think, that it will provide more clear design. But then one could argue that a lot of new types can be introduced: stAlterObject or stDDL and so on. Exactly. It is not my intention make for each existing sql statement their corresponding StatementType (1:1) My thinking is in oposite way. Have only small group of statements, theoreticaly something like: stQuery ... selects, shows, pragmas, CTEs stDML ... insert, update, delete, merge, replace stExecProcedure ... executing procedures, functions, may be DML with ... returning ... stDDL ... create, drop, truncate and get rid of stGetSegment, stPutSegment, stSelectForUpd etc, which are IMO unnecessarily. One would also need to see to it that stQuery can be used both in Open and ExecSQL, since you don't know in detail what the statement does. meaning of stQuery would be, that statement return data, so it can also be used in ExecSQL, but result must be processed (silently discarded or so) Maybe Joost can comment on this. If nobody will comment, I leave things as are. I only wanted bring to light this thing. -Laco. ___ fpc-devel maillist - fpc-devel@lists.freepascal.org http://lists.freepascal.org/mailman/listinfo/fpc-devel
