Java Database Connectivity (JDBC)

Hello,

As mentioned in the subject I am trying to read SPSS data files using the SPSS 32-Bit data driver, ODBC and the JDBC-ODBC Bridge.

Using this SPSS Driver I manged to read the data directly into an MS-SQL Server using:

SELECT [...] FROM
OPENROWSET(''MSDASQL.1'',''DRIVER={SPSS 32-BIT Data Driver (*.sav)};DBQ=' SomePathWhereTheFilesAre';SERVER=NotTheServer'', ''SELECT 'SomeSPSSColumn' FROM "'SomeSPSSFileNameWithoutExt'"'') AS a

This works fine!

Using Access and an ODBC System DNS works for IMPORTING but NOT for LINKING.

It is even possible to read the data using the very slow SPSS API.

However, when it comes to JDBC-ODBC the below code does only work in part. The driver is loaded successfully, but when it comes to transferring data into the resultset object the error

SQLState: null
Result Set Type is not supported
Vendor: 0

occurs.

The official answer from SPSS is to use .Net or to use their implementation with Python in their new version 14.0. But this is obviously not an option when you want to use only Java.

Does anybody have experience with SPSS and JDBC-ODBC??? I have tried the possible ResultSet Types, which I took from:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/ad/rjvdsprp.htm

and none of them worked.

Thank you in advance for your ideas and input & stay happy!

Here the code without all the rest of the class arround it:

// Module:  SimpleSelect.java
//
// Description: Test program for ODBC API interface.  This java application
// will connect to a JDBC driver, issue a select statement
// and display all result columns and rows
//
// Product: JDBC to ODBC Bridge
//
// Author:  Karl Moss
//
// Date:  February, 1996
//
// Copyright: 1990-1996 INTERSOLV, Inc.
// This software contains confidential and proprietary
// information of INTERSOLV, Inc.
//----------------------------------------------------------------------------


public static void main1() {
  String url   = "jdbc:odbc:SomeSystemDNS";
  String query = "SELECT SomeSPSSColumn FROM 'SomeSPSSFileName'";

  try {

    // Load the jdbc-odbc bridge driver

    Class.forName ("sun.jdbc.odbc.JdbcOdbcDriver");

    DriverManager.setLogStream(System.out);

    // Attempt to connect to a driver.  Each one
    // of the registered drivers will be loaded until
    // one is found that can process this URL

    Connection con = DriverManager.getConnection (url);

    // If we were unable to connect, an exception
    // would have been thrown.  So, if we get here,
    // we are successfully connected to the URL

    // Check for, and display and warnings generated
    // by the connect.

    checkForWarning (con.getWarnings ());

    // Get the DatabaseMetaData object and display
    // some information about the connection

    DatabaseMetaData dma = con.getMetaData ();

    System.out.println("\nConnected to " + dma.getURL());
    System.out.println("Driver       " +
      dma.getDriverName());
    System.out.println("Version      " +
      dma.getDriverVersion());
    System.out.println("");

    // Create a Statement object so we can submit
    // SQL statements to the driver

    Statement stmt = con.createStatement(ResultSet.TYPE_FORWARD_ONLY ,ResultSet.CONCUR_READ_ONLY);

    // Submit a query, creating a ResultSet object

    ResultSet rs = stmt.executeQuery (query);

    // Display all columns and rows from the result set

    dispResultSet (rs);

    // Close the result set

    rs.close();

    // Close the statement

    stmt.close();

    // Close the connection

    con.close();
  }