Question
· Mar 12

SQL Query not transforming into class structure correctly

My query that I am running on my Custom SQL Inbound Service has columns that are larger than the typical string length. How do I enlarge the SQL Snapshot Column limitations 

Class osuwmc.Epic.Clarity.DepartmentMaster Extends %Persistent [ DdlAllowed ]
{
Parameter USEEXTENTSET = 1;
Property CostCenter As %String(MAXLEN = 15) [ SqlColumnNumber = 2 ];
Property ID As %String [ Required, SqlColumnNumber = 3 ];
Index IDIndex On ID;
Property Abbr As %String(MAXLEN = 20) [ SqlColumnNumber = 4 ];
Property Name As %String(MAXLEN = 254) [ SqlColumnNumber = 5 ];
Property ExternalName As %String(MAXLEN = 254) [ SqlColumnNumber = 6 ];
Property PhoneNumber As %String(MAXLEN = 254) [ SqlColumnNumber = 7 ];
Property ApptPhone As %String(MAXLEN = 20) [ SqlColumnNumber = 8 ];
Property FaxNumber As %String(MAXLEN = 20) [ SqlColumnNumber = 9 ];
Property Address1 As %String(MAXLEN = 254) [ SqlColumnNumber = 10 ];
Property Address2 As %String(MAXLEN = 254) [ SqlColumnNumber = 11 ];
Property City As %String(MAXLEN = 254) [ SqlColumnNumber = 12 ];
Property Zip As %String(MAXLEN = 254) [ SqlColumnNumber = 13 ];
Property Specialty As %String(MAXLEN = 50) [ SqlColumnNumber = 14 ];
Property RevLocID As %String [ SqlColumnNumber = 15 ];
Property RevLocName As %String(MAXLEN = 200) [ SqlColumnNumber = 16 ];
Property BuildingCategoryID As %String(MAXLEN = 66) [ SqlColumnNumber = 17 ];
Property BuildingName As %String(MAXLEN = 254) [ SqlColumnNumber = 18 ];
Property DepCategoryTypeID As %String(MAXLEN = 66) [ SqlColumnNumber = 19 ];
Property DepType As %String(MAXLEN = 254) [ SqlColumnNumber = 20 ];
Property Center As %String(MAXLEN = 254) [ SqlColumnNumber = 21 ];
Index BitmapExtent [ Extent, Type = bitmap .....
Class osuwmc.Epic.Clarity.SelectEpicClarityDepartment Extends Ens.BusinessService [ ClassType = "", ProcedureBlock ]
{
Parameter ADAPTER = "EnsLib.SQL.InboundAdapter";
Parameter REQUESTCLASSES As %String = "EnsLib.SQL.Snapshot";
Property InitDSN As %String;
Method OnInit() As %Status
{
  Set ..InitDSN = ..Adapter.DSN
  //Set ..Adapter.ConnectAttrs = "QueryTimeout:45" ; try this too just in case...
  set pInput = ##class(EnsLib.SQL.Snapshot).%New()
  set pInput.MaxRowsToGet = -1
  set tSC = rs.Insert(pInput)
  Quit $$$OK
}

Method OnProcessInput(pInput As EnsLib.SQL.Snapshot, pOutput As %RegisteredObject) As %Status
{
  set req=##class(osuwmc.Epic.Clarity.DepartmentMaster).%New()
  set req.CostCenter = pInput.Get("CostCenter")
  set req.ID = pInput.Get("ID")
  set req.Abbr = pInput.Get("Abbr")
  set req.Name = pInput.Get("Name")
  set req.ExternalName = pInput.Get("ExternalName")
  set req.PhoneNumber = pInput.Get("PhoneNumber")
  set req.ApptPhone = pInput.Get("ApptPhone")
  set req.FaxNumber = pInput.Get("FaxNumber")
  set req.Address1 = pInput.Get("Address1")
  set req.Address2 = pInput.Get("Address2")
  set req.City = pInput.Get("City")
  set req.Zip = pInput.Get("Zip")
  set req.Specialty = pInput.Get("Specialty")
  set req.RevLocID = pInput.Get("RevLocID")
  set req.RevLocName= pInput.Get("RevLocName")
  set req.BuildingCategoryID = pInput.Get("BuildingCategoryID")
  set req.BuildingName = pInput.Get("BuildingName")
  set req.DepCategoryTypeID = pInput.Get("DepCategoryTypeID")
  set req.DepType = pInput.Get("DepType")
  set req.Center = pInput.Get("Center")
  set status = req.%Save()
  IF $$$ISERR(status){
    Write "Error saving data: ", status, !
    }
    Else {
        Write "Data inserted successfully!", !
    }
    quit status
}
}

For example.... ExternalName is not getting populated correctly and is NULL when I look at the table in SQL.

Product version: IRIS 2024.1
$ZV: IRIS for UNIX (Red Hat Enterprise Linux 8 for x86-64) 2024.1 (Build 267_2U) Tue Apr 30 2024 16:06:39 EDT [HealthConnect:7.2.0-1.r1]
Discussion (4)3
Log in or sign up to continue

Hi Scott,

I don't consider 255 as larger than the typical string length and I'm surprised of your issue and I don't fully understand your code, probably because it's not complete (set tSC = rs.Insert(pInput) make no sense to me).

Anyway, my suggestion is to find some more info that may give you some hint.

For example I'd add the following lines in your OnProcessInput() method:

Set colId=pInput.GetColumnID("ExternalName")
$$$LOGINFO("ColumnType is "_pInput.GetColumnType(colId))
$$$LOGINFO("ColumnSQLType is "_pInput.GetColumnSQLType(colId))
$$$LOGINFO("ColumnSize is "_pInput.GetColumnSize(colId))

Please test this using a query that extract a few records to avoid flooding your event log.

I'm curious to see what you get.