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 Ajitesh Das <ad...@zona.net> on 2004/09/16 01:16:03 UTC
Configure generated SQL in OJB: HOWTO do that? Will Velocity solve my problem?
Hi List:
I want to store an *supersize* byte array into a db table.
How do I control the SQL generated by ojb during queries? Do I have to
write my own Velocity template and add that path to db.profile.
For the following case scenarion: My app needs to generate SQL query
like this:
Here is the SQL that was bombed and was generated by OJB...
SELECT A0.PROPERTY_VALUE,A0.PROPERTY_NAME,A0.ID FROM PROPERTY A0 WHERE
A0. PROPERTY_NAME = ?
I like to change/control the where clause here: for e.g:
SELECT A0.PROPERTY_VALUE,A0.PROPERTY_NAME,A0.ID FROM PROPERTY A0 WHERE
A0. PROPERTY_NAME LIKE 0x110110110
HOW CAN I DO THAT?
-------------------------------------------------------------------
The Scenario:
I have made the followings changes:
DB Type : MSSQL
In db-schema.xml:
<table name="PROPERTY" >
<column name="ID" required="true" primaryKey="true" type="INTEGER"/>
<column name=" PROPERTY_NAME" required="true" primaryKey="false"
type="VARBINARY" />
<column name="PROPERTY_VALUE" required="true" primaryKey="false"
type="VARBINARY"/>
</table>
In the MSSQL DB =>>
It creates IMAGE datatype in MSSQL Server.
In repository.xml:
<class-descriptor class="com.xxx.Property" table="PROPERTY">
<field-descriptor id="1"
name="dbId"
column="ID"
jdbc-type="INTEGER"
primarykey="true"
nullable="false"
indexed="true"
autoincrement="true"
locking="false"
default-fetch="false"
/>
<field-descriptor id="2"
name="propertyName"
column="PROPERTY_NAME"
jdbc-type="VARBINARY"
primarykey="false"
nullable="true"
indexed="false"
autoincrement="false"
locking="false"
default-fetch="false"
conversion="com.xxx.yyy.Converter"
/>
<field-descriptor id="3"
name="propertyValue"
column="PROPERTY_VALUE"
jdbc-type="VARBINARY"
primarykey="false"
nullable="true"
indexed="false"
autoincrement="false"
locking="false"
default-fetch="false"
conversion="com.xxx.yyy.Converter"
/>
</class-descriptor>
When I run simple query, I get the following exception:[ but running in
MySQL is fine].
[org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
during the execution of the query (for a com.xxx.Property): The text,
ntext, and image data types cannot be compared or sorted, except when
using IS NULL or LIKE operator.
The text, ntext, and image data types cannot be compared or sorted,
except when using IS NULL or LIKE operator.
java.sql.SQLException: The text, ntext, and image data types cannot be
compared or sorted, except when using IS NULL or LIKE operator.
---------------------------------------------------------------------
To unsubscribe, e-mail: ojb-user-unsubscribe@db.apache.org
For additional commands, e-mail: ojb-user-help@db.apache.org
Re: Configure generated SQL in OJB: HOWTO do that? Will Velocity
solve my problem?
Posted by Jakob Braeuchi <jb...@gmx.ch>.
hi ajitesh,
ojb creates the sql dynamically so there's no need for a velocity template.
if you want to change from equal to like you can use Criteria#addLike.
hth
jakob
Ajitesh Das schrieb:
> Hi List:
>
> I want to store an *supersize* byte array into a db table.
>
> How do I control the SQL generated by ojb during queries? Do I have to
> write my own Velocity template and add that path to db.profile.
>
> For the following case scenarion: My app needs to generate SQL query
> like this:
>
> Here is the SQL that was bombed and was generated by OJB...
>
> SELECT A0.PROPERTY_VALUE,A0.PROPERTY_NAME,A0.ID FROM PROPERTY A0 WHERE
> A0. PROPERTY_NAME = ?
>
> I like to change/control the where clause here: for e.g:
>
> SELECT A0.PROPERTY_VALUE,A0.PROPERTY_NAME,A0.ID FROM PROPERTY A0 WHERE
> A0. PROPERTY_NAME LIKE 0x110110110
>
> HOW CAN I DO THAT?
>
>
> -------------------------------------------------------------------
> The Scenario:
>
>
>
>
> I have made the followings changes:
> DB Type : MSSQL
> In db-schema.xml:
>
> <table name="PROPERTY" >
> <column name="ID" required="true" primaryKey="true" type="INTEGER"/>
> <column name=" PROPERTY_NAME" required="true" primaryKey="false"
> type="VARBINARY" />
> <column name="PROPERTY_VALUE" required="true" primaryKey="false"
> type="VARBINARY"/>
> </table>
>
> In the MSSQL DB =>>
> It creates IMAGE datatype in MSSQL Server.
>
>
> In repository.xml:
>
> <class-descriptor class="com.xxx.Property" table="PROPERTY">
> <field-descriptor id="1"
> name="dbId"
> column="ID"
> jdbc-type="INTEGER"
> primarykey="true"
> nullable="false"
> indexed="true"
> autoincrement="true"
> locking="false"
> default-fetch="false"
> />
>
> <field-descriptor id="2"
> name="propertyName"
> column="PROPERTY_NAME"
> jdbc-type="VARBINARY"
> primarykey="false"
> nullable="true"
> indexed="false"
> autoincrement="false"
> locking="false"
> default-fetch="false"
> conversion="com.xxx.yyy.Converter"
> />
>
> <field-descriptor id="3"
> name="propertyValue"
> column="PROPERTY_VALUE"
> jdbc-type="VARBINARY"
> primarykey="false"
> nullable="true"
> indexed="false"
> autoincrement="false"
> locking="false"
> default-fetch="false"
> conversion="com.xxx.yyy.Converter"
> />
> </class-descriptor>
> When I run simple query, I get the following exception:[ but running in
> MySQL is fine].
>
> [org.apache.ojb.broker.accesslayer.JdbcAccessImpl] ERROR: SQLException
> during the execution of the query (for a com.xxx.Property): The text,
> ntext, and image data types cannot be compared or sorted, except when
> using IS NULL or LIKE operator.
> The text, ntext, and image data types cannot be compared or sorted,
> except when using IS NULL or LIKE operator.
>
> java.sql.SQLException: The text, ntext, and image data types cannot be
> compared or sorted, except when using IS NULL or LIKE operator.
>
>
>
>
> ---------------------------------------------------------------------
> 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