I'm responding to several of Carol's posts. Carol's text is in
bold. It's like checkboxes are not supported by mysql! Checkboxes are not "supported" at all by MySQL. MySQL is a database and it stores data. A checkbox is a GUI element that
presents some representation of that data (or accepts data as input). In our given assignment, the GUI is web pages (HTML code) that's generated by Java servlets. It's up to you as the servlet programmer to provide the necessary translation between data as stored in the database and data as presented to end users of your system.
The assignment essentially tells us that we need some way to store the fact that a given video is or is not available as VHS (or DVD). We could choose all sorts of (outlandish?) ways of storing that information. We could make a numeric field and say the value "10" means "not available in VHS" and the value "23" means "available in VHS." We could make a large free-form text field that could store just about anything: "yes, since last year," "no, but is expected to be release in 3rd quarter of 2002." Etc. There are many reasons why these examples would be poor choices for storing the particular information. Luckily, the assignment tells us to use a 1-character field to store the values "Y" or "N".
Now given our underlying values of Y and N, how should we display them to end users? We could have our servlet translate Y to "Yessiree, bob!" and N to a blinking text "No, no, a thousand times no." Luckily, the assignment seems to tells us when listing out the videos through ListVideoServlet to translate Y and N to "yes" and "no" in the table and when accepting these values as input through AddVideoServlet in a HTML form to use checkboxes.
(Hint: there's a handy method in LogServlet that you've seen in previous instructor's solutions that can
help in the translating user input from checkboxes to the form we ultimately want to store in the database.)
Executive summary: don't mix up how data is stored and how it is presented. You as the programmer may have to do some conversion.
Somehow the entire row is not getting inserted into the table. <snip> I suspect the problem lies in getting the data completely inserted into the table. <snip> The incomplete records which have been input from the webpage are a different matter. I have serious doubts that you have "incomplete records" or "the entire row is not getting inserted." Unless MySQL is severly broken (which I doubt), there's no such thing as an "incomplete record/row" in a databse. SQL INSERT is a row-level operation in that it either successful inserts (a) row(s) of data or it fails. If an insert fails, an error is generated and if the INSERT was done programmatically through a Java servlet, that error should cause an SQLException (or somesuch) to be thrown.
I'm fairly confident that if you're not getting SQLExceptions, you have complete rows of data. However, the data that you
are storing is probably not what you're expecting, given the problems you're having working with it.
I have come to the conclusion that there is a problem with my code at the insertion point. Right, but in addition, I think in your servlet code you're not correctly translating from what the VHS and DVD checkboxes give you to something that gets inserted in the correct format for you to work with later.
For example, I can recreate what I believe you're describe by doing the following insert from the mysql prompt:
insert into videos values ( 'me' , 'you' , 'horror' , ' ' , '' , 'description' ) ;
Note that ' ' is a blank space and '' is a an empty string. In either case, I get "blank spots" in my HTML table because the current version of my ListVideoServlet wasn't written with the expectation of having blanks or empty strings returned from the database.
(You could argue that a more robust program would handle such degenerate data. You could also argue that a more robust system shouldn't allow degenerate data to be stored in the first place.)
Anyhow, I think that's what's going on in your case.
Try this select and tell me what values are getting stored in your VHS and DVD fields.
The concat function is just jamming angle brackets around the exact values stored for vhs and dvd. You could use any characters you like. I prefer angle brackets because they work well even if you're displaying in a proportional font. The "as vhs" and "as dvd" are field aliases. They just make things easier to read, so your column names aren't goofiness like "concat( '>' , vhs , '<' )".
Note, for what it's worth, in Oracle SQL you'd do this like this:
Anyhow, if it's blanks or empty strings causing your problems, I'd suggest just deleting all your data then revising your program to insert only Y or N.
I'll try to post something tomorrow or on the weekend about using temp tables for storing and shuttling around
test data.
*****
Some of the results you say you're getting from the mysql prompt surprise or puzzle me.
When I go into mysql and select videos, these new, incomplete entries are ignored when I try something like: select * from videos where type = "horror" ; Puzzling. Are you absolutely confident the type is exactly what's in the quotes?
Only those videos I inserted with sql commands from the dos window will be selected. Er, "mysql window."
BUT when I chose select * from videos, they are listed with blank spots where vhs and dvd cells should be. The select above with concats should make it clear what the exact values are.
If I choose count(*) from videos; They are included in the count. What's up with that? They seem to be visible to some commands but not others. Are you sure the yucko rows have a type = 'horror'? Are you absolutely certain your where clause is accurate?
I also cannot delete the incomplete rows. W'huh?
Are you just doing a
delete from videos ;
or are you qualifying with a where clause? Are you sure you got the where clause correct?
When I'm adding records in mysql, everything works fine. I can insert rows, change data, delete rows, etc. The incomplete records which have been input from the webpage are a different matter. They only appear for select * from videos; or count(*). I can't update them, select them individually, or delete them individually. They seem to be invisible as unique items, although they show up if you want to see the entire table. How are you trying to select, update, or delete them individually?
*****
Anyhow, I hope this gives you some ideas.