mORMot Open Source

JD

Member

Registered: 2015-08-20

Posts: 118

Problems encoding JSON array in URL for sending to PostgreSQL server

Hi there everyone,

(a) BACKGROUND
I've been struggling with this problem for a couple of days. I have a mORMot REST interface application server in front of a PostgreSQL 10.5 server. It is working very well for a while now.

I just wrote a function in the PostgreSQL server that generates and returns work/planning schedules as a JSON text output to clients. The PostgreSQL function has the following signature:

CREATE OR REPLACE FUNCTION public.agg_planning_json(id_json text, date_debut date, date_fin date)

The function expects the id_json parameter to have the following format:

{"ID": [20,19]}

I have tested this function directly in the PostgrSQL server and it works well. I can even call the function from any browser or REST client after encoding the JSON input parameter like this

http://localhost:8088/service/myapi/rapportplanning?schema=public&idlist=%7B%22ID%22%3A%20%5B19%2C20%5D%7D&datedebut='2018-01-01'&datefin='2018-04-30'

and it works.

The mORMot REST server code that handles these requests looks like this:

Res := aServer.fDbProps.Execute(Format('select * from %s.agg_planning_json(%s, %s, %s)',
   [Schema, QuotedStr(IDList), QuotedStr(DateDebut), QuotedStr(DateFin)]), []);
while Res.Step do
   Result := Res.ColumnUTF8('agg_planning_json');

(b) THE PROBLEM
My problem is with the Lazarus clients. I send the request to the server after encoding the JSON array as follows:

ARestThread.Post(sqlPlanningSalarie, '', '', [UrlEncode(strIDList), SQLDate(dtDateDebut.Date), SQLDate(dtDateFin.Date)]);

This ALWAYS fails with the error below:

Project server raised exception class 'Unknown' with message
SQL Error: ERREUR: syntaxe en entrée invalide pour le type json
DETAIL: le jeton << % >> n'est pas valide
CONTEXT: données JSON, ligne %1 : %...
instruction SQL << SELECT * FROM json_array_elements_text(id_json::json -> 'ID') >>
.....

The PostgreSQL function seems to be complaining about the format of the JSON parameter. I noticed that the Lazarus client sends the parameters to the server with each parameter in double quotes " ". Could this be the source of the problem? Is this normal? How do I correct this problem?

Thanks a lot for your assistance,

JD

JD

Member

Registered: 2015-08-20

Posts: 118

Re: Problems encoding JSON array in URL for sending to PostgreSQL server

I finally got it to work. This time I stopped encoding the JSON array before sending it to the REST server and it now works in the Lazarus clients as well as when I call the function from an SQL tool or even from my browser.

Thanks a lot,

JD