Usando o Zeos – ZQuery - Gladiston Santana

The component called TZQuery present in the Zeos palette is basically for executing queries that return values for a TDataset or ResultSet as you prefer to call it. Hence components that contain the Datasource and Dataset property such as DBGrid, DBEdit, DBNavigator,… feed on it.

Uma capacidade muto interessante do TZQuery é que o mesmo é capaz de fazer o streamming ou paginação de dados conforme o uso, isto é importante porque ao trazer uma quantidade muito grande de dados para estação cliente, os recursos do cliente podem ser exauridos e talvez parar de responder. Mas usando o recurso de paginação, o cliente requisita os registros que precisará ver sob demanda. Imagine um DBGrid e imagine uma pesquisa contendo mil registros, porém o DBGrid mostra apenas 50 registros por vez, isto significará que a mesmo que você precisará ir para o registro #51 para a query vá até o servido e pegue mais 50 registros. Claro que essa habilidade é configurável através de métodos como FetchRow ou FetchALL.

Há um componente muito similar chamado ZReadOnlyQuery, a única diferença é que não tem como usá-la para modificar os dados. Nem mesmo suporte a TUpdateSQL ela possui. Por não ter como usá-la para modificar o banco, pense nela como um componente mais leve para ser usaodo em consultas, relatórios, exportação de dados e afins.

Vamos a uma explicação sobre cada propriedade do componente ZQuery:

active

Type “true” or “false”. When it goes from false to true, the SQL sentence that is in the “SQL” property is executed and if everything goes well then Active becomes “true”, if it fails then it remains in “false”. Something suggested before going from false to true is to test if it is not active and if it is then close it, in addition to freeing memory, the other properties will not be influenced by changes with the dataset open, how do you do that? simple:

if ZQuery1.Active then ZQuery1.Close;

AutoCalcFields

Type “true” or “false”. When true, it indicates that when the Dataset is opened, TFields defined as “calculated” will be processed in the OnCalcFields event. The calculation also occurs when the focus of the dataset changes to the “calculated” field. If false, such TFields even if marked as “calculated” will not be processed:

Calculated fields exist only in the application and are calculated in the OnCalcFields event

CachedUpdates

Type “true” or “false”. The data will be stored locally by the program and will not be sent to the database until the method applyUpdates be invoked together with the CommitUpdates to confirm operations or CancelUpdates to undo them.

connection

ZConnection type. Every query must be assigned to a connection, in the case of the Zeos component, a connection is also a TIL (Transaction Isolation Layer). As of Zeos8 there is a component ZTransaction which corrects this difficulty and we can have a query assigned to a particular connection and TIL type.

DataSource

Type TDatasource. Similar to the LinkedFields, MasterFields and MasterSource properties for forms that display Master/Detail data, but with one difference, it dynamically creates the detail query while browsing the Master datasource (only Datasource in this context). For example:

select * from tdetalhe where field1_tdetalhe =:field2_tmaster

It is more advantageous than using LinkedFields, MasterFields and MasterSource because these only work when the data has already been brought in locally and the dataset is active, while using the Datasource property, a new query will be generated every time the cursor position in the datasource( master) change.

FetchRow

Integer Type. Determines the amount of records that will be brought to the client side at a time, this is basically an automatic data pagination. For example, if you are informed 50 so only 50 records will be brought to the client side at a time. This minimizes the impact that there would be if you brought in all the records at once. if use zero, the system will bring up all the records and there will be no paging and it will be a slower and more resource consuming query. If the value is -1, the system will arbitrate an amount it deems sufficient according to the available area to display them.

When data paging is involved we can use the FetchALL method to fetch all records to the client station. Some use FetchALL to be able to use the RecordCount property that returns the number of records, but this is not a good idea because all records will be read, a very heavy task to just know how many records there are. The ideal is a SELECT COUNT(*) over the adjacent query to obtain the number of records, imagine the query:

Select * from clientes where razao_social like  "a%"

How to know how many records the above query returned without having to use a FetchALL? We use a derivative query, see:

Select count(*) from (
  Select * from clientes where razao_social  like "a%"
)

So it will be perfectly possible to create a generic function and reuse it over any query. And if you were worried that some field will be read because of the mention of columns, the database manager has enough intelligence to understand that our action is to know the amount of record and not the data itself.

Filter and Filtered

Filter is of type String, while Filtered is "true" or "false". Filter is basically an expression that will determine the results that will be visible inside a TDataset when Filtered=true. If you are paging data, all data will be retrieved for the filter application. Examples of filters are “name like '*santos*'” or “cancelled=true”.

Important: If you are using data paging, when using Filter and Filtered, all records will be brought to the client side, so if you can use WHERE in your select it will be more performant. Use Filters when all the records you need are already on the client side, this is the case when we populate a temporary table to be displayed on screen and then processed.

IndexFieldNames

Names of the fields to keep sorting by. Names can be separated by semicolons, for example:

name; education

It is also possible to use the suffixes “asc” or “desc” so that the ordering is ascending or descending:

name desc

It is good to use this property with a table-bound index, otherwise the data will have to be sorted at runtime and this will significantly decrease performance. A practical example of using it is in the onTilteClick event of a DBGrid, see the example:

procedure TfmPrincipal.DBgrid1TitleClick(Column: TColumn); begin if SameText(zquery1.IndexFieldNames,Column.FieldName+' DESC') then zquery1.IndexFieldNames:=Column.FieldName+' ASC' else zquery1.IndexFieldNames:=Column.FieldName+' DESC'; end;

Sometimes it is also necessary to indicate that the column title is clickable for sorting, one way to do this is to change the cursor's appearance when the mouse pointer passes over them, this can be achieved using the DBGrid's OnMouseMove event, see the example:

procedure TfmPrincipal.DBGrid1MouseMove(Sender: TObject; Shift: TShiftState; X, Y: Integer); var Col, Row : Integer; InTitle:Boolean; begin Col := DBGrid1.MouseCoord(X, Y).X; Row := DBGrid1.MouseCoord(X, Y).Y; InTitle:=(Row=0); if InTitle then DBGrid1.Cursor:=crHandPoint else DBGrid1.Cursor:=crDefault; end;

Important: If using data paging, when using IndexFieldNames, all records will be brought to the client side.

LinkedFields, MasterFields and MasterSource

They are usually used together to display Master<->Detail type data. For example, the customers table and orders table, you indicate in orders that MasterSource will be the customers table, MasterFields will be CLIENT_ID which links the two tables and LinkedFields is automatically adjusted to say that CLIENT_ID from one table refers to CLIENT_ID from another table . We usually use the same field names in both, but if the modeling is different, there is no problem as long as the field names are pointed out correctly in LinkedFields.

Options

It is a set of possibilities, being:

doAlignMaxRequiredWideStringFieldSize: alignment will use the maximum size of the field definition as a reference
doAlwaysDetailResync: Some banks have number of virtual lines, and sometimes programmers use these lines as references in master/detail views. Here we have the possibility to change the rowno in the master table and fully reload the detail tables.
doCachedLobs: Some drivers, like Firebird, support loading LOBs (BLOBs and CLOBs) on demand. These drivers generally only load LOBs from the server when they are read and close them if they are no longer needed. The doCachedLobs configuration will cache these BLOBs and CLOBs on the client side. Drivers that do not support delayed loading of LOBs will ignore this option and always cache LOBs on the client side.
doCalcDefaults: Must be enabled by default. Zeos will attempt to load and manipulate the default values that your table definition contains for the fields. If off, the absence of a value can throw an exception.
doDontSortOnPost: Do not change the order of displayed lines when “Post”.
doNoAlignDisplayWidth: Do not align by field title width(DisplayWidth)
doOemTranslate: To use or not to convert to OEM.
doPreferPrepared: Self-prepare or not the queries. If the queries have parameters, but the preparation has been forgotten, from the second run it does the preparation automatically.
doSmartOpen: Normally TZQuery and TZReadOnlyQuery will throw an exception if you try to use the Open method in statements that don't return data. This can happen when using INSERT, UPDATE and DELETE. With this option set, the dataset will not throw an exception, but will open an empty dataset with zero rows and zero columns.
doUpdateMasterFirst: This means that all detail tables are registered in the master and vice versa. I determine which is the master and this table is updated first. Then all the detail tables will be updated. This is very useful if you have restrictions.

ParamChar

Parameters are those occurrences of dynamic variables usually preceded by “:”, example:

select * from clients where id_cliente=:p_id_cliente

well, :p_id_client is a parameter and the “:” é o ParamChar. Um problema a ser evitado é achar que parâmetros são como macro-substituições, uma especie de Search/Replace dentro de queries, pois não são. Parâmetros são formas de identificar para o servidor SGBD a possibilidade de repetição de uma mesma sentença SQL, daí ele passará a se lembrar do calculo de menor custo para a operação e não precisará recalculá-lo toda vez. Um ponto importante é que uma vez “preparada” qualquer query de mesma assinatura se beneficiará do calculo previamente realizado para qualquer usuário que estiver conectado e não apenas quem fez a primeira preparação, deveras, mesmo que quem preparou da primeira vez tenha sua conexão terminada a query ainda será mantida pelo tempo que o servidor considerar necessário.

A propriedade ParamChar existe porque nem sempre é desejável que parâmetros sejam identificados por “:”, por que não? Porque podem haver sentenças onde “:” deve ser tratado de outro jeito, por exemplo, no FirebirdSQL dependendo de como você cria um psql “:etiqueta” pode ser um parâmetro ou uma referencia a uma variável local e daí temos uma ambiguidade, ex:

declare variable id_cliente int;
begin
  (...)
  for select id_cliente from clientes where id_cliente=1001 into :id_cliente;

No exemplo acima, id_cliente é um campo da tabela, contudo :id_cliente é excepcionalmente uma variável local de mesmo nome. Situações assim acontecem em banco de dados que tenham suporte a psql e note que não são parâmetros, mas o Zeos não saberá como distinguir, a menos que o psql seja escrito de uma maneira diferente ou que deixe de usar “:” como indicador de parâmetro para usar outro caractere diferente.

No FirebirdSQL, em algumas situações como a descrita acima é necessário trocar ParamChar, ex:

execute block(p_id_cliente int=~p_id_cliente, p_cancelar boolean=~p_cancelar) returns (error_msg varchar(255));
begin
  error_msg='';
  if (:p_id_cliente&lt;=0) then
     error_msg='Codigo de cliente invalido';
  (...) 
  suspend;
end

Note que a assinatura (onde estão os parâmetros de entrada e saída) contêm ~p_id_cliente e ~p_cancelar, por que fiz assim? Para que pudêssemos indicar “~” como ParamChar e o Zeos entender que eles são os parâmetros e o :p_id_cliente mais abaixo fosse interpretado como parte do psql e o Zeos não toque nele.

IMPORTANT: Evite o “?”(ponto de interrogação) como ParamChar, pois ele é reservado para outra coisa e não funcionaria adequadamente.

ParamCheck

Tipo “true” ou “false”. Se “true” a lista de parâmetros é esvaziada e os parametros são populados com base na SQL informada no momento em que o método ParamByName ou Params é invocado. Se o valor for “false” a lista não será conferida com o SQL e portanto a lista de parâmetros estará sempre intacta, mas você terá de fornecer os parâmetros(propriedade Params) manualmente.

params

Tipo Lista. Em associação com as propriedades ParamCheck e SQL, mantêm uma lista de parametros quie poderão ser usados. Existem os métodos .ParamByName(‘p_nome’) e Params[n] que permitem acessar o parâmetro e seu valor. Ex:

ShowMessage('Quantidade de parametros: '+IntToStr(zqupdate.Params.Count)); 
zquery1.parambyname('p_id_cliente').AsInteger:=col_ID_CLIENTE;
zquery1.parambyname('p_NOME_ALTERNATIVO').AsString:=col_NOME_ALTERNATIVO;
zquery1.parambyname('p_END_CIDADE').AsString:=col_END_CIDADE;
zquery1.parambyname('p_END_UF').AsString:=col_END_UF;
zquery1.parambyname('p_STATUS').AsString:=col_STATUS;
zquery1.Params[0].AsInteger:=col_ID_CLIENTE;
zquery1.Params[1].AsString:=col_NOME_ALTERNATIVO;
zquery1.Params[2].AsString:=col_END_CIDADE;
zquery1.Params[3].AsString:=col_END_UF;
zquery1.Params[4].AsString:=col_STATUS;

IMPORTANTE: Acessar ou mudar parâmetros pelo seu índice é bem mais rápido, mas só faça isso se estiver numa operação de lote muito demorada porque acessar seus valores usando o nome do parâmetro tem a vantagem de evitar erros como mudar a ordem dos parâmetros.

Properties

A list of parameters to establish behavior with the database, using properties here means that the behavior I want is only for the query and not for the entire connection. For example, you can use properties to timeout your query. For example, in firebird we can:

SET STATEMENT TIMEOUT 30 SECONDS; UPDATE CLIENTS SET STATUS='C' WHERE (...)

However, the SET command could be suppressed if I added as a parameter:

isc_req_stmt_timeout=30

Why have a property like this when we can do it in the same sentence? Because some databases do not allow more than one statement in the same statement, so you would have to execute the set command in one execution and its SQL statement in the next one. So depending on the database you are going to use with Zeos you can save statement with Properties.

ReadOnly

Tipo “true” ou “false”. Quando “true”, os dados poderão ser lidos, mas nunca alterados. Vale ressaltar que se uma query será do inicio ao fim sempre read only then it would be better to use the component TZQueryReadOnly onde apesar do mesmo objetivo, nunca poderá mudar seu estado para realizar alterações ou ser usado em conjunto com TZUpdateSQL.

Sequence and SequenceField

It is used to get a numeric sequence from the SEQUENCES type object in the database. Until Firebird 2.5 this data type was used to obtain sequences that were usually used as auto-increment within tables. As of version 3.0, FirebirdSQL gained the “autoincrement” type for tables and no longer needs to use SEQUENCES for this purpose, but you can still use a SEQUENCES for other purposes.

ShowRecordTypes

It allows displaying a virtual column that indicates the type of operation performed with the record before CommitUpdates, namely:

usUnmodified: Unmodified
usModified: Modified
usInserted: Inserted
usDeleted: Deleted

Firedac in Delphi has something similar when we are using Cached Updates, we use it to understand in a dataset, what will be deleted, modified or inserted before CommitUpdates. There is no extensive documentation on how to display these operations in Zeos, but it is assumed something similar to Firedac with cached updates, for example, if “usDeleted” is turned on in this set and CachedUpdates is active then it will be possible to see all deleted records.

SortedFields

List of fields for sorting, often used in conjunction with the property SortType. It is often more practical to use SortedFields and SortType than IndexFieldNames in programming, see the example below where when clicking on the title of a column in the DBGrid there will be an ordering by the field, either ascending or descending if you click it a second time:

procedure TFormUsuarios.gridUsuarioTitleClick(Column: TColumn);
begin
  if tUsuario.SortType &lt;&gt; stAscending Then
  begin
    tUsuario.SortedFields:= Column.FieldName;
    DataModuleMain.tUsuario.SortType := stDescending;
  end
  else
  begin
    tUsuario.SortedFields:= Column.FieldName;
    tUsuario.SortType := stAscending;
  end;   
end;

SQL

Tipo String. É a sentença SQL propriamente dita. Use o ParamChar “:” quando desejar usar parâmetros ou referenciar campos noutro resultset especificado em MasterSource.

UpdateMode

Values Set. With CachedUpdates (or RequestLive with ZTable) active you can change the local values and they will be sent to the server after ApplyUpdates. For this to work, the system will dynamically generate queries to apply these updates and it basically has two methods where you can find the original records that need to be updated:

umUpdateAll: Uses all fields, modified or not. As you can see it is anti-performatic.
umUpdateChanged(default): Updates only the fields that have been modified, in this case you need to have a primary key(PK) for it to know how to properly generate the SQL where clause. This method is also important in case two or more people change the same record on the network, but each one of them will generate an update only of the fields that each one modified, keeping a mix of updates, not overlapping unaltered values.

UpdateObject

References a TZUpdateSQL. With a TZUpdateSQL in your system you can build your INSERT/UPDATE/DELETE so that a visual change of a record can be applied the way you want. It's much more practical than writing INSERT/UPDATE/DELETE on your fingernail.

WhereMode

Values Set. When these INSERT/UPDATE/DELETE queries are created dynamically, it will be necessary to correctly locate the original records that will be acted upon.

wmWhereAll: All fields will be referenced in the SQL WHERE clause to find the original value. Anti-performatic. This option only makes sense in non-standardized or “messy” data.
wmWhereKeyOnly: Uses only the primary-key as a reference.

Conclusion

The TZQuery component is the most used component within a program. While all the explanations mentioned here are for Zeos, many other suites have very similar properties. So, if you master them in Zeos, you can take this knowledge to other data access frameworks as well.