Hello,

I wrote some PSQL code today that you give a table name and it
returns an RTF table. I realized this could potentially be useful
outside the application I wrote it for so I'm posting it here.

It should be useable as is. For SQL statements, define these as
views and they should work. I haven't tested it in all possible
ways. The variable sizes defined are arbitrary, and I have so far
just increased them and recreated when I got truncation errors due to
larger data sets.

Which reminds me, does anyone know a good way of returning a text
BLOB in the output instead of just the largest possible varchar?
str2blob seems insufficient or maybe I used it wrong.

Rob

/*

DECLARE EXTERNAL FUNCTION rtrim
CSTRING(80)
RETURNS CSTRING(80) FREE_IT
ENTRY_POINT 'IB_UDF_rtrim' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION substr
CSTRING(80), SMALLINT, SMALLINT
RETURNS CSTRING(80) FREE_IT
ENTRY_POINT 'IB_UDF_substr' MODULE_NAME 'ib_udf';

DECLARE EXTERNAL FUNCTION strlen
CSTRING(32767)
RETURNS INTEGER BY VALUE
ENTRY_POINT 'IB_UDF_strlen' MODULE_NAME 'ib_udf';
*/

drop procedure TBL2RTF;

set term ^! ;

create procedure TBL2RTF(sTable Varchar(35)) returns (RTF varchar
(32765)) as
declare variable sHeader varchar(200);
declare variable sFooter varchar(35);
declare variable sFontTags1 varchar(35);
declare variable sFontTags2 varchar(35);
declare variable sColWidths varchar(10000);
declare variable sFieldLabels varchar(10000);
declare variable sRecord varchar(10000);
declare variable sRecordSelect varchar(2000);
declare variable iCurrTableWidth integer;
declare variable iPixPerChar integer;
declare variable iLengthOfField integer;
declare variable sFIELDNAME char(31);
declare variable iFIELDTYPE integer;
declare variable iFIELDLENGTH integer;
begin
sHeader =
'{\rtf1\ansi\ansicpg1252\deff0\deflang1033\deflangfe1033{\fonttbl
{\f0\fswiss\fprq2\fcharset0 Arial;}}{\colortbl;\red0\green0\blue0;}
\viewkind4\uc1\trowd\trgaph30\trleft-30';
sFooter =
'\pard\nowidctlpar\cf0\fs20\par} ';
sFontTags1 =
'\pard\intbl\cf1\b\f0\fs16';
sFontTags2 =
'\b0\trowd\trgaph30\trleft-30';
iCurrTableWidth = 0;
iPixPerChar = 75;
sColWidths = '';
sFieldLabels = '';
sRecordSelect = 'select ''\intbl''';
for select rf.rdb$field_name, f.rdb$field_type, f.rdb$field_length
from rdb$relation_fields rf
join rdb$fields f on rf.rdb$field_source=f.rdb$field_name
where rf.rdb$relation_name=:sTable
into :sFIELDNAME, :iFIELDTYPE, :iFIELDLENGTH do
begin
if (strlen(:sFieldName) > iFieldLength) then iFieldLength = strlen
(:sFieldName);
iCurrTableWidth = iCurrTableWidth + (iPixPerChar * iFIELDLENGTH);
sColWidths = sColWidths || '\cellx' || rtrim(cast(iCurrTableWidth
as char(8)));
sFieldLabels = sFieldLabels || ' ' || rtrim(sFieldName)
|| '\cell';
if ((iFIELDTYPE=7) or (iFIELDTYPE=8) or (iFIELDTYPE=16) or
(iFIELDTYPE=27)) then
sRecordSelect = sRecordSelect || ' || '' '' || rtrim(cast(' ||
sFieldName || ' as char(8))) || ''\cell''';
else if (iFIELDTYPE<>261) then
sRecordSelect = sRecordSelect || ' || '' '' || rtrim
('||sFieldName||')|| ''\cell''';
else
sRecordSelect = sRecordSelect || ' || '' '' || ''BLOB''
|| ''\cell''';
end
sRecordSelect = sRecordSelect || ' || ''\row'' as ROWRTF from ' ||
sTable;
sFieldLabels = sFieldLabels || '\row';
RTF =
sHeader ||
sColWidths ||
sFontTags1 ||
sFieldLabels ||
sFontTags2 ||
sColWidths;
sRecord = '';
/* RTF = sRecordSelect;
SUSPEND; */
for execute statement sRecordSelect into :sRecord do
RTF = RTF || :sRecord;
RTF = RTF || sFooter;
SUSPEND;
end ^!


set term ; ^!