while loop not executing

[sandeep43]

Hi,

 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

[Amit20]

Are you getting any errror?

[pbrockway2]

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.

[DarrylBurke]

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

db

[sandeep43]

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

[sandeep43]

Hi DarrylBurke,

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

Regards
Sandeep

[yellowledbet]

Are you sure your query is returning results?

[sandeep43]

Hi yellowledbet,

it is not returning results

[pbrockway2]

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:

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).

[Tolls]

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.