Forum Discussion

wwilbur's avatar
wwilbur
Occasional Contributor
3 years ago

java.sql.DataTruncation: Data truncation error

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

 

 

 

  • 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 

  • richie's avatar
    richie
    Community Hero

    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's avatar
      sonya_m
      SmartBear Alumni (Retired)

      Great suggestion, Richie!

       

      wwilbur does this help you solve the issue?

  • wwilbur's avatar
    wwilbur
    Occasional Contributor

    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's avatar
      richie
      Community Hero

      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