HI Friends I am calling a procedure on MySQL based db server from my vb.net based application .
Now I haven't used any Int variable in my code but I am getting overflow exception for INt32 conversion as shown below..
Below is stack trace for the same
System.OverflowException: Value was either too large or too small for an Int32.
at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
at System.Int32.Parse(String s, IFormatProvider provider)
at MySql.Data.Types.MySqlInt32.MySql.Data.Types.IMySqlValue.ReadValue(MySqlPacket packet, Int64 length, Boolean nullVal)
at MySql.Data.MySqlClient.NativeDriver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue valObject)
at MySql.Data.MySqlClient.Driver.ReadColumnValue(Int32 index, MySqlField field, IMySqlValue value)
at MySql.Data.MySqlClient.ResultSet.ReadColumnData(Boolean outputParms)
at MySql.Data.MySqlClient.ResultSet.NextRow(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlDataReader.Read()
at MySql.Data.MySqlClient.StoredProcedure.ProcessOutputParameters(MySqlDataReader reader)
at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader()
at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
at ALMTestBeta.ATB_DBConnector.create_session(String userid) in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_DAL\ATB_DBConnector.vb:line 82
at ALMTestBeta.AWM.InitiateAWM() in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_BL\AWM_BL\AWM.vb:line 55
at ALMTestBeta.AuthenticationWindow.CPGMLauncher_ButtonClick(Object sender, EventArgs e) in C:\Users\Satyam Mishra\Documents\SharpDevelop Projects\ATB\ALMTestBeta\ATB_UI\AWM_UI\AuthenticationWindow.vb:line 43
at System.Windows.Forms.Control.OnClick(EventArgs e)
at System.Windows.Forms.Button.OnClick(EventArgs e)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.SendMessage(HandleRef hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.Control.SendMessage(Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.Control.ReflectMessageInternal(IntPtr hWnd, Message& m)
at System.Windows.Forms.Control.WmCommand(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.GroupBox.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.CallWindowProc(IntPtr wndProc, IntPtr hWnd, Int32 msg, IntPtr wParam, IntPtr lParam)
at System.Windows.Forms.NativeWindow.DefWndProc(Message& m)
at System.Windows.Forms.Control.DefWndProc(Message& m)
at System.Windows.Forms.Control.WmMouseUp(Message& m, MouseButtons button, Int32 clicks)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ButtonBase.WndProc(Message& m)
at System.Windows.Forms.Button.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.DebuggableCallback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
at System.Windows.Forms.UnsafeNativeMethods.DispatchMessageW(MSG& msg)
at System.Windows.Forms.Application.ComponentManager.System.Windows.Forms.UnsafeNativeMethods.IMsoComponentManager.FPushMessageLoop(IntPtr dwComponentID, Int32 reason, Int32 pvLoopData)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoopInner(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.ThreadContext.RunMessageLoop(Int32 reason, ApplicationContext context)
at System.Windows.Forms.Application.Run(ApplicationContext context)
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.OnRun()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.DoApplicationModel()
at Microsoft.VisualBasic.ApplicationServices.WindowsFormsApplicationBase.Run(String[] commandLine)
at ALMTestBeta.My.MyApplication.Main(String[] Args) in 17d14f5c-a337-4978-8281-53493378c1071.vb:line 81
NOw the coding which is calling the procedure is
Public Function create_session(ByVal userid As String) As String
Dim session_id As String
session_id="-1"
myCommand=New MySqlCommand()
myCommand.Connection=atbconn
myCommand.CommandText="p_create_chk_session"
myCommand.CommandType=CommandType.StoredProcedure
myCommand.Parameters.AddWithValue("p_user_id",userid)
myCommand.Parameters("p_user_id").Direction=ParameterDirection.Input
myCommand.Parameters.AddWithValue("p_session_id",MySqlDbType.VarChar)
myCommand.Parameters("p_session_id").Direction=ParameterDirection.Output
Try
myCommand.ExecuteNonQuery()
session_id=CType(myCommand.Parameters("p_session_id").Value,String)
Catch myprocedureException As MySqlException
MsgBox ("ErrorGenerated in session creation " & myprocedureException.Number & "has occured " & myprocedureException.Message)
End Try
Return session_id
End Function
and called procedure is as below
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DELIMITER $$
CREATE PROCEDURE p_create_chk_session
(IN p_user_id varchar(45),
OUT p_session_id varchar(45)
)
BEGIN
Declare v_session_exists int;
set v_session_exists=0;
set v_session_exists=(select count(1) from atb_session_records where user_id=p_user_id );
if v_session_exists=0 then
-- create a new session, 0 means there are no older entry for the same user.
Set p_session_id= f_datetoINT((select sysdate() from dual));
insert into atb_session_records (user_id,
user_session_id
)
values
(
p_user_id,
p_session_id
);
Else
-- session already exists, return the status as -1
set p_session_id=-1;
End if ;
END
as you can see I haven't used any of the integer variable here.
I cant understand as I haven't used any int so how-come i m getting int32 conversion exception.
Please help , i m kind of on deadline on this.
thanks in advance