Good workaround to being able to use LIKE. For small or low-usage databases, this would work fine. For a large database, there are performance hits:
- using Substring would put extra overhead on the server (substring isn't free) - returning 250 fixed bytes instead of a variable number of bytes would put extra overhead on the network (unless Advantage trims trailing blanks) - a char (or true varchar) would be read as part of the database row (one I/O), while a memo requires 2 or more I/Os (one to read the base row, then one I/O for each memo field in that row). I don't know the overhead that Advantage has for memo fields, but in other DBMS, the overhead is high. IIRC, in DB2, varchar has 2 bytes of overhead per row, while CLOB has something like 32 bytes of overhead per row. Again, no problem in a small or low usage database, but in a large database the size of each record would increase, records per blocks would go down, and I/Os would go up, all reducing performance. For our needs, we plug into many different DBMS using ADO. So we try to keep our SQL standard across DBMS. While the Substring solutions gets around the problem in Advantage, it is not the best way to code for other DBMS. Having not used sp_CreateIndex90, I'm not sure what it's doing, but if it lets you create an index on a varchar, great. In any case, the OP has more information than he probably wanted to know! :-) At 02:08 AM 12/8/2008, [EMAIL PROTECTED] wrote: >At the risk of teaching you to suck eggs :') > >SELECT cropcode, formulation, upper(substring(labeltext,1,250)) AS test1 >from labeltx >where upper(substring(labeltext,1,250)) LIKE '%QUARTS%' > > Order by upper(substring(labeltext,1,250)) > >CREATE TABLE vchar_test ( > f1 VarChar( 250 ), > id Char( 10 )) IN DATABASE; >EXECUTE PROCEDURE sp_CreateIndex90( > 'vchar_test', > 'vchar_test.adi', > 'F1', > 'f1', > '', > 2, > 1024, > '' ); > >I do not use varchar at all, and I do not use memo's much but I find that >substring gets round my problems with this. The modern way is I suppose to >use the Full Text Search (FTS) stuff but I have not tried any of it yet. I >will have to make some time. > > >Sid Gudes <[EMAIL PROTECTED]> >Sent by: [EMAIL PROTECTED] >05/12/2008 16:51 >Please respond to >Moderated discussion list about database programming with Borland's >Delphi <delphi-db@elists.org> > >To >Moderated discussion list about database programming with Borland's Delphi ><delphi-db@elists.org> >cc > >Subject >Re: Choose DB for Delphi > > >Advantage is fine, we use it for some of our >internal testing via ADO and have had no >problems. One thing I don't like about Advantage >is that it doesn't have a true Varchar; if you do >a Create Table, Varchars become Memos. This >means, for example, that you can't have a Varchar >in a key and that the overhead per field is >higher than Varchar. Also can't use Like on a >memo. We end up changing many of our Varchars to >Chars and just wasting the space, but that also >gets into recordsize limitations. > >If you're working on a small database, this may >not be important. If the database is large or >you need to use varchars in keys or with the Like >operator, it may be important. YMMV. > > >At 01:25 AM 12/5/2008, [EMAIL PROTECTED] wrote: > >I use Advantage database. What they call Local server is available free. > >But even better is the two user development server that has beeen made > >available at http://www.sybase.com/ads_developer_guide_book. > >Then explore the advantage site to download the TTable replcement and > >ordert the book . Total cost about $30 for realy exalant product. And >your > >application will be fully scalable. > > > > > >"Cédric Joubert" <[EMAIL PROTECTED]> > >Sent by: [EMAIL PROTECTED] > >04/12/2008 11:15 > >Please respond to > >Moderated discussion list about database programming with Borland's > >Delphi <delphi-db@elists.org> > > > > > >To > >delphi-db@elists.org > >cc > > > >Subject > >Choose DB for Delphi > > > > > >Hi, > > > >It's me again ;-) > > > >Which best DB would you use with Delphi7 today for a single user > >application > >(medium size, 50 unit/forms) ? > > > >1/ I need one that fit with : > >- secure data > >- dynamic (auto startup like Interbase/Firebird) > >- freeware if possible > >- querying with good performance > >- transactionnal > > > >5 years ago, I would think about the open source Firebird... but today > >i've > >no idea... > > > >2/ which access method is recommanded, DvExpress, Ado, other ? > > > >Thanks in advance > >Cédric > >*********************************************************************** >NOTICE: This e-mail transmission, and any documents, files or previous >e-mail messages attached to it, may contain confidential or privileged >information. If you are not the intended recipient, or a person >responsible for delivering it to the intended recipient, you are >hereby notified that any disclosure, copying, distribution or use of >any of the information contained in or attached to this message is >STRICTLY PROHIBITED. If you have received this transmission in error, >please immediately notify the sender and delete the e-mail and attached >documents. Thank you. >*********************************************************************** Regards, Sid Gudes PIA Systems Corporation [EMAIL PROTECTED] _______________________________________________ Delphi-DB mailing list Delphi-DB@elists.org http://lists.elists.org/cgi-bin/mailman/listinfo/delphi-db
