JDBC Connection... Not is Supported All SQL Commands

[Former Member]

Hi,

I try get connection using this code:

javax.naming.Context ctx = new javax.naming.InitialContext();
avax.sql.DataSource ds = (javax.sql.DataSource) ctx.lookup("jdbc/SAPDB");
java.sql.Connection conn = ds.getConnection();

This code work, but if I try use Group By, Sum, Distinct, Count, return a error... if I try use a simple Select it work fine.

For resolve this problem I using the ojdbc driver:

oracle.jdbc.pool.OracleDataSource ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL(config.getStringEntry("oracle.datasource.url"));
ods.setUser(config.getStringEntry("oracle.datasource.user"));
ods.setPassword(config.getStringEntry("oracle.datasource.pass"));
java.sql.Connection conn = ods.getConnection();

With ojdbc all SQL commands work fine, but the better is use the Context.lookup, but is possible use all SQL commands with Context.lookup? How?

Example... this query:

select CUSTOM "MODULO" from
   SAPEPTDB.WCR_WEBCONTENTSTAT  inner join
      SAPEPTDB.WCR_USERPAGEUSAGE 
        on (ID = HOURLY_ID or 
            ID = DAILY_ID or 
            ID = WEEKLY_ID or 
            ID = MONTHLY_ID or 
            ID = QUARTERLY_ID) 
group by CUSTOM order by CUSTOM

With OJDBC work fine, but with Context.lookup return this error:

The SQL statement 
"SELECT "CUSTOM" "MODULO" FROM 
"SAPEPTDB"."WCR_WEBCONTENTSTAT" INNER JOIN 
"SAPEPTDB"."WCR_USERPAGEUSAGE" ON "ID" = "HOURLY_ID" OR 
"ID" = "DAILY_ID" OR "ID" = "WEEKLY_ID" OR "ID" = 
"MONTHLY_ID" OR "ID" = "QUARTERLY_ID" GROUP BY "CUSTOM" 
ORDER BY "CUSTOM"" contains the semantics error[s]: ON 
conditions should only be concatenated with AND

Thx

Message was edited by: Eduardo Velasques

[Former Member]

Hi Eduardo,

Well, the error message is self-explanatory enough:

If the driver says they should, then they should...

You can try to rephrase the query without the use of inner-join:

select CUSTOM "MODULO"
from SAPEPTDB.WCR_WEBCONTENTSTAT as A, SAPEPTDB.WCR_USERPAGEUSAGE as B
WHERE (A.ID = B.HOURLY_ID or
      A.ID = B.DAILY_ID or
      A.ID = B.WEEKLY_ID or
      A.ID = B.MONTHLY_ID or 
      A.ID = B.QUARTERLY_ID)
group by CUSTOM order by CUSTOM

Hope that helps,

Yoav.