<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE></TITLE>
<META content="MSHTML 6.00.6000.16414" name=GENERATOR></HEAD>
<BODY><!-- Converted from text/plain format -->
<P><FONT size=2>> Sherman Wood wrote:<BR>><BR></FONT><FONT size=2>> DB2
is all screwed up. Quoting identifiers in DB2 is inconsistent.<BR>><BR>>
If I quote all identifiers in DB2 using " which is the quote character<BR>>
indicated by the DB2 JDBC driver, like:<BR>><BR>> select
"time_by_day"."the_year" as "c0" from "time_by_day" as<BR>> "time_by_day"
group by "time_by_day"."the_year" order by<BR>> "time_by_day"."the_year"
ASC<BR>><BR>> ... the query fails with with things like:<BR>><BR>>
com.ibm.db2.jcc.c.SqlException: DB2 SQL error: SQLCODE: -204,<BR>>
SQLSTATE:<BR>> 42704, SQLERRMC: DB2ADMIN.time_by_day)<BR><BR><FONT
face=Verdana color=#000080>There's a problem which occurs in Oracle: people
create tables with mixed-case names but they don't double-quote the table and
column names, so the names are converted to upper-case in the catalog. Then
mondrian submits queries with double-quotes and they get "ORA-00904: invalid
column name" or "ORA-00942: table or view does not exist". The solution is not
to remove double-quotes from the identifiers in the queries; it is to add
double-quotes to the DDL statements, so everything is stored in mixed-case from
the start.</FONT></FONT></P>
<P><FONT face=Verdana color=#000080 size=2>From this (<A
href="http://www-128.ibm.com/developerworks/db2/library/techarticle/0203adamache/0203adamache.html">http://www-128.ibm.com/developerworks/db2/library/techarticle/0203adamache/0203adamache.html</A>),
it looks like DB2's rules are identical to Oracle:</FONT></P>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV><FONT size=2>
<P><A name=N10114><SPAN class=atitle><STRONG>Case sensitivity and object
names</STRONG></SPAN></A></P>
<P>All database object names (tables, views, columns and so on) are stored in
the catalog tables in uppercase unless the identifier is delimited. If you use
a delimited name to create the identifier, the exact case of the name is
stored in the catalog tables.</P>
<P>An identifier, such as a column name or table name, is treated as case
insensitive when used in an SQL statement unless it is delimited. For example,
assume that the following statements are issued:</P>
<TABLE cellSpacing=0 cellPadding=5 width="100%" bgColor=#eeeeee border=1>
<TBODY>
<TR>
<TD><PRE><CODE class=section>
CREATE TABLE MyTable (id INTEGER)
CREATE TABLE "YourTable" (id INTEGER)
</CODE></PRE></TD></TR></TBODY></TABLE><BR>
<P>Two tables -- MYTABLE and YourTable -- will exist.</P>
<P>Now, the following two statements are equivalent:</P>
<TABLE cellSpacing=0 cellPadding=5 width="100%" bgColor=#eeeeee border=1>
<TBODY>
<TR>
<TD><PRE><CODE class=section>
SELECT * FROM MyTable
SELECT * FROM MYTABLE
</CODE></PRE></TD></TR></TBODY></TABLE><BR>
<P>However, the second statement below will fail with TABLE NOT FOUND since
there is no table named YOURTABLE:</P>
<TABLE cellSpacing=0 cellPadding=5 width="100%" bgColor=#eeeeee border=1>
<TBODY>
<TR>
<TD><PRE><CODE class=section>
SELECT * FROM "YourTable" // executes without error
SELECT * FROM YourTable // error, table not found
</CODE></PRE></TD></TR></TBODY></TABLE></FONT></DIV></BLOCKQUOTE>
<DIV><FONT size=2> </DIV>
<P><FONT face=Verdana color=#000080>So, the solution for DB2 would be the same
as for Oracle: fix your DDL.</FONT></P>
<P><FONT face=Verdana><FONT color=#000080>Let me know whether this works. If so,
we can unwind all of the stuff in the dialect which treats DB2 identifiers
differently (starting way back in your change
4009)<FONT>.</FONT></FONT></FONT><BR><BR>> 1. The solution for DB2 drill
through I did, works. It is not<BR>> just about<BR>> case sensitivity and
embedded spaces, unfortunately. I would<BR>> love it to be<BR>> cleaner
than this.<BR><BR><FONT face=Verdana color=#000080>I need a better reason than
'it just works' to let the code into the code base.</FONT><BR><BR>><BR>>
2. I take your point about not dealing with embedded "s. The<BR>> rest of
the<BR>> code in SqlQuery.Dialect does not deal with it either. Is it a
real<BR>> problem?<BR><BR><FONT face=Verdana color=#000080>Not true.
SqlQuery.java, line
874:<BR><BR>
String val2 = Util.replace(val, q, q +
q);<BR>
buf.append(q);<BR>
buf.append(val2);<BR>
buf.append(q);<BR><BR>It's not very likely that an identifier would contain a
double-quote, but it doesn't take much effort to deal with that problem, and so
we do. The principle that SQL generation is handled in the dialect, and nowhere
else, is a very important one. Your code breaks that.</FONT><BR><BR>><BR>>
3. Drill through in Mondrian is left up to the caller of the<BR>> Mondrian
API.<BR>> Mondrian hands back the SQL and it is up to the caller to
run<BR>> the SQL and<BR>> deal with the result. Given everything Mondrian
does to hide<BR>> the database,<BR>> this is messy. We should probably
have a DrillThroughResult that wraps<BR>> that does the SQL and manage the
difference between the database<BR>> structures and the schema metadata.
There are issues with dealing with<BR>> dealing with the potential data
volumes (ie. 100s of 1,000s<BR>> of rows) which<BR>> is currently left up
to the caller to deal with. Maybe the<BR>> DrillThroughResult should appear
as a ResultSet with an Iterator<BR>> interface, so that the result set is not
all in memory.<BR><BR><FONT face=Verdana color=#000080>I agree that handing back
SQL text breaks the abstraction, somewhat. But it also gives them the control to
execute the SQL with different cursor options, or even to hand the SQL back and
execute it on the client. We should let the current mondrian API stand. We can
re-open the discussion in the context of the olap4j
API.<BR><BR>Julian</FONT><BR></P></FONT></BODY></HTML>