Character Translation
Is there any way to determine via the JDBC which code page is being used for character translation? My DBA swears we're using ISO-8859-1, but character 0x96 keeps coming out as 0x13. Thanks! _ Dont just search. Find. Check out the new MSN Search! http://search.msn.click-url.com/go/onm00200636ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Inserting 1:1 mapping
Fiona, let me relate how we're doing it. We would have 2 stored procedures, one for each table. We would insert the Payment first and get back the PK. Then update the Transaction's FK and insert it. One of our input arguments is a COMMIT flag so that we control when the commit or rollback is performed. To be honest, I don't know how much we coded on top of OJB, or if OJB has the ability to do this--I'm thinking it does. Oh, we use the SequenceManagerNextValImpl sequence manager, and our field descriptors look like: I hope this helps. Good Luck! From: "Fiona Magner" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: Re: Inserting 1:1 mapping Date: Fri, 28 May 2004 18:10:23 +0100 Hi Glenn, Yes I had to insert the into my repository_database and also insert the autoincrement=true into my class descriptor. I guess I didn't play around with enough combinations! As for the Stored Proc problem, this is now no longer a problem with primary keys :). I know I will have to get the primary key of the Payments within my SP to pass to my Transactions insert but my problem is this. I have two class descriptors that are linked through a foreign key. I want to be able to insert a record for each using only one stored procedure (ie within a database transaction). I can use a SP to map attributes from one class descriptor to the runtime arguments for the SP but cannot map arguments from the foreign class descriptor in the same tag. I hope I am making sense. Anyway I appreciate any input. I will keep plugging away at it to see if I can get any joy Thanks! Fiona - Original Message - From: "Glenn Barnard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 28, 2004 5:27 PM Subject: Re: Inserting 1:1 mapping > Fiona, glad it worked for. Was that with or without the > autoincrement="true"??? > > As per your stored proceduresThis is a guess only. Since OJB won't know > what's returned from the stored procedure, I'd have to say you have to take > full control and insert the Payment first, get the PK from the stored > procedure and then insert the Transaction. Hopefully, someone else with more > experience with stored procedure can help you. > > > >From: "Fiona Magner" <[EMAIL PROTECTED]> > >Reply-To: "OJB Users List" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Subject: Re: Inserting 1:1 mapping > >Date: Fri, 28 May 2004 15:19:23 +0100 > > > >Hi there, > > > >Yes that was the problem alright! I was under the impression that if you > >increment the id field at the database level that you didn't need to > >specify > >a SequenceManager - thanks! Its working fine for me now when I do a > >straighforward insert i.e. I am getting the payment inserted and the > >transaction inserted with the correct payment id. However now I am trying > >to > >get the insert to work using a stored procedure. I can get the transaction > >to get inserted when I use the for the transactions > >class > >descriptor. However I am not able to get it working to insert two entities > >together (my Transactions and Payments record). Does anyone know if its > >possible to use stored procedures to insert two separate class decriptors > >that are linked by a foreign key. To reiterate: > > > >Transactions-> class descriptor specifies all transactions > >transactionid (pk)entity attributes to send to SP (using > >) > >paymentid (fk) > > > >Payments-> nothing in this class descriptor for SP > >insert > >paymentid (pk) > > > >The problem is that I cannot refer to values of the Payments attributes in > >the of the transactions class. Would it be necessary to > >create a class that 'joins' Payments and Transactions together (eg in a new > >Deposits entity) and then map that to a class descriptor which would have a > > that could access all the attributes of both classes? > > > >Thanks again for all your help! > > > >Fiona > > > > > > > >- Original Message - > >From: "Glenn Barnard" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Sent: Friday, May 28, 2004 2:08 PM > >Subject: RE: Inserting 1:1 mapping > > > > > > > Fiona, a couple of questions/issues... > > > > > > 1) Iin your repository.xml, have you declared the as > > > SequenceManagerNativeImpl? Let me know if you need the syntax > > > 2) I think you need also "
Re: Inserting 1:1 mapping
Fiona, glad it worked for. Was that with or without the autoincrement="true"??? As per your stored proceduresThis is a guess only. Since OJB won't know what's returned from the stored procedure, I'd have to say you have to take full control and insert the Payment first, get the PK from the stored procedure and then insert the Transaction. Hopefully, someone else with more experience with stored procedure can help you. From: "Fiona Magner" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: Re: Inserting 1:1 mapping Date: Fri, 28 May 2004 15:19:23 +0100 Hi there, Yes that was the problem alright! I was under the impression that if you increment the id field at the database level that you didn't need to specify a SequenceManager - thanks! Its working fine for me now when I do a straighforward insert i.e. I am getting the payment inserted and the transaction inserted with the correct payment id. However now I am trying to get the insert to work using a stored procedure. I can get the transaction to get inserted when I use the for the transactions class descriptor. However I am not able to get it working to insert two entities together (my Transactions and Payments record). Does anyone know if its possible to use stored procedures to insert two separate class decriptors that are linked by a foreign key. To reiterate: Transactions-> class descriptor specifies all transactions transactionid (pk)entity attributes to send to SP (using ) paymentid (fk) Payments-> nothing in this class descriptor for SP insert paymentid (pk) The problem is that I cannot refer to values of the Payments attributes in the of the transactions class. Would it be necessary to create a class that 'joins' Payments and Transactions together (eg in a new Deposits entity) and then map that to a class descriptor which would have a that could access all the attributes of both classes? Thanks again for all your help! Fiona - Original Message - From: "Glenn Barnard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, May 28, 2004 2:08 PM Subject: RE: Inserting 1:1 mapping > Fiona, a couple of questions/issues... > > 1) Iin your repository.xml, have you declared the as > SequenceManagerNativeImpl? Let me know if you need the syntax > 2) I think you need also "autoincrement="true"" in the > for PaymentID in the Payments table > > > > >From: "Fiona Magner" <[EMAIL PROTECTED]> > >Reply-To: "OJB Users List" <[EMAIL PROTECTED]> > >To: <[EMAIL PROTECTED]> > >Subject: Inserting 1:1 mapping > >Date: Thu, 27 May 2004 17:23:15 +0100 > > > >Hi there: > > > >I am just learning OJB and so far I think its great! I am having some > >problems inserting 1:1 records that I was hoping someone could help me > >with. I have a table Transactions that has a reference (1:1) to a table > >Payments: > > > >Transactions > >transactionid (pk) > >paymentid (fk) > > > >Payments > >paymentid (pk) > > > >These therefore correspond to my entity classes like so: > > > >Transactions > >long transactionid; > >long paymentid; > >Payments thePayment; > > > >Payments > >long paymentid; > > > >In my repository_user file have the following: > > > > > > > >jdbc-type="INTEGER" primarykey="true" access="readonly"/> > > .. > > > >jdbc-type="INTEGER" /> > > > >auto-update="false"> > > > > > > > >and > > > > > > > >jdbc-type="INTEGER" primarykey="true" access="readonly" /> > > . > > > > > >The access=readonly is because I am using MSSQL and need to use the > >autoincrement for the primary key field used by the database (as I also > >have legacy systems working from the same database). When I try to do an > >insert on the 1:1 mapping above I get a payment record created and a > >transaction record created successfully but the paymentid in the > >transaction record is 0. Can anyone see why this would happen. I have gone > >through all my mappings with a fine tooth comb and also checked to see if > >any others are having the same problem. I am using ODMG (therefore I have > >set the auto-update=false in the reference desciptor as recommended). I > >would appreciate any help anyone has on this! > > > >Thanks > >Fiona > > _
RE: Inserting 1:1 mapping
Fiona, a couple of questions/issues... 1) Iin your repository.xml, have you declared the as SequenceManagerNativeImpl? Let me know if you need the syntax 2) I think you need also "autoincrement="true"" in the for PaymentID in the Payments table From: "Fiona Magner" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Subject: Inserting 1:1 mapping Date: Thu, 27 May 2004 17:23:15 +0100 Hi there: I am just learning OJB and so far I think its great! I am having some problems inserting 1:1 records that I was hoping someone could help me with. I have a table Transactions that has a reference (1:1) to a table Payments: Transactions transactionid (pk) paymentid (fk) Payments paymentid (pk) These therefore correspond to my entity classes like so: Transactions long transactionid; long paymentid; Payments thePayment; Payments long paymentid; In my repository_user file have the following: .. and . The access=readonly is because I am using MSSQL and need to use the autoincrement for the primary key field used by the database (as I also have legacy systems working from the same database). When I try to do an insert on the 1:1 mapping above I get a payment record created and a transaction record created successfully but the paymentid in the transaction record is 0. Can anyone see why this would happen. I have gone through all my mappings with a fine tooth comb and also checked to see if any others are having the same problem. I am using ODMG (therefore I have set the auto-update=false in the reference desciptor as recommended). I would appreciate any help anyone has on this! Thanks Fiona _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: How to use DUAL table for multiple types of queries
Andrew, thanks for the quick response. Unfortunately, we're already using a
ReportQuery. Here's the code snippet calling OJB:
String[] columns = { "ypas.f_GetRotationDate ( " + listingId +
"," + locationId + ",'"
+ zuluStartDate + "') rotationDate"
};
Criteria criteria = new Criteria();
Query contactsQuery =
QueryFactory.newReportQuery(RotationGenie.class,
columns, criteria, false);
collection = broker.getCollectionByQuery(contactsQuery);
From: "Clute, Andrew" <[EMAIL PROTECTED]>
Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Subject: RE: How to use DUAL table for multiple types of queries
Date: Thu, 20 May 2004 14:34:21 -0400
Based on looking in
org.apache.ojb.broker.accesslayer.sql.SqlSelectStatement.getStatement()
-- it looks like if no columns are specified, then it will do the
multimapped object select, which you are seeing.
It is clearer as what to do in the HEAD version, but in RC5, it looks
like if you change it over to a ReportQuery, and ask for only that one
column that you need, it would work.
if (columns == null || columns.length == 0)
{
/**
* MBAIRD: use the appendListofColumnsForSelect, as it
finds
* the union of select items for all object mapped to
the same table. This
* will allow us to load objects with unique mapping
fields that are mapped
* to the same table.
*/
columnList =
appendListOfColumnsForSelect(getSearchClassDescriptor(), stmt);
}
else
{
columnList = appendListOfColumns(columns, stmt);
}
In this case, columns are ones that are specified, which ReportQuery is
used for.
-Andrew
-Original Message-
From: Glenn Barnard [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 20, 2004 2:10 PM
To: [EMAIL PROTECTED]
Subject: How to use DUAL table for multiple types of queries
I posted this yesterdayWould someone PLEASE help me. Am running out
of time.
We use Oracle 9 and have several different functions we can call. In
OJB, they are all mapped with the table name of DUAL. For example:
SELECT function(args) AS column FROM DUAL
Our repository.xml has a table entry for each function so that the
result is kept in it's own class. For example:
and:
The problem is that when OJB goes to extract the values from the
result set, it tries to do so for 2 columns, fieldName1 and
fieldName2.
I thought that by specifiying the class name I wanted (e.g., Class1)
that OJB would only get the fields for that class. Can anyone advise
me how I can do this without resorting to having only one class for
a function calls? Oh, I'm using a customized rc5 and cannot upgrade
until after this release ships (a timing/resource issue).
Thanks ya'll!
_
Stop worrying about overloading your inbox - get MSN Hotmail Extra
Storage!
http://join.msn.click-url.com/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]
_
Get 200+ ad-free, high-fidelity stations and LIVE Major League Baseball
Gameday Audio! http://radio.msn.click-url.com/go/onm00200491ave/direct/01/
-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
How to use DUAL table for multiple types of queries
I posted this yesterdayWould someone PLEASE help me. Am running out of time. We use Oracle 9 and have several different functions we can call. In OJB, they are all mapped with the table name of DUAL. For example: SELECT function(args) AS column FROM DUAL Our repository.xml has a table entry for each function so that the result is kept in it's own class. For example: and: The problem is that when OJB goes to extract the values from the result set, it tries to do so for 2 columns, fieldName1 and fieldName2. I thought that by specifiying the class name I wanted (e.g., Class1) that OJB would only get the fields for that class. Can anyone advise me how I can do this without resorting to having only one class for a function calls? Oh, I'm using a customized rc5 and cannot upgrade until after this release ships (a timing/resource issue). Thanks ya'll! _ Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! http://join.msn.click-url.com/go/onm00200362ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
How to use DUAL table for multiple types of queries
We use Oracle 9 and have several different functions we can call. In OJB, they are all mapped with the table name of DUAL. For example: SELECT function(args) AS column FROM DUAL Our repository.xml has a table entry for each function so that the result is kept in it's own class. For example: and: The problem is that when OJB goes to extract the values from the result set, it tries to do so for 2 columns, fieldName1 and fieldName2. I thought that by specifiying the class name I wanted (e.g., Class1) that OJB would only get the fields for that class. Can anyone advise me how I can do this without resorting to having only one class for a function calls? Oh, I'm using a customized rc5 and cannot upgrade until after this release ships (a timing/resource issue). Thanks ya'll! _ Learn to simplify your finances and your life in Streamline Your Life from MSN Money. http://special.msn.com/money/0405streamline.armx - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: extent mapping (was: another JOIN question)
WOW! Are you saying name="super" works Or, just a suggestion. This is some good s! From: Phil Warrick <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: extent mapping (was: another JOIN question) Date: Wed, 12 May 2004 11:29:31 -0400 Hi all, While reading this thread, I came across something that looks like an improved way to map extents. (reference-descriptor name - "super" etc.) Have I been sleeping while this development took place? This seems to be undocumented. Phil - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] _ Stop worrying about overloading your inbox - get MSN Hotmail Extra Storage! http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1/go/onm00200362ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: Retrieve auto-incremeneted primary-key?
It should be returned in the data object that was just inserted. From: "Mindy Pereira" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: "OJB Users List" <[EMAIL PROTECTED]> Subject: Retrieve auto-incremeneted primary-key? Date: Thu, 29 Apr 2004 11:21:28 -0400 Is there a way to retrieve the id of an object with an autoincremented primary key that was just inserted using the broker.store() method? Thanks. - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] _ MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://toolbar.msn.com/go/onm00200413ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: auto increment not working
Nilesh, if you are still having difficulty, it is time to step through the code with a debugger, like Eclipse. But first, you'll need to get the OJB source code and compile it (at least org/apache/ojb/broker/util/sequence/SequenceManagerNativeImpl) in your IDE. This because rc5 and rc6 are not compiled with debug on. The methods you want to focus on are afterStore() and getLastInsert(). If you do not get to afterStore, get org/apache/ojb/broker/accesslayer/JdbcAccessImpl and step through executeInsert(). It would seem your metadata is incorrect, or repository.xml is not setup for NativeImpl. If you're getting this fa...In SequenceManagerNativeImpl.getLastInsert(), step into lastInsertSelect() and watch with particular detail the SQL statement returned from the platform class. This is the heart of the code that gets the PK value assigned by your DB. It was also the source of all my problems with MS SQL Server! Good luck and let us know how you fare. From: Armin Waibel <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: auto increment not working Date: Sat, 24 Apr 2004 10:31:35 +0200 Hi Nilesh, >class="com.haystack.emailmarketingchoice.member.MemberVO" > >table="tblMember"> > > > > >jdbc-type="BIGINT" primarykey="true" autoincrement="true" > >access="readonly" /> > > > > > > As Glenn said your mapping looks fine. There is a test case in OJB test suite called broker.sequence.NativeIdentifierTest. This test is valid for Hsql and MySQL. I don't use MySQL personally, but some other developer use it and test seems to pass. Only problem I know about is with old versions of MySQL, seems they don't support the used query to get the last inserted id (in that case you have to patch the PlatformMySQLImpl class). >>>I am using MySQL in which I have also used the auto-increment constraint >>>for the Primarykey ( bigint(20) ) I don't know the best setting for "member_id" column, but shouldn't it be NATIVE_ID int(20) or something else instead bigint(20) (sorry if I tell nonsense)? regards, Armin Nilesh wrote: Thanks Glenn, Yes I am using OJB rc6. And I have also tried without using any sequence manager. Do I neeed to create my own sequence manager. How will it help and how do I do that ? Please tell me if you have any idea. Thanks again. Regards, Nilesh. - Original Message - From: "Glenn Barnard" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> Sent: Friday, April 23, 2004 8:56 PM Subject: RE: auto increment not working Your settings look good. I use this technique with MS SQL Server and all is working well. Please note which version of OJB you're using. Hopefully, its rc6! From: "Nilesh" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> CC: <[EMAIL PROTECTED]> Subject: auto increment not working Date: Sun, 22 Feb 2004 19:40:34 +0530 In my OJB (using PersistenceBroker ) application auto-increment is not working. I am using MySQL in which I have also used the auto-increment constraint for the Primarykey ( bigint(20) ) For my database definition i have used the "SequenceManagerNativeImpl" className="org.apache.ojb.broker.util.sequence.SequenceManagerNativeImpl"> also in the mapping file i have i have also tried without the --> access="readonly" but it generates a new 20 digit "auto-increment" number only for the first insert, for the subsequent inserts it generates the same number and a Duplicate Entry error arises. I have also tried the following sequence managers SequenceManagerMySQLImpl SequenceManagerSeqHiLoImpl with and without these attributes attribute-value="1"/> But ALL Failed. Have any one of you tried this. Please let me know. Thanks in advance for your interest in my problem. Regards, Arjun. _ From must-see cities to the best beaches, plan a getaway with the Spring Travel Guide! http://special.msn.com/local/springtravel.armx - 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] - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-
RE: auto increment not working
Your settings look good. I use this technique with MS SQL Server and all is working well. Please note which version of OJB you're using. Hopefully, its rc6! From: "Nilesh" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: <[EMAIL PROTECTED]> CC: <[EMAIL PROTECTED]> Subject: auto increment not working Date: Sun, 22 Feb 2004 19:40:34 +0530 In my OJB (using PersistenceBroker ) application auto-increment is not working. I am using MySQL in which I have also used the auto-increment constraint for the Primarykey ( bigint(20) ) For my database definition i have used the "SequenceManagerNativeImpl" also in the mapping file i have i have also tried without the --> access="readonly" but it generates a new 20 digit "auto-increment" number only for the first insert, for the subsequent inserts it generates the same number and a Duplicate Entry error arises. I have also tried the following sequence managers SequenceManagerMySQLImpl SequenceManagerSeqHiLoImpl with and without these attributes But ALL Failed. Have any one of you tried this. Please let me know. Thanks in advance for your interest in my problem. Regards, Arjun. _ From must-see cities to the best beaches, plan a getaway with the Spring Travel Guide! http://special.msn.com/local/springtravel.armx - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: HowToDo INTERSECT query
Thank you, Jakob. That's what I ended up doing. From: Jakob Braeuchi <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: HowToDo INTERSECT query Date: Sun, 18 Apr 2004 22:23:55 +0200 hi glenn, unfortunately the only solutions is to use a queryBySql or you do the intersect processing programmatically. jakob Glenn Barnard wrote: I need to perform the following SQL query against my db. Can anyone steer me in the right direction? SELECT * FROM keywords WHERE keyword_id IN ( SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=1007869 intersect SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=20339 intersect SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=20341 ) _ Tax headache? MSN Money provides relief with tax tips, tools, IRS forms and more! http://moneycentral.msn.com/tax/workshop/welcome.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] _ Watch LIVE baseball games on your computer with MLB.TV, included with MSN Premium! http://join.msn.com/?page=features/mlb&pgmarket=en-us/go/onm00200439ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Problems saving a m:n relationship
Aye, readonly will do it! I had to look in the source code for
SequenceManagerNativeImpl to the extractIdentityColumnField() method. Try
it! You'll like it!
private FieldDescriptor extractIdentityColumnField(ClassDescriptor cld)
{
FieldDescriptor[] pkFields = cld.getPkFields();
for (int i = 0; i < pkFields.length; i++)
{
// to find the identity column we search for a autoincrement
// read-only field
if (pkFields[i].isAutoIncrement() &&
pkFields[i].isAccessReadOnly())
{
return pkFields[i];
}
}
return null;
}
From: "Stijn de Witt" <[EMAIL PROTECTED]>
Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
To: "OJB Users List" <[EMAIL PROTECTED]>
Subject: Re: Problems saving a m:n relationship
Date: Wed, 14 Apr 2004 15:52:35 +0200
Hi Glenn,
Thanks for your message. I had just completed converting the
sequencemanager
to HighLow and setting autoincrement to "ojb", and that seems to fix it,
however, I find this solution less elegant, because the sequencemanager is
ojb-specific and I would like to keep the base db model approachable form
multiple persistence layers. In short, I'd like to keep the responsibility
for generating the database id's in the database.
> OK. If you're having the DB assign the PK value, then you need an
attribute
> "autoincrement="true"" and "access="readonly"" in the field descriptor.
> Upon insert, that'll fire the OJB code that retrieves the value assigned
and
> updates the data model.
Interesting, this access="readonly" stuff...I didn't have that set...Maybe
that was the cause?
Also I had autoincrement set to "database" in my XDoclet comments.
I'll reconvert it back and see if this was the culprit. Thanks for your
time!
-Stijn
P.S. To my understanding I should use the NativeImpl sequence manager for
- Original Message -
From: "Glenn Barnard" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Wednesday, April 14, 2004 3:06 PM
Subject: Re: Problems saving a m:n relationship
> Just a stab at trying to help. Hopefully I have read your problem
statement
> correctly.
>
> I'm using auto increment for a MS SQL Server 2000 DB. It needs rc6--but
> that's just MS SQL Server.
>
> OK. If you're having the DB assign the PK value, then you need an
attribute
> "autoincrement="true"" and "access="readonly"" in the field descriptor.
> Upon insert, that'll fire the OJB code that retrieves the value assigned
and
> updates the data model.
>
>
> >From: Armin Waibel <[EMAIL PROTECTED]>
> >Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
> >To: OJB Users List <[EMAIL PROTECTED]>
> >Subject: Re: Problems saving a m:n relationship
> >Date: Wed, 14 Apr 2004 12:20:57 +0200
> >
> >Hi Stijn,
> >
> > > Is it recommended to use mysql's auto-increment with OJB? If so,
what
am
> >I
> > > doing wrong? If not, what other approach might you suggest?
> > >
> >
> >hmm, it should work. Your code seems ok. In OJB junit test suite we
have
> >one test using Identity column based PK fields. You can find this test
in
> >[db-ojb]/src/test/org/.../broker/sequence/NativeIdentifierTest
> >This test works for hsql and mysql and use SequenceManagerNativeImpl as
> >sequence-manager in jdbc-connection-decriptor (in test the SM was set
at
> >runtime). The metadata mappings can be found in
> >repository_junit_meta_seq.xml file.
> >I never try to store m:n relations using SequenceManagerNativeImpl, but
it
> >work for 1:n relation, thus I assume it will work for m:n too.
> >
> >regards,
> >Armin
> >
> >Stijn de Witt wrote:
> >>Ok, I have fixed my versioning problems. I can access the new
> >>PersistenceBroker methods and the code runs. To rule out any website
> >>related
> >>sideeffects, and to get more info about what is happening, I have
created
> >>a
> >>separate test app and set debugging levels more verbose.
> >>The test code looks like this:
> >>
> >>-
> >> public static void main(String[] args)
> >> {
> >> logger.info("main: Starting...");
> >> PropertyConfigurator.configure(args[0]);
> >>
> >> logger.info("main: Getting persistence broker...");
> >> PersistenceBroker broker = null;
> >> try
> >> {
> >> broker =
PersistenceBrokerFactory.defaultPer
Re: Problems saving a m:n relationship
Just a stab at trying to help. Hopefully I have read your problem statement
correctly.
I'm using auto increment for a MS SQL Server 2000 DB. It needs rc6--but
that's just MS SQL Server.
OK. If you're having the DB assign the PK value, then you need an attribute
"autoincrement="true"" and "access="readonly"" in the field descriptor.
Upon insert, that'll fire the OJB code that retrieves the value assigned and
updates the data model.
From: Armin Waibel <[EMAIL PROTECTED]>
Reply-To: "OJB Users List" <[EMAIL PROTECTED]>
To: OJB Users List <[EMAIL PROTECTED]>
Subject: Re: Problems saving a m:n relationship
Date: Wed, 14 Apr 2004 12:20:57 +0200
Hi Stijn,
> Is it recommended to use mysql's auto-increment with OJB? If so, what am
I
> doing wrong? If not, what other approach might you suggest?
>
hmm, it should work. Your code seems ok. In OJB junit test suite we have
one test using Identity column based PK fields. You can find this test in
[db-ojb]/src/test/org/.../broker/sequence/NativeIdentifierTest
This test works for hsql and mysql and use SequenceManagerNativeImpl as
sequence-manager in jdbc-connection-decriptor (in test the SM was set at
runtime). The metadata mappings can be found in
repository_junit_meta_seq.xml file.
I never try to store m:n relations using SequenceManagerNativeImpl, but it
work for 1:n relation, thus I assume it will work for m:n too.
regards,
Armin
Stijn de Witt wrote:
Ok, I have fixed my versioning problems. I can access the new
PersistenceBroker methods and the code runs. To rule out any website
related
sideeffects, and to get more info about what is happening, I have created
a
separate test app and set debugging levels more verbose.
The test code looks like this:
-
public static void main(String[] args)
{
logger.info("main: Starting...");
PropertyConfigurator.configure(args[0]);
logger.info("main: Getting persistence broker...");
PersistenceBroker broker = null;
try
{
broker = PersistenceBrokerFactory.defaultPersistenceBroker();
logger.info("main: Assigning data to persistent objects...");
Person person = new Person();
Address address = new Address();
person.getAddresses().add(address);
person.setFirstName("stijn");
person.setMiddleName("de");
person.setLastName("Witt");
address.setStreet("Graafseweg");
address.setHouseNr("7");
logger.info("main: Starting transaction...");
broker.beginTransaction();
Address tmp;
Iterator it = person.getAddresses().iterator();
while (it.hasNext())
{
logger.info("main: Store address...");
broker.store(it.next());
}
logger.info("main: Store person...");
broker.store(person);
broker.serviceBrokerHelper().link(person, true);
logger.info("main: Commit transaction...");
broker.commitTransaction();
}
catch(Exception e)
{
logger.error("main: ERROR getting persistence broker:");
logger.error("main: " + e.getMessage());
}
logger.info("main: Done.");
}
-
Here is a fragment of the output:
-
2004-04-14 11:41:48,500 [main] DEBUG ConnectionFactoryAbstractImpl -
Create
new connection using DriverManager: [EMAIL PROTECTED]
2004-04-14 11:41:48,500 [main] DEBUG ConnectionManagerImpl - Request new
connection from ConnectionFactory: [EMAIL PROTECTED]
2004-04-14 11:41:48,500 [main] DEBUG ConnectionManagerImpl - localBegin
was
called for con [EMAIL PROTECTED]
2004-04-14 11:41:48,500 [main] DEBUG ConnectionManagerImpl - Try to change
autoCommit state to 'false'
2004-04-14 11:41:48,500 [main] INFO OjbTester - main: Store address...
2004-04-14 11:41:48,531 [main] DEBUG JdbcAccessImpl - executeInsert :
[EMAIL PROTECTED]
2004-04-14 11:41:48,531 [main] DEBUG SqlGeneratorDefaultImpl - SQL:INSERT
INTO bit_Address (id,street,houseNr,zipCode,city,state,country,type)
VALUES
(?,?,?,?,?,?,?,?)
2004-04-14 11:41:48,546 [main] DEBUG JdbcAccessImpl - executeInsert:
[EMAIL PROTECTED]: INSERT INTO bit_Address
(id,street,houseNr,zipCode,city,state,country,type) VALUES
(0,'Graafseweg','7','','','','',null)
2004-04-14 11:41:48,562 [main] DEBUG StatementManager - closeResources was
called
2004-04-14 11:41:48,562 [main] INFO CacheDistributor - Create new
ObjectCacheImplementation for 'default'
2004-04-14 11:41:48,562 [main] INFO OjbTester - main: Store person...
2004-04-14 11:41:48,562 [main] DEBUG JdbcAccessImpl - executeInsert :
[EMAIL PROTECTED]
2004-04-14 11:41:48,562 [main] DEBUG SqlGeneratorDefaultImpl - SQL:INSERT
INTO bit_Person
(id,salutation,title,initials,middleName,lastName,firstName,birthDate,nation
ality) VALUES (?,?,?,?,?,?,?,?,?)
2004-04-14 11:41:48,562 [main] DEBUG JdbcAccessImpl - executeInsert:
[EMAIL PROTECTED]: INSERT INTO bi
Re: Primary Key from Insert
Armin, I'm happy to report that version rc6 fixed my problem. Am now getting the primary key returned in the data object upon insert with MS SQL Server. I can now proceed full steam ahead with my development. Thanks for your help a few weeks back. 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: Thu, 18 Mar 2004 01:34:41 +0100 Hi, Glenn Barnard wrote: Armin: My suspicions are confirmed. JdbcAccessImpl closes its Statement after the insert so that the SequenceManager must get another Statement (and, therefore a new connection) why should the SequenceManager lookup a new connection after closing of the statement in JdbcAccessImpl?? The connection used by the actual PB instance was managed by ConnectionManagerImpl class, this class is responsible to release the connection after use. During a PB-transaction always the same connection was used. On begin of the tx the connection was obtained, on commit or abort the connection was released. in order to submit the SCOPE_IDENTITY() query. Looks like I've got some serious re-tooling to make this work! Before I consider that, let me ask one more question: If I do a BeginTransaction before all this, how does OJB/JDBC keep the transactions coordinated among all the Statements so that the Commit persists the data? It would seem that the broker would keep the Statement/Connection and execute all the transactions with that same object I don't understand again (sorry my bad understanding english) transactions? plural? With beginTransaction you start one PB-tx. One connection will be associated with the used PB-instance. Till the abort/commitTransaction call all sql operation use the same connection. Nested transaction are not supported. Sorry if I couldn't help you. regards, Armin Thanks for being patient with me. 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/onm0
HowToDo INTERSECT query
I need to perform the following SQL query against my db. Can anyone steer me in the right direction? SELECT * FROM keywords WHERE keyword_id IN ( SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=1007869 intersect SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=20339 intersect SELECT keyword_id FROM listing_keywords WHERE listing_id=3289923 AND location_id=20341 ) _ Tax headache? MSN Money provides relief with tax tips, tools, IRS forms and more! http://moneycentral.msn.com/tax/workshop/welcome.asp - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
RE: [ann] new release 1.0.RC6
Congratulations!!! I have a couple of questions... First, will it have a debuggable version? rc5 didn't (at least as far as I could see)? McCaffrey, John G. <[EMAIL PROTECTED]> and I have been working with OJB to get the primary key from a native sequence manager. John's using DB2 and I'm using MS SQL Server. Seems like John's solution works, but mine isn't (yet). When can we get our solutions included with the base code? From: Thomas Mahler <[EMAIL PROTECTED]> Reply-To: [EMAIL PROTECTED] To: OJB Developers List <[EMAIL PROTECTED]>, OJB Users List <[EMAIL PROTECTED]> Subject: [ann] new release 1.0.RC6 Date: Sun, 28 Mar 2004 14:11:03 +0200 Dear all, We've got a new release! Given we find no showstoppers during the next week we will approach the Project Management Committee (PMC) of the db.apache.org project to get approval to relabel this release as the final 1.0 version. Armin worked hard to get our regression testbed rocksolid. We now have more than 600 testcases covering all major areas of the framework! I'm confident that this testsuite will help us to maintain and even increse the reliability of our code from release to release. Thanks to all who helped and contributed to this release! --Thomas From the release notes: - Release 1.0 rc6 - NEW FEATURES: - NOTES: - Repository.dtd has changed, don't forget to replace this file - Upgraded to the new Apache License, Version 2.0 - No support for HSQLDB 1.7.2RC1 yet - No support for Torque 3.1 yet - Add new PersistenceBrokerFactory implementation for use in managed environments when *only* the PB-api was used and participation in JTA transaction via Synchronization interface is needed. - Add new configuration property 'autoSync' to ObjectCacheDefaultImpl. Used to enable a simple synchronization mechanism to keep cache in sync with DB - Add new attribute values for reference-/collection-descriptor auto-update/-delete attribute. Now we have five possible values: none, link, object, true, false. The true, false values are now deprecated but will NOT be removed in near future (so don't panic!). CHANGES: - fix bug in internal table OJB_HL_SEQ, column type of MAX_ID was INTEGER but needs BIGINT to support Long on java side - no longer throw an exception when calling abortTransaction more than one time or an internal rollback (by ConnectionManager on the used connection) was already done - Add object-cache tag in standard jdbc-connection-descriptor in repository_database.xml to enable new 'autoSync' property of ObjectCacheDefaultImpl for all operations made by this DB connection - Allow infinite lifetime of cached objects in ObjectCacheDefaultImpl (handle with care ;-)) - odmg-api implementation, disable restore of transient objects on transaction abort, because we can't really restore the whole object with all references - now using ANTLR 2.7.2 and Unicode support in OQL queries - now using ANT 1.6.0 - RelationshipPrefetcher no longer modifies auto-retrieve setting of Relationship-Descriptor. This may result in additional sql-queries but is safer in a multi-threaded environment. - Added column methods to Criteria. addColumnEqualTo, addColumnIn etc. The column parameter of these methods will not be translated during sql-generation. These methods are mainly used for internal purpose. BUG FIXES: Please refer to our Bug tracking site (http://issues.apache.org/scarab/servlet/scarab/) under http://issues.apache.org/scarab/servlet/scarab/issues/id/OJBxxx to see details for a bug with id OJBxxx. - fix bug in MetadataManager, when 'per thread changes' is enabled and many different DescriptorRepository instances were used, gc can't collect unused instances because StatementManager doesn't release references to used DescriptorRepository instances. - fix bug in ObjectCacheDefaultImpl, it doesn't make sense to use a soft reference for the object wrapper of the cached object. Use a softreference for the cached object instead - fix bug in CacheDistributor (internal used ObjectCache implementation), each CacheDistributor instance have to held ObjectCache instances for the associated PersistenceBroker instance - fix documentation bug in ObjectCacheDefaultImpl, property 'timeout' use seconds instead of milliseconds - fix bug in new DList implementation (DListImpl_2), materialization of objects failed when DList instance itself was persisted (using db.bind(...)) and re-loaded within tx, but iteration over the list values was done outside of the tx - The bogus ODMG Distributed Lockmanagement feature has been replaced by a new Servlet based LockServer. Transaction isolation should now work properly even accross a cluster of JVMs. - fix bug in MtoNCollectionPrefetcher, multi-key handling is now supported. - ClassCastException with ManageableCollection implementations on m:n rela
RE: Primary Key from Insert
Thank you for the affirmation
There's a couple of things that are different. First, your query invokes a
function against a DB2 system table (by appearances anyway. This is similar
to Oracle, but not MS SQL Server.
Secondly, you created a new platform. So you must have changed the DTD.
The only other thing I can think of is that the MS JDBC classes don't work
right. I've been using the same ones for a few years now, so this weekend
I'll upgrade. I'm not holding out much hope since these are the same JDBC
files I used for my test code.
Thanks again!
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 12:57:23 -0600
well, it seems like everything you are doing is correct.
Here is what I am doing:
version = rc5
seq = SequenceManagerNativeImpl
platform = PlatformCustDb2Impl (extends PlatformDb2Impl)
override getLastInsertIdentityQuery()
qry = "SELECT IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1"
(the query for DB2 that is in rcd didn't work for me)
primary key setup = name="evntRqstId" column="EVNT_RQST_ID"
jdbc-type="INTEGER" primarykey="true" autoincrement="true"
access="readonly"
the access is read-only because I have to tell OJB not to check if its Null
(cuz it is Null before its inserted, bc DB2 will assign the value after its
inserted)
the data type is Integer, so that it can be Null. (if I used int, the
default value of 0 would get set, I think)
what is your java type?
I don't really know what else could be going on. Originally I thought that
the connection was a new connection so that the call to get the identity
would not happen within the same tx that the row was inserted with, but now
I see that that isn't the case.(though I haven't run any multithreaded
tests)
-Original Message-
From: Glenn Barnard [mailto:[EMAIL PROTECTED]
Sent: Friday, March 26, 2004 12:22 PM
To: [EMAIL PROTECTED]
Subject: RE: Primary Key from Insert
So you are getting the PK populated now with your DB2 application? VERY
GOOD! I hope my posts were helpful.
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
RE: Primary Key from Insert
So you are getting the PK populated now with your DB2 application? VERY
GOOD! I hope my posts were helpful.
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-
&g
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 () 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: Pri
Primary Key from Insert-Round 2
data.fieldaccess.PersistentFieldPrivilegedImpl #PersistentFieldClass=org.apache.ojb.broker.metadata.fieldaccess.PersistentFieldIntrospectorImpl #PersistentFieldClass=org.apache.ojb.broker.metadata.fieldaccess.PersistentFieldDynaBeanAccessImpl # # outdated deprecated versions: #PersistentFieldClass=org.apache.ojb.broker.metadata.fieldaccess.PersistentFieldMaxPerformanceImpl #PersistentFieldClass=org.apache.ojb.broker.metadata.fieldaccess.PersistentNestedFieldMaxPerformanceImpl #PersistentFieldClass=org.apache.ojb.broker.metadata.fieldaccess.PersistentFieldPropertyImpl # # # Component Intercepting for Profiling and Tracing # # By enabling an InterceptorClass all OJB components will use # this Interceptor. Interceptors allow advanced tracing and Profiling # of all component method calls. # This is currently an experimental feature useful only for OJB kernel developers. # #InterceptorClass=org.apache.ojb.broker.util.interceptor.TracingInterceptor # # # Transaction Management and assocation # # Use the LocalTxManager if you want the transaction to be associated by a thread OJBTxManagerClass=org.apache.ojb.odmg.LocalTxManager # Use the JTATxManager if you want the transaction to be associated via the Transaction # manager that is in your application server. #OJBTxManagerClass=org.apache.ojb.odmg.JTATxManager # # The TransactionManager is acquired in different ways dependent on the application server. # The JTATransactionManagerClass property allows you to specify the class that implements # the proper behaviour for finding the transaction manager. Only use when OJBTxManagerClass # is set to a factory that uses the application server transaction manager # (org.apache.ojb.odmg.JTATxManager) # # JBoss Transaction Manager Factory JTATransactionManagerClass=org.apache.ojb.odmg.transaction.JBossTransactionManagerFactory # Weblogic Transaction Manager Factory #JTATransactionManagerClass=org.apache.ojb.odmg.transaction.WeblogicTransactionManagerFactory # WebSphere transaction manager factory #JTATransactionManagerClass=org.apache.ojb.odmg.transaction.WebSphereTransactionManagerFactory # Orion transaction manager factory #JTATransactionManagerClass=org.apache.ojb.odmg.transaction.OrionTransactionManagerFactory # # # End of OJB.properties file # Here's the Respository.xml file ]> className="org.apache.ojb.broker.util.sequence.SequenceManagerNativeImpl"> &internal; &dccs; And, here's the mappings for the table I'm testing with: 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: Thu, 18 Mar 2004 01:34:41 +0100 Hi, Glenn Barnard wrote: Armin: My suspicions are confirmed. JdbcAccessImpl closes its Statement after the insert so that the SequenceManager must get another Statement (and, therefore a new connection) why should the SequenceManager lookup a new connection after closing of the statement in JdbcAccessImpl?? The connection used by the actual PB instance was managed by ConnectionManagerImpl class, this class is responsible to release the connection after use. During a PB-transaction always the same connection was used. On begin of the tx the connection was obtained, on commit or abort the connection was released. in order to submit the SCOPE_IDENTITY() query. Looks like I've got some serious re-tooling to make this work! Before I consider that, let me ask one more question: If I do a BeginTransaction before all this, how does OJB/JDBC keep the transactions coordinated among all the Statements so that the Commit persists the data? It would seem that the broker would keep the Statement/Connection and execute all the transactions with that same object I don't understand again (sorry my bad understanding english) transactions? plural? With beginTransaction you start one PB-tx. One connection will be associated with the used PB-instance. Till the abort/commitTransaction call all sql operation use the same connection. Nested transaction are not supported. Sorry if I couldn't help you. regards, Armin Thanks for being patient with me. From: Armin Waibel <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Re: Prima
RE: Primary Key from Insert-For John
Yep, you're in the exact same boat as I am with MS SQL Server.
The platform class is loaded by PlatformFactory and it uses the following
statement to name the class.:
return "org.apache.ojb.broker.platforms.Platform" + pf.substring(0,
1).toUpperCase() + pf.substring(1) + "Impl";
where the parameters come from the platform element in the
tag in repository.xml. For me, that attribute
is "MsSQLServer" and loads PlatformMsSQLServerImpl.
I made a copy of the source code for PlatformMsSQLServerImpl and added to my
Eclipse project. Next, I implemented the getLastInsertIdentity() method that
returns the SQL statement. For you it would return SELECT
IDENTITY_VAL_LOCAL() FROM SYSIBM.SYSDUMMY1. For a final implementation, I
may rename the class or try to get the change into OJB.
However, the query needs to execute on the same connection as the insert,
and that's where I'm having a problem with OJB.
I'm away for the weekend and will be looking at the problem in more depth.
Will post my results Monday.
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 () 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
>
> >>
Re: Primary Key from Insert
Armin: My suspicions are confirmed. JdbcAccessImpl closes its Statement after the insert so that the SequenceManager must get another Statement (and, therefore a new connection) in order to submit the SCOPE_IDENTITY() query. Looks like I've got some serious re-tooling to make this work! Before I consider that, let me ask one more question: If I do a BeginTransaction before all this, how does OJB/JDBC keep the transactions coordinated among all the Statements so that the Commit persists the data? It would seem that the broker would keep the Statement/Connection and execute all the transactions with that same object Thanks for being patient with me. 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] _ Get reliable access on MSN 9 Dial-up. 3 months for the price of 1! (Limited-time offer) http://click.atdmt.com/AVE/go/onm00200361ave/direct/01/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
Re: Primary Key from Insert
Am not using "mysql". Am using "MsSQLServer" as the platform in
.
My previous post has a pseudo source that I'm trying to do. For the overall
picture, I'm trying to get the PK from an insert to populate the FK in
another record.
The pseudo code again is below. Please note that the first statement is
executed in OJB by JdbcAccessImpl.executeInsert() and the remaining
statements in SequenceManagerNativeImpl.getLastInsert().
statement.executeUpdate("INSERT INTO () VALUES ();
ResultSet rs = statement.executeQuery("SELECT SCOPE_IDENTITY() AS newID");
while (rs.next())
{
long pk = rs.getLong(1);
System.out.("PK=" + pk);
}
BTB, if you are an OJB developer, shouldn't getLastInsert close the
resultset in the finally clause, not under the try?
That's the pseudo source that I'm trying to get OJB to perform. This is a
code snippet from my application--but, I don't think it'll tell you much.
I've annotated it to help.
PersistenceBroker broker =
PersistenceBrokerFactory.defaultPersistenceBroker();
// Start broker transaction
broker.beginTransaction();
// If the record is to be inserted
if (thisForm.isNew())
{
// Insert the new address
Addresses address = (Addresses)CommonDelegate.insertModel(address, false,
broker);
// insertModel() stores the row and returns the updated model with
// the PK-Address ID-populated
// Create and insert the 1-to-many reference row
EntityToAddress entityAddress = new EntityToAddress();
entityAddress.setEntityId(thisForm.getEntity().getEntityId()); // the 1
entityAddress.setAddressId(addressExisting.getAddressId()); // the many
entityAddress = (EntityToAddress)CommonDelegate.insertModel(entityAddress,
false, broker);
}
broker.commitTransaction();
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 20:25:42 +0100
Hi Glenn,
in OJB test suite you can find a test case called ...NativeIdentifierTest.
This test use database based identity columns for PK assignment and pass
against hsql and mysql.
Can post more detailed what you are doing? Source or pseudo code?
regards,
Armin
Glenn Barnard wrote:
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 () 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 l
RE: Primary Key from Insert--FOR John M.
John, you're needing to do pretty much exactly what I'm trying too. Just
keep reading the thread. I won't quit till it's solved!
I see you're using DB2. Are you also letting the DB assign a PK? I'm trying
to use the PK assigned as the value for a FK in another table. I notice that
with Oracle you can get the PK BEFORE inserting a row (NextVal), so that why
I'm asking
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 () 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).
>>>
>
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 () 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]
Re: Primary Key from Insert
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". 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]
Primary Key from Insert
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]
RE: Criteria with select with no where clause?
That's easy: Query query = new QueryByCriteria(, null) Collection c = broker.getCollectionByQuery(query); From: "Robert S. Sfeir" <[EMAIL PROTECTED]> Reply-To: "OJB Users List" <[EMAIL PROTECTED]> To: OJB Users List <[EMAIL PROTECTED]> Subject: Criteria with select with no where clause? Date: Tue, 03 Feb 2004 23:17:01 -0500 Can't seem to find a method which would allow me to add a criteria to return all records in a table. select * from table without where clause... what's the trick? R - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED] _ Learn how to choose, serve, and enjoy wine at Wine @ MSN. http://wine.msn.com/ - To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
