Please help with JSP mysql update query

Question

cacorat 0 Newbie Poster

19 Years Ago

I keep getting an error where the symbol cannot be resolved and the arrow pointing to where i have written statement.ExcecuteUpdate
Am not able to insert any data into my database, can anyone see where i may have gone wrong. thanx
heres the code:

<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>

<%
String connectionURL = "jdbc:mysql://localhost:3306/learningtest";
Connection connection = null;
Statement statement = null;
int UQ=0;
%>

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
"http://www.w3.org/TR/html4/loose.dtd">

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
<title>Submit Question</title>
</head>
<body>

<h1>Submit Question</h1>

<%
String questionin = request.getParameter("questionin");
String answerin = request.getParameter("answerin");
String subjectin = request.getParameter("subjectin");%>

<td align="right">Question:</td>
<td><%=questionin %></td>
<br>
<td align="right">Answer:</td>
<td><%=answerin %></td>
<br>
<td align="right">Subject:</td>
<td><%=subjectin %></td>

<%
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "", "");
statement = connection.createStatement();

UQ=statement.executeUpdate("INSERT INTO questions" +
"(qid, question, answer, subject)" + "VALUES (" ," + questionin + "," + answerin + "," + subjectin)" );

out.println("Question Added Success");
statement.close();

%>

</body>
</html>

p.s. the excecutequery works fine i can read from the database so there isnt anyprobs with the connection string

java-jsp mysql

9 Contributors
13 Replies
351 Views
2 Years Discussion Span
Latest Post 16 Years Ago Latest Post by masijade

yup 0 Newbie Poster

18 Years Ago

Hi,
I think your Html missing some part. Could you please clarify that what is your form doing? Get the question + answer + subject from user... then update to database?

kind regards,
yup

dnanetwork 18 Practically a Master Poster

16 Years Ago

In the following examples, please substitute your information where the following data is referenced:

<server>: enter the MySQL server that you are assigned to, for example, mysql4.safesecureweb.com
<username>: enter the username provided for your database
<password>: enter the password provided for your database
<database>: enter the database name provided for your database
<DSN>: enter the DSN name (ColdFusion only)
PHP
<?php
$link = mysql_connect('<server>', '<username>', '<password>');
if (!$link) {
die('Could not connect: ' . mysql_error());
}
echo 'Connected successfully';
mysql_select_db(<database>);
?>

ColdFusion
<CFQUERY Name="test" DATASOURCE="<DSN>" USERNAME="<username>" PASSWORD="<password>">
</CFQUERY>

Perl
#!/usr/bin/perl

use DBI;

$db = DBI->connect("dbi:mysql:<database>","<username>","<password>")
or die("Couldn't connect");

$db->disconnect;

JSP
<%@ page import="java.sql.*" %>
<%@ page import="com.mysql.jdbc.Driver" %>

<%!
Class.forName("com.mysql.jdbc.Driver").newInstance();
java.sql.Connection conn;
conn = DriverManager.getConnection(
"jdbc:mysql://<server>/<database>?user=<username>&password=<password>");
%>

jwenting 1,889 duckman

16 Years Ago

Kid, check the dates on threads you're replying to. This one was resolved well over 2 years ago.

But do tell, is it a new sport somewhere to create forum accounts just to post nonsense to age old threads?
I've been seeing it a lot lately.

masijade 1,351 Industrious Poster

16 Years Ago

Javascript is amazing, I didn't think you could touch mysql with it!
Everyone kept telling me to use php with my js to get into sql.
HAHA I feel like a million bucks!

Except that this isn't JavaScript.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

cacorat 0 Newbie Poster · Answer 1 · 2005-12-08T03:59:52+00:00

this is the error message
: method executeUpdate(java.lang.String, java.lang.String) not found in interface java.sql.Statement

it seems there is a declaration problem. I am using the com.mysql, and the function seems to be present

cacorat 0 Newbie Poster · Answer 2 · 2005-12-08T06:37:15+00:00

sorry this is the correct code:

<%@ page import="java.sql.*" %>
<%@ page import="java.util.*" %>

<%
String connectionURL = "jdbc:mysql://localhost:3306/learningtest";
Connection connection = null;
Statement statement = null;

%>

<%@page contentType="text/html"%>
<%@page pageEncoding="UTF-8"%>


<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">

<html>
    <head>
        <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
        <title>Submit Question</title>
    </head>
    <body>

    <h1>Submit Question</h1>

  <%
String questionin = request.getParameter("questionin");
String answerin = request.getParameter("answerin");
String subjectin = request.getParameter("subjectin");%>

<td align="right">Question:</td>
<td><%=questionin %></td>
<br>
<td align="right">Answer:</td>
<td><%=answerin %></td>
<br>
<td align="right">Subject:</td>
<td><%=subjectin %></td>


  <%
Class.forName("com.mysql.jdbc.Driver").newInstance();
connection = DriverManager.getConnection(connectionURL, "", "");
statement = connection.createStatement();


int UQ=statement.executeUpdate("insert into questions (qid, question, answer, subject) values ('," + questionin + "," + answerin + "," + subjectin + ")"); 


if (UQ==1)
{
out.println("Question Added Success");
}
else
{
    out.println("Addition failed");
}

statement.close();


%>


    </body>
</html>

it runs except i get an sql error where the syntax is incorrect at the values being inserted...pls help

cacorat 0 Newbie Poster · Answer 3 · 2005-12-08T06:41:33+00:00

the error message:
java.sql.SQLException: 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 '',ooo,ppp,Chemistry)' at line 1

brambo71c 0 Newbie Poster · Answer 4 · 2006-01-10T14:20:39+00:00

the error message:
java.sql.SQLException: 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 '',ooo,ppp,Chemistry)' at line 1

The problem is in the query brackets: ('" + blabla + "','" + blabla + "','" + blabla + "')" letting the output like ('xxx','yyy','zzz')

weasy 0 Newbie Poster · Answer 5 · 2008-05-24T18:56:38+00:00

Javascript is amazing, I didn't think you could touch mysql with it!
Everyone kept telling me to use php with my js to get into sql.
HAHA I feel like a million bucks!

jwenting 1,889 duckman Team Colleague · Answer 6 · 2008-05-25T11:23:47+00:00

in fact its only commonality with Javascript is that it's a mess (which it happens to have in common with php as well).

~s.o.s~ 2,560 Failure as a human Team Colleague Featured Poster · Answer 7 · 2008-05-26T23:53:25+00:00

> Javascript is amazing, I didn't think you could touch mysql with it!

You can't, at least not with browser embedded Javascript. It's a different ball game altogether if you are using Server Side Javascript.

articles 0 Newbie Poster · Answer 8 · 2008-05-28T18:10:18+00:00

The code you have written is correct... but java is a case sensitive language so might be there is some problem with the case..

check it carefully

masijade 1,351 Industrious Poster Team Colleague Featured Poster · Answer 9 · 2008-05-28T18:21:48+00:00

The code you have written is correct... but java is a case sensitive language so might be there is some problem with the case..
check it carefully

To tell you the truth, the code is not correct. This line

int UQ=statement.executeUpdate("insert into questions (qid, question, answer, subject) values ('," + questionin + "," + answerin + "," + subjectin + ")");

is definately false (and the cause of the sql error, as noted earlier). Where is the closing quote (') after the variable questionin? And why are there no quotes around any of the others?

All this would not be a problem if the OP would two things that she should always do, and should have done from the beginning (but she probably learned from tutorials on roseindia, they love pushing this dope), which are:

1) Use a PreparedStatement. Do not cobble together a Statement like this. It is error prone in the coding, error prone in the execution (what happens if one of the variables contains a quote (') itself), and wide open to SQL injection attacks.

2) Don't use scriplets. They only still exist for backwards compatability and it is strongly reccommended not to use them anymore.