[Info-vax] VMS databases

[Arne Vajhøj]

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