Link to home
Start Free TrialLog in
Avatar of Prashant Vasha
Prashant VashaFlag for India

asked on

DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: name of the database field

I have added a new column in the database table and am trying to insert a record into the table using my application. The application throws the following exception:
com.ibm.db2.jcc.a.SqlException: DB2 SQL error: SQLCODE: -206, SQLSTATE: 42703, SQLERRMC: VERSANDART
VERSANDART is the name of the new column.
We I insert the records into the table from the SQL Prompt, the record is inserted into the database without any problems.
I am using a PreparedStatement for inserting the values into the database.
Am I missing something?

Best Regards,

Prashant
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Please post your code
Avatar of Prashant Vasha

ASKER

The database field type is shortint. The following is the code snippet that I am using
    String stmt = "INSERT INTO direct.VNIDENT (" 
    +"VNID,"              
    +"ANREDEID,"          
    +"VORNAME,"           
    +"NAME,"              
    +"STRASSE,"           
    +"WOHNORT,"           
    +"POSTLEITZAHL,"      
    +"LANDID,"             
    +"TELEFONABENDS,"     
    +"TELEFONUNTERTAGS,"  
    +"EMAIL,"             
    +"ERREICHBARKEIT,"    
    +"EMAILSERWUENSCHT,"  
    +"GEBURTSDATUM,"      
    +"BERUF,"             
    +"KORRSPRACHEID,"     
    +"NATIONALITAETID,"   
    +"ZIVILSTANDID"
    +",VERSANDART"
    +") VALUES (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
 
    System.out.println("stmt: \r\n" + stmt);
	myVNID = getNextVNId();
	vn.setVnId(myVNID);
 
    PreparedStatement prepStmt = null;
    
    try {
        prepStmt = conn.prepareStatement(stmt);
 
        if (vn.getVnId() < -1)
        {
            prepStmt.setNull(1,Types.DECIMAL);
            logger.error("getInsertStmtVersicherungsnehmerTabVnident() - Konnte keine VnId einfügen");
        }
        else
        {
            prepStmt.setLong(1,vn.getVnId());
        }
 
        if (vn.getAnrede().getAnredeId() == null )
        {
            prepStmt.setNull(2,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(2,Formatter.checkString(vn.getAnrede().getAnredeId(), 2));
        }
 
        if (vn.getVorname() == null )
        {
            prepStmt.setNull(3,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(3,Formatter.checkString(vn.getVorname(), 25));
        }
 
        if (vn.getName() == null )
        {
            prepStmt.setNull(4,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(4,Formatter.checkString(vn.getName(), 25));
        }
 
        if (vn.getAdresse().getAdresse() == null )
        {
            prepStmt.setNull(5,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(5,Formatter.checkString(vn.getAdresse().getAdresse(), 25));
        }
 
        if (vn.getAdresse().getOrt() == null )
        {
            prepStmt.setNull(6,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(6,Formatter.checkString(vn.getAdresse().getOrt(), 25));
        }
 
        if (vn.getAdresse().getPlz() == null )
        {
            prepStmt.setNull(7,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(7,Formatter.checkString(vn.getAdresse().getPlz(), 4));
        }
 
        if (vn.getAdresse().getLand() == null )
        {
            prepStmt.setNull(8,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(8,Formatter.checkString(vn.getAdresse().getLand(), 3));
        }
 
        if (vn.getKontaktInfo().getTelAbend() == null )
        {
            prepStmt.setNull(9,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(9,Formatter.checkString(vn.getKontaktInfo().getTelAbend(), 30));
        }
 
        if (vn.getKontaktInfo().getTelTag() == null )
        {
            prepStmt.setNull(10,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(10,Formatter.checkString(vn.getKontaktInfo().getTelTag(), 30));
        }
 
        if (vn.getKontaktInfo().getEmail() == null )
        {
            prepStmt.setNull(11,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(11,Formatter.checkString(vn.getKontaktInfo().getEmail(), 100));
        }
 
        // Erreichbarkeit wird immer mit NULL gespeichert
        prepStmt.setNull(12,Types.VARCHAR);
 
        if (vn.getKontaktInfo().hasNewsLetter() == true)
        {
            prepStmt.setInt(13,1);
        }
        else
        {
            prepStmt.setInt(13,0);
        }
 
        if (vn.getGeburtsdatum() == null )
        {
            prepStmt.setNull(14,Types.DATE);
        }
        else 
        {
            prepStmt.setDate(14,java.sql.Date.valueOf(Formatter.dateToString_DB2Date(vn.getGeburtsdatum())));
        }
        
        if (vn.getBeruf() == null )
        {
            prepStmt.setNull(15,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(15,Formatter.checkString(vn.getBeruf(), 25));
        }
 
        if (mPolice.getKorrespondenzSprache() == null )
        {
            prepStmt.setNull(16,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(16,Formatter.checkString(mPolice.getKorrespondenzSprache(), 2));
        }
 
        if (vn.getNationalitaetId() == null )
        {
            prepStmt.setNull(17,Types.VARCHAR);
        }
        else 
        {
            prepStmt.setString(17,Formatter.checkString(vn.getNationalitaetId(), 2));
        }
 
        // 11.7.2007 - Abfrage ob Zivilstand null ist, da wir sonst eine Exception erhalten.
        if(vn.getZivilstand() == null){
            prepStmt.setNull(18,Types.VARCHAR);
        } else {
            if (vn.getZivilstand().getZivilstandId() == null )
            {
                prepStmt.setNull(18,Types.VARCHAR);
            }
            else 
            {
                prepStmt.setString(18,Formatter.checkString(vn.getZivilstand().getZivilstandId(), 1));
            }
        }
                
        // X006369 : CR 894 Zustellung des Antrags
        if (getVersandArt(mPolice) < 2){
            
            // ------------------ Code to be removed --------------------
            System.out.println("---------------------------------------------------------------------");
            System.out.println("VersandArt:"+getVersandArt(mPolice));
            System.out.println("---------------------------------------------------------------------");
            // ------------------ till here -----------------------------
            
            prepStmt.setInt(19,getVersandArt(mPolice));
        }  else {
            prepStmt.setNull(19, Types.SMALLINT);
        }

Open in new window

>>prepStmt.setInt(19,getVersandArt(mPolice));

Did you try setShort/setByte?
I tried both setShort as well as setByte, but the same error.
>>SQL error: SQLCODE: -206, SQLSTATE: 42703

What does your reference give for these?
It gives the name of the newly added field, i.e., VERSANDART. When I checked the SQLCODE and SQLSTATE defined in the IBM Site, it talks about triggers. The database table does not have any triggers associated to it and therefore I am not sure where the problem lies.
Any difference between when you set a value on that column and when you set it to null?
If the user does not select one of the radio buttons then the value goes as null. The possible values are 0 or 1 or null. So basically a big difference if the value is set to null from the end user perspective.
No - i'm talking about from the point of view of whether the code works or not in each case
Another thing - is it possible that your Java app still is in a state where it's unaware of the column that was added?
It does not work in all the cases.
Also, whenever I make a change to java code, I restart my JBoss App Server so that the latest code is deployed and therefore there is no chance that the java code is unaware of the database change as the DB Pool is recreated each time the server is restarted.
SOLUTION
Avatar of CEHJ
CEHJ
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
the same is the case with me... :) it is a very peculiar problem and i really do not know what to do...hopefully i should be able to think of something tonite. If I find a solution then I will post it out here for the benefit of others who might face this problem in the future.
Hi Prashant,

Is the target column VERSANDART, perhaps defined as NOT NULL?


Kent
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hello everybody. The problem was that the application was indeed connecting to the wrong database as pointed out by Kent. Thanks to CEHJ for the prompt responses to the question. I am awarding 400 points to Kent for him pointing to the problem and awarding 100 points to CEHJ for putting his efforts. Hope Kent and CEHJ will not mind with the arrangement.
Thanks.
Prashant
Thank you to Kent and CEHJ for the prompt responses and the exact reason to the exception.
That's fine be me. Well done Kent :-)