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 (5)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