In this article, we will take a closer look at the ZConnection component, the main component of the “Zeos” palette. Yes, without it we would not be able to connect to a database. If you are not sure how to install it, see this article titled “ZeosLib“.
The ZConnection component allows your application to connect to a very large list of databases, but before we talk about it, let's clarify two types of data access, direct and indirect:
- indirect access: This is when database access depends on a third-party component, commonly called data access API, known examples are: ADO, ODBC and JODBC. These APIs talk to another API called the 'SQL Client' which on Windows refers to a DLL distributed to talk to the database.
- direct access: It is when the access to the database is direct to the database or through the API 'SQL Client', in Windows it refers to a DLL distributed to talk to the database.
Zeos has indirect access to virtually any ADO and ODBC compliant database (Zeos8+). In this case you must install in addition to the 'SQL Client' also the ADO drivers on your system.
Zeos separately has another component called ZODBC, it allows access to any database that has the 'SQL Client' and ODBC drivers installed.
But the biggest advantage is the direct access, because in this case only the 'SQL Client' is enough to access the database, generally making the access faster and easier to distribute with your application. However, there are advantages to using ADO/ODBC drivers in certain circumstances, for example when we want to pool connections. It is possible to pool connections, but then we would have to program.
Meet the ZConnection component, this one:
Let's explain its properties before demonstrating how we make the connection:
AutoCommit
True or False Value. To explain how it works, we need to understand what a transaction is, in this context, a transaction is a period in which we start a series of modifications in the database that must be closed with a commit, the confirmation that everything that was done from the beginning of the transaction must be kept or the rollback which is to undo the entire transaction. If AutoCommit Mode is on, it is not necessary to initiate a transaction or commit because the component will implicitly send the commit to each write operation that it detects, however there may be operations that Zeos does not correctly detect as a write operation, for example:
- select * from someprocedure;
- select * from someprocedure(:param1, :param2);
Zeos has no way of knowing if the stored procedure will or will not write data, this is just an example that Autocommit mode may fail when objects are executed inside the database that Zeos cannot see. So if the database supports autocommit internally it is better to enable it. Something very interesting about Autocommit Mode is that when starting transactions manually with the ZConnection.StartTransaction command, Autocommit Mode will be disabled momentarily until you commit or rollback.
In the following link, there is a nice explanation with diagrams and graphs about how this property works:
https://sourceforge.net/p/zeoslib/wiki/TZConnection.AutoCommit/
There's an explainer video that gives more details about autocommit and finding out if it's a villain or hero:
AutoEncodeStrings:
Value of type True or False. When AutoEncodeStrings is on (true), Zeos tries to translate its data between the connection character set and the ControlsCodePage setting. This can lead to data loss if no conversion is possible. AutoEncodeStrings, therefore, should only be set to true if a conversion is required. In Zeos 7.2 or higher this property is innocuous and no longer exists, if it appears on your system, ignore it, it only exists so as not to break compatibility with previous versions.
Observation: To avoid character page conversion headaches, whenever possible use Unicode as UTF-8. Developing using the Windows environment can be complex because it uses WIN1252(Ansi) for GUI applications while the terminal uses ISO 8859_1/cp850(known as Latin1) and this soup of encodings, especially in textual files makes hell if don't pay much attention to how you read or write files.
Catalog:
Value of type String. It is a property dedicated to Postgree database, it has no use for other databases as far as I know.
protocol:
Value of type String. This string will define the database communication protocol (or driver) that Zeos will use, if it is to communicate with FirebirdSQL then it is “firebird”. When defining the protocol, some properties such as ClientCodePage will become selectable, because only when we know what the protocol is we will be able to know which code pages will be available to the selected protocol. From the Zeos8+ version, ODBC and OLEDB protocols were added, being able to access any database that has drivers in these frameworks for them.
In the protocol name there is a prefix reserved word called pooled which should only be used when implementing a pool of connections, that is, keep connections open for ready-to-use. Usually required when serving connections from the WEB for the purpose of queries.
ClientCodepage:
Value of type String. This is the character set that our program will ask the database server to send to our program. Most servers can easily convert characters between the requestor and the server. In fully unicode IDEs like Lazarus, it's better to request unicode character data and let the host system handle the rest be it ISO8859_1(Latin1) or WIN1252(Ansi). At the ZConnection this property can also be configured in “Properties” through the “lc_ctype” or “Codepage” parameters:
ZConnection.Properties.Add ('lc_ctype=ISO8859_1'); // firebird
or
ZConnection.Properties.Add ('Codepage=ISO8859_1'); // others
Because you should pay attention to this, after all the database stores whatever it has to store no matter the encoding on the client side, right? It is true that the server will accept what the client side sends and there is no specific treatment for one or another page encoding, however, when the client side indicates which code page the text will be stored on then the bank will also know how to “translate” it later. So identifying the charset in the connection will serve as rosette stone translating the “code points” which is the representation of the character between the database and the client side.
very important note: Be very careful when loading scripts from files into the database, usually editors like vscode, notepad++ create files using UTF-8 encoding, but if your database is set to ISO8859_1(Latin1) or WIN1252(Ansi) you may have problems with some types of accents. As I said, it won't be the database's fault for not accepting them, it's the rosette stone being bugged thinking it's using a code page, but when loading an external file it's on another. When developing within Linux/BSD/Mac, UTF-8 reigns supreme in all applications, IDE, editors,…. so you hardly find problems of this type, but on Windows there is a salad of code pages that you need to be careful when creating or enjoying external data in a different encoding than your connection.
ControlsCodePage:
Value of type True or False. This lets you determine which character encoding you prefer in your TDatasets (TZQuery, TZReadOnlyQuery, and TZTable). There are 3 options, but only one really works:
- cCP_UTF8 : This is the default configuration for Lazarus. Strings passed are expected as UTF8 and TStringFields are passed. Since TStringField only provides one byte for multibyte characters, Zeos increases the size of TStringField four times. A string in VarChar format of 50 characters has a TSTringField.Size of 200 characters.
- cCP_UTF16 and cGET_ACP: are implemented because of Delphi and should not be used in Lazarus.
When using UTF-8 in the database, but your connection is Win1252 as code page then there is a very important note which refers to a bug up to Zeos 7.2 that deserves your attention.
Data base:
Value of type String. Refers to the location of the database, either in literal form like C:\Path\to\to\bank.fdb” or in connection string form like:
localhost/3050:C:\Path\to\to\bank.fdb (remote on own computer) intranet.local/3050:bank.fdb (remote on another computer) xnet://C:\Path\to\o\ bank.fdb (local)Or other ways recognized by ADO or 'SQL Client' of your database.
Some databases like FirebirdSQL, MSSQL, PostgreSQL,… a connection string can supply everything the 'SQL Client' needs to connect to the database, so properties like Host, Port, Protocol, ClientCodepage, UserName and password become unnecessary.
DesignConnection:
Value of type True or False. If the value is set to true(true), the connection will be active at design time, but will remain disconnected when you give a 'run' in the project or run the application standalone. This avoids active connections forgotten inside the executable that only run on localhost and when going to the production environment cause problems. Needless to say, this option is very useful.
hostname:
Value of type String. Hostname that will serve the database, it can be an IP, qualified network name or simply “localhost”, in all these cases we are claiming to use client/server access. It is important to say that “localhost” is not exactly a non-networked access as some claim, but that the host is the very computer we are using, in a nutshell you use the network infrastructure to locate yourself. When the access is direct to the physical file, we say that it is a local connection and in this situation it is not necessary to inform the hostname and either port.
LibraryLocation:
Value of type String. Indicates where the access library (SQL Client) will be, in the case of FirebirdSQL it is called fbclient.dll on Windows or libfbclient.so on Linux. Leaving this parameter blank implies that the location is automatic and this can be a problem for some databases that over time have changed the name of the library, for example, FirebirdSQL 1.x used as 'SQL Client' a file called gds32.dll, then turned fbclient.dll, but for backwards compatibility it still accepts gds32.dll and in this situation it is not always possible to guarantee that the “automatic” will recognize which DLL is right when both are on the system. That's why I recommend that you at least leave the correct name of the 'SQL Client' you want to use, for example, “fbclient.dll” and prevent the “automatic” from finding the wrong DLL first.
There is a very common problem when the host operating system is 64bit, as commonly Windows programs are still 32bit. Programmers confuse thinking that they must install the 'SQL Client' equal to the architecture of the operating system and this will go wrong because the 'SQL Client' must be of the same architecture of the programs that we will use to access the database. Microsoft has implemented a very efficient way to solve this problem on 64bit systems, as libraries are installed in C:\Windows\System32 and 32bit libraries in C:\Windows\SysWow64 and any program that installs DLLs will use the directory corresponding to the architecture his. However, the lack of knowledge of this information leads programmers to a lot of confusion, spreading DLLs unnecessarily and later confusing the resolution of the problem. So when we have a 64bit operating system, we install the SQL Client of our database twice, the 32bit 'SQL Client' and then the 64bit one. Some banks already know this and simplify this operation, when FirebirdSQL 3+ installs the 64bit 'SQL Client', it also installs the 32bit version.
It is worth noting that if you specify a path like “c:\app\bin\fbclient.dll” as your librarylocation then the system will not look for it in the system path, but only the one specified, we will call this 'plastering the DLL'. Many programmers take this attitude because they consider a simpler way, however this creates another problem: you will be responsible for updating the 'SQL Client' manually and getting bugs and security flaws fixed. If you are deploying a system in the cloud, you should never plaster the 'SQL Client' to one location. Linux environments update programs obtained from their repositories automatically and will update the 'SQL Client' as well.
LoginPrompt:
Value of type True or False. If it is “true”, it will ask for the username and password every time it connects, even though the UserName and Password parameters have been provided.
User:
Value of type String. The database username. For example, “SYSDBA”, “dbo”, “sa”,…
password:
Value of type String. Password to connect to the database, example: masterkey
Port:
Value of type Integer(0-65535). Specifies the connection port, in the Firebird example, it is usually 3050. If you leave “0” Zeos will use the default according to the protocol (driver) of the database.
TransactIsolationLevel (TIL):
Values: tiNone, tiReadCommitted, tiReadUncommitted, tiRepeatableRead, tiSerializable. Determines what the Transaction Isolation Level (TIL from now on) will be. Explanation for each of them:
- tiNone: As the name suggests, it means “none”, as “none” is an impossible choice for a relational database, this means you will use properties (parameters from here on) with a set of commands that will define the TIL. Leave tiNone and not have any kind of properties that form a TIL will cause a fatal error message because the characteristic ACID of a database only makes sense if there is a TIL defined.
- tiRepeatableRead: Corresponds to the TIL “REPEATABLE READ”. A transaction sees during its lifetime only the data that was committed before the transaction was started.
- tiReadCommitted: Corresponds to the TIL “READ COMMITTED”. A transaction only sees the data committed before the statement is executed. This is a subtle difference to the “repeatable read” .
- tiReadUnCommitted: Corresponds to the TIL “READ UNCOMMITTED”. A transaction sees changes made by uncommitted transactions. It is also known in other databases as TIL “SNAPSHOT”.
- tiSerializable: Corresponds to the TIL “SERIALIZABLE”. This is the strictest isolation level, which enforces transaction serialization, this means that every time you start the transaction and then give a commit or rollback. If you use a WHERE clause, the lock may prevent others from starting to update the same records. Data accessed in the context of a SERIALIZABLE transaction cannot be accessed by any other transaction except the “SNAPSHOT” TIL. It is also known in other systems as “concurrency control”.
The concern of every “newbie” is to prevent the infamous “dead lock”, when in reality, they are welcome depending on the scenario. The most experienced DBA's concern is "phantom" records, they occur when in the course of a transaction, new lines or records are added by another concurrent transaction. Imagine station #1 in a SELECT with a WHERE clause status='A' and while the select is running station #2 does some INSERTs where the STATUS is 'A' so without waiting for anything station #1 gets some extra records that may not be seen depending on the TIL and may or may not be outside of an UPDATE, hence the term “ghost records”. In all applications, but especially large ones, a command like UPDATE or MERGE raises the question: “If there are ghosts, what do I do with them, do I update or ignore?” and then select the proper TIL with “SERIALIZABLE” being the strictest and likely to solve the problem. Details about the TILs can be read in this wikipedia article.
For FirebirdSQL database users there are some considerations (ignore if using another database):
- TIL "READ COMMITTED": In FirebirdSQL matches the combination of parameters isc_tpb_read_committed, isc_tpb_rec_version and isc_tpb_nowait juntos. Uma transação vê apenas os dados confirmados antes que a instrução seja executada. O parâmetro “rec_version” para o Firebird é responsável pelo comportamento de que os valores mais recentes que foram “commitados” por outros usuários serão considerados. O parâmetro “nowait” é responsável pelo comportamento de que não há espera pela liberação de um registro bloqueado. Neste nível o servidor é mais sobrecarregado que no TIL “REPEATABLE READ”, porque tem que fazer todos os “refresh” para adquirir estes valores novamente. O FirebirdSQL é mais usado com esta configuração.
- TIL "READ UNCOMMITTED": The Zeos documentation says that this option does not exist in Firebird, however it is documented in the official site. But when trying to use this option, Zeos responds saying that this option is not supported. Regardless of which information is correct, reading information that has not yet been confirmed may not make sense, but there may be scenarios where this is necessary. This TIL can be simulated by ausência da property(parâmetro, daqui em diante) “isc_tpb_rec_version” leaving only the parameters: isc_tpb_concurrency and isc_tpb_nowait.
- TIL "REPEATABLE READ" It is known as “SNAPSHOT”. It is a combination of transaction parameters “concurrency" and "nowait”.
- TIL “SERIALIZABLE” It is known as “Snapshot table stability”. It is a combination of transaction parameters “concurrency" and "nowait”.
- While Zeos uses the TIL tiReadCommitted, tiReadUncommitted, tiRepeatableRead, tiSerializable which defines well what we want, Firebird is flexible in being able to combine several characteristics through parameters, if you understand well how they work: isc_tpb_concurrency, isc_tpb_nowait, isc_tpb_read, isc_tpb_write, isc_tpb_consistency, isc_tpb_read_committed, isc_tpb_rec_version you will have a custom TIL.
- A brief explanation with examples is provided by IbSurgeon in this article.
These TILs are a headache for inexperienced people when they complain that the system is slow, having dead lock or resulting in wrong values, so having a DBA (database specialist) helps guide programmers to use the most appropriate TIL for the given scenario. Take a look at these scenarios below and try on your own to decide which TIL best suits you:
Scene 1: Let's say you want any closing report, the report takes a long time and before it ends, during the process, some values change and at the end of the report you can see, for example, that the total for each month does not match the end of the year , this happened because processed values were changed while the report was still running, how would you solve it?
Scene #2: Let's say that your airline seat ticketing system has to solve the following problem, you don't want that when someone is deciding to buy a specific number that someone else is quicker or who lives in a geographic region with more abundant internet to go ahead and then confirm the purchase of the same seat as the first one that comes from a slower connection has not yet made the purchase. In this situation, which TIL would be more suitable? “SNAPSHOT TABLE STABILITY” or “SERIALIZABLE” and then SELECT FOR UPDATE WITH LOCK?
Using the same TIL for an entire application may be unreasonable, perhaps some features in parts of the program require a different TIL, otherwise it can cause logical problems that are difficult to understand. But if you understand TILs well, an application using TIL like “SERIALIZABLE” (known in FirebirdSQL as SNAPSHOT TABLE STABILITY) would avoid unwanted simultaneous edits, but if you don't know how to use it it will be a crash festival (technically waiting for records to be released) or deadlocks.
If you don't know how to take advantage of TILs, I recommend that you create applications that simulate the effects I exemplified before implementing a solution within the project, if you prefer, you can use my example project made by Lazarus/FPC called “lazdemo_transacoes” hosted on github:
https://github.com/gladiston/lazdemos_gsl
One difficulty with Zeos up to version 7.2 is that it is not possible to change TILs once connected, so create a new connection with the appropriate TIL and do what needs to be done under this new connection. Other suites like Firedac, SQLdb, … have a component titled Transaction which allows it to be associated with an existing connection and exchange it without having to open a new connection with a different TIL. As of Zeos8+ there is already a ZTransaction component for this purpose, if you want to help the project, it is at:
https://sourceforge.net/projects/zeoslib/
And to ask questions or report bugs:
https://zeoslib.sourceforge.io/
The ability to change TILs without having to establish a new connection is quite interesting because sometimes we experience different scenarios within the same program, for example, using “READ COMMITED” for research, but a “SNAPSHOT” when the situation is reports or even “SERIALIZABLE” when editing specific records and blocking others from editing them. Also, some RDBMS charge their licenses “per connection” to the bank rather than “per device”.
Properties(properties or parameters):
Value will be a PairList-style List of parameters(variable=content).
Properties are database connection parameters that can define behaviors of the Zeos component or the database, for example. as an example of properties that change the behavior of Zeos we can mention its ability to create a database with the properties CreateNewDatabase, see this example:
// O codigo abaixo esta criando o banco de dados, porém o charset UTF8 que
// deveria ter o collate UNICODE_CI_AI ficou sem nenhum collation. Este foi bug
// que reportei e aguardo estarem resolvidos na próxima revisão.
try
if ZConnection1.Connected then
ZConnection1.Disconnect;
ZConnection1.Properties.Clear;
ZConnection1.Properties.Values['dialect']:='3';
ZConnection1.Properties.Values['CreateNewDatabase'] :=
'CREATE DATABASE ' + QuotedStr('C:\PATH\TO\DATABASE.FDB') +
' USER ' + QuotedStr('SYSDBA') +
' PASSWORD ' + QuotedStr('masterkey') +
' PAGE_SIZE ' + intToStr(8192) +
' DEFAULT CHARACTER SET '+QuotedStr('UTF8')
' COLLATION '+QuotedStr('UNICODE_CI_AI') +';'+sLineBreak;
ZConnection1.Connect;
ShowMessage('banco criado!');
except
on e:exception do ShowMessage(e.Message);
end; Observation: Usar database com charset UNICODE como no exemplo acima traz algumas limitações, visto que um caractere pode consumir mais bytes que outros charsets, o tamanho de página máximo será reduzido a metade, se o tamanho de página máximo for 16.384 bytes para outros charsets, provavelmente em UNICODE não passará de 8.192 bytes.
Personally, I prefer to run scripts to create databases because the iSQL tool is optimized and tested for that. Creating a database using a component there is no guarantee that it was created without any bugs.
Properties it can also be used to change the way the bank should behave in some situations. In the example below, I'm using Properties to modify transaction isolation (TIL) in a FirebirdSQL database:
// read committed properties for FirebirdSQL ZConnection1.Connection.TransactIsolationLevel := tiNone; ZConnection1.Properties.Clear; ZConnection1.Properties.Add('isc_tpb_read_committed'); ZConnection1.Properties.Add('isc_tpb_rec_version'); ZConnection1.Properties.Add('isc_tpb_nowait'); The properties and characteristics vary depending on the database, but specifically for TIL it is not necessary to use them as we have the property TransactIsolationLevel specialized in it. Change the type of insulation through Properties and not using TransactIsolationLevel := tiNone can result in a problem because one can mischaracterize another and Zeos prevents this by giving a fatal error message.
ReadOnly:
Value of type True or False. When “true”, no write operations to the database will work. This does not mean that there cannot be commit or RollBack, as they also have to do with TIL where records can only be available after a commit followed by a refresh.
SQLHourGlass:
Valor Verdadeiro ou Falso. Quando SQLHourGlass é “true”, no momento em que um comando SQL é submetido, a aparência cursor incluirá as iniciais “SQL” dentro da mesma.
UseMetadata:
Valor do tipo Verdadeiro ou Falso. Se “true”, os metadados (tabelas internas do banco de dados, não as tabelas criadas por você) serão usados para determinar se as colunas(campos) são graváveis ou não, isso é necessários para que comandos SQL como insert, update e delete gerados automaticamente sejam funcionais. Boa parte dos componentes como TZUpdateSQL ou TFields precisam disso então a boa politica aqui é manter ligado, isto é, verdadeiro.
Mantenha “falso” apenas se tiver certeza de que o acesso a metadados não serão necessários, geralmente desejamos isso quando estamos escrevendo um backend ou classes onde todas as atualizações em SQL serão escritas e executadas manualmente sem as automações que falei. Alguns programadores evitam os data-awares – componentes associados a TFields – e tem seus SQLs de INSERT/UPDATE/DELETE escritos manualmente e assim também não precisam dessa opção ligada. Evitar que a aplicação faça consulta aos metadados é também uma forma de otimizar o sistema, já que sua aplicação fará menos consulta ao banco de dados, útil especialmente em serviços hospedados na nuvem como consultas REST.
If you use methods like RowAffected or RowCount, que retornam o numero de registros afetados pelo ultimo select/insert/update/delete não é muito saudável deixar essa propriedade em Verdadeiro, pois seus resultados obtidos podem ter sido influenciados por consulta ao metadados depois que você concluiu um comando SQL.
connected
Value of type True or False. When “true” connects to the database and remains as real, if the connection fails then it returns as false. If you are already logged in and are informed false then the connection will be broken. Ex:
if ZConnection1.Connected then ZConnection1.Connected:=false; Mas para alguns é estranho usar do jeito acima, por isso, há esse outro jeito:
if ZConnection1.Connected then ZConnection1.Disconnect;Different ways of doing the same thing is common in programming, much of what we know today comes from patterns created in other languages, this makes it much easier for programmers of other languages to feel comfortable.
Connecting to a database
Now that we know all the Zeos connection properties, we can then connect to a database:
try if zConnection1.Connected then zConnection1.Disconnect; zConnection1.AutoCommit:=false; //zConnection1.AutoEncodeStrings:=false; // it is innocuous zConnection1.Catalog:=''; // do you use postgre? zConnection1.Protocol:='firebird'; zConnection1.ClientCodePage:='ISO8859_1'; // The cCP_UTF8 constant needs the ZCompatibility unit no uses // the cCP_UTF16 and cGET_ACP constants are not used in Lazarus. zConnection1.ControlsCodePage:=cCP_UTF8; zConnection1.Database:='c:\path\to\to\bank.fdb'; zConnection1.Hostname:='localhost'; zConnection1.LibraryLocation:='fbclient.dll'; zConnection1.LoginPrompt:=false; zConnection1.User:='SYSDBA'; zConnection1.Password:='masterkey'; zConnection1.Port:=3050; // The constants inside the TransactIsolationLevel // are inside the unit ZDbcIntfs zConnection1.TransactIsolationLevel := tiReadCommitted; // read committed properties for FirebirdSQL will change isolation // even with TransactIsolationLevel=tiReadCommitted zConnection1.Properties.Clear; zConnection1.Properties.Add('isc_tpb_read_committed'); zConnection1.Properties.Add('isc_tpb_rec_version'); zConnection1.Properties.Add('isc_tpb_nowait'); zConnection1.ReadOnly:=false; zConnection1.SQLHourGlass:=true; zConnection1.UseMetadata:=true; ZConnection1.Connected:=true; finally end; connection pool
Este é um recurso muito útil para quem servirá múltiplas conexões e não necessariamente uma conexão por usuário, é quase uma exigência para serviços WEB, pois ao manter conexões abertas pré-dispostas não é necessário conectar a base todas as vezes, e se uma conexão já estiver em uso, podemos usufruir de outra disponível ou abrir uma nova. Saiba que é possível pool de conexões usando o Zeos, o método é bastante simples envolvendo uma nova propriedade em Properties and rename the connection and the protocol to have the suffix pooled in connections. But it lacks material for me to demonstrate how to do it, but if you intend to implement it I suggest you read the link:
https://sourceforge.net/p/zeoslib/wiki/Connection%20Pooling/
Conclusion
Everything we learned with Zeos can be found in other data access component suites such as SQLdb (Lazarus), FireDAC, IBO and many others. So learning how Zeos works also teaches us how other suites work, learned one, practically learned them all.
If you're still in doubt, watch the video:
In the following video, we will understand the difference between an embedded, local and remote connection using FirebirdSQL Database:
If you want to study the examples, they can be obtained here: