[thelist] CF / SQL problem

You know, the verity search engine in CF works pretty well... have you 
tried it?

instead of going through hell trying to create the right sql - you can 
create a collection and provide full text searching on it....

I know its not the way you are going with this... but its an option.

good luck.

-w

At 07:55 AM 12/22/99 -0800, you wrote:


>Thanks for all the responses.  Instead fo cluttering up everyone's inbox 
>with a
>response to each one, here are my replies to the very issues:
>
>1)  Concatenating Strings:  It seems like I've had problems using string
>concatentation in CF in the past, so I went with the Insert function 
>instead.  I
>don't know that concatenating the string is "brute force", becuase I'm
>essentially doing the same thing, but with more typing and possibly 
>unnecessary
>complexity.
>2) Creation of the string itself [ Len function and debugging ].  Len 
>points to
>the last character, yes.  But, insert starts inserting the string after that
>character.  So, if you want to insert a string to the beginning of another
>string, you would start at 0, and not 1.  Also, I have been outputting the
>generated string to the screen, but neglected to include that code in my
>question. The outputted string looks correct.
>3) The use of the wildcard:  I've tried it without the wildcard and 
>testing for
>equality (=) instead of comparing with LIKE.  Same error either way.
>4) The #department# field:  It is from a form, and I probably should have used
>#Form.department#, but all of my other queries (zip code, state, etc.) have
>worked without using "Form." in it.  I will try it that way, though, just to
>make sure.  Nope, didn't change any results.
>4)  The weirdest part:  If I take the string generated (copied and pasted from
>the debug output) and go back and put it into the the query instead, it works
>fine.  Here's what I mean:
>
>-- I run the app putting in "Internal Audit" into the Department field.
>-- The query string is outputted at the top of the screen (before the error is
>generated) [formatted here for readability]:
>      SELECT * FROM mailcodeTB, divisionTB, stateTB, sacramentoTB
>      WHERE mailcodeTB.divisionID = divisionTB.divisionID AND 
> mailcodeTB.stateID
>= stateTB.stateID
>      AND mailcodeTB.sacID = sacramentoTB.sacID
>      AND mailcodeTB.department LIKE 'Internal Audit%' ORDER BY mailcode
>-- I copy and pasted that into my code as the query (instead of using the
>qryString variable).
>-- I re-run the application and it works just fine.
>
>So, now you see why I'm stumped.  I may just have to end up providing a
>drop-down list of departments based on our company directory.   However, that
>list of
>departments may not be up-to-date as departments get created and mailcodes are
>generated.
>
>
>If there are any other ideas, I'd be extremely grateful.
>
>Scott
>
>
>
>
>
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>: From: rudy limeback <r937@interlog.com>
>:
>: >   <cfset qryString = Insert(" AND mailcodeTB.department LIKE
>: >   '#department#%'", qryString, Len(qryString))>
>:
>: no jeff, that's not an extraneous %, that's the wildcard character
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>um, i guess i musta forget to include my obligatory "i'm just a sql hack"
>disclaimer to that response.  thanks for correcting me though cause now i
>know what that's all about.
>
>well, kinda.  care to expand on precisely what the wildcard character is
>used for?
>
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>: scott, i think what you are trying to do with the insert function is slick
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>i agree.  i've seen dynamic sql statements with the logic embedded in the
>cfquery, but never building the sql in a variable then passing that to the
>cfquery.  it's genius.
>
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>: i would've done it the old fashioned, dare i say brute force, way, by
>: concatenating the to-be-appended bit to the original string, something
>like
>:
>:    <cfset qryString = "#qryString#" + " AND et cetera">
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>actually, you concatenate strings in cf with an ampersand.
>
><cfset qryString = #qryString# & " AND et cetera">
>
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>: i *think* what's happening is that the blank which is at the front of the
>: appended string is overlaying the last character of the original string,
>: the D at the end of sacramentoTB.sacID -- hence, syntax error, ain't gots
>: no column called sacI
>:
>: Len(qryString) points to the last character, and that's where you are
>: "inserting"
>:
>: you can fix the problem by adding a blank to the end of the original
>string
>:
>: either that, or specify Len(qryString)+1 for the position where to insert
>: the append string
>:~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
>
>i gotta agree with this analysis, although writing the string to the screen
>would certainly help debug this.
>
>good luck,
>
>
>
>
>
>_______________________________________________________
>unsubscribe+options: http://lists.evolt.org/mailman/listinfo/thelist
>tip harvester: http://lists.evolt.org/harvest/
>email archive: http://lists.evolt.org/archive/
>http://evolt.org/  Workers of the Web, evolt !

____________________
walker fenton
http://www.sdproductions.com
303.898.7849