INOUT parameter is not updated for PG

[Andrey7700]

Postgres procedure signature:

test_proc(
<par1 numeric>,
<par2 numeric>,
<par3 numeric>,
<par4_no numeric>,
<par5 numeric>,
<par6 numeric>,
<par7 character varying>,
<par8 numeric>,
<par9 numeric>,
<par10 numeric>,
<INOUT io_ret_code integer>,
<INOUT io_ret_msg character varying>
)

in test cpp file, I have this code:

        int par1 = 116;
        int par2 = 0;
        int par3 = 127;
        int par4 = 0;
        int par5 = 2;
        int par6 = 99999;
        std::string par7 = "SAPI";
        int par8= 1;
        int par9 = 1;
        int par10 = 46;

        int io_ret_code = -1;
        std::string io_ret_msg;

        procedure proc = 
           (sql.prepare << "call cbs_owner.test_proc(:par1,:par2,:par3,:par4,:par5,:par6,:par7,:par8,:par9,:par10,:io_ret_code,:io_ret_msg)", use(par1, "par1"), use(par2, "par2"),use(par3, "par3"),use(par4, "par4"), use(par5, "par5"),use(par6, "par6"), use(par7, "par7"),use(par8, "par8"), use(par9, "par9"),use(par10, "par10"),
           use(io_ret_code,"io_ret_code"), use(io_ret_msg,"io_ret_msg"));

        proc.execute(true);

also, in soci/src/backends/postgresql/statement.cpp,

query_ = "call cbs_owner.test_proc($1,$2,$3,$4,$5,$6,$7,$8,$9,$10,$11,$12)";

before this call is fired:

postgresql_result result(session_,
                PQprepare(session_.conn_, statementName.c_str(),
                    query_.c_str(), static_cast<int>(names_.size()), NULL));

After C++ call, in Postgres related table is updated based on test_proc logic, but variables io_ret_code and io_ret_msgare not changed. Not sure how I can make these parameters return value.

[zann1x]

The handling of stored procedures is very database-specific and I believe that Postgres doesn't even support INOUT parameters. For any OUT values you should try using into(...) instead of use(...).

[Andrey7700]

The handling of stored procedures is very database-specific and I believe that Postgres doesn't even support INOUT parameters. For any OUT values you should try using into(...) instead of use(...).

Postgres definitely supports it, I've tested via pgAdmin4, these params were updated.

[vadz]

I don't think in/out parameters are supported by any backend other than Oracle. We discussed this in #366 many years ago and I see that I was in favour of adding a separate inout() and implementing support for it for all, or at least several, backends, instead of allowing to modify the variables passed to use(). I think it's still probably a better idea, but this remains rather academic unless somebody plans to actually work on it.

[Andrey7700]

Thank you guys for your response! Finally I managed to get proper result, with following steps:

1. dummy values need to be passed in procedure call instead of variables (11 and null in my case), and into specifier to be used for both variables.

   (sql.prepare << "call cbs_owner.test_proc(:par1,:par2,:par3,:par4,:par5,:par6,:par7,:par8,:par9,:par10,11,null)", use(par1, "par1"), use(par2, "par2"),use(par3, "par3"),use(par4, "par4"), use(par5, "par5"),use(par6, "par6"), use(par7, "par7"),use(par8, "par8"), use(par9, "par9"),use(par10, "par10"),
           into(io_ret_code ), into(io_ret_msg ));

   2. In soci/src/backends/postgresql/statement.cpp, a couple of lines to be commented out, as it was producing "select" in front of "call" key word already passed to sql.prepare above .

      std::string postgresql_statement_backend::rewrite_for_procedure_call(
      std::string const & query)
      {
      // std::string newQuery("");
      // newQuery += query;
      return query;
      }