Solved

Filtering Paradox Tables on Memo Fields

Posted on 1998-12-19
36
473 Views
Last Modified: 2010-04-04
With 16 Delphi 1, you could create an SQL query:

Select * from TableName
where MemoField LIKE '%SearchString%'

and get all fields matching the SerachString - regardless of
the case of the SearchString.
In 32 bit Delphi, it doesn't wotk any more. The match is always case sensitive. This also behaves like this in DBD and SQL explorer and ReportSmith, so the problem must be in BDE. (Interestingly, it works OK in Crystal Reports).

Can anyone help a desperate man? I cannot find the answer anywhere.
Thank you,
John Zelnik,
zelnikj@zeta.org.au
0
Comment
Question by:zelnikj
  • 14
  • 13
  • 9
36 Comments
 

Author Comment

by:zelnikj
ID: 1352059
Edited text of question
0
 
LVL 1

Expert Comment

by:venks
ID: 1352060
I was about to post a similar question.But i found yours first.
This is indeed a real problem.Actually i am on the look out for
a free component(and alos trying to develop one) which does the job of all sorts of SQL queries including searching on memofields..If i find on e i will post youIn the mean time we will have to wait for some expert to come out with the answer
Regards
venks
0
 
LVL 8

Accepted Solution

by:
ZifNab earned 200 total points
ID: 1352061
Hi zelnikj, venks,

look at this one, try it out and let us know if this does what you both want:

http://www.geocities.com/SiliconValley/Network/2114/dbsrch40.zip

Regards, Zif.

0
 

Author Comment

by:zelnikj
ID: 1352062
Well, I'm new to this and do not know exactly how to react. I didin't get an answer, only a suggestion where to find the answer. How do you grade that? I guess it depends on the answer elsewhere - which I have not seen yet.
Wolud appreciate some advice in respect to this...
Meanwhile, thank you for suggesting the above URL, I'll gi it a try.
Regards,
John Zelnik


0
 

Author Comment

by:zelnikj
ID: 1352063
Well, I'm new to this and do not know exactly how to react. I didin't get an answer, only a suggestion where to find the answer. How do you grade that? I guess it depends on the answer elsewhere - which I have not seen yet.
Wolud appreciate some advice in respect to this...
Meanwhile, thank you for suggesting the above URL, I'll give it a try.
Regards,
John Zelnik


0
 
LVL 1

Expert Comment

by:venks
ID: 1352064
Dear zelnikj and Zifnab
If the suggestionn leads to an answer and solves your problem you should award the points and Zifnab(Tom) truly deserves the points.
I have tried out the component and it works very well.The best part is no SQL even for memo searches.It takes care case insensitive searces also and all work pretty fast too..
The following improvements are required:-
         How to look for null values
         Multiple search statemnets (with more than one  field       searches)
         
         Displaying the complete set of records meeting the search criteria rather than displaying the record one by one.

But the tip from Zifnab has saved me many days of work and i am ready to award 50 points to zifnab on my own.Tom,I am posting a separate question for you to answer so that i can award the points to you
Thanks and regards
venks

0
 

Author Comment

by:zelnikj
ID: 1352065
ZifNab,
Thank you for you suggestion. I dowloaded the component and it shure has its use. But this doesn't answer my question. I was enquring about SQL code and not a serch component.
I'll repeat again the reason for this enquiry:
With Delhi 1 I colud ask Show me all records containing the search string - case insensitive. Also using ODBC and Paradox driver, I can write:

Select * from TableName
where UPPER(MemoField) LIKE (%SearchString%)    //SearchString in  UpperCase

and get the correct answer.

It also works with DBASE ODBC driver using code:

Select * from TableName
where UCASE(MemoField) LIKE (%SearchString%)   //SearchString in  UpperCase

But the BDE does not accept UPPER function for Standard Paradox driver.
In 16 bit Delhi (and BDE) you just use

Select * from TableName
where MemoField LIKE (%SearchString%)

and get the corrent case-insensitive result.
My question is why it work everywhere else but STANDARD Paradox Driver (32 bit) and is there a workaround - like direct programming of BDE. There must be a way.
If you go to Database Desktop, you can construct a QBE Query using ..SearchString..
on the MemoField and get a case-insensitive result.

Now here's a problem for you Experts - if you want 200 points.
I thought this was some kind of technical forum here, but it looks like everybody is just trying to chase points.

Regards John Zelnik
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352066
John,

We're just not chasing points. I'm sorry if you got this idea, we just want to try to help. If you don't like the answer then you just reject it. If the answer/comment is just not good enough you explain the reason or you reformat your question. You know, lot of questions can be interpreted in a lot of ways.

Regards, Zif.
0
 

Author Comment

by:zelnikj
ID: 1352067
Sorry Tom,
Didn't mean any offece - just frustrated from chsing the answers for moths and getting newhere... I just emailed Borland and asked the same question again. I guess it is really their business to explain where they altered the BDE not to work the way the 16 bit BDE worked. I'll be surprised if I get any answers from them, they are too big these days to talk to their cutomers.

Regards
  John
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352068
Hi zelnikj,

I just tried it with the QBE on datadesktop. I can't get it to work... It just gives no results... How did you got it right?

For your information. The freeware RxLib has a QBE component.

Regards, Zif.
0
 
LVL 1

Expert Comment

by:venks
ID: 1352069
Dear Tom and zelnikj
Even i could not get it right.Rather it does not accept any memo fields for queries at all.I dont know whether it was a deliberate goof up by Borland .
Does the Rx library QBE accept memo fields.Even i have to give it a try and find out.
Regards
venks

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352070
venks,
 I haven't tried it further because I couldn't get the filter right in the QBE of datadesktop for a memo-field.
 So, sorry, haven't tried QBE of RxLib with memo-fields yet.
Zif.
0
 

Author Comment

by:zelnikj
ID: 1352071
Hi everybody,
This is getting interesting...
I repeat the steps I used to get it working BDE.

Open BDE
SelectFile/New  QBE Query
Select Alias DBDEMOS
Select File BIOLIFE.DB
Click any fields you want to display
Into the NOTES field type: ..aust..
Run the Query
You will get a display of two records containing the word "Australia" in the NOTES Memo
field.
You can see the search is case insensitive - you can type ..AUST.. into the NOTES field and get the same result..
REM: Yoy will not be able to see the NOTES field in BDE as it does'nt display MEMO fields, but you can check records in Delphi, but other fields will display correctly.

Would love to hear from you getlemen again to see how it went.
As I said before, I tried to contact Inprise about the problem, but they wouldn't accept my e-mail. Nice customer relations at Inprise.
This problem is a pain in the bum as it makes searching in Memo fields so much more complicated. You can use UPPER function with String fields, but it is really important to have case-insensitive search on Memo fields as it is never possible to be sure what case the info is in.

Try using the 16 bit BDE, the SQL code
Select * from MemoField
where SearchField LIKE '%SearchString%' works fine.

Regards
  John
Have a nice X-mas & hope to hear from you soon.

P.S. I have not tried the Rx Lib, actually I trying to figure out how to do it with SQL  as this gives me the freedom to use my own code rather than relying on other peoples work.

0
 
LVL 1

Expert Comment

by:venks
ID: 1352072
Dear John
You are right partly.I tried your example and it worked as you said.But when i tried out another example of my own the query returned a  blank record which was  incorrect.In both the case the sql could not be seen and only the message 'No sql available for this query'was flashed.
But this is off track since we are trying to find a solution for code and not Datbase desktop,
I agree with you that this is a serious problem.Many of my own applications are having bugs because of this.
Regrds
venks
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352073
Hi all, yes it works... I wrote 'Aust' instead of '..Aust..', didn't know I had to put these '..' with the search. Pitty offcourse it doesn't shows the SQL.

I've tried it with the QBE of RXLib and yes it works in the same way

This is the query :

Query

:dbdemos:Biolife.db | Species No | Category | Common_Name | Species Name | Length (cm) | Length_In | Notes | Graphic |
            | Check      | Check    | Check       | Check        | Check       | Check     | ..AUST.. | Check   |

EndQuery

Damn, how comes this works...

Zif.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352074
Isn't QBE specially designed for Paradox tables?
0
 

Author Comment

by:zelnikj
ID: 1352075
Hi,
It also puzzels me how it works. The NO SQL available meesage in DBD means that there must be some sort of translation taking place. It finally must go through the BDE, so there must by a way of doing it - I guess by direct calls to BDE. But I have no clue how to do this. I wonder if anybody has a way of talking to Borland to ask some serious questions. Their arogance is incredible. Wont answer your e-mail or give you any info on the subject - yet the bug (I cannot call it enything else) has been there since the 32 bit BDE.I wonder if you guys have some contacts to get through to Borland and ask some questions.

There are ways of doing a search on a Memo fields by iterating the table and doing string comparison but there must a SQL solution since this is the only way to display multiple records in a grid...

Regards
  John
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352076
no it isn't the only way. You can use a memory table to get all the files. Let's asume we use a memory table and use the component I give the URL of. If we make it so that it searches for the first mach and put this record to the memory table. Then automatically searches for the next one, puts it in the memory table etc...

sorry, have no contact with Borland.

I guess the problem is that QBE is only designed for paradox tables (correct?). I've looked at the source code of RX-Lib with their QBE component, but I haven't figured out how they do this, yet....
0
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:zelnikj
ID: 1352077
Hi ZifNab
Memo tables are possibly the way to go, except I have no idea how create them. Do you need ClientDataSet to do this? I do not have a client/server version of Delhi which provides a ClientDataSet component. Can you enlighten me on the subject of creating Memory Tables?
Also I do not understand the point system you guys work with here. Do we somehow have to terminate this session?
I can leave you my e-mail address so you can notify me if find out some more info on the subject in future. I'll keep digging for info until I find some answers...
Regards
  John Zelnik
  zelnikj@zeta.org.au
P.S. Althought I didin't try this I think QBE works an DBASE tables too. When you start a new query in DBD, you can select *.db or *.dbf files, so it must work with DBASE as well.
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352078
Hi zelnikj,

When you ask a question, we can give a comment or answer. If an answer is given, you can grade it or ask more information (with comments). Grading : you can reject it (question can be answered again) or grade it with a category (good, excellent answer etc). You've already answered my question, so normally the problem should be solved. But it isn't, because you still got problems. Therefore I keep talking to you trough comments.

Memtable. Not so difficult. No you don't need Client/Server. Look at freeware RxLib (everybody should have this one!) There is a readymade MemTable. All you have to do is, place it on a form like a normal query or table component. also Inprise has some examples of it on there net.

RxLib can be found at. http://rx.demo.ru/

Regards, Zif.
0
 
LVL 1

Expert Comment

by:venks
ID: 1352079
Hi all
Some facts about memory tables of RXlib 2.5
********************
Memory tables are tables created in memory (RAM) and the contents are deleted when you close them. They are much faster and are very useful when you need fast operations on small tables. Memory tables do not support certain features (like referntial integrity, indexes, autoincrement fields and
BLOBs).
 
Only standard (logical) BDE field types are supported by the in-memory table. "Physical" field types are not supported. The table is kept in memory if possible, but it could be swapped to disk if the table becomes too big.
 
The maximum table size is 512Mb with a maximum record size of 16Kb with a maximum of 1024 fields.
 
Please note that Logical Auto-increment and BLOB fields are not supported due to their nature of operations.
***************************************************

Since memo fields are Blob fields it has to be checked whether
memo fields can be copied to memory tables.
Regards
venks
0
 

Author Comment

by:zelnikj
ID: 1352080
Hi,
It is frustrating when you realize that such seemingly simple omission by Borland in 32 bit BDE can cause so many obstacles to solving such a fundamental task as searching a memo field. I'll have to look at RxLib. So far I had no luck dowloading it. The Rusian site just showed a blank page, when I tried to download it. I'll try again today.
Thank for the hint.
Regards
  John

0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352081
Hi zelnikj, If it gives you too much trouble, I can send it to you... just say how, email, FTP, ...
0
 
LVL 1

Expert Comment

by:venks
ID: 1352082
Dear tom
Have you actually used memory tables for the task at hand.If so
please send me the code .My e mail address is
venks@bom3.vsnl.net.in
regards
venks
0
 

Author Comment

by:zelnikj
ID: 1352083
Hi,
Yes thank you, I would appreciate if you could send it to me. I have tried again the Russian URL to download it, but it only shows a blank page.
Please send it by e-mail.
My address :  zelnikj@zeta.org.au
Thanks again..
Just looking at Venks comment, I do not think we will be lucky with Memory Tables either, because if they do not handle Blobs, they for shure will not handle Memo Fields.
I still think there must be some way to program the BDE (hopefully) to disregard case.
When you use QBE and it works, it must finally go trough BDE, and BDE communicates with tables through SQL. But this is just my guess...
  Regads to all
    John

 
0
 

Author Comment

by:zelnikj
ID: 1352084
Hi ZifNab,
I finally managed to download RxLib so don't send it to me any more.
John
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352085
ooops, didn't saw your previous comment. Sorry. About memotables. No, i haven't tested them with memo-fields and I'm afraid venks is correct, didn't thought on that. This method would only be usefull if you didn't have to show the memo-fields, but just uses them for filtering.
0
 

Author Comment

by:zelnikj
ID: 1352086
Hi ZifNab,
I had a go today at the RxLib. And luckilly, the QBE component works OK with Memo fields just like the QBE in DBD!!!. So you can use this component to do what SQL cannot do. Haven't tried Mem Tables yet. There seems to a bug in it, when I select in from the Component Palette, it shows a sequence of error messages. (GPF I think). I saw something on the WEB about a bug in Memory Tables but I have not got involved in this yet. The Help Files are killing me though - althought I learnt some Russian when I was young. Cannot understand the tech. jargon. Is there any way to get English help files?

Regards & Happy New Year,
 John

0
 
LVL 1

Expert Comment

by:venks
ID: 1352087
Dear John
I can hekp you with the help files.I have got a english version and it will be sent to your e mail address zelnikj@zeta.org.au
Do let me know any breakthroughs
Regards and happy new year
venks
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352088
zelnikj, offcourse it works with QBE component, that's what I told at comment : Date: Wednesday, December 23 1998 - 01:04AM PST .

Happy New Year.
0
 
LVL 1

Expert Comment

by:venks
ID: 1352089
Dear zelnikj
I tried to send the help file.But the server is not accepting large files (914 K)May be Zifnab can send it to you,I had sent it to him a few months back
Happy new year
venks
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352090
I can send it to you, sure. But does it will not give the same problem? Because the server doesn't except large files? Zif.
0
 
LVL 1

Expert Comment

by:venks
ID: 1352091
Dear Tom
I finally send it.You need not send it now.But i have not recieved any confirmation from John
regards
venks
0
 
LVL 8

Expert Comment

by:ZifNab
ID: 1352092
Hi venks, okelidokeli.
0
 

Author Comment

by:zelnikj
ID: 1352093
Hi All,
and thanks for the Help Files. They arrived OK at my e-mail adress.
I got them on 29.Dec.98. Didn't know about the File Size limit. I send files 1Meg + on this server with any complaints?
Thanks again
  John
0
 

Author Comment

by:zelnikj
ID: 1352094
Hi All,
and thanks for the Help Files. They arrived OK at my e-mail adress.
I got them on 29.Dec.98. Didn't know about the File Size limit. I send files 1Meg + on this server without any complaints...
         -----------
Thanks again
  John
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A lot of questions regard threads in Delphi.   One of the more specific questions is how to show progress of the thread.   Updating a progressbar from inside a thread is a mistake. A solution to this would be to send a synchronized message to the…
This article explains how to create forms/units independent of other forms/units object names in a delphi project. Have you ever created a form for user input in a Delphi project and then had the need to have that same form in a other Delphi proj…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now