Reading Visual Foxpro Data with Java Using ODBC

Question

I am trying to query a dbf table from my Java application. I added a link

I created a system data source using the ODBC Data Source Administrator, I set the data source name to VFPDS and set the database type to .DBC finaly, I set the path to the .dbc file.

below is the java code:

import javax.swing.* ; import java.awt.* ; import java.awt.event.* ; import java.sql.Connection; import java.sql.ResultSet; import java.sql.SQLException; import java.sql.SQLWarning; import java.sql.Statement; // Import custom library containing myRadioListener import java.sql.DriverManager; public class testodbc { public static void main( String args[] ) { try { // Load the database driver Class.forName( "sun.jdbc.odbc.JdbcOdbcDriver" ) ; // Get a connection to the database Connection conn = DriverManager.getConnection( "jdbc:odbc:VFPDS" ) ; // Print all warnings for( SQLWarning warn = conn.getWarnings(); warn != null; warn = warn.getNextWarning() ) { System.out.println( "SQL Warning:" ) ; System.out.println( "State : " + warn.getSQLState() ) ; System.out.println( "Message: " + warn.getMessage() ) ; System.out.println( "Error : " + warn.getErrorCode() ) ; } // Get a statement from the connection Statement stmt = conn.createStatement() ; // Execute the query ResultSet rs = stmt.executeQuery( "SELECT * FROM pmsquoteh" ) ;//code crashes here // Loop through the result set while( rs.next() ) System.out.println( rs.getString(1) ) ; // Close the result set, statement and the connection rs.close() ; stmt.close() ; conn.close() ; } catch( SQLException se ) { se.printStackTrace(); System.out.println( "SQL Exception:" ) ; // Loop through the SQL Exceptions while( se != null ) { se.printStackTrace(); System.out.println( "State : " + se.getSQLState() ) ; System.out.println( "Message: " + se.getMessage() ) ; System.out.println( "Error : " + se.getErrorCode() ) ; se = se.getNextException() ; } } catch( Exception e ) { System.out.println( e ) ; } } } 

I got this exception:

 java.sql.SQLException: [Microsoft][ODBC Visual FoxPro Driver]Not a table. at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source) at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source) at sun.jdbc.odbc.JdbcOdbcStatement.executeQuery(Unknown Source) at UsingButtons.main(testodbc.java:38) SQL Exception: State : S0002 Message: [Microsoft][ODBC Visual FoxPro Driver]Not a table. Error : 123 

I am sure that the pmsquoteh table exits the database and my machine is 64 bit. What am I doing wrong? I would appreciate specific answers.

Answer 1

I managed to access the FoxPro table with the jdbc-odbc bridge in Windows 7, but it took a few steps. I already have the VFP driver installed and I don’t remember where I downloaded it from, so I don’t have a link for this.

I copied the code from the jbdc: odbc example here: http://www.java2s.com/Code/Java/Database-SQL-JDBC/SimpleexampleofJDBCODBCfunctionality.htm .

The DriverManager.getConnection method accepts the database URL. To create the url you need to use the ODBC manager. Unfortunately, for me, the ODBC manager, with whom I could get through the control panel, worked only for 64-bit data sources. I do not know the 64-bit foxpro driver.

To generate a 32-bit DSN, you need to run the 32-bit ODBC manager: odbcad32.exe. My car had several copies. I ran one of C: \ Windows \ SysWOW64. Click the System DSN tab and select the Microsoft Visual Foxpro driver. When you click Finish, you will get a dialog box asking for the name of the data source, description, and path to the FoxPro database or tables. You must also indicate whether you want to connect to .dbc or a free table. Your error message makes me wonder if you had the wrong option selected in your DSN.

The database url I passed to the getConnection method was "jdbc: odbc: mydsnname".

After that, I received an error message:

[Microsoft] [ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and the Application

This was due to the fact that I used a 64-bit JVM with a 32-bit DSN. I downloaded the 32-bit JVM and was able to use it to run my class.

I do not know if there is a switch that you can set on a 64-bit JVM to say that it works as 32-bit.

Answer 2

I used the JDBC driver here:

http://www.csv-jdbc.com/relational-junction/jdbc-database-drivers-products/new-relational-junction-dbf-jdbc-driver/

Works great for me. While still testing (about an hour), it reads MEMO files and seems to have no problems with tables containing DBCs or with free DBFs.

Not sure how much this driver costs. The customer is likely to pay $ 100 since VFP is out of date.