Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Retrieve PK from Java Insert while auto commit off

Posted on 2013-12-06
14
Medium Priority
?
518 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 28

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 27

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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 28

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 28

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 36

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 36

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 36

Accepted Solution

by:
mccarl earned 2000 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 36

Expert Comment

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

Featured Post

The top UI technologies you need to be aware of

An important part of the job as a front-end developer is to stay up to date and in contact with new tools, trends and workflows. That’s why you cannot miss this upcoming webinar to explore the latest trends in UI technologies!

Question has a verified solution.

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

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
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:
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.

670 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