[AccessD] HELP!! fastest way to execute sql

This problem just seems to grow.  I have written the passthough query...but 
Informix says I cannot use a wild card on this type of 
field(TEXT,LONGCHAR,BLOB,MEMO).  I have contacted the DBA...and they are 
lost to(but trying to look)...so...2 questions:
1.  Anyone know how to search TEXT fields on informix with a wildcard?
2.  Can you do this on an SQL or MYSQL db?

Desperately begging,

Mark A. Matte

P.S...The error on the passthrough surprised me because I know I have done 
this type of search on informix before...so I called the DBA and had them 
watch what SQL was actually executed.  If they run the same SQL on the 
server that I use in the Passthrough...they get the same error I do...but if 
I just use a select query against a linked table...I get 
results(unfortunately this takes a very long time...forever)...now I know 
why.  Using a select query to linked table:
SELECT informix_ps_rc_case_note.case_id, 
informix_ps_rc_case_note.rc_descrlong
FROM informix_ps_rc_case_note
WHERE (((informix_ps_rc_case_note.rc_descrlong) Like "*night*"));

When I execute this in A97 or A2K...this is what hits the server:
SELECT informix_ps_rc_case_note.case_id, 
informix_ps_rc_case_note.rc_descrlong
FROM informix_ps_rc_case_note;
Either access or informix ignores my where clause...but access returns the 
correct records.

Any ideas, thoughts, comments, suggestions?






>From: "Robert L. Stewart" <rl_stewart at highstream.net>
>Reply-To: Access Developers discussion and problem 
>solving<accessd at databaseadvisors.com>
>To: accessd at databaseadvisors.com
>CC: markamatte at hotmail.com
>Subject: Re: [AccessD] fastest way to execute sql
>Date: Fri, 14 Jul 2006 08:37:41 -0400
>
>Mark,
>
>Actually, it is very simple.
>
>You will need to know the general syntack for the backend database, but it
>should be something like this:
>
>Set the query type as passthrough.
>
>SELECT *
>FROM MyTable
>WHERE MyMemoField LIKE '%TheWord%' OR MyMemoField LIKE '%AnotherWord%'
>
>Open the query properties.
>Set the ODBC string.
>Set the timeout to 0.  This will let it run until it completes.
>
>This will let the server to run the query and it will return only the 
>results
>back to Access, not all 1.2 mil rows.
>
>If you want the results in an Access table, then you can use this query as 
>the
>source for a make table or append query.
>
>NOTE:  The synatx I used above is generic.  You might have to use " instead 
>of
>'.  The % is the wild card for SQL Server like the * is for Access.  It 
>might
>be different in Informix.
>
>Robert L. Stewart
>The Dyson Group International
>Software for the Non-profit Enterprise
>Expanding your Sphere of Knowledge
>
>
>Quoting accessd-request at databaseadvisors.com:
>
> > Date: Thu, 13 Jul 2006 20:32:59 +0000
> > From: "Mark A Matte" <markamatte at hotmail.com>
> > Subject: Re: [AccessD] fastest way to execute sql
> > To: accessd at databaseadvisors.com
> > Message-ID: <BAY121-F219DE40FE5014F9980C62ED26E0 at phx.gbl>
> > Content-Type: text/plain; format=flowed
> >
> > Robert,
> >
> > Thanks for the feedback...I have never used pass-throughs...here is my
> > scenario.  Access db connected to Informix via ODBC.  I need to do some
> > fuzzy/wildcard searches against a number of  memo/blob fields.  I have 
>tried
> > to do these types of searches in the past on the linked tables with no
> > success(time outs or just never finishes).  I typically pull ALL(1.2 
>mil)
> > records from the needed table and then do the fuzzy/wildcard searches on
> > local tables.  This has been the only way I got the info needed.  In 
>this
> > scenario, would your suggestion still apply...and if so, do you mean 
>create
> > my fuzzy/wildcard searches as a pass-through...and then use the 
>pass-through
> > as the source in another query to append to a local table?
> >
> > Thanks,
> >
> > Mark A. Matte
>
>
>--
>AccessD mailing list
>AccessD at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/accessd
>Website: http://www.databaseadvisors.com