ILE Bind by reference using CRTSQLRPGI

[inigo.redin]

Hi.

I've been trying a solution for this, but. I cannot find it.

What I'm trying to do, is work with the "bind by reference" ability, but working with ILE RPG written with embedded sql.

I can use the BNDDIR ctl opt in my source. And everything works correctly.
But that means a "bind by copy" method. Checked deleting the SRVPGM and even the BINDDIR. And the caller program still works.

So, is there any way to use "bind by reference" in an ILERPGSQL program?


After my question, an example:

Program SNILOG is a module, that conains several procedures. Part of them, exported.
In QSRVSRC I set the exported procedures, with a source with the same name: SNILOG. Something like this:

STRPGMEXP PGMLVL(*CURRENT)
/************************************************** ******************/
/* *MODULE SNILOG INIGREDI 04/10/21 15:25:30 */
/************************************************** ******************/
EXPORT SYMBOL("GETDIAG_TOSTRING")
EXPORT SYMBOL("GETDIAGNOSTICS")
EXPORT SYMBOL("GRABAR_LOG")
EXPORT SYMBOL("SNILOG")
ENDPGMEXP

As part of the procedures are programmed with embedded sql, the compilation must be done with CRTSQLRPGI, using the parameter OBJTYPE(*SRVPGM).
So, I finally get a SRVPGM called SNILOG, with those 4 procedures exported.

Once I've got the SRVPGM, I add it to a BNDDIR called SNI_BNDDIR.

Ok, let's go to the caller program: SNI600V.
Defined with

dftactgrp(*no)

, of course!.
And compiled with CRTSQLRPGI and parameter OBJTYPE(*PGM).

Here, if I use the control spec

bnddir('SNI_BNDDIR')

, it works fine.
But not fine enough, as this is a "bind by copy" method (I can delete the SRVPGM or the BNDDIR, and it is still working fine).

When I'm not working with SQL, I can use the CRTPGM command, and I can set the BNDSRVPGM parameter, to set the SRVPGM the program is going to be called. Well, just their procedures...
But I cannot find any similar option in CRTSQLRPGI command.
Nor in opt codes in ctl-opt sentence (We have BNDDIR, but not BNDSRVPGM option).


Any idea?


I'm running V7R3M0 with TR level: 6


Thanks in advance!

[MartinTosney]

Bind-by-reference should work fine for this, I would have thought. Can you confirm that you definitely added the service program object to your binding directory, and not the module object?

It would also be interesting to see the module and service programs lists from DSPPGM SNI600V after creation, so you can confirm it has definitely bound the module.

Note that if you want to pass parameters through to the underlying RPG compile command (e.g. to CRTBNDRPG, for CRTSQLRPGI OBJTYPE(*PGM) ) then you can specify these in the COMPILEOPT parameter of CRTSQLRPGI. e.g.

CRTSQLRPGI COMPILEOPT('BNDDIR(SNI_BNDDIR)')

However, CRTBNDRPG doesn't let you specify a list of service programs either. So without a binding directory you would be looking at a two-stage compile, where you create the module with CRTSQLRPGI OBJTYPE(*MODULE), and then use CRTPGM or CRTSRVPGM to create the final object.

[inigo.redin]

Yes, in he BNDDIR I had only the SRVPGM.

Now I kow what was the problem.
Even deleting the SRVPGM, the call was OK. That was what made me suspect it was copied.
But, after ending the job, it worked fine. So i got an error about SRVPGM didn't exist.

So, once again, the ACTGRP was the problem.


Thanks for your help!!

[Vectorspace]

In my experience, the first time you access a service program in a job, the service program is "remembered" by the job even if the service program is then deleted (not sure about removal from bnddir, haven't tried that). If the job is ended and restarted, or reset using some combination of RCLRSC and RCLACTGRP, that can cause it to do a fresh lookup.
I see this a lot with my current project, when I use a JDBC SQL connection to connect to the IBMi to call SQL procedures that ultimately reference a service program. If I update the service program and recompile it, the JDBC connection still sees the previous version, and I have to disconnect and reconnect the JDBC connection to see updates.

I assume it's an efficiency thing, so that the system does not need to look up the service program every time (similar to an RPGLE program that does not set on INLR)