Solved

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

Posted on 2008-06-19
18
50,990 Views
Last Modified: 2010-05-18
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
Comment
Question by:pvasha
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 8
  • 2
18 Comments
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822013
Please post your code
0
 

Author Comment

by:pvasha
ID: 21822233
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822358
>>prepStmt.setInt(19,getVersandArt(mPolice));

Did you try setShort/setByte?
0
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

Author Comment

by:pvasha
ID: 21822386
I tried both setShort as well as setByte, but the same error.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822414
>>SQL error: SQLCODE: -206, SQLSTATE: 42703

What does your reference give for these?
0
 

Author Comment

by:pvasha
ID: 21822472
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822557
Any difference between when you set a value on that column and when you set it to null?
0
 

Author Comment

by:pvasha
ID: 21822637
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
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822828
No - i'm talking about from the point of view of whether the code works or not in each case
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 21822863
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
 

Author Comment

by:pvasha
ID: 21822964
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
 
LVL 86

Assisted Solution

by:CEHJ
CEHJ earned 100 total points
ID: 21823087
I'm out of ideas for now i'm afraid
0
 

Author Comment

by:pvasha
ID: 21823122
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
 
LVL 45

Expert Comment

by:Kent Olsen
ID: 21825937
Hi Prashant,

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


Kent
0
 
LVL 45

Accepted Solution

by:
Kent Olsen earned 400 total points
ID: 21826018

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

Kent
0
 

Author Comment

by:pvasha
ID: 21829405
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
 

Author Closing Comment

by:pvasha
ID: 31469079
Thank you to Kent and CEHJ for the prompt responses and the exact reason to the exception.
0
 
LVL 86

Expert Comment

by:CEHJ
ID: 21829473
That's fine be me. Well done Kent :-)
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction This article is the first of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article explains our test automation goals. Then rationale is given for the tools we use to a…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Suggested Courses

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question