Page 1 of 1
How to preview generated SQL ?
Posted: 21.05.2009, 11:07
by Uvigii
If I have the following insert statement:
Code: Select all
ZQuery.SQL.Text:='Insert into table1 Values(null,:field1)';
ZQuery.ParamByName('field1').Value := 'abcd';
I need to see 'parsed' SQL before Zquery.Open (or ZQuery.ExecSQL), and depending on what I see to suspend or allow execution.
Any suggestions ? Thank You.
Posted: 21.05.2009, 12:37
by trupka
Did you try TZSQLMonitor component?
Posted: 21.05.2009, 13:47
by Uvigii
Yes I Do. But SQLMonitor log queries if I understand it correctly.
I need a way to preview query , and depending on the parsed sql to cancel or execute it.
Thank You.
//off-topic
Let describe it a little more:
I have extremely tiny network bandwidth between client and server. My idea is to :
1. prepare (parse) sql.
2. depending on some criteria to put query in queue (A) or queue (B)
3. queue (A) is executed in user interactive mode while queue (B) is executed in different thread in the background.
On tcp/ip layer (A) and (B) goes to different routes. So different QOS is applied to (A-net) and (B-net) network streams. (A-net) has greater priority over (B-net) . I am trying to implement a simple dump class that will work as some kind of SQL-execution proxy.
Posted: 21.05.2009, 16:39
by seawolf
Prepare statement will be implemented in version 7, so what you ask is partially implemented on the lastest snapshot.
Download it and try if it fits your requestes
Posted: 22.05.2009, 09:44
by Uvigii
seawolf wrote:Prepare statement will be implemented in version 7, so what you ask is partially implemented on the lastest snapshot.
Download it and try if it fits your requestes
Thank You
seawolf for Your reply!
I did install REV619. ZQuery.Prepare procedure seems to do nothing.
Can You please point me to a simple example of how to use those prep. statements. I think that I am missing something.
Thank You again.
Posted: 23.05.2009, 14:05
by mdaems
Hi,
Let me disappoint both of you
Preparing statements doesn't help at all in this situation. Yes, it's about sending a query to the server before the actual execution, but no, it doesn't result in extra information about the statement at zeoslib side. The only advantage of the prepared statement changes is that it will not be necessary to send the full statement to the server every time the statement is executed later on. Which also might result in less server side parsing time.
The only classes provided by zeoslib to do some parsing af SQL are in the parsesql directory. They provide ways to tokenize the query in arrays and there's logic provided to map result fields to effective table columns. Much depends on what you exactly want to look at when looking at the query. Just separating selects from updates can be done easily at client side, I believe.
Just in case you're using mysql : have a look at mysqlproxy. That's designed to do the job you're talking about.
Mark
Posted: 11.06.2009, 09:02
by Uvigii
mdaems wrote:Hi,
....
Just in case you're using mysql : have a look at mysqlproxy. That's designed to do the job you're talking about.
Mark
Thank You for guidance !
I do not think that mysqlproxy will fit my needs. To do what I need mysqlproxy should be running on each client machine, or on dedicated host on each network. Lets say there is about 30 different client networks with 1 or 2 nodes on site.
So if there is easy way to do the following task (in pseudo code), Please point me to it
:
Code: Select all
.......
// Main thread.
........
Zquery....AddParametrs;
Zquery.....Parse;
sTemp := Zquery......Preview;
do_some_delayed_inserts(sTemp,false);
.......
function do_some_delayed_inserts(sTemp:string,bFastConn:boolean)
.....
begin
...... create new thread and detach from main thread ......
...... in new thread ... :
if bFastConn then
Qry.Connection : = ... fast ....
else
Qry.Connection := ..... slow ....;
end;
For now I have one function 'do_some_delayed_inserts(tableField1,tableField2 ... tableFieldN, bFastConn)' for each table i want to update which is sub optimal. I prefer to pass only 2 parameters to function: Parsed query as string and fast/slow connection as boolean.
Cheers .
Posted: 16.06.2009, 13:03
by mdaems
You could try to post the asynchronous update sql to a custom server side program and proces the urgent calls using zeoslib on the client side?
No experience with client-server programs, however.
Mark