As a member of this team, I am actively working with talented and passionate community members around the world to advance PostgreSQL.
Our team will publish blog posts focusing on the features and patches that members have worked on. In this article, I will discuss about a new syntax for ECPG -DECLARE STATEMENT - which was committed for PostgreSQL14.
Background
Embedded SQL program in C is a method to connect to databases, which has been specified in the SQL standard since 1989 (SQL 89). Various vendors, such as Oracle, Microsoft, and IBM, provide this functionality. PostgreSQL also provides this capability via the ECPG preprocess. The basic syntax and rules are specified in the SQL standard, but the degree of compliance depends on each vendor. Because vendors have also added their own embedded SQL syntax, the differences may cause a barrier in database product migration.
I proposed a new embedded SQL syntax -DECLARE STATEMENT. This feature is originally provided by Oracle. I believe that this syntax will make it easier to migrate from Oracle and other databases to PostgreSQL, and thus increase PostgreSQL adoption.
Functional overview – What isDECLARE STATEMENT?
DECLARE STATEMENT is an embedded SQL syntax that declares an identifier for a prepared query. This syntax is introduced to improve compatibility with Oracle's embedded SQL programs, but it also benefits original ECPG users. When cursors are used in Oracle’s embedded SQL programs, identifiers for a prepared query must be declared before they are used in theDECLARE CURSOR syntax. In other words, precompilation in the following example will fail.
Example 1
…
EXEC SQL DECLARE my_cursor CURSOR FORmy_stmt;// precompilation error
EXEC SQL PREPAREmy_stmt FROM "SELECT …";
…
So, how are cursors used in Oracle? One of the solutions is to useDECLARE STATEMENT. The syntax is used for declaring the identifier on the precompiler. When the precompiler reads the syntax, it understands thatmy_stmt is the identifier for a prepared query and can successfully precompile all embedded SQLs. Based on this solution, the modified embedded SQL example is as follows:
Example 2
…
EXECSQL DECLAREmy_stmt STATEMENT;
EXEC SQL DECLARE my_cursor CURSOR FORmy_stmt;
EXEC SQL PREPAREmy_stmt FROM "SELECT …";
…
DECLARE STATEMENT was not available in PostgreSQL13 or earlier, which means users running embedded SQL programs like in example 2 had to change their applications. From PostgreSQL14 onwards, the syntax can be utilized, so Oracle users can easily migrate to PostgreSQL. Note that ECPG allows implicit declarations of identifiers. Therefore, example 1 can be precompiled in the ECPG andDECLARE STATEMENT does not impact this behavior.
There is also another use for this syntax -DECLARE STATEMENT can be used for associating an identifier with the connection. When an embedded SQL statement with an identifier is executed, the associated connection is chosen instead of the current connection. To associate an identifier with a connection you will need to explicitly specify the connection by adding theAT clause forDECLARE STATEMENT. Let’s take a look into the following sample program:
Example 3
EXEC SQL BEGIN DECLARE SECTION;
char my_dbname[128];
EXEC SQL END DECLARE SECTION;
…
EXEC SQL CONNECT TO my_db1 AS my_conn1;
EXEC SQL CONNECT TO my_db2 AS my_conn2;
EXEC SQL SET CONNECTION TOmy_conn2;1
EXEC SQL ATmy_conn1 DECLARE my_stmt STATEMENT;2
EXEC SQL PREPAREmy_stmt FROM "SELECT current_database()";3
EXEC SQL EXECUTEmy_stmt INTO :my_dbname;3
Now, which connection will be used forPREPARE andEXECUTE in3?
At first glance, it would seem thatmy_conn2 would be used, becauseSET CONNECTION is set to my_conn2 in1.
However, the identifiermy_stmt is associated withmy_conn1 in the DECLARE STATEMENT 2.
Therefore,my_conn1 will be chosen when running3 and these statements will be executed on databasemy_db1.
Benefits
What are the benefits of usingDECLARE STATEMENT? The first benefit is the ease of migration. Decreasing incompatibilities between products allows users to avoid rewriting their applications for migration and spend 100% of their time on the services they want to provide.
The second benefit is that the source code can be shortened by reducing the number ofAT<connection> clauses in your code. Without usingDECLARE STATEMENT, connections need to be specified carefully in everyPREPARE,EXECUTE, and cursor operations in order to establish multiple connections in a program. It not only makes your source code longer, but may also lead to bugs caused by choosing wrong connections. UsingDECLARE STATEMENT to associate a connection with an identifier will prevent such connection-related bugs by fixing the appropriate connection to be used in subsequent statements.
Looking ahead
Today, using drivers such as psqlODBC and PostgreSQL JDBC Driver to connect to databases from user applications is mainstream. ECPG is not so actively discussed in the PostgreSQL community, and Commitfest has a small number of ECPG related entries. However, past assets are never lost, and many Fujitsu customers are leveraging embedded SQL programs in C and COBOL. Based on our experience and feedback from the users, our team will continue to improve ECPG for enhanced usability.
Personally, I am very happy that my first assignment at Fujitsu has paid off - you can check my commit to the source code here
. In addition to ECPG, I look forward to improving scale-out capabilities of PostgreSQL with sharding.
Subscribe to be notified of future blog posts
If you would like to be notified of my next blog posts and other PostgreSQL-related articles, fill the form
here.
We also have a series of technical articles for PostgreSQL enthusiasts of all stripes, with tips and how-to's.
