CODEWITHSUNDEEP
javajavascriptmysqlajaxjson
user3044240
user3044240

Reputation: 631

JavaScript error in Parameterized query

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.

Upvotes: 0

Views: 95

Answers (3)

Chetan Verma
Chetan Verma

Reputation: 873

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();

Upvotes: 1

Cymen
Cymen

Reputation: 14429

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?.

Upvotes: 0

Chetan Verma
Chetan Verma

Reputation: 873

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.

Upvotes: 0

Related Questions