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 news: esIC$wePEHA.2704@TK2MSFTNGP10.phx.gbl,
Anthony M. Davis <tony@fnwarranty.com> wrote:

> 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?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
[email]cindy.winegarden@mvps.org[/email] [url]www.cindywinegarden.com[/url]

[Anthony M. Davis]

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

Tony

"Cindy Winegarden" <cindy.winegarden@mvps.org> wrote in message
news:%230sF6vgPEHA.2468@TK2MSFTNGP11.phx.gbl...

> In news: esIC$wePEHA.2704@TK2MSFTNGP10.phx.gbl,
> Anthony M. Davis <tony@fnwarranty.com> wrote:

> > 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?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> [email]cindy.winegarden@mvps.org[/email] [url]www.cindywinegarden.com[/url]
>
>
>

[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

"Wolfgang Schmale" <Spamkocher@strang.lt> wrote in message
news:%23Eb$sRrPEHA.3476@tk2msftngp13.phx.gbl...

> Hi Tony!
>
> Look at the VFP help for SQL pass-through technology
>
> --
> ----------------------------------------------
>
> Mit freundlichen Grüßen
>
> Wolfgang Schmale
>
> [MVP für Visual FoxPro]
>
> --------------------------------------------
>
>

[Egbert Nierop \(MVP for IIS\)]

"Anthony M. Davis" <tony@fnwarranty.com> wrote in message
news:OGbG9suPEHA.1312@TK2MSFTNGP12.phx.gbl...

> 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

"Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
message news:%23D4JMByPEHA.3012@tk2msftngp13.phx.gbl...

> "Anthony M. Davis" <tony@fnwarranty.com> wrote in message
> news:OGbG9suPEHA.1312@TK2MSFTNGP12.phx.gbl...

> > 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

"Anthony M. Davis" <tony@fnwarranty.com> wrote in message
news:esIC$wePEHA.2704@TK2MSFTNGP10.phx.gbl...

> 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 news: [email]eFmBmHiPEHA.2128@TK2MSFTNGP11.phx.gbl[/email],
Anthony M. Davis <davisam2@bellsouth.net> wrote:

>>> 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?

> 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?

--
Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
[email]cindy.winegarden@mvps.org[/email] [url]www.cindywinegarden.com[/url]

[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


"Cindy Winegarden" <cindy.winegarden@mvps.org> wrote in message
news:eQybrOCQEHA.620@TK2MSFTNGP10.phx.gbl...

> In news: [email]eFmBmHiPEHA.2128@TK2MSFTNGP11.phx.gbl[/email],
> Anthony M. Davis <davisam2@bellsouth.net> wrote:

> >>> 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?

>

> > 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?
>
> --
> Cindy Winegarden MCSD, Microsoft Visual FoxPro MVP
> [email]cindy.winegarden@mvps.org[/email] [url]www.cindywinegarden.com[/url]
>
>
>

[Egbert Nierop \(MVP for IIS\)]

"Anthony M. Davis" <davisam2@bellsouth.net> wrote in message
news:OFoDP2yPEHA.3456@TK2MSFTNGP11.phx.gbl...

> 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
>
> "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
> message news:%23D4JMByPEHA.3012@tk2msftngp13.phx.gbl...

> > "Anthony M. Davis" <tony@fnwarranty.com> wrote in message
> > news:OGbG9suPEHA.1312@TK2MSFTNGP12.phx.gbl...

> > > 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]

Thanks Egbert,

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

Tony

"Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
message news:OT$LbAdQEHA.3348@TK2MSFTNGP09.phx.gbl...

> "Anthony M. Davis" <davisam2@bellsouth.net> wrote in message
> news:OFoDP2yPEHA.3456@TK2MSFTNGP11.phx.gbl...

> > 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
> >
> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
> > message news:%23D4JMByPEHA.3012@tk2msftngp13.phx.gbl...

> > > "Anthony M. Davis" <tony@fnwarranty.com> wrote in message
> > > news:OGbG9suPEHA.1312@TK2MSFTNGP12.phx.gbl...
> > > > 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

"Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
message news:OT$LbAdQEHA.3348@TK2MSFTNGP09.phx.gbl...

> "Anthony M. Davis" <davisam2@bellsouth.net> wrote in message
> news:OFoDP2yPEHA.3456@TK2MSFTNGP11.phx.gbl...

> > 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
> >
> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
> > message news:%23D4JMByPEHA.3012@tk2msftngp13.phx.gbl...

> > > "Anthony M. Davis" <tony@fnwarranty.com> wrote in message
> > > news:OGbG9suPEHA.1312@TK2MSFTNGP12.phx.gbl...
> > > > 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\)]

"Anders Altberg" <x_pragma@telia.com> wrote in message
news:%237TmvpmQEHA.3580@TK2MSFTNGP11.phx.gbl...

> 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.

> "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
> message news:OT$LbAdQEHA.3348@TK2MSFTNGP09.phx.gbl...

> > "Anthony M. Davis" <davisam2@bellsouth.net> wrote in message
> > news:OFoDP2yPEHA.3456@TK2MSFTNGP11.phx.gbl...

[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

"Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
message news:eCYMVnpQEHA.2936@TK2MSFTNGP12.phx.gbl...

> "Anders Altberg" <x_pragma@telia.com> wrote in message
> news:%237TmvpmQEHA.3580@TK2MSFTNGP11.phx.gbl...

> > 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.
>

> > "Egbert Nierop (MVP for IIS)" <egbert_nierop@nospam.invalid> wrote in
> > message news:OT$LbAdQEHA.3348@TK2MSFTNGP09.phx.gbl...

> > > "Anthony M. Davis" <davisam2@bellsouth.net> wrote in message
> > > news:OFoDP2yPEHA.3456@TK2MSFTNGP11.phx.gbl...

>

[BillB]

Hi All

I'm trying create my first stored procedure. Can anyone tell me why this
isn't working.

BTW this was created by the program at [url]www.switch-box.org[/url] and I'm trying to
run it in Query Analyzer


if exists (select * from dbo.sysobjects where id = object_id(N'[ pr_
news_nws_SEL ]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [pr_ news_nws_SEL]
GO

IF OBJECT_ID(N'[pr_ news_nws_SEL ]') IS NOT NULL
SELECT '<<< FAILED DROPPING PROCEDURE pr_ news_nws_SEL >>>'AS Report
ELSE
SELECT '<<< DROPPED PROCEDURE pr_ news_nws_SEL >>>'AS Report
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS OFF
GO

CREATE PROCEDURE pr_ news_nws_SEL
AS
BEGIN
BEGIN TRAN
SELECT TOP 100 PERCENT
[id_nws],[idctg_nws],[date_nws],[title_nws],[desc_nws],[shdesc_nws],[visible_nws]
FROM bill.news_nws
WHERE 0=0

IF (@@error!=0)
BEGIN
RAISERROR 20000 'pr_ news_nws_SEL : Cannot Select data from bill.news_nws'
ROLLBACK TRAN
RETURN(1)
END

COMMIT TRAN
RETURN(0)
END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

IF OBJECT_ID(N'[pr_ news_nws_SEL ]') IS NOT NULL
SELECT '<<< PROCEDURE pr_ news_nws_SEL Exists >>>' AS Report
ELSE
SELECT '<<< PROCEDURE pr_ news_nws_SEL Does Not Exists >>>' AS Report
GO

When I run the Parse Query I get this error

Server: Msg 170, Level 15, State 1, Procedure pr_, Line 2
Line 2: Incorrect syntax near 'news_nws_SEL'.


Thank you

Bill

[Ted]

OK, I have been studying stored procedures. There are a couple things
that aren't clear.

1) How do you deal with the possibility that a call may be made to a
given stored procedure before the previous call is finished? This
wouldn't make a difference if the SQL involved involves simple SELECT
statements only, but it could create problems if the procedure consists
largely of INSERT statements. (e.g. if an integer column is used, and
the developer, foolishly, first executes an SQL to find the maximum
value of ndx, and then executes a second SQL statement to insert the
required data into the first table, including one plus the maximum
value of ndx - and it gets worse if the procedure begins with an SQL
statement to determine whethe or not the data to be inserted already
exists in the table).

2) How do you use data from one SQL statement in a second within the
procedure?

A simple example will serve to illustrate both issues. Suppose we have
two tables, and the first has an autoincremented int variable, called
'ndx', that serves as both a primary key in the first table and a
foreign key for the second. The stored procedure, called with all the
required values first inserts the data for the first table EXCEPT for
the index column. Since the index column is an autoincremented
integer, MySQL will increment it for me during this first insert. But
I need to retrieve that value and use it in a second insert, into the
second table, as the foreign key. I haven't found an illustration of
the SQL required to do this within a stored procedure.

I have not had an opportunity to work with stored procedures before, so
I am still learning about them. But I am a quick study, so a little
simple SQL example would be appreciated.

Thanks

Ted

[Bill Karwin]

"Ted" <r.ted.byers@rogers.com> wrote in message
news:1135217853.968205.237550@g47g2000cwa.googlegr oups.com...

> 2) How do you use data from one SQL statement in a second within the
> procedure?
> ... Since the index column is an autoincremented
> integer, MySQL will increment it for me during this first insert. But
> I need to retrieve that value and use it in a second insert, into the
> second table, as the foreign key.

I assume the answer to this case is the same as if you were doing it in
application code.
Use the LAST_INSERT_ID() function.

See [url]http://dev.mysql.com/doc/refman/5.0/en/getting-unique-id.html[/url]
and [url]http://dev.mysql.com/doc/refman/5.0/en/information-functions.html[/url]

Regards,
Bill K.

[Ted]

Thanks. That is just what I need. I didn't know LAST_INSERT_ID()
existed. None of my database/SQL books mention it. Is it standard SQL
or something unique to MySQL? Would I, for example, find it, or
something similar, in e.g. Postgres?

Thanks again,

Ted

[Dikkie Dik]

If I am correct, there is no way of determining an autoincrement value
in standard SQL. An alternative that uses only standard SQL keywords
(but is not standard either) is:

SELECT <autoincrement column> WHERE <autoincrement column> IS NULL;

According to the manual, this feature exists for compatibility with
other databases. I find LAST_INSERT_ID() much clearer, so I have never
used the above select query.
If you want to use the LAST_INSERT_ID() value in more than one table,
just put it in a variable:

SET @RootTableId = LAST_INSERT_ID();
INSERT INTO BranchTable(RootId) VALUES(@RootTableId);
INSERT INTO OtherBranchTable(RootId) VALUES(@RootTableId);

etc.

Best regards

Ted wrote:

> Thanks. That is just what I need. I didn't know LAST_INSERT_ID()
> existed. None of my database/SQL books mention it. Is it standard SQL
> or something unique to MySQL? Would I, for example, find it, or
> something similar, in e.g. Postgres?
>
> Thanks again,
>
> Ted
>