CREATE DATABASE query using java jdbc and prepared statement returns syntax error [duplicate]

Question

I am trying to get Java to create a database using JDBC but I get a syntax error, despite the query being correct. If I write the name of a database into the code explicitly, for example, it works fine. Here's my code:

package mysql_manipulator;

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

public class Create {
    private static final String 
            driver = "com.mysql.jdbc.Driver",
            url = "jdbc:mysql://localhost",
            username = "dylan",
            password = "******";
    private String databaseName;

    public Create (){
        this.databaseName = null;
    }

    public String getDatabaseName() {
        return databaseName;
    }

    public void setDatabaseName(String databaseName) {
        this.databaseName = databaseName;
    }

    public void createDatabase(String databaseName){
        try {
            this.setDatabaseName(databaseName);        
            Class.forName(driver);
            Connection con = DriverManager.getConnection(url,username, 
                password);
            String query = "CREATE DATABASE ?";
            PreparedStatement preparedStmt = con.prepareStatement(query);
            preparedStmt.setString (1,this.getDatabaseName());
            preparedStmt.execute();
            preparedStmt.close();
            con.close();        
        }
        catch (Exception anException){
            System.out.println("Error: " + anException);
        }
    }
}

My Main Class

package mysql_manipulator;

public class Mysql_manipulator {
    public static void main(String[] args) {
        Create myObj = new Create();
        myObj.createDatabase("whatevs");        
    }    
}

The error

Error: com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You  have an error in
your SQL syntax; check the manual that corresponds to your  MariaDB server version 
for the right syntax to use near ''whatevs'' at line 1

Huge thanks to anyone who knows why this isn't working - I've searched all over the web to no avail.

You cannot have the database name as a bind variable -- you have to embed it in the query literal: String query = "CREATE DATABASE " + getDatabaseName(); You can also use a plain Statement for running this SQL. — Mick Mnemonic
– Mick Mnemonic, Commented Aug 1, 2015 at 15:04

Mick Mnemonic · Accepted Answer · 2015-08-01 15:07:13Z

7

You cannot have the database name as a bind variable -- you have to embed it in the query literal. Change your query into this instead:

String query = "CREATE DATABASE " + getDatabaseName();

You can also use a plain Statement (instead of PreparedStatement) for running this SQL.

answered Aug 1, 2015 at 15:07

Mick Mnemonic

7,9562 gold badges29 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dylan White Over a year ago

Cheers Mick - don't know why I didn't work this out myself but you're totally correct.

itwasntme Over a year ago

could someone explain why we can't use PreparedStatement to create database? Is there any particular reason to disallow it?

Mick Mnemonic Over a year ago

@itwasntme, prepared statements are meant to be used for SQL operations that are frequently executed during the application's lifetime. Creating a database, or any DDL (data definition language) operations should only be executed during installation or upgrade, so there is not much use for "preparing" the statement for later use. Additionally, these operations shouldn't normally take parameters from user input, so protection from SQL injection isn't required, either.

Community · Accepted Answer · 2017-05-23 12:08:32Z

0

Conn = DriverManager.getConnection 
("jdbc:mysql://localhost/?user=root&password=rootpassword"); 
Statement s=Conn.createStatement(); 

int Result=s.executeUpdate("CREATE DATABASE databasename");

see

http://forums.mysql.com/read.php?39,99321,102211#msg-102211

Create MySQL database from Java

edited May 23, 2017 at 12:08

CommunityBot

11 silver badge

answered Aug 1, 2015 at 15:11

M Sach

34.6k78 gold badges232 silver badges317 bronze badges

Collectives™ on Stack Overflow

CREATE DATABASE query using java jdbc and prepared statement returns syntax error [duplicate]

2 Answers 2

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Linked

Related