Source language: Translate to:

Querying memo field problem

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

Querying memo field problem

Postby Wrangler » Thu Mar 26, 2009 11:04 am

Using this to query access db:

dbpQuery "organizer" "gen" "field[num] LIKE [#34][search][#34]"

OR

dbpExecSQL "organizer" "SELECT * FROM gen WHERE field[num] LIKE [#34][search][#34]" ""

in a memo field. Fields are field1, field2, etc.

I'm using multi-line textentry boxes. If I run the query on field[num]
([num] is set in button code), with just a single word, it finds it.
But if there is more than one word in the box, and search, it says no can find.

This seems like a bug. Can anyone else verify this, or tell me what I'm doing wrong?
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby smartmedia » Thu Mar 26, 2009 11:31 am

Try this

dbpExecSQL "organizer" "SELECT * FROM gen WHERE field[num] LIKE '%[search]%';" ""

% is used like willcard so when you put it in the start and in the end of the word you look in for will return all words contains that phrase.
User avatar
smartmedia
 
Posts: 889
Joined: Fri Apr 01, 2005 6:50 am
Location: Hellas

Postby Wrangler » Thu Mar 26, 2009 11:47 am

Thanks, Smartmedia, but it didn't work. I never use ; at the end of my mysql statements in Neobook and they work fine. I guess neobook handles it. I know it's mandatory when running sql commands like in PHPMyAdmin though.

Can anyone try this and verify? If it's at my end, I'll need to dig in much deeper.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Gaev » Thu Mar 26, 2009 11:47 am

Wrangler:

a) From the Help file ...

LIKE
The search string is similar to the field contents. The LIKE operator allows you to include wildcards to find partial matches. For example, the following will find all customers having a first name that starts with the letter J:

FirstName LIKE "J%"

The % character is a wildcard. The wildcard can be placed anywhere in the string and you can use as many wildcards as you like.

Here are some examples:

LastName LIKE "%duff%"
will find "Duffy", "Macduff", "Duffel", etc.

Phone LIKE "416%"
will find telephone numbers beginning with 416.

Another wildcard is the underscore character (_) which can be used to represent any single character. For example:

LastName LIKE "sm_th"
will find "Smith", "Smyth", "Smath", "Smeth", etc.

So, if you don't specify any wild cards, the query becomes one for an EXACT MATCH !!!


b) If you are using a Multi-Line TextEntry Box to enter the search text ... and searching for matches in MEMO fields ... there are too many oppotunities for mismatches due to "invisible new line character(s)" e.g. ...

- wordwrap is not same as new lines caused by pressing the Enter key

- differences between Windows and *nix in new line specifications ([#13][#10] vs. [#10]?)

So, there could be a number of reasons for the system returning "no matches" ... if there is a problem, you will need to do a lot of "test cases" in order to find it ... but you could start by creating new content in a MEMO field that does not have a new line code(s) ... e.g. "John Smith" ... and then do a hard coded ...

dbpQuery "organizer" "gen" "field1 LIKE [#34]John Smith[#34]"

... and depending on whether it works or not ... progress to more/less complex cases.
User avatar
Gaev
 
Posts: 3733
Joined: Fri Apr 01, 2005 7:48 am
Location: Toronto, Canada

Postby Wrangler » Thu Mar 26, 2009 11:50 am

Oops. You edited your post while I was gone. :) It DOES work with the % in it. Thanks.
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA

Postby Wrangler » Thu Mar 26, 2009 11:53 am

Gaev: the search variable is entered into a single line box. The data entry boxes are multi-line. The problem was the lack of wild cards.

Thanks guys for your help. :)
Wrangler
--------------
"You never know about a woman. Whether she'll laugh, cry or go for a gun." - Louis L'Amour

Windows 7 Ultimate SP1 64bit
16GB Ram
Asus GTX 950 OC Strix
Software made with NeoBook
http://highdesertsoftware.com
User avatar
Wrangler
 
Posts: 1507
Joined: Thu Mar 31, 2005 11:40 pm
Location: USA


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 2 guests

cron