How to execute SQL using DQL Session

Hi,

Does any body knows how to execute SQL query using session? for example, I want to do something like that, but the query is DQL and not SQL

private static IDfCollection runQuery(IDfSession dfcSession, String query) {

IDfQuery dqlQuery = new DfQuery();

IDfCollection coll = null;

dqlQuery.setDQL(query);

try {

coll = dqlQuery.execute(dfcSession, IDfQuery.DF_QUERY);

} catch (Exception e) {

e.printStackTrace();

}

return coll;

}

Thanks!

Find more posts tagged with

Note:

If there are Accepted Answers, those will be shown by default. You can switch to 'All Replies' by selecting the tab below.

Accepted answers

witchedwiz

can't you run the queries directly via dfc?

say

IDfCollection coll = null;

IDfClientX clientx = new DfClientX();
String docbase = SessionManagerHttpBinding.getCurrentDocbase();
dfSession = sessionManager.getSession(docbase);
IDfQuery query = clientx.getQuery();
String queryStr = "YOURQUERY";
query.setDQL(queryStr);
coll=query.execute(dfSession,IDfQuery.DF_EXECUTE_QUERY);

(name should be DF_EXECUTE_QUERY or 3 iirc)

you make a component to batch such procedures, you grant access to such component only to some specific roles/something similar, and batch the query like that...

Unless I'm terribly mistaken this should cover your issue

All comments

Miller-zzMST

Hi!

I haven't use dql in dfc code but dql has the special administrative method exec_sql:

EXECUTE exec_sql WITH query='sql_query'

It seems to me, there should be any problems to use it in dfc code.

Please, have a look at Content Server DQL Reference > Administration Methods > Exec_sql.

~ Konstantin

witchedwiz

I can't remember if this is your case, but if I recall correctly there *is* a way to pass sql statement, but....you require super-user privilege

Now, if you REALLY, REALLY must query against the a database directly you can sort it out with your admin to have those specific database table registered as content within documentum.. to my knowledge this is a practice when dealing with lookup table and ONLY lookup table, i can't really imagine a valid reason for registering db table as documentum content myself..

Not to be too invasive, but why do you need to use SQL instead of DQL? ok, DQL might be limited in some ways, but under other aspects is more than just SQL (or at least it seems so to me so far, feel free to disagree).. Is is related to subqueries?

sagar.singh

Hi Dimal,

You can achieve the functionality by registering your table in the docbase. It will create an dm_register object in the docbase and allow to execute the operations on RDBMS table using DQL queries.

further help can be from DQL Reference guide page 108 v6.6

I don;t feel it will have any impact and also solves your purpose.

sagar

witchedwiz

yes and no..

1) Registering a table inside documentum will make it queryable, but he'll have to assign rights as by default any rights are given ONLY to the registering owner...

2) I adviced against because we don't know what table he wants to register.. In my comprehension so far he should never allow core documentum table to be queried directly... If he wants to query directly a "generic" table sure, go ahed and register, though so far I only needed to register some views..

3) I'm still wondering what queries he's aiming at that can't be handled with some dql + eventual workaround, I'll admit that I didn't delve too deep into DQL so I'm all ears in case some bit of tips comes around (you never know, as the saying goes "your doubt today might as well be my doubt tomorrow".

Vito

DimaL

Hi,

thanks for the answers. I want to explain shortly what I'm aiming for:

Suppose you have and SDT in your process named "my_sdt".

Process engine will create a reportable type for it, something like "dmc_wfsdrp_my_sdt" and will persisit all the data into this type.

As time passes by, I want to delete outdated rows from this object.

I tried to delete it using DQL and webtop registered jobs, but it takes a lot of time, so I create some sample tool that connect to SQL database underlying the docbase and deletes the rows.

This has some disadvantage, since I need a separate configuration for this tool, and I was wondering if I could execute my SQL from the DFC.

In this case, I could leverage the performance of database SQL and the configuration of my DQL

Dima

witchedwiz

can't you run the queries directly via dfc?

say

IDfCollection coll = null;

(name should be DF_EXECUTE_QUERY or 3 iirc)

you make a component to batch such procedures, you grant access to such component only to some specific roles/something similar, and batch the query like that...

Unless I'm terribly mistaken this should cover your issue

DimaL

Thanks witchedwiz!

This worked great!