Create and handle several DBs in SQL Server (2017) from Java

Question

I'm developing a desktop app to organize different events, thus creating a DB for each event. So far, I've managed to create a DB with whatever name the user wants, using a simple GUI.

However, I can't create tables nor columns for said database, even though it's exactly the same syntax I use in SQL Server Manager.

My code so far:

public static void creDB(String db_name, String table_name){

        Connection conn = null;
        Statement stmt =  null;
        ResultSet rs = null;

        try {
            Class.forName("com.microsoft.sqlserver.jdbc.SQLServerDriver");
            conn = DriverManager.getConnection(connectionUrl);

            String SQL = "CREATE DATABASE " + db_name;
            stmt = conn.createStatement();
            int result = stmt.executeUpdate(SQL);

            String SQL3 = "USE " + db_name;
            boolean ree = stmt.execute(SQL3);
            String SQL4 = "GO";
            boolean rr = stmt.execute(SQL4);

            if (result == 0){
                System.out.println("Se insertó :D!");
                String SQL2 = "CREATE TABLE Pepe(Name_emp INT NOT NULL PRIMARY KEY)";
                int res = stmt.executeUpdate(SQL2);
                if (res == 0)
                    System.out.println("GRACIAS DIOS");
            }else
                System.out.println("Raios shico");


        }catch (Exception e) {e.printStackTrace();}
        finally {
            if (rs != null) try {rs.close();} catch (Exception e) {e.printStackTrace();}
            if (stmt != null) try {stmt.close();} catch (Exception e) {e.printStackTrace();}
            if (conn != null) try {conn.close();} catch (Exception e) {e.printStackTrace();}
        }
    }

The error I get is when I try to actually use the DB, using the use [DB name] go; I tried already using that same syntax in one single SQL statement, however it didn't work, so I tried doing it separately and got this error:

com.microsoft.sqlserver.jdbc.SQLServerException: Could not find stored procedure 'GO'.

I know the code above looks like a mess, and it is, but it's just for testing purposes since I'm new to doing DB-related projects with Java; I mixed-matched some of the concepts of this site, which were successful up until the creation of the tables.

I know there's a better way of managing several databases, but as I said, I'm just starting so any advice would be greatly appreciated.

GO is only for SQL server Management Studio. When using in Java the command is executed by stmt.executeUpdate(sqlcommand). The equivalent of the 'use' in JDBC is within the connection URL; jdbc:sqlserver://hostname:1433;databaseName=DATABASE. — Fredy Fischer, Feb 08 '18 at 08:17
Sir, I can't thank you enough for that simple explanation that I couldn't find anywhere else. — Rolin Azmitia, Feb 08 '18 at 10:24

Mark Rotteveel · Accepted Answer · 2018-02-08T08:36:12.160

You should not use statements like USE <dbname> when using JDBC, it may lead to unexpected behavior because parts of the driver may still use metadata for the original connected database. You should either use setCatalog on the current connection to switch databases or create an entirely new connection to the new database.

In short, after creating the database, you should use:

conn.setCatalog(db_name);

That's it.

Also, go is not part of the SQL Server syntax, it is only used by tools like the Management Studio, see What is the use of GO in SQL Server Management Studio & Transact SQL? The equivalent in JDBC is to simply execute the statement.

Create and handle several DBs in SQL Server (2017) from Java

1 Answers1