[HN Gopher] ECPG - Embedded SQL in C
___________________________________________________________________
ECPG - Embedded SQL in C
Author : hamilyon2
Score : 29 points
Date : 2023-04-17 08:51 UTC (1 days ago)
(HTM) web link (www.postgresql.org)
(TXT) w3m dump (www.postgresql.org)
| hot_gril wrote:
| 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?
| jsmith45 wrote:
| 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.
| jpgvm wrote:
| I will now use this opportunity to bitch about Oracle not
| supporting the BOOLEAN SQL type.
|
| Also ''=NULL.
|
| </bitch>
| hot_gril wrote:
| 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 wrote:
| > 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 wrote:
| But.. you can use stored procedures right?
| chasil wrote:
| 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/...
| lmz wrote:
| And SQLJ for Java should you want that: https://docs.oracle
| .com/database/121/JSQLJ/overview.htm#JSQL...
| rockwotj wrote:
| 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.
| vivegi wrote:
| Ah! Brings back memories of Oracle Pro*C from decades ago...
| pjmlp wrote:
| 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...
| icedchai wrote:
| 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 wrote:
| > 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.
| codr7 wrote:
| 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
| UncleEntity wrote:
| I was going to jokingly say "sounds like a job for a lisp
| macros"...
___________________________________________________________________
(page generated 2023-04-18 23:02 UTC)