SQLite JDBC on VMS x86-64

[Arne Vajhøj]

This is a little HOWTO.

1) Get latest SQLite for VMS from
https://sourceforge.net/projects/vms-ports/files/SQLITE3/
(currently sqlite3_vms_051.zip)
2) Build with 64 bit pointers using MMS:
$ mms/macro=(pointer_opt=64)
3) Get latest SQLite JDBC driver from https://github.com/xerial/sqlite-jdbc
(currently sqlite-3.47.2.0.jar)
4) Get latest NativeDB.c from
https://github.com/xerial/sqlite-jdbc/tree/master/src/main/java/org/sqlite/core
5) Generate JNI shareable image using COM file below.

$ jcc :=
cc/pointer=64/name=(as_is,shortened)/reent=multi/float=ieee/ieee=denorm/include=(sys$common:[openjdk$80.include],sys$common
:[openjdk$80.include.openvms],sqlite3_include)"
$ javah -cp sqlite-jdbc-3_47_2_0.jar org.sqlite.core.NativeDB
$ ren org_sqlite_core_NativeDB.h NativeDB.h
$ jcc NativeDB
$ pipe java$build_option NativeDB.obj > NativeDB.opt
$ link/map/share=sqlitejdbc NativeDB + NativeDB/opt + sys$input/opt
sqlite3shr64/share
$
$ exit

When running your JVM language application just put
the JDBC har in classpath and define java.library.path to
point to directory with sqlitejdbc.EXE.

Example:

$ javac Demo.java
$ java -cp .:sqlite-jdbc-3_47_2_0.jar -Djava.library.path=. Demo

Note that the Alpha and Itanium drivers at
https://www.vms2linux.de/sqlitejdbc.html
are a little bit more advanced and extract the shareable image
from the jar file at runtime and load it similar to what it does
on Windows and Linux. Just having it on disk and specify
location in command line is good enough for me.

Arne

[Arne Vajhøj]

When running your JVM language application just put the JDBC har in
classpath and define java.library.path to point to directory with
sqlitejdbc.EXE.

Isn’t there a more direct way yet, to access the world’s most popular
DBMS from Java?
<https://developer.android.com/reference/android/database/sqlite/package-summary>

Not really.

The context put some constraints on the options.

SQLite is an embedded database. SQLite is written in C.

The only way for Java to ensure 100% compatibility with native
code is to have the Java code call the same C code. That
means the JDBC driver will include native code (type 2 driver).

The application programmer do not care about the implementation
only about the API. Most Java code use the JDBC API or an
ORM on top of JDBC. Android additionally offers an Android
specific API.

But the implementation is still needed. Out of the box
driver comes with:

org/sqlite/native/Mac/x86_64/libsqlitejdbc.dylib
org/sqlite/native/Mac/aarch64/libsqlitejdbc.dylib
org/sqlite/native/Linux/armv7/libsqlitejdbc.so
org/sqlite/native/Linux/arm/libsqlitejdbc.so
org/sqlite/native/Linux/x86_64/libsqlitejdbc.so
org/sqlite/native/Linux/riscv64/libsqlitejdbc.so
org/sqlite/native/Linux/x86/libsqlitejdbc.so
org/sqlite/native/Linux/ppc64/libsqlitejdbc.so
org/sqlite/native/Linux/armv6/libsqlitejdbc.so
org/sqlite/native/Linux/aarch64/libsqlitejdbc.so
org/sqlite/native/Linux-Musl/x86_64/libsqlitejdbc.so
org/sqlite/native/Linux-Musl/x86/libsqlitejdbc.so
org/sqlite/native/Linux-Musl/aarch64/libsqlitejdbc.so
org/sqlite/native/Windows/armv7/sqlitejdbc.dll
org/sqlite/native/Windows/x86_64/sqlitejdbc.dll
org/sqlite/native/Windows/x86/sqlitejdbc.dll
org/sqlite/native/Windows/aarch64/sqlitejdbc.dll
org/sqlite/native/FreeBSD/x86_64/libsqlitejdbc.so
org/sqlite/native/FreeBSD/x86/libsqlitejdbc.so
org/sqlite/native/FreeBSD/aarch64/libsqlitejdbc.so
org/sqlite/native/Linux-Android/arm/libsqlitejdbc.so
org/sqlite/native/Linux-Android/x86_64/libsqlitejdbc.so
org/sqlite/native/Linux-Android/x86/libsqlitejdbc.so
org/sqlite/native/Linux-Android/aarch64/libsqlitejdbc.so

so people on those platforms do not need to do anything.

OpenVMS did not make the cut. So we need to find
a way to provide that native module for VMS.

Builds for VMS Alpha and VMS Itanium have been available
for years, but I have not seen anything for VMS x86-64,
so I created something.

It must be the same for that Android specific API. Google
just make sure the native piece is there.

If the database is a database server or written in Java, then
the JDBC driver can be made all Java (type 4 driver) and
life is easier because same driver can be used everywhere.

Arne

[Lawrence D'Oliveiro]

Post by Arne Vajhøj
Most Java code use the JDBC API or an
ORM on top of JDBC. Android additionally offers an Android
specific API.

That’s what I mean. JDBC is just such a roundabout way of doing DBMS
access.

[Arne Vajhøj]

Post by Arne Vajhøj
This is a little HOWTO.
1) Get latest SQLite for VMS from https://sourceforge.net/projects/vms-
ports/files/SQLITE3/
   (currently sqlite3_vms_051.zip)
     $ mms/macro=(pointer_opt=64)
3) Get latest SQLite JDBC driver from https://github.com/xerial/sqlite-jdbc
   (currently sqlite-3.47.2.0.jar)
4) Get latest NativeDB.c from https://github.com/xerial/sqlite-jdbc/
tree/master/src/main/java/org/sqlite/core
5) Generate JNI shareable image using COM file below.
$ jcc := cc/pointer=64/name=(as_is,shortened)/reent=multi/float=ieee/
ieee=denorm/include=(sys$common:[openjdk$80.include],sys$common
:[openjdk$80.include.openvms],sqlite3_include)"
$ javah -cp sqlite-jdbc-3_47_2_0.jar org.sqlite.core.NativeDB
$ ren org_sqlite_core_NativeDB.h NativeDB.h
$ jcc NativeDB
$ pipe java$build_option NativeDB.obj > NativeDB.opt
$ link/map/share=sqlitejdbc NativeDB + NativeDB/opt + sys$input/opt
sqlite3shr64/share
$
$ exit
When running your JVM language application just put
the JDBC har in classpath and define java.library.path to
point to directory with sqlitejdbc.EXE

And have a logical sqlitejdbc pointing to sqlitejdbc.EXE
(unless it is in current directory)

Post by Arne Vajhøj
.
$ javac Demo.java

$ define/nolog sqlitejdbc sys$disk:[]sqlitejdbc.exe

Post by Arne Vajhøj
$ java -cp .:sqlite-jdbc-3_47_2_0.jar -Djava.library.path=. Demo

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Arne Vajhøj
This is a little HOWTO.
1) Get latest SQLite for VMS from https://sourceforge.net/projects/
vms- ports/files/SQLITE3/
    (currently sqlite3_vms_051.zip)
      $ mms/macro=(pointer_opt=64)
3) Get latest SQLite JDBC driver from https://github.com/xerial/
sqlite-jdbc
    (currently sqlite-3.47.2.0.jar)
4) Get latest NativeDB.c from https://github.com/xerial/sqlite-jdbc/
tree/master/src/main/java/org/sqlite/core
5) Generate JNI shareable image using COM file below.
$ jcc := cc/pointer=64/name=(as_is,shortened)/reent=multi/float=ieee/
ieee=denorm/include=(sys$common:[openjdk$80.include],sys$common
:[openjdk$80.include.openvms],sqlite3_include)"
$ javah -cp sqlite-jdbc-3_47_2_0.jar org.sqlite.core.NativeDB
$ ren org_sqlite_core_NativeDB.h NativeDB.h
$ jcc NativeDB
$ pipe java$build_option NativeDB.obj > NativeDB.opt
$ link/map/share=sqlitejdbc NativeDB + NativeDB/opt + sys$input/opt
sqlite3shr64/share
$
$ exit
When running your JVM language application just put
the JDBC har in classpath and define java.library.path to
point to directory with sqlitejdbc.EXE

And have a logical sqlitejdbc pointing to sqlitejdbc.EXE

It is "or" not "and". Either the logical or the define.
But the logical is preferable as the define can be tricky
with other JVM languages.

And for those that prefer JPA and JPQL over JDBC and SQL,
then out of the box Hibernate 5.6 and the SQLite dialect from
https://mvnrepository.com/artifact/com.github.gwenn/sqlite-dialect
works fine.

Arne

[Arne Vajhøj]

Post by Arne Vajhøj

Post by Arne Vajhøj

Post by Arne Vajhøj
This is a little HOWTO.
1) Get latest SQLite for VMS from https://sourceforge.net/projects/
vms- ports/files/SQLITE3/
    (currently sqlite3_vms_051.zip)
      $ mms/macro=(pointer_opt=64)
3) Get latest SQLite JDBC driver from https://github.com/xerial/
sqlite-jdbc
    (currently sqlite-3.47.2.0.jar)
4) Get latest NativeDB.c from https://github.com/xerial/sqlite-jdbc/
tree/master/src/main/java/org/sqlite/core
5) Generate JNI shareable image using COM file below.
$ jcc := cc/pointer=64/name=(as_is,shortened)/reent=multi/float=ieee/
ieee=denorm/include=(sys$common:[openjdk$80.include],sys$common
:[openjdk$80.include.openvms],sqlite3_include)"
$ javah -cp sqlite-jdbc-3_47_2_0.jar org.sqlite.core.NativeDB
$ ren org_sqlite_core_NativeDB.h NativeDB.h
$ jcc NativeDB
$ pipe java$build_option NativeDB.obj > NativeDB.opt
$ link/map/share=sqlitejdbc NativeDB + NativeDB/opt + sys$input/opt
sqlite3shr64/share
$
$ exit
When running your JVM language application just put
the JDBC har in classpath and define java.library.path to
point to directory with sqlitejdbc.EXE

And have a logical sqlitejdbc pointing to sqlitejdbc.EXE

It is "or" not "and". Either the logical or the define.
But the logical is preferable as the define can be tricky
with other JVM languages.
And for those that prefer JPA and JPQL over JDBC and SQL,
then out of the box Hibernate 5.6 and the SQLite dialect from
https://mvnrepository.com/artifact/com.github.gwenn/sqlite-dialect
works fine.

Note that the SQLite JDBC driver despite being latest version
is still far behind regarding JDBC driver functions.

con = args.length >= 3
? DriverManager.getConnection(args[0], args[1], args[2])
: DriverManager.getConnection(args[0])
dbmd = con.getMetaData()
printf("%s %s\n", dbmd.getDatabaseProductName(),
dbmd.getDatabaseProductVersion())
printf("%s %s\n", dbmd.getDriverName(), dbmd.getDriverVersion())
...
printf("Numeric functions = %s\n", dbmd.getNumericFunctions())
printf("String functions = %s\n", dbmd.getStringFunctions())
printf("TimeDate functions = %s\n", dbmd.getTimeDateFunctions())
printf("System functions = %s\n", dbmd.getSystemFunctions())

MySQL 8.0.33
MySQL Connector/J mysql-connector-j-8.0.33 (Revision:
7d6b0800528b6b25c68b52dc10d6c1c8429c100c)
...
Numeric functions =
ABS,ACOS,ASIN,ATAN,ATAN2,BIT_COUNT,CEILING,COS,COT,DEGREES,EXP,FLOOR,LOG,LOG10,MAX,MIN,MOD,PI,POW,POWER,RADIANS,
RAND,ROUND,SIN,SQRT,TAN,TRUNCATE
String functions =
ASCII,BIN,BIT_LENGTH,CHAR,CHARACTER_LENGTH,CHAR_LENGTH,CONCAT,CONCAT_WS,CONV,ELT,EXPORT_SET,FIELD,FIND_IN_SET,HEX,INSERT,INSTR,LCASE,LEFT,LENGTH,LOAD_FILE,LOCATE,LOCATE,LOWER,LPAD,LTRIM,MAKE_SET,MATCH,MID,OCT,OCTET_LENGTH,ORD,POSITION,QUOTE,REPEAT,REPLACE,REVERSE,RIGHT,RPAD,RTRIM,SOUNDEX,SPACE,STRCMP,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING,SUBSTRING_INDEX,TRIM,UCASE,UPPER
TimeDate functions =
DAYOFWEEK,WEEKDAY,DAYOFMONTH,DAYOFYEAR,MONTH,DAYNAME,MONTHNAME,QUARTER,WEEK,YEAR,HOUR,MINUTE,SECOND,PERIOD_ADD,PERIOD_DIFF,TO_DAYS,FROM_DAYS,DATE_FORMAT,TIME_FORMAT,CURDATE,CURRENT_DATE,CURTIME,CURRENT_TIME,NOW,SYSDATE,CURRENT_TIMESTAMP,UNIX_TIMESTAMP,FROM_UNIXTIME,SEC_TO_TIME,TIME_TO_SEC
System functions =
DATABASE,USER,SYSTEM_USER,SESSION_USER,PASSWORD,ENCRYPT,LAST_INSERT_ID,VERSION

SQLite 3.47.2
SQLite JDBC 3.47.2.0
...
Numeric functions =
String functions =
TimeDate functions = DATE,TIME,DATETIME,JULIANDAY,STRFTIME
System functions =

But given that almost noone use JDBC driver functions, then it is not
a huge problem.

Arne