• 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

I'm stumped!

 
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Has anyone else run into this problem? I have been able to get everything to work on JDBC 2 except for the vhs and dvd fields when a new video is added. If I insert the data from mysql, I have no trouble. All of the fields can be updated. The other four fields can be updated from the html form, but for some reason, the vhs and dvd fields won't update. They are just blank spaces in the form. It seems to me that getting the input from a checkbox should work just like getting the input from a radio button, and adding the data to the database should work the same as well but it doesn't!!! I mean, VARCHAR and CHAR both refer to strings, right? One can have lots of characters, and the other only one, right? So they can be treated the same, right? Any clues?
 
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I may be remembering incorrectly (I didn't check yet)...
Take a good look at the sql statement that created the table. I recall that the settings for the dvd and vhs columns both required a single character as the input. So, are you trying to input a String where a character is required? Or do I remember incorrectly...
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
VHS CHAR(1) NULL, DVD CHAR(1) NULL
Good. My memory is holding up (maybe). So, are you sure you aren't trying to pass "yes" or "no" where "Y" or "N" is more appropriate?
 
Ranch Hand
Posts: 424
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am using "YES" resp. "NO" in the query string for DVD or VHS and it works fine .
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I altered the code in AddVideo so that "Y" was assigned to vhs if inVHS() was true and "N" was assigned if it was false. From the way I read the sql instructions, CHAR(1) NULL , allows a string of exactly 1 character in length, so I made vhs a String type variable. I can add records from mysql, but the two CHAR() fields won't update from the html form. I are confused!
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I were you, I'd consider just submitting the assignment in the best possible condition and ask specifically about your problem.
 
Ranch Hand
Posts: 129
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
My understanding of checkboxes is that they provide one of two possible results when submitted:
1) if checked, the String value of the value parameter (value="xxx") is assigned
2) if not checked, null is assigned
With that in mind, I set up a method to check the parms.getString() value. If it equals null return the "don't have" String ("N", "F", whatever), else return the "have" String. Then you can store that String in your database for DVD and VHS. Good luck.
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
AHA! So checkboxes are different from radio boxes! I thought it might have something to do with HTML. I will look into that when I get home from work. Thanks!
 
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Just wanted to address a sidebar point here.

Originally posted by Carol Murphy:
I mean, VARCHAR and CHAR both refer to strings, right? One can have lots of characters, and the other only one, right? So they can be treated the same, right?


From the database side of things (nothing to do with servlets, Java, HTML, etc. here) --
A CHAR field can hold either NULL or a character string that is the *exact length* as specified when you create or alter the table. Note that a CHAR field can further be set to be NOT NULL, in which case the only valid value is a character string that is the exact length as specified.
A VARCHAR field can hold either NULL or a character string whose length is zero to the length specified when you create or alter the table. Note that a VARCHAR field can further be set to NOT NULL, in which case the only valid values are character strings whose lengths are zero to the length as specified.
In other words, a field defined as CHAR (5) NULL can hold NULL or strings that are 5 characters long (no more, no less).
A field defined as VARCHAR (5) NULL can hold NULL or strings that are anywhere from zero to five characters long.
The difference between a VARCHAR (1) NULL field and a CHAR (1) NULL field is that the VARCHAR (1) NULL field could hold a string of zero length (""), but the CHAR (1) NULL field could not.
It's a fairly common idiom in database land when you're working with a system that doesn't allow real boolean datatypes to use a CHAR (1) NOT NULL field instead and "make sure" programmatically that you only insert "Y" or "N" (or "T" or "F) as values. (And also setting the whichever value you're using for false as a field default.)
Note the sample insert statement from the assignment:
INSERT INTO VIDEOS ( TITLE , STAR , TYPE , VHS , DVD , DESCRIPTION ) VALUES ( 'The Gods Must Be Crazy' , 'a coke bottle' , 'comedy' , 'Y' , 'N' , 'A bushman is introduced to civilization by a coke bottle.' );
The 'Y' and 'N' here imply to me that those are the values we are to store.
Given this, I'm not thrilled to pieces that the VHS and DVD fields in the assignment are NULLABLE, but I appreciate that the point of the assignment is not a rigorous introduction to database design.
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Thanks for the explanation Michael! It makes sense, but it still doesn't solve my problem. I am taking the liberty of e-mailing you a snippet of my code. Perhaps you can determine why my values for VHS and DVD are not being inserted into my table.
Another thing I noticed, the new Videos I'm inserting into the table from the webpage are showing up in the database with no "box" for the VHS and DVD fields. 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"; Only those videos I inserted with sql commands from the dos window will be selected. BUT when I chose select * from videos, they are listed with blank spots where vhs and dvd cells should be. 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. I also cannot delete the incomplete rows. I are very confused.....
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Carol, gimme a couple of days. I'm still on vacation.
Remember though, I haven't been nitpicked on the JDBC assignments yet and we might also need to resort to cyptic hints so as not to discuss details of the assingment too directly.
I'll be back in a few.
 
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Despite Michael's wonderful grasp of things and ability to explain so clearly, I think it is a bad idea to send code for an assignment to someone who has not been nitpicked on that assignment.
 
Dirk Schreckmann
Sheriff
Posts: 7023
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When your mother knows you are stealing a cookie when she has her back turned, we say that she has eyes on the back of her head.
When Marilyn knows about sneaking around on the Cattle Drive while she is on vacation, what is that called?
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
This is totally my fault! In my frustration and desire to get the solution to this puzzle, I didn't even stop to consider whether Michael had passed this assignment or not. I simply assumed he had. By the time I found out otherwise, I had already sent the code snippets. He told me that he didn't think he should look at my code until he has been nitpicked on this assignment, so I don't think too much damage was done. Like my code would help him anyway! It might just taint his approach to solving it!
Anyway, my apologies! That's what I get for making assumptions. Now, about this problem......
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
When you created your table, did you "copy and paste" from the web assignment page or did you type everything in? Are all your headings in your table in uppercase? Are they uppercase in your code?

If this doesn't help, send me the snippets and I'll take a look.
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I've tried all Uppercase, all lowercase, and a combination of the two. I tried making vhs and dvd varchar(1), that didn't work. It's like checkboxes are not supported by mysql! They just seem to be invisible. The other columns work no matter what I do. VHS and DVD won't work no matter what I do!
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Marilyn de Queiroz:
Despite Michael's ...


< blush >
Sorry about the commotion. Due to some problems I had remotely accessing my email while on vacation, I never ended up reading Carol's email.
So neither of us has compromised the assignment.
If it's OK, I'll post some more general SQL tips. I think there's a chance some of the problem might be data related.
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
More general SQL tips would be wonderful!
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I have come to the conclusion that there is a problem with my code at the insertion point. Somehow the entire row is not getting inserted into the table. AddVideo and ListVideo are working and the values for each type of input field are being sent. I printed them out on the page just to make sure they were there. They are.
For some reason, VHS and DVD are not being inserted into the table, so they don't show up on the video list. I suspect the problem lies in getting the data completely inserted into the table.
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. Is there some other command besides INSERT that we should be using here? Or is there some kind of bug in this version of mmmysql?
 
Darryl Failla
Ranch Hand
Posts: 129
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
If I read your last response correctly, you may just need some SQL tutorial help. The basic command to edit a record is:
UPDATE tablename SET columnName=newValue WHERE targetRecordColumnName=targetRecordColumnValue
The basic command to delete a record is:
DELETE FROM tableName WHERE targetRecordColumnName=targetRecordColumnValue
Good Luck
 
Sheriff
Posts: 4012
6
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Dirk Schreckmann:
When your mother knows you are stealing a cookie when she has her back turned, we say that she has eyes on the back of her head.
When Marilyn knows about sneaking around on the Cattle Drive while she is on vacation, what is that called?


Eyes all over the world!!
 
Marilyn de Queiroz
Sheriff
Posts: 9109
12
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Darryl,

She's not editing or deleting a record (yet). She is adding a record using INSERT.
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Michael, Michael, Michael.........
I was soaking in the tub, when a thought occurred to me. Just redo the assignment without vhs and dvd and see what happens. Trying not to drip water onto the keyboard, I am puzzled by the results. My videos look great on the webpage, and all of the columns have values when I'm working in the mysql window. (I'm a post-dos computer user, so I don't know nothin' bout dos.) But I can't select certain items using = . That's weird. Hmmmmmm. Why are the column entries made from the web page indented one space when the entries made from mysql are not? Hmmmmmm.......... I go back to my insert command and guess what I see? Every item to insert is typed like so:
( ' " + item + " ' , etc )
Hmmmmmmmm...... What happens if I do it like this?
( '" + item + "' , etc )
Eureka!!!
It's not a bug in mysql. It's a very subtle little glitch in my code! Those pesky spaces!
I will try working with the sql command later. It looks like a good thing to know how to do when I pull something like this again! (And I'm sure I will!)
Right now I feel so good about solving this little mystery, I'm afraid to do anything to more tonight! Thanks again!
[ September 20, 2002: Message edited by: Carol Murphy ]
[ September 20, 2002: Message edited by: Carol Murphy ]
 
Michael Matola
whippersnapper
Posts: 1843
5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

Originally posted by Carol Murphy:
I was soaking in the tub, when a thought occurred to me. <snip>
Eureka!!!


Er, um, did you run down the street?
 
Carol Murphy
village idiot
Posts: 1208
1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
No, not down the street. Just across the landing at the top of the stairs.
Thanks again for all your help, and I hope I didn't get you in too much trouble with the Sheriff!
 
A sonic boom would certainly ruin a giant souffle. But this tiny ad would protect it:
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic