[PATCH] Add Oid_JSON in pqconnection unit
Original Reporter info from Mantis: silvioprog
-
Reporter name: silvioprog
Original Reporter info from Mantis: silvioprog
- Reporter name: silvioprog
Description:
Hello,
The patch allows to use this ... :
select row_to_json(row(1,'foo'))
... instead of this (in this case, the ::text cast avoids the "Missing (compatible) underlying dataset, can not open" error):
select row_to_json(row(1,'foo'))::text
--- result of both ---
{ "f1" : 1, "f2" : "foo" }
It worked fine too:
drop table if exists person cascade; create table person ( id serial not null primary key, name varchar(50) not null unique ); insert into person (name) values ('Foo'); insert into person (name) values ('Bar'); ... select array_to_json(array_agg(row_to_json(t))) from ( select id, name from person ) t
--- result ---
[{ "id" : 1, "name" : "Foo" }, { "id" : 2, "name" : "Bar" }]
The test:
program project1; {$mode objfpc}{$H+} uses pqconnection, sqldb, jsonparser, fpjson; procedure ValidateJson(const S: string); var j: TJSONData; begin // just to validade the JSON result with TJSONParser.Create(S) do try j := Parse; try // WriteLn(j.AsJSON); uncomment to print the JSON resut finally j.Free; end; finally Free; end; end; var q: TSQLQuery; con: TPQConnection; begin con := TPQConnection.Create(nil); con.Transaction := TSQLTransaction.Create(con); q := TSQLQuery.Create(con); try con.HostName := '127.0.0.1'; con.DatabaseName := 'postgres'; con.UserName := 'postgres'; con.Password := 'postgres'; q.Transaction := con.Transaction; q.SQL.Text := 'select row_to_json(row(1,''foo''))'; q.Open; while not q.EOF do begin ValidateJson(q.Fields[0].AsString); q.Next; end; finally con.Free; end; end.
P.S.: Get the 114 type via:
$ psql -qAt -F $'\t' -p 5432 postgres -c $ "select 'Oid_' || upper(typname), '=' || oid from pg_type"
Thank you!
Additional information:
Topic about this patch: http://lists.freepascal.org/pipermail/fpc-pascal/2015-March/043857.html.
Mantis conversion info:
- Mantis ID: 27606
- Version: 3.1.1
- Fixed in version: 3.0.0
- Fixed in revision: 30121 (#f2b23108)
- Target version: 3.0.0