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:
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.
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.
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.
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.
22 comments
[ 4.2 ms ] story [ 61.7 ms ] threadAside from that, I'm not convinced yet that this is much easier than using libpq. Is there an example of a bigger difference?
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.
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.
Oracle also has Pro*COBOL.
https://www.oracle.com/database/technologies/instant-client/...
Also ''=NULL.
</bitch>
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.
https://docs.oracle.com/en/database/oracle/oracle-database/2...
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.
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
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...