[dba-SQLServer] MySQL not MS SQL question

Wow. Very interesting. I'm off to the lab to get my typing fingers dirty!

Thanks.
A.


On Mon, Feb 25, 2013 at 7:46 PM, Stuart McLachlan <stuart at lexacorp.com.pg>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
>
>


-- 
Arthur
Cell: 647.710.1314

Prediction is difficult, especially of the future.
  -- Niels Bohr