VB.net 到 MySql 存储过程错误 [英] VB.net to MySql Stored Procedure Error

问题描述

我是存储过程的新手，所以我可能会遗漏一些简单的东西，但是我研究了基础知识并且我一直在尝试从 vb.net 代码中集成它.我创建了一个简单的存储过程(我认为)，它只为今天的结果运行数据查询:

I'm a total newb to stored procedures, so I may be missing something easy, but I researched the basics and I'm stuck trying to integrate it from vb.net code. I created a simple stored procedure (I think) that just runs a query of data for today's results:

-- 常规 DDL-- 注意:例程正文前后的注释不会被服务器存储

-- Routine DDL -- Note: comments before and after the routine body will not be stored by the server

DELIMITER $$

CREATE DEFINER=`root`@`%` PROCEDURE `GetRuntestToday`()
BEGIN

Select * From runtest.runtest_records where
Test_Date=CURDATE()
order by test_date desc, Convert(test_time_stop,DECIMAL(5,2)) desc;

END

当我登录到主 MySql 数据库并尝试从 MySql 提示符运行它时，它似乎工作正常.我只需键入 call runtest.GetRuntestToday(); 并以命令提示符文本形式返回 59 行数据.

When I log on to the main MySql database and attempt to run it from the MySql prompt, it appears to work fine. I simply type call runtest.GetRuntestToday(); and it returns 59 rows of data in command prompt text form.

我编写了一个 VB.net 程序来尝试获取相同的数据，但我一直收到错误消息.错误异常详情为:

I wrote a VB.net program to try to get that same data but I keep getting an error. The error exception details are:

System.Data.Odbc.OdbcException was unhandled
  ErrorCode=-2146232009
  Message="ERROR [42000] [MySQL][ODBC 5.1 Driver][mysqld-5.1.51-community-log]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'GetRuntestToday' at line 1"
  Source="myodbc5.dll"
  StackTrace:
       at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object[] methodArguments, SQL_API odbcApiMethod)
       at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
       at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
       at System.Data.Odbc.OdbcCommand.ExecuteReader()
       at MySqlHelper.mMain.DoMyStoredProcedure() in C:\vss\MySqlHelper\MySqlHelper\mMain.vb:line 2642
       at MySqlHelper.mMain.Main() in C:\vss\MySqlHelper\MySqlHelper\mMain.vb:line 29
       at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
       at System.AppDomain.ExecuteAssembly(String assemblyFile, Evidence assemblySecurity, String[] args)
       at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
       at System.Threading.ThreadHelper.ThreadStart_Context(Object state)
       at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
       at System.Threading.ThreadHelper.ThreadStart()
  InnerException: 

我正在运行的代码是:

Public Sub DoMyStoredProcedure()

    Dim MyConString As String = "My String Details"
    Dim dbcRuntest As New OdbcConnection(MyConString)


    Dim cmd As New OdbcCommand
    Dim reader As OdbcDataReader

    cmd.CommandText = "GetRuntestToday"
    cmd.CommandType = CommandType.StoredProcedure
    cmd.Connection = dbcRuntest

    dbcRuntest.Open()

    reader = cmd.ExecuteReader()

    dbcRuntest.Close()
End Sub

错误发生在线路上:

reader = cmd.ExecuteReader()

我错过了什么?我没有看到任何语法问题，存储过程在命令提示符下工作.我已经玩了一些 DELIMITER 的东西使它//而不是 $$ 但似乎没有什么可以解决它.

What am I missing? I don't see any syntax problems and the stored procedure works from the command prompt. I've played around a little with the DELIMITER stuff making it // instead of $$ but nothing seems to fix it.

推荐答案

您可以尝试改为直接调用(我还没有测试过:

You might try changing to a direct call (I haven't tested this:

 cmd.CommandType = CommandType.Text
 cmd.CommandText = "CALL GetRuntestToday"

此外，还有一种更好的代码编写方式:

Also, a better way of writing your code:

Dim MyConString As String = "My String Details"
Using dbcRuntest As New OdbcConnection(MyConString)
  dbcRuntest.Open()
  Using cmd As New OdbcCommand("CALL GetRuntestToday", dbcRuntest)
    cmd.CommandType = CommandType.Text
    Using reader As OdbcDataReader = cmd.ExecuteReader
      'do someting with reader'
    End Using
  End Using 
End Using

Using 结构自动关闭连接并处理它(连同其他对象).

The Using structure automatically closes the connection and disposes it (along with the other objects).

编辑为使用 CALL 而不是 EXECUTE

这篇关于VB.net 到 MySql 存储过程错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！