JavaScript error in Parameterized query

Question

Here is what I am trying to do (for over a day now :( A user clicks on a link of a book name and I read the name of that book. I then take that book name and make an Ajax request to a Jersey resource. Within that Jersey resource, I call a method in a POJO class where one method interacts with database and gets the data to be sent back to a Jersey resource. I have got many errors but I have been able to fix them one at a time. The error currently I am stuck at is:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '?' at line 1

Here is my JavaScript code:

function dealWithData(nameOfBook){

    var bookName = encodeURI(nameOfBook);

    console.log("http://localhost:8080/library/rest/books/allBooks/"+bookName);

    var requestData = {

            "contentType":  "application/json",
            "dataType":     "text",
            "type":         "GET",
            "url":          "http://localhost:8080/library/rest/books/allBooks/"+bookName

**//beforeSend has been added as an edit to original code**

beforeSend: function (jqXHR, settings) {
              var theUrlBeingSent = settings.url;
              alert(theUrlBeingSent);
}
    };

    var request = $.ajax(requestData);

    request.success(function(data) {

        alert("Success!!");
    });

    request.fail(function(jqXHR, status, errorMessage) {
        if((errorMessage = $.trim(errorMessage)) === "") {
            alert("An unspecified error occurred.  Check the server error log for details.");
        }
        else {
            alert("An error occurred:  " + errorMessage);
        }
    });
}

For some reason in above code, console.log line shows url with spaces being encoded as %20 while in the variable 'requestData', url doesn't have that encoding. I am unable to understand why.

Here is the code for my resource:

@GET
    @Path("/allBooks/{bookName}")
    @Produces(MediaType.APPLICATION_JSON)
    public Response getBook(@PathParam("bookName") String bookName){

        System.out.println("Book name is: "+ bookName);
        BookInformation bookInfo = new BookInformation();
        String bookInformation =bookInfo.bookInformation(bookName);

        ResponseBuilder responseBuilder = Response.status(Status.OK);
        responseBuilder.entity(bookInformation);

        Response response = responseBuilder.build();
        return response;
    }

Here is the bookInformation method:

public String bookInformation(String bookName){

        String infoQuery = "Select * from bookinfo where name = ?";
        ResultSet result = null;
        conn = newConnection.dbConnection();    
        try
        {   
            preparedStatement = conn.prepareStatement(infoQuery);
            preparedStatement.setString(1, bookName);
            result = preparedStatement.executeQuery(infoQuery);
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        try
        {
            if(result != null){

                while(result.next()){

                    availability = result.getString("availability");
                    isbn = result.getInt("isbn");
                    hardback = result.getString("hardback");
                    paperback = result.getString("paperback");
                    name = result.getString("name");

                }
            }
            else{
                System.out.println("No result set obtained");
            }
        }
        catch (SQLException e)
        {
            e.printStackTrace();
        }
        //I will build this String using a String builder which I will return
String finalBookInformation = information.toString();
                    return finalBookInformation;
    }

Earlier, in dataType I had json which was throwing a different error, but I realized I was not building json so I changed dataType to text and that error went away. My parametirized query doesn't execute. If I try hard coding a value from database, it works fine but not when I use prepared statement. I eventually want to return JSON but for now I just want it to work. Any help will be appreciated. I have tried researching and doing whatever I can but it is not working. Is it the encoding causing the problem? Is it my Ajax call? Any help is appreciated. Thanks.

Chetan Verma · Accepted Answer · 2015-06-14 19:58:36Z

1

Seems like issue is in your database query execution please replace the code

preparedStatement = conn.prepareStatement(infoQuery);
preparedStatement.setString(1, bookName);
result = preparedStatement.executeQuery(infoQuery);

with

preparedStatement = conn.prepareStatement(infoQuery);
preparedStatement.setString(1, bookName);
result = preparedStatement.executeQuery();

answered Jun 14, 2015 at 19:58

Chetan Verma

8836 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Chetan Verma · Accepted Answer · 2015-06-14 06:33:15Z

0

You are using HTTP GET method and GET method automatically %20 if find space in url.

If you change your method type to POST It should work find for you.

answered Jun 14, 2015 at 6:33

Chetan Verma

8836 silver badges10 bronze badges

13 Comments

user3044240 Over a year ago

If I change GET to POST in my Ajax request and similarly in Resource class, it still gives the same error.

Chetan Verma Over a year ago

please alert the book name before where you are adding it to url and let me know what it prompt

user3044240 Over a year ago

Name being alerted is the same as text of link being clicked. If my book's name is Harry Potter, then it is alerting it in the exact same format as in the link, Harry Potter

Chetan Verma Over a year ago

you don't think space between "Harry Potter" is producing that %20 ?

user3044240 Over a year ago

I am not sure. As per your instructions I alerted name of book which was passed to JavaScript function as nameOfBook in my posted code. After alerting, I encode the uri and I think that's what is adding %20 instead of that space.

|

Community · Accepted Answer · 2017-05-23 11:43:42Z

0

You can use HTTP GET but it will encode the URL as you discovered. You will need to decode the URL on the server-side. For how to do that, take a look at: How to do URL decoding in Java?.

edited May 23, 2017 at 11:43

CommunityBot

11 silver badge

answered Jun 14, 2015 at 6:34

Cymen

14.5k4 gold badges58 silver badges77 bronze badges

2 Comments

user3044240 Over a year ago

When I check in the bookInformation method, bookName being passed is without any %20. Code is able to reach the correct resource, as the bookInformation method is within that resource. I think it means that it did reach the correct url. Am I thinking and understanding this correctly or am I totally wrong?

user3044240 Over a year ago

I can see now after adding beforeSend in my above JavaScript that the URL being sent to server is encoded. You were correct. But in the example link that you have given, it already has String url. How will I access the URL in my Java class in that bookInformation method? Also, how will this decoding help me in solving the error that I am receiving. Me confused!

Collectives™ on Stack Overflow

JavaScript error in Parameterized query

3 Answers 3

Comments

13 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

13 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related