Strange behavior SELECTing FROM Delphi .dbfs

[worky24]

Hello all!

I've encountered a strange behavior of SELECT when trying to query few tables (.dbf) created and maintained with a Delphi application. To be more specific:

I have two tables, let it be "TABLE1.DBF" and "TABLE2.DBF", created and used by a Delphi application. I'm trying to select some records from them, using VFP, using a SQL statement like this:

SELECT some_fields ;
  FROM table1, table2 ;
  WHERE join_condition AND ;
        some_other_conditions AND ;
        NOT DELETED() ;
  ORDER BY some_field ;
  INTO TABLE result

If the two tables are closed, then the query returns 0 (zero) rows. If tables are open before performing SELECT, then the query returns correct number of rows.

Also, it seem that "NOT DELETED()" condition from WHERE clause isn't respected (there are SELECT-ed both deleted and not deleted rows). I have to issue a "SET DELETED ON" before SELECT to obtain only not deleted rows.

This things are happening in VFP 6, SP 5.

Any ideeas?

Thank you in advance!

worky

 

[Mike Lewis]

Worky,

First, I'm not sure what you mean by "Delphi DBFs". Are they created by the Borland Database Engine, or what? That said, I would think the fact they were created in Delphi doesn't make any difference to your problem.

In general, you don't need a table to be open in order for SELECT to access it. Provided VFP can find it (that is, if it's in the current database or the search path), the SELECT will access it correctly.

Is it possible that you have more than one copy of Table1 and Table2, in different folders? That might explain the behaviour you are seeing.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[worky24]

First, I'm not sure what you mean by "Delphi DBFs". Are they created by the Borland Database Engine, or what?

I really don't know! It's a legacy application and data is stored in few DBF files which have associated MDX files. I have to say that I've never worked in Delphi.

In general, you don't need a table to be open in order for SELECT to access it. Provided VFP can find it (that is, if it's in the current database or the search path), the SELECT will access it correctly.

Yes, I know and I allways relied on this behavior.

Is it possible that you have more than one copy of Table1 and Table2, in different folders?

No, there are only this two files. More of that, I've created two new tables (let say Table11 and Table21) in VFP and APPEND-ed from Table1, respectively Table2. In this case, another strange thing happens:

In fact, I copied the original tables from another computer, without corresponding MDX files. When I try to append them to Table11, respectively Table21, programmatically without open them previously via Command Window to remove references to structural index file, having in ErrorHandler:

IF ERROR()=1707
    RETRY
ENDIF

the program remains stuck on RETRY command (this is made to avoid "The structural index file associated with a table file could not be found." error message).

Have a nice weekend!

Worky

 

[Mike Lewis]

Worky,

The fact that the indexes are MDXs says that these are in fact dBASE IV files. The Delphi connection is a red herring.

You should have kept the indexes even though they are not compatible with VFP's indexes, as VFP would have converted them the first time you accessed the tables.

Re the error message, I guess there is no point in doing a RETRY on that particular message. If the index is not there, it is not there.

Sorry I can't help with your original problem. Maybe someone else can help.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[zhavic]

How you are connecting to that tables ?
If that are not the VFP tables, you have to connect to them using ODBC, ADO or SQL pass-through (SPT).

Zhavic


---------------------------------------------------------------
In the 1960s you needed the power of two Comodore64s to get a rocket to the moon. Now you need a machine which is a vast number of times more powerful just to run the most popular GUI.

 

[rob444]

Use the program in faq184-3162 to determine the exact kind of tables yoú're using...

Rob.