[Info-vax] Other than Oracle, What MySQL/DBMS options are there for OpenVms?

[Arne Vajhøj]

On 3/26/2019 2:39 PM, Jan-Erik Söderholm wrote:
> Den 2019-03-26 kl. 18:12, skrev Arne Vajhøj:
>> On 3/26/2019 10:53 AM, Jan-Erik Söderholm wrote:
>>> *External* procedures/functions can be written in anything, Macro
>>> is just fine. This procedure calls a Macro routine:
>>>
>>> create module CPMS_EVENT_SUBM_MODULE
>>>
>>>      procedure CPMS_EVENT_SUBM_PROC (
>>>          in    :EVENT
>>>              INTEGER)
>>>      ;
>>>    begin
>>>      declare :stat integer;
>>>      call zvsubmit2('CPMS_QUE',
>>>                     'mk_com:MK_CPMS.com',
>>>                     cast(:event as char(8)),
>>>                     'N',
>>>   Cancel            'Y',
>>>                     'N',
>>>                     'MK',
>>>                     :stat);
>>>    end;
>>>
>>> end module;
>>>
>>> And the external routine doesn't have to be written at all,
>>> it can be a system service in VMS.
>>
>> Interesting.
>>
>> But it seems to be more external than the SP
>> in other languages, that has access to the database
>> context, can return result sets etc..
>>
> 
> Of course. But then you use stored procedures. What is pour point?
> 
> External procedures/functions are for the cases that are not
> specifically database oriented, We use it to submit a batch job
> by an "instert trigger" on a table. Actually the example above.

And I can see that it is useful.

I just think that it would be bit cumbersome to use
that for the same as you can with the non-SQL SP's.

Let me give an example. MS SQLServer and C#.

Code:

using System;
using System.Data;
using System.Data.SqlTypes;
using System.Text.RegularExpressions;

using Microsoft.SqlServer.Server;


namespace SP
{
     public class Demo
     {
         [SqlProcedure]
         public static void Test(int arg)
         {
             SqlDataRecord rec = new SqlDataRecord(new SqlMetaData("f1", 
SqlDbType.Int), new SqlMetaData("f2", SqlDbType.VarChar, 50));
             SqlContext.Pipe.SendResultsStart(rec);
             for(int i = arg; i < arg + 10; i++)
             {
                 rec.SetInt32(0, i + 1);
                 rec.SetString(1, new string((char)('A' + i), i + 1));
                 SqlContext.Pipe.SendResultsRow(rec);
             }
             SqlContext.Pipe.SendResultsEnd();
         }
     }
}

Definition:

CREATE ASSEMBLY SPDemo FROM 'C:\Work\SPUDF\SPDemo.dll' WITH 
PERMISSION_SET = SAFE;
GO
CREATE PROCEDURE SPDemo(@arg INTEGER)
AS
EXTERNAL NAME SPDemo.[SP.Demo].Test;
GO

Usage:

1> EXEC dbo.SPDemo 3;
2> GO
  f1          f2
  ----------- --------------------------------------------------
            4 DDDD
            5 EEEEE
            6 FFFFFF
            7 GGGGGGG
            8 HHHHHHHH
            9 IIIIIIIII
           10 JJJJJJJJJJ
           11 KKKKKKKKKKK
           12 LLLLLLLLLLLL
           13 MMMMMMMMMMMMM

(10 rows affected)

Yes - the example is stupid, but it illustrates that the code is
very much executing within database context and can return a
result set to the calling connection.

>>>> Does RDB support returning multiple result sets or only a single 
>>>> result set?
>>>
>>> You define what paramaters are "in" or "out".
>>> We are not talking about "result sets" like a stream
>>> of records here.
>>
>> Typical SP's whether SQL or another language allow to
>> return result sets.
>>
>> But only some allow a SP to return multiple result sets.
> 
> Well, your external procedure/function (in any language) can in
> itself make a database attach and return a "result set" in some
> table.

Sure. But it will require both more code and more SQL to get
it all working.

Arne