|
More site info...
Stored Procedures | Forum profile
|
|
Forum profile page for Stored Procedures on http://www.mysql.com.
This report page is the aggregated overview from a single forum: Stored Procedures, located on the Message Board at http://www.mysql.com.
This forum profile page summarizes the general forum statistics such as: Users Activity, Forum Activity, and Top Authors, which are reported in either a table or graph below for a given reporting time period.
Additional forum profile information for "Stored Procedures" on the Message Board at http://www.mysql.com is also shown in the following ways:
1) Latest Active Threads
2) Hot Threads for Last Week
Warning: These statistics are generated using 'best efforts' and can experience delays and reporting errors at times. Please note that such statistics do not constitute a forum's popularity and/or exact posting volumes at any given reporting period.
|
|
|
|
|
Posting activity on Stored Procedures:
|
|
Week
|
Month
|
3 Months
|
|
Threads:
|
26
|
69
|
217
|
|
Post:
|
71
|
154
|
413
|
|
|
Stored Procedures Posting activity graph:
|
Top authors during last week:
user's latest post:
Re: Pivot
Published (2009-11-06 18:01:00)
Ok ashr, Here it is... But first, let me again say that you should change your table design. This is horrible. It works, but for some reason when I run it more than once with different date ranges, it doesn't work right. If I open a new tab in Query Browser and run it again, it works fine. It may be some kind of caching problem in QB. Or the temporary table isn't being dropped. I have a drop statement in there, but I don't know....
user's latest post:
Re: Pivot
Published (2009-11-06 15:18:00)
USE `TEST2`; DROP TABLE IF EXISTS teams, bats,cats, rats, dogs, output; CREATE TABLE teams(id INT ,tname CHAR(32)); CREATE TABLE bats(DATE DATETIME, temp INT); CREATE TABLE cats(DATE DATETIME, temp INT); CREATE TABLE rats(DATE DATETIME, temp INT); CREATE TABLE dogs(DATE DATETIME, temp INT); INSERT INTO teams VALUES(1,'bats'),(2,'cats'),(3,'rats'),(4,'dogs'); INSERT INTO bats VALUES...
user's latest post:
Re: parsing parameter list to...
Published (2009-11-09 12:12:00)
Tupa, A simple way to do that is with PREPARE: [code] DROP PROCEDURE IF EXISTS spMySampleStoredProc; DELIMITER go CREATE PROCEDURE spMySampleStoredProc( list varchar(1000) ) BEGIN SET @sql=concat( 'select * from user_list where country in(', list, ')' ); PREPARE stmt FROM @sql; EXECUTE @sql; DROP PREPARE stmt; END; go DELIMITER ; PB http://www.artfulsoftware.com
user's latest post:
How to declare an array in a...
Published (2009-11-05 12:22:00)
Anyonw know how to declare an array in a stored procedure? I need to declare a TEXT or VARCHAR(255) array. Thanks in advance, James
user's latest post:
Re: Call to a stored procedure...
Published (2009-11-04 13:26:00)
Fixed by cpan install Bundle::DBD::mysql which upgraded to DBD-mysql-4.013 "PROCEDURE X can't return a result set in the given context" is a poor error message.
user's latest post:
Create cursor from string
Published (2009-11-06 08:01:00)
In PostgreSQL it is possible: OPEN curs1 FOR EXECUTE 'SELECT * FROM ' || quote_ident($1); http://www.postgresql.org/docs/8.2/static/plpgsql-cursors.html Is there possible this feture in MySQL?
user's latest post:
Re: Bind variables in stored...
Published (2009-11-06 12:15:00)
Hi Chad, Thanks for your response. I'm sorry if my question was a bit unclear, but I asked if (and how) I can use bind variables inside a stored procedure. From PHP I call for the routine with a few parameters: $query = "call p_counter($ip, $browser_name, $version_number, $ie_version, $dom_browser, $safe_browser, $os, $os_number, $s_browser, $type, $delay, $ip_exclude);"; Here is my routine: CREATE DEFINER= PROCEDURE...
|
|
|
|
Latest active threads on Stored Procedures::
Started 3 days, 1 hour ago (2009-11-09 05:57:00)
by Devart Team
To support lists, you could use a temporary table instead of a procedure parameter:
CREATE TEMPORARY TABLE list_table(
country VARCHAR(255)
);
INSERT INTO test.ttt VALUES ('MY'),('ID'),('TW');
Then, just rewrite your SELECT:
select * from user_list where country IN (SELECT country FROM list_table);
Devart Company,
Database development tools
http://www.devart.com...
Started 1 week, 2 days ago (2009-11-02 12:52:00)
by Chad Bourque
ashr,
This link has a section on Pivot tables:
http://www.artfulsoftware.com/infotree/queries.php ?&bw=1108
HTH,
Chad
Started 5 days, 22 hours ago (2009-11-06 09:03:00)
by Chad Bourque
Rene,
I believe you should be able to, but I don't use php so I'm not sure. All it's doing is replacing the question marks with the values passed before passing it to MySQL, I would think. Why not just try it and see?
HTH,
Chad
Started 5 days, 18 hours ago (2009-11-06 13:37:00)
by Peter Brawley
> if we can pass in an array or table as parameter to a stored procedure.
No arrays in MySQL, nor is there a table datatype. Pass the name of a table for the sproc to query.
Started 6 days, 16 hours ago (2009-11-05 15:30:00)
by Peter Brawley
>Is something like this possible?
>select sName from tblcategories where iCategoryID in (call spCategoryListChildIDs(52));
Alas not in MySQL. Have the sproc save its results to a temp table, call the sproc, and join to that table.
PB
http://www.artfulsoftware.com
Started 5 days, 20 hours ago (2009-11-06 11:02:00)
by ashr ben
try this for starter: read whole post.
http://forums.mysql.com/read.php?98,289131,289324# msg-289324
Started 6 days, 23 hours ago (2009-11-05 08:49:00)
by Chad Bourque
Androrion,
In the current Query Browser, You can only put one command per line in a query tab. You can put it this way:
CALL myProc('arg1','arg2');
CALL myProc('arg1','arg2bis');
Then, you can put your cursor on either line and press Ctrl+Enter to execute it.
HTH,
Chad
Started 6 days, 22 hours ago (2009-11-05 09:21:00)
by Chad Bourque
Manhao,
Perhaps just adding this having clause to the end of your query will get you what you want:
HAVING tbl_permissions_lookup.FK_sites IS NULL;
HTH,
Chad
|
|
Hot threads for last week on Stored Procedures::
Started 1 week, 2 days ago (2009-11-02 12:52:00)
by Chad Bourque
ashr,
This link has a section on Pivot tables:
http://www.artfulsoftware.com/infotree/queries.php ?&bw=1108
HTH,
Chad
Started 6 days, 20 hours ago (2009-11-05 11:04:00)
by Chad Bourque
James,
Look right before where the error says it is near. In your case, you have this:
SET s1 = 'SELECT *';
All of your declare statements must come before any other statements. Move all of your declares to the top, then do your assignments.
HTH,
Chad
Started 5 days, 22 hours ago (2009-11-06 09:03:00)
by Chad Bourque
Rene,
I believe you should be able to, but I don't use php so I'm not sure. All it's doing is replacing the question marks with the values passed before passing it to MySQL, I would think. Why not just try it and see?
HTH,
Chad
Started 3 days, 1 hour ago (2009-11-09 05:57:00)
by Devart Team
To support lists, you could use a temporary table instead of a procedure parameter:
CREATE TEMPORARY TABLE list_table(
country VARCHAR(255)
);
INSERT INTO test.ttt VALUES ('MY'),('ID'),('TW');
Then, just rewrite your SELECT:
select * from user_list where country IN (SELECT country FROM list_table);
Devart Company,
Database development tools
http://www.devart.com...
Started 1 week, 1 day ago (2009-11-03 16:45:00)
by Chad Bourque
Arthur,
While this may not be your problem, are you aware you are passing a string to the procedure that is expecting an integer?
CALL ARTH_DB.getObjectsBFS( '3606658', 'Group2Group' )
Should likely be this instead:
CALL ARTH_DB.getObjectsBFS( 3606658, 'Group2Group' )
But, like I say, that may not be your problem as I expect MySQL probably auto-converts it for you. But, there's no ...
Started 6 days, 16 hours ago (2009-11-05 15:30:00)
by Peter Brawley
>Is something like this possible?
>select sName from tblcategories where iCategoryID in (call spCategoryListChildIDs(52));
Alas not in MySQL. Have the sproc save its results to a temp table, call the sproc, and join to that table.
PB
http://www.artfulsoftware.com
Started 6 days, 23 hours ago (2009-11-05 08:49:00)
by Chad Bourque
Androrion,
In the current Query Browser, You can only put one command per line in a query tab. You can put it this way:
CALL myProc('arg1','arg2');
CALL myProc('arg1','arg2bis');
Then, you can put your cursor on either line and press Ctrl+Enter to execute it.
HTH,
Chad
Started 6 days, 22 hours ago (2009-11-05 09:21:00)
by Chad Bourque
Manhao,
Perhaps just adding this having clause to the end of your query will get you what you want:
HAVING tbl_permissions_lookup.FK_sites IS NULL;
HTH,
Chad
Started 6 days, 16 hours ago (2009-11-05 15:32:00)
by Peter Brawley
In SQL, "array" is spelled "table". Try ...
drop table if exists txt;
create table txt(txt varchar(255)) engine=heap;
PB
http://www.artfulsoftware.com
Started 6 days, 22 hours ago (2009-11-05 09:20:00)
by Chad Bourque
Bimsara,
First, the problems are with your Java code, not your MySQL code.
Second, you don't use curly braces for calling MySQL SPs.
Third, your SP doesn't return a value, but rather a result set.
Fourth, you should be using executeQuery, not execute.
Fifth, you have to iterate through the result set.
Your code should be more like this, I think (rough code, untested):
String ...
|
|
Size: 553 bytes
