Meaningless Drivel is fun!*
The moose likes JDBC and the fly likes Trouble connecting to MS Access via ODBC Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Java 8 in Action this week in the Java 8 forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trouble connecting to MS Access via ODBC" Watch "Trouble connecting to MS Access via ODBC" New topic
Author

Trouble connecting to MS Access via ODBC

Ola Kesh
Ranch Hand

Joined: Dec 24, 2002
Posts: 40
Hello, please eac time i tried to load my JSP code, i get a GENERAL ERROR. I have registered my Access Database with microsoft ODBC and it is located in the same directory with my JSP codes; please what should i do?
This is the code attached below:

<HTML>
<HEAD><TITLE>JDBC in JSP page</TITLE></HEAD>
<BODY>

<%--Set the Scripting Languagento java--%>
<%--Import the 'java.sql'package--%>

<%@ page language = "java" import = "java.sql.*" %>
<%
Connection con = null;
try{

//Load the driver class file
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

// Make a connection to the data source "Airline" with no user name and password
con = DriverManager.getConnection("jdbc dbc:Airlinee","","");

//Create Connection
Statement stmt = con.createStatement();

//Use the created statment to select data from the table
ResultSet rs = stmt.executeQuery("SELECT * FROM Domestic");

// iterate over the result set
%>

<!--Add an HTML table to hold the data-->

<TABLE BORDER="1">
<TR>
<TH>Domestic</TH> <TH>Address</TH> <TH>Phone</TH> <TH>Schedule</TH> <TH>WebSite</TH>
</TR>
<%
while(rs.next()) {
//get the Domestic Airline of data type String
out.println("<TR>\n<TD>" +rs.getString("DOMESTIC") +"</TD>");

//get the Address of data type String
out.println("<TD>" +rs.getString("ADDRESS") +"</TD>");

//get the Phone Number of data type String
out.println("<TD>" +rs.getString("PHONE") +"</TD>");

//get the Flight Schedule of data type String
out.println("<TD>" +rs.getString("SCHEDULE") +"</TD>");

//get the Website of data type String
out.println("<TD>" +rs.getString("WEBSITE") +"</TD>\n</TR>");
}

// Close the resultset
rs.close();
}

//catch(IOException i) {
//out.println(i.getMessage());
//}

catch(SQLException sqle) {
out.println(sqle.getMessage());
}

catch(ClassNotFoundException cnfex) {
out.println(cnfex.getMessage());
}

catch(Exception e) {
out.println(e.getMessage());
}

finally {
try {
if(con != null) {
//close the connection
con.close();
}
}

catch(SQLException sqle) {
out.println(sqle.getMessage());
}
}
%>
</TABLE>

</BODY>
</HTML>


[ July 12, 2005: Message edited by: Bear Bibeault ]
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

Keshinro,

Two tips:

First, when posting more than a line or two of your code, wrap the code in UBB Code tags (there is a button on the edit screen to help you with this). Doing so will preserve your indenting and make your code much easier to read.

Second: Post the relevant portion of your stack trace. 'GENERAL ERROR' doesn't tell us much about what the problem is.


Java API J2EE API Servlet Spec JSP Spec How to ask a question... Simple Servlet Examples jsonf
Ola Kesh
Ranch Hand

Joined: Dec 24, 2002
Posts: 40
Hello,
I realized that my jsp code is not connecting to the access database that i have in the directory.
Please kindly check up if my connection is okay.
Thanks
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60082
    
  65

1) You'd be better off relying upon a connection pool (preferably container-managed) rather than creating connections on the fly.

2) Directly accessing a DB from a JSP page is an affront to all that is holy.

3) Do you have a datasource named "Airlinee" actually defined?

// Make a connection to the data source "Airline" with no user name and password
con = DriverManager.getConnection("jdbc dbc:Airlinee","","");


Your comment and your code don't jive.


[Asking smart questions] [Bear's FrontMan] [About Bear] [Books by Bear]
Ola Kesh
Ranch Hand

Joined: Dec 24, 2002
Posts: 40
Yes i have a data source named Airlinee already defined, it has two tables omestic and International tables
Ben Souther
Sheriff

Joined: Dec 11, 2004
Posts: 13410

I think he means, have you set up an ODBC datasource.
You do this from the windows control panel.






<slightly offtopic observation>
Bear:2) Directly accessing a DB from a JSP page is an affront to all that is holy.

One may conclude from this statement that Bear has strong feelings about model 1 programming.
</slightly offtopic observation>
[ July 12, 2005: Message edited by: Ben Souther ]
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60082
    
  65

One may conclude from this statement that Bear has strong feelings about model 1 programming.


Ya think?

Seriously, even under a Model 1 architecture, any such processing should at minimum be factored off the page and into more easily debugged and testable beans.
[ July 12, 2005: Message edited by: Bear Bibeault ]
Ola Kesh
Ranch Hand

Joined: Dec 24, 2002
Posts: 40
Thanks but am still stock.
My database is already registered with the ODBC stuff. I tried the Connection Pooling stuff but the material am using only discussed oracle and sybase database.
I tried to figure things out but gave this error:
"[Microsoft][ODBC Driver Manager] Data source name too long".
On the second note, i will try the to use a beans...
Bear Bibeault
Author and ninkuma
Marshal

Joined: Jan 10, 2002
Posts: 60082
    
  65

On the second note, i will try the to use a beans...


Factoring code out of the JSP and into beans won't solve your issue -- which appears to be in latching up to the Windows ODBC data source naming -- but it will benefit you with code that is easier to debug, test and maintain. It's a good step in the right direction.

Since it's clear at this point that this is not a JSP, but rather a JDBC issue, I've moved this topic over to the JDBC forum where you will get more exposure to JDBC-ODBC users.

I've also changed the subject text to better reflect the question.
[ July 12, 2005: Message edited by: Bear Bibeault ]
Ola Kesh
Ranch Hand

Joined: Dec 24, 2002
Posts: 40
Hello, Am still stuck with my JSP and MS Access Connectivity.
Am not clear with the code below:
Connection con = DriverManager.getConnection(url,username,password);
How do i insert or what i sthe format of my url since am using JSP and Access?
Wei Dai
Ranch Hand

Joined: Jun 22, 2005
Posts: 86
Try to use a dns-less url for your java-odbc bridge, for instance:
String dbsrc = "jdbc dbc RIVER=Microsoft Access Driver (*.mdb); "
+"DBQ="+database+"; "
+"ImplicitCommitSync=Yes; "
+"UserCommitSync=Yes; "
+"Threads=3; "
+"SafeTransactions=0; "
+"PageTimeout=5; "
+"MaxScanRows=8; "
+"MaxBufferSize=2048; "
+"DriverId=281; "
+"DefaultDir=C:/Program Files/Common Files/ODBC/Data Sources";

return DriverManager.getConnection(dbsrc, "", "");

Or try HXTT Access(www.hxtt.net) to skip the annoying odbc driver limitation.
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trouble connecting to MS Access via ODBC
 
Similar Threads
Calling another servlet
reading data from jsp page to another jsp page using request.getParameter
Getting Problem in Delete operation in Struts application
JDBC: NullPointerException
Connection Pooling example appears to create more than one pool