Question
· Mar 26

"Bad value for type long" on select from PostgreSQL via EnsLib.SQL.OutboundAdapter

Here is my code:

Method getStocks(pRequest As Stock.Message.Req, Output pResponse As Ens.StreamContainer) As %Status
{
     s tSC = pRequest.NewResponse(.pResponse)
     q:$$$ISERR(tSC) tSC

     #dim pRS As EnsLib.SQL.GatewayResultSet

     s tSC = ..Adapter.ExecuteQuery(.pRS, "select jsonb_agg(s) #>> '{}' FROM prod.stocks s where s.""Warehouse"" = ?", pRequest.Warehouse)
     q:$$$ISERR(tSC) tSC

     s pResponse = ##class(Ens.StreamContainer).%New()
     s pResponse.Stream = ##class(%GlobalCharacterStream).%New()

     i pRS.Next() {
          d pResponse.Stream.CopyFrom(pRS.GetDataStream(1))
     else {
          s tSC = $$$ERROR($$$GeneralError"ResultSet is empty")
     }

     q tSC
}

I created BO with EnsLib.SQL.OutboundAdapter, created DSN and seated it in operation settings. I am using a postgresql-42.7.4 JDBC driver. When I ran it, I got an empty record set error in trace and next record in Java Gateway log:

Sent: (10:12:08:696) [Job number = -1] [ThreadID = 10]
  0000:  4A  13  7E  00  00  00  00  00  00  00  00  00  59  4D      J.~.........YM
  000E:  00  00  00  43  13  7E  00  02  52  00  65  00  6D  00      ...C.~..R.e.m.
  001C:  6F  00  74  00  65  00  20  00  4A  00  44  00  42  00      o.t.e. .J.D.B.
  002A:  43  00  20  00  65  00  72  00  72  00  6F  00  72  00      C. .e.r.r.o.r.
  0038:  3A  00  20  00  42  00  61  00  64  00  20  00  76  00      :. .B.a.d. .v.
  0046:  61  00  6C  00  75  00  65  00  20  00  66  00  6F  00      a.l.u.e. .f.o.
  0054:  72  00  20  00  74  00  79  00  70  00  65  00  20  00      r. .t.y.p.e. .
  0062:  6C  00  6F  00  6E  00  67  00  20  00  3A  00  20  00      l.o.n.g. .:. .

[Update 1] The problem is: Why does Java Gateway try to read a text (varchar) type field as a long type?

[Update 2] The reason is not in a big text value of the selected field. I tried to limit records for json and got the same error

Product version: IRIS 2019.1
Discussion (3)2
Log in or sign up to continue

I'm sure the problem is in a selected field. The warehouse has a varchar type on the database side and %String on the IRIS side. It's just a warehouse ID. I need to select a big JSON string as one field, one record.

I could go with this problem to the database team, but in external SQL console with the same driver (I used SQuirreL SQL) all works fine.

My main hypothesis is that there is some error on the Java Gateway level. But I don't understand what the Long type value means here. Long, it's a number, right? Selected field have type text.