Using index of foxpro from vb.net 1

[makdu]

Hi all,
I am having a foxpro table ( VF6). This is linked in MS access. From a vb.net application, i am reading the values in the foxpro table. Currently the search is taking a long time. So thought about indexing the foxpro table. But from a vb.net application, i am not able to index the table. So i indexed the foxpro table. Then it created a .idx file. How can i access this indexed foxpro table from vb.net
here is the sample of code used for connecting to the tables through MS ACCESS.

Private Sub indexng_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
        cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\database.mdb;Persist Security Info=False")
        cn.Open()
    End Sub

 

[Olaf Doschke]

idx is a bad choice. Create an index in a CDX file, and this is automatically used together with the table. Especially there is automatic optimization of the where clause with indexes as tags in CDX files.

Execute a non sql query as "INDEX ON field TAG field" for a regular index for example. You could also create a compound index like "INDEX ON field1+field2 TAG fields1n2".

Some rules: TAG names must be 10 chars at max. expressions like filed1+field2 must not cause a type mismatch. If you want to combine a date field with a char field index on DTOS(datefield)+charfiled for example.

Using the same expression that is indexed in a where clause on the left side does cause rushmore to use the index to optimize a query. Eg WHERE DTOS(datefield)+charfield = '20090904sometext' will be optimized by such a compound index. WHERE datefield = {^2009-09-04} AND charfield = 'sometext' will not be optimized by that compound index, you'll need a seperate index on each field then.

I don't know what Access does though, if you have a linked table. But I assume the difference to an imported table is, that the DBF remains a seperate table and any SQL statement done in ACCESS is kind of forwarded to the foxpo SQL engine, which would make this a good advice.

I'd say you'd be better off to use the DBF tables more directly, but if it's just part of the data and the main data is in access it's okay. You need an Access expert to tell you how linked tables are handled by Access, though.

Bye, Olaf.

 

[makdu]

I am not able to use INDEX ON command on a linked table. ie, i tried to use

cmd = New OleDbCommand("Create INDEX itmno_idx ON INVENT(ITEM_NO) ", cn)

But this statement is not supported from vb.net.

 

[Olaf Doschke]

Well, Create INDEX is not the VFP syntax.

You do INDEX ON... like I said directly towards the VFP DBF. Connect with the VFP OLEDBProvider to a directory of free tables or to a DBC, see

http://www.connectionstrings.com

BYe, Olaf.

 

[Mike Lewis]

Makdu,

The syntax you need is:

INDEX ON <expression> TAG <index name>

So, if you want to create an index on a field named CustomerCode converted to upper case, you could do this:

INDEX ON UPPER(CustomerCode) TAG Cust

Often, the index name is the same as the name of the field you are indexing on, but that won't work in this case, because of the ten-character maximum that Olaf mentioned.

Once you have created the index, you can use normal SQL to query the table. At that point, you don't need to know the index name or which field is indexed; VFP uses the index behind the scenes.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[makdu]

I tired with what Mike has suggested

 fcn = New ADODB.Connection
        fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" & tablefolderpath 
        fcn.Open()
        fcn.Execute("Index on BRAND tag idx1 ")

where tablefolderpath = D:\myfolder
In this folder the file named sales.dbf is there . I want to index the brand field in this dbf file which is situtated in the tablefolderpath.
I got the following error

One or more errors occurred during processing of command.
[/color red]

 

[Olaf Doschke]

Youi need exclusive access to create an index. You also should be able to get a more precise error. What you see is just the main ADODB error, there should be more details you can get via fcn.Errors. See here for retrieving all error informations from ADODB in VB:

http://www.devx.com/tips/Tip/13483

Bye, Olaf.

 

[makdu]

Thanks for the message.
when i used it , i got the following message

"Errors reported by ADO
(1) Error#: -2147467259
	Desc. : [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
	Source: Microsoft OLE DB Provider for ODBC Drivers
	Native Error: 0
	SQL State: IM002
	Help Context: 0
	Help File: 
"

i have used the following for this test

  fcn = New ADODB.Connection
            fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" & tablefolderpath 
            fcn.Open()
      fcn.Execute("INDEX  on sale.dbf (BRAND)")

 

[makdu]

Sorry, a correction in the error generated

"Errors reported by ADO
(1) Error#: -2147217900
	Desc. : Command is missing required clause.
	Source: Microsoft OLE DB Provider for Visual FoxPro
	Native Error: 221
	SQL State: 
	Help Context: 0
	Help File: 
"

 

[Mike Lewis]

Makdu,

Index on BRAND tag idx1

The syntax is correct, but you also have to tell it which table you want to index. You do that with the USE command:

USE Sale
Index on BRAND tag idx1

In your later post, you wrote:

INDEX on sale.dbf (BRAND)

That's not valid syntax.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[makdu]

Thanks Mike for the reply
I used

fcn.Execute("use sale; index on brand tag idx1")

This didnt throw any exception. But i am not able to see any index file named idx1 getting generated. Also in table i cannot see the changes getting reflected( i.e the field Brand getting arranged in order)

 

[Mike Lewis]

Makdu,

You won't see the index as a separate file. What you should see is a file named SALE.CDX. This contains all the indexes that exist for SALE.DBF.

You say you are not "seeing" the records arranged in index order. How are you "seeing" the records? If you are using a native VFP command, like BROWSE (which is probably not available in ADO, but I'm not sure), you must first execute:

SET ORDER TO IDX1

This establishes the index as the "current" index.

If you are using SQL to see the data, you need to an ORDER BY clause:

SELECT * FROM Sale ORDER BY Brand

In that case, the index is used to optimise the query, but it's the ORDER BY that actually determines the ordering of the result set.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro tips, advice, training, consultancy
Custom software for your business

 

[cbasoz]

Indexing is NOT supported by VFPOLEDB driver. You can however use "index on" inside a code block and call that block via ExecScript( codeParameter ). ExecScript would be a stored procedure and code is the parameter to procedure. ie:

using System;
using System.Data;
using System.Data.OleDb;

using System.Windows.Forms;
using System.Drawing;

class VFPCreateIndex
{
 static void Main()
 {
   string vfpScript =@"
   use customer exclusive
   local ix, llExist && llExist initialized to false implicitly
   for ix = 1 to tagcount()
     if upper(tag(m.ix)) == 'DUMMY'
	    llExist = .t.
     endif
   endfor
   if !m.llExist
	 index on upper(Country+Company) tag dummy
   endif
   use";

  string strCon =
      @"Provider=vfpoledb;Data Source=C:\PROGRAM FILES\MICROSOFT VISUAL FOXPRO 9\SAMPLES\data\testdata.dbc";
   OleDbConnection con = new OleDbConnection(strCon);
   OleDbCommand cmd = con.CreateCommand();
    con.Open();    
    cmd.CommandType = CommandType.StoredProcedure;
    cmd.CommandText = "ExecScript";
    cmd.Parameters.AddWithValue("code", vfpScript);
	cmd.ExecuteNonQuery();
   
   DataTable t  = con.GetSchema("indexes",new string[] {null,null,null,null,"customer"});
   Form f = new ShowDataForm(t);
   f.ShowDialog();

   con.Close();
 }
}


public class ShowDataForm : Form
{
  public ShowDataForm(object tbl)
  {
     this.dgv = new System.Windows.Forms.DataGridView();
     this.dgv.Location = new System.Drawing.Point(0, 0);
     this.dgv.Dock = DockStyle.Fill;
     this.dgv.DataSource = tbl;
     this.Text = "Schema Check";
     this.Controls.Add(this.dgv);
     this.ClientSize = new System.Drawing.Size(1024, 768);
  }
  private System.Windows.Forms.DataGridView dgv;
}

However, if you have VFP do the indexing from there, it is a one time job.

To utilize the index use an expression that matches the signature of the index. ie:

select * from customer where upper(Country+Company) like 'USA%'

would benefit from the index created in C# code. This one wouldn't use an index (assuming there is not an index on upper(Country)):

select * from customer where upper(Country) =  'USA'

Cetin Basoz
MS Foxpro MVP, MCP

 

[makdu]

Thanks again Mike,
You have cleared one of my basic understanding of Indexing

 

[Olaf Doschke]

Hi Cetin,

surely nice code. But I see the INDEX Command is among the supported Commands and Functions in the OLE DB Provider.

Makdu made an error in the syntax in the first place and in the second place tried to do two things in one Execute call separated with semicolon, which is not the way to seperate commands in VFP. Most probably as there are no Exceptions/Errors what was executed was only the USE of the table.

Execscript is the way to go. It doesn't need to be so generic and flexible. This is what I did:

First step: Create a test database and a table (this is the outset makdu already has:

MKDIR ("D:\temp\oledbtest\")
CD ("D:\temp\oledbtest\")
Create Database oledbdata.dbc
Create Table sometable.dbf (iid I autoinc, cText C(10))
CLOSE DATABASE

Second step: Index the cText field of Sometable.Dbf:

loConn = CREATEOBJECT("ADODB.Connection")
loConn.ConnectionString="Provider=vfpoledb;Data Source=D:\temp\oledbtest\oledbdata.dbc"
loConn.Open()
loConn.Execute("Execscript('Use Sometable Exclusive'+CHR(13)+'Index On cText Tag xText')")
loConn.Close()

In makdu's situation this should translate to VB as

fcn = New ADODB.Connection
fcn.ConnectionString = "provider=VFPOLEDB.1;Data Source=" &tablefolderpath
fcn.Open()        
fcn.Execute("Execscript('Use sale exclusive'+CHR(13)+'Index on BRAND tag idx1')")
loConn.Close()

The only problem with a pure INDEX ON is, that it works on an empty datasession, and INDEX ON needs the table of which a field should be indexed opened and opened exclusive beforehand. Execution of two separate commands 'USE table exclusive' and 'INDEX ON field TAG tagname' does only not work, because it seems every call to the OLEDB Provider starts in it's own new empty datasession, that's why you also get an error with the correct syntax of an INDEX ON done without an Execscript.

Bye, Olaf.

 

[cbasoz]

Olaf,

"Execution of two separate commands 'USE table exclusive' and 'INDEX ON field TAG tagname' does only not work, because it seems every call to the OLEDB Provider starts in it's own new empty datasession, that's why you also get an error with the correct syntax of an INDEX ON done without an Execscript."

If this statement was true then 'SET' commands that are scoped to current datasession wouldn't work either, but they do. A session is composed by the connection and not the single command. You indirectly verify what I have said, doing it with ExecScript() function instead of "Index" command.

And most interestingly on my help "INDEX ON command" is listed under "Unsupported Visual FoxPro Commands and Functions in OLE DB Provider".



Cetin Basoz
MS Foxpro MVP, MCP

 

[makdu]

Thank you all for the support providing.
The code provided by Olaf is working .
The index file generated is sale.cdx thought the tag is given as idx1. Is this fine
Also if i want to have index on mulitple field, when i tried in single statment like

            fcn.Execute("Execscript('Use sale exclusive'+CHR(13)+'Index on ITEM, brand tag idx1')")

it generated syntax error

so i tried with multi line option and it worked fine

            fcn.Execute("Execscript('Use sale exclusive'+CHR(13)+'Index on ITEM tag idx1')")

             fcn.Execute("Execscript('Use sale exclusive'+CHR(13)+'Index on brand tag idx2')")

Is the approach is correct?

 

[cbasoz]

Hmm I would use the easier syntax:

string vfpScript =@"
 Use sale exclusive
 Index on ITEM tag idx1
 Index on brand tag idx2";

// ...
 cmd.CommandType = CommandType.StoredProcedure;
 cmd.CommandText = "ExecScript";
 cmd.Parameters.AddWithValue("code", vfpScript);
 cmd.ExecuteNonQuery();

Cetin Basoz
MS Foxpro MVP, MCP