Meaningless Drivel is fun!*
The moose likes Servlets and the fly likes problem with prepared statement Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "problem with prepared statement" Watch "problem with prepared statement" New topic
Author

problem with prepared statement

Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I am trying to put info into jdbc and got the folloing code from here and the sun site. I am getting the following error: expecting a ")"for each line of code. I don't see where a parenteses is needed.

Statement statement;
ResultSet book;



try{

statement = con.createStatement();

statement.execute("INSERT into 'APP'.'BOOKS' Values (?,?,?)");
statement.setInt(1, <isbn >);
statement.setString(2, <title value>);
statement.setString(3, <author value>);
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61103
    
  66

Is that your exact code, character for character?


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61103
    
  66

Also, please use UBB code tags when posting code.
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
Why have you got single quotes around APP and BOOKS ?

What happens if you remove them?

What I am thinking is that the single quote is used in SQL to contain strings, and I believe jdbc implicitly puts one at the start and end of a prepared statement. So when one is encountered embedded in the middle of your sql the database thinks its the end of your statement.

This doesn't fully explain the missing ) error, so I put this forward as a tentative suggestion.

Also - I second Bear's question - is that the exact code ? I would think that the stuff in angle brackets is intended to be replaced by literals or variables of the correct datatype:


[ December 12, 2006: Message edited by: Chris Hendy ]
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I have the single quotes around app and books because that is the way you would write the name of the DB using sql. I took out the <>'s and I quit getting the errors. I tried to put information into the DB but it does not seem to work. Will forward code when I change computers.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
here is my code that does not give me compiler errors but does not work either. This is the exact wording I copied and pasted it:



I'm trying to get variables where the String are. I also changes the table to be all strings.
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

That doesn't look like a valid SQL INSERT statement to me. Your first post looked closer to valid, except for the weird quotes.
Dhanya Palanisamy
Ranch Hand

Joined: Oct 20, 2006
Posts: 61
Hi,

Opening & closing braces and comma between the values are missing in your query.
you can insert like this:
statement.setString (1, "7899000,");

Also print your query before executing and see whether it has been formed correctly.

INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka', 'Sandnes')
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
Please, whatever you do, don't put commas in the bind variable values.

The structure of the SQL should be constant. Any RDBMS that caches and reuses SQL and execution plans won't recognise this as reusable sql (also I am dubious whether this is even legal).

Secondly, I don't think its a good idea to use strings for non-string values.

You are forcing the database to do an implicit type conversion if the column is actually numeric.

In Oracle if there is an index on such a numeric column, you will likely disable use of the index because of the type conversion.

Thirdly there seems to be some confusion between Statement and PreparedStatement. Your very first code example used a Statement, but the topic title says PreparedStatement. To use bind variables you need to use a PreparedStatement.

What I believe you should have is

Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
Also

To future proof your code somewhat, avoid using SQL like

INSERT INTO Persons VALUES ('Hetland', 'Camilla', 'Hagabakka', 'Sandnes')

Instead always explicitly name the columns as well.

INSERT INTO Persons (LastName,FirstName,Address,City)
VALUES ('Hetland', 'Camilla', 'Hagabakka', 'Sandnes')

If someone alters the table to add another column, code using the first type of INSERT will be broken.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
Chris,
That worked I now have put values in my DB. Now how do you put the variable from a text box into the DB? The textboxes are labeled:isbn, title, author, subject, condition and price. ;)
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
You're lucky I am a permanent employee at the moment. I used to be a contractor and I'd charge you �47 / hour for this.

I'll leave the working between your text boxes and the class containing the JDBC as an exercise for the student. But what you should aim to have is string variables

myTitle, myAuthor, mySubject, myCondition

an int called myIsbn

and I suppose a float called myPrice.

You would then write your statement as

Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I got that far. I decided to try moving one varible from a textbox first: here is the code I have on the jsp:

When I use this code on the jsp I get no errors.
When I go to the servlet I put the following code.

I get a null pointer exception
Ps I know your not suppose to put scriplets in your jsp. but that was the only way I could think of to get the textbox value. I'll keep working on it.
Chris Hendy
Ranch Hand

Joined: Mar 04, 2006
Posts: 98
What happens if you remove the leading space from isbn in

isbn = request.getParameter(" isbn");

so it becomes

isbn = request.getParameter("isbn");
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I still get a null pointer exception. one question does the code look right. Am i on the right track.
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30382
    
150

Donna,
Is ISBN the thing that is null?

I'm going to move this thread to our servlets forum since the question is no longer about JDBC.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I got it to work I had missed an assignment statement. I think i'll get done with this thing by the deadline tomorrow night.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I'm Stuck I thought I had it. I get no error messages It just does not print the right answer. I am going fron a textbox on a jsp to the servlet. here is the code I have for the jsp.

Here is what I have for the servlet:



I am using this from my bean:


The code from the bean works. I just can't get the value from the textbox to the servlet. Is there a syntax problem. This is the code as written.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61103
    
  66

String myisbn = (String) request.getAttribute(isbn);


getAttribute()?

Wrong method. Please research the difference between "attributes" (more correctly known as scoped variables) and request parameters.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
Tried that it did not work either:
Paul Clapham
Bartender

Joined: Oct 14, 2005
Posts: 18541
    
    8

"Did not work"... is that the same did-not-work that you had earlier or a different one? It's better to provide some detail so we can guess at your problem.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61103
    
  66

Originally posted by Donna Bachner:
Tried that it did not work either


getParameter() the correct means to obtain request parameters. Finding out why it is not working is the correct approach rather than trying random other methods.

Is the following your real code, character for character?



Or is it another red herring in which you are showing us incomplete code?

To obtain the isbn parameter, the line should be:


[ December 13, 2006: Message edited by: Bear Bibeault ]
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
Bear,
That is the code I have.

is what I just tried. The rest of the code (which I listed)I did not change. I know the bean part works. I tested it by putting values for the variables.
Dhanya Palanisamy
Ranch Hand

Joined: Oct 20, 2006
Posts: 61
session.setAttribute("isbn", request.getParameter("isbn.value"));

this should be

session.setAttribute("isbn", request.getParameter("isbn"));

Also try setting in request object itself.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I tried that here is the code I have now. this is the jsp. I tried all request and session code listed below:


this is the servlet:

The bean has not changed.
Dhanya Palanisamy
Ranch Hand

Joined: Oct 20, 2006
Posts: 61
Try giving an alert statement inside the JSP to see what you are getting in
request.getParameter("isbn");
Also in sendRedirect try using https and see whether it works. Where you are setting isbn in request/response before redirecting?
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
I am getting the proper redirect just not the right value. haven't done an alert yet will see.
Dhanya Palanisamy
Ranch Hand

Joined: Oct 20, 2006
Posts: 61
You might get the proper redirect, but i have come across some situations where an https works properly, so suggested.
Donna Bachner
Ranch Hand

Joined: Oct 08, 2004
Posts: 109
Still can't find problem. is this correct to get the value of the textbox?
Dhanya Palanisamy
Ranch Hand

Joined: Oct 20, 2006
Posts: 61
Hi Donna,

Did you find the solution?
You are seeting this in session.
session.setAttribute("isbn2",request.getParameter("isbn2"));
Use session.getAttribute to retreive also.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: problem with prepared statement