Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Retrieve PK from Java Insert while auto commit off

Posted on 2013-12-06
14
Medium Priority
?
529 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Basic understanding on "OO- Object Orientation" is needed for designing a logical solution to solve a problem. Basic OOAD is a prerequisite for a coder to ensure that they follow the basic design of OO. This would help developers to understand the b…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

971 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