Professional Web Applications Themes

Problem with create procedure - MySQL

Here is what I tried. CREATE PROCEDURE 'sr19'.sp_find_food( IN search_string varchar(255) ) LANGUAGE SQL BEGIN DECLARE ss VARCHAR(257); SET ss = CONCAT('%',search_string,'%'); SELECT NDB_No,Long_Desc FROM 'sr19'.food_desc WHERE Long_Desc LIKE ss; END It should be onvious what I am trying to do. I want a map of id numbers (NDB_No) to food names (Long_Desc) for which all of the food names contain the string passed to the procedure in search_string. I do not care where the search string appears in the food name, only that it is there. I am doing the search in a table in the database called sr19, ...

  1. #1

    Default Problem with create procedure

    Here is what I tried.

    CREATE PROCEDURE 'sr19'.sp_find_food(
    IN search_string varchar(255)
    ) LANGUAGE SQL
    BEGIN
    DECLARE ss VARCHAR(257);
    SET ss = CONCAT('%',search_string,'%');
    SELECT NDB_No,Long_Desc FROM 'sr19'.food_desc WHERE Long_Desc LIKE
    ss;
    END


    It should be onvious what I am trying to do. I want a map of id
    numbers (NDB_No) to food names (Long_Desc) for which all of the food
    names contain the string passed to the procedure in search_string. I
    do not care where the search string appears in the food name, only
    that it is there. I am doing the search in a table in the database
    called sr19, and I am trying to attach the procedure to the same
    database.

    The error message is relatively useless in that all it says is that
    there is an error in the SQL syntax used for the select statement.

    Does anyone see my error? I am puzzled since the following works as
    desired:

    SELECT NDB_No,Long_Desc FROM 'sr19'.food_desc WHERE Long_Desc LIKE
    '%Butter%';

    Thisw statement returns 151 records.

    Any ideas?

    Ted

    Ted Guest

  2. #2

    Default Re: Problem with create procedure

    > Here is what I tried. 

    ....
     

    I think you meant backticks for the sr19 table. But it is not a reserved
    word, so omitting the single quotes would be more clear.

    Best regards
    --
    Willem Bogaerts

    Application smith
    Kratz B.V.
    http://www.kratz.nl/
    Willem Guest

  3. #3

    Default Re: Problem with create procedure

    On Feb 15, 3:52 am, Willem Bogaerts
    <maardanzonderditstuk.nl> wrote: 

    >
    > ...

    >
    > I think you meant backticks for the sr19 table. But it is not a reserved
    > word, so omitting the single quotes would be more clear.
    >
    > Best regards
    > --
    > Willem Bogaerts
    >
    > Application smith
    > Kratz B.V.http://www.kratz.nl/[/ref]

    Thanks Willem

    I get the right results when, in MySQL Browser, I execute the
    following:

    CALL sr19.sp_find_food('Butter');

    In this database, the resultset from this query should contain 151
    records. The above statement has that many records.

    What is not clear to me is whether these results are displayed to
    standard output from within the procedure or returned to the client
    application for the latter to write the output. If I invoke this
    procedure from an ASP.NET or JSF page, or a Java servlet using JDBC,
    will the resultset be put in the appropriate datasource object or
    disappear into never-never land?

    Thanks

    Ted

    Ted Guest

  4. #4

    Default Re: Problem with create procedure

    On Feb 15, 10:31 am, "Ted" <com> wrote: [/ref]
    > [/ref]

    > [/ref]



    >
    > Thanks Willem
    >
    > I get the right results when, in MySQL Browser, I execute the
    > following:
    >
    > CALL sr19.sp_find_food('Butter');
    >
    > In this database, the resultset from this query should contain 151
    > records. The above statement has that many records.
    >
    > What is not clear to me is whether these results are displayed to
    > standard output from within the procedure or returned to the client
    > application for the latter to write the output. If I invoke this
    > procedure from an ASP.NET or JSF page, or a Java servlet using JDBC,
    > will the resultset be put in the appropriate datasource object or
    > disappear into never-never land?
    >
    > Thanks
    >
    > Ted- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    Well, with about an hour of trial and error, it seems that although
    the stored procedure seems to work from within MySQL Broswer, it
    definitely does not work from an ASP.NET2 page. The statement gets
    executed, MS Visual Studio knows what columns are present in the
    resultset, but the resultset itself seems to disappear into never-
    never land. :-(

    How do I return the resultset to the calling client code?

    Ted

    Ted Guest

  5. #5

    Default Re: Problem with create procedure

    On Feb 15, 11:20 am, "Ted" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]








    >
    > Well, with about an hour of trial and error, it seems that although
    > the stored procedure seems to work from within MySQL Broswer, it
    > definitely does not work from an ASP.NET2 page. The statement gets
    > executed, MS Visual Studio knows what columns are present in the
    > resultset, but the resultset itself seems to disappear into never-
    > never land. :-(
    >
    > How do I return the resultset to the calling client code?
    >
    > Ted- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    With further experimentation, I am wondering if the problem might be
    with the MySQL .NET provider since even the strategy I have used
    successfully with MS SQL Server 2005, relating the text of a textfield
    to the poarameter of a parameterized select statement to the items in
    a dropdownlistbox has not worked. I do not yet know if I have missed
    something simple or if there is inevitably a problem using MySQL with
    an ASP.NET2 application.

    Ted

    Ted Guest

  6. #6

    Default Re: Problem with create procedure

    Ted wrote: 
    >> Well, with about an hour of trial and error, it seems that although
    >> the stored procedure seems to work from within MySQL Broswer, it
    >> definitely does not work from an ASP.NET2 page. The statement gets
    >> executed, MS Visual Studio knows what columns are present in the
    >> resultset, but the resultset itself seems to disappear into never-
    >> never land. :-(
    >>
    >> How do I return the resultset to the calling client code?
    >>
    >> Ted- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > With further experimentation, I am wondering if the problem might be
    > with the MySQL .NET provider since even the strategy I have used
    > successfully with MS SQL Server 2005, relating the text of a textfield
    > to the poarameter of a parameterized select statement to the items in
    > a dropdownlistbox has not worked. I do not yet know if I have missed
    > something simple or if there is inevitably a problem using MySQL with
    > an ASP.NET2 application.
    >
    > Ted
    >[/ref]

    Ted,

    It's impossible to tell. All you've shown us is your Create Procedure
    code. You've shown us nothing about any code you're using to call it.

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  7. #7

    Default Re: Problem with create procedure

    On Feb 15, 8:03 pm, Jerry Stuckle <net> wrote: [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > Ted,
    >
    > It's impossible to tell. All you've shown us is your Create Procedure
    > code. You've shown us nothing about any code you're using to call it.
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    OK. Here is some test ASP.NET 2 code.

    <asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
    SelectCommand="CALL sp_find_food(ss)">
    <SelectParameters>
    <asp:ControlParameter ControlID="search_string" Name="ss"
    PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
    SelectCommand="SELECT NDB_No,Long_Desc FROM sr19.food_des
    WHERE Long_Desc LIKE ss">
    <SelectParameters>
    <asp:ControlParameter ControlID="search_string" Name="ss"
    PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <table width=100%>
    <tr>
    <td style="width: 30%">Enter part of a food name</td>
    <td style="width: 70%">
    <asp:TextBox ID="search_string" runat="server"
    AutoPostBack="True"></asp:TextBox></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td><td style="width:
    70%">
    <asp:DropDownList ID="DropDownList1" runat="server"
    AutoPostBack="True" DataSourceID="SqlDataSource1"
    DataTextField="Long_Desc" DataValueField="NDB_No"
    Width="100%">
    </asp:DropDownList></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td><td style="width:
    70%">
    <asp:DropDownList ID="DropDownList2" runat="server"
    AutoPostBack="True" DataSourceID="SqlDataSource2"
    DataTextField="Long_Desc" DataValueField="NDB_No"
    Width="100%">
    </asp:DropDownList></td>
    </tr>
    </table><asp:SqlDataSource ID="SqlDataSource1" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
    SelectCommand="CALL sp_find_food(ss)">
    <SelectParameters>
    <asp:ControlParameter ControlID="search_string" Name="ss"
    PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <asp:SqlDataSource ID="SqlDataSource2" runat="server"
    ConnectionString="<%$ ConnectionStrings:sr19ConnectionString %>"
    ProviderName="<%$
    ConnectionStrings:sr19ConnectionString.ProviderNam e %>"
    SelectCommand="SELECT NDB_No,Long_Desc FROM sr19.food_des
    WHERE Long_Desc LIKE ss">
    <SelectParameters>
    <asp:ControlParameter ControlID="search_string" Name="ss"
    PropertyName="Text" />
    </SelectParameters>
    </asp:SqlDataSource>
    <table width=100%>
    <tr>
    <td style="width: 30%">Enter part of a food name</td>
    <td style="width: 70%">
    <asp:TextBox ID="search_string" runat="server"
    AutoPostBack="True"></asp:TextBox></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td><td style="width:
    70%">
    <asp:DropDownList ID="DropDownList1" runat="server"
    AutoPostBack="True" DataSourceID="SqlDataSource1"
    DataTextField="Long_Desc" DataValueField="NDB_No"
    Width="100%">
    </asp:DropDownList></td>
    </tr>
    <tr>
    <td style="width: 30%">select a food</td><td style="width:
    70%">
    <asp:DropDownList ID="DropDownList2" runat="server"
    AutoPostBack="True" DataSourceID="SqlDataSource2"
    DataTextField="Long_Desc" DataValueField="NDB_No"
    Width="100%">
    </asp:DropDownList></td>
    </tr>
    </table>

    When I do something similar, with data from an MS SQL Server 2005
    database, changing the value in one control results in the items in
    the other control (a drop down list) are changed. The principle
    difference is that the code for data from MS SQL Server involved
    testing for equality of integers while this code looks for records in
    which one column contains a string that itself contains the string
    provided in the parameter.

    In the sample ASP.NET 2 code above, the controls are properly created,
    but the dropdown lists never get any of the data that ought to have
    been returned by the SQL statement.

    Thanks,

    Ted

    Ted Guest

  8. #8

    Default Re: Problem with create procedure

    Ted wrote:

    <snipped good stuff to save electrons>
     

    Hi, Ted,

    I don't do ASP.NET, but I (think I was) able to understand your code.

    A couple of things:

    In your stored proc, 'ss' should be in single quotes since it's text
    (sorry - missed that before).

    Also, in your SELECT statement for SqlDataSource2, what would be in ss?
    You're doing a LIKE statement looking for text within a column of
    text, so it should be something like

    '%i want this%'

    Also, since it's a string, SQL indicated it should be within single
    quotes, as indicated above.

    Also, I don't see where you are checking for any errors from MySQL. I
    don't know how to do it in ASP.NET - but you should always be checking
    the results for any errors. In PHP, for instance, the result for a
    query is false when it fails, and mysql_error() retrieves the error text.

    Other than that I don't see anything wrong with your code. And since
    you've gotten similar to work with integers, it may just be the missing
    single quotes.

    One of these days I'm going to have to learn ASP.NET. But I'm still
    stuck maintaining some VBScript sites :-)

    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  9. #9

    Default Re: Problem with create procedure

    On Feb 16, 9:21 am, Jerry Stuckle <net> wrote: 
    Ah, OK. I'll check that when I get back.
     

    ss will contain a string.
     
    ASP.NET 2 (I don't know about earlier versions of .NET) has a number
    of default validators and supports creation of custom validators. I
    haven't shown them because that will be something I develop and test
    after I get the basic connectivity working. I have used C++ and Java
    as my main programming languages for over 15 years, and I've used a
    number of different application frameworks and UI libraries (curses,
    MFC, Borland's VCL, &c.) and I am still learning .NET myself
    (leveraging obvious similarities with the other libraries/frameworks
    I've used). It would be so easy doing this manually in Java with
    JDBC! Maybe that is just an impression due to comparing what I have
    done so often in the past to what I am trying to learn how to do.
     
    If you believe the hype from MS, it is worth it. The references I
    have for it make it seem like the greatest thing since sliced bread
    (but then, I would kill anyone who shoved sliced bread into my
    computer ;-). These references DO make a number of compelling
    arguments about the benefits of ASP.NET 2. But I am not yet
    convinced, but maybe after I get as comfortable with it as I am with
    MFC, VCL, Java and all its wonderful libraries, ...

    I don't envy you having to work with VBScript. Too bad you can't
    upgrade them to at least use Perl or PHP for the ASP pages. Yes, I
    have taken a look at PHP, and have toyed with it a bit, but I haven't
    had the time to work seriously with it. Thus, if time is of the
    essence and I need powerful scipting, I tend to resort to Perl (I like
    Perl even though I find some of it quite obtuse, but it is hard to
    beat C++ ;-).

    Thanks again,

    Ted

    Ted Guest

  10. #10

    Default Re: Problem with create procedure

    On Feb 16, 9:21 am, Jerry Stuckle <net> wrote: 



    >
    > Hi, Ted,
    >
    > I don't do ASP.NET, but I (think I was) able to understand your code.
    >
    > A couple of things:
    >
    > In your stored proc, 'ss' should be in single quotes since it's text
    > (sorry - missed that before).
    >
    > Also, in your SELECT statement for SqlDataSource2, what would be in ss?
    > You're doing a LIKE statement looking for text within a column of
    > text, so it should be something like
    >
    > '%i want this%'
    >
    > Also, since it's a string, SQL indicated it should be within single
    > quotes, as indicated above.
    >
    > Also, I don't see where you are checking for any errors from MySQL. I
    > don't know how to do it in ASP.NET - but you should always be checking
    > the results for any errors. In PHP, for instance, the result for a
    > query is false when it fails, and mysql_error() retrieves the error text.
    >
    > Other than that I don't see anything wrong with your code. And since
    > you've gotten similar to work with integers, it may just be the missing
    > single quotes.
    >
    > One of these days I'm going to have to learn ASP.NET. But I'm still
    > stuck maintaining some VBScript sites :-)
    >
    > --
    > ==================
    > Remove the "x" from my email address
    > Jerry Stuckle
    > JDS Computer Training Corp.
    > net
    > ==================- Hide quoted text -
    >
    > - Show quoted text -[/ref]

    OK, I tried enclosing ss in single quotes, and it had no effect.

    I am a bit puzzled now. On rereading the MySQL manual, I see 'CALL'
    returns the number of records. But the manual also says "MySQL
    supports the very useful extension that allows the use of regular
    SELECT statements (that is, without using cursors or local variables)
    inside a stored procedure. The result set of such a query is simply
    sent directly to the client." HOW? How can a CALL statement return
    both the number of records and a result set? And if this is an
    "extension", do any other RDBMS support it, and more importantly, do
    libraries or frameworks such as JDBC or ADO.NET2 know anything about
    it?

    Thanks

    Ted

    Ted Guest

  11. #11

    Default Re: Problem with create procedure

    Ted wrote: 
    >> Hi, Ted,
    >>
    >> I don't do ASP.NET, but I (think I was) able to understand your code.
    >>
    >> A couple of things:
    >>
    >> In your stored proc, 'ss' should be in single quotes since it's text
    >> (sorry - missed that before).
    >>
    >> Also, in your SELECT statement for SqlDataSource2, what would be in ss?
    >> You're doing a LIKE statement looking for text within a column of
    >> text, so it should be something like
    >>
    >> '%i want this%'
    >>
    >> Also, since it's a string, SQL indicated it should be within single
    >> quotes, as indicated above.
    >>
    >> Also, I don't see where you are checking for any errors from MySQL. I
    >> don't know how to do it in ASP.NET - but you should always be checking
    >> the results for any errors. In PHP, for instance, the result for a
    >> query is false when it fails, and mysql_error() retrieves the error text.
    >>
    >> Other than that I don't see anything wrong with your code. And since
    >> you've gotten similar to work with integers, it may just be the missing
    >> single quotes.
    >>
    >> One of these days I'm going to have to learn ASP.NET. But I'm still
    >> stuck maintaining some VBScript sites :-)
    >>
    >> --
    >> ==================
    >> Remove the "x" from my email address
    >> Jerry Stuckle
    >> JDS Computer Training Corp.
    >> net
    >> ==================- Hide quoted text -
    >>
    >> - Show quoted text -[/ref]
    >
    > OK, I tried enclosing ss in single quotes, and it had no effect.
    >
    > I am a bit puzzled now. On rereading the MySQL manual, I see 'CALL'
    > returns the number of records. But the manual also says "MySQL
    > supports the very useful extension that allows the use of regular
    > SELECT statements (that is, without using cursors or local variables)
    > inside a stored procedure. The result set of such a query is simply
    > sent directly to the client." HOW? How can a CALL statement return
    > both the number of records and a result set? And if this is an
    > "extension", do any other RDBMS support it, and more importantly, do
    > libraries or frameworks such as JDBC or ADO.NET2 know anything about
    > it?
    >
    > Thanks
    >
    > Ted
    >[/ref]

    Ted,

    OK, I was just trying to see if there were any errors first.

    To return values from a SP, you also need to use one or more OUT
    parameters. See

    <http://dev.mysql.com/doc/refman/5.1/en/create-procedure.html>

    And this is pretty standard SQL. I'm not sure exactly how it's
    implemented in other databases; the only one I've used very much has
    been DB2 and that supports a similar syntax (not quite the same, but close).

    Did you check for any errors from the MySQL calls? That's the most
    important thing. Any error messages will really help determine the
    problem(s).


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Create Stored Procedure
    By Snowcrash_10 in forum Coldfusion Database Access
    Replies: 3
    Last Post: August 18th, 02:38 PM
  2. Replies: 9
    Last Post: September 30th, 06:03 PM
  3. Create a View from a Stored Procedure
    By Stomper in forum Microsoft SQL / MS SQL Server
    Replies: 2
    Last Post: July 16th, 05:21 AM
  4. How to create table by store procedure in Oracle9i
    By SouthVN in forum Oracle Server
    Replies: 2
    Last Post: July 8th, 12:16 PM
  5. Re-create stored procedure in Oracle 8.0.5
    By Dusan Bolek in forum Oracle Server
    Replies: 0
    Last Post: January 10th, 07:57 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