I am using a little trick. H2 can emulate both PostgreSQL
SQL dialect and PostgreSQL network protocol.
As we all know then the available options are currently very limited....
But I just found a little trick to get one more.
:-)
$ write sys$output f$getsyi("VERSION")
V9.2-3
$ type TestPG.java
$ javac TestPG.java...
$ java -cp .:/javalib/postgresql-42_5_1.jar TestPG
1 A
2 BB
3 CCC
$ type test.c
$ cc /include=libpq$root:[include] /name=as_is test...
$ link test + libpq$root:[lib]libpq/libr + libpq$root:[lib]libgpgtypes/
libr + ssl111$lib:ssl111$libssl/libr + ssl111$lib:ssl111$libcrypto/libr
$ run test
1 A
2 BB
3 CCC
C:\IDEProjects\EclipsePHP\Test>type TestPDO.php
C:\IDEProjects\EclipsePHP\Test>php TestPDO.php
1 A
2 BB
3 CCC
I cannot use PHP on VMS yet as I don't have a PHP with pgsql and
pdo_pgsql. But given that libpq is ported, then those can probably
be build.
I cannot use PHP on VMS yet ...
So what is happening here?
PostgreSQL on VMS x86-64????
No. Sorry.
I am using a little trick. H2 can emulate both PostgreSQL
SQL dialect and PostgreSQL network protocol.
On 7/6/25 2:45 PM, Arne Vajhøj wrote:
So what is happening here?
PostgreSQL on VMS x86-64????
No. Sorry.
I am using a little trick. H2 can emulate both PostgreSQL
SQL dialect and PostgreSQL network protocol.
As well as others, apparently.
Did you have existing code, tooling, or experience that made the emulated route more attractive than just using
H2's own ODBC/JDBC interfaces?
On Sun, 6 Jul 2025 18:07:49 -0400, Arne Vajhøj wrote:
And embedded SQL without "FETCH cursorname INTO ..." is like a
hotdog without a hotdog sausage!
Never used FETCH before -- had to look it up. MariaDB/MySQL has it, SQLite doesn’t.
And embedded SQL without "FETCH cursorname INTO ..." is like a
hotdog without a hotdog sausage!
But in embedded SQL then it is the standard way to do queries.
And that PHP pgsql & pdo_pgsql and Python psychopg2
should be just a build problem.
On Sun, 6 Jul 2025 19:58:04 -0400, Arne Vajhøj wrote:
But in embedded SQL then it is the standard way to do queries.
Not sure what “embedded SQL” means. I normally use SQL “embedded” in an
app written in some other programming language.
On 7/6/25 5:57 PM, Arne Vajhøj wrote:
And that PHP pgsql & pdo_pgsql and Python psychopg2
should be just a build problem.
$ php -i
.
.
.
PDO
PDO support => enabled
PDO drivers => mysql, pgsql, sqlite
pdo_mysql
PDO Driver for MySQL => enabled
pdo_pgsql
PDO Driver for PostgreSQL => enabled
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
pgsql
pgsql.allow_persistent => On => On
pgsql.auto_reset_persistent => Off => Off
pgsql.ignore_notice => Off => Off
pgsql.log_notice => Off => Off
pgsql.max_links => Unlimited => Unlimited
pgsql.max_persistent => Unlimited => Unlimited
My PHP port has pgsql and pdo_pgsql support using the VSI libpq library.
On 7/7/2025 2:07 PM, Arne Vajhøj wrote:
On 7/6/2025 10:42 PM, Lawrence D'Oliveiro wrote:
On Sun, 6 Jul 2025 19:58:04 -0400, Arne Vajhøj wrote:
But in embedded SQL then it is the standard way to do queries.
Not sure what “embedded SQL” means. I normally use SQL “embedded” in an
app written in some other programming language.
Embedded SQL is a thing or was a thing 30-40-50 years ago.
Basically you write source code with SQL statements prefixed
by EXEC SQL, put it through a pre-compiler to get valid
code in whatever language (Cobol, PL/I, C or whatever).
I believe the available pre-compilers (database and language combos)
for VMS are:
Oracle DB - Cobol, C, Fortran [support is ended, will never be on x86-64] Oracle Rdb - Cobol, C, Pascal, Fortran [not on x86-64 yet but coming]
Mimer - Cobol, C, Fortran
? via SQLRelay - Cobol, C
any database with JDBC driver - Java [unsupported]
On 7/6/2025 10:42 PM, Lawrence D'Oliveiro wrote:
On Sun, 6 Jul 2025 19:58:04 -0400, Arne Vajhøj wrote:
But in embedded SQL then it is the standard way to do queries.
Not sure what “embedded SQL” means. I normally use SQL “embedded” in an
app written in some other programming language.
Embedded SQL is a thing or was a thing 30-40-50 years ago.
Basically you write source code with SQL statements prefixed
by EXEC SQL, put it through a pre-compiler to get valid
code in whatever language (Cobol, PL/I, C or whatever).
On 7/7/2025 10:50 AM, Mark Berryman wrote:
On 7/6/25 5:57 PM, Arne Vajhøj wrote:
And that PHP pgsql & pdo_pgsql and Python psychopg2
should be just a build problem.
$ php -i
.
.
.
PDO
PDO support => enabled
PDO drivers => mysql, pgsql, sqlite
pdo_mysql
PDO Driver for MySQL => enabled
pdo_pgsql
PDO Driver for PostgreSQL => enabled
pdo_sqlite
PDO Driver for SQLite 3.x => enabled
pgsql
pgsql.allow_persistent => On => On
pgsql.auto_reset_persistent => Off => Off
pgsql.ignore_notice => Off => Off
pgsql.log_notice => Off => Off
pgsql.max_links => Unlimited => Unlimited
pgsql.max_persistent => Unlimited => Unlimited
My PHP port has pgsql and pdo_pgsql support using the VSI libpq library.
I do use your PHP.
But I do not have pgsql and pdo_pgsql loaded.
$ pipe php -i | search sys$input php,version/match=and
PHP Version => 8.1.23
$ pipe php -i | search sys$input pdo
...
PDO
PDO support => enabled
PDO drivers => mysql
pdo_mysql
PDO Driver for MySQL => enabled
...
The funny thing is that unlike most other extensions
then PHP_PGSQL.EXE and PHP_PDO_PGSQL.EXE exist
as seperate files. But even if I try to load them in
php.ini, then nothing.
I must be missing something. Probably something basic,
but ...
On 7/7/25 12:01 PM, Arne Vajhøj wrote:
I do use your PHP.
But I do not have pgsql and pdo_pgsql loaded.
$ pipe php -i | search sys$input php,version/match=and
PHP Version => 8.1.23
$ pipe php -i | search sys$input pdo
...
PDO
PDO support => enabled
PDO drivers => mysql
pdo_mysql
PDO Driver for MySQL => enabled
...
The funny thing is that unlike most other extensions
then PHP_PGSQL.EXE and PHP_PDO_PGSQL.EXE exist
as seperate files. But even if I try to load them in
php.ini, then nothing.
I must be missing something. Probably something basic,
but ...
Some of the extensions were built as separate loadable modules either
due to their size or because they require separate optional libraries
that not everyone has. All that is necessary to load them is to have
the following in php_root:[000000]php.ini:
extension=php_pdo_pgsql.exe
extension=php_pgsql.exe
and to make sure that php.ini is readable by the account running php
(and, of course, for libpq from VSI to be installed).
On 7/7/2025 5:06 PM, Mark Berryman wrote:
On 7/7/25 12:01 PM, Arne Vajhøj wrote:
I do use your PHP.
But I do not have pgsql and pdo_pgsql loaded.
$ pipe php -i | search sys$input php,version/match=and
PHP Version => 8.1.23
$ pipe php -i | search sys$input pdo
...
PDO
PDO support => enabled
PDO drivers => mysql
pdo_mysql
PDO Driver for MySQL => enabled
...
The funny thing is that unlike most other extensions
then PHP_PGSQL.EXE and PHP_PDO_PGSQL.EXE exist
as seperate files. But even if I try to load them in
php.ini, then nothing.
I must be missing something. Probably something basic,
but ...
Some of the extensions were built as separate loadable modules either
due to their size or because they require separate optional libraries
that not everyone has. All that is necessary to load them is to have
the following in php_root:[000000]php.ini:
extension=php_pdo_pgsql.exe
extension=php_pgsql.exe
and to make sure that php.ini is readable by the account running php
(and, of course, for libpq from VSI to be installed).
First I did not have anything for pgsql in php.ini
and second I tried without php_ prefix.
Loading with php_ prefix and everything works!
Embedded SQL is a thing or was a thing 30-40-50 years ago.
Basically you write source code with SQL statements prefixed by EXEC
SQL, put it through a pre-compiler to get valid code in whatever
language (Cobol, PL/I, C or whatever).
Some of the extensions were built as separate loadable modules either
due to their size or because they require separate optional libraries
that not everyone has. All that is necessary to load them is to have
the following in php_root:[000000]php.ini:
extension=php_pdo_pgsql.exe
extension=php_pgsql.exe
On Mon, 7 Jul 2025 14:07:31 -0400, Arne Vajhøj wrote:
Embedded SQL is a thing or was a thing 30-40-50 years ago.
Basically you write source code with SQL statements prefixed by EXEC
SQL, put it through a pre-compiler to get valid code in whatever
language (Cobol, PL/I, C or whatever).
OK, so it was a horrible nonstandard hack invented for programming
languages like COBOL, which didn’t have the best ability to deal with dynamic strings, to make it easier for them to compose SQL statements.
And the horribleness of it is reinforced by your statement that you need server-side cursors in the DBMS to make this hack a little less difficult
to work with.
Not sure why it would be needed for PL/I, or even C.
I’ve pointed out before, the irony of COBOL being designed strictly for “business” needs, yet within a couple of decades of its widespread adoption, SQL databases became an important “business” need, but the best way of interfacing to them (using dynamic string manipulation mechanisms)
had been omitted from COBOL because it wasn’t seen as a “business” need.
On Mon, 7 Jul 2025 15:06:24 -0600, Mark Berryman wrote:
Some of the extensions were built as separate loadable modules either
due to their size or because they require separate optional libraries
that not everyone has. All that is necessary to load them is to have
the following in php_root:[000000]php.ini:
extension=php_pdo_pgsql.exe
extension=php_pgsql.exe
Why did VMS ever adopt the convention that shareable libraries would have
tne same .EXE extension as executables?
Maybe because they practically are the same?
... dynamic string manipulation is rarely used for database access. It
is a code smell.
On Mon, 7 Jul 2025 14:07:31 -0400, Arne Vajhøj wrote:
Embedded SQL is a thing or was a thing 30-40-50 years ago.OK, so it was a horrible nonstandard hack invented for programming
Basically you write source code with SQL statements prefixed by EXEC
SQL, put it through a pre-compiler to get valid code in whatever
language (Cobol, PL/I, C or whatever).
languages like COBOL, which didn’t have the best ability to deal with dynamic strings, to make it easier for them to compose SQL statements.
And the horribleness of it is reinforced by your statement that you need server-side cursors in the DBMS to make this hack a little less difficult
to work with.
Not sure why it would be needed for PL/I, or even C.
I’ve pointed out before, the irony of COBOL being designed strictly for “business” needs, yet within a couple of decades of its widespread adoption, SQL databases became an important “business” need, but the best way of interfacing to them (using dynamic string manipulation mechanisms)
had been omitted from COBOL because it wasn’t seen as a “business” need.
On Mon, 7 Jul 2025 19:21:09 -0400, Arne Vajhøj wrote:
Maybe because they practically are the same?
If they need to be distinguished, then they should have been given
different extensions.
On Mon, 7 Jul 2025 19:28:37 -0400, Arne Vajhøj wrote:
... dynamic string manipulation is rarely used for database access. It
is a code smell.
I posted examples some years ago in this group about how useful they are. Want to revisit those?
On 7/7/2025 4:07 PM, Lawrence D'Oliveiro wrote:
On Mon, 7 Jul 2025 14:07:31 -0400, Arne Vajhøj wrote:
Embedded SQL is a thing or was a thing 30-40-50 years ago.OK, so it was a horrible nonstandard hack invented for programming
Basically you write source code with SQL statements prefixed by EXEC
SQL, put it through a pre-compiler to get valid code in whatever
language (Cobol, PL/I, C or whatever).
languages like COBOL, which didn’t have the best ability to deal with
dynamic strings, to make it easier for them to compose SQL statements.
And the horribleness of it is reinforced by your statement that you need
server-side cursors in the DBMS to make this hack a little less difficult
to work with.
Not sure why it would be needed for PL/I, or even C.
As for embedded SQL being nonstandard, I think you might be interested
in this page:
https://www.iso.org/standard/84805.html
which contains this line:
ISO/IEC 9075-2 specifies embedded SQL for the programming languages:
Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I.
On 7/8/2025 4:14 AM, Louis Krupp wrote:
On 7/7/2025 4:07 PM, Lawrence D'Oliveiro wrote:
On Mon, 7 Jul 2025 14:07:31 -0400, Arne Vajhøj wrote:
Embedded SQL is a thing or was a thing 30-40-50 years ago.
Basically you write source code with SQL statements prefixed by EXEC
SQL, put it through a pre-compiler to get valid code in whatever
language (Cobol, PL/I, C or whatever).
OK, so it was a horrible nonstandard hack invented for programming
languages like COBOL, which didn’t have the best ability to deal with
dynamic strings, to make it easier for them to compose SQL statements.
And the horribleness of it is reinforced by your statement that you
need server-side cursors in the DBMS to make this hack a little less
difficult to work with.
As for embedded SQL being nonstandard, I think you might be interested
in this page:
https://www.iso.org/standard/84805.html
which contains this line:
ISO/IEC 9075-2 specifies embedded SQL for the programming languages:
Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I.
Note that while -2 covers native languages, then -10 cover Java.
I am surprised that they still maintain embedded SQL for Java.
SQLJ has been out of fashion for like 25 years.
On 7/7/2025 8:26 PM, Lawrence D'Oliveiro wrote:
On Mon, 7 Jul 2025 19:28:37 -0400, Arne Vajhøj wrote:
... dynamic string manipulation is rarely used for database access. It
is a code smell.
I posted examples some years ago in this group about how useful they
are. Want to revisit those?
You were also told how it should have been done in Python and how it
would be done in Cobol.
$ link main2 + sys$input/opt
main1shr/share
$
what bucket would you put main1.exe in?
On Tue, 8 Jul 2025 07:14:44 -0400, Arne Vajhøj wrote:
$ link main2 + sys$input/opt
main1shr/share
$
what bucket would you put main1.exe in?
Note that, on *nix systems, shareable libraries can be specified directly
on the command line, without having to go through options files. Why does
the VMS linker need options files for this common case?
On Tue, 8 Jul 2025 08:45:13 -0400, Arne Vajhøj wrote:
On 7/7/2025 8:26 PM, Lawrence D'Oliveiro wrote:
On Mon, 7 Jul 2025 19:28:37 -0400, Arne Vajhøj wrote:
... dynamic string manipulation is rarely used for database access. It >>>> is a code smell.
I posted examples some years ago in this group about how useful they
are. Want to revisit those?
You were also told how it should have been done in Python and how it
would be done in Cobol.
Except the alternative Python versions didn’t actually work.
I can’t decide whether there is actually widespread fear about the possibilities of dynamically-generated SQL, or just a lack of imagination.
On Tue, 8 Jul 2025 09:35:16 -0400, Arne Vajhøj wrote:
On 7/8/2025 4:14 AM, Louis Krupp wrote:
As for embedded SQL being nonstandard, I think you might be interested
in this page:
https://www.iso.org/standard/84805.html
which contains this line:
ISO/IEC 9075-2 specifies embedded SQL for the programming languages: >>> Ada, C, COBOL, Fortran, MUMPS, Pascal, and PL/I.
Note that while -2 covers native languages, then -10 cover Java.
I am surprised that they still maintain embedded SQL for Java.
SQLJ has been out of fashion for like 25 years.
Yes, but “out of fashion” or not, it’s still supposed to be an official standard according to ISO 9057, isn’t it? Otherwise we could apply the “out of fashion” argument to some or all of those other parts of the spec as well.
Standards evolve. They add lots of new stuff. And sometimes they remove
stuff that is not needed anymore.
Embedded SQL in native languages is still needed.
Dynamically creating SQL string where the dynamic part is for data is a security disaster waiting to happen (and possible poor performance as
well).
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 07:14:44 -0400, Arne Vajhøj wrote:
$ link main2 + sys$input/opt main1shr/share $
what bucket would you put main1.exe in?
Note that, on *nix systems, shareable libraries can be specified
directly on the command line, without having to go through options
files. Why does the VMS linker need options files for this common case?
/SHARE in command line means producing a shareable image.
/SHARE in option file means linking with a shareable image.
Different meaning based on context.
On Tue, 8 Jul 2025 18:20:50 -0400, Arne Vajhøj wrote:
Standards evolve. They add lots of new stuff. And sometimes they remove
stuff that is not needed anymore.
But those standards in particular have not evolved.
2023
Embedded SQL in native languages is still needed.
It is still just as clunky an idea as it was when first proposed.
On Tue, 8 Jul 2025 18:40:31 -0400, Arne Vajhøj wrote:
Dynamically creating SQL string where the dynamic part is for data is a
security disaster waiting to happen (and possible poor performance as
well).
That’s a pretty naïve statement to make.
Quoting literal data in standard SQL is quite simple: turn the data into a string literal with single quotation marks, and any embedded single
quotation marks are written twice. That’s it. Every other character can be represented as itself, literally.
On Tue, 8 Jul 2025 18:14:32 -0400, Arne Vajhøj wrote:
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
Note that, on *nix systems, shareable libraries can be specified
directly on the command line, without having to go through options
files. Why does the VMS linker need options files for this common case?
/SHARE in command line means producing a shareable image.
/SHARE in option file means linking with a shareable image.
Different meaning based on context.
Given that the VMS DCL syntax was specifically designed to allow a distinction between per-file qualifiers and global qualifiers, this seems like a strange limitation, don’t you think?
On 7/8/2025 7:38 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 18:40:31 -0400, Arne Vajhøj wrote:
Dynamically creating SQL string where the dynamic part is for data
is a security disaster waiting to happen (and possible poor
performance as well).
That’s a pretty naïve statement to make.
Quoting literal data in standard SQL is quite simple: turn the data
into a string literal with single quotation marks, and any embedded
single quotation marks are written twice. That’s it. Every other
character can be represented as itself, literally.
It is an assumption that all developers remember to do it right.
<quote>
Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied Input </quote>
Very few API's does not allow prepare/parameters ...
Because mysql extension did not support prepare/parameters
they first added a mysql_escape_string function to do what one
think should be done.
$s = mysql_escape_string($s);
But clever people found out that the argument list was
wrong.
error_reporting(E_ERROR);
On 7/8/2025 7:40 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 18:14:32 -0400, Arne Vajhøj wrote:
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
Note that, on *nix systems, shareable libraries can be specified
directly on the command line, without having to go through
options files. Why does the VMS linker need options files for
this common case?
/SHARE in command line means producing a shareable image.
/SHARE in option file means linking with a shareable image.
Different meaning based on context.
Given that the VMS DCL syntax was specifically designed to allow a
distinction between per-file qualifiers and global qualifiers, this
seems like a strange limitation, don’t you think?
Not really.
First I don't think a verb qualifier and a per-file qualifier with
same name will work well.
Second even if it did work, then I think it would be confusing to
have the qualifier in a command line with two different meanings.
On 7/8/2025 7:37 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 18:20:50 -0400, Arne Vajhøj wrote:
Standards evolve. They add lots of new stuff. And sometimes they
remove stuff that is not needed anymore.
But those standards in particular have not evolved.
SQL standard has evolved. Lot of stuff has been added.
First I don't think a verb qualifier and a per-file qualifier with same
name will work well.
On 7/9/25 04:18, Arne Vajhøj wrote:
First I don't think a verb qualifier and a per-file qualifier with same
name will work well.
Depends on what you mean with "well". It works and it is used: see
$ HELP SEARCH /KEY
... and documented in the Utility Routines Manual.
If you do not want to use an option, you can (create and) add your
shareable image to a shareable image library. Such a library can be
used on the command line - with the /LIBRARY qualifier.
$ duo 1.txt/q,2.txt,3.txt/q
Q=notpresent
1.txt Q=present
2.txt Q=notpresent
3.txt Q=present
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=present
1.txt Q=present
2.txt Q=present
3.txt Q=present
Where:
2.txt Q=present
is not what was intended.
On 7/9/25 16:13, Arne Vajhøj wrote:
...
$ duo 1.txt/q,2.txt,3.txt/q
Q=notpresent
1.txt Q=present
2.txt Q=notpresent
3.txt Q=present
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=present
1.txt Q=present
2.txt Q=present
3.txt Q=present
Where:
2.txt Q=present
is not what was intended.
If you look at the return code and not only check for success, you get
the information that a qualifier is global or local With a small C
program, I get
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=global
1.txt:Q=local
2.txt:Q=global
3.txt:Q=local
On 7/9/2025 12:51 PM, hb0815 wrote:
On 7/9/25 16:13, Arne Vajhøj wrote:
...
$ duo 1.txt/q,2.txt,3.txt/q
Q=notpresent
1.txt Q=present
2.txt Q=notpresent
3.txt Q=present
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=present
1.txt Q=present
2.txt Q=present
3.txt Q=present
Where:
2.txt Q=present
is not what was intended.
If you look at the return code and not only check for success, you get
the information that a qualifier is global or local With a small C
program, I get
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=global
1.txt:Q=local
2.txt:Q=global
3.txt:Q=local
Ah. There is a way. Not using path but via return status.
$ type duo.cld
define verb duo
image "sys$disk:[]duo"
parameter p1, value(type=$file, list, required)
qualifier q, placement=positional
$ type duo.pas [inherit('sys$library:pascal$cli_routines','sys$library:starlet')]
program duo(input,output);
type
pstr = varying [255] of char;
var
fnm, a, b : pstr;
begin
if cli$present('Q') = CLI$_PRESENT then begin
writeln('Q=present')
end else begin
writeln('Q=notpresent')
end;
while odd(cli$get_value('P1', fnm.body, fnm.length)) do begin
write(fnm);
if cli$present('Q') = CLI$_LOCPRES then begin
write(' Q=present')
end else begin
write(' Q=notpresent')
end;
writeln;
end;
end.
$ set command duo
$ pas duo
$ link duo
$ duo 1.txt/q,2.txt,3.txt/q
Q=notpresent
1.txt Q=present
2.txt Q=notpresent
3.txt Q=present
$ duo/q 1.txt/q,2.txt,3.txt/q
Q=present
1.txt Q=present
2.txt Q=notpresent
3.txt Q=present
I am still not keen on the approach of the same
qualifier having different meaning for verb and
parameters though.
Arne
I am still not keen on the approach of the same
qualifier having different meaning for verb and
parameters though.
On Tue, 8 Jul 2025 21:54:20 -0400, Arne Vajhøj wrote:
On 7/8/2025 7:38 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 18:40:31 -0400, Arne Vajhøj wrote:
Dynamically creating SQL string where the dynamic part is for data
is a security disaster waiting to happen (and possible poor
performance as well).
That’s a pretty naïve statement to make.
Quoting literal data in standard SQL is quite simple: turn the data
into a string literal with single quotation marks, and any embedded
single quotation marks are written twice. That’s it. Every other
character can be represented as itself, literally.
It is an assumption that all developers remember to do it right.
<quote>
Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied Input
</quote>
Unfortunately, you often have no choice.
Very few API's does not allow prepare/parameters ...
None of them include support for all the necessary cases.
Because mysql extension did not support prepare/parameters
they first added a mysql_escape_string function to do what one
think should be done.
$s = mysql_escape_string($s);
But clever people found out that the argument list was
wrong.
That was just the usual PHP brain damage. Others were able to do it
right from the beginning.
error_reporting(E_ERROR);
Here’s another example of PHP brain damage: the fact that reporting
SQL errors is *optional*!
On 7/9/2025 3:25 AM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 21:54:20 -0400, Arne Vajhøj wrote:
<quote>
Defense Option 4: STRONGLY DISCOURAGED: Escaping All User-Supplied
Input </quote>
Unfortunately, you often have no choice.
You practically always have a choice.
Your escape function does not have database connection either.
Reporting of SQL errors is not optional in PHP.
On Wed, 9 Jul 2025 15:33:50 -0400, Arne Vajhøj wrote:
Reporting of SQL errors is not optional in PHP.
If it wasn’t “optional”, why would you need to enable it via the “error_reporting()” function?
On Tue, 8 Jul 2025 20:31:37 -0400, Arne Vajhøj wrote:
On 7/8/2025 7:37 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 18:20:50 -0400, Arne Vajhøj wrote:
Standards evolve. They add lots of new stuff. And sometimes they
remove stuff that is not needed anymore.
But those standards in particular have not evolved.
SQL standard has evolved. Lot of stuff has been added.
But nothing new in EXEC SQL. For example, did they offer any
equivalent to the following utility functions?
def sql_string_list(the_list) :
"returns a list containing the quoted items of the_list, suitable" \
" for use in an “in” clause."
return \
"(" + ", ".join([sql_string(s) for s in the_list]) + ")"
#end sql_string_list
def escape_sql_wild(s, escch) :
"escapes SQL pattern wildcards in s with escch. The same escch needs" \
" to be passed to the ESCAPE clause for the LIKE operator."
if not isinstance(s, str) :
raise TypeError("expecting s to be a string")
#end if
if not isinstance(escch, str) or len(escch) != 1 :
raise TypeError("expecting escch to be a single-character string")
#end if
result = []
for ch in s :
if ch == escch or ch == "%" or ch == "_" :
result.append(escch)
#end if
result.append(ch)
#end for
return "".join(result)
#end escape_sql_wild
def escape_sql_name(n, escch = "\"") :
"converts n to escaped form to avoid potential conflicts with SQL keywords" \
" and other syntax errors (e.g. from embedded spaces). escch should be “\"”" \
" as per the SQL standard."
assert len(escch) == 1
out = []
for i, split1 in enumerate(n.split(escch)) :
if i != 0 :
out.append(escch * 2)
#end if
out.append(split1)
#end for
return escch + "".join(out) + escch
#end escape_sql_name
I would write your code a little simpler:
$ type dyn.py
import sqlite3
def esc(s):
return "''".join(s.split("'"))
def q(s):
return f"'{s}'"
def dump_range(con, vals):
jvals = ",".join(q(esc(val)) for val in vals)
c = con.cursor()
c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jvals})")
for row in c.fetchall():
print('%d %s' % (row[0], row[1]))
with sqlite3.connect('test.db') as con:
dump_range(con, ['A', 'CCC', 'EEEEE'])
dump_range(con, ['BB', 'DDDD'])
$ python dyn.py
1 A
3 CCC
5 EEEEE
2 BB
4 DDDD
But it is both simpler and safer to use dynamic SQL
with just dynamic non-data:
$ type sta.py
import sqlite3
def dump_range(con, vals):
jparm = ",".join('?' for val in vals)
c = con.cursor()
c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jparm})", vals)
for row in c.fetchall():
print('%d %s' % (row[0], row[1]))
with sqlite3.connect('test.db') as con:
dump_range(con, ['A', 'CCC', 'EEEEE'])
dump_range(con, ['BB', 'DDDD'])
$ python sta.py
1 A
3 CCC
5 EEEEE
2 BB
4 DDDD
I would write your code a little simpler:
def dump_range(con, vals):
jvals = ",".join(q(esc(val)) for val in vals)
c = con.cursor()
c.execute(f"SELECT f1,f2 FROM t1 WHERE f2 IN ({jvals})")
for row in c.fetchall():
print('%d %s' % (row[0], row[1]))
But dynamic SQL with dynamic data is still bad.
zxJDBC.Error: Literals of this kind are not allowed; SQL statement:
SELECT f1,f2 FROM t1 WHERE f2 IN ('A','CCC','EEEEE') [90116-220]
[SQLCode: 90116], [SQLState: 90116]
error_reporting(E_ERROR) is not need to get SQL errors - it is to avoid getting warnings about mysql extension being obsolete.
On Wed, 9 Jul 2025 20:25:06 -0400, Arne Vajhøj wrote:
zxJDBC.Error: Literals of this kind are not allowed; SQL statement:
SELECT f1,f2 FROM t1 WHERE f2 IN ('A','CCC','EEEEE') [90116-220]
[SQLCode: 90116], [SQLState: 90116]
What happens if you really want literals in your SQL code?
On Wed, 9 Jul 2025 19:51:30 -0400, Arne Vajhøj wrote:
error_reporting(E_ERROR) is not need to get SQL errors - it is to avoid
getting warnings about mysql extension being obsolete.
I think you need
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
in order to get error exceptions raised about incorrect MySQL. Sensible languages would do this by default.
There are fundamentally two different ways of handling errors:
* return status
* exception
On 7/9/2025 9:35 PM, Lawrence D'Oliveiro wrote:
On Wed, 9 Jul 2025 20:25:06 -0400, Arne Vajhøj wrote:
zxJDBC.Error: Literals of this kind are not allowed; SQL statement:
SELECT f1,f2 FROM t1 WHERE f2 IN ('A','CCC','EEEEE') [90116-220]
[SQLCode: 90116], [SQLState: 90116]
What happens if you really want literals in your SQL code?
You don't.
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 07:14:44 -0400, Arne Vajhøj wrote:
$ link main2 + sys$input/opt
main1shr/share
$
what bucket would you put main1.exe in?
Note that, on *nix systems, shareable libraries can be specified directly
on the command line, without having to go through options files. Why does
the VMS linker need options files for this common case?
See above... "on *nix systems"
He doesn't care about VMS. STOP FEEDING HE TROLL!!!!!
On 7/9/2025 3:55 PM, hb0815 wrote:
For linking with a single shareable, there is also a one-liner like:
$ pipe write sys$output "s/share" | link m,sys$pipe/opt
Clever.
But not a style I would recommend.
When I read it then I wondered why you used SYS$PIPE and
not SYS$INPUT.
They can be different but I don't think they are in this case.
For linking with a single shareable, there is also a one-liner like:
$ pipe write sys$output "s/share" | link m,sys$pipe/opt
On Wed, 9 Jul 2025 22:26:19 -0400, Arne Vajhøj wrote:
On 7/9/2025 9:35 PM, Lawrence D'Oliveiro wrote:
On Wed, 9 Jul 2025 20:25:06 -0400, Arne Vajhøj wrote:
zxJDBC.Error: Literals of this kind are not allowed; SQL statement:
SELECT f1,f2 FROM t1 WHERE f2 IN ('A','CCC','EEEEE') [90116-220]
[SQLCode: 90116], [SQLState: 90116]
What happens if you really want literals in your SQL code?
You don't.
Seems like somebody is really, really scared of the possibilities offered
by being able to embed one language inside another.
Just because PHP programmers can’t get their heads around it, doesn’t mean the rest of us have to be tied down to the same mental level.
On Wed, 9 Jul 2025 22:24:50 -0400, Arne Vajhøj wrote:
There are fundamentally two different ways of handling errors:
* return status
* exception
There’s a difference between different statuses that might be returned as part of the normal execution of an operation, and unexpected conditions indicating program bugs.
Syntax errors in the embedded language would almost always belong in the latter category.
But there are some incompatibilities between H2 in
PostgreSQL mode and real PostgreSQL.
And one of them is that H2 in PostgreSQL mode
does not support server side cursors.
And embedded SQL without "FETCH cursorname INTO ..."
is like a hotdog without a hotdog sausage!
On 7/10/2025 1:48 AM, Lawrence D'Oliveiro wrote:
Seems like somebody is really, really scared of the possibilities
offered by being able to embed one language inside another.
Embedded SQL, call API with static SQL, call API with dynamic SQL only dynamic non-data and call API with dynamic SQL and dynamic data all
embed SQL in Cobol/C/Java/PHP/Python/whatever.
Even ORM's tend to embed a language. Either SQL or something ORM
specific: EJBQL, HQL, JPQL, JDOQL, JDQL, DQL etc..
So nobody seems afraid of embedding SQL.
The style you propose match pretty well how PHP developers did things
back in the 00's. They learned that it was not good.
In article <[email protected]>,
bill <[email protected]> wrote:
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 07:14:44 -0400, Arne Vajhøj wrote:
$ link main2 + sys$input/opt
main1shr/share
$
what bucket would you put main1.exe in?
Note that, on *nix systems, shareable libraries can be specified directly >>> on the command line, without having to go through options files. Why does >>> the VMS linker need options files for this common case?
See above... "on *nix systems"
He doesn't care about VMS. STOP FEEDING HE TROLL!!!!!
I think that Arne may be incapable of that. He seems unable to
refrain from engaging, and clearly asking him to stop for the
benefit of other readers has been ineffective thus far.
Perhaps the best thing is to just plonk any part of a thread
where someone responds to the troll, whether the responder is
Arne or anyone else. I have no reason to believe that asking
Arne again is going to yield a different result.
PHP give you the choice of which you want (for mysqli and pdo mysql
and many other).
$ type err1.php
<?php
$con = mysqli_connect('arnepc5', 'arne', 'hemmeligt', 'test'); mysqli_report(MYSQLI_REPORT_OFF);
$stmt = mysqli_prepare($con, 'SELECT the data needed');
if(!$stmt) {
echo mysqli_error($con) . "\r\n";
goto closecon;
}
// whatever
closestmt: mysqli_stmt_close($stmt);
closecon: mysqli_close($con);
$ php err1.php
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'needed'
at line 1
$ type err2.php
<?php
$con = mysqli_connect('arnepc5', 'arne', 'hemmeligt', 'test'); mysqli_report(MYSQLI_REPORT_ALL);
try {
$stmt = mysqli_prepare($con, 'SELECT the data needed');
// whatever
mysqli_stmt_close($stmt);
mysqli_close($con);
} catch (mysqli_sql_exception $ex) {
echo $ex->getMessage() . "\r\n";
mysqli_close($con);
}
$ php err2.php
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'needed'
at line 1
Default in recent PHP versions is exceptions.
If the PHP developer configure the database connection to not throw exceptions because the developer want to check return status, then that
must be respected.
On Thu, 10 Jul 2025 19:05:42 -0400, Arne Vajhøj wrote:
If the PHP developer configure the database connection to not throw
exceptions because the developer want to check return status, then that
must be respected.
That’s a dumb way of doing it. Most of the errors from an SQL call
indicate program bugs. The only exception I have come across is IntegrityError, when used to indicate an attempt to add a record with a duplicate value for a key that must be unique. That can sometimes be
usefully caught and reported back to the user as an error with the data
that they are trying to enter.
On Thu, 10 Jul 2025 20:11:59 -0400, Arne Vajhøj wrote:
On 7/10/2025 7:58 PM, Lawrence D'Oliveiro wrote:
On Thu, 10 Jul 2025 19:05:42 -0400, Arne Vajhøj wrote:
If the PHP developer configure the database connection to not
throw exceptions because the developer want to check return
status, then that must be respected.
That’s a dumb way of doing it. Most of the errors from an SQL call
indicate program bugs. The only exception I have come across is
IntegrityError, when used to indicate an attempt to add a record
with a duplicate value for a key that must be unique. That can
sometimes be usefully caught and reported back to the user as an
error with the data that they are trying to enter.
There are other:
* dynamic SQL without prepare/parameters and a bad value
* foreign key constraint violation
* invalid data value
* transaction rollback due to timeout or deadlock
* timeout waiting for connection due to connection pool at max
* timeout waiting for connection due to connection at max in database
* no connectivity to server
* invalid login credentials
Again, all of that can be returned via exceptions, which I can
selectively catch as appropriate if they’re relevant to my program
logic, or leave them to be reported as program bugs if I’m not
expecting them.
(and yes - everybody should use PDO instead of mysqli!)
$con = new PDO('mysql:host=arnepc5;dbname=test', 'arne', 'hemmeligt'); $con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_SILENT);
$stmt = $con->prepare('SELECT the data needed');
if(!$stmt) {
echo $con->errorInfo()[2] . "\r\n";
goto finish;
}
if(!$stmt->execute()) {
echo $stmt->errorInfo()[2] . "\r\n";
goto finish;
}
On 7/10/2025 7:58 PM, Lawrence D'Oliveiro wrote:
On Thu, 10 Jul 2025 19:05:42 -0400, Arne Vajhøj wrote:
If the PHP developer configure the database connection to not
throw exceptions because the developer want to check return
status, then that must be respected.
That’s a dumb way of doing it. Most of the errors from an SQL call
indicate program bugs. The only exception I have come across is
IntegrityError, when used to indicate an attempt to add a record
with a duplicate value for a key that must be unique. That can
sometimes be usefully caught and reported back to the user as an
error with the data that they are trying to enter.
There are other:
* dynamic SQL without prepare/parameters and a bad value
* foreign key constraint violation
* invalid data value
* transaction rollback due to timeout or deadlock
* timeout waiting for connection due to connection pool at max
* timeout waiting for connection due to connection at max in database
* no connectivity to server
* invalid login credentials
And in PHP (at least for the most common database extensions)
the developer have the choice - do they want exceptions or do
they want to test on the return status. Everybody should be happy.
On Thu, 10 Jul 2025 20:23:08 -0400, Arne Vajhøj wrote:
And in PHP (at least for the most common database extensions)
the developer have the choice - do they want exceptions or do
they want to test on the return status. Everybody should be happy.
Except the default is not to raise exceptions.
Python makes it easy to catch exceptions. Not only that, it makes it easy
to filter the ones you want to catch, by a) subclassing so you can be more specific, and b) if even that is not enough, you can query parameters of
the exception object and do a “raise” statement without arguments to pass it on if your handler doesn’t want to handle it.
On Thu, 10 Jul 2025 19:14:13 -0400, Arne Vajhøj wrote:
Even ORM's tend to embed a language. Either SQL or something ORM
specific: EJBQL, HQL, JPQL, JDOQL, JDQL, DQL etc..
ORMs are a waste of time. I’ve seen how they deal with complex queries -- it’s just re-expressing the complexity of SQL in another equally complex form.
Is it bad to offer users dynamic query capabilities?
Here’s some code I wrote back in Python-2 days (MySQL-specific):
# collect list of items matching specified search criteria
condition = \
(
list
( # free-text fields
"%(name)s like %(value)s"
%
{
"name" : field[0],
"value" :
SQLString("%" + EscapeSQLWild(Params.getvalue(field[1])) + "%"),
}
for field in
(
("make", "search_make"),
("model", "search_model"),
("details", "search_details"),
("serial_nr", "search_serial"),
("inventory_nr", "search_invent"),
)
if Params.getvalue(field[1]) != ""
)
+
list
( # exact-match fields
"%(name)s = %(value)s"
%
{
"name" : field[0],
"value" : SQLString(Params.getvalue(field[1])),
}
for field in
(
("class_name", "search_class"),
("allocation", "search_allocated"),
("location_name", "search_location"),
)
if Params.getvalue(field[1]) != ""
)
+
list
( # date fields
"("
+
" or ".join
(
"%(name)s %(op)s %(value)s"
%
{
"name" : field[0],
"op" : op[0],
"value" : SQLString(Params.getvalue(field[1])),
}
for op in
(
("<", "lt"),
("=", "eq"),
(">", "gt"),
)
if GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op[1]})
)
+
")"
for field in
(
("when_purchased", "search_when_purchased"),
("warranty_expiry", "search_warranty_expiry"),
)
if reduce
(
operator.__or__,
(
GetCheckbox("%(name)s[%(op)s]" % {"name" : field[1], "op" : op})
for op in ("lt", "eq", "gt")
)
)
)
)
condition = " and ".join(condition)
used as
select «fields» from «table» where «condition»
Let’s see your EXEC SQL cope with that ...
On 7/10/2025 8:00 AM, Dan Cross wrote:
In article <[email protected]>,
bill <[email protected]> wrote:
On 7/8/2025 5:58 PM, Lawrence D'Oliveiro wrote:
On Tue, 8 Jul 2025 07:14:44 -0400, Arne Vajhøj wrote:
$ link main2 + sys$input/opt
main1shr/share
$
what bucket would you put main1.exe in?
Note that, on *nix systems, shareable libraries can be specified directly >>>> on the command line, without having to go through options files. Why does >>>> the VMS linker need options files for this common case?
See above... "on *nix systems"
He doesn't care about VMS. STOP FEEDING HE TROLL!!!!!
I think that Arne may be incapable of that. He seems unable to
refrain from engaging, and clearly asking him to stop for the
benefit of other readers has been ineffective thus far.
Perhaps the best thing is to just plonk any part of a thread
where someone responds to the troll, whether the responder is
Arne or anyone else. I have no reason to believe that asking
Arne again is going to yield a different result.
You are free to plonk all the threads you want to plonk.
And you are also free to post about about plonking this and that.
I would recommend that you do not post about it as it is
useless content for other.
Except if you run a VMS based newsreader and need help on
how to plonk. That would of course be a totally relevant topic.
But I have a strong feeling that is not the case.
But maybe consider this: when people read these threads
in 10 or 20 or 30 years, then what do you think people
will find most useful:
A) your rant(s) about Lawrence and talk about plonking
B) this subthread covering topics like: syntax for linking
shareable images vs syntax for linking with shareable images,
use of CLI$PRESENT return code to distinguish between
qualifier on verb vs on parameter, difference between
SYS$PIPE and SYS$INPUT when using PIPE command
?
The ability to subclass exceptions to catch specific and the ability
to rethrow exceptions are quite common. Not Python specific at all.
Java use sub-classing extensively for database. A little simplified:
|->SQLNonTransientException->non transient exception classses SQLException-|
|->SQLTransientException->transient exception classses
On 7/10/2025 3:50 PM, Arne Vajhøj wrote:
On 7/9/2025 3:55 PM, hb0815 wrote:
For linking with a single shareable, there is also a one-liner like:
$ pipe write sys$output "s/share" | link m,sys$pipe/opt
Clever.
But not a style I would recommend.
When I read it then I wondered why you used SYS$PIPE and
not SYS$INPUT.
They can be different but I don't think they are in this case.
And if someone want to see a case where they are different,
then this weird DCL does:
...
On 7/10/25 21:52, Arne Vajhøj wrote:
On 7/10/2025 3:50 PM, Arne Vajhøj wrote:
They can be different but I don't think they are in this case.
And if someone want to see a case where they are different,
then this weird DCL does:
...
As you probably know, the difference is documented. One example is:
$ help/noprompt/nopage PIPE Description Pipelines_and_TEEs
Using_TEEs_and_SYS
Most USENET newsgroups used to have an FAQ; it may be useful to restart
that for comp.os.vms (among others), perhaps with a section on
well-known trolls.
You can save a lot of code by using ORM.
Example (Groovy and JPA):
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN
FETCH o.orderLines ol", OrdersJPA.class).getResultList()
Gives you a list of order objects that each has a list of order lines objects.
On Thu, 10 Jul 2025 20:29:56 -0400, Arne Vajhøj wrote:
You can save a lot of code by using ORM.
Example (Groovy and JPA):
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN
FETCH o.orderLines ol", OrdersJPA.class).getResultList()
Gives you a list of order objects that each has a list of order lines
objects.
I thought the whole point of an ORM was to get away from having to hand- construct SQL queries. And yet you have done exactly that.
The main point of ORM is to avoid boilerplate code like iterating and
result sets and stuff them into the object data structure.
Some ORM's operate with a dirty concept and automatically save changed objects.
Some ORM's provide identical syntax for doing things even though the underlying databases have different SQL syntax for it, which help making
the application more portable.
On Fri, 11 Jul 2025 19:43:59 -0400, Arne Vajhøj wrote:
The main point of ORM is to avoid boilerplate code like iterating and
result sets and stuff them into the object data structure.
Those are just up to the DB API, taking advantage of features of the language. Like the query iterator I posted elsewhere.
The point of the ORM is supposed to be to get away from SQL altogether,
and let you operate in terms of language-native objects.
Some ORM's operate with a dirty concept and automatically save changed
objects.
How do you map between OO objects and SQL tables?
That’s called the “impedance mismatch”, which tends to cause more problems than it solves.
Some ORM's provide identical syntax for doing things even though the
underlying databases have different SQL syntax for it, which help making
the application more portable.
More commonly you want to support different programming languages
accessing the same back-end DBMS containing the common enterprise-wide
data, rather than move a program between different back-end DBMSes. ORMs
are language-specific, so they don’t help with that.
But the ORM makes it completely go away.
ORM cannot read minds.
Somehow the code need to tell the ORM what query to make.
20 years ago there were some pain points. But solution has been found
since then.
On 2025-07-10 12:00:31 +0000, Dan Cross said:
Most USENET newsgroups used to have an FAQ; it may be useful to restart
that for comp.os.vms (among others), perhaps with a section on
well-known trolls.
Having some familiarity with the topic, I can assure you that a FAQ
will get ignored. But if you want to post your own FAQ, by all means
have at.
For your concerns here, you can plonk the posters that are assisting
with getting unwanted posters and postings past others' existing
filters.
And if your particular newsreader has more modern filtering
capabilities, there can be other options.
On 7/11/2025 7:35 PM, Lawrence D'Oliveiro wrote:
On Thu, 10 Jul 2025 20:29:56 -0400, Arne Vajhøj wrote:
You can save a lot of code by using ORM.
Example (Groovy and JPA):
jpadata = em.createQuery("SELECT DISTINCT o FROM OrdersJPA AS o JOIN
FETCH o.orderLines ol", OrdersJPA.class).getResultList()
Gives you a list of order objects that each has a list of order lines
objects.
I thought the whole point of an ORM was to get away from having to hand-
construct SQL queries. And yet you have done exactly that.
That is not SQL but HQL.
:-)
On Fri, 11 Jul 2025 20:48:22 -0400, Arne Vajhøj wrote:
But the ORM makes it completely go away.
And yet ..
ORM cannot read minds.
Somehow it manages to be both: it can make the need for low-level SQL management go away, and yet it cannot figure out what language-native
objects you want to replace it with.
Somehow the code need to tell the ORM what query to make.
If you knew what query to make, you wouldn’t need the ORM.
20 years ago there were some pain points. But solution has been found
since then.
I would like to see what some such “solution” might be, given you haven’t
offered one yet.
How about tackling that web query-form example I posted elsewhere? The one with the long Python expression for packaging up all the fields with data that the user has entered into an appropriate SQL WHERE clause?
JVM languages (Java, Groovy, Jython etc.) -----------------------------------------
No problem.
JPA API and Hibernate works fine.
You will need different versions on Alpha (Java 5) and
Itanium/x86-64 (Java 8).
I have posted list of jar files before.
What the ORM does (as the name implies!) is to handle the mapping
between the object model and the SQL statements.
You give it a query and it translate that into one or more SELECT
statements, read the result and stuff it into the object model.
You tell it to persist an object and it translate that into one
or more INSERT statements.
For complex object models having that stuff done automatically
save a ton of code.
It is cutting 50-75% of the database access code. And most business applications has a lot of database access code.
It is not a silver bullet. There are a couple of caveats.
1) It only makes sense if you have an object model. If your code
operate on lists, maps/dictionaries and basic data types, then
there is no point in ORM.
2) There need to be at least one on the team with more than
basic skills in the ORM. Any developer can do the queries
and updates. But putting the annotations/attributes (or the
equivalent XML mapping for older ORM) on the domain classes
require deeper skills for any non-trivial case.
You have misunderstood what an ORM provide.
You need to define your object model.
You need to specify logical what queries or updates you want to do.
What the ORM does (as the name implies!) is to handle the mapping
between the object model and the SQL statements.
You give it a query and it translate that into one or more SELECT
statements, read the result and stuff it into the object model.
For complex object models having that stuff done automatically save a
ton of code.
It is cutting 50-75% of the database access code.
And most business applications has a lot of database access code.
1) It only makes sense if you have an object model. If your code
operate on lists, maps/dictionaries and basic data types, then there
is no point in ORM.
2) There need to be at least one on the team with more than
basic skills in the ORM.
I would like to see what some such “solution” might be, given you
haven’t offered one yet.
How about tackling that web query-form example I posted elsewhere? The
one with the long Python expression for packaging up all the fields
with data that the user has entered into an appropriate SQL WHERE
clause?
Query WHERE conditions are handled the same way in SQL and various ORM
query constructs.
You have misunderstood what an ORM provide.
You need to define your object model.
You need to specify logical what queries or updates you want to do.
What the ORM does (as the name implies!) is to handle the mapping
between the object model and the SQL statements.
You give it a query and it translate that into one or more SELECT
statements, read the result and stuff it into the object model.
You tell it to persist an object and it translate that into one or more INSERT statements.
For complex object models having that stuff done automatically save a
ton of code.
It is cutting 50-75% of the database access code. And most business applications has a lot of database access code.
On Wed, 16 Jul 2025 20:26:46 -0400, Arne Vajhøj wrote:
You have misunderstood what an ORM provide.
You need to define your object model.
You need to specify logical what queries or updates you want to do.
What the ORM does (as the name implies!) is to handle the mapping
between the object model and the SQL statements.
So, if I leave out the ORM, then I don’t need to “define” an “object model” that it can handle, and just go straight to the queries/updates. Sounds like less work, to me.
You give it a query and it translate that into one or more SELECT
statements, read the result and stuff it into the object model.
I’ve seen ORM calls corresponding to more complex queries, like joins. Basically you are just expressing the join in a separate language of equal complexity, with no effort saved that I could see.
For complex object models having that stuff done automatically save a
ton of code.
It is cutting 50-75% of the database access code.
And just replacing it with a lot of ORM code.
And most business applications has a lot of database access code.
I maintain one sizeable application for a client that was written in PHP
(by a previous developer). They tried to wrap table information in custom classes representing things like employees, work entries, customer
companies and company contacts. The code was just a convoluted mess,
adding complexity instead of removing it. In every page representing some database query/update function that I needed to make major changes to, I saved code by getting rid of use of these classes and substituting the queries and handling the results directly.
1) It only makes sense if you have an object model. If your code
operate on lists, maps/dictionaries and basic data types, then there
is no point in ORM.
But surely just about all code fits that description.
2) There need to be at least one on the team with more than
basic skills in the ORM.
And if you have different apps written in different languages, all
accessing a common database (a very common situation), you need someone
with ORM skills in every language.
I repeat: the saving is not in the query, but in the mapping from the
query result to the object model.
On Mon, 28 Jul 2025 19:28:51 -0400, Arne Vajhøj wrote:
I repeat: the saving is not in the query, but in the mapping from the
query result to the object model.
But I thought a key point of ORMs is hiding the SQL.
Look at SQLAlchemy,
for example <https://docs.sqlalchemy.org/en/20/orm/quickstart.html>, which does a lot of this. Is that a bad example of an ORM?
Even with ORM you need to express the query.
JPQL example (ORM specific query language):
List<T1> res = em.createQuery("SELECT o FROM T1 AS o WHERE o.f > 0", T1.class).getResultList();
The big difference in the above two examples compared to traditional
SQL API (JDBC and ADO.NET for Java and .NET) is that it return a
List<T1> instead of a generic result like List<Object[]>.
On Mon, 28 Jul 2025 22:36:59 -0400, Arne Vajhøj wrote:
Even with ORM you need to express the query.
So a lot of the stuff in SQLAlchemy is useless, then?
JPQL example (ORM specific query language):
List<T1> res = em.createQuery("SELECT o FROM T1 AS o WHERE o.f > 0",
T1.class).getResultList();
That seems like a really dumb idea. You see my point about having
their own query language does nothing to simplify the code.
The big difference in the above two examples compared to traditional
SQL API (JDBC and ADO.NET for Java and .NET) is that it return a
List<T1> instead of a generic result like List<Object[]>.
Is there a version that returns an iterator? Can be more
memory-efficient for dealing with lots of returned records.
On 7/29/2025 12:25 AM, Lawrence D'Oliveiro wrote:
On Mon, 28 Jul 2025 22:36:59 -0400, Arne Vajhøj wrote:
Even with ORM you need to express the query.
So a lot of the stuff in SQLAlchemy is useless, then?
No.
It does what it is supposed to do: do the mapping between
the query result and the object model.
JPQL example (ORM specific query language):
List<T1> res = em.createQuery("SELECT o FROM T1 AS o WHERE o.f > 0",
T1.class).getResultList();
That seems like a really dumb idea. You see my point about having
their own query language does nothing to simplify the code.
Again: the benefit is not in the query but in the fact that it
stuff the query result into the object model without any
application code.
On 7/29/2025 12:25 AM, Lawrence D'Oliveiro wrote:
On Mon, 28 Jul 2025 22:36:59 -0400, Arne Vajhøj wrote:
JPQL example (ORM specific query language):
List<T1> res = em.createQuery("SELECT o FROM T1 AS o WHERE o.f > 0",
T1.class).getResultList();
That seems like a really dumb idea. You see my point about having their
own query language does nothing to simplify the code.
Again: the benefit is not in the query but in the fact that it stuff the query result into the object model without any application code.
Is there a version that returns an iterator? Can be more
memory-efficient for dealing with lots of returned records.
Usually there are.
But it is a rare requirement when using object models.
.NET has one of the easiest ways to do it.
List<T1> res = db.T1.Where(o => o.F > 0).ToList();
foreach(T1 o in res)
will materialize all rows in memory and then iterate over it, but:
foreach(T1 o in db.T1.Where(o => o.F > 0))
will iterate directly.
On Tue, 29 Jul 2025 09:39:51 -0400, Arne Vajhøj wrote:
On 7/29/2025 12:25 AM, Lawrence D'Oliveiro wrote:
On Mon, 28 Jul 2025 22:36:59 -0400, Arne Vajhøj wrote:
JPQL example (ORM specific query language):
List<T1> res = em.createQuery("SELECT o FROM T1 AS o WHERE o.f > 0",
T1.class).getResultList();
That seems like a really dumb idea. You see my point about having their
own query language does nothing to simplify the code.
Again: the benefit is not in the query but in the fact that it stuff the
query result into the object model without any application code.
So why does it have its own extra mechanism for formulating the query,
then? You yourself are admitting there is no benefit in that. Yet here you are, having to go through that same extra layer of mechanism.
Is there a version that returns an iterator? Can be more
memory-efficient for dealing with lots of returned records.
Usually there are.
But it is a rare requirement when using object models.
On the contrary, I use it a lot. Maybe the problem is that object models
make the idea hard to use.
.NET has one of the easiest ways to do it.
List<T1> res = db.T1.Where(o => o.F > 0).ToList();
foreach(T1 o in res)
will materialize all rows in memory and then iterate over it, but:
foreach(T1 o in db.T1.Where(o => o.F > 0))
will iterate directly.
Can you explicitly call a “next()” method on an iterator object to retrieve each record in turn?
On 7/29/2025 7:07 PM, Lawrence D'Oliveiro wrote:
So why does it have its own extra mechanism for formulating the query,
then? You yourself are admitting there is no benefit in that. Yet here
you are, having to go through that same extra layer of mechanism.
It is not an extra layer. It is an alternative layer.
Transactional/operation code use an object model but don't work on huge
data. If the data is huge, then you paginate.
If your religious beliefs do not allow you to use foreach loop, then:
foreach(T1 o in whatever)
{
...
}
can be done as:
IEnumerator<T1> en = whatever.GetEnumerator();
while(en.MoveNext())
{
T1 o = en.Current;
...
}
But I really don't see any reason to do that.
def map_list_rel_to_obj(data):
res = []
lastorderid = -1
for row in data:
if row[0] != lastorderid:
res.append(myorder(row[0], row[1], row[2]))
lastorderid = row[0]
res[-1].orderlines.append(orderline(row[3], row[4], row[5], row[6]))
return res
On Tue, 29 Jul 2025 20:56:18 -0400, Arne Vajhøj wrote:
On 7/29/2025 7:07 PM, Lawrence D'Oliveiro wrote:
So why does it have its own extra mechanism for formulating the query,
then? You yourself are admitting there is no benefit in that. Yet here
you are, having to go through that same extra layer of mechanism.
It is not an extra layer. It is an alternative layer.
How would you redo your example to not use that layer?
Transactional/operation code use an object model but don't work on huge
data. If the data is huge, then you paginate.
So ORMs are not scalable?
If your religious beliefs do not allow you to use foreach loop, then:
foreach(T1 o in whatever)
{
...
}
can be done as:
IEnumerator<T1> en = whatever.GetEnumerator();
while(en.MoveNext())
{
T1 o = en.Current;
...
}
But I really don't see any reason to do that.
How would you handle level breaks?
On Tue, 29 Jul 2025 10:02:00 -0400, Arne Vajhøj wrote:
def map_list_rel_to_obj(data):
res = []
lastorderid = -1
for row in data:
if row[0] != lastorderid:
res.append(myorder(row[0], row[1], row[2]))
lastorderid = row[0]
res[-1].orderlines.append(orderline(row[3], row[4], row[5], row[6]))
return res
Here’s a somewhat more general way of doing it:
def group_query(db, table, field_names, cond, break_field) :
"iterates over records in table, grouped by values of break_field." \
" Note that table could be a join on multiple tables."
entries_iter = db_iter \
(
db,
"select %(fields)s from %(table)s where %(cond)s order by %(break)s"
%
{
"fields" : ", ".join(field_names),
"table" : table,
"cond" : cond,
"break" : break_field,
}
)
last_break_id = subbreak = None
while True :
entry = next(entries_iter, None)
if entry != None :
entry = dict(zip(field_names, entry))
this_break_id = entry[break_field]
#end if
if entry == None or this_break_id != last_break_id :
if subbreak != None :
yield subbreak
if entry == None :
break
last_break_id = this_break_id
subbreak = []
#end if
subbreak.append(entry)
#end while
#end group_query
That does not stuff data into an object model - it is still just a list
and dict approach.
On 7/29/2025 10:41 PM, Lawrence D'Oliveiro wrote:
So ORMs are not scalable?
It is not a technology limitation.
It is a functional requirement.
Nobody want to go to a list page and have it take 30 minutes to load a billion rows or make a web service call and get a 1 TB response back
with a billion rows.
How would you handle level breaks?
It is an ordinary while loop and break works like it does.
If you need to break out of two loops, then you would need to use a goto
as C# does not support break to labeled loops like Java does.
| Sysop: | Keyop |
|---|---|
| Location: | Huddersfield, West Yorkshire, UK |
| Users: | 715 |
| Nodes: | 16 (0 / 16) |
| Uptime: | 163:30:24 |
| Calls: | 12,095 |
| Calls today: | 3 |
| Files: | 15,000 |
| Messages: | 6,517,787 |