January 18, 2011 at 15:56How to make friends 1C 7.7 and Postgres
For such a popular software product as 1C: Enterprise, developers have to provide cross-platform and compatibility with a large number of applications. In particular, with each new version, support for all new DBMSs is added.
Many enterprises (mainly small and medium ones) are still in no hurry to switch from version 7.7 to 8.x. And in this case, the only compatible DBMS provided by the developers is Microsoft SQL. The option is very expensive for a small company: in addition to licenses for workstations and MS SQL Server, you also need to purchase MS Server.
An alternative solution could be SELTA @ Etersoft technologydeveloped by the St. Petersburg company Etersoft. SELTA is used in conjunction with the free Postgre SQL DBMS and allows 1C 7.7 to work with SQL databases. The product is commercial and is positioned by the developer as affordable (the solution costs from 15,000 p.) An alternative to MS SQL Server.
SELTA @ Etersoft is a MS SQL Server emulator for Postgre SQL. translating SQL queries from the T-SQL dialect to pgSQL. Installing and configuring SELTA @ Etersoft on a client PC is trivial: starting the installer, setting up a connection to Postgre SQL, and creating a database through the GUI with a few mouse clicks. It is much more interesting to consider the technology from the inside. At first glance, SELTA @ Etersoft, when processing each request, performs the following actions: the
software sends the request
The request is received, broadcast and sent Postgre SQL Postgre SQL accepted the request, processed it, returned the result, which is returned to the software that made the request. All these actions are called translation from one dialect of SQL to another. The ODBC driver for the MS SQL server in the registry changes to the Postgres driver. Requests in this driver are caught, parsed, and converted into Postgre SQL format.
In order to make such emulation possible, SELTA @ Etersoft solves a number of problems related to the dissimilarity of the two DBMSs, in particular, the difference in locks and the lack of dynamic cursors in Postgres. For example, the problem with missing dynamic queries was solved by a table with changes and automatic creation of triggers on all tables with fixing all changes. Thanks to this, the driver finds out whether it is necessary to recreate the cursor created by the dynamic.
Meanwhile, such a scheme creates restrictions on the use of SELTA @ Etersoft and does not allow the compiler to become fully universal. Currently, it only supports programs running through the ODBC driver. However, the developers plan to implement SELTA @ Etersoft as a server, which will ensure compatibility with all applications using MS SQL.
The Postgres and MS SQL Server locking system is quite nontrivial. But this issue goes beyond the first acquaintance with technology, and it can be considered in more detail in the continuation of the article if it arouses interest from readers.
Many enterprises (mainly small and medium ones) are still in no hurry to switch from version 7.7 to 8.x. And in this case, the only compatible DBMS provided by the developers is Microsoft SQL. The option is very expensive for a small company: in addition to licenses for workstations and MS SQL Server, you also need to purchase MS Server.
An alternative solution could be SELTA @ Etersoft technologydeveloped by the St. Petersburg company Etersoft. SELTA is used in conjunction with the free Postgre SQL DBMS and allows 1C 7.7 to work with SQL databases. The product is commercial and is positioned by the developer as affordable (the solution costs from 15,000 p.) An alternative to MS SQL Server.
SELTA @ Etersoft is a MS SQL Server emulator for Postgre SQL. translating SQL queries from the T-SQL dialect to pgSQL. Installing and configuring SELTA @ Etersoft on a client PC is trivial: starting the installer, setting up a connection to Postgre SQL, and creating a database through the GUI with a few mouse clicks. It is much more interesting to consider the technology from the inside. At first glance, SELTA @ Etersoft, when processing each request, performs the following actions: the
software sends the request
SELECT TOP 2 * FROM tab;
.
.
.The request is received, broadcast and sent Postgre SQL Postgre SQL accepted the request, processed it, returned the result, which is returned to the software that made the request. All these actions are called translation from one dialect of SQL to another. The ODBC driver for the MS SQL server in the registry changes to the Postgres driver. Requests in this driver are caught, parsed, and converted into Postgre SQL format.
SELECT * FROM tab LIMIT 2;
.In order to make such emulation possible, SELTA @ Etersoft solves a number of problems related to the dissimilarity of the two DBMSs, in particular, the difference in locks and the lack of dynamic cursors in Postgres. For example, the problem with missing dynamic queries was solved by a table with changes and automatic creation of triggers on all tables with fixing all changes. Thanks to this, the driver finds out whether it is necessary to recreate the cursor created by the dynamic.
Meanwhile, such a scheme creates restrictions on the use of SELTA @ Etersoft and does not allow the compiler to become fully universal. Currently, it only supports programs running through the ODBC driver. However, the developers plan to implement SELTA @ Etersoft as a server, which will ensure compatibility with all applications using MS SQL.
The Postgres and MS SQL Server locking system is quite nontrivial. But this issue goes beyond the first acquaintance with technology, and it can be considered in more detail in the continuation of the article if it arouses interest from readers.