ZeosLib - working like in PHP

[M[a]nny]

Hello,

if you've ever worked with MySQL in PHP, it was something like this:

Code: [Select]

$query := mysql_query('SELECT * FROM table');
I mean, you could make infinity times this command (mysql_query). The question is how can i do that here, in Lazarus with ZeosLib? I placed to the form Connection1, Query1 and successfuly connected to mysql. But while Query1 component is working (processing sql commands), it can't be used.
Is there any way to do it? Thank you for responses.

[JD]

Do this

Code: [Select]

with Query1.SQL do
begin
  Clear;
  Add('SELECT * FROM Table');
  Open;
end

That's all.

Make sure that the connection established using ZConnection is active or else you'll get errors.

[mdaems]

M[a]nny,

But while Query1 component is working (processing sql commands), it can't be used.

is quite unclear to me. Seems like you are having a grid or some other data aware components attached to it? In that case you shouldn't reuse it indeed to do other queries as the data aware components still need the original object.
For php like queries, you do't need a TZQuery component added at design time, however. You can just create some TZQuery at runtime and use it for your database queries.

Code: [Select]

var
  mytzquery : tzQUERY;
begin
  mytzquery := TZQuery.create;
  with mytzquery do
    begin
      connection := Connection1;
      SQL.text := 'select a from b';
      Open;
      while not EOF do
        begin
          print(fieldbyname('a').asString);
          next;
        end;
       Close;
    end;
  mytzquery.free;
end;Before the Free method call you can add as much sql commands as you ever want. When you just want to do DML statements (so no resultset is to be expected) use following statements instead of open/close:

Code: [Select]

      SQL.text := 'update b set a = 1 where a = 0';
      ExecSQL;

If you want it even shorter (but not necessarily more clear):

Code: [Select]

begin
  with TZQuery.create do
    begin
      Connection := Connection1;
      SQL.text := 'select a from b';
      Open;
      while not EOF do
        begin
          print(fieldbyname('a').asString);
          next;
        end;
       Close;
      SQL.text := 'update b set a = 1 where a = 0';
      ExecSQL;
    end;
end;
Mark

[M[a]nny]

Thanks for responses. I think i didn't write it well. Imagine this situation:

Code: Pascal  [Select][+][-]

1. procedure TFrmMain.Button1Click(Sender: TObject);
2. var
3.   i,j: integer;
4. begin
5.   for i:= 0 to 100 do begin
6.     Query1.SQL.Text := 'SELECT * FROM table1';
7.     Query1.Open;
8.     Memo1.Clear;
9.     while not Query1.EOF do begin
10.       Memo1.Lines.Add(Query1.FieldByName('SomeCollumn').AsString);
11.       Query2.SQL.Text := 'SELECT * FROM table2';
12.       Query2.Open;
13.       while not Query2.EOF do begin
14.         Memo1.Lines.Add(Query2.FieldByName('AnotherCollumn').AsString);
15.       end;
16.       Query2.Close;
17.     end;
18.     Query1.Close;
19.   end;
20. end;  

As you can see, I have to use 2 Querys to make it right. Is there any way to do it with some function that will work as one Query? I mean, that function will decide which Query in dynamic array is free, so that function will take it and give it as result. Does anybody know how to do that?

[fredycc]

Hi M[a]nny;  What you need to do?; for me is some confuse the example  , maybe a nested query resolved the problem, maybe you can specified params; more easy is create a stored procedure that do the work; if you need execute many sql sentence there is a component for zeos.

Regards.

[mdaems]

Sorry, but I don't think that's possible in php either.
Only there you need to have 2 resultset array's and one query object. The TZQuery object acts like a php query + resultset in one component. This gives a lot of extra possibilities, like having the resultset updatable with automatic posting of the changes back to the database. This is possible while the resultset remains linked to the original query.

When you're processing the results in code as in your example you could also solve the problem by joining the 2 queries into a single sql query. That's certainly more efficient.

Mark

(BTW you forgot the Query1.Next calls in you demo program)

[M[a]nny]

Amigos, all I want to do is execute more than one sql query at the same time. My question is -> "Am I able to use only one Query in the whole project?"
ZSequence1 - what is it and how can i use it? I think, maybe it is the thing i'm looking for.

[fredycc]

Hi Manny, I use ZSQLProcessor for execute scripts; for example inserts batch file, updates batch file.

[JD]

Amigos, all I want to do is execute more than one sql query at the same time. My question is -> "Am I able to use only one Query in the whole project?"
ZSequence1 - what is it and how can i use it? I think, maybe it is the thing i'm looking for.

You can use ZUpdateSQL which has a DeleteSQL, InsertSQL & ModifySQL property to handle multiple SQL queries.

ZSequence is used with database generators & triggers to create unique, auto increment values in databases.

[mdaems]

Amigos, all I want to do is execute more than one sql query at the same time. My question is -> "Am I able to use only one Query in the whole project?"
ZSequence1 - what is it and how can i use it? I think, maybe it is the thing i'm looking for.

You can use as many queries as you like with 1 connection. Or you can spread multiple queries over multiple connections, just as you like.

ZSequence is an interface to sequences/generators in the database. These can be used to automatically populate fields in a dataset when inserting records. Just link the sequence to the right field and when posting the field will get the next sequence value the database generates. There's a big condition : the database has to support it. As mysql doesn't support sequences ZSequence can't be used with mysql. However autoincrement is supported, so when inserting a row in a dataset with an autoinc column, the value will be automatically inserted and the inserted value will be available in the field after posting.

Mark

[michaelthuma]

Manny --> It is not clear what you want.

I guess it is one of the following options ...

a) You have a query you loop and a seconde where you look up a certain row or some rows in a detail table.

1) More for Delphi Clicky Clicky esthetics (or GUI 1:n relationshop ... master record ... detail in grids)
This can work automatically in case of an 1:n realtionship ... by assigning the datasource property of the TQuery (I do not mean here the Dataset property of the Datasource).

You can and should greate a query at runtime


in case of lacal use in a procdure.
var

lDS  : TSQLQuery...
begin
 try
   lDS:=TSQLQuera.Create;
 finally
  TSqlQuery.Free; // Don't free if you us a global one ... no need to tell I think ...
 end;
  
end;

2)
See a query like a cursor in a PL-SQL program.

The second option is keep both queries open - position at Query.First, Query.Next, Query.Prior, Query.Last. (Take care - going to last, in case of a non unidirectional query usually keeps them in memory. You can use Filer (little lmited but works).

3= Do you want to tranpose values?

b) You want to run them in parallel.
Put it into a seperate thread. I think this is what u want to do?

hth
Mike

[michaelthuma]

Manny: Do you want to have one or more queries per session? You want a Query pool?

Make a global array or a dynamic list of TSQLQuery  ...  myqueries : array of TZQuery, or something like this.

Ask the Query if it is executing or however you define a Query is available. In the procuedure you can hold a reference to the TSQLQuery you have choosen.

var
  lQry : TXXQuery;
begin
  lQry :=  myGlobalQryList[POS_CHOOSEN_BECAUSE_FREE] and work with it.
  lQry.SQL:='select mystuff1, mystuuf2 from mytab';
  lQry.Open;
  // Don't close or free in this case.
  // Same would work if you just take the index.
  //myGlobalQryList[POS_CHOOSEN_BECAUSE_FREE].SQL:='...';
  // myGlobalQryList[POS_CHOOSEN_BECAUSE_FREE].SQL:=Open;
;

I think something like this is what you seek. This code just for getting an idea.  The processing of a PHP script in our case is opening a Form. This is where this DB Query concept comes from. Also check if putting queries on a datamodule can help you. A data module can be created at runtime and with little phantasy one can see it as a submodel. Maybe this PDF documents will give you an idea about the underlying issue...

http://motaz.freevar.com/freespider.pdf

Mike

[M[a]nny]

Thanks for responses people, I really appreciate it.
michaelthuma: yes, that's exactly I'm looking for! To make some function which decides what Query is ready from dynamic query array to use.

If I understand well, component "TZUpdateSQL" is prepared for this kind of use => for multiple updating, deleting or inserting. The problem is SELECT... and it would be solved by dynamic array of Querys.

[michaelthuma]

Manny - sry - I cannot comment on ZEOS I did not read the source code.

Every TSQLQuery holds an ID for a structure that represents a DB cursor. One SQLQuery hold exactly one cursor. Stored procedures that give us back REF_CURSORS are little different. --> One Query holds a handle to a result set.

The Update SQL is something differnt. Imagine you have an application that shows data and you modify this data and you want to make it persisten you have to commit the transaction, the cursor is closed (Grid is empty:-)). The exception was the Interbase Server who was in the position to do a commit retaining. In the early days of the BDE (old borland database engine) this was requried to post updates to the database in a seperate transaction - so the data in the GUI Form remained visible.

This has changed with DB Express. DBExpress follows today a disconnected model. The SQLDB Components are built very similar. This model has the advantage that the DB Connection can disconnect in background from the DB and reconnect when required. This is where the ApplyUpdates comes from (originally from MiDAS which was a middleware that talked in a strange way with the serverside and also the Delphi Client Dataset I think still uses this strange protocol). This is really disconnected.

When you look at the processing of a PHP page you don't have to care about this, "PHP" does this all in the background for us. Not in a native application. Here we have to take care on our own. This behavoir was available under Delphi in middle tier (e.g. components for developers or other so called MIDAS replacements).

Maybe one here knows, how asynchrous (background) query processing works with ZEOS. Be little careful. Try to write whatever in just a simple sequence. If you really need to pool then you will have to cope with background (async) processing otherwise you the "executable" waits for the synchronous call to complete.

I am assuming you want to write maybe a CGI Module. Dont care about query pooling the cgi is a sepearate process. Only in case that you have a GUI app that has some long running queries then look.

a) Is it possible to run the query asynchronous (in background)
b) Is it simply possible to start it in a seperate thread

In case of usually you have callback that at least tells you if the query is processed and maybe how many records are fetched currently.

Indepent how ZEOS is going to solve such situations this is the general approach on the GUI.

I am currently testing with AnyDAC, but I think ZEOS can do it for all of us here in the meanwhile. It is good and flexible enough from what I have seen.

"We" - I do the testing under FPC - will not come out so soon for FPC, but Anydac can do all these asynchronous staff and array updates .... the next point to jump in is the next "official" Lazarus release. (at least a 0.9.29 + FPC > 2.4.0). I currently test on the snapshot. Here it works. I have just tested under Windows and cannot say anything - which is lot more of interest for me in combination with Extpascal under Linux. (a bug in FPC with Interfaces broke the library for a short period but in the last release that is shipped officially there was this problem. It is fixed now.)