New post
AllAccepted answers
Vitaliy Serdtsev · Aug 11, 2023

One of the possible options:

ClassMethod odbcTest() As %Integer [ ReturnResultsets, SqlName = PersonSets2, SqlProc ]
{
  #dim %sqlcontext As %ProcedureContext
  if '$isobject($Get(%sqlcontext)) { set %sqlcontext = ##class(%ProcedureContext).%New() } 

  s tReturn = 0

  s conn=##class(%SQLGatewayConnection).%New()
  s sc=conn.Connect("TEST Samples","_system","SYS") //datasource
  if $$$ISOK(sc) {
    d conn.AllocateStatement(.h1)
    d conn.Prepare(h1,"select name,dob,spouse from sample.person where name %STARTSWITH 'A'")
    d conn.Execute(h1)
    d %sqlcontext.AddResultSet(conn.getResultSet(h1))
    d conn.AllocateStatement(.h2)
    d conn.Prepare(h2,"select name,age,home_city,home_state from sample.person where home_state = 'MA'")
    d conn.Execute(h2)
    d %sqlcontext.AddResultSet(conn.getResultSet(h2))
    s tReturn = 1
  }else{
    s sqlcode=$system.Status.StatusToSQLCODE(sc,.msg)
    s %sqlcontext.%SQLCODE = sqlcode, %sqlcontext.%Message = msg
  }
  q tReturn
}

Output:

SAMPLES>d ##class(%SQL.Statement).%ExecDirect(,"call Sample.PersonSets2()").%Display()
...

Surely there is a way to make it even easier.

Vitaliy Serdtsev · Aug 11, 2023

It's clearer now, this is the tricks of Excel, which convert the string 01022000 to the number 1022000.
In your case, try changing the cell type from General to Text.

PS: when generating Excel reports, I use the Date cell type (short format DD.MM.YYYY), and from Caché/IRIS I return the %Date type.
The components that I use already know how to translate the internal representation of the date 01.02.2000 from the Caché/IRIS format (58105) to Excel (36557) and vice versa.

Vitaliy Serdtsev · Aug 1, 2023

Thanks for the comment. I tested only on the provided data. The second option requires improvement.

 
size = 72
 
size = 69
Vitaliy Serdtsev · Jul 19, 2023 
Query FilterBy(
  Name As %String = "",
  Age As %Integer = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_Filter_By, SqlProc ]
{
SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(:Name IS NULL or Name %STARTSWITH :Name)
AND
(:Age IS NULL or Age >= :Age)
}
This kind of query ends up preventing Caché SQL compiler from optimizing using index based on each of the criteria made optional.

What is your conclusion based on?
If you check the plans of this query for different combinations of parameters, then the corresponding indexes are used (it is assumed that the table was previously configured via TuneTable).

select * from Sample.SP_Sample_Filter_By('s')


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(NULL IS NULL or Age >= NULL)
select * from Sample.SP_Sample_Filter_By(,47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
(NULL IS NULL or Name %STARTSWITH NULL)
AND
(47 IS NULL or Age >= 47)
select * from Sample.SP_Sample_Filter_By('s',47)


SELECT TOP 5 ID, Name, Age, SSN FROM %ALLINDEX Sample.Person WHERE 
('s' IS NULL or Name %STARTSWITH 's')
AND
(47 IS NULL or Age >= 47)
Vitaliy Serdtsev · Jul 19, 2023

Did a little test:

Class dc.test [ Abstract ]
{

ClassMethod GetObjectFieldValue(
  pObject As %DynamicAbstractObject,
  pField As %String) As %String
{
  quit:pObject="" ""
  quit:pField="" "" set tResponse=""
  set tResponse = pObject.%Get(pField)
  return tResponse
}

ClassMethod Test()
{
  s p1={
    "priority": "2",
    "lastmodifieduser": "PORTAL",
    "assignedto": "jdoe01 STAFF",
    "status": "REVIEW",
    "documentclass": "PATIENTCASE",
    "versiontoken": "937486",
    "departmentid": "999",
    "patientcaseid": "105555424",
    "providerusername": "jdoe01",
    "internalnote": "firstMessage",
    "subject": "first123",
    "patientid": 9877654321,
    "createduser": "PORTAL",
    "description": "patient case - first123",
    "documentroute": "MDP",
    "documentsubclass": "PATIENTCASE_CLINICALQUESTION",
    "documentsource": "PORTAL",
    "createddate": "07/17/2023",
    "lastmodifieddate": "07/17/2023"}
    
  s p2="documentsubclass"
  w ..GetObjectFieldValue(p1,p2)
}

}

Result:

USER>d ##class(dc.test).Test()
PATIENTCASE_CLINICALQUESTION
Vitaliy Serdtsev · Mar 31, 2023

I wrote a small test on VBS, but you can easily redo this code for your language. For details on working with streams for ADO, see the documentation.

 
Code on Visual Basic Script
Vitaliy Serdtsev · Mar 9, 2023

In my opinion, the documentation clearly explains the characteristics of such a property and its purpose, for example:

  • To specify that this property has the characteristics of a multidimensional array
  • That is, the property is also automatically Transient.
  • Multidimensional properties are rare but provide a useful way to temporarily contain information about the state of an object.