Reformat: Connector/Python and calling stored procedures with backticks
Hi again,
Sorry about the bad formatting of my previous e-mail.Hopefully this copy will
be more readable, or else I will just leave it at that.
I have also included some extra info at the bottom that might be relevant.
Hi, everyone
I'm having trouble using MySQL Connector/Python's cursor.callproc() method to
call stored procedures whose names include backticks.
Here is the entire traceback (with minor redactions):
Traceback (most recent call last): File
"C:\Users\[...]\Bottle\Website\website.py", line 108, in
cursor.callproc('`insert_page`', args) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\cursor.py", line 705,
in callprocself.execute("SET {0}=%s".format(argname), (arg,)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\cursor.py", line 515,
in executeself._handle_result(self._connection.cmd_query(stmt)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\connection.py", line
488, in cmd_queryresult =
self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\connection.py", line
395, in _handle_resultraise
errors.get_exception(packet)mysql.connector.errors.ProgrammingError: 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
'`insert_page`_arg1=NULL' at line 1
And here is the stored procedure call that produces it:
args = (None, 'foo', 'bar', None)cursor.callproc('`insert_page`',
args)
Having gone through the Connector/Python manual, I have noticed demonstrations
of extended Python string formatting with the cursor.execute() method in order
to ensure correct parsing down the line, however, that doesn't seem to be
applicable to the callproc() method.
Using the MySQL Command Line Client, I'm able to call the exact same stored
procedures without any issues, so the problem has to lie somewhere within the
Python application.
I'm using MySQL Connector/Python version 2.1.3, MySQL Server version 5.7.12,
Python 3.5.1 (64-bit) on Windows 7 64-bit.
I have asked the same question on stackoverflow. I will mirror any replies
to/from there that fix the issue, unless someone objects to that?
Thank you for your time.
Thank you,Magnus
Connector/Python and calling stored procedures with backticks
Hi, everyone
I'm having trouble using MySQL Connector/Python's cursor.callproc() method to
call stored procedures whose names include backticks.
Here is the entire traceback (with minor redactions):
Traceback (most recent call last): File
"C:\Users\[...]\Bottle\Website\website.py", line 108, in
cursor.callproc('`insert_page`', args) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\cursor.py", line 705,
in callprocself.execute("SET {0}=%s".format(argname), (arg,)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\cursor.py", line 515,
in executeself._handle_result(self._connection.cmd_query(stmt)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\connection.py", line
488, in cmd_queryresult =
self._handle_result(self._send_cmd(ServerCmd.QUERY, query)) File
"C:\Users\[...]\Bottle\lib\site-packages\mysql\connector\connection.py", line
395, in _handle_resultraise
errors.get_exception(packet)mysql.connector.errors.ProgrammingError: 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
'`insert_page`_arg1=NULL' at line 1
And here is the stored procedure call that produces it:
args = (None, 'foo', 'bar', None)cursor.callproc('`insert_page`', args)
Having gone through the Connector/Python manual, I have noticed demonstrations
of extended Python string formatting with the cursor.execute() method in order
to ensure correct parsing down the line, however, that doesn't seem to be
applicable to the callproc() method.
Using the MySQL Command Line Client, I'm able to call the exact same stored
procedures without any issues, so the problem has to lie somewhere within the
Python application.
I hope someone will be able to help me.
Thank you,
Magnus
Doubt with stored procedures
Hi everybody, I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. Thanks everybody. Best regards, Antonio.
Re: Doubt with stored procedures
- Original Message - From: Antonio Fernández Pérez antoniofernan...@fabergames.com Subject: Doubt with stored procedures I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. Yes, that's all part of the mysql magic, any stored procedure ever defined will work on any server ever to exist. Well, either that, or you might want to provide us with a little more detail, including but not limited to: * Is this MySQL or NDB Cluster? * Software version? * What is the relation between the servers (master, slave, master/master, ...) ? * how exactly did you define the SP, using what user etc. ? * how are you calling the SP, using what user, ... ? * ... That being said, using a set of default assumptions, I can tell you that both GRANT and CREATE PROCEDURE are replicated, so both all procedures and all users should logically exist on both sides of a replication setup. -- Linux Kriek Wanderung April 19-21, 2013 http://www.tuxera.be/lkw -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt with stored procedures
2013/04/17 14:16 +0200, Antonio Fernández Pérez I have a doubt with stored procedures functionality. Is possible that a stored procedure works with all databases form the server? I have created a stored procedure on dataBaseA and also works with dataBaseB. Is that correct? Independently of the user privileges defined. It is the default assumption that a procedure within a database is meant for use within that database, but one can call a procedure from any of the set of databases by qualifying the name--and the MySQL command show procedure status shows all procedures. The only question is the procedure s use of variables: if they refer only to the arguments, it is of no importance whence it is called. This is documented: file:///C:/Program%20Files/MySQL/MySQL%20Server%205.5/HELP/sql-syntax.html#useUSE statements within stored routines are not permitted. When a routine is invoked, an implicit USE db_name is performed (and undone when the routine terminates). The causes the routine to have the given default database while it executes. References to objects in databases other than the routine default database should be qualified with the appropriate database name. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Doubt with stored procedures
Hi everybody, Firstly thanks for your reply. I'm using MySQL only in one server (I have only one), without replication and without cluster configuration. I have defined the stored procedure as a normal user, not as a root. And the call is make as a normal user also. Occurs with MySQL 5.1.49 on Debian 6. This normal user has CREATE PROCEDURE privilege and EXECUTE privilege. Thanks. Best regards, Antonio.
Why do stored procedures limited to Select stmt to 1 OUT parameter?
I want to have a stored procedure that returns 5 columns from a table and do some calculations on it. But when I try it, it complains there is a syntax error on the 2nd INTO clause. It appears I can have only 1 INTO clause per SQL statement. That means I have to execute 5 different SQL statements to get all of the results. That to me is incredibly inefficient. Is there any way to avoid this? I'd like to be able to do this (only 3 OUT parameters in this example): CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl, Max(prod_price) INTO ph, Avg(prod_price) INTO pa FROM products; END; But I have to break them out into separate Select statements. CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END; Not only is this slower, but I also run the risk of the prices being modified between the Select calls. Is there a way around this? Do I have to resort to using session variables? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?
One statement will do: SELECT Min(prod_price) , Max(prod_price), Avg(prod_price) INTO pl, ph, pa from products; - michael dykman On Mon, Feb 28, 2011 at 4:30 PM, mos mo...@fastmail.fm wrote: I want to have a stored procedure that returns 5 columns from a table and do some calculations on it. But when I try it, it complains there is a syntax error on the 2nd INTO clause. It appears I can have only 1 INTO clause per SQL statement. That means I have to execute 5 different SQL statements to get all of the results. That to me is incredibly inefficient. Is there any way to avoid this? I'd like to be able to do this (only 3 OUT parameters in this example): CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl, Max(prod_price) INTO ph, Avg(prod_price) INTO pa FROM products; END; But I have to break them out into separate Select statements. CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END; Not only is this slower, but I also run the risk of the prices being modified between the Select calls. Is there a way around this? Do I have to resort to using session variables? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Why do stored procedures limited to Select stmt to 1 OUT parameter?
At 03:36 PM 2/28/2011, Michael Dykman wrote: One statement will do: SELECT Min(prod_price) , Max(prod_price), Avg(prod_price) INTO pl, ph, pa from products; - michael dykman Michael, Brilliant! Thanks. :-) Mike On Mon, Feb 28, 2011 at 4:30 PM, mos mo...@fastmail.fm wrote: I want to have a stored procedure that returns 5 columns from a table and do some calculations on it. But when I try it, it complains there is a syntax error on the 2nd INTO clause. It appears I can have only 1 INTO clause per SQL statement. That means I have to execute 5 different SQL statements to get all of the results. That to me is incredibly inefficient. Is there any way to avoid this? I'd like to be able to do this (only 3 OUT parameters in this example): CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl, Max(prod_price) INTO ph, Avg(prod_price) INTO pa FROM products; END; But I have to break them out into separate Select statements. CREATE PROCEDURE productpricing( OUT pl DECIMAL(8,2), OUT ph DECIMAL(8,2), OUT pa DECIMAL(8,2) ) BEGIN SELECT Min(prod_price) INTO pl FROM products; SELECT Max(prod_price) INTO ph FROM products; SELECT Avg(prod_price) INTO pa FROM products; END; Not only is this slower, but I also run the risk of the prices being modified between the Select calls. Is there a way around this? Do I have to resort to using session variables? Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=mdyk...@gmail.com -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [MySQL] Re: Variables in stored procedures
Huh? That doesn't make a whole lot of sense. If that is the case, can you explain why? Is the stored procedure mechanism that primitive? From: Ted Yu [mailto:yuzhih...@gmail.com] Sent: Saturday, May 01, 2010 7:11 AM To: Ashley M. Kirchner Subject: Re: [MySQL] Re: Variables in stored procedures I am afraid you need to construct the whole stored procedure using CONCAT.
Variables in stored procedures
I have the following procedure:
--
CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int)
begin
declare userlng double;
declare userlat double;
declare lng1 float;
declare lng2 float;
declare lat1 float;
declare lat2 float;
select lng, lat into userlng, userlat from coords_tbl where id=userid
limit 1;
set lng1 = userlng-maxdist/abs(cos(radians(userlat))*69);
set lng2 = userlng+maxdist/abs(cos(radians(userlat))*69);
set lat1 = userlat-(maxdist/69);
set lat2 = userlat+(maxdist/69);
select id,lat,lng,3959*2*asin(sqrt(power(sin((userlat - abs(lat)) *
pi()/180/2),2) + cos(userlat*pi()/180) * cos(abs(lat) * pi()/180) *
power(sin((userlng - lng) * pi()/180/2),2))) as distance from coords_tbl
where lng between lng1 and lng2 and lat between lat1 and lat2 having
distance maxdist order by distance;
--
This works just dandy ... with one small problem. I need to be
able to pass a variable to the procedure that represents the table it
should be querying. At the moment it's querying the hard coded table
'coords_tbl' ...
So the create statement should change to:
'CREATE PROCEDURE `geodistance` (IN userid int, IN maxdist int, IN
tblname char)'
But then, how do I pass that to the select queries after wards?
I tried setting a @tmp_query which is a CONCAT('select ... from ',
tblname, ' ...'); and using prepare and execute to run it, but it always
comes back telling me 'userlng' is undefined (presumably because that's
the first one in the select query). So I'm missing something, somewhere ...
Suggestions anyone?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Variables in stored procedures
Ashley M. Kirchner wrote:
I have the following procedure:
--
CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int)
begin
declare userlng double;
declare userlat double;
declare lng1 float;
declare lng2 float;
declare lat1 float;
declare lat2 float;
select lng, lat into userlng, userlat from coords_tbl where id=userid
limit 1;
set lng1 = userlng-maxdist/abs(cos(radians(userlat))*69);
set lng2 = userlng+maxdist/abs(cos(radians(userlat))*69);
set lat1 = userlat-(maxdist/69);
set lat2 = userlat+(maxdist/69);
select id,lat,lng,3959*2*asin(sqrt(power(sin((userlat - abs(lat)) *
pi()/180/2),2) + cos(userlat*pi()/180) * cos(abs(lat) * pi()/180) *
power(sin((userlng - lng) * pi()/180/2),2))) as distance from coords_tbl
where lng between lng1 and lng2 and lat between lat1 and lat2 having
distance maxdist order by distance;
--
This works just dandy ... with one small problem. I need to be able
to pass a variable to the procedure that represents the table it should
be querying. At the moment it's querying the hard coded table
'coords_tbl' ...
So the create statement should change to:
'CREATE PROCEDURE `geodistance` (IN userid int, IN maxdist int, IN
tblname char)'
But then, how do I pass that to the select queries after wards?
I tried setting a @tmp_query which is a CONCAT('select ... from ',
tblname, ' ...'); and using prepare and execute to run it, but it always
comes back telling me 'userlng' is undefined (presumably because that's
the first one in the select query). So I'm missing something, somewhere
...
Suggestions anyone?
Two options
1) use the placeholder, a question mark, to represent the location to
which you want a variable to appear then pass those variables in as part
of your execute statement. The name of the table cannot be resolved as a
variable at execution time. To quote
http://dev.mysql.com/doc/refman/5.1/en/prepare.html
###
Parameter markers can be used only where data values should appear, not
for SQL keywords, identifiers, and so forth.
###
see also:
http://dev.mysql.com/doc/refman/5.1/en/execute.html
2) resolve your variables as you build your SQL string so that they no
longer refer to a variable but become literal values within the query
you are constructing.
--
Shawn Green
MySQL Principle Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: [MySQL] Re: Variables in stored procedures
-Original Message-
From: Shawn Green [mailto:shawn.l.gr...@oracle.com]
Sent: Friday, April 30, 2010 9:49 PM
To: Ashley M. Kirchner
Cc: mysql@lists.mysql.com
Subject: [MySQL] Re: Variables in stored procedures
Two options
1) use the placeholder, a question mark, to represent the location to
which you want a variable to appear then pass those variables in as
part
of your execute statement. The name of the table cannot be resolved as
a
variable at execution time. To quote
http://dev.mysql.com/doc/refman/5.1/en/prepare.html
###
Parameter markers can be used only where data values should appear, not
for SQL keywords, identifiers, and so forth.
###
see also:
http://dev.mysql.com/doc/refman/5.1/en/execute.html
2) resolve your variables as you build your SQL string so that they no
longer refer to a variable but become literal values within the query
you are constructing.
Thanks for the reply Shawn. I'm trying the fill in the variable and I can
see it being replaced and all, but then it fails with the undeclared
variable error:
CREATE PROCEDURE `geodistance`(IN userid int, IN maxdist int, IN tblname
varchar(20))
begin
declare userlng double;
declare userlat double;
declare lng1 float;
declare lng2 float;
declare lat1 float;
declare lat2 float;
set @tmpquery = CONCAT('select lng, lat into userlng, userlat from ',
tblname, ' where id=userid limit 1');
prepare query from @tmpquery;
execute query;
deallocate prepare query;
set lng1 = userlng-maxdist/abs(cos(radians(userlat))*69);
set lng2 = userlng+maxdist/abs(cos(radians(userlat))*69);
set lat1 = userlat-(maxdist/69);
set lat2 = userlat+(maxdist/69);
set @tmpquery = CONCAT('select
id,lat,lng,3959*2*asin(sqrt(power(sin((userlat - abs(lat)) * pi()/180/2),2)
+ cos(userlat*pi()/180) * cos(abs(lat) * pi()/180) * power(sin((userlng -
lng) * pi()/180/2),2))) as distance from ', tblname, ' where lng between
lng1 and lng2 and lat between lat1 and lat2 having distance maxdist order
by distance');
prepare query from @tmpquery;
execute query;
deallocate prepare query;
When I run that, I get:
mysql call geodistance(231, 2, 'coords_tbl');
ERROR 1327 (42000): Undeclared variable: userlng
mysql select @tmpquery;
+---
+
| @tmpquery
|
+---
+
| select lng, lat into userlng, userlat from coords_tbl where id=userid
limit 1 |
+---
+
The query is correct, so why does it complain that userlng isn't declared
when I declared it at the very top? I also tried the place holder route and
get the same error .
Re: When to use Stored Procedures
Hi Johan, Johan De Meersman wrote: as a totally off-topc question, wouldn't something along the lines of LIMIT COUNT(*)/2, 1 do that trick? On 4/15/10, Rhino rhi...@sympatico.ca wrote: ...snip... For example, suppose you had to determine the median grade for a test. ... That would require running the query twice as LIMIT only accepts numeric literals. For a large dataset, that would destroy the efficiency of the stored procedure. It's more efficient with MySQL to capture the value in a temporary table, count those temporary results, then create a LIMIT query using the prepared statement syntax (dynamic SQL) against the data in the temp table. http://dev.mysql.com/doc/refman/5.1/en/select.html The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must both be nonnegative integer constants (except when using prepared statements). The above process could very easily be encapsulated by a stored PROCEDURE (but not by a stored FUNCTION) so that you would not need to implement it in your client code. Unfortunately the stored functions are not allowed to use prepared statements, yet. http://dev.mysql.com/doc/refman/5.1/en/stored-program-restrictions.html SQL prepared statements (PREPARE, EXECUTE, DEALLOCATE PREPARE) can be used in stored procedures, but not stored functions or triggers. Hope that helps! -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: When to use Stored Procedures
as a totally off-topc question, wouldn't something along the lines of LIMIT COUNT(*)/2, 1 do that trick? On 4/15/10, Rhino rhi...@sympatico.ca wrote: Shawn Green wrote: Tompkins Neil wrote: Hi, I've used mainly of the older versions of MySQL. However am looking to port a application across to MySQL 5. My question is when would one decide to use a Stored Procedure over a query written at the application level ? The decision to encapsulate a particular process or query within a stored procedure is usually based on your business needs. * Common queries that only change by parameters are good candidates * Complex multi-step queries are good candidates * If you need an unprivileged user to make a change to a sensitive table, you can avoid some security problems by wrapping that process in a stored procedure. For example, maybe part of your internal HR processes is an application that allows people to update their phone numbers but can't be allowed to see the private information in an employee's database record. You could create a privileged routine called change_phone_number() that could do that without giving the application (or another user) the rights to manipulate that table directly. Does this help? The other situation that strongly justifies a stored procedure in place of an application level query is one that involves a lot of churning through the database to return only a small amount of data. For example, suppose you had to determine the median grade for a test. Let's say there were a million separate people taking this test (perhaps something like college admission tests). To find the median, it's probably best to sort the rows in ascending order by final grade, then count down from the top until you are halfway through the list. Then you can find and return the median grade. That involves a lot of I/O: a million rows to read, then the sorting, then reading down a half million rows to find the midpoint. But all you're returning is the median. If a client program has to do that work, all of those I/Os are going to be sent over the network and cost a lot. But if you write a stored procedure, it will do all the heavy lifting LOCALLY on the database server, which will reduce costs dramatically. Only the median gets sent across the network back to the client program. Obviously, this will save on both I/O costs and network utilization. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: When to use Stored Procedures
Tompkins Neil wrote: Hi, I've used mainly of the older versions of MySQL. However am looking to port a application across to MySQL 5. My question is when would one decide to use a Stored Procedure over a query written at the application level ? The decision to encapsulate a particular process or query within a stored procedure is usually based on your business needs. * Common queries that only change by parameters are good candidates * Complex multi-step queries are good candidates * If you need an unprivileged user to make a change to a sensitive table, you can avoid some security problems by wrapping that process in a stored procedure. For example, maybe part of your internal HR processes is an application that allows people to update their phone numbers but can't be allowed to see the private information in an employee's database record. You could create a privileged routine called change_phone_number() that could do that without giving the application (or another user) the rights to manipulate that table directly. Does this help? -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: When to use Stored Procedures
Shawn Green wrote: Tompkins Neil wrote: Hi, I've used mainly of the older versions of MySQL. However am looking to port a application across to MySQL 5. My question is when would one decide to use a Stored Procedure over a query written at the application level ? The decision to encapsulate a particular process or query within a stored procedure is usually based on your business needs. * Common queries that only change by parameters are good candidates * Complex multi-step queries are good candidates * If you need an unprivileged user to make a change to a sensitive table, you can avoid some security problems by wrapping that process in a stored procedure. For example, maybe part of your internal HR processes is an application that allows people to update their phone numbers but can't be allowed to see the private information in an employee's database record. You could create a privileged routine called change_phone_number() that could do that without giving the application (or another user) the rights to manipulate that table directly. Does this help? The other situation that strongly justifies a stored procedure in place of an application level query is one that involves a lot of churning through the database to return only a small amount of data. For example, suppose you had to determine the median grade for a test. Let's say there were a million separate people taking this test (perhaps something like college admission tests). To find the median, it's probably best to sort the rows in ascending order by final grade, then count down from the top until you are halfway through the list. Then you can find and return the median grade. That involves a lot of I/O: a million rows to read, then the sorting, then reading down a half million rows to find the midpoint. But all you're returning is the median. If a client program has to do that work, all of those I/Os are going to be sent over the network and cost a lot. But if you write a stored procedure, it will do all the heavy lifting LOCALLY on the database server, which will reduce costs dramatically. Only the median gets sent across the network back to the client program. Obviously, this will save on both I/O costs and network utilization. -- Rhino -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to watch stored procedures
Since I work on MySQL by using phpMyAdmin, is there anyone saying to me if and how it's possibile to see the code of a stored procedure that I've created? For example on Oracle I use sql-developer but with phpMyAdmin is it possible to see the whole code of a procedure or of a trigger after creating it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: How to watch stored procedures
In phpmyqdmin in SQL window type : show create procedure procedureName; eg; SHOW CREATE PROCEDURE circle_area; in SQL goog_1193647372 Check more on : http://adminlinux.blogspot.com/2009/12/mysql-tips-verify-database-objects.html Thanks On Fri, Apr 9, 2010 at 1:00 PM, alba.albetti alba.albe...@libero.it wrote: Since I work on MySQL by using phpMyAdmin, is there anyone saying to me if and how it's possibile to see the code of a stored procedure that I've created? For example on Oracle I use sql-developer but with phpMyAdmin is it possible to see the whole code of a procedure or of a trigger after creating it? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA Datavail-India Mumbai Mobile : 91-9987681929 www.datavail.com My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
When to use Stored Procedures
Hi, I've used mainly of the older versions of MySQL. However am looking to port a application across to MySQL 5. My question is when would one decide to use a Stored Procedure over a query written at the application level ? Cheers Neil
Allowing triggers stored procedures on MySQL
Hi everyone, Currently we have a policy that prohibit our customers from creating stored procedures and triggers on their DB's which I imagine must be driving them up the walls. It's like having a car with a boot but you are not able to use it. :) Are there any reasons why we would'nt want customers to make use of these built in features and what other means are available. My reading showed that you need the create routine privilege and you *may* require the super privilege if you have binary logging enabled (and then that only becomes a potential issue if you are actually replaying those logs (ie. either for replication or for media recovery). I think I was reading the MySQL 5.1 manual - so maybe this is different with 5.0? Kind Regards Brent Clark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Allowing triggers stored procedures on MySQL
On 07/03/2010, at 3:30 AM, Brent Clark wrote: Hi everyone, Currently we have a policy that prohibit our customers from creating stored procedures and triggers on their DB's which I imagine must be driving them up the walls. It's like having a car with a boot but you are not able to use it. :) Are there any reasons why we would'nt want customers to make use of these built in features and what other means are available. My reading showed that you need the create routine privilege and you *may* require the super privilege if you have binary logging enabled (and then that only becomes a potential issue if you are actually replaying those logs (ie. either for replication or for media recovery). I think I was reading the MySQL 5.1 manual - so maybe this is different with 5.0? In MySQL 5.0 (I get the impression that's the version you are running) it requires SUPER to create triggers, however in 5.1 a new TRIGGER privilege was introduced for that. The requirement on SUPER for binary logging applies is the log_bin_trust_function_creators is not set to 1. The reason for this is to avoid random users creating non-deterministic procedures that then replicate to a slave and causes the slave and master to get out of sync. If binary logging is not enabled, SUPER is never required in order to create a stored procedure. See more in http://dev.mysql.com/ doc/refman/5.0/en/stored-programs-logging.html for MySQL 5.0 or http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html for MySQL 5.1. Best regards, Jesper -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: tuning stored procedures
Ted, all, Ted Yu wrote: Hi, We currently use SQL statements embedded in Java code. I believe stored procedures would give us better performance. In general, this is likely, because you would (at least) save on the number of statements and the amount of data communicated between your application and the server. I found http://lists.mysql.com/maxdb/16680 which is very old. MaxDB is a completely different thing from the MySQL server. Yes, both are DBMSs supporting the language SQL, but that is about all. You cannot in any way assume that a hint regarding MaxDB is valid for the MySQL server, or vice versa. Can someone share stored procedures tuning practices ? I personally can't help you there - hopefully someone else will. Regards, Jörg -- Joerg Bruehe, MySQL Build Team, joerg.bru...@sun.com Sun Microsystems GmbH, Komturstraße 18a, D-12099 Berlin Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Wolf Frenkel Vorsitzender des Aufsichtsrates: Martin Haering Muenchen: HRB161028 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
tuning stored procedures
Hi, We currently use SQL statements embedded in Java code. I believe stored procedures would give us better performance. I found http://lists.mysql.com/maxdb/16680 which is very old. Can someone share stored procedures tuning practices ? Thanks
Edit Stored Procedures
I'm trying to edit one of my stored procedures using MySQL Query Browser and this is what I get back as a listing when I select Edit All Procedures: DROP PROCEDURE IF EXISTS `kwbo4418_2008bookworminventory`.`UpdateStock` $$ /*!50003 SET @TEMP_SQL_MODE=@@SQL_MODE, SQL_MODE='' */ $$ (null) $$ /*!50003 SET SESSION sql_mo...@temp_sql_mode */ $$ I used to edit it in this fashion just fine, and I know the procedure is there on the server, as we run it many times a day successfully. Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Stored Procedures from SQL Server
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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Stored Procedures from SQL Server
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
Tips for optimizing stored procedures
Hi all I am new to stored procedures and have just started using them. Is there any Web site you could suggest which discusses common optimization tips for MySQL SPs? Vikram -- Operator: So what do you need? Besides a miracle. Neo: Guns. Lots of guns. -- The Matrix -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
stored procedures embedded server
For embedded server do I need to configure --with-embedded-privilege-control? It looks like stored procedure is looking for mysql.proc table. Thanks.
Query cache problem with stored procedures
Hi all, I'm benchmarking performance improvement with MySQL Query Cache turned on but I'm facing some problem with queries inside stored procedures when they contains variable parameters I just created this stored procedure to identify the problem CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS SQL DATA DETERMINISTIC BEGIN SELECT password FROM users WHERE mailbox = mailbox_number; END I see, looking at Qcache status, that calls to that stored procedure are not cached. Is it a known limitation ? I think that MySQL should evaluate caching after variable substitution but I'm not aware of MySQL internals to judge if this is the correct behaviour. P.S: I noticed that MySQL caches without problems queries contained in stored procedures which does not contains variables. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query cache problem with stored procedures
Hi, Your questions are answered in the manual: http://dev.mysql.com/doc/refman/5.0/en/query-cache-how.html It is a known limitation. Edoardo Serra wrote: Hi all, I'm benchmarking performance improvement with MySQL Query Cache turned on but I'm facing some problem with queries inside stored procedures when they contains variable parameters I just created this stored procedure to identify the problem CREATE PROCEDURE `proc_test_qcache`(IN mailbox_number VARCHAR(64)) READS SQL DATA DETERMINISTIC BEGIN SELECT password FROM users WHERE mailbox = mailbox_number; END I see, looking at Qcache status, that calls to that stored procedure are not cached. Is it a known limitation ? I think that MySQL should evaluate caching after variable substitution but I'm not aware of MySQL internals to judge if this is the correct behaviour. P.S: I noticed that MySQL caches without problems queries contained in stored procedures which does not contains variables. Tnx in advance for help Regards Edoardo Serra WeBRainstorm S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: drop stored procedures in prepare statment
Hi Xian, xian liu wrote: ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet mysql drop procedure ct_tb// Query OK, 0 rows affected (0.00 sec) the same, drop function/trigger xxx is also not supported in prepare statment. Is it a lack of feature in MySQL-5.0 version ?? Exactly. MySQL doesn't support dropping/creating functions/procedures or triggers in functions or triggers. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
回复： Re: drop stored procedures in p repare statment
thanks for your response! Hope engineers of MySQL AB can improve it. Nils Meyer [EMAIL PROTECTED] 写道： Hi Xian, xian liu wrote: ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet mysql drop procedure ct_tb// Query OK, 0 rows affected (0.00 sec) the same, drop function/trigger xxx is also not supported in prepare statment. Is it a lack of feature in MySQL-5.0 version ?? Exactly. MySQL doesn't support dropping/creating functions/procedures or triggers in functions or triggers. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] @@@^_^@@@ - 雅虎免费邮箱-3.5G容量，20M附件
drop stored procedures in prepare statment
hi guys, I'm searching stored procedures with prepare statment in mysql-5.0. It seems that can not use 'drop procedure xxx' in prepare statment , like this: mysql create procedure drpsp() - begin - prepare aa from 'drop procedure ct_tb'; - execute aa; - end - // Query OK, 0 rows affected (0.00 sec) mysql call drpsp// ERROR 1295 (HY000): This command is not supported in the prepared statement protocol yet mysql drop procedure ct_tb// Query OK, 0 rows affected (0.00 sec) the same, drop function/trigger xxx is also not supported in prepare statment. Is it a lack of feature in MySQL-5.0 version ?? Thanks! @@@^_^@@@ - 雅虎免费邮箱-3.5G容量，20M附件
Re: Create stored procedures with Query Browser
On Wednesday 06 December 2006 17:13, Ed Reed wrote: Can someone tell me if it is possible to create stored procedures with Query Browser and if it is can you provide an example the works? Thanks Should just be Script-Create Stored Procedure/Function, give it a name and got at it. Only thing I found buggy was that I didn't seem to be able to edit existing ones (sql came up blank, but show create procedure foo_bar doesn't in the text console). Not sure if this is a version thing, as I haven't attempted to upgrade. For now I just copy and paste from console into the query browser script editor and do it there. -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create stored procedures with Query Browser
Can someone tell me if it is possible to create stored procedures with Query Browser and if it is can you provide an example the works? Thanks
Re: Calling Stored Procedures from PHP
Filipe Freitas wrote:
Hi,
This is not a mysql thing but maybe you can help me.
I want to call a stored procedure from PHP, so I tried it like normal
querys: mysql_query(CALL mySP();); with no success.
thx
I'm not PHP expert but I've managed to do this in perl using DBI (in
spite of all docs saying there are no such thing as OUT params). Perhaps
you could try s.th. alike:
# perl DBI with firebird: stored proc has 3 IN and 4 OUT params as you
can see...
my $s = SELECT field1, field2, field3, CAST(field4 AS VARCHAR(32)).
FROM S_GET_NAL_WEB('12-31-05', 4201, '2051600');
my $sth = $dbh-prepare($s) or die $dbh-errstr();
$sth-execute() or die $sth-errstr();
while (@data = $sth-fetchrow_array()) {
print join(BR, @data).BRBR;
}
...
HTH
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
On Monday 27 November 2006 09:12, Filipe Freitas wrote: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end You seem to be mimicing the prepared query feature of mysqli in PHP5. Do you have the mysqli extension avaliable? If so, you can use things like: http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php which has an example as to how to utilize a prepared query. -- Chris White PHP Programmer Interfuel 805.642.2200 x110 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Chris White wrote: On Monday 27 November 2006 09:12, Filipe Freitas wrote: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end You seem to be mimicing the prepared query feature of mysqli in PHP5. Do you have the mysqli extension avaliable? If so, you can use things like: http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php which has an example as to how to utilize a prepared query. I never really looked into mysqli, so far I only used the mysql extention in PHP. So maybe that will help. thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
You seem to be mimicking the prepared query feature of mysqli in PHP5.
Do you
have the mysqli extension available? If so, you can use things like:
http://www.php.net/manual/en/function.mysqli-stmt-bind-param.php
which has an example as to how to utilize a prepared query.
I never really looked into mysqli, so far I only used the mysql
extension in PHP.
So maybe that will help.
thanks
Filipe,
As someone else suggested, the mysql extension also works just fine:
?php
// StoredProc(@param1, param2) sums a table Id int column and divides by param2.
// The result is stuffed into @param1
$query1 = call StoredProc(@param1, param2);
$result1 = mysql_query($query1) or die('Query failed: ' . mysql_error());
$query2 = select @param1;
$result2 = mysql_query($query2) or die('Query failed: ' . mysql_error());
echo table\n;
while ($line = mysql_fetch_array($result2, MYSQL_ASSOC)) {
echo \ttr\n;
foreach ($line as $col_value) {
echo \t\ttd$col_value/td\n;
}
echo \t/tr\n;
}
echo /table\n;
?
David
Calling Stored Procedures from PHP
Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Remove the Semicolon mysql_query(CALL mySP()); Give it a try !!! - Original Message - From: Filipe Freitas [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, November 27, 2006 10:59:09 AM GMT-0500 US/Eastern Subject: Calling Stored Procedures from PHP Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx -- 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: Calling Stored Procedures from PHP
On Monday 27 November 2006 07:59, Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx No success how? Generally with stored procedures and returning values, you use a session variable and utilize it as OUT like so: DROP PROCEDURE IF EXISTS CURVAL $ CREATE PROCEDURE CURVAL (OUT current INT) BEGIN SELECT value INTO current FROM sequence; END $ DELIMITER ; so when I go to call curval: CALL CURVAL(@cur) and the value can be received by: SELECT @cur; -- Chris White PHP Programmer Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Chris White wrote: On Monday 27 November 2006 07:59, Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. thx No success how? Generally with stored procedures and returning values, you use a session variable and utilize it as OUT like so: DROP PROCEDURE IF EXISTS CURVAL $ CREATE PROCEDURE CURVAL (OUT current INT) BEGIN SELECT value INTO current FROM sequence; END $ DELIMITER ; so when I go to call curval: CALL CURVAL(@cur) and the value can be received by: SELECT @cur; My stored procedure is the following: CREATE PROCEDURE `getListaDeNewsflashes`(in quantidade smallint) COMMENT 'Devolve uma tabela com um número limite de newsflashes' begin PREPARE statement FROM SELECT * FROM newsflashes ORDER BY RAND() LIMIT ?; SET @limit=quantidade; EXECUTE statement USING @limit; end it works when I execute it on mysql monitor: call getListaDeNewsflashes(10); in php it doesn't. I think I need the out session variable like you said. But how? I will need a variable for every column? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Calling Stored Procedures from PHP
Filipe Freitas wrote: Hi, This is not a mysql thing but maybe you can help me. I want to call a stored procedure from PHP, so I tried it like normal querys: mysql_query(CALL mySP();); with no success. What is happening when you make the call? That might help get some more precise advice. My understanding of using PHP with MySQL stored procedures is that you have to call them through the mysqli extension. It doesn't appear that you are using them. With them your would be using the mysqli_query() function. I don't do a lot of PHP, but I stumbled into a similar problem and using the mysqli extensions solved the issue. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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]
RE: Stored Procedures Question
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]
stored procedures and regex
Hi Can mysql's stored procedures do regex function -- Gregory Machin [EMAIL PROTECTED] www.linuxpro.co.za
How to dump stored procedures to a file?
It seems that mysqldump on mysql 5.0 does not export my stored procedures. How do I dump these out so that other people can load them into their copies of my database? Matt -- A better way of running series of SAS programs: http://overlook.homelinux.net/wilsonwiki/SasAndMakefiles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to call stored procedures using C API
Hi to all, I try to call a stored procedure using mysql_real_query and with the CLIENT_MULTI_STATEMENTS flag on mysql_real connect() function. When i try the statement call sp1(@myparam); the error can't return the results in actual context is occur. Please, if is some one which have experience whith the handling store procedures using MySQL C API, to give me a solution. I use mysql-5.0.2x on Fedora 5. Thank you, -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How to call stored procedures using C API
Hi again folks, The problem is solved.Was entirely my fault because i didn't check the version of mysqlclient library. -Original Message- From: Gelu Gogancea Sent: Monday, 21 August, 2006 12:47 PM To: mysql@lists.mysql.com Subject: How to call stored procedures using C API Hi to all, I try to call a stored procedure using mysql_real_query and with the CLIENT_MULTI_STATEMENTS flag on mysql_real connect() function. When i try the statement call sp1(@myparam); the error can't return the results in actual context is occur. Please, if is some one which have experience whith the handling store procedures using MySQL C API, to give me a solution. I use mysql-5.0.2x on Fedora 5. Thank you, -- 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]
Parameterized stored procedures via ADO Command Object
Hi, I have been trying very hard to get this working but I have NOT been able to call the stored procedures in my MySQL database using the ADO (**NOT** Ado.NET) Command object. I want to use stored procedures 'cos I want to restrict access to stored procedures and views only. However, it seems like support of ADO Command object has not been implemented - at least, that's what posters on the relevant MySQL forum say. Is there __any__ way I can do this? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
manual on stored procedures and triggers
Hello, i'm searching for a manual about stored procedures and triggers. I'm new about this arguments but i want to study them. Unfortunally, mysql online manual is not rich about examples so, can you suggest me some online manuals on stored procedures and triggers? Thank you, Vittorio -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: manual on stored procedures and triggers
On Wednesday 09 August 2006 07:27 am, Vittorio Zuccalà wrote: Hello, i'm searching for a manual about stored procedures and triggers. I'm new about this arguments but i want to study them. Unfortunally, mysql online manual is not rich about examples so, can you suggest me some online manuals on stored procedures and triggers? There's actually a MySQL dev article on it. Get the PDF here: http://dev.mysql.com/tech-resources/articles/mysql-storedprocedures.pdf I found it really helpfull when starting out on stored procedures with MySQL. If you're looking for book form, I found The Definitive Guide to MySQL 5 by Michael Kofler (Apress) to be a good resource. Thank you, Vittorio -- Chris White PHP Programmer/DBarbaric Interfuel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ROLLBACK/COMMIT in Stored Procedures
Hi, In my stored procedures, i want to ROLLBACK when I encounter any invalid values. However, as it happens, I cannot because MySQL does not support COMMIT/ROLLBACK functionality right now (as of ver. 5.0.22 on WinXP Pro). I am setting session variables (Set @XX=Error Message') according ot each anomally I find in the IN args of the procedures and inserting duplicate values in a temporary table to make MySQL throw me a duplicate-key error that I can then check from VB6 using another procedure giving me the @XX value. Is this the correct way? Is there any other better way of doing the same thing? -- Thanks in advance, Asif -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures
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
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
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
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]
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
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
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
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
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
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
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: arrays in stored procedures - pl. help
Chris, thank you for the response, but that was not my question. My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. On 7/17/06, Chris [EMAIL PROTECTED] wrote: L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance.
Re: arrays in stored procedures - pl. help
My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. MySQL doesn't have arrays. Pass the data in a comma-delimited string and PREPARE the statement, or pass it as a temp memory table. PB - L P wrote: Chris, thank you for the response, but that was not my question. My question is how do I send multiple sets of data into a stored procedure without doing the things I had outlined. On 7/17/06, Chris [EMAIL PROTECTED] wrote: L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance. No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.394 / Virus Database: 268.10.4/396 - Release Date: 7/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: arrays in stored procedures - pl. help
L P wrote: Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? insert into table(field1, field2, field3) values (value1, value2, value3), (value4, value5, value6); http://dev.mysql.com/doc/refman/5.1/en/insert.html Don't use arrays for storage, you'll lose a lot of performance. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
arrays in stored procedures - pl. help
Folks, say I have a need to add multiple rows at the same time. for instance, say I'm collecting customer information and I want to add 3 addresses and 3 phone numbers at the same time for a customer. The above is quite straightforward to accomplish when there is only one set of data to deal with (one address / one phone number) - with simple data types passed in as parameters. What alternatives / options do I have to accomplish storing multiple sets of data? I do not want to call the stored proc. multiple times from an external program. (avoid if possible) I do not want to write to a dump file, manage that, and use mysqlloader. I do not want incorporate split, join logic into the stored procedure. (avoid if possible) Thanks, listaction
Question on views, stored procedures temporary tables
Hi all. I have a long and complicated chain of queries in MS Access that I want to port to using stored procedures views. What I'd like to do is something like: 1) stored procedures grabs original data set, and dumps into temporary tables 2) I open a view, which is in turn based on other views, which eventually points back to the temporary tables created in 1) Can I do that? ie if I create the views now, will they still work later, considering the temporary tables will be dropped and re-created later on? Do I have to drop the views as well, and re-create them after the stored procedure that creates and populates the temporary tables? Thanks :) Dan -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stored Procedures and DECLARE weirdness
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? -- Chris White PHP Programmer/DB Knight 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 DECLARE weirdness
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
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]
The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS i'm crazy to use this ? Tnks in advance MySQL , InnoDB and Linux ! -- - ++ Dyego Souza Dantas Leal ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A ***http://javacoffe.blogspot.com*** - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 --ICQ : 1647350 $ look into my eyes Phone : +55 041 2106-1212 look: cannot open my eyes Fax : +55 041 3296-6640 - Reply: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The MySQL Stored Procedures and Functions is ready ?
The MySQL Stored Procedures and Functions is ready to use on production systems ? or support is very ammature ? I need to know because is a project desing decision ! Tnks !! PLEASE I NEED OPINIONS By the time your project is finished designing, it will be ready ;-) 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]
Stored procedures and views
Howdy folks, I'm trying to create a flatfile dump from our database which requires some functionality that's not possible with raw sql. Up to now we've been using PHP tied to mySql to do all the work and get the data in the correct format. We would like to switch this functionality over to stored procedures in mySql. My question is this: Is it possible to hook a series of stored procedures to a view so that the data is live and current? We could set it up to run our procedures on some regular interval and dump the result into a temporary table but having up-to-date data would be ideal. Thanks, Chris Carrier -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures and views
Chriss, I'm trying to create a flatfile dump from our database which requires some functionality that's not possible with raw sql. Up to now we've been using PHP tied to mySql to do all the work and get the data in the correct format. We would like to switch this functionality over to stored procedures in mySql. My question is this: Is it possible to hook a series of stored procedures to a view so that the data is live and current? What do you mean by that? A view is always current. Can you explain it a bit better? We could set it up to run our procedures on some regular interval and dump the result into a temporary table but having up-to-date data would be ideal. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedures and views
I just mean is it possible to hook a set of stored procedures to a view as opposed to a temporary table that we'd have to explicitly update? Chris -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, March 24, 2006 10:26 AM To: mysql@lists.mysql.com Subject: Re: Stored procedures and views Chriss, I'm trying to create a flatfile dump from our database which requires some functionality that's not possible with raw sql. Up to now we've been using PHP tied to mySql to do all the work and get the data in the correct format. We would like to switch this functionality over to stored procedures in mySql. My question is this: Is it possible to hook a series of stored procedures to a view so that the data is live and current? What do you mean by that? A view is always current. Can you explain it a bit better? We could set it up to run our procedures on some regular interval and dump the result into a temporary table but having up-to-date data would be ideal. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Stored procedures and views
I just mean is it possible to hook a set of stored procedures to a view as opposed to a temporary table that we'd have to explicitly update? hook? If you mean: using the views in select statements inside a procedure: sure it. Did you try? Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com Chris -Original Message- From: Martijn Tonies [mailto:[EMAIL PROTECTED] Sent: Friday, March 24, 2006 10:26 AM To: mysql@lists.mysql.com Subject: Re: Stored procedures and views Chriss, I'm trying to create a flatfile dump from our database which requires some functionality that's not possible with raw sql. Up to now we've been using PHP tied to mySql to do all the work and get the data in the correct format. We would like to switch this functionality over to stored procedures in mySql. My question is this: Is it possible to hook a series of stored procedures to a view so that the data is live and current? What do you mean by that? A view is always current. Can you explain it a bit better? We could set it up to run our procedures on some regular interval and dump the result into a temporary table but having up-to-date data would be ideal. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Stored procedures and views
If the features available in stored procedures {i.e. looping etc.} are
required, have you tried having the procedure 1st write/edit the data in a
temporary table and at the end select the values you want from that table.
I think I might write 2 procedures. One that does the data manipulation and
the other that selects the result.
something like this
DELIMITER //
DROP PROCEDURE IF EXITS manipulate//
CREATE PROCEDURE manipulate(
CREATE TEMPORARY TABLE data_result
...
...
END//
DROP PROCEDURE IF EXITS result//
CREATE PROCEDURE result(
...
CALL manipulate (
...
SELECT ... FROM data_result
END//
DELIMITER ;
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 1:04 PM
To: Chris Carrier; mysql@lists.mysql.com
Subject: Re: Stored procedures and views
I just mean is it possible to hook a set of stored procedures to a view
as
opposed to a temporary table that we'd have to explicitly update?
hook?
If you mean: using the views in select statements inside a procedure:
sure it. Did you try?
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
Chris
-Original Message-
From: Martijn Tonies [mailto:[EMAIL PROTECTED]
Sent: Friday, March 24, 2006 10:26 AM
To: mysql@lists.mysql.com
Subject: Re: Stored procedures and views
Chriss,
I'm trying to create a flatfile dump from our database which requires
some
functionality that's not possible with raw sql. Up to now we've been
using
PHP tied to mySql to do all the work and get the data in the correct
format.
We would like to switch this functionality over to stored procedures
in
mySql. My question is this: Is it possible to hook a series of stored
procedures to a view so that the data is live and current?
What do you mean by that?
A view is always current.
Can you explain it a bit better?
We could set it
up to run our procedures on some regular interval and dump the result
into
a
temporary table but having up-to-date data would be ideal.
Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
No virus found in this incoming message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.385 / Virus Database: 268.3.1/291 - Release Date: 3/24/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does MySQL have the ability to send resultsets from stored procedures?
Does MySQL have the ability to send resultsets from stored procedures? (similar to ref cursors in Oracle). _ FREE pop-up blocking with the new MSN Toolbar get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Support for temporary tables inside stored procedures?
Does MySQL have support for temporary tables inside stored procedures? _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Does MySQL have the ability to send resultsets from stored procedures?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:04:50 AM: Does MySQL have the ability to send resultsets from stored procedures? (similar to ref cursors in Oracle). _ FREE pop-up blocking with the new MSN Toolbar – get it now! http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/ It depend on how you call the stored procedure if your client library can handle a resultset or not. If you treat it like a reqular query, most client libraries have little problem accepting the results of a SELECT statement called from within your SPROC. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Support for temporary tables inside stored procedures?
J A [EMAIL PROTECTED] wrote on 03/01/2006 11:08:10 AM: Does MySQL have support for temporary tables inside stored procedures? _ Express yourself instantly with MSN Messenger! Download today - it's FREE! http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/ Yes, you can create and destroy temporary tables from within stored procedures. Remember that all temporary tables and user variables are connection-specific. You have to maintain the connection between SPROC executions to have the results of one SPROC be available to the next. Drop a connection and your user variables and temporary tables created by that connection go away (garbage collected by the server). The obverse situation is also true. Using a pooled connection may result in object carry-over from one process to another (because returning the connection to the pool does may not actually drop the connection). You should always destroy any temporary object when you are through using it and always initialize your variables before usage. That way you don't accidentally hand old data off to following threads and you don't inherit previous threads state values (unless you really want them). Shawn Green Database Administrator Unimin Corporation - Spruce Pine
How to use paramerters in stored procedures
Hi, I am trying to do something like this in MySQL 5.0 create function myfunc(param_name varchar(100)) returns int begin .. if exists(select 1 from my_table where name = param_name) then return -1; end if; . end; The condition is never true. What am doing wrong? Thanks, Dimitar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DOT.NET Connector and Stored Procedures: Exception trying to retrieve parameter info
Jesse is right. You use the ? to start a parameter name.
-Original Message-
From: Rehcra [mailto:[EMAIL PROTECTED]
Sent: Friday, December 02, 2005 2:26 PM
To: Jesse Castleberry; mysql@lists.mysql.com
Subject: Re: DOT.NET Connector and Stored Procedures: Exception trying to
retrieve parameter info
This fixes my problem. Thanks!. Now to figure out where I got the
ampersand
from I thought it was in an example.
On 12/2/05, Jesse Castleberry [EMAIL PROTECTED] wrote:
Have you tried using ? instead of ? I'm very new to both MySQL and
Stored Procedures, but I've got seversl stored procedures working right
now,
and I had to put a question mark in front of the parameters before it
would
work:
myCmd.Parameters.Add(New
MySql.Data.MySqlClient.MySqlParameter(?tbl,
escdofficer))
myCmd.Parameters(?tbl).Direction = ParameterDirection.Input
Jesse
- Original Message -
From: Rehcra [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, December 02, 2005 2:28 PM
Subject: DOT.NET Connector and Stored Procedures: Exception trying to
retrieve parameter info
I'm having a problem getting parameters working.
Mysql 5.0.16
mysql-connector-net-1.0.7
VB.NET 1.1
mysql SHOW CREATE PROCEDURE tbl_slct \G
*** 1. row ***
Procedure: tbl_slct
sql_mode:
Create Procedure: CREATE PROCEDURE `tbl_slct`(IN tbl CHAR(64))
READS SQL DATA
BEGIN
SET @s = CONCAT('SELECT * FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END
1 row in set (0.00 sec)
Public Function Test() As DataTable
If myConn.State ConnectionState.Open Then
myConn.ConnectionString = myConnString
myConn.Open()
End If
Test = New DataTable
Dim myCmd As New MySql.Data.MySqlClient.MySqlCommand(tbl_slct, myConn)
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Clear()
myCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter(tbl,
escdofficer))
myCmd.Parameters(tbl).Direction = ParameterDirection.Input
Dim myAdapter As New MySqlDataAdapter(myCmd)
myAdapter.Fill(Test) '*** raises MySqlException
End Function
Exception trying to retrieve parameter info for tbl_slct: Parameter
'tbl'
is not defined
What am I missing?
Thanks,
Steve
--
Rehcra
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DOT.NET Connector and Stored Procedures: Exception trying to retrieve parameter info
I'm having a problem getting parameters working.
Mysql 5.0.16
mysql-connector-net-1.0.7
VB.NET 1.1
mysql SHOW CREATE PROCEDURE tbl_slct \G
*** 1. row ***
Procedure: tbl_slct
sql_mode:
Create Procedure: CREATE PROCEDURE `tbl_slct`(IN tbl CHAR(64))
READS SQL DATA
BEGIN
SET @s = CONCAT('SELECT * FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END
1 row in set (0.00 sec)
Public Function Test() As DataTable
If myConn.State ConnectionState.Open Then
myConn.ConnectionString = myConnString
myConn.Open()
End If
Test = New DataTable
Dim myCmd As New MySql.Data.MySqlClient.MySqlCommand(tbl_slct, myConn)
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Clear()
myCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter(tbl,
escdofficer))
myCmd.Parameters(tbl).Direction = ParameterDirection.Input
Dim myAdapter As New MySqlDataAdapter(myCmd)
myAdapter.Fill(Test) '*** raises MySqlException
End Function
Exception trying to retrieve parameter info for tbl_slct: Parameter 'tbl'
is not defined
What am I missing?
Thanks,
Steve
Re: DOT.NET Connector and Stored Procedures: Exception trying to retrieve parameter info
This fixes my problem. Thanks!. Now to figure out where I got the ampersand
from I thought it was in an example.
On 12/2/05, Jesse Castleberry [EMAIL PROTECTED] wrote:
Have you tried using ? instead of ? I'm very new to both MySQL and
Stored Procedures, but I've got seversl stored procedures working right
now,
and I had to put a question mark in front of the parameters before it
would
work:
myCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter(?tbl,
escdofficer))
myCmd.Parameters(?tbl).Direction = ParameterDirection.Input
Jesse
- Original Message -
From: Rehcra [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Friday, December 02, 2005 2:28 PM
Subject: DOT.NET Connector and Stored Procedures: Exception trying to
retrieve parameter info
I'm having a problem getting parameters working.
Mysql 5.0.16
mysql-connector-net-1.0.7
VB.NET 1.1
mysql SHOW CREATE PROCEDURE tbl_slct \G
*** 1. row ***
Procedure: tbl_slct
sql_mode:
Create Procedure: CREATE PROCEDURE `tbl_slct`(IN tbl CHAR(64))
READS SQL DATA
BEGIN
SET @s = CONCAT('SELECT * FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END
1 row in set (0.00 sec)
Public Function Test() As DataTable
If myConn.State ConnectionState.Open Then
myConn.ConnectionString = myConnString
myConn.Open()
End If
Test = New DataTable
Dim myCmd As New MySql.Data.MySqlClient.MySqlCommand(tbl_slct, myConn)
myCmd.CommandType = CommandType.StoredProcedure
myCmd.Parameters.Clear()
myCmd.Parameters.Add(New MySql.Data.MySqlClient.MySqlParameter(tbl,
escdofficer))
myCmd.Parameters(tbl).Direction = ParameterDirection.Input
Dim myAdapter As New MySqlDataAdapter(myCmd)
myAdapter.Fill(Test) '*** raises MySqlException
End Function
Exception trying to retrieve parameter info for tbl_slct: Parameter 'tbl'
is not defined
What am I missing?
Thanks,
Steve
--
Rehcra
Re: Stored procedures using a variable tablename
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]
Stored procedures using a variable tablename
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]
Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table
name or alias?
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After doing
some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on a
table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something else
going on?
mysql EXPLAIN
- SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
++-+++--+-+-+-+---+-+
| id | select_type | table | type | possible_keys | key |
key_len | ref | rows | Extra |
++-+++--+-+-+-+---+-+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL |
NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY |
6 | t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY |
6 | NULL | 23508 | Using index |
++-+++--+-+-+-+---+-+
3 rows in set (0.01 sec)
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9
Re: Sub Selects, Alias Names and stored procedures
I don't have any idea about your second question but I have a thought on the
first one.
In DB2, which I use most of the time, you don't need to qualify a column
name like list_ID or acct_ID unless it is ambiguous. In this case, neither
one is ambiguous because both get used in single-table SELECT statements. If
I did those same queries in DB2, I would only expect an error message if I
did a join of two tables in which both tables had an acct_ID or list_ID;
then the SQL processor would get confused if the duplicated column names
weren't qualified in a SELECT list, GROUP BY, WHERE, or whatever.
Since DB2 and MySQL are presumably written to the same SQL standard, they
probably have the same rules; you only have to qualify a column name when it
is ambiguous.
That's my guess and I'm sticking to it until I hear otherwise from someone
more familiar with MySQL's implementation of SQL :-)
Rhino
- Original Message -
From: Gordon Bruce [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Friday, November 04, 2005 2:51 PM
Subject: Sub Selects, Alias Names and stored procedures
After reading one of the recent posts from Gobi [EMAIL PROTECTED]
I took his successful query and modified it for one of my tables. It indeed
produce the correct result, but in the process raised some questions.
1. Why do list_ID and acct_ID not have to be qualified with a table name or
alias?
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec)
2. While the subselect does work, it appears to generate a cartesian
product. Initial guess with 5.0 and stored procedures would be that
CREATING TEMPORARY TABLE
INSERT max values in temporary
SELECT from main table joined with temporary
would run faster and still allow this to be done with 1 statement.
However, even though the explains would indicate that this was so {23508 *
7354 rows for subselect VS 6060 rows for temporary table}
actual times are {0.03 for subselect VS 0.19 for temporary table} . After
doing some playing, it is the INSERT into temporary that adds the
time even though the table was memory resident. Trying a similar request on
a table with 3.5M rows still favors the subselect
{27.50 sec for subselect VS 1 min 13.91 sec for temporary table}.
Has EXPLAIN just not caught up with SUBSELECT logic or is there something
else going on?
mysql EXPLAIN
- SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
++-+++--+-+-
+-+---+-+
| id | select_type | table | type | possible_keys | key | key_len | ref |
rows | Extra |
++-+++--+-+-
+-+---+-+
| 1 | PRIMARY | derived2 | ALL | NULL | NULL | NULL | NULL | 7354 | |
| 1 | PRIMARY | lists | eq_ref | PRIMARY,acct_list_ID | PRIMARY | 6 |
t.acct_ID,t.list_ID | 1 | Using where |
| 2 | DERIVED | lists | index | NULL | PRIMARY | 6 | NULL | 23508 | Using
index |
++-+++--+-+-
+-+---+-+
3 rows in set (0.01 sec)
mysql SELECT list_ID, list_Name, acct_ID, list_Qty
- FROM lists
- INNER JOIN (SELECT acct_ID, max(list_ID) AS list_ID
- FROM lists
- GROUP BY acct_id
- ) AS t
- USING (acct_ID, list_ID)
- WHERE list_Active = 'Yes'
- AND cpny_ID = 'RER1'
- LIMIT 100,10;
+-++-+--+
| list_ID | list_Name | acct_ID | list_Qty |
+-++-+--+
| 3 | Farm | BA8M | 0 |
| 10 | Woodbury | BA8Y | 100 |
| 2 | Brookview Heights 03-23-04 | BA9O | 278 |
| 4 | Magnet Mailing | BABA | 250 |
| 2 | Fall Back | BABM | 223 |
| 1 | Contact list | BACF | 71 |
| 4 | Friends/Family | BAE2 | 10 |
| 1 | St. Michael | BAE7 | 139 |
| 2 | JS Prospects | BAE8 | 196 |
| 1 | Home Focus | BAE9 | 55 |
+-++-+--+
10 rows in set (0.03 sec
Re: A question about stored procedures
Hello. Very often PREPARED STATEMENTS are helpful in similar situations. For a pity, they are disabled in the current release. See: http://dev.mysql.com/doc/mysql/en/sqlps.html Leonardo Javier Belén wrote: Hi all, I am wondering if I can construct a query string inside a stored procedure and then execute it. I mean, what I want to do is given a set of IN parameters, be able to construct a huge where statement out of them an then complete the query string to execute. Is that possible? because if that is possible I could be able to abstract all the data logic from the program itself. I've been googling and reading books and references without success so far, but I think the functionality should be there, what I dont know is how to use it. Thanks in advance. sql - query - mysql -- 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]
A question about stored procedures
Hi all, I am wondering if I can construct a query string inside a stored procedure and then execute it. I mean, what I want to do is given a set of IN parameters, be able to construct a huge where statement out of them an then complete the query string to execute. Is that possible? because if that is possible I could be able to abstract all the data logic from the program itself. I've been googling and reading books and references without success so far, but I think the functionality should be there, what I dont know is how to use it. Thanks in advance. sql - query - mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: A question about stored procedures
Yes you can do this using prepared statements inside stored proc. Guess this helps. http://forums.mysql.com/read.php?98,22770,31459 sujay -Original Message- From: Leonardo Javier Belén [mailto:[EMAIL PROTECTED] Sent: Thursday, October 06, 2005 9:47 PM To: Ryan Stille; mysql@lists.mysql.com Subject: A question about stored procedures Hi all, I am wondering if I can construct a query string inside a stored procedure and then execute it. I mean, what I want to do is given a set of IN parameters, be able to construct a huge where statement out of them an then complete the query string to execute. Is that possible? because if that is possible I could be able to abstract all the data logic from the program itself. I've been googling and reading books and references without success so far, but I think the functionality should be there, what I dont know is how to use it. Thanks in advance. sql - query - mysql -- 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 Functions
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]
Stored Procedures and Functions
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. ---
Re: Stored procedures MS SQL Server to MySQL
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
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]
