Temporary Table + Variable + Java
Posted by: Eu Mesmo
Date: June 19, 2012 01:20AM

Hello

I'm having some problems to get a variable from a temporary table using Java. If I execute at the mysql console everything works fine, and I get the right value of "contador"

mysql> SELECT contador FROM ( (SELECT IFNULL((@conti:=@conti+1),(@conti:=1)) AS contador FROM m_142920))T LIMIT 10;
+----------+
| contador |
+----------+
| 1 |
| 2 |
| 3 |
| 4 |
| 5 |
| 6 |
| 7 |
| 8 |
| 9 |
| 10 |
+----------+

althought if I use from the ResultSet in java. Variable contador for the same line is always "1".

ResultSet rebusca;
...
while (rebusca.next()) {
...

System.out.println(rebusca.getString(1));

-----------------
The mysql line above I got from mysql.log so is exactly the same executed from java.

It seens like it cannot initiate and keep the variable in the memory, when using java.
If I initiate the variable @conti in other java line, its work.

I also tried to use:
CASE WHEN @conti IS NULL THEN @conti:=1 ELSE THEN @conti:=@conti+1 END

But still same result, from console it works but it doesnt from java.

The only workaround I have found that make it works is to use JOIN
then:
SELECT contador FROM ((SELECT (@conti:=@conti+1) AS contador FROM m_142920 JOIN (SELECT @conti:=1)tt ))T LIMIT 10;


Does anyone have some advice?

thanks a lot

Thiago
a00s.com



Edited 1 time(s). Last edit at 06/19/2012 02:38AM by Eu Mesmo.

Options: ReplyQuote


Subject
Written By
Posted
Temporary Table + Variable + Java
June 19, 2012 01:20AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.