[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 53789
  • Last Modified:

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
0
pvasha
Asked:
pvasha
  • 8
  • 8
  • 2
2 Solutions
 
CEHJCommented:
Please post your code
0
 
pvashaAuthor Commented:
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

0
 
CEHJCommented:
>>prepStmt.setInt(19,getVersandArt(mPolice));

Did you try setShort/setByte?
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
pvashaAuthor Commented:
I tried both setShort as well as setByte, but the same error.
0
 
CEHJCommented:
>>SQL error: SQLCODE: -206, SQLSTATE: 42703

What does your reference give for these?
0
 
pvashaAuthor Commented:
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.
0
 
CEHJCommented:
Any difference between when you set a value on that column and when you set it to null?
0
 
pvashaAuthor Commented:
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.
0
 
CEHJCommented:
No - i'm talking about from the point of view of whether the code works or not in each case
0
 
CEHJCommented:
Another thing - is it possible that your Java app still is in a state where it's unaware of the column that was added?
0
 
pvashaAuthor Commented:
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.
0
 
CEHJCommented:
I'm out of ideas for now i'm afraid
0
 
pvashaAuthor Commented:
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.
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Prashant,

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


Kent
0
 
Kent OlsenData Warehouse Architect / DBACommented:

And are you 100% sure that you're connecting to the correct database and schema?

Kent
0
 
pvashaAuthor Commented:
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
0
 
pvashaAuthor Commented:
Thank you to Kent and CEHJ for the prompt responses and the exact reason to the exception.
0
 
CEHJCommented:
That's fine be me. Well done Kent :-)
0

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

  • 8
  • 8
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now