6 ms·

ECPG – Embedded SQL in C

37 points by hamilyon2 3y ago

hot_gril 3y ago
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 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?
- jsmith45 3y ago
  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.
  - hot_gril 3y ago
    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.
  - mananaysiempre 3y ago
    > 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.
    - cozzyd 3y ago
      But.. you can use stored procedures right?
  - chasil 3y ago
    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/precompiler-downloads.html https://www.oracle.com/database/technologies/instant-client/...
    - lmz 3y ago
      And SQLJ for Java should you want that: https://docs.oracle.com/database/121/JSQLJ/overview.htm#JSQLJ136 https://docs.oracle.com/database/121/JSQLJ/overview.htm#JSQL...
  - jpgvm 3y ago
    I will now use this opportunity to bitch about Oracle not supporting the BOOLEAN SQL type. Also ''=NULL. </bitch>
vivegi 3y ago
Ah! Brings back memories of Oracle Pro*C from decades ago...
- icedchai 3y 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.
  - zimpenfish 3y ago
    > 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.
- pjmlp 3y ago
  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/23/lnpcc/index.html https://docs.oracle.com/en/database/oracle/oracle-database/2...
rockwotj 3y ago
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.
codr7 3y ago
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 https://github.com/codr7/cl-redb
- UncleEntity 3y ago
  I was going to jokingly say “sounds like a job for a lisp macros”…
kazinator 3y ago
Last time I saw SQL syntax embedded in C was like in 1996 or something (Informix DB).
- mangamadaiyan 3y ago
  Same decade, except in my case it was Baan C :)
  - kazinator 3y ago
    Fast forwarding to today, that's what Rustaceans are trying to do. :)
    - mangamadaiyan 3y ago
      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 :)
- MissTake 3y ago
  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...
  - deleted 3y ago
    [deleted]
theknarf 3y ago
A bit like LINQ, only its a pre-processor pass instead of being integrated into the language like in C#.

37 points by hamilyon2 3y ago

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 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?

jsmith45 3y ago
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.
- hot_gril 3y ago
  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.
- mananaysiempre 3y ago
  > 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.
  - cozzyd 3y ago
    But.. you can use stored procedures right?
- chasil 3y ago
  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/precompiler-downloads.html https://www.oracle.com/database/technologies/instant-client/...
  - lmz 3y ago
    And SQLJ for Java should you want that: https://docs.oracle.com/database/121/JSQLJ/overview.htm#JSQLJ136 https://docs.oracle.com/database/121/JSQLJ/overview.htm#JSQL...
- jpgvm 3y ago
  I will now use this opportunity to bitch about Oracle not supporting the BOOLEAN SQL type. Also ''=NULL. </bitch>

vivegi 3y ago

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

icedchai 3y 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.
- zimpenfish 3y ago
  > 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.
pjmlp 3y ago
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/23/lnpcc/index.html https://docs.oracle.com/en/database/oracle/oracle-database/2...

rockwotj 3y ago

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.

codr7 3y ago

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 https://github.com/codr7/cl-redb

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

kazinator 3y ago

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

mangamadaiyan 3y ago
Same decade, except in my case it was Baan C :)
- kazinator 3y ago
  Fast forwarding to today, that's what Rustaceans are trying to do. :)
  - mangamadaiyan 3y ago
    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 :)
MissTake 3y ago
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...
- deleted 3y ago
  [deleted]

theknarf 3y ago

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