how to call stored procedure from postgresql to jsp
i'm using a jdeveloper.. and i'm a newbie of jsp.. i just wanna know how to call a stored procedure in jsp?... This is my function in postgres and i don't know how to call it in jsp...
SELECT * from SP_1('2006-01-11') as ( company_code char(5), branch_category char(5), branch_code char(15), day_sales numeric(19,2), month_to_date numeric(19,2), no_of_transaction int8, month_to_date_py numeric(19,2), year_to_date numeric(19,2), year_to_date_py numeric(19,2), remarks text, time_reported text );
hi again.. i'm using jsp in jdeveloper, i just wanna know how to declare double in jsp because my module is report and i need to get the average daily sales = 4500/ no. of transaction = 3. This is my code.. Where will i put the code in declaring the double and the average daily sales? Thanks in advance..
<%@ page contentType="text/html;charset=windows-1252" import="java.sql.*,Connect.CRMCon"%>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<title>untitled</title>
</head>
<body>
<%
String ave = request.getParameter("ave_daily_sales");
double cashsales = Double.parseDouble(ave);
%>
<%
logparameters data = new logparameters();
data.txtbox = request.getParameter("txtbox");
%>
<table cellspacing="3" cellpadding="2" border="1" width="100%">
<tr>
<td width="5%" bgcolor="#cccccc" height="29">
<DIV align="center">Company Code</DIV>
</td>
<td width="4%" bgcolor="#cccccc" height="29">
<DIV align="center">Branch Category</DIV>
</td>
<td width="6%" bgcolor="#cccccc" height="29">
<DIV align="center">Branch Code</DIV>
</td>
<td width="8%" bgcolor="#cccccc" height="29">
<DIV align="center">Day Sales</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Month to Date</DIV>
</td>
<td width="5%" bgcolor="#cccccc" height="29">
<DIV align="center">No.of Transaction</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Month to Date(PY)</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Ave.Daily Sales to Date</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Year to Date</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Year to Date(PY)</DIV>
</td>
<td width="9%" bgcolor="#cccccc" height="29">
<DIV align="center">Remarks</DIV>
</td>
<td width="5%" bgcolor="#cccccc" height="29">
<DIV align="center">Time Reported</DIV>
</td>
</tr>
<%
try
{
CRMCon ccon = new CRMCon();
Connection con = ccon.getConnection();
String sql = "select * from sp_1('"+data.txtbox+"')";
// String sql = "SELECT * from SP_1('"+ request.getParameter("data.txtbox")+"')" ;
sql += " as (company_code char(5), branch_category char(5) ," ;
sql += "branch_code char(15) , day_sales numeric(19,2)," ;
sql += "month_to_date numeric(19,2), no_of_transaction int8," ;
sql += "month_to_date_py numeric(19,2), year_to_date numeric(19,2)," ;
sql += "year_to_date_py numeric(19,2), remarks text, time_reported text) " ;
//out.println(sql);
Statement st = con.createStatement();
ResultSet rs = st.executeQuery(sql);
while (rs.next())
{
%>
<tr>
<td width="13%"> <%= rs.getString("company_code") %> </td>
<td width="63%"> <%= rs.getString("branch_category") %> </td>
<td width="24%"> <%= rs.getString("branch_code") %> </td>
<td width="24%"> <%= rs.getString("day_sales") %> </td>
<td width="24%"> <%= rs.getString("month_to_date") %> </td>
<td width="24%"> <%= rs.getString("no_of_transaction") %> </td>
<td width="24%"> <%= rs.getString("month_to_date_py") %> </td>
<td width="24%"> <%= rs.getString("year_to_date") %> </td>
<td width="24%"> <%= rs.getString("year_to_date_py") %> </td>
<td width="24%"> <%= rs.getString("remarks") %> </td>
<td width="24%"> <%= rs.getString("time_reported") %> </td>
</tr>
<%
}
}
catch (Exception ex)
{
out.println("Error:"+ex.getMessage());
}
%>
</table>
</body>
</html>
<%!
class logparameters
{
public String txtbox;
}
%>
