richardsimnett
asked on
Whats wrong with my sproc?
Hello, Whats wrong with my sproc?
Error while execeuting query: CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM 'delete from ? where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
EXECUTE STMT USING tablename;
END:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tablename;
END' at line 6 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
Here is the sproc:
delimiter $
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM 'delete from ? where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
EXECUTE STMT USING tablename;
END$
delimiter ;
Worth 500 points.
Thanks,
Rick
Error while execeuting query: CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM 'delete from ? where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
EXECUTE STMT USING tablename;
END:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'tablename;
END' at line 6 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
Here is the sproc:
delimiter $
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM 'delete from ? where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
EXECUTE STMT USING tablename;
END$
delimiter ;
Worth 500 points.
Thanks,
Rick
ASKER
ok, I tried your suggestion, and I get the following error:
Error while execeuting query: DECLARE work as VARCHAR(255):
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE work as VARCHAR(255)' at line 1 (errno: 1064)
My Code:
DECLARE work as TEXT;
SET work = 'delete from ' + tablename + ' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;';
PREPARE STMT FROM work;
EXECUTE STMT;
Error while execeuting query: DECLARE work as VARCHAR(255):
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'DECLARE work as VARCHAR(255)' at line 1 (errno: 1064)
My Code:
DECLARE work as TEXT;
SET work = 'delete from ' + tablename + ' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;';
PREPARE STMT FROM work;
EXECUTE STMT;
![Avatar of Guy Hengel [angelIII / a3]](https://filedb.experts-exchange.com/files/public/2013/11/25/ddbe0ea6-aa6e-4279-b781-57b735dd3ea0.jpeg){kind=small}
REATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM v_SQL;
EXECUTE STMT;
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM v_SQL;
EXECUTE STMT;
ASKER
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM v_SQL;
EXECUTE STMT;
END;
Output:
Error while execeuting query: CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEXT' at line 3 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM v_SQL;
EXECUTE STMT;
END;
Output:
Error while execeuting query: CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TEXT' at line 3 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
ASKER
angeliii,
ok I fixed the first issue.. I was forgettign to issue the delimiter commands... I have a totally different issue now:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'v_SQL;
EXECUTE STMT;
END' at line 7 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
The Code:
delimiter $
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
PREPARE STMT from v_SQL;
EXECUTE STMT;
END$
delimiter ;
ok I fixed the first issue.. I was forgettign to issue the delimiter commands... I have a totally different issue now:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'v_SQL;
EXECUTE STMT;
END' at line 7 (errno: 1064)
Click 'Ignore' if you'd like to have this error ignored until the end of the script.
The Code:
delimiter $
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL TEXT;
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
PREPARE STMT from v_SQL;
EXECUTE STMT;
END$
delimiter ;
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ok that one submitted.. yet still a problem... when I call the procedure:
call importData('testlist','tes
I get this error:
mysql> call importData('testlist','tes
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
call importData('testlist','tes
I get this error:
mysql> call importData('testlist','tes
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '0' at line 1
ASKER
ok just screwing around to see what was happening I added a select @v_SQL line, and for some reason that outputs 0. I think the query isnt being interpreted properly.
ASKER
delimiter $
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
SET @v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
select @v_SQL as qry;
PREPARE STMT from @v_SQL;
EXECUTE STMT;
END$
delimiter ;
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
SET @v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
select @v_SQL as qry;
PREPARE STMT from @v_SQL;
EXECUTE STMT;
END$
delimiter ;
ASKER
angeliii,
nm I got it.... instead of:
SET @v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
use
select concat('delete from ',tablename,' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;');
PREPARE STMT FROM @v_SQL;
EXECUTE STMT;
Thanks for the help!
nm I got it.... instead of:
SET @v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
use
select concat('delete from ',tablename,' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;');
PREPARE STMT FROM @v_SQL;
EXECUTE STMT;
Thanks for the help!
ASKER
select concat('delete from ',tablename,' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;');
should read:
select concat('delete from ',tablename,' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;') into @v_SQL;
should read:
select concat('delete from ',tablename,' where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1;') into @v_SQL;
you have to build the sql statement:
CREATE PROCEDURE importData(tablename varchar(255), userlist varchar(255))
BEGIN
DECLARE v_SQL as VARCHAR(400);
SET v_SQL = 'delete from `' + tablename + '` where datestamp is null or source is null or ip is null or length(datestamp) < 1 or length(source) < 1 or length(ip) < 1';
/* First execute a query to clean out incomplete data */
PREPARE STMT FROM v_SQL;
EXECUTE STMT;
...