Idea: Add SQL prefix to generate RPG procedure

[SanjulaGanepola]

To help with creating APIs in RPG for querying tables, I was thinking it would be useful to add an SQL prefix that goes beyond what rpg does and generates an actual RPG procedure.

For example, given:

select
       empno,
       rtrim(firstnme) || ' ' || rtrim(midinit) || ' ' || rtrim(lastname),
       salary + bonus + comm
from
       sample.employee
where
       empno = ?;

We could generate something like:

**free

ctl-opt nomain;

dcl-ds employee_t qualified template;
  empno char(6);
  name varchar(50);
  netincome packed(9:2);
end-ds;

dcl-proc getEmployee export;
  dcl-pi *n like(employee_t);
    empno char(6) const;
  end-pi;

  dcl-ds employee likeds(employee_t);

  exec sql
    select
      empno,
      rtrim(firstnme) || ' ' || rtrim(midinit) || ' ' || rtrim(lastname),
      salary + bonus + comm
    into
      :employee.empno,
      :employee.name,
      :employee.netincome
    from
      sample.employee
    where
      empno = :empno;

  return employee;
end-proc;

the template is basically:

**free

ctl-opt nomain;

dcl-ds <table_name>_t qualified template;
  <table_columns>
end-ds;

dcl-proc get<table_name> export;
  dcl-pi *n like(<table_name>_t);
    <prepared_statement_parameters>
  end-pi;

  dcl-ds <table_name> likeds(<table_name>_t);

  exec sql
    <user_query_with_into_added_and_where_with_parameters>

  return <table_name>;
end-proc;

This would tie in really well with the unit test stub generation. Imagine now (all now in a couple seconds!): Write an SQL query -> Generate an RPG procedure -> Generate a RPG test stub -> Celebrate! 🎉

The difficult part may be adding the into ... in the correct spot and properly handling the parameters, but I think this can be done.

Questions:

• What prefix makes sense? Should this simply be rpg: or have a new prefix (rpgproc: or something else)? Change both prefixes to dcl-ds: and dcl-proc:?

@worksofliam @forstie What do you think about this?

[forstie]

I like it.
Let's get @chrjorgensen 's take on this one too

[chrjorgensen]

@SanjulaGanepola What an excellent idea! I can't wait to see this in action! 😍

About the prefix: IMHO I think the prefix should contain the language generated, i.e. something with ...rpg.... Your suggestion rpgproc: sounds fine to me. Another idea could be rpgapi:. One could argue that the existing rpg: should be renamed (or duplicated) to rpgds:, to indicate a RPG DS is generated.

Having the language in the prefix would also allow for later addition of the same feature for JavaScript, Python and PHP.

An idea: Could the template be user-editable, to allow the user to change the template to comply with company standard? E.g. two commands Edit RPG procedure template and Reset RPG procedure template?

Finally, IIRC the | character is a variant character and not consistent across CCSID's. Please use the concat operator instead of ||, if I'm correct on this. @forstie ?

EDIT: Sorry, just realized your use of | was only an example. Just forget the above remark. 😊

[SanjulaGanepola]

An idea: Could the template be user-editable, to allow the user to change the template to comply with company standard? E.g. two commands Edit RPG procedure template and Reset RPG procedure template?

Making the template user configurable is actually a very interesting idea (probably in settings.json). I will take a look at how this can be best done.

[forstie]

Yes, I advise using CONCAT instead of '|' for the very reason you cited.
For the prefix name, it has to be simple, clear, and leave us room to add other prefix names that do other things.
Including rpg in the name makes some sense as it is rpg specific.
The creation of the rpg prefix (perhaps) didn't take all of these factors into consideration and I would agree that rpgds seems a better fit now and into the future.

For the new prefix, rpgproc seems a good fit.

[chrjorgensen]

@SanjulaGanepola @forstie After some additional thinking during the weekend, I've come to the opinion that we may be violating the intention behind the prefix - it was created to alter the output from the SQL statement, displaying the result in different formats. The rpg prefix changed this into generating code, and the udtf PR #459 and this idea are following the same path as the rpg prefix.

Is it possible to have code generation in a context menu item instead? Like the RPGLE extension, where you can extract some code into its own procedure? The context menu has keyboard shortcut Shift-F10:

=>

Moving code generation to a context menu would make a clear distinction between altering the format of the result (prefix) and generating code based on a SQL SELECT statement. And may be more obvious and logical for the developer?

WDYT? 🤔

[Wright4i]

@chrjorgensen is touching on a great point about the prefix previously being a way to format the resulting data, not a way to generate code that would retrieve the data.

I think offering menu options for both the reformatting and code generation would be wonderful.

However, I also wouldn't mind an in-line shortcut. Maybe if it uniquely separates the two distinct functions. For example @rpgproc: generates RPG procedure code while non-@ prefixes are reserved for formatting the resulting data?

[worksofliam]

I think we could go with two options:

1. We keep prefixes for the use case of converting the result set to a different data form (json, csv, etc)
2. We convert rpg: and others to [individual] code actions for each item we generate.

And then the one off is keeping cl for the backwards compatibility with ACS.

Thoughts?

[NicolasSchindler]

I had exactly the same idea after techXchange. That would definitely be a nice addition. Could also consider adding something like rpgcursor to read the entire table in a loop? Separating code generation from this would make sense to me. And I would leave cl as it is, as most people are familiar with it.

[janfh]

@SanjulaGanepola : Are you working on this? I could look into it if you want to

[SanjulaGanepola]

@janfh I haven't had the chance to spend much time on this one yet so yes please feel free to take a look! Thanks