Blob field in ISeries

[Cesar Gonzalez]

Hi all,

I'm testing the blob field in ISeries with CA Plex in 6.1 build 43.032d, ISeries in 5.3, client C++ and RPG400. I read the chapter 11 of the manual, Working with Blobs, and I started working with the test model of the product (blobsample.mdl) but I have several problems:

- I increased the length of the blob because I had errors in the compilation of rpg programs (255).
- When I try to insert a file in the database, .txt .doc, etc.., the process seems to work but the original file is modified and its size is reduced to 1 Kb ¿¿??
- When I retrieve the blob of the database, the file created in the %temp% folder too has 1 Kb.

Conclusion, the insert process modifies the original file ¿¿?? and inserts an object into the database that it isn´t the original file.

I would appreciate if you told me how to work with this type of fields in the Iseries.

Regards,

Cesar Gonzalez

[George Jeffcock]

Hi Cesar

In case you missied it here is the limitations for Blob on the Iseries implmentation

System i limitations

CA Plex support for blobs on the System i has significant limitations. Most notably the maximum allowed size is determined by the 9999 byte limit on the total size of a variable in an RPG/400 function. RPG IV functions have increased this maximum allowed size to 32,767 bytes. To maximize the available space, you could place the blob field in a separate variable with no other fields. Use the GetBlobSize API to check the size of the blob before writing it to the database. Blob fields for the System i should have FLD AS400 format SYS=Hex.

Additionally, there is no support for piecewise fetches from the System i.



So to get the most you need to make sure the update and fetch function are language type RPGIV (preferablly just the key as input and the blob field as output) and the blob field has the triple FLD AS400 format SYS=Hex.

hope you missed this in chapter 11 and it helps.

[Cesar Gonzalez]

Hi George,

Thanks for your response. I read that chapter and I applied the recommendations: FLD AS400 format SYS = Hex, FLD SQL format SYS = Blob and the language in RPG400 (mandatory in my case) but the problems continue.

Do you have more idea?

Regards,

Cesar

[George Jeffcock]

You must set the function that updates and the function that fetches from the database to be Language Sys RPGIV not RPG400

[Cesar Gonzalez]

Hi George,

I just compile the functions in RPGIV and the problems continue. Also I don´t understand why I must compile in RPGIV when the manual too indicate RPG400.

[Deactivated User]

cesar.gonzalez wrote:

I just compile the functions in RPGIV and the problems continue. Also I don´t understand why I must compile in RPGIV when the manual too indicate RPG400.

You can continue to use RPG400 if you wish, but you will be limited to a field size of 9999.

Using RPGIV the limit is 65535.

These were RPG limitations related to character field sizes. At release 6.1 of IBM i that limitation has been increased to 16773104, for RPGIV. Maybe you could get CA to increase the maximum supported field size to 16773104 for RPGIV.

Crispin.

[Cesar Gonzalez]

Hi Crispin,

Thanks for your response.

The field size of the blob isn´t the problem, my tests are with files .txt of 4 Kb or pdf of 100 Kb.

[Deactivated User]

How is the field defined in the Insert and Fetch function?

100KB won't work as that is larger than 9999 and 65535.

Crispin.

[Cesar Gonzalez]

Hi Crispin,

The field is defined in both functions as: Blob type blob, length 255 (in the original local model, blobsample.mdl, it has 32 but it had compilation erros), as400 format Hex and sql format blob.

You are right with the file of 100 KB but the files of 1 KB until 4 KB don´t work and they are in the range.

[George Jeffcock]

Your problem is the field length I would guess for Iseries implmentation.

Help says

A blob requires a length (or generation errors occur). However, note that the length does not actually affect the implementation of the corresponding database column. The only practical effect of the length is to determine the default length of the control that is created if a blob is placed on a panel

but this is not true for Iseries

If you leave the sample model length of 35 for the Blob Field you will only be able to store 35 HEX characters..not much use.

So you must increase the length of the database field to 32766 as you´will get the following message if set to greater

'Table: Blob Store.Physical table Field: Blob' has invalid length 32767 for DDS, should be between 1 and 32766

BUT you come up against another limitation as the Physical file can not have a record length greater than 32766, ie all the fields lengths combined

'Table: Blob Store.Physical table Field: Blob' causes the DDS maximum record length of '32766' to be exceeded

     Z* CRTPF TEXT('Blob Store.Physical table')
     A* 
     A          R BB10T                     TEXT( 'Blob Store.Physical table' )
     A            BB1EA         10A         TEXT( 'Blob Code' )
     A                                      COLHDG( 'Blob Code' )
     A            BB1FA          3A         TEXT( 'Blob Type' )
     A                                      COLHDG( 'Blob Type' )
     A            BB1GA         20A         TEXT( 'Blob Description' )
     A                                      COLHDG( 'Blob Description' )
     A            BB1HA      32766H         TEXT( 'Blob' )
     A                                      COLHDG( 'Blob' )

So make the Blob field 32766 - 10 - 3- 20 = 32733 Long

     Z* CRTPF TEXT('Blob Store.Physical table')
     A* 
     A          R BB10T                     TEXT( 'Blob Store.Physical table' )
     A            BB1EA         10A         TEXT( 'Blob Code' )
     A                                      COLHDG( 'Blob Code' )
     A            BB1FA          3A         TEXT( 'Blob Type' )
     A                                      COLHDG( 'Blob Type' )
     A            BB1GA         20A         TEXT( 'Blob Description' )
     A                                      COLHDG( 'Blob Description' )
     A            BB1HA      32733H         TEXT( 'Blob' )
     A                                      COLHDG( 'Blob' )

Iseries implmentation has

significant limitations

as CA Plex Help States


See Designing for Multi-Platform Applications in Chapter 16 Configuration Control

RPG/400 imposes a maximum length of 9999 characters on the total length of variables (the sum of all the field lengths). The combined total length of input and output variables cannot exceed 9992. Panel grid variables have a maximum length of 9996, which is reduced by one for every label, static, and data control in the region.

RPGIV allows a maximum length of 32767 for character fields. However, DDS allows the creation of files with a maximum record length of 32766.

[George Jeffcock]

Secondly

A variable can have a maximum length of 9999. This allows fields to be up to 9999 characters in length. This surpasses the RPG/400 limitation of a 256 character length for a field. RPGIV variables can have a maximum length of 32767. This allows RPGIV fields to be up to 32,767 characters in length.

This we already know but interesting the following statement

The combined total length of input and output variables may not exceed 9992, as 7 characters are used as the return status.

So to be on the safe side you should look at making the Blob field 32733 - 7 = 32726 Long so that Function: Blob Store.Fetch.SingleFetch.FetchedData is correct.

[Cesar Gonzalez]

Hi George,

Thanks for this comprehensive explanation. I changed the length of the field according to your information and everything works fine. Thanks you very much.

Crispin, thank you also for your responses.