Hi, I hope this post helps.
The bottom line: MAXLEN is relevant mostly for odbc/jdbc connections and you need to specify an appropriate value within your tables (classes), otherwise the data might be truncated when you query it, or even fail when you try to insert data.
Long story:
the sql GUI in the portal is very lenient in reference to the MAXLEN , for example you can insert data into a table where there is data longer then the size of a column, if you're using fhir sql the columns in the tables are mostly MAXLEN =50 even if there is much larger data, additionally if you create a table from a select (create as select ) the created table will have MAXLEN=50 the data will be complete. however if you try to insert values larger then 50 through ODBC/JDBC it will fail.
so pay attention to the columns / parameters size in the class itself (not in the sql gui)
hth, Eyal
Check your TRUNCATE parameter.
Class dc.test Extends %Persistent { Property s1 As %String(TRUNCATE = 1); Property s2 As %String; ClassMethod Test() { d ..%KillExtent(,$$$YES) s t=..%New() s t.s1=$tr($j("",51)," ","a") s sc=t.%Save() w "s1: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),! s t=..%New() s t.s2=$tr($j("",51)," ","a") s sc=t.%Save() w "s2: ",$s($$$ISERR(sc):$system.Status.GetErrorText(sc),1:"OK"),! } } USER>d ##class(dc.test).Test() s1: OK s2: ERROR #7201: Datatype value 'aaaa..aa' length longer than MAXLEN allowed of 50 > ERROR #5802: Datatype validation failed on property 'dc.test:s2', with value equal to "aaaa..aa"The same error occurs in the Management Portal.
Hi Vitaliy,
I'm not using the TRUNCATE parameter, the class is created automatically with the create table.
I'm trying to raise an awareness to the different behavior between the JDBC/ODBC and the SQL GUI.
I do not observe any differences in the behavior of xDBC and Portal.
Given:
Class dc.test Extends %Persistent { Property s1 As %String(MAXLEN = 2, TRUNCATE = 1); Property s2 As %String(MAXLEN = 2); }insert into dc.test(s1,s2)values('abc','ab')insert into dc.test(s1,s2)values('abc','abc')Result for any JDBC/ODBC tools (DBVisualizer, SQL Data Lens, etc.):
Result for Management Portal:
Its limited based on column type as described in the documentation for 2019.1 strings and streams are limited to 100 characters
https://docs.intersystems.com/healthconnect20191/csp/docbook/DocBook.UI....
for 2024 strings are fully shown but streams are still limited to 100 characters
https://docs.intersystems.com/healthconnect20241/csp/docbook/DocBook.UI....
you can generater sql by classdefinition, to get parameter maxlen value such as :
s maxlen=
s truncate=
i (truncate=1) && ($l(originalVal)>maxlen){
s originalVal =$e(originalVal,1,maxlen)
}
Maybe it can help you!
I also noticed a difference : when using the ODBC driver to get data into Microsoft Access, MAXLEN determines the length of a text field.
This is also true when connecting to a view. Moreover, when you use a stored procedure as one of the fields in the view, the result is limited to 50 characters in case MAXLEN is not specified.
For example, if I define a method like this :
ClassMethod SomeStringPrep(plnput1 As %String, pInput2 As %String) As %String [ SqlProc ]the output will be truncated to 50 characters (at the ODBC client, not in the Caché portal). Resolve this restriction by specifying MAXLEN :
ClassMethod SomeStringPrep(plnput1 As %String, pInput2 As %String) As %String(MAXLEN=1000) [ SqlProc ]Thanks Marc,
that was exactly what I was trying to say, you just explained it much better now