This article is a followup on the articles:
These articles cover:
This article has a much narrower scope and does not cover:
But instead this article goes much deeper and use more complex examples.
The original 2 articles only used the query:
SELECT f1,f2 FROM t1
This article use the queries:
INSERT INTO customer VALUES(?, ?, ?);
INSERT INTO customer_actual VALUES(?, ?, ?, ?, ?);
INSERT INTO order_main VALUES(?, ?, ?);
INSERT INTO order_line VALUES(?, ?, ?, ?, ?);
and:
SELECT '**future customer**', customer.id AS cid, name, phone, source
FROM customer JOIN customer_potential ON customer.id = customer_potential.id
ORDER BY cid;
SELECT customer.id AS cid, name, address1, address2, phone, order_main.id AS oid, status, SUM(quantity * price) AS sumval
FROM customer JOIN customer_actual ON customer.id = customer_actual.id
JOIN order_main ON customer.id = order_main.customer
JOIN order_line ON order_main.id = order_line.order_main
GROUP BY customer.id, name, address1, address2, phone, order_main.id, status
ORDER BY cid, oid;
The SQL is still relative simple, but does show elements like parameters and JOIN.
The article will cover 3 common types of database access:
Two types of Database API's exist:
We will setup tables in and test 7 databases on VMS:
The following table structure and initial data will be loaded:
CREATE TABLE customer (
id INTEGER NOT NULL,
name VARCHAR(32),
phone VARCHAR(16),
PRIMARY KEY(id)
);
CREATE TABLE customer_potential (
id INTEGER NOT NULL,
source VARCHAR(256),
PRIMARY KEY(id)
);
CREATE TABLE customer_actual (
id INTEGER NOT NULL,
address1 VARCHAR(64),
address2 VARCHAR(64),
contact VARCHAR(32),
discount INTEGER,
PRIMARY KEY(id)
);
CREATE TABLE order_main (
id INTEGER NOT NULL,
customer INTEGER NOT NULL,
status VARCHAR(16),
PRIMARY KEY(id)
);
CREATE TABLE order_line (
id INTEGER NOT NULL,
order_main INTEGER NOT NULL,
item VARCHAR(32),
quantity INTEGER,
price DECIMAL(12,2),
PRIMARY KEY(id)
);
INSERT INTO customer VALUES(1, 'A company', '1111-1111');
INSERT INTO customer VALUES(2, 'B company', '2222-2222');
INSERT INTO customer VALUES(3, 'C company', '3333-3333');
INSERT INTO customer VALUES(4, 'D company', '4444-4444');
INSERT INTO customer VALUES(5, 'E company', '5555-5555');
INSERT INTO customer_potential VALUES(1, 'Sleazy information seller');
INSERT INTO customer_potential VALUES(2, 'Sleazy information seller');
INSERT INTO customer_actual VALUES(3, 'C road 3', 'C town', 'Mr. C', 10);
INSERT INTO customer_actual VALUES(4, 'D road 4', 'D town', 'Mr. D', 20);
INSERT INTO customer_actual VALUES(5, 'E road 5', 'E town', 'Mr. E', 10);
INSERT INTO order_main VALUES(1, 3, 'Delivered');
INSERT INTO order_main VALUES(2, 4, 'Delivered');
INSERT INTO order_main VALUES(3, 4, 'Delivered');
INSERT INTO order_main VALUES(4, 4, 'Delivered');
INSERT INTO order_main VALUES(5, 5, 'Delivered');
INSERT INTO order_line VALUES(1, 1, 'X stuff', 1, 75.00);
INSERT INTO order_line VALUES(2, 2, 'Y stuff', 10, 40.00);
INSERT INTO order_line VALUES(3, 3, 'Y stuff', 5, 40.00);
INSERT INTO order_line VALUES(4, 4, 'Y stuff', 20, 40.00);
INSERT INTO order_line VALUES(5, 5, 'X stuff', 1, 72.00);
INSERT INTO order_line VALUES(6, 5, 'Y stuff', 1, 45.00);
INSERT INTO order_line VALUES(7, 5, 'Z stuff', 1, 90.00);
CREATE TABLE abbr (
id INTEGER NOT NULL,
term VARCHAR(32),
PRIMARY KEY(id)
);
INSERT INTO abbr VALUES(1, 'Internationalization');
INSERT INTO abbr VALUES(2, 'Localization');
CREATE TABLE ts (
id INTEGER NOT NULL,
dt DATETIME,
PRIMARY KEY (id)
);
INSERT INTO ts VALUES(1, '2023-10-01 08:00:00');
INSERT INTO ts VALUES(2, '2023-11-01 08:00:00');
INSERT INTO ts VALUES(3, '2023-12-01 08:00:00');
CREATE TABLE aid (
id INTEGER NOT NULL AUTO_INCREMENT,
val VARCHAR(255),
PRIMARY KEY(id)
);
CREATE TABLE counter (
id INTEGER NOT NULL,
val INTEGER,
PRIMARY KEY(id)
);
INSERT INTO counter VALUES(1, 0);
Embedded SQL works like:
The syntax for embedded SQL is:
EXEC-SQL BEGIN DECLARE SECTION <statement terminator>
<declaration variables>
EXEC-SQL END DECLARE SECTION <statement terminator>
...
EXEC-SQL <SQL using variables directly> <statement terminator>
Example snippet in Cobol:
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 v PIC S9(8) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
...
EXEC SQL INSERT INTO TABLE something VALUES(:v) END-EXEC
Example snippet in C:
EXEC SQL BEGIN DECLARE SECTION;
int v;
EXEC SQL END DECLARE SECTION;
...
EXEC SQL INSERT INTO TABLE something VALUES(:v);
customer.cob:
01 customer.
03 customer-id pic s9(8) comp-3.
03 customer-name pic x(32).
03 customer-phone pic x(16).
01 customer-potential.
03 customer-potential-id pic s9(8) comp-3.
03 customer-potential-source pic x(256).
01 customer-actual.
03 customer-actual-id pic s9(8) comp-3.
03 customer-actual-address1 pic x(64).
03 customer-actual-address2 pic x(64).
03 customer-actual-contact pic x(16).
03 customer-actual-discount pic s9(8) comp-3.
order.cob:
01 order-main.
03 order-id pic s9(8) comp-3.
03 order-customer pic s9(8) comp-3.
03 order-status pic x(16).
01 order-line.
03 order-line-id pic s9(8) comp-3.
03 order-line-order pic s9(8) comp-3.
03 order-line-item pic x(32).
03 order-line-quantity pic s9(8) comp-3.
03 order-line-price pic s9(8)v9(2) comp-3.
add_embrdb.sco:
identification division.program-id. xadd.
data division.
working-storage section.
EXEC SQL INCLUDE sqlca END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE 'customer.cob' END-EXEC.
EXEC SQL INCLUDE 'order.cob' END-EXEC.
01 constr pic x(255).
EXEC SQL END DECLARE SECTION END-EXEC.
procedure division.
main-paragraph.
move "FILENAME disk4:[rdb]test" to constr
EXEC SQL CONNECT TO :constr END-EXEC
move 6 to customer-id
move "F company" to customer-name
move "6666-6666" to customer-phone
EXEC SQL
INSERT INTO customer
VALUES(:customer-id,
:customer-name,
:customer-phone)
END-EXEC
move 6 to customer-actual-id
move "F road 6" to customer-actual-address1
move "F town" to customer-actual-address2
move "Mr. F" to customer-actual-contact
move 10 to customer-actual-discount
EXEC SQL
INSERT INTO customer_actual
VALUES(:customer-actual-id,
:customer-actual-address1,
:customer-actual-address2,
:customer-actual-contact,
:customer-actual-discount)
END-EXEC
move 6 to order-id
move 6 to order-customer
move "In progress" to order-status
EXEC SQL
INSERT INTO order_main
VALUES(:order-id,
:order-customer,
:order-status)
END-EXEC
move 8 to order-line-id
move 6 to order-line-order
move "X stuff" to order-line-item
move 1 to order-line-quantity
move 72.00 to order-line-price
EXEC SQL
INSERT INTO order_line
VALUES(:order-line-id,
:order-line-order,
:order-line-item,
:order-line-quantity,
:order-line-price)
END-EXEC
EXEC SQL COMMIT END-EXEC
stop run.
list_embrdb.sco:
identification division.program-id. list.
data division.
working-storage section.
EXEC SQL INCLUDE sqlca END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE 'customer.cob' END-EXEC.
EXEC SQL INCLUDE 'order.cob' END-EXEC.
01 constr pic x(255).
01 sumval pic s9(8)v9(2) comp-3.
EXEC SQL END DECLARE SECTION END-EXEC.
01 last-customer-id pic s9(8) comp.
01 name-len pic s9(8) comp.
01 phone-len pic s9(8) comp.
01 source-len pic s9(8) comp.
01 address1-len pic s9(8) comp.
01 address2-len pic s9(8) comp.
01 status-len pic s9(8) comp.
01 len pic s9(8) comp.
01 order-id-2 pic 9(8) display.
01 sumval2 pic 9(8)v9(2) display.
EXEC SQL
DECLARE custpot CURSOR FOR
SELECT customer.id AS cid, name, phone, source
FROM customer JOIN customer_potential
ON customer.id = customer_potential.id
ORDER BY cid
END-EXEC.
EXEC SQL
DECLARE custact CURSOR FOR
SELECT customer.id AS cid,name,address1,address2,phone,
order_main.id AS oid,status,
SUM(quantity*price) AS sumval
FROM customer JOIN customer_actual
ON customer.id = customer_actual.id
JOIN order_main
ON customer.id = order_main.customer
JOIN order_line
ON order_main.id = order_line.order_main
GROUP BY customer.id,
name,
address1,
address2,
phone,
order_main.id,
status
ORDER BY cid, oid
END-EXEC.
procedure division.
main-paragraph.
move "FILENAME disk4:[rdb]test" to constr
EXEC SQL CONNECT TO :constr END-EXEC
EXEC SQL OPEN custpot END-EXEC
move 0 to sqlcode
perform until not sqlcode = 0
EXEC SQL
FETCH custpot
INTO :customer-id,
:customer-name,
:customer-phone,
:customer-potential-source
END-EXEC
if sqlcode = 0 then
perform display-potential-paragraph
end-if
end-perform
EXEC SQL CLOSE custpot END-EXEC
EXEC SQL OPEN custact END-EXEC
move 0 to sqlcode
move 0 to last-customer-id
perform until not sqlcode = 0
EXEC SQL
FETCH custact
INTO :customer-id,
:customer-name,
:customer-actual-address1,
:customer-actual-address2,
:customer-phone,
:order-id,
:order-status,
:sumval
END-EXEC
if sqlcode = 0 then
if customer-id not = last-customer-id then
perform display-actual-paragraph
move customer-id to last-customer-id
end-if
perform display-order-paragraph
end-if
end-perform
EXEC SQL CLOSE custact END-EXEC
stop run.
display-potential-paragraph.
move 32 to len
call "trim"
using customer-name, len
giving name-len
end-call
move 16 to len
call "trim"
using customer-phone, len
giving phone-len
end-call
move 256 to len
call "trim"
using customer-potential-source, len
giving source-len
end-call
display "**future customer** " customer-name(1:name-len)
", " customer-phone(1:phone-len)
", " customer-potential-source(1:source-len).
display-actual-paragraph.
move 32 to len
call "trim"
using customer-name, len
giving name-len
end-call
move 64 to len
call "trim"
using customer-actual-address1, len
giving address1-len
end-call
move 64 to len
call "trim"
using customer-actual-address2, len
giving address2-len
end-call
move 16 to len
call "trim"
using customer-phone, len
giving phone-len
end-call
display customer-name(1:name-len)
", " customer-actual-address1(1:address1-len)
", " customer-actual-address2(1:address2-len)
", " customer-phone(1:phone-len).
display-order-paragraph.
move order-id to order-id-2
move 16 to len
call "trim"
using order-status, len
giving status-len
end-call
move sumval to sumval2
display " " order-id-2
", " order-status(1:status-len)
", ", sumval2.
Build and run:
$ def/nolog sql$database disk4:[rdb]test
$ sqlpre /cob /sqloptions=connect add_embrdb
$ link add_embrdb + sys$library:sql$user73/libr
$ sqlpre /cob /sqloptions=connect list_embrdb
$ cob trim
$ link list_embrdb + trim + sys$library:sql$user73/libr
$ run list_embrdb
$ run add_embrdb
$ run list_embrdb
customer2.cob:
01 customer.
03 customer-id pic s9(8) comp-3.
03 customer-name pic x(32).
03 customer-phone pic x(16).
01 customer-potential.
03 customer-potential-id pic s9(8) comp-3.
03 customer-potential-source pic x(256).
01 customer-actual.
03 customer-actual-id pic s9(8) comp-3.
03 customer-actual-address1 pic x(64).
03 customer-actual-address2 pic x(64).
03 customer-actual-contact pic x(16).
03 customer-actual-discount pic s9(8) comp-3.
order2.cob:
01 order-main.
03 order-id pic s9(8) comp-3.
03 order-customer pic s9(8) comp-3.
03 order-status pic x(16).
01 order-line.
03 order-line-id pic s9(8) comp-3.
03 order-line-order pic s9(8) comp-3.
03 order-line-item pic x(32).
03 order-line-quantity pic s9(8) comp-3.
03 order-line-price pic s9(8)v9(2) comp-3.
add_embmim.eco:
identification division.program-id. xadd.
data division.
working-storage section.
EXEC SQL INCLUDE sqlca END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE 'customer2.cob' END-EXEC.
EXEC SQL INCLUDE 'order2.cob' END-EXEC.
01 con pic x(255).
01 usr pic x(255).
01 pwd pic x(255).
EXEC SQL END DECLARE SECTION END-EXEC.
procedure division.
main-paragraph.
move "test" to con
move "SYSADM" to usr
move "hemmeligt" to pwd
EXEC SQL CONNECT TO :con USER :usr USING :pwd END-EXEC
move 6 to customer-id
move "F company" to customer-name
move "6666-6666" to customer-phone
EXEC SQL
INSERT INTO customer
VALUES(:customer-id,
:customer-name,
:customer-phone)
END-EXEC
move 6 to customer-actual-id
move "F road 6" to customer-actual-address1
move "F town" to customer-actual-address2
move "Mr. F" to customer-actual-contact
move 10 to customer-actual-discount
EXEC SQL
INSERT INTO customer_actual
VALUES(:customer-actual-id,
:customer-actual-address1,
:customer-actual-address2,
:customer-actual-contact,
:customer-actual-discount)
END-EXEC
move 6 to order-id
move 6 to order-customer
move "In progress" to order-status
EXEC SQL
INSERT INTO order_main
VALUES(:order-id,
:order-customer,
:order-status)
END-EXEC
move 8 to order-line-id
move 6 to order-line-order
move "X stuff" to order-line-item
move 1 to order-line-quantity
move 72.00 to order-line-price
EXEC SQL
INSERT INTO order_line
VALUES(:order-line-id,
:order-line-order,
:order-line-item,
:order-line-quantity,
:order-line-price)
END-EXEC
EXEC SQL COMMIT END-EXEC
stop run.
list_embmim.eco:
identification division.program-id. list.
data division.
working-storage section.
EXEC SQL INCLUDE sqlca END-EXEC.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
EXEC SQL INCLUDE 'customer2.cob' END-EXEC.
EXEC SQL INCLUDE 'order2.cob' END-EXEC.
01 con pic x(255).
01 usr pic x(255).
01 pwd pic x(255).
01 sumval pic s9(8)v9(2) comp-3.
EXEC SQL END DECLARE SECTION END-EXEC.
01 last-customer-id pic s9(8) comp.
01 name-len pic s9(8) comp.
01 phone-len pic s9(8) comp.
01 source-len pic s9(8) comp.
01 address1-len pic s9(8) comp.
01 address2-len pic s9(8) comp.
01 status-len pic s9(8) comp.
01 len pic s9(8) comp.
01 order-id-2 pic 9(8) display.
01 sumval2 pic 9(8)v9(2) display.
EXEC SQL
DECLARE custpot CURSOR FOR
SELECT customer.id AS cid, name, phone, source
FROM customer JOIN customer_potential
ON customer.id = customer_potential.id
ORDER BY cid
END-EXEC.
EXEC SQL
DECLARE custact CURSOR FOR
SELECT customer.id AS cid,name,address1,address2,phone,
order_main.id AS oid,status,
SUM(quantity*price) AS sumval
FROM customer JOIN customer_actual
ON customer.id = customer_actual.id
JOIN order_main
ON customer.id = order_main.customer
JOIN order_line
ON order_main.id = order_line.order_main
GROUP BY customer.id,
name,
address1,
address2,
phone,
order_main.id,
status
ORDER BY cid, oid
END-EXEC.
procedure division.
main-paragraph.
move "test" to con
move "SYSADM" to usr
move "hemmeligt" to pwd
EXEC SQL CONNECT TO :con USER :usr USING :pwd END-EXEC
EXEC SQL OPEN custpot END-EXEC
move 0 to sqlcode
perform until not sqlcode = 0
EXEC SQL
FETCH custpot
INTO :customer-id,
:customer-name,
:customer-phone,
:customer-potential-source
END-EXEC
if sqlcode = 0 then
perform display-potential-paragraph
end-if
end-perform
EXEC SQL CLOSE custpot END-EXEC
EXEC SQL OPEN custact END-EXEC
move 0 to sqlcode
move 0 to last-customer-id
perform until not sqlcode = 0
EXEC SQL
FETCH custact
INTO :customer-id,
:customer-name,
:customer-actual-address1,
:customer-actual-address2,
:customer-phone,
:order-id,
:order-status,
:sumval
END-EXEC
if sqlcode = 0 then
if customer-id not = last-customer-id then
perform display-actual-paragraph
move customer-id to last-customer-id
end-if
perform display-order-paragraph
end-if
end-perform
EXEC SQL CLOSE custact END-EXEC
stop run.
display-potential-paragraph.
move 32 to len
call "trim"
using customer-name, len
giving name-len
end-call
move 16 to len
call "trim"
using customer-phone, len
giving phone-len
end-call
move 256 to len
call "trim"
using customer-potential-source, len
giving source-len
end-call
display "**future customer** " customer-name(1:name-len)
", " customer-phone(1:phone-len)
", " customer-potential-source(1:source-len).
display-actual-paragraph.
move 32 to len
call "trim"
using customer-name, len
giving name-len
end-call
move 64 to len
call "trim"
using customer-actual-address1, len
giving address1-len
end-call
move 64 to len
call "trim"
using customer-actual-address2, len
giving address2-len
end-call
move 16 to len
call "trim"
using customer-phone, len
giving phone-len
end-call
display customer-name(1:name-len)
", " customer-actual-address1(1:address1-len)
", " customer-actual-address2(1:address2-len)
", " customer-phone(1:phone-len).
display-order-paragraph.
move order-id to order-id-2
move 16 to len
call "trim"
using order-status, len
giving status-len
end-call
move sumval to sumval2
display " " order-id-2
", " order-status(1:status-len)
", ", sumval2.
Build and run:
$ esql/cobol add_embmim
$ cobol/ansi add_embmim
$ link add_embmim + sys$input/opt
mimlib9:lr/lib
mimlib9:mdr/lib
mimlib9:mimdb9/share
$
$ cobol trim
$ esql/cobol list_embmim
$ cobol/ansi list_embmim
$ link list_embmim + trim + sys$input/opt
mimlib9:lr/lib
mimlib9:mdr/lib
mimlib9:mimdb9/share
$ run list_embmim
$ run add_embmim
$ run list_embmim
The Rdb and Mimer code may look very different, but there are really only 2 differences: the Mimer code use ANSI format indentation while the Rdb code does not, the connection establishing is different.
customer.h:
int customer_id;
char customer_name[33];
char customer_phone[17];
int customer_potential_id;
char customer_potential_source[257];
int customer_actual_id;
char customer_actual_address1[65];
char customer_actual_address2[65];
char customer_actual_contact[17];
int customer_actual_discount;
order.h:
int order_id;
int order_customer;
char order_status[17];
int order_line_id;
int order_line_order;
char order_line_item[33];
int order_line_quantity;
double order_line_price;
add_embrdb.sc:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
/* these are uppercase in Rdb */
#define sqlca SQLCA
#define sqlcode SQLCODE
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char con[256];
EXEC SQL INCLUDE 'customer.h';
EXEC SQL INCLUDE 'order.h';
double sumval;
EXEC SQL END DECLARE SECTION;
strcpy(con, "FILENAME disk4:[rdb]test");
EXEC SQL CONNECT TO :con;
customer_id = 6;
strcpy(customer_name, "F company");
strcpy(customer_phone, "6666-6666");
EXEC SQL INSERT INTO customer VALUES(:customer_id,:customer_name,:customer_phone);
customer_actual_id = 6;
strcpy(customer_actual_address1, "F road 6");
strcpy(customer_actual_address2, "F town");
strcpy(customer_actual_contact, "Mr. F");
customer_actual_discount = 10;
EXEC SQL INSERT INTO customer_actual VALUES(:customer_actual_id,:customer_actual_address1,:customer_actual_address2,:customer_actual_contact,:customer_actual_discount);
order_id = 6;
order_customer = 6;
strcpy(order_status, "In progress");
EXEC SQL INSERT INTO order_main VALUES(:order_id,:order_customer,:order_status);
order_line_id = 8;
order_line_order = 6;
strcpy(order_line_item, "X stuff");
order_line_quantity = 1;
order_line_price = 72.00;
EXEC SQL INSERT INTO order_line VALUES(:order_line_id,:order_line_order,:order_line_item,:order_line_quantity,:order_line_price);
EXEC SQL COMMIT;
return 0;
}
list_embrdb.sc:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
/* these are uppercase in Rdb */
#define sqlca SQLCA
#define sqlcode SQLCODE
char *trim(char *s)
{
int ix = strlen(s) - 1;
while(ix >= 0 && s[ix] == ' ')
{
s[ix] = 0;
ix--;
}
return s;
}
int main()
{
int last_customer_id;
EXEC SQL BEGIN DECLARE SECTION;
char con[256];
EXEC SQL INCLUDE 'customer.h';
EXEC SQL INCLUDE 'order.h';
double sumval;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE curspot CURSOR FOR
SELECT customer.id AS cid, name, phone, source
FROM customer JOIN customer_potential ON customer.id = customer_potential.id
ORDER BY cid;
EXEC SQL DECLARE cursact CURSOR FOR
SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval
FROM customer JOIN customer_actual ON customer.id = customer_actual.id
JOIN order_main ON customer.id = order_main.customer
JOIN order_line ON order_main.id = order_line.order_main
GROUP BY customer.id,name,address1,address2,phone,order_main.id,status
ORDER BY cid, oid;
strcpy(con, "FILENAME disk4:[rdb]test");
EXEC SQL CONNECT TO :con;
EXEC SQL OPEN curspot;
for(;;)
{
EXEC SQL FETCH curspot INTO :customer_id, :customer_name, :customer_phone, :customer_potential_source;
if(sqlca.sqlcode != 0) break;
printf("**future customer** %s, %s, %s\n", trim(customer_name), trim(customer_phone), trim(customer_potential_source));
}
EXEC SQL CLOSE curspot;
EXEC SQL OPEN cursact;
last_customer_id = 0;
for(;;)
{
EXEC SQL FETCH cursact INTO :customer_id, :customer_name, :customer_actual_address1, :customer_actual_address2, :customer_phone, :order_id, :order_status, :sumval;
if(sqlca.sqlcode != 0) break;
if(customer_id != last_customer_id)
{
printf("%s, %s, %s, %s\n", trim(customer_name), trim(customer_actual_address1), trim(customer_actual_address2), trim(customer_phone));
last_customer_id = customer_id;
}
printf(" %d, %s, %.2f\n", order_id, trim(order_status), sumval);
}
EXEC SQL CLOSE cursact;
return 0;
}
Build and run:
$ def/nolog sql$database disk4:[rdb]test
$ sqlpre /cc /sqloptions=connect add_embrdb
$ link add_embrdb + sys$library:sql$user73/libr
$ sqlpre /cc /sqloptions=connect list_embrdb
$ link list_embrdb + sys$library:sql$user73/libr
$ run list_embrdb
$ run add_embrdb
$ run list_embrdb
customer.h:
int customer_id;
char customer_name[33];
char customer_phone[17];
int customer_potential_id;
char customer_potential_source[257];
int customer_actual_id;
char customer_actual_address1[65];
char customer_actual_address2[65];
char customer_actual_contact[17];
int customer_actual_discount;
order.h:
int order_id;
int order_customer;
char order_status[17];
int order_line_id;
int order_line_order;
char order_line_item[33];
int order_line_quantity;
double order_line_price;
add_embmim.ec:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
int main()
{
EXEC SQL BEGIN DECLARE SECTION;
char con[256];
char usr[256];
char pwd[256];
EXEC SQL INCLUDE 'customer.h';
EXEC SQL INCLUDE 'order.h';
double sumval;
EXEC SQL END DECLARE SECTION;
strcpy(con, "test");
strcpy(usr, "SYSADM");
strcpy(pwd, "hemmeligt");
EXEC SQL CONNECT TO :con USER :usr USING :pwd;
customer_id = 6;
strcpy(customer_name, "F company");
strcpy(customer_phone, "6666-6666");
EXEC SQL INSERT INTO customer VALUES(:customer_id,:customer_name,:customer_phone);
customer_actual_id = 6;
strcpy(customer_actual_address1, "F road 6");
strcpy(customer_actual_address2, "F town");
strcpy(customer_actual_contact, "Mr. F");
customer_actual_discount = 10;
EXEC SQL INSERT INTO customer_actual VALUES(:customer_actual_id,:customer_actual_address1,:customer_actual_address2,:customer_actual_contact,:customer_actual_discount);
order_id = 6;
order_customer = 6;
strcpy(order_status, "In progress");
EXEC SQL INSERT INTO order_main VALUES(:order_id,:order_customer,:order_status);
order_line_id = 8;
order_line_order = 6;
strcpy(order_line_item, "X stuff");
order_line_quantity = 1;
order_line_price = 72.00;
EXEC SQL INSERT INTO order_line VALUES(:order_line_id,:order_line_order,:order_line_item,:order_line_quantity,:order_line_price);
EXEC SQL COMMIT;
return 0;
}
list_embmim.ec:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
EXEC SQL INCLUDE SQLCA;
char *trim(char *s)
{
int ix = strlen(s) - 1;
while(ix >= 0 && s[ix] == ' ')
{
s[ix] = 0;
ix--;
}
return s;
}
int main()
{
int last_customer_id;
EXEC SQL BEGIN DECLARE SECTION;
char con[256];
char usr[256];
char pwd[256];
EXEC SQL INCLUDE 'customer.h';
EXEC SQL INCLUDE 'order.h';
double sumval;
EXEC SQL END DECLARE SECTION;
EXEC SQL DECLARE curspot CURSOR FOR
SELECT customer.id AS cid, name, phone, source
FROM customer JOIN customer_potential ON customer.id = customer_potential.id
ORDER BY cid;
EXEC SQL DECLARE cursact CURSOR FOR
SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval
FROM customer JOIN customer_actual ON customer.id = customer_actual.id
JOIN order_main ON customer.id = order_main.customer
JOIN order_line ON order_main.id = order_line.order_main
GROUP BY customer.id,name,address1,address2,phone,order_main.id,status
ORDER BY cid, oid;
strcpy(con, "test");
strcpy(usr, "SYSADM");
strcpy(pwd, "hemmeligt");
EXEC SQL CONNECT TO :con USER :usr USING :pwd;
EXEC SQL OPEN curspot;
for(;;)
{
EXEC SQL FETCH curspot INTO :customer_id, :customer_name, :customer_phone, :customer_potential_source;
if(sqlca.sqlcode != 0) break;
printf("**future customer** %s, %s, %s\n", trim(customer_name), trim(customer_phone), trim(customer_potential_source));
}
EXEC SQL CLOSE curspot;
EXEC SQL OPEN cursact;
last_customer_id = 0;
for(;;)
{
EXEC SQL FETCH cursact INTO :customer_id, :customer_name, :customer_actual_address1, :customer_actual_address2, :customer_phone, :order_id, :order_status, :sumval;
if(sqlca.sqlcode != 0) break;
if(customer_id != last_customer_id)
{
printf("%s, %s, %s, %s\n", trim(customer_name), trim(customer_actual_address1), trim(customer_actual_address2), trim(customer_phone));
last_customer_id = customer_id;
}
printf(" %d, %s, %.2f\n", order_id, trim(order_status), sumval);
}
EXEC SQL CLOSE cursact;
return 0;
}
Build and run:
$ esql/c add_embmim
$ cc add_embmim
$ link add_embmim + sys$input/opt
mimlib9:lr/lib
mimlib9:mdr/lib
mimlib9:mimdb9/share
$
$ esql/c list_embmim
$ cc list_embmim
$ link list_embmim + sys$input/opt
mimlib9:lr/lib
mimlib9:mdr/lib
mimlib9:mimdb9/share
$ run list_embmim
$ run add_embmim
$ run list_embmim
The Rdb and Mimer code only differs in the connection establishing.
Embedded SQL support for databases available on VMS:
| Database | Cobol | Basic | Pascal | C/C++ | Fortran | Java | Python | PHP |
|---|---|---|---|---|---|---|---|---|
| Rdb | supported | supported | supported | supported | supported | unsupported | Not available | Not available |
| MySQL | Not available | Not available | Not available | Not available | Not available | unsupported | Not available | Not available |
| Mimer | supported | Not available | Not available | supported | supported | unsupported | Not available | Not available |
| SQLite | Not available | Not available | Not available | Not available | Not available | unsupported | Not available | Not available |
| Java databases (Derby/H2/HSQLDB) | Not available | Not available | Not available | Not available | Not available | unsupported | Not available | Not available |
MySQL comes with several connectors including a C API called libmysql.
I have created a Pascal wrapper around it called pmysql and a Basic wrapper called bmysql that is available here.
PHP mysqli extension also use the C API.
customer.h:
int customer_id;
char customer_name[33];
char customer_phone[17];
int customer_potential_id;
char customer_potential_source[257];
int customer_actual_id;
char customer_actual_address1[65];
char customer_actual_address2[65];
char customer_actual_contact[17];
int customer_actual_discount;
order.h:
int order_id;
int order_customer;
char order_status[17];
int order_line_id;
int order_line_order;
char order_line_item[33];
int order_line_quantity;
double order_line_price;
add_mysql.c:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define SOCKET int
#include <mysql.h>
#include "customer.h"
#include "order.h"
int main()
{
MYSQL *con;
MYSQL_STMT *stmt;
MYSQL_BIND in[5];
char *sqlstr;
char temp[17];
int stat;
con = mysql_init(NULL);
con = mysql_real_connect(con, "localhost", "root", "hemmeligt", "Test", 3306, NULL, 0);
stmt = mysql_stmt_init(con);
sqlstr = "INSERT INTO customer VALUES(?,?,?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
customer_id = 6;
strcpy(customer_name, "F company");
strcpy(customer_phone, "6666-6666");
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &customer_id;
in[0].buffer_length = sizeof(customer_id);
in[0].is_null = 0;
in[1].buffer_type = MYSQL_TYPE_STRING;
in[1].buffer = customer_name;
in[1].buffer_length = strlen(customer_name);
in[1].is_null = 0;
in[2].buffer_type = MYSQL_TYPE_STRING;
in[2].buffer = customer_phone;
in[2].buffer_length = strlen(customer_phone);
in[2].is_null = 0;
stat = mysql_stmt_bind_param(stmt, in);
stat = mysql_stmt_execute(stmt);
stmt = mysql_stmt_init(con);
sqlstr = "INSERT INTO customer_actual VALUES(?,?,?,?,?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
customer_actual_id = 6;
strcpy(customer_actual_address1, "F road 6");
strcpy(customer_actual_address2, "F town");
strcpy(customer_actual_contact, "Mr. F");
customer_actual_discount = 10;
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &customer_actual_id;
in[0].buffer_length = sizeof(customer_actual_id);
in[0].is_null = 0;
in[1].buffer_type = MYSQL_TYPE_STRING;
in[1].buffer = customer_actual_address1;
in[1].buffer_length = strlen(customer_actual_address1);
in[1].is_null = 0;
in[2].buffer_type = MYSQL_TYPE_STRING;
in[2].buffer = customer_actual_address2;
in[2].buffer_length = strlen(customer_actual_address2);
in[2].is_null = 0;
in[3].buffer_type = MYSQL_TYPE_STRING;
in[3].buffer = customer_actual_contact;
in[3].buffer_length = strlen(customer_actual_contact);
in[3].is_null = 0;
in[4].buffer_type = MYSQL_TYPE_LONG;
in[4].buffer = &customer_actual_discount;
in[4].buffer_length = sizeof(customer_actual_discount);
in[4].is_null = 0;
stat = mysql_stmt_bind_param(stmt, in);
stat = mysql_stmt_execute(stmt);
stmt = mysql_stmt_init(con);
sqlstr = "INSERT INTO order_main VALUES(?,?,?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
order_id = 6;
order_customer = 6;
strcpy(order_status, "In progress");
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &order_id;
in[0].buffer_length = sizeof(order_id);
in[0].is_null = 0;
in[1].buffer_type = MYSQL_TYPE_LONG;
in[1].buffer = &order_customer;
in[1].buffer_length = sizeof(order_customer);
in[1].is_null = 0;
in[2].buffer_type = MYSQL_TYPE_STRING;
in[2].buffer = order_status;
in[2].buffer_length = strlen(order_status);
in[2].is_null = 0;
stat = mysql_stmt_bind_param(stmt, in);
stat = mysql_stmt_execute(stmt);
stmt = mysql_stmt_init(con);
sqlstr = "INSERT INTO order_line VALUES(?,?,?,?,?)";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
order_line_id = 8;
order_line_order = 6;
strcpy(order_line_item, "X stuff");
order_line_quantity = 1;
order_line_price = 72.00;
memset(in, 0, sizeof(in));
in[0].buffer_type = MYSQL_TYPE_LONG;
in[0].buffer = &order_line_id;
in[0].buffer_length = sizeof(order_line_id);
in[0].is_null = 0;
in[1].buffer_type = MYSQL_TYPE_LONG;
in[1].buffer = &order_line_order;
in[1].buffer_length = sizeof(order_line_order);
in[1].is_null = 0;
in[2].buffer_type = MYSQL_TYPE_STRING;
in[2].buffer = order_line_item;
in[2].buffer_length = strlen(order_line_item);
in[2].is_null = 0;
in[3].buffer_type = MYSQL_TYPE_LONG;
in[3].buffer = &order_line_quantity;
in[3].buffer_length = sizeof(order_line_quantity);
in[3].is_null = 0;
sprintf(temp, "%.2f", order_line_price);
in[4].buffer_type = MYSQL_TYPE_STRING;
in[4].buffer = temp;
in[4].buffer_length = strlen(temp);
in[4].is_null = 0;
stat = mysql_stmt_bind_param(stmt, in);
stat = mysql_stmt_execute(stmt);
mysql_close(con);
return 0;
}
list_mysql.c:
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#define SOCKET int
#include <mysql.h>
#include "customer.h"
#include "order.h"
int main()
{
MYSQL *con;
MYSQL_STMT *stmt;
MYSQL_BIND out[8];
char *sqlstr;
unsigned long int namelen, phonelen, sourcelen, address1len, address2len, contactlen, statuslen, sumvallen;
int stat;
int last_customer_id;
char sumval[11];
con = mysql_init(NULL);
con = mysql_real_connect(con, "localhost", "root", "hemmeligt", "Test", 3306, NULL, 0);
stmt = mysql_stmt_init(con);
sqlstr = "SELECT customer.id AS cid, name, phone, source "
"FROM customer JOIN customer_potential ON customer.id = customer_potential.id "
"ORDER BY cid";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
stat = mysql_stmt_execute(stmt);
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &customer_id;
out[0].buffer_length = sizeof(customer_id);
out[1].buffer_type = MYSQL_TYPE_STRING;
out[1].buffer = &customer_name;
out[1].buffer_length = sizeof(customer_name);
out[1].length = &namelen;
out[2].buffer_type = MYSQL_TYPE_STRING;
out[2].buffer = customer_phone;
out[2].buffer_length = sizeof(customer_phone);
out[2].length = &phonelen;
out[3].buffer_type = MYSQL_TYPE_STRING;
out[3].buffer = customer_potential_source;
out[3].buffer_length = sizeof(customer_potential_source);
out[3].length = &sourcelen;
stat = mysql_stmt_bind_result(stmt, out);
stat = mysql_stmt_store_result(stmt);
while(!mysql_stmt_fetch(stmt))
{
customer_name[namelen] = 0;
customer_phone[phonelen] = 0;
customer_potential_source[sourcelen] = 0;
printf("**future customer** %s, %s, %s\n", customer_name, customer_phone, customer_potential_source);
}
mysql_stmt_free_result(stmt);
stmt = mysql_stmt_init(con);
sqlstr = "SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval "
"FROM customer JOIN customer_actual ON customer.id = customer_actual.id "
" JOIN order_main ON customer.id = order_main.customer "
" JOIN order_line ON order_main.id = order_line.order_main "
"GROUP BY customer.id,name,address1,address2,phone,order_main.id,status "
"ORDER BY cid, oid";
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
stat = mysql_stmt_execute(stmt);
memset(out, 0, sizeof(out));
out[0].buffer_type = MYSQL_TYPE_LONG;
out[0].buffer = &customer_id;
out[0].buffer_length = sizeof(customer_id);
out[1].buffer_type = MYSQL_TYPE_STRING;
out[1].buffer = customer_name;
out[1].buffer_length = sizeof(customer_name);
out[1].length = &namelen;
out[2].buffer_type = MYSQL_TYPE_STRING;
out[2].buffer = customer_actual_address1;
out[2].buffer_length = sizeof(customer_actual_address1);
out[2].length = &address1len;
out[3].buffer_type = MYSQL_TYPE_STRING;
out[3].buffer = customer_actual_address2;
out[3].buffer_length = sizeof(customer_actual_address2);
out[3].length = &address2len;
out[4].buffer_type = MYSQL_TYPE_STRING;
out[4].buffer = customer_phone;
out[4].buffer_length = sizeof(customer_phone);
out[4].length = &phonelen;
out[5].buffer_type = MYSQL_TYPE_LONG;
out[5].buffer = &order_id;
out[5].buffer_length = sizeof(order_id);
out[6].buffer_type = MYSQL_TYPE_STRING;
out[6].buffer = order_status;
out[6].buffer_length = sizeof(order_status);
out[6].length = &statuslen;
out[7].buffer_type = MYSQL_TYPE_DECIMAL;
out[7].buffer = &sumval;
out[7].buffer_length = sizeof(sumval);
out[7].length = &sumvallen;
stat = mysql_stmt_bind_result(stmt, out);
stat = mysql_stmt_store_result(stmt);
last_customer_id = 0;
while(!mysql_stmt_fetch(stmt))
{
customer_name[namelen] = 0;
customer_actual_address1[address1len] = 0;
customer_actual_address2[address2len] = 0;
customer_phone[phonelen] = 0;
order_status[statuslen] = 0;
sumval[sumvallen] = 0;
if(last_customer_id != customer_id)
{
printf("%s, %s, %s, %s\n", customer_name, customer_actual_address1, customer_actual_address2, customer_phone);
last_customer_id = customer_id;
}
printf(" %d, %s, %s\n", order_id, order_status, sumval);
}
mysql_stmt_free_result(stmt);
mysql_close(con);
return 0;
}
Build and run:
$ cc /include=mysql055_root:[include.mysql] /names=as_is /float=iee add_mysql.c
$ link add_mysql + sys$input/opt
mysql055_root:[lib.alpha]libclientlib/lib
mysql055_root:[lib.alpha]libsql/lib
mysql055_root:[lib.alpha]libmysys/lib
mysql055_root:[lib.alpha]libdbug/lib
mysql055_root:[lib.alpha]libstrings/lib
mysql055_root:[lib.alpha]libvio/lib
mysql055_root:[lib.alpha]libz/lib
mysql055_root:[lib.alpha]ssl_libssl32/lib
mysql055_root:[lib.alpha]ssl_libcrypto32/lib
$
$ cc /include=mysql055_root:[include.mysql] /names=as_is /float=ieee list_mysql.c
$ link list_mysql + sys$input/opt
mysql055_root:[lib.alpha]libclientlib/lib
mysql055_root:[lib.alpha]libsql/lib
mysql055_root:[lib.alpha]libmysys/lib
mysql055_root:[lib.alpha]libdbug/lib
mysql055_root:[lib.alpha]libstrings/lib
mysql055_root:[lib.alpha]libvio/lib
mysql055_root:[lib.alpha]libz/lib
mysql055_root:[lib.alpha]ssl_libssl32/lib
mysql055_root:[lib.alpha]ssl_libcrypto32/lib
$
$ run list_mysql
$ run add_mysql
$ run list_mysql
customer.pas:
var
customer_id : integer;
customer_name : longpstr(32);
customer_phone : longpstr(16);
customer_potential_id : integer;
customer_potential_source : longpstr(256);
customer_actual_id : integer;
customer_actual_address1 : longpstr(64);
customer_actual_address2 : longpstr(64);
customer_actual_contact : longpstr(16);
customer_actual_discount : integer;
order.pas:
var
order_id : integer;
order_customer : integer;
order_status : longpstr(16);
order_line_id : integer;
order_line_order : integer;
order_line_item : longpstr(32);
order_line_quantity : integer;
order_line_price : double;
add_mysql.pas:
[inherit('pmysqldir:pmysql2', 'pmysqldir:pmysql', 'pmysqldir:mysql')]
program add(input, output);
%include 'customer.pas'
%include 'order.pas'
var
con : mysql_ptr;
stmt : mysql_stmt_ptr;
stat : integer;
in3 : array [1..3] of mysql_bind;
in5 : array [1..5] of mysql_bind;
temp : pstr;
begin
con := pmysql_init;
con := pmysql_real_connect(con, 'localhost', 'root', 'hemmeligt', 'test');
stmt := pmysql_prepare(con, 'INSERT INTO customer VALUES(?,?,?)');
customer_id := 6;
longstr('F company', customer_name);
longstr('6666-6666', customer_phone);
pmysql_init_bind_long(in3[1], customer_id);
pmysql_init_bind_string_inx(in3[2], customer_name);
pmysql_init_bind_string_inx(in3[3], customer_phone);
stat := pmysql_stmt_bind_param(stmt, in3);
stat := pmysql_stmt_execute(stmt);
stmt := pmysql_prepare(con, 'INSERT INTO customer_actual VALUES(?,?,?,?,?)');
customer_actual_id := 6;
longstr('F road 6', customer_actual_address1);
longstr('F town', customer_actual_address2);
longstr('Mr. F', customer_actual_contact);
customer_actual_discount := 10;
pmysql_init_bind_long(in5[1], customer_actual_id);
pmysql_init_bind_string_inx(in5[2], customer_actual_address1);
pmysql_init_bind_string_inx(in5[3], customer_actual_address2);
pmysql_init_bind_string_inx(in5[4], customer_actual_contact);
pmysql_init_bind_long(in5[5], customer_actual_discount);
stat := pmysql_stmt_bind_param(stmt, in5);
stat := pmysql_stmt_execute(stmt);
stmt := pmysql_prepare(con, 'INSERT INTO order_main VALUES(?,?,?)');
order_id := 6;
order_customer := 6;
longstr('In progress', order_status);
pmysql_init_bind_long(in3[1], order_id);
pmysql_init_bind_long(in3[2], order_customer);
pmysql_init_bind_string_inx(in3[3], order_status);
stat := pmysql_stmt_bind_param(stmt, in3);
stat := pmysql_stmt_execute(stmt);
stmt := pmysql_prepare(con, 'INSERT INTO order_line VALUES(?,?,?,?,?)');
order_line_id := 8;
order_line_order := 6;
longstr('X stuff', order_line_item);
order_line_quantity := 1;
order_line_price := 72.00;
pmysql_init_bind_long(in5[1], order_line_id);
pmysql_init_bind_long(in5[2], order_line_order);
pmysql_init_bind_string_inx(in5[3], order_line_item);
pmysql_init_bind_long(in5[4], order_line_quantity);
writev(temp, order_line_price:3:1);
pmysql_init_bind_string_in(in5[5], temp);
stat := pmysql_stmt_bind_param(stmt, in5);
stat := pmysql_stmt_execute(stmt);
pmysql_close(con);
end.
list_mysql.pas:
[inherit('pmysqldir:pmysql2', 'pmysqldir:pmysql', 'pmysqldir:mysql')]
program list(input, output);
%include 'customer.pas'
%include 'order.pas'
var
con : mysql_ptr;
stmt : mysql_stmt_ptr;
stat : integer;
sumval : longpstr(10);
last_customer_id : integer;
begin
con := pmysql_init;
con := pmysql_real_connect(con, 'localhost', 'root', 'hemmeligt', 'test');
stmt := pmysql_prepare(con, 'SELECT customer.id AS cid, name, phone, source ' +
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' +
'ORDER BY cid');
stat := pmysql_get_result_start(stmt, long_arg(customer_id),
longstring_arg(customer_name),
longstring_arg(customer_phone),
longstring_arg(customer_potential_source));
while pmysql_stmt_fetch(stmt) = 0 do begin
writeln('**future customer** ', stdstr(customer_name), ', ', stdstr(customer_phone), ', ', stdstr(customer_potential_source));
end;
pmysql_stmt_free_result(stmt);
stmt := pmysql_prepare(con, 'SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' +
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' +
' JOIN order_main ON customer.id = order_main.customer ' +
' JOIN order_line ON order_main.id = order_line.order_main ' +
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' +
'ORDER BY customer.id');
stat := pmysql_get_result_start(stmt, long_arg(customer_id),
longstring_arg(customer_name),
longstring_arg(customer_actual_address1),
longstring_arg(customer_actual_address2),
longstring_arg(customer_phone),
long_arg(order_id),
longstring_arg(order_status),
longstring_arg(sumval));
last_customer_id := 0;
while pmysql_stmt_fetch(stmt) = 0 do begin
if customer_id <> last_customer_id then begin
writeln(stdstr(customer_name), ', ', stdstr(customer_actual_address1), ', ', stdstr(customer_actual_address2), ', ', stdstr(customer_phone));
last_customer_id := customer_id;
end;
writeln(' ', order_id:1, ', ', stdstr(order_status), ', ', stdstr(sumval));
end;
pmysql_close(con);
end.
Build and run:
$ pascal add_mysql
$ link add_mysql + sys$input/opt + pmysqldir:mysql55axp/opt
pmysqldir:pmysql2
pmysqldir:pmysql
pmysqldir:mysql
$
$ pascal list_mysql
$ link list_mysql + sys$input/opt + pmysqldir:mysql55axp/opt
pmysqldir:pmysql2
pmysqldir:pmysql
pmysqldir:mysql
$
$ run list_mysql
$ run add_mysql
$ run list_mysql
customer.bas:
declare long customer_id
declare string customer_name
declare string customer_phone
declare long customer_potential_id
declare string customer_potential_source
declare long customer_actual_id
declare string customer_actual_address1
declare string customer_actual_address2
declare string customer_actual_contact
declare long customer_actual_discount
order.bas:
declare long order_id
declare long order_customer
declare string order_status
declare long order_line_id
declare long order_line_order
declare string order_line_item
declare long order_line_quantity
declare tfloat order_line_price
add_mysql.bas:
program add_mysql
option type = explicit
%include "customer.bas"
%include "order.bas"
%include "bmysqldir:b.bas"
declare integer dbcon, stmt, inparam
declare integer stat
declare string temp
dbcon = bmysql_open("localhost", 3306, "root", "", "test")
stmt = bmysql_stmt_prepare(dbcon, "INSERT INTO customer VALUES(?,?,?)")
inparam = bmysql_inparam_init(3)
customer_id = 6
customer_name = "F company"
customer_phone = "6666-6666"
call bmysql_inparam_long(inparam, 0, customer_id)
call bmysql_inparam_string(inparam, 1, customer_name)
call bmysql_inparam_string(inparam, 2, customer_phone)
stat = bmysql_stmt_execute(stmt, inparam, 0)
call bmysql_inparam_free(inparam)
call bmysql_stmt_free(stmt)
stmt = bmysql_stmt_prepare(dbcon, "INSERT INTO customer_actual VALUES(?,?,?,?,?)")
inparam = bmysql_inparam_init(5)
customer_actual_id = 6
customer_actual_address1 = "F road 6"
customer_actual_address2 = "F town"
customer_actual_contact = "Mr. F"
customer_actual_discount = 10
call bmysql_inparam_long(inparam, 0, customer_actual_id)
call bmysql_inparam_string(inparam, 1, customer_actual_address1)
call bmysql_inparam_string(inparam, 2, customer_actual_address2)
call bmysql_inparam_string(inparam, 3, customer_actual_contact)
call bmysql_inparam_long(inparam, 4, customer_actual_discount)
stat = bmysql_stmt_execute(stmt, inparam, 0)
call bmysql_inparam_free(inparam)
call bmysql_stmt_free(stmt)
stmt = bmysql_stmt_prepare(dbcon, "INSERT INTO order_main VALUES(?,?,?)")
inparam = bmysql_inparam_init(3)
order_id = 6
order_customer = 6
order_status = "In progress"
call bmysql_inparam_long(inparam, 0, order_id)
call bmysql_inparam_long(inparam, 1, order_customer)
call bmysql_inparam_string(inparam, 2, order_status)
stat = bmysql_stmt_execute(stmt, inparam, 0)
call bmysql_stmt_free(stmt)
stmt = bmysql_stmt_prepare(dbcon, "INSERT INTO order_line VALUES(?,?,?,?,?)")
inparam = bmysql_inparam_init(5)
order_line_id = 8
order_line_order = 6
order_line_item = "X stuff"
order_line_quantity = 1
order_line_price = 72.00
call bmysql_inparam_long(inparam, 0, order_line_id)
call bmysql_inparam_long(inparam, 1, order_line_order)
call bmysql_inparam_string(inparam, 2, order_line_item)
call bmysql_inparam_long(inparam, 3, order_line_quantity)
temp = str$(order_line_price)
call bmysql_inparam_string(inparam, 4, temp)
stat = bmysql_stmt_execute(stmt, inparam, 0)
call bmysql_stmt_free(stmt)
call bmysql_close(dbcon)
end program
list_mysql.bas:
program list_mysql
option type = explicit
%include "customer.bas"
%include "order.bas"
%include "bmysqldir:b.bas"
declare integer dbcon, stmt, outparam
declare integer stat
declare tfloat sumval
declare integer last_customer_id
dbcon = bmysql_open("localhost", 3306, "root", "", "test")
stmt = bmysql_stmt_prepare(dbcon, "SELECT customer.id AS cid, name, phone, source " + &
"FROM customer JOIN customer_potential ON customer.id = customer_potential.id " + &
"ORDER BY cid")
outparam = bmysql_outparam_init(4)
call bmysql_outparam_long(outparam, 0, customer_id)
call bmysql_outparam_string(outparam, 1, customer_name)
call bmysql_outparam_string(outparam, 2, customer_phone)
call bmysql_outparam_string(outparam, 3, customer_potential_source)
stat = bmysql_stmt_execute(stmt, 0, outparam)
while bmysql_stmt_fetch(stmt, outparam) = 0
print using "_**future customer_** 'E_, 'E_, 'E", customer_name, customer_phone, customer_potential_source
next
call bmysql_outparam_free(outparam)
call bmysql_stmt_free(stmt)
stmt = bmysql_stmt_prepare(dbcon, "SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval " + &
"FROM customer JOIN customer_actual ON customer.id = customer_actual.id " + &
" JOIN order_main ON customer.id = order_main.customer " + &
" JOIN order_line ON order_main.id = order_line.order_main " + &
"GROUP BY customer.id,name,address1,address2,phone,order_main.id,status " + &
"ORDER BY customer.id")
outparam = bmysql_outparam_init(8)
call bmysql_outparam_long(outparam, 0, customer_id)
call bmysql_outparam_string(outparam, 1, customer_name)
call bmysql_outparam_string(outparam, 2, customer_actual_address1)
call bmysql_outparam_string(outparam, 3, customer_actual_address2)
call bmysql_outparam_string(outparam, 4, customer_phone)
call bmysql_outparam_long(outparam, 5, order_id)
call bmysql_outparam_string(outparam, 6, order_status)
call bmysql_outparam_tfloat(outparam, 7, sumval)
stat = bmysql_stmt_execute(stmt, 0, outparam)
last_customer_id = 0
while bmysql_stmt_fetch(stmt, outparam) = 0
if customer_id <> last_customer_id then
print using "'E_, 'E_, 'E_, 'E", customer_name, customer_actual_address1, customer_actual_address2, customer_phone
last_customer_id = customer_id
end if
print using " #_, 'E_, ########.##", order_id, order_status, sumval
next
call bmysql_outparam_free(outparam)
call bmysql_stmt_free(stmt)
call bmysql_close(dbcon)
end program
Build and run:
$ bas add_mysql
$ link add_mysql + bmysqldir:b.opt/opt
$ bas list_mysql
$ link list_mysql + bmysqldir:b.opt/opt
$ run list_mysql
$ run add_mysql
$ run list_mysql
add_mysqli.php:
<?php
$con = mysqli_connect('localhost', 'root', 'hemmeligt', 'test');
$stmt = $con->prepare('INSERT INTO customer VALUES(?,?,?)');
$cid = 6;
$name = 'F company';
$phone = '6666-6666';
$stmt->bind_param('iss', $cid, $name, $phone);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO customer_actual VALUES(?,?,?,?,?)');
$cid = 6;
$address1 = 'F road 6';
$address2 = 'F town';
$contact = 'Mr. F';
$discount = 10;
$stmt->bind_param('isssi', $cid, $address1, $address2, $contact, $discount);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO order_main VALUES(?,?,?)');
$oid = 6;
$customer = 6;
$status = 'In progress';
$stmt->bind_param('iis', $oid, $customer, $status);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO order_line VALUES(?,?,?,?,?)');
$olid = 8;
$order = 6;
$item = 'X stuff';
$quantity = 1;
$price = '72.00';
$stmt->bind_param('iisis', $olid, $order, $item, $quantity, $price);
$stmt->execute();
mysqli_close($con);
?>
list_mysqli.php:
<?php
$con = mysqli_connect('localhost', 'root', 'hemmeligt', 'test');
$stmt = $con->prepare('SELECT customer.id AS cid, name, phone, source ' .
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' .
'ORDER BY cid');
$stmt->execute();
$rs = $stmt->get_result();
while($row = $rs->fetch_array(MYSQLI_ASSOC)) {
$name = $row['name'];
$phone = $row['phone'];
$source = $row['source'];
echo "**future customer** $name, $phone, $source\r\n";
}
$stmt->close();
$stmt = $con->prepare('SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' .
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' .
' JOIN order_main ON customer.id = order_main.customer ' .
' JOIN order_line ON order_main.id = order_line.order_main ' .
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' .
'ORDER BY cid, oid');
$stmt->execute();
$rs = $stmt->get_result();
$last_cid = 0;
while($row = $rs->fetch_array(MYSQLI_ASSOC)) {
$cid = $row['cid'];
$name = $row['name'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$phone = $row['phone'];
$oid = $row['oid'];
$status = $row['status'];
$sumval = $row['sumval'];
if($cid != $last_cid) {
echo "$name, $address1, $address2, $phone\r\n";
$last_cid = $cid;
}
echo " $oid, $status, $sumval\r\n";
}
$stmt->close();
$con->close();
?>
Run:
$ php list_mysqli.php
$ php add_mysqli.php
$ php list_mysqli.php
SQLite comes with a sqlite3 library.
I have created a Pascal wrapper around it called psqlite that is available here.
PHP sqlite3 extension also use the C library..
customer.h:
int customer_id;
char customer_name[33];
char customer_phone[17];
int customer_potential_id;
char customer_potential_source[257];
int customer_actual_id;
char customer_actual_address1[65];
char customer_actual_address2[65];
char customer_actual_contact[17];
int customer_actual_discount;
order.h:
int order_id;
int order_customer;
char order_status[17];
int order_line_id;
int order_line_order;
char order_line_item[33];
int order_line_quantity;
double order_line_price;
add_sqlite.c:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlite3.h"
#include "customer.h"
#include "order.h"
int main()
{
sqlite3 *con;
sqlite3_stmt *stmt;
char *sqlstr;
int stat;
stat = sqlite3_open("test.db", &con);
sqlstr = "INSERT INTO customer VALUES(?,?,?)";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
customer_id = 6;
strcpy(customer_name, "F company");
strcpy(customer_phone, "6666-6666");
sqlite3_bind_int(stmt, 1, customer_id);
sqlite3_bind_text(stmt, 2, customer_name, strlen(customer_name), NULL);
sqlite3_bind_text(stmt, 3, customer_phone, strlen(customer_phone), NULL);
stat = sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlstr = "INSERT INTO customer_actual VALUES(?,?,?,?,?)";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
customer_actual_id = 6;
strcpy(customer_actual_address1, "F road 6");
strcpy(customer_actual_address2, "F town");
strcpy(customer_actual_contact, "Mr. F");
customer_actual_discount = 10;
sqlite3_bind_int(stmt, 1, customer_actual_id);
sqlite3_bind_text(stmt, 2, customer_actual_address1, strlen(customer_actual_address1), NULL);
sqlite3_bind_text(stmt, 3, customer_actual_address2, strlen(customer_actual_address2), NULL);
sqlite3_bind_text(stmt, 4, customer_actual_contact, strlen(customer_actual_contact), NULL);
sqlite3_bind_int(stmt, 5, customer_actual_discount);
stat = sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlstr = "INSERT INTO order_main VALUES(?,?,?)";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
order_id = 6;
order_customer = 6;
strcpy(order_status, "In progress");
sqlite3_bind_int(stmt, 1, order_id);
sqlite3_bind_int(stmt, 2, order_customer);
sqlite3_bind_text(stmt, 3, order_status, strlen(order_status), NULL);
stat = sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlstr = "INSERT INTO order_line VALUES(?,?,?,?,?)";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
order_line_id = 8;
order_line_order = 6;
strcpy(order_line_item, "X stuff");
order_line_quantity = 1;
order_line_price = 72.00;
sqlite3_bind_int(stmt, 1, order_line_id);
sqlite3_bind_int(stmt, 2, order_line_order);
sqlite3_bind_text(stmt, 3, order_line_item, strlen(order_line_item), NULL);
sqlite3_bind_int(stmt, 4, order_line_quantity);
sqlite3_bind_double(stmt, 5, order_line_price);
stat = sqlite3_step(stmt);
sqlite3_finalize(stmt);
sqlite3_close(con);
return 0;
}
list_sqlite.c:
#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "sqlite3.h"
#include "customer.h"
#include "order.h"
int main()
{
sqlite3 *con;
sqlite3_stmt *stmt;
char *sqlstr;
int stat;
int last_customer_id;
double sumval;
stat = sqlite3_open("test.db", &con);
sqlstr = "SELECT customer.id AS cid, name, phone, source "
"FROM customer JOIN customer_potential ON customer.id = customer_potential.id "
"ORDER BY cid";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
while(sqlite3_step(stmt) == SQLITE_ROW)
{
strcpy(customer_name, (char *)sqlite3_column_text(stmt, 1));
strcpy(customer_phone, (char *)sqlite3_column_text(stmt, 2));
strcpy(customer_potential_source, (char *)sqlite3_column_text(stmt, 3));
printf("**future customer** %s, %s, %s\n", customer_name,
customer_phone,
customer_potential_source);
}
sqlite3_finalize(stmt);
sqlstr = "SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval "
"FROM customer JOIN customer_actual ON customer.id = customer_actual.id "
" JOIN order_main ON customer.id = order_main.customer "
" JOIN order_line ON order_main.id = order_line.order_main "
"GROUP BY customer.id,name,address1,address2,phone,order_main.id,status "
"ORDER BY cid, oid";
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &stmt, NULL);
last_customer_id = 0;
while(sqlite3_step(stmt) == SQLITE_ROW)
{
customer_id = sqlite3_column_int(stmt, 0);
strcpy(customer_name, (char *)sqlite3_column_text(stmt, 1));
strcpy(customer_actual_address1, (char *)sqlite3_column_text(stmt, 2));
strcpy(customer_actual_address2, (char *)sqlite3_column_text(stmt, 3));
strcpy(customer_phone, (char *)sqlite3_column_text(stmt, 4));
order_id = sqlite3_column_int(stmt, 5);
strcpy(order_status, (char *)sqlite3_column_text(stmt, 6));
sumval = sqlite3_column_double(stmt, 7);
if(customer_id != last_customer_id)
{
printf("%s, %s, %s, %s\n", customer_name,
customer_actual_address1,
customer_actual_address2,
customer_phone);
last_customer_id = customer_id;
}
printf(" %d, %s, %.2f\n", order_id,
order_status,
sumval);
}
sqlite3_finalize(stmt);
sqlite3_close(con);
return 0;
}
Build and run:
$ cc/include=sqlite3_include:/float=ieee add_sqlite
$ link add_sqlite + sys$input/opt
sqlite3shr/share
$
$ cc/include=sqlite3_include:/float=ieee list_sqlite
$ link list_sqlite + sys$input/opt
sqlite3shr/share
$
$ run list_sqlite
$ run add_sqlite
$ run list_sqlite
customer2.pas:
var
customer_id : integer;
customer_name : pstr;
customer_phone : pstr;
customer_potential_id : integer;
customer_potential_source : pstr;
customer_actual_id : integer;
customer_actual_address1 : pstr;
customer_actual_address2 : pstr;
customer_actual_contact : pstr;
customer_actual_discount : integer;
order2.pas:
var
order_id : integer;
order_customer : integer;
order_status : pstr;
order_line_id : integer;
order_line_order : integer;
order_line_item : pstr;
order_line_quantity : integer;
order_line_price : double;
add_sqlite.pas:
[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program add(input, output);
%include 'customer2.pas'
%include 'order2.pas'
var
con : sqlite_ptr;
stmt : sqlite_stmt_ptr;
stat : integer;
begin
con := psqlite_open('test.db');
stmt := psqlite_prepare(con, 'INSERT INTO customer VALUES(?,?,?)');
customer_id := 6;
customer_name := 'F company';
customer_phone := '6666-6666';
psqlite_bind_int(stmt, 1, customer_id);
psqlite_bind_text(stmt, 2, customer_name);
psqlite_bind_text(stmt, 3, customer_phone);
psqlite_step_nonquery(stmt);
psqlite_finalize(stmt);
stmt := psqlite_prepare(con, 'INSERT INTO customer_actual VALUES(?,?,?,?,?)');
customer_actual_id := 6;
customer_actual_address1 := 'F road 6';
customer_actual_address2 := 'F town';
customer_actual_contact := 'Mr. F';
customer_actual_discount := 10;
psqlite_bind_int(stmt, 1, customer_actual_id);
psqlite_bind_text(stmt, 2, customer_actual_address1);
psqlite_bind_text(stmt, 3, customer_actual_address2);
psqlite_bind_text(stmt, 4, customer_actual_contact);
psqlite_bind_int(stmt, 5, customer_actual_discount);
psqlite_step_nonquery(stmt);
psqlite_finalize(stmt);
stmt := psqlite_prepare(con, 'INSERT INTO order_main VALUES(?,?,?)');
order_id := 6;
order_customer := 6;
order_status := 'In progress';
psqlite_bind_int(stmt, 1, order_id);
psqlite_bind_int(stmt, 2, order_customer);
psqlite_bind_text(stmt, 3, order_status);
psqlite_step_nonquery(stmt);
psqlite_finalize(stmt);
stmt := psqlite_prepare(con, 'INSERT INTO order_line VALUES(?,?,?,?,?)');
order_line_id := 8;
order_line_order := 6;
order_line_item := 'X stuff';
order_line_quantity := 1;
order_line_price := 72.00;
psqlite_bind_int(stmt, 1, order_line_id);
psqlite_bind_int(stmt, 2, order_line_order);
psqlite_bind_text(stmt, 3, order_line_item);
psqlite_bind_int(stmt, 4, order_line_quantity);
psqlite_bind_double(stmt, 5, order_line_price);
psqlite_step_nonquery(stmt);
psqlite_finalize(stmt);
psqlite_close(con);
end.
list_sqlite.pas:
[inherit('psqlitedir:sqlite', 'psqlitedir:psqlite')]
program list(input, output);
%include 'customer2.pas'
%include 'order2.pas'
var
con : sqlite_ptr;
stmt : sqlite_stmt_ptr;
sumval : double;
last_customer_id : integer;
begin
con := psqlite_open('test.db');
stmt := psqlite_prepare(con, 'SELECT customer.id AS cid, name, phone, source ' +
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' +
'ORDER BY cid');
while psqlite_step_query(stmt) do begin
customer_name := psqlite_column_text(stmt, 1);
customer_phone := psqlite_column_text(stmt, 2);
customer_potential_source := psqlite_column_text(stmt, 3);
writeln('**future customer** ', customer_name, ', ', customer_phone, ', ', customer_potential_source);
end;
psqlite_finalize(stmt);
stmt := psqlite_prepare(con, 'SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' +
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' +
' JOIN order_main ON customer.id = order_main.customer ' +
' JOIN order_line ON order_main.id = order_line.order_main ' +
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' +
'ORDER BY cid, oid');
last_customer_id := 0;
while psqlite_step_query(stmt) do begin
customer_id := psqlite_column_int(stmt, 0);
customer_name := psqlite_column_text(stmt, 1);
customer_actual_address1 := psqlite_column_text(stmt, 2);
customer_actual_address2 := psqlite_column_text(stmt, 3);
customer_phone := psqlite_column_text(stmt, 4);
order_id := psqlite_column_int(stmt, 5);
order_status := psqlite_column_text(stmt, 6);
sumval := psqlite_column_double(stmt, 7);
if customer_id <> last_customer_id then begin
writeln(customer_name, ', ', customer_actual_address1, ', ', customer_actual_address2, ', ', customer_phone);
last_customer_id := customer_id;
end;
writeln(' ', order_id:1, ', ', order_status, ', ', sumval:3:1);
end;
psqlite_finalize(stmt);
psqlite_close(con);
end.
Build and run:
$ pascal/float=ieee add_sqlite
$ link add_sqlite + psqlitedir:psqlite + psqlitedir:sqlite + psqlitedir:sqlite/opt
$ pascal/float=ieee list_sqlite
$ link list_sqlite + psqlitedir:psqlite + psqlitedir:sqlite + psqlitedir:sqlite/opt
$ run list_sqlite
$ run add_sqlite
$ run list_sqlite
add_sqlite3.php:
<?php
$con = new SQLite3('/disk2/arne/art/dbx/test.db');
$stmt = $con->prepare('INSERT INTO customer VALUES(?,?,?)');
$cid = 6;
$name = 'F company';
$phone = '6666-6666';
$stmt->bindParam(1, $cid, SQLITE3_INTEGER);
$stmt->bindParam(2, $name, SQLITE3_TEXT);
$stmt->bindParam(3, $phone, SQLITE3_TEXT);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO customer_actual VALUES(?,?,?,?,?)');
$cid = 6;
$address1 = 'F road 6';
$address2 = 'F town';
$contact = 'Mr. F';
$discount = 10;
$stmt->bindParam(1, $cid, SQLITE3_INTEGER);
$stmt->bindParam(2, $address1, SQLITE3_TEXT);
$stmt->bindParam(3, $address2, SQLITE3_TEXT);
$stmt->bindParam(4, $contact, SQLITE3_TEXT);
$stmt->bindParam(5, $discount, SQLITE3_INTEGER);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO order_main VALUES(?,?,?)');
$oid = 6;
$customer = 6;
$status = 'In progress';
$stmt->bindParam(1, $oid, SQLITE3_INTEGER);
$stmt->bindParam(2, $customer, SQLITE3_INTEGER);
$stmt->bindParam(3, $status, SQLITE3_TEXT);
$stmt->execute();
$stmt = $con->prepare('INSERT INTO order_line VALUES(?,?,?,?,?)');
$olid = 8;
$order = 6;
$item = 'X stuff';
$quantity = 1;
$price = '72.00';
$stmt->bindParam(1, $olid, SQLITE3_INTEGER);
$stmt->bindParam(2, $customer, SQLITE3_INTEGER);
$stmt->bindParam(3, $item, SQLITE3_TEXT);
$stmt->bindParam(4, $quantity, SQLITE3_INTEGER);
$stmt->bindParam(5, $price, SQLITE3_TEXT);
$stmt->execute();
$con->close();
?>
list_sqlite3.php:
<?php
$con = new SQLite3('/disk2/arne/art/dbx/test.db');
$stmt = $con->prepare('SELECT customer.id AS cid, name, phone, source ' .
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' .
'ORDER BY cid');
$rs = $stmt->execute();
while($row = $rs->fetchArray()) {
$name = $row['name'];
$phone = $row['phone'];
$source = $row['source'];
echo "**future customer** $name, $phone, $source\r\n";
}
$stmt = $con->prepare('SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' .
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' .
' JOIN order_main ON customer.id = order_main.customer ' .
' JOIN order_line ON order_main.id = order_line.order_main ' .
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' .
'ORDER BY cid, oid');
$rs = $stmt->execute();
$last_cid = 0;
while($row = $rs->fetchArray()) {
$cid = $row['cid'];
$name = $row['name'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$phone = $row['phone'];
$oid = $row['oid'];
$status = $row['status'];
$sumval = $row['sumval'];
if($cid != $last_cid) {
echo "$name, $address1, $address2, $phone\r\n";
$last_cid = $cid;
}
echo " $oid, $status, $sumval\r\n";
}
$con->close();
?>
Run:
$ php list_sqlite3.php
$ php add_sqlite3.php
$ php list_sqlite3.php
Database specific API's are usually available for C/C++ for all non-JVM databases.
But the API's tend to be a bit cumbersome to use and if alternatives (embedded SQL or standard API) are available then those are usually preferred.
Today most tech stacks has a standard database API:
JDBC is the standard Java API for access to relational databases and available for Java and other JVM languages.
AddJDBC.java:
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.PreparedStatement;
public class AddJDBC {
private static void perform(String clznam, String conurl, String usr, String pwd) throws Exception {
System.out.println(conurl);
Class.forName(clznam);
Connection con = DriverManager.getConnection(conurl, usr, pwd);
PreparedStatement c = con.prepareStatement("INSERT INTO customer VALUES(?,?,?)");
c.setInt(1, 6);
c.setString(2, "F company");
c.setString(3, "6666-6666");
c.executeUpdate();
PreparedStatement ca = con.prepareStatement("INSERT INTO customer_actual VALUES(?,?,?,?,?)");
ca.setInt(1, 6);
ca.setString(2, "F road 6");
ca.setString(3, "F town");
ca.setString(4, "Mr. F");
ca.setInt(5, 10);
ca.executeUpdate();
PreparedStatement o = con.prepareStatement("INSERT INTO order_main VALUES(?,?,?)");
o.setInt(1, 6);
o.setInt(2, 6);
o.setString(3, "In progress");
o.executeUpdate();
PreparedStatement ol = con.prepareStatement("INSERT INTO order_line VALUES(?,?,?,?,?)");
ol.setInt(1, 8);
ol.setInt(2, 6);
ol.setString(3, "X stuff");
ol.setInt(4, 1);
ol.setBigDecimal(5, new BigDecimal("72.00"));
ol.executeUpdate();
con.close();
}
public static void main(String[] args) throws Exception {
if(args.length == 4) {
perform(args[0], args[1], args[2], args[3]);
} else if(args.length == 2) {
perform(args[0], args[1], null, null);
}
}
}
ListJDBC.java:
import java.math.BigDecimal;
import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
public class ListJDBC {
private static void perform(String clznam, String conurl, String usr, String pwd) throws Exception {
System.out.println(conurl);
Class.forName(clznam);
Connection con = DriverManager.getConnection(conurl, usr, pwd);
Statement stmt = con.createStatement();
ResultSet rspot = stmt.executeQuery("SELECT customer.id AS cid, name, phone, source " +
"FROM customer JOIN customer_potential ON customer.id = customer_potential.id " +
"ORDER BY cid");
while(rspot.next()) {
String customer_name = rspot.getString(2);
String customer_phone = rspot.getString(3);
String customer_potential_source = rspot.getString(4);
System.out.printf("**future customer** %s, %s, %s\n", customer_name, customer_phone, customer_potential_source);
}
rspot.close();
ResultSet rsact = stmt.executeQuery("SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval " +
"FROM customer JOIN customer_actual ON customer.id = customer_actual.id " +
" JOIN order_main ON customer.id = order_main.customer " +
" JOIN order_line ON order_main.id = order_line.order_main " +
"GROUP BY customer.id,name,address1,address2,phone,order_main.id,status " +
"ORDER BY cid, oid");
int last_customer_id = 0;
while(rsact.next()) {
int customer_id = rsact.getInt(1);
String customer_name = rsact.getString(2);
String customer_actual_address1 = rsact.getString(3);
String customer_actual_address2 = rsact.getString(4);
String customer_phone = rsact.getString(5);
int order_id = rsact.getInt(6);
String order_status = rsact.getString(7);
BigDecimal sumval = rsact.getBigDecimal(8);
if(customer_id != last_customer_id) {
System.out.printf("%s, %s, %s, %s\n", customer_name.trim(), customer_actual_address1.trim(), customer_actual_address2.trim(), customer_phone.trim());
last_customer_id = customer_id;
}
System.out.printf(" %d, %s, %.2f\n", order_id, order_status.trim(), sumval);
}
rsact.close();
stmt.close();
con.close();
}
public static void main(String[] args) throws Exception {
if(args.length == 4) {
perform(args[0], args[1], args[2], args[3]);
} else if(args.length == 2) {
perform(args[0], args[1], null, null);
}
}
}
Build and run:
$ define/nolog java$filename_controls 8
$ define/nolog decc$efs_charset true
$ javac AddJDBC.java
$ javac ListJDBC.java
$ java -cp .:/javalib/rdbthinv5.jar "ListJDBC" "oracle.rdb.jdbc.rdbThin.Driver" "jdbc:rdbThin://localhost:1701/dka4:[rdb]test" "arne" "hemmeligt"
$ java -cp .:/javalib/rdbthinv5.jar "AddJDBC" "oracle.rdb.jdbc.rdbThin.Driver" "jdbc:rdbThin://localhost:1701/dka4:[rdb]test" "arne" "hemmeligt"
$ java -cp .:/javalib/rdbthinv5.jar "ListJDBC" "oracle.rdb.jdbc.rdbThin.Driver" "jdbc:rdbThin://localhost:1701/dka4:[rdb]test" "arne" "hemmeligt"
$ java -cp .:/javalib/mimjdbc3.jar "ListJDBC" "com.mimer.jdbc.Driver" "jdbc:mimer:/test" "SYSADM" "hemmeligt"
$ java -cp .:/javalib/mimjdbc3.jar "AddJDBC" "com.mimer.jdbc.Driver" "jdbc:mimer:/test" "SYSADM" "hemmeligt"
$ java -cp .:/javalib/mimjdbc3.jar "ListJDBC" "com.mimer.jdbc.Driver" "jdbc:mimer:/test" "SYSADM" "hemmeligt"
$ java -cp .:/javalib/mysql-connector-java-5_1_36-bin.jar "ListJDBC" "com.mysql.jdbc.Driver" "jdbc:mysql://localhost:3306/test" "root" "hemmeligt"
$ java -cp .:/javalib/mysql-connector-java-5_1_36-bin.jar "AddJDBC" "com.mysql.jdbc.Driver" "jdbc:mysql://localhost:3306/test" "root" "hemmeligt"
$ java -cp .:/javalib/mysql-connector-java-5_1_36-bin.jar "ListJDBC" "com.mysql.jdbc.Driver" "jdbc:mysql://localhost:3306/test" "root" "hemmeligt"
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListJDBC" "org.sqlite.JDBC" "jdbc:sqlite:test.db"
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "AddJDBC" "org.sqlite.JDBC" "jdbc:sqlite:test.db"
$ java -cp .:/javalib/sqlite-jdbc-3_14_1-vms.jar "ListJDBC" "org.sqlite.JDBC" "jdbc:sqlite:test.db"
$ java -cp .:/javalib/derby.jar "ListJDBC" "org.apache.derby.jdbc.EmbeddedDriver" "jdbc:derby:test"
$ java -cp .:/javalib/derby.jar "AddJDBC" "org.apache.derby.jdbc.EmbeddedDriver" "jdbc:derby:test"
$ java -cp .:/javalib/derby.jar "ListJDBC" "org.apache.derby.jdbc.EmbeddedDriver" "jdbc:derby:test"
$ java -cp .:/javalib/h2-1_2_147.jar "ListJDBC" "org.h2.Driver" "jdbc:h2:test;FILE_LOCK=FS"
$ java -cp .:/javalib/h2-1_2_147.jar "AddJDBC" "org.h2.Driver" "jdbc:h2:test;FILE_LOCK=FS"
$ java -cp .:/javalib/h2-1_2_147.jar "ListJDBC" "org.h2.Driver" "jdbc:h2:test;FILE_LOCK=FS"
$ java -cp .:/javalib/hsqldb.jar "ListJDBC" "org.hsqldb.jdbc.JDBCDriver" "jdbc:hsqldb:file:test"
$ java -cp .:/javalib/hsqldb.jar "AddJDBC" "org.hsqldb.jdbc.JDBCDriver" "jdbc:hsqldb:file:test"
$ java -cp .:/javalib/hsqldb.jar "ListJDBC" "org.hsqldb.jdbc.JDBCDriver" "jdbc:hsqldb:file:test"
$ exit
DB API 2.0 is the standard Python database API.
add_dbapi.py:
from sys import argv
from rdb import dbapi2
#import pymysql
import sqlite3
def perform(con):
c = con.cursor()
c.execute('INSERT INTO customer VALUES(?,?,?)', (6, 'F company', '6666-6666'))
c.execute('INSERT INTO customer_actual VALUES(?,?,?,?,?)', (6, 'F road 6', 'F town', 'Mr. F', 10))
c.execute('INSERT INTO order_main VALUES(?,?,?)', (6, 6, 'In progress'))
c.execute('INSERT INTO order_line VALUES(?,?,?,?,?)', (8, 6, 'X stuff', 1, 72.00));
c.close()
con.commit()
con.close()
if argv[1] == 'Rdb':
perform(dbapi2.connect(database='disk4:[rdb]test'))
#if argv[1] == 'MySQL':
# perform(pymysql.connect(host='localhost',user='root',password='',db='test'))
if argv[1] == 'SQLite':
perform(sqlite3.connect('test.db'))
list_dbapi.py:
from sys import argv
from rdb import dbapi2
#import pymysql
import sqlite3
def perform(con):
c = con.cursor()
c.execute('SELECT customer.id AS cid, name, phone, source ' + \
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' + \
'ORDER BY cid')
data = c.fetchall()
c.close()
for row in data:
print('**future customer** %s, %s, %s' % (row[1], row[2], row[3]))
c = con.cursor()
c.execute('SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' + \
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' + \
' JOIN order_main ON customer.id = order_main.customer ' + \
' JOIN order_line ON order_main.id = order_line.order_main ' + \
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' + \
'ORDER BY cid, oid')
data = c.fetchall()
c.close()
last_customer_id = 0
for row in data:
if row[0] != last_customer_id:
print('%s, %s, %s, %s' % (row[1], row[2], row[3], row[4]))
last_customer_id = row[0]
print(' %s, %s, %s' % (row[5], row[6], row[7]))
con.close()
if argv[1] == 'Rdb':
perform(dbapi2.connect(database='disk4:[rdb]test'))
#if argv[1] == 'MySQL':
# perform(pymysql.connect(host='localhost',user='root',password='',db='test'))
if argv[1] == 'SQLite':
perform(sqlite3.connect('test.db'))
Run:
$ python list_dbapi.py "Rdb"
$ python add_dbapi.py "Rdb"
$ python list_dbapi.py "Rdb"
$! python list_dbapi.py "MySQL"
$! python add_dbapi.py "MySQL"
$! python list_dbapi.py "MySQL"
$ python list_dbapi.py "SQLite"
$ python add_dbapi.py "SQLite"
$ python list_dbapi.py "SQLite"
add_jdbc.py:
from sys import argv
from com.ziclix.python.sql import zxJDBC
def perform(con):
c = con.cursor()
c.execute('INSERT INTO customer VALUES(?,?,?)', (6, 'F company', '6666-6666'))
c.execute('INSERT INTO customer_actual VALUES(?,?,?,?,?)', (6, 'F road 6', 'F town', 'Mr. F', 10))
c.execute('INSERT INTO order_main VALUES(?,?,?)', (6, 6, 'In progress'))
c.execute('INSERT INTO order_line VALUES(?,?,?,?,?)', (8, 6, 'X stuff', 1, 72.00));
c.close()
con.commit()
con.close()
def performj(clznam, conurl, usr, pwd):
print(conurl)
perform(zxJDBC.connect(conurl, usr, pwd, clznam))
if len(argv) == 5:
performj(argv[1], argv[2], argv[3], argv[4])
if len(argv) == 3:
performj(argv[1], argv[2], None, None)
list_jdbc.py:
from sys import argv
from com.ziclix.python.sql import zxJDBC
def perform(con):
c = con.cursor()
c.execute('SELECT customer.id AS cid, name, phone, source ' + \
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' + \
'ORDER BY cid')
data = c.fetchall()
c.close()
for row in data:
print('**future customer** %s, %s, %s' % (row[1], row[2], row[3]))
c = con.cursor()
c.execute('SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' + \
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' + \
' JOIN order_main ON customer.id = order_main.customer ' + \
' JOIN order_line ON order_main.id = order_line.order_main ' + \
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' + \
'ORDER BY cid, oid')
data = c.fetchall()
c.close()
last_customer_id = 0
for row in data:
if row[0] != last_customer_id:
print('%s, %s, %s, %s' % (row[1], row[2], row[3], row[4]))
last_customer_id = row[0]
print(' %s, %s, %.2f' % (row[5], row[6], row[7]))
con.commit()
con.close()
def performj(clznam, conurl, usr, pwd):
print(conurl)
perform(zxJDBC.connect(conurl, usr, pwd, clznam))
if len(argv) == 5:
performj(argv[1], argv[2], argv[3], argv[4])
if len(argv) == 3:
performj(argv[1], argv[2], None, None)
Run:
$ define/nolog java$filename_controls 8
$ define/nolog decc$efs_charset true
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/rdbthinv5.jar"
$ jython list_jdbc.py """oracle.rdb.jdbc.rdbThin.Driver""" """jdbc:rdbThin://localhost:1701/dka4:[rdb]test""" """arne""" """hemmeligt"""
$ jython add_jdbc.py """oracle.rdb.jdbc.rdbThin.Driver""" """jdbc:rdbThin://localhost:1701/dka4:[rdb]test""" """arne""" """hemmeligt"""
$ jython list_jdbc.py """oracle.rdb.jdbc.rdbThin.Driver""" """jdbc:rdbThin://localhost:1701/dka4:[rdb]test""" """arne""" """hemmeligt"""
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/mimjdbc3.jar"
$ jython list_jdbc.py """com.mimer.jdbc.Driver""" """jdbc:mimer:/test""" """SYSADM""" """hemmeligt"""
$ jython add_jdbc.py """com.mimer.jdbc.Driver""" """jdbc:mimer:/test""" """SYSADM""" """hemmeligt"""
$ jython list_jdbc.py """com.mimer.jdbc.Driver""" """jdbc:mimer:/test""" """SYSADM""" """hemmeligt"""
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython list_jdbc.py """com.mysql.jdbc.Driver""" """jdbc:mysql://localhost:3306/test""" """root""" """hemmeligt"""
$ jython add_jdbc.py """com.mysql.jdbc.Driver""" """jdbc:mysql://localhost:3306/test""" """root""" """hemmeligt"""
$ jython list_jdbc.py """com.mysql.jdbc.Driver""" """jdbc:mysql://localhost:3306/test""" """root""" """hemmeligt"""
$!!!! define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/sqlite-jdbc-3_14_1-vms.jar"
$!!!! jython list_jdbc.py """org.sqlite.JDBC""" """jdbc:sqlite:test.db"""
$!!!! jython add_jdbc.py """org.sqlite.JDBC""" """jdbc:sqlite:test.db"""
$!!!! jython list_jdbc.py """org.sqlite.JDBC""" """jdbc:sqlite:test.db"""
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/derby.jar
$ jython list_jdbc.py """org.apache.derby.jdbc.EmbeddedDriver""" """jdbc:derby:test"""
$ jython add_jdbc.py """org.apache.derby.jdbc.EmbeddedDriver""" """jdbc:derby:test"""
$ jython list_jdbc.py """org.apache.derby.jdbc.EmbeddedDriver""" """jdbc:derby:test"""
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/h2-1_2_147.jar
$ jython list_jdbc.py """org.h2.Driver""" """jdbc:h2:test;FILE_LOCK=FS"""
$ jython add_jdbc.py """org.h2.Driver""" """jdbc:h2:test;FILE_LOCK=FS"""
$ jython list_jdbc.py """org.h2.Driver""" """jdbc:h2:test;FILE_LOCK=FS"""
$ define/nolog jython_libs "/javalib/zxjdbc.jar:/javalib/hsqldb.jar
$ jython list_jdbc.py """org.hsqldb.jdbc.JDBCDriver""" """jdbc:hsqldb:file:test""" """sa""" """"""
$ jython add_jdbc.py """org.hsqldb.jdbc.JDBCDriver""" """jdbc:hsqldb:file:test""" """sa""" """"""
$ jython list_jdbc.py """org.hsqldb.jdbc.JDBCDriver""" """jdbc:hsqldb:file:test""" """sa""" """"""
SQLite is outcommented because the combination of zxJDBC (DB API 2.0 provider for JDBC), SQLIte JDBC driver and SQLite I used to test with does not like the second query in list_jdbc.py even though it works with the same driver and database from Java. It seems to relate to usage of the aggregating function. In general Jython and zxJDBC work fine with SQLite.
PDO is the standard PHP database API.
add_pdo.php:
<?php
if($argc == 2) {
$con = new PDO($argv[1]);
}
if($argc == 4) {
$con = new PDO($argv[1], $argv[2], $argv[3]);
}
$stmt = $con->prepare('INSERT INTO customer VALUES(?,?,?)');
$cid = 6;
$name = 'F company';
$phone = '6666-6666';
$stmt->execute(array($cid, $name, $phone));
$stmt = $con->prepare('INSERT INTO customer_actual VALUES(?,?,?,?,?)');
$cid = 6;
$address1 = 'F road 6';
$address2 = 'F town';
$contact = 'Mr. F';
$discount = 10;
$stmt->execute(array($cid, $address1, $address2, $contact, $discount));
$stmt = $con->prepare('INSERT INTO order_main VALUES(?,?,?)');
$oid = 6;
$customer = 6;
$status = 'In progress';
$stmt->execute(array($oid, $customer, $status));
$stmt = $con->prepare('INSERT INTO order_line VALUES(?,?,?,?,?)');
$olid = 8;
$order = 6;
$item = 'X stuff';
$quantity = 1;
$price = '72.00';
$stmt->execute(array($olid, $order, $item, $quantity, $price));
?>
list_pdo.php:
<?php
if($argc == 2) {
$con = new PDO($argv[1]);
}
if($argc == 4) {
$con = new PDO($argv[1], $argv[2], $argv[3]);
}
$stmt = $con->prepare('SELECT customer.id AS cid, name, phone, source ' .
'FROM customer JOIN customer_potential ON customer.id = customer_potential.id ' .
'ORDER BY cid');
$stmt->execute();
while($row = $stmt->fetch()) {
$name = $row['name'];
$phone = $row['phone'];
$source = $row['source'];
echo "**future customer** $name, $phone, $source\r\n";
}
$stmt = $con->prepare('SELECT customer.id AS cid,name,address1,address2,phone,order_main.id AS oid,status,SUM(quantity*price) AS sumval ' .
'FROM customer JOIN customer_actual ON customer.id = customer_actual.id ' .
' JOIN order_main ON customer.id = order_main.customer ' .
' JOIN order_line ON order_main.id = order_line.order_main ' .
'GROUP BY customer.id,name,address1,address2,phone,order_main.id,status ' .
'ORDER BY cid, oid');
$stmt->execute();
$last_cid = 0;
while($row = $stmt->fetch()) {
$cid = $row['cid'];
$name = $row['name'];
$address1 = $row['address1'];
$address2 = $row['address2'];
$phone = $row['phone'];
$oid = $row['oid'];
$status = $row['status'];
$sumval = $row['sumval'];
if($cid != $last_cid) {
echo "$name, $address1, $address2, $phone\r\n";
$last_cid = $cid;
}
echo " $oid, $status, $sumval\r\n";
}
?>
Run:
$ php list_pdo.php mysql:host=localhost;dbname=test root "hemmeligt"
$ php add_pdo.php mysql:host=localhost;dbname=test root "hemmeligt"
$ php list_pdo.php mysql:host=localhost;dbname=test root "hemmeligt"
$ php list_pdo.php sqlite:/disk2/arne/art/dbx/test.db
$ php add_pdo.php sqlite:/disk2/arne/art/dbx/test.db
$ php list_pdo.php sqlite:/disk2/arne/art/dbx/test.db
Standard API's are the preferred option for non-native languages.
And the API's are usually much simpler than the C/C++ API's.
There are two aspects of application portability across databases:
Differences in API are pretty obvious.
stmt = mysql_stmt_init(con);
stat = mysql_stmt_prepare(stmt, sqlstr, strlen(sqlstr));
stat = mysql_stmt_execute(stmt);
and:
stat = sqlite3_prepare(con, sqlstr, strlen(sqlstr), &sel, NULL);
stat = sqlite3_step(stmt);
are totally different code.
But even if the API used is the same, then different databases can have different SQL dialect and they can handle things differently.
ResultSet rs = stmt.executeQuery("SELECT TOP 3 * FROM sometable");
and:
ResultSet rs = stmt.executeQuery("SELECT * FROM sometable LIMIT 3");
both use the same API, but the SQL is not portable - the first works with MS SQLServer etc. - the second works with MySQL etc..
Known portability issues are:
So a standard database API make it possible to write portable database access code - it does not guarantee that database access code will be portable.
JDBC provide two mechanisms to help navigate around non-portable SQL:
This enable developers to do "defensive programming" aka write code that will work with most/all databases.
That sounds a bit theoretical, but let us see some practical examples.
The examples are not quite realistic but should be good enough to illustrate problems that are seen in the real world.
Problem: Find rows where text field match abbreviation.Database specific SQL in various SQL dialects:
WHERE CONCAT(SUBSTRING(term FROM 1 FOR 1), UPPER(SUBSTRING(term FROM LENGTH(term) FOR 1))) = ?
WHERE CONCAT(LEFT(term, 1), UPPER(RIGHT(term, 1))) = ?
WHERE SUBSTR(term, 1, 1) || UPPER(SUBSTR(term, LENGTH(term), 1)) = ?
WHERE SUBSTRING(term FROM 1 FOR 1) || UPPER(SUBSTRING(term FROM CHAR_LENGTH(term) FOR 1)) = ?
JDBC logic:
if driver supports CONCAT, LEFT, RIGHT and UCASE escape functions {
use "WHERE {fn concat({fn left(term,1)}, {fn ucase({fn right(term,1)})})} = ?"
} else {
fallback to read all rows and compare client side
}
JDBC code:
public void findAbbreviation(Connection con) throws SQLException {
String s = "IN";
if(Arrays.asList(con.getMetaData().getStringFunctions().split(",")).containsAll(Arrays.asList("CONCAT", "LEFT", "RIGHT", "UCASE"))) {
System.out.println("String (JDBC):");
PreparedStatement pstmt = con.prepareStatement("SELECT id,term FROM abbr " +
"WHERE {fn concat({fn left(term,1)}, {fn ucase({fn right(term,1)})})} = ?");
pstmt.setString(1, s);
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt(1);
String term = rs.getString(2);
System.out.printf("%d : %s\n", id, term);
}
rs.close();
pstmt.close();
} else {
System.out.println("String (JDBC fallback client side):");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id,term FROM abbr");
while(rs.next()) {
int id = rs.getInt(1);
String term = rs.getString(2);
String term2 = term.substring(0, 1) + term.substring(term.length() - 1).toUpperCase();
if(term2.equals(s)) {
System.out.printf("%d : %s\n", id, term);
}
}
rs.close();
stmt.close();
}
}
Doing it client side will cost performance wise.
Problem: Find rows where timestamp field is in the future.Database specific SQL in various SQL dialects:
WHERE dt > CURRENT_TIMESTAMP
WHERE dt > NOW()
WHERE dt > DATETIME()
WHERE dt > LOCALTIMESTAMP
JDBC logic:
if driver supports NOW escape function }
use "WHERE dt > {fn now()}"
} else {
fallback to send and use client time
}
JDBC code:
public void later(Connection con) throws SQLException {
if(Arrays.asList(con.getMetaData().getTimeDateFunctions().split(",")).contains("NOW")) {
System.out.println("Later (JDBC):");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id,dt FROM ts WHERE dt > {fn now()}");
while(rs.next()) {
int id = rs.getInt(1);
if(rs.getObject(2) instanceof Timestamp) {
Timestamp dt = rs.getTimestamp(2);
System.out.printf("%d %s\n", id, dt);
} else if(rs.getObject(2) instanceof String) {
String dt = rs.getString(2);
System.out.printf("%d %s\n", id, dt);
} else {
System.out.println("Error: Unknown type");
}
}
rs.close();
stmt.close();
} else {
System.out.println("Later (JDBC fallback client side):");
PreparedStatement pstmt = con.prepareStatement("SELECT id,dt FROM ts WHERE dt > ?");
pstmt.setTimestamp(1, new Timestamp(System.currentTimeMillis()));
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt(1);
if(rs.getObject(2) instanceof Timestamp) {
Timestamp dt = rs.getTimestamp(2);
System.out.printf("%d %s\n", id, dt);
} else if(rs.getObject(2) instanceof String) {
String dt = rs.getString(2);
System.out.printf("%d %s\n", id, dt);
} else {
System.out.println("Error: Unknown type");
}
}
rs.close();
pstmt.close();
}
}
Using client side time can give problems if client and server are in different time zones.
Problem: Find rows where timestamp field is in current month.Database specific SQL in various SQL dialects:
WHERE EXTRACT(MONTH FROM dt) = EXTRACT(MONTH FROM CURRENT_TIMESTAMP) AND EXTRACT(YEAR FROM dt) = EXTRACT(YEAR FROM CURRENT_TIMESTAMP)
WHERE MONTH(dt) = MONTH(NOW()) AND YEAR(dt) = YEAR(NOW())
WHERE STRFTIME('%m',dt) = STRFTIME('%m',DATE()) AND STRFTIME('%Y',dt) = STRFTIME('%Y', DATE())
WHERE EXTRACT(MONTH FROM dt) = EXTRACT(MONTH FROM LOCALTIMESTAMP) AND EXTRACT(YEAR FROM dt) = EXTRACT(YEAR FROM LOCALTIMESTAMP)
WHERE MONTH(dt) = MONTH(CURRENT_TIMESTAMP) AND YEAR(dt) = YEAR(CURRENT_TIMESTAMP)
JDBC logic:
if driver supports NOW, MONTH and YEAR escape functions {
use "WHERE {fn month(dt)} = {fn month({fn now()})} AND {fn year(dt)} = {fn year({fn now()})}"
} else {
fallback to have client side provide timestamp interval
}
JDBC code:
public void thisMonth(Connection con) throws SQLException {
if(Arrays.asList(con.getMetaData().getTimeDateFunctions().split(",")).containsAll(Arrays.asList("NOW","MONTH","YEAR"))) {
System.out.println("This month(JDBC):");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT id,dt FROM ts WHERE {fn month(dt)} = {fn month({fn now()})} AND {fn year(dt)} = {fn year({fn now()})}");
while(rs.next()) {
int id = rs.getInt(1);
if(rs.getObject(2) instanceof Timestamp) {
Timestamp dt = rs.getTimestamp(2);
System.out.printf("%d %s\n", id, dt);
} else if(rs.getObject(2) instanceof String) {
String dt = rs.getString(2);
System.out.printf("%d %s\n", id, dt);
} else {
System.out.println("Error: Unknown type");
}
}
rs.close();
stmt.close();
} else {
System.out.println("This month (JDBC fallback client side):");
PreparedStatement pstmt = con.prepareStatement("SELECT id,dt FROM ts WHERE dt BETWEEN ? AND ?");
Calendar start = new GregorianCalendar();
start.set(Calendar.DAY_OF_MONTH, 1);
start.set(Calendar.HOUR_OF_DAY, 0);
start.set(Calendar.MINUTE, 0);
start.set(Calendar.SECOND, 0);
pstmt.setTimestamp(1, new Timestamp(start.getTime().getTime()));
Calendar end = new GregorianCalendar();
pstmt.setTimestamp(2, new Timestamp(end.getTime().getTime()));
ResultSet rs = pstmt.executeQuery();
while(rs.next()) {
int id = rs.getInt(1);
if(rs.getObject(2) instanceof Timestamp) {
Timestamp dt = rs.getTimestamp(2);
System.out.printf("%d %s\n", id, dt);
} else if(rs.getObject(2) instanceof String) {
String dt = rs.getString(2);
System.out.printf("%d %s\n", id, dt);
} else {
System.out.println("Error: Unknown type");
}
}
rs.close();
pstmt.close();
}
}
Using client side time can give problems if client and server are in different time zones.
Problem: find auto generated id's.Database specific SQL in various SQL dialects:
SELECT aid.CURRVAL FROM aid
SELECT LAST_INSERT_ID()
SELECT LAST_INSERT_ROWID()
SELECT (CURRENT_VALUE OF aid_id) FROM aid
SELECT IDENTITY_VAL_LOCAL() FROM aid
SELECT SCOPE_IDENTITY()
SELECT IDENTITY() FROM aid
JDBC logic:
if driver supports Statement getGeneratedKeys {
just INSERT and get ResultSet from Statement with generated keys
} else {
INSERT and SELECT MAX(id) in transaction with transaction isolation level serializable
}
JDBC code:
public void getAutoIds(Connection con) throws SQLException {
DatabaseMetaData md = con.getMetaData();
if(md.supportsGetGeneratedKeys()) {
System.out.println("Id (JDBC):");
PreparedStatement pstmt = con.prepareStatement("INSERT INTO aid (val) VALUES(?)", PreparedStatement.RETURN_GENERATED_KEYS);
for(int i = 0; i < 3; i++) {
pstmt.setString(1, "Bla bla bla");
pstmt.executeUpdate();
ResultSet rs = pstmt.getGeneratedKeys();
if(rs.next()) {
if(rs.getObject(1) instanceof Integer) {
System.out.println(rs.getInt(1));
} else if(rs.getObject(1) instanceof Long) {
System.out.println(rs.getLong(1));
} else if(rs.getObject(1) instanceof BigDecimal) {
System.out.println(rs.getBigDecimal(1));
} else {
System.out.println("Error: Unknown type");
}
} else {
System.out.println("Error: No value");
}
rs.close();
}
pstmt.close();
} else if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
System.out.println("Id (JDBC fallback to transaction):");
boolean old_status = con.getAutoCommit();
int old_level = con.getTransactionIsolation();
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = con.createStatement();
PreparedStatement pstmt = con.prepareStatement("INSERT INTO aid (val) VALUES(?)");
for(int i = 0; i < 3; i++) {
pstmt.setString(1, "Bla bla bla");
pstmt.executeUpdate();
ResultSet rs = stmt.executeQuery("SELECT MAX(id) FROM aid");
if(rs.next()) {
System.out.println(rs.getBigDecimal(1));
} else {
throw new RuntimeException("Ooops");
}
rs.close();
con.commit();
}
pstmt.close();
stmt.close();
con.setTransactionIsolation(old_level);
con.setAutoCommit(old_status);
} else {
System.out.println("*Statement.getGeneratedKeys() not supported and transacion isolation level serializzable not supported");
}
}
Database specific SQL in various SQL dialects:
FETCH FIRST 3 ROW ONLY
LIMIT 3
-- not possible
JDBC logic:
try {
use "{limit 3}"
} catch database exception {
fallback to just query and stop reading after 3 rows
}
JDBC code:
public void limit(Connection con) throws SQLException {
Statement stmt = con.createStatement();
try {
ResultSet rs = stmt.executeQuery("SELECT id FROM customer {limit 3}");
System.out.println("Limit (JDBC):");
while(rs.next()) {
int id = rs.getInt(1);
System.out.printf("%d\n", id);
}
rs.close();
} catch(SQLException ex) {
ResultSet rs = stmt.executeQuery("SELECT id FROM customer");
System.out.println("Limit (JDBC fallback client side):");
int n = 0;
while(rs.next() && n < 3) {
int id = rs.getInt(1);
System.out.printf("%d\n", id);
n++;
}
rs.close();
}
stmt.close();
}
The fallback of just stop reading will put more load on the database.
Problem: read value from database, modify value and write value back to database in a way safe for concurrent access.Database specific SQL in various SQL dialects:
JDBC logic:
if driver supports SELECT FOR UPDATE and transactions {
enable transactions at lowest transaction isolation level
SELECT FOR UPDATE and UPDATE in transaction with retry logic
} else if driver supports transaction isolation level serializable {
enable transactions at transaction isolation level serializable
SELECT and UPDATE in transaction with retry logic
} else {
problem
}
For more details about why this logic see here here.
JDBC code:
public static void counter(Connection con) throws SQLException {
DatabaseMetaData md = con.getMetaData();
boolean retry;
do {
retry = false;
try {
if(md.supportsSelectForUpdate() && md.supportsTransactions()) {
int level;
if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_UNCOMMITTED)) {
level = Connection.TRANSACTION_READ_UNCOMMITTED;
} else if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_READ_COMMITTED)) {
level = Connection.TRANSACTION_READ_COMMITTED;
} else if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_REPEATABLE_READ)) {
level = Connection.TRANSACTION_REPEATABLE_READ;
} else if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
level = Connection.TRANSACTION_SERIALIZABLE;
} else {
throw new RuntimeException("No suitable transaction isolation level");
}
System.out.printf("Counter (JDBC) - SELECT FOR UPDATE (%s)\n", til.get(level));
boolean old_status = con.getAutoCommit();
int old_level = con.getTransactionIsolation();
con.setAutoCommit(false);
con.setTransactionIsolation(level);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT val FROM counter WHERE id = 1");
if(!rs.next()) throw new RuntimeException("No rows in counter table");
int val = rs.getInt(1);
rs.close();
stmt.close();
val++;
PreparedStatement pstmt = con.prepareStatement("UPDATE counter SET val = ? WHERE id = 1");
pstmt.setInt(1, val);
pstmt.executeUpdate();
pstmt.close();
con.commit();
con.setTransactionIsolation(old_level);
con.setAutoCommit(old_status);
} else if(md.supportsTransactionIsolationLevel(Connection.TRANSACTION_SERIALIZABLE)) {
System.out.println("Counter (JDBC fallback to transaction) - transaction isolation level serializable");
boolean old_status = con.getAutoCommit();
int old_level = con.getTransactionIsolation();
con.setAutoCommit(false);
con.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT val FROM counter WHERE id = 1");
if(!rs.next()) throw new RuntimeException("No rows in counter table");
int val = rs.getInt(1);
rs.close();
stmt.close();
val++;
PreparedStatement pstmt = con.prepareStatement("UPDATE counter SET val = ? WHERE id = 1");
pstmt.setInt(1, val);
pstmt.executeUpdate();
pstmt.close();
con.commit();
con.setTransactionIsolation(old_level);
con.setAutoCommit(old_status);
} else {
System.out.println("Not possible");
}
} catch(SQLException ex) {
retry = true;
}
} while(retry);
}
Does writing database independent code require more code than writing code targetting only one database?
Yes. There is no free lunch. Staying database independent require some effort.
Note though that it requires less code to write database independent code than to write 5 different sets of code for 5 different databases.
So does it work?
Reasonable well!
| string functions | date and time functions | get generated ids | limit | atomic counter update | |
|---|---|---|---|---|---|
| Works with JDBC mechanisms (no need for database specific solution) |
Rdb MySQL Mimer HSQLDB |
Rdb MySQL Mimer HSQLDB |
MySQL SQLite H2 HSQLDB |
Rdb Derby H2 |
Rdb MySQL Mimer Derby H2 HSQLDB |
| Works with JDBC fallback/workaround (works but a database specific solution may be preferable) |
SQLite Derby H2 |
SQLite Derby H2 |
Rdb Mimer Derby |
MySQL SQLite Mimer HSQLDB |
SQLite |
| Database specific solution required | (none) | (none) | (none) | (none) | (none) |
A different question is whether database independence is needed.
It is usually a long and expensive process to switch database, so it does not happen that often.
But fundamental changes in IT strategy happen for various reasons (new CIO, mergers/acquisitions/spinoffs, changes in vendors strategy or pricing policy etc.).
And history show that many companies end up switching database even though they expected never to have to.
So it can be a very good investment to keep ones application code database independent.
Using an ORM enables the application to work on an object view of the data instead of on a relational model.
And typical will ORM usage mean no SQL usage. Updates and simple queries loading just one object does typical not require any query langauge usage and complex queries typical use another query language than SQL.
Note that usage of ORM obviously only makes sense when an object view of data makes sense. In some cases the relational model makes more sense.
JPA is the standard ORM API for Java.
The most common JPA implementation is Hibernate, but other exist like OpenJPA and EclipseLink.
JPA use JPQL as query language.
JPA can be used not only by Java but also by other JVM languages like Jython, Groovy, Kotlin, Scala etc..
With JPA the database connections are defined in META-INF/persistence.xml:
<persistence xmlns="http://java.sun.com/xml/ns/persistence"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://java.sun.com/xml/ns/persistence http://java.sun.com/xml/ns/persistence/persistence_2_0.xsd"
version="2.0">
<persistence-unit name="Rdb">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="oracle.rdb.jdbc.rdbThin.Driver"/>
<property name="hibernate.connection.url" value="jdbc:rdbThin://localhost:1701/dka4:[rdb]test"/>
<property name="hibernate.connection.username" value="arne"/>
<property name="hibernate.connection.password" value="hemmeligt"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.RdbDialect"/>
</properties>
</persistence-unit>
<persistence-unit name="Mimer">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="com.mimer.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mimer:/test"/>
<property name="hibernate.connection.username" value="SYSADM"/>
<property name="hibernate.connection.password" value="hemmeligt"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.MimerSQLDialect"/>
</properties>
</persistence-unit>
<persistence-unit name="MySQL">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="com.mysql.jdbc.Driver"/>
<property name="hibernate.connection.url" value="jdbc:mysql://localhost/Test"/>
<property name="hibernate.connection.username" value="root"/>
<property name="hibernate.connection.password" value="hemmeligt"/>
<property name="hibernate.connection.pool_size" value="5"/>
</properties>
</persistence-unit>
<persistence-unit name="SQLite">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="org.sqlite.JDBC"/>
<property name="hibernate.connection.url" value="jdbc:sqlite:test.db"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.SQLiteDialect"/>
</properties>
</persistence-unit>
<persistence-unit name="Derby">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="org.apache.derby.jdbc.EmbeddedDriver"/>
<property name="hibernate.connection.url" value="jdbc:derby:test"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.DerbyDialect"/>
</properties>
</persistence-unit>
<persistence-unit name="H2">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="org.h2.Driver"/>
<property name="hibernate.connection.url" value="jdbc:h2:test;FILE_LOCK=FS"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.H2Dialect"/>
</properties>
</persistence-unit>
<persistence-unit name="HSQLDB">
<provider>org.hibernate.ejb.HibernatePersistence</provider>
<class>Customer</class>
<class>CustomerActual</class>
<class>CustomerPotential</class>
<class>Order</class>
<class>OrderLine</class>
<exclude-unlisted-classes/>
<properties>
<property name="hibernate.show_sql" value="false"/>
<property name="hibernate.connection.driver_class" value="org.hsqldb.jdbc.JDBCDriver" />
<property name="hibernate.connection.url" value="jdbc:hsqldb:file:test"/>
<property name="hibernate.connection.pool_size" value="5"/>
<property name="hibernate.dialect" value="org.hibernate.dialect.HSQLDialect"/>
</properties>
</persistence-unit>
</persistence>
Most of it is rather self-explanatory, but two items is worth mentioning.
<property name="hibernate.show_sql" value="false"/>
If changed to true then Hibernate will log all the executed SQL statements. This is great for verifying that Hibernate is actually doing what you are expecting it to do.
<property name="hibernate.dialect" value="xxx.xxx.xxx.XxxxDialect"/>
This tells Hibernate which SQL dialect to use, which enables Hibernate to generate different SQL for different databases. Note that if not specified then Hibernate tries to auto-detect.
Customer.java:
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="customer")
@Inheritance(strategy=InheritanceType.JOINED)
public class Customer {
private int id;
private String name;
private String phone;
public Customer() {
this(0, "", "");
}
public Customer(int id, String name, String phone) {
super();
this.id = id;
this.name = name;
this.phone = phone;
}
@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="phone")
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
CustomerPotential.java:
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
@Entity
@Table(name="customer_potential")
@PrimaryKeyJoinColumn(name="id")
public class CustomerPotential extends Customer {
private String source;
public CustomerPotential( ) {
this(0, "", "", "");
}
public CustomerPotential(int id, String name, String phone, String source) {
super(id, name, phone);
this.source = source;
}
@Column(name="source")
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
}
CustomerActual.java:
import java.util.Set;
import java.util.TreeSet;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
@Entity
@Table(name="customer_actual")
@PrimaryKeyJoinColumn(name="id")
public class CustomerActual extends Customer {
private String address1;
private String address2;
private String contact;
private int discount;
private Set<Order> orders;
public CustomerActual() {
this(0, "", "", "", "", "", 0);
}
public CustomerActual(int id, String name, String phone, String address1, String address2, String contact, int discount) {
super(id, name, phone);
this.address1 = address1;
this.address2 = address2;
this.contact = contact;
this.discount = discount;
this.orders = new TreeSet<Order>();
}
@Column(name="address1")
public String getAddress1() {
return address1;
}
public void setAddress1(String address1) {
this.address1 = address1;
}
@Column(name="address2")
public String getAddress2() {
return address2;
}
public void setAddress2(String address2) {
this.address2 = address2;
}
@Column(name="contact")
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
@Column(name="discount")
public int getDiscount() {
return discount;
}
public void setDiscount(int discount) {
this.discount = discount;
}
@OneToMany(mappedBy = "customer", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
public Set<Order> getOrders() {
return orders;
}
public void setOrders(Set<Order> orders) {
this.orders = orders;
}
}
Order.java:
import java.math.BigDecimal;
import java.util.Set;
import java.util.TreeSet;
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.OrderBy;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="order_main")
public class Order {
private int id;
int customer;
private String status;
private Set<OrderLine> orderLines;
public Order() {
this(0, 0, "");
}
public Order(int id, int customer, String status) {
this.id = id;
this.customer = customer;
this.status = status;
this.orderLines = new TreeSet<OrderLine>();
}
@Id
@Column(name="id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="customer")
public int getCustomer() {
return customer;
}
public void setCustomer(int customer) {
this.customer = customer;
}
@Column(name="status")
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
@OneToMany(mappedBy = "order", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
public Set<OrderLine> getOrderLines() {
return orderLines;
}
public void setOrderLines(Set<OrderLine> orderLines) {
this.orderLines = orderLines;
}
@Transient
public BigDecimal getTotalPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : orderLines) {
res = res.add(ol.getTotalPrice());
}
return res;
}
}
OrderLine.java:
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="order_line")
public class OrderLine {
private int id;
private int order;
private String item;
private int quantity;
private BigDecimal price;
public OrderLine() {
this(0, 0, "", 0, BigDecimal.ZERO);
}
public OrderLine(int id, int order, String item, int quantity, BigDecimal price) {
this.id = id;
this.order = order;
this.item = item;
this.quantity = quantity;
this.price = price;
}
@Id
@Column(name="id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="order_main")
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
@Column(name="item")
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
@Column(name="quantity")
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
@Column(name="price")
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
@Transient
public BigDecimal getTotalPrice() {
return price.multiply(new BigDecimal(quantity));
}
}
AddJPA.java:
import java.math.BigDecimal;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.persistence.EntityManager;
import javax.persistence.EntityManagerFactory;
import javax.persistence.Persistence;
public class AddJPA {
public static void perform(String unit) {
System.out.printf("%s:\n", unit);
EntityManagerFactory emf = Persistence.createEntityManagerFactory(unit);
EntityManager em = emf.createEntityManager();
em.getTransaction().begin();
CustomerActual ca = new CustomerActual(6, "F company", "6666-6666", "F road 6", "F town", "Mr. F", 10);
Order o = new Order(6, 6, "In progress");
OrderLine ol = new OrderLine(8, 6, "X stuff", 1, new BigDecimal("72.00"));
o.getOrderLines().add(ol);
ca.getOrders().add(o);
em.persist(ca);
em.getTransaction().commit();
em.close();
emf.close();
}
public static void main(String[] args) throws Exception {
Logger.getLogger("org.hibernate").setLevel(Level.OFF);
if(args.length == 1) {
perform(args[0]);
}
}
}
ListJPA.java:
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 ListJPA {
public static void perform(String unit) {
System.out.printf("%s:\n", unit);
EntityManagerFactory emf = Persistence.createEntityManagerFactory(unit);
EntityManager em = emf.createEntityManager();
TypedQuery<CustomerPotential> qp = em.createQuery("SELECT cp FROM CustomerPotential AS cp ORDER BY cp.id", CustomerPotential.class);
List<CustomerPotential> resp = qp.getResultList();
for(CustomerPotential cp : resp) {
System.out.printf("**future customer** %s, %s, %s\n", cp.getName(), cp.getPhone(), cp.getSource());
}
TypedQuery<CustomerActual> qa = em.createQuery("SELECT DISTINCT ca FROM CustomerActual AS ca " +
"JOIN FETCH ca.orders o " +
"JOIN FETCH o.orderLines ol " +
"ORDER BY ca.id", CustomerActual.class);
List<CustomerActual> resa = qa.getResultList();
for(CustomerActual ca : resa) {
System.out.printf("%s, %s, %s, %s\n", ca.getName(), ca.getAddress1(), ca.getAddress2(), ca.getPhone());
for(Order o : ca.getOrders()) {
System.out.printf(" %d, %s, %.2f\n", o.getId(), o.getStatus(), o.getTotalPrice());
}
}
em.close();
emf.close();
}
public static void main(String[] args) throws Exception {
Logger.getLogger("org.hibernate").setLevel(Level.OFF);
if(args.length == 1) {
perform(args[0]);
}
}
}
Build and run:
$ define/nolog java$filename_controls 8
$ define/nolog decc$efs_charset true
$ hibpath = "/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ javac -cp 'hibpath' ListJPA.java AddJPA.java Customer.java CustomerActual.java CustomerPotential.java Order.java OrderLine.java
$ java -cp .:'hibpath':/javalib/rdbthinv5.jar:/javalib/rdbhib.jar "ListJPA" "Rdb"
$ java -cp .:'hibpath':/javalib/rdbthinv5.jar:/javalib/rdbhib.jar "AddJPA" "Rdb"
$ java -cp .:'hibpath':/javalib/rdbthinv5.jar:/javalib/rdbhib.jar "ListJPA" "Rdb"
$! java -cp .:'hibpath':/javalib/mimjdbc3.jar "ListJPA" "Mimer"
$! java -cp .:'hibpath':/javalib/mimjdbc3.jar "AddJPA" "Mimer"
$! java -cp .:'hibpath':/javalib/mimjdbc3.jar "ListJPA" "Mimer"
$ java -cp .:'hibpath':/javalib/mysql-connector-java-5_1_36-bin.jar "ListJPA" "MySQL"
$ java -cp .:'hibpath':/javalib/mysql-connector-java-5_1_36-bin.jar "AddJPA" "MySQL"
$ java -cp .:'hibpath':/javalib/mysql-connector-java-5_1_36-bin.jar "ListJPA" "MySQL"
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListJPA" "SQLite"
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "AddJPA" "SQLite"
$ java -cp .:'hibpath':/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar "ListJPA" "SQLite"
$ java -cp .:'hibpath':/javalib/derby.jar "ListJPA" "Derby"
$ java -cp .:'hibpath':/javalib/derby.jar "AddJPA" "Derby"
$ java -cp .:'hibpath':/javalib/derby.jar "ListJPA" "Derby"
$ java -cp .:'hibpath':/javalib/h2-1_2_147.jar "ListJPA" "H2"
$ java -cp .:'hibpath':/javalib/h2-1_2_147.jar "AddJPA" "H2"
$ java -cp .:'hibpath':/javalib/h2-1_2_147.jar "ListJPA" "H2"
$ java -cp .:'hibpath':/javalib/hsqldb.jar "ListJPA" "HSQLDB"
$ java -cp .:'hibpath':/javalib/hsqldb.jar "AddJPA" "HSQLDB"
$ java -cp .:'hibpath':/javalib/hsqldb.jar "ListJPA" "HSQLDB"
Customer.groovy:
import javax.persistence.*
@Entity
@Table(name="customer")
@Inheritance(strategy=InheritanceType.JOINED)
class Customer {
@Id
@Column(name="id")
int id
@Column(name="name")
String name
@Column(name="phone")
String phone
}
@Entity
@Table(name="customer_potential")
@PrimaryKeyJoinColumn(name="id")
class CustomerPotential extends Customer {
@Column(name="source")
String source
}
@Entity
@Table(name="customer_actual")
@PrimaryKeyJoinColumn(name="id")
class CustomerActual extends Customer {
@Column(name="address1")
String address1
@Column(name="address2")
String address2
@Column(name="contact")
String contact
@Column(name="discount")
int discount
@OneToMany(mappedBy = "customer", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
Set<Order> orders = new TreeSet<Order>()
}
Order.groovy:
import javax.persistence.*
@Entity
@Table(name="order_main")
class Order implements Comparable<Order> {
@Id
@Column(name="id")
int id
@Column(name="customer")
int customer
@Column(name="status")
String status
@OneToMany(mappedBy = "order", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
Set<OrderLine> orderLines = new TreeSet<OrderLine>()
@Transient
BigDecimal getTotalPrice() {
BigDecimal res = 0.00
for(OrderLine ol in orderLines) {
res = res + ol.getTotalPrice()
}
return res
}
@Override
int compareTo(Order o) {
return id <=> o.id
}
}
@Entity
@Table(name="order_line")
class OrderLine implements Comparable<OrderLine> {
@Id
@Column(name="id")
int id
@Column(name="order_main")
int order
@Column(name="item")
String item
@Column(name="quantity")
int quantity
@Column(name="price")
BigDecimal price
@Transient
BigDecimal getTotalPrice() {
return price * quantity
}
@Override
int compareTo(OrderLine o) {
return id <=> o.id
}
}
Note: implementing Comparable on M side is necessary to get Grrovy and Hibernate to work with the 1:M relations.
AddJPA.groovy:
import java.util.logging.*
import javax.persistence.*
void perform(String unit) {
emf = Persistence.createEntityManagerFactory(unit)
em = emf.createEntityManager()
em.transaction.begin()
ca = new CustomerActual(id:6, name:"F company", phone:"6666-6666", address1:"F road 6", address2:"F town", contact:"Mr. F", discount:10)
o = new Order(id:6, customer:6, status:"In progress")
ol = new OrderLine(id:8, order:6, item:"X stuff", quantity:1, price:72.00)
o.orderLines.add(ol)
ca.orders.add(o)
em.persist(ca)
em.transaction.commit()
em.close()
emf.close()
}
Logger.getLogger("org.hibernate").setLevel(Level.OFF)
if(args.length == 1) {
perform(args[0])
}
ListJPA.groovy:
import java.util.logging.*
import javax.persistence.*
void perform(String unit) {
emf = Persistence.createEntityManagerFactory(unit)
em = emf.createEntityManager()
qp = em.createQuery("SELECT cp FROM CustomerPotential AS cp ORDER BY cp.id", CustomerPotential.class)
resp = qp.getResultList()
for(cp in resp) {
println("**future customer** ${cp.name}, ${cp.phone}, ${cp.source}")
}
qa = em.createQuery("SELECT DISTINCT ca FROM CustomerActual AS ca " +
"JOIN FETCH ca.orders o " +
"JOIN FETCH o.orderLines ol " +
"ORDER BY ca.id", CustomerActual.class)
resa = qa.getResultList()
for(ca in resa) {
println("${ca.name}, ${ca.address1}, ${ca.address2}, ${ca.phone}")
for(o in ca.orders) {
println(" ${o.id}, ${o.status}, ${o.totalPrice}")
}
}
em.close()
emf.close()
}
Logger.getLogger("org.hibernate").setLevel(Level.OFF)
if(args.length == 1) {
perform(args[0])
}
gr_jpa.com:
$ groovy_cp = ".:/javalib/javax_persistence-api-2_2.jar:/javalib/hibernate-core-5_6_5_Final.jar:/javalib/h2-2_2_220.jar:/javalib/hsqldb.jar:/javalib/derby.jar:/javalib/hibernate-commons-annotations-5_1_2_Final.jar:/javalib/javax_activation-api-1_2_0.jar:/javalib/jboss-transaction-api_1_2_spec-1_1_1_Final.jar:/javalib/istack-commons-runtime-3_0_7.jar:/javalib/stax-ex-1_8.jar:/javalib/txw2-2_3_1.jar:/javalib/jboss-logging-3_4_3_Final.jar:/javalib/antlr-2_7_7.jar:/javalib/byte-buddy-1_12_7.jar:/javalib/classmate-1_5_1.jar:/javalib/jandex-2_4_2_Final.jar"
$ groovyc Order.groovy Customer.groovy
$ groovy ListJPA.groovy "H2"
$ groovy AddJPA.groovy "H2"
$ groovy ListJPA.groovy "H2"
$ groovy ListJPA.groovy "HSQLDB"
$ groovy AddJPA.groovy "HSQLDB"
$ groovy ListJPA.groovy "HSQLDB"
$!!!! groovy ListJPA.groovy "Derby"
$!!!! groovy AddJPA.groovy "Derby"
$!!!! groovy ListJPA.groovy "Derby"
$ groovy ListJPA.groovy "RemoteMySQL"
$ groovy AddJPA.groovy "RemoteMySQL"
$ groovy ListJPA.groovy "RemoteMySQL"
Derby is outcommented because there are some problem with the VMS, Java, Groovy, Hibernate, Derby version combination I used for test.
MySQL is tested with remote database as I ran this on VMS x86-64 and MySQL/MariaDB is not yet available on VMS x86-64. It will be available at some point and then the code will work with local database.
Customer.java:
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="customer")
@Inheritance(strategy=InheritanceType.JOINED)
public class Customer {
private int id;
private String name;
private String phone;
public Customer() {
this(0, "", "");
}
public Customer(int id, String name, String phone) {
super();
this.id = id;
this.name = name;
this.phone = phone;
}
@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="phone")
public String getPhone() {
return phone;
}
public void setPhone(String phone) {
this.phone = phone;
}
}
CustomerPotential.java:
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
@Entity
@Table(name="customer_potential")
@PrimaryKeyJoinColumn(name="id")
public class CustomerPotential extends Customer {
private String source;
public CustomerPotential( ) {
this(0, "", "", "");
}
public CustomerPotential(int id, String name, String phone, String source) {
super(id, name, phone);
this.source = source;
}
@Column(name="source")
public String getSource() {
return source;
}
public void setSource(String source) {
this.source = source;
}
}
CustomerActual.java:
import java.util.Set;
import java.util.TreeSet;
import javax.persistence.CascadeType;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.FetchType;
import javax.persistence.OneToMany;
import javax.persistence.OrderBy;
import javax.persistence.PrimaryKeyJoinColumn;
import javax.persistence.Table;
@Entity
@Table(name="customer_actual")
@PrimaryKeyJoinColumn(name="id")
public class CustomerActual extends Customer {
private String address1;
private String address2;
private String contact;
private int discount;
private Set<Order> orders;
public CustomerActual() {
this(0, "", "", "", "", "", 0);
}
public CustomerActual(int id, String name, String phone, String address1, String address2, String contact, int discount) {
super(id, name, phone);
this.address1 = address1;
this.address2 = address2;
this.contact = contact;
this.discount = discount;
this.orders = new TreeSet<Order>();
}
@Column(name="address1")
public String getAddress1() {
return address1;
}
public void setAddress1(String address1) {
this.address1 = address1;
}
@Column(name="address2")
public String getAddress2() {
return address2;
}
public void setAddress2(String address2) {
this.address2 = address2;
}
@Column(name="contact")
public String getContact() {
return contact;
}
public void setContact(String contact) {
this.contact = contact;
}
@Column(name="discount")
public int getDiscount() {
return discount;
}
public void setDiscount(int discount) {
this.discount = discount;
}
@OneToMany(mappedBy = "customer", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
public Set<Order> getOrders() {
return orders;
}
public void setOrders(Set<Order> orders) {
this.orders = orders;
}
}
Order.java:
import java.math.BigDecimal;
import java.util.Set;
import java.util.TreeSet;
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.OrderBy;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="order_main")
public class Order {
private int id;
int customer;
private String status;
private Set<OrderLine> orderLines;
public Order() {
this(0, 0, "");
}
public Order(int id, int customer, String status) {
this.id = id;
this.customer = customer;
this.status = status;
this.orderLines = new TreeSet<OrderLine>();
}
@Id
@Column(name="id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="customer")
public int getCustomer() {
return customer;
}
public void setCustomer(int customer) {
this.customer = customer;
}
@Column(name="status")
public String getStatus() {
return status;
}
public void setStatus(String status) {
this.status = status;
}
@OneToMany(mappedBy = "order", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
@OrderBy("id")
public Set<OrderLine> getOrderLines() {
return orderLines;
}
public void setOrderLines(Set<OrderLine> orderLines) {
this.orderLines = orderLines;
}
@Transient
public BigDecimal getTotalPrice() {
BigDecimal res = BigDecimal.ZERO;
for(OrderLine ol : orderLines) {
res = res.add(ol.getTotalPrice());
}
return res;
}
}
OrderLine.java:
import java.math.BigDecimal;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.Table;
import javax.persistence.Transient;
@Entity
@Table(name="order_line")
public class OrderLine {
private int id;
private int order;
private String item;
private int quantity;
private BigDecimal price;
public OrderLine() {
this(0, 0, "", 0, BigDecimal.ZERO);
}
public OrderLine(int id, int order, String item, int quantity, BigDecimal price) {
this.id = id;
this.order = order;
this.item = item;
this.quantity = quantity;
this.price = price;
}
@Id
@Column(name="id")
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
@Column(name="order_main")
public int getOrder() {
return order;
}
public void setOrder(int order) {
this.order = order;
}
@Column(name="item")
public String getItem() {
return item;
}
public void setItem(String item) {
this.item = item;
}
@Column(name="quantity")
public int getQuantity() {
return quantity;
}
public void setQuantity(int quantity) {
this.quantity = quantity;
}
@Column(name="price")
public BigDecimal getPrice() {
return price;
}
public void setPrice(BigDecimal price) {
this.price = price;
}
@Transient
public BigDecimal getTotalPrice() {
return price.multiply(new BigDecimal(quantity));
}
}
add_jpa.py:
from sys import argv
from java.math import BigDecimal
from java.util.logging import Level
from java.util.logging import Logger
from javax.persistence import Persistence
import CustomerActual
import Order
import OrderLine
def perform(unit):
print('%s:' % (unit))
emf = Persistence.createEntityManagerFactory(unit)
em = emf.createEntityManager()
em.getTransaction().begin()
ca = CustomerActual(6, 'F company', '6666-6666', 'F road 6', 'F town', 'Mr. F', 10)
o = Order(6, 6, 'In progress')
ol = OrderLine(8, 6, 'X stuff', 1, BigDecimal('72.00'))
o.orderLines.add(ol)
ca.orders.add(o)
em.persist(ca)
em.getTransaction().commit()
em.close()
emf.close()
Logger.getLogger('org.hibernate').setLevel(Level.OFF)
if len(argv) == 2:
perform(argv[1])
list_jpa.py:
from sys import argv
from java.util.logging import Level
from java.util.logging import Logger
from javax.persistence import Persistence
import CustomerPotential
import CustomerActual
def perform(unit):
print('%s:' % (unit))
emf = Persistence.createEntityManagerFactory(unit)
em = emf.createEntityManager()
qp = em.createQuery('SELECT cp FROM CustomerPotential AS cp ORDER BY cp.id', CustomerPotential)
resp = qp.getResultList();
for cp in resp:
print('**future customer** %s, %s, %s' % (cp.name, cp.phone, cp.source))
qa = em.createQuery('SELECT DISTINCT ca FROM CustomerActual AS ca ' + \
'JOIN FETCH ca.orders o ' + \
'JOIN FETCH o.orderLines ol ' + \
'ORDER BY ca.id', CustomerActual)
resa = qa.getResultList()
for ca in resa:
print('%s, %s, %s, %s' % (ca.name, ca.address1, ca.address2, ca.phone))
for o in ca.orders:
print(' %d, %s, %s' % (o.id, o.status, o.totalPrice))
em.close()
emf.close()
Logger.getLogger('org.hibernate').setLevel(Level.OFF)
if len(argv) == 2:
perform(argv[1])
Build and run:
$ hibpath = "/javalib/antlr-2_7_6.jar:/javalib/cglib-2_2.jar:/javalib/commons-collections-3_1.jar:/javalib/dom4j-1_6_1.jar:/javalib/hibernate-jpa-2_0-api-1_0_0_final.jar:/javalib/hibernate3.jar:/javalib/javassist-3_12_0_ga.jar:/javalib/jta-1_1.jar:/javalib/slf4j-api-1_6_1.jar:/javalib/slf4j-jdk14-1_6_1.jar"
$ jython_libs_prefix = hibpath + ":"
$ define/nolog java$filename_controls 8
$ define/nolog decc$efs_charset true
$ javac -cp 'hibpath' Customer.java CustomerActual.java CustomerPotential.java Order.java OrderLine.java
$ define/nolog jython_libs "/javalib/rdbthinv5.jar:/javalib/rdbhib.jar"
$ jython list_jpa.py """Rdb"""
$ jython add_jpa.py """Rdb"""
$ jython list_jpa.py """Rdb"""
$! define/nolog jython_libs "/javalib/mimjdbc3.jar"
$! jython list_jpa.py """Mimer"""
$! jython add_jpa.py """Mimer"""
$! jython list_jpa.py """Mimer"""
$ define/nolog jython_libs "/javalib/mysql-connector-java-5_1_36-bin.jar"
$ jython list_jpa.py """MySQL"""
$ jython add_jpa.py """MySQL"""
$ jython list_jpa.py """MySQL"""
$ define/nolog jython_libs "/javalib/sqlite-jdbc-3_14_1-vms.jar:/javalib/sqlitehib.jar"
$ jython list_jpa.py """SQLite"""
$ jython add_jpa.py """SQLite"""
$ jython list_jpa.py """SQLite"""
$ define/nolog jython_libs "/javalib/derby.jar
$ jython list_jpa.py """Derby"""
$ jython add_jpa.py """Derby"""
$ jython list_jpa.py """Derby"""
$ define/nolog jython_libs "/javalib/h2-1_2_147.jar
$ jython list_jpa.py """H2"""
$ jython add_jpa.py """H2"""
$ jython list_jpa.py """H2"""
$ define/nolog jython_libs "/javalib/hsqldb.jar
$ jython list_jpa.py """HSQLDB"""
$ jython add_jpa.py """HSQLDB"""
$ jython list_jpa.py """HSQLDB"""
Mimer is outcommented because the combination of Hibernate and Mimer I used to test with can not handle queries with ORDER BY. In general MIMER works fine with JPA and I suspect that newer versions may also handle queries with ORDER BY fine.
A few notes about the data classes and the JPQL:
ORM has become widely used in development today (Java - JPA, Hibernate, .NET - Entity Framework, NHibernate, PHP - Doctrine, Eloquent etc.), but not so much on VMS. Java and other JVM languages are probably the only language(s) that frequently use ORM.
People may prefer the object oriented view or they may prefer the relational view, but it is a fact that usage of ORM result in having to write much less code.
from code examples above (not counting data definitions that are resuable between many programs):
| Stack | Lines for add | Lines for list | Lines total | ||
|---|---|---|---|---|---|
| Technology | Language | Database | |||
| Embedded SQL | Cobol | * (Rdb, Mimer) | 61-65 | 148-152 | 209-217 |
| Embedded SQL | C | * (Rdb, Mimer) | 42-43 | 66-67 | 108-110 |
| Database specific API | C | MySQL | 128 | 116 | 244 |
| Database specific API | Pascal | MySQL | 64 | 51 | 115 |
| Database specific API | Basic | MySQL | 67 | 57 | 124 |
| Database specific API | PHP | MySQL | 32 | 41 | 73 |
| Database specific API | C | SQLite | 67 | 66 | 133 |
| Database specific API | Pascal | SQLite | 59 | 49 | 108 |
| Database specific API | PHP | SQLite | 44 | 37 | 81 |
| Standard API - JDBC | Java | * (Rdb, MySQL, Mimer, SQLite, Derby, H2, HSQLDB) | 44 | 56 | 100 |
| Standard API - DB API 2.0 | Python/Jython | * (Rdb, MySQL, SQLite) / (Rdb, MySQL, Mimer, SQLite, Derby, H2, HSQLDB) | 22 | 38-39 | 60-61 |
| Standard API - PDO | PHP | * (MySQL, SQLite) | 32 | 41 | 73 |
| ORM - JPA | Java | * (Rdb, MySQL, Mimer, SQLite, Derby, H2, HSQLDB) | 31 | 40 | 71 |
| ORM - JPA | Groovy | * (Rdb, MySQL, Mimer, SQLite, Derby, H2, HSQLDB) | 23 | 31 | 54 |
| ORM - JPA | Jython | * (Rdb, MySQL, Mimer, SQLite, Derby, H2, HSQLDB) | 30 | 33 | 63 |
Disclaimer: number of lines obviously depends on the coding style. The code here is intended to follow a common coding style for the particular languages as good as I can.
The conclusions are:
| Technology | Portable across databases | Language support | Tooling requirement | Code size | Speeed (*) | |
|---|---|---|---|---|---|---|
| Embedded SQL | Mostly, exceptions include:
|
Static typed compiled languages | A pre-compiler specific for combination of programming language and underlying database API (example: Cobol and Rdb) |
Medium | Fast | |
| Database API | Database specific API | No | Usually C and languages able to call C libraries | Just the library | Large | Fast |
| Standard API | Yes or mostly depending on the API support for handling differences in SQL dialect | Technology specific:
|
Just the library | Medium | Fast | |
| ORM | Yes (an ORM specific query language handles differences in SQL dialect) |
Object oriented languages | An ORM library specific for combination of technology and underlying database API (example: JVM and JDBC) |
Small | Startup overhead and then fast Warning: wrong use of ORM can result in very inefficient SQL being generated |
|
*) speed is only the speed of the database access itself. Besides that there are generally startup overhead of starting a JVM and interpreted script languages are slower than AOT/JIT compiled languages executing code.
The final conclusion must be:
| Version | Date | Description |
|---|---|---|
| 1.0 | November 17th 2023 | Initial version |
| 1.1 | December 12th 2023 | Add Basic and Groovy examples |
See list of all articles here
Please send comments to Arne Vajhøj
Embedded SQL works great.
The problem is the support. Today very few combinations of databases and languages support embedded SQL.