ODBC - SQL Query

[fox007]

I am using the VFP ODBC driver to query a FP2.6 table. However I get the following error:

Warning: SQL error: [Microsoft][ODBC Visual FoxPro Driver]File 'instr.prg' does not exist., SQL state S0002 in SQLExecDirect in c:\phpdev\

http://www\public\foxprotest\repairresults.php

on line 62

when running the follow query:

select location.loc_id, location.loc_co, pr_serial,tr_date,tr_code,tr_memo from prtrans left join location on location.loc_id = prtrans.loc_id where instr(tr_memo, '$searchterm1') or pr_serial = '$searchterm1'";

It seems the problem is the instr(tr_memo, '$searchterm1') part of the statement because if I remove that part, it will return information. I have tried the full query on directy on another table and it does work. I think it is the way the the ODBC driver is translating or parsing the statement.

The reason for that part of the statement is the I need to search a memo field for a string. Different solutions to get this done would also be appreciated.

How can I get this resolved?
Where can I find a reference for the command and structure that the ODBC driver will accept?

Thank you

 

[DSummZZZ]

Fox .DBFs can be opened directly from VFP. You don't need ODBC. They will remain compatible with older versions of Fox also, unless you do something to modify the table structure.

USE [D:\PATH\]prtrans

Now the problem with the query, is that VFP thinks there is a program named instr.prg which performs a function of some sort. instr(tr_memo, '$searchterm1')

Assuming searchterm1 is the search string, just change your query to:

select location.loc_id, location.loc_co, pr_serial,tr_date,tr_code,tr_memo from prtrans left join location on location.loc_id = prtrans.loc_id where (searchterm1 $ tr_memo) or (searchterm1 $ pr_serial)
Dave S.

 

[JRB-Bldr]

Between the fact that VFP can access FP data tables without the need for ODBC and with your use of instr(<source string>,<desired string>) which is a VB command, I am guessing that you are not using one of the Foxpro languages.

Why don't you use the SQL statement to return the entire FP Memo field and then, within your VB program search the Memo to find where and/or if the desired string &quot;lives&quot;?

Side Note: Within the Foxpro languages the equivalent command is AT() or ATC(). Not having used it in the VB environment, I cannot confirm if it will work within the ODBC SQL Statement.

For a VB vs FP command comparison, go look at

http://www.lespinter.com/articles/English/216.htm

Good Luck,
JRB-Bldr
VisionQuest Consulting
Business Analyst & CIO Consulting Services
CIOServices@yahoo.com

 

[fox007]

Thank for the quick replies and suggestion.
I guess that I didn't make it clear that I am using the ODBC driver because I am using PHP.

ODBC seems to be the only way to query this FP table. I have the need to search a memo field for a substring. Is there a better SQL query to search a memo field?

 

[DSummZZZ]

I don't know PHP. What string search/manipulation functions are built in?
As mentioned previously, if there is a limitation on string searches, you may have to retrieve the entire record set and search within it. If necessary, you could retrieve the record set based on
pr_serial = '$searchterm1'&quot;;
then go through it after the fact looking for the substring in the returned memo field using another substring search, if you can't find one compatible with ODBC.
Dave S.

 

[fox007]

I am searching the table for the search string in either the memo field or the pr_serial field. Searching the memo field from the returned recordset is not a good solution because the set will not be correct. PHP can handle the string search however, is there a way to do this within SQL.

Is there a way to return records that have the search string in either location?

 

[DSummZZZ]

Sorry I can't be more definitive, but I believe AT() is the Fox equivilent of instr().

AT(SearchString, WhereToLook)

So, AT('$searchterm1', tr_memo) instead of instr(tr_memo, '$searchterm1')

may work for you.
Dave S.

 

[craigsboyd]

I concur with Dave, there is no Instr in FP so use AT() or ATC() Slighthaze = NULL