• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

getGeneratedKeys() to get inserted primary key after executeBatch() statement return empty result se

 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using mysql DB. I need inserted primary keys after batch job. I have used getGeneratedKeys() after executeBatch() which is not working.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does your code look like?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
my code look like this :



String insertQueryPOItemCharges = "insert into "+PurchaseOrderCharges.TABLE_NAME
+" "+PurchaseOrderCharges.getInsertColumns();

String sLogicalDbName = db1.getLogicalDBName(appId, orgId);
UserInfo userInfo = new UserInfo();
userInfo.setGblUserId(userId);
userInfo.setOrgId(orgId);
userInfo.setAppId(appId);
userInfo.setLogicalDbName(sLogicalDbName);

connection = WfbUtilities.getConnection(userInfo);
ptmt2 = connection.prepareStatement(insertQueryPOItemCharges,PreparedStatement.RETURN_GENERATED_KEYS);

for (int i=1;i<=totalitems;i++)
{
ptmt2.setString(1,"val");
|
|
|
ptmt2.addBatch();
}

if(itemExist)
{
logger.info(" IN Execute Batch For Item Charge");
int[] insertCounts = ptmt2.executeBatch();
ResultSet rs = ptmt2.getGeneratedKeys();

while (rs.next())
{
logger.info(" IN Generate Keys");
java.sql.ResultSetMetaData rsMetaData = rs.getMetaData();
int columnCount = rsMetaData.getColumnCount();

for (int i = 1; i <= columnCount; i++)
{
String key = rs.getString(i);
System.out.println("key " + i + " is " + key);
}
logger.info(" IN Generate Keys End ");
}
}
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And what results do you see?
"Not working" doesn't tell us anything useful, I'm afraid.
 
Paul Clapham
Sheriff
Posts: 21588
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And are you using a database which supports getting generated keys? Does getting generated keys work if your inserts aren't in a batch?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am getting empty result set. I am using mysql DB. I have tried this with other ways like using extended insert also but not getting values. Second thing i tried as i was having autocommit = false that i make as true but not working. If anybody knows this let me know. If it is compatibility issue or feature not supported.



Thanks
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What does the insertCounts array contain?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
it shows count of particular row has been inserted or not it has nothing to deal with getgeneratedkeys method.
 
Paul Clapham
Sheriff
Posts: 21588
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes, but what does it contain? It certainly is relevant to getting generated keys -- if you didn't add any rows then there aren't any generated keys to be had. So... What does it contain?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It returns array of integer for inserted raw it is returning 1.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
OK, so first off can you post the code in code tags (there's a button for it in the reply window), as it's quite difficult to follow unformatted code.

Second, could you post the log section showing a flow through the code?

Finally, what version of MySQL are you using, including what version of JConnector, or whatever JDBC driver you are using?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator



I am using mysql version 5.6 and connector as java.sql.Connection.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Is your code really not formatted?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And a run through of that (with the logging for the value(s) in insertCounts) gives you what?
That bit in brackets (the extra logging) is important.

In fact I'd print the INSERT statement as well, as that seems to be generated.
As well as each set of parameters you are setting in the batch.

Because I have produced your basic structure there in 5.6 and it works fine (as expected), so it is going to be something to do with your setup, and you really need much more logging to track it down.
 
Paul Clapham
Sheriff
Posts: 21588
33
Eclipse IDE Firefox Browser MySQL Database
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
The existence of a WfbUtilities class makes it possible that your ptmt2 variable refers to some subclass of PreparedStatement which is a wrapper for whatever MySQL returns. Perhaps the implementer of that subclass was lazy and didn't get around to implementing the getGeneratedKeys() method.
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Oh yes, I also just noticed you didn't say what version of JConnector you're using.
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am not using jconnector but below is the sample code i am using for getting connection object. I do no think that problem might be with connection object as i am able to save the transaction data in database. I have tried using different logging but no error is shown.

 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
pragnesh pandya wrote:I am not using jconnector


So what driver are you using?
What is the name of the JDBC jar file?
 
pragnesh pandya
Greenhorn
Posts: 11
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am using below two jars for JDBC.

mysql-connector-java-5.1.10-bin.jar
mysql-connector-java-commercial-5.1.25-bin.jar
 
Dave Tolls
Ranch Hand
Posts: 2112
16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
And both those drivers produce the same problem?

Which just leaves a test run, showing the output with all the additional logging, and the code that shows where you have added additional logging, in code tags please.

But I'm beginning to suspect you're going to need to up the logging level inside the MySQL connector to see what it's doing.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic