Safe to pointer overlay a varchar onto a clob?

[JonBoy]

Yea - I finally found the comments. The whole thing is a design mess - you can see that there are comments but not view them - and while I did eventually try the "Aha" link it was far from obvious why I would want/need to.

[Vectorspace]

Thanks - you'll see I gave you credit! Any edits you think I should make let me know.

IBM also don't make it obvious how to add or view comments - you can only do that from the view on https://ibm-power-systems.ideas.ibm.com, not on https://ideas.ibm.com/. If you load it on the latter you have to click on the "See in the Aha! portal" button to move over to https://ibm-power-systems.ideas.ibm.com before you can do anything with comments.

[JonBoy]

Voted. Made me realize though that IBM could do a lot to make it more obvious how to "vote" - a teeny-tiny thumbs-up image that can be clicked is not my idea of a vote button - but i couldn't see any other option.

[Vectorspace]

Apologies for the necro, and the 4 year delay - but I have raised two IDEAs for this:

https://ideas.ibm.com/ideas/IBMI-I-3732

https://ideas.ibm.com/ideas/IBMI-I-3733

Please consider upvoting

Note they can also be found at:

https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3732

https://ibm-power-systems.ideas.ibm.com/ideas/IBMI-I-3733

[Vectorspace]

I would write three RFE's: one for #1, one for #2.1, and one for #2.2 marking it as dependent on #2.1. I figure this way it's more likely that one of the ideas will get delivered. Otherwise if they reject the RFE because of one of the ideas, we would have to re-raise. Do these look good to everyone:

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

SQLRPGLE/CLOB support improvement - define sqlType(CLOB) as VARCHAR

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 optionally convert sqlType(CLOB) to a VARCHAR instead of a DS. This would have several benefits:
* CLOBs could be used as-is with regular RPGLE BIFs such as %trim, %scan, %xlate, etc.
* Programmers would not need to explicitly set separate length and data variables.
* All of this serves to simplify the use of CLOB columns in SQLRPGLE processing

This could be accomplished in a backwards-compatible way by the use of a Definition-Spec keyword to control which type the CLOB is converted to
Dcl-S myVar1 sqlType(CLOB:10000) lobSubType(*VARCHAR); // This one will be a VARCHAR
Dcl-S myVar2 sqlType(CLOB:10000) lobSubType(*DS); // This one will be a DS
Dcl-S myVar2 sqlType(CLOB:10000); // This one will be a DS because DS is the default

A Control-Spec keyword could be used to override the global default
Dcl-C lobSubType(*VARCHAR)
Dcl-S myVar1 sqlType(CLOB:10000) lobSubType(*VARCHAR); // This one will be a VARCHAR
Dcl-S myVar2 sqlType(CLOB:10000) lobSubType(*DS); // This one will be a DS
Dcl-S myVar2 sqlType(CLOB:10000); // This one will be a VARCHAR because the default has been overridden to VARCHAR

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

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

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, without needing to reassign to/pointer overlay with an sqlType(CLOB)
* CLOBs could be selected 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 the 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)

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, without needing to reassign to/pointer overlay with an sqlType(CLOB)
* CLOBs could be selected 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

[JonBoy]

JonBoy, I was wondering if you are still planning to raise RFE's for this?

Yes - but I've been insanely busy for the last couple of weeks and it is not getting better any time soon.

Please feel free to write it up and I'll gladly vote for it.

[Vectorspace]

JonBoy, I was wondering if you are still planning to raise RFE's for this?

[JonBoy]

However will you get it back out of their tiny tummies Scott?

[Scott Klement]

In order to use my idea, I'll need $1 million transferred to my private bank account in the Caymans

[Vectorspace]

Use anything you want. Though full disclaimer, #1 is just a fleshed out explanation of Scott's idea.

I feel I should also mention, when I was experimenting with this earlier, I found that using a larger than supported varchar with embedded sql was causing the precompiler to crash. I never narrowed down the exact circumstances as work had other demands for my time, but felt it worth mentioning.

[JonBoy]

Thanks for that thoughtful contribution. Together with Scott's idea I think we have some reasonable options here.

P.S. Hope you don;'t mind if I steal some of your text for the RFE.

[Vectorspace]

I think there are two different possible changes here. Making CLOBs work like large varchars, and/or making embedded SQL treat large varchars as CLOBs.

#1, Precompiler converts SQLTYLE(CLOB:X) to VARCHAR(X:4) instead of to a DS.
This would let the variable be used in regular RPG. This should require no changes to actual SQL processing, as it is referenced by address and the memory layout (4 byte unsigned int for number of chars, followed by X bytes of character space) is identical. This could be accomplished using a global control-spec keyword to set a program-wide default, and/or a d-spec keyword or SQLTYPE keyword parameter to allow each individual SQLTYPE definition to be individually specified.


#2, Allow embedded SQL to directly reference large varchars (>32kb).
This would let existing large varchars be used in embedded SQL without having to first copy them to CLOBs (increased memory usage, which is especially an issue here because these are large variables) or use pointer/DS overlays.
This could be done with a precompiler-only change that would not work for some varchars (#2.1) or a precompiler and SQL Processing change to support all varchars (#2.2)

#2.1 Only allow this for large varchars defined with 4 byte prefixes.
Varchars with no explicitly specified prefix have a 2 byte prefix if their number of characters is 65565 or less. 65536 or more get a 4 byte prefix. You can explicitly specify a 4 byte prefix for an X <= 65535 varchar with VARCHAR(X:4).
It is the 4 byte varchars that are identical in memory layout to the CLOBs, therefore with a precompiler-only change, varchars with an implicit or explicit 4 byte prefix could be directly referenced in exactly the same way as a CLOB (via %addr). This means varchars above 64kb will work (they always have a 4 byte prefix), varchars above 32kb and below 64kb will work only if they have an explicit 4 byte prefix, but varchars above 32kb and below 64kb with only a 2 byte prefix will not work. (varchars < 32kb are of course directly referenced already)

#2.2 Allow this for large varchars defined with 4 byte prefixes.
Option #2.1 being a precompiler-only change works because a varchar with a 4 byte prefix has identical memory layout to a CLOB. The SQL Processing is referencing the CLOB only by %addr which means that it must be assuming the number of characters portion is 4 bytes. It would therefore need amending to recognise when a varchar has a 2 byte prefix instead of 4, in addition to the precompiler changes. But this would mean that ALL varchars are directly reference-able in embedded SQL operations, irrespective of length. This to my mind is the ideal solution.


Personally I would like to see both options #1 and #2.2 done, but I recognise #2.2 is much more complex piece of work because it is more than just the precompiler.

[JonBoy]

The SQL pre-compiler folks have asked me to write up an RFE for this ability. Any and all suggestions on approach are welcome. Will post the RFE number here when it is done.

[Scott Klement]

Yeah, I agree... adding an overlaid varchar in the DS would be awkward. (Though, still better than having to set both the length and data separately.)

I'd prefer to see something like this:

dcl-s myLobData SQLTYPE(CLOB: 1000000: RPGVARCHAR);

Then the precompiler could convert it to a VARCHAR rather than a DS when that extra keyword is given.

[Vectorspace]

That would mean the varchar has a different name to the clob itself, since the ds has the name of the clob. Kind of like my pointer example. In an ideal world, you'd use the same variable name in both rpg and sql