aspose file tools*
The moose likes JDBC and the fly likes JSP pagination using  MS Sql Server 2008 Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JSP pagination using  MS Sql Server 2008" Watch "JSP pagination using  MS Sql Server 2008" New topic
Author

JSP pagination using MS Sql Server 2008

Andreas Mutota
Greenhorn

Joined: Dec 13, 2012
Posts: 5
I am new to JPS. I am trying to paginate some records in MS SQL Server 2008 database, but not luck

Here is a portion of the code used mysql database:

"
String sqlPgintn=" SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
//String sqlPgintn=" SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
psPgintn=con.prepareStatement(sqlPgintn);
rsPgin=psPgintn.executeQuery();
// Count total number of fetched rows
String sqlRwCnt="SELECT FOUND_ROWS() as cnt";
psRwCn=con.prepareStatement(sqlRwCnt);
rsRwCn=psRwCn.executeQuery();
"

It seems like MS SQL Server uses some functions which I do not know. Please help.



Here is the whole script which can run and see the error.

=============================

<%@ page language="java" contentType="text/html; charset=ISO-8859-1" %>
<%@ page import="java.sql.PreparedStatement" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.Connection" %>
<%@ page import="java.sql.DriverManager" %>
<%--
The taglib directive below imports the JSTL library. If you uncomment it,
you must also add the JSTL library to the project. The Add Library... action
on Libraries node in Projects view can be used to add the JSTL 1.1 library.
--%>
<%--
<%@taglib uri="http://java.sun.com/jsp/jstl/core" prefix="c"%>
--%>

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

<%!
public int Converter(String str)
{
int convrtr=0;
if(str==null)
{
str="0";
}
else if((str.trim()).equals("null"))
{
str="0";
}
else if(str.equals(""))
{
str="0";
}
try{
convrtr=Integer.parseInt(str);
}
catch(Exception e)
{
}
return convrtr;
}
%>
<%
Connection con = null;

Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
//STEP 2: create a connection url -
String userName = "";
String password = "";
String url = "jdbc:odbc:TestCon";
/*
* STEP 3: establish connection to the database -
*/
/*
* Declares a database connection object
*/
con = DriverManager.getConnection(url,"sa", "Password1");
/*
* STEP 4: /*Now declare a statement object
*/
// stmt = con.createStatement();
ResultSet rsPgin = null;
ResultSet rsRwCn = null;
PreparedStatement psPgintn=null;
PreparedStatement psRwCn=null;

// Number of records displayed on each page
int iSwRws=5;
// Number of pages index displayed
int iTotSrhRcrds=10;

int iTotRslts=Converter(request.getParameter("iTotRslts"));
int iTotPags=Converter(request.getParameter("iTotPags"));
int iPagNo=Converter(request.getParameter("iPagNo"));
int cPagNo=Converter(request.getParameter("cPagNo"));

int iStRsNo=0;
int iEnRsNo=0;

if(iPagNo==0)
{
iPagNo=0;
}
else{
iPagNo=Math.abs((iPagNo-1)*iSwRws);
}

String sqlPgintn=" SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
//String sqlPgintn=" SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
psPgintn=con.prepareStatement(sqlPgintn);
rsPgin=psPgintn.executeQuery();
// Count total number of fetched rows
String sqlRwCnt="SELECT FOUND_ROWS() as cnt";
psRwCn=con.prepareStatement(sqlRwCnt);
rsRwCn=psRwCn.executeQuery();

if(rsRwCn.next())
{
iTotRslts=rsRwCn.getInt("cnt");
}
%>
<html>
<head>
<title>Pagination using JSP page</title>
</head>
<body>
<form name="frm">
<input type="hidden" name="iPagNo" value="<%=iPagNo%>">
<input type="hidden" name="cPagNo" value="<%=cPagNo%>">
<input type="hidden" name="iSwRws" value="<%=iSwRws%>">

<table width="100%" cellpadding="0" cellspacing="0" border="0" >
<tr>
<td>Emp Name</td>
<td>Emp Batch</td>
<td>Emp Address</td>
</tr>
<%
while(rsPgin.next())
{
%>
<tr>
<td><%=rsPgin.getString("Countryname")%></td>
<td><%=rsPgin.getString("CountryId")%></td>
<td><%=rsPgin.getString("CountryAbr")%></td>
</tr>
<%
}
%>
<%
// Calculate next record start and end position
try{
if(iTotRslts<(iPagNo+iSwRws))
{
iEnRsNo=iTotRslts;
}
else
{
iEnRsNo=(iPagNo+iSwRws);
}

iStRsNo=(iPagNo+1);
iTotPags=((int)(Math.ceil((double)iTotRslts/iSwRws)));
}
catch(Exception e)
{
e.printStackTrace();
}
%>
<tr>
<td colspan="3">
<div>
<%
// Create index of pages
int i=0;
int cPge=0;
if(iTotRslts!=0)
{
cPge=((int)(Math.ceil((double)iEnRsNo/(iTotSrhRcrds*iSwRws))));
int prePageNo=(cPge*iTotSrhRcrds)-((iTotSrhRcrds-1)+iTotSrhRcrds);
if((cPge*iTotSrhRcrds)-(iTotSrhRcrds)>0)
{
%>
<a href="index.jsp?iPagNo=<%=prePageNo%>&cPagNo=<%=prePageNo%>"><< Previous</a>
<%
}

for(i=((cPge*iTotSrhRcrds)-(iTotSrhRcrds-1));i<=(cPge*iTotSrhRcrds);i++)
{
if(i==((iPagNo/iSwRws)+1))
{
%>
<a href="index.jsp?iPagNo=<%=i%>" style="cursor:pointer;color:red"><b><%=i%></b></a>
<%
}
else if(i<=iTotPags)
{
%>
<a href="index.jsp?iPagNo=<%=i%>"><%=i%></a>
<%
}
}

if(iTotPags>iTotSrhRcrds&& i<iTotPags)
{
%>
<a href="index.jsp?iPagNo=<%=i%>&cPagNo=<%=i%>">>> Next</a>
<%
}
}
%>
<b>Rows <%=iStRsNo%> - <%=iEnRsNo%> Total Result <%=iTotRslts%></b>
</div>
</td>
</tr>
</table>
</form>
</body>
</html>
<%
try{
if(psPgintn!=null){
psPgintn.close();
}
if(rsPgin!=null){
rsPgin.close();
}
if(psRwCn!=null){
psRwCn.close();
}
if(rsRwCn!=null){
rsRwCn.close();
}
if(con!=null){
con.close();
}
}
catch(Exception e)
{
e.printStackTrace();
}
%>


Thanks in advance.

Andreas
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61226
    
  66

Welcome to the Ranch!

As a newcomer to JSP (not JPS) you should be aware that putting Java code into a JSP is not a good start. Java scriptlets (code in a JSP) was obsoleted over 12 years ago with the introduction of the EL and JSTL with JSP 2.0.

If you are learning scriptlets in order to support legacy applications that still use them, that's one thing. If you are learning them in order to use them in new code, then you are heading down the wrong path.

Please find study material that covers modern JSP, which means, no Java code in the pages.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41884
    
  63
What does "no luck" mean? What is or is not happening when you execute the code, and how is that different from what you were expecting?


Ping & DNS - my free Android networking tools app
Andreas Mutota
Greenhorn

Joined: Dec 13, 2012
Posts: 5

It is not for legacy system. I must real get down with possible new technologies. I will look into these EL and JSTL with JSP. Thanks,

"No luck", means , not working at all.

I am getting an error like MS Sql server does not recognize these line:

String sqlPgintn="SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
.
.
.
String sqlRwCnt="SELECT FOUND_ROWS() as cnt";
.
.
.
Thanks again
Ulf Dittmer
Marshal

Joined: Mar 22, 2005
Posts: 41884
    
  63
Post the error messages, and also the entire queries (with strings filled in) as they are sent to the DB.
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 61226
    
  66

If this is not for a legacy system, then your first step is to move all that data access code to its own Java class. Not only will it improve the structure of the web app, it will make it easier to test.

You should also be using a Prepared statement for your DB query. What you have there is open to SQL injection attacks.

I recommend newcomers to JSP read the following:
  • The Secret Life of JSPs
  • The Front Man

  • The latter might be a bit confusing at first, but it's really important to know how to properly structure Java web apps.
    Andreas Mutota
    Greenhorn

    Joined: Dec 13, 2012
    Posts: 5
    Here is error messages:

    "
    type Exception report

    message

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

    exception

    javax.servlet.ServletException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.
    org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:848)
    org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:781)
    org.apache.jsp.index_jsp._jspService(index_jsp.java:318)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)

    root cause

    java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'.
    sun.jdbc.odbc.JdbcOdbc.createSQLException(JdbcOdbc.java:6958)
    sun.jdbc.odbc.JdbcOdbc.standardError(JdbcOdbc.java:7115)
    sun.jdbc.odbc.JdbcOdbc.SQLExecute(JdbcOdbc.java:3150)
    sun.jdbc.odbc.JdbcOdbcPreparedStatement.execute(JdbcOdbcPreparedStatement.java:214)
    sun.jdbc.odbc.JdbcOdbcPreparedStatement.executeQuery(JdbcOdbcPreparedStatement.java:89)
    org.apache.jsp.index_jsp._jspService(index_jsp.java:131)
    org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:97)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:322)
    org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:291)
    org.apache.jasper.servlet.JspServlet.service(JspServlet.java:241)
    javax.servlet.http.HttpServlet.service(HttpServlet.java:802)
    org.netbeans.modules.web.monitor.server.MonitorFilter.doFilter(MonitorFilter.java:362)

    note The full stack trace of the root cause is available in the Apache Tomcat/5.5.9 logs.
    "

    and the query as it follows:
    "
    String sqlPgintn="SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
    //String sqlPgintn=" SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPagNo+","+iSwRws+"";
    psPgintn=con.prepareStatement(sqlPgintn);
    rsPgin=psPgintn.executeQuery();
    // Count total number of fetched rows
    String sqlRwCnt="SELECT FOUND_ROWS() as cnt";
    psRwCn=con.prepareStatement(sqlRwCnt);
    rsRwCn=psRwCn.executeQuery();
    "

    Thanks in advance
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61226
        
      66

    No, that's the markup. It is not the final SQL.

    In any case, the SQL is malformed, check the syntax carefully.

    And again: move the code to a Java class, and use a PreparedStatement.

    If you have continued difficulties with the SQL syntax, I can move this topic to the JDBC forum as SQL syntax has nothing at all to do with JSP (and as said, should not even be in a JSP).
    Bear Bibeault
    Author and ninkuma
    Marshal

    Joined: Jan 10, 2002
    Posts: 61226
        
      66

    P.S. You should be testing your SQL statements using a SQL tool such as Squirrel SQL or DbVisualizer. Testing it in the code is time-consuming. Determine what the correct syntax is first; then write the code.
    Andreas Mutota
    Greenhorn

    Joined: Dec 13, 2012
    Posts: 5
    Was JSP ever used to generate paginated records using MSSQL Server database?

    The main purpose is to have an idea how such records using JSP and MSSQL Server 2008 database. The only function I can see now used by someone using mysql database and work fine is this
    “SELECT SQL_CALC_FOUND_ROWS * FROM Country” . When I am trying that in Server 2008, I get this massage=
    [Microsoft][ODBC SQL Server Driver][SQL Server]Incorrect syntax near the keyword 'FROM'

    The MYSQL database understands the following functions:
    SQL_CALC_FOUND_ROWS and
    FOUND_ROWS().
    Which functions does the MSSQL Server 2008 database understands instead?

    Sample Code:

    String sqlPagination="SELECT SQL_CALC_FOUND_ROWS * FROM Country limit "+iPageNo+","+iShowRows+"";
    psPagination=conn.prepareStatement(sqlPagination);
    rsPagination=psPagination.executeQuery();
    //// this will count total number of rows
    String sqlRowCnt="SELECT FOUND_ROWS() as cnt";
    psRowCnt=conn.prepareStatement(sqlRowCnt);
    rsRowCnt=psRowCnt.executeQuery();

    Thanks in Advance
    Paul Clapham
    Bartender

    Joined: Oct 14, 2005
    Posts: 18570
        
        8

    Since this post seems almost entirely about databases and SQL, I'm going to move it to the JDBC forum.
    Andreas Mutota
    Greenhorn

    Joined: Dec 13, 2012
    Posts: 5
    Thanks , let us hope someone will real resolve pagination JSP and MSSQLL server 2008
     
    I agree. Here's the link: http://aspose.com/file-tools
     
    subject: JSP pagination using MS Sql Server 2008