SQL pass-through queries in Documentum

DQL language, used by Documentum Content server, is a pitiful semblance of ANSI SQL, and when you want to write some complex query you have following options:

  • write a lot of java code
  • create view and register it
  • take advantage of EXEC_SQL RPC command and “insert into table_name select …” statement

But since Documentum 6.6 EMC introduced new RPC command – EXEC_SELECT_SQL:

API>apply,c,0000000000000000,ENTRY_POINTS
...
q0
API>next,c,q0
...
ОК
API>dump,c,q0
...
USER ATTRIBUTES

  name                         [0]: ABORT_LOAD
....
                             [111]: EXEC
                             [112]: EXEC_SELECT_SQL
                             [113]: EXEC_SQL
                             [114]: EXEC_XQUERY

it allows to run SQL queries directly against a database:

API> ?,c,execute EXEC_SELECT_SQL with QUERY='SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10'
level
----------------------
                     1
                     2
                     3
                     4
                     5
                     6
                     7
                     8
                     9
                    10
(10 rows affected)

Bad news is this “new” feature works only in D7, in previous releases it raises an error:

Connected to Documentum Server running Release 6.7.1230.0293  Linux.Oracle
Session id is s0
API> ?,c,execute EXEC_SELECT_SQL with QUERY='SELECT LEVEL FROM DUAL CONNECT BY LEVEL <=10'
level
----------------------
(0 rows affected)
[DM_SESSION_E_RPC_ERROR]error:  "Server communication failure"

java.io.IOException: Unexpected end-of-stream

Java support of EXEC_SELECT_SQL RPC command is available here

One thought on “SQL pass-through queries in Documentum

  1. Pingback: Another way to implement “controlled” database lock | Documentum in a (nuts)HELL

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s