• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Cannot perform SQL UPDATE null

 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I would start by looking at the SQL statement which caused that error.
 
Giuseppa Cefalu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is this what you mean?

 
Paul Clapham
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I think I do not understand what you are suggesting. have not I posted the sql statement?
 
Giuseppa Cefalu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
How about this: is this what you mean?

 
Paul Clapham
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thank you. I will try that.
 
Giuseppa Cefalu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
could you please post an example?
 
Paul Clapham
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
 
Giuseppa Cefalu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Marshal
Posts: 28177
95
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks a lot!
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
reply
    Bookmark Topic Watch Topic
  • New Topic