Reading Visual Foxpro Data From Java using ODBC

Question

I am trying to query a dbf table from my Java application. I put in reference this thread

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

the following is my 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 table pmsquoteh exits in the database and my machine is a 64 bit machine. What am i doing wrong ? I would appreciate specific answers.

Answer 1

I was able to access a FoxPro table with the jdbc-odbc bridge on Windows 7 but it took a number of steps. I already had the VFP driver installed, and I don't remember where I downloaded it from, so I don't have a link for that.

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

The DriverManager.getConnection method takes a database URL. To create a URL you need to use the ODBC manager. Unfortunately for me, the ODBC manager that I could get to through the control panel only worked for 64 bit data sources. I am not aware of a 64 bit foxpro driver.

To generate a 32 bit DSN you need to run the 32 bit ODBC manager: odbcad32.exe. My machine had several copies. I ran the one from C:\Windows\SysWOW64. Go to the System DSN tab and select the Microsoft Visual Foxpro Driver. When you click finish, you will get a dialog that asks for a Data Source Name, description and path to the FoxPro database or tables. You also have to specify whether you want to connect to a .dbc or a free table directory. Your error message makes me wonder if you had the wrong option selected on your DSN.

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

After I ran this, I received an error:

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

This was because I was using a 64 bit JVM with a 32 bit DSN. I downloaded a 32 bit JVM and was able to use it to run my sample class.

I do not know if there is a switch you can set on a 64 bit JVM to tell it to run as 32 bit.

Answer 2

I used the JDBC driver from here:

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

Works fine for me. In testing so far (about an hour), it reads MEMO files and seems to have had no issues with either DBC-containted tables or free DBFs.

Not sure how much this driver costs. The client will only probably pay $100 max since VFP is beyond obsolete.