Prepared Statement for Create Table

User_AYF65

Hi,

Hi,

I am trying to create a prepared statement for create table query. This means that I want to parameterize the table name. Some body please guide. I am using the following code:

import java.io.*;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import javax.swing.*;

import java.sql.*;

/**

*

* @author HP

*/

public class ReadExcelSheetMain4 {

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;

                

                

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

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

                   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 ? (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT)";

                           

                           pStmt = conn.prepareStatement(sql);

                           

                           

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

                            pStmt.setString(1, rollNo);

                            pStmt.executeUpdate();

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

                        }

                        catch(SQLException sqle) {

System.out.println("Error1");

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

        ReadExcelSheetMain4 obj = new ReadExcelSheetMain4();

        obj.readExcel();

    }

    

}

==

Comes Here1

Connecting to database...

Creating statement Testing 999 ...2015-SE-001

Error1

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2015-SE-001' (id INTEGER not null primary key auto_increment, name varchar(30),' at line 1

        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:936)

        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:2985)

        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1631)

        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:1723)

        at com.mysql.jdbc.Connection.execSQL(Connection.java:3283)

        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1332)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1604)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1519)

        at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1504)

        at ReadExcelSheetMain4.readExcel(ReadExcelSheetMain4.java:79)

        at ReadExcelSheetMain4.main(ReadExcelSheetMain4.java:122)

I have also applied the concatenation option but it is also giving me the same error:

I have tried concatenation option but I am getting same error.

import java.io.*;

import jxl.Sheet;

import jxl.Workbook;

import jxl.read.biff.BiffException;

import javax.swing.*;

import java.sql.*;

/**

*

* @author HP

*/

public class ReadExcelSheetMain5 {

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;

               

               

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

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

                   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" + rollNo+ " (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

        ReadExcelSheetMain5 obj = new ReadExcelSheetMain5();

        obj.readExcel();

    }

   

}

Some body please guide me how to solve this problem.

Zulfi.

mNem

Your table name contains invalid character.

Check for the permitted characters in the table name.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Invoke the below two statements on http://rextester.com/l/mysql_online_compiler  and check the results.

Create table if not exists 2015-SE-001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

Create table if not exists 2015_SE_001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

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

mNem

Your table name contains invalid character.

Check for the permitted characters in the table name.

https://dev.mysql.com/doc/refman/5.7/en/identifiers.html

Invoke the below two statements on http://rextester.com/l/mysql_online_compiler  and check the results.

Create table if not exists 2015-SE-001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

Create table if not exists 2015_SE_001 (id INTEGER not null primary key auto_increment, name varchar(30),  totalTheoryClasses INTEGER, totalLabs INTEGER, WeekNo INTEGER, DaysPresent INTEGER, DaysAbset INTEGER, Percentage FLOAT);

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

Unknown

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.

User_AYF65

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.

Unknown

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.

mNem

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.

mNem

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