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 Ilya Boyandin <Il...@fh-joanneum.at> on 2007/03/30 12:43:34 UTC
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
Re: Configurable static parameters in SQL
Posted by Jeff Butler <je...@gmail.com>.
Ahh SQL Server...you vex me.
Starting with SQL Server 2005, Microsoft has finally started using the
proper terminology here (schema is the proper term, SQL Server called it
"database owner" previously).
Most databases allow you to set a default schema in the connection URL, but
I'm not sure if SQL Server does or not. This is new territory for SQL
Server. However, I know that you could create a database user and specify a
default schema that way.
Jeff Butler
On 3/30/07, Ilya Boyandin <Il...@fh-joanneum.at> wrote:
>
> Really? And how can I set it in the URL?
>
> I think, it's not the schema, but something different. It's "database
> owner", something specific to MS SQL, I think.
>
> Ilya
>
> Jeff Butler wrote:
> > If you are only needing to set the schema, then you could set a
> > default schema on the JDBC connection URL and then avoid the use of
> > schemas completely within your SQL.
> >
> > Jeff Butler
> >
> >
>
>
Re: Configurable static parameters in SQL
Posted by Ilya Boyandin <Il...@fh-joanneum.at>.
Really? And how can I set it in the URL?
I think, it's not the schema, but something different. It's "database
owner", something specific to MS SQL, I think.
Ilya
Jeff Butler wrote:
> If you are only needing to set the schema, then you could set a
> default schema on the JDBC connection URL and then avoid the use of
> schemas completely within your SQL.
>
> Jeff Butler
>
>
Re: Configurable static parameters in SQL
Posted by Jeff Butler <je...@gmail.com>.
If you are only needing to set the schema, then you could set a default
schema on the JDBC connection URL and then avoid the use of schemas
completely within your SQL.
Jeff Butler
On 3/30/07, Ilya Boyandin <Il...@fh-joanneum.at> wrote:
>
> Hi Seth,
>
> thanks for the quick answer!
>
> Yes, it works this way, but it would be much better if I could get rid
> of passing the dbOwner parameter and specify it just once in a config
> file, because it's static and I need it for every single query.
>
> The need of the extra parameter makes using objects of the returning
> type as query parameters (by setting their properties corresponding to
> the primary key), which is very handy, hardly possible.
>
> Regards,
> Ilya
>
> Seth Helstrip wrote:
> > Hey Ilya,
> > You can insert dynamic portions into your sql with
> > iBatis using the $ syntax. To take your example,
> > below you'd use something like...
> >
> >
> > <sqlMap namespace="Students">
> > <select id="isStudent"
> > resultClass="java.lang.Integer"
> > parameterClass="String">
> > SELECT count(*) FROM $dbOwner$.students
> > WHERE id = #value#
> > </select> </sqlMap>
> >
> > And then supply the String parameter as an object to
> > the query from your Java sqlMap. E.g.
> >
> > Boolean isTrue = (Boolean) queryForObject("isStudent",
> > myParameterString);
> >
> > Hope this helps.
> > Seth
> >
> > --- Ilya Boyandin <Il...@fh-joanneum.at>
> > wrote:
> >
> >
> >> 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?
> >>
> >>
> >
>
>
Re: Configurable static parameters in SQL
Posted by Ilya Boyandin <Il...@fh-joanneum.at>.
Hi Seth,
thanks for the quick answer!
Yes, it works this way, but it would be much better if I could get rid
of passing the dbOwner parameter and specify it just once in a config
file, because it's static and I need it for every single query.
The need of the extra parameter makes using objects of the returning
type as query parameters (by setting their properties corresponding to
the primary key), which is very handy, hardly possible.
Regards,
Ilya
Seth Helstrip wrote:
> Hey Ilya,
> You can insert dynamic portions into your sql with
> iBatis using the $ syntax. To take your example,
> below you'd use something like...
>
>
> <sqlMap namespace="Students">
> <select id="isStudent"
> resultClass="java.lang.Integer"
> parameterClass="String">
> SELECT count(*) FROM $dbOwner$.students
> WHERE id = #value#
> </select> </sqlMap>
>
> And then supply the String parameter as an object to
> the query from your Java sqlMap. E.g.
>
> Boolean isTrue = (Boolean) queryForObject("isStudent",
> myParameterString);
>
> Hope this helps.
> Seth
>
> --- Ilya Boyandin <Il...@fh-joanneum.at>
> wrote:
>
>
>> 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?
>>
>>
>
Re: Configurable static parameters in SQL
Posted by Seth Helstrip <se...@yahoo.com>.
Hey Ilya,
You can insert dynamic portions into your sql with
iBatis using the $ syntax. To take your example,
below you'd use something like...
<sqlMap namespace="Students">
<select id="isStudent"
resultClass="java.lang.Integer"
parameterClass="String">
SELECT count(*) FROM $dbOwner$.students
WHERE id = #value#
</select> </sqlMap>
And then supply the String parameter as an object to
the query from your Java sqlMap. E.g.
Boolean isTrue = (Boolean) queryForObject("isStudent",
myParameterString);
Hope this helps.
Seth
--- Ilya Boyandin <Il...@fh-joanneum.at>
wrote:
> 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
>
____________________________________________________________________________________
Bored stiff? Loosen up...
Download and play hundreds of games for free on Yahoo! Games.
http://games.yahoo.com/games/front
Re: Configurable static parameters in SQL
Posted by Larry Meadors <lm...@apache.org>.
That's for those .net nerds. ;-)
Larry
On 3/30/07, Ilya Boyandin <Il...@fh-joanneum.at> wrote:
> Hi Larry,
>
> Thanks a lot for the hint, now it works!
>
> BTW, I found the description of the properties file format on this page:
> http://opensource.atlassian.com/confluence/oss/display/IBATIS/What+should+my+properties.config+file+look+like
> Is it about some other kind of a properties file?
>
> Ilya
>
> Larry Meadors wrote:
> > 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
> >
>
>
Re: Configurable static parameters in SQL
Posted by Ilya Boyandin <Il...@fh-joanneum.at>.
Hi Larry,
Thanks a lot for the hint, now it works!
BTW, I found the description of the properties file format on this page:
http://opensource.atlassian.com/confluence/oss/display/IBATIS/What+should+my+properties.config+file+look+like
Is it about some other kind of a properties file?
Ilya
Larry Meadors wrote:
> 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
>
Re: Configurable static parameters in SQL
Posted by Larry Meadors <lm...@apache.org>.
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 <Il...@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
> >
> >
> >
>
Re: Configurable static parameters in SQL
Posted by Ilya Boyandin <Il...@fh-joanneum.at>.
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
>
>
>
RE: Configurable static parameters in SQL
Posted by Meindert <me...@pastelebusiness.com>.
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