Win a copy of The Little Book of Impediments (e-book only) this week in the Agile and Other Processes forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to pass parameter to MySQL statement?

 
Ripal Naik
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello Guys,

I am trying to fetch data from the database using the parameter. I have spent more than 3 hours to get it work but the query is not working. If I will enter uid manually then its working.
for example: String sql="select RLastName, RFirstName, RUserId, RDepartNo, REmail, Tarea from Awards where SUserId='kvaughan'";
but it's not working when i pass parameter inside it. I have tried both Prepared and createStatement ways.
Error is as below:
com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: 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 'kvaughan''' at line 1

Can anybody pls tell me what's wrong in my code?

 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to JavaRanch.

Several points to note.- For starters, DB access doesn't go into a JSP, that's bad design. It goes into Java code - either a servlet or a backing bean.

Secondly, using a PreparedStatement is a must, otherwise your code is wide open to SQL injection attacks. What you had was almost correct - but for parameters in a PreparedStatement you don't use the single quotes around character parameters; the statement does that for you.
 
K. Tsang
Bartender
Posts: 3583
16
Android Java
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Welcome to the Ranch.

How are you getting the "uid" parameter for the sql statement?

Also all the db connection stuff you have should be in a servlet rather than in a JSP. A JSP page is best for display/output.
 
Ripal Naik
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting uid like this from the previous page:



I have tried many different ways to get my sql query work but its not working
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have tried many different ways to get my sql query work but its not working

The two approaches you posted in your code were wrong for the reasons I tried to explain in my previous post - did that not make sense? If you use a PreparedStatement, you don't use quotes around character parameters. If you use a non-prepared Statement, you do use quotes around character parameters. Does that make it clearer?
 
Ripal Naik
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Are you telling me something like this:



It's still not working.

It's working for


this is the uid m passing "kvaughan"

 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Well, you should really use a PreparedStatement for the security reason I mentioned earlier. Something like "String sql="select RLastName, RFirstName, RUserId, RDepartNo, REmail, Tarea from Awards where SUserId='"+uid+"'" " must never be used in production.

You should also ensure that "uid" has the correct value. Earlier you mentioned "&userid='<%=userid%>'" - this would add single quotes around the argument which changes its value. You should print out its value in the JSP, so that you know it's getting passed around correctly.
 
Ripal Naik
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello,

I am not understanding what you saying I am now using PreparedStatement but still not working.

Could you modify my code and tell me what are you exactly wanting me to modify?



I know I should use servlet instead. But I am not using any confidential data. It's for my practice as I am learning it.

kvaughan is the value uid i am passing to query as paramater and then I want to access all the information of kvaughan from database.
 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
kvaughan is the value uid i am passing to query as paramater

That may well be, but if the URL is constructed as you showed earlier, then that is not going to work because of the reason I mentioned in my last post. That is why I am advising (strongly, now) to print out the value that you're using to construct the SQL query. Do it in a way so you'll be sure that there are no leading/trailing blanks, either.

 
Ulf Dittmer
Rancher
Posts: 42969
73
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It's for my practice as I am learning it.

Practicing bad practices doesn't provide you with useful experience, it trains you to do things that you will need to unlearn later. What is the point of that?
 
Paul Ngom
Ranch Hand
Posts: 355
1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator

[

String uid = request.getParameter("userid");

//out.println(uid);


String sql="select RLastName, RFirstName, RUserId, RDepartNo, REmail, Tarea from Awards where SUserId="+uid;

]
@Ripal
I strongly advice you to issue other out.println("param") for the other parameters you pass from the previous page to see that the correct values are received. I suspect that some parameters are not fully passed. And tell us a bit about your development environment.
You could also try to remove the single quotes around the parameters in the first page for it to look as follows:
<a href = "./submittedawards.jsp?lname=<%=lname%>&fname=<%=fname%>&userid=<%=userid%>&deptno=<%=deptno%>">Submitted awards
And replace in variables you suspect could contain white spaces by *
and make your query look as follows:
String sql="select RLastName, RFirstName, RUserId, RDepartNo, REmail, Tarea from Awards where RUserId='"+uid+"';";

The column name is RUserId and not SUserId as you used it.
 
Ripal Naik
Greenhorn
Posts: 20
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Paul and Thanks you guys for you help.

It's working fine now after removing the quotes from .


Need one more help as you can see in my code I am having all this values in table like firstname, lastname etc.

In my table I have all the entries fetch from award database now to use this values of table and by taking one check box I want to want to add only one record of table to another database.

How can I get below table values parameter and using a check box with them I want to store particular entry in another database?

any help with the code would be appreciated guys.

I have attached my code which I am trying.



This is my table:
Select Award Winner:

  • Andy Bergin abergin 8671 Brad Walker bwalker 6711 bwalker@airius.com Best Friend of the year [] //select on check box and add this record to another database
    Andy Bergin abergin 8671 Eric Walker ewalker 4231 ewalker@airius.com Best sales person of the year []
    Andy Bergin abergin 8671 John Walker jwalker 8671 jwalker@airius.com Best Business maker of the month []



  • If I select the record then I want it to add to another database:

    Can I do it in same file by using another insert query where checkbox value = userid or do I need to make another file?

    This is what I am trying but getting null values:
    do I need to pass all this values in form with action =winaward.jsp?//all values// ?



     
    Paul Ngom
    Ranch Hand
    Posts: 355
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    String SLastName = request.getParameter("SLastName");

    out.println("SLastName");

    String SFirstName = request.getParameter("FirstName");

    String SUserId = request.getParameter("SUserId");

    String SDepartNo = request.getParameter("SDepartNo");

    String RLastName = request.getParameter("RLastName");

    String RFirstName = request.getParameter("RFirstName");

    String RUserId = request.getParameter("RUserId");

    String RDepartNo = request.getParameter("RDepartNo");

    String REmail = request.getParameter("REmail");

    String Tarea = request.getParameter("Tarea");

    By doing the above, you will definitely get null values. Try
    1. see if checkbox is checked. If it is checked, it returns its value otherwise it returns -1
    2. if checked connect to the database again and retrieve the above information using the value of the checkbox
    3.you can now open the second database and make the second transaction
    Hope this will help.
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    @Paul

    Sorry but I don't understand what are saying?

    Can you help me with any code snippet?

    I just want that when I clicked on check box of table the values of that rows coming from database table 'award' will be added to another database table 'winner'?

    I am alreay getting values from table award now I want to insert particular one in table 'winner' selected by checkbox.
     
    Paul Ngom
    Ranch Hand
    Posts: 355
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    I see that you know how to connect to a database and retrieve a record therefore,
    do this


    and you may change the name checkbox to a more meaningful name why not ruserid?
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Paul,

    I am deleting entry from database table only when string matches the entry with entry of table,

    I am having problem in writing queries. Can you pleases let me know how to write MySQL query with multiple fields in where clause for delete and update?

    here is my code:


    deleteentry.jsp


    error:

    please reply soon.
     
    Paul Ngom
    Ranch Hand
    Posts: 355
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    String sql = "DELETE FROM Awards"+"WHERE SUserID="+suserid+"and RUserId="+ruserid+"and SDepartNo="+sdeptno+"and RDepartNo="+rdeptno;


    Hi Ripal,
    Just provide spaces in your query as follows:
    String sql = "DELETE FROM Awards "+"WHERE SUserID="+suserid+" and RUserId="+ruserid+" and SDepartNo="+sdeptno+" and RDepartNo="+rdeptno;
     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    ... and use PreparedStatement. Dynamically cobbling together SQL queries is just nasty, and opens you up SQL injection attacks.
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Thank you guys.

    Can you let me know how can I write same query for update?
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Paul,

    The query is still not working:



    this is another error for



    This column is in my table with the exact name!!

    there is an error somewhere in concatenating where clause.

    reply soon
     
    Paul Ngom
    Ranch Hand
    Posts: 355
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    "DELETE FROM Awards"+"WHERE SUserID="+suserid+" and RUserId="+ruserid+" and SDepartNo="+sdeptno+" and RDepartNo="+rdeptno;

    You had forgotten to add a space after Awards

     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hello Paul,

    Thanks for your efforts. Appreciated.
    needed spaces after and before + +
    It works for below query:


    Could you let me know how to write query for update:

    This is how I am writing it but it's not working:



    error:
     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    The syntax for UPDATE statements is rather different from the syntax for SELECT statements. Any SQL book or online tutorial will tell you how to do that.
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hi Ulf,

    I have used this way as you said using prepare statementowing me an
    this query is not working

    error:


    Is there any problem with the space in sql query of update with spaces?


     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Are you really executing the statement before setting the parameters?
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Yes I am.

    I don't know.

    I have shown you the error that I am getting.

    Please consider 1st query.

    don't look at the one which is commented. It was for createStatement.

    Could you figure out what;s wrong in the query?
     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    My previous post was my -obviously too subtle- way to point out that you need to set parameters before executing the statement.
     
    Ripal Naik
    Greenhorn
    Posts: 20
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    Hello guys,

    Please help me out with this error:



     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    You need to decide once and for all that you are going to use a PreparedStatement, and then stick to it. Seriously. As I mentioned before, creating SQL manually is error-prone, and here you have introduced the same error you had before (not properly handling varchar columns).

    I have taken the liberty of removing everything from your post that is not relevant to the problem at hand.
     
    Paul Ngom
    Ranch Hand
    Posts: 355
    1
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator

    out.println(suserid);
    out.println(sdeptno);
    out.println(ruserid);
    out.println(rdeptno);
    out.println(slname);
    out.println(sfname);
    out.println(rlname);
    out.println(rfname);
    out.println(area);
    out.println(remail);

    Good morning Ripal,
    Can you post the content of these variables as displayed on the page? Or you can also issue an out.println(sql) just before executing .executeUpdate(sql) so that you can see where the mistake in the query is.
     
    Ulf Dittmer
    Rancher
    Posts: 42969
    73
    • Mark post as helpful
    • send pies
    • Quote
    • Report post to moderator
    There's no need for that. The error message mentions specifically that the problem is in the "where clause", where there's only a single parameter - which is indeed used incorrectly.
     
    • Post Reply
    • Bookmark Topic Watch Topic
    • New Topic