Problems with stored procedures that were working ... (just a few hours after purchase)

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
cesarvegamx
Posts: 37
Joined: Tue 30 Jan 2007 07:43

Problems with stored procedures that were working ... (just a few hours after purchase)

Post by cesarvegamx » Wed 14 Feb 2007 03:17

I'm migrating an application that used the community Ado.Net provider to use CoreLab's.
Seems all my stored procedures are broken.
They were working, the only thing I did was to change the driver.
See for example:

Code: Select all

DROP PROCEDURE IF EXISTS sp_stsn_CheckState;

CREATE PROCEDURE sp_stsn_CheckState
(
   _Country Char(2), 
   _State Char(2), 
   _CityName Varchar(70)
 )
BEGIN

  Select 'X' From STSN_STATE
  Where CTRY_CODE = _Country And
  STAT_CODE = _State;
  
  SET @rows = ROW_COUNT();
  
  if @rows = 0 Then
     Insert Into STSN_STATE Values ( _Country , _State ,  _CityName);
  End If;

END;

This gives me the error:

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 ':'00'_Country,:'00'_State,:''_CityName)' at line 1
CoreLab.MySql
at CoreLab.MySql.a0.n()
at CoreLab.MySql.a0.c()
at CoreLab.MySql.b.a(e[]& A_0, Int32& A_1)
at CoreLab.MySql.b.a(Byte[] A_0, Int32 A_1, Boolean A_2)
at CoreLab.MySql.v.d()
at CoreLab.MySql.MySqlCommand.a(CommandBehavior A_0, IDisposable A_1, Int32 A_2, Int32 A_3)
at CoreLab.Common.DbCommandBase.b(CommandBehavior A_0)
at System.Data.Common.DbCommand.ExecuteReader()
at CoreLab.Common.DbCommandBase.ExecuteNonQuery()
I'm passing the values '00', '00' and '' to this stored. It is the same stored was working 3 minutes ago. Same code, same everything.

I don't know, I change nothing, but the DLL to use. If I comment the part of my code that use this stored and let it go, it will broke in the next store with very similar error and so on.
Do I have to modify my stored procedure syntax to use CoreLab's provider?

cesarvegamx
Posts: 37
Joined: Tue 30 Jan 2007 07:43

Post by cesarvegamx » Wed 14 Feb 2007 04:14

Seems I know what it was.
The other driver needed a "?" in front of each param name.
Seems CoreLab's provider does not needed.

Which is the standard ?

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Wed 14 Feb 2007 12:40

There are a few ways to denote parameters in MySQLDirect .NET.
Please take a look at our article "Using Parameters" in the documentation for the full information on this topic.

cesarvegamx
Posts: 37
Joined: Tue 30 Jan 2007 07:43

Post by cesarvegamx » Wed 14 Feb 2007 15:43

Thanks.

Please, check my other problem about BigInt columns

Alexey
Posts: 2756
Joined: Mon 13 Mar 2006 07:43

Post by Alexey » Thu 15 Feb 2007 07:53

Answered you in that thread as well.

Post Reply