Question
· Aug 16, 2018

MAXLEN question

Let's say I have something like this:

Query ClientList(BusinessType As %String) As %SQLQuery (CONTAINID = 1, ROWSPEC = "Name:%String(MAXLEN=100),AltName:%String(MAXLEN=100)") [ SqlProc ]

{...}

Is there a way I can specify the MAXLEN by a function or a calculated method?

Like maybe:

MAXLEN=#(..GetLengthName())#

Or is this something that has to be hardcoded in Studio?

Discussion (6)1
Log in or sign up to continue

Besides dynamically changing the maxlen what else are you trying to accomplish/avoid? These values are used at compile time to generate the SQL catalog and become part of your contract (for clients calling them). If you were to dynamically change the values 1) you'll be breaking the contract (e.g. you could change the values to a more restrictive size) and 2) you would still need to compile the class holding the stored proc/query. 

The benefit I can think of this approach right now is avoiding pushing code to prod (assuming that process is cumbersome), however you still need the compile part.  It'll be better to define and modify the stored proc using DDLs (maybe  a faster change in prod depending your company's rules).  Since this change is a database object change it should be treated as code and thus (hopefully) kept in source control (for audit and replication purposes). 

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.