CODEWITHSUNDEEP
c++postgresql
Jockey
Jockey

Reputation: 31

Can anyone let me know how to call a postgres db function & procedure in c++ using with pqxx library?

I'm working with C++ database application we are looking to migrate oracle to Postgre so need help to call Postgre db function & procedure . I'm using pqxx library to connect & transaction data with database .using pqxx library I'm able to connect and insert and update data in to database but un able to call db function and procedure in my C++ code . Can anyone please help me on this ?

I have a sample function -> ec_special_item$bhutan_getitem

code started below ->

CREATE OR REPLACE FUNCTION public."ec_special_item$bhutan_getitem"(
INOUT ai_presentingbank text,
ai_bankroutingnum integer,
OUT ao_numerrorid integer,
OUT ao_strerrordesc text,
OUT ao_item_state text)
    RETURNS record
    LANGUAGE 'plpgsql'
    COST 100
    VOLATILE PARALLEL UNSAFE
AS $BODY$
DECLARE
  
BEGIN
        ai_presentingbank:='12345678';
        INSERT INTO public.Tyre(item_type_code, description)VALUES (ai_bankroutingnum, 'outward');
        
        SELECT
            UPPER(description) AS Tyre into ao_item_state
            FROM public.Tyre
            WHERE LOWER(description)  = 'inward'
        ;
    ao_numerrorid := 50;
    ao_strerrordesc := 'PASS';
     RETURN ;
    EXCEPTION
        WHEN others THEN
            
            ao_numerrorid := 0;
            ao_strerrordesc := 'FAIL';
        
            RETURN ;
END;
$BODY$;

Upvotes: 1

Views: 459

Answers (1)

Botje
Botje

Reputation: 30830

The PostgreSQL documentation on the CALL command states:

If the procedure has any output parameters, then a result row will be returned, containing the values of those parameters.

So you should treat the CALL expression like a SELECT and process the single resulting row.

If you adapt the quick example, it should look something like:

pqxx::work txn{c};

pqxx::result r{txn.exec("CALL ec_special_item$bhutan_getitem('some_bank', 12345)"};
for (auto row: r)
    std::cout
    << "\nai_presentingbank: " << row["ai_presentingbank"].c_str()
    << "\nao_numerrorid: " << row["ao_numerrorid"].as<int>()
    << "\nao_strerrordesc: " << row["ao_strerrordesc"].c_str()
    << "\nao_item_state: " << row["ao_item_state"].c_str()
    << std::endl;
}

txn.commit();

Upvotes: 2

Related Questions