How to call a procedure with out parameter?

[Thiago Pimenta]

Hello everyone,

Does anyone know how to create a procedure call in gelscript with input and output parameters?

I am in an on demand environment with Postgres database.

Log error:

 Exception Trace: Error: org.apache.commons.jelly.JellyTagException: null:11:94: <sql:query> call CMN_AUTONUM_GET_NEXT_SP('project'::VARCHAR, 'unique_code'::VARCHAR, 'NEC'::VARCHAR, null::VARCHAR, null::VARCHAR, null::VARCHAR, 0::BIGINT, ?::VARCHAR): ERROR: syntax error at or near "::"

Code:

<gel:script xmlns:gel="jelly:com.niku.union.gel.GELTagLibrary" xmlns:core="jelly:core" xmlns:soap="jelly:com.niku.union.gel.SOAPTagLibrary" xmlns:soapenv="http://schemas.xmlsoap.org/soap/envelope/" xmlns:sql="jelly:sql" xmlns:xog="http://www.niku.com/xog" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
  <gel:log level="debug">START ####</gel:log>

  <core:catch var="connFail">
    <gel:setDataSource dbId="niku" var="interna"/>
  </core:catch>
  <core:if test="${ connFail != null }">
    <gel:log level="debug">Sem conexão a uma base de dados.</gel:log>
  </core:if>
<core:catch var="erro">
		
			<sql:query dataSource="${interna}" escapeText="false" var="auto_num_sp">
			call CMN_AUTONUM_GET_NEXT_SP('project'::VARCHAR, 'unique_code'::VARCHAR, 'PTC'::VARCHAR, null::VARCHAR, null::VARCHAR, null::VARCHAR, 0::BIGINT, ?::VARCHAR)
			</sql:query>
			<!-- How to obtain the value of the procedure's return parameter? -->
            <gel:log level="debug">code_prj: ${nextId} </gel:log>
			
</core:catch>
  <core:choose>
    <core:when test="${erro != null}">
      <gel:log level="debug">Error: ${erro}</gel:log>
    </core:when>
    <core:otherwise>
      <gel:log level="debug">OPER Successfully.</gel:log>
    </core:otherwise>
  </core:choose>
  <gel:log level="debug"> ####################### Final ############################ </gel:log>
</gel:script>			​

Thanks in advance
------------------------------
Analista de Sistemas
Ágila Tecnologia da Informação
------------------------------

[Nick Darlington]

It didn't work when I last tried it, but this was long before 15.8.x so it may have changed.

1) Postgres allows its procedures to be called using traditional "callable" statement calls, with or without the JDBC CALL xyz() style (no curly brackets), or with a ? = xyz() format.
2) However, Postgres also doesn't strongly differentiate between stored procedures and functions, so you can capture the results of a stored procedure's "out" parameter as a recordset or relationship.
3) Historically this has to do with a difference in direction between the Postgres core team and the Postgres JDBC driver team preventing "callable statements" from working, so the "call it like a function" is something of a workaround and it executes internally using "prepared statement" instead.

So for the most part you're doing it right by using the sql:query tag but instead of an actual out parameter with a ? you should populate it with a dummy value, as if it was NOT an out parameter.

Then examine your query results in the ${auto_num_sp} variable as if it were rows and columns for the answer / output like a regular query.

If that doesn't work, then I think it's still not supported.

[Thiago Pimenta]

Hi Nick,

Thank you very much for the tip. It was necessary to use only a dummy value in the return parameter and use the recordset itself to return the new code, using the procedure return parameter name. (p_next_id).

------------------------------
Analista de Sistemas
Ágila Tecnologia da Informação
------------------------------