HI every one ,I have account with one of the hosting company, and i did my project in asp.net and I used MySQL for the database.
the hosting company is not giving me the full privileges to create new user or to create new stored procedure!!!
this is what they said for me:
Due to the shared nature of our environment we had to make some modifications to your procedure (namely the definer). We also had to review your procedure to determine if it would be compatible with our environment.
While your procedures will work (via phpMyAdmin or some other interface), it is unlikely they will be accessible via the Connector/.NET (ADO.NET) that your application is likely using. This is due to a security restriction with how that connector works in shared environments.
http://dev.mysql.com/doc/refman/5.0/en/connector-net-programming-stored.html
"Note
When you call a stored procedure, the command object makes an additional SELECT call to determine the parameters of the stored procedure. You must ensure that the user calling the procedure has the SELECT privilege on the mysql.proc table to enable them to verify the parameters. Failure to do this will result in an error when calling the procedure."
Unfortunately, giving read privileges on the mysql.proc table will give you access to the data of our other customers and that is not an acceptable risk. If your application can only work using stored procedures, then MSSQL will probably be the better option for your site.
I apologize for the inconvenience and the wait to have this ticket completed.
So is there any good hosting that any body already used it to publish his asp.net and mysql project ???
this is one of my stored procedure and i think it's sample and it will not harm any other uses!!:
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `SpcategoriesRead`(
IN PaRactioncode VARCHAR(5),
IN PaRCatID BIGINT,
IN PaRSearchText TEXT
)
BEGIN
-- CREATING TEMPORARY TABLE TO SAVE DATA FROM THE ACTIONCODE SELECTS --
DROP TEMPORARY TABLE IF EXISTS TEMP;
CREATE temporary table tmp
(
CatID BIGINT primary key not null,
CatTitle TEXT,
CatDescription TEXT,
CatTitleAr TEXT,
CatDescriptionAr TEXT,
PictureID BIGINT,
Published BOOLEAN,
DisplayOrder BIGINT,
CreatedOn DATE
);
IF PaRactioncode = 1 THEN -- Retrive all DATA from the database --
INSERT INTO tmp
SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn
FROM tbcategories;
ELSEIF PaRactioncode = 2 THEN -- Retrive all from the database By ID --
INSERT INTO tmp
SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn
FROM tbcategories
WHERE CatID=PaRCatID;
ELSEIF PaRactioncode = 3 THEN -- NOSET YET --
INSERT INTO tmp
SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn
FROM tbcategories
WHERE Published=1
ORDER BY DisplayOrder;
END IF;
IF PaRSearchText IS NOT NULL THEN
set PaRSearchText=concat('%', PaRSearchText ,'%');
SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn
FROM tmp
WHERE Concat(CatTitle, CatDescription, CatTitleAr, CatDescriptionAr)
LIKE PaRSearchText;
ELSE
SELECT CatID,CatTitle,CatDescription,CatTitleAr,CatDescriptionAr,PictureID,Published,DisplayOrder,CreatedOn
FROM tmp;
END IF;
DROP TEMPORARY TABLE IF EXISTS tmp;
END
