Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc

Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer 2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, ( which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I missing something?
Thanks in advance
How exactly are you populating the resultset? Do you see the same behavior
from a standalone JDBC program? Below is a code snippet that connects to
Northwind database. It retrieves the data without skipping the first row:
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
con = java.sql.DriverManager.getConnection(url, userName, password);
String query = "SELECT * from Customers";
//Option 1
PreparedStatement stmt = con.prepareStatement(query);
ResultSet rs = stmt.executeQuery();
//Option 2
//Statement stmt = con.createStatement();
//ResultSet rs = stmt.executeQuery(query);
while(rs.next())
{
System.out.println("Contact Name: " + rs.getString("ContactName"));
}
rs.close();
stmt.close();
con.close();
con=null;
Give this code a try to see if the behavior is the same. Do you see the
same behavior with JDBC SP2?
Carb Simien, MCSE MCDBA MCAD
Microsoft Developer Support - Web Data
Please reply only to the newsgroups.
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection
Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
| Thread-Topic: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| thread-index: AcQiWoPDPDw+LtmiRSmrZYKbSsks1w==
| X-WN-Post: microsoft.public.sqlserver.jdbcdriver
| From: "=?Utf-8?B?U3VtYUpEQkM=?=" <anonymous@.discussions.microsoft.com>
| Subject: Reg. MS SLQSRVER 2000 driver (sp 1) driver for jdbc
| Date: Wed, 14 Apr 2004 12:56:02 -0700
| Lines: 42
| Message-ID: <F9963AEF-09BA-428A-B5C9-12505D15AE9F@.microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="Utf-8"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| Content-Class: urn:content-classes:message
| Importance: normal
| Priority: normal
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.0
| Newsgroups: microsoft.public.sqlserver.jdbcdriver
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.jdbcdriver:5891
| NNTP-Posting-Host: tk2msftcmty1.phx.gbl 10.40.1.180
| X-Tomcat-NG: microsoft.public.sqlserver.jdbcdriver
|
| Hi
I am writing jsp pages on JRUN (DM2k MX) server connecting to MS SQLServer
2000 dbase. using
MS SQLServer 2000 driver for JDBC (SP 1).
Everything is working fine, except when I try to access the result set, (
which is storing my query results),
I have a JSP scriptlet as below
//successfully connect a
// exceutet the query a
// store the result in ResultSet rs
<%
while (rs.next())
{
%><option value=<%=rs.getString("DeptName")%></option><%
}
%>
Each time this code runs, it skips the first record. I tried placing the
cursor before the first record , prior to entering the
while loop, by
1) rs.beforeFirst()
or
2)
if (rs.getRow() == 2)
{
rs.absolute(1);
//rs.first();
}
which should force it to goto the first record. but botjh 1 & 2 failed and
the error was
MS SQLServer 2000 driver for jdbc do not support beforeFirst / First
How do I get around this problem? Is this a bug in the driver or am I
missing something?
Thanks in advance
|
sql

Reg JDBC MS sQLSERVer driver SP2

Hi
Has anyone used ResultSet.first(), ResultSet.beforeFirst(), ResultSet.AfterLast(), ResultSet.absolute(1) methods
in your JSP code, while connecting to SQL Server 2000, using MS SQLSERVER JDBC driver SP2 ?
I am hving problems with it., as it keeps throwing the msg, SQLSERVER 2000 driver for JDBC - unsupported mehod error for all the above calls. I am using JRUN server on DM2k MX to run my JSP.
I would appreciate your replies,
Thanks
Suma
"SumaJDBC" <anonymous@.discussions.microsoft.com> wrote in message
news:015E27EC-8BFC-49B6-96FD-669661A86912@.microsoft.com...
> Hi
> Has anyone used ResultSet.first(), ResultSet.beforeFirst(),
ResultSet.AfterLast(), ResultSet.absolute(1) methods
> in your JSP code, while connecting to SQL Server 2000, using MS SQLSERVER
JDBC driver SP2 ?
> I am hving problems with it., as it keeps throwing the msg, SQLSERVER 2000
driver for JDBC - unsupported mehod error for all the above calls. I am
using JRUN server on DM2k MX to run my JSP.
> I would appreciate your replies,
> Thanks
> Suma
What do you not understand about the error message? The calls are not
supported by the driver. In general it is best to stay away from them
anyway, simply stick to next(). It will give you optimal performance and
works on all JDBC drivers.
Silvio Bierman
|||Hi silvio
Thanks for your reply. I did use next(), but I am having wierd problem with that . I have to construct a dropdown box
, populating it from the database table values.
I connect to the DB, retrieve the required column values into the result set rs.
As I display the values , the first record is always being skipped and it is always picking up from second record onwards. Now can now U understand why I need to place rs.beforeFirst(), prior to the starting of that while loop!
or is there any other way of doing this? I am new to this , thus would appreciate an expert advice!
here's my scriptlet after (connection to the Db and populatinf the ResultSet rs (using a javabean (db) class for this )
try{
//rs.beforeFirst();
while(rs.next()) {
%><option value=<%=rs.getString("DeptName")%></option><BR><%
}
} catch (Exception e) {
msg = e.getMessage();
out.println(msg);
}
%><% db.close();%></select>
|||"SumaJDBC" <anonymous@.discussions.microsoft.com> wrote in message
news:3D950D2F-8ACE-468E-97AD-A6D33DDCC897@.microsoft.com...
> Hi silvio
> Thanks for your reply. I did use next(), but I am having wierd problem
with that . I have to construct a dropdown box
> , populating it from the database table values.
> I connect to the DB, retrieve the required column values into the result
set rs.
> As I display the values , the first record is always being skipped and it
is always picking up from second record onwards. Now can now U understand
why I need to place rs.beforeFirst(), prior to the starting of that while
loop!
> or is there any other way of doing this? I am new to this , thus would
appreciate an expert advice!
> here's my scriptlet after (connection to the Db and populatinf the
ResultSet rs (using a javabean (db) class for this )
> try{
> //rs.beforeFirst();
> while(rs.next()) {
>
> %><option value=<%=rs.getString("DeptName")%></option><BR><%
> }
> } catch (Exception e) {
> msg = e.getMessage();
> out.println(msg);
> }
> %><% db.close(); %></select>
I have heard that problem previously several times and each time it was an
error in the application code that caused the behaviour. Using
ResultSet rs = ...;
while (rs.next()) //process current row
should work always. If it doesn't something is seriously broken and the
methods you mention can;t help you. I would suggest you post some actual
code that produces the behaviour.
Silvio Bierman
|||Hi Silvio,
As U suggested I tried to look into my code for possible logical errors, but couldnt find any,. So I am posting my code below:
Thanks in advance (again) for taking time to answer my queries.
Suma
// This is DbConnection.java, the bean class for getting the DBconnection and excecuting DB related methods
package BeanClasses;
import java.sql.*;
import java.io.*;
import java.lang.*;
public class DbConnection{
String dbURL = "jdbc:microsoft:sqlserver://sql1;databaseName=DEVPurContr";
String dbDriver = "com.microsoft.jdbc.sqlserver.SQLServerDriver" ;
private Connection dbCon;
public String temp;
public String getTemp() { return temp;
}
public void setTemp (String Temp){
this.temp = Temp;
}
public DbConnection() {
super();
/*try {
connect();
} catch (Exception e) { }*/
}
public boolean connect () throws Exception { //This functionis called first get a valid connection
try{
Class.forName("com.microsoft.jdbc.sqlserver.SQLSer verDriver");
dbCon = DriverManager.getConnection(dbURL,"webuser","webus er3070");
}
catch (Exception e) { System.out.println(e.getMessage());
}
return true;
}
public void close() throws SQLException {
dbCon.close();
}
public ResultSet execSQL (String sql) throws SQLException { //This function is called to retrive the result set
// String (sql) is supplied at the run time, which is my select statement and the returned
// result set.
Statement s = dbCon.createStatement();
ResultSet r = s.executeQuery(sql);
return (r==null) ? null : r;
}
public int execUpdate (String sql ) throws SQLException {
Statement s = dbCon.createStatement ();
int r = s.executeUpdate (sql);
return (r==0) ? -1 : r ;
}
}
// Here's my import , where I am importing relevant java packages
<%@. page contentType="text/html; charset=iso-8859-1" language="java" import="java.sql.*,java.util.*,java.lang.*,java.io .*" errorPage="" %><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><html><head>
// Heres's JSP code (relevant part )
<tr><td class="RequiredDetail" style="text-align:left;"><span style="color:white; font-size:10px; font-weight:bolder"></span>
Enter Department Name
</td><td style="text-align:right;"><select name="DeptName"><%
ResultSet rs = null;
int i;
%><%
String msg;
db.connect(); // Call to the bean
try {
rs = db.execSQL("SELECT distinct DeptName FROM tblDepartment order by DeptName" ); // call to the bean
} catch(Exception e) {
msg = e.getMessage();
}
try{
//rs.beforeFirst();
while(rs.next()) {
out.println(rs.getString("DeptName"));
%><option value=<%=rs.getString("DeptName")%></option><BR><%
}
} catch (Exception e) {
msg = e.getMessage();
out.println(msg);
}
%><% db.close();%></select></td></tr>
|||As far is I can tell from this code there is no reason for the strange
behaviour. Have you tried running it in a normal application instead of a
JSP page (I have little experience with JSPs)? Have you compared such
results with running the same query from the Query Analyzer?
Silvio Bierman
|||Hi
Yes. I have run the query (thru JDBC) on regular java compiler. I have also done it on sql analyzer. In both cases the behavior is as exepected. But in JSP, when I use MS SQL Server JDBC driver. I get this odd results.
I still fail to understand why MS JDBC driver fails to support ResultSet.beforeFirst () and ResultSet.afterLast() functions?
Suma
|||te cuento que tengo el mismo problema con ResultSet.absolute(1) methods in your JSP code, while connecting to SQL Server 2000, using MS SQLSERVER JDBC driver SP2 ?
pero se logra solucionar con el drive JnetDirect pero tienes que comprarlo yo se que esa no es la idea por favor ayudame si hallas la solucion
Gracias
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||trivadeneira@.olade.org.ec wrote:

> te cuento que tengo el mismo problema con ResultSet.absolute(1) methods in your JSP code, while connecting to SQL Server 2000, using MS SQLSERVER JDBC driver SP2 ?
> pero se logra solucionar con el drive JnetDirect pero tienes que comprarlo yo se que esa no es la idea por favor ayudame si hallas la solucion
>
> Gracias
Hola. Si un mtodo falla con el MS jdbc conductor libre,
usted debe esperar o antes de que ellos liberen a su
siguiente conductor, y esperen que sea fijado entonces,
o usted puede usar un producto comercial.
El DataDirect es el negocio que realmente hace el
JDBC conductor de MS., entonces usted debera intentar su
conductor. Esto mostrar si el siguiente conductor de
los MS ser fijado.

>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||trivadeneira@.olade.org.ec wrote:

> te cuento que tengo el mismo problema con ResultSet.absolute(1) methods in your JSP code, while connecting to SQL Server 2000, using MS SQLSERVER JDBC driver SP2 ?
> pero se logra solucionar con el drive JnetDirect pero tienes que comprarlo yo se que esa no es la idea por favor ayudame si hallas la solucion
>
> Gracias
Hola. Si un mtodo falla con el MS jdbc conductor libre,
usted debe esperar o antes de que ellos liberen a su
siguiente conductor, y esperen que sea fijado entonces,
o usted puede usar un producto comercial.
El DataDirect es el negocio que realmente hace el
JDBC conductor de MS., entonces usted debera intentar su
conductor. Esto mostrar si el siguiente conductor de
los MS ser fijado.

>
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...

Refresh with jsp doesnt work

Hi!

I've created a simple jsp page with a crystal report viewer on it. I used Crystal Reports Developer to design the report, as db I use access and IBM WSED to create and run the jsp file on a server. That works fine, but when I would refresh the report it shows no recordset (and also no error message appears). Why I can't refresh the data?

The code:

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<%@. page import="com.crystaldecisions.report.web.viewer.CrystalReportViewer" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory,
com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2,com.crystaldecisions.sdk.occa.report.reportsource.IReportSource"
%>
<HTML>
<HEAD>
</HEAD>
<BODY>
CrystalTest.jsp

<%
try {

IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();

String report = "/CrystalReport/Report1.rpt";

Object reportSource = rptSrcFactory.createReportSource(report, request.getLocale())

CrystalReportViewer viewer = new CrystalReportViewer();
viewer.setReportSource(reportSource);

viewer.setOwnPage(true);
viewer.setTop(80);
viewer.setDisplayGroupTree(false);
viewer.setHasLogo(false);
viewer.setHasRefreshButton(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
viewer.dispose();
} catch(Exception e)
{

out.println("CrystalTest: "+e);
}
%>

</BODY
</HTML>hi there!

I've now solved the problem with a jdbc/db2 connection.

The solution is, that I must connect the Crystal Report with jdbc/db2 to the database and also connect in java/jsp to the database as the follows (only samplecode from helpfile):

setDbLogonViewReport.jsp

<%@. page import= "com.crystaldecisions.report.web.viewer.*,
com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.report.web.viewer.*" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.data.*" %>
<%@. page import="com.crystaldecisions.reports.reportengineinterface.JPEReportSourceFactory" %>
<%@. page import="com.crystaldecisions.sdk.occa.report.reportsource.IReportSourceFactory2" %>

<%
Object reportSource = session.getAttribute("reportSource");
if (reportSource == null)
{
String report = "/reports/sample.rpt";
IReportSourceFactory2 rptSrcFactory = new JPEReportSourceFactory();
reportSource = rptSrcFactory.createReportSource(report, request.getLocale());
session.setAttribute("reportSource", reportSource);
}

ConnectionInfos connInfos = new ConnectionInfos();
IConnectionInfo connInfo1 = new ConnectionInfo();
connInfo1.setUserName("reportLogin");
connInfo1.setPassword("");
connInfos.add(connInfo1);

CrystalReportViewer viewer = new CrystalReportViewer();

viewer.setReportSource(reportSource);
&n bsp; viewer.setEnableLogonPrompt(false);
viewer.setDatabaseLogonInfos(connInfos);

if (session.getAttribute("refreshed") == null)
{
viewer.refresh();
session.setAttribute("refreshed", "true");
}

viewer.setOwnPage(true);

viewer.processHttpRequest(request, response, getServletConfig().getServletContext(), null);
%>

Ask if you've more questions to that.