This article only covers JVM databases. For native databases see here.
No surprise that JVM languages dominate for JVM databases.
These JVM databases run on any system with Java, but this article will focus on VMS and only show server side scripts in DCL. There is no point in showing how to run them on Windows and Linux as they come with documentation on how to run them on Windows and Linux.
Comparison of content in 2 articles:
For more complex examples see Access VMS database - API comparison.
HSQLDB is an open source relational database for JVM. It was first released in 2001.
It of course comes with a JDBC driver. An ODBC driver is also available (latest version supposedly also works with a standard PostgreSQL ODBC driver).
HSQLDB can be used both as embedded database and database server:
Start HSQLDB server (on VMS):
$ java -cp hsqldb.jar "org.hsqldb.server.Server" --database.0 file:test --dbname.0 test
For details on ODBC API including SQL updates, parameters and error handling see here.
The example assumes a DSN has been defined in ODBC manager.
hsqldb2.c:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <windows.h>
#include <sql.h>
#include <sqlext.h>
#define F2_MAX_LEN 50
static const char *dsn = "ARNE1_HSQLDB";
static const char *un = "";
static const char *pw = "";
int main()
{
SQLHENV env;
SQLHDBC con;
SQLHSTMT stmt;
long int f1;
char *sqlstr, f2[F2_MAX_LEN + 1];
SQLLEN lenf2;
int stat;
printf("HSQLDB from C (ODBC)\n");
stat = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &env);
stat = SQLSetEnvAttr(env, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3, 0);
stat = SQLAllocHandle(SQL_HANDLE_DBC, env, &con);
stat = SQLConnect(con, (SQLCHAR *)dsn, (SQLSMALLINT)strlen(dsn),
(SQLCHAR *)un, (SQLSMALLINT)strlen(un),
(SQLCHAR *)pw, (SQLSMALLINT)strlen(pw));
sqlstr = "SELECT f1,f2 FROM t1";
stat = SQLAllocHandle(SQL_HANDLE_STMT, con, &stmt);
stat = SQLExecDirect(stmt, (SQLCHAR *)sqlstr, strlen(sqlstr));
stat = SQLBindCol(stmt, 1, SQL_C_LONG, &f1, 0, NULL);
stat = SQLBindCol(stmt, 2, SQL_C_CHAR, f2, sizeof(f2), &lenf2);
for(;;)
{
stat = SQLFetch(stmt);
if((stat != SQL_SUCCESS) && (stat != SQL_SUCCESS_WITH_INFO)) break;
f2[lenf2] = 0;
printf("%ld %s\n", f1, f2);
}
SQLCloseCursor(stmt);
SQLFreeHandle(SQL_HANDLE_STMT, stmt);
SQLDisconnect(con);
SQLFreeHandle(SQL_HANDLE_DBC, con);
SQLFreeHandle(SQL_HANDLE_ENV, env);
return 0;
}
Build and run - Windows:
gcc -m32 hsqldb2.c -lodbc32 -o hsqldb2.exe
hsqldb2
FPC comes with SQLDB unit for database access. SQLDB supports ODBC.
For details on SQLDB API including SQL updates, parameters and error handling see here.
The example assumes a DSN has been defined in ODBC manager.
hsqldb2.pas:
program hsqldb2(input, output);
uses
SQLDB, ODBCConn;
var
con : TSQLConnection;
tx : TSQLTransaction;
q : TSQLQuery;
f1 : integer;
f2 : string;
begin
writeln('HSQLDB from Pascal (SQLDB, ODBC)');
con := TODBCConnection.Create(nil);
con.DatabaseName := 'ARNE1_HSQLDB';
con.Open;
tx := TSQLTransaction.Create(nil);
con.Transaction := tx;
q := TSQLQuery.Create(nil);
q.DataBase := con;
q.SQL.Text := 'SELECT f1,f2 FROM t1';
q.UsePrimaryKeyAsKey := false; (* needed to work around some ODBC quirk *)
q.Open;
while not q.EOF do begin
f1 := q.FieldByName('f1').AsInteger;
f2 := q.FieldByName('f2').AsString;
writeln(f1, ' ', f2);
q.Next;
end;
q.Close;
q.Free;
con.Close;
con.Free;
end.
Build and run - Windows:
fpc hsqldb2.pas
hsqldb2
JDBC is Java basic database API.
HSQLDB can operate in 2 modes:
Here we will look at embedded.
For details on JDBC API including SQL updates and parameters see here.
HSQLDB1.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class HSQLDB1 {
public static void main(String[] args) throws Exception {
System.out.println("HSQLDB from Java (JDBC, embedded)");
Class.forName("org.hsqldb.jdbc.JDBCDriver");
Connection con = DriverManager.getConnection("jdbc:hsqldb:file:test", "sa", "hemmeligt");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - VMS:
$ javac HSQLDB1.java
$ java -cp .:hsqldb.jar "HSQLDB1"
jHSQLDB1.py:
from java.lang import Class
from java.sql import DriverManager
from org.hsqldb.jdbc import JDBCDriver # replaces Class.forName('org.hsqldb.jdbc.JDBCDriver')
print('HSQLDB from JVM Python (JDBC, embedded)')
con = DriverManager.getConnection('jdbc:hsqldb:file:test', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
JDBC is Java basic database API.
HSQLDB can operate in 2 modes:
Here we will look at HSQL (TCP).
For details on JDBC API including SQL updates and parameters see here.
HSQLDB2.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class HSQLDB2 {
public static void main(String[] args) throws Exception {
System.out.println("HSQLDB from Java (JDBC, TCP)");
Class.forName("org.hsqldb.jdbc.JDBCDriver");
Connection con = DriverManager.getConnection("jdbc:hsqldb:hsql://192.168.0.10/test", "sa", "hemmeligt");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - Windows:
javac HSQLDB2.java
java -cp .;hsqldb.jar HSQLDB2
Build and run - VMS:
$ javac HSQLDB2.java
$ java -cp .:hsqldb.jar "HSQLDB2"
jHSQLDB2.py:
from java.lang import Class
from java.sql import DriverManager
from org.hsqldb.jdbc import JDBCDriver # replaces Class.forName('org.hsqldb.jdbc.JDBCDriver')
print('HSQLDB from JVM Python (JDBC, TCP)')
con = DriverManager.getConnection('jdbc:hsqldb:hsql://192.168.0.10/test', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jHSQLDB1z.py:
from com.ziclix.python.sql import zxJDBC
print('HSQLDB from JVM Python (DB API 2.0 via zxJDBC, embedded)')
con = zxJDBC.connect('jdbc:hsqldb:file:test', 'sa', 'hemmeligt', 'org.hsqldb.jdbc.JDBCDriver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jHSQLDB2z.py:
from com.ziclix.python.sql import zxJDBC
print('HSQLDB from JVM Python (DB API 2.0 via zxJDBC, TCP)')
con = zxJDBC.connect('jdbc:hsqldb:hsql://192.168.0.10/test', 'sa', 'hemmeligt', 'org.hsqldb.jdbc.JDBCDriver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
ADO.NET is .NET basic database API.
ADO.NET can use an ODBC driver.
For details on ADO.NET API including SQL updates, parameters and how to make the code database independent see here.
The example assumes a DSN has been defined in ODBC manager.
hsqldb2.cs:
using System;
using System.Data.Odbc;
public class Program
{
public static void Main(String[] args)
{
Console.WriteLine("HSQLDB from C# (ODBC)");
using(OdbcConnection con = new OdbcConnection("Dsn=ARNE1_HSQLDB"))
{
con.Open();
using(OdbcCommand cmd = new OdbcCommand("SELECT f1,f2 FROM t1", con))
{
using(OdbcDataReader rdr = cmd.ExecuteReader())
{
while(rdr.Read())
{
Console.WriteLine((int)rdr["f1"] + " " + (string)rdr["f2"]);
}
}
}
}
}
}
Build and run - Windows:
csc -platform:x86 /r:C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll hsqldb2.cs
hsqldb2
hsqldb2.vb:
Imports System
Imports System.Data.Odbc
Public Class Program
Public Shared Sub Main(args As [String]())
Console.WriteLine("HSQLDB from VB.NET (ODBC)")
Using con As New OdbcConnection("Dsn=ARNE1_HSQLDB")
con.Open()
Using cmd As New OdbcCommand("SELECT f1,f2 FROM t1", con)
Using rdr As OdbcDataReader = cmd.ExecuteReader()
While rdr.Read()
Console.WriteLine(rdr("f1") & " " & rdr("f2"))
End While
End Using
End Using
End Using
End Sub
End Class
Build and run - Windows:
vbc -platform:x86 /r:C:\Windows\Microsoft.NET\Framework\v4.0.30319\System.Data.dll hsqldb2.vb
hsqldb2
As standard Python does not run in a JVM then it can not directly use a JDBC driver.
But a module "JayDeBeApi" exist that allows Python to use a JDBC driver via JNI.
JayDeBeApi can be installed via pip.
It is obviously a relative deep stack (Python -> native -> Java), but it seems to work fine for the simple tests I have done.
Architecture:
For details on DB API 2.0 including SQL updates and parameters see here.
hsqldb3.py:
import jaydebeapi
print('HSQLDB from Python (DB API 2.0, JDBC, JayDeBeApi)')
con = jaydebeapi.connect('org.hsqldb.jdbc.JDBCDriver', 'jdbc:hsqldb:hsql://192.168.0.10/test', ['sa', 'hemmeligt'], 'hsqldb.jar',)
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
Options for Jython (JVM Python) are described under JVM languages.
PHP supports ODBC.
The example assumes a DSN has been defined in ODBC manager.
hsqldb2.php:
<?php
echo "HSQLDB from PHP (ODBC)\r\n";
$con = odbc_connect('ARNE1_HSQLDB', '', '');
$stmt = odbc_prepare($con, 'SELECT f1,f2 FROM t1');
odbc_execute($stmt, array());
while(odbc_fetch_row($stmt)){
$f1 = odbc_result($stmt, 'f1');
$f2 = odbc_result($stmt, 'f2');
echo "$f1 $f2\r\n";
}
odbc_free_result($stmt);
odbc_close($con);
?>
PHP has a standard database API called PDO.
PDO supports ODBC.
The example assumes a DSN has been defined in ODBC manager.
hsqldb2p.php:
<?php
echo "HSQLDB from PHP (PDO, ODBC)\r\n";
$con = new PDO('odbc:DSN=ARNE1_HSQLDB;UID=;PWD=');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$stmt = $con->prepare('SELECT f1,f2 FROM t1');
$stmt->execute(array());
while($row = $stmt->fetch()) {
$f1 = $row['F1']; // note uppercase field names
$f2 = $row['F2'];
echo "$f1 $f2\r\n";
}
?>
As PHP does not run in a JVM then PHP can not directly use a JDBC driver.
But an open source project "PHP JDBC Bridge" allows PHP to use plain socket to communicate with a Java application that use JDBC driver to communicate with a database.
It is available from here.
It is obviously a bit of a hack, but it seems to work fine for the simple tests I have done.
Architecture:
Start PJBS for HSQLDB - Windows:
java -cp pjbs.jar;hsqldb.jar Server org.hsqldb.jdbc.JDBCDriver 4444
Start PJBS for HSQLDB - VMS:
$ java -cp pjbs.jar:hsqldb.jar "Server" "org.hsqldb.jdbc.JDBCDriver" 4444
hsqldb3.php:
<?php
require 'PJBridge.php';
echo "HSQLDB from PHP (JDBC, bridge)\r\n";
$db = new PJBridge('localhost', 4444); // proxy DB server
$con = $db->connect('jdbc:hsqldb:hsql://192.168.0.10/test', 'sa', 'hemmeligt'); // real DB server
$rs = $db->exec('SELECT f1,f2 FROM t1');
while($row = $db->fetch_array($rs)){
$f1 = $row['F1'];
$f2 = $row['F2'];
echo "$f1 $f2\r\n";
}
$db->free_result($rs);
?>
hsqldb.vbs:
WScript.Echo "HSQLDB from VBS (ODBC)"
Set con = CreateObject("ADODB.Connection")
con.Open "Dsn=ARNE1_HSQLDB"
Set cmd = CreateObject("ADODB.Command")
cmd.ActiveConnection = con
cmd.CommandText = "SELECT f1,f2 FROM t1"
Set rs = cmd.Execute
Do While Not rs.EOF
WScript.Echo rs("f1") & " " & rs("f2")
rs.MoveNext
Loop
Set rs = Nothing
Set cmd = Nothing
Set con = Nothing
H2 is an open source relational database for JVM. It was first released in 2005.
It of course comes with a JDBC driver.
At one time it was able to emulate PostgreSQL and work with a standard PostgreSQL ODBC driver, but it does not work with recent versions.
H2 can be used both as embedded database and database server:
Start H2 server (on VMS):
$ java -cp h2-1_2_147.jar "org.h2.tools.Server" -web "-webAllowOthers" -tcp "-tcpAllowOthers"
JDBC is Java basic database API.
H2 can operate in 2 modes:
Here we will look at embedded.
For details on JDBC API including SQL updates and parameters see here.
H2_1.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class H2_1 {
public static void main(String[] args) throws Exception {
System.out.println("H2 from Java (JDBC, embedded)");
Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection("jdbc:h2:test;FILE_LOCK=FS", "sa", "hemmeligt"); // default file lock no good on VMS Alpha
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - VMS:
$ javac H2_1.java
$ java -cp .:h2-1_2_147.jar "H2_1"
jH2_1.py:
from java.lang import Class
from java.sql import DriverManager
from org.h2 import Driver # replaces Class.forName('org.h2.Driver')
print('H2 from JVM Python (JDBC, embedded)')
con = DriverManager.getConnection('jdbc:h2:test;FILE_LOCK=FS', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
JDBC is Java basic database API.
H2 can operate in 2 modes:
Here we will look at TCP.
For details on JDBC API including SQL updates and parameters see here.
H2_2.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class H2_2 {
public static void main(String[] args) throws Exception {
System.out.println("H2 from Java (JDBC, TCP)");
Class.forName("org.h2.Driver");
Connection con = DriverManager.getConnection("jdbc:h2:tcp://192.168.0.10/test;FILE_LOCK=FS", "sa", "hemmeligt"); // default file lock no good on VMS Alpha
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - Windows:
javac H2_2.java
java -cp .;h2-1.2.147.jar H2_2
Build and run - VMS:
$ javac H2_2.java
$ java -cp .:h2-1_2_147.jar "H2_2"
jH2_2.py:
from java.lang import Class
from java.sql import DriverManager
from org.h2 import Driver # replaces Class.forName('org.h2.Driver')
print('H2 from JVM Python (JDBC, TCP)')
con = DriverManager.getConnection('jdbc:h2:tcp://192.168.0.10/test;FILE_LOCK=FS', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jH2_1z.py:
from com.ziclix.python.sql import zxJDBC
print('H2 from JVM Python (DB API 2.0 via zxJDBC, embedded)')
con = zxJDBC.connect('jdbc:h2:test;FILE_LOCK=FS', 'sa', 'hemmeligt', 'org.h2.Driver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jH2_2z.py:
from com.ziclix.python.sql import zxJDBC
print('H2 from JVM Python (DB API 2.0 via zxJDBC, TCP)')
con = zxJDBC.connect('jdbc:h2:tcp://192.168.0.10/test;FILE_LOCK=FS', 'sa', 'hemmeligt', 'org.h2.Driver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
For details on DB API 2.0 including SQL updates and parameters see here.
h2_3.py:
import jaydebeapi
print('H2 from Python (DB API 2.0, JDBC, JayDeBeApi)')
con = jaydebeapi.connect('org.h2.Driver', 'jdbc:h2:tcp://192.168.0.10/test;FILE_LOCK=FS', ['sa', 'hemmeligt'], 'h2-1.2.147.jar',)
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
Options for Jython (JVM Python) are described under JVM languages.
Start PJBS for H2 - Windows:
java -cp C:\DivJava\php-jdbc-bridge-master\pjbs.jar;h2-1.2.147.jar Server org.h2.Driver 4445
Start PJBS for H2 - VMS:
$ java -cp pjbs.jar:h2-1_2_147.jar "Server" "org.h2.Driver" 4445
h2_3.php:
<?php
require 'PJBridge.php';
echo "H2 from PHP (JDBC, bridge)\r\n";
$db = new PJBridge('localhost', 4445); // proxy DB server
$con = $db->connect('jdbc:h2:tcp://192.168.0.10/test;FILE_LOCK=FS', 'sa', 'hemmeligt'); // real DB server
$rs = $db->exec('SELECT f1,f2 FROM t1');
while($row = $db->fetch_array($rs)){
$f1 = $row['F1'];
$f2 = $row['F2'];
echo "$f1 $f2\r\n";
}
$db->free_result($rs);
?>
Derby is an open source relational database for JVM.
Derby has an interesting history. It was first released as Cloudscape in 1997 by a company of that name. In 1999 Informix aquired Cloudscape. In 2001 IBM acquired Informix. In 2004 IBM donated the product to Apache as open source under the name Derby.
Derby was bundled with Oracle Java 7 under the name Java DB.
It of course comes with a JDBC driver.
At one time it was able to emulate DB2 and work with a standard DB2 ODBC driver, but it does not work with recent versions.
Derby can be used both as embedded database and database server:
Start Derby server (on VMS):
$ java -jar derbyrun.jar server start -h 192.168.0.10
JDBC is Java basic database API.
Derby can operate in 2 modes:
Here we will look at embedded.
For details on JDBC API including SQL updates and parameters see here.
Derby1.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class Derby1 {
public static void main(String[] args) throws Exception {
System.out.println("Derby from Java (JDBC, embedded)");
Class.forName("org.apache.derby.jdbc.EmbeddedDriver");
Connection con = DriverManager.getConnection("jdbc:derby:test", "sa", "hemmeligt");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - VMS:
$ javac Derby1.java
$ java -cp .:derby.jar "Derby1"
jDerby1.py:
from java.lang import Class
from java.sql import DriverManager
from org.apache.derby.jdbc import EmbeddedDriver # replaces Class.forName('org.apache.derby.jdbc.EmbeddedDriver')
print('Derby from JVM Python (JDBC, embedded)')
con = DriverManager.getConnection('jdbc:derby:test', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
JDBC is Java basic database API.
Derby can operate in 2 modes:
Here we will look at network.
For details on JDBC API including SQL updates and parameters see here.
Derby2.java:
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class Derby2 {
public static void main(String[] args) throws Exception {
System.out.println("Derby from Java (JDBC, client)");
Class.forName("org.apache.derby.jdbc.ClientDriver");
Connection con = DriverManager.getConnection("jdbc:derby://192.168.0.10:1527/test", "sa", "hemmeligt");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT f1,f2 FROM t1");
while(rs.next()) {
int f1 = rs.getInt(1);
String f2 = rs.getString(2);
System.out.println(f1 + " " + f2);
}
rs.close();
stmt.close();
con.close();
}
}
Build and run - Windows:
javac Derby2.java
java -cp .;derbyclient.jar Derby2
Build and run - VMS:
$ javac Derby2.java
$ java -cp .:derbyclient.jar "Derby2"
jDerby2.py:
from java.lang import Class
from java.sql import DriverManager
from org.apache.derby.jdbc import ClientDriver # replaces Class.forName('org.apache.derby.jdbc.ClientDriver')
print('Derby from JVM Python (JDBC, client)')
con = DriverManager.getConnection('jdbc:derby://192.168.0.10:1527/test', 'sa', 'hemmeligt')
stmt = con.createStatement()
rs = stmt.executeQuery('SELECT f1,f2 FROM t1')
while rs.next():
f1 = rs.getInt(1)
f2 = rs.getString(2)
print(str(f1) + ' ' + f2)
rs.close()
stmt.close()
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jDerby1z.py:
from com.ziclix.python.sql import zxJDBC
print('Derby from JVM Python (DB API 2.0 via zxJDBC, embedded)')
con = zxJDBC.connect('jdbc:derby:test', 'sa', 'hemmeligt', 'org.apache.derby.jdbc.EmbeddedDriver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.commit() # necessary
con.close()
Jython (JVM Python) can use standard Python DB API 2.0 to access a database via the zxJDBC module and a standard JDBC driver.
jDerby2z.py:
from com.ziclix.python.sql import zxJDBC
print('Derby from JVM Python (DB API 2.0 via zxJDBC, client)')
con = zxJDBC.connect('jdbc:derby://192.168.0.10:1527/test', 'sa', 'hemmeligt', 'org.apache.derby.jdbc.ClientDriver')
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.commit() # necessary
con.close()
For details on DB API 2.0 including SQL updates and parameters see here.
derby3.py:
import jaydebeapi
print('Derby from Python (DB API 2.0, JDBC, JayDeBeApi)')
con = jaydebeapi.connect('org.apache.derby.jdbc.ClientDriver', 'jdbc:derby://192.168.0.10:1527/test', ['sa', 'hemmeligt'], 'C:\Apache\db-derby-10.10.2.0-bin\lib\derbyclient.jar',)
c = con.cursor()
c.execute('SELECT f1,f2 FROM t1')
data = c.fetchall()
c.close()
for row in data:
print('%d %s' % (row[0], row[1]))
con.close()
Options for Jython (JVM Python) are described under JVM languages.
Start PJBS for Derby - Windows:
java -cp pjbs.jar;derbyclient.jar Server org.apache.derby.jdbc.ClientDriver 4446
Start PJBS for Derby - VMS:
$ java -cp pjbs.jar:derbyclient.jar "Server" "org.apache.derby.jdbc.ClientDriver" 4446
derby3.php:
<?php
require 'PJBridge.php';
echo "Derby from PHP (JDBC, bridge)\r\n";
$db = new PJBridge('localhost', 4446); // proxy DB server
$con = $db->connect('jdbc:derby://192.168.0.10/test', 'sa', 'hemmeligt'); // real DB server
$rs = $db->exec('SELECT f1,f2 FROM t1');
while($row = $db->fetch_array($rs)){
$f1 = $row['F1'];
$f2 = $row['F2'];
echo "$f1 $f2\r\n";
}
$db->free_result($rs);
?>
| Version | Date | Description |
|---|---|---|
| 1.0 | November 11th 2020 | Initial version |
See list of all articles here
Please send comments to Arne Vajhøj