New post
Article
· Apr 9 1m read

ODBC / JDBC data truncation

#JDBC #ODBC #InterSystems IRIS

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

Discussion (7)3
Log in or sign up to continue
Vitaliy Serdtsev · Apr 10

Check your TRUNCATE parameter.

Common Parameters:TRUNCATE
TRUNCATE — Specifies whether to truncate the string to MAXLEN characters, where 1 is TRUE and 0 is FALSE. This parameter is used by the Normalize() and IsValid() methods but is not used by database drivers.

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.

0 0
Vitaliy Serdtsev · Apr 11

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')
    Result
    ID s1 s2
    1 ab ab 
  • insert into dc.test(s1,s2)values('abc','abc')

    Result for any JDBC/ODBC tools (DBVisualizer, SQL Data Lens, etc.):

    [SQLCODE: <-104>:<Field validation failed in INSERT>]
<Field 'dc.test.s2' Truncation (Varchar Value: 'abc ...' Length: 3) > maxlen:  (2)>]

    Result for Management Portal:

    [SQLCODE: <-104>:<Field validation failed in INSERT>]

  [%msg: <Field 'dc.test.s2' (value 'abc') failed validation>]
0 0
Marc Umans · May 1

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 ]
1 0