John, I won't hold it against you if you can't help me on MS SQL Server. But, somebody out there should be able to shed some light on the problem.
In summary, I'm using autoincrement=true to let the DB assign the PK, and then expect OJB to return the PK value in the data object after an insert.
OJB version:rc5
Sequence manager?org.apache.ojb.broker.util.sequence.SequenceManagerNativeImpl
platformImpl ?MsSQLServer
What is your query?Am returning "SELECT SCOPE_IDENTITY()" in an overloaded PlatformMsSqlImpl.getLastInsertIdentityQuery() method.
exceptions?None-Query returns nothing.
debug info?I've traced the code and everything appears to process OK, except the ID's value is 0. In order to highlight and isolate the problem, I've modified JdbcAccessImpl.executeInsert() method as follows. I know the query is correct by running test code not using OJB. Note that this problem is reproduceable in my test program IF the Statement object is not closed first.
public void executeInsert(ClassDescriptor cld, Object obj) throws PersistenceBrokerException
{
if(logger.isDebugEnabled()) logger.safeDebug("executeInsert", obj);
PreparedStatement stmt = null;
try
{
stmt = broker.serviceStatementManager().getInsertStatement(cld);
if (stmt == null)
{
logger.error("getInsertStatement returned a null statement");
throw new PersistenceBrokerException("getInsertStatement returned a null statement");
}
broker.serviceStatementManager().bindInsert(stmt, cld, obj);
if (logger.isDebugEnabled())
logger.debug("executeInsert: " + stmt);stmt.executeUpdate();
// Harvest any return values.
this.harvestReturnValues(cld.getInsertProcedure(), obj, stmt);// This is test code I've inserted to isolate my problem....
Connection connection = stmt.getConnection();
stmt.close();
Statement statement = connection.createStatement();
ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY()");
while (rs.next())
{
long val = rs.getLong(1);
System.out.println("PK=" + val); // IS ALWAYS 0. rs.get(1) is NULL
}
}
From: "McCaffrey, John G." <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: 'OJB Users List' <[EMAIL PROTECTED]> Subject: RE: Primary Key from Insert Date: Fri, 26 Mar 2004 10:37:28 -0600
Glenn, well, I am no expert at this, but I would like to try to help. can you give me a quick run down of your situation again (I know its in previous posts, just give me a summary)
details: OJB version Sequence manager? platformImpl ? (what is your query?) debug info? exceptions?
-----Original Message----- From: Glenn Barnard [mailto:[EMAIL PROTECTED] Sent: Thursday, March 25, 2004 9:05 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: RE: Primary Key from Insert
I'm dead in the water. Can't get the PK query to return anything. Am at wits
end as all my development efforts are dependent on this working. No further responses from the OJB guys leading me to try something else.
>From: "McCaffrey, John G." <[EMAIL PROTECTED]>
>To: 'Glenn Barnard' <[EMAIL PROTECTED]>
>Subject: RE: Primary Key from Insert
>Date: Thu, 25 Mar 2004 17:05:45 -0600
>
>yeah, I upgraded to rc 5 to fix a bug.
>everything seems to work out ok, but i did have to break the object apart.
>I had the mapping for object A contains object B, but object A has the
>identity, and needs to be inserted first but OJB always wants to insert
>object B first, and it fails on the foriegn Key constraint.
>
>How is it working for you?
>
>-----Original Message-----
>From: Glenn Barnard [mailto:[EMAIL PROTECTED]
>Sent: Thursday, March 25, 2004 4:35 PM
>To: [EMAIL PROTECTED]
>Subject: RE: Primary Key from Insert
>
>
>John, have you had any luck?
>
>
> >From: "McCaffrey, John G." <[EMAIL PROTECTED]>
> >To: 'Glenn Barnard' <[EMAIL PROTECTED]>
> >Subject: RE: Primary Key from Insert
> >Date: Wed, 17 Mar 2004 17:58:58 -0600
> >
> >Glenn,
> >the query that I was told to use for DB2 is
> >SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1
> >
> >which I need to select after doing the insert, but before doing the
>commit.
> >I looked into extending the PlatformDB2Impl but I didn't see how you
>could
> >tell ojb to use it.
> >
> >-----Original Message-----
> >From: Glenn Barnard [mailto:[EMAIL PROTECTED]
> >Sent: Wednesday, March 17, 2004 5:08 PM
> >To: [EMAIL PROTECTED]
> >Subject: RE: Primary Key from Insert
> >
> >
> >
> >John, sorry for going direct... Got a DB2 question...
> >
> >How does DB2 get the PK from the last insert? If you've read my thread,
>MS
> >SQL Server provides the function SCOPE_IDENTITY() and Oracle has
> >field.NextVal.
> >
> >
> >
> > >From: "McCaffrey, John G." <[EMAIL PROTECTED]>
> > >Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
> > >To: 'OJB Users List' <[EMAIL PROTECTED]>
> > >Subject: RE: Primary Key from Insert
> > >Date: Wed, 17 Mar 2004 13:52:25 -0600
> > >
> > >I have this same issue. I need to use the PlatformDB2Impl and use its
> > >getLastInsertIdentity() method to pick up the last Identity that was
> > >created.
> > >I noticed that the interface is defined as
> > >public String getLastInsertIdentityQuery(String tableName);
> > >and what I specifically need is access to the pb that was used to
>insert
> > >the
> > >last row, and I need it before the commit is called (I need to be
>within
> >my
> > >transaction).
> > >
> > >I don't know how to solve this, so I was told (by a team member) to
>break
> > >apart my OJB mapping (for my multi-joined object) and insert one table,
> >get
> > >the identity, then insert the children objects (that are relying on
>that
> >ID
> > >as a FK).
> > >What I would really like is to tell OJB to get the Identity, for the
> > >parent,
> > >and insert it into the children, all on its own.
> > >
> > >Let me know if you solve your problem
> > >
> > >
> > >
> > >-----Original Message-----
> > >From: Glenn Barnard [mailto:[EMAIL PROTECTED]
> > >Sent: Wednesday, March 17, 2004 12:57 PM
> > >To: [EMAIL PROTECTED]
> > >Subject: Re: Primary Key from Insert
> > >
> > >
> > >Interesting exercise, but it didn't work.
> > >
> > >I extended PlatformMsSQLServerImpl() and have it's
> >getLastInsertIdentity()
> > >method return "SELECT SCOPE_IDENTITY() AS newID". It tries to process,
> >but
> > >the resultset contains a null.
> > >
> > >Which means that the connection/statement used to executeUpdate is not
> >the
> > >same as the one performing the executeQuery. I know this because I went
> >to
> > >a
> > >
> > >rudimentary JDBC application I wrote and implemented the following
> > >statements:
> > >
> > >statement.executeUpdate("INSERT INTO <table> (<fields>) VALUES
> >(<values>);
> > >ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY() AS
> >newID");
> > >while (rs.next())
> > > {
> > > long pk = rs.getLong(1);
> > > System.out.("PK=" + pk);
> > > }
> > >
> > >This returns the correct value. If you create another statement object
> > >(hence connection) between the executeUpdate and executeQuery, the
>result
> > >returns 0 (null). Which is as expected.
> > >
> > >Any suggestions?
> > >
> > >
> > > >From: Armin Waibel <[EMAIL PROTECTED]>
> > > >Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
> > > >To: OJB Users List <[EMAIL PROTECTED]>
> > > >Subject: Re: Primary Key from Insert
> > > >Date: Wed, 17 Mar 2004 17:43:02 +0100
> > > >
> > > >Glenn Barnard wrote:
> > > >
> > > >>Armin, thanks for the direction. I've switched over to the
> > > >>NativeSequenceManager. It calls lastInsertSelect() which in turn
> > >invokes
> > > >>the supported platforms getLastInsertIdentity(). The platform it's
> > >loading
> > >
> > > >>is the PlatformMsSQLServerImpl and it doesn't overload
> > > >>PlatformDefaultImpl's stub for getLastInsertIdentity which throws a
> >not
> > > >>implemented exception.
> > > >>
> > > >>I'm back on version 1.4 and will upgrade immediately to 1.5. If that
> > > >>version does not have the correct getLastInsertIdentity, then I will
> > >have
> > > >>to look into implementing it elsewhere, perhaps by extending
> > > >>PlatformMsSQLServerImpl with a custom getLastInsertIdentity method
> >that
> > > >>simply returns "SELECT SCOPE_IDENTITY() AS newID".
> > > >>
> > > >
> > > >In rc5 method was not implemented.
> > > >You can also use CVS version of OJB - it's stable (98%, to check you
> >can
> > > >run OJB junit test suite) and include identity column support in
> > > >PlatformMsSQLServerImpl.
> > > >
> > > >regards,
> > > >Armin
> > > >
> > > >>Thanks again for your help.
> > > >>
> > > >>
> > > >>>From: Armin Waibel <[EMAIL PROTECTED]>
> > > >>>Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
> > > >>>To: OJB Users List <[EMAIL PROTECTED]>
> > > >>>Subject: Re: Primary Key from Insert
> > > >>>Date: Wed, 17 Mar 2004 15:49:44 +0100
> > > >>>
> > > >>>Hi Glenn,
> > > >>>
> > > >>>why don't you use a identity based sequence manager implementation?
> > > >>>
> > > >>>http://db.apache.org/ojb/sequencemanager.html#nativeSequenceManager
> > > >>>
> > > >>>(seems that one part of this section was "corrupted" by Maven, with
> > > >>>"ant htmldoc" you can generate the local documentation)
> > > >>>
> > > >>>or your persistent capable objects can implement
> >PersistenceBrokerAware
> > > >>>interface and you assign the PK by hand in the afterInsert method
> > >(obtain
> > >
> > > >>>connection from PB instance and perform sql query by your own).
> > > >>>
> > > >>>regards,
> > > >>>Armin
> > > >>>
> > > >>>Glenn Barnard wrote:
> > > >>>
> > > >>>>
> > > >>>>I'm using SQL Server 2000 and need to get the primary key returned
> >in
> > > >>>>the data model after an insert. My research shows that the query
> > >"SELECT
> > >
> > > >>>>SCOPE_IDENTITY() AS newID" needs to be executed. What do I need to
> >do
> > >to
> > >
> > > >>>>get OJB to automatically populate the PK field in the data model
>it
> > > >>>>returns?
> > > >>>>
> > > >>>>_________________________________________________________________
> > > >>>>Get tax tips, tools and access to IRS forms - all in one place at
> >MSN
> > > >>>>Money! http://moneycentral.msn.com/tax/home.asp
> > > >>>>
> > > >>>>
> > >
> >
> >>>>---------------------------------------------------------------------
> > > >>>>To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > >>>>For additional commands, e-mail: [EMAIL PROTECTED]
> > > >>>>
> > > >>>>
> > > >>>>
> > > >>>
> > >
> >>>---------------------------------------------------------------------
> > > >>>To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > >>>For additional commands, e-mail: [EMAIL PROTECTED]
> > > >>>
> > > >>
> > > >>_________________________________________________________________
> > > >>Free up your inbox with MSN Hotmail Extra Storage. Multiple plans
> > > >>available. http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/
> > > >>
> > > >>
> > >
> >>---------------------------------------------------------------------
> > > >>To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > >>For additional commands, e-mail: [EMAIL PROTECTED]
> > > >>
> > > >>
> > > >>
> > > >
> > > >---------------------------------------------------------------------
> > > >To unsubscribe, e-mail: [EMAIL PROTECTED]
> > > >For additional commands, e-mail: [EMAIL PROTECTED]
> > > >
> > >
> > >_________________________________________________________________
> > >All the action. All the drama. Get NCAA hoops coverage at MSN Sports by
> > >ESPN. http://msn.espn.go.com/index.html?partnersite=espn
> > >
> > >
> > >---------------------------------------------------------------------
> > >To unsubscribe, e-mail: [EMAIL PROTECTED]
> > >For additional commands, e-mail: [EMAIL PROTECTED]
> > >
> > >---------------------------------------------------------------------
> > >To unsubscribe, e-mail: [EMAIL PROTECTED]
> > >For additional commands, e-mail: [EMAIL PROTECTED]
> > >
> >
> >_________________________________________________________________
> >All the action. All the drama. Get NCAA hoops coverage at MSN Sports by
> >ESPN. http://msn.espn.go.com/index.html?partnersite=espn
>
>_________________________________________________________________
>Get tax tips, tools and access to IRS forms - all in one place at MSN
>Money!
>
>http://moneycentral.msn.com/tax/home.asp
_________________________________________________________________ Get reliable access on MSN 9 Dial-up. 3 months for the price of 1! (Limited-time offer) http://join.msn.com/?page=dept/dialup&pgmarket=en-us&ST=1/go/onm00200361ave/ direct/01/
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
_________________________________________________________________
FREE pop-up blocking with the new MSN Toolbar � get it now! http://toolbar.msn.com/go/onm00200415ave/direct/01/
--------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
