Hi,

I have TSQLQuery that have SQL with parameters and I will set them and execute it but get an error because of a mistake in SQL but I cant find out what so I want to check result SQL after setting parameters but before execute so can check it and fin problem but how can I fin out what query will be execute?I couldn't find any event or checking call stack don't reach me to result.

Regards, Ara

Enable query logging and register a global log hook.

- in the connection, set the OnLog handler.
- set LogEvents to LogAllEvents
- the detExecute events should send you the query as sent to the server ?

Michael.

I checked that but SQL is parameter and not with values for example like this:
INSERT INTO table1 values(:id,:name);
but I set parameter values and I looking for something like this: INSERT INTO table1 values(1,'Book');

Regards,
Ara

aradeonas wrote

I couldn't find any event or checking call stack don't reach me to result.

You don't mention the database system you use. eg: if you use MS SQL Server, Oracle, Firebird, PostgreSQL you can run trace or profilers on the server end and see exactly what is passed to the server.

Regards,
- Graeme -

MySQL and ant its interseting to know what query, application will pass to server.

Regards,
Ara

aradeonas wrote

MySQL and ant its interseting to know what query, application will pass to server.

Indeed. I've been using Firebird for years, but only recently did they add decent tracing support for the server. tiOPF (what I use for all database access and more) already had a very good logging framework, so years ago I added an option to log all generated SQL. It was very useful to have, but I guess you are not using tiOPF. ;-)

Regards,
- Graeme -

I will read about tiOPF (Thank you) but I want to ask is TSQLQuery fill parameters in SQL and then send it to MySQL or not?And if yes where so I cal log it after fill?

Regards,
Ara

aradeonas wrote

I will read about tiOPF (Thank you) but I want to ask is TSQLQuery fill parameters in SQL and then send it to MySQL or not?

Yes.

And if yes where so I cal log it after fill?

Currently, I think it is not accessible for you, I need to create an extra log type.

Michael.

aradeonas wrote

I will read about tiOPF (Thank you)

tiOPF makes database development very easy, and a big plus is that you will not be tied to one specific database backend, and you can simply recompile to enable 3-tier support too (without application changes).

but I want to ask is TSQLQuery fill parameters in SQL and then send it to MySQL or not?

Yes of course, otherwise your queries wouldn't work. ;-) In tiOPF I first log the SQL statement, then output the query parameter list/values.

Regards,
- Graeme -

Currently, I think it is not accessible for you, I need to create an extra log type.

Thanks,Can you point me to the address for now?

Regards, Ara

aradeonas wrote

Can you point me to the address for now?

Website:
http://tiopf.sourceforge.net

API Class documentation:
http://geldenhuys.co.uk/tiopf/

Support Newsgroups:
Server: geldenhuys.co.uk
Port: 119

Minimalist web interface: http://geldenhuys.co.uk/webnews/

Source code:
$> git clone git://git.code.sf.net/p/tiopf/code tiopf $> cd tiopf
$> git checkout tiopf2

Regards,
- Graeme -

Thank you VERY MUCH Graeme for your helps but Im not that lazy :D That sentence was for Michael to show me where TSQLQuery fill parameters.

Regards,
Ara

aradeonas wrote

That sentence was for Michael

Interestingly, Thunderbird filed your message as a reply to one of mine.

Regards,
- Graeme -

Sure its interesting because its two days Im thinking check Thunderbird :)

Regards,
Ara

aradeonas wrote

Thank you VERY MUCH Graeme for your helps but Im not that lazy :D That sentence was for Michael to show me where TSQLQuery fill parameters.

TSQLQuery does not do this, the TSQLConnection does this. For mysql this is mysqlconn.inc, line 592.

And the detExecute log DOES show the SQL with parameters replaced (as I imagined).

Michael.

And the detExecute log DOES show the SQL with parameters replaced (as I imagined).

What version?I used last stable 1.4 release and test this code :

ShowMessage just show :

TQuerySQLStatement : INSERT INTO test(id,name) VALUE(:id,:name);

Am I doing something wrong? :/

Ara

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?

Ara

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.

Thank you very much for example.

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.

Yes,because in my version I have these errors:

Regards, Ara

Yes, it will only work with trunk, but you should easily be able to adapt it to 2.6.4.

Michael.

OK.I will get it.

Thank you.
Ara

OK.I will get it.

Thank you.
Ara

Is there a way to update my current Lazarus to last trunc and get just changed?

Regards,
Ara