This topic describes how to use binary large objects (blobs) in PolarDB-X.

MySQL drivers of versions 5.1.x and 8.0.x have defects in implementing the PreparedStatement.setBlob method. The defects persist regardless of whether you use the method to connect to a MySQL server or a PolarDB-X instance. For specific blobs that are used to store binary data such as images and videos, errors may be reported due to invalid syntax. This topic describes how to troubleshoot syntax errors that occur when you use blobs in specific scenarios.

Scenario 1: PolarDB-X versions earlier than V5.4.9

In PolarDB-X versions earlier than V5.4.9, binary arrays cannot be used as arguments in SQL statements because binary arrays do not match the character set that is used for the connection string. You must convert binary arrays that are stored in blobs to hexadecimal strings on the client. The following code provides an example: 

insert into t1 values (0xaabbccdd,x'aabbccdd');

Method 1: If you use Java, you can use the PreparedStatement.setBytes method instead of the PreparedStatement.setBlob method. Your MySQL driver automatically converts byte[] arrays to hexadecimal strings.

Method 2: If you use Java and cannot use Method 1 to resolve the issue, submit a ticket to contact Alibaba Cloud technical support. For example, when you use frameworks such as Hibernate, you cannot specify the set method that is used. In this case, you can submit a ticket to contact Alibaba Cloud technical support. After you submit a ticket to contact Alibaba Cloud technical support, you are sent a customized MySQL driver package. This package can be used to install a MySQL driver that automatically returns the converted strings.

Method 3: If you use a different programming language, you must convert binary arrays that are stored in blobs to hexadecimal strings in your application.

Scenario 2: PolarDB-X versions earlier than V5.4.13 and later than or equal to V5.4.9

For PolarDB-X versions earlier than V5.4.13 and later than or equal to V5.4.9, you can use the _binary prefix. In addition to the methods provided in Scenario 1, you can use the following methods:

Method 4: If you use Java, you can modify SQL statements. For example, you can modify the following statement: 

insert into t1 values (?)

The following code shows the modification result:

insert into t1 values (_binary?)

Method 5: If you use Java, you can use a MySQL driver of V8.0.26. When you use the PreparedStatement.setBlob method, the driver automatically specifies the _binary prefix in the INSERT INTO statement.

Scenario 3: PolarDB-X V5.4.13 and later

PolarDB-X V5.4.13 and later are fully compatible with MySQL for processing binary data. In addition to the methods provided in Scenario 1 and Scenario 2, you can use the following methods:

Method 6: You can use the character encoding parameter of utf8 or utf8mb4 to connect to PolarDB-X. If you use Java, you can include the following parameter in the JDBC URL: 

useUnicode=true&characterEncoding=utf8

If you use a different programming language and your application is connected to a PolarDB-X database, you can execute the following statement: 

set names utf8mb4;

Specific issues can occur when you use a MySQL driver to implement the PreparedStatement.setBlob method. These issues may persist even if you connect your application to an official MySQL server. To resolve these issues, you must connect your application to an official MySQL server and use the utf8 or utf8mb4 character set. If you use GBK or other encoding schemes, errors may be reported due to invalid syntax.