ABOUT DB2 Query

guruk2k5 · #1 (**permalink**) 03-23-10, 23:57

Hi,
I am new to DB2. and i have a problem with query execution from JSP.

feed.jsp

<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<!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=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<form action="check_feedback.jsp" method="post">
<table width="475" border="1">
<tr>
<td width="219">NAME:</td>
<td width="240"><input type="text" name="name1"></td>
</tr>
<tr>
<td>AGE:</td>
<td><input type="text" name="age1"></td>
</tr>
<tr>
<td>RNO:</td>
<td><input type="text" name="rno1"></td>
</tr>
<tr>
<td colspan="2"><form name="form1" method="post" action="">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</form></td>
</tr>
</table>
</form>

</body>
</html>

check_feedback.jsp

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%@page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>
<link rel="stylesheet"
href="${pageContext.request.contextPath}/theme/Master.css"
type="text/css">
<title>check_feedback</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta name="GENERATOR" content="Rational Application Developer">
</head>
<body>
<% String temp1=(String) request.getParameter("name1");
String temp2=(String) request.getParameter("age1");
String temp3=(String) request.getParameter("rno1");

try {
// create data source
com.ibm.db2.jcc.DB2SimpleDataSource ds =
new com.ibm.db2.jcc.DB2SimpleDataSource();

// set connection properties
ds.setServerName("localhost");
ds.setPortNumber(50000);
ds.setDatabaseName("example1");
ds.setDriverType(4);

// get connection
java.sql.Connection con = ds.getConnection("gururaj", "sdmcet");

java.sql.Statement stmt = con.createStatement();
java.sql.ResultSet rs = null;
%><table><tr><td>
<%out.println(temp1+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println(temp2+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println(temp3+"\n");%>
</td>
</tr>
</table>
<%
String query="INSERT INTO gururaj.SAMPLE ('NAME',AGE,RNO) VALUES ('"+temp1+"',"+temp2+","+temp3+")";
stmt.executeUpdate(query);

%>

<jsp:forward page="success.jsp"></jsp:forward>
<%
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
out.println("Error "+e.getMessage());

%>

<h1>error</h1>
<%
}

%>

</body>
</html>

i am getting this error

Error DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: GURURAJ.SAMPLE

Plz help me my mail id is guruk2k5@in.com

sathyaram_s · #2 (**permalink**) 03-24-10, 07:24

The table does not exist

muffaddalDBA · #3 (**permalink**) 03-24-10, 08:35

if table exists then check table previledge if its high level
qualifier for - ds.getConnection("gururaj", "sdmcet");

guruk2k5 · #4 (**permalink**) 03-27-10, 06:10

Quote:

Originally Posted by sathyaram_s

The table does not exist

Sir,
The Table exists and i am able to execute query in DB2. but only through JSP i am not getting. the only problem with Recordset object i guess.
Thanks,

guruk2k5 · #5 (**permalink**) 03-27-10, 06:15

Quote:

Originally Posted by muffaddalDBA

if table exists then check table previledge if its high level
qualifier for - ds.getConnection("gururaj", "sdmcet");

i am sending these both files with little bit changes

as per ur suggestion.

One more query, how to check the privilages of table. Plz guide me. Thank you.

feed.jsp
<%@ page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*" errorPage="" %>
<!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=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<form action="check_feedback.jsp" method="post">
<table width="475" border="1">
<tr>
<td width="219">FNAME:</td>
<td width="240"><input type="text" name="name1"></td>
</tr>
<tr>
<td>MNAME:</td>
<td><input type="text" name="age1"></td>
</tr>
<tr>
<td>LNAME:</td>
<td><input type="text" name="rno1"></td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
</table>
</form>

</body>
</html>

check_feedback.jsp

<%@page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>

<title>check_feedback</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta name="GENERATOR" content="Rational Application Developer">
</head>
<body>
<% String temp1=(String) request.getParameter("name1");
String temp2=(String) request.getParameter("age1");
String temp3=(String) request.getParameter("rno1");
int ch=0;

try {
// create data source
com.ibm.db2.jcc.DB2SimpleDataSource ds =
new com.ibm.db2.jcc.DB2SimpleDataSource();

// set connection properties
ds.setServerName("localhost");
ds.setPortNumber(50000);
ds.setDatabaseName("EXAMPLE1");
ds.setDriverType(4);

//String u="jdbc:db2://localhost:50000/EXAMPLE1";

// get connection
java.sql.Connection con=null;
java.sql.Statement stmt = null;// con.createStatement();
try{
con = ds.getConnection("gururaj","sdmcet");
stmt = con.createStatement();
out.print("OK");
// java.sql.Statement stmt = con.createStatement();
ch=1;
} catch(Exception e)
{
out.print(""+e);
out.print("r"+ch);
}
java.sql.ResultSet rs = null;
%><table><tr><td>
<%out.println(temp1+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println(temp2+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println(temp3+"\n");%>
</td>
</tr>
</table>
<%
rs.refreshRow();
rs.moveToCurrentRow();
// rs.moveToInsertRow();

//String query="INSERT INTO gururaj.SAMPLE ('NAME',AGE,RNO) VALUES ("+temp1+","+temp2+","+temp3+")";

//OR

//String query="INSERT INTO gururaj.SAMPLE VALUES('temp1','temp2','temp3')";
String query="INSERT INTO gururaj.SAMPLE VALUES ('"+temp1+"','"+temp2+"','"+temp3+"')";

stmt.executeUpdate(query);

//stmt.executeQuery(query);
%>

<jsp:forward page="success.jsp"></jsp:forward>
<%
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
out.println("Error "+e.getMessage());

%>

<h1>error</h1>
<%
}

%>

</body>
</html>

sathyaram_s · #6 (**permalink**) 03-28-10, 11:01

Quote:

Originally Posted by guruk2k5

Sir,
The Table exists and i am able to execute query in DB2. but only through JSP i am not getting. the only problem with Recordset object i guess.
Thanks,

check if you are connecting to the right database ...

very few messages are as concise and precise as
SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: GURURAJ.SAMPLE

DB2Plus · #7 (**permalink**) 03-28-10, 11:38

Quote:

Originally Posted by guruk2k5

Hi,
I am new to DB2. and i have a problem with query execution from JSP.

// get connection
java.sql.Connection con = ds.getConnection("gururaj", "sdmcet");
String query="INSERT INTO gururaj.SAMPLE ('NAME',AGE,RNO) VALUES ('"+temp1+"',"+temp2+","+temp3+")";
stmt.executeUpdate(query);

i am getting this error

Error DB2 SQL error: SQLCODE: -204, SQLSTATE: 42704, SQLERRMC: GURURAJ.SAMPLE

Plz help me my mail id is guruk2k5@in.com

You made connection to DATABASE but CURRENT SCHEMA could be different.

The best way:

Code:

String query="INSERT INTO SAMPLE ('NAME',AGE,RNO) VALUES ('"+temp1+"',"+temp2+","+temp3+")";
stmt.executeUpdate(query);

Kara+

guruk2k5 · #8 (**permalink**) 04-02-10, 09:28

Now i am not getting any error. But the query is not executing.

<b>feed.jsp</b>

<%@ page contentType="text/html; charset=iso-8859-1" language="java" errorPage="" %>
<!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=iso-8859-1">
<title>Untitled Document</title>
</head>

<body>
<form action="check_feedback.jsp" method="post">
<table width="475" border="1">
<tr>
<td width="219">FNAME:</td>
<td width="240"><input type="text" name="fname"></td>
</tr>
<tr>
<td>MNAME:</td>
<td><input type="text" name="mname"></td>
</tr>
<tr>
<td>LNAME:</td>
<td><input type="text" name="lname"></td>
</tr>
<tr>
<td colspan="2">
<div align="center">
<input type="submit" name="Submit" value="Submit">
</div>
</td>
</tr>
</table>
</form>

</body>
</html>

<b>check_feedback.jsp</b>

<%@page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<html>
<head>

<title>check_feedback</title>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<meta name="GENERATOR" content="Rational Application Developer">
</head>
<body>
<% String temp1=(String) request.getParameter("name1");
String temp2=(String) request.getParameter("age1");
String temp3=(String) request.getParameter("rno1");
int ch=0;

try {
// create data source
com.ibm.db2.jcc.DB2SimpleDataSource ds =
new com.ibm.db2.jcc.DB2SimpleDataSource();

// set connection properties
ds.setServerName("localhost");
ds.setPortNumber(50000);
ds.setDatabaseName("cop");
ds.setDriverType(4);

//String u="jdbc:db2://localhost:1527/EXAMPLE1";

// get connection
java.sql.Connection con=null;
java.sql.Statement stmt = null;// con.createStatement();
try{
con = ds.getConnection("gururaj","sdmcet");
stmt = con.createStatement();
out.print("STATEMENT CREATED");

// java.sql.Statement stmt = con.createStatement();
ch=1;
} catch(Exception e)
{
out.print("STATEMENT CAN'T CREATED"+e);
out.print("r"+ch);
}
java.sql.ResultSet rs = null;
%><table><tr><td>
<%out.println("FNAME");%>
</td>
<td>
<%out.println(temp1+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println("MNAME");%>
</td>
<td>
<%out.println(temp2+"\n");%>
</td>
</tr>
<tr>
<td>
<%out.println("LNAME");%>
</td>
<td>
<%out.println(temp3+"\n");%>
</td>
</tr>
</table>
<%
//rs.refreshRow();
//rs.moveToCurrentRow();
// rs.moveToInsertRow();

//String query="INSERT INTO gururaj.SAMPLE ('NAME',AGE,RNO) VALUES ("+temp1+","+temp2+","+temp3+")";

//OR('FNAME','MNAME','LNAME')('FNAME','MNAME','LNAME ')

//String query="INSERT INTO gururaj.SAMPLE VALUES('temp1','temp2','temp3')";
rs.afterLast();

// rs.moveToCurrentRow();
rs.insertRow();
String query="INSERT INTO COP.NAME VALUES ('"+temp1+"','"+temp2+"','"+temp3+"')";

stmt.executeUpdate(query);
// rs.insertRow();

%>

<jsp:forward page="success.jsp"></jsp:forward>
<%
rs.close();
stmt.close();
con.close();
}
catch(Exception e)
{
out.println("INSERT FAILED"+e.getMessage());
//out.println("INSERT FAILED"+e.detailMessage);
%>

<h1>error</h1>
<%
}

%>

</body>
</html>

INPUT

FNAME: ggg
MNAME: ggg
LNAME: ggg

OUTPUT

STATEMENT CREATED
FNAME ggg
MNAME ggg
LNAME ggg
INSERT FAILEDnull
error

Internet Services

Web Development

Digital Marketing

Consumer Tech