Mailing-List: contact derby-user-help@db.apache.org; run by ezmlm
Precedence: bulk
Reply-To: "Derby Discussion" <derby-user@db.apache.org>
Received-SPF: pass (nike.apache.org: domain of rick.hillegas@oracle.com
 designates 141.146.126.69 as permitted sender)
Message-ID: <534FE1D1.5020001@oracle.com>
Date: Thu, 17 Apr 2014 07:14:41 -0700
From: Rick Hillegas <rick.hillegas@oracle.com>
User-Agent: Mozilla/5.0 (Macintosh; U; Intel Mac OS X 10.7; en-US;
 rv:1.9.2.18) Gecko/20110616 Thunderbird/3.1.11
MIME-Version: 1.0
To: derby-user@db.apache.org
Subject: Re: --DERBY-PROPERTIES throws error
References: <AE4F7C83-8AEE-4A1B-928C-E6EDEECBCAF7@HoudiniESQ.com>
In-Reply-To: <AE4F7C83-8AEE-4A1B-928C-E6EDEECBCAF7@HoudiniESQ.com>
Content-Type: multipart/mixed;
 boundary="------------060305020404010602030302"

This is a multi-part message in MIME format.
--------------060305020404010602030302
Content-Type: text/plain; charset=windows-1252; format=flowed
Content-Transfer-Encoding: 8bit

Hi Frank,

I am unable to reproduce the problem you are seeing when I run the 
following script using Derby 10.10.1.1:

connect 'jdbc:derby:memory:db;create=true';

create table attachment( guid int, file_name varchar( 10 ) );
create index ATTACHMENT_LINKNAMES_INDEX on attachment( guid, file_name );

SELECT
attachment.guid,  attachment.file_name
FROM
attachment
--DERBY-PROPERTIES index = ATTACHMENT_LINKNAMES_INDEX
;


The error message you're seeing is raised by the parser. I have 
extracted the relevant method from the 10.10.2.0 parser into the 
attached zzz.java program. I am unable to reproduce the problem you're 
seeing when I run that program on your text. Try setting the PROPS field 
in that program equal to what follows "--DERBY-PROPERTIES" in your query 
and see if you can tickle the program into failing.

Hope this helps,
-Rick

On 4/11/14 9:33 AM, Frank Rivera wrote:
>
> We have a simple query using LIKE but is very slow.
> We need to tell the optimizer which index to use but the override 
> DERBY-PROPERTIES statement returns the following error.
>
> *Invalid syntax for optimizer overrides. *The syntax should be -- 
> DERBY-PROPERTIES propertyName = value [, propertyName = value]*
>
> Here is the SQL. The index does exist.
>
> *SELECT *
> esq.attachment.guid, esq.attachment.file_name
> *FROM *
> esq.attachment
> *--DERBY-PROPERTIES *index = ATTACHMENT_LINKNAMES_INDEX
> *where
> *(
> esq.attachment.file_name  LIKE �%drisco%'
>    OR esq.attachment.file_index_name  LIKE �%drisco%'
> )
> AND
> (
>         esq.attachment.link_guid IS NOT NULL
>         AND
>         esq.attachment.link_table='mat'
>         AND
>         esq.attachment.link_guid IN (
>                 SELECT esq.matter.guid
>                 FROM esq.matter
>                 WHERE
>                 esq.matter.guid=esq.attachment.link_guid
>                 AND (
> ( esq.matter.grp_guid=0 OR esq.matter.grp_guid IS NULL ) OR ( 
> esq.matter.grp_guid>0 AND esq.matter.grp_guid IN ( 2,1,6,4,3 ) )
> )
>
>         )
> ) ;
>
>
>
> Sincerely,
>
> Frank Rivera
> LogicBit Software - Research Triangle Park, North Carolina
>
> Product Brochures http://houdiniesq.com/HoudiniEsq_2014BrochureProduct.pdf
> Product Testimonialshttp://houdiniesq.com/HoudiniEsq_2014BrochureShow.pdf
>
>   Support  919.238.7024Corporate  408.213.8138Sales  888.366.2280
>
> ***HoudiniEsq � Legal Practice Management Suite *http://HoudiniEsq.com 
> <http://houdiniesq.com/>
>
>   Feature Rich web-based Legal Practice Management for today's cutting 
> edge Law Firms
>   Any Browser, Any OS, Anytime, Anywhere, Any size organization, SaaS 
> or On-premise
>
> Simple & Simplicity are not the same thing. *"Simple", as it turns 
> out, simply isn't good enough.*
> -----------------------------------------------------------------------------------------------------------------
> *NOTICE*: The sender does not accept liability for any errors 
> or omissions in the contents of this message which arise as
> a result of e-mail transmission.  This e-mail and any files 
> transmitted with it are confidential and are intended solely for
> the use of the individual or entity to which they are addressed. It is 
> not an offer or acceptance, and it is not intended to
> be all or part of an agreement.  This communication may 
> contain material protected by the attorney-client privilege.
> Original material created by the author may be protected by US 
> Copyright law, 17 U.S.C. � 101 et seq. This communication
> constitutes an electronic communication within the meaning of the 
> Electronic Communications Privacy Act, 18 U.S.C.
> � 2510, and its disclosure is strictly limited to the recipient 
> intended by the sender of this message.   If you are not the
> intended recipient or the person responsible for delivering the e-mail 
> to the intended recipient, be advised that you have
> received this e-mail in error and that any review, use, dissemination, 
> forwarding, printing, or copying of this e-mail is strictly
> prohibited. If you have received this e-mail in error, please notify 
> the sender immediately by return e-mail and delete this email.
>


--------------060305020404010602030302
Content-Type: text/plain; x-mac-type="54455854"; x-mac-creator="454D4178";
 name="zzz.java"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="zzz.java"

import java.util.StringTokenizer;

import org.apache.derby.iapi.util.StringUtil;

public class zzz
{
    private static  final   String  PROPS = " index = ATTACHMENT_LINKNAMES_INDEX";
    
	private static final String SINGLEQUOTES = "\'\'";
	private static final String DOUBLEQUOTES = "\"\"";

    public  static  void    main( String... args ) throws Exception
    {
		StringTokenizer commaSeparatedProperties = new StringTokenizer(PROPS,",");
		while (commaSeparatedProperties.hasMoreTokens()) {
			//Now verify that tokens delimited by ,s follow propertyName=value pattern
			String currentProperty = commaSeparatedProperties.nextToken();
			StringTokenizer equalOperatorSeparatedProperty = new StringTokenizer(currentProperty,"=", true);
			if (equalOperatorSeparatedProperty.countTokens() != 3)
            {
                System.out.println( "Could not find 3 tokens" );
                System.exit( 1 );
            }
			else {
				String key = equalOperatorSeparatedProperty.nextToken().trim();
				if (!equalOperatorSeparatedProperty.nextToken().equals("="))
                {
                    System.out.println( "Next token is not =" );
                    System.exit( 1 );
                }
				String value = equalOperatorSeparatedProperty.nextToken().trim();

                if ( value.length() > 64000 )
                {
                    System.out.println( "Value too long" );
                    System.exit( 1 );
                }
				/* Trim off the leading and trailing ', and compress all '' to ' */
				if (value.startsWith("'") && value.endsWith("'"))
					value = StringUtil.compressQuotes(value.substring(1, value.length() - 1), SINGLEQUOTES);
				/* Trim off the leading and trailing ", and compress all "" to " */
				else if (value.startsWith("\"") && value.endsWith("\""))
					value = StringUtil.compressQuotes(value.substring(1, value.length() - 1), DOUBLEQUOTES);
				else 
					value = value.toUpperCase();
            }
        }
    }
        
}

--------------060305020404010602030302--