[Lazarus] Get SQL before TSQLQuery execute

Michael Van Canneyt michael at freepascal.org
Fri Jun 26 11:24:14 CEST 2015



On Wed, 24 Jun 2015, aradeonas wrote:

> I tried to add log in the mysqlconn.inc but after clean buld project and
> all files still my new code wont run.
> How can I get the value of FStatement for now?

As far as  I can see, the actual sent statement is logged. 
I have added an example program to show this, to fcl-db/examples:

home: >examples/logsqldemo -c postgresql -d mydb -u NNN -p XXX -s 'SELECT * FROM users where id=:id' -P id=i:12
[detPrepare] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detPrepare] : prepare prepst0 (int) as SELECT * FROM users where id=$1
[detActualSQL] : prepare prepst0 (int) as SELECT * FROM users where id=$1
[detPrepare] : TQuerySQLStatement : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detPrepare] : prepare prepst1  as select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detActualSQL] : prepare prepst1  as select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detExecute] : TQuerySQLStatement : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detFetch] : select ic.relname as indexname,  tc.relname as tablename, ia.attname, i.indisprimary, i.indisunique from pg_attribute ta, pg_attribute ia, pg_class tc, pg_class ic, pg_index i where (i.indrelid = tc.oid) and (ta.attrelid = tc.oid) and (ia.attrelid = i.indexrelid) and (ic.oid = i.indexrelid) and (ta.attnum = i.indkey[ia.attnum-1]) and (tc.relname = lower('users')) order by ic.relname;
[detExecute] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detParamValue] : Parameter "id" value : "12"
[detFetch] : SELECT * FROM users where id=:id
[detRollBack] : Rolling back transaction

As you can see, there are also 2 new log types: detActualSQL and detParamValue. 
For PostGres, the detActualSQL and detPrepare are the same.

For MySQL also:
home: >examples/logsqldemo -c "mysql 5.5" -d mydb -u NNN -p XXX -s 'SELECT * FROM users where id=:id' -P id=i:12
[detPrepare] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detPrepare] : TQuerySQLStatement : show index from users
[detPrepare] : TQuerySQLStatement : show index from users
[detExecute] : TQuerySQLStatement : show index from users
[detExecute] : show index from users
[detActualSQL] : show index from users
[detPrepare] : TQuerySQLStatement : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detFetch] : show index from users
[detPrepare] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detExecute] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detParamValue] : Parameter "id" value : "12"
[detExecute] : SELECT * FROM users where id=12
[detActualSQL] : SELECT * FROM users where id=12
[detPrepare] : TQuerySQLStatement : SELECT * FROM users where id=:id
[detFetch] : SELECT * FROM users where id=:id
[detRollBack] : Rolling back transaction

As you can see, the 
where id=:id 
was replaced by 
where id=12.

And this is true for detExecute and detActualSQL.

For Firebird, the detExecute and detActualSQL are different:

home: >examples/logsqldemo -c firebird -d localhost:/home/firebird/timetrack.fb -u NNN -p XXX -s 'SELECT * FROM PROJECT WHERE PJ_ID=:ID' -P ID=s:632F3D2F-055A-4DD9-852B-4050BF6A2E
[detPrepare] : TQuerySQLStatement : SELECT * FROM PROJECT WHERE PJ_ID=:ID
[detActualSQL] : SELECT * FROM PROJECT WHERE PJ_ID=?
[detPrepare] : TQuerySQLStatement : select ind.rdb$index_name, ind.rdb$relation_name, ind.rdb$unique_flag, ind_seg.rdb$field_name, rel_con.rdb$constraint_type, ind.rdb$index_type from rdb$index_segments ind_seg, rdb$indices ind left outer join rdb$relation_constraints rel_con on rel_con.rdb$index_name = ind.rdb$index_name where (ind_seg.rdb$index_name = ind.rdb$index_name) and (ind.rdb$relation_name='PROJECT') order by ind.rdb$index_name;
[detExecute] : TQuerySQLStatement : select ind.rdb$index_name, ind.rdb$relation_name, ind.rdb$unique_flag, ind_seg.rdb$field_name, rel_con.rdb$constraint_type, ind.rdb$index_type from rdb$index_segments ind_seg, rdb$indices ind left outer join rdb$relation_constraints rel_con on rel_con.rdb$index_name = ind.rdb$index_name where (ind_seg.rdb$index_name = ind.rdb$index_name) and (ind.rdb$relation_name='PROJECT') order by ind.rdb$index_name;
[detFetch] : select ind.rdb$index_name, ind.rdb$relation_name, ind.rdb$unique_flag, ind_seg.rdb$field_name, rel_con.rdb$constraint_type, ind.rdb$index_type from rdb$index_segments ind_seg, rdb$indices ind left outer join rdb$relation_constraints rel_con on rel_con.rdb$index_name = ind.rdb$index_name where (ind_seg.rdb$index_name = ind.rdb$index_name) and (ind.rdb$relation_name='PROJECT') order by ind.rdb$index_name;
[detFetch] : select ind.rdb$index_name, ind.rdb$relation_name, ind.rdb$unique_flag, ind_seg.rdb$field_name, rel_con.rdb$constraint_type, ind.rdb$index_type from rdb$index_segments ind_seg, rdb$indices ind left outer join rdb$relation_constraints rel_con on rel_con.rdb$index_name = ind.rdb$index_name where (ind_seg.rdb$index_name = ind.rdb$index_name) and (ind.rdb$relation_name='PROJECT') order by ind.rdb$index_name;
[detExecute] : TQuerySQLStatement : SELECT * FROM PROJECT WHERE PJ_ID=:ID
[detParamValue] : Parameter "ID" value : "632F3D2F-055A-4DD9-852B-4050BF6A2E"
[detFetch] : SELECT * FROM PROJECT WHERE PJ_ID=:ID
[detRollBack] : Rolling back transaction

It may be that something changed in trunk so you see different things for MySQL when using 2.6.4, 
but I didn't see any sign of such a change.

Michael.




More information about the Lazarus mailing list