Solved

Retrieve PK from Java Insert while auto commit off

Posted on 2013-12-06
14
490 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 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Viewers learn how to read error messages and identify possible mistakes that could cause hours of frustration. Coding is as much about debugging your code as it is about writing it. Define Error Message: Line Numbers: Type of Error: Break Down…
This video teaches viewers about errors in exception handling.

829 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