ECPG – Embedded SQL in C | Hacker News Reader

ECPG – Embedded SQL in C

postgresql.org

37 pointshamilyon221 comments

I'm comparing this to libpq, the typical client library for interacting with Postgres in C. Never used either one (though libpq underpins the psycopg2 and node-pg I've used), so I might be naive here. Selecting a single row in libpq looks like:

    PGresult *res = PQexec(conn, "SELECT VERSION()");   
    if (PQresultStatus(res) != PGRES_TUPLES_OK) {
        // handle error
    }    
    char* verName = PQgetvalue(res, 0, 0);  // Gives null-terminated string.

vs with ECPG:

    char verName[100];  // ?
    EXEC SQL SELECT VERSION() INTO :verName;
    if (sqlca.sqlcode != ECPG_NO_ERROR) {
        // handle error
    }

It seems wrong that ECPG doesn't allocate a null-terminated string on the heap like libpq. Now you have to manage that yourself, and the intro code examples have buffer overrun risks. Am I missing something? Found this: https://postgrespro.com/list/thread-id/1910796

Aside from that, I'm not convinced yet that this is much easier than using libpq. Is there an example of a bigger difference?

This is honestly more of a feature that exists because the SQL standard specifies it, and supporting it can be useful for people porting code from another DB that uses a similar system. If you are not doing that, then it doesn't seem to provide enough value to be worth the headache of ading an additional preprocessor to your build chain. Not to mention, it likely messes with syntax highlighting, linters, etc.

Indeed, this (or the equivalent in other languages) is one of the main ways the SQL standard expects to be used! It get presented before more familiar approaches, and various wording and placement choices make it clear that the standard considers this embedded SQL approach more core to how it works than other approaches.

A design like libpq, ODBC, etc where the sql syntax needs to be parsed at runtime rather than compile time is considered "dynamic sql" by the standard, and the standard acts like it is less likely to be provided than this interface. Obviously that is hogwash.

This is one of many reasons (like backwards compatibility) that unlike most other programming languages very few SQL implementations make much effort towards faithfully implementing the standard. PostgreSQL actually puts a lot more effort towards implementing the standard than many other popular RDBMses. Postgres has fairly few places where they intentionally violate the standard and don't hope to fix things in the future, and several are fairly obscure, or othewise not likely to cause issues. While Oracle has MANY super common features not spec complaint with no plans to fix. Same with SQL Server. I'm not sufficiently familiar with MySQL/MariaDB to evaluate how closely it tracks the SQL standard. It seems to claim only minor deviations, but that may well be from not claiming conformance at all with features it has implemented but differently from the standard.

But what are you going to use this for besides Postgres? Nothing else adheres to the standard, and if it did, there'd be no point in using it over Postgres. In my experience, it's been fine to marry a particular DBMS and deal with code changes in the worst case that you have to switch, which would probably be a huge rework either way.

> A design like libpq, ODBC, etc where the sql syntax needs to be parsed at runtime rather than compile time is considered "dynamic sql" by the standard, and the standard acts like it is less likely to be provided than this interface. Obviously that is hogwash.

Yet the “static” variant sounds like a match made in heaven for optimizations that need to know the specific queries you are going to be making ahead of time, like Noria (Materialize.io, etc.). So maybe not so dumb after all, whatever its actual popularity.

But.. you can use stored procedures right?

To place a name on "another" DB, this is a reimplementation of Oracle Pro*C.

Oracle also has Pro*COBOL.

https://www.oracle.com/database/technologies/instant-client/...

And SQLJ for Java should you want that: https://docs.oracle.com/database/121/JSQLJ/overview.htm#JSQL...

I will now use this opportunity to bitch about Oracle not supporting the BOOLEAN SQL type.

Also ''=NULL.

</bitch>

Ah! Brings back memories of Oracle Pro*C from decades ago...

I was going to write the same thing! I took a course in college where we had to use Pro*C... I still have nightmares about it.

> Pro*C... I still have nightmares about it.

We had to use Pascal with Oracle embeddings (CS130 with Carol Goble if anyone from cs.man.ac.uk is reading.) Most peculiar it was.

I thought it would have been deprecated by now given the alternatives on the Oracle ecosystem, but nope, we can still use it.

https://docs.oracle.com/en/database/oracle/oracle-database/2...

A similar thing I'm familiar with and like is sqlc.dev, it forces writing SQL in another file, which is a win if you need multiple languages or want to lint the SQL separately.

SQLc is a compiler that translates your SQL statements into little RPC like functions with all the types translated for you. It has the added benefit of making sure you don't typo a table or column name, so you don't have to worry about that exploding at runtime.

This is a step in the wrong direction imho.

What I've been doing more and more is modelling relational concepts in whatever programming language I'm using atm [0]; tables, columns, keys, indexes etc; rather than littering my code with SQL.

[0] https://github.com/codr7/cl-redb

I was going to jokingly say “sounds like a job for a lisp macros”…

A bit like LINQ, only its a pre-processor pass instead of being integrated into the language like in C#.

Last time I saw SQL syntax embedded in C was like in 1996 or something (Informix DB).

Same decade, except in my case it was Baan C :)

Fast forwarding to today, that's what Rustaceans are trying to do. :)

I'm impressed! I never thought I'd bump into another soul who knew about Baan C. Then again, I shouldn't be surprised given the demographic of the people who hang out here :)

Many unhappy memories putting together a couponing system for Vons-Safeway in Informix Esql/C back in the late 1990s.

Might have been easier if I'd ever done C before; but since it was Informix, I had a pulse, and I was the only person on the bench at the time, I got volunteered that role.

Still got the scars to prove it...

Reply on news.ycombinator.com