Retrieve PK from Java Insert while auto commit off

I hava a series of SQL Server Insert statements that I am doing in a Java APP with the connection Auto commit OFF, (so I can revert if needed).    I need to retrieve the PK for the inserted row.   I tried using the "RETURN_GENERATED_KEYS" on my executeUpdate statement:   statement.executeUpdate(myInsQry, Statement.RETURN_GENERATED_KEYS);

But I get unsupported operation.  guess my JDBC does not suport that.

What is the best method to attack this?
SLI-PGHAsked:
Who is Participating?
 
mccarlConnect With a Mentor IT Business Systems Analyst / Software DeveloperCommented:
Sorry, maybe it wasn't obvious in what I posted above. You are now correctly "loading" the JDBC driver (with the Class.forName() method call) but you are still "using" the ODBC driver because of this...

String connectionUrl = "jdbc:odbc:WINSLISSDEV01-FDRSS";

You need to change that URL so that the correct JDBC driver gets used...

String connectionUrl = "jdbc:sqlserver://localhost:1433;databaseName=XXXXXX;user=YYYYYY;password=******";


You'll notice with the above that you CAN'T just use the DSN (DSN's are an ODBC thing), you need to actually find out the hostname, port, database name, username & password of your SQL Server database and put those in the right place above. The important thing being the start of that URL, the jdbc:sqlserver:.... part of it. This is what tells Java which driver to actually use.
0
 
dpearsonCommented:
Hmm - RETURN_GENERATED_KEYS is the right way to solve this.

You could try other approaches (like reading the MAX value of the key from the table after an insert) but those are all just approximate as you could have multiple inserts at the same time and get the wrong value.

I'd suggest trying to upgrade your JDBC driver?

Doug
0
 
Chris LuttrellSenior Database ArchitectCommented:
I am not a Java expert but I would have to agree with dpearson about updating your driver if that is the right way.
An alternative to do in your SQL statement, you can try to output the resulting PK and capture it in a result set.
I am assuming you have an IDENTITY setting the PK on insert something like this simplified example:
CREATE TABLE YourTable
    (
      ID INT IDENTITY (1,1) PRIMARY KEY,
      ColumnA VARCHAR(50),
      ColumnB INT
    );

INSERT  INTO YourTable
        ( ColumnA, ColumnB )
OUTPUT  INSERTED.ID
VALUES  ( 'Val1', 100 ),
        ( 'Val2', 200 );

Open in new window

You can get more than just the PK back also if you need other information.
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
SLI-PGHAuthor Commented:
My version of sqljdbc.jar is 4.0  I thought that was the latest.
 CGLuttrell,   I see what you did but not sure how to do that in Java.

I see a lot of examples using  statement = connection.prepareStatement(SQL_INSERT, Statement.RETURN_GENERATED_KEYS);
but just can't get it to work.   Is it truly my version sqljdbc.jar or something else?
0
 
dpearsonCommented:
I guess the other possibility is that it's in the configuration of SQL server somehow?  (We use MySQL).

Perhaps you can setup accounts with limited privilege that aren't allowed to retrieve the primary keys?  OK - that sounds like a pretty dumb idea as I type it, but it could be interpreted as "unsupported operation" at the database, rather than in the driver itself.

Certainly RETURN_GENERATED_KEYS is the norm.  We use it every day w/o issue.  But as I say, we're using MySQL so our Jar is currently mysql-connector-java-5.1.20-bin.jar

Doug
0
 
SLI-PGHAuthor Commented:
Anyone else out there used RETURN_GENERATED_KEYS for SQL SERVER Databases in JAVA?  IF so, what version of SQLJDBC do you use?   Any insight would be appreciated.
0
 
SLI-PGHAuthor Commented:
To add,    on my UnsupportedOperationException ex,   the ex.getMessage() is null and the stacktrack shows Unknown Source:

java.lang.UnsupportedOperationException
      at sun.jdbc.odbc.JdbcOdbcConnection.prepareStatement(Unknown Source)
      at flowPK.FlowParser.insertExperiment(FlowParser.java:618)
      at flowPK.FlowParser.processHeader(FlowParser.java:1358)
      at flowPK.FlowParser.processFile(FlowParser.java:485)
      at flowPK.FlowParser.main(FlowParser.java:226)

So, I'm not getting a lot of help from the Exception thrown.
In Eclipse,  when I hover over the statement I get:
prepareStatement:
PreparedStatement prepareStatement(String sql,
                                  int autoGeneratedKeys)
                                  throws SQLException
which I'm assume means that the SQL drive is supposed to support this opration.
0
 
dpearsonCommented:
The "Unknown Source" part is likely just because it's going into native code.  So no big surprise there.

The fact that prepareStatement's interface supports this alas is no guarantee that the underlying driver actually supports it.  The driver of course *should* support it - but I guess that's up to the guy writing that specific driver implementation.

Doug
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
So, I'm not getting a lot of help from the Exception thrown
Actually that exception message has given (hopefully) all the help that we need to solve your problem!! :)   Look at that line that mentions "Unknown Source" but the part before that...
sun.jdbc.odbc.JdbcOdbcConnection...
You are using the JDBC-ODBC bridge driver rather than the actual SQL Server JDBC driver. I'm assuming in your code where you make a connection you are using a URL of this form...
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:XXXXXXXX";
Connection con = DriverManager.getConnection(url);

Open in new window

You should change that to be something more like the following (obviously replacing your values for host, port and dbname, user, password, etc)...
Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
String url = "jdbc:sqlserver://localhost:1433;databaseName=XXXXXX;user=YYYYYY;password=******";
Connection con = DriverManager.getConnection(url);

Open in new window

0
 
SLI-PGHAuthor Commented:
I'm using:

  public static Connection conM;
  String connURL = "jdbc:odbc:MyserverDSN";
  conM = DriverManager.getConnection(connURL);
  conM.setAutoCommit(false);
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
So yeah, as I said, you are using the jdbc-odbc bridge driver, and that would be the reason for the UnsupportedOperationException. Did you try what I suggested?
0
 
SLI-PGHAuthor Commented:
Yes I did.   Below is my stub of the program.  The only jar files I am including in the library are:     sqljabc4.jar   and JRE SystemLibrary[JavaSE-1.6].   At one time I thought it might be related to 64 vs 32 bit.   But tried the same on  both platforms with same error. On the
   PreparedStatement ps = conM.prepareStatement myInsQry,Statement.RETURN_GENERATED_KEYS);   it keeps throwing the UnsupportedOperationException.

import java.sql.*;
import java.util.Properties;
public class testSql {
  public static void main(String[] args) {
  try {
      Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
      String connectionUrl = "jdbc:odbc:WINSLISSDEV01-FDRSS";
      Connection  conM = DriverManager.getConnection(connectionUrl);
      String myInsQry = "insert into FLOW_EXPERIMENT (Experiment_NAME)  values ('Exp1234')";
      PreparedStatement ps = conM.prepareStatement(myInsQry,Statement.RETURN_GENERATED_KEYS);
      ps.executeUpdate();
      } catch (SQLException e) {                   
            e.printStackTrace();
      }    
      catch (UnsupportedOperationException ex) {
            System.out.println(ex.getLocalizedMessage());
            System.out.println(ex.getMessage());
            ex.printStackTrace();       
      }
      catch (ClassNotFoundException e) {
            System.out.println(e.getLocalizedMessage());
            e.printStackTrace();
      }      
  }
}
0
 
SLI-PGHAuthor Commented:
Awesome,    That was the ticket.  Thanks for your help.
0
 
mccarlIT Business Systems Analyst / Software DeveloperCommented:
Not a problem, glad to help! :)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.