Is it possible to create a database in groovy when connected to a MySQL? If so, how?

Question

It seems that an existing database is required to connect to a database server in groovy. However, what if I want to create a new database? Is it possible to create a database in groovy when connected to a MySQL? If so, how? Thank you in advance.

score 5 · Answer 1 · answered Jan 27 '14 at 14:49

One additional "gotcha" that I think is worth mentioning here is that the Groovy Sql class attempts to protect against SQL injection in the GString input to the execute methods, so, if you're trying to create MySQL databases dynamically using interpolation of the GString, like this...

sql.execute("CREATE DATABASE ${someName}")

it will fail with a MySQLSyntaxErrorException. I think this is because it will create single quotes around the interpolated variable (i.e. CREATE DATBASE 'foo'), which is invalid syntax. To fix it, just resolve the GString before passing it to execute(), like this...

sql.execute("CREATE DATABASE " + "${someName}")

Another option is use Sql.expand (http://docs.groovy-lang.org/latest/html/api/groovy/sql/Sql.html#expand-java.lang.Object-). Seen here: https://stackoverflow.com/a/5399518/1096370 — Boris Lopez, Feb 13 '18 at 04:12

score 4 · Accepted Answer · answered Sep 07 '13 at 22:04

4

It's not necessary to include the database name when connecting to MySQL. No database is selected if you don't specify the name, and you can create the DB by running:

import groovy.sql.Sql
def sql = Sql.newInstance("jdbc:mysql://localhost:3306","root","","com.mysql.jdbc.Driver")
sql.executeUpdate("create database newDB")

answered Sep 07 '13 at 22:04

Ko-Chih Wu

682
7
11

Also see http://stackoverflow.com/questions/3816015/sqlexception-no-suitable-driver-found-for-jdbcderby-localhost1527 regarding property "create=true" to connection string if db doesn't exist. – AndyJ May 11 '16 at 18:44

dmahapatro · Answer 3 · 2013-09-06T21:40:26.073

0

You need to have a look at GSQL. I have not validated it entirety but is exactly something what you are looking for.

edited Sep 06 '13 at 21:40

answered Sep 06 '13 at 21:33

dmahapatro

49,365
7
88
117

score 0 · Answer 4 · answered Sep 06 '13 at 21:42

You can create a database by running the

create database foobar

command. You can do that with MySQL Server and then you can connect using that database with groovy. As for your concrete question I do not understand why you want to connect to a database-less database server? Why do you desire to create the database with groovy?

Is it possible to create a database in groovy when connected to a MySQL? If so, how?

4 Answers4