Stored Procedures

[Anthony M. Davis]

Hi all,

I'm a little confused about how to obtain a result set from a stored
procedure (stored in a Visual FoxPro 8.0 database) from an ASP.NET web page
in order to populate a WebForms DataGrid control.

I've been experimenting using the code below but the only output i can get
is:

return_value
True

Here is attempt #1:

OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = selectCMD;

conn.Open();

DataSet ds = new DataSet();
da.Fill(ds);

DataView dv = new DataView(ds.Tables[0]);

grdSurcharges.DataSource = dv;
grdSurcharges.DataBind();

conn.Close();


....and attempt #2 (same results as #1)



OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

//OleDbParameter sampParm = selectCMD.Parameters.Add("RETURN_VALUE",
OleDbType.Integer);
//sampParm.Direction = ParameterDirection.ReturnValue;

conn.Open();

OleDbDataReader reader = selectCMD.ExecuteReader();

grdSurcharges.DataSource = reader;
grdSurcharges.DataBind();

reader.Close();
conn.Close();


The VFP stored procedure is defined as follows:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application
ENDPROC


Thanks for your help,
Tony

 

[Cindy Winegarden]

In [email protected],

I'm a little confused about how to obtain a result set from a stored
procedure (stored in a Visual FoxPro 8.0 database) from an ASP.NET
web page in order to populate a WebForms DataGrid control.........
The VFP stored procedure is defined as follows:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application
ENDPROC

Hi Tony,

Even in Visual FoxPro this stored procedure selects some data into a local
temporary cursor and then returns "True". Have you considered SQL
Pass-through?

 

[Anthony M. Davis]

Hi Cindy. Thanks. Could you please provide an example using SQL
Pass-through?

Tony

 

[Wolfgang Schmale]

Hi Tony!

Look at the VFP help for SQL pass-through technology

--
----------------------------------------------

Mit freundlichen Grüßen

Wolfgang Schmale

[MVP für Visual FoxPro]

--------------------------------------------

 

[Anthony M. Davis]

Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing SQL
Server data from FoxPro? Please remember my data is all in FoxPro dbf's and
the stored procedure I'm calling via VFPOLEDB is in the container .DBC/.DCT.

Again, all I'm trying to do is use OleDb to call a VFP-stored procedure and
obtain a result set. I'm now wondering if it is not possible to obtain
result sets from VFP stored procedures via OleDb?? (i.e. need to upsize to
SQL Server if i want to make use of stored procedures in this manner?)

Tony

 

[Egbert Nierop \(MVP for IIS\)]

Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing SQL
Server data from FoxPro? Please remember my data is all in FoxPro dbf's and
the stored procedure I'm calling via VFPOLEDB is in the container ..DBC/.DCT.

Again, all I'm trying to do is use OleDb to call a VFP-stored procedure and
obtain a result set. I'm now wondering if it is not possible to obtain
result sets from VFP stored procedures via OleDb?? (i.e. need to upsize to
SQL Server if i want to make use of stored procedures in this manner?)

Hi,

In access, a stored procedure looks like

PARAMETERS name Text(50), id Long;
UPDATE tblName
SET lastname = [name]
WHERE name_id = [id]

You can access or declare this stuff using ADO (use a ADODB.Command object
with declared parameters and use the jet provider) as if it were a stored
proc. I suspect for 90% that something equal must be possible with Foxpro...

 

[Anthony M. Davis]

Hi Egbert. I'm able to call my stored procedure okay, but I'm only getting a
return_value back as opposed to a result set (my stored proc uses SELECT
rather than UPDATE). Here's the code I included in my original post:


OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = selectCMD;

conn.Open();

DataSet ds = new DataSet();
da.Fill(ds);

DataView dv = new DataView(ds.Tables[0]);

grdSurcharges.DataSource = dv;
grdSurcharges.DataBind();

conn.Close();


DataGrid Output:

return_value
True

My test stored procedure is defined as:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application

* note to readers: this proc will be more complex. i'm only using a
simple SELECT for test purposes.. otherwise, i have no problem changing the
OleDbCommand to "SELECT * FROM surcharge_application" and retrieving a
result set
ENDPROC

Thanks for all your input,

Tony

Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing SQL
Server data from FoxPro? Please remember my data is all in FoxPro dbf's and
the stored procedure I'm calling via VFPOLEDB is in the container .DBC/.DCT.

Again, all I'm trying to do is use OleDb to call a VFP-stored procedure and
obtain a result set. I'm now wondering if it is not possible to obtain
result sets from VFP stored procedures via OleDb?? (i.e. need to upsize to
SQL Server if i want to make use of stored procedures in this manner?)

Hi,

In access, a stored procedure looks like

PARAMETERS name Text(50), id Long;
UPDATE tblName
SET lastname = [name]
WHERE name_id = [id]

You can access or declare this stuff using ADO (use a ADODB.Command object
with declared parameters and use the jet provider) as if it were a stored
proc. I suspect for 90% that something equal must be possible with Foxpro...

 

[Anders Altberg]

Tony
There'll a frre public beta of VFP9 available in a few weeks. Be sure to
check out enhancements to the OLE DB functionality. I would think there may
be some that solve these kind of problems with stored procedures in VFP.
-Anders

 

[Cindy Winegarden]

In news: (e-mail address removed),

Hi Cindy. Thanks. Could you please provide an example using SQL
Pass-through?

Hi Tony,

You asked about SQL Pass-through in your other post. Basically it's passing
a SQL sommand in the form of a string to another database - it doesn't
matter which is the front and which is the back end. Using SQL Pass-through
you would send the string "SELECT * FROM surcharge_application" to the VFP
database.

I'm pretty sure VFP can't return a dataset from a stored procedure, but I
haven't had time to search thoroughly. Why do you think you need to call a
stored procedure instead of just sending the query string?

 

[Anthony M. Davis]

Well, the actual strored procedure would be more complex. The procedure
I've illustrated is only for testing and for the purposes of this thread..

Tony

 

[Egbert Nierop \(MVP for IIS\)]

Hi Egbert. I'm able to call my stored procedure okay, but I'm only getting a
return_value back as opposed to a result set (my stored proc uses SELECT
rather than UPDATE). Here's the code I included in my original post:

Your code, looking at the syntax is correct.

But the same with Access (and VP might be more powerfull) stored procs are
not assumed to be really working in monolithic database. The point is that
fox pro, needs to know how to execute a stored proc with multiple or
difficult statements (such as IF ELSE ). But as soon as you access, the VP
database through an OLEDB driver, the driver also should need to know how to
access and execute it. Because of limitations of the driver and because of
the monolithic characteristics, of VP you can't program stored procs as if
Fox Pro (.exe) were in memory to execute it.

I'd advise to build a dataadapter with simple SELECT/DELETE/INSERT
statements and to combine them to have, after all, the same effect as a
stored proc. Although, this is less efficient.

For real stored procs, you need a non monolithic database such as SQL server
or Oracle.

OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = selectCMD;

conn.Open();

DataSet ds = new DataSet();
da.Fill(ds);

DataView dv = new DataView(ds.Tables[0]);

grdSurcharges.DataSource = dv;
grdSurcharges.DataBind();

conn.Close();


DataGrid Output:

return_value
True

My test stored procedure is defined as:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application

* note to readers: this proc will be more complex. i'm only using a
simple SELECT for test purposes.. otherwise, i have no problem changing the
OleDbCommand to "SELECT * FROM surcharge_application" and retrieving a
result set
ENDPROC

Thanks for all your input,

Tony

accessing
SQL dbf's
and procedure
and

upsize

to

SQL Server if i want to make use of stored procedures in this manner?)

Hi,

In access, a stored procedure looks like

PARAMETERS name Text(50), id Long;
UPDATE tblName
SET lastname = [name]
WHERE name_id = [id]

You can access or declare this stuff using ADO (use a ADODB.Command object
with declared parameters and use the jet provider) as if it were a stored
proc. I suspect for 90% that something equal must be possible with Foxpro...

 

[Anthony M. Davis]

Thanks Egbert,

Well, I'll be upsizing to SQL Server in the near future. Appreciated your
input.

Tony

Hi Egbert. I'm able to call my stored procedure okay, but I'm only

getting

a
return_value back as opposed to a result set (my stored proc uses SELECT
rather than UPDATE). Here's the code I included in my original post:

Your code, looking at the syntax is correct.

But the same with Access (and VP might be more powerfull) stored procs are
not assumed to be really working in monolithic database. The point is that
fox pro, needs to know how to execute a stored proc with multiple or
difficult statements (such as IF ELSE ). But as soon as you access, the VP
database through an OLEDB driver, the driver also should need to know how to
access and execute it. Because of limitations of the driver and because of
the monolithic characteristics, of VP you can't program stored procs as if
Fox Pro (.exe) were in memory to execute it.

I'd advise to build a dataadapter with simple SELECT/DELETE/INSERT
statements and to combine them to have, after all, the same effect as a
stored proc. Although, this is less efficient.

For real stored procs, you need a non monolithic database such as SQL server
or Oracle.

OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = selectCMD;

conn.Open();

DataSet ds = new DataSet();
da.Fill(ds);

DataView dv = new DataView(ds.Tables[0]);

grdSurcharges.DataSource = dv;
grdSurcharges.DataBind();

conn.Close();


DataGrid Output:

return_value
True

My test stored procedure is defined as:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application

* note to readers: this proc will be more complex. i'm only using a
simple SELECT for test purposes.. otherwise, i have no problem changing the
OleDbCommand to "SELECT * FROM surcharge_application" and retrieving a
result set
ENDPROC

Thanks for all your input,

Tony

Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing
SQL
Server data from FoxPro? Please remember my data is all in FoxPro dbf's
and
the stored procedure I'm calling via VFPOLEDB is in the container
.DBC/.DCT.

Again, all I'm trying to do is use OleDb to call a VFP-stored procedure
and
obtain a result set. I'm now wondering if it is not possible to obtain
result sets from VFP stored procedures via OleDb?? (i.e. need to

upsize

to
SQL Server if i want to make use of stored procedures in this manner?)


Hi,

In access, a stored procedure looks like

PARAMETERS name Text(50), id Long;
UPDATE tblName
SET lastname = [name]
WHERE name_id = [id]

You can access or declare this stuff using ADO (use a ADODB.Command object
with declared parameters and use the jet provider) as if it were a stored
proc. I suspect for 90% that something equal must be possible with Foxpro...

 

[Anders Altberg]

VFP's OLE DB driver supports 122 commands, 206 functions, 15 SYS functions
and 40 SET commands. IF ..ELSE..ENDIF and SCAN - ENDSCAN are supported and
VFP's entire SQL language implementation of course.
VFP 9 is bound to enhance this in quite a few frequently requested areas.
-Anders

Hi Egbert. I'm able to call my stored procedure okay, but I'm only

getting

a
return_value back as opposed to a result set (my stored proc uses SELECT
rather than UPDATE). Here's the code I included in my original post:

Your code, looking at the syntax is correct.

But the same with Access (and VP might be more powerfull) stored procs are
not assumed to be really working in monolithic database. The point is that
fox pro, needs to know how to execute a stored proc with multiple or
difficult statements (such as IF ELSE ). But as soon as you access, the VP
database through an OLEDB driver, the driver also should need to know how to
access and execute it. Because of limitations of the driver and because of
the monolithic characteristics, of VP you can't program stored procs as if
Fox Pro (.exe) were in memory to execute it.

I'd advise to build a dataadapter with simple SELECT/DELETE/INSERT
statements and to combine them to have, after all, the same effect as a
stored proc. Although, this is less efficient.

For real stored procs, you need a non monolithic database such as SQL server
or Oracle.

OleDbCommand selectCMD = new OleDbCommand("GetSurcharges", conn);
selectCMD.CommandType = CommandType.StoredProcedure;
selectCMD.CommandTimeout = 30;

OleDbDataAdapter da = new OleDbDataAdapter();
da.SelectCommand = selectCMD;

conn.Open();

DataSet ds = new DataSet();
da.Fill(ds);

DataView dv = new DataView(ds.Tables[0]);

grdSurcharges.DataSource = dv;
grdSurcharges.DataBind();

conn.Close();


DataGrid Output:

return_value
True

My test stored procedure is defined as:

PROCEDURE GetSurcharges
SELECT * FROM surcharge_application

* note to readers: this proc will be more complex. i'm only using a
simple SELECT for test purposes.. otherwise, i have no problem changing the
OleDbCommand to "SELECT * FROM surcharge_application" and retrieving a
result set
ENDPROC

Thanks for all your input,

Tony

Sorry, I guess I don't understand.. Isn't SQL Pass-Through for accessing
SQL
Server data from FoxPro? Please remember my data is all in FoxPro dbf's
and
the stored procedure I'm calling via VFPOLEDB is in the container
.DBC/.DCT.

Again, all I'm trying to do is use OleDb to call a VFP-stored procedure
and
obtain a result set. I'm now wondering if it is not possible to obtain
result sets from VFP stored procedures via OleDb?? (i.e. need to

upsize

to
SQL Server if i want to make use of stored procedures in this manner?)


Hi,

In access, a stored procedure looks like

PARAMETERS name Text(50), id Long;
UPDATE tblName
SET lastname = [name]
WHERE name_id = [id]

You can access or declare this stuff using ADO (use a ADODB.Command object
with declared parameters and use the jet provider) as if it were a stored
proc. I suspect for 90% that something equal must be possible with Foxpro...

 

[Egbert Nierop \(MVP for IIS\)]

VFP's OLE DB driver supports 122 commands, 206 functions, 15 SYS functions
and 40 SET commands. IF ..ELSE..ENDIF and SCAN - ENDSCAN are supported and
VFP's entire SQL language implementation of course.
VFP 9 is bound to enhance this in quite a few frequently requested areas.
-Anders

Great.
I'm surprised. This might void my answer to Anthony. But I was right that
oledb/FP is a monolithic approach.

 

[Anders Altberg]

Monolithic, well that depends on which backend you use in a VFP application,
or what front-end you use in to drive a VFP backend.
-Anders