• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

JSP pagination using MS Sql Server 2008

 
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
 
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.
 
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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?
 
Andreas Mutota
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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
Rancher
Posts: 43081
77
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
Post the error messages, and also the entire queries (with strings filled in) as they are sent to the DB.
 
Bear Bibeault
Sheriff
Posts: 67746
173
Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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
    Posts: 5
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Sheriff
    Posts: 67746
    173
    Mac Mac OS X IntelliJ IDE jQuery TypeScript Java iOS
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
    Posts: 5
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    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
     
    Marshal
    Posts: 28193
    95
    Eclipse IDE Firefox Browser MySQL Database
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Since this post seems almost entirely about databases and SQL, I'm going to move it to the JDBC forum.
     
    Andreas Mutota
    Greenhorn
    Posts: 5
    • Mark post as helpful
    • send pies
      Number of slices to send:
      Optional 'thank-you' note:
    • Quote
    • Report post to moderator
    Thanks , let us hope someone will real resolve pagination JSP and MSSQLL server 2008
     
    Bras cause cancer. And tiny ads:
    a bit of art, as a gift, that will fit in a stocking
    https://gardener-gift.com
    reply
      Bookmark Topic Watch Topic
    • New Topic