Technical Questions and Answers
 PostgreSQL Client DatabaseEngine

We designed the PostgreSQL DatabaseEngine with a single goal in mind: we wanted to provide an easy and automated migration path for existing ISAM-based applications to the PostgreSQL DBMS without major code changes in business logic.

We knew we needed to design and implement two different technologies for that. First and foremost, a technology that emulates ISAM behavior with the minimum of compromises possible while at the same time beating existing performance in multi-user scenarios. And second, there is also the requirement to have transparent SQL access to that data. From within the Xbase++ application and outside - meaning other programming languages and tools.

The PostgreSQL DBE is for your existing ISAM code and your existing ISAM knowledge. It is our promise to protect your existing investments. If you are experienced with SQL and writing new code, we encourage you to use the parametrized SQL statements via DacSqlStatement(). Specifically, query processing becomes a snap.

It enables your Xbase++ application to use regular navigational commands/functions (like SKIP, dbGoBottom(), EOF(), Rechno(), INDEX ON, ...) while letting the PostgreSQL DBMS manages your data. You can make an existing DBF/NTX/CDX-based application a SQL server DBMS-based client/server application with almost no code changes.

It also enables you to migrate existing data and data models from DBF/CDX/NTX to the PostgreSQL DBMS via the upsizing tool. Since the upsizing tool has a programmatic interface, you can plan and describe your upsizing using .upsize XML files and run them on your customer site.

A parameterized SQL statement is a type of SQL query that allows you to use placeholders instead of directly inserting values into the query. This provides benefits in terms of performance, the SQL engine can pre-compile the SQL statement, allowing it to be reused with different values. More importantly, parameterized statements can significantly reduce the risk of SQL injection attacks because the SQL engine treats placeholders as literal values, not as part of the SQL command. Finally, since in Xbase++, the language level value gets automatically transformed to the proper binary representation instead of a string, the statement is more compact.

In summary, this code is performing two parameterized SQL queries, one for customers from Germany and one for customers from Italy, and then displaying the number of customers from each country.

Almost perfectly. Just think about this scenario: SQL statements with INSERT/UPDATE/DELETE can be sent directly to the server using DacSqlStatement(). Even better, to send a bitmap object, you do not need to deal with any data format to do a oStmt:image := oBmp:getBuffer(), everything else is done automatically.

On the other hand, if you need to deal with a result set, you can use DacSqlStatement() again, but this time you use the: query() method to put the result set into a work area. Consequently, you can use regular navigational code and field access; all encapsulated in a work area.

And if you do not like parametrized statements, you can use universal SQL to query like below.

You see, we did as much as we could to ensure that even SQL integration allows the partial reuse of existing code, specifically for UI binding. Finally, there is no need to deal with ugly and semantically wrong string concatenation if doing SQL with Xbase++.

Yes, but keep in mind: you are now dealing with a transaction-capable DBMS. This has implications, one of the major changes here is the fact that a connection/session defines your transaction content. Therefore it is a bad idea to share a session/connection between threads.

It is good practice to have a connection for each thread of your application to isolate transactions.

Yes, they do. You can use any logical or physical navigational commands. There are, however, some minor restrictions with some scope and filter expressions.

The Xbase++ ISAM emulation for the PostgreSQL Server fully supports CDX/NTX index features and allows 100% migration of existing data models. This includes simple or complex index expressions as well as user-defined functions (UDFs) used in your index key.

ISAM/Xbase++ indices are not like SQL indexes. An ISAM index can best be understood as a surrogate key in SQL terms. The great thing with the ISAM index emulation is that re-index requirements due to index corruption are gone. Forever.

Both features, SCOPEs, and FILTERs, are supported by the ISAM emulation; however, in case you are using user-defined functions in your FILTER or SCOPE expression, you need to rewrite your expression to make it work w/o them.

Often it is easier to use a SQL SELECT statement instead of your filter/scopes and use the result set as you would have used the scoped/filtered work area. Since your code interacts with the workspace, it works identically even if the data comes from a SQL SELECT rather than a subset of a SCOPE/FILTER table. The great thing about this approach is that using the SQL SELECT is usually much faster than using the ISAM filter or scope. It is worth rewriting this SCOPE/FILTER section of your code.

We provide a DbfUpsize tool that allows you to fully automate the data transformation/upsize process from local tables and indexes to the PostgreSQL server. The upsize process is defined by a .upsize XML file which describes the input, your DBF tables, related index files, and your output.

Yes, you can. Specifically, after the initial successful migration from DBF/NTX/CDX to the PostgreSQL DBMS using the ISAM emulation, we encourage our customers to learn SQL and use it step by step in areas such as query processing. The benefits are clear because the developer tells the system what he wants and not how to get the data - the code becomes more transparent and more compact than with pure ISAM.

Yes, of course, we designed the ISAM emulation in such a way that not only Xbase++ with plain SQL is allowed to access the data but also foreign tools/applications.

Generally, access to data in ISAM emulating SQL tables on the PostgreSQL server has to be divided into read and write access. While there are no restrictions with read access, some minor restrictions apply if it comes to changing data in SQL from a foreign tool. But thanks to the fix keys feature, even out-of-sync index key values are detected and automatically repaired.

Yes, it is. The Xbase++ ISAM emulation supports record and table locking. In fact, from the application logic perspective, there is no difference in behavior.

One major performance drawback of ISAM with DBF/CDX/NTX data is the implicit locking of the index files when accessing them in a shared scenario. With the Xbase++ ISAM emulation for the PostgreSQL DBMS, this drawback is history leading to higher performance in high concurrency scenarios.

The DBF/NTX/CDX locking works based on the file system file locking mechanism. This has a couple of drawbacks which mostly lead to performance loss. The Xbase++ ISAM emulation uses an IPC event mechanism to communicate and share locking information between the PostgreSQL server and clients.

Since IPC events are queued, the locking is fair as they are granted in the requested order. This leads to a much higher performance, even in multiple reader/writer scenarios.

Because every emulating is slower? No, it isn't. We developed the underlying algorithms for the ISAM emulation engine over the years and have finally reached a stage where the performance is superior in typical use cases. In fact, as of today, the engine easily beats multi-user scenarios (>2 users) with network-shared DBF/CDX/NTX files as well as Advantage Database Server-based setups.

There are still areas where we are unhappy, but we are confident we are progressing here as we did in the past.