SQLSyntaxErrorException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported

Question

Please have a look at the following code. I am using Apache Derby as an embedded database

public List getDetails(String name)
    {
        List details = new ArrayList();

        name = name.replaceAll("\\s+", "").toUpperCase();
        try
        {
            createConnection();

            PreparedStatement ps = con.prepareStatement("select * from PhoneData where upper(rtrim(NULLIF(names,''))) + upper(rtrim(NULLIF(middleName,''))) +  upper(rtrim(NULLIF(lastName,'')))=?");
            ps.setString(1, name);

            ResultSet rs = ps.executeQuery();

          // System.out.println("First Mobile Number: "+rs.getInt(3));
            while(rs.next())
            {
                details.add(rs.getInt(1));
                details.add(rs.getString(2));
                details.add(rs.getString(3));
                details.add(rs.getString(4));
                details.add(rs.getString(5));
                details.add(rs.getString(6));
                details.add(rs.getString(7));
                details.add(rs.getString(8));
                details.add(rs.getString(9));
                details.add(rs.getString(10));
                details.add(rs.getString(11));
                details.add(rs.getString(12));
                details.add(rs.getString(13));
                details.add(rs.getString(14));
            }

            for(int i=0;i<details.size();i++)
            {
                System.out.println(details.get(i));
            }
            System.out.println("------------Database handler Done------------");
        }
        catch(Exception e)
        {
            e.printStackTrace();
        }
        finally
        {
            closeConnection();
        }

        return details;
    }

This code issues the following error

java.sql.SQLSyntaxErrorException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.Util.generateCsSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.wrapInSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.TransactionResourceImpl.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.ConnectionChild.handleException(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement20.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement30.<init>(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedPreparedStatement40.<init>(Unknown Source)
    at org.apache.derby.jdbc.Driver40.newEmbedPreparedStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at org.apache.derby.impl.jdbc.EmbedConnection.prepareStatement(Unknown Source)
    at normal.DatabaseHandler.getDetails(DatabaseHandler.java:150)
    at normal.UpdateDeleteForm$DetailsLoader.itemStateChanged(UpdateDeleteForm.java:417)
    at javax.swing.JComboBox.fireItemStateChanged(JComboBox.java:1225)
    at javax.swing.JComboBox.selectedItemChanged(JComboBox.java:1282)
    at javax.swing.JComboBox.contentsChanged(JComboBox.java:1329)
    at javax.swing.AbstractListModel.fireContentsChanged(AbstractListModel.java:118)
    at javax.swing.DefaultComboBoxModel.setSelectedItem(DefaultComboBoxModel.java:94)
    at javax.swing.JComboBox.setSelectedItem(JComboBox.java:578)
    at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:624)
    at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.java:835)
    at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(BasicComboPopup.java:499)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
Caused by: java.sql.SQLException: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.impl.jdbc.SQLExceptionFactory.getSQLException(Unknown Source)
    at org.apache.derby.impl.jdbc.SQLExceptionFactory40.wrapArgsForTransportAcrossDRDA(Unknown Source)
    ... 56 more
Caused by: ERROR 42Y95: The '+' operator with a left operand type of 'VARCHAR' and a right operand type of 'VARCHAR' is not supported.
    at org.apache.derby.iapi.error.StandardException.newException(Unknown Source)
    at org.apache.derby.impl.sql.compile.BaseTypeCompiler.resolveArithmeticOperation(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryArithmeticOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.BinaryComparisonOperatorNode.bindExpression(Unknown Source)
    at org.apache.derby.impl.sql.compile.SelectNode.bindExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bindExpressions(Unknown Source)
    at org.apache.derby.impl.sql.compile.DMLStatementNode.bind(Unknown Source)
    at org.apache.derby.impl.sql.compile.CursorNode.bindStatement(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepMinion(Unknown Source)
    at org.apache.derby.impl.sql.GenericStatement.prepare(Unknown Source)
    at org.apache.derby.impl.sql.conn.GenericLanguageConnectionContext.prepareInternalStatement(Unknown Source)
    ... 50 more
Exception in thread "AWT-EventQueue-0" java.lang.IndexOutOfBoundsException: Index: 6, Size: 0
    at java.util.ArrayList.rangeCheck(ArrayList.java:604)
    at java.util.ArrayList.get(ArrayList.java:382)
    at normal.UpdateDeleteForm$DetailsLoader.itemStateChanged(UpdateDeleteForm.java:435)
    at javax.swing.JComboBox.fireItemStateChanged(JComboBox.java:1225)
    at javax.swing.JComboBox.selectedItemChanged(JComboBox.java:1282)
    at javax.swing.JComboBox.contentsChanged(JComboBox.java:1329)
    at javax.swing.AbstractListModel.fireContentsChanged(AbstractListModel.java:118)
    at javax.swing.DefaultComboBoxModel.setSelectedItem(DefaultComboBoxModel.java:94)
    at javax.swing.JComboBox.setSelectedItem(JComboBox.java:578)
    at javax.swing.JComboBox.setSelectedIndex(JComboBox.java:624)
    at javax.swing.plaf.basic.BasicComboPopup$Handler.mouseReleased(BasicComboPopup.java:835)
    at java.awt.AWTEventMulticaster.mouseReleased(AWTEventMulticaster.java:290)
    at java.awt.Component.processMouseEvent(Component.java:6504)
    at javax.swing.JComponent.processMouseEvent(JComponent.java:3321)
    at javax.swing.plaf.basic.BasicComboPopup$1.processMouseEvent(BasicComboPopup.java:499)
    at java.awt.Component.processEvent(Component.java:6269)
    at java.awt.Container.processEvent(Container.java:2229)
    at java.awt.Component.dispatchEventImpl(Component.java:4860)
    at java.awt.Container.dispatchEventImpl(Container.java:2287)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.LightweightDispatcher.retargetMouseEvent(Container.java:4832)
    at java.awt.LightweightDispatcher.processMouseEvent(Container.java:4492)
    at java.awt.LightweightDispatcher.dispatchEvent(Container.java:4422)
    at java.awt.Container.dispatchEventImpl(Container.java:2273)
    at java.awt.Window.dispatchEventImpl(Window.java:2713)
    at java.awt.Component.dispatchEvent(Component.java:4686)
    at java.awt.EventQueue.dispatchEventImpl(EventQueue.java:707)
    at java.awt.EventQueue.access$000(EventQueue.java:101)
    at java.awt.EventQueue$3.run(EventQueue.java:666)
    at java.awt.EventQueue$3.run(EventQueue.java:664)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:87)
    at java.awt.EventQueue$4.run(EventQueue.java:680)
    at java.awt.EventQueue$4.run(EventQueue.java:678)
    at java.security.AccessController.doPrivileged(Native Method)
    at java.security.ProtectionDomain$1.doIntersectionPrivilege(ProtectionDomain.java:76)
    at java.awt.EventQueue.dispatchEvent(EventQueue.java:677)
    at java.awt.EventDispatchThread.pumpOneEventForFilters(EventDispatchThread.java:211)
    at java.awt.EventDispatchThread.pumpEventsForFilter(EventDispatchThread.java:128)
    at java.awt.EventDispatchThread.pumpEventsForHierarchy(EventDispatchThread.java:117)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:113)
    at java.awt.EventDispatchThread.pumpEvents(EventDispatchThread.java:105)
    at java.awt.EventDispatchThread.run(EventDispatchThread.java:90)
BUILD SUCCESSFUL (total time: 7 seconds)

In here, what I am trying to do is, getting the full name, which is stored in the DB as FirstName, MiddleName and LastName.

Example: FirstName - Peter, MiddleName - Metae, LastName - Johnson

Then I am trying to get "PeterMetaeJohnson". That's why I am using the + operator to get combine the values in 3 columns.

Why I am getting this error? This worked in MSSQL Server, but fails in Derby. Please help!

Answer 1

In Derby, String concatenation operator is not + but ||

http://db.apache.org/derby/docs/10.2/ref/rrefsqlj40899.html

Answer 2

According to the Derby documentation ( http://db.apache.org/derby/docs/10.4/ref/rrefsqlj40899.html ), the concatenation operator in Derby is || - like Oracle, so substitute the '+'s with '||'s.