> I am receiving the following CF error: > > Error Executing Database Query. > [Macromedia][SequeLink JDBC Driver][ODBC > Socket][Microsoft][ODBC Microsoft Access Driver]Invalid > precision value > > If I try to run the query directly from MS Access, using the > same SQL, I receive no error and the results are returned properly. > > Any ideas on this one? > > The SQL is: > > SELECT * FROM foo > WHERE targetCoStage LIKE '%Start-up%' > AND targetCoStage LIKE '%Development%' > AND targetCoStage LIKE '%Early Stage%' > AND targetCoStage LIKE '%Late Stage%' > AND targetCoStage LIKE '%Middle Stage%' > AND UpdateStatus = 'Updated' > AND 0 = 0 > ORDER BY ID ASC > > The query above will run successfully in CF as long as I have > no more than four "AND" clauses. Once I add a fifth, no > matter what the clause is, CF chokes, but Access is fine. > > The "targetCoStage" column contains a comma delimited values, e.g.: > > "Start-up,Growth,Development,Early Stage"
I don't have an answer for the question you asked, but I think you're asking the wrong question. First, instead of having a column which contains a comma-delimited list, you'd probably be better off with a lookup table with those values instead, and an intersection table between your table and the lookup table. Your database doesn't appear to be in third normal form, and it should be. Second, instead of doing a bunch of LIKE searches to find values within database columns, you'd probably be better off using full-text indexing. Searching for a string in the middle of a larger string using LIKE is very expensive, since your database can't use indexes for such a search. Fortunately, CF comes with Verity, which is ideal for these sorts of searches. Of course, if you redesign the database properly you can avoid this kind of search in this specific case, but you may run into future cases where full-text indexing is the better approach. Finally, you might try using the Access with Unicode driver instead. This driver uses ADODB instead of ODBC, I think, so it may not suffer from the same limitations. To see if it's an ODBC limitation, you could try running the query from an ODBC client directly (like MS Query) rather than from CF. Dave Watts, CTO, Fig Leaf Software http://www.figleaf.com/ Fig Leaf Software provides the highest caliber vendor-authorized instruction at our training centers in Washington DC, Atlanta, Chicago, Baltimore, Northern Virginia, or on-site at your location. Visit http://training.figleaf.com/ for more information! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Logware (www.logware.us): a new and convenient web-based time tracking application. Start tracking and documenting hours spent on a project or with a client with Logware today. Try it for free with a 15 day trial account. http://www.houseoffusion.com/banners/view.cfm?bannerid=67 Message: http://www.houseoffusion.com/lists.cfm/link=i:4:207882 Archives: http://www.houseoffusion.com/cf_lists/threads.cfm/4 Subscription: http://www.houseoffusion.com/lists.cfm/link=s:4 Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4 Donations & Support: http://www.houseoffusion.com/tiny.cfm/54
