hi danilo,
thanks for the samples.
as you said, we cannot use || for ms-sql, that's why we currently use + for concatenation. the problem is imho when trying to concatenate numeric columns. i excpect ms-sql to add the values instead of concatenating them.
do you know what ms-sql does, when the first value is a string ? i'm thinking of using something like this :
'OJB' + col1 + col2
if ms-sql converts col1, col2 to string, the problem would be solved.
jakob
Danilo Tommasina schrieb:
Hi Jakob,
thanks for the reply.
An example: ( Table 1 ) 1 -> N ( Table 2 )
ReportQueryByCriteria( Table1.class, new String [] { "table1PK", "pathToTable2.table2PK" }, criteria );
Executing the query as usual generates something like this in ANSI syntax:
SELECT A0.TABLE_1_PK, A1.TABLE_2_PK INNER JOIN TABLE_2 A1 ON A0.TABLE_1_PK = A1.FK_TO_TABLE_1 FROM TABLE_1 A0 WHERE ...
or for the distinct:
SELECT DISTINCT A0.TABLE_1_PK, A1.TABLE_2_PK INNER JOIN TABLE_2 A1 ON A0.TABLE_1_PK = A1.FK_TO_TABLE_1 FROM TABLE_1 a0 WHERE ...
I would expect that doing a PersistenceProker.getCount() on the same query should generate following SQL: ( Sybase/MsSql concatenation syntax)
SELECT count ( A0.TABLE_1_PK + A1.TABLE_2_PK ) INNER JOIN TABLE_2 A1 ON A0.TABLE_1_PK = A1.FK_TO_TABLE_1 FROM TABLE_1 A0 WHERE ...
and for the distinct:
SELECT count ( distinct A0.TABLE_1_PK + A1.TABLE_2_PK ) INNER JOIN TABLE_2 A1 ON A0.TABLE_1_PK = A1.FK_TO_TABLE_1 FROM TABLE_1 A0 WHERE ...
And here comes the problem with the concatenation:
As I just found out there in Sybase/MsSql there are two types of concatentation characters the || is used ONLY for string concatenation while the + can be used on other datatypes.
Since I am not a Sybase/MsSql guru, I cannot tell you the exact difference between them. Hoever the || cannot be used if the columns are of numeric type.
I saw from your code commentary that other databases uses also other syntax that may have nothing to do with the currently implemented Platform.concatenate() method.
Probably we need a new method like Platform.concatWithinExpr( String [] cols ) that would generate the correct string for any kind of expressions that support this syntax.
There is another problem, the concatenation with + seems also not to work if the operands are not of compatible types (TEXT and NUMERIC for example) this may also cause some problems.
bye danilo
P.S: please forget my previous proposal with the GROUP BY, I am using a workaround for simulating distinct queries for objects that contains TEXT columns in Sybase, however this cannot work for this problem.
hi danilo,
my comment is correct ms-sql platform uses "+" as concatenation character. is this wrong ?
jakob
Jakob Braeuchi schrieb:
hi danilo,
Danilo Tommasina schrieb:
An addition to my previous post:
I saw in the BrokerHelper class that the Platform.concatenate() method is used to combine multi-primary keys, however there is a problem:
The method is thought for concatenating strings and not numeric fields. For ex. in Sybase and MS-SQL you use the || character for string concatenation, but the count( distinct expr ) ) clause uses the + character, other dbms use also other syntax. I think that a separate
afaik the platform for ms-sql also uses || for concatenation. i think my comment in BrokerHelper is wrong.
jakob
method should be added for this purpose, something like concatForDistinct() that would generate the correct String for the distinct clause.
Hi,
(Using OJB 1.0.1)
I think the PersistenceBroker.getCount() on ReportQueryByCriteria used on joined tables produces wrong or at least unexpected results. :)
An example:
( Table 1 ) 1 -> N ( Table 2 )
Build a ReportQueryByCriteria like this: new ReportQueryByCriteria( Table1.class, new String [] { "table1PK", "pathToTable2.table2PK" }, criteria );
now calling PersistenceBroker.getCount( reportQuery ) will produce a count( * ) statement, which can deliver different results than count( table1PK, table2PK ) depending on the used join clause and the WHERE criterias.
Furthermore if setting the query to DISTINCT, OJB uses the PK column(s) of the 'searchClass' and generates following statement: SELECT count( distinct table1PK ) ... which will also not produce the expected result, at least the one expected by me :)
I think the correct query (distinct and not) should just contain the same columns defined in the original ReportQuery.
OJB shares the same core algorithm for generating the count query for QueryByCriteria and ReportQueryByCriteria, while for the QueryByCriteria the algorithm is correct for the ReportQuery it probably isn't.
The 'evil' code is in class org.apache.ojb.broker.util.BrokerHelper
bye danilo
--------------------------------------------------------------------- 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]
