New post
Question
· Jul 22, 2024

SQL Gateway getClob() errors in Redshift and Postgresql

#SQL #Health Connect #InterSystems IRIS for Health

I'm trying to use the EnsLib.SQL.Operation.GenericOperation component in a production to read a column from a Redshift table that is set up as VARCHAR(65535) and am getting the following error.  

An error was received : ERROR #5023: Remote Gateway Error: JDBC Gateway getClob(0,1) errorRemote JDBC error: Cannot convert the column of type VARCHAR to requested type long..

The query I'm using is a simple 'SELECT column_name FROM table_name'.  I've done a little research and it sounds like Redshift doesn't support getClob().  Is there anything I can do to force the gateway to not use getClob() on this Redshift column, or some other work around?  Casting the column in my select statement doesn't work... it seems the getClob() call is done under the hood by the gateway, and I would potentially need some way to override that?  

Thanks,

Don Martin, Sanford Health

Product version: IRIS 2023.3
Discussion (5)3
Log in or sign up to continue
Don Martin · Aug 1, 2024

The source of this issue lies in a property setting in the EnsLib.SQL.CommonJ class, which gets extended by the EnsLib.SQL.OutboundAdapter.

Class EnsLib.SQL.CommonJ Extends EnsLib.JavaGateway.Common [ Abstract, ClassType = "", ProcedureBlock, System = 4 ]
{
.
.
.

/// When greater than this length of characters a VARCHAR column is
/// to be treated as a LOB and stored in a stream. <br>
/// The default is 32767. <br>
/// Use -1 to use the system maximum string length of 3641144. <br>
/// Note the system maximum string length may change in future versions
/// and therefore the value used when -1 is selected can change. <br>
/// If the value entered is greater than the maximum system string length
/// then the maximum system string length will be used. <br><br>
/// This setting can be used for example when 'text' columns on the 
/// target system cannot be retrieved as LOBs and it is necessary to 
/// CAST the column as a VARCHAR longer than 32767. <br> 
Property MaxVarCharLengthAsString As %Integer [ InitialExpression = 32767 ];
ObjectScript
ObjectScript

If the column you are pulling data from is larger than varchar(32767),  the data will be treated as a LOB.

There are a couple of workarounds.  

If you are working with a database column configured to be larger than varchar(32767), but you're confident the data length in your query result for that column will be less than 32767, you can cast the column in your select statement as follows:

select cast(column_name as varchar(32767)) from table;

A more robust solution is to modify the MaxVarCharLengthAsString property in the Adapter.  The following worked for me - I was able to pull a varchar(65535) column from Redshift without casting.

Class Example.Redshift.MyGenericOperation Extends EnsLib.SQL.Operation.GenericOperation
{

Method OnMessage(pRequest As Ens.Request, Output pResponse As Ens.Response) As %Status
{
    set ..Adapter.MaxVarCharLengthAsString = -1
    set tSC = ##class(EnsLib.SQL.Operation.GenericOperation)$this.OnMessage(pRequest,.pResponse)
    return tSC
}

}
ObjectScript
ObjectScript

Fair warning - I'm new to Intersystems, so I don't know if there are better ways to do this or if there are any hidden problems with what I proposed.  Input from anyone who has suggestions to improve this approach would be appreciated!

0 0