Re: Selecting Varchar range (through JDBC).

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.html#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(at)michvhf(dot)com]
> Sent: Monday, August 12, 2002 8:49 AM
> To: Daryl Beattie
> Cc: pgsql-general(at)postgresql(dot)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;
}