Thread: Problem with MySql Stored Procedure

Originally Posted by jmurrayhead

hmm..interesting...any reason you're using ODBC instead of the MySQL connector? Your syntax before, minus the parenthesis, would have probably worked if you were using that instead.

i dont know J, i have no experience!

i thought that may be odbc will be there at the client server and i dont know much abt all this

Sorry guys....Was in meetings all day yesterday...

I agree with J...Download the MySQL Connector and use that....You just drop the MySQL dll in your bin folder.

The namespace is MySQL.Data.MySqlClient

Then you have MySQL objects: MySQLConnection, MySQLCommand, MySQLDataReader etc....

Then you just set MySqlCommand.CommandType = StoredProcedure and CommandText to the name of your SP.

I'm not really using SPs as I found them a bit of a PITA without giving a huge increase in performance, but if you get stuck I'll see if I can help...

thanx J n RR!!
so basically is it better to use "MySQL connector" than odbc one??

and do u suggest that i shud drop the idea of using SPs with MySql??
i wanted to use them coz i thought it'll be faster!!

I use the mysql connector and don't have any issues....I think it's better to use the connector from MySQL than a generic one, just because it's been developed by the same people that develop the db...

SPs should be quicker, so if I was you, I'd use them...

You could always try making up 2 pages, one using an SP and one not and see if you find any performance differences....That's what I did....

Thanx for that one RR
but u said that u didnt find any increase in performance and i also feel they r a little PITA coz of their syntax and all

Originally Posted by micky

Thanx for that one RR
but u said that u didnt find any increase in performance and i also feel they r a little PITA coz of their syntax and all

I have one page that brings up a list with about 600-700 rows....I found running a query as an SP and running a query in query browser gave very little difference in the speed the query was returned (we're talking milliseconds). Any improvement in the speed the query was returned was completely negated by the time it took .NET to process the page and render it to the screen.

I didn't delve into db inserts and updates and test them, but maybe where there is a small page load time (ie retrieving a single record), using an SP could improve performance.

ok RR, i got u
thanx for inputs, downloaded the MySql connector and will use it and SPs.

and surely will be bak with problems

Originally Posted by micky

ok RR, i got u
thanx for inputs, downloaded the MySql connector and will use it and SPs.

and surely will be bak with problems

No problem micky....

i m bak

Code:

my SP
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_LoginCheck`(
  IN EmailId varchar(100))
BEGIN
  Select First_Name, Last_Name From tblUserMaster Where Email_Id=EmailId;
END

my new code

Code:

Dim Cmd As New MySqlCommand("call sp_LoginCheck", Con)

Dim EmailIDPara As New MySqlParameter("EmailId", Trim(txtEmail.Text), MySql.Data.MySqlClient.MySqlDbType.VarChar, System.Data.ParameterDirection.Input)
Cmd.Parameters.Add(EmailIDPara)

Dim Dtr As MySqlDataReader = Cmd.ExecuteReader()
Cmd.Dispose()

While Dtr.Read
          Response.Write("Welcome " & Dtr("First_Name") & " " & Dtr("Last_Name"))
End While

i get this error using debug

Code:

System.InvalidCastException = {"Conversion from string "xxx" to type 'Integer' is not valid."}

y is it converting to integer?