<div dir="ltr">Hello,<div><br></div><div>The patch allows to use this ... :</div><div><br></div><div> select row_to_json(row(1,'foo'))</div><div><br></div><div>... instead of this (in this case, the ::text cast avoids the "Missing (compatible) underlying dataset, can not open" error):</div><div><br></div><div> select row_to_json(row(1,'foo'))::text<br></div><div><br></div><div>--- result of both ---</div><div><br></div><div><div> { "f1" : 1, "f2" : "foo" }</div></div><div><br></div><div>It worked fine too:</div><div><br></div><div> drop table if exists person cascade;<br></div><div><div> create table person (</div><div><span class="" style="white-space:pre"> </span>id serial not null primary key,</div><div><span class="" style="white-space:pre"> </span>name varchar(50) not null unique</div><div> );</div><div><br></div><div> insert into person (name) values ('Foo');</div><div> insert into person (name) values ('Bar');</div></div><div><br></div><div>...</div><div><div><br></div><div> select array_to_json(array_agg(row_to_json(t)))<br></div><div><div> from (</div><div> select id, name from person</div><div> ) t</div></div></div><div><br></div><div>--- result ---</div><div><div><br></div><div> [{ "id" : 1, "name" : "Foo" }, { "id" : 2, "name" : "Bar" }]</div></div><div><br></div><div>=== begin patch ===</div><div><br></div><div><div>From 079807a984a0f414860e599c8836c9f099add46c Mon Sep 17 00:00:00 2001</div><div>From: silvioprog <<a href="mailto:silvioprog@gmail.com">silvioprog@gmail.com</a>></div><div>Date: Wed, 4 Mar 2015 22:10:48 -0300</div><div>Subject: [PATCH 1/1] fcl-db: postgresql: add support for JSON.</div><div><br></div><div>---</div><div> packages/fcl-db/src/sqldb/postgres/pqconnection.pp | 3 ++-</div><div> 1 file changed, 2 insertions(+), 1 deletion(-)</div><div><br></div><div>diff --git a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp</div><div>index bcf4382..bdf036d 100644</div><div>--- a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp</div><div>+++ b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp</div><div>@@ -176,6 +176,7 @@ const Oid_Bool = 16;</div><div> Oid_Int8 = 20;</div><div> Oid_int2 = 21;</div><div> Oid_Int4 = 23;</div><div>+ Oid_JSON = 114;</div><div> Oid_Float4 = 700;</div><div> Oid_Money = 790;</div><div> Oid_Float8 = 701;</div><div>@@ -736,7 +737,7 @@ begin</div><div> ATypeOID:=0;</div><div> AOID:=PQftype(res,Tuple);</div><div> case AOID of</div><div>- Oid_varchar,Oid_bpchar,</div><div>+ Oid_varchar,Oid_bpchar,Oid_JSON,</div><div> Oid_name : begin</div><div> Result := ftstring;</div><div> size := PQfsize(Res, Tuple);</div><div>-- </div><div>1.9.5.msysgit.0</div></div><div><br></div><div>=== end patch ===<br></div><div><br></div><div>== begin test ==<br></div><div><br></div><div><div>program project1;</div><div><br></div><div>{$mode objfpc}{$H+}</div><div><br></div><div>uses</div><div> pqconnection,</div><div> sqldb,</div><div> jsonparser,</div><div> fpjson;</div><div><br></div><div> procedure ValidateJson(const S: string);</div><div> var</div><div> j: TJSONData;</div><div> begin</div><div> // just to validade the JSON result</div><div> with TJSONParser.Create(S) do</div><div> try</div><div> j := Parse;</div><div> try</div><div> // WriteLn(j.AsJSON); uncomment to print the JSON resut</div><div> finally</div><div> j.Free;</div><div> end;</div><div> finally</div><div> Free;</div><div> end;</div><div> end;</div><div><br></div><div>var</div><div> q: TSQLQuery;<br></div><div> con: TPQConnection;</div><div>begin</div><div> con := TPQConnection.Create(nil);</div><div> con.Transaction := TSQLTransaction.Create(con);</div><div> q := TSQLQuery.Create(con);</div><div> try</div><div> con.HostName := '127.0.0.1';</div><div> con.DatabaseName := 'postgres';</div><div> con.UserName := 'postgres';</div><div> con.Password := 'postgres';</div><div> q.Transaction := con.Transaction;</div><div> q.SQL.Text := 'select row_to_json(row(1,''foo''))';</div><div> q.Open;</div><div> while not q.EOF do</div><div> begin</div><div> ValidateJson(q.Fields[0].AsString);</div><div> q.Next;</div><div> end;</div><div> finally</div><div> con.Free;</div><div> end;</div><div>end.</div></div><div><br></div><div>== end test ==<br></div><div><br></div><div>P.S.: Get the 114 type via:</div><div><br></div><div><div>$ psql -qAt -F $'\t' -p 5432 postgres -c</div><div>$ "select 'Oid_' || upper(typname), '=' || oid from pg_type"</div></div><div><br></div><div><div>A: why get it as string instead of memo or blob?</div><div>R: the string it is easy to be parsed, converted to other sources (eg: stream), and internally the FCL-JSON uses a stringlist as JSON data source, so the string type could be the best type for JSON representation.</div></div><div><br></div><div>Thank you!</div><div><br></div><div>-- <br></div><div><div class="gmail_signature">Silvio Clécio<br>My public projects - <a href="http://github.com/silvioprog" target="_blank">github.com/silvioprog</a></div>
</div></div>