Re: Primary Key from Insert

[Armin Waibel]

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/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 unsubscr

Re: Primary Key from Insert

[Armin Waibel]

Hi,

Glenn Barnard wrote:
Am not using "mysql". Am using "MsSQLServer" as the platform in 
.

It doesn't matter which DB was used, important is support for identity 
columns and hsql and mysql does support this. For both databases the 
test pass, thus I assume it would pass for mssql too. The only 
difference is the SQL query to obtain the PK value (identity column 
value) in PlatformXXXImpl classes.

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().

Sorry, I don't understand what you are doing. If you enable 
SequenceManagerNativeImpl as described in the docs, then OJB should do 
the work for you. Means if you want to store a new object, you only need 
to call

Address a = new 
broker.beginT...
broker.store(a)
System.out.println(a.getPK()) // show generated PK
broker.commitT...
After the store call the PK is set for object 'a'.

regards,
Armin
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 
PlatformMsSQLServerImp

Re: Primary Key from Insert

[Glenn Barnard]

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: Getting started

[balza]

mmm ... realy ... no,
I don't remember how I get there

> Balza -- the Quick Start isn't linked on pupose, the getting started is
> designed to replace it =)
>
> Did you find information on quick start you needed which wasn't
> available from the present getting started tutorial?
>
> -Brian
>
> On Mar 17, 2004, at 10:56 AM, balza wrote:
>
> > Thank you for the answers.
> > I've seen a recent post on this argument in the Mail archives
> > (post:"Installation of OJB").
> > OJB is a very flexyble framework, but for a newbie this choice shoud
> > be imbaracing.
> > OJB should provide a "Default" and documented solution and a complex
> > or "non
> > Default" implementation at user choice.
> > What do you think?
> >
> > While I'm searching for information, I've noted a problem (?) in the
> > documentation:
> > The "Getting Started" area is logically related to "Quick start"
> > (http://db.apache.org/ojb/quickstart.html) but does not point to it.
> > The "Quick
> > start" area seems unreachable, I don't know how to get there from home
> > page.
> >
> > Thank you
> >
> >> Unfortunately I don't think there is one. I hit this issue when I
> >> started with ojb.
> >>
> >> There's a strong case both for and against having an ant task that
> >> just
> >> creates the OJB system table schemas.
> >>
> >> On the "for" side it would make it easy to dump the basic schemas you
> >> may into your database without all the test tables you currently get.
> >>
> >> On the against side:
> >> You don't always need OJB system tables. If you setup your primary key
> >> generation (auto-increment) to use database sequences, then (as far
> >> as I
> >> know) you only need OJB system tables for certain features such as the
> >> ODMG api.
> >> Because everyone's usage of these tables is different, what system
> >> tables would you include?
> >>
> >>
> >> Personally, I'd like to see an ant task that generates all the OJB
> >> system tables you may need and then you could just choose to not use
> >> them later once you've started to understand what's going on. That
> >> approach seems to provide the easiest learning curve for somebody new
> >> to
> >> OJB.
> >>
> >>
> >> So to answer your question what I use is:
> >> 1) database sequences for auto-increment
> >> http://db.apache.org/ojb/howto-use-db-sequences.html
> >>
> >> 2) No OJB system tables at all.
> >>
> >>
> >>
> >> -Original Message-
> >> From: balza [mailto:[EMAIL PROTECTED]
> >> Sent: 16 March 2004 15:09
> >> To: ojb-user
> >> Subject: Getting started
> >>
> >> Hello,
> >> what is the ant task to run to create only OJB system table (OJB_*)
> >> with OJB RC5?
> >> I've run ojb-blank task but has created a lot of table.
> >>
> >> thanks
> >>
> >>
> >> -
> >> 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-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-mail: [EMAIL PROTECTED]

mapping one class with simple types to two tables

[Oliver]

Hi,

What's the best way doing the following using
ODMG:
I have a class with various fields. The fields
have simple types. The values of the fields are stored in
two tables (like in the tutorial for mapping 1:1 associations).

But in the tutorial one object (Article) holds a reference
to another object (ProductGroup) and this is what I DONT want.

I want to get the values of the two tables (join) and map them to
my object - I dont want and need a second class like in the
tutorial.
How do I have to modify the ClassDescriptor in the repository.xml 
for doing this ?

Thanks
Greetings
Oliver


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: query alias problem: 1:1

[Phil Warrick]

Jakob,

hi phil,

Phil Warrick wrote:

 > Hi Jakob,
 >
 > And I could try extending the current code to ?quickly? see whether
 > there are other issues, and perhaps later all this could be refactored.
 >
 > Often I have too much on my plate integrating components in my EJB
 > system to spend extra time with one component (i.e. ojb), but right now
 > is a good time for me because the system is relatively stable and the
 > users are happy.  I'd like to give something back again to the ojb
 > project which has allowed me to concentrate on higher-level issues.  And
 > this will in turn allow me to entend the query functionality that I need
well this sounds great !

imo refactoring SqlQueryStatement to pass the Criteria to the relevant 
methods
start early in the call-tree:

appendSQLClause knows the SelectionCriteria which has a link to its parent:
getCriteria(). another crucial method is getAttributeInfo which 
currently only
gets the attribute-name as parameter.

if you then have the Criteria available in the relevant methods (don't 
know all
of them right now) you'll need to extend Criteria to hold path-hints and
path-segments for the alias. extending Criteria is imo the easier part, 
but it's
useless without the changes in SqlQueryStatement. another issue is backward
compatibility.

 > in my system.  In the meantime, I am prototyping with SQL queries, as 
in:

what are you prototyping using SQL ?

All the queries that I can't do at the moment with the PB-api, as we 
have discussed, that is per-Criteria specification of
1) aliases
2) hints

I need to generate annual reports containing hundreds of queries each. 
They operate on large tables: worst case table collects ~1M rows per year.

The problem domain is obstetrics: a statistical database of all mothers 
and babies for births at a McGill University teaching hospital.

Phil



jakob

 >
 > Query query = QueryFactory.newQuery(A.class, sqlString);
 >
 > The object model in small but complex and so these are very nasty
 > queries, and I literally need hundreds of them :(
 >
 > Phil
 >
 >> hi phil,
 >>
 >> the problem is that most of the methods in SqlQueryStatement do not
 >> know from which criteria they are called, so we'd have to add Criteria
 >> as additional parameter. actually it's almost the same problem as in
 >> my post regarding aliases.
 >> in the future (no one knows when...) i'd like to completely refactor
 >> SqlQueryStatement because it's getting larger and larger... actually
 >> i'd like to separate join-/alias-handling from building the
 >> sql-string. during this refactoring i could integrate criteria-base
 >> paths and hints.
 >>
 >> jakob
 >>
 >> Phil Warrick wrote:
 >>
 >>> Jakob,
 >>>
 >>> This looks good and I think it also applies to the other cases that I
 >>> mentioned.  Is it a relatively simple change to SqlQueryStatement?
 >>>
 >>> Phil
 >>>
 >>> Jakob Braeuchi wrote:
 >>>
  hi phil,
 
  imo the best solution would be to declare for what segments of the
  path the alias is valid:
 
  crit1 = new Criteria();
  crit1.setAlias("bToC1","cs");
  crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
 
  crit2 = new Criteria();
  crit2.setAlias("bToC2","cs");
  crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
 
  crit1.addAndCriteria(crit2);
  query = new QueryByCriteria(A.class, crit1);
 
  this would result in A,B,C1 and C2.
 
  crit1 = new Criteria();
  crit1.setAlias("bToC1","bs.cs");
  crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
 
  crit2 = new Criteria();
  crit2.setAlias("bToC2","bs.cs");
  crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
 
  crit1.addAndCriteria(crit2);
  query = new QueryByCriteria(A.class, crit1);
 
  this would result in A,B1,B2,C1 and C2.
 
  it's not as elegant as an automatic solution, but i think i
  sufficiently flexible.
 
  jakob
 
  Phil Warrick wrote:
 
 > Hi Jakob,
 >
 > You're right, there's some more general rule at work here.
 >
 > I think that if the path is 1-M or 1-1 followed by another 1-1, the
 > alias should affect all segments of the path.
 >
 > If the path is 1-M followed by 1-M, there should be a choice. 
 > Consider the following:
 >
 > X-1M-Y-1M-Z
 >
 > At least two different query are possible:
 >
 > 1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
 > another Z with z.zAttrib = 'bar'
 >
 >  (i.e. the same Y instance)
 >
 > 2) Find all X's having a Y with z.zAttrib = 'foo' AND
 >   having another Y with z.zAttrub = 'bar'
 >
 >  (i.e. two Y instances)
 >
 > In case 1) only Z needs 2 SQL aliases.
 > In case 2) Y and Z bo

Re: Primary Key from Insert

[Glenn Barnard]

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 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 

RE: Primary Key from Insert--FOR John M.

[Glenn Barnard]

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).
>>>
>>>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

Re: query alias problem: 1:1

[Jakob Braeuchi]

hi phil,

Phil Warrick wrote:

Hi Jakob,

And I could try extending the current code to ?quickly? see whether 
there are other issues, and perhaps later all this could be refactored.

Often I have too much on my plate integrating components in my EJB 
system to spend extra time with one component (i.e. ojb), but right now 
is a good time for me because the system is relatively stable and the 
users are happy.  I'd like to give something back again to the ojb 
project which has allowed me to concentrate on higher-level issues.  And 
this will in turn allow me to entend the query functionality that I need 
well this sounds great !

imo refactoring SqlQueryStatement to pass the Criteria to the relevant methods 
start early in the call-tree:

appendSQLClause knows the SelectionCriteria which has a link to its parent: 
getCriteria(). another crucial method is getAttributeInfo which currently only 
gets the attribute-name as parameter.

if you then have the Criteria available in the relevant methods (don't know all 
of them right now) you'll need to extend Criteria to hold path-hints and 
path-segments for the alias. extending Criteria is imo the easier part, but it's 
useless without the changes in SqlQueryStatement. another issue is backward 
compatibility.

in my system.  In the meantime, I am prototyping with SQL queries, as in:
what are you prototyping using SQL ?

jakob

Query query = QueryFactory.newQuery(A.class, sqlString);

The object model in small but complex and so these are very nasty 
queries, and I literally need hundreds of them :(

Phil

hi phil,

the problem is that most of the methods in SqlQueryStatement do not 
know from which criteria they are called, so we'd have to add Criteria 
as additional parameter. actually it's almost the same problem as in 
my post regarding aliases.
in the future (no one knows when...) i'd like to completely refactor 
SqlQueryStatement because it's getting larger and larger... actually 
i'd like to separate join-/alias-handling from building the 
sql-string. during this refactoring i could integrate criteria-base 
paths and hints.

jakob

Phil Warrick wrote:

Jakob,

This looks good and I think it also applies to the other cases that I 
mentioned.  Is it a relatively simple change to SqlQueryStatement?

Phil

Jakob Braeuchi wrote:

hi phil,

imo the best solution would be to declare for what segments of the 
path the alias is valid:

crit1 = new Criteria();
crit1.setAlias("bToC1","cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B,C1 and C2.

crit1 = new Criteria();
crit1.setAlias("bToC1","bs.cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","bs.cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B1,B2,C1 and C2.

it's not as elegant as an automatic solution, but i think i 
sufficiently flexible.

jakob

Phil Warrick wrote:

Hi Jakob,

You're right, there's some more general rule at work here.

I think that if the path is 1-M or 1-1 followed by another 1-1, the 
alias should affect all segments of the path.

If the path is 1-M followed by 1-M, there should be a choice.  
Consider the following:

X-1M-Y-1M-Z

At least two different query are possible:

1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
another Z with z.zAttrib = 'bar'
 (i.e. the same Y instance)

2) Find all X's having a Y with z.zAttrib = 'foo' AND
  having another Y with z.zAttrub = 'bar'
 (i.e. two Y instances)

In case 1) only Z needs 2 SQL aliases.
In case 2) Y and Z both need 2 SQL aliases.
(I just saw your response...)

Phil

hi phil,

you're right, both B and C must have in your case.
so the alias should affect all segements of the path, not only the 
last as it
does now. but i'm not sure if this is always the case ?

jakob

Phil Warrick wrote:
 > Hi Jakob,
 >
 > I've run into a difficulty using aliases on a model with a path
 > involving a 1-M followed by a 1-1 association, as in:
 >
 > A-1--M-B-1--1-C
 >
 > I want to find all A's having a B with c.cAttrib = 'foo1' AND 
another B
 > with c.cAttrib = 'foo2'
 >
 > I use the following code:
 >
 > Criteria crit1 = new Criteria();
 > crit1.setAlias("alias1");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));
 >
 > Criteria crit2 = new Criteria();
 > crit2.setAlias("alias2");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo2"));
 >
 > crit1.addAndCriteria(crit2);
 >> Query query = new QueryByCriteria(A.class, crit1);
 >
 >
 > The following SQL 

Re: Order by and joins

[Jakob Braeuchi]

hi alan,

thanks for pointing to the version where i changed this behaviour. it was 
changed because since 1.37 you can define the join-type for a path:

QueryByCriteria#setPathOuterJoin

/**
 * Force outer join for path
 *
 * @param aPath
 */
public void setPathOuterJoin(String aPath)
{
getPathOuterJoin().put(aPath, Boolean.TRUE);
}
before ojb always assumed outer joins for additional columns.

hth
jakob
Olmanson, Alan wrote:

Jakob,

Sure, the generated SQL:

SELECT
A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_
REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY
PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN
GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH
ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0.
OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as
ojb_col_29 FROM R_SOLUTION A0 INNER JOIN M_CUSTOMER A1 ON
A0.CUST_ID=A1.CUST_ID INNER JOIN M_ADDRESS A2 ON A1.ADDRESS_ID=A2.ADDRESS_ID
ORDER BY 30
I did noticed that Criteria#addOrderByAscending had been deprecated,
unfortunately, switching to QueryByCriteria is going to take a lot of
reworking of my application to use.
I did take a look into the OJB code, and the problem appears to be in
SqlQueryStatement.  The ensureColumns method was changed (between versions
1.36 and 1.37) to call appendColName with a false for useOuterJoins.
Changing that to true, resulted in the following query being generated which
worked. (I however have no idea what side effects this would have, I assume
the true-->false change was made for a reason).
SELECT
A0.CREATED_DATE,A0.REVENUE,A0.SOL_NAME2,A0.SOL_DESC,A0.CLOSING_DATE,A0.TERM_
REASON_ID,A0.CURR_ISO_CODE,A0.NOTE,A0.SOL_MARK_TYPE_ID,A0.CREATED,A0.SOUR_TY
PE_ID,A0.SOL_NAME,A0.CONT_ID,A0.CURR_PROPOSAL_ID,A0.LAST_UPDATE_DATE,A0.CHAN
GED,A0.SOL_STATUS_ID,A0.PROBABILITY,A0.CREATOR,A0.CURR_PRESENTATION_ID,A0.CH
ANGER,A0.EXPECTED_VALUE,A0.CURR_FINANCE_ID,A0.SOL_ID,A0.PRIMARY_QUOTE_ID,A0.
OWNER_ID,A0.SAL_CHAN_ID,A0.CUST_ID,A0.CURR_QUOTATION_ID,A2.CITY as
ojb_col_29 FROM R_SOLUTION A0 LEFT OUTER JOIN M_CUSTOMER A1 ON
A0.CUST_ID=A1.CUST_ID LEFT OUTER JOIN M_ADDRESS A2 ON
A1.ADDRESS_ID=A2.ADDRESS_ID ORDER BY 30
Alan Olmanson.

-Original Message-
From: Jakob Braeuchi [mailto:[EMAIL PROTECTED]
Sent: Monday, March 15, 2004 4:01 PM
To: OJB Users List
Subject: Re: Order by and joins
hi alan,

please post the generated sql. is there an ORDER BY clause ?
btw Criteria#addOrderByAscending is deprecated has been moved to
QueryByCriteria.
jakob

Olmanson, Alan wrote:


Hello,

I'm having a problem with query sorting not working the same in the CVS I
pulled on 3/4/04 as it did in the 0.9.8.  

I do the following query:

Criteria crit = new Criteria();
   crit.addOrderByAscending("customer.mainAddress.city");
   query = QueryFactory.newQuery("com.firepond.domain.Solution",
crit);
In 0.9.8 this would work, I'd get all the solutions, in the current
version

this returns an empty list.

The problem seem to be that OJB is now using INNER JOIN to join solution,
customer, and address, rather than left outer joins.
Repository (fragment):

  
   
   
   
   

   




   jdbc-type="NUMERIC"

primarykey="true" autoincrement="true">
   
  
   
jdbc-type="NUMERIC">
   

   

   


   
   
   jdbc-type="VARCHAR">

   

Alan Olmanson

-
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-mail: [EMAIL PROTECTED]

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: query alias problem: 1:1

[Phil Warrick]

Hi Jakob,

And I could try extending the current code to ?quickly? see whether 
there are other issues, and perhaps later all this could be refactored.

Often I have too much on my plate integrating components in my EJB 
system to spend extra time with one component (i.e. ojb), but right now 
is a good time for me because the system is relatively stable and the 
users are happy.  I'd like to give something back again to the ojb 
project which has allowed me to concentrate on higher-level issues.  And 
this will in turn allow me to entend the query functionality that I need 
in my system.  In the meantime, I am prototyping with SQL queries, as in:

Query query = QueryFactory.newQuery(A.class, sqlString);

The object model in small but complex and so these are very nasty 
queries, and I literally need hundreds of them :(

Phil

hi phil,

the problem is that most of the methods in SqlQueryStatement do not know 
from which criteria they are called, so we'd have to add Criteria as 
additional parameter. actually it's almost the same problem as in my 
post regarding aliases.
in the future (no one knows when...) i'd like to completely refactor 
SqlQueryStatement because it's getting larger and larger... actually i'd 
like to separate join-/alias-handling from building the sql-string. 
during this refactoring i could integrate criteria-base paths and hints.

jakob

Phil Warrick wrote:

Jakob,

This looks good and I think it also applies to the other cases that I 
mentioned.  Is it a relatively simple change to SqlQueryStatement?

Phil

Jakob Braeuchi wrote:

hi phil,

imo the best solution would be to declare for what segments of the 
path the alias is valid:

crit1 = new Criteria();
crit1.setAlias("bToC1","cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B,C1 and C2.

crit1 = new Criteria();
crit1.setAlias("bToC1","bs.cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","bs.cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B1,B2,C1 and C2.

it's not as elegant as an automatic solution, but i think i 
sufficiently flexible.

jakob

Phil Warrick wrote:

Hi Jakob,

You're right, there's some more general rule at work here.

I think that if the path is 1-M or 1-1 followed by another 1-1, the 
alias should affect all segments of the path.

If the path is 1-M followed by 1-M, there should be a choice.  
Consider the following:

X-1M-Y-1M-Z

At least two different query are possible:

1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
another Z with z.zAttrib = 'bar'
 (i.e. the same Y instance)

2) Find all X's having a Y with z.zAttrib = 'foo' AND
  having another Y with z.zAttrub = 'bar'
 (i.e. two Y instances)

In case 1) only Z needs 2 SQL aliases.
In case 2) Y and Z both need 2 SQL aliases.
(I just saw your response...)

Phil

hi phil,

you're right, both B and C must have in your case.
so the alias should affect all segements of the path, not only the 
last as it
does now. but i'm not sure if this is always the case ?

jakob

Phil Warrick wrote:
 > Hi Jakob,
 >
 > I've run into a difficulty using aliases on a model with a path
 > involving a 1-M followed by a 1-1 association, as in:
 >
 > A-1--M-B-1--1-C
 >
 > I want to find all A's having a B with c.cAttrib = 'foo1' AND 
another B
 > with c.cAttrib = 'foo2'
 >
 > I use the following code:
 >
 > Criteria crit1 = new Criteria();
 > crit1.setAlias("alias1");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));
 >
 > Criteria crit2 = new Criteria();
 > crit2.setAlias("alias2");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo2"));
 >
 > crit1.addAndCriteria(crit2);
 >> Query query = new QueryByCriteria(A.class, crit1);
 >
 >
 > The following SQL is generated by ojb (assume that the class 'X'
 > corresponds to table 'XTABLE')
 >
 > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3 WHERE
 > A1.C_ID=A2.ID AND A1.C_ID=A3.ID AND
 > A0.ID=A1.A_ID AND
 > (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))
 >
 > I believe that both the B _and_ C tables must have two SQL 
aliases each
 > to correctly complete the query, as in:
 >
 > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3,BTABLE 
A4 WHERE
 > A1.C_ID=A2.ID AND A4.C_ID=A3.ID AND
 > A0.ID=A1.A_ID AND A4.ID=A1.A_ID
 > (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))
 >
 > (Perhaps there is better SQL possible than this?)
 >
 > I've been looking carefully at
 > org.apache.ojr.broker.accesslayer.sql

RE: Primary Key from Insert

[McCaffrey, John G.]

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).
>>>
>>>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/dir

Re: Primary Key from Insert

[Armin Waibel]

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 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]

Re: query alias problem: 1:1

[Jakob Braeuchi]

hi phil,

the problem is that most of the methods in SqlQueryStatement do not know from 
which criteria they are called, so we'd have to add Criteria as additional 
parameter. actually it's almost the same problem as in my post regarding aliases.
in the future (no one knows when...) i'd like to completely refactor 
SqlQueryStatement because it's getting larger and larger... actually i'd like to 
separate join-/alias-handling from building the sql-string. during this 
refactoring i could integrate criteria-base paths and hints.

jakob

Phil Warrick wrote:

Jakob,

This looks good and I think it also applies to the other cases that I 
mentioned.  Is it a relatively simple change to SqlQueryStatement?

Phil

Jakob Braeuchi wrote:

hi phil,

imo the best solution would be to declare for what segments of the 
path the alias is valid:

crit1 = new Criteria();
crit1.setAlias("bToC1","cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B,C1 and C2.

crit1 = new Criteria();
crit1.setAlias("bToC1","bs.cs");
crit1.addEqualTo("bs.cs.idInternal",new Integer(101));
crit2 = new Criteria();
crit2.setAlias("bToC2","bs.cs");
crit2.addEqualTo("bs.cs.idInternal",new Integer(202));
crit1.addAndCriteria(crit2);
query = new QueryByCriteria(A.class, crit1);
this would result in A,B1,B2,C1 and C2.

it's not as elegant as an automatic solution, but i think i 
sufficiently flexible.

jakob

Phil Warrick wrote:

Hi Jakob,

You're right, there's some more general rule at work here.

I think that if the path is 1-M or 1-1 followed by another 1-1, the 
alias should affect all segments of the path.

If the path is 1-M followed by 1-M, there should be a choice.  
Consider the following:

X-1M-Y-1M-Z

At least two different query are possible:

1) Find all X's having a Y with one Z with z.zAttrib = 'foo' AND
another Z with z.zAttrib = 'bar'
 (i.e. the same Y instance)

2) Find all X's having a Y with z.zAttrib = 'foo' AND
  having another Y with z.zAttrub = 'bar'
 (i.e. two Y instances)

In case 1) only Z needs 2 SQL aliases.
In case 2) Y and Z both need 2 SQL aliases.
(I just saw your response...)

Phil

hi phil,

you're right, both B and C must have in your case.
so the alias should affect all segements of the path, not only the 
last as it
does now. but i'm not sure if this is always the case ?

jakob

Phil Warrick wrote:
 > Hi Jakob,
 >
 > I've run into a difficulty using aliases on a model with a path
 > involving a 1-M followed by a 1-1 association, as in:
 >
 > A-1--M-B-1--1-C
 >
 > I want to find all A's having a B with c.cAttrib = 'foo1' AND 
another B
 > with c.cAttrib = 'foo2'
 >
 > I use the following code:
 >
 > Criteria crit1 = new Criteria();
 > crit1.setAlias("alias1");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));
 >
 > Criteria crit2 = new Criteria();
 > crit2.setAlias("alias2");
 > crit1.addEqualTo("allBs.c.cAttrib", new String("foo2"));
 >
 > crit1.addAndCriteria(crit2);
 >> Query query = new QueryByCriteria(A.class, crit1);
 >
 >
 > The following SQL is generated by ojb (assume that the class 'X'
 > corresponds to table 'XTABLE')
 >
 > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3 WHERE
 > A1.C_ID=A2.ID AND A1.C_ID=A3.ID AND
 > A0.ID=A1.A_ID AND
 > (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))
 >
 > I believe that both the B _and_ C tables must have two SQL 
aliases each
 > to correctly complete the query, as in:
 >
 > SELECT A0.ID FROM ATABLE A0,BTABLE A1,CTABLE A2,CTABLE A3,BTABLE 
A4 WHERE
 > A1.C_ID=A2.ID AND A4.C_ID=A3.ID AND
 > A0.ID=A1.A_ID AND A4.ID=A1.A_ID
 > (( A2.CODE =  ? ) AND  (A3.CODE =  ? ))
 >
 > (Perhaps there is better SQL possible than this?)
 >
 > I've been looking carefully at
 > org.apache.ojr.broker.accesslayer.sql.SqlQueryStatement.java
 >
 > and was wondering if you could suggest the best approach to 
correctly
 > addressing this limitation.
 >
 > Thanks,
 >
 > Phil
 >
 >
 >
 >
 >
 >
 >
 >
 >
 > 
-
 > 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-mail: [EMAIL PROTECTED]

-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [

Re: Query casts [was: subqueries: doc]

[Jakob Braeuchi]

hi phil,

the problem is that most of the methods in SqlQueryStatement do not know from 
which criteria they are called, so we'd have to add Crteria as additional parameter.

jakob

Phil Warrick wrote:

Hi Jakob,

I'd be very willing to help out if you think that this is sufficiently 
useful (it's crucial to my app).  If you could point me in the right 
direction, I could look into it.

Phil


hi phil,

this feature seems really undocumented ! i'll inform brian about it.

adding the path-class hint to the criteria would be even more 
flexible. but it would require heavy refactoring in SqlQueryStatement.

jakob

Phil Warrick wrote:

Hi Jakob,

Great, that's a nice (undocumented) feature that I didn't know about 
:) Now to answer my query (which requires aliases), I think it would 
be necessary to somehow have these hints apply at the Criteria level 
rather than the entire query, something like:

Criteria crit1 = new Criteria();
crit1.setAlias("alias1");
crit1.addEqualTo("allBs.c.cAttrib", new String("foo1"));
crit1.setPathClass("c", C.class); // maybe unnecesary for base class
Criteria crit2 = new Criteria();
crit2.setAlias("alias2");
crit1.addEqualTo("allBs.c.dAttrib", new String("foo2"));
crit1.setPathClass("c", D.class);
crit1.addAndCriteria(crit2);

Query query = new QueryByCriteria(A.class, crit1);

Do you think that this (or equivalent) would be a good approach?

Phil

Jakob Braeuchi wrote:

hi phil,

can't this be done by using a pathClass ? see 
QueryByCriteriy#addPathClass.

query.addPathClass("a.b.c",D.class);

afaik the oql-cast is not yet supported and i'm not an antlr-guru.

jakob

Phil Warrick wrote:

 > Hi Jakob,
 >
 > I have concluded that the missing feature here is an OQL-style 
cast (to
 > downcast the C to a D).  In your opinion, would adding this feature
 > involve a major refactoring or could it be a straightforward 
addition to
 > the PB-api?
 >
 > Thanks,
 >
 > Phil
 >
 >
 >
 > Philip Warrick wrote:
 >
 >> Hi again,
 >>
 >> A small clarification, the query that I'd like to do is a little 
more
 >> complicated:
 >> find all A's with aAttribute = "foo" and having a D with
 >> dAttribute = "bar" and a C with cAttribute = "foobar".
 >>
 >> My original query could be achieved efficiently by searching for 
the D's
 >> with the two criteria:
 >> d.b.a.aAttribute = "foo" AND
 >> d.dAttribute = "bar"
 >>
 >> and then navigating to the A object(s) which should be cached.
 >>
 >> Phil
 >>
 >>  > Hi Jakob,
 >>  >
 >>  > Thanks for this writeup.
 >>  >
 >>  > Here's an example of a query that I'd like to perform:
 >>  >
 >>  > A-1--M-B-1--M-C
 >>  >|
 >>  >/\
 >>  >--
 >>  > |
 >>  > D
 >>  >
 >>  > i.e. A has a 1-M association with B, B has a 1-M association 
with C
 >> and
 >>  > D is one subclass of C
 >>  >
 >>  > I'd like to query for all A's with aAttribute = "foo" having 
a D with
 >>  > dAttribute = "bar".
 >>  >
 >>  > Obviously I cannot use a criteria like "a.b.c.dAttribute".
 >>  >
 >>  > I can query for all the D's first, then iterate through the
 >>  > corresponding A's and check for the aAttribute, but I'd like 
to do
 >> this
 >>  > in one query (and let the dbms do most of the work).
 >>  >
 >>  > Sounds like a job for a subquery, but I am not sure if I can 
do this
 >>  > with PB queries and subqueries.  Can anyone help?
 >>  >
 >>  > Phil
 >>  >
 >>  >
 >>  > Jakob Braeuchi wrote:
 >>  >  > hi phil,
 >>  >  >
 >>  >  > i added a small docu to query.xml
 >>  >  >
 >>  >  > jakob
 >>  >  >
 >>  >  > Phil Warrick wrote:
 >>  >  >
 >>  >  >  > Hi again,
 >>  >  >  >
 >>  >  >  > I came across some discussions with Jakob from last 
Sept. about
 >>  > allowing
 >>  >  >  > references to parents in subqueries.  Did anything come 
of this?
 >>  >  >  >
 >>  >  >  > The reason I'm asking is that after six months of using 
OJB in
 >>  >  >  > production to get data into the system (ojb has been rock
 >> solid!)
 >>  > it's
 >>  >  >  > time for many, many complex queries to get data out 
(similar to
 >>  > Oliver
 >>  >  >  > Matz's queries:
 >>  >  >  >
 >>  >  >
 >>  >
 >> 
http://nagoya.apache.org/eyebrowse/[EMAIL PROTECTED]&msgId=1005213 
 

 >> 
> 

 >>
 >>  >
 >> 
> 
>> 

 >>
 >>  >
 >>  >  >
 >>  >
 >> 
>
 >>  >
 >> 
> 

Re: Primary Key from Insert

[Glenn Barnard]

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]

creationDate and lastUpdate for classes

[Tino Schöllhorn]

Hi,

I am successfully using OJB rc5 and I really appreciate this tool. Now I 
want to extend my data-model so that I have 2 additional fields:

- creationDate: the date the object/row was created
- lastUpdate: the date the object/row was last updated
Currently we are using MySQL as database, but we don't want to use any 
database-specific functions so that I have to implement it myself.

I identified several cases and one I can't imagine how to solve the 
maintainence of my two dates.

1.) "Normal Object": say I have class A. via the interface 
PersistenceBrokerAware I can easily set "creationDate" and "lastUpdate" 
to the correct values.

2.) Decomposed NM-Relation between class A and B via class AC: well this 
is just another case of 1.)

3.) Non-Decomposed NM-Relation between class A and B: This is the case 
which puzzles me: is it possible to implement a callback mechanism or 
something like that, when inserting or updating this relationship?

I would really be glad for any help!

Tino



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: RE : Query Performance

[edson . richter]

To see what exactly being going to/from database server, enable P6Spy.
I'm using p6spy with sqlprofiler tool, and I get exactly information passing between 
OJB and
database server (not only sql commands, but including data sen't and returned).


Edson Richter

> Hello,
>
> I think I need to give you more explanation.
> In our production environnement, a website page takes 6 minutes to be
> displayed. And I think it is not enough quick ;-)
> In order to find the performance problem, we added some logs and found that
> the following code was the problem (and takes 6 minutes) :
>
> list = broker.getCollectionByQuery(requete);
> broker.commitTransaction();
>
> This query returns only 10 records (with simple data, no lob).
> I thought it was an Oracle performance problem so with the debug mode, I
> found the generated query. This query is not optimised, but when I tried it
> on the database, it only takes 4 secondes to be executed.
> I'm sure that the mapping is bad but i don't understand such difference
> between executing a query directly from a database and with OJB.
>
> So here are my questions :
>
> 1) Is the query displayed in the log server (with obj properties
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.LogLevel=DEBUG
> ) really the query executed on the database by OJB?
>
> 2) How really works OJB to retrieve data from a database?
>
>
> Thanks a lot.
>
> Yannick
>
> -Message d'origine-
> De : Yannick Goujon [mailto:[EMAIL PROTECTED]
> Envoyé : mardi 16 mars 2004 14:56
> À : [EMAIL PROTECTED]
> Objet : Query Performance
>
> Hi,
>
>
>
> We use Oracle 8.1.7 and we have performance problem with a query generated
> by OJB.
>
> With OJB, it takes 6 minutes (maybe due to a bad mapping).
>
> We found the generated query by changing the LogLevel of the sql generator
> (org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.LogLevel=DEBU
> G)
>
> When we try this query directly under sqlplus, it only takes 4 secondes!
>
> Can someone explain me why is it so long with OJB? Is it due to a bad
> mapping or a bad configuration of OJB?
>
>
>
> Thanks
>
>
>
> Yannick
>
>
>
> -
> 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]

RE : Query Performance

[Yannick Goujon]

Hello,

I think I need to give you more explanation.
In our production environnement, a website page takes 6 minutes to be
displayed. And I think it is not enough quick ;-)
In order to find the performance problem, we added some logs and found that
the following code was the problem (and takes 6 minutes) :

list = broker.getCollectionByQuery(requete);
broker.commitTransaction();

This query returns only 10 records (with simple data, no lob).
I thought it was an Oracle performance problem so with the debug mode, I
found the generated query. This query is not optimised, but when I tried it
on the database, it only takes 4 secondes to be executed.
I'm sure that the mapping is bad but i don't understand such difference
between executing a query directly from a database and with OJB.

So here are my questions :

1) Is the query displayed in the log server (with obj properties
org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.LogLevel=DEBUG
) really the query executed on the database by OJB?

2) How really works OJB to retrieve data from a database?


Thanks a lot.

Yannick

-Message d'origine-
De : Yannick Goujon [mailto:[EMAIL PROTECTED] 
Envoyé : mardi 16 mars 2004 14:56
À : [EMAIL PROTECTED]
Objet : Query Performance

Hi,

 

We use Oracle 8.1.7 and we have performance problem with a query generated
by OJB.

With OJB, it takes 6 minutes (maybe due to a bad mapping).

We found the generated query by changing the LogLevel of the sql generator
(org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl.LogLevel=DEBU
G)

When we try this query directly under sqlplus, it only takes 4 secondes!

Can someone explain me why is it so long with OJB? Is it due to a bad
mapping or a bad configuration of OJB?

 

Thanks

 

Yannick



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

Re: Primary Key from Insert

[Armin Waibel]

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]

Re: Primary Key from Insert

[Glenn Barnard]

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]

Re: Getting started

[Brian McCallister]

Balza -- the Quick Start isn't linked on pupose, the getting started is 
designed to replace it =)

Did you find information on quick start you needed which wasn't 
available from the present getting started tutorial?

-Brian

On Mar 17, 2004, at 10:56 AM, balza wrote:

Thank you for the answers.
I've seen a recent post on this argument in the Mail archives
(post:"Installation of OJB").
OJB is a very flexyble framework, but for a newbie this choice shoud 
be imbaracing.
OJB should provide a "Default" and documented solution and a complex 
or "non
Default" implementation at user choice.
What do you think?

While I'm searching for information, I've noted a problem (?) in the 
documentation:
The "Getting Started" area is logically related to "Quick start"
(http://db.apache.org/ojb/quickstart.html) but does not point to it. 
The "Quick
start" area seems unreachable, I don't know how to get there from home 
page.

Thank you

Unfortunately I don't think there is one. I hit this issue when I
started with ojb.
There's a strong case both for and against having an ant task that 
just
creates the OJB system table schemas.

On the "for" side it would make it easy to dump the basic schemas you
may into your database without all the test tables you currently get.
On the against side:
You don't always need OJB system tables. If you setup your primary key
generation (auto-increment) to use database sequences, then (as far 
as I
know) you only need OJB system tables for certain features such as the
ODMG api.
Because everyone's usage of these tables is different, what system
tables would you include?

Personally, I'd like to see an ant task that generates all the OJB
system tables you may need and then you could just choose to not use
them later once you've started to understand what's going on. That
approach seems to provide the easiest learning curve for somebody new 
to
OJB.

So to answer your question what I use is:
1) database sequences for auto-increment
http://db.apache.org/ojb/howto-use-db-sequences.html
2) No OJB system tables at all.



-Original Message-
From: balza [mailto:[EMAIL PROTECTED]
Sent: 16 March 2004 15:09
To: ojb-user
Subject: Getting started
Hello,
what is the ant task to run to create only OJB system table (OJB_*)
with OJB RC5?
I've run ojb-blank task but has created a lot of table.
thanks

-
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-mail: [EMAIL PROTECTED]



-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: Getting started

[balza]

Thank you for the answers.
I've seen a recent post on this argument in the Mail archives
(post:"Installation of OJB").
OJB is a very flexyble framework, but for a newbie this choice shoud be imbaracing.
OJB should provide a "Default" and documented solution and a complex or "non
Default" implementation at user choice.
What do you think?

While I'm searching for information, I've noted a problem (?) in the documentation:
The "Getting Started" area is logically related to "Quick start"
(http://db.apache.org/ojb/quickstart.html) but does not point to it. The "Quick
start" area seems unreachable, I don't know how to get there from home page.

Thank you

> Unfortunately I don't think there is one. I hit this issue when I
> started with ojb.
>
> There's a strong case both for and against having an ant task that just
> creates the OJB system table schemas.
>
> On the "for" side it would make it easy to dump the basic schemas you
> may into your database without all the test tables you currently get.
>
> On the against side:
> You don't always need OJB system tables. If you setup your primary key
> generation (auto-increment) to use database sequences, then (as far as I
> know) you only need OJB system tables for certain features such as the
> ODMG api.
> Because everyone's usage of these tables is different, what system
> tables would you include?
>
>
> Personally, I'd like to see an ant task that generates all the OJB
> system tables you may need and then you could just choose to not use
> them later once you've started to understand what's going on. That
> approach seems to provide the easiest learning curve for somebody new to
> OJB.
>
>
> So to answer your question what I use is:
> 1) database sequences for auto-increment
> http://db.apache.org/ojb/howto-use-db-sequences.html
>
> 2) No OJB system tables at all.
> 
>
>
> -Original Message-
> From: balza [mailto:[EMAIL PROTECTED]
> Sent: 16 March 2004 15:09
> To: ojb-user
> Subject: Getting started
>
> Hello,
> what is the ant task to run to create only OJB system table (OJB_*)
> with OJB RC5?
> I've run ojb-blank task but has created a lot of table.
>
> thanks
>
>
> -
> 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-mail: [EMAIL PROTECTED]

Re: Primary Key from Insert

[Armin Waibel]

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]

Re: Getting started

[Brian McCallister]

There is a workaround, btw:

Thomas D added the ability (I haven't used it) to create and execute  
DDL from a persistence broker by giving it a torque schema. ojb-blank  
includes the torque schema for the system tables in src/schema/

If you add the schema file to the classpath you can:

TorqueDBHandling torque = new TorqueDBHandling();
PBKey key = PersistenceBrokerFactory.getDefaultKey();
torque.setConnection(MetadataManager.getInstance().connectionRepository( 
).getDescriptor(key));
InputStream in =  
Thread.contextClassLoader().getResourceAsStream("ojbcore-schema.xml");
torque.addDBDefinitionFile(in);
// torque.createDB();
torque.initDB();

Or something like that =)

On Mar 17, 2004, at 8:22 AM, Brendan Richards wrote:

Unfortunately I don't think there is one. I hit this issue when I
started with ojb.
There's a strong case both for and against having an ant task that just
creates the OJB system table schemas.
On the "for" side it would make it easy to dump the basic schemas you
may into your database without all the test tables you currently get.
On the against side:
You don't always need OJB system tables. If you setup your primary key
generation (auto-increment) to use database sequences, then (as far as  
I
know) you only need OJB system tables for certain features such as the
ODMG api.
Because everyone's usage of these tables is different, what system
tables would you include?

Personally, I'd like to see an ant task that generates all the OJB
system tables you may need and then you could just choose to not use
them later once you've started to understand what's going on. That
approach seems to provide the easiest learning curve for somebody new  
to
OJB.

So to answer your question what I use is:
1) database sequences for auto-increment
http://db.apache.org/ojb/howto-use-db-sequences.html
2) No OJB system tables at all.



-Original Message-
From: balza [mailto:[EMAIL PROTECTED]
Sent: 16 March 2004 15:09
To: ojb-user
Subject: Getting started
Hello,
what is the ant task to run to create only OJB system table (OJB_*)
with OJB RC5?
I've run ojb-blank task but has created a lot of table.
thanks

-
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-mail: [EMAIL PROTECTED]

RE: Getting started

[Brendan Richards]

Unfortunately I don't think there is one. I hit this issue when I
started with ojb. 

There's a strong case both for and against having an ant task that just
creates the OJB system table schemas.

On the "for" side it would make it easy to dump the basic schemas you
may into your database without all the test tables you currently get.

On the against side:
You don't always need OJB system tables. If you setup your primary key
generation (auto-increment) to use database sequences, then (as far as I
know) you only need OJB system tables for certain features such as the
ODMG api.
Because everyone's usage of these tables is different, what system
tables would you include?


Personally, I'd like to see an ant task that generates all the OJB
system tables you may need and then you could just choose to not use
them later once you've started to understand what's going on. That
approach seems to provide the easiest learning curve for somebody new to
OJB.


So to answer your question what I use is:
1) database sequences for auto-increment  
http://db.apache.org/ojb/howto-use-db-sequences.html

2) No OJB system tables at all.



-Original Message-
From: balza [mailto:[EMAIL PROTECTED] 
Sent: 16 March 2004 15:09
To: ojb-user
Subject: Getting started

Hello,
what is the ant task to run to create only OJB system table (OJB_*)
with OJB RC5?
I've run ojb-blank task but has created a lot of table.

thanks


-
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]

Logger error

[Carles Duque]

Hello, I'm using tomcat and ojb. I test an example of cocoon with ojb:

PersistenceManager manager = factory.getPersistenceManager();
/* 2. Get current transaction */
Transaction tx = manager.currentTransaction();
/* 3. Start a Transaction */
tx.begin();
/* 4. now perform persistence operations. Store the new user */
manager.makePersistent(usr);
/* 5. Commit the transaction  */
  tx.commit();


In the "tx.begin();" tha application throws an error like this:
[BOOT] ERROR: [org.apache.ojb.broker.util.logging.LoggerFactoryImpl]
Could not set logger for class null
org/apache/commons/lang/exception/NestableException
java.lang.NoClassDefFoundError:
org/apache/commons/lang/exception/NestableException
at java.lang.ClassLoader.defineClass0(Native Method)
at java.lang.ClassLoader.defineClass(ClassLoader.java:509)


Can you help me?

Carles

Primary Key from Insert

[Glenn Barnard]

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: mssql: sqlexception

[salgado.pc]

  It worked :)

  With MSSQL/java.sql.Date it stores data in this format 2004-03-17
00:00:00.000.

  I haven't tried with java.sql.Timestamp (minutes/seconds/etc are not
relevant for me) but I am sure that would do the trick for MSSQL also
(like Roland say it would do for Oracle).
  One other thing, I am using Torque to generate my DAOs and it is simpler
to change the java.util.Date import on the DAO template to java.sql.Date
than to change the model by replacing all Date by Timestamp (each time
you change the model, one has to make a search-replace operation on the
package). Maybe there is an easier way but... I only understand the
Torque's velocity templates.


  To Roland and Brendan, thank you very much for your help,

Pedro Salgado

> I tried it now and with java.sql.Date it only saves the Date, with
> java.sql.Timestamp it also saves the Time. This on Oracle.
>
>
> Roland Ribi
>
>
>> -Original Message-
>> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
>> Sent: Wednesday, March 17, 2004 11:00 AM
>> To: [EMAIL PROTECTED]
>> Subject: RE: mssql: sqlexception
>>
>>
>>
>>  Well, that makes sense. I used Torque to generate my DAOs
>> and since it
>> worked with MySQL I made the assumption the mapping should be ok.
>>
>>   Thank you, I will try this.
>>
>> Pedro Salgado
>>
>> > I'm using MSSQL successfully with java.sql.Timestamp in my classes.
>> >
>> > Using java.sql.Date worked with MSSQL for me but only the
>> date was saved
>> > not the time as well - the time was always set to midday.
>> >
>> >
>> >
>> > -Original Message-
>> > From: Ribi Roland [mailto:[EMAIL PROTECTED]
>> > Sent: 17 March 2004 06:43
>> > To: 'OJB Users List'
>> > Subject: RE: mssql: sqlexception
>> >
>> > Hi
>> >
>> > I had also a problem with java.util.Date on a Oracle DB. I
>> had to change
>> > the
>> > types in my classes to java.sql.Date and it works fine now.
>> >
>> > An other way could be a conversion-class for java.util.Date (like
>> Integer2IntegerFieldConversion) which creates a java.sql.Date from
>> java.util.Date.
>> >
>> > Let me now if it solves your problem.
>> >
>> > Roland Ribi
>> >
>> >
>> >> -Original Message-
>> >> From: Pedro Salgado [mailto:[EMAIL PROTECTED]
>> >> Sent: Thursday, January 01, 1970 2:47 AM
>> >> To: OJB Users List
>> >> Subject: mssql: sqlexception
>> >>
>> >>
>> >>
>> >>   For several reasons, I have been using a OJB/MySQL
>> >> development platform
>> >> and everything was going ok. The problem was when I chose to
>> >> test the same
>> >> application with a OJB/MSSQL platform.
>> >>
>> >>
>> >>   Apart of some minor changes, I made it all work except for
>> >> a class (the
>> >> source and OJB description is below) that has a
>> >> TIMESTAMP/DATETIME mapping
>> >> and that also could be NULL (also tried with NOT NULL <=>
>> >> nullable="false"
>> >> and didn't work either).
>> >>   I tried Google and it seems a MSSQL JDBC driver bug (don't
>> >> know if it
>> >> could be a bug in OJB) but I wonder if anyone has came across with
>> this
>> >> problem and solved this or found a way around (please don't
>> >> tell me I have
>> >> to store the day-month-year on 3 database fields for each
>> >> date :( or change
>> >> it to a char/varchar field).
>> >>
>> >>   Below is the error (log file) the DAO, OJB descriptor and
>> >> SQL for the
>> >> class in question.
>> >>
>> >>
>> >>   All help/ideas are welcome,
>> >>
>> >>
>> >> Pedro Salgado
>> >>
>> >>
>> >>
>> >> --- LOG FILE
>> >>
>> >> 12:46:34,503 - DEBUG
>> >> org.apache.ojb.broker.accesslayer.StatementManager -
>> >> closeResources was called
>> >>
>> >>
>> >> 12:46:34,503 - DEBUG
>> >> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> >> executeInsert : [EMAIL PROTECTED]
>> >>
>> >>
>> >> 12:46:34,503 - DEBUG
>> >> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl -
>> SQL:INSERT
>> >> INTO REVISIONS
>> >> (id,document,title,edition,date_implementation,date_issued,dat
>> e_approval,dat
>> >> e_approval_qa,date_authorization,date_cancelled,date_deadline,
>> originator,app
>> >>
>> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
>> >> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>> >>
>> >>
>> >> 12:46:34,519 - ERROR
>> >> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> >> SQLException during the execution of the insert (for a
>> >> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
>> >> JDBC]Unable to
>> >> determine the type of the specified object.
>> >>
>> >>
>> >> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
>> >> JDBC]Unable to
>> >> determine the type of the specified object.
>> >> at
>> com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
>> >> Source)
>> >> at
>> >> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
>> >> at
>> com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
>> >> Source)
>> >> at
>> >> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
>> orStatement(Un
>> 

RE: mssql: sqlexception

[salgado.pc]

 Well, that makes sense. I used Torque to generate my DAOs and since it
worked with MySQL I made the assumption the mapping should be ok.

  Thank you, I will try this.

Pedro Salgado

> I'm using MSSQL successfully with java.sql.Timestamp in my classes.
>
> Using java.sql.Date worked with MSSQL for me but only the date was saved
> not the time as well - the time was always set to midday.
>
>
>
> -Original Message-
> From: Ribi Roland [mailto:[EMAIL PROTECTED]
> Sent: 17 March 2004 06:43
> To: 'OJB Users List'
> Subject: RE: mssql: sqlexception
>
> Hi
>
> I had also a problem with java.util.Date on a Oracle DB. I had to change
> the
> types in my classes to java.sql.Date and it works fine now.
>
> An other way could be a conversion-class for java.util.Date (like
> Integer2IntegerFieldConversion) which creates a java.sql.Date from
> java.util.Date.
>
> Let me now if it solves your problem.
>
> Roland Ribi
>
>
>> -Original Message-
>> From: Pedro Salgado [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, January 01, 1970 2:47 AM
>> To: OJB Users List
>> Subject: mssql: sqlexception
>>
>>
>>
>>   For several reasons, I have been using a OJB/MySQL
>> development platform
>> and everything was going ok. The problem was when I chose to
>> test the same
>> application with a OJB/MSSQL platform.
>>
>>
>>   Apart of some minor changes, I made it all work except for
>> a class (the
>> source and OJB description is below) that has a
>> TIMESTAMP/DATETIME mapping
>> and that also could be NULL (also tried with NOT NULL <=>
>> nullable="false"
>> and didn't work either).
>>   I tried Google and it seems a MSSQL JDBC driver bug (don't
>> know if it
>> could be a bug in OJB) but I wonder if anyone has came across
>> with this
>> problem and solved this or found a way around (please don't
>> tell me I have
>> to store the day-month-year on 3 database fields for each
>> date :( or change
>> it to a char/varchar field).
>>
>>   Below is the error (log file) the DAO, OJB descriptor and
>> SQL for the
>> class in question.
>>
>>
>>   All help/ideas are welcome,
>>
>>
>> Pedro Salgado
>>
>>
>>
>> --- LOG FILE
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.StatementManager -
>> closeResources was called
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> executeInsert : [EMAIL PROTECTED]
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl
>> - SQL:INSERT
>> INTO REVISIONS
>> (id,document,title,edition,date_implementation,date_issued,dat
>> e_approval,dat
>> e_approval_qa,date_authorization,date_cancelled,date_deadline,
>> originator,app
>> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
>> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>>
>>
>> 12:46:34,519 - ERROR
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> SQLException during the execution of the insert (for a
>> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>>
>>
>> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
>> Source)
>> at
>> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
>> at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
>> orStatement(Un
>> known Source)
>> at
>> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
>> Source)
>>
>>
>> --- Java class
>> (Date => java.util.Date)
>>
>> private int id = 0;
>> private int document = 0;
>> private String Title = null;
>> private String Edition = null;
>> private Date dateImplementation = null;
>> private Date dateIssued = null;
>> private Date dateApproval = null;
>> private Date dateApprovalQA = null;
>> private Date dateAuthorization = null;
>> private Date dateCancelled = null;
>> private Date dateDeadline = null;
>> private int originator = 0;
>> private int approver = 0;
>> private int approverQA = 0;
>> private int authorizer = 0;
>> private int canceller = 0;
>> private String comment = null;
>> private int State = 0;
>> private String fileExtension = null;
>>
>>
>>
>> --- MSSQL SQL
>>
>>
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
>> name='REVISIONS_FK_1')
>> ALTER TABLE REVISIONS DROP CONSTRAINT REVISIONS_FK_1;
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name
>> = 'REVISIONS')
>> BEGIN
>>  DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
>> DECLARE refcursor CURSOR FOR
>>  select r

RE: mssql: sqlexception

[salgado.pc]

> Hi
>
> I had also a problem with java.util.Date on a Oracle DB. I had to change
> the types in my classes to java.sql.Date and it works fine now.
>
> An other way could be a conversion-class for java.util.Date (like
> Integer2IntegerFieldConversion) which creates a java.sql.Date from
> java.util.Date.
>
> Let me now if it solves your problem.
>

Thank you Roland, I will try this and send a reply to the newsletter.

Pedro Salgado

> Roland Ribi
>
>
>> -Original Message-
>> From: Pedro Salgado [mailto:[EMAIL PROTECTED]
>> Sent: Thursday, January 01, 1970 2:47 AM
>> To: OJB Users List
>> Subject: mssql: sqlexception
>>
>>
>>
>>   For several reasons, I have been using a OJB/MySQL
>> development platform
>> and everything was going ok. The problem was when I chose to
>> test the same
>> application with a OJB/MSSQL platform.
>>
>>
>>   Apart of some minor changes, I made it all work except for
>> a class (the
>> source and OJB description is below) that has a
>> TIMESTAMP/DATETIME mapping
>> and that also could be NULL (also tried with NOT NULL <=>
>> nullable="false"
>> and didn't work either).
>>   I tried Google and it seems a MSSQL JDBC driver bug (don't
>> know if it
>> could be a bug in OJB) but I wonder if anyone has came across
>> with this
>> problem and solved this or found a way around (please don't
>> tell me I have
>> to store the day-month-year on 3 database fields for each
>> date :( or change
>> it to a char/varchar field).
>>
>>   Below is the error (log file) the DAO, OJB descriptor and
>> SQL for the
>> class in question.
>>
>>
>>   All help/ideas are welcome,
>>
>>
>> Pedro Salgado
>>
>>
>>
>> --- LOG FILE
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.StatementManager -
>> closeResources was called
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> executeInsert : [EMAIL PROTECTED]
>>
>>
>> 12:46:34,503 - DEBUG
>> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl
>> - SQL:INSERT
>> INTO REVISIONS
>> (id,document,title,edition,date_implementation,date_issued,dat
>> e_approval,dat
>> e_approval_qa,date_authorization,date_cancelled,date_deadline,
>> originator,app
>> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
>> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
>>
>>
>> 12:46:34,519 - ERROR
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
>> SQLException during the execution of the insert (for a
>> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>>
>>
>> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
>> JDBC]Unable to
>> determine the type of the specified object.
>> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
>> Source)
>> at
>> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
>> at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
>> orStatement(Un
>> known Source)
>> at
>> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
>> Source)
>> at
>> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
>> Source)
>>
>>
>> --- Java class
>> (Date => java.util.Date)
>>
>> private int id = 0;
>> private int document = 0;
>> private String Title = null;
>> private String Edition = null;
>> private Date dateImplementation = null;
>> private Date dateIssued = null;
>> private Date dateApproval = null;
>> private Date dateApprovalQA = null;
>> private Date dateAuthorization = null;
>> private Date dateCancelled = null;
>> private Date dateDeadline = null;
>> private int originator = 0;
>> private int approver = 0;
>> private int approverQA = 0;
>> private int authorizer = 0;
>> private int canceller = 0;
>> private String comment = null;
>> private int State = 0;
>> private String fileExtension = null;
>>
>>
>>
>> --- MSSQL SQL
>>
>>
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
>> name='REVISIONS_FK_1')
>> ALTER TABLE REVISIONS DROP CONSTRAINT REVISIONS_FK_1;
>> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name
>> = 'REVISIONS')
>> BEGIN
>>  DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
>> DECLARE refcursor CURSOR FOR
>>  select reftables.name tablename, cons.name constraintname
>>   from sysobjects tables,
>>sysobjects reftables,
>>sysobjects cons,
>>sysreferences ref
>>where tables.id = ref.rkeyid
>>  and cons.id = ref.constid
>>  and reftables.id = ref.fkeyid
>>  and tables.name = 'REVISIONS'
>>  OPEN refcursor
>>  FETCH NEXT from refcursor into @reftable_7, @constraintname_7
>> while @@FETCH_STATUS = 0
>>  BEGIN

Re: XDoclet and Torque idMethod

[Thomas Dudziak]

On Tue, 16 Mar 2004, Laurie Harper wrote:

> The CVS version of the OJB XDoclet module requires 'autoincrement' to be 
> set to one of none/ojb/database. I have it set to 'database' which I 
> think should result in xxx-schema.xml containing an idMethod="native" on 
> the table declaration. Without it, Torque doesn't include the necessary 
> SQL to declare the column as an auto increment. Did I miss something 
> obbvious again? :-)
> 
> L.
> 
> PS, this is using torque-gen 3.1.

This is probably a change between torque 3.0 and 3.1. In 3.0, torque
generates the autoincrement attribute just fine. If you're not
specifically requiring torque 3.1, I'd suggest you stick to 3.0.2 for the
time being as I can't have a look at torque 3.1 before April.

Tom


-
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]

RE: mssql: sqlexception

[Ribi Roland]

I tried it now and with java.sql.Date it only saves the Date, with
java.sql.Timestamp it also saves the Time. This on Oracle.


Roland Ribi


> -Original Message-
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
> Sent: Wednesday, March 17, 2004 11:00 AM
> To: [EMAIL PROTECTED]
> Subject: RE: mssql: sqlexception
> 
> 
> 
>  Well, that makes sense. I used Torque to generate my DAOs 
> and since it
> worked with MySQL I made the assumption the mapping should be ok.
> 
>   Thank you, I will try this.
> 
> Pedro Salgado
> 
> > I'm using MSSQL successfully with java.sql.Timestamp in my classes.
> >
> > Using java.sql.Date worked with MSSQL for me but only the 
> date was saved
> > not the time as well - the time was always set to midday.
> >
> >
> >
> > -Original Message-
> > From: Ribi Roland [mailto:[EMAIL PROTECTED]
> > Sent: 17 March 2004 06:43
> > To: 'OJB Users List'
> > Subject: RE: mssql: sqlexception
> >
> > Hi
> >
> > I had also a problem with java.util.Date on a Oracle DB. I 
> had to change
> > the
> > types in my classes to java.sql.Date and it works fine now.
> >
> > An other way could be a conversion-class for java.util.Date (like
> > Integer2IntegerFieldConversion) which creates a java.sql.Date from
> > java.util.Date.
> >
> > Let me now if it solves your problem.
> >
> > Roland Ribi
> >
> >
> >> -Original Message-
> >> From: Pedro Salgado [mailto:[EMAIL PROTECTED]
> >> Sent: Thursday, January 01, 1970 2:47 AM
> >> To: OJB Users List
> >> Subject: mssql: sqlexception
> >>
> >>
> >>
> >>   For several reasons, I have been using a OJB/MySQL
> >> development platform
> >> and everything was going ok. The problem was when I chose to
> >> test the same
> >> application with a OJB/MSSQL platform.
> >>
> >>
> >>   Apart of some minor changes, I made it all work except for
> >> a class (the
> >> source and OJB description is below) that has a
> >> TIMESTAMP/DATETIME mapping
> >> and that also could be NULL (also tried with NOT NULL <=>
> >> nullable="false"
> >> and didn't work either).
> >>   I tried Google and it seems a MSSQL JDBC driver bug (don't
> >> know if it
> >> could be a bug in OJB) but I wonder if anyone has came across
> >> with this
> >> problem and solved this or found a way around (please don't
> >> tell me I have
> >> to store the day-month-year on 3 database fields for each
> >> date :( or change
> >> it to a char/varchar field).
> >>
> >>   Below is the error (log file) the DAO, OJB descriptor and
> >> SQL for the
> >> class in question.
> >>
> >>
> >>   All help/ideas are welcome,
> >>
> >>
> >> Pedro Salgado
> >>
> >>
> >>
> >> --- LOG FILE
> >>
> >> 12:46:34,503 - DEBUG
> >> org.apache.ojb.broker.accesslayer.StatementManager -
> >> closeResources was called
> >>
> >>
> >> 12:46:34,503 - DEBUG
> >> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> >> executeInsert : [EMAIL PROTECTED]
> >>
> >>
> >> 12:46:34,503 - DEBUG
> >> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl
> >> - SQL:INSERT
> >> INTO REVISIONS
> >> (id,document,title,edition,date_implementation,date_issued,dat
> >> e_approval,dat
> >> e_approval_qa,date_authorization,date_cancelled,date_deadline,
> >> originator,app
> >> 
> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
> >> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
> >>
> >>
> >> 12:46:34,519 - ERROR
> >> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> >> SQLException during the execution of the insert (for a
> >> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for
> >> JDBC]Unable to
> >> determine the type of the specified object.
> >>
> >>
> >> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for
> >> JDBC]Unable to
> >> determine the type of the specified object.
> >> at 
> com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
> >> Source)
> >> at
> >> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> >> at 
> com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
> >> Source)
> >> at
> >> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
> >> orStatement(Un
> >> known Source)
> >> at
> >> 
> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
> >> Source)
> >> at
> >> 
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
> >> Source)
> >> at
> >> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
> >> Source)
> >>
> >>
> >> --- Java class
> >> (Date => java.util.Date)
> >>
> >> private int id = 0;
> >> private int document = 0;
> >> private String Title = null;
> >> private String Edition = null;
> >> private Date dateImplementation = null;
> >> private Date dateIssued = null;
> >> private Date dateApproval = null;
> >> private Date dateApprovalQA = null;
> >> private Date dateAuthorization = null;
> >> private Date dateCancelled = null;
> >> private Date dateDeadline = null;
> >> private int 

RE: mssql: sqlexception

[Brendan Richards]

I'm using MSSQL successfully with java.sql.Timestamp in my classes. 

Using java.sql.Date worked with MSSQL for me but only the date was saved
not the time as well - the time was always set to midday.



-Original Message-
From: Ribi Roland [mailto:[EMAIL PROTECTED] 
Sent: 17 March 2004 06:43
To: 'OJB Users List'
Subject: RE: mssql: sqlexception

Hi

I had also a problem with java.util.Date on a Oracle DB. I had to change
the
types in my classes to java.sql.Date and it works fine now.

An other way could be a conversion-class for java.util.Date (like
Integer2IntegerFieldConversion) which creates a java.sql.Date from
java.util.Date. 

Let me now if it solves your problem.

Roland Ribi


> -Original Message-
> From: Pedro Salgado [mailto:[EMAIL PROTECTED]
> Sent: Thursday, January 01, 1970 2:47 AM
> To: OJB Users List
> Subject: mssql: sqlexception
> 
> 
> 
>   For several reasons, I have been using a OJB/MySQL 
> development platform
> and everything was going ok. The problem was when I chose to 
> test the same
> application with a OJB/MSSQL platform.
> 
> 
>   Apart of some minor changes, I made it all work except for 
> a class (the
> source and OJB description is below) that has a 
> TIMESTAMP/DATETIME mapping
> and that also could be NULL (also tried with NOT NULL <=> 
> nullable="false"
> and didn't work either).
>   I tried Google and it seems a MSSQL JDBC driver bug (don't 
> know if it
> could be a bug in OJB) but I wonder if anyone has came across 
> with this
> problem and solved this or found a way around (please don't 
> tell me I have
> to store the day-month-year on 3 database fields for each 
> date :( or change
> it to a char/varchar field).
> 
>   Below is the error (log file) the DAO, OJB descriptor and 
> SQL for the
> class in question.
> 
> 
>   All help/ideas are welcome,
> 
> 
> Pedro Salgado
> 
> 
> 
> --- LOG FILE
> 
> 12:46:34,503 - DEBUG 
> org.apache.ojb.broker.accesslayer.StatementManager -
> closeResources was called
> 
> 
> 12:46:34,503 - DEBUG 
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> executeInsert : [EMAIL PROTECTED]
> 
> 
> 12:46:34,503 - DEBUG
> org.apache.ojb.broker.accesslayer.sql.SqlGeneratorDefaultImpl 
> - SQL:INSERT
> INTO REVISIONS 
> (id,document,title,edition,date_implementation,date_issued,dat
> e_approval,dat
> e_approval_qa,date_authorization,date_cancelled,date_deadline,
> originator,app
> rover,approver_qa,authorizer,canceller,obs,state,sys_extension) VALUES
> (?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)
> 
> 
> 12:46:34,519 - ERROR 
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl -
> SQLException during the execution of the insert (for a
> pkg.pkg.dao.Revision): [Microsoft][SQLServer 2000 Driver for 
> JDBC]Unable to
> determine the type of the specified object.
> 
> 
> java.sql.SQLException: [Microsoft][SQLServer 2000 Driver for 
> JDBC]Unable to
> determine the type of the specified object.
> at com.microsoft.jdbc.base.BaseExceptions.createException(Unknown
> Source)
> at 
> com.microsoft.jdbc.base.BaseExceptions.getException(Unknown Source)
> at com.microsoft.jdbc.base.BasePreparedStatement.setObject(Unknown
> Source)
> at 
> org.apache.ojb.broker.platforms.PlatformDefaultImpl.setObjectF
> orStatement(Un
> known Source)
> at 
> org.apache.ojb.broker.accesslayer.StatementManager.bindInsert(Unknown
> Source)
> at 
> org.apache.ojb.broker.accesslayer.JdbcAccessImpl.executeInsert(Unknown
> Source)
> at 
> org.apache.ojb.broker.core.PersistenceBrokerImpl.storeToDb(Unknown
> Source)
> 
> 
> --- Java class
> (Date => java.util.Date)
> 
> private int id = 0;
> private int document = 0;
> private String Title = null;
> private String Edition = null;
> private Date dateImplementation = null;
> private Date dateIssued = null;
> private Date dateApproval = null;
> private Date dateApprovalQA = null;
> private Date dateAuthorization = null;
> private Date dateCancelled = null;
> private Date dateDeadline = null;
> private int originator = 0;
> private int approver = 0;
> private int approverQA = 0;
> private int authorizer = 0;
> private int canceller = 0;
> private String comment = null;
> private int State = 0;
> private String fileExtension = null;
> 
> 
> 
> --- MSSQL SQL
> 
> 
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type ='RI' AND
> name='REVISIONS_FK_1')
> ALTER TABLE REVISIONS DROP CONSTRAINT REVISIONS_FK_1;
> IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name 
> = 'REVISIONS')
> BEGIN
>  DECLARE @reftable_7 nvarchar(60), @constraintname_7 nvarchar(60)
>  DECLARE refcursor CURSOR FOR
>  select reftables.name tablename, cons.name constraintname
>   from sysobjects tables,
>sysobjects reftables,
>sysobjects cons,
>sysreferences ref
>where tables.id = ref.rkeyid
>  and cons.id = ref.constid
>  and reftables.id = ref.fkeyid
>  an