[RESOLVED] Mysql Sprocs Not running Mysql 8 and VS2022

[KenBZim2]

Dear All I have been using Visual Studio 2008 and Mysql 5.0 for a great number
of years and have written many applications using this combination.
I have just upgraded to Mysql 8 and at the same time decided to upgrade to Visual Studio 2022
I have migrated one of my applications into VS2022, installed the lastest Mysql connector for .Net 6
and all is working fine i am able to connect to the db, run queries and do inserts and updates the problem
i am facing is executing stored procedures.
when i try and execute any of my stored procedures i get the same error message:
'MySql.Data.MySqlClient.MySqlException: 'Table 'mysql.proc' doesn't exist'
yet there is no mention of such a table name in the code or in the sproc

I can execute the sproc with no problem from a third party database manager, so there is no problem with the sproc
Yet it returns the same error every time in VS 2022

To me this means that there is something wrong with the VB code although it ran fine in the older version of VB and Mysql??

To try and drill down to the problem, i wrote a very basic sproc to see if that would work and even that throws the same error?

See VB code and sproc below:

Public Function sproc_fusion_test(ByVal InputInteger As Integer) As Integer
        Dim MyDatacon As New MySqlConnection("Server=192.168.2.80;Database=fusion;Uid=root;Pwd=12345;Connection Timeout= 30; respect binary flags=false;")
        Try
            'Open The Connection
            MyDatacon.Open()

            Dim Cmd As New MySqlCommand("fusion_test", MyDatacon)
            Cmd.CommandType = CommandType.StoredProcedure
            Cmd.Parameters.Add("@sp_input_value", MySqlDbType.Int16).Value = InputInteger
            Cmd.Parameters.Add("@sp_output_value", MySqlDbType.Int16)

            'Specify the direction of the outward parameters
            Cmd.Parameters("@sp_output_value").Direction = ParameterDirection.Output

            Cmd.ExecuteNonQuery
            sproc_fusion_test = Val(Cmd.Parameters("@sp_output_value").Value.ToString)

            MyDatacon.Close()
        Catch Ex As Exception
          
            MsgBox(Ex.ToString)
        End Try
    End Function

Stored Procedure

CREATE DEFINER = 'root'@'localhost' PROCEDURE `fusion_test`(
IN `sp_input_value` INTEGER(6),
OUT `sp_output_value` INTEGER(6)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
set sp_output_value=sp_input_value * 10;
END;

Any help would be greatly appreciated

[Zvoni]

From MySQL: https://dev.mysql.com/doc/refman/8.0...procedure.html

These statements are used to create a stored routine (a stored procedure or function). That is, the specified routine becomes known to the server. By default, a stored routine is associated with the default database. To associate the routine explicitly with a given database, specify the name as db_name.sp_name when you create it.

At a guess: your sproc is in the scope of your Database-Schema

btw: You do know you can create a Function in MySQL8 which has a Return-Value?

EDIT2: Found something
https://stackoverflow.com/questions/...mysql-stored-p

Doy ou have the correct NuGet-Package?

EDIT3: https://stackoverflow.com/questions/...ource-to-visua
And set CheckParameters=False in your Connection-String

[KenBZim2]

Hi Zvoni

Thanks for your reply

"At a guess: your sproc is in the scope of your Database-Schema " --- Yes it is

"btw: You do know you can create a Function in MySQL8 which has a Return-Value?" - i have functions in Mysql5.0 that return a value


I have tried using the database name before the sproc name i.e. fusion.fusion_test and i have tried changing the user from root to another user name
I am wondering if it has anything to do with the fact that i am NOT running the sproc from the localhost but rather from a client machine on the network???

[KenBZim2]

ok i have made a few changes to the sproc and the code in the function, but now getting a different error :

'MySql.Data.MySqlClient.MySqlException: 'Incorrect number of arguments for PROCEDURE fusion.fusion_test; expected 2, got 0' ???

Public Function sproc_fusion_test(ByVal InputInteger As Integer) As Decimal
        Dim MyDatacon As New MySqlConnection("Server=192.168.2.80;Database=fusion;Uid=test;Pwd=123456;Connection Timeout= 30; respect binary flags=false; CheckParameters=false;")
 
        MyDatacon.Open()
        Dim Cmd As New MySqlCommand
        Dim Sql As String = ""
        Sql = "call fusion_test()"
        Cmd = New MySql.Data.MySqlClient.MySqlCommand(Sql, MyDatacon)
        Cmd.CommandType = System.Data.CommandType.Text
        Cmd.Parameters.Add("@sp_input_value", MySqlDbType.Int16).Value = InputInteger
        Cmd.Parameters.Add("@sp_output_value", MySqlDbType.Int16)

            'Specify the direction of the outward parameters
            Cmd.Parameters("@sp_output_value").Direction = ParameterDirection.Output

            Cmd.ExecuteNonQuery
            sproc_fusion_test = Val(Cmd.Parameters("@sp_output_value").Value.ToString)

            MyDatacon.Close()
     
    End Function

CREATE DEFINER = 'test'@'*.*' PROCEDURE `fusion_test`(
IN `sp_input_value` INTEGER(6),
OUT `sp_output_value` INTEGER(6)
)
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
set sp_output_value=sp_input_value * 10;
END;

[Zvoni]

Why did you change the Command-Type?

[KenBZim2]

from one of the posts that i saw

[techgnome]

Classic case of code slinging w/o understanding what you're doing, as well as not reading the documentation.
The command type tells the command object what kind of command it will be running. I forget all of the option, but the two important ones are Procedure and Text. Text means you are running a STRING through it, while Procedure means you are running a PROCEDURE that is ON THE SERVER.
When to use TEXT -> When you construct hte SQL inside of your application dynamically and pass it to the command object. Set the CommandText to the SQL you want to execute.
When to use PROCEDURE -> When you want to run a sproc that is stored on the server in the database. Set the CommandText to the name of the sproc you want to run.
When to use TABLE -> When you just want the contents of a table. Set the CommandText to the name of the table whose contents you want.

-tg

[KenBZim2]

hi tg, thanks for your reply. I have changed the code back to its original

Public Function sproc_fusion_test(ByVal InputInteger As Integer) As String
        Dim MyDatacon As New MySqlConnection("Server=192.168.2.80;Database=fusion;Uid=test;Pwd=Bitumen2022!;Connection Timeout= 30; respect binary flags=false; CheckParameters=false;")

        MyDatacon.Open()
        Dim Cmd As New MySqlCommand
        Cmd.CommandType = CommandType.StoredProcedure
        Cmd.Parameters.Add("@sp_input_value", MySqlDbType.Int16).Value = InputInteger
        Cmd.Parameters.Add("@sp_output_value", MySqlDbType.Int16)

        'Specify the direction of the outward parameters
        Cmd.Parameters("@sp_output_value").Direction = ParameterDirection.Output

        Cmd.ExecuteNonQuery()
        sproc_fusion_test = Cmd.Parameters("@sp_output_value").Value.ToString

        MyDatacon.Close()

    End Function

and am now getting a different error
'System.InvalidOperationException: 'Connection must be valid and open.'

This is throwing more problems at me than imaginable!!

Yet all worked fine with Mysql 5 and VS2008 ???

'

[PlausiblyDamp]

Shouldn't the command object be configured to use the connection your are creating and opening?

[Zvoni]

Shouldn't the command object be configured to use the connection your are creating and opening?

Yep. This line is missing/wrong

Dim Cmd As New MySqlCommand("fusion_test", MyDatacon)

EDIT: This whole Thread reminds me of: "Change ONLY this!! ...oO(But he changes two other things in the Background)"

[techgnome]

Yep. This line is missing/wrong

Dim Cmd As New MySqlCommand("fusion_test", MyDatacon)

EDIT: This whole Thread reminds me of: "Change ONLY this!! ...oO(But he changes two other things in the Background)"

On reddit there's a sub /r/IdidntHaveEggs ... where people post about people who reviewed recipies, but changed key ingredients, creating a completely different dish, then knock stars off because it was horrible. This thread kind of reminds me of that.

-tg

[KenBZim2]

Thank You Zvoni!!
working now