ibatis-user-java mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Ilya Boyandin <Ilya.Boyan...@fh-joanneum.at>
Subject Re: Configurable static parameters in SQL
Date Fri, 30 Mar 2007 14:30:06 GMT
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