java applet to MS Access Database connection/query problem

Hello!
Basically, I’m trying to retrieve info from a database and display it in a TextArea of an applet.
For now, I’ve
1. An applet, consists of a button and txtArea.
2. When button is “hit”,
an instance of class Item1 is created with a connection variable passed to Item1’s constructor.
With this, a driver and connection to the specified database is established.
3. Database info is retrieved using the getItem( ) method declared in class Item1( of return type  “class ItemInfo”).
4. The whole purpose of class ItemInfo is to store the results of the query for display later in TextArea.
5. Prior to all this, the ODBC connection is created with Data Source Name = ProductODBC and
database = c:\….\Product.mdb (Note: Table name is Item).

This is my final year project, and master students working in the same lab as me do not intend to use JSDK2.1
And I was told that what I’ve got here is basically similar to theirs! And it should make it.
I’ve no problem compiling but the query result is not displayed in the textArea.

Following are the codes for applet,class Item1 and class ItemInfo

----------------------------------------
----------------------------------------
//~start of code for java applet~
import java.applet.Applet;
import java.awt.event.*;
import java.awt.*;
import java.sql.*;

public class Applet1 extends Applet implements ActionListener
 {
// Attribute for database.
private Connection sybaseConn;
private ItemInfo itemInfo;

// Attribute for GUI.
TextArea text;
Button button;

// Attribute in txtArea.
String t;
float cost;
String desc;  
String imgURL;
int productID;
int qtyOnHand;

public void init()
{

t = System.getProperty("line.separator");

button = new Button("Click!");
add(button);

text = new TextArea(5,20);
text.setEditable(false);
add(text);
}

public void actionPerformed(ActionEvent e)
{
if(e.getSource() == button)
{
// Create the instance of the Item class.
Item1 item = new Item1(sybaseConn);

// Retrieve item details for the current row
// from ProductODBC, Item table.

try{itemInfo = item.getItem();}
catch (SQLException ez)
{text.setText("Read failed:" + ez.getMessage());}

cost = itemInfo.Cost;
desc = itemInfo.Desc;
imgURL = itemInfo.ImgURL;
productID = itemInfo.ProductID;
qtyOnHand = itemInfo.QtyOnHand;


text.append(cost + t + desc + t + imgURL
+ t + productID + qtyOnHand );
}
}
}
//~end of code for java applet~
----------------------------------------
----------------------------------------
//~start of code for class Item1~
import java.sql.*;

public class Item1
{
private Connection conn;

// Item constructor starts: store a local copy of the
  // Connection object create statements for use later
public Item1 (Connection c)
{

conn = c;

// Make ODBC-JDBC bridge driver
try {Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");}
catch (java.lang.ClassNotFoundException z)
{System.err.println("Class not found: " + z.getMessage());}

// Make connection.
try{conn = DriverManager.getConnection("jdbc:odbc:ProductODBC");}
catch (SQLException e)
{System.err.println("Database connection failed:"
+ e.getMessage());}
   
}


// Method for getting a single item record with this ID
// Note: this method maps the returned data onto a
// ItemInfo container object
public ItemInfo getItem ()
throws SQLException
{

// Create the select statement and get results
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT *FROM Item");
rs.next();

// Create a ItemInfo container object
ItemInfo info = new ItemInfo ();

// Populate the ItemInfo object
info.ProductID = rs.getInt    (1);
info.ImgURL    = rs.getString (2);
info.Desc      = rs.getString (3);
info.Cost      = rs.getFloat  (4);
info.QtyOnHand = rs.getInt    (5);

return (info);
   }


}
//~end of code for class Item1~
----------------------------------------
----------------------------------------
//~start of code for class ItemInfo~
public class ItemInfo {
int ProductID;
String ImgURL;
String Desc;
float Cost;
int QtyOnHand;
}
//~end of code for class ItemInfo~
----------------------------------------
----------------------------------------
hikaruAsked:
Who is Participating?
 
JodConnect With a Mentor Commented:
You cannot use the JDBC:ODBC bridge driver from an applet directly as it requires to run native code which is outside the applet security "sandbox".

So it will compile the program, but when you run it the security manager will stop the Applet from accessing the JDBC:ODBC driver.

There are a number of ways around this (including using a java application instead of an applet) so I should ask first, is it essential that you use an applet?
0
 
JodCommented:
Something to note about Applets is this, from the JDBC FAQ:

http://java.sun.com/products/jdbc/jdbc-frequent.html 

Can the JDBC-ODBC Bridge be used with applets?

Use of the JDBC-ODBC bridge from an untrusted applet running in a browser, such as Netscape Navigator, isn't allowed. The JDBC-ODBC bridge doesn't allow untrusted code to call it for security reasons. This is good because it means that an untrusted applet that is downloaded by the browser can't circumvent Java security by calling ODBC. Remember that ODBC is native code, so once ODBC is called, Java can't guarantee that a security violation won't occur. On the other hand, Pure Java JDBC drivers work well with applets. They are fully downloadable and do not require any client-side configuration.

Finally, we would like to note that it is possible to use the JDBC-ODBC bridge with applets that will be run in appletviewer since appletviewer assumes that applets are trusted. It is also possible to use the JDBC-ODBC bridge with applets that are run in the HotJava browser (available from Java Software), since HotJava provides an option to turn off applet security. In general, it is dangerous to turn applet security off, but it may be appropriate in certain controlled situations, such as for applets that will only be used in a secure intranet environment. Remember to exercise caution if you chose this option, and use an all-Java JDBC driver whenever possible to avoid security problems.


You can access local data files by creating a policy file that allows this access or you may be able to add permissions specifically for the files in the security model.

Try your application in the Applet viewer which should get round these restrictions.

The overall point is, that in general an applet will be downloaded from a server and the database file should be on the server. This way all downloaded applets are not violating security issues on the client and areusing the same database.

0
 
zicaiCommented:
Hi, Jod

Do you mean I can access the database from the server using JDBC-ODBC bridge? My doubt is that the ODBC is native and the applet is not alowed to use it..

Zicai
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
hikaruAuthor Commented:
yes, it is essential that I use an applet. cos I'm working on something that is to be put on a browser.So, is that a way to run an application from an applet?
0
 
JodCommented:
An untrusted (meaning unsigned) applet cannot use the JDBC:ODBC bridge at all. It also cannot start an application as this also breaks the security.

However, as it says above, the applet viewer that comes with sun's JDK can get around this because it turns some of the security features off.

This is the best route for you to head down - creating a signed applet that works in both IE and NN is not that hard, but it is also not trivial and ecpecially in your circumstances it is best avoided.

zicai:

>> Do you mean I can access the database from the server using JDBC-ODBC bridge?

No unless the applet is signed etc. I mean that applets downloaded from a server will rarely want or need to connect to a local database on the client. Mostly they will connect back to the server to access a central database via JDBC or RMI, for example. In general, this is the most relevant architecture for Applets.
0
 
zicaiCommented:
Hi, Jod

>Mostly they will connect back to the server to access a central database via JDBC or RMI

But after the applet connect back to the server with JDBC, it still need ODBC at the server side to talk to the database, right?

Zicai
0
 
JodCommented:
>> But after the applet connect back to the server with JDBC, it still need ODBC at the server side to talk to the database, right?

Only if you are using ODBC on your server. The database could be talking directly to the Database using a type 3 or 4 driver.

We use a different approach again whereby the client talks via RMI to a remote application on the server - this application then manages all database access and returns query results to the client via RMI also. This centralises your database access and means there is no need to have database drivers downloaded to the client.
0
All Courses

From novice to tech pro — start learning today.