Teradata JDBC Blob Example

JdbcBlobExample.scala

import java.io.{File, FileInputStream, FileOutputStream}
import java.sql.{Connection, DriverManager}

object JdbcBlobExample {

  def main(args: Array[String]) {
    val driver = "com.teradata.jdbc.TeraDriver"
    val url = "jdbc:teradata://mytdhost/TMODE=ANSI,CHARSET=UTF8"
    val username = "myusername"
    val password = "mypassword"
    val payloadFilename = "myfile"

    var connection: Connection = null

    try {
      Class.forName(driver)
      connection = DriverManager.getConnection(url, username, password)

      val statement = connection.createStatement()

      statement.execute(s"drop table $username.blob_example")
      println("Dropped experiment table")

      statement.execute(s"create table $username.blob_example (id integer, binary_payload blob)")
      println("Created experiment table")

      val preparedStatement = connection.prepareStatement(s"insert into $username.blob_example (?,?)")
      preparedStatement.setInt(1, 1)

      val myFile = new File(payloadFilename)
      val inputStream = new FileInputStream(myFile)
      preparedStatement.setBinaryStream(2, inputStream, myFile.length())
      preparedStatement.execute()
      println("Executed insert")

      val blobResultSet = statement.executeQuery(s"select id, binary_payload from $username.blob_example where id = 1")
      println("Executed select")

      blobResultSet.next()
      val in = blobResultSet.getBlob("binary_payload").getBinaryStream()
      val out = new FileOutputStream(new File(s"fetched_$payloadFilename"))

      val buffer = new Array[Byte](1024)

      Stream
        .continually(in.read(buffer))
        .takeWhile(-1 !=)
        .foreach(read => out.write(buffer, 0, read))
      out.close()
      println("Fetched payload")

    } catch {
      case e => e.printStackTrace
    }
    connection.close()
  }

}

source_target_files.txt

λ ~/src/jdbc-blob-example/ du -h myfile 
4.1M	myfile
λ ~/src/jdbc-blob-example/ md5 myfile  
MD5 (myfile) = f62123c733f822ec36751cc773a63fc6
λ ~/src/jdbc-blob-example/ du -h fetched_myfile 
4.1M	fetched_myfile
λ ~/src/jdbc-blob-example/ md5 fetched_myfile 
MD5 (fetched_myfile) = f62123c733f822ec36751cc773a63fc6