Re: SQL Madness
This WebDNA talk-list message is from 2005
It keeps the original formatting.
numero = 62668
interpreted = N
texte = Just a thought. I wonder if the '%' in combination with a integer value for [descriptionvar] is getting [unurl]'d somewhere in the parsing process. For example: LIKE '%541%' is getting changed to LIKE 'T1%' before being sent to the SQL server. You may want to experiment with the [URL] context. Perhaps try: LIKE '[URL]%[descriptionvar]%[/URL]' -----Original Message----- From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of Alex McCombie Sent: Wednesday, July 27, 2005 9:44 AM To: WebDNA Talk Subject: SQL Madness Trying again. It appears through our best testing that the LIKE command is not working as should and is only problematic with WebDNA sending it over. Scott can you confirm, deny, or comment on this below. Thanks Alex Windows EXE version 6.0 Have an ODBC setup for this to MSSQL which up until now has worked wonderfully (well at least I thought so). Here is the dilemma. While trying to do a 'LIKE' search on any field (varchar or integer) the results are erratic when searching on anything that is number (integer) related. For example: [SQL dsn=SOMEDSN&username=SOMEUSERNAME&password=SOMEPASS &statement=SELECT * from tickets where description LIKE '%[descriptionvar]%' ORDER BY priority] [founditems] [title]
[/founditems] [/sql] Works great until you search on something like 54. So a record that has the phrase: "We ordered item number 5437. This item should be delivered promptly" In the description field (which is a varchar field) Searching 54 Returns the proper result. However, change that search string to 543 and nothing. 5437, nothing. This is a simplistic example but I have tested this now on varchar fields, integer, and text fields. It appears that anything over 1 or 2 chars in your search string (if they are numbers) sends back 0 results. If you search on 1 number I am getting results for records that do not even have the number in use. :_( Of course running these simple searches through query analyzer works like a charm, as does asp pages on the same server so I am 100% certain this is a result of WebDNA communicating with MSSQL. Like I said, the interaction between the 2 has been flawless and VERY productive -- until this. Has ANYONE seen erratic results when dealing with LIKE searches and if so does anyone have any advice. I am about ready to go postal. This started as a fairly complicated search and after many hours of trying to 'fix it' reducing it to the most simple of searches showed that there is no fixing erratic results on a single variable search. Help! Alex -- Alex J McCombie Adventure Skies Interactive (ASI) Owner Box 124 888/892.6379 MartVille, NY 13111 Alex@Adventureskies.com Interface Designer WebDNA Programmer Database Designer ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list
. To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
Associated Messages, from the most recent to the oldest:
Just a thought. I wonder if the '%' in combination with a integer value for [descriptionvar] is getting [unurl]'d somewhere in the parsing process. For example: LIKE '%541%' is getting changed to LIKE 'T1%' before being sent to the SQL server. You may want to experiment with the [url] context. Perhaps try: LIKE '[url]%[descriptionvar]%[/URL]' -----Original Message----- From: WebDNA Talk [mailto:WebDNA-Talk@talk.smithmicro.com]On Behalf Of Alex McCombie Sent: Wednesday, July 27, 2005 9:44 AM To: WebDNA Talk Subject: SQL Madness Trying again. It appears through our best testing that the LIKE command is not working as should and is only problematic with WebDNA sending it over. Scott can you confirm, deny, or comment on this below. Thanks Alex Windows EXE version 6.0 Have an ODBC setup for this to MSSQL which up until now has worked wonderfully (well at least I thought so). Here is the dilemma. While trying to do a 'LIKE' search on any field (varchar or integer) the results are erratic when searching on anything that is number (integer) related. For example: [SQL dsn=SOMEDSN&username=SOMEUSERNAME&password=SOMEPASS &statement=SELECT * from tickets where description LIKE '%[descriptionvar]%' ORDER BY priority] [founditems] [title]
[/founditems] [/sql] Works great until you search on something like 54. So a record that has the phrase: "We ordered item number 5437. This item should be delivered promptly" In the description field (which is a varchar field) Searching 54 Returns the proper result. However, change that search string to 543 and nothing. 5437, nothing. This is a simplistic example but I have tested this now on varchar fields, integer, and text fields. It appears that anything over 1 or 2 chars in your search string (if they are numbers) sends back 0 results. If you search on 1 number I am getting results for records that do not even have the number in use. :_( Of course running these simple searches through query analyzer works like a charm, as does asp pages on the same server so I am 100% certain this is a result of WebDNA communicating with MSSQL. Like I said, the interaction between the 2 has been flawless and VERY productive -- until this. Has ANYONE seen erratic results when dealing with LIKE searches and if so does anyone have any advice. I am about ready to go postal. This started as a fairly complicated search and after many hours of trying to 'fix it' reducing it to the most simple of searches showed that there is no fixing erratic results on a single variable search. Help! Alex -- Alex J McCombie Adventure Skies Interactive (ASI) Owner Box 124 888/892.6379 MartVille, NY 13111 Alex@Adventureskies.com Interface Designer WebDNA Programmer Database Designer ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/ ------------------------------------------------------------- This message is sent to you because you are subscribed to the mailing list . To unsubscribe, E-mail to: To switch to the DIGEST mode, E-mail to Web Archive of this list is at: http://webdna.smithmicro.com/
"Scott Anderson"
DOWNLOAD WEBDNA NOW!
Top Articles:
Talk List
The WebDNA community talk-list is the best place to get some help: several hundred extremely proficient programmers with an excellent knowledge of WebDNA and an excellent spirit will deliver all the tips and tricks you can imagine...
Related Readings:
PSC recommends what date format yr 2000??? (1997)
Interfacing WebMerchant to www.fedex.com (1997)
If Empty ? (1997)
Carrying Forward (1998)
Counting records (2000)
Carriage Returns on mail receipt (2002)
[WebDNA] a major shift in strategy? (2008)
getting images' width/height (1998)
[/application] error? (1997)
searches with dash, period etc. (2000)
Emailer setup (1997)
Question (1997)
Odd Cart Behavior (1997)
FileMaker and WebCat (1999)
mod_webcatalog2.so (2003)
[WebDNA] [BULK] which of these tags exist in 7.0 (2011)
File Upload (1997)
Sort Issue? (2000)
Locking up with WebCatalog... (1997)
Email within tmpl ? (1997)