File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes Cannot perform SQL UPDATE null Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Cannot perform SQL UPDATE null" Watch "Cannot perform SQL UPDATE null" New topic
Author

Cannot perform SQL UPDATE null

Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
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
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

I would start by looking at the SQL statement which caused that error.
Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121

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


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
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.





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

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
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
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Is this what you mean?

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
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.



Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
I think I do not understand what you are suggesting. have not I posted the sql statement?
Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
How about this: is this what you mean?

Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Thank you. I will try that.
Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
This is the printed QSL statement. The program runs while there is non null input and it stops once it finds the null input.


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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Can any one help with this please?


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

Joined: Oct 14, 2005
Posts: 18991
    
    8

I believe that SQL should look like this, shouldn't it?

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
could you please post an example?
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

Giuseppa Cefalu
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
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)?
}


Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18991
    
    8

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
Ranch Hand

Joined: Jul 15, 2011
Posts: 121
Thanks a lot!
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Cannot perform SQL UPDATE null