BPM - query internal SAP table BC_BPEM_EL_ENTRY - binary columns

[Former Member]

Hi,

I need to do a query involving the internal SAP tables for the BPM and I have the following problem with querying the BC_BPEM_EL_ENTRY table (the DB used is SAP's MaxDB):

As you can see there are a lot of binary columns. How can I query those columns in the WHERE clause of a SELECT query?

I thought that converting the binary columns to hexadecimal and querying against that would work but the HEX function doesn't seem to work although it's listed in the docs (http://help.sap.com/saphelp_nw74/helpdata/en/44/d9ca62b7f74427e10000000a155369/content.htm)

I did a very simple test query but I get an error:

Exception of type com.sap.sql.log.OpenSQLException caught: The SQL statement "SELECT HEX(PROC_DEF_ID) FROM BC_BPEM_EL_ENTRY " contains the syntax error[s]: - 1:11 - SQL syntax error: the token "(" was not expected here

I thought I could use the hex function in the WHERE clause like that:

SELECT whatever columns

FROM BC_BPEM_EL_ENTRY

WHERE HEX(PROC_DEF_ID) = '...'

Could you suggest how to query those binary columns?

Thanks in advance!

[Former Member]

OK, I managed to resolve it somehow. The issue was due to the fact that SAP uses an intermediary step in executing queries, i.e. it evaluates the SQL you pass it to a DB vendor independent syntax called Open SQL. In my case I had to bypass that and issue native SQL in order to use the HEX function to filter on binary data.

You can check:

https://help.sap.com/saphelp_nw73/helpdata/en/4a/8fa363ebe03897e10000000a42189c/content.htm

and

https://archive.sap.com/discussions/thread/560210

In order to import the com.sap.sql.NativeSQLAccess class you need to add a dependency to the SAP opensql component in NWDI.

Anyone with a better solution?