Problem converting MsSql to MySql Stored procedure
- by karthik
Original source of MsSql SP is here..
http://www.codeproject.com/KB/database/InsertGeneratorPack.aspx
I am using the below MySql stored procedure, created by SQLWAYS [Tool to convert MsSql to MySql].
The purpose of this is to take backup of selected tables to a script file. when the SP returns a value {Insert statements}.
When i Execute the Below SP, i am getting a weird Result Set :
SQLWAYS_EVAL# ll(cast(UidSQLWAYS_EVAL# 0)),'0')+''','+SQLWAYS_EVAL# ll(UserNameSQLWAYS_EVAL# '+SQLWAYS_EVAL# ll(PasswordSQLWAYS_EVAL# '+
I see a lot of "SQLWAYS_EVAL#" in the code, which is produced in the result too.
What values need to be passed instead of "SQLWAYS_EVAL#".
So that i get the proper Insert statements for each record in the table.
I am new to MySql. Please help me. Its Urgent. Thanks.
DELIMITER $$
DROP PROCEDURE IF EXISTS `InsertGenerator` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGenerator`()
SWL_return:
BEGIN
-- SQLWAYS_EVAL# to retrieve column specific information
-- SQLWAYS_EVAL# table
DECLARE v_string VARCHAR(3000); -- SQLWAYS_EVAL# first half
-- SQLWAYS_EVAL# tement
DECLARE v_stringData VARCHAR(3000); -- SQLWAYS_EVAL# data
-- SQLWAYS_EVAL# statement
DECLARE v_dataType VARCHAR(1000); -- SQLWAYS_EVAL#
-- SQLWAYS_EVAL# columns
DECLARE v_colName VARCHAR(50);
DECLARE NO_DATA INT DEFAULT 0;
DECLARE cursCol CURSOR FOR
SELECT column_name,data_type FROM information_schema.`columns`
-- WHERE table_name = v_tableName;
WHERE table_name = 'tbl_users';
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
SET NO_DATA = -2;
END;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET NO_DATA = -1;
OPEN cursCol;
SET v_string = CONCAT('INSERT ',v_tableName,'(');
SET v_stringData = '';
SET NO_DATA = 0;
FETCH cursCol INTO v_colName,v_dataType;
IF NO_DATA <> 0 then
-- NOT SUPPORTED print CONCAT('Table ',@tableName, ' not found, processing skipped.')
close cursCol;
LEAVE SWL_return;
end if;
WHILE NO_DATA = 0 DO
IF v_dataType in('varchar','char','nchar','nvarchar') then
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(',v_colName,'SQLWAYS_EVAL# ''+');
ELSE
if v_dataType in('text','ntext') then -- SQLWAYS_EVAL#
-- SQLWAYS_EVAL# else
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(',v_colName,'SQLWAYS_EVAL# 00)),'''')+'''''',''+');
ELSE
IF v_dataType = 'money' then -- SQLWAYS_EVAL# doesn't get converted
-- SQLWAYS_EVAL# implicitly
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# y,''''''+
isnull(cast(',v_colName,'SQLWAYS_EVAL# 0)),''0.0000'')+''''''),''+');
ELSE
IF v_dataType = 'datetime' then
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# time,''''''+
isnull(cast(',v_colName,
'SQLWAYS_EVAL# 0)),''0'')+''''''),''+');
ELSE
IF v_dataType = 'image' then
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(convert(varbinary,',v_colName,
'SQLWAYS_EVAL# 6)),''0'')+'''''',''+');
ELSE
SET v_stringData = CONCAT(v_stringData,'SQLWAYS_EVAL# ll(cast(',v_colName,'SQLWAYS_EVAL# 0)),''0'')+'''''',''+');
end if;
end if;
end if;
end if;
end if;
SET v_string = CONCAT(v_string,v_colName,',');
SET NO_DATA = 0;
FETCH cursCol INTO v_colName,v_dataType;
END WHILE;
select v_stringData;
END $$
DELIMITER ;
