PJBS for Python

[Arne Vajhøj]

There is a PJBS open source project at:
https://sourceforge.net/projects/pjbs/
which has been forked at:
https://github.com/JCotton1123/php-jdbc-bridge

It allows PHP applications to access a database using a
JDBC driver.

It works like:

PHP app with PJBridge class-->proxy process with JDBC driver-->database

PJBridge.php is very simple so it was easy to convert to Python:

Python app with PJBridge class-->proxy process with JDBC driver-->database

And I even created a DB API 2.0 layer on top of it to be more Pythonesque:

Python app with PJBS and PJBridge classes-->proxy process with JDBC
driver-->database

And it seems to work reasonable well.

There are a few things to be aware:
* data are always returned as string so numeric data need to be
manual converted.
* no support for database transaction
* I have not tested error handling or performance
* very limited functional testing so lot of stuff may be missing
(feel free to add it!)

This tool is not particular interesting on common platforms like
Linux and Windows as both PHP and Python has drivers for almost
all databases available there.

But it is a bit more interesting on VMS as a driver for VMS
is not a top priority for most language projects and
database vendors. But the database vendors usually
provide a type 4 JDBC driver. A type 4 JDBC driver is a pure
Java JDBC driver, which means that you can copy the jar file
from Linux/Windows to VMS and it works.

So with:

portable PHP/Python API-->portable Java proxy-->portable type 4 JDBC driver

it all works even if the language project and the database vendor has
not even heard about VMS!

And this is not just theory. I have tested with:
- MySQL
- PostgreSQL
- Oracle DB - thin
- IBM DB2
- MS SQLServer
- SQLite (*)
- Derby
- H2
- HSQLDB

And previously I have tested PJBS with:
- Rdb - native (*)
- Rdb - thin
- Mimer

*) Type 2 not type 4 so VMS version required!

I would group them in:
* PHP/Python drivers available (or will be available) on VMS: MySQL,
PostgreSQL, SQLite
* not interesting for PHP/Python developers (they will prefer SQLIte):
Derby, H2, HSQLDB
* potential relevance of PJBS: Oracle DB, IBM DB2, MS SQLServer

If anyone want to play with it then it is available at:
https://www.vajhoej.dk/arne/vmsstuff/pjbs/

Arne

[Lawrence D'Oliveiro]

But it is a bit more interesting on VMS as a driver for VMS is not a top
priority for most language projects and database vendors. But the
database vendors usually provide a type 4 JDBC driver. A type 4 JDBC
driver is a pure Java JDBC driver, which means that you can copy the jar
file from Linux/Windows to VMS and it works.

If they have a client library for C, say, then you should be able to
create a pure-Python wrapper around it using ctypes.

[Arne Vajhøj]

Post by Lawrence D'Oliveiro

But it is a bit more interesting on VMS as a driver for VMS is not a top
priority for most language projects and database vendors. But the
database vendors usually provide a type 4 JDBC driver. A type 4 JDBC
driver is a pure Java JDBC driver, which means that you can copy the jar
file from Linux/Windows to VMS and it works.

If they have a client library for C, say, then you should be able to
create a pure-Python wrapper around it using ctypes.

There are some possibilities for some databases.

MS SQLServer is probably the easiest. I believe FreeTDS can build
on VMS and pymssql can use FreeTDS, so someone just need to figure
everything out on how to make it build on VMS. Also as fallback
the TDS protocol is known (FreeTDS and jTDS) and a pure Python
solution (using TCP to port 1433) could be developed. All that
is required is some work.

Oracle DB client lib (OCI not to be confused with OCI!) was
available in an old version on older VMS platforms, but
will not be available on VMS x86-64. So that seems like
a dead end future wise. Then there are the commercial ODBC
solutions.

IBM DB2 client lib has never been available on VMS AFAIK.
That leaves commercial ODBC solutions, if they support DB2,
which is far from a given.

Then there is the SQLRelay solution. There are both PHP
and Python clients for that. They could be ported to
VMS. Again just work. But that is also with a proxy
and even though SQLRelay proxy is a way more robust and
supported solution than PJBS, then it does not even run
on VMS - it has to run on Linux or Windows.

Compared to that:
* translating 123 lines of PHP to 61 lines of Python
* write 46 lines of Python to get DB API 2.0 API
is dirt cheap to get all 3 working!!

But if someone has a serious need for like MS SQLServer
access, then they should be able to make it work.

Arne

[Lawrence D'Oliveiro]

... then it does not even run on VMS - it has to run on Linux or
Windows.

Imagine if VSI had followed the strategy I suggested those years ago, to
simply get rid of the VMS kernel, keeping just DCL and the userland APIs,
and reimplementing those on top of Linux. So many vistas would be open now
that are otherwise shut.

[Simon Clubley]

Post by Lawrence D'Oliveiro

... then it does not even run on VMS - it has to run on Linux or
Windows.

Imagine if VSI had followed the strategy I suggested those years ago, to
simply get rid of the VMS kernel, keeping just DCL and the userland APIs,
and reimplementing those on top of Linux. So many vistas would be open now
that are otherwise shut.

As you have already been told, people have done exactly that. Multiple times.
And yet there is still a need for "real" VMS or VSI would never have existed.

Simon.

[Arne Vajhøj]

Post by Arne Vajhøj
Python app with PJBridge class-->proxy process with JDBC driver-->database
Python app with PJBS and PJBridge classes-->proxy process with JDBC
driver-->database
And it seems to work reasonable well.
  https://www.vajhoej.dk/arne/vmsstuff/pjbs/

I have update the ZIP with a few small changes needed
to make it work with Python 2 as well (VMS Alpha).

Arne