Posts Tagged ‘MySQL’
Executing a MySQL Script through JDBC July 30, 2013 | 03:26 pm

There’s no way to execute a MySQL script through JDBC, and most of the tools (including SimpleJdbcTestUtils) have weird requirements for the script and/or a bunch of overhead to do what you want to do. The big trick is that DELIMITER isn’t actually a SQL command: it’s a pre-processing instruction (at least conceptually). And that makes feeding the script into MySQL a pain.

Groovy and Commons-Lang StringUtils to the rescue. Here’s the short script that will break up a SQL script into a Collection of String objects, which can then be fed to your favorite SQL execution tool.

	static Collection<String> breakUpSqlScript(String loadSql) {
		List<String> statements = []
 
		String delimiter = ";"
		String currentStatement = ""
		loadSql.eachLine { String line ->
			line = line.trim()
			if(StringUtils.startsWithIgnoreCase(line, "--")) {
				// Comment: IGNORE
			} else if(line.endsWith(delimiter)) {
				line = StringUtils.removeEnd(line, delimiter)
				currentStatement = "$currentStatement \n $line".trim()
				statements << currentStatement
				currentStatement = ""
			} else if(StringUtils.startsWithIgnoreCase(line, "DELIMITER")) {
				delimiter = StringUtils.removeStartIgnoreCase(line, "DELIMITER").trim()
			} else {
				currentStatement = "$currentStatement \n $line"
			}
		}
		if(!StringUtils.isBlank(currentStatement)) statements << currentStatement
 
		return statements
	}

PS: If you want to get the contents of a file as a string, use File#getText().

Wanted: MySQL Magic Chanting April 12, 2010 | 09:30 am

Here’s my problem. I’m working on integrating the EPA MOVES model, which means I’m living on MySQL. I need to load large amounts of data to the database, so I’m using the MySQL “LOAD DATA INFILE” command. The problem is that while the data file format is always the same, the tables that I need to load are going to differ, as are the file locations.

Complicating the issue is that I’m calling this from GAMS, which means any kind of advanced programming stunts (like parsing the file or dynamically generating SQL on the client) are pretty much out.

So I’d like to do something like this on the server:

LOAD DATA INFILE @data_file INTO TABLE @table_name

Except that MySQL really dislikes the idea of using variables to specify the table name.

“No problem,” I think to myself, “I’ll generate dynamic SQL on the server using PREPARE.” (Yes, I do think in hyperlinks.) A bit of cutesy abuse of CONCAT and PREPARE later, I’m ready to roll.

The catch? ERROR 1295: MySQL doesn’t support LOAD DATA in prepared statements. Nerts.

At this point, I’m seriously considering automatically generating a stored procedure load_data_TABLENAME for every table name in the database, and that’s making me feel sad deep inside. An alternative is to add a layer of indirection via a call to .Net, but the result will split some of the database logic which I’d like to keep consolidated on the server, not to mention complicating a system that I’d much prefer to simplify.

Any suggestions on how to make this work?