[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Whats wrong with my sproc?

Posted on 2007-10-02
11
Medium Priority
?
378 Views
Last Modified: 2008-02-26
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
0
Comment
Question by:richardsimnett
  • 8
  • 3
11 Comments
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20004063
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
 

Author Comment

by:richardsimnett
ID: 20004154
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
 
LVL 143

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 20004296
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
Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

 

Author Comment

by:richardsimnett
ID: 20004495
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
 

Author Comment

by:richardsimnett
ID: 20004541
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
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 1500 total points
ID: 20004547
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
 

Author Comment

by:richardsimnett
ID: 20004729
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
 

Author Comment

by:richardsimnett
ID: 20004749
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
 

Author Comment

by:richardsimnett
ID: 20004753
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
 

Author Comment

by:richardsimnett
ID: 20012203
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
 

Author Comment

by:richardsimnett
ID: 20012204
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

Featured Post

Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month18 days, 18 hours left to enroll

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question