Hey Chris, On 5/12/12 11:25 AM, "Mattmann, Chris A (388J)" <[email protected]> wrote:
>Hey Mike, > >On May 11, 2012, at 6:07 AM, Cayanan, Michael D (388J) wrote: > >>>> >>>> org.apache.oodt.cas.filemgr.structs.exceptions.CatalogException: >>>>Failed >>>> to perform complex query : You have an error in your SQL syntax; check >>>> the manual that corresponds to your MySQL server version for the right >>>> syntax to use near 'INTERSECT (SELECT DISTINCT product_id FROM >>>> L0a_Radar_metadata WHERE element_id =' at line 1 >>>> at >>>> >>>>org.apache.oodt.cas.filemgr.system.XmlRpcFileManagerClient.complexQuery >>>>(X >>>> mlRpcFileManagerClient.java:958) >>>> at >>>> >>>>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.j >>>>av >>>> a:251) >>>> at >>>>org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241) >>> >>> Just out of curiosity, is that correct ISO 8601 date/time format? Looks >>> like a partial one, missing the timezone do you think that might >>> affect ir? >> >> I talked with Rishi regarding this and he recommended that the date and >> time be split when performing a query. Reason being is that the query >>tool >> blows up when trying to compare datetime values. He mentioned that he >> tried querying against ISO 8601 date/time values before and it didn't >>work >> for him and the way around it was to split it up. I think behind the >> scenes, the query tool is actually doing an ascii comparison, which >>might >> be why the tool might be having performance issues? > >Gotcha, that might help, yes. I was thinking: what is your repository >manager, >and catalog combination? If you are using e.g., a DataSourceCatalog, >with the XMLRepositoryManager, you'll need to turn on the quoteFields >option in the filemgr.properties for the DataSourceCatalog. This is >because, >in these scenarios, the identifier for elementIds is a string, compared >to a >number (which would be the case if you used the >DataSourceRepositoryManager -- >the short answer there is don't, it's not as well maintained as the XML >one). My repository manager and catalog combination is defined as follows in the filemgr.properties: filemgr.repository.factory=org.apache.oodt.cas.filemgr.repository.XMLReposi toryManagerFactory filemgr.catalog.factory=org.apache.oodt.cas.filemgr.catalog.DataSourceCatal ogFactory org.apache.oodt.cas.filemgr.catalog.datasource.quoteFields=true > >> >>> >>>> >>>> I tried surrounding the entire condition with quotes, but still no >>>>luck: >>>> >>>> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>>> L0a_Radar WHERE "RangeBeginningDate>'2007-01-01' AND >>>> RangeBeginningTime>'12:00:00.000Z'"" >>>> Ambiguous output redirect. >>>> >>>> I'm assuming this is a syntax thing, although I don't know what the >>>> tool is expecting. >>> >>> Did you check the code in SVN? >> >> I'm running 0.3 of the code. Does the trunk fix this? I have the code >> checked out onto my local machine. I can certainly build the trunk and >>see >> if I get the same results. > >I think there is a fix for something similar to this in the trunk (as >bfoster mentioned), >but thinking about this more, I bet you're having the quoteFields >problem, per >above. Scope it out and let me know. Based on the properties specified above, the quoteFields option is turned on. So I think the properties are set correctly, no? It'll be interesting to see if Brian was able to find an error in the policy files that I sent in the previous e-mail. > >> >>> >>>> >>>> My second issue that I'm running into is in regards to querying of >>>> dates. I tried the following query below and got the following output: >>>> >>>> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>>> L0a_Radar WHERE RangeBeginningDate>'2007-03-02'" >>>> log4j:WARN No appenders could be found for logger >>>> (org.apache.commons.httpclient.HttpClient). >>>> log4j:WARN Please initialize the log4j system properly. >>>> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig >>>> for more info. >>>> Exception in thread "main" java.lang.StringIndexOutOfBoundsException: >>>> String index out of range: -1 >>>> at >>>> >>>>java.lang.AbstractStringBuilder.substring(AbstractStringBuilder.java:88 >>>>1) >>>> at java.lang.StringBuffer.substring(StringBuffer.java:416) >>>> at >>>> >>>>org.apache.oodt.cas.filemgr.tools.QueryTool.performSqlQuery(QueryTool.j >>>>av >>>> a:255) >>>> at >>>>org.apache.oodt.cas.filemgr.tools.QueryTool.main(QueryTool.java:241) >>>> >>>> For this particular product, I have 1 product in my catalog where the >>>> RangeBeginningDate is equal to '2007-03-01'. Not sure if that factors >>>> into why an exception is being thrown here. When I use an earlier date >>>> on my query, the tool returns a result as expected: >>>> >>>> ./query_tool --url http://localhost:9000 --sql -query "SELECT * FROM >>>> L0a_Radar WHERE RangeBeginningDate>'2007-01-01'" >>>> log4j:WARN No appenders could be found for logger >>>> (org.apache.commons.httpclient.HttpClient). >>>> log4j:WARN Please initialize the log4j system properly. >>>> log4j:WARN See http://logging.apache.org/log4j/1.2/faq.html#noconfig >>>> for more info. >>>> >>>> >>>>/Users/mcayanan/smap/staging,2007-03-01,23:30:25.000Z,314,L0a_Radar,V20 >>>>51 >>>> >>>>7SGS0706023302501.VCD,V20517SGS0706023302501.VCD,2012-05-08T14:27:59.38 >>>>5- >>>> 07:00,L0a_Radar,23:30:25.000Z,2007-03-01 >>> >>> Interesting! Did you scope the code to see if there's a RangeQuery >>>issue? >>> >>> Feel free to file a bug and would love you to investigate! >> >> I haven't dived into the code, but will certainly do this as SMAP will >> need these capabilities. I will file a bug if it turns out that this is >> indeed a bug. > >Great Mike, thanks. > >Cheers, >Chris Thanks, Mike > >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Chris Mattmann, Ph.D. >Senior Computer Scientist >NASA Jet Propulsion Laboratory Pasadena, CA 91109 USA >Office: 171-266B, Mailstop: 171-246 >Email: [email protected] >WWW: http://sunset.usc.edu/~mattmann/ >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >Adjunct Assistant Professor, Computer Science Department >University of Southern California, Los Angeles, CA 90089 USA >++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ >
