Hey

I'm about to create my first stored procedure in MySql. This is against a
MySql 5.0 database:

below is the stored procedure I'm having problems with:

Basically this line is causing me problems:
SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE field1;
Only one record exist in the Property table and for this record the Field1
has the value 0.62500500 116947212145b4ba79989976.06599366. And during
testing of this SP I give field1 the value '0.71875300
116947384145b4c131af7ce6.57316889'. Here is the problem... Field1 and field1
has different values, but SELECT count(*) INTO counter FROM Property WHERE
Field1 LIKE field1; gives counter the value 1.. counter should have got the
value 0 instead of 1.

I tryed with no luck to solve this replacing LIKE with =, but that didn't
help

Any suggestions on how to solve this?

***** source code **********
CREATE PROCEDURE SaveProperty
(
id int unsigned,
field1 varchar(250),
field2 int unsigned
)
BEGIN
DECLARE counter int;
IF (id = 0) THEN
SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE field1;
IF (counter=0) THEN
INSERT INTO Property (Field1, Field2) VALUES (field1, field2);
END IF;
ELSE
UPDATE Property
Set Field1 = field1, Field2 = field2 WHERE Id = id;
END IF;
END;

To the best of my knowledge

WHERE field1 LIKE field2

is the same as

WHERE field1 = field2

because you've not used anything to pattern match, as:

LIKE '%aaa' ... ends with 'aaa'
LIKE 'bbb%' ... starts with 'bbb'
LIKE '%ccc%' ... contains 'ccc'

Okay, are your fieldnames realy Field1 and field1 ... yikes, that could
cause some confusion.

What is the fundamental basic of what you trying to do?


"Jeff" <EMAIL REMOVED> wrote in message
news:EMAIL REMOVED...
> Hey
>
> I'm about to create my first stored procedure in MySql. This is against a
> MySql 5.0 database:
>
> below is the stored procedure I'm having problems with:
>
> Basically this line is causing me problems:
> SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE field1;
> Only one record exist in the Property table and for this record the Field1
> has the value 0.62500500 116947212145b4ba79989976.06599366. And during
> testing of this SP I give field1 the value '0.71875300
> 116947384145b4c131af7ce6.57316889'. Here is the problem... Field1 and
> field1 has different values, but SELECT count(*) INTO counter FROM
> Property WHERE Field1 LIKE field1; gives counter the value 1.. counter
> should have got the value 0 instead of 1.
>
> I tryed with no luck to solve this replacing LIKE with =, but that didn't
> help
>
> Any suggestions on how to solve this?
>
> ***** source code **********
> CREATE PROCEDURE SaveProperty
> (
> id int unsigned,
> field1 varchar(250),
> field2 int unsigned
> )
> BEGIN
> DECLARE counter int;
> IF (id = 0) THEN
> SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE field1;
> IF (counter=0) THEN
> INSERT INTO Property (Field1, Field2) VALUES (field1, field2);
> END IF;
> ELSE
> UPDATE Property
> Set Field1 = field1, Field2 = field2 WHERE Id = id;
> END IF;
> END;
>
>

See my repsond at the bottom of this post

"Sean" <sean.anderson@[nospam]oakleafgroup.biz> wrote in message
news:EMAIL REMOVED...
> To the best of my knowledge
>
> WHERE field1 LIKE field2
>
> is the same as
>
> WHERE field1 = field2
>
> because you've not used anything to pattern match, as:
>
> LIKE '%aaa' ... ends with 'aaa'
> LIKE 'bbb%' ... starts with 'bbb'
> LIKE '%ccc%' ... contains 'ccc'
>
> Okay, are your fieldnames realy Field1 and field1 ... yikes, that could
> cause some confusion.
>
> What is the fundamental basic of what you trying to do?
>
>
> "Jeff" <EMAIL REMOVED> wrote in message
> news:EMAIL REMOVED...
>> Hey
>>
>> I'm about to create my first stored procedure in MySql. This is against a
>> MySql 5.0 database:
>>
>> below is the stored procedure I'm having problems with:
>>
>> Basically this line is causing me problems:
>> SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE field1;
>> Only one record exist in the Property table and for this record the
>> Field1 has the value 0.62500500 116947212145b4ba79989976.06599366. And
>> during testing of this SP I give field1 the value '0.71875300
>> 116947384145b4c131af7ce6.57316889'. Here is the problem... Field1 and
>> field1 has different values, but SELECT count(*) INTO counter FROM
>> Property WHERE Field1 LIKE field1; gives counter the value 1.. counter
>> should have got the value 0 instead of 1.
>>
>> I tryed with no luck to solve this replacing LIKE with =, but that didn't
>> help
>>
>> Any suggestions on how to solve this?
>>
>> ***** source code **********
>> CREATE PROCEDURE SaveProperty
>> (
>> id int unsigned,
>> field1 varchar(250),
>> field2 int unsigned
>> )
>> BEGIN
>> DECLARE counter int;
>> IF (id = 0) THEN
>> SELECT count(*) INTO counter FROM Property WHERE Field1 LIKE
>> field1;
>> IF (counter=0) THEN
>> INSERT INTO Property (Field1, Field2) VALUES (field1, field2);
>> END IF;
>> ELSE
>> UPDATE Property
>> Set Field1 = field1, Field2 = field2 WHERE Id = id;
>> END IF;
>> END;
>>
>>
>
>
>

Hey

Thanks for that tip, it looks like sql isn't case sensitive...

The actual fields were like these:
SELECT count(*) INTO counter FROM Property WHERE UniqueId LIKE uniqueId;

I changed it to:
SELECT count(*) INTO counter FROM Property WHERE UniqueId LIKE varUniqueId;

and problem is solved....


By the way, can I use parameter names like these(@ as a prefix)??
SELECT count(*) INTO counter FROM Property WHERE UniqueId LIKE @uniqueId;

Jeff