001/*
002// This software is subject to the terms of the Eclipse Public License v1.0
003// Agreement, available at the following URL:
004// http://www.eclipse.org/legal/epl-v10.html.
005// You must accept the terms of that agreement to use this software.
006//
007// Copyright (C) 2008-2009 Pentaho
008// All Rights Reserved.
009*/
010package mondrian.spi.impl;
011
012import java.sql.*;
013import java.util.Calendar;
014import java.util.List;
015
016/**
017 * Implementation of {@link mondrian.spi.Dialect} for the Microsoft Access
018 * database (also called the JET Engine).
019 *
020 * @author jhyde
021 * @since Nov 23, 2008
022 */
023public class AccessDialect extends JdbcDialectImpl {
024
025    public static final JdbcDialectFactory FACTORY =
026        new JdbcDialectFactory(
027            AccessDialect.class,
028            DatabaseProduct.ACCESS);
029
030    /**
031     * Creates an AccessDialect.
032     *
033     * @param connection Connection
034     */
035    public AccessDialect(Connection connection) throws SQLException {
036        super(connection);
037    }
038
039    public String toUpper(String expr) {
040        return "UCASE(" + expr + ")";
041    }
042
043    public String caseWhenElse(String cond, String thenExpr, String elseExpr) {
044        return "IIF(" + cond + "," + thenExpr + "," + elseExpr + ")";
045    }
046
047    public void quoteDateLiteral(StringBuilder buf, String value) {
048        Date date;
049        try {
050            /*
051             * The format of the 'value' parameter is not certain.
052             * Some JDBC drivers will return a timestamp even though
053             * we ask for a date (access is one of them). We must try to
054             * convert both formats.
055             */
056            date = Date.valueOf(value);
057        } catch (IllegalArgumentException ex) {
058            try {
059                date =
060                    new Date(Timestamp.valueOf(value).getTime());
061            } catch (IllegalArgumentException ex2) {
062                throw new NumberFormatException(
063                    "Illegal DATE literal:  " + value);
064            }
065        }
066        quoteDateLiteral(buf, value, date);
067    }
068
069    protected void quoteDateLiteral(
070        StringBuilder buf,
071        String value,
072        Date date)
073    {
074        // Access accepts #01/23/2008# but not SQL:2003 format.
075        buf.append("#");
076        Calendar calendar = Calendar.getInstance();
077        calendar.setTime(date);
078        buf.append(calendar.get(Calendar.MONTH) + 1);
079        buf.append("/");
080        buf.append(calendar.get(Calendar.DAY_OF_MONTH));
081        buf.append("/");
082        buf.append(calendar.get(Calendar.YEAR));
083        buf.append("#");
084    }
085
086    @Override
087    protected String generateOrderByNulls(
088        String expr,
089        boolean ascending,
090        boolean collateNullsLast)
091    {
092        if (collateNullsLast) {
093            if (ascending) {
094                return "Iif(" + expr + " IS NULL, 1, 0), " + expr + " ASC";
095            } else {
096                return "Iif(" + expr + " IS NULL, 1, 0), " + expr + " DESC";
097            }
098        } else {
099            if (ascending) {
100                return "Iif(" + expr + " IS NULL, 0, 1), " + expr + " ASC";
101            } else {
102                return "Iif(" + expr + " IS NULL, 0, 1), " + expr + " DESC";
103            }
104        }
105    }
106
107    public boolean requiresUnionOrderByExprToBeInSelectClause() {
108        return true;
109    }
110
111    public boolean allowsCountDistinct() {
112        return false;
113    }
114
115    public String generateInline(
116        List<String> columnNames,
117        List<String> columnTypes,
118        List<String[]> valueList)
119    {
120        // Fall back to using the FoodMart 'days' table, because
121        // Access SQL has no way to generate values not from a table.
122        return generateInlineGeneric(
123            columnNames, columnTypes, valueList,
124            " from `days` where `day` = 1", false);
125    }
126}
127
128// End AccessDialect.java