You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user-java@ibatis.apache.org by "a.rubalcaba" <a....@yahoo.com> on 2009/03/27 18:11:51 UTC
Ibatis CLOB Support
I am currently working on a project using Spring + Ibatis. DBA's have
recently switched a varchar field into a CLOB field to support large Strings
of data.
My SQL map has the following
<result property="filters" column="FILTER_DATA" jdbcType="CLOB"/>
which maps to a String object in my java class.
I can retrieve data ok. Its when I try to insert data that I get the
following error.
java.sql.SQLException: ORA-01704: string literal too long
I am using the classes12.jar JDBC driver, Ibatis 2.2.0, and Spring 2.0
Any help on this issue would be greatly appreciated.
--
View this message in context: http://www.nabble.com/Ibatis-CLOB-Support-tp22745185p22745185.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Ibatis CLOB Support
Posted by Jeff Butler <je...@gmail.com>.
If you use a parameterMap, use question marks (?). If you use a
parameterClass, then use hash marks (#id#, etc.). In your case, use
question marks.
Jeff Butler
On Sun, Mar 29, 2009 at 8:35 PM, a.rubalcaba <a....@yahoo.com> wrote:
>
> So what parameter marks should I be using?
>
>
>
> Larry Meadors wrote:
>>
>> Yikes, be careful with that thing, it's loaded. ;-)
>>
>> $variable$ does substitution, so should really only be used as an
>> absolute last resort because of the SQL injection risk.
>>
>> Also, this statement will be sent to the database with no parameters,
>> because they are all being substituted in.
>>
>> For example, if you did "insert into blah (col1, col2) values ($val1$,
>> $val2$)" where val1 = 12 and val2 = '34'...
>>
>> The database doesn't get this: "insert into blah (col1, col2) values (?,
>> ?)".
>>
>> It gets "insert into blah (col1, col2) values (12, '34')" instead.
>>
>> In your case, you are then trying to set parameters on it, but there
>> are no parameter markers, so you get "Invalid column index".
>>
>> Further, if val2 is '34;drop table blah;--', you just inserted a
>> record, then dropped the table. When that happens in a live app, you
>> better hope you have a recent resume. :-D
>>
>> Larry
>>
>>
>
> --
> View this message in context: http://www.nabble.com/Ibatis-CLOB-Support-tp22745185p22775019.html
> Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
>
>
Re: Ibatis CLOB Support
Posted by "a.rubalcaba" <a....@yahoo.com>.
So what parameter marks should I be using?
Larry Meadors wrote:
>
> Yikes, be careful with that thing, it's loaded. ;-)
>
> $variable$ does substitution, so should really only be used as an
> absolute last resort because of the SQL injection risk.
>
> Also, this statement will be sent to the database with no parameters,
> because they are all being substituted in.
>
> For example, if you did "insert into blah (col1, col2) values ($val1$,
> $val2$)" where val1 = 12 and val2 = '34'...
>
> The database doesn't get this: "insert into blah (col1, col2) values (?,
> ?)".
>
> It gets "insert into blah (col1, col2) values (12, '34')" instead.
>
> In your case, you are then trying to set parameters on it, but there
> are no parameter markers, so you get "Invalid column index".
>
> Further, if val2 is '34;drop table blah;--', you just inserted a
> record, then dropped the table. When that happens in a live app, you
> better hope you have a recent resume. :-D
>
> Larry
>
>
--
View this message in context: http://www.nabble.com/Ibatis-CLOB-Support-tp22745185p22775019.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.
Re: Ibatis CLOB Support
Posted by Larry Meadors <la...@gmail.com>.
Yikes, be careful with that thing, it's loaded. ;-)
$variable$ does substitution, so should really only be used as an
absolute last resort because of the SQL injection risk.
Also, this statement will be sent to the database with no parameters,
because they are all being substituted in.
For example, if you did "insert into blah (col1, col2) values ($val1$,
$val2$)" where val1 = 12 and val2 = '34'...
The database doesn't get this: "insert into blah (col1, col2) values (?, ?)".
It gets "insert into blah (col1, col2) values (12, '34')" instead.
In your case, you are then trying to set parameters on it, but there
are no parameter markers, so you get "Invalid column index".
Further, if val2 is '34;drop table blah;--', you just inserted a
record, then dropped the table. When that happens in a live app, you
better hope you have a recent resume. :-D
Larry
Re: Ibatis CLOB Support
Posted by "a.rubalcaba" <a....@yahoo.com>.
a.rubalcaba wrote:
>
> I am currently working on a project using Spring + Ibatis. DBA's have
> recently switched a varchar field into a CLOB field to support large
> Strings of data.
>
> My SQL map has the following
>
> <result property="filters" column="FILTER_DATA" jdbcType="CLOB"/>
>
> which maps to a String object in my java class.
>
> I can retrieve data ok. Its when I try to insert data that I get the
> following error.
>
> java.sql.SQLException: ORA-01704: string literal too long
>
> I am using the classes12.jar JDBC driver, Ibatis 2.2.0, and Spring 2.0
>
> Any help on this issue would be greatly appreciated.
>
I got rid of the ORA-01704: string literal too long error. My new issue is I
keep getting the following error.
--- Check the parameter mapping for the 'id' property.
--- Cause: java.sql.SQLException: Invalid column index
Here is my parameter Map:
<parameterMap class="chartFavoriteVO" id="chartParameter" >
<parameter property="id" javaType="int" jdbcType="NUMBER" />
<parameter property="chartName" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="userId" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="filters" jdbcType="CLOB" javaType="string"
typeHandler="org.springframework.orm.ibatis.suppor
t.ClobStringTypeHandler"/>
<parameter property="publicInd" javaType="string" jdbcType="CHAR"/>
<parameter property="groupBy" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="chartType" javaType="int" jdbcType="NUMBER"/>
<parameter property="chartNotes" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="measureData" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="orderBy" javaType="string" jdbcType="VARCHAR2"/>
<parameter property="sortOrder" javaType="string" jdbcType="VARCHAR2"/>
</parameterMap>
Here is my insert
<insert id="insertChartFavorite" parameterMap="chartParameter">
insert INTO CHART_FAVS( FAV_ID, CHART_NAME, USER_ID,
FILTER_DATA,GROUPBY,PUBLIC_FLAG,CHART_TYPE,CHART_N
OTES,MEASURE_DATA,ORDER_BY,SORT_ORDER)
values ($id$,'$chartName$','$userId$','$filters$','$group
By$','$publicInd$',$chartType$,'$chartNotes$','$me
asureData$','$orderBy$','$sortOrder$')
</insert>
I'm trying to figureout what I'm missing and why I keep getting this error.
I tried removing some of the fields and inserting only the 3 needed fields
and that didn't work either.
--
View this message in context: http://www.nabble.com/Ibatis-CLOB-Support-tp22745185p22768773.html
Sent from the iBATIS - User - Java mailing list archive at Nabble.com.