Re: [GENERAL] Selecting Varchar range (through JDBC).

Thanks for your speedy reply Tom.

I will test using psql as you suggested. Hopefully I'll work something out
that way. I'll maybe get back with my results or further questions sometime
over a week from now (I'm going on vacation). I just wanted to say thanks
now for your reply. :)

- Daryl.


> -----Original Message-----
> From: Thomas O'Dowd [mailto:tom@nooper.com]
> Sent: Friday, August 30, 2002 9:47 AM
> To: Daryl Beattie
> Cc: pgsql-general@postgresql.org; pgsql-jdbc@postgresql.org
> Subject: Re: [JDBC] [GENERAL] Selecting Varchar range (through JDBC).
>
>
> Hi Daryl,
>
> Don't ya just love playing with regex :)
>
> If you think the query is corrupt, try turning on print_query in the
> postgresql logs and you can see what the query that the backend is
> receiving is there. Chances are that the driver is working.
>
> If you think your regex is wrong just test it with psql. ie, just try
> the following...
>     select 'Art' ~* '^[a-c]';
>
> Regarding your regex, if you want artist names between A and C, the
> regex would be: sortName ~* '^[a-c]'
>
> The ~* bit is a case insensitive match.
>
> Hope that helps a little,
>
> Tom.
>
> On Fri, 2002-08-30 at 22:17, Daryl Beattie wrote:
> > Dear PostgreSQL people,
> >
> >     Thanks for your help from before, Vince. I didn't get around to
> > attempting to implement that varchar range function until
> last night, and
> > when I did I ran into some problems. Whenever I tried to execute the
> > statement:
> >
> > SELECT ArtistId FROM Artist WHERE SortName ~*
> > '[[.testArtist1.]-[.testArtist2.]].*'
> >
> >     I get the following JDBC error:
> >
> > javax.ejb.FinderException: SQLException while finding Artists:
> > java.sql.SQLException: ERROR:  Invalid regular expression:
> invalid collating
> > element
> >
> >     The SQLException is coming from the database, however,
> it MAY be a
> > JDBC driver issue with it not escaping the statement properly.
> >     My suspicion, however, is that I'm simply not using the regular
> > expressions correctly. Can anybody kindly explain to me how
> to do this
> > correctly? (I am trying to search a varchar range, for
> example, any Artists
> > whose names start with the letters A to C.)
> >     I have attached my Java code to the bottom of this e-mail for
> > reference.
> >     For your convenience, here's the link to the idocs that
> describes
> > regex functionality in PostgreSQL:
> >
> http://www.postgresql.org/idocs/index.php?functions-matching.h
> tml#FUNCTIONS-
> > REGEXP
> >
> > Sincerely,
> >
> >     Daryl.
> >
> > P.S. Some notes:
> >     - I'm using PostgreSQL 7.2.1 on RH Linux 7.2, and the
> latest binary
> > PostgreSQL JDBC driver.
> >     - It doesn't matter what I put in the [. .], it never
> seems to work
> > any better. For example, a good test would be WHERE SortName ~*
> > '[[.A.]-[.C.]].*'
> >     - I tried using a straight Statement-from-a-query-string that I
> > constructed instead of a PreparedStatement (to bypass any
> automatic escaping
> > done by the PreparedStatement), and got the same results.
> >
> >
> > > -----Original Message-----
> > > From: Vince Vielhaber [mailto:vev@michvhf.com]
> > > Sent: Monday, August 12, 2002 8:49 AM
> > > To: Daryl Beattie
> > > Cc: pgsql-general@postgresql.org
> > > Subject: Re: [GENERAL] Selecting Varchar range.
> > >
> > >
> > > On Mon, 12 Aug 2002, Daryl Beattie wrote:
> > >
> > > > Dear PostgreSQL people,
> > > >
> > > >     I didn't find this in the archives or the idocs, so...
> > > >
> > > >     How does one select a varchar range? The meaning of my
> > > question is
> > > > best illustrated by the following completely hypothetical
> > > and totally
> > > > imaginary situation:
> > > >     Let's say I have a table of musical artists:
> > > >
> > > > CREATE TABLE Artists (
> > > >     Id SERIAL PRIMARY KEY,
> > > >     Name VARCHAR(100)
> > > > );
> > > >
> > > >     And I want to eventually display only the artists from
> > > 'Aa' to 'Am'.
> > > > Naturally doing a select where Name like 'A%' will not
> > > return me what I
> > > > want.
> > > >     Any ides on how to do this? [Will "SELECT Name FROM
> > > Artists WHERE
> > > > Name > 'Aa%' AND Name < 'Am%';" work?]
> > > >
> > > >     Bonus question: How do I select artists whose
> names start with a
> > > > number?
> > >
> > > Think REGEX.    A[a-m]%   [0-9]%
> > >
> > > If any of your artists are indies, send them to
> www.unknown-artists.com
> > >
> > > Vince.
> >
> >
> >
> >
> >
> > /**
> >  * Finds all of the Artists whose sort-names fall within a
> particular range,
> >  * and returns them in a Collection. An example of this
> would be to ask for
> >  * all of the artists that are within "Aa" and "Cz", and it
> would return all
> >  * the Artists that start with A, B, and C. Case is ignored
> for the search,
> >  * but <i>be careful</i> because some bands could start
> with numbers!
> >  *
> >  * @param start The start String of the Artists we want to find.
> >  * @param end The end String of the Artists we want to find.
> >  * @return A Collection containing all the Artists whose
> sort-names fall
> >  * within a particular range.
> >  * @throws FinderException If there was a problem finding
> such Artists.
> >  */
> > public Collection
> ejbFindArtistsWhoseSortNamesAreWithin(String start, String
> > end) throws FinderException {
> >     ArrayList artists = new ArrayList(); // All of the Artists.
> >     Connection dbConnection = null;      // The database connection.
> >     PreparedStatement ps = null;         // The
> statement(s) to execute.
> >     ResultSet resultSet = null;          // The results of
> the query.
> >
> >     if ((start.indexOf("-") > 0) || (start.indexOf("[") > 0) ||
> > (start.indexOf("]") > 0) || (end.indexOf("-") > 0) ||
> (end.indexOf("[") > 0)
> > || (end.indexOf("]") > 0)) throw new FinderException("The
> start and end
> > strings for findArtistsWhoseSortNamesAreWithin() cannot
> have '-', '[' or ']'
> > in them.");
> >     if ((start == null) || ("".equals(start)) || (end == null) ||
> > ("".equals(end))) return artists;
> >
> >     // STEP 1: Get the database connection.
> >     try { dbConnection = getConnection(); } catch
> (Exception e) { throw
> > new EJBException(e.getMessage()); }
> >
> >     // STEP 2: Find the Artists.
> >     try {
> >         String queryStr = "SELECT " +
> DatabaseNames.ARTIST_ID + "
> > FROM " +
> >                 DatabaseNames.ARTIST_TABLE + " WHERE " +
> >                 DatabaseNames.ARTIST_SORTNAME + " ~* ?";
> >         ps = dbConnection.prepareStatement(queryStr);
> >         ps.setString(1, "[[." + start + ".]-[." + end +
> ".]].*");
> >         resultSet = ps.executeQuery();
> >         ArtistPK artistPK = null;
> >         while (resultSet.next()) {
> >             artistPK = new ArtistPK();
> >
> > artistPK.setId(resultSet.getInt(DatabaseNames.ARTIST_ID));
> >             artists.add(artistPK);
> >         }
> >     }
> >     catch(SQLException e) {
> >         context.setRollbackOnly();
> >         throw new FinderException("SQLException while finding
> > Artists:\n" + e);
> >     }
> >     finally {
> >         try { closeResultSet(resultSet); } catch (Exception e) {
> > log.warning("Failed to close ResultSet: " + e.getMessage()); }
> >         try { closeStatement(ps); } catch (Exception e) {
> > log.warning("Failed to close Statement: " + e.getMessage()); }
> >         try { closeConnection(dbConnection); } catch
> (Exception e) {
> > log.warning("Failed to close Connection: " + e.getMessage()); }
> >     }
> >
> >     // STEP 3: Return that Collection of ArtistPKs.
> >     return artists;
> > }
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> --
> Thomas O'Dowd. - Nooping - http://nooper.com
> tom@nooper.com - Testing - http://nooper.co.jp/labs
>