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 Gilles SCHLIENGER <gi...@cncc.fr> on 2009/06/11 15:42:53 UTC
Using a user variable in a request with MySQL
Hello,
I'm using iBATIS (2.3.4.726) integrated with Spring, MySQL 5.0 (iBATOR with Eclipse) and I love it.
I'm faced with a specific need where I have to use a MySQL user variable.
I need to execute the following (simplified) request from my java code:
SET @NUM=0;
SELECT (@NUM:=@NUM+1), JET_ID FROM JETON
WHERE JET_FK_ABO_ID = 3;
If I put this inside a <select> tag in my SqlMap, it complains
If I put this in 2 successive queries, the user variable might not be reset if it's not in the same connexion
Ideally, I would even like to have my variable use a different custom name everytime, just in case of concurrent calls.
Would anyone have any idea as to how to do that ?
Thanks a lot in advance!
Regards
Gilles
RE : RE : Using a user variable in a request with MySQL
Posted by Gilles SCHLIENGER <gi...@cncc.fr>.
It works great with iBATIS/MySQL.
Thanks a lot Larry and sorry for bothering you with something I could/should have found myself...
Regards
Gilles
________________________________________
De : Larry Meadors [larry.meadors@gmail.com]
Date d'envoi : jeudi 11 juin 2009 17:22
À : user-java@ibatis.apache.org
Objet : Re: RE : Using a user variable in a request with MySQL
I see, try this:
SELECT (@rownum:=@rownum+1), JET_ID
FROM JETON, (SELECT @rownum:=0) r
WHERE JET_FK_ABO_ID = 3;
Found it here: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/
It might work, I don't have a mysql install handy to try it on. :-/
Larry
Re: RE : Using a user variable in a request with MySQL
Posted by Larry Meadors <la...@gmail.com>.
I see, try this:
SELECT (@rownum:=@rownum+1), JET_ID
FROM JETON, (SELECT @rownum:=0) r
WHERE JET_FK_ABO_ID = 3;
Found it here: http://jimlife.wordpress.com/2008/09/09/displaying-row-number-rownum-in-mysql/
It might work, I don't have a mysql install handy to try it on. :-/
Larry
RE : Using a user variable in a request with MySQL
Posted by Gilles SCHLIENGER <gi...@cncc.fr>.
Thanks Larry,
As far as I know, this is the standard workaround in MySQL to get the ROW_NUM of the resultset.
For example, this would return:
1,190
2,560
3,989
I actually need this to retrieve that number in a more complex query with joins
Thanks
Gilles
________________________________________
De : Larry Meadors [larry.meadors@gmail.com]
Date d'envoi : jeudi 11 juin 2009 15:58
À : user-java@ibatis.apache.org
Objet : Re: Using a user variable in a request with MySQL
On Thu, Jun 11, 2009 at 7:42 AM, Gilles
SCHLIENGER<gi...@cncc.fr> wrote:
>
> SET @NUM=0;
> SELECT (@NUM:=@NUM+1), JET_ID FROM JETON
> WHERE JET_FK_ABO_ID = 3;
>
Sorry, I'm not really up on mysql - what is this supposed to do?
Larry
Re: Using a user variable in a request with MySQL
Posted by Larry Meadors <la...@gmail.com>.
On Thu, Jun 11, 2009 at 7:42 AM, Gilles
SCHLIENGER<gi...@cncc.fr> wrote:
>
> SET @NUM=0;
> SELECT (@NUM:=@NUM+1), JET_ID FROM JETON
> WHERE JET_FK_ABO_ID = 3;
>
Sorry, I'm not really up on mysql - what is this supposed to do?
Larry