homepage Welcome to WebmasterWorld Guest from 54.205.254.108
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Become a Pro Member
Visit PubCon.com
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderators: physics

Databases Forum

    
Help, problem with stored procedure.
nelsonm




msg:4416750
 4:51 am on Feb 12, 2012 (gmt 0)

Hi all,

I have the following store procedure that does not work:

DROP PROCEDURE `usp_selectcount`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `usp_selectcount`(
IN pfrom VARCHAR(50)
)
BEGIN
SELECT COUNT(*) AS count
FROM pfrom;
END

It returns error: #1146 - Table 'irishmistusa-db.pfrom' doesn't exist

It's being called as:
call usp_selectcount("workorder");

I'm passing the name of the table "workorder" through the "pfrom" parameter but "pfrom" it's not getting parsed.

Does anyone know what is going on?

thanks.

 

Dijkgraaf




msg:4426690
 10:54 pm on Mar 8, 2012 (gmt 0)

You can't use a variable in a SQL FROM clause.

You would either have to do a case statement

CASE pfrom
WHEN 'workorder' THEN
SELECT COUNT(*) AS count FROM workorder
WHEN ...
ELSE ...

Or you have to construct a Dynamic SQL string and execute that (not good practice).

In fact passing a table name to a stored procedure in the first place is a bad idea and probably means you've made a fundamental flaw in you database/application design. Maybe explain what you are trying to achieve in the first place and then someone will suggest a better option.

nelsonm




msg:4426780
 3:13 am on Mar 9, 2012 (gmt 0)

Thanks...

I looked in the mysql manual but i could not find anything that talks about the restrictions on sql statements in stored procedures.

Does anyone know where i get some info on this?

Dijkgraaf




msg:4427908
 8:19 pm on Mar 11, 2012 (gmt 0)

It is not a matter of being a restriction, but rather that you trying to do something which just is not a feature, hence it it not documented.

And there is a good reason that it isn't a feature as dynamically executing SQL from a parameter passed to a stored procedure opens you up to SQL injection attacks.
And don't think that nobody will ever do that to your site as there was just such an attack that hit thousands of sites recently.

Again, explain what your end objective is at a higher level, and someone will probably suggest a better way of doing things.

nelsonm




msg:4427952
 11:33 pm on Mar 11, 2012 (gmt 0)

The end objective was to use a single stored procedure to count the records in tables. This count would be used to determine the number of grid pages available for display in the pager section of the jqGrid plugin navigation panel.

since parameter variables are not allowed to be used for table names in stored procedures, the count query mysql statements can stay in my php crud scripts.

Dijkgraaf




msg:4427988
 2:28 am on Mar 12, 2012 (gmt 0)

If there is a limited set of tables what you could do is

SELECT 'workorder' as tablename, COUNT(*) AS count FROM workorder
UNION ALL
SELECT 'table2' as tablename, COUNT(*) AS count FROM table2;

and have your application select the rows as needed from the results. This way you don't need to pass a parameter.

nelsonm




msg:4427989
 2:38 am on Mar 12, 2012 (gmt 0)

thanks, i'll look into it.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved