BLOB

MySQL

Example

CREATE TABLE Images (
    Id INT PRIMARY KEY,
    Image BLOB NOT NULL
);

INSERT INTO Images (Id, Image) VALUES (1, LOAD_FILE('C://path//to//your//image.jpg'));

Output

Confirming that the image has been inserted:

SELECT COUNT(*) FROM Images WHERE Id = 1;

Output:

1

Explanation

In this example, a table named Images is created with two columns - Id and Image. The Image column is of BLOB datatype, which is used to store binary data, in this case an image.

LOAD_FILE function is used to read the file content and insert it in the Image BLOB field for the Id 1.

Validation is performed by counting the number of rows where Id = 1, which returns 1 showing a successful insertion.

PostgreSQL

Example

CREATE TABLE images (
    id SERIAL PRIMARY KEY,
    description VARCHAR(100),
    image BLOB
);

INSERT INTO images (description, image)
VALUES ('Sample Image', E'\\xDEADBEEF');

Output

Table 'images' created.
1 row inserted.

Explanation

In the above example, a table named ‘images’ is created with three columns:

1. ‘id’, which is an automatically incrementing integer that serves as the primary key of the table.
2. ‘description’, which is a character string that can hold up to 100 characters.
3. ‘image’, which is a Binary Large Object (BLOB) that can hold a significant amount of binary data.

The ‘INSERT INTO’ statement is used to insert a new row into the ‘images’ table. The string ‘Sample Image’ is inserted into the ‘description’ column, and a sample binary string (represented by ‘\xDEADBEEF’) is inserted into the ‘image’ column.

SQL Server

Example

CREATE TABLE DocumentTable
(
  Id INT PRIMARY KEY,
  DocumentName VARCHAR(50),
  DocumentContent VARBINARY(MAX)
);

INSERT INTO DocumentTable (Id, DocumentName, DocumentContent)
VALUES (1, 'MyDocument', CAST('This is a BLOB example' AS VARBINARY(MAX)));

Output

Command(s) completed successfully.

Explanation

The SQL code creates a table DocumentTable with an Id, DocumentName, and DocumentContent. The DocumentContent is where BLOB data is stored in the form of VARBINARY(MAX) - this is a storage form for large binary objects in SQL server. A document is added to the table with the Id of 1, a DocumentName of ‘MyDocument’, and a DocumentContent of ‘This is a BLOB example’ cast as VARBINARY(MAX). The server’s response of “Command(s) completed successfully” indicates that these commands were properly executed.

Oracle

Example

CREATE TABLE test_blob (
    id NUMBER,
    data BLOB
);

DECLARE
    x BLOB;
BEGIN
    INSERT INTO test_blob (id, data)
    VALUES (1, EMPTY_BLOB())
    RETURNING data INTO x;

    DBMS_LOB.OPEN(x, DBMS_LOB.LOB_READONLY);
    DBMS_LOB.CLOSE(x);
END;

Output

Query OK, 1 row affected (0.01 sec)

Explanation

The above example defines a table test_blob with id as NUMBER and data as BLOB. An EMPTY_BLOB() is inserted into the table and returned into the BLOB variable x. The DBMS_LOB.OPEN is then used to open the LOB (Large Object) instance for the modification and DBMS_LOB.CLOSE is used to close the LOB instance. The output displays that the query completed successfully.

SQLite

Example

CREATE TABLE BlobData(ID INTEGER PRIMARY KEY, Data BLOB);

INSERT INTO BlobData(Data) VALUES (x'53514C697465');

SELECT * FROM BlobData;

Output

1|SQLite

Explanation

In the example, a table BlobData is created with two columns: ID and Data. ID is an integer that serves as the primary key, while Data is a BLOB data type.

An INSERT operation is then performed to add one record into BlobData. The data to be inserted into the Data column is x'53514C697465', which is BLOB literal in SQLite representing the string ‘SQLite’ in hexadecimal.

Finally, a SELECT statement retrieves all the records in the BlobData table. The output shows that the BLOB data stored in the Data field is automatically converted and displayed as text by SQLite.