MySQL, scala and BLOB

First download the JDBC driver.

To work in the scala REPL type:

-=[biomunky@playtime svn]=-  scala -cp path/to/mysql-connector-java-5.1.13/mysql-connector-java-5.1.13-bin.jar

import the required methods from java.sql:

import java.sql.{Connection, DriverManager, SQLException, ResultSet}

the connection string to the database should look something like this:

val conString = "jdbc:mysql://localhost:3306/DBNAME?user=UID&password=PASSWD"

Then, load the driver and start the connection:

classOf[com.mysql.jdbc.Driver]
val con = DriverManager.getConnection(conString)

Then, if working in a script/class/whatever it would be worth starting a try/catch/finally block

try {
    // do some stuff
}
catch {
    case _ => println("A problem!")
}
finally {
    println("Closing the con")
    con.close
}

This isn’t really required in the REPL, not for the stuff I am doing. The data I am after is in BLOB format and all I wanted to do was dump it to a file (not what the final code will do).

val handle = con.createStatement(ResultSet.TYPE_FORWARD_ONLY, ResultSet.CONCUR_READ_ONLY)
val rs 	   = handle.executeQuery("select my_blob from BLOBS limit 1")

The first line creates a handle to the database that has read only access, the second pulls a single blob entry from the dataset into an sql.ResultSet. Not being familiar with the syntax/classes available for processing the data the following seemed suitably functional:

while (rs.next) {
    val theBlob = rs.getBlob("my_blob") // for more options see *
}

* JavaDoc

To write this to a file do the following inside the while loop (below theBlob).

val outputFile = new File( "/tmp/" + rs.getString("/tmp/filename") )
val outhandle = new BufferedOutputStream( new FileOutputStream(outputFile))
outhandle.write( myBlob.getBytes(1 , myBlob.length.toInt), 0, myBlob.length.toInt )
outhandle.close

As alternative, coerce the BLOB into a list:

val byteStream =  new ByteArrayOutputStream()
byteStream.write(theBlobl.getBytes(1, theBlob.length.toInt), 0, theBlob.length.toInt).toString.split("\n")
(byteStream.toString.split("\n").toList).foreach { println(_)}

The previous two steps are made possible by doing the following import

import java.io.{BufferedOutputStream, FileOutputStream, File, ByteArrayOutputStream};

Then close the database connection and make a cup of tea.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s