Cannot perform SQL UPDATE null

[Giuseppa Cefalu]

Hi,

My Jtable retrives a database table . Some of the cells in the table are varchar, they are empty and initiallized to " ". Some other are integer and initialized to -1 and some others are date cannot be initialized to any date value so they are null. When I try to update the database using the update set.... method, I get the following message: "Cannot perform SQL UPDATE null.

I do not understand this because one can insert null values in the database. Any ideas?

Thank you

[Paul Clapham]

I would start by looking at the SQL statement which caused that error.

[Giuseppa Cefalu]

This is the code that produces the SQl sattement. Could you plese take a look at it?. I do not seem to see where the problem is.

Thank you

 for(int z = 0; z < totalRows; z++){
                currentRow = (Vector)allRows.elementAt(z);
                int numElements = currentRow.size();
                dbValues = new String[numElements];
                for(int s = 0; s < numElements; s++){
                    String classType = currentRow.elementAt(s).getClass().toString();
                    int pos = classType.indexOf("String");
                    if(pos > 0){
                        dbValues[s] = currentRow.elementAt(s).toString();
                        updateLine[s] =  tableColNames[s] + " " + "=" + " " + "'"+ currentRow.elementAt(s)
                             + " "   + "',";
                        if(tableColNames[s].toUpperCase().equals(pKeyCol.toUpperCase())){
                        pKeyValues[z] = currentRow.elementAt(s).toString();
                       
                        }
                    }

                    pos = classType.indexOf("Integer");
                    if(pos > 0){
                        dbValues[s] = currentRow.elementAt(s).toString();
                        updateLine[s] =  tableColNames[s] + " " + "=" + " " +
                                    currentRow.elementAt(s).toString() + ",";
                        if(tableColNames[s].toUpperCase().equals(pKeyCol.toUpperCase())){
                            pKeyValues[z] = currentRow.elementAt(s).toString();
                        }
                       
                    }

                    pos = classType.indexOf("Date");
                    if(pos > 0){
                        dbValues[s] = currentRow.elementAt(s).toString();
                        updateLine[s] = tableColNames[s] + " " + "=" + " " + "'" +
                                 currentRow.elementAt(s) + "',";
                        if(tableColNames[s].toUpperCase().equals(pKeyCol.toUpperCase())){
                            pKeyValues[z] = currentRow.elementAt(s).toString();
                        }
                    }
                    
                }

 if(rowInserted == false){
                    StringBuffer updateSQL = new StringBuffer();
                    updateSQL.append("UPDATE" + " " + tableName + " " + "SET" + " ");
                    for(int y = 0; y <= updateLine.length - 1; y++){
                        if(updateLine[y] != null){
                            updateSQL.append(updateLine[y]);
                        }

                    }
        
                   updateSQL.replace(updateSQL.length()-1, updateSQL.length(),"");
                   
                   updateSQL.append(" " + "WHERE" + " " + pKeyCol + " " + "=" + " " + pKeyValues[z] +";");
                   System.out.println(updateSQL);
                   stm.executeUpdate(updateSQL.toString());
                }

[Paul Clapham]

Actually I meant to suggest you look at the SQL statement. I see you are writing it to the console at line 14 of one of those code fragments.

[Giuseppa Cefalu]

I have introduced a space in the updateLine statement before tableColNames[s], and I have eliminated a space sfter SET. The statement works well if I input a value in the JTable cell; but if does not work if I input a blank; which is coded as null.

updateLine[s] =  " " + tableColNames[s] + " " + "=" + " " + "'"+ currentRow.elementAt(s)
                             + " "   + "',";

updateSQL.append("UPDATE" + " " + tableName + " " + "SET");

The result is update tablename set varciable1 = number, variable2 = 'string', variable3 = 'date', variable4 = number where pkey = number;

[Paul Clapham]

Giuseppa Cefalu wrote:The result is update tablename set varciable1 = number, variable2 = 'string', variable3 = 'date', variable4 = number where pkey = number;

And is that the actual SQL statement which caused the error message?

[Giuseppa Cefalu]

It has to be, because it only happens if I update by clicking the update button. The only other statement is the one which reads the table and it has never produced an error. I wonder if it has to do with the JTable. The thing is that if the JTable reads null data from the database table and I click the update button to do the database update things work well. I only ge the error when I modify by deleting the cell contents to enter a blank (null).

[Paul Clapham]

I get the distinct feeling that, instead of posting the actual SQL, you are posting something made up. Is the table you are updating really called "tablename"? And does it really have columns named "varciable1" and "variable2" and so on?

[Giuseppa Cefalu]

Is this what you mean?

update test3 set name = 'Mary', DateOfBirth = '2010-12-12' where MRN =123456;

[Paul Clapham]

Yes, that's what I meant. When the database complains about an SQL statement, it doesn't know or care that you built the SQL statement from data in a JTable, or anything else like that. All it knows about is the SQL statement which you gave it. So telling us about the code you used to construct the statement is useless. The first step is to look at the actual SQL statement.

But that SQL statement doesn't seem to have anything null-like in it. Are you sure that is the same SQL statement which caused the exception?

[Giuseppa Cefalu]

The problem that I see is the following: The statement below tries to define the input for a date. Dates in SQL must be input as "" not as ' ' as I do here. Please see "'" and "'," below. This is supposed to mean for exmaple '1999-09-02'. Writing the statetement update test3 set Date = '1999-09-02' responds with an error. the correct way to do this is update test3 set date = "1999-09-02". I do not know how to hundle inserting "" instead of '' in java. On the other hand. If I write the statement in the command line as update test3 set Date = NULL, the database dnot respond with an error. When I retrieve the database table in the JTable all the NULLs appear as empty cells. if I press the update button to update I get the error. I am not writting update test3 set date = NULL becuase I am not using the command line. This is implied in the statement below; which is not only wrong for Date input since date input should be done with "" instead of ''; but also for strings and integers. the problem is not with the statement. On This same problem applies to any other data type. If I enter -1 for integers or " " for strings; it works fine; but if I enter nothing (empty cell) the program returns with an error. The problem does not reside on the SQL statement; but on the program. the table does not insert null values in the database probably because NULL cannot be surrounded by "" or ' ';therefore , I am going to try to do some thing with the program. Please suggest any ideas that you may have.

 pos = classType.indexOf("Date");
                    if(pos > 0){
                        dbValues[s] = currentRow.elementAt(s).toString();
                        updateLine[s] = " " +  tableColNames[s] + " " + "=" + " " + "'" +
                                 currentRow.elementAt(s) + "',";
                        if(tableColNames[s].toUpperCase().equals(pKeyCol.toUpperCase())){
                            pKeyValues[z] = currentRow.elementAt(s).toString();
                        }

[Paul Clapham]

I have already suggested what you should do: look at the SQL statement which causes the exception. Then once you have decided why it causes the exception, you can look at the code which produces the SQL statement and modify it accordingly.

You don't seem to be interested in doing that. Perhaps somebody else will be interested in helping you follow the path you're on, but in my opinion it is totally impractical. I wish you good luck with it, though.

[Giuseppa Cefalu]

I think I do not understand what you are suggesting. have not I posted the sql statement?

[Giuseppa Cefalu]

How about this: is this what you mean?

update test3 set DateOf Birth = NULL where Id = 1;

[Paul Clapham]

What I meant was to run your program until the exception occurred, and then to look at the SQL statement which was written in System.out just before that happened. Looking at anything else would be ineffective; the exception complains about a particular SQL statement so looking at some other SQL statement isn't all that useful.

So is that it? The problem I see right away is that you have a space in the middle of the "DateOfBirth" column name.

[Giuseppa Cefalu]

Thank you. I will try that.

[Giuseppa Cefalu]

This is the printed QSL statement. The program runs while there is non null input and it stops once it finds the null input.

update test2 set MRN = 123456, name = 'Rick      ',  DateOfBirth = '1200-12-12', Age = 1 WHERE MRN = 123456; 

Cannot perform SQL UPDATEnull
I can get the printed output becuase I input '1200-12-12', but I get The error and not printed output becuase I input nothing in the
cell immediately below.

[Giuseppa Cefalu]

Can any one help with this please?

UPDATE test2 SET MRN = 123456, name = 'Rick       ',  DateOfBirth = '\N',  Age = 1 WHERE MRN = 123456;

SQL Error Cannot perform SQL UPDATEData truncation: Incorrect data value: 'N' for column DateOfBirth at row 1

[Paul Clapham]

I believe that SQL should look like this, shouldn't it?
UPDATE test2 SET MRN = 123456, name = 'Rick ', DateOfBirth = null, Age = 1 WHERE MRN = 123456;
Or am I wrong? Try it from the MySQL command line and see.

(I should mention that I always use PreparedStatement when I'm updating my database, which does happen to be MySQL. I haven't ever had any problem with null dates.)

[Giuseppa Cefalu]

could you please post an example?

[Paul Clapham]

PreparedStatement ps = conn.prepareStatement("Update table set startdate = ?");
ps.setDate(1, null);
ps.executeUpdate();

[Giuseppa Cefalu]

thanks a lot. I copied an example from google to explain my self -please see below. In my case I have to get input through the table. This input is stored into a vector. I can interrogate the vector elements for the datatype and I can interrogate the toble for the table column name; But coul I create PreparedStatement ps = conn.prepareStatement(
"UPDATE Messages SET description = ?, author = ? WHERE id = ? AND seq_num = ?"); using a loop?

Would using a loop in the following way work? for(in i = 0; i < N; i++){ "UPDATE Messages SET variable_name = ? WHERE id = ?) and then ps.setDataType(i, variableName)?
}

public static void updateDescriptionAndAuthor (Connection conn,
                                               String description,
                                               int id,
                                               int seqNum,
                                               String author )
throws SQLException
{
  try
  {
    // create our java preparedstatement using a sql update query
    PreparedStatement ps = conn.prepareStatement(
      "UPDATE Messages SET description = ?, author = ? WHERE id = ? AND seq_num = ?");

    // set the preparedstatement parameters
    ps.setString(1,description);
    ps.setString(2,author);
    ps.setInt(3,id);
    ps.setInt(4,seqNum);

    // call executeUpdate to execute our sql update statement
    ps.executeUpdate();
    ps.close();
  }
  catch (SQLException se)
  {
    // log the exception
    throw se;
  }
}

[Paul Clapham]

Giuseppa Cefalu wrote:But coul I create PreparedStatement ps = conn.prepareStatement(
"UPDATE Messages SET description = ?, author = ? WHERE id = ? AND seq_num = ?"); using a loop?

Would using a loop in the following way work? for(in i = 0; i < N; i++){ "UPDATE Messages SET variable_name = ? WHERE id = ?) and then ps.setDataType(i, variableName)?
}

Yes, I expect you could create the PreparedStatement with a loop. You would start with the constant part at the beginning: "UPDATE Messages SET" and follow that with a loop which appended "variable_name = ?" for each of the variables you wanted to update. After that you would append the constant part at the end: "WHERE id = ?". You have to put spaces and commas in the right place, but I'm sure you've already been through that sort of problem already.

[Giuseppa Cefalu]

Thanks a lot!