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.