SQL query for updating column with values from a local variable

Question

How can I update my SQL Table column with the value that is stored in a local variable.

In my program I have taken value from the HTML page using the following statement:

String idd=request.getParameter("id");
String report=request.getParameter("rprt");

So now I have to update the value of report in my database table named "ptest" and I am using the following query:

Class.forName("com.mysql.jdbc.Driver"); 
java.sql.Connection con = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/tcs","root","root"); 
Statement st= con.createStatement(); 
ResultSet rs; 
int i=st.executeUpdate("update ptest set result = @reprt where patient_id= 
@idd");


out.println("Successfully Entered");

But the value is not being stored in the database instead NULL is being stored.

I have already seen this question and got no help. Question

Please ignore my mistakes if any in this question as I am new to MYSQL.

idd is used to get the value of patient_id from the html page and reprt is used to get the report from the html page — Piyush Agarwal
– Piyush Agarwal, Commented Jun 29, 2018 at 13:46
@MarkRotteveel I think he started doing that by asking the question — user3059517
– user3059517, Commented Jun 29, 2018 at 14:26
@markg Learning is better done by following a good tutorial or book, than by asking questions like this. — Mark Rotteveel
– Mark Rotteveel, Commented Jun 29, 2018 at 14:33

Farhan Qasim · Accepted Answer · 2018-06-29 14:01:39Z

1

You can use prepared statements in java.

setString or setInt can set different data types into your prepared statements.

The parameter 1, 2 are basically the positions of the question mark. setString(1,report) means that it would set the string report in the 1st question mark in your query.

Hope this code helps you in achieving what you want.

String query = "update ptest set result = ? where patient_id = ?";
PreparedStatement preparedStatement = con.prepareStatement(query);

preparedStatement.setString(1, report);
preparedStatement.setString(2, idd);

preparedStatement.executeUpdate();

edited Jun 29, 2018 at 14:01

answered Jun 29, 2018 at 13:50

Farhan Qasim

9805 silver badges19 bronze badges

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

5 Comments

Piyush Agarwal Over a year ago

how to use this statement as it is showing the following error PreparedStatment cannot be resolved to a type

Farhan Qasim Over a year ago

@PiyushAgarwal please tell what is the error you are getting so I can cater the answer more correctly.

user9420984 Over a year ago

PreparedStatment preparedStatement should be PreparedStatement preparedStatement (Typo in the type declaration)

Piyush Agarwal Over a year ago

@FarhanQasim The error: Can not issue data manipulation statements with executeQuery()

Farhan Qasim Over a year ago

@PiyushAgarwal yes I have already edited it and used executeUpdate. sorry for the mistakes, but it would work correctly now.

score 0 · Accepted Answer · 2018-06-29 14:23:30Z

0

In JDBC, you use ? as placeholders for where you want to inject values into a statement. So you should do something like this ...

Class.forName("com.mysql.jdbc.Driver"); 
java.sql.Connection con = 
DriverManager.getConnection("jdbc:mysql://localhost:3306/tcs","root","root"); 
PreparedStatement st= con.prepareCall("update ptest set result = ? where patient_id= 
?"); 

///now set the params in order
st.setString(1, report);
st.setString(2, idd);
//then execute
st.executeUpdate();

Doing a string concat with the values is dangerous due to sql injection possibilities, so I typically make statement text static and final, and also if your value has a ' in it that could blow up your sql syntax etc. Also, notice the use of executeUpdate rather than query. Hope this helps

edited Jun 29, 2018 at 14:23

answered Jun 29, 2018 at 13:57

user3059517

2 Comments

Piyush Agarwal Over a year ago

there is an error : The method createStatement() in the type Connection is not applicable for the arguments (String)

user3059517 Over a year ago

oops, forgot to change to prepareCall, I changed it so it should be ok now

Collectives™ on Stack Overflow

SQL query for updating column with values from a local variable

2 Answers 2

5 Comments

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related