Re: PB with the cmp_op when building queries on specific columns with QueryBuilder

This is a multi-part message in MIME format...

------------=_1114584852-17769-9
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit

Michael Strapp wrote:

> Hi Mathieu,
>     I ran into this problem a little while ago, and after some 
> searching in the source code, located the cause.  The QueryBuilder, by 
> default, is not configured for use with any specific type of database 
> - I forget whether a QueryBuilder object takes its settings from the 
> StandardConf.xml, or whether it takes it from private static member 
> variables; I think it might have been the latter.  This is reasonable 
> (though a little annoying), since you can have multiple logical 
> database configurations of differing types defined under the same DODS 
> configuration, and the QueryBuilder object won't automatically know 
> the type of database you'll end up using it with.  The solution is to 
> set the appropriate database configuration, as the XxxQuery objects do:
>  
>   String dbName = mytableDO.get_logicalDBName();
>   String vendor = DODS.getDatabaseManager().logicalDatabaseType(dbName);
>   if (vendor != null) {
>     myQueryBuilder.setDatabaseVendor(vendor);
>   } else {
>     myQueryBuilder.setDatabaseVendor();
>   }
>  
> For most types of queries this turns out to be unnecessary as the 
> defaults the QueryBuilder uses work OK (at least they have with 
> Oracle, which is what we're using), but the settings for the LIKE 
> clause can vary from database to database, and the QueryBuilder 
> defaults don't work for the LIKE clause.
> A more indirect solution than the above would be to create your 
> QueryBuilder object in the following way:
>  
>   QueryBuilder myQueryBuilder = new mytableQuery().getQueryBuilder();
>   myQueryBuilder.resetSelectedFields();
>   myQueryBuilder.select(mytableDO.EntryId);
>  
> Which would use the code in the mytableQuery() constructor to 
> initialize the QueryBuilder settings (although create an unnecessary 
> mytableQuery object in the process).
>  
> Regards,
> Mike
>
>     ----- Original Message -----
>     *From:* Mathieu MANGEOT-NAGATA <mailto:[email protected]>
>     *To:* [email protected] <mailto:[email protected]>
>     *Sent:* Tuesday, April 26, 2005 1:19 PM
>     *Subject:* [dods] PB with the cmp_op when building queries on
>     specific columns with QueryBuilder
>
>     Dear colleagues,
>
>     I'm having some difficulties when trying to build a multi-criteria
>     sql search with the dods QueryBuilder.
>     I am using Enhydra5.1 with the corresponding dods (so I suppose
>     that it is dods 5.1...).
>
>     The SQL I would like to obtain is the following:
>     select mytable.* from mytable where mytable.key='word' and
>     mytable.value like 'a%' and mytable.entryid in (select
>     mytable.entryid from mytable where mytable.key='pos' and
>     mytable.value like 's%');
>
>     First, I need to build the subquery:
>     RDBColumn entryidColumn = mytableDO.EntryId;
>     RDBColumn[] oneColumnArray = new RDBColumn[1];
>     oneColumnArray[0] = entryidColumn;
>     myQueryBuilder = new QueryBuilder(oneColumnArray);
>     myQueryBuilder.addWhere(mytableDO.Key, "pos", QueryBuilder.EQUAL);
>     myQueryBuilder.addWhere(mytableDO.Value, "s",
>     QueryBuilder.CASE_SENSITIVE_STARTS_WITH);
>
>     The first problem is that the queryBuilder does not take into
>     account the cmp_op if it is different from EQUAL (here
>     CASE_SENSITIVE_STARTS_WITH) and if I query specific columns
>     instead of the whole table (like the following main query).
>     The SQL output is the following:
>     select mytable.entryId from mytable WHERE mytable.key = ? AND
>     mytable.value = ?
>     instead of the following:
>     select mytable.entryId from mytable WHERE mytable.key = ? AND
>     mytable.value LIKE ? ESCAPE '?'
>
>     The second problem is when I add the subquery to the main query
>     via an addWhereIn clause, it adds automatically another select:
>     mytableQuery query = new mytableQuery();
>     query.getQueryBuilder().addWhere(mytableDO.Key, "word",
>     QueryBuilder.EQUAL);
>     query.getQueryBuilder().addWhere(mytable.Value, "a",
>     QueryBuilder.CASE_SENSITIVE_STARTS_WITH);
>     query.getQueryBuilder().addWhereIn(entryidColumn, myQueryBuilder);
>
>     Note here that the main query is on the whole table and therefore,
>     I have no pb with the cmp_op. It is taken into account.
>     The SQL output is the following:
>     select mytable.* from mytable WHERE mytable.key = ? AND
>     mytable.value LIKE ? ESCAPE '?' AND mytable.entryId IN ( select
>     mytable.entryId, mytable.entryId from mytable WHERE mytable.key =
>     ? AND mytable.value = ? )
>     instead of the following:
>     select mytable.* from mytable WHERE mytable.key = ? AND
>     mytable.value LIKE ? ESCAPE '?' AND mytable.entryId IN ( select
>     mytable.entryId, from mytable WHERE mytable.key = ? AND
>     mytable.value = ? )
>
>     I found a workaround for the second problem by building the
>     QueryBuilder from Strings only:
>     myQueryBuilder = new QueryBuilder("mytable","entryid");
>
>     But I don't know how to solve the first problem.
>
>     Any help would be greatly appreciated!
>
>     Thanks in advance,
>
>     Mathieu
>
>
>     Mathieu MANGEOT-NAGATA
>     Auxiliaire de recherche CNRS
>     ATILF 44, avenue de la Libération
>     B.P. 30687 - 54063 Nancy Cedex
>     Tel : +33 3 83 96 86 98
>
I think have solved some similiar problem short time ago.
I mean LIKE searching but within easy SQLs only.

  LadyQuery q = new LadyQuery();
  q.getQueryBuilder().setStringMatchDetails("LIKE", "%");
  q.getQueryBuilder().addWhere(LadyDO.Name, "keyword for search" , 
QueryBuilder.CASE_INSENSITIVE_STARTS_WITH);
  q.getDOArray() .....

I hove it helps you.




------------=_1114584852-17769-9
Content-Type: text/plain; name="message.footer"
Content-Disposition: inline; filename="message.footer"
Content-Transfer-Encoding: 8bit


--
You receive this message as a subscriber of the [email protected] mailing list.
To unsubscribe: mailto:[email protected]
For general help: mailto:[email protected]?subject=help
ObjectWeb mailing lists service home page: http://www.objectweb.org/wws

------------=_1114584852-17769-9--