RE: Function returns null when running sql manually works
I have the solution, had to add a begin/end inside the repeat to protect
my original done var and declare another inside the loop.
I'm glad we had this little talk =P
l8r
DELIMITER $$
DROP FUNCTION IF EXISTS `firescope`.`reportWhereClause`$$
CREATE [EMAIL PROTECTED] FUNCTION `reportWhereClause`(RepID
BIGINT(20) UNSIGNED) RETURNS varchar(255) CHARSET latin1
BEGIN
DECLARE doneINT DEFAULT 0;
DECLARE filterSQL, filterTMP, colName VARCHAR (255);
DECLARE colID, rID BIGINT (20) UNSIGNED;
DECLARE cur1 CURSOR FOR SELECT
report_column_id, column_name
FROM
report_columns
WHERE
report_id = RepID
ORDER BY
report_column_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
BEGIN
DECLARE CONTINUE HANDLER FOR NOT FOUND BEGIN END;
SELECT CONCAT(' ', rvf.filter_operator, ' ', colName, ' ', (CASE
rvf.filter_condition WHEN 'not_equal' THEN '<>' WHEN 'greater_than' THEN
'>' ELSE '=' END)
, ' ', , rvf.filter_value,, ' ')
INTO
filterTMP
FROM
report_filters rvf
WHERE
rvf.report_id = RepID
AND rvf.report_column_id = colID;
IF filterTMP IS NOT NULL THEN
IF filterSQL IS NULL THEN
SELECT
filterTMP
INTO
filterSQL;
ELSE
SELECT
CONCAT(filterSQL, filterTMP)
INTO
filterSQL;
END IF;
END IF;
SET filterTMP = NULL;
END;
END IF;
UNTIL done
END REPEAT;
CLOSE cur1;
RETURN filterSQL;
END$$
DELIMITER ;
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 5:13 PM
To: mysql@lists.mysql.com
Subject: RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null
RE: Function returns null when running sql manually works
OK, I know WHY it is returning null, just not WHAT to do about it.
In the inside sql, there is not always a result. So, done becomes 1 and
the repeat exits.
How can I keep from this happening? How could I make another 'done' like
variable that would not get also set to 1 if the inner sql doesn't
return a record in this pass?
thx
-Original Message-
From: Cantwell, Bryan [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2008 4:17 PM
To: mysql@lists.mysql.com
Subject: Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Function returns null when running sql manually works
Below I have a function with a cursor. I have tested the cursor sql
manually and it is fine, I have put the variables into the sql inside
the cursor loop and it returns data too, BUT, executing this function
returns null even though I know the correct info is available. Am I
missing something obvious here?
CREATE TABLE `report_columns` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`column_index` smallint(6) NOT NULL,
`column_name` varchar(128) NOT NULL,
`column_alias` varchar(128) NOT NULL,
`visible` char(1) NOT NULL,
`relationship_type_id` bigint(20) NOT NULL,
`relationship_ci` bigint(20) NOT NULL,
`relationship_index` bigint(20) NOT NULL,
`graph_value` tinyint(4) NOT NULL,
`graph_label` tinyint(4) NOT NULL,
PRIMARY KEY (`report_id`,`report_column_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
CREATE TABLE `report_filters` (
`report_id` bigint(20) NOT NULL,
`report_column_id` bigint(20) NOT NULL,
`report_filter_id` bigint(20) NOT NULL default '0',
`filter_condition` varchar(128) default NULL,
`filter_value` varchar(128) default NULL,
`filter_special` varchar(128) default NULL,
`filter_operator` varchar(10) default NULL,
PRIMARY KEY (`report_id`,`report_column_id`,`report_filter_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
DROP FUNCTION IF EXISTS firescope.testing;
CREATE FUNCTION firescope.`testing`(RepID BIGINT(20) unsigned) RETURNS
varchar(255)
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE filterSQL,filterTMP, colName varchar(255);
DECLARE colID, rID BIGINT(20) unsigned;
DECLARE cur1 CURSOR FOR SELECT report_column_id, column_name
FROM report_columns
WHERE report_id = RepID ORDER BY report_column_id;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1;
OPEN cur1;
REPEAT
FETCH cur1 INTO colID, colName;
IF NOT done THEN
SELECT concat(' ' ,rvf.filter_operator,'
', colName,' ', (case rvf.filter_condition when 'not_equal' then '<>'
when 'greater_than' then '>' else '=' end)
,' ' ,rvf.filter_value,'
') into filterTMP
FROM report_filters rvf
WHERE rvf.report_id = RepID
and rvf.report_column_id
= colID;
IF filterTMP is NOT null then
IF filterSQL is null
then
select
filterTMP into filterSQL;
ELSE
select
concat(filterSQL,filterTMP) into filterSQL;
END IF;
END IF;
SET filterTMP = null;
END IF;
UNTIL done END REPEAT;
CLOSE cur1;
return filterSQL;
END;
RE: is INSERT into VIEW supported
generally no as join conditions do not guarantee 1)the column to be inserted is unique 2)the column is updatable //Create a View based on 2 tables joined on location+id CREATE VIEW locations_view AS SELECT d.department_id, d.department_name, l.location_id, l.city FROM departments d, locations l WHERE d.location_id = l.location_id; //find out which columns are updateable from the view SELECT column_name, updatable FROM user_updatable_columns WHERE table_name = 'LOCATIONS_VIEW'; //The location_id is not updateable from this view (inserts will fail) COLUMN_NAMEUPD -- --- DEPARTMENT_ID YES DEPARTMENT_NAMEYES LOCATION_IDNO CITY NO //Attempts at inserting/updating location_id will error out In the preceding example, the primary key index on the location_id column of the locations table is not unique in the locations_view view. Therefore, locations is not a key-preserved table and columns from that base table are not updatable. INSERT INTO locations_view VALUES (999, 'Entertainment', 87, 'Roma'); INSERT INTO locations_view VALUES * ERROR at line 1: ORA-01776: cannot modify more than one base table through a join view //department_id and department_name are updateable so DML operations will work You can insert, update, or delete a row from the departments base table, because all the columns in the view mapping to the departments table are marked as updatable and because the primary key of departments is retained in the view. INSERT INTO locations_view (department_id, department_name) VALUES (999, 'Entertainment'); 1 row created. This information is available via Oracle 10G documentation from Stanford University http://stanford.edu/dept/itss/docs/oracle/10g/server.101 Dziękuję Martin __ Disclaimer and confidentiality note Everything in this e-mail and any attachments relates to the official business of Sender. This transmission is of a confidential nature and Sender does not endorse distribution to any party other than intended recipient. Sender does not necessarily endorse content contained within this transmission. > Date: Thu, 13 Nov 2008 20:17:09 -0100 > Subject: is INSERT into VIEW supported > From: [EMAIL PROTECTED] > To: mysql@lists.mysql.com > > I have a VIEW that is defined over two base tables. One table is subtype > of another table and it's the VIEW that connects them. Now when I want to > insert into a subtable I have to insert through the VIEW. However I am > getting an error message when I try to insert into a VIEW. I found the > solution to this problem using the TRIGGER with "INSTEAD OF" but that was > from one of the Oracle discussions, and that works only with Oracle. > As far as I know MySQL does not support "INSTEAD OF", and my question is > does MySQL support INSERTS into VIEW? Or is there some way I can insert > into a view? > > Thanks > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] > _ Stay up to date on your PC, the Web, and your mobile phone with Windows Live http://clk.atdmt.com/MRT/go/119462413/direct/01/
is INSERT into VIEW supported
I have a VIEW that is defined over two base tables. One table is subtype of another table and it's the VIEW that connects them. Now when I want to insert into a subtable I have to insert through the VIEW. However I am getting an error message when I try to insert into a VIEW. I found the solution to this problem using the TRIGGER with "INSTEAD OF" but that was from one of the Oracle discussions, and that works only with Oracle. As far as I know MySQL does not support "INSTEAD OF", and my question is does MySQL support INSERTS into VIEW? Or is there some way I can insert into a view? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing MySQL Twice
We have a web server that am using solely as a mysql replication server right now, so it's not really being used that much. I'd like to use it as a backup web server as well, but I don't want to mess with the replication that I've got going on. I believe I've heard that you can install MySQL twice on a server, right? How does one go about doing that? This is a Windows 2003 server. I realize that as an alternative, I can install a Virtual Machine and some flavor of Linux (Ubuntu would be my choice, most likely), and that would probably solve the problem, but would it be easier to just install MySQL twice on this machine? Obviously, when I install, I'd want to install to a different directory, put the data in a different directory, and use a different port. How would Windows handle having the MySQL service running twice, would that be a problem? Are there any "got 'cha's" in this situation? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Error: "Got error 139 from storage engine"
I was running the 'perror' command from the bash command line of a Linux system. That explains it. This is a Windows server, not Linux. A column of type text will allow a maximum of 65'535 characters being stored, but this could be less when a multibyte character set is used. Not using a multi-character character set. This is regular old english (latin, I think it's called?) What storage engine type are you using? I found some issues with InnoDB regarding this error. Please check the .err log of mysqld as well. InnoDB is the engine I'm using, and searcing the net, I found that there was a problem with InnoDB and this error, but nothing about how to prevent it. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
recurring corrupt table?
Application in php 5.1.6.I'm running MySQL version 5.0.45 on CentOS5 using a HP DL380 with 8G ram and 15krpm raid10 disks. Tables are myisam. I'm seeing errors in my application where I'm getting from simple selects every few hours: SELECT id, host_id, uri_id, profile FROM activity_profiles WHERE 1=1 AND id IN (48823962 ) Table 'activity_profiles' is marked as crashed and should be repaired. My application isn't reporting errors when writing, though. I do a "flush table activity_profiles; check table activity_profiles;" and the table seems to have fixed itself. Any thots? TIA, Jed -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: com_* status variables seem to reset in mysql 5
Try show global status like 'com_select'; Donna "Jim Lyons" <[EMAIL PROTECTED] .com> To "MySQL List" 11/12/2008 05:24 PM cc Subject com_* status variables seem to reset in mysql 5 I have been trying to compute query cache utilization in mysql 5 but cannot because the com_select status variable is always 1 when I start a new mysql session. This probably holds for all the com_* variables and maybe others, but I've only been working with com_select. They're supposed to be cumulative and reset only when you explicitly reset status or bounce the server. Here's an example, showing the tail end of a test mysql session showing the value of com_select when I exited, and the value a few seconds later when I began a new mysql session. This was on my own test server, no one else was on to reset status. It repeats every time I try it: ### BEGIN SESSION mysql> show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 4 | +---+---+ 1 row in set (0.01 sec) mysql> quit Bye > mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 94774 Server version: 5.0.45-community-log MySQL Community Edition (GPL) Reading history-file /home/jlyons/.mysql_history Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> show status like 'com_select'; -- show status like 'com_select' -- +---+---+ | Variable_name | Value | +---+---+ | Com_select| 1 | +---+---+ 1 row in set (0.01 sec) mysql> ### END SESSION Note how com_select is 4 in the first session, then is reset to 1. I tried this on Linux RHEL, Linux RH 5 community (as shown here) and a Windows mysql 5 platform. Heres' the script I ran: drop table if exists t; create table t (x serial); # put some data in insert into t values (null); insert into t values (null); insert into t values (null); insert into t values (null); # create some selects select * from t where x = 1; select * from t where x = 2; select * from t where x = 3; select * from t where x = 4; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; select * from t where x = 1; show status like 'qcache_hits'; show status like 'com_select'; When I ran the same script on a Windows mysql 4 version, the value of com_select persisted over the login, which is what it should. Is this a bug in mysql 5? Is something set incorrectly in my config file that would cause this (I can't find anything)? Thanks for any help. -- Jim Lyons Web developer / Database administrator http://www.weblyons.com -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. CONFIDENTIALITY NOTICE:This email is intended solely for the person or entity to which it is addressed and may contain confidential and/or protected health information. Any duplication, dissemination, action taken in reliance upon, or other use of this information by persons or entities other than the intended recipient is prohibited and may violate applicable laws. If this email has been received in error, please notify the sender and delete the information from your system. The views expressed in this email are those of the sender and may not necessarily represent the views of IntelliCare. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
