Can you please provide some more details on "columns that are larger than the typical string length"?

How long (max) can be?
What's the data type for these columns in the external database?
What database are you connecting to? Using ODBC or JDBC?

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.

I wish I could see the code in $$macroERROR^%occMsgXML() (used by $$$ERROR() macro and other) to store the stack in a Status.

If the SetValueAt() method is not working I guess it's also returning an error, my suggestion is to check the returned error, so:

Set sc=tOBXSegment.SetValueAt(tOBXText, 5)
Do $system.OBJ.DisplayError(sc)

For training and certification you get a corresponding Credly badge that you can add/share in your Developer Community profile or your Linkedin and other social accounts.

When you take an InterSystems training course you get attendance badge, for example:

Building and Managing HL7 Integrations Training

Certification is separate from training, when you pass a certification exam (performed by a third party entity) you get the corresponding certification Credly badge, for example:

InterSystems HL7® Interface Specialist

I doubt it can be done with a generic process for any VDOC type.

For HL7 to get Virtual Property Path using names ("symbolic ones") you must know and use the "DocType" (that is the schema category and message structure) of the message.

In HL7, for a given HL7 message, it's not clear to me what you need/want as output, you want to visit all nodes/fields....defined? All possible nodes/fields defined in the schema structure? All nodes/fields that contains values?

If you need all possible nodes/fields for a given DocType, then you can get them from the Schema Definition.

It's difficult to tell if the bad value is in the passed parameter or in the returned value.

Just as curiosity, what's the value of pRequest.Warehouse and what's the datatype of Warehouse column in the prod.stocks table?

Hi @Aya Heshmat , please consider adding the host Pool Size in the Production Configuration page as it once was.

For details on this topic see my post/discussion:

Display Pool Size in Production Configuration page

and related Idea:

Add pool size in production configuration page

For us is REALLY important, at the moment (where we can) we have hacked/changed the Prod. Config. Portal page, in the future with the new modernized page this change may be difficult to implement/hack.

I came across this old question and I was (almost) shocked by the answers because we are considering using System Default Settings for a project and one of the settings value we need to customize per system is Pool Size!

Fortunately things have changed since this was posted an now Pool Size can be defined/configured in System Default Settings and this is also documented:

Settings That Can Act As System Overrides

I'm not sure when this has changed,I believe this has been implemented in 2024.1, the version I tested and it works.

The behavior of this default setting a a little different then others (cannot be modified/overridden from within the production settings) but, IMHO, that's fine or even better!

I'm writing this to warn anyone using a recent product version looking/reading this question that now, from 2024.1 Pool Size can indeed be configured in System Default Settings.

%ResultSet in deprecated, use %SQL.Statement instead.
Never concatenate parameters to query text, use placeholders ("?").

Set sql = "SELECT COUNT(*) FROM "_tableName_" WHERE "_fieldName_">=? AND "_fieldName_"<=?"Set resultset = ##class(%SQL.Statement).%ExecDirect(,sql,fromDate,currentDate)
Do tResult.%Display()

It the issue of this question/post the "?wsdl" not working or that the SOAP pass-through does not work?

To get "?wsdl" to work you need an HTTP pass-through, technically it's not a SOAP call.
In fact the "?wsdl" parameter is interpreted by IRIS and you get the "wsdl" returned by EnsLib.SOAP.GenericService. 

I created the production and tested the call to the SOAP service (I'm using 2024.1 but I don't thin is relevant) and it works.
I'm using SOAP UI to call the service through the IRIS production and I get the correct result.

Apart from the "?wsdl" call, have you tried calling the actual SOAP service?

For simple cases I found that often using a HTTP pass-through instead of SOAP pass-through makes thing easier.

While the suggested method should still work (haven't tested), I'd advise using the official method $system.SQL.Schema.ExportDDL()

See Class Reference for documentation details  and samples.

In these cases I use Ens.InboudAdapter with a very high call interval so it runs only once when the BS is enabled (START action in schedule definition)

To address similar cases some time ago I developed a little utility to export to a %DynamicArray the output from an SQL query.
In my case I had to export existing classes that did not extend %JSON.Adaptor (I'm not even sure %JSON.Adaptor existed at that time).
 

Class Community.SQL2JSON
{

/// Execute am SQL query and returns a %DynamicArray containing the rows (as %DynamocObject) returned by the query/// RetDynArray is the returned %DynamicArray containing the results/// Parameters used by the query must be included in the query using placeholders (?) e passed By Reference in the local variable array ParamArray/// where the root node contains the number of parameters, ie:/// ParamArray = 1/// ParamArray(1) = 123ClassMethod QueryToJSON(ByRef SQLQuery As%String, Output RetDynArray As%DynamicArray, ByRef ParamArray As%String) As%Status
{
	Set sc=$$$OKTry {
		Set stSql=##class(%SQL.Statement).%New()
		Set stSql.%ObjectSelectMode=0Set rsSql=##class(%SQL.Statement).%ExecDirect(.stSql, .SQLQuery,ParamArray...)
		If rsSql.%SQLCODE < 0 {
			Set sc = $$$ERROR($$$GeneralError,"%SQLCODE="_rsSql.%SQLCODE_", %Message="_rsSql.%Message)
			Quit
		}
		If '$IsObject($g(RetDynArray)) Set RetDynArray = []
		While rsSql.%Next(.sc) {
			If$$$ISERR(sc) QuitSet RowDynObj={}

			Set sc=..RowToDynObj(stSql,rsSql,.RowDynObj)
			If$$$ISERR(sc) QuitDo RetDynArray.%Push(RowDynObj)
		}
	} Catch CatchError {
		#dim CatchError as%Exception.SystemExceptionSet sc=CatchError.AsStatus()
	}
	Quit sc
}

/// Convert a recordset in a %DynamicObject with property name equal to the column name/// If RowDynObj is passed, then adds the properties are added to it, otherwise creates and returns a new dynamic objectClassMethod RowToDynObj(StSql As%SQL.Statement, RsSql As%SQL.StatementResult, ByRef RowDynObj As%DynamicObject) As%Status
{
	Set sc=$$$OKTry {
		If '$IsObject($g(RowDynObj)) Set RowDynObj= {}
		
		For col=1:1:StSql.%Metadata.columnCount {
			Set ColumnName=StSql.%Metadata.columns.GetAt(col).colName
			Set ColumnValue=$Property(RsSql,ColumnName)
			Do RowDynObj.%Set(ColumnName,ColumnValue)
		}
	} Catch CatchError {
		#dim CatchError as%Exception.SystemExceptionSet sc=CatchError.AsStatus()
	}
	Quit sc
}

}

Using it is very simple:

EPTEST>Set SQLQuery="select Name, DOB as ""Birth Date"", Home_City as City from Sample.Person where Home_City=?" 
EPTEST>Set ParamArray=1 
EPTEST>Set ParamArray(1)="Newton" 
EPTEST>Set sc=##class(Community.SQL2JSON).QueryToJSON(SQLQuery,.RetDynArray,.ParamArray) 
EPTEST>Do RetDynArray.%ToJSON()
[{"Name":"Uhles,Susan D.","Birth Date":31836,"City":"Newton"},{"Name":"Ubertini,Debby N.","Birth Date":42513,"City":"Newton"},{"Name":"Harrison,Rob E.","Birth Date":62265,"City":"Newton"},{"Name":"Adams,Robert E.","Birth Date":62769,"City":"Newton"}]
EPTEST>

Yes, it's normal, Web Applications are local configuration on each mirror member.

If/when you create a new Web Application you need to create it in all mirror members. Personally I prefer to create the application using a script (a class method) that is deployed and run (typically along with code etc.) in all members nodes.

In addition to Web Applications note that also all security related configuration (Users, Roles, Resources, SQL privileges etc.) are local to each mirror member.

It's my understanding that InterSystems is working on enhancing the mirror to include security configuration synchronization (Web Applications are part of the Security) between mirror member.

Configure a VIP for the mirror using built in IRIS VIP configuration, or, if you cannot use built in VIP, you can use an external balancer (F5, HAProxy etc.) to handle/create the VIP "externally".

Then instead of using the IP/name of the mirror members you use the VIP address/name to connect to the active mirror member node. Use VIP for ODBC and any other connection to IRIS.

You did not specified what kind of HealthShare environment you have in place (UCR?), depending of your HealthShare setup a VIP is a prerequisite for configuring an HealthShare  federation.