[
https://issues.apache.org/jira/browse/CALCITE-5867?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17747134#comment-17747134
]
Julian Hyde commented on CALCITE-5867:
--------------------------------------
Is it true that I can call {{getBlob}} on columns of type {{BINARY}} and
{{VARBINARY}}? If so, it seems to me that blob is more of an access method than
a data type.
So the real work is to implement {{getBlob}} and {{setBlob}} in Avatica, with a
wire protocol to match.
I do worry that if we add a {{BLOB}} data type to SQL, it will be a lot of work
to add the necessary operators and functions. And so people may end up creating
a {{VARCHAR}} column with no maximum length and just using the {{getBlob}} and
{{setBlob}} accessor methods.
All of the above seems to apply to character types: {{CLOB}}, {{CHAR}},
{{VARCHAR}}, {{getClob}}, {{setClob}}.
> Support BLOB data type
> ----------------------
>
> Key: CALCITE-5867
> URL: https://issues.apache.org/jira/browse/CALCITE-5867
> Project: Calcite
> Issue Type: New Feature
> Components: core
> Reporter: hujianhong
> Assignee: hujianhong
> Priority: Major
> Attachments: image-2023-07-25-14-24-34-839.png,
> image-2023-07-25-14-35-09-898.png
>
>
> In java.sql.JDBCType, there are binary, varbinary, and blob types for binary
> objects. However, there are only two types of binary and varbinary in
> SqlTypeName of Apache Calcite.
> In addition, for binary and varbinary types, we usually use the following
> code to read and write in JDBC:
>
> {code:java}
> //write
> PreparedStatement ps = conn. preparedStatment();
> ps.setBytes(1, new byte[]{xxx});
> //read
> ResultSet rs = xxx;
> rs. next();
> byte[] b1 = rs. getBytes(1); {code}
>
>
> For the Blob type, we usually use the following code in JDBC for streaming
> read and write:
> {code:java}
> //write
> PreparedStatement ps = conn. preparedStatment();
> ps.setBlob(1, inputstream);
> //read
> ResultSet rs = xxx;
> rs. next();
> Blob b1 = rs.getBlob(1); // or InputStream in = rs.getBinaryStream(2); {code}
>
> Because the BLOB is large, it cannot be read in one row like binary and
> varbinary objects, but needs to be read in a stream.
> In our scenario, our storage layer both supports binary, varbinary, and blob
> types, but because there is no native support BLOB data type in Apache
> Calcite, our approach is to map the BLOB type of the storage layer to the
> binary/varbinary type of Apache calcite, so as to read and write BLOB types
> through calcite.
> However, BLOB objects are read and written as binary/varbinay types, which
> will put a lot of pressure on the memory of the server, easily cause OOM on
> the server, and bring great challenges to the stability of the system.
> Therefore, we expect to add BLOB type support in Apache Calcite, and use
> streams to support BLOB type reading and writing with less memory, so as to
> avoid OOM pressure caused by one-time reading and writing of BLOB objects to
> the system.
>
> ------------------
> MySQL support BLOB data type：
> [https://dev.mysql.com/doc/refman/8.0/en/blob.html]
> In addition, Avatica currently does not support streaming reading and writing
> for BLOB data type. Therefore, in order to solve this problem, in our
> scenario, we support the MySQL protocol on the server side, and can directly
> use the MySQL client to read and write BLOB objects in streaming mode.
--
This message was sent by Atlassian Jira
(v8.20.10#820010)
