Professional Web Applications Themes

Syntax error? - MySQL

Hi, I'm developing an ASP.NET web application with MySql 5. I have a little problem: if i create a stored procedure like this one: CREATE PROCEDURE `spProva` (out retval int, in idemployee int) BEGIN UPDATE employee e SET e.Room = 'Milan 41' WHERE e.IdEmployee = idemployee; SET retval = idemployee; END everything works fine, while if I use a textual query programmatically created: string sqlText = "UPDATE employee e SET e.Room = ?Room WHERE e.IdEmployee=?IdEmployee; "; sqlText += "SET ?retval = ?IdEmployee; "; with ?Room, ?IdEmployee and ?retval presetted parameters, an error raises: #42000You have an error in your SQL ...

  1. #1

    Default Syntax error?

    Hi,

    I'm developing an ASP.NET web application with MySql 5.
    I have a little problem: if i create a stored procedure like this one:

    CREATE PROCEDURE `spProva` (out retval int, in idemployee int)
    BEGIN
    UPDATE employee e SET e.Room = 'Milan 41' WHERE e.IdEmployee =
    idemployee;
    SET retval = idemployee;
    END

    everything works fine, while if I use a textual query programmatically
    created:

    string sqlText = "UPDATE employee e SET e.Room = ?Room
    WHERE e.IdEmployee=?IdEmployee; ";
    sqlText += "SET ?retval = ?IdEmployee; ";

    with ?Room, ?IdEmployee and ?retval presetted parameters, an error
    raises: #42000You have an error in your SQL syntax; check the manual
    that corresponds to your MySQL server version for the right syntax to
    use near '0 = 55'.

    It seems that the expression is pre-evaluated and the variables
    replaced before executing the query, and so an istruction like 'SET 0 =
    55' clearly has no sense.
    Is there a way to work around that issue, without using stored
    procedure?

    Thanks.

    Joneleth Guest

  2. #2

    Default Re: Syntax error?

    "Joneleth" <Joneleth_76hotmail.com> wrote in message
    news:1139932173.725034.108500f14g2000cwb.googlegr oups.com...
    > string sqlText = "UPDATE employee e SET e.Room = ?Room
    > WHERE e.IdEmployee=?IdEmployee; ";
    > sqlText += "SET ?retval = ?IdEmployee; ";
    ....
    > Is there a way to work around that issue, without using stored
    > procedure?
    I've never used Visual Basic. Can you tell me what the second line is
    intended to do? I assume it's copying the value of one VB variable to
    another VB variable. Why make MySQL do that? Can't you just assign one
    variable's value to another like this:
    retval = IdEmployee;

    It also seems to me that you might not need the retval variable at all,
    since its purpose in the stored procedure example is just to return a value
    from the procedure; this is not relevant if you're not using a stored
    procedure. In fact, its purpose in the stored procedure example is not
    clear, since it just returns the value that you input when you called the
    procedure.

    Regards,
    Bill K.


    Bill Karwin Guest

  3. #3

    Default Re: Syntax error?

    Bill Karwin wrote:
    > > Is there a way to work around that issue, without using stored
    > > procedure?
    >
    > I've never used Visual Basic.
    It's not VB but C# :)
    > It also seems to me that you might not need the retval variable at all,
    In the case of that small example you're right, the retval is quite
    useless.
    However, if you're going to use an insert statement instead of an
    update, for example,
    you might need to retrieve the last inserted ID in the same transaction
    to avoid potential (concurrency) errors.
    Since I'm used to write query with SqlServer, I've never had such a
    problem, even using textual query in place of stored procedures.
    > In fact, its purpose in the stored procedure example is not
    > clear, since it just returns the value that you input when you called the
    > procedure.
    yeah, it was just an example :)

    Regards,
    J.

    Joneleth Guest

Similar Threads

  1. Replies: 6
    Last Post: September 2nd, 01:22 PM
  2. Syntax error
    By middletree in forum Macromedia ColdFusion
    Replies: 11
    Last Post: May 2nd, 03:10 AM
  3. Help on Syntax error please
    By Gilles in forum MySQL
    Replies: 0
    Last Post: April 28th, 01:51 PM
  4. error : syntax error at or near $1 for over select rows
    By vinita bansal in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: December 27th, 06:51 PM
  5. syntax error ???
    By Ben in forum PHP Development
    Replies: 1
    Last Post: September 30th, 10:31 PM

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