[Info-vax] VMS databases

[Neil Rieck]

On Saturday, November 18, 2023 at 6:47:22 PM UTC-5, Arne Vajhøj wrote:
> On 11/18/2023 9:22 AM, Arne Vajhøj wrote: 
> > On 11/17/2023 8:10 PM, Arne Vajhøj wrote: 
> >> I just did another little article. 
> >> 
> >> https://www.vajhoej.dk/arne/articles/vmsdbx.html 
> >> 
> >> providing some examples for the databases available on VMS (Rdb, MySQL, 
> >> SQLite, Mimer, Derby, H2, HSQLDB) in various programming languages 
> >> (Cobol, C, Pascal, Java, Jython, Python, PHP). 
> >> 
> >> Nothing surprising and a lot of overlap with previous 
> >> articles, but a different perspective. 
> > 
> > Niel Rieck replied (for some reason the post did not propagate 
> > to eternal-september, so this is a manual copy from Google Groups): 
> > 
> > > Two additional points. 
> > > 
> > > 1) I've done a bit of hacking with SQLite (on both OpenVMS + Linux ) 
> > > and can inform that it should only be used in single user 
> > > applications. When any process issues an "update table" command, all 
> > > other processes are locked out. 
> > 
> > For multi user scenarios database servers are usually better than 
> > embedded databases. 
> > 
> > I would have thougth SQLite could only have locked some part of 
> > the database when doing an UPDATE, but ... 
> > 
> > > 2) Back in 2016 I received a copy of MariaDB-5.5 for OpenVMS Itanium 
> > > from Mark Berryman. Since 90% of our VMS stuff is written in BASIC, I 
> > > spent more than a few days trying to glue Mark's client code to my 
> > > BASIC programs (we are in the process of moving our data from RMS to 
> > > MariaDB). Linking the object files was fairly easy using Mark's 
> > > instructions. Trying to pass string arrays from C to BASIC was a 
> > > little more difficult. My eventual solution involved created string 
> > > arrays in a BASIC declared COMMON then had C employ str$get1_dx() to 
> > > copy the data back. I would be interested to learn if you had a better 
> > > method. 
> > 
> > > https://neilrieck.net/demo_vms_html/mysql_api_demo14_c.html 
> > > (crude c demo) 
> > > https://neilrieck.net/demo_vms_html/mysql_demo14_bas.html 
> > > (crude BASIC demo) 
> > 
> > VMS Basic is as it is and the libmysql API is what it is and 
> > any solution is constrained by that. 
> > 
> > I do not have any magic solution for the problem. I would 
> > probably have made the API differently (but differently does 
> > not necessarily mean better).
> I could not resist trying. 
> 
> This is first attempt. But I think it is a relative clean API. 
> 
> $ type list.bas 
> program main 
> 
> option type = explicit 
> 
> declare integer dbcon, stmt, outparam 
> declare integer f1 
> declare string f2 
> 
> %include "b.bas" 
> 
> dbcon = bmysql_open("localhost", 3306, "root", "", "test") 
> stmt = bmysql_stmt_prepare(dbcon, "SELECT f1,f2 FROM t1") 
> outparam = bmysql_outparam_init(2) 
> call bmysql_outparam_integer(outparam, 0, f1) 
> call bmysql_outparam_string(outparam, 1, f2) 
> call bmysql_stmt_execute(stmt, 0, outparam) 
> while bmysql_stmt_fetch(stmt, outparam) = 0 
> print f1, "|" + f2 + "|" 
> next 
> call bmysql_outparam_free(outparam) 
> call bmysql_stmt_free(stmt) 
> call bmysql_close(dbcon) 
> 
> end program 
> $ bas list 
> $ link list + b/opt 
> $ type list2.bas 
> program main 
> 
> option type = explicit 
> 
> declare integer dbcon, stmt, inparam, outparam 
> declare integer f1 
> declare string f2 
> 
> %include "b.bas" 
> 
> dbcon = bmysql_open("localhost", 3306, "root", "", "test") 
> stmt = bmysql_stmt_prepare(dbcon, "SELECT f1 FROM t1 WHERE f2 LIKE ?") 
> f2 = "B%" 
> inparam = bmysql_inparam_init(1) 
> call bmysql_inparam_string(inparam, 0, f2) 
> outparam = bmysql_outparam_init(1) 
> call bmysql_outparam_integer(outparam, 0, f1) 
> call bmysql_stmt_execute(stmt, inparam, outparam) 
> while bmysql_stmt_fetch(stmt, outparam) = 0 
> print f1 
> next 
> call bmysql_outparam_free(inparam) 
> call bmysql_outparam_free(outparam) 
> call bmysql_stmt_free(stmt) 
> call bmysql_close(dbcon) 
> 
> end program 
> $ bas list2 
> $ link list2 + b/opt 
> $ type add.bas 
> program main 
> 
> option type = explicit 
> 
> declare integer dbcon, stmt, inparam 
> declare integer f1 
> declare string f2 
> 
> %include "b.bas" 
> 
> dbcon = bmysql_open("localhost", 3306, "root", "", "test") 
> stmt = bmysql_stmt_prepare(dbcon, "INSERT INTO t1 VALUES(?,?)") 
> f1 = 4 
> f2 = "DDDD" 
> inparam = bmysql_inparam_init(2) 
> call bmysql_inparam_integer(inparam, 0, f1) 
> call bmysql_inparam_string(inparam, 1, f2) 
> call bmysql_stmt_execute(stmt, inparam, 0) 
> call bmysql_inparam_free(inparam) 
> call bmysql_stmt_free(stmt) 
> call bmysql_close(dbcon) 
> 
> end program 
> $ bas add 
> $ link add + b/opt 
> $ type del.bas 
> program main 
> 
> option type = explicit 
> 
> declare integer dbcon, stmt, inparam 
> declare integer f1 
> 
> %include "b.bas" 
> 
> dbcon = bmysql_open("localhost", 3306, "root", "", "test") 
> stmt = bmysql_stmt_prepare(dbcon, "DELETE FROM t1 WHERE f1 = ?") 
> f1 = 4 
> inparam = bmysql_inparam_init(1) 
> call bmysql_inparam_integer(inparam, 0, f1) 
> call bmysql_stmt_execute(stmt, inparam, 0) 
> call bmysql_inparam_free(inparam) 
> call bmysql_stmt_free(stmt) 
> call bmysql_close(dbcon) 
> 
> end program 
> $ bas del 
> $ link del + b/opt 
> $ run list 
> 1 |A| 
> 2 |BB| 
> 3 |CCC| 
> $ run list2 
> 2 
> $ run add 
> $ run list 
> 1 |A| 
> 2 |BB| 
> 3 |CCC| 
> 4 |DDDD| 
> $ run list2 
> 2 
> $ run del 
> $ run list 
> 1 |A| 
> 2 |BB| 
> 3 |CCC| 
> $ run list2 
> 2 
> 
> Arne

Could you post the included code: "b.bas"

Neil