Win a copy of Design for the Mind this week in the Design forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trouble connecting to MS Access via ODBC

 
Ola Kesh
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ola Kesh
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64699
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Ola Kesh
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes i have a data source named Airlinee already defined, it has two tables omestic and International tables
 
Ben Souther
Sheriff
Posts: 13411
Firefox Browser Redhat VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64699
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Pie
Posts: 64699
86
IntelliJ IDE Java jQuery Mac Mac OS X
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 86
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic