Raw SQL in DFC (Documentum)

Question

In DFC one can execute SQL directly (bypassing DQL) using the IDfSession.apiExec() method. The problem is that the method is marked as deprecated in the current (6.x, 7.x) versions of the DFC API.

Here's some example code using the deprecated method:

IDfSession session;
(...)
String sql = "UPDATE dm_sysobject_s SET r_modifier = 'hacker' WHERE r_object_id = '<some_id>'";
session.apiExec("execsql", sql);

This works fine, but as already mentioned apiExec is deprecated.

I've also tried an alternative approach:

(...)
IDfQuery query = new DfQuery(sql);
query.execute(session, IDfQuery.DF_EXEC_QUERY);

To my knowledge this ought to work, but I keep getting the following message:

[DM_QUERY_E_REG_TABLE_PERMIT_IN]error:  "You have insufficient privilege to UPDATE the dbo.dm_sysobject_s table."

I get the same error message when using IDfQuery.DF_QUERY, but anyway DF_EXEC_QUERY is the one that should be working -- or? I am of course trying this out with a super user account, so I wouldn't know which privileges I'm missing.

Is there a good, non-deprecated way to execute raw SQL statements from within DFC?

I would also like to add that I am very aware that raw SQL is strongly discouraged because it bypasses Documentum's security model. I am also aware that I can use @SuppressWarnings("deprecation") in my Java code, but that doesn't make the method any less deprecated.

Permeability

Answer 1

I looked into this myself and decompiled DfSession. In the apiExec() method DFC uses some internal calls that are available to "outsiders", and this enables the possibility of creating your own "apiExec() equivalent".

Here's a simple example from within a BOF class (a TBO implementation, for instance):

import static com.google.common.base.Strings.isNullOrEmpty;
...
import com.documentum.dmcl.impl.DmclApi;
...

public class MyBofClass extends MyBofInterface {
    ...

    private boolean execSql(String sql) throws DfException {
        return execApi("execsql", sql);
    }

    private boolean execApi(String command, String args) throws DfException {
        return execApi(getSession(), command, args);
    }

    private boolean execApi(IDfSession session, String command, String args) throws DfException {
        StringBuilder apiBuilder = new StringBuilder(command);
        apiBuilder.append(',');
        apiBuilder.append(session.getSessionId());
        if (!isNullOrEmpty(args)) {
            apiBuilder.append(',');
            apiBuilder.append(args);
        }
        return DmclApi.getInstance().exec(apiBuilder.toString());
    }
    ...
}

(One should probably restructure this into static util classes or whatever is preferred.)

The crucial part here is of course the DmclApi.getInstance().exec() call. This is an ugly hack, but no worse than the original apiExec() in my opinion. It's a reimplementation, really.

Permeability

Answer 2

You can support direct connection to your sql server as to Documentum Content Server. And execute your sql statements in those connection. The other thing is to find a way substitute your SQL stuff with smth else. Create sql views, stored procedures and so on.

Blakey

But what if the DFC client doesn't have access to the DB service directly? This is often the case (only privileged clients -- like the Content Server -- may connect to a DB, or the DB is on another network segment, etc.). It's also an "even more discouraged" way to do it as no-one but the CS should really access the DB. But thanks for your reply, it is certainly a creative approach that I didn't think about that might work well in some cases. – Permeability

Answer 3

Try the getCommand, setQuery and execute chain, like this:

IDfApplyExecSQL execSQL = DfAdminCommand
        .getCommand(IDfAdminCommand.APPLY_EXEC_SQL);
execSQL.setQuery("UPDATE....");
execSQL.execute(session);

If you still get the permissions message, it may be a problem with your SQL server or your connection to it.

Cameleer