Is there a way to do a custom data truncation of array elements so that a part of each array element can be seen in the column output when querying the whole column containing the array elements when the database is started with -SQLTruncateTooLarge OUTPUT?


 

The current behavior is that as much as possible is shown from the first array element of that column, then as much as possible as possible from the second array element of that column, etc ... until the SQL width is reached and therefore the data truncated (because of the database being started with the option -SQLTruncateTooLarge OUTPUT)

The default truncation is expected behavior. when selecting a column containing an array of Strings as a regular column the content of the array is treated as a single String.

There is no built in functionality to distribute the content of the column array elements more evenly between those column arrays.

Since OpenEdge 11.7 user defined functions (UDF) can be created:

• OpenEdge Data Management: SQL Development, SQL Development, User Defined Functions
  https://documentation.progress.com/output/ua/OpenEdge_latest/index.html#page/dmsdv/user-defined-functions.html
• Extend Your SQL with User-Defined Functions in OpenEdge 11.7:
  https://www.progress.com/blogs/extend-your-sql-with-user-defined-functions-in-openedge-11-7

For SQL user defined functions to work the CLASSPATH and PATH environment variables need to be created or modified to include additional .jar files and an additional path reference before starting the database from the same OpenEdge PROENV command prompt. If the database is already running, the database will need to be restarted.
 
As an example, on Windows:
 

set CLASSPATH=C:\Progress\OpenEdge117\java\prosp.jar;C:\Progress\OpenEdge117\java\jdbc.jar;%CLASSPATH%
set PATH=C:\Progress\OpenEdge117\jre\bin\server;%PATH%

proserve testdb -S 5555

To avoid forgetting to set the PATH and CLASSPATH environment variables when using proserve to start a database from a script or the command line, these can be set in the <DLC>\bin\proserve.bat file:
 

if "%DLC%"==""         set DLC=C:\Progress\OpenEdge117
set CLASSPATH=%DLC%\java\prosp.jar;C:\Progress\OpenEdge117\java\jdbc.jar;%CLASSPATH%
set PATH=%DLC%\jre\bin\server;%PATH%

In OpenEdge 12 jdbc.jar does not need to be added in the CLASSPATH.
 
When using the AdminServer to manage databases:
 
The CLASSPATH and PATH need to be modified in <DLC>\properties\AdminServerPlugins.properties:
 
Add prosp.jar to the line beginning with 'classpath='.
 

[PluginPolicy.Progress.Database]
classpath=C:\Progress\OpenEdge/java/juniper.jar,C:\Progress\OpenEdge/java/prorepl.jar,C:\Progress\OpenEdge/java/prosp.jar

In the OpenEdge Explorer/management web interface navigate to the 'Environment Variables' configuration tab of the database(s) to configure and add:
 

PATH=C:\Progress\OpenEdge120\jre\bin\server;%PATH%

This will add the following line under the [environment] section of the <DLC>\properties\conmgr.properties file:
 

[environment.testdb]
    PATH=C:\Progress\OpenEdge\jre\bin\server;%PATH%

Both the AdminServer and the databases will need to be restarted to consider the modified Classpath and Path Environment Variable.
 
Create a UDF myCustomFunction SQL Function:
 
The following SQL/java code can be used to create a user defined myCustomFunction SQL function in a SQL Editor.
 
For example: A good SQL editor to create SQL user defined functions is the free DBEAVER SQL community edition tool version 6.1.3 from https://dbeaver.io/download/ described in Article:

•  OpenEdge How to configure the DBeaver SQL GUI Client to connect to an OpenEdge database   

Create Function myCustomFunction(col CHAR,concatenatedTableFieldName varchar(20))
return VARCHAR(200)
 import
 import java.io.* ;
 import java.lang.*;
begin
  String primKey = col;
  String inputTableColumnInfo = concatenatedTableFieldName ;
  String[] tableColumnInfo = inputTableColumnInfo.split("\\.");
  String tableName = tableColumnInfo[0];
  String columnName = tableColumnInfo[1];        
  int sqlWidth = 0;
  String tempSqlWidth = "";
  int arrayAmount = 0;
  String tempArrayAmount = "";
  String recordLength = "";
  String tempRecordLengthElement = "";
  String tempFieldContent = "";
  int mytemp = 0;
  String tempTotalLengthOfRecord = "";
  int totalLengthOfRecord = 0;
 
  SQLCursor empcursor = new SQLCursor ("select width, array_extent from sysprogress.syscolumns_full where tbl = '" + tableName + "' and col = '" + columnName + "' " ) ;
  empcursor.open () ;
  do
  {empcursor.fetch ();
  if (empcursor.found ())
  {
   tempSqlWidth = (String) empcursor.getValue (1, CHAR);
   sqlWidth = Integer.parseInt(tempSqlWidth);
   tempArrayAmount = (String) empcursor.getValue (2, CHAR);
   arrayAmount = Integer.parseInt(tempArrayAmount);
  }
 } while (empcursor.found ()) ;
  empcursor.close () ;

int[] intArray = new int[arrayAmount];
empcursor = new SQLCursor ("SELECT LENGTH("+ columnName + "[?]) FROM PUB." + tableName + " WHERE id = '"+ primKey +"' " ) ;
    
for (int i = 0; i < arrayAmount; i++)
 {
   empcursor.setParam (1, (i + 1)); 
   empcursor.open () ;
  do
  {empcursor.fetch ();
  if (empcursor.found ())
   {
   tempRecordLengthElement = (String) empcursor.getValue (1, CHAR);
   }
  } while (empcursor.found ()) ;
  empcursor.close () ;
        
 intArray[i] = Integer.valueOf(tempRecordLengthElement)  ;
 }

  empcursor = new SQLCursor ("SELECT LENGTH("+ columnName +") FROM PUB." + tableName + " WHERE id = '"+ primKey +"' " ) ;
  empcursor.open () ;
  do
  {empcursor.fetch ();
  if (empcursor.found ())
   {
   tempTotalLengthOfRecord = (String) empcursor.getValue (1, CHAR);
   }
  } while (empcursor.found ()) ;
  empcursor.close () ;
 
  totalLengthOfRecord = Integer.valueOf(tempTotalLengthOfRecord)  ;
 
 
 if (totalLengthOfRecord > sqlWidth)
 {
  String[] FieldArrayContent = new String[arrayAmount];
  empcursor = new SQLCursor ("SELECT "+ columnName + "[?] FROM PUB." + tableName + " WHERE id = '"+ primKey +"' " ) ;    
  for (int i = 0; i < arrayAmount; i++)
   {
    empcursor.setParam (1, (i + 1)); 
    empcursor.open () ;
  do
  {empcursor.fetch ();
  if (empcursor.found ())
   {
   tempFieldContent = (String) empcursor.getValue (1, CHAR);
   }
  } while (empcursor.found ()) ;
  empcursor.close () ;
        
 FieldArrayContent[i] = tempFieldContent ;
 }
 
 int chCount = (sqlWidth - (arrayAmount -1));
 int[] truncateLength = new int[arrayAmount];
 
 int i = 0;
        
        while (i < chCount) {
              for(int j=0; j < (arrayAmount ); j++){
                   if ( (intArray[j] != 0) && (truncateLength[j] < intArray[j]) && (i < chCount) )  
                    {
                      truncateLength[j] = truncateLength[j] + 1;
                      i++;
                    }                  
              }
        }
        

String outputString = "";
for (int j = 0; j < arrayAmount; j++)
 {
   outputString = outputString + ";" + FieldArrayContent[j].substring(0, truncateLength[j])  ;
 }

return outputString.substring(1);

}
 else
{
  String recContent = "";
  empcursor = new SQLCursor ("SELECT "+ columnName +" FROM PUB." + tableName + " WHERE id = '"+ primKey +"' " ) ;
  empcursor.open () ;
  do
  {empcursor.fetch ();
  if (empcursor.found ())
   {
   recContent = (String) empcursor.getValue (1, CHAR);
   }
  } while (empcursor.found ()) ;
  empcursor.close () ;
 
 return recContent;
 
}
 
END

(other SQL editors might give the error 'Error: [DataDirect][OpenEdge JDBC Driver][OpenEdge] Syntax error in SQL statement at or about " import java.util.Date" (10713)' when trying to execute the above SQL/java code)

Add the user defined function myCustomFunction in the database schema:
 
Within an OpenEdge PROENV command prompt window. The text file myCustomFunction.sql is attached to this article:
 
sqlexp testdb -S 5555 -user sysprogress -password sysprogress -infile myCustomFunction.sql
 
The UDF will be created in the database schema of the user creating the user defined function, therefore when the UDF is called by another SQL user, instead of using myCustomFunction() ,  sysprogress.myCustomFunction() will need to be used in this example. 
 
To use the custom truncation:  

SELECT myCustomFunction(ID, 'testTable.testfield') as testField FROM PUB."testTable";

The 'ID' used in the above user defined function need to be unique for the table, and in 'testTable.testField' the tableName need to be explicitly stated.\
The custom user defined function will return:
 

TESTFIELD           |
--------------------|
111;222;333;444     |
12345;1234;1234;1234|
87654;8765;8765;8765|
a1a2a;b1b2;c1c2;d1d2|
A1A2A3A;;C1C;D1D2D3D|
A1;;C;D1D2D3D4D5D6D7|
A1A2A3A4A;;;D1D2D3D4|

instead of:
 

testField           |
--------------------|
111;222;333;444     |
1234567890ABCDEF;123|
8765432;8765432;8765|
a1a2a3a4a5a6a7a8a9;b|
A1A2A3A4A5A6A7A8;;C1|
A1;;C;D1D2D3D4D5D6D7|
A1A2A3A4A5A6A7A8;;;D|

when a regular 'SELECT testfield FROM PUB."testTable";' with a SQL width of 20 is done on the same column






 

To test the SQL user defined function described above first execute the following commands on a database started with the option -SQLTruncateTooLarge OUTPUT:
 

create table "PUB"."testTable" (
"ID" varchar (40) default '' collate "basic_I",
"testField" varchar (3) vararray[4] default '' collate "basic_I"
)
area "Schema Area" ;

ALTER TABLE PUB."testTable" ALTER COLUMN testField SET PRO_SQL_WIDTH 72;

INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('1','111;222;333;444');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('2','1234567890ABCDEF;1234567890ABCDEF;1234567890ABCDEF;1234567890ABCDEF');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('3','8765432;8765432;8765432;8765432');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('4','a1a2a3a4a5a6a7a8a9;b1b2b3b4b5b6b7b8b9;c1c2c3c4c;d1d2d3d4d5d6d7d8d9');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('5','A1A2A3A4A5A6A7A8;;C1C;D1D2D3D4D5D6D7');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('6','A1;;C;D1D2D3D4D5D6D7');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('7','A1A2A3A4A5A6A7A8;;;D1D2D3D4D5D6D7');
INSERT INTO "PUB"."testTable" (ID,testField) VALUES ('8','A1A2A3A4A5A6A7A8A9a10a11a12a13a14a15a16a17a18;;;D1D2D3D4D5D6D7');

commit;

and then decrease the SQL width to 20 by executing:
 

ALTER TABLE PUB."testTable" ALTER COLUMN testField SET PRO_SQL_WIDTH 20;