Layer7 API Management

Expand all | Collapse all

How to get Readable data of a BLOB type field while querying DB

Jump to Best Answer
  • 1.  How to get Readable data of a BLOB type field while querying DB

    Posted 05-22-2017 08:04 AM

    StephaneMOLDOCH4453211 norre01 sridevi.kanchustambham #layer7gateway #jdbcquery

    Hi,

     

    I am trying to query a MySQL RDS Database using Query JDBC assertion, but the result i get is in BLOB(binary) format.

    i even tried CASTING it with following query:

    SELECT CAST(<column_Name> AS CHAR(10000) CHARACTER SET utf8) FROM <table_name>;

    but the result i get from this is in unreadable format.

     

    please advice on how to query such rows.

     

    Regards,

    Neha Singh



  • 2.  Re: How to get Readable data of a BLOB type field while querying DB

    Posted 05-23-2017 01:30 AM

    It may due to the charset, the default charset should be utf8, but the content input on client side might not be utf8



  • 3.  Re: How to get Readable data of a BLOB type field while querying DB

    Posted 05-24-2017 02:59 AM

    Hi All,

     

    Let me explain what i am trying to do exactly.

     

    Layer7 stores Audits in MySql db. audit_message stores the request and response messages of an API. We need to list all APIs which contains speicific data.

     

    For example: CustomerId = 123,

    To list all APIs audits whose request contains customerid:123.

     

    MySQL Query: Select * from audit_message where request_zipxml like '%123%'

     

    This query has given a result but we are not able to validate as columns request_zipxml and response_zipxml are of type blob. It is not in human readable format.

    There are some other fields like 'goid' which is of type binary.

     

     

    How to convert this data into human readable format on query?

     

    I am looking at the wrong place? Any suggestions ?

    The DB i  have connected to is an External RDS Mysql DataBase.

    The client I am using is MySQL Query Browser.

    Regards,

    Neha 



  • 4.  Re: How to get Readable data of a BLOB type field while querying DB
    Best Answer

    Posted 05-24-2017 04:04 AM

    You can use the uncompress function for the "*zipxml" columns to retrieve the data



  • 5.  Re: How to get Readable data of a BLOB type field while querying DB

    Posted 05-24-2017 04:47 AM

    Awesome Arvind ! 

    UNCOMPRESS function Worked for me, able to see the Data now

     

    Thanks a lot.