ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "Larry Meadors" <lmead...@apache.org>
Subject Re: Configurable static parameters in SQL
Date Fri, 30 Mar 2007 15:10:53 GMT
What's this?

<?xml version="1.0" encoding="utf-8" ?>
<settings>
   <add key="dbOwner" value="ep63" />
</settings>

That should just read like this:

dbOwner=ep63

Larry


On 3/30/07, Ilya Boyandin <Ilya.Boyandin@fh-joanneum.at> wrote:
> Hello Meindert,
>
> in the log I see the following:
>
> [DEBUG,PreparedStatement,http-8080-2] {pstm-100001} Executing
> Statement:    SELECT      LOWER(SUBSTRING(personen.per_edv_user,
> PATINDEX('[\\/]', personen.per_edv_user), LEN(personen.per_edv_user)))
> as username,      personen.oid,       personen.per_vorname as
> firstname,       personen.per_nachname as lastname      FROM
> dbOwner.personen AS personen     WHERE      personen.per_edv_user LIKE ?
> [DEBUG,PreparedStatement,http-8080-2] {pstm-100001} Parameters:
> [technikum[\/]boyan]
> [DEBUG,PreparedStatement,http-8080-2] {pstm-100001} Types:
> [java.lang.String]
> [WARN,LoggerListener,http-8080-2] Authentication event
> AuthenticationFailureServiceExceptionEvent: boyan; details:
> org.acegisecurity.ui.WebAuthenticationDetails@fffdaa08: RemoteIpAddress:
> 127.0.0.1; SessionId: 8E65664FB3F76EDFEB74273D8C6DD66A; exception:
> SqlMapClient operation; bad SQL grammar []; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in org/fhj/joanna/dao/ibatis/maps/Persons.xml.
> --- The error occurred while applying a parameter map.
> --- Check the loadPerson-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: Invalid object name
> 'dbOwner.personen'.; nested exception is
> org.springframework.jdbc.BadSqlGrammarException: SqlMapClient operation;
> bad SQL grammar []; nested exception is
> com.ibatis.common.jdbc.exception.NestedSQLException:
> --- The error occurred in org/fhj/joanna/dao/ibatis/maps/Persons.xml.
> --- The error occurred while applying a parameter map.
> --- Check the loadPerson-InlineParameterMap.
> --- Check the statement (query failed).
> --- Cause: java.sql.SQLException: Invalid object name 'dbOwner.personen'.
>
>
> And the query itself:
>
>     <select id="loadPerson" resultMap="personResult">
>         SELECT
>              LOWER(SUBSTRING(personen.per_edv_user,
>                  PATINDEX('[\\/]', personen.per_edv_user),
> LEN(personen.per_edv_user))) as username,
>              personen.oid,
>              personen.per_vorname as firstname,
>              personen.per_nachname as lastname
>          FROM
>                ${dbOwner}.personen AS personen
>          WHERE
>              personen.per_edv_user LIKE #value#
>     </select>
>
>
> So it simply removes ${} and leaves "dbOwner" unchanged.
>
> My sqlMap.properties looks like:
>
> <?xml version="1.0" encoding="utf-8" ?>
> <settings>
>     <add key="dbOwner" value="ep63" />
> </settings>
>
>
> sql-map-config.xml:
>
> <sqlMapConfig>
>
>     <properties resource="sqlMap.properties" />
>     <sqlMap resource="org/fhj/joanna/dao/ibatis/maps/Instructors.xml"/>
>     <sqlMap resource="org/fhj/joanna/dao/ibatis/maps/Students.xml"/>
>     <sqlMap resource="org/fhj/joanna/dao/ibatis/maps/Persons.xml"/>
>
> </sqlMapConfig>
>
>
> Bye
> Ilya
>
> Meindert wrote:
> > Not to sure why it doesn't work in your code, I have the following in my
> > database.config
> >
> > #nowfunction=Now()
> > And
> > nowfunction=getDate()
> >
> > Now() to be used if database is MySql and getDate() if database is Sql
> > Server
> >
> > And have no issues using ${nowfunction} in my queries
> > UPDATE Profile SET
> >       ModificationDate = ${nowfunction}
> >     WHERE Id = #id#
> >
> > What statement do you see in your log?
> >
> > I also use dbtype=mssql in my properties and fetch database specific queries
> > in sql-map-config.xml with;
> > <sqlMap resource="com/persistence/sqlmapdao/sql/${dbtype}_queries.xml"/>
> >
> >
> >
> > -----Original Message-----
> > From: Ilya Boyandin [mailto:Ilya.Boyandin@fh-joanneum.at]
> > Sent: 30 March 2007 12:44 PM
> > To: user-java@ibatis.apache.org
> > Subject: Configurable static parameters in SQL
> >
> > Hello,
> >
> > I would like to use static parameters that I can set in a config file
> > directly in SQL queries. I need it especially for the MS SQL database
> > owner that I want neither to hard code in SQL nor to pass as a parameter
> > from Java code each time I execute queiries. I tried to define a
> > "dbOwner" setting in properties.config, but it didn't work with the
> > following code:
> >
> > <sqlMap namespace="Students">
> >    <select id="isStudent" resultClass="java.lang.Integer">
> >        SELECT count(*)  FROM ${dbOwner}.students WHERE id = #value#
> >    </select>  </sqlMap>
> >
> > Is it possible in any other way with iBatis?
> >
> > Thanks a lot in advance
> > Ilya
> >
> >
> >
>

Mime
View raw message