0 Replies - 862 Views - Last Post: 04 May 2010 - 09:25 PM

#1 srk1982  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 1
  • Joined: 04-May 10

Problem with Executing Mysql stored procedure

Posted 04 May 2010 - 09:25 PM

The stored procedure builds without any problem. The purpose of this is to take backup of selected tables to a script file. when the SP returns a value {Insert statements}.

I am using the below MySql stored procedure, created by SQLWAYS [Tool to convert MsSql to MySql]. The actual MsSql SP is from http://www.codeproje...eratorPack.aspx

When i execute the SP in MySql Query Browser, It says "Unknown column 'tbl_users' in 'field list'"

What would be the problem ? Because there was no error when i build-ed this Converted MySql SP.

Help..


Stored Procedure :

DELIMITER $$

DROP PROCEDURE IF EXISTS `demo`.`InsertGenerator` $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `InsertGenerator`(v_tableName VARCHAR(100))
SWL_return:
BEGIN
-- SQLWAYS_EVAL# to retrieve column specific information

-- SQLWAYS_EVAL# table

DECLARE v_string NATIONAL VARCHAR(3000); -- SQLWAYS_EVAL# first half

-- SQLWAYS_EVAL# tement

DECLARE v_stringData NATIONAL VARCHAR(3000); -- SQLWAYS_EVAL# data

-- SQLWAYS_EVAL# statement

DECLARE v_dataType NATIONAL VARCHAR(1000); -- SQLWAYS_EVAL#

-- SQLWAYS_EVAL# columns

DECLARE v_colName NATIONAL VARCHAR(50);
DECLARE NO_DATA INT DEFAULT 0;
DECLARE cursCol CURSOR FOR
SELECT column_name,data_type FROM `columns`
WHERE table_name = v_tableName;
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;
END $$

DELIMITER ;

This post has been edited by srk1982: 04 May 2010 - 09:29 PM


Is This A Good Question/Topic? 0
  • +

Page 1 of 1