Discussions
Categories
- 385.5K All Categories
- 4.9K Data
- 2.5K Big Data Appliance
- 2.4K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
Prepared Statement for Create Table

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.
Best Answer
-
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?
Answers
-
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?
-
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.