Db2 LUW getting error: pickle.UnpicklingError: invalid load key for binary data type

[Siddhesh Naik]

Hi,

In Db2 LUW, which fetching the binary data type table data, its gives following error:

pickle.UnpicklingError: invalid load key

Also observation is , its storing memory location instead of data (I might be wrong about this observation. Please check this sql log line "2019-09-11 11:33:35,780 INFO sqlalchemy.engine.base.Engine ('text', <memory at 0x7f6f6becc048>)")

My code snippet is below:

from sqlalchemy import *
from sqlalchemy import create_engine
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
import config
import pickle


URL = "ibm_db_sa://{user}:{password}@{host}/{database}"

engine = create_engine(URL, poolclass=config.POOL_CLASS, echo=True)
meta = MetaData(bind=engine, schema=config.SCHEMA)
db_session = sessionmaker(bind=engine)
model = declarative_base(metadata=meta)


json_data = {
        "title": "Sample Konfabulator Widget",
        "name": "main_window",
        "width": 500,
        "height": 500
    }


class Client(model):
    __tablename__ = 'client'
    id = Column("id", Integer, nullable=False, primary_key=True)
    name = Column("name", String(128), nullable=True)
    client_address = Column("client_address", Binary)


def add_client(client_details):
    session = db_session()
    client = Client(**client_details)
    session.add(client)
    session.commit()
    return True


def get_client():
    session = db_session()
    client = session.query(Client).filter(Client.id == "1").first()
    print("Client_id =", client.id)
    print("Client_name =", client.name)
    print("client_address =", pickle.loads(client.client_address))
    session.commit()
    session.close()
    return True


meta.create_all(engine)
client_data = {"name": "text", "client_address": pickle.dumps(json_data)}
add_client(client_data)
get_client()

My sql log is below:

2019-09-11 11:33:26,833 INFO sqlalchemy.engine.base.Engine SELECT CAST('test plain returns' AS VARCHAR(60)) AS anon_1 FROM SYSIBM.SYSDUMMY1
2019-09-11 11:33:26,834 INFO sqlalchemy.engine.base.Engine ()
2019-09-11 11:33:26,845 INFO sqlalchemy.engine.base.Engine SELECT CAST('test unicode returns' AS VARGRAPHIC(60)) AS anon_1 FROM SYSIBM.SYSDUMMY1
2019-09-11 11:33:26,846 INFO sqlalchemy.engine.base.Engine ()
2019-09-11 11:33:26,866 INFO sqlalchemy.engine.base.Engine SELECT "SYSCAT"."TABLES"."TABNAME" 
FROM "SYSCAT"."TABLES" 
WHERE "SYSCAT"."TABLES"."TABSCHEMA" = ? AND "SYSCAT"."TABLES"."TABNAME" = ?
2019-09-11 11:33:26,866 INFO sqlalchemy.engine.base.Engine (b'CLIENT_SCHEMA', b'CLIENT')
2019-09-11 11:33:26,909 INFO sqlalchemy.engine.base.Engine 
CREATE TABLE client_schema.client (
        id INT NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1), 
        name VARCHAR(128), 
        client_address BLOB(1M), 
        PRIMARY KEY (id)
)


2019-09-11 11:33:26,909 INFO sqlalchemy.engine.base.Engine ()
2019-09-11 11:33:35,683 INFO sqlalchemy.engine.base.Engine COMMIT
2019-09-11 11:33:35,777 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-09-11 11:33:35,780 INFO sqlalchemy.engine.base.Engine INSERT INTO client_schema.client (name, client_address) VALUES (?, ?)
2019-09-11 11:33:35,780 INFO sqlalchemy.engine.base.Engine ('text', <memory at 0x7f6f6becc048>)
2019-09-11 11:33:35,835 INFO sqlalchemy.engine.base.Engine COMMIT
2019-09-11 11:33:35,884 INFO sqlalchemy.engine.base.Engine BEGIN (implicit)
2019-09-11 11:33:35,886 INFO sqlalchemy.engine.base.Engine SELECT client_schema.client.id AS client_schema_client_id, client_schema.client.name AS client_schema_client_name, client_schema.client.client_address AS client_schema_client_client_address 
FROM client_schema.client 
WHERE client_schema.client.id = ? FETCH FIRST 1 ROWS ONLY
2019-09-11 11:33:35,887 INFO sqlalchemy.engine.base.Engine (b'1',)
Client_id = 1
Client_name = text
Traceback (most recent call last):
  File "/home/abc/abc.py", line 57, in <module>
    get_client()
  File "/home/abc/abc.py", line 48, in get_client
    print("client_address =", pickle.loads(client.client_address))
_pickle.UnpicklingError: invalid load key, '<'.

Please help.