[Info-vax] VMS databases

[Craig A. Berry]

On 11/18/23 5:47 PM, 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

On a semi-related note, I once wrote wrappers around some of the socket
routines so I could call them from BASIC.  recv looked like this:

#define BASIC_True -1
#define BASIC_False 0

int
mytcp_recv( int *sockfd,
              struct dsc$descriptor_d *msg_dsc,
              int *bytes_read,
              struct dsc$descriptor_d *errmsg_dsc ) {

     *bytes_read = recv( *sockfd,
                         msg_dsc->dsc$a_pointer,
                         msg_dsc->dsc$w_length,
                         0 );

     if ( *bytes_read < 0 ) {
         /* other error reporting */
         return(BASIC_False);
     }

     return(BASIC_True);
}

Call it from BASIC like so:

   EXTERNAL LONG FUNCTION MYTCP_RECV(  LONG BY REF, STRING BY DESC, LONG 
BY REF, STRING BY DESC )
   SOCKET_SUCCESS = MYTCP_RECV( SOCKET, MESSAGE, DATA_LENGTH, ERRMSG )

I think it took me a bit to wise up to the fact that BASIC's descriptors
are dynamic and needed the dsc$descriptor_d declarations.