Solved

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

Posted on 2008-06-19
18
49,211 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
  • 8
  • 8
  • 2
18 Comments
 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
Please post your code
0
 

Author Comment

by:pvasha
Comment Utility
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
Comment Utility
>>prepStmt.setInt(19,getVersandArt(mPolice));

Did you try setShort/setByte?
0
 

Author Comment

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

Expert Comment

by:CEHJ
Comment Utility
>>SQL error: SQLCODE: -206, SQLSTATE: 42703

What does your reference give for these?
0
 

Author Comment

by:pvasha
Comment Utility
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
Comment Utility
Any difference between when you set a value on that column and when you set it to null?
0
 

Author Comment

by:pvasha
Comment Utility
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
Comment Utility
No - i'm talking about from the point of view of whether the code works or not in each case
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 86

Expert Comment

by:CEHJ
Comment Utility
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
Comment Utility
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
Comment Utility
I'm out of ideas for now i'm afraid
0
 

Author Comment

by:pvasha
Comment Utility
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:Kdo
Comment Utility
Hi Prashant,

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


Kent
0
 
LVL 45

Accepted Solution

by:
Kdo earned 400 total points
Comment Utility

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

Kent
0
 

Author Comment

by:pvasha
Comment Utility
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
Comment Utility
Thank you to Kent and CEHJ for the prompt responses and the exact reason to the exception.
0
 
LVL 86

Expert Comment

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

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
Viewers learn about the third conditional statement “else if” and use it in an example program. Then additional information about conditional statements is provided, covering the topic thoroughly. Viewers learn about the third conditional statement …
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…

762 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

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now