zelnikj
asked on
Filtering Paradox Tables on Memo Fields
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
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
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
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
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
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
ASKER
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
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
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.
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.
ASKER
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
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
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.
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.
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
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
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.
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.
ASKER
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.
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.
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
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
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.
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.
Isn't QBE specially designed for Paradox tables?
ASKER
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
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
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....
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....
ASKER
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.
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.
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.
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.
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
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
ASKER
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
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
Hi zelnikj, If it gives you too much trouble, I can send it to you... just say how, email, FTP, ...
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
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
ASKER
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
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
ASKER
Hi ZifNab,
I finally managed to download RxLib so don't send it to me any more.
John
I finally managed to download RxLib so don't send it to me any more.
John
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.
ASKER
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
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
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
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
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.
Happy New Year.
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
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
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.
Dear Tom
I finally send it.You need not send it now.But i have not recieved any confirmation from John
regards
venks
I finally send it.You need not send it now.But i have not recieved any confirmation from John
regards
venks
Hi venks, okelidokeli.
ASKER
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
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
ASKER
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
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
ASKER