On Sunday, November 19, 2023 at 8:12:22 PM UTC-5, Arne Vajhøj wrote:
> On 11/19/2023 11:50 AM, Arne Vajhøj wrote:
> > On 11/19/2023 4:47 AM, Neil Rieck wrote:
> >> 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:
> >>>> 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.
> >
> >> Could you post the included code: "b.bas"
> >
> > B.BAS is not so interesting:
> > B2C.C has the interesting stuff:
> I just made bunch of changes (support more data types,
> support CLASS S strings, some error handling in the form
> of negative return values and a sub to get error message).
>
> https://www.vajhoej.dk/arne/opensource/vms/vmsbmysql-src-v0_1.zip
>
> Arne

I will check it out this offering.

BTW the stuff you published here yesterday worked like a charm (once I was able to divine what went in the opt file)

Neil Rieck
Waterloo, Ontario, Canada.
https://neilrieck.net
https://neilrieck.net/OpenVMS.html
https://neilrieck.net/OpenVMS-Programmers-Corner.html

On Sunday, November 19, 2023 at 3:01:40 PM UTC-8, Single Stage to Orbit wrote:
> On Sun, 2023-11-19 at 16:03 -0500, Arne Vajhøj wrote:
> > > SQLite and MySQL are not solutions for the same problem.
> >
> > I just did a check on my PC for what applications use SQLite.
> >
> > A lot.
> >
> > 3 interesting ones:
> > * Oracle DB GDAL
> > * MySQL WorkBench
> > * PostgreSQL pgAdmin
> >
> > :-)
> >
> > Even if it is all about a database server then a small embedded
> > database
> > can be the right tool for some task.
> Android is riddled with apps that make use of SQLite3.

I would hope so, considering that android.database.sqlite has been part of the API since version 1. No need to reinvent the wheel. I believe most experienced Android devs know about and use Room or some other persistence API as opposed to raw SQL queries, but I could be wrong.

I just looked at what Dart / Flutter are doing and they have a “sqlflite” plugin that supports iOS, Android, and macOS.

On Saturday, November 18, 2023 at 3:44:00 AM UTC-8, Neil Rieck wrote:
> On Friday, November 17, 2023 at 8:10:25 PM UTC-5, 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.
> >
> > Arne
> This is a very impressive publication. On behalf of the OpenVMS community I say "thanks!"
>
> 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.

I’ve also long considered SQLite to be basically only useful for single-process use. SQLite 3 has file locking support using various OS-specific APIs but I’m not sure how efficient or concurrent it is in practice.

I see Arne’s blog post doesn’t appear to mention the native VMS port of SQLite by David L. Jones. I downloaded it and looked at it very briefly and it has a custom locking implementation using a “lock nexus file” (default sys$login:sqlite3_lock_nexus.nxs). Code comment: “The intent is the actual files are unlocked and shared access is coordinated through locking of records in the nexus files (which allows waits) as a proxy.”

There’s clearly a lot of effort that has gone into the native port but I’m not sure if it matters much when multi-process access doesn’t seem to be a common use case for SQLite. It’s also an unusual choice to create a separate file for lock management and put it in the user’s home directory. What if you want to make the database file accessible to multiple users?

On 11/20/2023 1:58 PM, Neil Rieck wrote:
> On Sunday, November 19, 2023 at 8:12:22 PM UTC-5, Arne Vajhøj wrote:
>> On 11/19/2023 11:50 AM, Arne Vajhøj wrote:
>>> On 11/19/2023 4:47 AM, Neil Rieck wrote:
>>>> 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:
>>>>>> 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.
>>>
>>>> Could you post the included code: "b.bas"
>>>
>>> B.BAS is not so interesting:
>>> B2C.C has the interesting stuff:
>> I just made bunch of changes (support more data types,
>> support CLASS S strings, some error handling in the form
>> of negative return values and a sub to get error message).
>>
>> https://www.vajhoej.dk/arne/opensource/vms/vmsbmysql-src-v0_1.zip
>
> I will check it out this offering.

It is the same code just added and modified a few things.

> BTW the stuff you published here yesterday worked like a charm (once I was able to divine what went in the opt file)

The ZIP also has B.OPT!

:-)

Arne

On 11/20/2023 2:46 PM, Jake Hamby (Solid State Jake) wrote:
> On Saturday, November 18, 2023 at 3:44:00 AM UTC-8, Neil Rieck wrote:
>> On Friday, November 17, 2023 at 8:10:25 PM UTC-5, 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.

>> 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.
>
> I’ve also long considered SQLite to be basically only useful for
> single-process use. SQLite 3 has file locking support using various
> OS-specific APIs but I’m not sure how efficient or concurrent it is
> in practice.

Embedded database are really for the single-process use case.

> I see Arne’s blog post doesn’t appear to mention the native VMS port
> of SQLite by David L. Jones.

It is the one I am using. I am not even aware of any other.

> I downloaded it and looked at it very
> briefly and it has a custom locking implementation using a “lock
> nexus file” (default sys$login:sqlite3_lock_nexus.nxs). Code comment:
> “The intent is the actual files are unlocked and shared access is
> coordinated through locking of records in the nexus files (which
> allows waits) as a proxy.”
>
> There’s clearly a lot of effort that has gone into the native port
> but I’m not sure if it matters much when multi-process access doesn’t
> seem to be a common use case for SQLite.

I think it works pretty well within its expected "comfort zone".

The JDBC driver could need some work though.

> It’s also an unusual choice
> to create a separate file for lock management and put it in the
> user’s home directory. What if you want to make the database file
> accessible to multiple users?
Multi-users does not necessarily mean multiple usernames.

But if multiple usernames then you can define the logical
SQLITE3_NEXUS_FILE to point to a file shared among them.

Arne

On 11/20/2023 11:50 AM, Stephen Hoffman wrote:
> On 2023-11-19 22:22:37 +0000, Jan-Erik Söderholm said:
>> Den 2023-11-19 kl. 18:37, skrev Stephen Hoffman:
>>> As with many things in IT, that depends.
>>>
>>> Expectations and related sizes can also differ. What can be
>>> considered a small database for SQLite can potentially be considered
>>> a large database for OpenVMS, for instance.
>>>
>>> Expectations? SQLite tops out at 256 TiB databases, while OpenVMS
>>> file storage tops out at 2 TiB files absent 'heroic' efforts.
>>
>> A single Rdb database can have 8192 storage areas (individual files)
>> so the max database size would be something like 16.000 TiB.
>
> So... Heroic _and_ expensive.

I believe that people would:
* switch from SQLite to a database server way before 256 TB
* switch from Rdb to Wide Column Data Store NoSQL database (like
HBase or Cassandra/ScyllaDB) way before 16 PB

Arne

On Monday, November 20, 2023 at 4:14:49 PM UTC-5, Arne Vajhøj wrote:
> On 11/20/2023 2:46 PM, Jake Hamby (Solid State Jake) wrote:
> > It’s also an unusual choice
> > to create a separate file for lock management and put it in the
> > user’s home directory. What if you want to make the database file
> > accessible to multiple users?
> Multi-users does not necessarily mean multiple usernames.
>
> But if multiple usernames then you can define the logical
> SQLITE3_NEXUS_FILE to point to a file shared among them.
>

The VMS VFS supports 2 lock methods. The default is to use a nexus file,
which lets users run SQLite without requiring an administrator account to
set up. The alternate method uses system DLM locks accessed via a User
Written System Service (UWSS), which requires an administrator to
install the SQLITE3_PRV_LOCK protected image (usually at system startup).

On Monday, November 20, 2023 at 6:17:41 PM UTC-8, David Jones wrote:
> On Monday, November 20, 2023 at 4:14:49 PM UTC-5, Arne Vajhøj wrote:
> > On 11/20/2023 2:46 PM, Jake Hamby (Solid State Jake) wrote:
> > > It’s also an unusual choice
> > > to create a separate file for lock management and put it in the
> > > user’s home directory. What if you want to make the database file
> > > accessible to multiple users?
> > Multi-users does not necessarily mean multiple usernames.
> >
> > But if multiple usernames then you can define the logical
> > SQLITE3_NEXUS_FILE to point to a file shared among them.
> >
> The VMS VFS supports 2 lock methods. The default is to use a nexus file,
> which lets users run SQLite without requiring an administrator account to
> set up. The alternate method uses system DLM locks accessed via a User
> Written System Service (UWSS), which requires an administrator to
> install the SQLITE3_PRV_LOCK protected image (usually at system startup).

Ah, that's interesting. My apologies for skipping over that option. I'll have to check out that code. User-written system services are an interesting concept.

Jake

On 11/18/2023 6: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):

>>  > 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.

>> 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.

I updated the article with:
* Basic & MySQL examples
* Groovy & JPA examples

(yes - Groovy and JPA makes for real short code)

Arne