Professional Web Applications Themes

newbie: I got problems with this SP - MySQL

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 ...

  1. #1

    Default newbie: I got problems with this SP

    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;


    Jeff Guest

  2. #2

    Default Re: newbie: I got problems with this SP

    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" <com.NOSPAM> wrote in message
    news:com... 



    Sean Guest

  3. #3

    Default Re: newbie: I got problems with this SP

    See my repsond at the bottom of this post

    "Sean" <sean.anderson[nospam]oakleafgroup.biz> wrote in message
    news:skynet.co.uk... 
    >
    >
    >[/ref]

    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


    Jeff Guest

Similar Threads

  1. newbie problems with phpinfo()
    By Pepebuho in forum PHP Development
    Replies: 8
    Last Post: July 7th, 12:32 PM
  2. Newbie problems
    By Bryan Dickerson in forum ASP.NET Web Services
    Replies: 0
    Last Post: April 21st, 01:53 PM
  3. Newbie problems using HTTP_USER_AGENT
    By toedipper in forum PHP Development
    Replies: 4
    Last Post: February 8th, 07:42 PM
  4. Newbie problems with > in a string
    By Albert Browne in forum PERL Beginners
    Replies: 3
    Last Post: December 27th, 04:05 PM
  5. Newbie Cropping Problems
    By Nancy_S@adobeforums.com in forum Adobe Photoshop Elements
    Replies: 2
    Last Post: November 7th, 03:58 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139