Janssen, Roger
Fri, 04 Apr 2008 05:05:16 -0700
Hi Christian,
It took me a while to have a closer look at your suggested solution, but I
finally had some time for it.
I do have a few/questions remarks. Maybe some it is caused by using different
versions of OJB, I do not know... We use 1.0rc5, but all the things you
describe, match this version as far as I can tell.
As I understand your example, it boils down to the appendParameter method in
your solution. That method adds the String representation of the value into the
query instead of the questionmark, used as argument placeholder in the prepared
statement.
In your example you use the asDBParameter method, but that one is not available
within 1.0rc5 version of OJB. However, it seems unlikely to me that this method
will do everything that is required. This method has just one argument, a value
represented by an instance of a java datatype (integer/date/string/...). What
the asDBParameter should do is the following:
1. execute the fieldconversion for the attribute associated with this value,
which is given by the fielddescriptor
2. convert the outcome of the fieldconversion into a JDBC typed object instance
3. add the string value representation of the result from step 2 to into the
query
For step one you require the classdescriptor and the fieldname, or the
fielddescriptor. The method asDBParameter does not have access to these
objects! They are not passed as arguments. Neither does the encapsulating
appendParameter method. So to make this work, these arguments should be passed
on, with all these nested method calls, which implies that you have to change a
lot of existing OJB method signatures (api interfaces). I am reluctant to do
so, because this would really complicate migrating to newer versions of OJB.
Step two is not obvious as well. The current OJB solution embeds this
conversion inside the PreparedStatement class (used in the StatementManager en
Platform-implementation classes), it's like a black box, and you cannot pull
the converted argument values out of this statement. So I have no solution yet
to perform the conversion.
So how are the fieldconversions executed in your suggested solution, and when
does the java to jdbc datatype conversion takes place?
What also is missing in your solution is that in the end, the StatementManager
class (in 1.0rc5) binds all the values to the parameters. This now should no
longer be necessary, so you need to implement your own JdbcAccessImpl class
that implements a custom public ResultSetAndStatement executeQuery(Query query,
ClassDescriptor cld) throws PersistenceBrokerException method. Within this
method, the value/parameter binding can be removed (call to method of
StatementManager). Not doing this, may not lead to errors, but doing this will
remove a lot of now redundant overhead.
So... a bit late... but here is my response on your suggested solution and the
exaample code you send.
Of course... there is always a chance that I got it completely wrong, please
let me know.
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Christian Lipp [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2008 5:45 PM
To: 'OJB Users List'
Subject: RE: Is it possible to force OJB not use execute a prepared statement?
In the OJB property file you have an entry "SqlGeneratorClass", which allows
you to choose a different generator implementation.
Override SqlGeneratorDefaultImpl. You have to implement a ctor, which calls the
super ctor and a function getPreparedSelectStatement(), which delegates to your
own Statement class.
This means that you handle SELECT statements, the rest (INSERT, DELETE,
UPDATE) is still handeld by SqlGeneratorDefaultImpl. If you want more, you have
to override getPreparedDeleteStatement for DELETE and so on. The class would
look like:
Then you have to write your own statement class (override SqlSelectStatement).
Again, you have to implement the ctors and override the function
appendParameter().
In this function you have to format the concrete value (function
asDBParameter):
/**
* Overridden method; appends the Parameter (real value) or the
sub-query.
* @param value the value of the criteria
*/
protected void appendParameter(Object value, StringBuffer buf)
{
if (value instanceof Query)
{
super.appendSubQuery((Query) value, buf);
}
else
{
buf.append(asDBParameter(value));
}
}
Since the statements are recursive, you also have to override
getSubQuerySQL:
/**
* Convert subQuery to SQL
* @param subQuery the subQuery value of SelectionCriteria
*/
protected String getSubQuerySQL(Query subQuery)
{
ClassDescriptor cld =
getRoot().cld.getRepository().getDescriptorFor(subQuery.getSearchClass());
String sql;
if (subQuery instanceof QueryBySQL)
{
sql = ((QueryBySQL) subQuery).getSql();
}
else
{
sql = new DynamicSqlSelectStatement(this,
getPlatform(), cld, subQuery, getLogger()).getStatement();
}
return sql;
}
You have to build your own strategie. I will post our strategies soon.
Greetings, CL
-----Original Message-----
From: Janssen, Roger [EMAIL PROTECTED]
Sent: Mittwoch, 05. März 2008 15:02
To: OJB Users List
Subject: RE: Is it possible to force OJB not use execute a prepared statement?
Hi Christian,
The problem, I believe, is with the number of parameters (parameter
markers) that crosses a certain boundary. We ran into an upper limit of 2000
(or something like that) on MSSQL server. That is a problem for us because we
sometimes generate queries that have more parameters (yes...
really), and then MSSQL server throws an exception.
So, I am very interested in your solution. It could help solve our problem as
well.
Roger Janssen
iBanx
-----Original Message-----
From: Christian Lipp [EMAIL PROTECTED]
Sent: Wednesday, March 05, 2008 2:27 PM
To: 'OJB Users List'
Subject: Potential SPAM:RE: Is it possible to force OJB not use execute a
prepared statement?
Hi Roger,
OJB uses alsways prepared statements as armin said before, but do you have
problems with the prepared statements or with the count of parameter markers (?
in the where clause)?
I am asking because we did a lot of experiments with different implementations
(or specialisations) of SqlGeneratorDefaultImpl. The first implementation was
not using parameter markers at all.
So we used prepared statements without any parameter markers, which comes close
to dynamic statements (at least in my understanding and I think this is what
you need).
I would like to post what we did to discuss it here on the mailing list (we are
using db2 and could increase the performance 40% in online acces and 50% in
batch access).
CL
*************************************************************************
The information contained in this communication is confidential and is intended
solely for the use of the individual or entity to whom it is addressed.You
should not copy, disclose or distribute this communication without the
authority of iBanx bv. iBanx bv is neither liable for the proper and complete
transmission of the information has been maintained nor that the communication
is free of viruses, interceptions or interference.
If you are not the intended recipient of this communication please return the
communication to the sender and delete and destroy all copies.
---------------------------------------------------------------------
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]