SQLRPGLE VARCHAR/CLOB Improved interoptability RFE proposal

[Vectorspace]

RPGLE allows VARCHARs up to 16MB
SQL only allows VARCHARs up to 32KB, and CLOBs for anything larger

Embedded SQL in an SQLRPGLE program cannot process VARCHAR host variables larger than 32740. Instead, the developer must code an SQLTYPE(CLOB) host variable, which is converted at precompile into a DS with a 4-byte length subfield and a CHAR subfield.

This makes interoperability between large VARCHARs and CLOB columns difficult in an SQLRPGLE program - the developmer must either use a pointer overlay (the SQLTYPE(CLOB:x) DS is actually an identical memory structure to a VARCHAR(x:4)), or manually reassign between two different host variables.

Because the CLOB DS uses the same memory layout as a large VARCHAR, it should be a fairly straightforward change to make large VARCHAR host variables interoperable with embedded SQL. This was discussed in a prior thread: https://www.code400.com/forum/forum/...ar-onto-a-clob

Below I have drafted two RFE's for this that I am going to raise to IBM. But before I do, I am after feedback from you all. Do these look ok? Should I change anything?

================================================== ==========================

SQLRPGLE Embedded SQL support for large VARCHARs level 1

See linked RFE (SQLRPGLE Embedded SQL support for large VARCHARs level 2) for a proposed further enhancement on top of this change

  Dcl-S myVar sqlType(CLOB:10000);

is converted by the SQL Precompiler into:

  Dcl-Ds myVar;
    myVar_LEN  uns(10);
    myvar_DATA char(10000);
  End-Ds;

Embedded SQL functions referencing myVar do so by address:

  SQL0001 = %addr(myVar);

This layout (4 byte unsigned int followed by character bytes) is identical in memory layout to a 4-byte-prefix VARCHAR

  Dcl-S myVar  VARCHAR(10000:4);

Therefore, SQL processing should be able to treat host variables VARCHAR(x:4) exactly the same as sqlType(CLOB:x) up to the max 16MB size of RPGLE variables

The SQL Precompiler should be updated to recognise that VARCHAR(x:4) is the same as a converted sqlType(CLOB:x), and allow them to be referenced as host variables using the same method (%addr()).
This is in effect an implicit conversion between SQL CLOBs and RPGLE VARCHAR(x:4)
This would have several benefits:
* Programs with existing large 4-byte-prefix VARCHARs would be instantly interoperable with embedded SQL (selecting and inserting/updating), without needing to reassign to or pointer overlay with an sqlType(CLOB)
* CLOB columns could be selected directly to regular VARCHARs to then be used with regular RPGLE BIFs such as %trim, %scan, %xlate, etc.
* All of this serves to simplify the use of CLOB columns in SQLRPGLE processing

This would not work with VARCHAR lengths 32705 to 65535 unless they had an explicit 4 byte prefix (by default these will only have a 2 byte prefix, making them incompatible without SQL processing changes), or unless SQL processing is updated to account for it.

The linked level 2 RFE, which is dependent on this RFE, proposes amending SQL processing too so it can handle 2 byte prefix VARCHARs as well as 4 byte, completing support. It was kept as a separate RFE to enable partial/staged delivery

This could also apply to sqlType(BLOB) and VARGRAPHIC/VARBINARY

================================================== ==========================

SQLRPGLE Embedded SQL support for large VARCHARs level 2

This change is dependent on the linked RFE (SQLRPGLE Embedded SQL support for large VARCHARs level 1) being delivered

  Dcl-S myVar sqlType(CLOB:10000);

is converted by the SQL Precompiler into:

  Dcl-Ds myVar;
    myVar_LEN  uns(10);
    myvar_DATA char(10000);
  End-Ds;

Embedded SQL functions referencing myVar do so by address:

  SQL0001 = %addr(myVar);

This layout (4 byte unsigned int followed by character bytes) is identical in memory layout to a 4-byte-prefix VARCHAR

  Dcl-S myVar  VARCHAR(10000:4);

Therefore, SQL processing should be able to treat host variables VARCHAR(x:4) exactly the same as sqlType(CLOB:x) up to the max 16MB size of RPGLE variables

VARCHARs of lengths up to 32704 can be handled by SQL processing as VARCHARs. But lengths 32705-65535 are too long for an SQL VARCHAR, but will by default have a 2 byte prefix making them incompatible with sqlType(CLOB), unless the developer adds an explicit 4 byte prefix to the VARCHAR definition.

The level 1 RFE proposes amending the SQL precompiler to recognise that VARCHAR(x:4) is the same as a converted sqlType(CLOB:x), and allow them to be referenced as host variables using the same method (%addr())
The SQL precompiler and SQL processing should be further amended to be able to handle 2 byte prefix VARCHARs. They could be handled in the same way (via %addr()) but SQL Processing must be made aware of and account for the length component being only 2 bytes instead of 4.

This would have several benefits:
* Programs with existing large VARCHARs of any size/prefix would be instantly interoperable with embedded SQL (selecting and inserting/updating), without needing to reassign to/pointer overlay with an sqlType(CLOB)
* CLOB columns could be selected directly to regular VARCHARs to then be used with regular RPGLE BIFs such as %trim, %scan, %xlate, etc.
* All of this serves to simplify the use of CLOB columns in SQLRPGLE processing

This could also apply to sqlType(BLOB) and VARGRAPHIC/VARBINARY

[Brian Rusch]

There is currently an RFE that is the same as your first request (at least I think it's the same): https://www.ibm.com/developerworks/r...e&CR_ID=130836