I have a table that has training records. Each record has a field with an Agency value. I have another table of just agency values. I want to export the records into CSV files for each agency. There are over 2 million records so I don't want to export the whole table and do it manually.
I've created a stored procedure that pulls a value from the agency_codes table using a cursor and uses that value in a select statement for the WHERE clause and part of the INTO OUTFILE name.
The procedure works but only for the first two values in the agency_codes table. On the third (ACB) value it gives the error Unknown column 'ACB' in 'where clause' I'm confused why it worked with the first two values then stops with the third.
Here is the procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS export_csv $$
CREATE PROCEDURE export_csv()
BEGIN
DECLARE agency_name VARCHAR(255);
DECLARE exit_loop BOOLEAN;
DECLARE agency_cursor CURSOR FOR
SELECT agency FROM agency_codes;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_loop = TRUE;
OPEN agency_cursor;
agency_loop: LOOP
FETCH agency_cursor INTO agency_name;
SET @sql_text = Concat("(select 'class_code','course_code','course_name','course_type','username','grade_type','score','letter_grade','is_passed','completion_date','completion_status','registration_date','registration_entry_status','registration_type','comment','first_name','last_name','class_name','agency') Union (select class_code,course_code,course_name,course_type,username,grade_type,score,letter_grade,is_passed,completion_date,completion_status,registration_date,registration_entry_status,registration_type,comment,first_name,last_name,class_name,agency from hrdis_oru where hrdis_oru.agency =", agency_name," into outfile 'C:/HDD/",agency_name,".csv' fields enclosed by '\"' terminated by ',' escaped by '\"' lines terminated by '\r\n')");
prepare s1 from @sql_text;
execute s1;
deallocate prepare s1;
IF exit_loop THEN
CLOSE agency_cursor;
LEAVE agency_loop;
END IF;
END LOOP agency_loop;
END $$
DELIMITER ;
The first few values in my Agency table are:
Any help would be great. Thanks.
If you think about what values @sql_text will hold on each iteration, the problem should be immediately obvious. Adding some whitespace for clarity:
(
select 'class_code','course_code','course_name','course_type','username',
'grade_type','score','letter_grade','is_passed','completion_date',
'completion_status','registration_date','registration_entry_status',
'registration_type','comment','first_name','last_name','class_name',
'agency'
) Union (
select class_code,course_code,course_name,course_type,username,
grade_type,score,letter_grade,is_passed,completion_date,
completion_status,registration_date,registration_entry_status,
registration_type,comment,first_name,last_name,class_name,
agency
from hrdis_oru
where hrdis_oru.agency =ACB
into outfile 'C:/HDD/ACB.csv'
fields enclosed by '\"'
terminated by ','
escaped by '\"'
lines terminated by '\r\n'
)
Note in particular, where htdis_oru.agency =ACB.
Since ACB has not been quoted, MySQL parses it as a schema object identifier and complains when it cannot find any such-named object (this isn't the case with the purely numeric agency names, since they are parsed as integers that subsequently get cast to strings during expression evaluation).
For MySQL to correctly parse non-numeric values as string literals, they must be quoted:
... where hrdis_oru.agency ='", agency_name, "' ...
^ ^
Naturally, this presents a problem should agency_name contain the ' string quotation character—any such occurrences must of course be escaped. MySQL handily provides a QUOTE() function for precisely this purpose:
... where hrdis_oru.agency =", QUOTE(agency_name), " ...
However, to guard against possible SQL injection attacks, you really should instead parameterise your prepared statement:
FETCH agency_cursor INTO @agency_name;
(You no longer need to DECLARE agency_name); then:
... where hrdis_oru.agency = ? into outfile CONCAT('C:/HDD/', ?, '.csv') ...
Followed by:
PREPARE s1 FROM @sql_text;
EXECUTE s1 USING @agency_name, @agency_name;
DEALLOCATE PREPARE s1;
Note that you can now also PREPARE the statement before entering the loop and simply EXECUTE it (with appropriate values) inside the loop—this should yield a slight performance improvement. Remember to DEALLOCATE after exiting the loop.
One final note: you should inspect exit_loop immediately after the FETCH command—otherwise you will end up attempting to execute the SELECT ... INTO OUTFILE statement a final time when there are no more agencies.
It may also be worth noting that, in this case, you have no particular need to use prepared statements at all. You could simply do the following:
CREATE PROCEDURE export_csv() BEGIN
DECLARE agency_name VARCHAR(255);
DECLARE agency_cursor CURSOR FOR SELECT agency FROM agency_codes;
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE agency_cursor;
OPEN agency_cursor;
LOOP
FETCH agency_cursor INTO agency_name;
SELECT 'class_code','course_code','course_name','course_type','username',
'grade_type','score','letter_grade','is_passed','completion_date',
'completion_status','registration_date','registration_entry_status',
'registration_type','comment','first_name','last_name','class_name'
UNION ALL
SELECT class_code,course_code,course_name,course_type,username,
grade_type,score,letter_grade,is_passed,completion_date,
completion_status,registration_date,registration_entry_status,
registration_type,comment,first_name,last_name,class_name
FROM hrdis_oru
WHERE agency = agency_name
INTO OUTFILE CONCAT('C:/HDD/', agency_name, '.csv')
FIELDS ENCLOSED BY '"'
TERMINATED BY ','
ESCAPED BY '"'
LINES TERMINATED BY '\r\n';
END LOOP;
END