Stuart McLachlan
stuart at lexacorp.com.pg
Mon Feb 25 19:26:36 CST 2013
Don't use AS - that's MS TSQL.
DROP PROCEDURE IF EXISTS mySP$$
CREATE PROCEDURE mySP
BEGIN
...
END$$
On 25 Feb 2013 at 20:14, Arthur Fuller wrote:
> So the MySQL would read:
>
> DROP PROCEDURE mySP IF EXISTS$$
> CREATE PROCEDURE mySP
> AS
> etc
> $$
>
> A.
>
>
> On Mon, Feb 25, 2013 at 7:56 PM, Stuart McLachlan <stuart at lexacorp.com.pg>wrote:
>
> > My bad.
> >
> > > Create or replace procedure ShowDistrict(DistID int)
> >
> > That doesn't work in MySQL.
> >
> > Lose the "or replace". If the SP already exists, you need to DROP it
> > first.
> >
> > --
> > Stuart
> >
> > On 26 Feb 2013 at 10:46, Stuart McLachlan wrote:
> >
> > > Delimiter $$
> > > Create or replace procedure ShowDistrict(DistID int)
> > > Begin
> > > Select * from tblDistricts where DistrictPK = DistID;
> > > End$$
> > >
> > > Then:
> > >
> > > You would put: Call ShowDistrict(1)
> > > as the SQL of your passthrough query
> > >
> > >
> > > Since you can't stipulate parameters for a pass-through query, You
> > will have to rewrite the
> > > querydef before calling.the procedure to pull the correct record..
> > >
> > > Assume that your have bound your form to the recordsource
> > "qryShowDistrict" and that on
> > > your display form, you have an unbound textbox txtDistrictID and a
> > button in addition to the
> > > "bound" fields. You would do this:
> > >
> > > Private Sub CmdShowDistrict_Click()
> > > Dim qd As DAO.QueryDef
> > > Set qd = CurrentDb.QueryDefs("qryShowDistrict")
> > > qd.SQL = "CALL SHowDistrict(" & txtDistrictID & ")"
> > > Me.Requery
> > > End Sub
> > >
> > >
> > >
> > > On 25 Feb 2013 at 19:05, Arthur Fuller wrote:
> > >
> > > > Ah! Thanks so much! I hope to be off to the races now. I had no idea
> > you
> > > > could use CALL in a pass-through query.
> > > >
> > > > This begs a question. Assuming the SP accepts parameters, how to pass
> > them
> > > > in the CALL statement? just type them in after the SP name?
> > > >
> > > > Thanks,
> > > > A.
> > > >
> > > >
> > > > On Mon, Feb 25, 2013 at 5:58 PM, Stuart McLachlan <
> > stuart at lexacorp.com.pg>wrote:
> > > >
> > > > > You use CALL, not SELECT ....
> > > > >
> > > > > Here's a simple example:
> > > > >
> > > > > In MySQL, I run the following SQL:
> > > > >
> > > > > CREATE PROCEDURE ShowDistricts()
> > > > > BEGIN
> > > > > SELECT * FROM tblDistricts
> > > > > END;
> > > > >
> > > > >
> > > > > In Access I create a Passthrough query:
> > > > >
> > > > > In SQL view I just put: CALL ShowDistricts()
> > > > > and for the ODBC Connect string, I either create a suitable
> > COnnection
> > > > > string or point to an
> > > > > existing DSN for the MySQL database.
> > > > >
> > > > > I save the query. I then create a new form and set its
> > RecordSource to
> > > > > the name of that
> > > > > query.
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > On 25 Feb 2013 at 17:41, Arthur Fuller wrote:
> > > > >
> > > > > > I'm trying to stick with you on this, Stuart, but I don't
> > understand what
> > > > > > you mean by "put the stored procedure in a pass-thru query. If the
> > SP
> > > > > says
> > > > > > "SELECT * FROM someTables" then how do I "put it in"? SELECT FROM
> > > > > procName?
> > > > > > That doesn't make sense. Can you please explain?
> > > > > >
> > > > > > TIA,
> > > > > > Arthur
> > > > > >
> > > > > >
> > > > > > On Mon, Feb 25, 2013 at 5:35 PM, Stuart McLachlan <
> > > > > stuart at lexacorp.com.pg>wrote:
> > > > > >
> > > > > > > You can "Bind" the recordset from a stored procedure by putting
> > that
> > > > > > > procedure in a
> > > > > > > pass-trhough query and "binding" to the query
> > > > > > >
> > > > > > > Note the quotes around "bind". Recordset returned by SPs are
> > > > > snapshots,
> > > > > > > there is no
> > > > > > > persistent connection. So you only view/filter/sort the
> > records. You
> > > > > > > can't create/edit/delete
> > > > > > > any.
> > > > > > >
> > > > > > > --
> > > > > > > Stuart
> > > > > > >
> > > > > > >
> > > > > > _______________________________________________
> > > > > > dba-SQLServer mailing list
> > > > > > dba-SQLServer at databaseadvisors.com
> > > > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > > > > http://www.databaseadvisors.com
> > > > > >
> > > > > >
> > > > >
> > > > >
> > > > > _______________________________________________
> > > > > dba-SQLServer mailing list
> > > > > dba-SQLServer at databaseadvisors.com
> > > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > > > http://www.databaseadvisors.com
> > > > >
> > > > >
> > > >
> > > >
> > > > --
> > > > Arthur
> > > > Cell: 647.710.1314
> > > >
> > > > Prediction is difficult, especially of the future.
> > > > -- Niels Bohr
> > > > _______________________________________________
> > > > dba-SQLServer mailing list
> > > > dba-SQLServer at databaseadvisors.com
> > > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > > http://www.databaseadvisors.com
> > > >
> > > >
> > >
> > >
> > > _______________________________________________
> > > dba-SQLServer mailing list
> > > dba-SQLServer at databaseadvisors.com
> > > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > > http://www.databaseadvisors.com
> > >
> > >
> >
> >
> > _______________________________________________
> > dba-SQLServer mailing list
> > dba-SQLServer at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> > http://www.databaseadvisors.com
> >
> >
>
>
> --
> Arthur
> Cell: 647.710.1314
>
> Prediction is difficult, especially of the future.
> -- Niels Bohr
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>