Stored procedures and views
Howdy folks, 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? 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. Thanks, Chris Carrier -- 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/[EMAIL PROTECTED]
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/[EMAIL PROTECTED]
RE: Stored procedures and views
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? Chris -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, March 24, 2006 10:26 AM To: mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- 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/[EMAIL PROTECTED]
Re: Stored procedures and views
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? hook? If you mean: using the views in select statements inside a procedure: sure it. Did you try? 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 Chris -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, March 24, 2006 10:26 AM To: mysql@lists.mysql.com 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/[EMAIL PROTECTED] -- 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/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedures and views
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.
I think I might write 2 procedures. One that does the data manipulation and
the other that selects the result.
something like this
DELIMITER //
DROP PROCEDURE IF EXITS manipulate//
CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result
...
...
END//
DROP PROCEDURE IF EXITS result//
CREATE PROCEDURE result(
...
CALL manipulate (
...
SELECT ... FROM data_result
END//
DELIMITER ;
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 1:04 PM
To: Chris Carrier; mysql@lists.mysql.com
Subject: Re: Stored procedures and views
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?
hook?
If you mean: using the views in select statements inside a procedure:
sure it. Did you try?
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
Chris
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 10:26 AM
To: mysql@lists.mysql.com
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/[EMAIL PROTECTED]
--
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/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
triggers, stored procedures, and views
I am building a database system and have been, like many before me, quite surprised that MySQL doesn't support triggers, sp's, and views. I can see how one can get around not having views with advanced permissions, as stated in the manual, although as I understand it this is no substitue for views- it is simply a workaround. I want to be very clear in explicating my understanding that MysQL is an open source technology, and putting it next to MS sQL Server 7 is not a fair comparison, as MS has invested many millions in that product over the years developing features...as has Oracle. It's kind of like putting AbiWord next to MS Word...apples to oranges... But not having triggers DOES make implementation of a transactional layer very difficult. I'm wondering if anyone that has encountered this problem and found a suitable solution would be so kind as to share his/her experience with myself and the rest of the list? Or do the developers have any suggestions? Perhaps a collection of Perl scripts sending records to a transactional table as well as the main data tables??? but then I would have to code this for all the different operations that take place when one of the data editors touches the database. Thanks in advance to anyone willing to approach this subject and share their experiences. Regards, jd -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 6:26 AM To: [EMAIL PROTECTED] Subject: Is Triggers Possible in MySQL sreedhar, Wednesday, January 30, 2002, 6:02:35 AM, you wrote: s Hi All s Is Triggers Possible in MySQL. If Yes, It is like in other RDBMS. MySQL doesn't support triggers, look at: http://www.mysql.com/doc/A/N/ANSI_diff_Triggers.html s regards, s SREEDHAR -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: triggers, stored procedures, and views
Sounds like we just need a table to keep track of LAST_INSERT_ID per table. C:heers -Original Message- From: jds [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 7:05 AM To: [EMAIL PROTECTED] Cc: David T. Anderson Subject: triggers, stored procedures, and views I am building a database system and have been, like many before me, quite surprised that MySQL doesn't support triggers, sp's, and views. I can see how one can get around not having views with advanced permissions, as stated in the manual, although as I understand it this is no substitue for views- it is simply a workaround. I want to be very clear in explicating my understanding that MysQL is an open source technology, and putting it next to MS sQL Server 7 is not a fair comparison, as MS has invested many millions in that product over the years developing features...as has Oracle. It's kind of like putting AbiWord next to MS Word...apples to oranges... But not having triggers DOES make implementation of a transactional layer very difficult. I'm wondering if anyone that has encountered this problem and found a suitable solution would be so kind as to share his/her experience with myself and the rest of the list? Or do the developers have any suggestions? Perhaps a collection of Perl scripts sending records to a transactional table as well as the main data tables??? but then I would have to code this for all the different operations that take place when one of the data editors touches the database. Thanks in advance to anyone willing to approach this subject and share their experiences. Regards, jd -Original Message- From: Victoria Reznichenko [mailto:[EMAIL PROTECTED]] Sent: Wednesday, January 30, 2002 6:26 AM To: [EMAIL PROTECTED] Subject: Is Triggers Possible in MySQL sreedhar, Wednesday, January 30, 2002, 6:02:35 AM, you wrote: s Hi All s Is Triggers Possible in MySQL. If Yes, It is like in other RDBMS. MySQL doesn't support triggers, look at: http://www.mysql.com/doc/A/N/ANSI_diff_Triggers.html s regards, s SREEDHAR -- For technical support contracts, goto https://order.mysql.com/ This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
