Populate TDBGrid with results from a function or query (C++ builder)

[Taddaiv]

Hi

I have an SQL function that returns a table, and I would like to display the results in a grid.

Code: Select all

create or replace function get_table()
  returns table (group_name varchar(50), log_id integer, log_entry varchar(50))
as
$body$
select A.group_name, B.log_id, B.log_entry
from
(
	(select group_id, group_name from "group") A
		full outer join
	(select log_id, log_entry, group_id from log) B on A.group_id = B.group_id
)
$body$
language sql;

This function I would prefer to call using a TPgStoredProc object

Code: Select all

	TPgStoredProc* pgProc = new TPgStoredProc(NULL);
	pgProc->Connection = pgCon;
	pgProc->StoredProcName = "get_table";
	pgProc->PrepareSQL();
	pgProc->ExecProc();
	//Now what?
	grdMain->DataSet = ...

, but a TPgQuery is fine if it gets the job done

Code: Select all

	TPgQuery* query = new TPgQuery(NULL);
	query->Connection = pgCon;
	query->SQL->Text = "select * from get_table();";
	query->Execute();
	//Now what?
	grdMain->DataSource = q->MasterSource;
	grdMain->Enabled = true;

I expect this table to be read-only.
However, if there exists some way of both
showing and editing data that is joined like this using a TDBGrid,
it would be amazing.

Any insight on this is greatly appreciated :)

Also here are the tables:

Code: Select all

create table group (
    group_id integer primary key,
    group_name varchar(50)
);

create table log (
    log_id integer primary key,
    log_entry varchar(50),
    group_id integer references "group" (group_id)
)

[MaximG]

Our components support editing the data retrieved from tables using the SQL Join statement. You can use the SQLUpdate property for that task:

https://www.devart.com/pgdac/docs/devar ... update.htm

[Taddaiv]

Turns out that the only thing needed for this to work was throwing in a TPgDataSource object in the middle.

Code: Select all

	pgProc = new TPgStoredProc(NULL);
	pgDataSource = new TPgDataSource(NULL);
	pgProc->Connection = pgCon;
	pgProc->StoredProcName = "get_table";
	pgProc->PrepareSQL();
	pgProc->ExecProc();
	
	pgDataSource->DataSet = pgProc;
	grdMain->DataSource = pgDataSource;
    	grdMain->Enabled = true;	
	

The only unexpected result from this, is that columns with a varchar(50) datatype are represented as (MEMO)
(or (Memo) in the case of a NULL value). I thought only the SQL text datatype was supposed to do this?

[MaximG]

1. Indeed, every data-aware control has a DataSource property that specifies which data source class in the application provides the data to the control: http://docwiki.embarcadero.com/Librari ... DataSource
2. The group_name and log_entry fields returned by the function get_table() will be of type TMemoField in the project , which is the correct behavior. This is caused by the function CREATE FUNCTION that ignores modifiers of argument length even if they're defined explicitly when creating the functiion https://www.postgresql.org/docs/10/sql- ... ction.html (Section "Notes"). As a result, we're not able to receive information about the length of the returned fields from a PostgreSQL server.
You can implement the necessary behaviour by calling the function get_table() in the following query:

Code: Select all

UniQuery->SQL->Text = "select group_name::character varying(50), log_id, log_entry::character varying(50) from get_table()";