Thread: [BUGS] Error in SPI_execute_plan (when inserting JSON from C code first { simboll is lost)

Hello 

i've got table with json field.

create table json_test(id serial,Num integer,Obj json);


i'm trying to insert from C function some json data.
i'm using SPI_execute_plan function to do this.

FULL CODE EXAMPLE
###################################################
###################################################

#include <string.h>
#include "postgres.h"
#include "fmgr.h"
#include "executor/spi.h"
#include "utils/geo_decls.h"
#include "utils/builtins.h"
#include "funcapi.h"

SPIPlanPtr m_JsonInsertPlan = NULL;

#ifdef PG_MODULE_MAGIC
PG_MODULE_MAGIC;
#endif

#ifdef _WIN32

PGDLLEXPORT Datum JsonError(PG_FUNCTION_ARGS);

#endif

PG_FUNCTION_INFO_V1(JsonError);

// create table json_test(id serial,Num integer,Obj json);
void InitPlans(){
if (m_JsonInsertPlan == NULL){
Oid * Types = (Oid *)palloc(2 * sizeof(Oid));
Types[0] = 23; //Int32
Types[1] = 114; //JSON

m_JsonInsertPlan = SPI_saveplan(SPI_prepare("insert into json_test(Num,Obj) values ($1,$2)\0", 2, Types));
}
}

Datum JsonError(PG_FUNCTION_ARGS)
{
SPI_connect();
InitPlans();

StringInfo JsonObject = makeStringInfo();
appendStringInfoString(JsonObject, "{\"ObjectName\":\"Bug Json Object\",\"Type\":227,\"Command\":88}");

Datum Values[2];

Values[0] = Int32GetDatum(227);

Values[1] = CStringGetDatum(JsonObject->data);

int ret = SPI_execute_plan(m_JsonInsertPlan, Values, NULL, false, 0);

bool Returner = false;
if (ret > 0 && SPI_processed){
//INSERT DONE
Returner = true;
}

SPI_finish();

PG_RETURN_BOOL(Returner);
}

###################################################
###################################################

rmdatabase=# select JsonError();
jsonerror
-----------
t
(1 row)

rmdatabase=# select JsonError();
jsonerror
-----------
t
(1 row)

rmdatabase=# select * from json_test;
id | num | obj
----+-----+---------------------------------------------------------
9 | 227 | "ObjectName":"Bug Json Object","Type":227,"Command":88}
10 | 227 | "ObjectName":"Bug Json Object","Type":227,"Command":88}
(2 rows)

first { simboll is lost....
first { simboll is lost....
first { simboll is lost....

best
Arsen

--
Арсен Арутюнян

Hi,

On 2017-06-27 01:21:04 +0300, Арсен Арутюнян wrote:
> Oid * Types = (Oid *)palloc(2 * sizeof(Oid));
> Types[0] = 23; //Int32
> Types[1] = 114; //JSON
> m_JsonInsertPlan = SPI_saveplan(SPI_prepare("insert into json_test(Num,Obj) values ($1,$2)\0", 2, Types));

So the type of what you're inserting is int and json. The latter is a
variable length type.  But then you do:

> Values[0] = Int32GetDatum(227);
> Values[1] = CStringGetDatum(JsonObject->data);
> int ret = SPI_execute_plan(m_JsonInsertPlan, Values, NULL, false, 0);

inserting a cstring, rather than a json value.  I.e. the issue is that
you're "lying" about the types, and their validity. You should either
use text as the plan input, and cast the result of the statement, or you
need to actually construct a proper datum, using OidInputFunctionCall or
json_in directly.

> m_JsonInsertPlan = SPI_saveplan(SPI_prepare("insert into json_test(Num,Obj) values ($1,$2)\0", 2, Types));

Not sure what that \0 is supposed to do here?

> Types[1] = 114; //JSON

You should probably include catalog/pg_type.h and use JSONOID etc.

Andres


-- 
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs

Арсен Арутюнян <arutar@bk.ru> writes:
> StringInfo JsonObject = makeStringInfo();
> appendStringInfoString(JsonObject, "{\"ObjectName\":\"Bug Json Object\",\"Type\":227,\"Command\":88}");
> Datum Values[2];
> Values[0] = Int32GetDatum(227);
> Values[1] = CStringGetDatum(JsonObject->data);

That's certainly not going to work, because a CString datum doesn't
have the same representation as a JSON datum.

I think you could get away with using CStringGetTextDatum, relying on
the equivalent physical representations of JSON and text.  But it
wouldn't be a terribly good idea IMO, partly because you'd be passing
up validity checking on the JSON string; plus you could never make
that work for JSONB.  What you really should do instead is pass the
CString to JSON's input function, say
 Values[1] = OidInputFunctionCall(F_JSON_IN, JsonObject->data, JSONOID, -1);

This is still cheating a bit --- it knows more than it ought to about
what to pass for typioparam --- but it's unlikely to break.
        regards, tom lane


--
Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-bugs