Support Article
Error during execution of UDFs for pr_read_from_stream
SA-3848
Summary
Following an upgrade from PRPC 6.2 SP2 to PRPC 6.3 SP1, application developers notice that UDF functions for pr_read_from_stream are missing.
Using the procedure previously provided by GCS to install these UDFs, the developers installed the functions on their DEV environment. They used the same procedure on their UAT environment. The UDF installation does not work and the error message is displayed.
Error Messages
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "pr_read_from_stream":
System.IndexOutOfRangeException: Index was outside the bounds of the array.
System.IndexOutOfRangeException:
at System.Collections.Generic.Dictionary`2.Insert(TKey key, TValue value, Boolean add)
at System.Collections.Generic.Dictionary`2.Add(TKey key, TValue value)
at com.pega.pegarules.data.udf.directstreamreader.FixedCacheMap.put(String aKey, CachedDirectStream aStream)
at com.pega.pegarules.data.udf.directstreamreader.UserDefinedFunctions.pr_read_from_stream(String aProperty, String aInsKey, SqlBinary aBlob)
.
Steps to Reproduce
Run a Federated Case Management (FCM) report or run the following query:
SELECT dbo.pr_read_from_stream('pzInsKey', pzInsKey, pzPVStream) as "StringValue" from pr4_rule where pxInsID like 'CANNOT%'
Root Cause
The root cause of this problem is defect or misconfiguration in the PRPC operating environment.
Resolution
Perform the following local-change to reinstall the Java UDF reporting feature.
Make sure that all previous versions of the database functions are gone.
Use the plain SQL 'drop function' DDL [drop function pr_read_from_stream, and so on] or a database managing GUI program, such as SQL Developer, to accomplish this.
Install the jar library into your database.
The library is named prreadstream.jar (for all databases except SQL Server) or prreadstream.txt (for SQL Server). The tool needed to install the library is a main Java class named com.pega.pegarules.install.DatabaseLibraryLoader.
Find all of these in the standard distribution image.
Find prreadstream.jar and prreadstream.txt under archives/udf.
Find the DatabaseLibraryLoader class in scripts/prdeploy.jar.
Once you have identified the library and the DatabaseLibraryLoader tool, you can use these to install the UDFs.
This utility was designed to be very similar to SQLLoader: It takes the same kind of properties file and many of the same parameters.
To run the utility, specify a properties file that contains database connection information and the jar containing the UDF.
On SQL Server, instead of a jar, you must specify the location of the prreadstreamlib.txt library file. (This file contains the DLLs that need to be installed on SQL Server in an encoded format.)
This utility could be used to install other Java or C-sharp functions or stored procedures in the future.
How do I use the DatabaseLibraryLoader?
Run DatabaseLibraryLoaderwith the Help switch: --help
1. Loads a jar or a DLL containing user-defined functions and/or stored procedures into a database
2. DatabaseLibraryLoader [--help] --config=<file> --profile=<name> <jar/library pathname>
3. --help: Print this message, and exit
4. --config=<file>: Specifies the bootstrap config file used to connect to
5. --profile=<name>: Specifies the name of the datasource profile in the boostrap config to use.
6. <jar/library pathname>: The .jar file containing the Java UDF or .txt file representing the C# libraries to load.
7. This file is usually found in prprivate/libext.
When invoking this utility, the database driver classes must be on the classpath.
What properties go into the config file?
In the config file, specify one or more named datasources. For each datasource, you must specify the following properties:
Property
Value
Example
db-type.jdbc.class
The database driver class for the datasource. db-type is the database type, and must be oracle, db2, or mssql.
com.microsoft.sqlserver.jdbc.SQLServerDriver
name.db-type.url
The JDBC URL of the database. name is the name of the datasource, and db-typeindicates the type of database (as described above).
jdbc://sqlserver://localhost;SelectMethod=cursor;SendStringParametersAsUnicode=false
name.db-type.username
The database user name.
name.db-type.password
The database password.
name.db-type.schema
The schema name to use when installing the jar or library into the database.
Example: On an Oracle database, the arguments and the config file might look something like this:
user-deployudf.properties (the config file)
oracledate.jdbc.class=oracle.jdbc.OracleDriver
dev.oracledate.url=jdbc:oracle:thin:@localhost:1521:ORCL
dev.oracledate.username=PegaUser
dev.oracledate.password=pega
dev.oracledate.schema=PegaUser
The DatabaseLibraryLoader.java arguments
--config=C:/Users/PegaUser/...{path}.../PegaUser-deployudf.properties
--profile=dev.oracledate
C:/Users/PegaUser/workspace/prprivate/libext/prreadstream.jar
Create the database functions
There are three functions you need to create after the jar library has been loaded. For each database, there are scripts that can be run to create these functions in the distribution image. They are under schema/db/install/udfstatements.
Some of these scripts contain the text #DBOWNR. If this appears in the script, then each occurrence must be manually replaced with the name of the schema into which the UDF is to be installed. Similarly, #UDFWLM (only on DB2 z/OS) should be replaced with the name of a Java-enabled Workload Manager.
Published April 29, 2016 - Updated October 8, 2020
Have a question? Get answers now.
Visit the Collaboration Center to ask questions, engage in discussions, share ideas, and help others.