java.sql.DataTruncation: Data truncation error

[wwilbur]

I am able to update and insert into a DB2 schema and table using DBeaver but when I try to do these in ReadyAPI I get the error message java.sql.DataTruncation: Data truncation error

Here is the groovy script code for insert:

import groovy.sql.Sql
import com.eviware.soapui.support.GroovyUtilsPro;
def groovyUtilsPro = new GroovyUtilsPro(context)
//def sql = groovyUtilsPro.getGroovySql('sysdev2')
def storeNumber = context.expand( '${getDataFromDWDataDWSMast#storeNumber}' )
def lineCode = context.expand( '${getDataFromDWDataDWSMast#lineCode}' )
def itemNumber = context.expand( '${getDataFromDWDataDWSMast#itemNumber}' )
def dSAC = context.expand( '${#TestCase#dSAC}' )
def qOH = context.expand( '${getDataFromDWDataDWSMast#QOH}' )
def qOO = context.expand( '${getDataFromDWDataDWSMast#QOO}' )
def dSQBO = context.expand( '${getDataFromDWDataDWSMast#DSQBO}' )
def mAX = context.expand( '${getDataFromDWDataDWSMast#MAX}' )
def mIN = context.expand( '${getDataFromDWDataDWSMast#MIN}' )
def sPSUPL = context.expand( '${getDataFromDWDataDWSMast#SPSUPL}' )
def dSSWSQ = context.expand( '${getDataFromDWDataDWSMast#DSSWSQ}' )
def dSDOLT = context.expand( '${getDataFromDWDataDWSMast#DSDOLT}' )
def dSLOAD = context.expand( '${getDataFromDWDataDWSMast#DSLOAD}' )
def dSALCD = context.expand( '${#TestCase#dSALCD}' )
def dSUSER = context.expand( '${getDataFromDWDataDWSMast#DSUSER}' )
def dSDEAL = context.expand( '${getDataFromDWDataDWSMast#DSDEAL}' )
def dSCORE = context.expand( '${getDataFromDWDataDWSMast#DSCORE}' )

def line = ("'" + lineCode + "'")
def item = ("'" + itemNumber + "'")

Map dbConnParams = [
url: 'jdbc:as400://sysdev2.oreillyauto.com;libraries=OPPDATA',
user: 'xxxxxxx',
password: 'xxxxxx',
driver: 'com.ibm.as400.access.AS400JDBCDriver']

def sql = Sql.newInstance(dbConnParams)

def result = sql.executeInsert ("INSERT INTO DWDATA.DWSMAST (STR#, LINE, ITEM#, DSAC, QOH, QOO, DSQBO, MAX, MIN, SPSUPL, DSSWSQ, DSDOLT, DSLOAD, DSALCD, DSUSER, DSDEAL, DSCORE) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)", [storeNumber, line, item, dSAC, qOH, qOO, dSQBO, mAX, mIN, sPSUPL, dSSWSQ, dSDOLT, dSLOAD, dSALCD, dSUSER, dSDEAL, dSCORE])

sql.close()

and here is for update:

import groovy.sql.Sql
import com.eviware.soapui.support.GroovyUtilsPro;
def groovyUtilsPro = new GroovyUtilsPro(context)
def sql = groovyUtilsPro.getGroovySql('sysdev2')

String storeNumber = context.expand( '${getRandomStoreNumberCustomerName#storeNumber}' )
String lineCode = context.expand( '${getPart#lineCode}' )
String itemNumber = context.expand( '${getPart#itemNumber}' )
def line = ("'" + lineCode + "'")
def item = ("'" + itemNumber + "'")

Map dbConnParams = [
url: 'jdbc:as400://sysdev2.oreillyauto.com;libraries=OPPDATA',
user: 'xxx',
password: 'xxxx',
driver: 'com.ibm.as400.access.AS400JDBCDriver']

def con = Sql.newInstance(dbConnParams)

def result = sql.execute("UPDATE OPPDATA.DWSMAST SET QOH = 10, MAX = 50 WHERE STR# = ${storeNumber} AND LINE = ${line} AND ITEM# = ${item}")

sql.close()

[richie]

Hey @wwilbur,

The data truncation error's pretty standard for DB2.  If I were getting the error in ReadyAPI I'd double check all the propertyexpansions for any erroneous space chars on the ends of the sourced values.  If you're confident the values themselves are < the column's length, then the cause of the issue's gotta be something like invisible control/space characters or similar

Cheers,

Rich

[sonya_m]

Great suggestion, Richie!

@wwilbur does this help you solve the issue?

[wwilbur]

If I hard code the values I am able to update and insert into the table but if I try to use parameters it gives me the data truncation error. I verified the data in the parameters was the correct length or shorter.

[richie · Accepted solution]

Hey @wwilbur 

In that case there's some problem with the propertyexpansion I'm guessing.

Only thing I can suggest to diagnose is try doing the simplest INSERT and repeatedly swap out expanded values across the different table attributes to identify the problematic attribute value (I'm assuming here that the problem isn't EVERY single attribute).

So - I'd do the following:

Step1: hardcode all the values excepting 1 attribute (where you expand), run the steps and check the results

Step2: move to the next attribute value and repeat Step1 process

Step3: continue until you've covered off all the attributes

Cheers,

Rich