Professional Web Applications Themes

Stored Procedures - Coldfusion - Advanced Techniques

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

  1. #1

    Default Stored Procedures

    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


    Anthony M. Davis Guest

  2. #2

    Default Re: Stored Procedures

    In news: esIC$wePEHA.2704TK2MSFTNGP10.phx.gbl,
    Anthony M. Davis <tonyfnwarranty.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.winegardenmvps.org[/email] [url]www.cindywinegarden.com[/url]



    Cindy Winegarden Guest

  3. #3

    Default Re: Stored Procedures

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

    Tony

    "Cindy Winegarden" <cindy.winegardenmvps.org> wrote in message
    news:%230sF6vgPEHA.2468TK2MSFTNGP11.phx.gbl...
    > In news: esIC$wePEHA.2704TK2MSFTNGP10.phx.gbl,
    > Anthony M. Davis <tonyfnwarranty.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.winegardenmvps.org[/email] [url]www.cindywinegarden.com[/url]
    >
    >
    >

    Anthony M. Davis Guest

  4. #4

    Default Re: Stored Procedures

    Hi Tony!

    Look at the VFP help for SQL pass-through technology

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

    Mit freundlichen Grüßen

    Wolfgang Schmale

    [MVP für Visual FoxPro]

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


    Wolfgang Schmale Guest

  5. #5

    Default Re: Stored Procedures

    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" <Spamkocherstrang.lt> wrote in message
    news:%23Eb$sRrPEHA.3476tk2msftngp13.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]
    >
    > --------------------------------------------
    >
    >

    Anthony M. Davis Guest

  6. #6

    Default Re: Stored Procedures

    "Anthony M. Davis" <tonyfnwarranty.com> wrote in message
    news:OGbG9suPEHA.1312TK2MSFTNGP12.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\) Guest

  7. #7

    Default Re: Stored Procedures

    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_nieropnospam.invalid> wrote in
    message news:%23D4JMByPEHA.3012tk2msftngp13.phx.gbl...
    > "Anthony M. Davis" <tonyfnwarranty.com> wrote in message
    > news:OGbG9suPEHA.1312TK2MSFTNGP12.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 Guest

  8. #8

    Default Re: Stored Procedures

    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" <tonyfnwarranty.com> wrote in message
    news:esIC$wePEHA.2704TK2MSFTNGP10.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
    >
    >
    Anders Altberg Guest

  9. #9

    Default Re: Stored Procedures

    In news: [email]eFmBmHiPEHA.2128TK2MSFTNGP11.phx.gbl[/email],
    Anthony M. Davis <davisam2bellsouth.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.winegardenmvps.org[/email] [url]www.cindywinegarden.com[/url]



    Cindy Winegarden Guest

  10. #10

    Default Re: Stored Procedures

    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.winegardenmvps.org> wrote in message
    news:eQybrOCQEHA.620TK2MSFTNGP10.phx.gbl...
    > In news: [email]eFmBmHiPEHA.2128TK2MSFTNGP11.phx.gbl[/email],
    > Anthony M. Davis <davisam2bellsouth.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.winegardenmvps.org[/email] [url]www.cindywinegarden.com[/url]
    >
    >
    >

    Anthony M. Davis Guest

  11. #11

    Default Re: Stored Procedures

    "Anthony M. Davis" <davisam2bellsouth.net> wrote in message
    news:OFoDP2yPEHA.3456TK2MSFTNGP11.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_nieropnospam.invalid> wrote in
    > message news:%23D4JMByPEHA.3012tk2msftngp13.phx.gbl...
    > > "Anthony M. Davis" <tonyfnwarranty.com> wrote in message
    > > news:OGbG9suPEHA.1312TK2MSFTNGP12.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\) Guest

  12. #12

    Default Re: Stored Procedures

    Thanks Egbert,

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

    Tony

    "Egbert Nierop (MVP for IIS)" <egbert_nieropnospam.invalid> wrote in
    message news:OT$LbAdQEHA.3348TK2MSFTNGP09.phx.gbl...
    > "Anthony M. Davis" <davisam2bellsouth.net> wrote in message
    > news:OFoDP2yPEHA.3456TK2MSFTNGP11.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_nieropnospam.invalid> wrote in
    > > message news:%23D4JMByPEHA.3012tk2msftngp13.phx.gbl...
    > > > "Anthony M. Davis" <tonyfnwarranty.com> wrote in message
    > > > news:OGbG9suPEHA.1312TK2MSFTNGP12.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 Guest

  13. #13

    Default Re: Stored Procedures

    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_nieropnospam.invalid> wrote in
    message news:OT$LbAdQEHA.3348TK2MSFTNGP09.phx.gbl...
    > "Anthony M. Davis" <davisam2bellsouth.net> wrote in message
    > news:OFoDP2yPEHA.3456TK2MSFTNGP11.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_nieropnospam.invalid> wrote in
    > > message news:%23D4JMByPEHA.3012tk2msftngp13.phx.gbl...
    > > > "Anthony M. Davis" <tonyfnwarranty.com> wrote in message
    > > > news:OGbG9suPEHA.1312TK2MSFTNGP12.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 Guest

  14. #14

    Default Re: Stored Procedures

    "Anders Altberg" <x_pragmatelia.com> wrote in message
    news:%237TmvpmQEHA.3580TK2MSFTNGP11.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_nieropnospam.invalid> wrote in
    > message news:OT$LbAdQEHA.3348TK2MSFTNGP09.phx.gbl...
    > > "Anthony M. Davis" <davisam2bellsouth.net> wrote in message
    > > news:OFoDP2yPEHA.3456TK2MSFTNGP11.phx.gbl...
    Egbert Nierop \(MVP for IIS\) Guest

  15. #15

    Default Re: Stored Procedures

    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_nieropnospam.invalid> wrote in
    message news:eCYMVnpQEHA.2936TK2MSFTNGP12.phx.gbl...
    > "Anders Altberg" <x_pragmatelia.com> wrote in message
    > news:%237TmvpmQEHA.3580TK2MSFTNGP11.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_nieropnospam.invalid> wrote in
    > > message news:OT$LbAdQEHA.3348TK2MSFTNGP09.phx.gbl...
    > > > "Anthony M. Davis" <davisam2bellsouth.net> wrote in message
    > > > news:OFoDP2yPEHA.3456TK2MSFTNGP11.phx.gbl...
    >
    Anders Altberg Guest

  16. #16

    Default Stored Procedures

    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 yzer


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



    BillB Guest

  17. #17

    Default Stored Procedures

    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

    Ted Guest

  18. #18

    Default Re: Stored Procedures

    "Ted" <r.ted.byersrogers.com> wrote in message
    news:1135217853.968205.237550g47g2000cwa.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.


    Bill Karwin Guest

  19. #19

    Default Re: Stored Procedures

    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

    Ted Guest

  20. #20

    Default Re: Stored Procedures

    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
    >
    Dikkie Dik Guest

Page 1 of 2 12 LastLast

Similar Threads

  1. Stored Procedures JDBC
    By mighty wayward in forum Coldfusion - Advanced Techniques
    Replies: 6
    Last Post: June 14th, 11:00 AM
  2. dt_ Stored Procedures
    By chopper in forum ASP Database
    Replies: 2
    Last Post: July 20th, 04:06 PM
  3. New to ASP and Stored Procedures
    By John Berman in forum ASP Database
    Replies: 6
    Last Post: February 28th, 01:37 AM
  4. Stored Procedures and 4GL
    By Ahmer Sajjad in forum Informix
    Replies: 1
    Last Post: September 9th, 01:23 PM
  5. Your opinion about stored procedures
    By mono in forum ASP.NET General
    Replies: 3
    Last Post: July 26th, 08:23 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139