When you retrieve a non-binary large-value data type—such as the varchar(max) data type—from a database, one approach is to read that data as a character stream.In the following example, the execute Query method of the SQLServer Statement class is used to retrieve data from the database and return it as a result set.If at least one row in the table has blob data that is longer than 32K (which means that Derby will stream it, so far as I can tell), then the error will be: ERROR XCL30: An IOException was thrown when reading a 'BLOB' from an Input Stream. Surprisingly, it doesn't (appear to) matter what the trigger statement is actually doing--so long as it references the blob column at least once, one of these two errors will occur, depending on the length of the data.And if the data is greater than 32k, then the error will happen regardless of what the trigger does or whether or not it references the blob column.
The following table lists the default mappings between the advanced SQL Server, JDBC, and Java programming language data types. Specifically, CLOB types can be used with the varchar(max) and nvarchar(max) data types, BLOB types can be used with varbinary(max) and image data types, and NCLOB types can be used with ntext and nvarchar(max).
The Microsoft JDBC Driver for SQL Server supports sending and retrieving CLR UDTs as binary data but does not support manipulation of the CLR metadata. In earlier versions of SQL Server, working with large-value data types required special handling.
The following sections provide examples of how you can use the JDBC driver and the advanced data types. Large-value data types are those that exceed the maximum row size of 8 KB.
In my case, I copied the text (XML) from the source column in the source database and pasted it to a file.
The file handler is “EXAMPLE_LOB_DIR” and the directory is “/directory/to/filter.xml”. Update Column In Database Now that we have the file handler, we’ll use the chunk of SQL Syntax below, to read the XML File into a SQL UPDATE statement, which updates DATA(column) in the CONFIG(table) with data in the dest_clob(xml file).