{"id":757,"date":"2022-02-09T17:53:03","date_gmt":"2022-02-09T20:53:03","guid":{"rendered":"https:\/\/gladiston.net.br\/?page_id=757"},"modified":"2022-08-18T10:27:00","modified_gmt":"2022-08-18T13:27:00","slug":"usando-zeos-conectando-uma-base-de-dados-com-zconnection","status":"publish","type":"page","link":"https:\/\/gladiston.net.br\/en\/usando-zeos-conectando-uma-base-de-dados-com-zconnection\/","title":{"rendered":"Using Zeos - Connecting a Database with ZConnection"},"content":{"rendered":"<p>In this article, we will take a closer look at the ZConnection component, the main component of the \u201cZeos\u201d 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 \u201c<a href=\"https:\/\/gladiston.net.br\/en\/?page_id=309\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"ZeosLib\">ZeosLib<\/a>&#8220;.<\/p>\n\n\n\n<p>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:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>indirect access<\/strong>: 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.<\/li><li><strong>direct access<\/strong>: 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.<\/li><\/ul>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>Zeos separately has another component called ZODBC, it allows access to any database that has the 'SQL Client' and ODBC drivers installed.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>Meet the ZConnection component, this one:<\/p>\n\n\n\n<figure class=\"wp-block-image size-full\"><img loading=\"lazy\" decoding=\"async\" width=\"769\" height=\"453\" src=\"https:\/\/gladiston.net.br\/wp-content\/uploads\/2022\/02\/lazarus-zeos-zconnection01.png\" alt=\"\" class=\"wp-image-759\" srcset=\"https:\/\/gladiston.net.br\/wp-content\/uploads\/2022\/02\/lazarus-zeos-zconnection01.png 769w, https:\/\/gladiston.net.br\/wp-content\/uploads\/2022\/02\/lazarus-zeos-zconnection01-300x177.png 300w, https:\/\/gladiston.net.br\/wp-content\/uploads\/2022\/02\/lazarus-zeos-zconnection01-18x12.png 18w\" sizes=\"auto, (max-width: 769px) 100vw, 769px\" \/><figcaption>ZConnection Component<\/figcaption><\/figure>\n\n\n\n<p>Let's explain its properties before demonstrating how we make the connection:<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"autocommit\">AutoCommit<\/h2>\n\n\n\n<p id=\"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: <\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>select * from someprocedure;<\/li><li>select * from someprocedure(:param1, :param2);<\/li><\/ul>\n\n\n\n<p>Zeos has no way of knowing if the <em>stored procedure<\/em> 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.<\/p>\n\n\n\n<p>In the following link, there is a nice explanation with diagrams and graphs about how this property works:<\/p>\n\n\n\n<p><a href=\"https:\/\/sourceforge.net\/p\/zeoslib\/wiki\/TZConnection.AutoCommit\/\">https:\/\/sourceforge.net\/p\/zeoslib\/wiki\/TZConnection.AutoCommit\/<\/a><\/p>\n\n\n\n<p>There's an explainer video that gives more details about autocommit and finding out if it's a villain or hero:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Part 3: Database connection with Zeos, talking about autocommit - Lazarus\" width=\"580\" height=\"326\" src=\"https:\/\/www.youtube.com\/embed\/xHB7oFudD7I?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><figcaption>Database connection with ZeosLib\/Autocommit \u2013 Lazarus<\/figcaption><\/figure>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"autoencodestrings-verdadeiro-ou-falso\"><strong>AutoEncodeStrings<\/strong>: <\/h2>\n\n\n\n<p id=\"autoencodestrings-verdadeiro-ou-falso\">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.<\/p>\n\n\n\n<p><strong>Observation<\/strong>: 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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"catalog-string\"><strong>Catalog<\/strong>: <\/h2>\n\n\n\n<p id=\"catalog-string\">Value of type String. It is a property dedicated to Postgree database, it has no use for other databases as far as I know.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"connected-verdadeiro-ou-falso\"><strong>protocol<\/strong>: <\/h2>\n\n\n\n<p id=\"connected-verdadeiro-ou-falso\">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 \u201cfirebird\u201d. When defining the protocol, some properties such as <em>ClientCodePage<\/em> 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.<\/p>\n\n\n\n<p>In the protocol name there is a prefix reserved word called <strong>pooled<\/strong> which should only be used when implementing a <a href=\"https:\/\/pt.wikipedia.org\/wiki\/Pool_de_conex%C3%B5es\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"connection pool\"><em>pool<\/em> of connections<\/a>, that is, keep connections open for ready-to-use. Usually required when serving connections from the WEB for the purpose of queries.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"clientcodepage-string\"><strong>ClientCodepage<\/strong>: <\/h2>\n\n\n\n<p id=\"clientcodepage-string\">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&#039;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 <em>ZConnection <\/em>this property can also be configured in \u201cProperties\u201d through the \u201clc_ctype\u201d or \u201cCodepage\u201d parameters:<br>ZConnection.Properties.Add (&#039;lc_ctype=ISO8859_1&#039;); \/\/ firebird<br>or<br>ZConnection.Properties.Add (&#039;Codepage=ISO8859_1&#039;); \/\/ others<\/p>\n\n\n\n<p>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 \u201ctranslate\u201d it later. So identifying the charset in the connection will serve as <a href=\"https:\/\/pt.wikipedia.org\/wiki\/Pedra_de_Roseta\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"rosette stone\">rosette stone<\/a> translating the \u201ccode points\u201d which is the representation of the character between the database and the client side.<\/p>\n\n\n\n<p><strong>very important note<\/strong>: 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,\u2026. 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. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"controlscodepage-verdadeiro-ou-falso\"><strong>ControlsCodePage<\/strong>: <\/h2>\n\n\n\n<p id=\"controlscodepage-verdadeiro-ou-falso\">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:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>cCP_UTF8 <\/strong>: 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.<\/li><\/ul>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>cCP_UTF16 <\/strong>and <strong>cGET_ACP<\/strong>: are implemented because of Delphi and should not be used in Lazarus.<\/li><\/ul>\n\n\n\n<p>When using UTF-8 in the database, but your connection is Win1252 as code page then there is a <a href=\"https:\/\/sourceforge.net\/p\/zeoslib\/wiki\/Zeos%20and%20character%20sets\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"very important note\">very important note<\/a> which refers to a bug up to Zeos 7.2 that deserves your attention.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"database-string\"><strong>Data base<\/strong>: <\/h2>\n\n\n\n<p id=\"database-string\">Value of type String. Refers to the location of the database, either in literal form like C:\\Path\\to\\to\\bank.fdb\u201d or in connection string form like:<\/p>\n\n\n\n<pre id=\"database-string\" class=\"wp-block-code\"><code>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)<\/code><\/pre>\n\n\n\n<p id=\"database-string\">Or other ways recognized by ADO or &#039;SQL Client&#039; of your database.<\/p>\n\n\n\n<p id=\"database-string\">Some databases like FirebirdSQL, MSSQL, PostgreSQL,\u2026 a connection string can supply everything the &#039;SQL Client&#039; needs to connect to the database, so properties like <em>Host, Port, Protocol, ClientCodepage, UserName<\/em> and <em>password<\/em> become unnecessary. <\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"designconnection-verdadeiro-ou-falso\"><strong>DesignConnection<\/strong>: <\/h2>\n\n\n\n<p id=\"designconnection-verdadeiro-ou-falso\">Value of type True or False. If the value is set to <em>true<\/em>(true), the connection will be active at design time, but will remain disconnected when you give a '<em>run<\/em>' in the project or run the application <em>standalone<\/em>. 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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"hostname-string\"><strong>hostname<\/strong>: <\/h2>\n\n\n\n<p id=\"hostname-string\">Value of type String. Hostname that will serve the database, it can be an IP, qualified network name or simply \u201clocalhost\u201d, in all these cases we are claiming to use client\/server access. It is important to say that \u201clocalhost\u201d 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 <em>hostname<\/em> and either <em>port<\/em>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"librarylocation-string\"><strong>LibraryLocation<\/strong>: <\/h2>\n\n\n\n<p id=\"librarylocation-string\">Value of type String. Indicates where the access library (SQL Client) will be, in the case of FirebirdSQL it is called <em>fbclient.dll<\/em> on Windows or<em> libfbclient.so<\/em> 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 &#039;SQL Client&#039; a file called <em>gds32.dll<\/em>, then turned <em>fbclient.dll<\/em>, but for backwards compatibility it still accepts <em>gds32.dll<\/em> and in this situation it is not always possible to guarantee that the \u201cautomatic\u201d will recognize which DLL is right when both are on the system. That&#039;s why I recommend that you at least leave the correct name of the &#039;SQL Client&#039; you want to use, for example, \u201cfbclient.dll\u201d and prevent the \u201cautomatic\u201d from finding the wrong DLL first. <\/p>\n\n\n\n<p id=\"librarylocation-string\">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 &#039;SQL Client&#039; equal to the architecture of the operating system and this will go wrong because the &#039;SQL Client&#039; 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 &#039;SQL Client&#039; and then the 64bit one. Some banks already know this and simplify this operation, when FirebirdSQL 3+ installs the 64bit &#039;SQL Client&#039;, it also installs the 32bit version.<\/p>\n\n\n\n<p id=\"librarylocation-string\">It is worth noting that if you specify a path like \u201cc:\\app\\bin\\fbclient.dll\u201d as your <em>librarylocation<\/em> then the system will not look for it in the system path, but only the one specified, we will call this &#039;plastering the DLL&#039;. Many programmers take this attitude because they consider a simpler way, however this creates another problem: you will be responsible for updating the &#039;SQL Client&#039; manually and getting bugs and security flaws fixed. If you are deploying a system in the cloud, you should never plaster the &#039;SQL Client&#039; to one location. Linux environments update programs obtained from their repositories automatically and will update the &#039;SQL Client&#039; as well.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"loginprompt-verdadeiro-ou-falso\"><strong>LoginPrompt<\/strong>: <\/h2>\n\n\n\n<p id=\"loginprompt-verdadeiro-ou-falso\">Value of type True or False. If it is \u201ctrue\u201d, it will ask for the username and password every time it connects, even though the UserName and Password parameters have been provided.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"user-string\"><strong>User<\/strong>: <\/h2>\n\n\n\n<p id=\"user-string\">Value of type String. The database username. For example, \u201cSYSDBA\u201d, \u201cdbo\u201d, \u201csa\u201d,\u2026<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"password-string\"><strong>password<\/strong>: <\/h2>\n\n\n\n<p id=\"password-string\">Value of type String. Password to connect to the database, example: masterkey<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"port-inteiro-0-65535\"><strong>Port<\/strong>: <\/h2>\n\n\n\n<p id=\"port-inteiro-0-65535\">Value of type Integer(0-65535). Specifies the connection port, in the Firebird example, it is usually 3050. If you leave \u201c0\u201d Zeos will use the <em>default<\/em> according to the protocol (driver) of the database.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"transactisolationlevel-tinone-tireadcommitted-tireaduncommitted-tirepeatableread-tiserializable\"><strong>TransactIsolationLevel<\/strong> (TIL): <\/h2>\n\n\n\n<p id=\"transactisolationlevel-tinone-tireadcommitted-tireaduncommitted-tirepeatableread-tiserializable\">Values: tiNone, tiReadCommitted, tiReadUncommitted, tiRepeatableRead, tiSerializable. Determines what the Transaction Isolation Level (TIL from now on) will be. Explanation for each of them:<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>tiNone<\/strong>: As the name suggests, it means \u201cnone\u201d, as \u201cnone\u201d is an impossible choice for a relational database, this means you will use <em>properties<\/em> (parameters from here on) with a set of commands that will define the TIL. Leave <em>tiNone<\/em> and not have any kind of <em>properties <\/em>that form a TIL will cause a fatal error message because the characteristic <a href=\"https:\/\/pt.wikipedia.org\/wiki\/ACID\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"ACID\">ACID<\/a> of a database only makes sense if there is a TIL defined.<\/li><li><strong>tiRepeatableRead<\/strong>: Corresponds to the TIL \u201cREPEATABLE READ\u201d. A transaction sees during its lifetime only the data that was committed before the transaction was started.<\/li><li><strong>tiReadCommitted<\/strong>: Corresponds to the TIL \u201cREAD COMMITTED\u201d. A transaction only sees the data committed before the statement is executed. This is a subtle difference to the \u201crepeatable read\u201d .<\/li><li><strong>tiReadUnCommitted<\/strong>: Corresponds to the TIL \u201cREAD UNCOMMITTED\u201d. A transaction sees changes made by uncommitted transactions. It is also known in other databases as TIL \u201cSNAPSHOT\u201d.<\/li><li><strong>tiSerializable<\/strong>: Corresponds to the TIL \u201cSERIALIZABLE\u201d. This is the strictest isolation level, which enforces transaction serialization, this means that every time you start the transaction and then give a  <em>commit<\/em> or <em>rollback<\/em>. 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 \u201cSNAPSHOT\u201d TIL. It is also known in other systems as \u201cconcurrency control\u201d.<\/li><\/ul>\n\n\n\n<p>The concern of every \u201cnewbie\u201d is to prevent the infamous \u201cdead lock\u201d, 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 \u201cghost records\u201d. In all applications, but especially large ones, a command like UPDATE or MERGE raises the question: \u201cIf there are ghosts, what do I do with them, do I update or ignore?\u201d and then select the proper TIL with \u201cSERIALIZABLE\u201d being the strictest and likely to solve the problem. Details about the TILs can be read <a href=\"https:\/\/en.wikipedia.org\/wiki\/Isolation_(database_systems)\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"in this wikipedia article\">in this wikipedia article<\/a>.<\/p>\n\n\n\n<p>For FirebirdSQL database users there are some considerations (ignore if using another database):<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li><strong>TIL &quot;READ COMMITTED&quot;:<\/strong> In FirebirdSQL matches the combination of parameters <strong>isc_tpb_read_committed<\/strong>, <strong>isc_tpb_rec_version <\/strong>and <strong>isc_tpb_nowait <\/strong>juntos. Uma transa\u00e7\u00e3o v\u00ea apenas os dados confirmados antes que a instru\u00e7\u00e3o seja executada. O par\u00e2metro \u201crec_version\u201d para o Firebird \u00e9 respons\u00e1vel pelo comportamento de que os valores mais recentes que foram \u201ccommitados\u201d por outros usu\u00e1rios ser\u00e3o considerados. O par\u00e2metro \u201cnowait\u201d \u00e9 respons\u00e1vel pelo comportamento de que n\u00e3o h\u00e1 espera pela libera\u00e7\u00e3o de um registro bloqueado. Neste n\u00edvel o servidor \u00e9 mais sobrecarregado que no TIL &#8220;REPEATABLE READ&#8221;, porque tem que fazer todos os \u201crefresh\u201d para adquirir estes valores novamente. O FirebirdSQL \u00e9 mais usado com esta configura\u00e7\u00e3o.<\/li><li><strong>TIL &quot;READ UNCOMMITTED&quot;<\/strong>: The Zeos documentation says that this option does not exist in Firebird, however it is documented in the <a href=\"https:\/\/www.firebirdsql.org\/pdfmanual\/html\/isql-transactions.html\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"official site\">official site<\/a>. 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 <strong>aus\u00eancia <\/strong>da <em>property<\/em>(par\u00e2metro, daqui em diante) <strong>&#8220;isc_tpb_rec_version<\/strong>\u201d leaving only the parameters: <strong>isc_tpb_concurrency <\/strong>and <strong>isc_tpb_nowait<\/strong>.<\/li><li><strong>TIL &quot;REPEATABLE READ&quot;<\/strong> It is known as <strong>\u201cSNAPSHOT\u201d<\/strong>. It is a combination of transaction parameters \u201c<strong>concurrency<\/strong>\" and \"<strong>nowait<\/strong>\u201d.<\/li><li><strong>TIL \u201cSERIALIZABLE\u201d <\/strong>It is known as <strong>\u201cSnapshot table stability\u201d<\/strong>. It is a combination of transaction parameters \u201c<strong>concurrency<\/strong>\" and \"<strong>nowait<\/strong>\u201d.<\/li><li>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: <strong>isc_tpb_concurrency<\/strong>, <strong>isc_tpb_nowait<\/strong>, <strong>isc_tpb_read<\/strong>, <strong>isc_tpb_write<\/strong>, <strong>isc_tpb_consistency<\/strong>, <strong>isc_tpb_read_committed<\/strong>, <strong>isc_tpb_rec_version <\/strong>you will have a custom TIL.<\/li><li>A brief explanation with examples is provided by IbSurgeon <a href=\"https:\/\/ib-aid.com\/en\/transactions-in-firebird-acid-isolation-levels-deadlocks-and-update-conflicts-resolution\/\" target=\"_blank\" rel=\"noreferrer noopener\" title=\"in this article\">in this article<\/a>.<\/li><\/ul>\n\n\n\n<p>These TILs are a headache for inexperienced people when they complain that the system is slow, having <em>dead lock<\/em> 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:<\/p>\n\n\n\n<p><strong>Scene 1:<\/strong> Let's say you want any closing report, the report takes a long time and before it ends, during the process, some values \u200b\u200bchange 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 \u200b\u200bwere changed while the report was still running, how would you solve it?<\/p>\n\n\n\n<p><strong>Scene #2:<\/strong> 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? \u201cSNAPSHOT TABLE STABILITY\u201d or \u201cSERIALIZABLE\u201d and then SELECT FOR UPDATE WITH LOCK?<\/p>\n\n\n\n<p>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 \u201cSERIALIZABLE\u201d (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. <\/p>\n\n\n\n<p>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 \u201clazdemo_transacoes\u201d hosted on github:<\/p>\n\n\n\n<p><a href=\"https:\/\/github.com\/gladiston\/lazdemos_gsl\">https:\/\/github.com\/gladiston\/lazdemos_gsl<\/a><\/p>\n\n\n\n<p>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, \u2026 have a component titled <em><strong>Transaction<\/strong><\/em> 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:<\/p>\n\n\n\n<p><a href=\"https:\/\/sourceforge.net\/projects\/zeoslib\/\">https:\/\/sourceforge.net\/projects\/zeoslib\/<\/a><\/p>\n\n\n\n<p>And to ask questions or report bugs:<\/p>\n\n\n\n<p><a href=\"https:\/\/zeoslib.sourceforge.io\/\">https:\/\/zeoslib.sourceforge.io\/<\/a><\/p>\n\n\n\n<p>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 \u201cREAD COMMITED\u201d for research, but a \u201cSNAPSHOT\u201d when the situation is reports or even \u201cSERIALIZABLE\u201d when editing specific records and blocking others from editing them. Also, some RDBMS charge their licenses \u201cper connection\u201d to the bank rather than \u201cper device\u201d.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"properties-lista-de-parametros\"><strong>Properties<\/strong>(properties or parameters): <\/h2>\n\n\n\n<p id=\"properties-lista-de-parametros\">Value will be a PairList-style List of parameters(variable=content). <\/p>\n\n\n\n<p><em>Properties<\/em> are database connection parameters that can define behaviors of the Zeos component or the database, for example. as an example of <em>properties <\/em>that change the behavior of Zeos we can mention its ability to create a database with the <em>properties <\/em><strong>CreateNewDatabase<\/strong>, see this example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\/\/ O codigo abaixo esta criando o banco de dados, por\u00e9m o charset UTF8 que \n\/\/ deveria ter o collate UNICODE_CI_AI ficou sem nenhum collation. Este foi bug \n\/\/ que reportei e aguardo estarem resolvidos na pr\u00f3xima revis\u00e3o.\ntry\n  if ZConnection1.Connected then\n    ZConnection1.Disconnect;\n  ZConnection1.Properties.Clear;\n  ZConnection1.Properties.Values&#91;'dialect']:='3';\n  ZConnection1.Properties.Values&#91;'CreateNewDatabase'] :=\n    'CREATE DATABASE ' + QuotedStr('C:\\PATH\\TO\\DATABASE.FDB') +  \n    ' USER ' + QuotedStr('SYSDBA') +\n    ' PASSWORD ' + QuotedStr('masterkey') +\n    ' PAGE_SIZE ' + intToStr(8192) +\n    ' DEFAULT CHARACTER SET '+QuotedStr('UTF8')\n    ' COLLATION '+QuotedStr('UNICODE_CI_AI') +';'+sLineBreak;\n  ZConnection1.Connect;\n  ShowMessage('banco criado!'); \nexcept\non e:exception do ShowMessage(e.Message);\nend;   <\/code><\/pre>\n\n\n\n<p><strong>Observation<\/strong>: Usar database com charset UNICODE como no exemplo acima traz algumas limita\u00e7\u00f5es,  visto que um caractere pode consumir mais bytes que outros charsets, o tamanho de p\u00e1gina m\u00e1ximo ser\u00e1 reduzido a metade, se o tamanho de p\u00e1gina m\u00e1ximo for <strong>16.384<\/strong> bytes para outros charsets, provavelmente em UNICODE n\u00e3o passar\u00e1 de <strong>8.192<\/strong> bytes.<\/p>\n\n\n\n<p>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.<\/p>\n\n\n\n<p><em>Properties<strong> <\/strong><\/em>it can also be used to change the way the bank should behave in some situations. In the example below, I&#039;m using <em>Properties<\/em> to modify transaction isolation (TIL) in a FirebirdSQL database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>\/\/ read committed properties for FirebirdSQL ZConnection1.Connection.TransactIsolationLevel := tiNone; ZConnection1.Properties.Clear; ZConnection1.Properties.Add(&#039;isc_tpb_read_committed&#039;); ZConnection1.Properties.Add(&#039;isc_tpb_rec_version&#039;); ZConnection1.Properties.Add(&#039;isc_tpb_nowait&#039;); <\/code><\/pre>\n\n\n\n<p>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 <strong>TransactIsolationLevel<\/strong> specialized in it. Change the type of insulation through <em>Properties <\/em>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"readonly-verdadeiro-ou-falso\"><strong>ReadOnly<\/strong>: <\/h2>\n\n\n\n<p id=\"readonly-verdadeiro-ou-falso\">Value of type True or False. When \u201ctrue\u201d, no write operations to the database will work. This does not mean that there cannot be <em>commit<\/em> or <em>RollBack<\/em>, as they also have to do with TIL where records can only be available after a commit followed by a refresh.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"sqlhourglass-verdadeiro-ou-falso\"><strong>SQLHourGlass<\/strong>: <\/h2>\n\n\n\n<p id=\"sqlhourglass-verdadeiro-ou-falso\">Valor Verdadeiro ou Falso. Quando SQLHourGlass \u00e9 &#8220;true&#8221;, no momento em que um comando SQL \u00e9 submetido, a apar\u00eancia cursor incluir\u00e1 as iniciais &#8220;SQL&#8221; dentro da mesma.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"usemetadata-verdadeiro-ou-falso\"><strong>UseMetadata<\/strong>: <\/h2>\n\n\n\n<p id=\"usemetadata-verdadeiro-ou-falso\">Valor do tipo Verdadeiro ou Falso. Se &#8220;true&#8221;, os metadados (tabelas internas do banco de dados, n\u00e3o as tabelas criadas por voc\u00ea) ser\u00e3o usados para determinar se as colunas(campos) s\u00e3o grav\u00e1veis ou n\u00e3o, isso \u00e9 necess\u00e1rios para que comandos SQL como insert, update e delete gerados automaticamente sejam funcionais. Boa parte dos componentes como TZUpdateSQL ou TFields precisam disso ent\u00e3o a boa politica aqui \u00e9 manter ligado, isto \u00e9, verdadeiro. <\/p>\n\n\n\n<p id=\"usemetadata-verdadeiro-ou-falso\">Mantenha &#8220;falso&#8221; apenas se tiver certeza de que o acesso a metadados n\u00e3o ser\u00e3o necess\u00e1rios, geralmente desejamos isso quando estamos escrevendo um backend ou classes onde todas as atualiza\u00e7\u00f5es em SQL ser\u00e3o escritas e executadas manualmente sem as automa\u00e7\u00f5es que falei. Alguns programadores evitam os data-awares &#8211; componentes associados a TFields &#8211; e tem seus SQLs de INSERT\/UPDATE\/DELETE escritos manualmente e assim tamb\u00e9m n\u00e3o precisam dessa op\u00e7\u00e3o ligada. Evitar que a aplica\u00e7\u00e3o fa\u00e7a consulta aos metadados \u00e9 tamb\u00e9m uma forma de otimizar o sistema, j\u00e1 que sua aplica\u00e7\u00e3o far\u00e1 menos consulta ao banco de dados, \u00fatil especialmente em servi\u00e7os hospedados na nuvem como consultas REST.<\/p>\n\n\n\n<p>If you use methods like <strong><em>RowAffected <\/em><\/strong>or <em><strong>RowCount<\/strong><\/em>, que retornam o numero de registros afetados pelo ultimo select\/insert\/update\/delete n\u00e3o \u00e9 muito saud\u00e1vel deixar essa propriedade em Verdadeiro, pois seus resultados obtidos podem ter sido influenciados por consulta ao metadados depois que voc\u00ea concluiu um comando SQL.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"connected\">connected<\/h2>\n\n\n\n<p>Value of type True or False. When \u201ctrue\u201d connects to the database and remains as <em>real<\/em>, if the connection fails then it returns as <em>false<\/em>. If you are already logged in and are informed <em>false<\/em> then the connection will be broken. Ex:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>    if ZConnection1.Connected then ZConnection1.Connected:=false;  <\/code><\/pre>\n\n\n\n<p>Mas para alguns \u00e9 estranho usar do jeito acima, por isso, h\u00e1 esse outro jeito:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>if ZConnection1.Connected then ZConnection1.Disconnect;<\/code><\/pre>\n\n\n\n<p>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.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"conectando-a-uma-base-de-dados\">Connecting to a database<\/h2>\n\n\n\n<p>Now that we know all the Zeos connection properties, we can then connect to a database:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>try if zConnection1.Connected then zConnection1.Disconnect; zConnection1.AutoCommit:=false; \/\/zConnection1.AutoEncodeStrings:=false; \/\/ it is innocuous zConnection1.Catalog:=&#039;&#039;; \/\/ do you use postgre? zConnection1.Protocol:=&#039;firebird&#039;; zConnection1.ClientCodePage:=&#039;ISO8859_1&#039;; \/\/ 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:=&#039;c:\\path\\to\\to\\bank.fdb&#039;; zConnection1.Hostname:=&#039;localhost&#039;; zConnection1.LibraryLocation:=&#039;fbclient.dll&#039;; zConnection1.LoginPrompt:=false; zConnection1.User:=&#039;SYSDBA&#039;; zConnection1.Password:=&#039;masterkey&#039;; 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(&#039;isc_tpb_read_committed&#039;); zConnection1.Properties.Add(&#039;isc_tpb_rec_version&#039;); zConnection1.Properties.Add(&#039;isc_tpb_nowait&#039;); zConnection1.ReadOnly:=false; zConnection1.SQLHourGlass:=true; zConnection1.UseMetadata:=true; ZConnection1.Connected:=true; finally end; <\/code><\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"pool-de-conexoes\">connection pool<\/h2>\n\n\n\n<p>Este \u00e9 um recurso muito \u00fatil para quem servir\u00e1 m\u00faltiplas conex\u00f5es e n\u00e3o necessariamente uma conex\u00e3o por usu\u00e1rio, \u00e9 quase uma exig\u00eancia para servi\u00e7os WEB, pois ao manter conex\u00f5es abertas pr\u00e9-dispostas n\u00e3o \u00e9 necess\u00e1rio conectar a base todas as vezes, e se uma conex\u00e3o j\u00e1 estiver em uso, podemos usufruir de outra dispon\u00edvel ou abrir uma nova. Saiba que \u00e9 poss\u00edvel pool de conex\u00f5es usando o Zeos, o m\u00e9todo \u00e9 bastante simples envolvendo uma nova propriedade em <em>Properties <\/em>and rename the connection and the <em>protocol <\/em>to have the suffix <strong>pooled<\/strong> 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:<\/p>\n\n\n\n<p><a href=\"https:\/\/sourceforge.net\/p\/zeoslib\/wiki\/Connection%20Pooling\/\">https:\/\/sourceforge.net\/p\/zeoslib\/wiki\/Connection%20Pooling\/<\/a><\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"conclusao\">Conclusion<\/h2>\n\n\n\n<p>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.<\/p>\n\n\n\n<p>If you&#039;re still in doubt, watch the video:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Part 2: Database connection with Zeos, talking about TILs - Lazarus\" width=\"580\" height=\"326\" src=\"https:\/\/www.youtube.com\/embed\/8GL851YCDNY?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>In the following video, we will understand the difference between an embedded, local and remote connection using FirebirdSQL Database:<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-video is-provider-youtube wp-block-embed-youtube wp-embed-aspect-16-9 wp-has-aspect-ratio\"><div class=\"wp-block-embed__wrapper\">\n<iframe loading=\"lazy\" title=\"Database: Difference between local, remote and embedded connection, especially with FirebirdSQL.\" width=\"580\" height=\"326\" src=\"https:\/\/www.youtube.com\/embed\/KT5VDXnLCFc?feature=oembed\" frameborder=\"0\" allow=\"accelerometer; autoplay; clipboard-write; encrypted-media; gyroscope; picture-in-picture; web-share\" referrerpolicy=\"strict-origin-when-cross-origin\" allowfullscreen><\/iframe>\n<\/div><\/figure>\n\n\n\n<p>If you want to study the examples, they can be obtained here:<\/p>\n\n\n\n<p><a href=\"https:\/\/github.com\/gladiston\/lazdemos_gsl\">https:\/\/github.com\/gladiston\/lazdemos_gsl<\/a><\/p>","protected":false},"excerpt":{"rendered":"<p>Neste artigo vamos esmiu\u00e7ar o componente ZConnection, o principal componente da paleta &#8220;Zeos&#8221;. Sim, sem ele n\u00e3o conseguiriamos conectar a uma base de dados. Se tiver d\u00favida em como instal\u00e1-lo, veja este artigo intitulado &#8220;ZeosLib&#8220;. O componente ZConnection permite que sua aplica\u00e7\u00e3o conecte-se a uma lista bem grande de base de dados, mas antes de [&hellip;]<\/p>\n","protected":false},"author":4,"featured_media":0,"parent":0,"menu_order":0,"comment_status":"closed","ping_status":"closed","template":"templates\/template-full-width.php","meta":{"footnotes":""},"class_list":["post-757","page","type-page","status-publish","hentry"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/pages\/757","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/pages"}],"about":[{"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/types\/page"}],"author":[{"embeddable":true,"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/users\/4"}],"replies":[{"embeddable":true,"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/comments?post=757"}],"version-history":[{"count":133,"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/pages\/757\/revisions"}],"predecessor-version":[{"id":1998,"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/pages\/757\/revisions\/1998"}],"wp:attachment":[{"href":"https:\/\/gladiston.net.br\/en\/wp-json\/wp\/v2\/media?parent=757"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}