[fpc-pascal] [PATCH] Add Oid_JSON in pqconnection unit

[silvioprog]

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" }]

=== begin patch ===

>From 079807a984a0f414860e599c8836c9f099add46c Mon Sep 17 00:00:00 2001
From: silvioprog <silvioprog at gmail.com>
Date: Wed, 4 Mar 2015 22:10:48 -0300
Subject: [PATCH 1/1] fcl-db: postgresql: add support for JSON.

---
 packages/fcl-db/src/sqldb/postgres/pqconnection.pp | 3 ++-
 1 file changed, 2 insertions(+), 1 deletion(-)

diff --git a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
index bcf4382..bdf036d 100644
--- a/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
+++ b/packages/fcl-db/src/sqldb/postgres/pqconnection.pp
@@ -176,6 +176,7 @@ const Oid_Bool     = 16;
       Oid_Int8     = 20;
       Oid_int2     = 21;
       Oid_Int4     = 23;
+      Oid_JSON     = 114;
       Oid_Float4   = 700;
       Oid_Money    = 790;
       Oid_Float8   = 701;
@@ -736,7 +737,7 @@ begin
   ATypeOID:=0;
   AOID:=PQftype(res,Tuple);
   case AOID of
-    Oid_varchar,Oid_bpchar,
+    Oid_varchar,Oid_bpchar,Oid_JSON,
     Oid_name               : begin
                              Result := ftstring;
                              size := PQfsize(Res, Tuple);
-- 
1.9.5.msysgit.0

=== end patch ===

== begin 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.

== end test ==

P.S.: Get the 114 type via:

$ psql -qAt -F $'\t' -p 5432 postgres -c
$ "select 'Oid_' || upper(typname), '=' || oid from pg_type"

A: why get it as string instead of memo or blob?
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.

Thank you!

-- 
Silvio Clécio
My public projects - github.com/silvioprog
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.freepascal.org/pipermail/fpc-pascal/attachments/20150304/eff0cf96/attachment.html>