Visual Foxpro Database Stored Procedure with Oledb Provider

Question

I am using Visual Foxpro 8.0 database. Below procedure I am using to return records from database on basis of condition matching but it raised error that:

"Function is not implemented."

Foxpro Procedure ------------------------

PROCEDURE FX_Proc_ValidateUser (paramUserName AS Character, paramPassword AS Character)
LOCAL VarUserName AS Character, varXml
VarUserName = IIF(VARTYPE(paramUserName)!="N","",paramUserName)
LOCAL VarPassword AS Character
VarPassword = IIF(VARTYPE(paramPassword)!="N","",paramPassword)
SELECT userinfoid, ;
        username, ;
        password ;
FROM tm_userinfo.dbf ;
WHERE username = VarUserName AND password = VarPassword ;
INTO CURSOR procResult  
varXml = ""
CURSORTOXML("procResult","varXml",1,32,0,"1")
RETURN varXml
ENDPROC

Front End code for calling this procedure------------------

string ConnectionString = "Provider=VFPOLEDB.1;Data Source=C:\Users\raj\Documents\Visual FoxPro Projects\dbFoxMaster.dbc;Collating Sequence=machine;" providerName="System.Data.OleDb.OleDbConnection, System.Data, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089";
OledbConnection objOleDbConnection = new OleDbConnection(ConnectionString);
objOleDbConnection.Open();
OleDbCommand objOleDbCommand = new OleDbCommand();
objOleDbCommand.CommandType = CommandType.StoredProcedure;
objOleDbCommand.CommandText = "FX_Proc_ValidateUser";
objOleDbCommand.Connection = objOleDbConnection;
objOleDbCommand.Parameters.Add("paramUserName", OleDbType.Char).Value = "abc";
objOleDbCommand.Parameters.Add("paramPassword", OleDbType.Char).Value = "123";
var xmlString = oOleDbCommand.ExecuteScalar().ToString();
    DataTable table = new DataTable();
    using (var reader = new StringReader(xmlString))
    {
        var dataSet = new DataSet();

        // creating a dataset from the xml 
        dataSet.ReadXml(reader);
        table = dataSet.Tables[0];
    }

How to get resultset from foxpro 8.0 stored procedure using OledbCommand?

Answer 1

It looks like the problem is that you are putting your results into an array and only the first item in the array is being returned. You should change the stored procedure so that it uses a cursor.

VFP9 Example

Here is a stored procedure example from the northwind.dbc:

Here is an example of calling the stored procedure using C#:

var northwindDbcPath = @"C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind.dbc";
var connectionString = "Provider=VFPOLEDB.1;Data Source=" + northwindDbcPath;
var table = new DataTable();

using(var connection = new OleDbConnection(connectionString)) {
    using(var command = connection.CreateCommand()) {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "CustOrdersDetail";
        command.Parameters.Add("x", 10248);

        var adapter = new OleDbDataAdapter(command);

        adapter.Fill(table);
    }
}

VFP8 Example: (I don't have a copy of VFP8 but I believe that this will work)

var northwindDbcPath = @"C:\Program Files (x86)\Microsoft Visual FoxPro 9\Samples\Northwind\Northwind.dbc";
var connectionString = "Provider=VFPOLEDB.1;Data Source=" + northwindDbcPath;
DataTable table;

using(var connection = new OleDbConnection(connectionString)) {
    using(var command = connection.CreateCommand()) {
        command.CommandType = CommandType.StoredProcedure;
        command.CommandText = "CustOrdersDetail2";
        command.Parameters.AddWithValue("x", 10248);

        connection.Open();      
        // executing stored procedure and getting xml result
        var xml = command.ExecuteScalar() as string;

        connection.Close();

        using(var reader = new StringReader(xml)) {
            var dataSet = new DataSet();

            // creating a dataset from the xml 
            dataSet.ReadXml(reader);
            table = dataSet.Tables[0];
        }
    }
}

Answer 2

A Visual Foxpro stored procedure is basically using Visual Foxpro code to perform tasks such as validating columns on insert or update or delete.

For Example, you might add the following code to VFP database stored procedure to validate a "State" column:

PROCEDURE ValidateState()
IF address.state <> [OH]
    MESSAGEBOX([Incorrect State value!], 48, [Invalid State])
ENDIF
ENDPROC

For querying a table in a Foxpro database, you may want to look at creating local views. You can do this by right clicking inside the foxpro database and selecting "New Local View", then define your view using the query builder. In the Filter tab, under the "Example" column, you can define parameters using the "?" such as "?pusername".

Here is how you would call the view from your Foxpro code:

 LOCAL pusername AS Integer   
 pusername = "SomeUserName"    &&Use this to filter the view
 SELECT 0
 USE MyViewName       &&This will call the view and perform the filter.

You can also make these views updatable and perform your inserts against the view.

Answer 3

I have no VFP around and the last time I used VFP it was still version 3.0...

I would try something like this based on the specs here. To learn the rest read-up here

PROCEDURE insertData (pusername, ppassword)

                    SELECT userinfoid, username, password 
                    FROM tm_userinfo.dbf 
                    WHERE username = pusername AND password = ppassword
                    INTO ARRAY results
     RETURN results
ENDPROC