How to use JDBC Driver SQL Server & execute Query in JSP

sdk development
1

Hello,

Here is my “configuration” : Windows Server 2003, BO XI R2, Apache/Tomcat, SQL Server 2005.

I put sqljdbc.jar into my tomcat/lib directory and put this directory into my classpath environnement variable.

Here is what I wanna do : Displaying, on login page, a date which is in my SQL Server database.

I modify logon.jsp in order to run the appropriate query, I follow examples found on the web.

This is my code (file logon.jsp) :
(in bold the code I added to display the date)

[...]

<%@ page language="java" contentType="text/html;charset=utf-8" %>
<%@ page import="com.crystaldecisions.ePortfolio.framework.common.CEClientConstants,
                 com.crystaldecisions.ePortfolio.framework.common.UserSettings,
                 com.crystaldecisions.webapp.CEConstants,
                 java.util.Locale" %>

[b]<%@ page import= "java.sql.*"%>[/b]

<%@ taglib uri='/WEB-INF/fmt.tld' prefix='fmt' %>
<%@ taglib uri="/WEB-INF/struts-html.tld" prefix="html" %>
<%@ taglib uri='/WEB-INF/c.tld' prefix='c' %>
<%@ taglib uri='/WEB-INF/c-rt.tld' prefix='c_rt' %>


[b]<%
  
  Connection con = null;
  String DT_ALIM= null; 
try { 
// Load the JDBC driver 
String driverName = "com.jdbc.microsoft.sqlserver.SQLServerDriver"; 
//Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");
//Class.forName("com.inet.tds.TdsDriver");
Class.forName(driverName); 
// Create a connection to the database 
String serverName = "myserver"; 
String mydatabase = "mybase"; 
String url = "jdbc:microsoft:sqlserver://" + serverName + "/" + mydatabase; 
//String url = "jdbc:inetdae7:myserver:1433?useCursorAlways=true";
// a JDBC url 
String username = "myuser"; 
String password = "mypass"; 
con = DriverManager.getConnection(url, username, 
password); 
} catch (ClassNotFoundException e) { 
// Could not find the database driver
 DT_ALIM="Driver Error";
} catch (SQLException e) { 
// Could not connect to the database 
DT_ALIM="Database Error";
}
  
   Statement stmt = con.createStatement();

    ResultSet rs = stmt.executeQuery("SELECT VAL_PARAM FROM DWH_PARAMETRE_VISION WHERE ID_PARAM = 'DT_ALIM'") ;
  
   rs.next();

  DT_ALIM=rs.getString(1);
	
	
%>[/b]

[...]

[b]<td height="24" width="398"> 
        <div align="center"><font color="#CCCCCC"><span style="font-family:Verdana; font-size:10px; font-weight:normal; color:#A3A3A3; "><font face="Verdana, Arial, Helvetica, sans-serif" font-size:10px><b><span class="texte_gras_fonce"><font color="#000000">Derni&amp;egrave;re 
          alimentation le :</font></span><br>
          </b></font> <b> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"> 
          </font><font color="#000000"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%= DT_ALIM %></font></b></font></b></span></font></div>
      </td>[/b]

The problem is that the page is not working.

When I comment the Statement, ResultSet and assignment (only the last one) lines, the page is displayed “correctly” but DT_ALIM = Driver Error[b] ! :hb:

Has anyone any idea of what I’m doing badly !!??? :crazy:

Thanks in advance for any help,

[/b]

ausylan :fr: (BOB member since 2005-01-10)

2

Hello again,

no one has any idea ? :blue:

come on !! please help ! :wah:

ausylan :fr: (BOB member since 2005-01-10)

3

This isn’t a BO issue. BO doesn’t work with JDBC. You’re just building an app of your own. If you want to connect to SQL Server and use the BO engine, you can’t do it this way.

Steve Krandel :us: (BOB member since 2002-06-25)

4

Sorry ??? :crazy_face:

Why it is not a BO issue ? I work on BO XI, I wanna display a date on my login page which is taken from a SQLServer database, I use TOMCAT so JSP files so I cannot connect without JDBC, no ? :blue:

ausylan :fr: (BOB member since 2005-01-10)

5

Hi,

You have this line :-

String driverName = "com.jdbc.microsoft.sqlserver.SQLServerDriver";

Try replacing it with :-

String driverName = "com.microsoft.sqlserver.jdbc.SQLServerDriver";

Just a guess from what I read in the MS SQL 2005 JDBC documentation :wink:

Regards

Rod

rodallen :uk: (BOB member since 2006-02-10)

6

Hello,

thanks for your idea, I’ll try as soon as possible.

I’ve already tried to put this :

String driverName = "com.microsoft.jdbc.sqlserver.SQLServerDriver";

instead of this :

String driverName = "com.jdbc.microsoft.sqlserver.SQLServerDriver";

but it didn’t work… I hope that’s your semantic will work fine,

I’ll let you know,

thanks again and have a good day,

AuSyLaN

ausylan :fr: (BOB member since 2005-01-10)

7

You have to add class path in the tomcat start batch file also. Instead of using driver, you can use DSN connection. if you use DSN, you don’t need to copy the jar file in the lib or you don’t need add to class path in tomcat start batch file also. thats very easy to maintain compared to driver in the jsp page.

let me know. Thanks, Rad

RadN :us: (BOB member since 2003-04-28)

8

I have done exactly this using the code below :

<html>
<head>
</head>
<body>
<%@ page import="java.sql.*" %>
<% Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver").newInstance(); %>
<% Connection conn = DriverManager.getConnection("jdbc:microsoft:sqlserver://servername:1433","username","password"); 
Statement stmt = conn.createStatement();
ResultSet columns = stmt.executeQuery("SELECT T_Date,T_Time FROM Date_Table");
while(columns.next())
{
String T_Date = columns.getString("T_Date");
String T_Time = columns.getString("T_Time");
%><%=T_Date%>&amp;nbsp<%=T_Time%>
<%}%>
</body>
</html>

I had this saved as sql.jsp in the logon folder, then had a include in the logon.jsp page.

HTH

Daniel

danch :uk: (BOB member since 2005-06-02)