How to preview generated SQL ?

In this forum we will discuss things relating the ZEOSLib 6.6.x stable versions

Moderators: gto, EgonHugeist

Post Reply
Uvigii
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2009, 16:41

How to preview generated SQL ?

Post 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.
trupka
Expert Boarder
Expert Boarder
Posts: 140
Joined: 26.08.2007, 22:10

Post by trupka »

Did you try TZSQLMonitor component?
Uvigii
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2009, 16:41

Post 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.
seawolf
Zeos Dev Team *
Zeos Dev Team *
Posts: 385
Joined: 04.06.2008, 19:50
Contact:

Post 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
Uvigii
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2009, 16:41

Post 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.
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post by mdaems »

Hi,

Let me disappoint both of you :twisted:

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
Image
Uvigii
Fresh Boarder
Fresh Boarder
Posts: 7
Joined: 08.05.2009, 16:41

Post 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 .
User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Post 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
Image
Post Reply