• 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

problem with prepared statement

 
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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>);
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Is that your exact code, character for character?
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Also, please use UBB code tags when posting code.
 
Ranch Hand
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Marshal
Posts: 28193
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
That doesn't look like a valid SQL INSERT statement to me. Your first post looked closer to valid, except for the weird quotes.
 
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 98
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I still get a null pointer exception. one question does the code look right. Am i on the right track.
 
author & internet detective
Posts: 41860
908
Eclipse IDE VI Editor Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Donna Bachner
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Tried that it did not work either:
 
Paul Clapham
Marshal
Posts: 28193
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
"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
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
I am getting the proper redirect just not the right value. haven't done an alert yet will see.
 
Dhanya Palanisamy
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
You might get the proper redirect, but i have come across some situations where an https works properly, so suggested.
 
Donna Bachner
Ranch Hand
Posts: 109
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Still can't find problem. is this correct to get the value of the textbox?
 
Dhanya Palanisamy
Ranch Hand
Posts: 61
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Straws are for suckers. Now suck on this tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com
reply
    Bookmark Topic Watch Topic
  • New Topic