link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

Creating Text Files Using MySQL Stored Procedures (SP)

I am new to MySQL stored procedures and need to know the idea and guidance how to write a file via MySQL stored procedures. Basically, the SP will populate the entire rows from table A and output to a file per row for selected columns (ie, transaction_param and transaction_results) .
I am hoping anyone can provide the command to create and write to file to a specific file path other than DB server installation folder and sharing the information on file/folder access permission on Linux.

Umesh

Here is the sample stored procedure which creates text files in /tmp directory (I assume you are trying it on Linux.. or else you can change the path if you are on windows box)
Change the table name from below sp.. I'm creating the text file name unique .. so you may want to change that also if needed

DELIMITER $$
DROP PROCEDURE IF EXISTS `uCreateFile`$$
CREATE PROCEDURE `uCreateFile`()
BEGIN
          SET @tmp_sql= CONCAT("SELECT * INTO OUTFILE '/tmp/",UNIX_TIMESTAMP(NOW()),".txt' FROM tablename");
          PREPARE s1 FROM @tmp_sql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1;
END$$
DELIMITER ;

Open in new window

Umesh

This is a link for CREATE stored procedure syntax
httpp://dev.mysql.com/doc/ refman/5.0 /en/create -procedure .html
Better I suggest you to download MySQL Manual and go thru in details.

wintersun

ASKER

The sample store procedure will output all records in table to one file. What if I need to output 1 file per record with the filename based on the db field value of seq_id ? Is this possible ?
i.e. 100 records = 100 files and each filename will be named based on the record.seq_id such as 1001.txt, 1002.txt ...etc
Also, I will need to consider the error and performance issues due to currently the table contains about 570000 ++ records. What do you think ?

Umesh

>>The sample store procedure will output all records in table to one file. What if I need to output 1 file per record with the filename based on the db field value of seq_id ? Is this possible ?
i.e. 100 records = 100 files and each filename will be named based on the record.seq_id such as 1001.txt, 1002.txt ...etc
Yes possible.. use cursor to navigate each and every record... let me know if you need any help in this regard
>>>>>Also, I will need to consider the error and performance issues due to currently the table contains about 570000 ++ records. What do you think ?
I don't suggest you to create "570000" files..definitely you would see performance heat.

Umesh

Here you go... below stored procedure would do required thing ( file name - based on unique id, file contents - rest of the columns of that row )
Update the select queries and you are done..

DELIMITER $$
 
DROP PROCEDURE IF EXISTS `uCreateFile`$$
 
CREATE PROCEDURE `uCreateFile`()
    READS SQL DATA
BEGIN
 
  DECLARE l_id            int;
  DECLARE l_column1         varchar(100);
 
  DECLARE l_done          INT DEFAULT  0;
 
  DECLARE cursor1 cursor  FOR
    SELECT id,column1
      FROM tableName;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
	OPEN cursor1;
	cursor_loop:LOOP
 
	    FETCH cursor1 INTO l_id,l_column1;
	    IF l_done=1 THEN
		 LEAVE cursor_loop;
	    END IF;
 
 
          SET @tmp_sql= CONCAT("SELECT l_column1 INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
          PREPARE s1 FROM @tmp_sql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1
 
 	END LOOP cursor_loop;
	CLOSE cursor1;
	SET l_done=0;
END$$
 
DELIMITER ;
 
 
 
##########
Please note that I don't suggest  you to use this for 5700000++ rows..

Open in new window

wintersun

ASKER

Due to time constraints, I am deeply appreciate if you can show me the sample store procedure use cursor to navigate every record and output file with the filename based on the record.seq_id at one time.
Besides, is there any ways to format the output data properly in the output file such as
<seqid<1001</seqid>
<params>column params data</params>
<results>column results data</results>
thanks for your help !!!

Umesh

>>Besides, is there any ways to format the output data properly in the output file such as
<seqid<1001</seqid>
<params>column params data</params>
<results>column results data</results>
Updated the stored procedure.. pls check I have done for single column and you may need to do it for rest of the columns,,

DELIMITER $$
DROP PROCEDURE IF EXISTS `uCreateFile`$$
CREATE PROCEDURE `uCreateFile`()
    READS SQL DATA
BEGIN
  DECLARE l_id            int;
  DECLARE l_column1         varchar(100);
  DECLARE l_done          INT DEFAULT  0;
  DECLARE cursor1 cursor  FOR
    SELECT id,column1
      FROM tableName;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET l_done=1;
        OPEN cursor1;
        cursor_loop:LOOP
            FETCH cursor1 INTO l_id,l_column1;
            IF l_done=1 THEN
                 LEAVE cursor_loop;
            END IF;
          SET @tmp_sql= CONCAT("SELECT CONCAT('<params',l_column1,'</params') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
 
          PREPARE s1 FROM @tmp_sql;
          EXECUTE s1;
          DEALLOCATE PREPARE s1
        END LOOP cursor_loop;
        CLOSE cursor1;
        SET l_done=0;
END$$
DELIMITER ;

Open in new window

Umesh

My bad.....change the below line
SET @tmp_sql= CONCAT("SELECT CONCAT('<params',l_column1 ,'</params ') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
SET @tmp_sql= CONCAT("SELECT CONCAT('<params>',l_column 1,'</param s>') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");

Umesh

Did you check??

wintersun

ASKER

My bad.....change the below line
SET @tmp_sql= CONCAT("SELECT CONCAT('
SET @tmp_sql= CONCAT("SELECT CONCAT('',l_column1,'') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
compare above 2 statements, thery're the same, am I right ?

Umesh

You may notice in first statement.. missing > from the <params & same for closing..
SET @tmp_sql= CONCAT("SELECT CONCAT('<params',l_column1 ,'></param s',l_colum n1,'>
so changed it to
SET @tmp_sql= CONCAT("SELECT CONCAT('<params>',l_column 1,'</param s>') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");

wintersun

ASKER

>> SET @tmp_sql= CONCAT("SELECT CONCAT('<params>',l_column 1,'</param s>') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
since we're using cursor and assign respective columns to the variables "FETCH cursor1 INTO l_id,l_column1" why do we need to have the SELECT .. FROM -> "SELECT CONCAT('<params>',l_column 1,'</param s>') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual" ?
I am confused on the statement and still trying to understand to concat more than 1 columns. Please advice. Thanks

ASKER CERTIFIED SOLUTION

Umesh

membership

Create a free account to see this answer

Signing up is free and takes 30 seconds. No credit card required.

See answer

wintersun

ASKER

>> SET @tmp_sql= CONCAT("SELECT CONCAT('<params>',l_column 1,'</param s>') INTO OUTFILE '/tmp/",l_id,".txt' FROM dual");
When execute, error returned "unknown l_column1 in field list", so I guess we need to put the table.fieldname instead SP variable.
Finally, I worked out the query by adding in more columns and the where clause to output 1 record per file. I know it will going to be a performance beat, I will re-consider.
SET @tmp_sql= CONCAT("SELECT CONCAT('<logid>',log_id,'< /logid>'), CONCAT('<params>',log_para ms,'</para ms>'), CONCAT('<results>',log_res ults,'</re sults>') INTO OUTFILE 'C:/", l_id, ".log' FROM logs WHERE log_id =", l_id);
Thanks for your big help !!

Umesh

You are welcome...