VMS Tech Demo 3 - from index-sequential file to SQLite database

address_isam.pas:

type
   address = record
                id : [key(0)]unsigned;
                name : packed array [1..32] of char;
                address : packed array [1..64] of char;
             end;

program insert_before_2(input, output);

%include 'address_isam.pas'

var
   addr : address;
   db : file of address;

begin
   open(db, 'address.isq', unknown, organization := indexed, access_method := keyed);
   addr.id := 3;
   addr.name := 'C C';
   addr.address := 'C Road 33';
   db^ := addr;
   put(db);
   addr.id := 4;
   addr.name := 'D D';
   addr.address := 'D Road 44';
   db^ := addr;
   put(db);
   close(db);
end.

Build and run:

$ pas insert_before_2
$ link insert_before_2
$ run insert_before_2

address_isam.pas:

type
   address = record
                id : [key(0)]unsigned;
                name : packed array [1..32] of char;
                address : packed array [1..64] of char;
             end;

program list_before_2(input, output);

%include 'address_isam.pas'

var
   addr : address;
   db : file of address;

begin
   open(db, 'address.isq', unknown, organization := indexed, access_method := keyed);
   resetk(db, 0);
   while not eof(db) do begin
      addr := db^;
      writeln(addr.id,'|',addr.name,'|',addr.address);
      get(db);
   end;
   close(db);
end.

Build and run:

$ pas list_before_2
$ link list_before_2
$ run list_before_2

address.pas:

type
   address = record
                id : unsigned;
                name : varying[32] of char;
                address : varying[64] of char;
             end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program insert_after_2(input, output);

%include 'address.pas'

var
   con : sqlite_ptr;
   ins : sqlite_stmt_ptr;
   addr : address;

begin
   con := psqlite_open('address.db');
   ins := psqlite_prepare(con, 'INSERT INTO address VALUES(?,?,?)');
   addr.id := 7;
   addr.name := 'G G';
   addr.address := 'G Road 77';
   psqlite_bind_int(ins, 1, addr.id);
   psqlite_bind_text(ins, 2, addr.name);
   psqlite_bind_text(ins, 3, addr.address);
   psqlite_step_nonquery(ins);
   psqlite_finalize(ins);
   ins := psqlite_prepare(con, 'INSERT INTO address VALUES(?,?,?)');
   addr.id := 8;
   addr.name := 'H H';
   addr.address := 'H Road 88';
   psqlite_bind_int(ins, 1, addr.id);
   psqlite_bind_text(ins, 2, addr.name);
   psqlite_bind_text(ins, 3, addr.address);
   psqlite_step_nonquery(ins);
   psqlite_finalize(ins);
   psqlite_close(con);
end.

Build and run:

$ pas insert_after_2
$ link insert_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run insert_after_2

address.py:

class Address(object):
    def __init__(self, _id = 0, _name = '', _address = ''):
        self.id = _id
        self.name = _name
        self.address = _address

import sqlite3

from address import Address

con = sqlite3.connect('address.db')
c = con.cursor()
addr = Address(9, 'I I', 'I Road 99')
c.execute('INSERT INTO address VALUES(?,?,?)', (addr.id, addr.name, addr.address))
addr = Address(10, 'J J', 'J Road 1010')
c.execute('INSERT INTO address VALUES(?,?,?)', (addr.id, addr.name, addr.address))
con.commit()
con.close()

address.php:

<?php
class Address {
    public $id;
    public $name;
    public $address;
    public function __construct($id = 0, $name = '', $address = '') {
        $this->id = $id;
        $this->name = $name;
        $this->address = $address;
    }
}
?>

<?php
include '/disk2/arne/migr/simple/address.php';

$con = new PDO('sqlite:/disk2/arne/migr/simple/address.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$stmt = $con->prepare('INSERT INTO address VALUES(:ID,:NAME,:ADDRESS)');
$addr = new Address(11, 'K K', 'K Road 1111');
$stmt->execute(array(':ID' => $addr->id, ':NAME' => $addr->name, ':ADDRESS' => $addr->address));
$addr = new Address(12, 'L L', 'L Road 1212');
$stmt->execute(array(':ID' => $addr->id, ':NAME' => $addr->name, ':ADDRESS' => $addr->address));
?>

public class Address {
    private int id;
    private String name;
    private String address;
    public Address() {
        this(0, "", "");
    }
    public Address(int id, String name, String address) {
        this.id = id;
        this.name = name;
        this.address = address;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        Connection con = DriverManager.getConnection("jdbc:sqlite:address.db");
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO address VALUES(?,?,?)");
        Address addr;
        addr = new Address(13, "M M", "M Road 1313");
        pstmt.setInt(1, addr.getId());
        pstmt.setString(2, addr.getName());
        pstmt.setString(3, addr.getAddress());
        pstmt.executeUpdate();
        addr = new Address(14, "N N", "N Road 1414");
        pstmt.setInt(1, addr.getId());
        pstmt.setString(2, addr.getName());
        pstmt.setString(3, addr.getAddress());
        pstmt.executeUpdate();
        pstmt.close();
        con.close();
    }
}

Build and run:

$ javac InsertAfter5.java Address.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "InsertAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="address")
public class AddressJPA {
    private int id;
    private String name;
    private String address;
    public AddressJPA() {
        this(0, "", "");
    }
    public AddressJPA(int id, String name, String address) {
        this.id = id;
        this.name = name;
        this.address = address;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Column(name="address")
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class InsertAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Address");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        AddressJPA addr;
        addr = new AddressJPA(15, "O O", "O Road 1515");
        em.persist(addr);
        addr = new AddressJPA(16, "P P", "P Road 1616");
        em.persist(addr);
        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' InsertAfter6.java AddressJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "InsertAfter6"

address.pas:

type
   address = record
                id : unsigned;
                name : varying[32] of char;
                address : varying[64] of char;
             end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program list_after_2(input, output);

%include 'address.pas'

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;
   count : integer;
   res : array [1..1000] of address;
   i : integer;

begin
   con := psqlite_open('address.db');
   sel := psqlite_prepare(con, 'SELECT id,name,address FROM address');
   count := 0;
   while psqlite_step_query(sel) do begin
      count := count + 1;
      res[count].id := psqlite_column_int(sel, 0);
      res[count].name := psqlite_column_text(sel, 1);
      res[count].address := psqlite_column_text(sel, 2);
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
   for i := 1 to count do begin
      writeln(res[i].id, '|', res[i].name, '|', res[i].address);
   end;
end.

Build and run:

$ pas list_after_2
$ link list_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run list_after_2

address.py:

class Address(object):
    def __init__(self, _id = 0, _name = '', _address = ''):
        self.id = _id
        self.name = _name
        self.address = _address

import sqlite3

from address import Address

con = sqlite3.connect('address.db')
c = con.cursor()
c.execute('SELECT id,name,address FROM address')
res = []
for row in c.fetchall():
    res.append(Address(row[0], row[1], row[2]))
con.commit()
con.close()

for o in res:
    print('%d|%s|%s' % (o.id, o.name, o.address))

address.php:

<?php
class Address {
    public $id;
    public $name;
    public $address;
    public function __construct($id = 0, $name = '', $address = '') {
        $this->id = $id;
        $this->name = $name;
        $this->address = $address;
    }
}
?>

<?php
include '/disk2/arne/migr/simple/address.php';

$con = new PDO('sqlite:/disk2/arne/migr/simple/address.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$stmt = $con->prepare('SELECT id,name,address FROM address');
$stmt->execute(array());
$res = array();
while($row = $stmt->fetch()) {
   $res[] = new Address($row['id'], $row['name'], $row['address']);
}

foreach($res as $o) {
    echo sprintf('%d|%s|%s', $o->id, $o->name, $o-> address) . "\r\n";
}
?>

public class Address {
    private int id;
    private String name;
    private String address;
    public Address() {
        this(0, "", "");
    }
    public Address(int id, String name, String address) {
        this.id = id;
        this.name = name;
        this.address = address;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ListAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        List<Address> res = new ArrayList<Address>();
        Connection con = DriverManager.getConnection("jdbc:sqlite:address.db");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT id,name,address FROM address");
        while(rs.next()) {
            int id = rs.getInt(1);
            String name = rs.getString(2);
            String address = rs.getString(3);
            res.add(new Address(id, name, address));
        }
        rs.close();
        stmt.close();
        con.close();
        for(Address o : res) {
            System.out.printf("%d|%s|%s\n", o.getId(), o.getName(), o.getAddress());
        }
    }
}

Build and run:

$ javac ListAfter5.java Address.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="address")
public class AddressJPA {
    private int id;
    private String name;
    private String address;
    public AddressJPA() {
        this(0, "", "");
    }
    public AddressJPA(int id, String name, String address) {
        this.id = id;
        this.name = name;
        this.address = address;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Column(name="address")
    public String getAddress() {
        return address;
    }
    public void setAddress(String address) {
        this.address = address;
    }
}

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class ListAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Address");
        EntityManager em = emf.createEntityManager();
        TypedQuery<AddressJPA> q = em.createQuery("SELECT o FROM AddressJPA AS o", AddressJPA.class);
        List<AddressJPA> res = q.getResultList();
        for(AddressJPA o : res) {
            System.out.printf("%d|%s|%s\n", o.getId(), o.getName(), o.getAddress());
        }
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' ListAfter6.java AddressJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListAfter6"

order_isam.pas

type
   orderline = record
                  item : packed array [1..32] of char;
                  qty : unsigned;
               end;
   order = record
              id : [key(0)]unsigned;
              customer : packed array [1..32] of char;
              nlines : integer;
              line : array [1..10] of orderline;
           end;

program insert_before_2(input, output);

%include 'order_isam.pas'

var
   o : order;
   db : file of order;

begin
   open(db, 'order.isq', unknown, organization := indexed, access_method := keyed);
   o.id := 3;
   o.customer := 'C C';
   o.nlines := 1;
   o.line[1].item := 'Thingy';
   o.line[1].qty := 1;
   db^ := o;
   put(db);
   o.id := 4;
   o.customer := 'D D';
   o.nlines := 2;
   o.line[1].item := 'Big thing';
   o.line[1].qty := 1;
   o.line[2].item := 'Small thing';
   o.line[2].qty := 3;
   db^ := o;
   put(db);
   close(db);
end.

Build and run:

$ pas insert_before_2
$ link insert_before_2
$ run insert_before_2

order_isam.pas

type
   orderline = record
                  item : packed array [1..32] of char;
                  qty : unsigned;
               end;
   order = record
              id : [key(0)]unsigned;
              customer : packed array [1..32] of char;
              nlines : integer;
              line : array [1..10] of orderline;
           end;

program list_before_2(input, output);

%include 'order_isam.pas'

var
   o : order;
   db : file of order;
   i : integer;

begin
   open(db, 'order.isq', unknown, organization := indexed, access_method := keyed);
   resetk(db, 0);
   while not eof(db) do begin
      o := db^;
      writeln(o.id:1,' ',o.customer);
      for i := 1 to o.nlines do begin
         writeln('  ',o.line[i].item,': ',o.line[i].qty);
      end;
      get(db);
   end;
   close(db);
end.

Build and run:

$ pas list_before_2
$ link list_before_2
$ run list_before_2

order.pas:

type
   orderline = record
                  id : integer;
                  orderid : integer;
                  item : varying[32] of char;
                  qty : unsigned;
               end;
   order = record
              id : unsigned;
              customer : varying[32] of char;
              nlines : integer;
              line : array [1..100] of orderline;
           end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program insert_after_2(input, output);

%include 'order.pas'

var
   con : sqlite_ptr;
   oins, olins : sqlite_stmt_ptr;
   o : order;
   i : integer;

begin
   con := psqlite_open('order.db');
   oins := psqlite_prepare(con, 'INSERT INTO myorder VALUES(?,?)');
   o.id := 7;
   o.customer := 'G G';
   o.nlines := 1;
   o.line[1].id := 700;
   o.line[1].orderid := 7;
   o.line[1].item := 'A good Pascal book';
   o.line[1].qty := 1;
   for i := 1 to o.nlines do begin
      olins := psqlite_prepare(con, 'INSERT INTO orderline VALUES(?,?,?,?)');
      psqlite_bind_int(olins, 1, o.line[i].id);
      psqlite_bind_int(olins, 2, o.line[i].orderid);
      psqlite_bind_text(olins, 3, o.line[i].item);
      psqlite_bind_int(olins, 4, o.line[i].qty);
      psqlite_step_nonquery(olins);
      psqlite_finalize(olins);
   end;
   psqlite_bind_int(oins, 1, o.id);
   psqlite_bind_text(oins, 2, o.customer);
   psqlite_step_nonquery(oins);
   psqlite_finalize(oins);
   oins := psqlite_prepare(con, 'INSERT INTO myorder VALUES(?,?)');
   o.id := 8;
   o.customer := 'H H';
   o.nlines := 1;
   o.line[1].id := 800;
   o.line[1].orderid := 8;
   o.line[1].item := 'Another good Pascal book';
   o.line[1].qty := 1;
   for i := 1 to o.nlines do begin
      olins := psqlite_prepare(con, 'INSERT INTO orderline VALUES(?,?,?,?)');
      psqlite_bind_int(olins, 1, o.line[i].id);
      psqlite_bind_int(olins, 2, o.line[i].orderid);
      psqlite_bind_text(olins, 3, o.line[i].item);
      psqlite_bind_int(olins, 4, o.line[i].qty);
      psqlite_step_nonquery(olins);
      psqlite_finalize(olins);
   end;
   psqlite_bind_int(oins, 1, o.id);
   psqlite_bind_text(oins, 2, o.customer);
   psqlite_step_nonquery(oins);
   psqlite_finalize(oins);
   psqlite_close(con);
end.

Build and run:

$ pas insert_after_2
$ link insert_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run insert_after_2

order.py:

class OrderLine(object):
    def __init__(self, _id = 0, _orderid = 0, _item = '', _qty = 0):
        self.id = _id
        self.orderid = _orderid
        self.item = _item
        self.qty = _qty

class Order(object):
    def __init__(self, _id = 0, _customer = '', _lines = []):
        self.id = _id
        self.customer = _customer
        self.lines = _lines

import sqlite3

from order import Order, OrderLine

con = sqlite3.connect('order.db')
c = con.cursor()
o = Order(9, 'I I', [ OrderLine(900, 9, 'A good Python book', 1) ]);
for ol in o.lines:
    c.execute('INSERT INTO orderline VALUES(?,?,?,?)', (ol.id, ol.orderid, ol.item, ol.qty))
c.execute('INSERT INTO myorder VALUES(?,?)', (o.id, o.customer))
o = Order(10, 'J J', [ OrderLine(1000, 10, 'Another good Python book', 1) ]);
for ol in o.lines:
    c.execute('INSERT INTO orderline VALUES(?,?,?,?)', (ol.id, ol.orderid, ol.item, ol.qty))
c.execute('INSERT INTO myorder VALUES(?,?)', (o.id, o.customer))
con.commit()
con.close()

order.php:

<?php
class OrderLine {
    public $id;
    public $orderid;
    public $item;
    public $qty;
    public function __construct($id = 0, $orderid = 0, $item = '', $qty = 0) {
        $this->id = $id;
        $this->orderid = $orderid;
        $this->item = $item;
        $this->qty = $qty;
    }
}

class Order {
    public $id;
    public $customer;
    public $lines;
    public function __construct($id = 0, $customer = '', $lines = array()) {
        $this->id = $id;
        $this->customer = $customer;
        $this->lines = $lines;
    }
}

?>

<?php
include '/disk2/arne/migr/relsub/order.php';

$con = new PDO('sqlite:/disk2/arne/migr/relsub/order.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$ostmt = $con->prepare('INSERT INTO myorder VALUES(:ID,:CUSTOMER)');
$olstmt = $con->prepare('INSERT INTO orderline VALUES(:ID,:ORDERID,:ITEM,:QTY)');
$o = new Order(11, 'K K', array(new OrderLine(1100, 11, 'A good PHP book', 1)));
foreach($o->lines as $ol) {
    $olstmt->execute(array(':ID' => $ol->id, ':ORDERID' => $ol->orderid, ':ITEM' => $ol->item, ':QTY' => $ol->qty));
}
$ostmt->execute(array(':ID' => $o->id, ':CUSTOMER' => $o->customer));
$o = new Order(12, 'L L', array(new OrderLine(1200, 12, 'Another good PHP book', 1)));
foreach($o->lines as $ol) {
    $olstmt->execute(array(':ID' => $ol->id, ':ORDERID' => $ol->orderid, ':ITEM' => $ol->item, ':QTY' => $ol->qty));
}
$ostmt->execute(array(':ID' => $o->id, ':CUSTOMER' => $o->customer));
?>

public class OrderLine {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLine() {
        this(0, 0, "", 0);
    }
    public OrderLine(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

public class Order {
    private int id;
    private String customer;
    private List<OrderLine> lines;
    public Order() {
        this(0, "", new ArrayList<OrderLine>());
    }
    public Order(int id, String customer) {
        this(id, customer, new ArrayList<OrderLine>());
    }
    public Order(int id, String customer, List<OrderLine> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public List<OrderLine> getLines() {
        return lines;
    }
    public void setLines(List<OrderLine> lines) {
        this.lines = lines;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        Connection con = DriverManager.getConnection("jdbc:sqlite:order.db");
        PreparedStatement opstmt = con.prepareStatement("INSERT INTO myorder VALUES(?,?)");
        PreparedStatement olpstmt = con.prepareStatement("INSERT INTO orderline VALUES(?,?,?,?)");
        Order o;
        o = new Order(13, "M M");
        o.getLines().add(new OrderLine(1300, 13, "A good Java book", 1));
        for(OrderLine ol : o.getLines()) {
            olpstmt.setInt(1, ol.getId());
            olpstmt.setInt(2, ol.getOrderId());
            olpstmt.setString(3, ol.getItem());
            olpstmt.setInt(4, ol.getQty());
            olpstmt.executeUpdate();
        }
        opstmt.setInt(1, o.getId());
        opstmt.setString(2, o.getCustomer());
        opstmt.executeUpdate();
        o = new Order(14, "N N");
        o.getLines().add(new OrderLine(1400, 14, "Another good Java book", 1));
        for(OrderLine ol : o.getLines()) {
            olpstmt.setInt(1, ol.getId());
            olpstmt.setInt(2, ol.getOrderId());
            olpstmt.setString(3, ol.getItem());
            olpstmt.setInt(4, ol.getQty());
            olpstmt.executeUpdate();
        }
        opstmt.setInt(1, o.getId());
        opstmt.setString(2, o.getCustomer());
        opstmt.executeUpdate();
        opstmt.close();
        con.close();
    }
}

Build and run:

$ javac InsertAfter5.java Order.java OrderLine.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "InsertAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="orderline")
public class OrderLineJPA {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLineJPA() {
        this(0, 0, "", 0);
    }
    public OrderLineJPA(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="orderid")
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    @Column(name="item")
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    @Column(name="qty")
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="myorder")
public class OrderJPA {
    private int id;
    private String customer;
    private List<OrderLineJPA> lines;
    public OrderJPA() {
        this(0, "", new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer) {
        this(id, customer, new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer, List<OrderLineJPA> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="customer")
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    @OneToMany(mappedBy = "orderId", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<OrderLineJPA> getLines() {
        return lines;
    }
    public void setLines(List<OrderLineJPA> lines) {
        this.lines = lines;
    }
}

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class InsertAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Order");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        OrderJPA o;
        o = new OrderJPA(15, "O O");
        o.getLines().add(new OrderLineJPA(1500, 15, "A good Java book", 1));
        em.persist(o);
        o = new OrderJPA(16, "P P");
        o.getLines().add(new OrderLineJPA(1600, 16, "Another good Java book", 1));
        em.persist(o);
        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' InsertAfter6.java OrderJPA.java OrderLineJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "InsertAfter6"

order.pas:

type
   orderline = record
                  id : integer;
                  orderid : integer;
                  item : varying[32] of char;
                  qty : unsigned;
               end;
   order = record
              id : unsigned;
              customer : varying[32] of char;
              nlines : integer;
              line : array [1..100] of orderline;
           end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program list_after_2(input, output);

%include 'order.pas'

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;
   res : array [1..1000] of order;
   count : integer;
   lastid : integer;
   i, j : integer;

begin
   con := psqlite_open('order.db');
   sel := psqlite_prepare(con, 'SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid');
   count := 0;
   lastid := -1;
   while psqlite_step_query(sel) do begin
      if psqlite_column_int(sel, 0) <> lastid then begin
         lastid := psqlite_column_int(sel, 0);
         count := count + 1;
         res[count].id := psqlite_column_int(sel, 0);
         res[count].customer := psqlite_column_text(sel, 1);
         res[count].nlines := 0;
      end; 
      res[count].nlines := res[count].nlines + 1;
      res[count].line[res[count].nlines].id := psqlite_column_int(sel, 2);
      res[count].line[res[count].nlines].orderid := psqlite_column_int(sel, 3);
      res[count].line[res[count].nlines].item := psqlite_column_text(sel, 4);
      res[count].line[res[count].nlines].qty := psqlite_column_int(sel, 5);
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
   for i := 1 to count do begin
      writeln(res[i].id:1, ' ', res[i].customer);
      for j := 1 to res[i].nlines do begin
         writeln('  ', res[i].line[j].item, ': ', res[i].line[j].qty);
      end;
   end;
end.

Build and run:

$ pas list_after_2
$ link list_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run list_after_2

order.py:

class OrderLine(object):
    def __init__(self, _id = 0, _orderid = 0, _item = '', _qty = 0):
        self.id = _id
        self.orderid = _orderid
        self.item = _item
        self.qty = _qty

class Order(object):
    def __init__(self, _id = 0, _customer = '', _lines = []):
        self.id = _id
        self.customer = _customer
        self.lines = _lines

import sqlite3

from order import Order, OrderLine

con = sqlite3.connect('order.db')
c = con.cursor()
c.execute('SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid')
res = []
lastid = -1
lasto = None
for row in c.fetchall():
    if row[0] != lastid:
        lastid = row[0]
        lasto = Order(row[0], row[1], [])
        res.append(lasto)
    lasto.lines.append(OrderLine(row[2], row[3], row[4], row[5]))
con.commit()
con.close()

for o in res:
    print('%d %s' % (o.id, o.customer))
    for ol in o.lines:
        print('  %s : %d' % (ol.item, ol.qty))

order.php:

<?php
class OrderLine {
    public $id;
    public $orderid;
    public $item;
    public $qty;
    public function __construct($id = 0, $orderid = 0, $item = '', $qty = 0) {
        $this->id = $id;
        $this->orderid = $orderid;
        $this->item = $item;
        $this->qty = $qty;
    }
}

class Order {
    public $id;
    public $customer;
    public $lines;
    public function __construct($id = 0, $customer = '', $lines = array()) {
        $this->id = $id;
        $this->customer = $customer;
        $this->lines = $lines;
    }
}

?>

<?php
include '/disk2/arne/migr/relsub/order.php';

$con = new PDO('sqlite:/disk2/arne/migr/relsub/order.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
$stmt = $con->prepare('SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid');
$stmt->execute(array());
$res = array();
$lastid = -1;
$lasto = null;
while($row = $stmt->fetch()) {
    if($lastid != $row[0]) {
        $lastid = $row[0];
        $lasto = new Order($row[0], $row[1], array());
        $res[] = $lasto;
    }
    $lasto->lines[] = new OrderLine($row[2], $row[3], $row[4], $row[5]);
}

foreach($res as $o) {
    echo sprintf('%d %s', $o->id, $o->customer) . "\r\n";
    foreach($o->lines as $ol) {
        echo sprintf('  %s : %d', $ol->item, $ol->qty) . "\r\n";
    }
}
?>

public class OrderLine {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLine() {
        this(0, 0, "", 0);
    }
    public OrderLine(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

public class Order {
    private int id;
    private String customer;
    private List<OrderLine> lines;
    public Order() {
        this(0, "", new ArrayList<OrderLine>());
    }
    public Order(int id, String customer) {
        this(id, customer, new ArrayList<OrderLine>());
    }
    public Order(int id, String customer, List<OrderLine> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public List<OrderLine> getLines() {
        return lines;
    }
    public void setLines(List<OrderLine> lines) {
        this.lines = lines;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ListAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        List<Order> res = new ArrayList<Order>();
        Connection con = DriverManager.getConnection("jdbc:sqlite:order.db");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid");
        int lastid = -1;
        Order lasto = null;
        while(rs.next()) {
            int id = rs.getInt(1);
            String customer = rs.getString(2);
            int lineid = rs.getInt(3);
            int orderid = rs.getInt(4);
            String item = rs.getString(5);
            int qty = rs.getInt(6);
            if(id != lastid) {
                lastid = id;
                lasto = new Order(id, customer);
                res.add(lasto);
            }
            OrderLine line = new OrderLine(lineid, orderid, item, qty);
            lasto.getLines().add(line);
        }
        rs.close();
        stmt.close();
        con.close();
        for(Order o : res) {
            System.out.printf("%d %s\n", o.getId(), o.getCustomer());
            for(OrderLine ol : o.getLines()) {
                System.out.printf("  %s : %d\n", ol.getItem(), ol.getQty());
            }
        }
    }
}

Build and run:

$ javac ListAfter5.java Order.java OrderLine.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="orderline")
public class OrderLineJPA {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLineJPA() {
        this(0, 0, "", 0);
    }
    public OrderLineJPA(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="orderid")
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    @Column(name="item")
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    @Column(name="qty")
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="myorder")
public class OrderJPA {
    private int id;
    private String customer;
    private List<OrderLineJPA> lines;
    public OrderJPA() {
        this(0, "", new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer) {
        this(id, customer, new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer, List<OrderLineJPA> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="customer")
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    @OneToMany(mappedBy = "orderId", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<OrderLineJPA> getLines() {
        return lines;
    }
    public void setLines(List<OrderLineJPA> lines) {
        this.lines = lines;
    }
}

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class ListAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Order");
        EntityManager em = emf.createEntityManager();
        TypedQuery<OrderJPA> q = em.createQuery("SELECT o FROM OrderJPA AS o JOIN FETCH o.lines ol", OrderJPA.class);
        List<OrderJPA> res = q.getResultList();
        for(OrderJPA o : res) {
            System.out.printf("%d %s\n", o.getId(), o.getCustomer());
            for(OrderLineJPA ol : o.getLines()) {
                System.out.printf("  %s : %d\n", ol.getItem(), ol.getQty());
            }
        }
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' ListAfter6.java OrderJPA.java OrderLineJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListAfter6"

order_isam.pas:

type
   orderline = record
                  id : [key(0)]unsigned;
                  orderid : [key(1)]unsigned;
                  item : packed array [1..32] of char;
                  qty : unsigned;
               end;
   order = record
              id : [key(0)]unsigned;
              customer : packed array [1..32] of char;
           end;

program insert_before_2(input, output);

%include 'order_isam.pas'

var
   o : order;
   odb : file of order;
   ol : orderline;
   oldb : file of orderline;

begin
   open(odb, 'order.isq', unknown, organization := indexed, access_method := keyed);
   open(oldb, 'orderline.isq', unknown, organization := indexed, access_method := keyed);
   o.id := 3;
   o.customer := 'C C';
   odb^ := o;
   put(odb);
   ol.id := 300;
   ol.orderid := 3;
   ol.item := 'Thingy';
   ol.qty := 1;
   oldb^ := ol;
   put(oldb);
   o.id := 4;
   o.customer := 'D D';
   odb^ := o;
   put(odb);
   ol.id := 400;
   ol.orderid := 4;
   ol.item := 'Big thing';
   ol.qty := 1;
   oldb^ := ol;
   put(oldb);
   ol.id := 401;
   ol.orderid := 4;
   ol.item := 'Small thing';
   ol.qty := 3;
   oldb^ := ol;
   put(oldb);
   close(odb);
   close(oldb);
end.

Build and run:

$ pas insert_before_2
$ link insert_before_2
$ run insert_before_2

order_isam.pas:

type
   orderline = record
                  id : [key(0)]unsigned;
                  orderid : [key(1)]unsigned;
                  item : packed array [1..32] of char;
                  qty : unsigned;
               end;
   order = record
              id : [key(0)]unsigned;
              customer : packed array [1..32] of char;
           end;

program list_before_2(input, output);

%include 'order_isam.pas'

var
   o : order;
   odb : file of order;
   ol : orderline;
   oldb : file of orderline;
   done : boolean;

begin
   open(odb, 'order.isq', unknown, organization := indexed, access_method := keyed);
   open(oldb, 'orderline.isq', unknown, organization := indexed, access_method := keyed);
   resetk(odb, 0);
   while not eof(odb) do begin
      o := odb^;
      writeln(o.id:1,' ',o.customer);
      findk(oldb, 1, o.id);
      done := false;
      while not eof(oldb) do begin
         ol := oldb^;
         if (ol.orderid = o.id) and not done then begin
            writeln('  ', ol.item, ': ', ol.qty);
         end else begin
            done := true;
         end;
         get(oldb);
      end;
      get(odb);
   end;
   close(odb);
   close(oldb);
end.

Build and run:

$ pas list_before_2
$ link list_before_2
$ run list_before_2

order.pas:

type
   orderline = record
                  id : integer;
                  orderid : integer;
                  item : varying[32] of char;
                  qty : unsigned;
               end;
   order = record
              id : unsigned;
              customer : varying[32] of char;
              nlines : integer;
              line : array [1..100] of orderline;
           end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program insert_after_2(input, output);

%include 'order.pas'

var
   con : sqlite_ptr;
   oins, olins : sqlite_stmt_ptr;
   o : order;
   i : integer;

begin
   con := psqlite_open('order.db');
   oins := psqlite_prepare(con, 'INSERT INTO myorder VALUES(?,?)');
   o.id := 7;
   o.customer := 'G G';
   o.nlines := 1;
   o.line[1].id := 700;
   o.line[1].orderid := 7;
   o.line[1].item := 'A good Pascal book';
   o.line[1].qty := 1;
   for i := 1 to o.nlines do begin
      olins := psqlite_prepare(con, 'INSERT INTO orderline VALUES(?,?,?,?)');
      psqlite_bind_int(olins, 1, o.line[i].id);
      psqlite_bind_int(olins, 2, o.line[i].orderid);
      psqlite_bind_text(olins, 3, o.line[i].item);
      psqlite_bind_int(olins, 4, o.line[i].qty);
      psqlite_step_nonquery(olins);
      psqlite_finalize(olins);
   end;
   psqlite_bind_int(oins, 1, o.id);
   psqlite_bind_text(oins, 2, o.customer);
   psqlite_step_nonquery(oins);
   psqlite_finalize(oins);
   oins := psqlite_prepare(con, 'INSERT INTO myorder VALUES(?,?)');
   o.id := 8;
   o.customer := 'H H';
   o.nlines := 1;
   o.line[1].id := 800;
   o.line[1].orderid := 8;
   o.line[1].item := 'Another good Pascal book';
   o.line[1].qty := 1;
   for i := 1 to o.nlines do begin
      olins := psqlite_prepare(con, 'INSERT INTO orderline VALUES(?,?,?,?)');
      psqlite_bind_int(olins, 1, o.line[i].id);
      psqlite_bind_int(olins, 2, o.line[i].orderid);
      psqlite_bind_text(olins, 3, o.line[i].item);
      psqlite_bind_int(olins, 4, o.line[i].qty);
      psqlite_step_nonquery(olins);
      psqlite_finalize(olins);
   end;
   psqlite_bind_int(oins, 1, o.id);
   psqlite_bind_text(oins, 2, o.customer);
   psqlite_step_nonquery(oins);
   psqlite_finalize(oins);
   psqlite_close(con);
end.

Build and run:

$ pas insert_after_2
$ link insert_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run insert_after_2

order.py:

class OrderLine(object):
    def __init__(self, _id = 0, _orderid = 0, _item = '', _qty = 0):
        self.id = _id
        self.orderid = _orderid
        self.item = _item
        self.qty = _qty

class Order(object):
    def __init__(self, _id = 0, _customer = '', _lines = []):
        self.id = _id
        self.customer = _customer
        self.lines = _lines

import sqlite3

from order import Order, OrderLine

con = sqlite3.connect('order.db')
c = con.cursor()
o = Order(9, 'I I', [ OrderLine(900, 9, 'A good Python book', 1) ]);
for ol in o.lines:
    c.execute('INSERT INTO orderline VALUES(?,?,?,?)', (ol.id, ol.orderid, ol.item, ol.qty))
c.execute('INSERT INTO myorder VALUES(?,?)', (o.id, o.customer))
o = Order(10, 'J J', [ OrderLine(1000, 10, 'Another good Python book', 1) ]);
for ol in o.lines:
    c.execute('INSERT INTO orderline VALUES(?,?,?,?)', (ol.id, ol.orderid, ol.item, ol.qty))
c.execute('INSERT INTO myorder VALUES(?,?)', (o.id, o.customer))
con.commit()
con.close()

order.php:

<?php
class OrderLine {
    public $id;
    public $orderid;
    public $item;
    public $qty;
    public function __construct($id = 0, $orderid = 0, $item = '', $qty = 0) {
        $this->id = $id;
        $this->orderid = $orderid;
        $this->item = $item;
        $this->qty = $qty;
    }
}

class Order {
    public $id;
    public $customer;
    public $lines;
    public function __construct($id = 0, $customer = '', $lines = array()) {
        $this->id = $id;
        $this->customer = $customer;
        $this->lines = $lines;
    }
}

?>

<?php
include '/disk2/arne/migr/reltwo/order.php';

$con = new PDO('sqlite:/disk2/arne/migr/reltwo/order.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$ostmt = $con->prepare('INSERT INTO myorder VALUES(:ID,:CUSTOMER)');
$olstmt = $con->prepare('INSERT INTO orderline VALUES(:ID,:ORDERID,:ITEM,:QTY)');
$o = new Order(11, 'K K', array(new OrderLine(1100, 11, 'A good PHP book', 1)));
foreach($o->lines as $ol) {
    $olstmt->execute(array(':ID' => $ol->id, ':ORDERID' => $ol->orderid, ':ITEM' => $ol->item, ':QTY' => $ol->qty));
}
$ostmt->execute(array(':ID' => $o->id, ':CUSTOMER' => $o->customer));
$o = new Order(12, 'L L', array(new OrderLine(1200, 12, 'Another good PHP book', 1)));
foreach($o->lines as $ol) {
    $olstmt->execute(array(':ID' => $ol->id, ':ORDERID' => $ol->orderid, ':ITEM' => $ol->item, ':QTY' => $ol->qty));
}
$ostmt->execute(array(':ID' => $o->id, ':CUSTOMER' => $o->customer));
?>

public class OrderLine {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLine() {
        this(0, 0, "", 0);
    }
    public OrderLine(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

public class Order {
    private int id;
    private String customer;
    private List<OrderLine> lines;
    public Order() {
        this(0, "", new ArrayList<OrderLine>());
    }
    public Order(int id, String customer) {
        this(id, customer, new ArrayList<OrderLine>());
    }
    public Order(int id, String customer, List<OrderLine> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public List<OrderLine> getLines() {
        return lines;
    }
    public void setLines(List<OrderLine> lines) {
        this.lines = lines;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        Connection con = DriverManager.getConnection("jdbc:sqlite:order.db");
        PreparedStatement opstmt = con.prepareStatement("INSERT INTO myorder VALUES(?,?)");
        PreparedStatement olpstmt = con.prepareStatement("INSERT INTO orderline VALUES(?,?,?,?)");
        Order o;
        o = new Order(13, "M M");
        o.getLines().add(new OrderLine(1300, 13, "A good Java book", 1));
        for(OrderLine ol : o.getLines()) {
            olpstmt.setInt(1, ol.getId());
            olpstmt.setInt(2, ol.getOrderId());
            olpstmt.setString(3, ol.getItem());
            olpstmt.setInt(4, ol.getQty());
            olpstmt.executeUpdate();
        }
        opstmt.setInt(1, o.getId());
        opstmt.setString(2, o.getCustomer());
        opstmt.executeUpdate();
        o = new Order(14, "N N");
        o.getLines().add(new OrderLine(1400, 14, "Another good Java book", 1));
        for(OrderLine ol : o.getLines()) {
            olpstmt.setInt(1, ol.getId());
            olpstmt.setInt(2, ol.getOrderId());
            olpstmt.setString(3, ol.getItem());
            olpstmt.setInt(4, ol.getQty());
            olpstmt.executeUpdate();
        }
        opstmt.setInt(1, o.getId());
        opstmt.setString(2, o.getCustomer());
        opstmt.executeUpdate();
        opstmt.close();
        con.close();
    }
}

Build and run:

$ javac InsertAfter5.java Order.java OrderLine.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "InsertAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="orderline")
public class OrderLineJPA {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLineJPA() {
        this(0, 0, "", 0);
    }
    public OrderLineJPA(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="orderid")
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    @Column(name="item")
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    @Column(name="qty")
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="myorder")
public class OrderJPA {
    private int id;
    private String customer;
    private List<OrderLineJPA> lines;
    public OrderJPA() {
        this(0, "", new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer) {
        this(id, customer, new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer, List<OrderLineJPA> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="customer")
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    @OneToMany(mappedBy = "orderId", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<OrderLineJPA> getLines() {
        return lines;
    }
    public void setLines(List<OrderLineJPA> lines) {
        this.lines = lines;
    }
}

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class InsertAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Order");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        OrderJPA o;
        o = new OrderJPA(15, "O O");
        o.getLines().add(new OrderLineJPA(1500, 15, "A good Java book", 1));
        em.persist(o);
        o = new OrderJPA(16, "P P");
        o.getLines().add(new OrderLineJPA(1600, 16, "Another good Java book", 1));
        em.persist(o);
        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' InsertAfter6.java OrderJPA.java OrderLineJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "InsertAfter6"

order.pas:

type
   orderline = record
                  id : integer;
                  orderid : integer;
                  item : varying[32] of char;
                  qty : unsigned;
               end;
   order = record
              id : unsigned;
              customer : varying[32] of char;
              nlines : integer;
              line : array [1..100] of orderline;
           end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program list_after_2(input, output);

%include 'order.pas'

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;
   res : array [1..1000] of order;
   count : integer;
   lastid : integer;
   i, j : integer;

begin
   con := psqlite_open('order.db');
   sel := psqlite_prepare(con, 'SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid');
   count := 0;
   lastid := -1;
   while psqlite_step_query(sel) do begin
      if psqlite_column_int(sel, 0) <> lastid then begin
         lastid := psqlite_column_int(sel, 0);
         count := count + 1;
         res[count].id := psqlite_column_int(sel, 0);
         res[count].customer := psqlite_column_text(sel, 1);
         res[count].nlines := 0;
      end; 
      res[count].nlines := res[count].nlines + 1;
      res[count].line[res[count].nlines].id := psqlite_column_int(sel, 2);
      res[count].line[res[count].nlines].orderid := psqlite_column_int(sel, 3);
      res[count].line[res[count].nlines].item := psqlite_column_text(sel, 4);
      res[count].line[res[count].nlines].qty := psqlite_column_int(sel, 5);
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
   for i := 1 to count do begin
      writeln(res[i].id:1, ' ', res[i].customer);
      for j := 1 to res[i].nlines do begin
         writeln('  ', res[i].line[j].item, ': ', res[i].line[j].qty);
      end;
   end;
end.

Build and run:

$ pas list_after_2
$ link list_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run list_after_2

order.py:

class OrderLine(object):
    def __init__(self, _id = 0, _orderid = 0, _item = '', _qty = 0):
        self.id = _id
        self.orderid = _orderid
        self.item = _item
        self.qty = _qty

class Order(object):
    def __init__(self, _id = 0, _customer = '', _lines = []):
        self.id = _id
        self.customer = _customer
        self.lines = _lines

import sqlite3

from order import Order, OrderLine

con = sqlite3.connect('order.db')
c = con.cursor()
c.execute('SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid')
res = []
lastid = -1
lasto = None
for row in c.fetchall():
    if row[0] != lastid:
        lastid = row[0]
        lasto = Order(row[0], row[1], [])
        res.append(lasto)
    lasto.lines.append(OrderLine(row[2], row[3], row[4], row[5]))
con.commit()
con.close()

for o in res:
    print('%d %s' % (o.id, o.customer))
    for ol in o.lines:
        print('  %s : %d' % (ol.item, ol.qty))

order.php:

<?php
class OrderLine {
    public $id;
    public $orderid;
    public $item;
    public $qty;
    public function __construct($id = 0, $orderid = 0, $item = '', $qty = 0) {
        $this->id = $id;
        $this->orderid = $orderid;
        $this->item = $item;
        $this->qty = $qty;
    }
}

class Order {
    public $id;
    public $customer;
    public $lines;
    public function __construct($id = 0, $customer = '', $lines = array()) {
        $this->id = $id;
        $this->customer = $customer;
        $this->lines = $lines;
    }
}

?>

<?php
include '/disk2/arne/migr/reltwo/order.php';

$con = new PDO('sqlite:/disk2/arne/migr/reltwo/order.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);
$stmt = $con->prepare('SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid');
$stmt->execute(array());
$res = array();
$lastid = -1;
$lasto = null;
while($row = $stmt->fetch()) {
    if($lastid != $row[0]) {
        $lastid = $row[0];
        $lasto = new Order($row[0], $row[1], array());
        $res[] = $lasto;
    }
    $lasto->lines[] = new OrderLine($row[2], $row[3], $row[4], $row[5]);
}

foreach($res as $o) {
    echo sprintf('%d %s', $o->id, $o->customer) . "\r\n";
    foreach($o->lines as $ol) {
        echo sprintf('  %s : %d', $ol->item, $ol->qty) . "\r\n";
    }
}
?>

public class OrderLine {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLine() {
        this(0, 0, "", 0);
    }
    public OrderLine(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

public class Order {
    private int id;
    private String customer;
    private List<OrderLine> lines;
    public Order() {
        this(0, "", new ArrayList<OrderLine>());
    }
    public Order(int id, String customer) {
        this(id, customer, new ArrayList<OrderLine>());
    }
    public Order(int id, String customer, List<OrderLine> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    public List<OrderLine> getLines() {
        return lines;
    }
    public void setLines(List<OrderLine> lines) {
        this.lines = lines;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ListAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        List<Order> res = new ArrayList<Order>();
        Connection con = DriverManager.getConnection("jdbc:sqlite:order.db");
        Statement stmt = con.createStatement();
        ResultSet rs = stmt.executeQuery("SELECT myorder.id,myorder.customer,orderline.id,orderline.orderid,orderline.item,orderline.qty FROM myorder JOIN orderline ON myorder.id = orderline.orderid");
        int lastid = -1;
        Order lasto = null;
        while(rs.next()) {
            int id = rs.getInt(1);
            String customer = rs.getString(2);
            int lineid = rs.getInt(3);
            int orderid = rs.getInt(4);
            String item = rs.getString(5);
            int qty = rs.getInt(6);
            if(id != lastid) {
                lastid = id;
                lasto = new Order(id, customer);
                res.add(lasto);
            }
            OrderLine line = new OrderLine(lineid, orderid, item, qty);
            lasto.getLines().add(line);
        }
        rs.close();
        stmt.close();
        con.close();
        for(Order o : res) {
            System.out.printf("%d %s\n", o.getId(), o.getCustomer());
            for(OrderLine ol : o.getLines()) {
                System.out.printf("  %s : %d\n", ol.getItem(), ol.getQty());
            }
        }
    }
}

Build and run:

$ javac ListAfter5.java Order.java OrderLine.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;

@Entity
@Table(name="orderline")
public class OrderLineJPA {
    private int id;
    private int orderId;
    private String item;
    private int qty;
    public OrderLineJPA() {
        this(0, 0, "", 0);
    }
    public OrderLineJPA(int id, int orderId, String item, int qty) {
        this.id = id;
        this.orderId = orderId;
        this.item = item;
        this.qty = qty;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="orderid")
    public int getOrderId() {
        return orderId;
    }
    public void setOrderId(int orderId) {
        this.orderId = orderId;
    }
    @Column(name="item")
    public String getItem() {
        return item;
    }
    public void setItem(String item) {
        this.item = item;
    }
    @Column(name="qty")
    public int getQty() {
        return qty;
    }
    public void setQty(int qty) {
        this.qty = qty;
    }
}

import java.util.ArrayList;
import java.util.List;

import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.Id;
import javax.persistence.OneToMany;
import javax.persistence.Table;

@Entity
@Table(name="myorder")
public class OrderJPA {
    private int id;
    private String customer;
    private List<OrderLineJPA> lines;
    public OrderJPA() {
        this(0, "", new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer) {
        this(id, customer, new ArrayList<OrderLineJPA>());
    }
    public OrderJPA(int id, String customer, List<OrderLineJPA> lines) {
        this.id = id;
        this.customer = customer;
        this.lines = lines;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="customer")
    public String getCustomer() {
        return customer;
    }
    public void setCustomer(String customer) {
        this.customer = customer;
    }
    @OneToMany(mappedBy = "orderId", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    public List<OrderLineJPA> getLines() {
        return lines;
    }
    public void setLines(List<OrderLineJPA> lines) {
        this.lines = lines;
    }
}

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class ListAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Order");
        EntityManager em = emf.createEntityManager();
        TypedQuery<OrderJPA> q = em.createQuery("SELECT o FROM OrderJPA AS o JOIN FETCH o.lines ol", OrderJPA.class);
        List<OrderJPA> res = q.getResultList();
        for(OrderJPA o : res) {
            System.out.printf("%d %s\n", o.getId(), o.getCustomer());
            for(OrderLineJPA ol : o.getLines()) {
                System.out.printf("  %s : %d\n", ol.getItem(), ol.getQty());
            }
        }
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' ListAfter6.java OrderJPA.java OrderLineJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListAfter6"

asset_isam.pas:

const
   TRUCK = 1;
   TRAILER = 2;
   TOOL = 3;

const
   PERMOPEN = 1;
   COVERABLE = 2;
   REALROOF = 3;

type
   asset = record
               id : [key(0)]unsigned;
               description : packed array [1..32] of char;
               assettype : integer;
               case integer of
                  TRUCK:
                     (
                        model : packed array[1..16] of char;
                        license : packed array[1..8] of char;
                        towcapacity : integer;
                     );
                  TRAILER:
                     (
                        ownweight : integer;
                        maxcargo : integer;
                        roof : integer;
                     );
                  TOOL:
                     (
                        tooltype : packed array[1..16] of char;
                        name : packed array[1..16] of char;
                        weight : integer;
                     );
            end;

program insert_before_2(input, output);

%include 'asset_isam.pas'

var
   a : asset;
   db : file of asset;

begin
   open(db, 'asset.isq', unknown, organization := indexed, access_method := keyed);
   a.id := 4;
   a.description := 'Blue truck';
   a.assettype := TRUCK;
   a.model := 'Ford F-250';
   a.license := 'CD5678';
   a.towcapacity := 10000;
   db^ := a;
   put(db);
   a.id := 5;
   a.description := 'Large trailer';
   a.assettype := TRAILER;
   a.ownweight := 1000;
   a.maxcargo := 6000;
   a.roof := PERMOPEN;
   db^ := a;
   put(db);
   a.id := 6;
   a.description := 'Lawn tractor #2';
   a.assettype := TOOL;
   a.tooltype := 'Lawn tractor';
   a.name := 'Craftsman';
   a.weight := 1000;
   db^ := a;
   put(db);
   close(db);
end.

Build and run:

$ pas insert_before_2
$ link insert_before_2
$ run insert_before_2

asset_isam.pas:

const
   TRUCK = 1;
   TRAILER = 2;
   TOOL = 3;

const
   PERMOPEN = 1;
   COVERABLE = 2;
   REALROOF = 3;

type
   asset = record
               id : [key(0)]unsigned;
               description : packed array [1..32] of char;
               assettype : integer;
               case integer of
                  TRUCK:
                     (
                        model : packed array[1..16] of char;
                        license : packed array[1..8] of char;
                        towcapacity : integer;
                     );
                  TRAILER:
                     (
                        ownweight : integer;
                        maxcargo : integer;
                        roof : integer;
                     );
                  TOOL:
                     (
                        tooltype : packed array[1..16] of char;
                        name : packed array[1..16] of char;
                        weight : integer;
                     );
            end;

program list_before_2(input, output);

%include 'asset_isam.pas'

var
   a : asset;
   db : file of asset;

begin
   open(db, 'asset.isq', unknown, organization := indexed, access_method := keyed);
   resetk(db, 0);
   while not eof(db) do begin
      a := db^;
      write(a.id, ' ', a.description, ' ');
      case a.assettype of
         TRUCK:
            begin
               write(a.model, ' ', a.license, ' ', a.towcapacity);
            end;
         TRAILER:
            begin
               write(a.ownweight, ' ', a.maxcargo, ' ', a.roof);
            end;
         TOOL:
            begin
               write(a.tooltype, ' ', a.name, ' ', a.weight);
            end;
      end;
      writeln;
      get(db);
   end;
   close(db);
end.

Build and run:

$ pas list_before_2
$ link list_before_2
$ run list_before_2

asset.pas:

const
   TRUCK = 1;
   TRAILER = 2;
   TOOL = 3;

const
   PERMOPEN = 1;
   COVERABLE = 2;
   REALROOF = 3;

type
   asset = record
               id : unsigned;
               description : varying[32] of char;
               assettype : integer;
               case integer of
                  TRUCK:
                     (
                        model : varying[16] of char;
                        license : varying[8] of char;
                        towcapacity : integer;
                     );
                  TRAILER:
                     (
                        ownweight : integer;
                        maxcargo : integer;
                        roof : integer;
                     );
                  TOOL:
                     (
                        tooltype : varying[16] of char;
                        name : varying[16] of char;
                        weight : integer;
                     );
            end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program insert_after_2(input, output);

%include 'asset.pas'

var
   con : sqlite_ptr;
   ins, toolins : sqlite_stmt_ptr;
   a : asset;

begin
   con := psqlite_open('asset.db');
   ins := psqlite_prepare(con, 'INSERT INTO asset VALUES(?,?,?)');
   toolins := psqlite_prepare(con, 'INSERT INTO toolasset VALUES(?,?,?,?)');
   a.id := 8;
   a.description := 'Rake';
   a.assettype := TOOL;
   a.tooltype := 'Rake';
   a.name := 'Noname';
   a.weight := 5;
   psqlite_bind_int(ins, 1, a.id);
   psqlite_bind_text(ins, 2, a.description);
   psqlite_bind_int(ins, 3, a.assettype);
   psqlite_step_nonquery(ins);
   psqlite_bind_int(toolins, 1, a.id);
   psqlite_bind_text(toolins, 2, a.tooltype);
   psqlite_bind_text(toolins, 3, a.name);
   psqlite_bind_int(toolins, 4, a.weight);
   psqlite_step_nonquery(toolins);
   psqlite_finalize(ins);
   psqlite_finalize(toolins);
   psqlite_close(con);
end.

Build and run:

$ pas insert_after_2
$ link insert_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run insert_after_2

asset.py:

TRUCK = 1
TRAILER = 2
TOOL = 3

PERMOPEN = 1
COVERABLE = 2
REALROOF = 3

class Asset(object):
    def __init__(self, _id = 0, _description = '', _assettype = 0):
        self.id = _id
        self.description = _description
        self.assettype = _assettype

class TruckAsset(Asset):
    def __init__(self, _id = 0, _description = '', _model = '', _license = '', _towcapacity = 0):
        super(TruckAsset, self).__init__(_id, _description, TRUCK)
        self.model = _model
        self.license = _license
        self.towcapacity = _towcapacity

class TrailerAsset(Asset):
    def __init__(self, _id = 0, _description = '', _ownweight = 0, _maxcargo = 0, _roof = 0):
        super(TrailerAsset, self).__init__(_id, _description, TRAILER)
        self.ownweight = _ownweight
        self.maxcargo = _maxcargo
        self.roof = _roof

class ToolAsset(Asset):
    def __init__(self, _id = 0, _description = '', _tooltype = '', _name = '', _weight = 0):
        super(ToolAsset, self).__init__(_id, _description, TOOL)
        self.tooltype = _tooltype
        self.name = _name
        self.weight = _weight

import sqlite3

from asset import *

con = sqlite3.connect('asset.db')
c = con.cursor()
a = ToolAsset(9, 'Craftsman snowblower', 'Snowblower', 'Craftsman', 200)
c.execute('INSERT INTO asset VALUES(?,?,?)', (a.id, a.description, a.assettype))
c.execute('INSERT INTO toolasset VALUES(?,?,?,?)', (a.id, a.tooltype, a.name, a.weight))
con.commit()
con.close()

asset.php:

<?php
define('TRUCK', 1);
define('TRAILER', 2);
define('TOOL', 3);

define('PERMOPEN', 1);
define('COVERABLE', 2);
define('REALROOF', 3);

class Asset {
    public $id;
    public $description;
    public $assetType;
    public function __construct($id = 0, $description = '', $assetType = '') {
        $this->id = $id;
        $this->description = $description;
        $this->assetType = $assetType;
    }
}

class TruckAsset extends Asset {
    public $model;
    public $license;
    public $towcapacity;
    public function __construct($id = 0, $description = '', $model = '', $license = '', $towcapacity = 0) {
        parent::__construct($id, $description, TRUCK);
        $this->model = $model;
        $this->license = $license;
        $this->towcapacity = $towcapacity;
    }
}

class TrailerAsset extends Asset {
    public $ownWeight;
    public $maxCargo;
    public $roof;
    public function __construct($id = 0, $description = '', $ownWeight = 0, $maxCargo = 0, $roof = 0) {
        parent::__construct($id, $description, TRAILER);
        $this->ownWeight = $ownWeight;
        $this->maxCargo = $maxCargo;
        $this->roof = $roof;
    }
}

class ToolAsset extends Asset {
    public $toolType;
    public $name;
    public $weight;
    public function __construct($id = 0, $description = '', $toolType = '', $name = '', $weight = 0) {
        parent::__construct($id, $description, TOOL);
        $this->toolType = $toolType;
        $this->name = $name;
        $this->weight = $weight;
    }
}

?>

<?php
include '/disk2/arne/migr/inher/asset.php';

$con = new PDO('sqlite:/disk2/arne/migr/inher/asset.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);
$stmt = $con->prepare('INSERT INTO asset VALUES(:ID,:DESCRIPTION,:ASSETTYPE)');
$toolstmt = $con->prepare('INSERT INTO toolasset VALUES(:ID,:TOOLTYPE,:NAME,:WEIGHT)');
$a = new ToolAsset(10, 'Normal wheelbarrow', 'Wheelbarrow', 'Noname', 25);
$stmt->execute(array(':ID' => $a->id, ':DESCRIPTION' => $a->description, ':ASSETTYPE' => $a->assetType));
$toolstmt->execute(array(':ID' => $a->id, ':TOOLTYPE' => $a->toolType, ':NAME' => $a->name, ':WEIGHT' => $a->weight));
?>

public class Asset {
    public static final int TRUCK = 1;
    public static final int TRAILER = 2;
    public static final int TOOL = 3;
    private int id;
    private String description;
    private int assetType;
    public Asset() {
        this(0, "", 0);
    }
    public Asset(int id, String description, int assetType) {
        this.id = id;
        this.description = description;
        this.assetType = assetType;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public int getAssetType() {
        return assetType;
    }
    public void setAssetType(int assetType) {
        this.assetType = assetType;
    }
}

public class TruckAsset extends Asset {
    private String model;
    private String license;
    private int towcapacity;
    public TruckAsset() {
        this(0, "", "", "", 0);
    }
    public TruckAsset(int id, String description, String model, String license, int towcapacity) {
        super(id, description, TRUCK);
        this.model = model;
        this.license = license;
        this.towcapacity = towcapacity;
    }
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    public String getLicense() {
        return license;
    }
    public void setLicense(String license) {
        this.license = license;
    }
    public int getTowcapacity() {
        return towcapacity;
    }
    public void setTowcapacity(int towcapacity) {
        this.towcapacity = towcapacity;
    }
}

public class TrailerAsset extends Asset {
    private int ownWeight;
    private int maxCargo;
    private int roof;
    public TrailerAsset() {
        this(0, "", 0, 0, 0);
    }
    public TrailerAsset(int id, String description, int ownWeight, int maxCargo, int roof) {
        super(id, description, TRAILER);
        this.ownWeight = ownWeight;
        this.maxCargo = maxCargo;
        this.roof = roof;
    }
    public int getOwnWeight() {
        return ownWeight;
    }
    public void setOwnWeight(int ownWeight) {
        this.ownWeight = ownWeight;
    }
    public int getMaxCargo() {
        return maxCargo;
    }
    public void setMaxCargo(int maxCargo) {
        this.maxCargo = maxCargo;
    }
    public int getRoof() {
        return roof;
    }
    public void setRoof(int roof) {
        this.roof = roof;
    }
}

public class ToolAsset extends Asset {
    public static final int PERMOPEN = 1;
    public static final int COVERABLE = 2;
    public static final int REALROOF = 3;
    private String toolType;
    private String name;
    private int weight;
    public ToolAsset() {
        this(0, "", "", "", 0);
    }
    public ToolAsset(int id, String description, String toolType, String name, int weight) {
        super(id, description, TOOL);
        this.toolType = toolType;
        this.name = name;
        this.weight = weight;
    }
    public String getToolType() {
        return toolType;
    }
    public void setToolType(String toolType) {
        this.toolType = toolType;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getWeight() {
        return weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;

public class InsertAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        Connection con = DriverManager.getConnection("jdbc:sqlite:asset.db");
        PreparedStatement pstmt = con.prepareStatement("INSERT INTO asset VALUES(?,?,?)");
        PreparedStatement toolpstmt = con.prepareStatement("INSERT INTO toolasset VALUES(?,?,?,?)");
        ToolAsset a = new ToolAsset(11, "Echo edger", "Edger", "Echo", 25);
        pstmt.setInt(1, a.getId());
        pstmt.setString(2, a.getDescription());
        pstmt.setInt(3, a.getAssetType());
        pstmt.executeUpdate();
        toolpstmt.setInt(1, a.getId());
        toolpstmt.setString(2, a.getToolType());
        toolpstmt.setString(3, a.getName());
        toolpstmt.setInt(4, a.getWeight());
        toolpstmt.executeUpdate();
        pstmt.close();
        toolpstmt.close();
        con.close();
    }
}

Build and run:

$ javac InsertAfter5.java Asset.java TruckAsset.java TrailerAsset.java ToolAsset.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "InsertAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.Table;

@Entity
@Table(name="asset")
@Inheritance(strategy=InheritanceType.JOINED)
public class AssetJPA {
    public static final int TRUCK = 1;
    public static final int TRAILER = 2;
    public static final int TOOL = 3;
    private int id;
    private String description;
    private int assetType;
    public AssetJPA() {
        this(0, "", 0);
    }
    public AssetJPA(int id, String description, int assetType) {
        this.id = id;
        this.description = description;
        this.assetType = assetType;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="description")
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    @Column(name="assettype")
    public int getAssetType() {
        return assetType;
    }
    public void setAssetType(int assetType) {
        this.assetType = assetType;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="truckasset")
@PrimaryKeyJoinColumn(name="id")
public class TruckAssetJPA extends AssetJPA {
    private String model;
    private String license;
    private int towcapacity;
    public TruckAssetJPA() {
        this(0, "", "", "", 0);
    }
    public TruckAssetJPA(int id, String description, String model, String license, int towcapacity) {
        super(id, description, TRUCK);
        this.model = model;
        this.license = license;
        this.towcapacity = towcapacity;
    }
    @Column(name="model")
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    @Column(name="license")
    public String getLicense() {
        return license;
    }
    public void setLicense(String license) {
        this.license = license;
    }
    @Column(name="towcapacity")
    public int getTowcapacity() {
        return towcapacity;
    }
    public void setTowcapacity(int towcapacity) {
        this.towcapacity = towcapacity;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="trailerasset")
@PrimaryKeyJoinColumn(name="id")
public class TrailerAssetJPA extends AssetJPA {
    private int ownWeight;
    private int maxCargo;
    private int roof;
    public TrailerAssetJPA() {
        this(0, "", 0, 0, 0);
    }
    public TrailerAssetJPA(int id, String description, int ownWeight, int maxCargo, int roof) {
        super(id, description, TRAILER);
        this.ownWeight = ownWeight;
        this.maxCargo = maxCargo;
        this.roof = roof;
    }
    @Column(name="ownweight")
    public int getOwnWeight() {
        return ownWeight;
    }
    public void setOwnWeight(int ownWeight) {
        this.ownWeight = ownWeight;
    }
    @Column(name="maxcargo")
    public int getMaxCargo() {
        return maxCargo;
    }
    public void setMaxCargo(int maxCargo) {
        this.maxCargo = maxCargo;
    }
    @Column(name="roof")
    public int getRoof() {
        return roof;
    }
    public void setRoof(int roof) {
        this.roof = roof;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="toolasset")
@PrimaryKeyJoinColumn(name="id")
public class ToolAssetJPA extends AssetJPA {
    public static final int PERMOPEN = 1;
    public static final int COVERABLE = 2;
    public static final int REALROOF = 3;
    private String toolType;
    private String name;
    private int weight;
    public ToolAssetJPA() {
        this(0, "", "", "", 0);
    }
    public ToolAssetJPA(int id, String description, String toolType, String name, int weight) {
        super(id, description, TOOL);
        this.toolType = toolType;
        this.name = name;
        this.weight = weight;
    }
    @Column(name="tooltype")
    public String getToolType() {
        return toolType;
    }
    public void setToolType(String toolType) {
        this.toolType = toolType;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Column(name="weight")
    public int getWeight() {
        return weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
}

import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class InsertAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Asset");
        EntityManager em = emf.createEntityManager();
        em.getTransaction().begin();
        AssetJPA a = new ToolAssetJPA(12, "Echo blower", "Blower", "Echo", 50);
        em.persist(a);
        em.getTransaction().commit();
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' InsertAfter6.java AssetJPA.java TruckAssetJPA.java TrailerAssetJPA.java ToolAssetJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "InsertAfter6"

asset.pas:

const
   TRUCK = 1;
   TRAILER = 2;
   TOOL = 3;

const
   PERMOPEN = 1;
   COVERABLE = 2;
   REALROOF = 3;

type
   asset = record
               id : unsigned;
               description : varying[32] of char;
               assettype : integer;
               case integer of
                  TRUCK:
                     (
                        model : varying[16] of char;
                        license : varying[8] of char;
                        towcapacity : integer;
                     );
                  TRAILER:
                     (
                        ownweight : integer;
                        maxcargo : integer;
                        roof : integer;
                     );
                  TOOL:
                     (
                        tooltype : varying[16] of char;
                        name : varying[16] of char;
                        weight : integer;
                     );
            end;

[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program list_after_2(input, output);

%include 'asset.pas'

var
   con : sqlite_ptr;
   sel : sqlite_stmt_ptr;
   count : integer;
   res : array [1..1000] of asset;
   i : integer;

begin
   con := psqlite_open('asset.db');
   count := 0;
   sel := psqlite_prepare(con, 'SELECT asset.id,asset.description,asset.assettype,truckasset.model,truckasset.license,truckasset.towcapacity FROM asset JOIN truckasset ON asset.id = truckasset.id');
   while psqlite_step_query(sel) do begin
      count := count + 1;
      res[count].id := psqlite_column_int(sel, 0);
      res[count].description := psqlite_column_text(sel, 1);
      res[count].assettype := psqlite_column_int(sel, 2);
      res[count].model := psqlite_column_text(sel, 3);
      res[count].license := psqlite_column_text(sel, 4);
      res[count].towcapacity := psqlite_column_int(sel, 5);
   end;
   psqlite_finalize(sel);
   sel := psqlite_prepare(con, 'SELECT asset.id,asset.description,asset.assettype,trailerasset.ownweight,trailerasset.maxcargo,trailerasset.roof FROM asset JOIN trailerasset ON asset.id = trailerasset.id');
   while psqlite_step_query(sel) do begin
      count := count + 1;
      res[count].id := psqlite_column_int(sel, 0);
      res[count].description := psqlite_column_text(sel, 1);
      res[count].assettype := psqlite_column_int(sel, 2);
      res[count].ownweight := psqlite_column_int(sel, 3);
      res[count].maxcargo := psqlite_column_int(sel, 4);
      res[count].roof := psqlite_column_int(sel, 5);
   end;
   psqlite_finalize(sel);
   sel := psqlite_prepare(con, 'SELECT asset.id,asset.description,asset.assettype,toolasset.tooltype,toolasset.name,toolasset.weight FROM asset JOIN toolasset ON asset.id = toolasset.id');
   while psqlite_step_query(sel) do begin
      count := count + 1;
      res[count].id := psqlite_column_int(sel, 0);
      res[count].description := psqlite_column_text(sel, 1);
      res[count].assettype := psqlite_column_int(sel, 2);
      res[count].tooltype := psqlite_column_text(sel, 3);
      res[count].name := psqlite_column_text(sel, 4);
      res[count].weight := psqlite_column_int(sel, 5);
   end;
   psqlite_finalize(sel);
   psqlite_close(con);
   for i := 1 to count do begin
      write(res[i].id, ' ', res[i].description, ' ');
      case res[i].assettype of
         TRUCK:
            begin
               write(res[i].model, ' ', res[i].license, ' ', res[i].towcapacity);
            end;
         TRAILER:
            begin
               write(res[i].ownweight, ' ', res[i].maxcargo, ' ', res[i].roof);
            end;
         TOOL:
            begin
               write(res[i].tooltype, ' ', res[i].name, ' ', res[i].weight);
            end;
      end;
      writeln;
   end;
end.

Build and run:

$ pas list_after_2
$ link list_after_2 + sys$input/opt + psqlitedir:sqlite/opt
psqlitedir:psqlite
psqlitedir:sqlite
$
$ run list_after_2

asset.py:

TRUCK = 1
TRAILER = 2
TOOL = 3

PERMOPEN = 1
COVERABLE = 2
REALROOF = 3

class Asset(object):
    def __init__(self, _id = 0, _description = '', _assettype = 0):
        self.id = _id
        self.description = _description
        self.assettype = _assettype

class TruckAsset(Asset):
    def __init__(self, _id = 0, _description = '', _model = '', _license = '', _towcapacity = 0):
        super(TruckAsset, self).__init__(_id, _description, TRUCK)
        self.model = _model
        self.license = _license
        self.towcapacity = _towcapacity

class TrailerAsset(Asset):
    def __init__(self, _id = 0, _description = '', _ownweight = 0, _maxcargo = 0, _roof = 0):
        super(TrailerAsset, self).__init__(_id, _description, TRAILER)
        self.ownweight = _ownweight
        self.maxcargo = _maxcargo
        self.roof = _roof

class ToolAsset(Asset):
    def __init__(self, _id = 0, _description = '', _tooltype = '', _name = '', _weight = 0):
        super(ToolAsset, self).__init__(_id, _description, TOOL)
        self.tooltype = _tooltype
        self.name = _name
        self.weight = _weight

import sqlite3

from asset import *

con = sqlite3.connect('asset.db')
c = con.cursor()
res = []
c.execute('SELECT asset.id,asset.description,asset.assettype,truckasset.model,truckasset.license,truckasset.towcapacity FROM asset JOIN truckasset ON asset.id = truckasset.id')
for row in c.fetchall():
    res.append(TruckAsset(row[0], row[1], row[3], row[4], row[5]))
c.execute('SELECT asset.id,asset.description,asset.assettype,trailerasset.ownweight,trailerasset.maxcargo,trailerasset.roof FROM asset JOIN trailerasset ON asset.id = trailerasset.id')
for row in c.fetchall():
    res.append(TrailerAsset(row[0], row[1], row[3], row[4], row[5]))
c.execute('SELECT asset.id,asset.description,asset.assettype,toolasset.tooltype,toolasset.name,toolasset.weight FROM asset JOIN toolasset ON asset.id = toolasset.id')
for row in c.fetchall():
    res.append(ToolAsset(row[0], row[1], row[3], row[4], row[5]))
con.commit()
con.close()

for o in res:
    if isinstance(o, TruckAsset):
        print('%d %s %s %s %d' % (o.id, o.description, o.model, o.license, o.towcapacity))
    if isinstance(o, TrailerAsset):                    
        print('%d %s %d %d %d' % (o.id, o.description, o.ownweight, o.maxcargo, o.roof))
    if isinstance(o, ToolAsset):
        print('%d %s %s %s %d' % (o.id, o.description, o.tooltype, o.name, o.weight))

asset.php:

<?php
define('TRUCK', 1);
define('TRAILER', 2);
define('TOOL', 3);

define('PERMOPEN', 1);
define('COVERABLE', 2);
define('REALROOF', 3);

class Asset {
    public $id;
    public $description;
    public $assetType;
    public function __construct($id = 0, $description = '', $assetType = '') {
        $this->id = $id;
        $this->description = $description;
        $this->assetType = $assetType;
    }
}

class TruckAsset extends Asset {
    public $model;
    public $license;
    public $towcapacity;
    public function __construct($id = 0, $description = '', $model = '', $license = '', $towcapacity = 0) {
        parent::__construct($id, $description, TRUCK);
        $this->model = $model;
        $this->license = $license;
        $this->towcapacity = $towcapacity;
    }
}

class TrailerAsset extends Asset {
    public $ownWeight;
    public $maxCargo;
    public $roof;
    public function __construct($id = 0, $description = '', $ownWeight = 0, $maxCargo = 0, $roof = 0) {
        parent::__construct($id, $description, TRAILER);
        $this->ownWeight = $ownWeight;
        $this->maxCargo = $maxCargo;
        $this->roof = $roof;
    }
}

class ToolAsset extends Asset {
    public $toolType;
    public $name;
    public $weight;
    public function __construct($id = 0, $description = '', $toolType = '', $name = '', $weight = 0) {
        parent::__construct($id, $description, TOOL);
        $this->toolType = $toolType;
        $this->name = $name;
        $this->weight = $weight;
    }
}

?>

<?php
include '/disk2/arne/migr/inher/asset.php';

$con = new PDO('sqlite:/disk2/arne/migr/inher/asset.db');
$con->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$con->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_NUM);

$res = array();
$stmt = $con->prepare('SELECT asset.id,asset.description,asset.assettype,truckasset.model,truckasset.license,truckasset.towcapacity FROM asset JOIN truckasset ON asset.id = truckasset.id');
$stmt->execute(array());
while($row = $stmt->fetch()) {
   $res[] = new TruckAsset($row[0], $row[1], $row[3], $row[4], $row[5]);
}
$stmt = $con->prepare('SELECT asset.id,asset.description,asset.assettype,trailerasset.ownweight,trailerasset.maxcargo,trailerasset.roof FROM asset JOIN trailerasset ON asset.id = trailerasset.id');
$stmt->execute(array());
while($row = $stmt->fetch()) {
   $res[] = new TrailerAsset($row[0], $row[1], $row[3], $row[4], $row[5]);
}
$stmt = $con->prepare('SELECT asset.id,asset.description,asset.assettype,toolasset.tooltype,toolasset.name,toolasset.weight FROM asset JOIN toolasset ON asset.id = toolasset.id');
$stmt->execute(array());
while($row = $stmt->fetch()) {
   $res[] = new ToolAsset($row[0], $row[1], $row[3], $row[4], $row[5]);
}

foreach($res as $o) {
    if($o instanceof TruckAsset) {
        echo sprintf('%d %s %s %s %d', $o->id, $o->description, $o->model, $o->license, $o->towcapacity) . "\r\n";
    }
    if($o instanceof TrailerAsset) {
        echo sprintf('%d %s %d %d %d', $o->id, $o->description, $o->ownWeight, $o->maxCargo, $o->roof) . "\r\n";
    }
    if($o instanceof ToolAsset) {
        echo sprintf('%d %s %s %s %d', $o->id, $o->description, $o->toolType, $o->name, $o->weight) . "\r\n";
    }
}
?>

public class Asset {
    public static final int TRUCK = 1;
    public static final int TRAILER = 2;
    public static final int TOOL = 3;
    private int id;
    private String description;
    private int assetType;
    public Asset() {
        this(0, "", 0);
    }
    public Asset(int id, String description, int assetType) {
        this.id = id;
        this.description = description;
        this.assetType = assetType;
    }
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    public int getAssetType() {
        return assetType;
    }
    public void setAssetType(int assetType) {
        this.assetType = assetType;
    }
}

public class TruckAsset extends Asset {
    private String model;
    private String license;
    private int towcapacity;
    public TruckAsset() {
        this(0, "", "", "", 0);
    }
    public TruckAsset(int id, String description, String model, String license, int towcapacity) {
        super(id, description, TRUCK);
        this.model = model;
        this.license = license;
        this.towcapacity = towcapacity;
    }
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    public String getLicense() {
        return license;
    }
    public void setLicense(String license) {
        this.license = license;
    }
    public int getTowcapacity() {
        return towcapacity;
    }
    public void setTowcapacity(int towcapacity) {
        this.towcapacity = towcapacity;
    }
}

public class TrailerAsset extends Asset {
    private int ownWeight;
    private int maxCargo;
    private int roof;
    public TrailerAsset() {
        this(0, "", 0, 0, 0);
    }
    public TrailerAsset(int id, String description, int ownWeight, int maxCargo, int roof) {
        super(id, description, TRAILER);
        this.ownWeight = ownWeight;
        this.maxCargo = maxCargo;
        this.roof = roof;
    }
    public int getOwnWeight() {
        return ownWeight;
    }
    public void setOwnWeight(int ownWeight) {
        this.ownWeight = ownWeight;
    }
    public int getMaxCargo() {
        return maxCargo;
    }
    public void setMaxCargo(int maxCargo) {
        this.maxCargo = maxCargo;
    }
    public int getRoof() {
        return roof;
    }
    public void setRoof(int roof) {
        this.roof = roof;
    }
}

public class ToolAsset extends Asset {
    public static final int PERMOPEN = 1;
    public static final int COVERABLE = 2;
    public static final int REALROOF = 3;
    private String toolType;
    private String name;
    private int weight;
    public ToolAsset() {
        this(0, "", "", "", 0);
    }
    public ToolAsset(int id, String description, String toolType, String name, int weight) {
        super(id, description, TOOL);
        this.toolType = toolType;
        this.name = name;
        this.weight = weight;
    }
    public String getToolType() {
        return toolType;
    }
    public void setToolType(String toolType) {
        this.toolType = toolType;
    }
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    public int getWeight() {
        return weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
}

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class ListAfter5 {
    public static void main(String[] args) throws Exception {
        Class.forName("org.sqlite.JDBC");
        List<Asset> res = new ArrayList<Asset>();
        Connection con = DriverManager.getConnection("jdbc:sqlite:asset.db");
        Statement stmt = con.createStatement();
        ResultSet rs;
        rs = stmt.executeQuery("SELECT asset.id,asset.description,asset.assettype,truckasset.model,truckasset.license,truckasset.towcapacity FROM asset JOIN truckasset ON asset.id = truckasset.id");
        while(rs.next()) {
            int id = rs.getInt(1);
            String description = rs.getString(2);
            int assettype = rs.getInt(3);
            String model = rs.getString(4);
            String license = rs.getString(5);
            int towcapacity = rs.getInt(6);
            res.add(new TruckAsset(id, description, model, license, towcapacity));
        }
        rs.close();
        rs = stmt.executeQuery("SELECT asset.id,asset.description,asset.assettype,trailerasset.ownweight,trailerasset.maxcargo,trailerasset.roof FROM asset JOIN trailerasset ON asset.id = trailerasset.id");
        while(rs.next()) {
            int id = rs.getInt(1);
            String description = rs.getString(2);
            int assettype = rs.getInt(3);
            int ownweight = rs.getInt(4);
            int maxcargo = rs.getInt(5);
            int roof = rs.getInt(6);
            res.add(new TrailerAsset(id, description, ownweight, maxcargo, roof));
        }
        rs.close();
        rs = stmt.executeQuery("SELECT asset.id,asset.description,asset.assettype,toolasset.tooltype,toolasset.name,toolasset.weight FROM asset JOIN toolasset ON asset.id = toolasset.id");
        while(rs.next()) {
            int id = rs.getInt(1);
            String description = rs.getString(2);
            int assettype = rs.getInt(3);
            String tooltype = rs.getString(4);
            String name = rs.getString(5);
            int weight = rs.getInt(6);
            res.add(new ToolAsset(id, description, tooltype, name, weight));
        }
        rs.close();
        stmt.close();
        con.close();
        for(Asset o : res) {
            if(o instanceof TruckAsset) {
                TruckAsset o2 = (TruckAsset)o;
                System.out.printf("%d %s %s %s %d\n", o2.getId(), o2.getDescription(), o2.getModel(), o2.getLicense(), o2.getTowcapacity());
            }
            if(o instanceof TrailerAsset) {
                TrailerAsset o2 = (TrailerAsset)o;
                System.out.printf("%d %s %d %d %d\n", o2.getId(), o2.getDescription(), o2.getOwnWeight(), o2.getMaxCargo(), o2.getRoof());
            }
            if(o instanceof ToolAsset) {
                ToolAsset o2 = (ToolAsset)o;
                System.out.printf("%d %s %s %s %d\n", o2.getId(), o2.getDescription(), o2.getToolType(), o2.getName(), o2.getWeight());
            }
        }
    }
}

Build and run:

$ javac ListAfter5.java Asset.java TruckAsset.java TrailerAsset.java ToolAsset.java
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListAfter5"

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Inheritance;
import javax.persistence.InheritanceType;
import javax.persistence.Table;

@Entity
@Table(name="asset")
@Inheritance(strategy=InheritanceType.JOINED)
public class AssetJPA {
    public static final int TRUCK = 1;
    public static final int TRAILER = 2;
    public static final int TOOL = 3;
    private int id;
    private String description;
    private int assetType;
    public AssetJPA() {
        this(0, "", 0);
    }
    public AssetJPA(int id, String description, int assetType) {
        this.id = id;
        this.description = description;
        this.assetType = assetType;
    }
    @Id
    @Column(name="id")
    public int getId() {
        return id;
    }
    public void setId(int id) {
        this.id = id;
    }
    @Column(name="description")
    public String getDescription() {
        return description;
    }
    public void setDescription(String description) {
        this.description = description;
    }
    @Column(name="assettype")
    public int getAssetType() {
        return assetType;
    }
    public void setAssetType(int assetType) {
        this.assetType = assetType;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="truckasset")
@PrimaryKeyJoinColumn(name="id")
public class TruckAssetJPA extends AssetJPA {
    private String model;
    private String license;
    private int towcapacity;
    public TruckAssetJPA() {
        this(0, "", "", "", 0);
    }
    public TruckAssetJPA(int id, String description, String model, String license, int towcapacity) {
        super(id, description, TRUCK);
        this.model = model;
        this.license = license;
        this.towcapacity = towcapacity;
    }
    @Column(name="model")
    public String getModel() {
        return model;
    }
    public void setModel(String model) {
        this.model = model;
    }
    @Column(name="license")
    public String getLicense() {
        return license;
    }
    public void setLicense(String license) {
        this.license = license;
    }
    @Column(name="towcapacity")
    public int getTowcapacity() {
        return towcapacity;
    }
    public void setTowcapacity(int towcapacity) {
        this.towcapacity = towcapacity;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="trailerasset")
@PrimaryKeyJoinColumn(name="id")
public class TrailerAssetJPA extends AssetJPA {
    private int ownWeight;
    private int maxCargo;
    private int roof;
    public TrailerAssetJPA() {
        this(0, "", 0, 0, 0);
    }
    public TrailerAssetJPA(int id, String description, int ownWeight, int maxCargo, int roof) {
        super(id, description, TRAILER);
        this.ownWeight = ownWeight;
        this.maxCargo = maxCargo;
        this.roof = roof;
    }
    @Column(name="ownweight")
    public int getOwnWeight() {
        return ownWeight;
    }
    public void setOwnWeight(int ownWeight) {
        this.ownWeight = ownWeight;
    }
    @Column(name="maxcargo")
    public int getMaxCargo() {
        return maxCargo;
    }
    public void setMaxCargo(int maxCargo) {
        this.maxCargo = maxCargo;
    }
    @Column(name="roof")
    public int getRoof() {
        return roof;
    }
    public void setRoof(int roof) {
        this.roof = roof;
    }
}

import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;

@Entity
@Table(name="toolasset")
@PrimaryKeyJoinColumn(name="id")
public class ToolAssetJPA extends AssetJPA {
    public static final int PERMOPEN = 1;
    public static final int COVERABLE = 2;
    public static final int REALROOF = 3;
    private String toolType;
    private String name;
    private int weight;
    public ToolAssetJPA() {
        this(0, "", "", "", 0);
    }
    public ToolAssetJPA(int id, String description, String toolType, String name, int weight) {
        super(id, description, TOOL);
        this.toolType = toolType;
        this.name = name;
        this.weight = weight;
    }
    @Column(name="tooltype")
    public String getToolType() {
        return toolType;
    }
    public void setToolType(String toolType) {
        this.toolType = toolType;
    }
    @Column(name="name")
    public String getName() {
        return name;
    }
    public void setName(String name) {
        this.name = name;
    }
    @Column(name="weight")
    public int getWeight() {
        return weight;
    }
    public void setWeight(int weight) {
        this.weight = weight;
    }
}

import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
import javax.persistence.TypedQuery;

public class ListAfter6 {
    public static void main(String[] args) throws Exception {
        Logger.getLogger("org.hibernate").setLevel(Level.OFF);
        EntityManagerFactory emf = Persistence.createEntityManagerFactory("Asset");
        EntityManager em = emf.createEntityManager();
        TypedQuery<AssetJPA> q = em.createQuery("SELECT o FROM AssetJPA AS o", AssetJPA.class);
        List<AssetJPA> res = q.getResultList();
        for(AssetJPA o : res) {
            if(o instanceof TruckAssetJPA) {
                TruckAssetJPA o2 = (TruckAssetJPA)o;
                System.out.printf("%d %s %s %s %d\n", o2.getId(), o2.getDescription(), o2.getModel(), o2.getLicense(), o2.getTowcapacity());
            }
            if(o instanceof TrailerAssetJPA) {
                TrailerAssetJPA o2 = (TrailerAssetJPA)o;
                System.out.printf("%d %s %d %d %d\n", o2.getId(), o2.getDescription(), o2.getOwnWeight(), o2.getMaxCargo(), o2.getRoof());
            }
            if(o instanceof ToolAssetJPA) {
                ToolAssetJPA o2 = (ToolAssetJPA)o;
                System.out.printf("%d %s %s %s %d\n", o2.getId(), o2.getDescription(), o2.getToolType(), o2.getName(), o2.getWeight());
            }
        }
        em.close();
        emf.close();
    }
}

Build and run:

$ javac -cp 'hibpath' ListAfter6.java AssetJPA.java TruckAssetJPA.java TrailerAssetJPA.java ToolAssetJPA.java
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListAfter6"