ActiveJDBC trying to insert rather than update?

[Robert Smith]

Hi all,

I am using:

1. ActiveJDBC 1.4.9

2. SQL Server 2012

I have a very simple table (called Updates) in which I track the last update times for a few components. It has 2 columns - "Component", a varchar(50) which is the primary key, and "LastUpdate" a datetime.

When I try to use ActiveJDBC to update the "LastUpdate" column of a row, it seems to be doing an insert, which fails due to primary key constraint.

Here is the code:

Update update = Update.findFirst("Component = ?", "Rally");

if (update != null)

{

logger.debug("Found existing record in Updates table");

update.set("LastUpdate", iso8601.format(currentTime)).saveIt();

}

And here is the result:

2014-05-16 11:13:02 DEBUG RallyToSQLServer:132 - Found existing record in Updates table

Exception in thread "main" org.javalite.activejdbc.DBException: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_Updates'. Cannot insert duplicate key in object 'dbo.Updates'. The duplicate key value is (Rally)., Query: INSERT INTO updates (lastupdate, component) VALUES (?, ?), params: 2014-05-16T18:13:02Z,Rally

at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.doExecutePreparedStatement(SQLServerPreparedStatement.java:404)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement$PrepStmtExecCmd.doExecute(SQLServerPreparedStatement.java:350)

at com.microsoft.sqlserver.jdbc.TDSCommand.execute(IOBuffer.java:5696)

at com.microsoft.sqlserver.jdbc.SQLServerConnection.executeCommand(SQLServerConnection.java:1715)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeCommand(SQLServerStatement.java:180)

at com.microsoft.sqlserver.jdbc.SQLServerStatement.executeStatement(SQLServerStatement.java:155)

at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeUpdate(SQLServerPreparedStatement.java:314)

at org.javalite.activejdbc.DB.execInsert(DB.java:551)

at org.javalite.activejdbc.Model.doInsert(Model.java:2160)

at org.javalite.activejdbc.Model.save(Model.java:2067)

at com.intel.spd.rally.models.db.Update.save(Update.java)

at org.javalite.activejdbc.Model.saveIt(Model.java:1994)

at com.intel.spd.rally.models.db.Update.saveIt(Update.java)

at com.intel.spd.rally.RallyToSQLServer.run(RallyToSQLServer.java:134)

at com.intel.spd.rally.RallyToSQLServer.main(RallyToSQLServer.java:105)

Caused by: com.microsoft.sqlserver.jdbc.SQLServerException: Violation of PRIMARY KEY constraint 'PK_Updates'. Cannot insert duplicate key in object 'dbo.Updates'. The duplicate key value is (Rally).

... 17 more

Why is it trying to insert a new row? What can I do?

Cheers,

Robert

rjmp...@gmail.com

[Igor Polevoy]

Robert, you got two things incorrect:

1. Looks like you do not have a column "id". Without it, ActiveJDBC cannot find out if it needs to execute update or insert. Please, see http://javalite.io/surrogate_primary_keys

2. What you are actually trying to do is already done by the framework: http://javalite.io/autogenerated_fields. Simply add a column "updated_at" and it will automatically keep it up to date

tx

[Igor Polevoy]

Yeah, this is a correct step to fix your issue.

However, it is fully documented on the same page I referred above: http://javalite.io/surrogate_primary_keys - see "Override primary key" section

To answer your other question, please see the bottom section on that same page. 

tx

On Friday, May 16, 2014 1:52:25 PM UTC-5, Robert Smith wrote:

I think that I've found the problem. It looks like ActiveJDBC assumes the primary key will be a column called "ID". Since this is not the case in my table, I had to add an annotation to my Update class, as follows:

@IdName(value = "Component")

public class Update extends Model 

{

}

This seems to have solved the immediate problem. 

I don't like to complain (especially when using high quality, free, software) but the documentation seems to be lacking when it comes to overriding default behavior using annotations. I only found this through experimentation and deduction (calling update.exists() threw an exception about missing "ID" column). It must be quite a common case where your primary key wont be called "ID". Some JavaDoc would be wonderful, too.

Actually, come to think of it, how would I handle a case where a table has two or more primary keys? I DO have such a table...

Cheers,

Robert

[Robert Smith]

Thanks, Igor.

My appologies. I guess I should read the entire documentation before posting. I missed the section that you referenced. I was looking for the term "annotations", and missed the obvious.

Cheers,

Robert

[Igor Polevoy]

No problem, this is just an indicator that documentation needs to somehow be organized better. We are working on it :)

[Igor Polevoy]

BWT, you can jut write this:

@IdName("Component")

public class Update extends Model {}

tx