You are viewing a plain text version of this content. The canonical link for it is here.
Posted to ojb-user@db.apache.org by "Janssen, Roger" <ro...@ibanx.nl> on 2008/03/03 12:24:24 UTC
Is it possible to force OJB not use execute a prepared statement?
Hi,
Just a general question about sql statement execution.
Is it possible to force OJB not to execute a prepared statement, but to
execute the sql as a regular sql statement?
The reason behind this is that we hit some kind of upperbound limit with
MSSQL regarding the maximum allowed number of parameters in a prepared
statement. Some of the statements that are being generated by OJB are
rejected (SQL exceptions are thrown) by MSSQL, because this upperbound
is crossed.
One solution would be to not use a prepared statement in these cases.
Greetings,
Roger Janssen
*************************************************************************
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.
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by Christian Lipp <c....@xion.at>.
Hi Roger,
you are right with the chess match ....
I understand your point now, but we are not using a strategie at all (which
means we have exactly one strategie and it's hardcoded).
So we have always the same way to convert a boolean, we always enclose
strings with quotes, and so on...
The whole function looks like this:
private static SimpleDateFormat df_date = new
SimpleDateFormat("yyyy-MM-dd");
private static SimpleDateFormat df_time = new
SimpleDateFormat("HH:mm:ss.SSS");
public static String asDBParameter(Object value)
{
if (value instanceof Number)
return value.toString();
if (value instanceof String || value instanceof Character)
return "'" + escapeString(value.toString()) + "'";
if (value instanceof Timestamp)
return "'" + value.toString() + "'";
if (value instanceof Time)
return "'" + df_time.format((Time)value) + "'";
if (value instanceof Date)
return "'" + df_date.format((Date)value) + "'";
return value.toString();
}
That's enough for our needs.
But when you want to implement a strategie and you need class descriptor or
field descriptor,
then I would override all functions which are calling appendParameter (for
example appendSelectionCriteria) and would provide a myAppendParameter,
which I would call from the overriden functions. The
myAppendParameter-function can have more arguments for strategie handling.
The signatur for appendSelectionCriteria is:
void appendSelectionCriteria(TableAlias alias, PathInfo pathInfo,
SelectionCriteria c, StringBuffer buf)
With the first two parameters you could call
protected FieldDescriptor getFieldDescriptor(TableAlias aTableAlias,
PathInfo aPathInfo)
and then you can give the field descriptor to myAppendParameter and from
there to asDBParameter and in this function you can perform your strategie.
Hope that helps,
CL
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Donnerstag, 08. Mai 2008 11:39
To: OJB Users List
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
Hi Christian,
I am also late in my response, this thread feels like a long distance chess
match communicating the moves by post (not email that is..) :)...
I understand you implemted a method "public static String
asDBParameter(Object value)", but with this solution, one of my questions
is: where/when are the conversion-strategies executed? The logical location
would be within this method, but then we need more information within this
method. We need to know whether there is a strategy configured, so we need
to have either the class-desriptor and attribute name, or the
field-descriptor. But that is all missing. We do need this because you just
cannot convert the java value object to the a string representation, and use
this within your query... A java boolean value may need to be converted into
an int value on a DB level... String clauses need to be enclosed within
quotes, integer values not... So more needs to be done with the java value
object... in the following order :
- apply conversion strategy
- convert to jdbc type
- convert to string representation (enclosed with quotes when required)
I am missing all this in your solution... Or I do not fully understand your
solution... Please explain me where/when in your solution the
conversion-strategies are appplied? Where/when are the values converted to
the proper JDBC type?
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Christian Lipp [mailto:c.lipp@xion.at]
Sent: Thursday, April 17, 2008 5:04 PM
To: 'OJB Users List'
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
Hi Roger, sorry for my delay.
> 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.
Exactly.
> In your example you use the asDBParameter method, but that one is not
available within 1.0rc5 version of OJB.
This should be a static function in your class that overrided
SqlSelectStatement (in my example it is DynamicSqlSelectStatement):
public static String asDBParameter(Object value)
{
if (value instanceof Number)
return value.toString();
// and so on for all your data types. Don't forgot to quote
strings.
This is all, you don't need the other things you mentioned.
It's just two classes, try it out.
You activate the class in your OJB property file with:
#-----------------------------------------------------------------------
----
-------------
# SqlGenerator
#-----------------------------------------------------------------------
----
-------------
# The SqlGeneratorClass entry defines the SqlGenerator implemementation to
be used
SqlGeneratorClass=org.apache.ojb.broker.accesslayer.sql.DynamicSqlSelect
Stat
ement
Regards, CL
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Freitag, 04. April 2008 14:05
To: OJB Users List
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
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
*************************************************************************
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
Hi Christian,
I am also late in my response, this thread feels like a long distance
chess match communicating the moves by post (not email that is..) :)...
I understand you implemted a method "public static String
asDBParameter(Object value)", but with this solution, one of my
questions is: where/when are the conversion-strategies executed? The
logical location would be within this method, but then we need more
information within this method. We need to know whether there is a
strategy configured, so we need to have either the class-desriptor and
attribute name, or the field-descriptor. But that is all missing. We do
need this because you just cannot convert the java value object to the a
string representation, and use this within your query... A java boolean
value may need to be converted into an int value on a DB level... String
clauses need to be enclosed within quotes, integer values not... So more
needs to be done with the java value object... in the following order :
- apply conversion strategy
- convert to jdbc type
- convert to string representation (enclosed with quotes when required)
I am missing all this in your solution... Or I do not fully understand
your solution... Please explain me where/when in your solution the
conversion-strategies are appplied? Where/when are the values converted
to the proper JDBC type?
Greetings,
Roger Janssen
iBanx
-----Original Message-----
From: Christian Lipp [mailto:c.lipp@xion.at]
Sent: Thursday, April 17, 2008 5:04 PM
To: 'OJB Users List'
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
Hi Roger, sorry for my delay.
> 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.
Exactly.
> In your example you use the asDBParameter method, but that one is not
available within 1.0rc5 version of OJB.
This should be a static function in your class that overrided
SqlSelectStatement (in my example it is DynamicSqlSelectStatement):
public static String asDBParameter(Object value)
{
if (value instanceof Number)
return value.toString();
// and so on for all your data types. Don't forgot to
quote strings.
This is all, you don't need the other things you mentioned.
It's just two classes, try it out.
You activate the class in your OJB property file with:
#-----------------------------------------------------------------------
----
-------------
# SqlGenerator
#-----------------------------------------------------------------------
----
-------------
# The SqlGeneratorClass entry defines the SqlGenerator implemementation
to be used
SqlGeneratorClass=org.apache.ojb.broker.accesslayer.sql.DynamicSqlSelect
Stat
ement
Regards, CL
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Freitag, 04. April 2008 14:05
To: OJB Users List
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
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
*************************************************************************
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by Christian Lipp <c....@xion.at>.
Hi Roger, sorry for my delay.
> 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.
Exactly.
> In your example you use the asDBParameter method, but that one is not
available within 1.0rc5 version of OJB.
This should be a static function in your class that overrided
SqlSelectStatement (in my example it is DynamicSqlSelectStatement):
public static String asDBParameter(Object value)
{
if (value instanceof Number)
return value.toString();
// and so on for all your data types. Don't forgot to quote
strings.
This is all, you don't need the other things you mentioned.
It's just two classes, try it out.
You activate the class in your OJB property file with:
#---------------------------------------------------------------------------
-------------
# SqlGenerator
#---------------------------------------------------------------------------
-------------
# The SqlGeneratorClass entry defines the SqlGenerator implemementation to
be used
SqlGeneratorClass=org.apache.ojb.broker.accesslayer.sql.DynamicSqlSelectStat
ement
Regards, CL
-----Original Message-----
From: Janssen, Roger [mailto:roger.janssen@ibanx.nl]
Sent: Freitag, 04. April 2008 14:05
To: OJB Users List
Subject: RE: Is it possible to force OJB not use execute a prepared
statement?
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 [mailto:c.lipp@xion.at]
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 [mailto:roger.janssen@ibanx.nl]
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 [mailto:c.lipp@xion.at]
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
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
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 [mailto:c.lipp@xion.at]
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 [mailto:roger.janssen@ibanx.nl]
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 [mailto:c.lipp@xion.at]
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by Christian Lipp <c....@xion.at>.
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 [mailto:roger.janssen@ibanx.nl]
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 [mailto:c.lipp@xion.at]
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by "Janssen, Roger" <ro...@ibanx.nl>.
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 [mailto:c.lipp@xion.at]
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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
RE: Is it possible to force OJB not use execute a prepared statement?
Posted by Christian Lipp <c....@xion.at>.
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
-----Original Message-----
From: Armin Waibel [mailto:arminw@apache.org]
Sent: Montag, 03. März 2008 16:19
To: OJB Users List
Subject: Re: Is it possible to force OJB not use execute a prepared
statement?
Hi Roger,
Janssen, Roger wrote:
> Hi,
>
> Just a general question about sql statement execution.
>
> Is it possible to force OJB not to execute a prepared statement, but
> to execute the sql as a regular sql statement?
Sorry no! The use of prepared statements is deeply rooted in the OJB core.
>
> The reason behind this is that we hit some kind of upperbound limit
> with MSSQL regarding the maximum allowed number of parameters in a
> prepared statement. Some of the statements that are being generated by
> OJB are rejected (SQL exceptions are thrown) by MSSQL, because this
> upperbound is crossed.
Maybe you can find a "hidden property" to adjust the upper bound of maximal
PS parameter.
regards,
Armin
>
> One solution would be to not use a prepared statement in these cases.
>
> Greetings,
>
> Roger Janssen
>
> **********************************************************************
> *** 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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Is it possible to force OJB not use execute a prepared statement?
Posted by Armin Waibel <ar...@apache.org>.
Hi Roger,
Janssen, Roger wrote:
> Hi,
>
> Just a general question about sql statement execution.
>
> Is it possible to force OJB not to execute a prepared statement, but to
> execute the sql as a regular sql statement?
Sorry no! The use of prepared statements is deeply rooted in the OJB core.
>
> The reason behind this is that we hit some kind of upperbound limit with
> MSSQL regarding the maximum allowed number of parameters in a prepared
> statement. Some of the statements that are being generated by OJB are
> rejected (SQL exceptions are thrown) by MSSQL, because this upperbound
> is crossed.
Maybe you can find a "hidden property" to adjust the upper bound of
maximal PS parameter.
regards,
Armin
>
> One solution would be to not use a prepared statement in these cases.
>
> Greetings,
>
> Roger Janssen
>
> *************************************************************************
> 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: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org