MySQL data get and set problem

--=_4z198t8o41s0
Content-Type: text/plain;
charset=UTF-8
Content-Description: Plaintext Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit

Dear All,

Table values are getting rollback without calling rollback.

I have a table Test with some columns in it.

Here's the Table Structure:-

Create Table TestID
(
TestID int not null,
MyID int
);

I am calling two procedures one is get the value of MyID column and one is to set the value after that.

FirstProc :-

Create procedure SP_GetMyID(TestID int)
Begin

Select MyID From Test Where TestID = TestID;

End;

Call SP_GetMyID(1);

[OUTPUT = 1]

If suppose the above proc returns me 1 as MyID then below proc will set MyID as 2 (incrementing by one)

Call SP_SetMyID(1,2);

Create Procedure SP_SetMyID(TestID int, NewMyID int)
Begin

Update Test
Set MyID = NewMyID
Where TestID = TestID;

End;

After setting the value I am again calling get procedure

Call SP_GetMyID(1);

[OUTPUT = 2]

and if after some time say after 10 seconds if I m calling SP_GetMyID again. It gives me OUTPUT as 1.

--
Regards,
Manasi Save
Artificial Machines Private Limited
manasi.save@artificialmachines.com
Ph:-9833537392

--=_4z198t8o41s0
Content-Type: multipart/related;
boundary="=_5dr28fsoji0w";
start="5dsd0vrcehvk@mail.artificialmachines.com"
Content-Transfer-Encoding: 7bit

This message is in MIME format.

--=_5dr28fsoji0w
Content-Type: text/html;
charset=UTF-8
Content-Description: HTML Version of Message
Content-Disposition: inline
Content-Transfer-Encoding: 7bit
Content-ID: 5dsd0vrcehvk@mail.artificialmachines.com

Dear All,

 

Table values are getting rollback without calling rollback.

 

I have a table Test with some columns in it.

 

Here's the Table Structure:-

 

Create Table TestID

(

TestID int not null,

MyID int

);

 

I am calling two procedures one is get the value of MyID column and one is to
set the value after that.

 

FirstProc :- 

 

Create procedure SP_GetMyID(TestID int)

Begin

 

Select MyID From Test Where TestID =
TestID;

 

End;

 

Call SP_GetMyID(1);

 

[OUTPUT = 1]

 

If suppose the above proc returns me 1 as MyID then below proc will set MyID as
2 (incrementing by one)

 

Call SP_SetMyID(1,2);

 

Create Procedure SP_SetMyID(TestID int, NewMyID int)

Begin

 


Update Test 

Set MyID  = NewMyID 

Where TestID = TestID;



End;

 

After setting the value I am again calling get procedure 

 

Call SP_GetMyID(1);

 

[OUTPUT = 2]

 

and if after some time say after 10 seconds if I m calling SP_GetMyID again. It
gives me OUTPUT  as 1.


--
Regards,
Manasi Save
Artificial Machines Private
Limited
manasi.save@artificialmachines.com
Ph:-9833537392

--=_5dr28fsoji0w--

--=_4z198t8o41s0--

Re: MySQL data get and set problem

You might have better results if you use unique identifiers:

Select MyID From Test Where TestID =3D TestID;

I'm not sure how mysql is supposed to tell the param 'TestID' from the
column name.

- michael dykman

On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save
wrote:
> Dear All,
>
> Table values are getting rollback without calling rollback.
>
> I have a table Test with some columns in it.
>
> Here's the Table Structure:-
>
> Create Table TestID
> (
> TestID int not null,
> MyID int
> );
>
> I am calling two procedures one is get the value of MyID column and one i=
s
> to set the value after that.
>
> FirstProc :-
>
> Create procedure SP_GetMyID(TestID int)
> Begin
>
> Select MyID From Test Where TestID =3D TestID;
>
> End;
>
> Call SP_GetMyID(1);
>
> [OUTPUT =3D 1]
>
> If suppose the above proc returns me 1 as MyID then below proc will set M=
yID
> as 2 (incrementing by one)
>
> Call SP_SetMyID(1,2);
>
> Create Procedure SP_SetMyID(TestID int, NewMyID int)
> Begin
>
> Update Test
> Set MyID  = NewMyID
> Where TestID =3D TestID;
>
> End;
>
> After setting the value I am again calling get procedure
>
> Call SP_GetMyID(1);
>
> [OUTPUT =3D 2]
>
> and if after some time say after 10 seconds if I m calling SP_GetMyID aga=
in.
> It gives me OUTPUT =A0as 1.
>
> --
> Regards,
> Manasi Save
> Artificial Machines Private Limited
> manasi.save@artificialmachines.com
> Ph:-9833537392
>



--=20
=A0- michael dykman
=A0- mdykman@gmail.com

=A0May the Source be with you.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL data get and set problem

Hi Michale,
 
I have made that parameter column name change. but I am unable to
understand how even after execution of update statement and commit
after that, data is getting reverted.
 
Any inputs on this will be a great help.

--
Regards,
Manasi Save

On Mon, 9 Aug 2010 14:11:19 -0400, Michael Dykman wrote:
You might have better results if you use unique identifiers:
>
> Select MyID From Test Where TestID =3D TestID;
>
> I'm not sure how mysql is supposed to tell the param 'TestID' from the
> column name.
>
> - michael dykman
>
> On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save
> wrote:
> > Dear All,
> >
> > Table values are getting rollback without calling rollback.
> >
> > I have a table Test with some columns in it.
> >
> > Here's the Table Structure:-
> >
> > Create Table TestID
> > (
> > TestID int not null,
> > MyID int
> > );
> >
> > I am calling two procedures one is get the value of MyID column
and one is
> > to set the value after that.
> >
> > FirstProc :-
> >
> > Create procedure SP_GetMyID(TestID int)
> > Begin
> >
> > Select MyID From Test Where TestID =3D TestID;
> >
> > End;
> >
> > Call SP_GetMyID(1);
> >
> > [OUTPUT =3D 1]
> >
> > If suppose the above proc returns me 1 as MyID then below proc
will set MyID
> > as 2 (incrementing by one)
> >
> > Call SP_SetMyID(1,2);
> >
> > Create Procedure SP_SetMyID(TestID int, NewMyID int)
> > Begin
> >
> > Update Test
> > Set MyID  =3D NewMyID
> > Where TestID =3D TestID;
> >
> > End;
> >
> > After setting the value I am again calling get procedure
> >
> > Call SP_GetMyID(1);
> >
> > [OUTPUT =3D 2]
> >
> > and if after some time say after 10 seconds if I m calling
SP_GetMyID again.
> > It gives me OUTPUT  as 1.
> >
> > --
> > Regards,
> > Manasi Save
> > Artificial Machines Private Limited
> > manasi.save@artificialmachines.com
> > Ph:-9833537392
> >
>
>
>
> --
>  - michael dykman
>  - mdykman@gmail.com
>
>  May the Source be with you.
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

Re: MySQL data get and set problem

--0-1266473888-1281445313=:5543
Content-Type: text/plain; charset=us-ascii

Manasi,

Your table structure doesn't show that the column TestID is unique. I believe
what Michael also suggested was that unless this column contains unique values,
you never know which row your procedure is reading.

I hope I'm making myself understood.


Regards,
Nitin




________________________________
From: Manasi Save
To: Michael Dykman
Cc: "mysql@lists.mysql.com"
Sent: Tue, August 10, 2010 10:34:37 AM
Subject: Re: MySQL data get and set problem

Hi Michale,

I have made that parameter column name change. but I am unable to
understand how even after execution of update statement and commit
after that, data is getting reverted.

Any inputs on this will be a great help.

--
Regards,
Manasi Save

On Mon, 9 Aug 2010 14:11:19 -0400, Michael Dykman wrote:
You might have better results if you use unique identifiers:
>
> Select MyID From Test Where TestID = TestID;
>
> I'm not sure how mysql is supposed to tell the param 'TestID' from the
> column name.
>
> - michael dykman
>
> On Mon, Aug 9, 2010 at 3:10 AM, Manasi Save
> wrote:
> > Dear All,
> >
> > Table values are getting rollback without calling rollback.
> >
> > I have a table Test with some columns in it.
> >
> > Here's the Table Structure:-
> >
> > Create Table TestID
> > (
> > TestID int not null,
> > MyID int
> > );
> >
> > I am calling two procedures one is get the value of MyID column
and one is
> > to set the value after that.
> >
> > FirstProc :-
> >
> > Create procedure SP_GetMyID(TestID int)
> > Begin
> >
> > Select MyID From Test Where TestID = TestID;
> >
> > End;
> >
> > Call SP_GetMyID(1);
> >
> > [OUTPUT = 1]
> >
> > If suppose the above proc returns me 1 as MyID then below proc
will set MyID
> > as 2 (incrementing by one)
> >
> > Call SP_SetMyID(1,2);
> >
> > Create Procedure SP_SetMyID(TestID int, NewMyID int)
> > Begin
> >
> > Update Test
> > Set MyID = NewMyID
> > Where TestID = TestID;
> >
> > End;
> >
> > After setting the value I am again calling get procedure
> >
> > Call SP_GetMyID(1);
> >
> > [OUTPUT = 2]
> >
> > and if after some time say after 10 seconds if I m calling
SP_GetMyID again.
> > It gives me OUTPUT as 1.
> >
> > --
> > Regards,
> > Manasi Save
> > Artificial Machines Private Limited
> > manasi.save@artificialmachines.com
> > Ph:-9833537392
> >
>
>
>
> --
> - michael dykman
> - mdykman@gmail.com
>
> May the Source be with you.
>
>


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=ntnmht@yahoo.com



--0-1266473888-1281445313=:5543--