Solved

Retrieve PK from Java Insert while auto commit off

Posted on 2013-12-06
14
493 Views
Last Modified: 2013-12-17
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?
0
Comment
Question by:SLI-PGH
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 3
  • +1
14 Comments
 
LVL 27

Expert Comment

by:dpearson
ID: 39703770
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
 
LVL 26

Expert Comment

by:Chris Luttrell
ID: 39703949
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
 

Author Comment

by:SLI-PGH
ID: 39706684
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
Why You Need a DevOps Toolchain

IT needs to deliver services with more agility and velocity. IT must roll out application features and innovations faster to keep up with customer demands, which is where a DevOps toolchain steps in. View the infographic to see why you need a DevOps toolchain.

 
LVL 27

Expert Comment

by:dpearson
ID: 39706919
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
 

Author Comment

by:SLI-PGH
ID: 39708506
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
 

Author Comment

by:SLI-PGH
ID: 39708818
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
 
LVL 27

Expert Comment

by:dpearson
ID: 39710174
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
 
LVL 35

Expert Comment

by:mccarl
ID: 39710215
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
 

Author Comment

by:SLI-PGH
ID: 39710495
I'm using:

  public static Connection conM;
  String connURL = "jdbc:odbc:MyserverDSN";
  conM = DriverManager.getConnection(connURL);
  conM.setAutoCommit(false);
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39710523
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
 

Author Comment

by:SLI-PGH
ID: 39711436
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
 
LVL 35

Accepted Solution

by:
mccarl earned 500 total points
ID: 39712946
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
 

Author Closing Comment

by:SLI-PGH
ID: 39725045
Awesome,    That was the ticket.  Thanks for your help.
0
 
LVL 35

Expert Comment

by:mccarl
ID: 39725550
Not a problem, glad to help! :)
0

Featured Post

Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question