Re: Stored Procedures from SQL Server

[Peter Brawley]

 Does anyone have examples on creating stored procedures?

There are quite a few in 
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html


PB

-

ML wrote:
I am working on moving a database from SQL Server to MySQL 5. Tables, 
data, etc I already know.


I have some procedures that i would like to move as well.

Does anyone have examples on creating stored procedures?

Best,

-Jason





No virus found in this incoming message.
Checked by AVG - www.avg.com 
Version: 8.5.375 / Virus Database: 270.13.7/ - Release Date: 07/07/09 05:53:00


  

RE: Stored Procedures Question

[Daniel Woods]

You need create your select statement using a variable and the do the
prepare statement;

Drop Procedure If Exists RankStock;
DELIMITER |
Create Procedure RankStock(IN TableNameIn CHAR(20),IN ColNameIn
CHAR(20))
begin
   Set @selStmt = Concat(Select ,ColNameIn, from ,TableNameIn,
order by ,ColNameIn); 
   Prepare s1 from @selStmt;
   Execute s1;
   Deallocate Prepare s1;
END|
DELIMITER ;


Dan 

-Original Message-
From: mos [mailto:[EMAIL PROTECTED] 
Sent: Wednesday, October 04, 2006 10:58 AM
To: mysql@lists.mysql.com
Subject: Stored Procedures Question

I have a dozen tables and I need to perform the same operations on them
so I thought I'd create a stored procedure and just pass the table name,
column name.

I was hoping it would take the parameters and substitute that in the
code. 
So it looks like this:

create procedure RankStock(IN TableNameIn CHAR(20),
IN ColNameIn   CHAR(20))
begin
   select ColNameIn from TableNameIn order by ColNameIn; end;

Of course this produces errors because it doesn't like the TableNameIn
in the select statement. So from all this I have to assume I can't build
a Select statement by using passed parameters. Is that right?

TIA
Mike 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
This email is intended only for the person or entity
to which it is addressed and may contain 
confidential information.  Any review, 
retransmission, dissemination or other use of, or 
taking of any action in reliance upon, this 
information by persons or entities other than the 
intended recipient is prohibited.  If you receive this 
e-mail in error, please contact the sender by 
replying to this e-mail and delete this e-mail and 
any attachments from all computers without 
reading or saving the same in any matter 
whatsoever.



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Jon]

Thanks both Devanada and Peter, your replies helped me resolve it.

/Jon

On 7/30/06, Peter Brawley [EMAIL PROTECTED] wrote:


 *I'm trying to make stored procedures use parameters for limit and
tables, I
guess this is either me using the wrong datatype or it's not possible.
I'm
having the same issue with seting the table for the query:
*
SET @sql = CONCAT( 'select * from some_table limit ', some_limit );
PREPARE stmt FROM @sql
etc.

PB

-


Devananda wrote:

Jon wrote:

Hi list

I'm trying to make stored procedures use parameters for limit and tables,
I
guess this is either me using the wrong datatype or it's not possible.
I'm
having the same issue with seting the table for the query:

CREATE  PROCEDURE sp_test1 (IN some_limit int)
select * from some_table limit some_limit;

and
CREATE  PROCEDURE sp_test2 (IN some_table table)
select * from some_table;


Both fail with ERROR 1064 (42000).

Can someone please shed some light on this for me? Is this a problem with
procedures not being able to do this or is it wrong datatypes or something

completely different?

Thanks in advance
Jon


It sounds like what you need is dynamic SQL in your stored procedures.
Check out http://forge.mysql.com/snippets/view.php?id=13 for some good
examples, more complex than what you're asking about, but they should shed
light on what you need to do.


Regards,
Devananda


No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/403 - Release Date: 7/28/2006

Re: Stored procedures

[Devananda]

Jon wrote:

Hi list

I'm trying to make stored procedures use parameters for limit and tables, I
guess this is either me using the wrong datatype or it's not possible.  I'm
having the same issue with seting the table for the query:

CREATE  PROCEDURE sp_test1 (IN some_limit int)
select * from some_table limit some_limit;

and
CREATE  PROCEDURE sp_test2 (IN some_table table)
select * from some_table;


Both fail with ERROR 1064 (42000).

Can someone please shed some light on this for me? Is this a problem with
procedures not being able to do this or is it wrong datatypes or something
completely different?

Thanks in advance
Jon



It sounds like what you need is dynamic SQL in your stored procedures. 
Check out http://forge.mysql.com/snippets/view.php?id=13 for some good 
examples, more complex than what you're asking about, but they should 
shed light on what you need to do.



Regards,
Devananda

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Peter Brawley]

I'm trying to make stored procedures use parameters for limit
and tables, I

guess this is either me using the wrong datatype or it's not
possible. I'm

having the same issue with seting the table for the query:


SET @sql = CONCAT( 'select * from some_table limit ', some_limit );
PREPARE stmt FROM @sql
etc.

PB

-

Devananda wrote:
Jon wrote:
  
  Hi list


I'm trying to make stored procedures use parameters for limit and
tables, I

guess this is either me using the wrong datatype or it's not possible.
I'm

having the same issue with seting the table for the query:


CREATE PROCEDURE sp_test1 (IN some_limit int)

select * from some_table limit some_limit;


and

CREATE PROCEDURE sp_test2 (IN some_table table)

select * from some_table;



Both fail with "ERROR 1064 (42000)".


Can someone please shed some light on this for me? Is this a problem
with

procedures not being able to do this or is it wrong datatypes or
something

completely different?


Thanks in advance

Jon


  
  
It sounds like what you need is dynamic SQL in your stored procedures.
Check out http://forge.mysql.com/snippets/view.php?id=13 for some good
examples, more complex than what you're asking about, but they should
shed light on what you need to do.
  
  
  
Regards,
  
Devananda
  
  



No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.394 / Virus Database: 268.10.5/403 - Release Date: 7/28/2006


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: Stored procedures

[Burke, Dan]

You would perform the prepare within the stored procedure.

Dan.



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon
Sent: Tuesday, July 25, 2006 10:31 AM
To: mysql@lists.mysql.com
Subject: Re: Stored procedures

But the scope of a prepared statement is only the session? I want a
stored
procedure to avoid some sql in clientside code...Or do you mean a prep
in
the stored?

/Jon

On 7/25/06, Burke, Dan [EMAIL PROTECTED] wrote:


 If I understand correctly, what you need is prepared statements.

 http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

 Dan.


 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of
Jon
 Sent: Tuesday, July 25, 2006 7:44 AM
 To: Visolve DB Team
 Cc: mysql@lists.mysql.com; Sena
 Subject: Re: Stored procedures

 On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote:
 
  Hello  Jon.


 Hi there Team :)

 And thanks for the quick reply

 Could you tell me the version of MySql ?. You can find the version by
  excuting the command
 
  SELECT version()
 
  If the version is below 5, the stored procedure feature would not
work
 .



 The version shown is 5.0.21-standard (from the rpm
 MySQL-server-standard-5.0.21-1.rhel3).

 I have no problem with other sp, like:
 CREATE PROCEDURE sp_test3 (IN value int)
 select count(*) from some_table where foo  value;

 It's just defining table and limit I've had problems with (there is
also
 one
 mentioning this in the manual about creating sp
 http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html )

 So could it be that it's not possible?

 /Jon


 Thanks
  Visolve DB Team.
 
 
 
  - Original Message -
  From: Jon [EMAIL PROTECTED]
  To: mysql@lists.mysql.com
  Sent: Tuesday, July 25, 2006 2:40 PM
  Subject: Stored procedures
 
 
   Hi list
  
   I'm trying to make stored procedures use parameters for limit and
  tables,
   I
   guess this is either me using the wrong datatype or it's not
 possible.
   I'm
   having the same issue with seting the table for the query:
  
   CREATE  PROCEDURE sp_test1 (IN some_limit int)
   select * from some_table limit some_limit;
  
   and
   CREATE  PROCEDURE sp_test2 (IN some_table table)
   select * from some_table;
  
  
   Both fail with ERROR 1064 (42000).
  
   Can someone please shed some light on this for me? Is this a
problem
  with
   procedures not being able to do this or is it wrong datatypes or
  something
   completely different?
  
   Thanks in advance
   Jon
  
 
 


 --
 This message has been scanned for viruses by TechTeam's email gateway.




---
 This e-mail transmission is strictly confidential and intended solely
 for the person or organization to whom it is addressed. It may contain
 privileged and confidential information and if you are not the
intended
 recipient, you must not copy, distribute or take any action in
reliance
 on it. If you have received this e-mail in error, please notify the
sender
 as soon as possible and delete the e-mail message and any
attachment(s).
 This message has been scanned for viruses by TechTeam's email gateway.


 ...

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]




--
This message has been scanned for viruses by TechTeam's email gateway.

---
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).
This message has been scanned for viruses by TechTeam's email gateway.


...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Visolve DB Team]

Hello  Jon.

Could you tell me the version of MySql ?. You can find the version by 
excuting the command


SELECT version()

If the version is below 5, the stored procedure feature would not work .

Thanks
Visolve DB Team.



- Original Message - 
From: Jon [EMAIL PROTECTED]

To: mysql@lists.mysql.com
Sent: Tuesday, July 25, 2006 2:40 PM
Subject: Stored procedures



Hi list

I'm trying to make stored procedures use parameters for limit and tables,
I
guess this is either me using the wrong datatype or it's not possible.
I'm
having the same issue with seting the table for the query:

CREATE  PROCEDURE sp_test1 (IN some_limit int)
select * from some_table limit some_limit;

and
CREATE  PROCEDURE sp_test2 (IN some_table table)
select * from some_table;


Both fail with ERROR 1064 (42000).

Can someone please shed some light on this for me? Is this a problem with
procedures not being able to do this or is it wrong datatypes or something
completely different?

Thanks in advance
Jon




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Jon]

On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote:


Hello  Jon.



Hi there Team :)

And thanks for the quick reply

Could you tell me the version of MySql ?. You can find the version by

excuting the command

SELECT version()

If the version is below 5, the stored procedure feature would not work .




The version shown is 5.0.21-standard (from the rpm
MySQL-server-standard-5.0.21-1.rhel3).

I have no problem with other sp, like:
CREATE PROCEDURE sp_test3 (IN value int)
select count(*) from some_table where foo  value;

It's just defining table and limit I've had problems with (there is also one
mentioning this in the manual about creating sp
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html )

So could it be that it's not possible?

/Jon


Thanks

Visolve DB Team.



- Original Message -
From: Jon [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Tuesday, July 25, 2006 2:40 PM
Subject: Stored procedures


 Hi list

 I'm trying to make stored procedures use parameters for limit and
tables,
 I
 guess this is either me using the wrong datatype or it's not possible.
 I'm
 having the same issue with seting the table for the query:

 CREATE  PROCEDURE sp_test1 (IN some_limit int)
 select * from some_table limit some_limit;

 and
 CREATE  PROCEDURE sp_test2 (IN some_table table)
 select * from some_table;


 Both fail with ERROR 1064 (42000).

 Can someone please shed some light on this for me? Is this a problem
with
 procedures not being able to do this or is it wrong datatypes or
something
 completely different?

 Thanks in advance
 Jon

RE: Stored procedures

[Burke, Dan]

If I understand correctly, what you need is prepared statements.

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

Dan.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon
Sent: Tuesday, July 25, 2006 7:44 AM
To: Visolve DB Team
Cc: mysql@lists.mysql.com; Sena
Subject: Re: Stored procedures

On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote:

 Hello  Jon.


Hi there Team :)

And thanks for the quick reply

Could you tell me the version of MySql ?. You can find the version by
 excuting the command

 SELECT version()

 If the version is below 5, the stored procedure feature would not work
.



The version shown is 5.0.21-standard (from the rpm
MySQL-server-standard-5.0.21-1.rhel3).

I have no problem with other sp, like:
CREATE PROCEDURE sp_test3 (IN value int)
select count(*) from some_table where foo  value;

It's just defining table and limit I've had problems with (there is also
one
mentioning this in the manual about creating sp
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html )

So could it be that it's not possible?

/Jon


Thanks
 Visolve DB Team.



 - Original Message -
 From: Jon [EMAIL PROTECTED]
 To: mysql@lists.mysql.com
 Sent: Tuesday, July 25, 2006 2:40 PM
 Subject: Stored procedures


  Hi list
 
  I'm trying to make stored procedures use parameters for limit and
 tables,
  I
  guess this is either me using the wrong datatype or it's not
possible.
  I'm
  having the same issue with seting the table for the query:
 
  CREATE  PROCEDURE sp_test1 (IN some_limit int)
  select * from some_table limit some_limit;
 
  and
  CREATE  PROCEDURE sp_test2 (IN some_table table)
  select * from some_table;
 
 
  Both fail with ERROR 1064 (42000).
 
  Can someone please shed some light on this for me? Is this a problem
 with
  procedures not being able to do this or is it wrong datatypes or
 something
  completely different?
 
  Thanks in advance
  Jon
 




--
This message has been scanned for viruses by TechTeam's email gateway.

---
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).
This message has been scanned for viruses by TechTeam's email gateway.


...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Jon]

But the scope of a prepared statement is only the session? I want a stored
procedure to avoid some sql in clientside code...Or do you mean a prep in
the stored?

/Jon

On 7/25/06, Burke, Dan [EMAIL PROTECTED] wrote:



If I understand correctly, what you need is prepared statements.

http://dev.mysql.com/doc/refman/5.0/en/sqlps.html

Dan.


-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Jon
Sent: Tuesday, July 25, 2006 7:44 AM
To: Visolve DB Team
Cc: mysql@lists.mysql.com; Sena
Subject: Re: Stored procedures

On 7/25/06, Visolve DB Team [EMAIL PROTECTED] wrote:

 Hello  Jon.


Hi there Team :)

And thanks for the quick reply

Could you tell me the version of MySql ?. You can find the version by
 excuting the command

 SELECT version()

 If the version is below 5, the stored procedure feature would not work
.



The version shown is 5.0.21-standard (from the rpm
MySQL-server-standard-5.0.21-1.rhel3).

I have no problem with other sp, like:
CREATE PROCEDURE sp_test3 (IN value int)
select count(*) from some_table where foo  value;

It's just defining table and limit I've had problems with (there is also
one
mentioning this in the manual about creating sp
http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html )

So could it be that it's not possible?

/Jon


Thanks
 Visolve DB Team.



 - Original Message -
 From: Jon [EMAIL PROTECTED]
 To: mysql@lists.mysql.com
 Sent: Tuesday, July 25, 2006 2:40 PM
 Subject: Stored procedures


  Hi list
 
  I'm trying to make stored procedures use parameters for limit and
 tables,
  I
  guess this is either me using the wrong datatype or it's not
possible.
  I'm
  having the same issue with seting the table for the query:
 
  CREATE  PROCEDURE sp_test1 (IN some_limit int)
  select * from some_table limit some_limit;
 
  and
  CREATE  PROCEDURE sp_test2 (IN some_table table)
  select * from some_table;
 
 
  Both fail with ERROR 1064 (42000).
 
  Can someone please shed some light on this for me? Is this a problem
 with
  procedures not being able to do this or is it wrong datatypes or
 something
  completely different?
 
  Thanks in advance
  Jon
 




--
This message has been scanned for viruses by TechTeam's email gateway.


---
This e-mail transmission is strictly confidential and intended solely
for the person or organization to whom it is addressed. It may contain
privileged and confidential information and if you are not the intended
recipient, you must not copy, distribute or take any action in reliance
on it. If you have received this e-mail in error, please notify the sender
as soon as possible and delete the e-mail message and any attachment(s).
This message has been scanned for viruses by TechTeam's email gateway.


...

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Chris White]

On Tuesday 25 July 2006 02:10 am, Jon wrote:
 CREATE  PROCEDURE sp_test1 (IN some_limit int)
 select * from some_table limit some_limit;

 and
 CREATE  PROCEDURE sp_test2 (IN some_table table)
 select * from some_table;

Well, first off with stored procedures the format is:

DELIMITER $$
CREATE PROCEDURE name ()
BEGIN
..
END $$
DELIMITER ;

DELIMITER is done so you can use ;'s within the stored procedure.  The other 
thing too is that you're trying to select a table by a variable.  That 
doesn't quite work, and I've tried a dozen or so variations myself hoping it 
would.  You know, I'd almost LIKE someone to go No you're wrong, you just 
need to do this... ;)

-- 
Chris White
PHP Programmer/DBlast
Interfuel

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Jon Molin]

On 7/25/06, Chris White [EMAIL PROTECTED] wrote:


On Tuesday 25 July 2006 02:10 am, Jon wrote:
 CREATE  PROCEDURE sp_test1 (IN some_limit int)
 select * from some_table limit some_limit;

 and
 CREATE  PROCEDURE sp_test2 (IN some_table table)
 select * from some_table;

Well, first off with stored procedures the format is:

DELIMITER $$
CREATE PROCEDURE name ()
BEGIN
..
END $$
DELIMITER ;

DELIMITER is done so you can use ;'s within the stored procedure.



no need for using blocks and setting delimiter when it's only a single query

 The other

thing too is that you're trying to select a table by a variable.  That
doesn't quite work, and I've tried a dozen or so variations myself hoping
it
would.  You know, I'd almost LIKE someone to go No you're wrong, you just
need to do this... ;)

Well, that's kinda what I want to hear. A simple yes or no for both tables

and limits. I take it tables are a nono considering you've tried so many
things.

Anyone who knows if the same is true for limits?

Re: Stored Procedures and DECLARE weirdness

[Paul DuBois]

Hello all and thanks again for any responses.

I'm hoping this is something really basic I'm not seeing.  I have MySQL
5.0.22-log, and I'm having a weird time creating a basic stored procedure.  I
get this far:

mysql CREATE PROCEDURE getRootId (IN company_id INT)
- BEGIN
- DECLARE variable1 INT;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
that corresponds to your MySQL server version for the right syntax to use
near 'INT' at line 3

Now, as stated in the MySQL manual, I have my DECLARE statement as the first
line after the BEGIN clause.  Anything else I'm missing?


When you use the mysql client, you must redefine the statement delimiter
during definition of a stored routine (or trigger or event) that uses
compound statement syntax with multiple statements making up the body of
the routine.  See the example near the end of this section:

http://dev.mysql.com/doc/refman/5.0/en/create-procedure.html

Also: http://dev.mysql.com/doc/refman/5.0/en/begin-end.html


--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.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 DECLARE weirdness

[Chris White]

On Friday 09 June 2006 02:31 pm, Paul DuBois wrote:
 When you use the mysql client, you must redefine the statement delimiter
 during definition of a stored routine (or trigger or event) that uses
 compound statement syntax with multiple statements making up the body of
 the routine.  See the example near the end of this section:

Ah, that was it.  Looks like I didn't RTFM in-depth enough.  Well I'm glad 
that's solved, I thought it was something weird on my side when I didn't see 
any bugs for it :).

 --
 Paul DuBois, MySQL Documentation Team
 Madison, Wisconsin, USA
 MySQL AB, www.mysql.com

-- 
Chris White
PHP Programmer/DB Unofficial
Interfuel

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

[Martijn Tonies]

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

[Chris Carrier]

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

[Martijn Tonies]

 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

[Gordon]

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]

Re: Stored procedures using a variable tablename

[Stefano Obliquid]

Hello everybody,

Rhino answer to Evaluating text as an expression and he solves my 
problem too. I need a prepared statement to execute dynamic sql.


I understand that I cannot pass a tablename as a parameter value, since 
it's not a value, so I have to prepare my sql separately.


I tried this simple test and it worked,

CREATE PROCEDURE p_select (IN tablen VARCHAR(128))
BEGIN
   SELECT CONCAT(SELECT * FROM , tablen) INTO @a;
   PREPARE stmt1 FROM @a;   
   EXECUTE stmt1;

   DEALLOCATE PREPARE stmt1;
END;

I don't understand why using a regular procedure variable instead of a 
session variable doesn't work:


CREATE PROCEDURE p_select2 (IN tablen VARCHAR(128))
BEGIN
   DECLARE m_sql VARCHAR(128);
   SELECT CONCAT(SELECT * FROM , tablen) INTO m_sql;
   PREPARE stmt1 FROM m_sql;   
   EXECUTE stmt1;

   DEALLOCATE PREPARE stmt1;
END;

ERROR 1064 (42000): You have an error in your SQL syntax; check the 
manual that corresponds to your MySQL server version for the right 
syntax to use near 'm_sql;

   EXECUTE stmt1;
   DEALLOCATE PREPARE stmt1;
END' at line 5

Is this a youth problem of stored procedures or an intented behaviour? 
anyone has some explanation or a better way to do this?


Thankyou
Stefano Locati - Obliquid
http://software.obliquid.com

Stefano Obliquid wrote:


Hello,

I am moving my first steps with stored procedures in Mysql and I was 
trying to write a stored procedure
using a tablename given as a variable. In this example the local 
variable m_sequence has the value of the

table I want to use.

CREATE PROCEDURE p_new_id (IN tablename VARCHAR(128), OUT new_id INT)
BEGIN
   DECLARE m_new_id INT DEFAULT 0;
   DECLARE m_sequence VARCHAR(128);
   SELECT CONCAT(_sequence_, tablename) INTO m_sequence;
   INSERT INTO m_sequence VALUES (NULL);
   SELECT last_insert_id() INTO m_new_id;
   DELETE FROM m_sequence WHERE sequence  m_new_id;
   SET new_id = m_new_id;
END;

However when I try to execute it,  it gives me this error message: 
Table 'db.m_sequence' doesn't exist


Meaning that a table literally called m_sequence was not found, but i 
would like to use the table inside

the m_sequence variable instead.

Anyone knows if it's possible and how?

Thank you very much
Stefano Locati - Obliquid
http://software.obliquid.it/




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored Procedures and Functions

[Gleb Paharenko]

Hello.



Have a look here:

  http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.html



However, it isn't clear for me what originally read data means. Do you 

store the time of the first access to the data in some table or 

somewhere else? You might obtain a better answer from the list members

if your add more details about table structure and your application logic.





 I don't have any experience with stored procedures and find the

 Documentation in the MYSQL manual a bit sketchy or maybe I am just miss

 reading it. Can any one point me to some documentation that will help with

 fully understanding Stored Procedures?

 

 

 

 What I am trying to migrate out of my program code is a procedure to 

do the

 following for update commands.

 

 

 

 1) Determine that the ID Field and the Last Updated Timestamp Field is

 still the same as when the data was originally read.

 

 2) If not the same then Raise an error back to the program so It can

 determine the action.

 

 3) If the same then lock row and perform update.

 

 

 

 Future development of this could extend to remove more out of code to 

handle

 when the two don't match. The procedure there is

 

 1) Compare Original Field Value to Current Value in Memory if the two

 don't match then

 

 2) IF the Original Field Value and the Current Value Stored in Table

 Match then update Field IF not then raise error and prompt user for 

action.

 

 

 

 This may be more information than required, but some one out their 

might be

 doing similar things that they can point me in the direction of some more

 documentation or even better still a few Example scripts that I can 

pull apart and learn from.





Blue Wave Software wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures MS SQL Server to MySQL

[Jim Seymour]

On Sun, Sep 25, 2005 at 03:56:46PM -0500, Peter Brawley wrote:
 Jim,
 
 If you really want to return the result in a variable, declare a user 
 var in the client, declare an OUT var in the SP, have the SP SELECT INTO 
 it, and pass the user var to the SP in the call:
 
 SET @x=0;
 SET GLOBAL log_bin_trust_routine_creators = TRUE;
 DROP PROCEDURE IF EXISTS CountPhoneNumbers;
 DELIMITER |
 CREATE PROCEDURE CountPhoneNumbers ( OUT count INT )
  BEGIN
SELECT COUNT(*) INTO count
FROM customer
WHERE Phone IS NOT NULL;
  END;
 |
 DELIMITER ;
 CALL CountPhoneNumbers(@x);
 SELECT @x;
 

Thanks Peter,

Since she was teaching us about using a variable I am trying your second
example.

Thanks again,

Jim Seymour
-- 
I started using something better than the standard back when IBM advertised
OS/2 Warp on TV. As Linux matured I made the transition from OS/2 v4 to Linux.
You don't have to accept less than you deserve.
Use the Power of the Penguin Registered Linux user #316735

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored Procedures and Functions

[Peter Brawley]

Justin,

I don't have any experience with stored procedures and find the
Documentation in the MYSQL manual a bit sketchy or maybe I am just 
miss reading it. Can any one point me to some documentation that
will 
help with fully understanding Stored Procedures? 

What I am trying to migrate out of my program code is a procedure
to 
do the following for update commands.

1) Determine that the ID Field and the Last Updated Timestamp
Field 
is still the same as when the data was originally read.

2) If not the same then Raise an error back to the program so
It can
determine the action.

3) If the same then lock row and perform update.

First, a quibble about the logic. Between the time a row is re-read for
changes and the time you write-lock it, it is possible for another
process 
to update the row. It would seem sounder to explicitly write-lock it up

front, make your changes, then unlock it. But secondly, LOCK is not 
permitted in MySQL stored procs, so if you want to use MyISAM 
and LOCK, you have to issue the LOCK outside the sproc, eg

SET GLOBAL log_bin_trust_routine_creators=TRUE;
DROP PROCEDURE IF EXISTS UpdTime;
LOCK TABLES test WRITE;
DELIMITER |
CREATE PROCEDURE UpdTime( IN readid INT, IN newtime TIMESTAMP )
BEGIN
 UPDATE test SET time=newtime WHERE id=readid;
END;
|
DELIMITER ;
UNLOCK TABLES;

or more elegantly, convert the table to InnoDB and use a transaction to
accomplish 
the same effect. 

PB
http://www.artfulsoftware.com

-

Blue Wave Software wrote:

  I don't have any experience with stored procedures and find the
Documentation in the MYSQL manual a bit sketchy or maybe I am just miss
reading it. Can any one point me to some documentation that will help with
fully understanding Stored Procedures? 

 

What I am trying to migrate out of my program code is a procedure to do the
following for update commands.

 

1) Determine that the ID Field and the Last Updated Timestamp Field is
still the same as when the data was originally read.

2) If not the same then Raise an error back to the program so It can
determine the action.

3) If the same then lock row and perform update.

 

Future development of this could extend to remove more out of code to handle
when the two don't match. The procedure there is 

1) Compare Original Field Value to Current Value in Memory if the two
don't match then

2) IF the Original Field Value and the Current Value Stored in Table
Match then update Field IF not then raise error and prompt user for action.

 

This may be more information than required, but some one out their might be
doing similar things that they can point me in the direction of some more
documentation or even better still a few Example scripts that I can pull
apart and learn from.

 

 

Regards,

  Justin Elward

 

Blue Wave Software Pty Limited

[EMAIL PROTECTED]

 

Ph. +61 2 4320 6090

Fx. +61 2 4320 6092

 


---

DISCLAIMER: 

This message is proprietary to Blue Wave Software Pty Limited (BWS) and is
intended solely for the use of the individual or individuals to whom it is
addressed. It may contain privileged or confidential information and should
not be circulated with out informing BWS prior or used for any purpose other
than for what it is intended. If you have received this message in error,
please notify the originator immediately. If you are not the intended
recipient, you are notified that you are strictly prohibited from using,
copying, altering, or disclosing the contents of this message. BWS accepts
no responsibility (except where required under Australian law) for loss or
damage arising from the use or misuse of the information transmitted by this
email including damage from virus."


---

 


  
  

No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005
  



No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures MS SQL Server to MySQL

[Peter Brawley]

Jim,

I have tried numerous variations of the following:

CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT
@count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END//

// was set to be the delimiter for the creation and have tried putting
various parts of the query into the parentheses. The error messages
always seem to occur around the @count variable. I am using MySQL
v.5.0.12 on Debian Etch.

The stored procedure docs 
(http://dev.mysql.com/doc/mysql/en/create-procedure.html) might not 
forbid prefixing declared variable names prefixed with @, but all the 
examples on that page show vars without such a prefix. The @ prefix in 
MySQL is for client user vars.


The simplest way to retreive a count frm an SP is just to run the query 
in the SP. MySQL will return the value, for example:


SET GLOBAL log_bin_trust_routine_creators = TRUE;
DROP PROCEDURE IF EXISTS CountPhoneNumbers;
DELIMITER |
CREATE PROCEDURE CountPhoneNumbers ()
 BEGIN
   SELECT COUNT(*)
   FROM customer
   WHERE Phone IS NOT NULL;
 END;
|
DELIMITER ;
CALL CountPhoneNumbers();
+--+
| COUNT(*) |
+--+
|   91 |
+--+

(Set log_bin_trust_routine_creators to bypass MySQL's declaration rules 
regarding deterministicness.)


If you really want to return the result in a variable, declare a user 
var in the client, declare an OUT var in the SP, have the SP SELECT INTO 
it, and pass the user var to the SP in the call:


SET @x=0;
SET GLOBAL log_bin_trust_routine_creators = TRUE;
DROP PROCEDURE IF EXISTS CountPhoneNumbers;
DELIMITER |
CREATE PROCEDURE CountPhoneNumbers ( OUT count INT )
 BEGIN
   SELECT COUNT(*) INTO count
   FROM customer
   WHERE Phone IS NOT NULL;
 END;
|
DELIMITER ;
CALL CountPhoneNumbers(@x);
SELECT @x;

PB
http://www.artfulsoftware.com
-



Jim Seymour wrote:


Taking on online class for SQL and am down to the last two classes and
cannot make the following work. This is a MS SQL Server query that I
have not been able to solve through the MySQL Documentation:

CREATE PROCEDURE CountPhoneNumbers AS DECLARE @count INTEGER
SELECT @count = COUNT (*) FROM Customer WHERE HomePhone IS NOT NULL
Print @count

I have tried numerous variations of the following:

CREATE PROCEDURE CountPhoneNumbers () BEGIN DECLARE @count INT SELECT
@count = COUNT(*) FROM CUSTOMER WHERE HomePhone IS NOT NULL; END//

// was set to be the delimiter for the creation and have tried putting
various parts of the query into the parentheses. The error messages
always seem to occur around the @count variable. I am using MySQL
v.5.0.12 on Debian Etch.

TIA,

Jim Seymour
 




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.344 / Virus Database: 267.11.6/111 - Release Date: 9/23/2005


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures and multi-queries: bug?

[Paul DuBois]

At 21:24 -0600 7/14/05, Warren Young wrote:
When you have a stored procedure that returns a result set, it seems 
that the server returns its results the same way as with a 
multi-query.  As a result, if you don't set the multi-query option 
when setting up the connection with the C API, the server refuses to 
return the result set.  You get this error:


PROCEDURE foo can't return a result set in the given context

If you set this flag in the mysql_real_connect() call, the stored 
procedure works fine.  But if you set it with 
mysql_set_server_option(), only regular multi-queries work fine; 
stored procedures returning result sets still fail.  According to 
this manual page:


http://dev.mysql.com/doc/mysql/en/c-api-multiple-queries.html

the two invocations should be equivalent.


They are equivalent -- for executing statements.

But to retrieve results, you also need the CLIENT_MULTI_RESULTS
flag in mysql_real_connect().  The CLIENT_MULTI_STATEMENTS flag automatically
enables CLIENT_MULTI_RESULTS, which is why you see the behavior that you do.

Due to the way my program is structured, it is highly inconvenient 
to set this flag in the real_connect call.  I would much rather set 
it on the connection after it is established.


Is there a good reason why the server behaves differently than the 
manual indicates, or is it a bug?



--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: Stored procedures

[Victoria Reznichenko]

Prasad Budim Ram [EMAIL PROTECTED] wrote:
 Hi,
 I'm testing stored procedure on  MySQL5.0.My question is can I change
 the definition of a procedure body  using ALTER PROCEDURE?(Not the
 characteristics). I'm not sure if it is implemented!
 

You can't do it with ALTER PROCEDURE. Only DROP procedure and then CREATE a new one.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Victoria Reznichenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.net
   ___/   www.mysql.com





-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: stored procedures in mysql5

[electroteque]

sorry insane is ozzy slang for awesome, the best whatever u want to call it
.

well , ok from what i know and have asked about , they are precompiled
functions , actually embedded into the server which do routines , so
therefore all u have to do is call the procedure from the API, therefore ok
now its standardised , makes it modular , therefore all u need to do is
change the function if any changes are to be made , but i guess how the hell
do u recompile, as in completely recompile the source , or just restarting
the server?

-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Sent: Monday, June 30, 2003 8:14 AM
To: electroteque; [EMAIL PROTECTED]
Subject: Re: stored procedures in mysql5


insane
you mean extremely fast? or easy to use? i'd like to know what those SPs are
good for...

-yves


-Ursprüngliche Nachricht-
Von: electroteque [EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Gesendet: Montag, 30. Juni 2003 00:09
Betreff: RE: stored procedures in mysql5


 thanks this is pretty insane cant wait for its release :D

 -Original Message-
 From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
 Sent: Monday, June 30, 2003 6:07 AM
 To: electroteque
 Cc: Mysql
 Subject: Re: stored procedures in mysql5


 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote:
  ok i have a bleeding edge server instance on my server
apache2/php5/mysql5
  running alongside the stable server , is stored procedures actually
  functioning in it yet ? is this the correct syntax there is no
 documentation
  for it yet.
  create procedure test as select * from tablename;

 There are a lot of SP examples in the source distribution or BK tree.

 Have a look at mysql-test/t/sp.test.

 Jeremy
 --
 Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
 [EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

 MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg)

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

Re: stored procedures in mysql5

[Jeremy Zawodny]

On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote:
 ok i have a bleeding edge server instance on my server apache2/php5/mysql5
 running alongside the stable server , is stored procedures actually
 functioning in it yet ? is this the correct syntax there is no documentation
 for it yet.
 create procedure test as select * from tablename;

There are a lot of SP examples in the source distribution or BK tree.

Have a look at mysql-test/t/sp.test.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg)

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: stored procedures in mysql5

[electroteque]

thanks this is pretty insane cant wait for its release :D

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Monday, June 30, 2003 6:07 AM
To: electroteque
Cc: Mysql
Subject: Re: stored procedures in mysql5


On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote:
 ok i have a bleeding edge server instance on my server apache2/php5/mysql5
 running alongside the stable server , is stored procedures actually
 functioning in it yet ? is this the correct syntax there is no
documentation
 for it yet.
 create procedure test as select * from tablename;

There are a lot of SP examples in the source distribution or BK tree.

Have a look at mysql-test/t/sp.test.

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg)

--
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 in mysql5

[nospam]

insane
you mean extremely fast? or easy to use? i'd like to know what those SPs are good 
for...

-yves


-Ursprüngliche Nachricht- 
Von: electroteque [EMAIL PROTECTED]
An: [EMAIL PROTECTED]
Gesendet: Montag, 30. Juni 2003 00:09
Betreff: RE: stored procedures in mysql5


 thanks this is pretty insane cant wait for its release :D
 
 -Original Message-
 From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
 Sent: Monday, June 30, 2003 6:07 AM
 To: electroteque
 Cc: Mysql
 Subject: Re: stored procedures in mysql5
 
 
 On Sun, Jun 29, 2003 at 08:00:29PM +1000, electroteque wrote:
  ok i have a bleeding edge server instance on my server apache2/php5/mysql5
  running alongside the stable server , is stored procedures actually
  functioning in it yet ? is this the correct syntax there is no
 documentation
  for it yet.
  create procedure test as select * from tablename;
 
 There are a lot of SP examples in the source distribution or BK tree.
 
 Have a look at mysql-test/t/sp.test.
 
 Jeremy
 --
 Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
 [EMAIL PROTECTED]  |  http://jeremy.zawodny.com/
 
 MySQL 4.0.13: up 26 days, processed 826,793,662 queries (360/sec. avg)
 
 --
 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]

re: Re: Stored Procedures

[Victoria Reznichenko]

Matt,
Monday, October 14, 2002, 7:14:24 AM, you wrote:

MH I believe you'll be waiting until version 4.1.0 for full Stored procedures
MH support.

Stored procedures will not come in 4.1, they are scheduled around
version 5.0

MH At that point I'm not sure what we'll be waiting for.  There are a few
MH features that will be missing, but they will be the kind of things that
MH financial institutions need, IIRC.  Most of us won't care about those... I'm
MH kinda waiting for Stored Procedures meself, but am hoping that they will hit
MH by June of next year.

MH Matt

MH - Original Message -
MH From: Ian Hagan [EMAIL PROTECTED]
MH To: [EMAIL PROTECTED]
MH Sent: Sunday, October 13, 2002 8:10 PM
MH Subject: Stored Procedures


 Hello All List Members,

 My name is Ian Hagan. I am a database developer specialising in Delphi. I
MH am
 new to Mysql. (Using a Windows 98 platform; installed version 3.23.51-max)

 Can anyone help me with stored procedures in Mysql??

 The Manual does not contain much info and refers me to several examples,
 none of which I can find in my installation or on the Mysql website.

 I am looking for general guidance only at this stage; some examples and
 install info would be of a great help.


-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
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

Re: Stored Procedures

[Gerald R. Jensen]

MySQL does not support stored procedures.

- Original Message -
From: Ian Hagan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, October 13, 2002 7:10 PM
Subject: Stored Procedures


Hello All List Members,

My name is Ian Hagan. I am a database developer specialising in Delphi. I am
new to Mysql. (Using a Windows 98 platform; installed version 3.23.51-max)

Can anyone help me with stored procedures in Mysql??

The Manual does not contain much info and refers me to several examples,
none of which I can find in my installation or on the Mysql website.

I am looking for general guidance only at this stage; some examples and
install info would be of a great help.

Thanks,

Ian


-
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: Stored Procedures

[Matt Hargraves]

I believe you'll be waiting until version 4.1.0 for full Stored procedures
support.

At that point I'm not sure what we'll be waiting for.  There are a few
features that will be missing, but they will be the kind of things that
financial institutions need, IIRC.  Most of us won't care about those... I'm
kinda waiting for Stored Procedures meself, but am hoping that they will hit
by June of next year.

Matt

- Original Message -
From: Ian Hagan [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Sunday, October 13, 2002 8:10 PM
Subject: Stored Procedures


 Hello All List Members,

 My name is Ian Hagan. I am a database developer specialising in Delphi. I
am
 new to Mysql. (Using a Windows 98 platform; installed version 3.23.51-max)

 Can anyone help me with stored procedures in Mysql??

 The Manual does not contain much info and refers me to several examples,
 none of which I can find in my installation or on the Mysql website.

 I am looking for general guidance only at this stage; some examples and
 install info would be of a great help.

 Thanks,

 Ian


 -
 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: stored procedures and triggers

[Curtis Maurand]

I have to agree here.If one doesn't know the schema and has to figure
out how to locate keys in related tables, then one's not paying too much
attention to the constructs of the data set.  Lets not forget joins, eh?

What network traffic?  The list of tables?  How much traffic is there in a
query/result set when finding a key? The table structures?  We're not
talking about MB's of keys.  We're talking about a couple KB's even on a
very large data set.  HTTP requests to the intranet server for each little
graphic file will generates more chat than you'll generate with your query.
 The query is sent to the machine running dbms and it returns a result set.
 This isn't like you're using Microsoft Access or DBase/Foxpro where the
query is executed by the client against a remote file forcing all that data
to be transferred back and forth using a chatty protocol like NetBIOS over
IP. Even though you haven't installed NetBIOS on your Win2K/XP/9n/ME box,
its still there (NetBIOS over TCP/IP) and is the primary method of
connection(less) between Windows machines.  Even the MySQL ODBC connector
uses pure TCP/IP on port 3306

non secure network?   SSH is your friend.

Curtis Maurand
Maine Line Systems

 That's only if you access it directly from client.

 What I was talking about is AppServer sitting next to MySQL
 server. Preferably on the same computer.
 Agree that for some cascade actions triggers are very useful.

 But most of the folks just try to push all business logic
 into sp/triggers.
 And that's better to do in AppServer for large-scale apps.


So it's strange when the initial poster said that they are
 core of his development. Because they shouldn't be.

 Yuri.


 Yuri.

 I don't agreed Yuri. triggers and sp are really useful and make the
 process
 run faster. Think on this: the user is trying to delete a record on a
 table.
 The primary key of that table is present in several other tables in
 the
 database. Before deleting the record you should search in every table
 for
 the primary key to be deleted, and if you find it the record couldn't
 be
 deleted.



 Well, with triggers and sp, all the job of opening every related table
 and
 look for the primary key will take place in the server, minimizing
 time and
 network traffic.



 Without triggers and sp, you have to manually code the process and
 every
 table you open is a request to the server and data navigating trough
 the
 network. The process is slower and more vulnerable in a non secure
 network.



 In an Internet environment triggers and sp take more importance.

 -
 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: stored procedures and triggers

[yvictorovich]

That's only if you access it directly from client.

What I was talking about is AppServer sitting next to MySQL
server. Preferably on the same computer.
Agree that for some cascade actions triggers are very useful.

But most of the folks just try to push all business logic
into sp/triggers.
And that's better to do in AppServer for large-scale apps.

So it's strange when the initial poster said that they are
core of his development. Because they shouldn't be.

Yuri.


Yuri.

 I don't agreed Yuri. triggers and sp are really useful and make the
 process
 run faster. Think on this: the user is trying to delete a record on a
 table.
 The primary key of that table is present in several other tables in
 the
 database. Before deleting the record you should search in every table
 for
 the primary key to be deleted, and if you find it the record couldn't
 be
 deleted.
 
 
 
 Well, with triggers and sp, all the job of opening every related table
 and
 look for the primary key will take place in the server, minimizing time
 and
 network traffic.
 
 
 
 Without triggers and sp, you have to manually code the process and
 every
 table you open is a request to the server and data navigating trough
 the
 network. The process is slower and more vulnerable in a non secure
 network.
 
 
 
 In an Internet environment triggers and sp take more importance.

-
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: stored procedures and triggers

[yvictorovich]

I would like to see the triggers/stored procedures/views
in MySQL also. And the only estimate I saw somewhere was
around version 5.0.

But think about this this way: in the complex enterprise-level
system you would probably need some kind of application
server instead of direct client-db access. And if this is
the case the need of triggers/sp is not that urgent. 3-teer
system w/app server is actually the best scalable architecture. 
So you can do stuff that triggers/sp do in the app server
where this stuff really belong.

Lots of client-server projects convinced me that this
approach is the best on the long run although somewhat
more laborious.

Yuri.


 Hello list,
 Here in my company people are concerned about which database to choose
 to
 start implementing a new application client-server. As the only person
 here
 with large experience in this field of activity is me, I am not very
 much
 inclined to our mysql due to its lack of resources like triggers and
 stored
 procedures, which are the kernel of my development. I would like to
 know
 from you if someone is in touch with the develolpment of these resources
 and
 know something about when they will be ready. And as second question I
 would
 like to know if some of you have had experience with interbase/firebird
 and
 know if it will always be a free database as well.
 
 hope you from the list are all well and healthy,
 yf marcello miorelli

-
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: Stored Procedures

[Cal Evans]

No.
http://www.mysql.com/doc/N/u/Nutshell_4.1_development_release.html

*
* Cal Evans
* The Virtual CIO
* http://www.calevans.com
*
 

-Original Message-
From: root [mailto:[EMAIL PROTECTED]]
Sent: Monday, July 15, 2002 10:06 AM
To: [EMAIL PROTECTED]
Subject: Stored Procedures


dear all,

Does Mysql support stored procedures ?

best regards
Menrit

-
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: Stored Procedures

[Egor Egorov]

root,
Monday, July 15, 2002, 6:05:54 PM, you wrote:


r Does Mysql support stored procedures ?

No, MySQL doesn't yet support stored procedures.




-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Egor Egorov
 / /|_/ / // /\ \/ /_/ / /__   [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

RE: Stored procedures

[Roma Gupta]

MySQL doesn't support stored procedures yet.
Roma

-Original Message-
From: andy thomas [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, June 27, 2002 8:01 AM
To: [EMAIL PROTECTED]
Subject: Stored procedures

Does anyone know if stored procedures have been implemented in MySQL?
Getting into the MySQL web site from the UK is almost impossible these
days so I thought I'd ask here.

Thanks in advance for any information,

Andy




-
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: Stored procedures

[Gerald Jensen]

Yes ... we all know that stored procedures have not been implemented in
MySQL.

Sorry to be so terse, but this is about the umpteenth posting of this same
question this week ... doesn't anybody read the list or search the archive
before they post stuff?

- Original Message -
From: andy thomas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 27, 2002 7:01 AM
Subject: Stored procedures


 Does anyone know if stored procedures have been implemented in MySQL?
 Getting into the MySQL web site from the UK is almost impossible these
 days so I thought I'd ask here.

 Thanks in advance for any information,

 Andy




 -
 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: Stored procedures

[Roma Gupta]

I think you didn't notice. He cannot access Mysql website from UK :)

-Original Message-
From: Gerald Jensen [mailto:[EMAIL PROTECTED]] 
Sent: Thursday, June 27, 2002 9:16 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Stored procedures

Yes ... we all know that stored procedures have not been implemented in
MySQL.

Sorry to be so terse, but this is about the umpteenth posting of this
same
question this week ... doesn't anybody read the list or search the
archive
before they post stuff?

- Original Message -
From: andy thomas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 27, 2002 7:01 AM
Subject: Stored procedures


 Does anyone know if stored procedures have been implemented in MySQL?
 Getting into the MySQL web site from the UK is almost impossible these
 days so I thought I'd ask here.

 Thanks in advance for any information,

 Andy




 -
 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

Re: Stored procedures

[andy thomas]

On Thu, 27 Jun 2002, Gerald Jensen wrote:

 Yes ... we all know that stored procedures have not been implemented in
 MySQL.

I thought as much but just wanted rapid confirmation.

 Sorry to be so terse, but this is about the umpteenth posting of this same
 question this week ... doesn't anybody read the list or search the archive
 before they post stuff?

I suspect the e-bone problems over in Continental Europe are the reason we
in the UK are getting an intermittent mailing list feed and almost no
access at all to the MySQL site.

Sorry if I upset anyone,

Andy

 - Original Message -
 From: andy thomas [EMAIL PROTECTED]
 To: [EMAIL PROTECTED]
 Sent: Thursday, June 27, 2002 7:01 AM
 Subject: Stored procedures


  Does anyone know if stored procedures have been implemented in MySQL?
  Getting into the MySQL web site from the UK is almost impossible these
  days so I thought I'd ask here.
 
  Thanks in advance for any information,
 
  Andy
 
 
 
 
  -
  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: Stored procedures

[Gerald Jensen]

I noticed ... but he was somehow able to get on the list to post his
message!

-Original Message-
From: Roma Gupta [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 27, 2002 9:13 AM
To: 'Gerald Jensen'; [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Stored procedures


I think you didn't notice. He cannot access Mysql website from UK :)

-Original Message-
From: Gerald Jensen [mailto:[EMAIL PROTECTED]]
Sent: Thursday, June 27, 2002 9:16 AM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: Re: Stored procedures

Yes ... we all know that stored procedures have not been implemented in
MySQL.

Sorry to be so terse, but this is about the umpteenth posting of this
same
question this week ... doesn't anybody read the list or search the
archive
before they post stuff?

- Original Message -
From: andy thomas [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 27, 2002 7:01 AM
Subject: Stored procedures


 Does anyone know if stored procedures have been implemented in MySQL?
 Getting into the MySQL web site from the UK is almost impossible these
 days so I thought I'd ask here.

 Thanks in advance for any information,

 Andy




 -
 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

Re: Stored Procedures

[Victoria Reznichenko]

Yiannis,
Wednesday, April 10, 2002, 10:17:03 AM, you wrote:

YD i've just started work with MySQL. Does anybody know how can i
YD use stored procedures with MySQL? Is this capability supported?

MySQL doesn't have stored procedures yet, but it is in our plans to
support them, look at:
http://www.mysql.com/doc/A/N/ANSI_diff_Triggers.html


YD Thanks 
YD John Dimos





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

Re: Stored procedures (2)

[Arnulf Kristiansen]

Claudio Cicali wrote:

 I've got a tour on the web, searching for info and ideas,
 regarding the subject to implement some sort of stored procedure in mysql
 (remember Lets approach stored procedure thread ?)

 This was what I discovered

 - The Perl stored procedures (myperl project) in not a good idea
at all, but, even if called poor man's stored procedure, I
think that this project don't add, actually, stored procedure in
the precise meamning of term. It add only a binding with Perl via UDF.
It's a nice (and quite simple) hack.
Dana Power, I've not take a look to _your_ hack, but I think this
is the same approach.

I agree, even if this is a nice hack, it is not the solution we are looking for.



 - I had searched everywhere the ANSI SQL3 directive... not luck.
I only found a 1994 document where this directives were proposed
It is quite incomprensile, though (like all standard doc I have read :)

Check our book recommendations at http://www.mysql.com/portal/books/html/index.html



 - I'm evalueting the IBM DB2 approach.
Since the ol' COBOL days (rattles...) I remember the EXEC SQL ... END-EXEC
commands embedded inside the COBOL as the host language.
At compile time there was a cross-compile step the produced some
code to get recompiled and finally linked and binded.
This is also the (old?) informix approach, where SQL EXECs were embedded
inside the C source, and then a cross-compile (or precompiler) make
the dirty work, creating another C source that could finally be
compiled and linked.

I believe a solution where you could invoke code/procedures external to the server is
needed and that this mechanism should be fairly independent of how and in which 
language
this code is written. There is no reason why you cannot use whatever means this 
language
might have to access MySQL. The fact that it was called from within the data base 
server
does not change anything. I believe that this mechanism and the embedded stored 
procedure
capability fulfills two quite different needs.


 But I don't know to have some (low skilled) SQL coders to be well
 skilled in some other language to write down a stored procedure.

 So here came my idea:

 use a SIMPLE language to write the logic of the stored procedure
 (no complex structures, definition and use of local variables,
 some test statement, error trapping, and of source free SQL coding),
 than use a precompiler to convert that source into the source of an UDF.


I think we might end up with something along those lines.

 At last, we had stored procedure written in a simple language,
 but compiled and used as UDFs.

 MySQL itself could then be used to store the packaging approach
 (as Oracle does), or other information regarding validation of
 the sp, last modification time, user... etc

 Just some thoughts


 Claudio Cicali

 mysql, database, tables

Your input is greatly appreciated.

/Arnulf



 -
 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: Stored procedures (2)

[Dana Powers]

 - The Perl stored procedures (myperl project) in not a good idea
at all, but, even if called poor man's stored procedure, I
think that this project don't add, actually, stored procedure in
the precise meamning of term. It add only a binding with Perl via UDF.
It's a nice (and quite simple) hack.
Dana Power, I've not take a look to _your_ hack, but I think this
is the same approach.
Actually, its not. Its more along the lines of apache's CGI vs. mod_perl.
And its not just perl, its an architecture for pluggable languages ( python,
javascript, lisp, or your new pl/sql clone ), which is exactly what you're
looking for, if im not mistaken. The problem you're going to have is that
MySQL does not support callbacks into the engine - i.e. youre in the middle
of an sql call when your function gets called ( whether its udf or something
else doesnt matter ), halting everything - effectively pushing it onto 'the
stack' - and making separate sql calls, is not supported. You'll find that
the hardest part is overcoming the current 'atomic' architecture, which has
been used to optimize pretty much every part of the engine ( atomic meaning
that 1 sql call is 1 simple actions. ). Of course CREATE ... SELECT and
INSERT ... SELECT are simple exceptions, but the same reason that
Sub-Selects are non-trivial, is the same reason that stored procedures are
non-trivial.

dpk


-
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: Stored Procedures and Triggers

[Jeremy Zawodny]

On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui wrote:

 How many types of triggers MySQL have? And what about stored procedures?

Zero and Zero, as explained in the docs.

If you need them today, I'd suggest looking at PostgreSQL.  If you're
patient, they'll likely appear in MySQL someday--I believe both are on
the TODO list.

Jeremy
-- 
Jeremy D. Zawodny, [EMAIL PROTECTED]
Technical Yahoo - Yahoo Finance
Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg)

-
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: Stored Procedures and Triggers

[David Turner]

A friend of mine mentioned something about perl stored procedures for
MYSQL. Has anyone heard about this? I have searched everywhere and only
seen posts related to POSTGRESQL. If they have this for MYSQL it would
be really helpful.

Thanks, Dave
On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote:
 On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui wrote:
 
  How many types of triggers MySQL have? And what about stored procedures?
 
 Zero and Zero, as explained in the docs.
 
 If you need them today, I'd suggest looking at PostgreSQL.  If you're
 patient, they'll likely appear in MySQL someday--I believe both are on
 the TODO list.
 
 Jeremy
 -- 
 Jeremy D. Zawodny, [EMAIL PROTECTED]
 Technical Yahoo - Yahoo Finance
 Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936
 
 MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg)
 
 -
 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: Stored Procedures and Triggers

[Steve Edberg]

At 1:10 PM -0700 9/3/01, David Turner wrote:
A friend of mine mentioned something about perl stored procedures for
MYSQL. Has anyone heard about this? I have searched everywhere and only
seen posts related to POSTGRESQL. If they have this for MYSQL it would
be really helpful.

That would be MyPerl:

http://software.tangent.org/

It looks to be very early on in development, so I'm not sure how much 
I'd rely on it...

-steve



Thanks, Dave
On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote:
   On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui 
[EMAIL PROTECTED] wrote:
   
   How many types of triggers MySQL have? And what about stored procedures?

  Zero and Zero, as explained in the docs.

  If you need them today, I'd suggest looking at PostgreSQL.  If you're
  patient, they'll likely appear in MySQL someday--I believe both are on
  the TODO list.

  Jeremy
  --
  Jeremy D. Zawodny, [EMAIL PROTECTED]
  Technical Yahoo - Yahoo Finance
  Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936

   MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg)



-- 
+ Open source questions? +
| Steve Edberg   University of California, Davis |
| [EMAIL PROTECTED]   Computer Consultant |
| http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
+--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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: Stored Procedures and Triggers of MySQL

[Alexander Skwar]

So sprach »Kimman Lui« am 2001-09-03 um 16:26:33 +0800 :
 How many types of triggers MySQL have? And what about stored procedures?

none and (close to) none.

Alexander Skwar
-- 
How to quote:   http://learn.to/quote (german) http://quote.6x.to (english)
Homepage:   http://www.digitalprojects.com   |   http://www.iso-top.de
   iso-top.de - Die günstige Art an Linux Distributionen zu kommen
Uptime: 14 hours 27 minutes

-
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: Stored Procedures and Triggers

[David Turner]

Great thanks, I'll check it out.

Dave
On Mon, Sep 03, 2001 at 01:49:31PM -0700, Steve Edberg wrote:
 At 1:10 PM -0700 9/3/01, David Turner wrote:
 A friend of mine mentioned something about perl stored procedures for
 MYSQL. Has anyone heard about this? I have searched everywhere and only
 seen posts related to POSTGRESQL. If they have this for MYSQL it would
 be really helpful.
 
 That would be MyPerl:
 
   http://software.tangent.org/
 
 It looks to be very early on in development, so I'm not sure how much 
 I'd rely on it...
 
   -steve
 
 
 
 Thanks, Dave
 On Mon, Sep 03, 2001 at 01:37:23AM -0700, Jeremy Zawodny wrote:
On Mon, Sep 03, 2001 at 04:23:23PM +0800, Kimman Lui 
 [EMAIL PROTECTED] wrote:

How many types of triggers MySQL have? And what about stored procedures?
 
   Zero and Zero, as explained in the docs.
 
   If you need them today, I'd suggest looking at PostgreSQL.  If you're
   patient, they'll likely appear in MySQL someday--I believe both are on
   the TODO list.
 
   Jeremy
   --
   Jeremy D. Zawodny, [EMAIL PROTECTED]
   Technical Yahoo - Yahoo Finance
   Desk: (408) 349-7878   Fax: (408) 349-5454   Cell: (408) 685-5936
 
MySQL 3.23.41-max: up 3 days, processed 33,335,761 queries (111/sec. avg)
 
 
 
 -- 
 + Open source questions? +
 | Steve Edberg   University of California, Davis |
 | [EMAIL PROTECTED]   Computer Consultant |
 | http://aesric.ucdavis.edu/  http://pgfsun.ucdavis.edu/ |
 +--- http://pgfsun.ucdavis.edu/open-source-tools.html ---+

-
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: Stored procedures (really sorry for bringing this up)

[Sinisa Milivojevic]

John N S Gill writes:
 
 The company I work for has been considering moving a key database from
 MS Access to a proper database.
 
 We use linux for a lot of the number crunching we do and I was keen for
 us to choose MySQL.  I happen to think it would be ideal for our
 applications.
 
 Unfortunately, those with the task of converting the application
 consider stored procedures to be essential.  I'm not at all convinced
 they are necessary (at least in the short term).  I fear the arguement
 is already lost.. but...
 
 I saw from the survey that is currently being done that stored
 procedures are one feature that appears to be in active consideration,
 but I see from the TODO list that they are only in the to be done
 sometime category.
 
 So the questions are:
   any idea how long it will be before something is in place?  would
 funding change this?
   can also offer some help with the coding (have solid C/C++ but
 no experience with MySQL itself)
 
 John Gill.
 


Hi!

We have plans of implementing stored procedures until  the end of this
year, but if it is too far away for you , you can try doing it
yourself.

If you have problems, post your mails to [EMAIL PROTECTED]


Regards,

Sinisa

 For technical support contracts, goto http://order.mysql.com

    __ _   _  ___ ==  MySQL AB
 /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
   /*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
  /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
  /*/^^^\*\^^^
 /*/ \*\Developers Team

-
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: Stored procedures (really sorry for bringing this up)

[Ken Menzel]

Hi John and Sinisa,
   I don't know if this helps but we are using php scripts in place of
stored procedures.  Our client/server apps open up an https or http
connection to a php server and pass in the script name and variables
in the URL (like cgi).  It's not stored procedures,  but is pretty
fast if apache/php is on the same server or a fast ethernet cable away
and using https can be very secure as well.  We currently use it to
get around things like multiple sql commands in one request and table
to table updates as it allows the client to execute an SQL procedure
remotely.
Hope this helps someone!
Ken
-
Ken Menzel  ICQ# 9325188
www.icarz.com  [EMAIL PROTECTED]
- Original Message -
From: Sinisa Milivojevic [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Sent: Thursday, June 07, 2001 7:54 AM
Subject: Re: Stored procedures (really sorry for bringing this up)


 John N S Gill writes:
 
  The company I work for has been considering moving a key database
from
  MS Access to a proper database.
 
  We use linux for a lot of the number crunching we do and I was
keen for
  us to choose MySQL.  I happen to think it would be ideal for our
  applications.
 
  Unfortunately, those with the task of converting the application
  consider stored procedures to be essential.  I'm not at all
convinced
  they are necessary (at least in the short term).  I fear the
arguement
  is already lost.. but...
 
  I saw from the survey that is currently being done that stored
  procedures are one feature that appears to be in active
consideration,
  but I see from the TODO list that they are only in the to be done
  sometime category.
 
  So the questions are:
  any idea how long it will be before something is in place? would
  funding change this?
  can also offer some help with the coding (have solid C/C++ but
  no experience with MySQL itself)
 
  John Gill.
 


 Hi!

 We have plans of implementing stored procedures until  the end of
this
 year, but if it is too far away for you , you can try doing it
 yourself.

 If you have problems, post your mails to [EMAIL PROTECTED]


 Regards,

 Sinisa

  For technical support contracts, goto http://order.mysql.com

     __ _   _  ___ ==  MySQL AB
  /*/\*\/\*\   /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
 /*/ /*/ /*/   \*\_   |*|   |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/  \*\|*|   |*||*| Larnaca, Cyprus
   /*/ /*/  /*/\*\_/*/ \*\_/*/ |*|
   /*/^^^\*\^^^
  /*/ \*\Developers Team

 
-
 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: Stored procedures in MySQL ?

[Paolo Michetti]

I'm interested in stored procedure as well.

I only know that SP are available in the INNOBASE engine, but I'm
looking for access SP from mysql SQL interface.

ciao - Paolo

 -Original Message-
 From: Razvan Veina [mailto:[EMAIL PROTECTED]]
 Sent: venerd 30 marzo 2001 8.39
 To: [EMAIL PROTECTED]
 Subject: Stored procedures in MySQL ?


 Is there any support for stored procedures in MySQL ? If so, what
 version of MySQL ?



-
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