Win a copy of Getting started with Java programming language this week in the Beginning Java forum!
  • Post Reply Bookmark Topic Watch Topic
  • New Topic

Retrieve data from database using jdbc  RSS feed

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
i am trying to retrieve the data from the database based on form input .there is no error when compile,debug and run .i shows me a textfield if i type the text and enter submit.,the url changes but the page is empty..there is no error in the console..can anyone tell me the place where it goes wrong

index.jsp



main.jsp

 
Dave Tolls
Ranch Hand
Posts: 2414
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
First off I would suggest removing all that code from the JSP and placing it in a servlet.
Have the servlet forward the result to a JSP page to display it.
That way you at least have a chance of testing this.
I would also suggest logging some things on the server, including the sql statement you have built.
That SQL is also open to SQL injection, but that's not really solvable since you are sending an entire WHERE clause as a parameter.

In any case, as is, are you sure that the correct value is being sent to the server?

I don't see any html element on that first page that has an id of 'cond'.
I see a field on the form that has a name of 'cond', but that's not the same thing.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes you are right. i have to use  getElementByName
here..i have to get the database what i have typed in text field

var id=document.getElementById("cond").value;
 
Dave Tolls
Ranch Hand
Posts: 2414
25
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
There's too much going on with this as it is at the moment.

The first thing I would do is forget the Javascript bit for now, and simply submit the form.
Give the form the URL as its action.

That will mean you can avoid any Javascript errors and see if the code on your server works.

I would still move all the Java code out of the JSP page and into a servlet.  There should be no Java code in your JSP page.
It will make it a lot easier to debug.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thank you...let me try your idea
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Dave Tolls thank you ..i got ..but i want to select all rows value containing same column name ..i used  WHERE IN  but getting only one row related to the INPUT.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
ResultSet resultset =
                statement.executeQuery("select * from t where name LIKE %"+id+"%"); is this correct?
 
Knute Snortum
Sheriff
Posts: 3445
85
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The above SELECT will return rows with the id anywhere in the name.  Is this what you want?  Does it return the rows you're looking for?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
actually i used id there that represent
String id=request.getParameter("input")
i want to get the data which is typed in the text box named "input".
i want to select all rows containing the same NAME 

EG)

TEAM LEADER.         TEAM MEMBERS
jon                                 Hari
Jon.                                deva
Jon.                                 Rahul
Suresh                             Raina
Suresh.                             Raksha

I want to get all information from the rows related to jon
So I used LIKE in MYSQL command
But it gives error..
 
Paul Clapham
Sheriff
Posts: 22106
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
surya preethaaa wrote:But it gives error..


That's great! Now you're getting somewhere.
 
Knute Snortum
Sheriff
Posts: 3445
85
Chrome Eclipse IDE Java Postgres Database VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Could you post the error and all relevant messages?
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
this is what the page shows

HTTP Status 500 - An exception occurred processing JSP page /main.jsp at line 25

type Exception report

message An exception occurred processing JSP page /main.jsp at line 25

description The server encountered an internal error that prevented it from fulfilling this request.

exception

org.apache.jasper.JasperException: An exception occurred processing JSP page /main.jsp at line 25

22:             String id = request.getParameter("name"); 
23:
24:             ResultSet resultset =
25:                 statement.executeQuery("select * from t where LIKE %" + id + "%'");
26:
27:             if(!resultset.next()) {
28:                 out.println("Sorry, could not find . ");


Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:584)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:466)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

javax.servlet.ServletException: com.mysql.jdbc.exceptions.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 'LIKE %அகல�%'' at line 1
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:909)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:838)
org.apache.jsp.main_jsp._jspService(main_jsp.java:188)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
root cause

com.mysql.jdbc.exceptions.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 'LIKE %அகல�%'' at line 1
com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)
com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)
com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)
com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)
com.mysql.jdbc.Connection.execSQL(Connection.java:3277)
com.mysql.jdbc.Connection.execSQL(Connection.java:3206)
com.mysql.jdbc.Statement.executeQuery(Statement.java:1232)
org.apache.jsp.main_jsp._jspService(main_jsp.java:136)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:443)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:385)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:329)
javax.servlet.http.HttpServlet.service(HttpServlet.java:729)
org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
note The full stack trace of the root cause is available in the Apache Tomcat/8.5.9 logs.
 
Paul Clapham
Sheriff
Posts: 22106
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
At least two problems.

First of all you're missing some quotes in your SQL. And you're missing other stuff too. You want your query to look like



But your query looks more like



(Do you really have a table named "t"? That doesn't seem like a very useful name to me.)

And second, your "id" value seems to have a whole bunch of garbage in it, although you seem to be expecting ordinary text. I have no idea where that's coming from, so perhaps examining your code (both front-end and back-end) to make sure no unnecessary transformations are taking place would be a good idea.

 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your reply..
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
on the other side ,

when i call jsp output in the d3
this is how i call jsp in D3
myJSON =  [ (
    <% while (resultset.next()) { %>
        <%= resultset.getString("name") %> ,
              <%= resultset.getString("child name") %>,
                <%= resultset.getString("child color") %> ,
           <%= resultset.getString("children t") %> ,
           <%= resultset.getString("child l") %> ,
          <%= resultset.getString("child child name") %> ,
         <%= resultset.getString("child child color") %>
                                                        
    <% } %>
   ) ];

BUT dont know why it shows error

அகல் ,
              நீங்கு,
                red ,
           நீங்குதல், பாயிருளகல (புறநா.25). ,   ---> in this line as Uncaught SyntaxError: missing ) after argument list
           red ,
          விலகு ,
         red
any suggestions over here?
 
Paul Clapham
Sheriff
Posts: 22106
36
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
You're using that antique version of JSP which has been obsolete for well over 10 years now -- why are you doing that? You ought to be using JSTL and EL, which support SQL a lot better than what you have to do.

But anyway it appears that your JSP is trying to generate a JSON expression. But remember that JSP generates Java, so you're going to have to generate Java code which generates that JSON expression. This bit which I can see at the beginning:



isn't correct Java. Is myJSON a String variable? If so then your Java needs to start like this:



This is typical of the problems that we used to have when generating Java from the antique JSP with scriptlets. Quoting things was a nightmare (and clearly it still is). It's way easier with JSTL, I strongly recommend you rewrite your code to use that.
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
thanks for your reply..i will try with JSTL..I am going to use this for the first time .....i will show the result with jstl
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
hi,i tried with JSTL ..

index.jsp



main.jsp




when i run this with d3.v3.min js   
in index console it shows      Uncaught TypeError: Cannot read property 'diagonal' of undefined 
where main.jsp console is blank..
Is there any wrong in getting jsp result into d3.   (i.e) treeData = "${text}";


when i run this with d3.v4.min
in index console it shows    

Uncaught TypeError: Cannot read property 'prelim' of undefined
    at Function.n [as nodes] (dd.js:4)
    at update (index1.jsp:166)
    at index1.jsp:332

whereas  main.jsp console is blank..

what should i do...???
 
surya preethaaa
Ranch Hand
Posts: 136
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
in main.jsp i fail to  the import file

actually it is

      <script src="http://localhost:8080/nav/v4.js"></script>
           <script src="http://localhost:8080/nav/v44.js"></script>

Please suggest me any idea to overcome this issue
 
  • Post Reply Bookmark Topic Watch Topic
  • New Topic
Boost this thread!