Stored procedure help please

[justforfuntwt]

hello,

I'm writing a stored procedure and i need some help:
[code=mysql]
DELIMITER //
DROP PROCEDURE IF EXISTS INSERTDBsb//


CREATE PROCEDURE INSERTDBsb( p_email varchar(160) , p_tablename varchar(80) , p_timestamp varchar(30) , p_data double)
BEGIN
DECLARE maxid bigint(20);
DECLARE userid bigint(20);
SELECT max(_id) into maxid from p_tablename;

SELECT id into userid FROM users WHERE email= p_email LIMIT 1;

INSERT INTO p_tablename (_id, _user_id, _datetime, timestamp, data)
VALUES
(maxid, userid, NOW(), p_timestamp, p_data);

END;

//
[/code]

This runs, however for SELECT max(_id) into maxid from p_tablename; it will take p_tablename literally and not the variable.
Same thing further on, any help would be appreciated, thanks in advance

[Atli]

Hi.

Take a look at this article.

To sum it up:
[code=mysql]
CREATE PROCEDURE colavg(IN tbl CHAR(64), IN col CHAR(64))
BEGIN
SET @s = CONCAT('SELECT AVG(' , col , ') FROM ' , tbl);
PREPARE stmt FROM @s;
EXECUTE stmt;
END;
[/code]

[justforfuntwt]

I tried that too before posting, but I kinda beleived it not the way to go:
[code=mysql]
DELIMITER //
DROP PROCEDURE IF EXISTS INSERTDBsb//

CREATE PROCEDURE INSERTDBsb( IN p_email varchar(160) , IN p_tablename varchar(80) , IN p_timestamp varchar(30) , IN p_data double)
BEGIN
DECLARE maxid bigint(20);
DECLARE userid bigint(20);
SET @s = CONCAT('SELECT max(_id) into maxid from ', p_tablename);
PREPARE stmt FROM @s;
EXECUTE stmt;

SET @s = CONCAT('SELECT id into userid from users WHERE email = ', p_email, ' LIMIT 1');
PREPARE stmt FROM @s;
EXECUTE stmt;

SET @s = CONCAT('INSERT INTO (' , p_tablename , ') VALUES ( ' , maxid, ',' , userid, ',' , NOW(), ',' , p_timestamp, ',', p_data, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;

END;
//
[/code]

my error is : _mysql_exceptio ns.OperationalE rror: (1327, 'Undeclared variable: maxid')

Is there some way to fix this so this works?

[Atli]

The problem is that the 'maxid' variable is not recognized inside the dynamic query.

Try doing something like:
[code=mysql]
SET @query = CONCAT('SELECT @maxid:=max(id) FROM ', p_table);
PREPARE stmt FROM @query;
EXECUTE stmt;

SET @query = CONCAT('INSERT INTO ', p_table, ' VALUES(@maxid)' );
PREPARE stmt FROM @query;
EXECUTE stmt;
[/code]

P.S.
Please us [code] tags when posting code examples.

[justforfuntwt]

ok so in the end I have this:

DELIMITER //
DROP PROCEDURE IF EXISTS INSERTDBsb//
CREATE PROCEDURE INSERTDBsb( IN p_email varchar(160) , IN p_tablename varchar(80) , IN p_timestamp varchar(30) , IN p_data double)
BEGIN
DECLARE maxid bigint(20);
DECLARE userid bigint(20);
SET @s = CONCAT('SELECT @maxid:=max(_id) from ', p_tablename);
PREPARE stmt FROM @s;
EXECUTE stmt;

SET @s = CONCAT('SELECT @userid:=id from users WHERE email = ', p_email, ' LIMIT 1');
PREPARE stmt FROM @s;
EXECUTE stmt;

SET @s = CONCAT('INSERT INTO (' , p_tablename , ') VALUES ( ' , @maxid, ',' , @userid, ',' , NOW(), ',' , p_timestamp, ',', p_data, ')');
PREPARE stmt FROM @s;
EXECUTE stmt;

END;
//

when I try to run it threw python:

		args = {}
		args[0] = self.params['email']
		args[1] = wholeTableName
		args[2] = l_timestamp
		args[3] = l_data	
		cursor.callproc('INSERTDBsb',args)

i get this error

_mysql_exceptio ns.ProgrammingE rror: (1064, "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 '1' at line 1")

thanks alot so far for the help I feel like I'm getting closer to the solution

[Atli]

That last INSERT statement is a little weird.

The table name should not be encapsulated.
The @variables can be called withing the query. They don't need to be CONCAT'ed.
Also, the NOW() function should not be a parameter in the CONCAT, it should be in the string.

So, it should look more like:
[code=mysql]
SET @s = CONCAT('INSERT INTO ' , p_tablename , ' VALUES (@maxid, @userid, NOW(),' , p_timestamp, ',', p_data, ')');
[/code]

[justforfuntwt]

thanks alot, I just tried it all, and even though that would have brought up an error later on, there must be one earlier, truth is that i'm almost sure that when the first statement is executed there is a problem

by just writing this minimalist procedure

DELIMITER //
DROP PROCEDURE IF EXISTS INSERTDBsb//
# MySQL returned an empty result set (i.e. zero rows).

CREATE PROCEDURE INSERTDBsb( IN p_email varchar(160) , IN p_tablename varchar(80) , IN p_timestamp varchar(30) , IN p_data double)
BEGIN
DECLARE maxid bigint(20);
DECLARE userid bigint(20);
SET @s = CONCAT('SELECT @maxid:=max(_id) from ', p_tablename);
PREPARE stmt FROM @s;
EXECUTE stmt;



END;
//

and then calling that like I do I'm getting the same error : (_mysql_excepti ons.Programming Error: (1064, "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 '1' at line 1")), hence I believe that this line

SET @s = CONCAT('SELECT @maxid:=max(_id) from ', p_tablename);
PREPARE stmt FROM @s;
EXECUTE stmt;

does not work for some reason, removing it of coarse resolves the problem

do you think that it see what p_tablename is?

[Atli]

That's weird. I can't see any problem with the query. It should be working.

Try hard-coding the table name into the query instead of adding it dynamically. Just for debugging purposes, to rule out that the parameter is somehow responsible.

Also try replacing the "@maxid:=max(_i d)" with just "max(_id)". Just to see if that produces the expected result.

Could it be that Python is somehow messing up the @variable system?