codefori / codefori/vscode-db2i

Idea: Add SQL prefix to generate RPG procedure

Open
#460 10 comments 0 reactions 2 assignees Claimed by @janfh View on GitHub
enhancement question
Dominant language
TypeScript
Stars
76
Forks
54
Avg merge
17m
Merged PRs (30d)
1

Description

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:
```sql
select
empno,
rtrim(firstnme) || ' ' || rtrim(midinit) || ' ' || rtrim(lastname),
salary + bonus + comm
from
sample.employee
where
empno = ?;
```

We could generate something like:
```RPGLE
**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:

```RPGLE
**free

ctl-opt nomain;

dcl-ds _t qualified template;

end-ds;

dcl-proc get export;
dcl-pi *n like(_t);

end-pi;

dcl-ds likeds(_t);

exec sql

return ;
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?

Contributor guide

No contributing guide indexed for this repository

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.