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
richardsimnettAsked:
Who is Participating?
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
any better with this:

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';

      PREPARE STMT from @v_SQL;
      EXECUTE STMT;
END$
delimiter ;
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
you cannot make the table a parameter...

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;
           
 ...
0
 
richardsimnettAuthor Commented:
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;

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
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;
0
 
richardsimnettAuthor Commented:
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.

0
 
richardsimnettAuthor Commented:
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 ;
0
 
richardsimnettAuthor Commented:
ok that one submitted.. yet still a problem... when I call the procedure:

call importData('testlist','testlist2');

I get this error:

mysql> call importData('testlist','testlist2');
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
0
 
richardsimnettAuthor Commented:
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.
0
 
richardsimnettAuthor Commented:
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 ;
0
 
richardsimnettAuthor Commented:
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!
0
 
richardsimnettAuthor Commented:
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;

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.