MY SQL
********
post in server with restriction use following
in connection string use the following code also
use procedure bodies=false //the user who not have the execution rights in the server for tables, procedure etc
allow user variables =true //for retriving values from db that is return in my sql use this code
Example connection string is
<connectionStrings>
<add name="STudConStr" connectionString="Server=ipaddress;Port=3306;Database=dbname;Uid=username;Pwd=password; use procedure bodies=false; allow user variables =true" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
Example procedure in mysql
***********************
DELIMITER $$
CREATE DEFINER=`username of db`@`server ip` PROCEDURE `spGetTotQuestionCount`()
BEGIN
SELECT count(distinct QuestionId) as QuestionCount
from AnswerMaster ;
END$$
DELIMITER ;
Some of diffence from ms sql VS My sql are as follows
The best way is user can download the tool for converting ms sql to mysql and convert it or else use mysql migration tool to convert table.This helps the user to get Database with value
********
post in server with restriction use following
in connection string use the following code also
use procedure bodies=false //the user who not have the execution rights in the server for tables, procedure etc
allow user variables =true //for retriving values from db that is return in my sql use this code
Example connection string is
<connectionStrings>
<add name="STudConStr" connectionString="Server=ipaddress;Port=3306;Database=dbname;Uid=username;Pwd=password; use procedure bodies=false; allow user variables =true" providerName="MySql.Data.MySqlClient"/>
</connectionStrings>
Example procedure in mysql
***********************
DELIMITER $$
CREATE DEFINER=`username of db`@`server ip` PROCEDURE `spGetTotQuestionCount`()
BEGIN
SELECT count(distinct QuestionId) as QuestionCount
from AnswerMaster ;
END$$
DELIMITER ;
Some of diffence from ms sql VS My sql are as follows
|
MsSQL
|
MySql
|
|
1)Use Identity(1,1)
Example
CREATE TABLE animals (
id int IDENTITY(1,1) NOT
NULL,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
|
use AUTO_INCREMENT
Example
CREATE TABLE animals (
id int NOT NULL AUTO_INCREMENT,
name CHAR(30) NOT NULL,
PRIMARY KEY (id)
);
INSERT INTO animals (name) VALUES
('dog'),('cat'),('penguin'),
('lax'),('whale'),('ostrich');
SELECT * FROM animals;
Which returns:
+----+---------+
| id | name |
+----+---------+
| 1 | dog |
| 2 | cat |
| 3 | penguin |
| 4 | lax |
| 5 | whale |
| 6 | ostrich |
+----+---------+
|
|
2)nvarchar(max)
|
longtext
|
|
For saving image
data type
3)Image
|
LONGBLOB
|
|
For creating procedure
4) CREATE PROCEDURE spGetTotQuestionCount
As
BEGIN
SELECT
count(distinct QuestionId) as QuestionCount from AnswerMaster
END
|
CREATE PROCEDURE spGetTotQuestionCount()
BEGIN
SELECT
count(distinct QuestionId) as QuestionCount from AnswerMaster
END
|
|
For parameterized procedure
5) CREATE PROCEDURE spGetTot (@a int)
|
Create procedure spGetTot( IN a int)
|
|
declaration
6)declare @a int,@b int=0;
|
Declare a int;
Declare b int;
Set b=0;
|
|
For if condition
7)if(@a>@b)
Begin
Set @a=100;
end
|
if(a>b) then
Begin
Set a=100;
End
End if
|
|
For while condition
8)while(@a>@b)
Begin
@a=100;
end
|
while(a>b) then
Begin
a=100;
end
end while
|
|
9) For set default constrains
ALTER TABLE UserExamResult ADD DEFAULT ((0.0)) FOR TotalMarks
|
ALTER TABLE UserExamResult alter TotalMarks set DEFAULT 0.0 ;
|
|
|
The best way is user can download the tool for converting ms sql to mysql and convert it or else use mysql migration tool to convert table.This helps the user to get Database with value
