Hello,
Below is a jsp that uses a bean. It reads data from a Microsoft Access mdb. It worked fine for a DB that I had created an ODBC dsn for. But now I am experimenting using application.getRealPath(...) as below for a new Access DB in the same location, but I did not create an ODBC dsn for this one. I am using the same bean as the jsp that did work with the ODBC dsn. My new DB is called mydata.mdb, has one table with 2 text fields, very basic. This DB is located at the root of my context. Could anyone see if I have the wrong syntax here? The idea is to not have to create an ODBC dsn on the machine. The bean should be fine, very basic also.
Here is the jsp that I am trying application.getRealPath(...). Below that is the jsp that did work. In the second jsp I do not set a driver property since I have the ODBC connection. In the first jsp (below) I set a driver property (setDriver(...)) in the bean. Heck, I will include the bean while I'm at it.
***************************************************************
<html>
<head>
<title>Set Property</title>
</head>
<body>
<jsp:useBean id="lineBeanId" scope="page" class="com.rdb3.java.beans.Sql" />
<jsp:setProperty name="lineBeanId" property="sql" value="SELECT * FROM tblmydb" />
<jsp:setProperty name="lineBeanId" property="dsn" value="jdbc:odbc:" />
<jsp:setProperty name="lineBeanId" property="driver" value="DRIVER=Microsoft Access Driver (*.mdb);DBQ=" + application.getRealPath("/mydb.mdb") />
Welcome to my website.
<%= lineBeanId.execute() %>
</body>
</html>
***************************************************************
***************************************************************
<html>
<head>
<title>Set Property</title>
</head>
<body>
//create dsn called mydatabase
<jsp:useBean id="lineBeanId" scope="page" class="com.rdb3.java.beans.Sql" />
<jsp:setProperty name="lineBeanId" property="sql" value="SELECT * FROM Employees" />
<jsp:setProperty name="lineBeanId" property="dsn" value="jdbc:odbc:mydatabase" />
Welcome to my website.
<%= lineBeanId.execute() %>
</body>
</html>
*****************************************************************
Here is the bean I am using:
***************************************************
// javac com\rdb3\java\beans\Sql.java
// jar cfvm rdb3Beans.jar com\rdb3\java\manifest com\rdb3\java\*.*
package com.rdb3.java.beans;
import java.util.*;
import java.net.*;
import java.io.*;
import java.sql.*;
public class Sql implements Serializable
{
private String dsn = "jdbc:odbc:mydatabase";
private String driver = "sun.jdbc.odbc.JdbcOdbcDriver";
private String uid; // e.g., "someone" or null
private String pwd; // e.g., "12345" or null
private String sql; // e.g., "SELECT * FROM Employees"
public void setDsn(String dsn){this.dsn = dsn;}
public void setDriver(String driver){this.driver = driver;}
public void setUid(String uid){this.uid = uid;}
public void setPwd(String pwd){this.pwd = pwd;}
public void setSql(String sql){this.sql = sql;}
public String execute(){return execute(this.sql);}
public String execute(String sql) // e.g., "SELECT * FROM Employees"
{
try
{
Class.forName(this.driver);
Connection con;
if ((this.uid == null) || (this.pwd == null))
con = DriverManager.getConnection(this.dsn);
else
con = DriverManager.getConnection(this.dsn, this.uid, this.pwd);
Statement stmt = con.createStatement();
StringBuffer table = new StringBuffer();
if (sql.toLowerCase().startsWith("select"))
{
ResultSet rs = stmt.executeQuery(sql);
ResultSetMetaData md = rs.getMetaData();
int nFields = md.getColumnCount();
table.append("<table>\n <tr>\n");
for (int i = 1; i <= nFields; i++)
table.append(" <th>").append(md.getColumnName(i)).append("</th>\n");
table.append(" </tr>\n");
while(rs.next())
{
table.append(" <tr>\n");
for (int i = 1; i <= nFields; i++)
table.append(" <td>").append(rs.getString(i)).append("</td>\n");
table.append(" </tr>\n");
}
table.append("</table>\n");
}
else
stmt.executeUpdate(sql);
con.close();
table.append("\n<hr><i>bean by Dr. Robert Burns</i><br>");
return table.toString();
}
catch(Exception e){return "ERROR: " + e;}
}
}
Thanks,
Rich