MAXLEN question

What is the reaction you expect here?
- truncate?
- throw an exception?

Do you want to precalculate length at runtime
- for all of the results
- per row
- per column
- only valid for the caller(i.e. each call has different length limitiations depending on context) ?

you sure could do some nasty things at runtime

You could pass an extra parameter that has a list of length elements for each column like
Query ClientList(pLengthList as %List = { $lb(10,10,50) })

SELECT SUBSTRING(f1,1,$listget(pLengthList,1,50)) as f1,
...

A simple sample could look like this

/// dynamic length restriction
Class User.DynaPar
{

ClassMethod RunTest(pMax As %Integer = 5)
{
#dim tSQL as %String
#dim tIdx as %Integer
#dim tRS as %SQL.StatementResult
set tSQL = "SELECT * FROM myQuery()"
for tIdx = 1 : 1 : 10
{
set tRS = ##class(%SQL.Statement).%ExecDirect(,tSQL)
if tRS.%Next()
{
write !,$j(tIdx,3),": "_tRS.%GetData(1)
}
}
}

Query myQuery(pListOfLengths As %List = {..GetLengths()}) As %SQLQuery [ SqlName = MyQuery, SqlProc ]
{
SELECT TOP 1 SUBSTRING('abcdef',1,$LISTGET(:pListOfLengths,1,1))
}

ClassMethod GetLengths() As %List
{
return $lb(1+$random(6))
}

}

Does that do what you want?

Roberto, Am I correct that you are having the common problem of SQL queries can return multiple columns that are too wide to display/print? A solution is to truncate the output widths without changing the class/table definition or the stored data. For example, if this is too wide: SELECT ID, Name, AltName, Address... FROM table WHERE BusinessType = ? there are solutions, depending on how/where the SQL is entered. If you are entering such queries manually, you can simply truncate, as with: SELECT ID, SUBSTR(Name,30), SUBSTR(AltName,25), Address... FROM table WHERE BusinessType = ? If this is a class query, you can change it to take these MAXLEN parameters. For example, in our SAMPLES namespace, class Sample.Person includes: Query ByName(name As %String = "") As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_By_Name, SqlProc ] { SELECT ID, Name, DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } You could create a similar Query that accepts truncate values, like: Query ByNameT(name As %String = "", nameLen As %Integer = 50) As %SQLQuery(CONTAINID = 1, SELECTMODE = "RUNTIME") [ SqlName = SP_Sample_By_NameT, SqlProc ] { SELECT ID, SUBSTR(Name,1,:nameLen), DOB, SSN FROM Sample.Person WHERE (Name %STARTSWITH :name) ORDER BY Name } Then users can run such SQL queries like: SELECT * FROM Sample.SP_Sample_By_NameT('A',10) and see truncated column data. Dynamic SQL (%SQL.Statement) provides similar functionality.