Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

JDBC call to Query in Microsoft Access

Posted on 2003-03-17
8
Medium Priority
?
221 Views
Last Modified: 2008-03-03
Hi experts,

Is it possible to use JDBC to call a Query object in Microsoft Access and retrieve back a ResultSet ?

I tried to use a CallableStatement object and supply it with the name of the Query, but receive a SQLException say that: "Invalid SQL Statement. Expected INSERT, SELECT, UPDATE, PROCEDURE, DELETE". So, I guess my approach is wrong.

Can someone please show me the correct way to do it.

Thanks.
0
Comment
Question by:SuperKinhLuan
  • 4
  • 3
8 Comments
 
LVL 92

Expert Comment

by:objects
ID: 8156960
CallableStatements are used for calling stored procs.
Use either a Statement, or a PreparedStatement.
0
 

Author Comment

by:SuperKinhLuan
ID: 8156999
thanks for your comment.
can you show me how to call a Query in Access using Statement or PreparedStatement ?
0
 
LVL 92

Expert Comment

by:objects
ID: 8157027
Statement s = connection.createStatement();
ResultSet rs = s.executQuery("select * from table");

For more info have a look at:
http://java.sun.com/docs/books/tutorial/jdbc/index.html


0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
LVL 30

Expert Comment

by:Mayank S
ID: 8157135
Something like:

String query = "SELECT * FROM .... ; " ;
Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
Connection con = DriverManager.getConnection ( "jdbc:odbc:<database-name>", "username", "password" ) ;
// if not password protected, leave the username and password blank, as ""
Statement stmnt = con.createStatement () ;
ResultSet rs = stmnt.executeQuery ( query ) ;    

while ( rs.next () )
{
  int intVal = rs.getInt ( "IntegerAttributeName" ) ;
  String str = rs.getString ( "StringAttributeName" ) ;
..
..

} // end while

con.close () ;

PreparedStatement:

String update = "INSERT INTO EMPLOYEE VALUES ( ?, ? ) ; " ;
Class.forName ( "sun.jdbc.odbc.JdbcOdbcDriver" ) ;
con = DriverManager.getConnection ( "jdbc:odbc:<database-name>", "username", "password" ) ;
PreparedStatement ps = con.prepareStatement ( update ) ;
ps.setInt ( 1, 89 ) ;
ps.setString ( 2, "Don Henley" ) ;
ps.executeUpdate () ; // add the record to the 'EMPLOYEE' table
con.close () ;

Hope that helps!

Mayank.
0
 

Author Comment

by:SuperKinhLuan
ID: 8157257
I really appreciate all of your help, but those are not what I'm looking for.
I'm sorry for not being clear in my question. What I meant is that I have a Query object named 'PersonQuery' in my Access database file (Query is a first class object in Access, the 2nd tab below Table). This query returns a sub-table of a main table (kind of a View in other database engine). Now, my question is whether JDBC can access PersonQuery and receive a ResultSet ? if the answer is yes, could you pls show me a sample code ? thanks in advance.
0
 
LVL 92

Accepted Solution

by:
objects earned 225 total points
ID: 8157277
Not sure. Try "select * from PersonQuery".
If that doesn't work then I'm guessing you cannot access it.
0
 

Author Comment

by:SuperKinhLuan
ID: 8157374
hi, thanks. that works.
0
 
LVL 92

Expert Comment

by:objects
ID: 8157391
Good stuff :-)
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Java had always been an easily readable and understandable language.  Some relatively recent changes in the language seem to be changing this pretty fast, and anyone that had not seen any Java code for the last 5 years will possibly have issues unde…
Introduction This article is the second of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers the basic installation and configuration of the test automation tools used by…
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.
This tutorial explains how to use the VisualVM tool for the Java platform application. This video goes into detail on the Threads, Sampler, and Profiler tabs.
Suggested Courses
Course of the Month13 days, 14 hours left to enroll

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question