Oracle Database Discussions

This means that I want to parameterize the table name.

That means you are doing SEVERAL things wrong.

1. You don't 'parameterize' table names in queries. The table name can NOT be a bind variable

2. You should NOT be using a parameter for the table name anyway. You should NOT be creating multiple identical tables with different names. That usually means you have the wrong data model.

This appears to be a continuation of your previous issue

How to check empty Excel cell using while loop?

Until you tell people ALL of the requirements about the real problem you are trying to solve it is hard to give you help.

Hi,

Thanks for your suggestions. I have solved this prob:

 
2. You should NOT be using a parameter for the table name anyway. You should NOT be creating multiple identical tables with different names. That usually means you have the wrong data model.

Its easy. I am keeping each students' record in a separate table. And the Head can check it with a simple 'Select' query but now there is table name alteration he has to remember. I mean its simple to say "Select * from 2015_SE_21" as compared to using a where clause.

<On the other hand, why would you need to create the tables on the fly?>

I have to read excel sheet for roll numbers & then create tables for each rollNo & each table will contain attendance data for each specific student.

My code is:

import java.io.*;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import javax.swing.*;

import java.sql.*;

/**

*

* @author HP

*/

public class ReadExcelSheetMain6 {

private static final String JDBC_DRIVER = "com.mysql.jdbc.Driver"; 

private static final String DB_URL = "jdbc:mysql://localhost/attendance";

   //  Database credentials

private static final String USER = "root";

private static final String PASS = "z";

private Connection conn = null;

private Statement stmt = null;

PreparedStatement pStmt = null;

    /**

     * @param args the command line arguments

     */

    public void readExcel() throws BiffException, IOException {

String FilePath = "D:\\Marks-2A-SE-OOP-2015.xls";

FileInputStream fs = new FileInputStream(FilePath);

Workbook wb = Workbook.getWorkbook(fs);

// TO get the access to the sheet

Sheet sh = wb.getSheet("Sheet1");

// To get the number of rows present in sheet

int totalNoOfRows = sh.getRows();

                JOptionPane.showMessageDialog(null, "KKK totalNoOfRows = " + totalNoOfRows);

// To get the number of columns present in sheet

int totalNoOfCols = sh.getColumns();

                JOptionPane.showMessageDialog(null, "GGG totalNoOfCols = " + totalNoOfCols);

                int row=8;

                int col=1;

                String rollNo=null;

                String newRollNo = null;

                //for ( row = 8; row < totalNoOfRows; row++) {

                   rollNo = sh.getCell(col, row).getContents();

                   newRollNo = rollNo.replace('-','_');

                   boolean isEmpty = rollNo == null || rollNo.trim().length() == 0;

                   //if (isEmpty)

                     // break;

                    try{

                          System.out.println("Comes Here1");

                           //STEP 2: Register JDBC driver

                           Class.forName("com.mysql.jdbc.Driver");

                           //STEP 3: Open a connection

                           System.out.println("Connecting to database...");

                           conn = DriverManager.getConnection(DB_URL,USER,PASS);

                           //STEP 4: Execute a query

                           System.out.println("Creating statement Testing 999 ..." + rollNo);

                           String sql;

                           sql = "Create table if not exists " + newRollNo+ " (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";

                           stmt = conn.createStatement();

                           //sql ="Create table if not exists Test(id Integer)";

                            stmt.executeUpdate(sql);

                            System.out.println("Comes Here Testing 110");

                        }

                        catch(SQLException sqle) {

System.out.println("Error1 after modifying");

sqle.printStackTrace();

                        }

                        catch(ClassNotFoundException cnfe) {

System.out.println("Error2");

                        }

                        catch (Exception e) {

System.out.println("Error3");

                        }

                        finally{

                           try{

                                 if(conn!= null)

                                 conn.close();

                               }

                        catch(SQLException sqle) {

System.out.println("Error4");

                        }

                       }//finally

//                     }//for

                   //System.out.println("rollNo" + rollNo);

                //System.out.print(sh.getCell(col, row).getContents() + "\t");

/*for (int row = 0; row < totalNoOfRows; row++) {

for (int col = 0; col < totalNoOfCols; col++) {

System.out.print(sh.getCell(col, row).getContents() + "\t");

}

System.out.println();

}*/

    }

    public static void main(String[] args)throws BiffException, IOException {

        // TODO code application logic here

        ReadExcelSheetMain6 obj = new ReadExcelSheetMain6();

        obj.readExcel();

    }

}

I check the documentation under prepared statement but i cant find that prepared statement can not be allowed on create table query. Even stackoverflows provide its solutions. 

Problem Solved.Thanks.

Zulfi.

I check the documentation under prepared statement but i cant find that prepared statement can not be allowed on create table query.

That is NOT what I said - reread my reply.

I said that table names can NOT be used for bind variables. You need to construct a query that has the table name already in it - you can't use a 'setXXX' statement to alter the table name.

I am keeping each students' record in a separate table.

That is what I meant when I said you are likely using the wrong data model.

You should keep like data in the same table. One table to hold rows for ALL students.

By using that correct data model your problem goes away - there is only ONE table name to remember and only one table to query.

Do a google search for "student attendance database" images. Should give you some idea.

Try to produce some queries with your design on paper first.

Try not to leave commented out code blocks in the methods. Keep it clean. It helps.