Hi Stephen,
I've run into a similar problem when I first tried to use out/inout
parameters in PostgreSQL. The solution turns out to be simple: any OUT
or INOUT parameter doesn't actually act like an output parameter, but
instead it defines an extra *return value* for your procedure. The
return type of your function is therefore a record consisting of the new
value of all OUT and INOUT parameters of your procedure.
Therefore, you should do something like:
Result r = t->prepared(queryname)(id).exec();
id = r[0].as<int>();
And things will work as expected. Note that this is untested code, but
even if it doesn't work you probably get the idea!
Cheers,
Bart
Stephen Cossgrove wrote:
> Hi,
>
>
>
> I am trying to convince my boss to allow me to user postgres and pqxx on
> linux as the platform for creating a new application for the health
> industry. In doing this I am exploring the libpqxx library and have come
> to a stumbling block that I think is just my understanding of the
> library. I will list the scene below:
>
>
>
> I am currently trying to change the value of an inout parameter inside a
> function and read its changed value but am having trouble.
>
>
>
> I was wondering whether you could look at my code and help me out?
>
>
>
> The problem is that the value never changes. I have listed the function
> code below as well as the libqxx c++ source.
>
>
>
>
>
> Stephen Cossgrove
>
>
>
> Here is the postgres function:
>
> CREATE OR REPLACE FUNCTION public.insert_gperson (
>
> INOUT id integer
>
> )
>
> AS
>
> $$
>
> begin
>
> /* Get the Next Person ID */
>
> id = nextval('"testing_seq"'::regclass);
>
>
>
> /* Insert the record */
>
> insert into "GPerson" ( "GPersonID", "DOB" ) values ( id, Now());
>
> end $$ LANGUAGE 'plpgsql'
>
> VOLATILE
>
> CALLED ON NULL INPUT
>
> SECURITY INVOKER;
>
>
>
>
>
> Here is the c++ source code:
>
> // Create the SQL Query
>
> // string query = "exec insert_gperson($1)";
>
> string query = "select insert_gperson($1)";
>
> string queryname = "insert_gperson";
>
>
>
> // Prepare the differing parameters
>
>
>
> // Prepare the statement
>
> C.prepare(queryname, query)
>
> ("integer", pqxx::prepare::treat_direct);
>
>
>
> // Create a new Transaction
>
> cout << "Creating Query...\n";
>
> work *t;
>
> t = new work(C, queryname);
>
>
>
> // create the return id;
>
> int id = 0;
>
>
>
> // Set the prepare statement
>
> cout << "Preparing statement....\n";
>
> t->prepared(queryname)(id).exec();
>
>
>
> // Commit the transaction
>
> cout << "Commiting .... \n";
>
> t->commit();
>
>
>
> // Delete the transaction
>
> delete t;
>
>
>
> // Unprepare the Command
>
> C.unprepare(queryname);
>
>
>
> // debug the new gperson id
>
> cout << "New GPersonID = [" << id << "]\n";
>
>
>
>
>
>
> ------------------------------------------------------------------------
>
> _______________________________________________
> Libpqxx-general mailing list
> Libpqxx-general@gborg.postgresql.org
> http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
_______________________________________________
Libpqxx-general mailing list
Libpqxx-general@gborg.postgresql.org
http://gborg.postgresql.org/mailman/listinfo/libpqxx-general
