Layer7 API Management

 View Only
  • 1.  SCOPE_IDENTITY within JDBC Connection?

    Posted Feb 24, 2017 11:49 AM

    I am trying to insert data into a JDBC connection, which works as expected... however I'd like to ensure it returns the ID of the autogenerated primary key. 


    When I run the following query in SQL Server directly, it works as expected - however I have been unable to emulate this behavior within the API gateway. 


    Has anybody accomplished returning the data resulting from the insert, or is there another way I should be attempting this? 


    Example Query

    INSERT INTO [***].[testDatabase]


    Assertion Details


    XML Results: (this also seems strange as I specify to Fail assertion if no results return. 

    <?xml version="1.0" encoding="UTF-8"?>
    <L7j:jdbcQueryResult xmlns:L7j=""></L7j:jdbcQueryResult>

  • 2.  Re: SCOPE_IDENTITY within JDBC Connection?
    Best Answer

    Posted Feb 24, 2017 12:29 PM

    It appears that having a SELECT query in the same assertion as the INSERT query will never return any data - even with a SELECT *.


    I was able to find a workaround by doing the following: 


    1. Create a uuid and store as ${uuid}
    2. JDBC Insert Query with ${uuid} stored in the table as a value
    3.  JDBC SELECT query WHERE uuid = ${uuid}. 


    I'm hoping there is a better method, but that's as good as I could get.