while loop not executing

Hi,

Code:

---

java.sql.Statement sta = conn.createStatement();
            ResultSet rs2 = sta.executeQuery("SELECT OBJECT_NAME FROM "+GetSrcString+"."+GetSchema+".OPTIM_NAA_CONTROLS");
           
           
            while (rs2.next()){
             
            RecName=rs2.getString("OBJECT_NAME");
           
       
          String sqlText = "CREATE TABLE "+GetArcSchema+"."+RecName+" (";
            java.sql.Statement stat = conn.createStatement();

            ResultSet rs= stat.executeQuery("SELECT  COLUMN_NAME,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM    "+GetSrcString+"."+"INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"+RecName+"' AND      TABLE_SCHEMA ='"+GetArcSchema+"' ORDER BY ORDINAL_POSITION ASC");         
         
          while(rs.next()) {
               
              String ColumnName =rs.getString("COLUMN_NAME");
              System.out.println(ColumnName);
              String DataType =rs.getString("DATA_TYPE");
              String ColumnDefault =rs.getString("COLUMN_DEFAULT");
              String CharaterMaximumLength = rs.getString("CHARACTER_MAXIMUM_LENGTH");
             
                  sqlText = sqlText + ColumnName+" " + DataType + " "+ColumnDefault+",";
                System.out.println(sqlText);
               
             
            }
           
            sqlText = sqlText.substring(0, sqlText.length()-1);
            sqlText = sqlText + ")";
            System.out.println(sqlText);
          boolean  rs1  = sta.execute(sqlText);
           
            }
           
            conn.close();

---

As shown in the code i have two while loops, first while is executing where as my inner while loop not executing at all, any reasons why or else how to handle them?

Regards
Sandeep

Are you getting any errror?

As written, the second while loop is *not* an inner loop of the first as you suggest. If this loop does not execute then presumably the result set in question is empty.

Learn to format code in a readable fashion.
Code Conventions for the Java(TM) Programming Language: Contents

db

Hi pbrockway2,

Sorry, the first while brace had to close after second while.

Amit i am not getting any error but my second while loop is not executing

Regards
Sandeep

Hi DarrylBurke,

i think now it is readable and can you help me out as why my second while is not executing ?

Regards
Sandeep

Are you sure your query is returning results?

Hi yellowledbet,

it is not returning results

Quote:

---

Originally Posted by sandeep43

it is not returning results

---

Do you see why, if the query does not return any results, the second for loop will not do anything?

What you need to do is execute a query that does return some results.

You could try seeing what the query is:

Code:

---

String queryStr = "SELECT  COLUMN_NAME,COLUMN_DEFAULT,CHARACTER_MAXIMUM_LENGTH,DATA_TYPE FROM    "
        +GetSrcString+"."+"INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = '"
        +RecName+"' AND      TABLE_SCHEMA ='"+GetArcSchema+"' ORDER BY ORDINAL_POSITION ASC");
[color=green]System. out.println("About to execute query: " + queryStr);[/color]
ResultSet rs= stat.executeQuery(queryStr);         
while(rs.next()) {

---

You can then check that you are making the query you think you are. And you can check that query with your database using some other interface (command line etc).

Can I suggest that the second Statement is placed outside of the first while loop and turned into a PreparedStatement. This is a perfect place for one. Decalre once and reuse each time round the first loop. That won't solve your query not returning anything, but it will improve your code a bit.