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 "Barnett, Brian W." <br...@pearson.com> on 2005/07/11 23:45:21 UTC
How to get ## into a SQL statement
Wondering how to get ## into a SQL statement successfully.
SELECT col1, col2
INTO #paramTempTable#
Blah blah
I set paramTempTable to ##tempTableName.
I get the following error:
com.ibatis.dao.client.DaoException: Error executing query for list. Cause:
com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/scholar/model/map/StudentProfileSQL.xml.
--- The error occurred while applying a parameter map.
--- Check the getStudentProfileDataCombined2-InlineParameterMap.
--- Check the statement (query failed).
--- Cause: java.sql.SQLException: Line 1: Incorrect syntax near '@P0'.
Here are the debug statements of the query and params:
[15:50] DEBUG PreparedStatement () - {pstm-100082} PreparedStatement:
SELECT ts.dateTested, ts.test_score AS the_test_score, ts.test_raw_points
AS the_raw_points, ts.grade_level_id, a.abbrev, a.assessment_id,
a.score_method_id, y.display, sc.school_name, tse.session_name,
tse.session_id, pp.lowcut AS stuLowCut, pp.highcut AS stuHighCut, pp.prof
AS stuProf, pp.comp AS stuAbove, pp2.lowcut AS schLowCut, pp2.highcut AS
schHighCut, pp2.prof AS schProf, pp2.comp AS schAbove, pp3.lowcut AS
disLowCut, pp3.highcut AS disHighCut, pp3.prof AS disProf, pp3.comp AS
disAbove, avs.schoolScoreAvg, avs.schoolRawAvg, avd.distScoreAvg,
avd.distRawAvg, avd.maxScore, avd.minScore INTO ? FROM test_score ts JOIN
assessment a ON a.assessment_id = ts.assessment_id JOIN test t ON t.test_id
= ts.test_id JOIN school_year y ON y.year_id = ts.year_id JOIN school sc ON
sc.school_id = ts.school_id JOIN test_session tse ON tse.session_id =
t.session_id JOIN s_avg_test_score avs ON avs.assessment_id =
ts.assessment_id AND avs.school_id = ts.school_id AND avs.grade_level_id =
ts.grade_level_id AND avs.test_id = ts.test_id AND avs.year_id =
ts.year_id AND avs.session_id = t.session_id JOIN d_avg_test_score avd ON
avd.assessment_id = ts.assessment_id AND avd.district_id = ts.district_id
AND avd.grade_level_id = ts.grade_level_id AND avd.test_id = ts.test_id
AND avd.year_id = ts.year_id AND avd.session_id = t.session_id JOIN
view_pp pp ON pp.a_id = ts.assessment_id AND pp.gl_id = ts.grade_level_id
AND pp.s_id = t.session_id AND pp.pp_id = ? AND pp.rce_id IS NULL AND
ts.test_score BETWEEN pp.lowcut AND pp.highcut JOIN view_pp pp2 ON pp2.a_id
= ts.assessment_id AND pp2.gl_id = ts.grade_level_id AND pp2.s_id =
t.session_id AND pp2.pp_id = ? AND pp2.rce_id IS NULL AND
avs.schoolScoreAvg BETWEEN pp2.lowcut AND pp2.highcut JOIN view_pp pp3 ON
pp3.a_id = ts.assessment_id AND pp3.gl_id = ts.grade_level_id AND pp3.s_id
= t.session_id AND pp3.pp_id = ? AND pp3.rce_id IS NULL AND
avd.distScoreAvg BETWEEN pp3.lowcut AND pp3.highcut WHERE ts.sch_student_id
= ? ; SELECT DISTINCT dateTested, the_test_score, the_raw_points,
grade_level_id, abbrev, assessment_id, score_method_id, display,
school_name, session_name, session_id, stuLowCut, stuHighCut, stuProf,
stuAbove, schLowCut, schHighCut, schProf, schAbove, disLowCut, disHighCut,
disProf, disAbove, schoolScoreAvg, schoolRawAvg, distScoreAvg, distRawAvg,
maxScore, minScore FROM ? ;
[15:50] DEBUG PreparedStatement () - {pstm-100082} Parameters:
[##112111861335110817, a.primary_prof_profile_id, a.primary_prof_profile_id,
a.primary_prof_profile_id, 10817, ##112111861335110817]
[15:50] DEBUG PreparedStatement () - {pstm-100082} Types: [java.lang.String,
java.lang.String, java.lang.String, java.lang.String, java.lang.Integer,
java.lang.String]
****************************************************************************
This email may contain confidential material.
If you were not an intended recipient,
Please notify the sender and delete all copies.
We may monitor email to and from our network.
****************************************************************************
Re: How to get ## into a SQL statement
Posted by Larry Meadors <la...@gmail.com>.
The remapResults attribute should not be required - the table name
changes, but the column list does not.
Larry
On 7/12/05, Brice Ruth <bd...@gmail.com> wrote:
> I think your problem is that you need to use $paramTempTable$ to
> parameterize your table. You'll probably also need to set
> remapResults="true" in your mapped statement definition.
Re: How to get ## into a SQL statement
Posted by Brice Ruth <bd...@gmail.com>.
I think your problem is that you need to use $paramTempTable$ to
parameterize your table. You'll probably also need to set
remapResults="true" in your mapped statement definition.
Brice
2005/7/11, Barnett, Brian W. <br...@pearson.com>:
> Wondering how to get ## into a SQL statement successfully.
>
> SELECT col1, col2
> INTO #paramTempTable#
> Blah blah
>
> I set paramTempTable to ##tempTableName.
>
> I get the following error:
> com.ibatis.dao.client.DaoException: Error executing query for list. Cause:
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in com/scholar/model/map/StudentProfileSQL.xml.
> --- The error occurred while applying a parameter map.
> --- Check the getStudentProfileDataCombined2-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: Line 1: Incorrect syntax near '@P0'.
>
> Here are the debug statements of the query and params:
> [15:50] DEBUG PreparedStatement () - {pstm-100082} PreparedStatement:
> SELECT ts.dateTested, ts.test_score AS the_test_score, ts.test_raw_points
> AS the_raw_points, ts.grade_level_id, a.abbrev, a.assessment_id,
> a.score_method_id, y.display, sc.school_name, tse.session_name,
> tse.session_id, pp.lowcut AS stuLowCut, pp.highcut AS stuHighCut, pp.prof
> AS stuProf, pp.comp AS stuAbove, pp2.lowcut AS schLowCut, pp2.highcut AS
> schHighCut, pp2.prof AS schProf, pp2.comp AS schAbove, pp3.lowcut AS
> disLowCut, pp3.highcut AS disHighCut, pp3.prof AS disProf, pp3.comp AS
> disAbove, avs.schoolScoreAvg, avs.schoolRawAvg, avd.distScoreAvg,
> avd.distRawAvg, avd.maxScore, avd.minScore INTO ? FROM test_score ts JOIN
> assessment a ON a.assessment_id = ts.assessment_id JOIN test t ON t.test_id
> = ts.test_id JOIN school_year y ON y.year_id = ts.year_id JOIN school sc ON
> sc.school_id = ts.school_id JOIN test_session tse ON tse.session_id =
> t.session_id JOIN s_avg_test_score avs ON avs.assessment_id =
> ts.assessment_id AND avs.school_id = ts.school_id AND avs.grade_level_id =
> ts.grade_level_id AND avs.test_id = ts.test_id AND avs.year_id =
> ts.year_id AND avs.session_id = t.session_id JOIN d_avg_test_score avd ON
> avd.assessment_id = ts.assessment_id AND avd.district_id = ts.district_id
> AND avd.grade_level_id = ts.grade_level_id AND avd.test_id = ts.test_id
> AND avd.year_id = ts.year_id AND avd.session_id = t.session_id JOIN
> view_pp pp ON pp.a_id = ts.assessment_id AND pp.gl_id = ts.grade_level_id
> AND pp.s_id = t.session_id AND pp.pp_id = ? AND pp.rce_id IS NULL AND
> ts.test_score BETWEEN pp.lowcut AND pp.highcut JOIN view_pp pp2 ON pp2.a_id
> = ts.assessment_id AND pp2.gl_id = ts.grade_level_id AND pp2.s_id =
> t.session_id AND pp2.pp_id = ? AND pp2.rce_id IS NULL AND
> avs.schoolScoreAvg BETWEEN pp2.lowcut AND pp2.highcut JOIN view_pp pp3 ON
> pp3.a_id = ts.assessment_id AND pp3.gl_id = ts.grade_level_id AND pp3.s_id
> = t.session_id AND pp3.pp_id = ? AND pp3.rce_id IS NULL AND
> avd.distScoreAvg BETWEEN pp3.lowcut AND pp3.highcut WHERE ts.sch_student_id
> = ? ; SELECT DISTINCT dateTested, the_test_score, the_raw_points,
> grade_level_id, abbrev, assessment_id, score_method_id, display,
> school_name, session_name, session_id, stuLowCut, stuHighCut, stuProf,
> stuAbove, schLowCut, schHighCut, schProf, schAbove, disLowCut, disHighCut,
> disProf, disAbove, schoolScoreAvg, schoolRawAvg, distScoreAvg, distRawAvg,
> maxScore, minScore FROM ? ;
>
> [15:50] DEBUG PreparedStatement () - {pstm-100082} Parameters:
> [##112111861335110817, a.primary_prof_profile_id, a.primary_prof_profile_id,
> a.primary_prof_profile_id, 10817, ##112111861335110817]
>
> [15:50] DEBUG PreparedStatement () - {pstm-100082} Types: [java.lang.String,
> java.lang.String, java.lang.String, java.lang.String, java.lang.Integer,
> java.lang.String]
>
> ****************************************************************************
> This email may contain confidential material.
> If you were not an intended recipient,
> Please notify the sender and delete all copies.
> We may monitor email to and from our network.
> ****************************************************************************
>
--
Brice Ruth
Software Engineer, Madison WI