> So I am after a more direct aproach anyone know of one?
**Apologises to everyone for the long email**
Hi Gary,
I had a similiar problem. I needed to use a soundex query on a recordset
stored in the application scope. QofQ didn't cut it so I wrote a java cfx
tag using some free java soundex code to search the query while in memory.
The speed is good and the results are excellent. I have copied the code
below. If you want the class file let me know. An example of how to call
the tag goes like this.
First the query that is already stored in the application scope -:
Select PersonId, FirstName, LastName
from Person
To call the tag you simply use this -:
<cfx_searchQPS query="<my query>" encodeLimit="4" columns="FIRSTNAME"
searchString="Tom"
variable="PersonIds" returnCol="PersonId">
The tag writes a variable called PersonIds into the variables scope of the
calling page with a list of values for the PersonId column in the query
that match the soundex search string Tom in the column 'FIRSTNAME'. QofQ
can then be used to pull the records from the query that match the soundex
search ie
select * from <my query>
where PersonId in (#personids#)
Hope this makes sense. If you want the class file for this tag send me an
email. Anyway hope this helps,
Rod
/*
* File: searchQPS.java
* Date: 03 March 2003 18:57
*
* @author Rod Higgins
* @version 1.01
*/
import com.allaire.cfx.*;
import java.io.*;
import java.lang.String.*;
public class searchQPS implements CustomTag {
public static boolean isVowel (char charToTest) {
if (charToTest == 'A') return true;
if (charToTest == 'E') return true;
if (charToTest == 'I') return true;
if (charToTest == 'O') return true;
if (charToTest == 'U') return true;
return false;
}
public StringBuffer Metafon4(String InputString) {
StringBuffer sb = new StringBuffer("");
String EncodeMe = new String(InputString.toUpperCase());
int encodeLimit = 4;
int index;
int lastidx = 0;
int offset = 0;
int length = EncodeMe.length();
lastidx = length - 1;
for (index = 0; offset < encodeLimit; index++)
{
if (index > lastidx)
break;
if (index > 0) {
if (EncodeMe.charAt(index) == EncodeMe.charAt(index-1))
continue;
}
if (EncodeMe.charAt(index) == 'A' ) {
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'E' ) {
sb.insert(offset, 'E');
offset++;
index += 1;
continue;
}
}
if (index == 0) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
}
continue;
}
if (EncodeMe.charAt(index) == 'B' ) {
if (index < lastidx) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
else {
if (EncodeMe.charAt(index-1) != 'M' ) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
}
}
if (EncodeMe.charAt(index) == 'C' ) {
if (index > 0) {
if (EncodeMe.regionMatches(index-1, "SCI", 0, 3)) {
index += 1;
continue;
}
if (EncodeMe.regionMatches(index-1, "SCE", 0, 3)) {
index += 1;
continue;
}
if (EncodeMe.regionMatches(index-1, "SCY", 0, 3)) {
index += 1;
continue;
}
}
if (index+1 < lastidx) {
if (EncodeMe.regionMatches(index+1, "IA", 0, 2)) {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
}
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'H') {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
if (EncodeMe.charAt(index+1) == 'E') {
sb.insert(offset, 'S');
offset++;
index += 1;
continue;
}
if (EncodeMe.charAt(index+1) == 'I') {
sb.insert(offset, 'S');
offset++;
index += 1;
continue;
}
if (EncodeMe.charAt(index+1) == 'Y') {
sb.insert(offset, 'S');
offset++;
index += 1;
continue;
}
}
sb.insert(offset, 'K');
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'D' ) {
if (index+1 < lastidx) {
if (EncodeMe.regionMatches(index+1, "GE", 0, 2)) {
sb.insert(offset, 'J');
offset++;
index += 2;
continue;
}
if (EncodeMe.regionMatches(index+1, "GI", 0, 2)) {
sb.insert(offset, 'J');
offset++;
index += 2;
continue;
}
if (EncodeMe.regionMatches(index+1, "GY", 0, 2)) {
sb.insert(offset, 'J');
offset++;
index += 2;
continue;
}
}
sb.insert(offset, 'T');
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'E' ) {
if (index == 0) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
}
continue;
}
if (EncodeMe.charAt(index) == 'F' ) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'G' ) {
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'H') {
if (index+1 < lastidx) {
if (isVowel(EncodeMe.charAt(index+2))) {
sb.insert(offset, 'K');
index += 1;
offset++;
continue;
}
index += 1;
continue;
}
}
if (EncodeMe.charAt(index+1) == 'N') {
sb.insert(offset, 'N');
offset++;
index += 1;
continue;
}
if (index > 0) {
if (EncodeMe.charAt(index-1) == 'G') {
sb.insert(offset, 'K');
offset++;
continue;
}
}
if (EncodeMe.charAt(index+1) == 'I') {
sb.insert(offset, 'J');
offset++;
continue;
}
if (EncodeMe.charAt(index+1) == 'E') {
sb.insert(offset, 'J');
offset++;
continue;
}
if (EncodeMe.charAt(index+1) == 'Y') {
sb.insert(offset, 'J');
offset++;
continue;
}
if (EncodeMe.regionMatches(index-1, "DGE", 0, 3))
continue;
if (EncodeMe.regionMatches(index-1, "DGI", 0, 3))
continue;
if (EncodeMe.regionMatches(index-1, "DGY", 0, 3))
continue;
}
sb.insert(offset, 'K');
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'H' ) {
if (index > 0) {
if (isVowel(EncodeMe.charAt(index-1))) {
if (index < lastidx) {
if (isVowel(EncodeMe.charAt(index+1))) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
else continue;
}
else continue;
}
}
else {
if (!(isVowel(EncodeMe.charAt(index+1))))
continue;
}
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'I' ) {
if (index == 0) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
}
continue;
}
if (EncodeMe.charAt(index) == 'J') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'K' ) {
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'N') {
sb.insert(offset, 'N');
offset++;
index += 1;
continue;
}
}
if (index > 0) {
if (EncodeMe.charAt(index-1) == 'C')
continue;
}
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'L') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'M') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'N') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'O' ) {
if (index == 0) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
}
continue;
}
if (EncodeMe.charAt(index) == 'P' ) {
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'H') {
sb.insert(offset, 'F');
offset++;
index += 1;
continue;
}
if (EncodeMe.charAt(index+1) == 'N') {
sb.insert(offset, 'N');
offset++;
index += 1;
continue;
}
}
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'Q') {
sb.insert(offset, 'K');
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'R') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'S' ) {
if (index+2 < lastidx) {
if (EncodeMe.regionMatches(index, "SCHE", 0, 4)) {
sb.append("SK");
offset += 2;
index += 2;
System.out.print (" Encoded String = "+ sb);
System.out.print(" \n");
continue;
}
if (EncodeMe.regionMatches(index, "SCHI", 0, 4)) {
sb.append("SK");
offset += 2;
index += 2;
System.out.print (" Encoded String = "+ sb);
System.out.print(" \n");
continue;
}
if (EncodeMe.regionMatches(index, "SCHO", 0, 4)) {
sb.append("SK");
offset += 2;
index += 2;
System.out.print (" Encoded String = "+ sb);
System.out.print(" \n");
continue;
}
}
if (index+1 < lastidx) {
if (EncodeMe.regionMatches(index, "SCH", 0, 3)) {
sb.insert(offset, 'X');
offset++;
index += 2;
continue;
}
if (EncodeMe.regionMatches(index+1, "IA", 0, 2)) {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
if (EncodeMe.regionMatches(index+1, "IO", 0, 2)) {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
}
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'H') {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
}
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'T' ) {
if (index > 0) {
if (index+1 < lastidx) {
if (EncodeMe.regionMatches(index+1, "IA", 0, 2)) {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
if (EncodeMe.regionMatches(index+1, "IO", 0, 2)) {
sb.insert(offset, 'X');
offset++;
index += 1;
continue;
}
}
}
if (index+1 < lastidx) {
if (EncodeMe.regionMatches(index+1, "CH", 0, 2)) {
continue;
}
}
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'H') {
sb.insert(offset, '0');
offset++;
index += 1;
continue;
}
}
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'U' ) {
if (index == 0) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
}
continue;
}
if (EncodeMe.charAt(index) == 'V') {
sb.insert(offset, 'F');
offset++;
continue;
}
if (EncodeMe.charAt(index) == 'W' ) {
if (index < lastidx) {
if (EncodeMe.charAt(index+1) == 'R') {
sb.insert(offset, 'R');
offset++;
index += 1;
continue;
}
if (EncodeMe.charAt(index+1) == 'H') {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
index += 1;
continue;
}
if (isVowel(EncodeMe.charAt(index+1))) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
}
continue;
}
if (EncodeMe.charAt(index) == 'X' ) {
if (index == 0) {
sb.insert(offset, 'S');
offset++;
continue;
}
sb.append("KS");
offset += 2;
continue;
}
if (EncodeMe.charAt(index) == 'Y' ) {
if (index < lastidx) {
if (isVowel(EncodeMe.charAt(index+1))) {
sb.insert(offset, EncodeMe.charAt(index));
offset++;
continue;
}
}
continue;
}
if (EncodeMe.charAt(index) == 'Z') {
sb.insert(offset, 'S');
offset++;
continue;
}
}
return sb;
}
public void processRequest(com.allaire.cfx.Request request,
com.allaire.cfx.Response response) throws java.lang.Exception {
if(!request.attributeExists("searchString")) {
throw new Exception("Missing searchString parameter !!! You
must pass a search string attribute.");
}
if(!request.attributeExists("query")) {
throw new Exception("Missing query parameter !!! You must pass
the name of a valid query in memory.");
}
if(!request.attributeExists("columns")) {
throw new Exception("Missing columns parameter !!! You must
pass the list of columns in the query to search through.");
}
if(!request.attributeExists("Variable")) {
throw new Exception("Missing variable parameter !!! You must
pass a variable name, the variable will contain the returned id's.");
}
if(!request.attributeExists("ReturnCol")) {
throw new Exception("Missing ReturnCol parameter !!! You must
pass a column name to be used to return the record id of matched
records.");
}
int encodeLimit = 8;
if(request.attributeExists("encodeLimit")) {
encodeLimit =
Integer.parseInt(request.getAttribute("encodeLimit"));
}
String searchString = request.getAttribute("searchString");
String strCols = request.getAttribute("Columns");
response.writeDebug("searchString phonetic code -: " +
Metafon4(searchString).toString() + "<br>");
// grab query details
Query query = request.getQuery();
int i = query.getRowCount();
String columns[] = query.getColumns();
String recList = "";
String returnList = "";
// find which column to grab data from to write to return list
int returnCol;
for(int col = 1; col < columns.length + 1; col++) {
if
(columns[col].equalsIgnoreCase(request.getAttribute("ReturnCol"))) {
returnCol = col;
}
}
// now loop through columns in query and record items in a string
list
for(int col2 = 1; col2 < columns.length + 1; col2++) {
if(strCols.indexOf(columns[col2 - 1]) != -1){
for(int row = 1; row < i + 1; row++){
response.writeDebug("Row -: " + Integer.toString(row)
+ " Col -: " + Integer.toString(col2) + " Data -: " +
Metafon4(query.getData(row, col2)).toString() + "<br>");
if (Metafon4(query.getData(row,
col2)).toString().equalsIgnoreCase(Metafon4(searchString).toString())) {
// write record to string list
recList = recList + query.getData(row, returnCol)
+ ",";
}
}
}
}
if (recList.length() > 0) {
response.writeDebug("Raw id list -: " + recList);
returnList = recList.substring(0, recList.length()-1);
response.writeDebug("returned id list -: " + returnList);
}
// Return id's
response.setVariable(request.getAttribute("Variable"),
returnList);
}
}
---
You are currently subscribed to cfaussie as: [EMAIL PROTECTED]
To unsubscribe send a blank email to [EMAIL PROTECTED]
MXDU2004 + Macromedia DevCon AsiaPac + Sydney, Australia
http://www.mxdu.com/ + 24-25 February, 2004
