Layer7 API Management

Expand all | Collapse all

Unable to call Stored Procedure via Perform JDBC Query Assertion

  • 1.  Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-17-2017 06:42 PM

    Running GW 9.2. I have an additional database I created on the GW's existing MySQL instance. I have a stored procedure in MySQL. It takes 5 input parameters. No output parameters. When I SSH into the GW and go into MySQL, I can call the stored procedure successfully. Whenever I try to call it in the GW, I get the error: "Query testing failed: Bad SQL Grammar: Function/Procedure does not exist or incorrect number of arguments for CheckAccess; expected 0 got 5." Then it goes on to show me the query that was generated, and the query looks perfect. I copy paste that same query into MySQL at the command line directly on the GW and it works perfectly. 

     

    This is what I have in the perform JDBC SQL Query Textbox:

    CALL CheckAccess 'https', 'xxxxx.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1'

     

    I've tried it with parenthesis as well - still doesn't work.

    CALL CheckAccess ('https', 'xx***.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1')

     

    I have verified that I'm using the correct JDBC connection to my custom DB. 

     

    I SSH into the GW, then at the command line I perform the following (note that I go into mysql command line as the exact same user that I have configured in my JDBC Connection):

    mysql --user=TheUsernameInMyJdbcConnection --password TheDBNameInMyJdbcConnection

    CALL CheckAccess ('https', 'xx***.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1');

     

    I get a successful response at the command line. 

     

    I am not using context variables for the values shown in the above calls (yet). I need to get it working with literals first. 

     

    I do not have the "convert variables to strings" checkbox checked, if it matters. 

     

     



  • 2.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-20-2017 10:25 PM

    Dear jeff.nibler ,

    Could you show the jdbc error message in the ssg log?

    Please try exec command to call the stored procedure. ie. EXEC CheckAccess(...)

     

    Regards,

    Mark



  • 3.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-21-2017 09:02 AM

    Thanks for the reply Mark. I have tried both "call" and "exec" - both return the same error message. The error message in the SSG log is:

     

    This is the only log entry in the ssg log for the JDBC query:

    "Perform JDBC Query" assertion failed due to: Bad SQL Grammar: Function/Procedure does not exist or incorrect number of arguments for CheckAccess; expected 0, got 5; query generated was CALL CheckAccess ('https', '******.compute-1.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1')



  • 4.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-21-2017 10:48 PM

    I tested the following,

    drop procedure if exists testsp;
    delimiter #
    create procedure testsp(IN col1 varchar(255), IN col2 varchar(255),IN col3 INT, IN col4 varchar(255),IN col5 varchar(255) )
    begin
    select col1,col2,col3,col4,col5;
    end#

     

    in jdbc query assertion, if the query str is,

    exec testsp('https', 'compute-1.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1');

    it fails,

    if the query str is,

    exec testsp('https', 'compute-1.amazonaws.com', 8443, '/HelloWorld', '192.168.1.1')

    it is successful.

    <L7j:jdbcQueryResult xmlns:L7j="http://ns.l7tech.com/2012/08/jdbc-query-result">
    <L7j:row>
    <L7j:col name="col1" type="java.lang.String">https</L7j:col>
    <L7j:col name="col2" type="java.lang.String">compute-1.amazonaws.com</L7j:col>
    <L7j:col name="col3" type="java.lang.Integer">8443</L7j:col>
    <L7j:col name="col4" type="java.lang.String">/HelloWorld</L7j:col>
    <L7j:col name="col5" type="java.lang.String">192.168.1.1</L7j:col>
    </L7j:row>
    </L7j:jdbcQueryResult>

     

    so, you cannot have ';' at the end of the query string.

     

    Regards,

    Mark



  • 5.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-21-2017 10:57 PM

    Hi Mark, yes I figured out that semicolon quirk some months ago when I was trying to get a select statement to work. But you can see in my above post that the only time I use the semi-colon is when I'm executing the stored procedure from the command line. If you look at the lines in my post where I am copy/pasting what's in the SQL Query Box of the JDBC Query assertion, there is no semi-colon. So its not that.... something else is the issue. 



  • 6.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-22-2017 01:05 AM

    interesting, when I try different things, I rebuild the procedure with something wrong, and then call the procedure in policy, it returns error. And then I correct the procedure, call the procedure in mysql client has no problem. but call the procedure in policy keep failing. I restart the gateway, and it works again.

     

    It seems the jdbc connection pool could have some kind of cache problem.

     

    Can you restart the gateway, and see if it resolve the problem?



  • 7.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 08-22-2017 07:15 PM

    Hi, thanks for the reply and suggestion. I re-started the SSG service and tried again. Same error. 



  • 8.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 11-22-2018 02:52 PM

    Jeffrey,

     

    Were you able to get this working or do you still need assistance?

     

    Sincerely,

     

    Stephen Hughes

    Broadcom Support



  • 9.  Re: Unable to call Stored Procedure via Perform JDBC Query Assertion

    Posted 02-21-2019 05:49 AM

    Seems correct answer would be:

     

    If logs shows:

     

    WARNING 4162 com.l7tech.external.assertions.jdbcquery.server.ServerJdbcQueryAssertion: 9104: "Perform JDBC Query" assertion failed due to: Bad SQL Grammar: Function/Procedure does not exist or incorrect number of arguments for ClearFailedAttempts; expected 0, got 1; query generated was CALL ClearFailedAttempts(?)

     

    Then

      • Add “noAccessToProcedureBodies=true” into JDBC connection definition (additional properties