DaniWeb IT Discussion Community

DaniWeb IT Discussion Community (http://www.daniweb.com/forums/)

- JSP (http://www.daniweb.com/forums/forum24.html)

- - SQL Query problem (http://www.daniweb.com/forums/thread122477.html)

SQL Query problem

Dear all,
I filter my query select with parameter from previous page.

SQL = "select a.KodePenyanyi, p.NamaPenyanyi, p.WebSite, a.KodeAlbum, a.NamaAlbum, NamaLabel, Tahun, a.Kaset, a.CD, a.CDImport, a.DVD, DaftarLagu, ReviewAlbum FROM masterpenyanyi p inner join masteralbum a on a.KodePenyanyi=p.KodePenyanyi inner join masterlabel on masterlabel.KodeLabel = a.KodeLabel where a.KodeAlbum='" + kodeAlbum + "'";

Result should be like this :
select a.KodePenyanyi, p.NamaPenyanyi, p.WebSite, a.KodeAlbum, a.NamaAlbum, NamaLabel, Tahun, a.Kaset, a.CD, a.CDImport, a.DVD, DaftarLagu, ReviewAlbum FROM masterpenyanyi p inner join masteralbum a on a.KodePenyanyi=p.KodePenyanyi inner join masterlabel on masterlabel.KodeLabel = a.KodeLabel where a.KodeAlbum='SID-001'

but I dont know why JSP gives this result :
select a.KodePenyanyi, p.NamaPenyanyi, p.WebSite, a.KodeAlbum, a.NamaAlbum, NamaLabel, Tahun, a.Kaset, a.CD, a.CDImport, a.DVD, DaftarLagu, ReviewAlbum FROM masterpenyanyi p inner join masteralbum a on a.KodePenyanyi=p.KodePenyanyi inner join masterlabel on masterlabel.KodeLabel = a.KodeLabel where a.KodeAlbum='"000002-0015"'

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

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

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

<%

        ResultSet rst=null;

        Statement stmt=null;

        Connection cn = null;

        try {            

            Class.forName("com.mysql.jdbc.Driver");                           

            String url;                       

            url = "jdbc:mysql://localhost:3306/tokomusik?user=root&password=root";

            cn = DriverManager.getConnection (url);  

            stmt=cn.createStatement(); 

            String kodeAlbum;

            kodeAlbum = request.getParameter("KodeAlbum");

            String SQL;

            SQL = "select a.KodePenyanyi, p.NamaPenyanyi, p.WebSite,  a.KodeAlbum, a.NamaAlbum, NamaLabel,  Tahun, a.Kaset, a.CD, a.CDImport, a.DVD, DaftarLagu, ReviewAlbum FROM masterpenyanyi p inner join masteralbum a on a.KodePenyanyi=p.KodePenyanyi inner join masterlabel on masterlabel.KodeLabel = a.KodeLabel where a.KodeAlbum='" + kodeAlbum + "'";            rst=stmt.executeQuery(SQL);              

            out.println(SQL);

        }

        catch (SQLException se) 

        {

            out.println(se.toString());

            

        } catch (Exception ex) 

        {

            out.println(ex.toString());            

        }

%>

Re: SQL Query problem

Ouch, do not put database connection in JSP! JSP is for presentation purposes. It is servlet that does logic, which include connection to DB. Also there is nothing worng with JSP (beside that DB connection part) it is your query that is wrong, work correct formula in database and then re-use it in connection

Re: SQL Query problem

Quote:

Originally Posted by peter_budo (Post 599781)

Yes, I know my false. I have read these matter in others threads.
I put connection in JSP because I still don't know how to connect it to servlet.
But my problem is in my query select.

Thanks,

Kusno.

Re: SQL Query problem

> but I dont know why JSP gives this result :

Maybe because "KodeAlbum" actually contains "000002-0015"? It would be better if you printed out the value of the form field "KodeAlbum" after form submission and made sure it is the same as you want. If yes, then the change is happening somewhere in between the request attribute being set and it's use.