Lindorm:BLOB data type (in invitational preview)

You can set the data type of a column in a table to BLOB when you create the table or modify the schema of the table.

• You can execute the following statement to create a table named tb. In the tb table, specify the p1 column as the primary key column and set the data type of p1 to INT, and specify the c1 and c2 columns as non-primary key columns and separately set the data types of c1 and c2 to VARCHAR and BLOB.

  CREATE TABLE tb (p1 INT, c1 VARCHAR, c2 BLOB, PRIMARY KEY(p1));

  Note

  • If the table name contains special characters such as underscores (_), specify the BLOB_BUCKET_NAME attribute in the statement. For more information about the BLOB_BUCKET_NAME attribute, see Table options.

  • For more information about the syntax used to create a table, see CREATE TABLE.

• You can execute the following statement to add the c3 column to the tb table and set the data type of the c3 column to BLOB:

  ALTER TABLE tb ADD COLUMN c3 BLOB;

  Important

  • For more information about the syntax used to add a column to a table, see ALTER TABLE.

  • In LindormTable whose version is earlier than 2.6.4, if you do not configure a column of the BLOB type when you create a table and use the ALTER TABLE ADD COLUMN syntax to add a column of the BLOB type after the table is created, errors occur when you write data to or read data from the column. We recommend that you upgrade the LindormTable version of your instance to 2.6.4 and later. For more information about how to view or upgrade the version of LindormTable, see Release notes of LindormTable and Upgrade the minor engine version of a Lindorm instance.

  • In LindormTable 2.6.4 and later versions, when you use the ALTER TABLE ADD COLUMN syntax to add a column of the BLOB type to a table, the value of BLOB_BUCKET_NAME is concatenated by using the database name and table name by default. If the concatenated value of BLOB_BUCKET_NAME does not comply the naming conventions of buckets, an error is reported. In this case, you can explicitly configure a valid value for BLOB_BUCKET_NAME. Example: ALTER TABLE tb_name SET 'BLOB_BUCKET_NAME'='my-bucket-name';. For more information about the naming conventions for a bucket, see Table options.

  • If the BLOB_BUCKET_NAME attribute is already configured for the table, do not change its value. You can perform the following operations to check whether the BLOB_BUCKET_NAME attribute is configured for the table: Log on to the cluster management system. On the Overview page, click the name of the table that you want to check. In the Current Table section, click View Table Attributes and view the value of the BLOB_BUCKET_NAME attribute. For more information about how to log on to the cluster management system, see Log on to the cluster management system.

You can configure parameters in the following format to write data to a BLOB column: PreparedStatement#setBlob(int parameterIndex, InputStream inputStream, long length). The inputStream parameter specifies the InputStream object that you want to write to the BLOB column. The length parameter specifies the size of the data in the InputStream object. The following code provides an example on how to write data to a BLOB column:

String upsert = "upsert into " + tableName + "(p1,c1,c2) values(?, ?, ?)";
int len = 20*1024*1024 + 3;
byte[] v = new byte[len];
try (PreparedStatement pStmt = conn.prepareStatement(upsert)) {
pStmt.setInt(1, 1);
pStmt.setString(2, "123");
pStmt.setBlob(3, new ByteArrayInputStream(v), len); // Write data to a BLOB column through a data stream.
pStmt.executeUpdate();
}

You can use one of the following two methods to read data in a BLOB column:

• Method 1: Read all data in one row of a BLOB column at a time and save the data to a byte array.

  // Read all data in one row of a BLOB column at a time.
  ResultSet resultSet = stmt.executeQuery("select * from " + tableName + " where p1 = 1" );
  byte[] readBytes2 = resultSet.getBytes(3);

• Method 2: Read data in the InputStream object obtained from a BLOB column.

  Note

  • This method is available only when the version of the Lindorm JDBC driver is 2.1.3 or later.

  • To use streams to process the data read from the BLOB column in subsequent operations, use this method to read data.

  // Read data in the InputStream object obtained from a BLOB column.
  ResultSet resultSet = stmt.executeQuery("select * from " + tableName + " where p1 = 1" );
  Blob blob = resultSet.getBlob(3);
  InputStream inputStream = blob.getBinaryStream();
  ....// Read data in the InputStream object obtained from the BLOB column for streaming processing.
  blob.free();    // Release the BLOB object after the data is obtained.