Inserting Binary Data into SQL Database Using KNIME

[SnigdhaBiswas]

I have a specific requirement where I need to insert binary data (bytes) into a column in an SQL database using KNIME. I’m looking for a way to achieve this without encoding the bytes as strings, as I want to preserve the data type as bytes.

Is there a direct method to pass a bytes object to the SQL Executor node in KNIME? Alternatively, how can I accomplish this using parameterized queries in the SQL Executor node?

I want to ensure that the data remains in its original binary form without being converted to a string during the insertion process.

Thank you for any insights or solutions you can provide!

[takbb]

Hi @SnigdhaBiswas , I would think you are more likely to achieve your objective using the DB Update or DB Insert nodes as I believe these make use of parameterization behind the scenes, so can directly upload the data in binary form.

I cannot think of a way to achieve it using SQL Executor which I think is reliant on text replacement rendering it unsuitable for inserting binary data.

Is this an MS SQL Server database? What is the datatype of your database column?

[SnigdhaBiswas]

It is a Teradata database and the datatype is binary large object (BLOB).
Can you explain how can this be done using DB Insert/update node?

[mlauber71]

@SnigdhaBiswas you might have to configure the type of he column in the DB write/Insert node as BLOB.

Python Script - export images in table

@ActionAndi what you can do is store the PNG file in a SQLite database and then read it back into KNIME. [image] Just make sure that you have an actual PNG file (binary) and store it as a BLOB data type in SQLite. If you read it back to KNIME make sure the column “data” with the image is defined as a PNG file: [image] You might be able to combine these approaches: You can extract the current surroundings from a node. I have also created a component for that:

You will have to have the data as column type binary in your knime workflow. The column will have to match the ones in the database table you want to write to

Files to Binary Objects

[takbb]

Hi @SnigdhaBiswas , I’ve put a demo workflow on the hub, using the H2 database as an example.

Upload binary files to database – takbb

This uploads some binary files from the workflow’s data folder, and then reads the binary data back from the database, for display in a KNIME table.

On the H2 database, I defined the column as BLOB datatype.

create table IMAGES
(ID numeric(5),
IMAGE_BINARY blob)

In the DB Insert node, I set the data type mapping for Binary object as follows:

It didn’t work if I set it mapping to BLOB, but it worked for any of the other Binary mappings (BINARY, LONGVARBINARY, VARBINARY).

[SnigdhaBiswas]

Thank you for providing the example workflow. However, it doesn’t fully solve my task. I’m working with binary data in a Python Script node, that is, reading a file in binary mode. When I attempt to convert this binary data into a Pandas DataFrame and then add it to the KNIME output table to follow the provided workflow steps, it fails because KNIME tables cannot accommodate binary column types.

Additionally, when I try to read the file using the method demonstrated in the example workflow, I encounter compatibility issues with my file format.

Any further guidance or alternative approaches would be greatly appreciated. Thank you for your assistance.

[takbb]

Hi @SnigdhaBiswas ,

When you say that KNIME tables cannot accommodate binary column types, I don’t fully understand what you are saying here because in the workflow above it is handling a binary column type, so can you be more specific about what data KNIME cannot handle, or what errors you are encountering.

The workflow was providing an example of how to load binary data into a database, but I think your question has now moved on to something unrelated to the database load, but is perhaps a python question, or more general binary file handling question?

Can I ask what type of binary data you have, and also is your python script being used to do anything other than load the data from a file and attempt to place it in a pandas dataframe?

What problem/error do you encounter if you try to read the file using the Files to Binary Objects node? When you say “compatibility issues” with the file format, can you be more specific. i.e. what is incompatible?

[mlauber71]

@SnigdhaBiswas I sent the data provided by @takbb thru a Python node with pandas dataframe and they go right thru and later can be used as binary image files. So this does work.

import knime.scripting.io as knio

# This example script simply outputs the node's input table.

df = knio.input_tables[0].to_pandas()

# Getting the data types of each column
dtypes_series = df.dtypes

# Converting the Series to a DataFrame
dtypes_df = dtypes_series.reset_index()
dtypes_df.columns = ['Column', 'DataType']

# Ensuring both columns are of type string
dtypes_df['Column'] = dtypes_df['Column'].astype(str)
dtypes_df['DataType'] = dtypes_df['DataType'].astype(str)

knio.output_tables[0] = knio.Table.from_pandas(df)
knio.output_tables[1] = knio.Table.from_pandas(dtypes_df)

Upload binary files to database.knwf (318.4 KB)