score:1

Accepted answer

We finally found a workaround as follows:

According to the column definition in slick

def column[C](n: String, options: ColumnOption[C]*)(implicit tt: TypedType[C]): Rep[C]

You can specify how the column is going to be translated between the driver and your code. If you want to use the out-of-the-box translations fine but for Oracle the translation for the CLOB type doesn't seem to work properly.

What we did was to define the column as a String but letting Slick to handle the translation with our custom code. The column definiton is the following:

def myClobColumn = column[String]( "CLOBCOLUMN" )( new StringJdbcType )

asd

Being StringJdbcType our custom code to solve the translation between our String to be inserted (up to 65535 bytes) and an Oracle CLOB.

The code for StringJdbcType is as follows: 

class StringJdbcType extends driver.DriverJdbcType[String] {
  def sqlType = java.sql.Types.VARCHAR
  // Here's the solution
  def setValue( v: String, p: PreparedStatement, idx: Int ) = {
    val conn = p.getConnection
    val clob = conn.createClob()
    clob.setString( 1, v )
    p.setClob( idx, clob )
  }
  def getValue( r: ResultSet, idx: Int ) = scala.io.Source.fromInputStream( r.getAsciiStream( "DSPOLIZARIESGO" ) )( Codec.ISO8859 ).getLines().mkString
  def updateValue( v: String, r: ResultSet, idx: Int ) = r.updateString( idx, v )
  override def hasLiteralForm = false
}

The setValue function was our salvation because we could build an Oracle CLOB with the already instantiated PreparedStatement and the String comming from our domain. In our implementation we only had to do the plumbing and dirty work for the Oracle CLOB.

In sum, the extension point offered by Slick in driver.DriverJdbcType[A] was what we actually used to make the thing work.

score:0

These are some improvements related to the solution: close resources and stream inspection

  class BigStringJdbcType
    extends profile.DriverJdbcType[String] {

    def sqlType: Int = java.sql.Types.VARCHAR

    def setValue(v: String, p: PreparedStatement, idx: Int): Unit = {
      val connection = p.getConnection
      val clob = connection.createClob()
      try {
        clob.setString(1, v)
        p.setClob(idx, clob)
      } finally {
        clob.free()
      }
    }

    def getValue(r: ResultSet, idx: Int): String = {
      val asciiStream = r.getAsciiStream(idx)
      try {
        val (bufferEmpty, encoding) = getInputStreamStatus(asciiStream)

        if (bufferEmpty) {
          convertInputStreamToString(asciiStream, encoding)
        } else ""

      } finally {
        asciiStream.close()
      }
    }

    def updateValue(v: String, r: ResultSet, idx: Int): Unit =
      r.updateString(idx, v)

    override def hasLiteralForm: Boolean = false
  }

Some utilities to complement the solution

  def getInputStreamStatus(stream: InputStream): (Boolean, String) = {
    val reader = new InputStreamReader(stream)
    try {
      val bufferEmpty = reader.ready()
      val encoding = reader.getEncoding
      bufferEmpty -> encoding

    } finally {
      reader.close()
    }
  }

  def convertInputStreamToString(
    stream: InputStream,
    encoding: String
  ): String = {
    scala.io.Source.fromInputStream(stream)(encoding).getLines().mkString
  }

Related Query

More Query from same tag