Filimon Ataklti Filimon Ataklti - 2 months ago 7
MySQL Question

while working with jdbc in mysql database using type-4 driver. I got MySQLSyntaxErrorException

when i type the command manually to mysql, it works but i don't know why it gives exception while running it from my program. The complete program is down below

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.util.Scanner;

public class Create {

public static void main(String[] args) {
try {
Scanner s = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/fila", "root", "filimon");
PreparedStatement ps = con.prepareStatement("create table ?(? int(4)primary key,? varchar(20),? float(4,2))");
System.out.print("enter the table name to create: ");
String name = s.nextLine();
ps.setString(1, name);
System.out.print("enter the primarykey column name: ");
String priname = s.nextLine();
ps.setString(2, priname);
System.out.print("enter the second column name: ");
String secname = s.nextLine();
ps.setString(3, secname);
System.out.print("enter the third column name: ");
String thrname = s.nextLine();
ps.setString(4, thrname);
int i = ps.executeUpdate();
System.out.println(i + " table created");
ps.close();
con.close();
s.close();
} catch (Exception e) {
System.err.println(e);
}
}
}


Thanks alot for your time

Answer

Per to the reason that @Jens gave, to create a table with the PreparedStatement class, the best way is to call its preparedStatement method as in:

Scanner s = new Scanner(System.in);
Class.forName("com.mysql.jdbc.Driver");
Connection con = DriverManager.getConnection("jdbc:mysql://localhost/fila", "root", "filimon");

System.out.print("enter the table name to create:  ");
String name = s.nextLine();

System.out.print("enter the primarykey column name: ");
String priname = s.nextLine();

System.out.print("enter the second column name:  ");
String secname = s.nextLine();

System.out.print("enter the third column name:  ");
String thrname = s.nextLine();


String sqlStatement = "create table "+name+"( "
        +priname+" int(4)primary key, "
        +secname+" varchar(20),"
        + thrname +" float(4,2))";

System.out.println(sqlStatement);
PreparedStatement ps = con.prepareStatement(sqlStatement);

ps.executeUpdate();

System.out.println("\""+name +"\" Table created");

ps.close();
con.close();
s.close();

You can check out for this reference