MySQL Java - while (temp.next()) wont iterate

Author

while (temp.next()) wont iterate

Darko Kalevski

2005-11-10, 11:23 am

booo! :)

hey folks, here it is a snippet of a code i'm writing and i ran onto
very strange problem...well at least to me...please have a look at the code

temp=null;
queryNasokiID="SELECT naspred_id FROM nasoki_predmeti WHERE semestar=1
AND institut=1 AND nasoka='Primeneta'";
temp=y. vratiRezultat(queryN
asokiID);

try{
while (temp.next()){
String queryZapisiNasoki="";
queryZapisiNasoki="INSERT INTO upisi_predmeti (sk_god, student,
semestar, naspred) VALUES (2005, 6266, 1, "+temp.getInt(1)+")";
y. dodadiRedica(queryZa
pisiNasoki);
}
}catch(Exception q){
q.printStackTrace();
}

well the while structue wont iterate, it just runs once and thats it.
For example, with the values in the code it has to iterate 4 times,
cause temp gives 4 rows with single column. which means i want to INSERT
for rows in a different table subsequently. Is it something i'm doing
wrong or??
I've tested the queries in MySQL Query Browser and they all work well.
But once in a bean file...the while condition wont iterate...? And In
the MySQL query browser I can do the INSERT query zillion times if I
want to as long as I have different value for 'naspred' column. Like this:
INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
(2005, 6266, 1, 1)
INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
(2005, 6266, 1, 5)
INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
(2005, 6266, 1, 9)

I can do them even within the bean place line after line, but once I try
to generate generic INSERT statement and place it within the while
condition...well it FAILS!

i've tried god knows how many combinations, initializing and etc...but
it wont work...

here is dodadiRedici method which is a method part of another class and
y is an object of that class:

public void dodadiRedica(String sql){
try{
statement.executeUpdate(sql);
} catch (SQLException e) {
System.err.println(sql);
}
}

thanks for looking!

darko
--
12:50, press return.

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Rhino

2005-11-10, 1:23 pm

Your example is somewhat confusing in some respects so I may say something
that wouldn't make sense if I knew your language....

The first problem is that the example doesn't identify the type for variable
'temp'; is it a ResultSet?

Is the 'vratiRezultat()' method doing an 'executeQuery()'? I'm not sure what
the 'y' variable in your example is either; I'm guessing it's an instance of
a utility class you've created to do SQL for you....

Assuming that 'temp' is a ResultSet, are you absolutely certain that 4 rows
satisfy the query? From the behaviour you are getting, it seems much more
likely that only 1 row satisfies the query so the while loop only iterates
once. The ResultSet.next() method should always iterate once for each row of
the result set, no matter how many rows there are. If you are only getting
one iteration, it seems VERY likely that only one row is satisfying the
query. Please try the EXACT SAME query against the EXACT SAME table outside
of Java and verify that it is returning 4 rows. Is there any chance that
Java is pointing to a different version of the table, perhaps in a different
database or server, that has only 1 row that satisfies the query?

If you're absolutely positive that the query will return 4 rows what happens
when you run the program? Are you getting an error or does it only iterate
the while loop once without throwing any exception?

If your Insert was failing it should be throwing an exception, which your
code would catch. (By the way, I think you should be catching SQLException,
not Exception, in your while loop.).

Are you aware that executeUpdate() returns an int that says how many rows
were inserted? If you change 'dodadiRedica()' to return that int, then you
can either display that int to make sure it is 1 or you can verify that it
is 1 in your debugger. That would help verify that the Insert is succeeding.

Rhino

----- Original Message -----
From: "Darko Kalevski" <kdarko@unet.com.mk>
To: <java@lists.mysql.com>
Sent: Thursday, November 10, 2005 11:16 AM
Subject: while (temp.next()) wont iterate

> booo! :)
>
> hey folks, here it is a snippet of a code i'm writing and i ran onto
> very strange problem...well at least to me...please have a look at the
code
>
>
> temp=null;
> queryNasokiID="SELECT naspred_id FROM nasoki_predmeti WHERE semestar=1
> AND institut=1 AND nasoka='Primeneta'";
> temp=y. vratiRezultat(queryN
asokiID);
>
> try{
> while (temp.next()){
> String queryZapisiNasoki="";
> queryZapisiNasoki="INSERT INTO upisi_predmeti (sk_god, student,
> semestar, naspred) VALUES (2005, 6266, 1, "+temp.getInt(1)+")";
> y. dodadiRedica(queryZa
pisiNasoki);
> }
> }catch(Exception q){
> q.printStackTrace();
> }
>
> well the while structue wont iterate, it just runs once and thats it.
> For example, with the values in the code it has to iterate 4 times,
> cause temp gives 4 rows with single column. which means i want to INSERT
> for rows in a different table subsequently. Is it something i'm doing
> wrong or??
> I've tested the queries in MySQL Query Browser and they all work well.
> But once in a bean file...the while condition wont iterate...? And In
> the MySQL query browser I can do the INSERT query zillion times if I
> want to as long as I have different value for 'naspred' column. Like this:
> INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
> (2005, 6266, 1, 1)
> INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
> (2005, 6266, 1, 5)
> INSERT INTO upisi_predmeti (sk_god, student, semestar, naspred) VALUES
> (2005, 6266, 1, 9)
>
> I can do them even within the bean place line after line, but once I try
> to generate generic INSERT statement and place it within the while
> condition...well it FAILS!
>
> i've tried god knows how many combinations, initializing and etc...but
> it wont work...
>
>
> here is dodadiRedici method which is a method part of another class and
> y is an object of that class:
>
> public void dodadiRedica(String sql){
> try{
> statement.executeUpdate(sql);
> } catch (SQLException e) {
> System.err.println(sql);
> }
> }
>
>
> thanks for looking!
>
>
>
> darko
> --
> 12:50, press return.
>
> --
> MySQL Java Mailing List
> For list archives: http://lists.mysql.com/java
> To unsubscribe: http://lists.mysql.com/java? unsub=...ati
co.ca
>
>
> --
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.1.362 / Virus Database: 267.12.8/165 - Release Date: 09/11/2005
>
>

--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.12.8/165 - Release Date: 09/11/2005

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Darko Kalevski

2005-11-10, 1:23 pm

Hey all

The problem was fixed. To quote Dirk Hillbrecht:
"You are using the Statement "y" within the loop over the ResultSet
"temp" which is also based on "y". JDBC says that a Statement must not
be used as long as a ResultSet derived from the Statement is in use.
Solution: Either use a second Statement within the loop or put the
ResultSet entries into a intermediate storage and then loop over that
storage (i.e. you finally have two loops). "

So if I write:

temp=y. vratiRezultat(queryN
asokiID);
try{
while (temp.next()){
[...]
y2. dodadiRedica(queryZa
pisiNasoki);
}
}catch(Exception q){
q.printStackTrace();
}

That works. :)

Darko

--
MySQL Java Mailing List
For list archives: http://lists.mysql.com/java
To unsubscribe: http://lists.mysql.com/java?unsub=m...sie.nctu.edu.tw

Sponsored Links