Executing a Microsoft Access query in java

[ChallengedProgrammer]

Hi,

First of all, is it possible to execute a query in Microsoft Access using the jdbc-odbc driver?

I have tried using a CallableStatement to try to access my query.

CODE EXCERPT:
********************************************************
Connection conn = DriverManager.getConnection("jdbcdbc:" + sDatabaseName, sUserName, sPassword);

CallableStatement sc = conn.prepareCall("{call QUERY_NAME}"

rset = cs.executeQuery();
********************************************************

...where QUERY_NAME is the query that exists in my Access db. However I get an empty result set even though I know that my query returns 3 records.

What am I doing wrong?

Thanks,
ChallengedProgrammer

 

[sedj]

Couple of things :

- CallableStatement is a class used for calling Stored Procedures. I'm not really familiar with Access, but is an Access query a stored proc in the same sense as an Oracle stored proc is ?

- If the answer to the above is "yes", then us your proc returning results - ie an 'out' parameter for you to create a result set from. For example, a java type of ResultSet is mapped to the Oracle 'CURSOR' type - what is the equivalent mapping to Access ?

- Are there any errors ?

- >>>> I get an empty result set even though I know that my query returns 3 records.
Are you absolutely positive about this ?

 

[JVZ]

When you save a query in Access it does not save as a Stored Procedure unless your are using MSDE or save the file as a ADP (Microsoft Access Project). The query you save in Access act like looks like a view to the ODBC driver (so bacically you treat it as a table). To get the results from the query you simply issue a normal query...

ie.

ResultSet     rs = stmt.executeQuery ("Select * FROM " + QUERY_NAME);

Hope this helps...

 

[ChallengedProgrammer]

Thanks for both your responses.
********************************************************
Response to: JVZ

I was able to execute the Access query using the SELECT statement as suggested, but I was hoping to be able to execute the query by calling on it as a Stored Procedure. However, since Access does not use "true" Stored Procedures as ORACLE does, this option may not possible.

What confuses me though is that a call on the metadata (DatabaseMetaData)supportsStoredProcedures()function gives a TRUE result, suggesting that it supports Stored Procedure Calls.

*********************************************************
Response to: SEDJ

>>>> I get an empty result set even though I know that my query returns 3 records.
>Are you absolutely positive about this ?

YES, And I did not get any errors, only an empty result set.

I was testing using an 'out' parameter, but I will have to figure out what the equivalent mapping of a java type of ResultSet is in Access.

I will give you feedback on this after I have tried it.

Thanks again.

 

[JVZ]

The method

supportsStoredProcedures()

is correct, Microsoft Access 2000 and above does have support for Stored Procedure. HOWEVER, only if the access database is saved as a .ADP file, and not as a .MDB. The difference is that when you save a Access Database as a .ADP (Microsoft Access Project) you are using Access as a front-end only connecting to SQL Server or MSDE (free version of SQL-Server) (so all Access is doing in acting like a interface)...

You can find learn more about this on Microsoft's site, here a link to help you get started if you want to learn more about this:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acaccessprojects.asp

 

[ChallengedProgrammer]

JVZ, Thanks for the link about Microsoft Access Project and for clearing up the supportStoredProcedures() method for me. That's interesting...I'll check it out.