From: Gordon
Date: March 24 2006 7:49pm
Subject: RE: Stored procedures and views
List-Archive: http://lists.mysql.com/mysql/196141
Message-Id: <01b301c64f7c$1f3f77f0$0c01a8c0@gbhome1>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="us-ascii"
Content-Transfer-Encoding: quoted-printable



If the features available in stored procedures {i.e. looping etc.} are
required, have you tried having the procedure 1st write/edit the data in =
a
temporary table and at the end select the values you want from that =
table.=20

I think I might write 2 procedures. One that does the data manipulation =
and
the other that selects the result.=20
something like this =20

DELIMITER //
DROP PROCEDURE IF EXITS manipulate//

CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result

...
...
END//=20

DROP PROCEDURE IF EXITS result//

CREATE PROCEDURE result(
...
  CALL manipulate (
...
  SELECT ... FROM data_result
END//

DELIMITER ;
> -----Original Message-----
> From: Martijn Tonies [mailto:m.tonies@stripped]
> Sent: Friday, March 24, 2006 1:04 PM
> To: Chris Carrier; mysql@stripped
> Subject: Re: Stored procedures and views
>=20
>=20
>=20
> > I just mean is it possible to hook a set of stored procedures to a =
view
> as
> > opposed to a temporary table that we'd have to explicitly update?
>=20
> "hook"?
>=20
> If you mean: using the views in select statements inside a procedure:
> sure it. Did you try?
>=20
> Martijn Tonies
> Database Workbench - development tool for MySQL, and more!
> Upscene Productions
> http://www.upscene.com
> My thoughts:
> http://blog.upscene.com/martijn/
> Database development questions? Check the forum!
> http://www.databasedevelopmentforum.com
>=20
> > Chris
> >
> > -----Original Message-----
> > From: Martijn Tonies [mailto:m.tonies@stripped]
> > Sent: Friday, March 24, 2006 10:26 AM
> > To: mysql@stripped
> > Subject: Re: Stored procedures and views
> >
> > Chriss,
> >
> > > I'm trying to create a flatfile dump from our database which =
requires
> some
> > > functionality that's not possible with raw sql.  Up to now we've =
been
> > using
> > > PHP tied to mySql to do all the work and get the data in the =
correct
> > format.
> > > We would like to switch this functionality over to stored =
procedures
> in
> > > mySql.  My question is this: Is it possible to hook a series of =
stored
> > > procedures to a view so that the data is live and current?
> >
> > What do you mean by that?
> >
> > A view is always current.
> >
> > Can you explain it a bit better?
> >
> > >We could set it
> > > up to run our procedures on some regular interval and dump the =
result
> into
> > a
> > > temporary table but having up-to-date data would be ideal.
> >
> > Martijn Tonies
> > Database Workbench - development tool for MySQL, and more!
> > Upscene Productions
> > http://www.upscene.com
> > My thoughts:
> > http://blog.upscene.com/martijn/
> > Database development questions? Check the forum!
> > http://www.databasedevelopmentforum.com
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> > http://lists.mysql.com/mysql?unsub=3Dchriscarrier@stripped
> >
> >
> > --
> > No virus found in this incoming message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: =
3/24/2006
> >
> >
> > --
> > No virus found in this outgoing message.
> > Checked by AVG Free Edition.
> > Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: =
3/24/2006
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:
> http://lists.mysql.com/mysql?unsub=3Dm.tonies@stripped
>=20
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:    =
http://lists.mysql.com/mysql?unsub=3Dgordon@stripped