TPostgreSQL + FWPG_* Funções

Fonte: source/function/pgsuport.prg

FiveWin provides PostgreSQL integration via the pgsuport.prg module, which bridges the Harbour hbpgsql library (a libpq wrapper) with FWH components such as TXBrowse and TDataRow. Rather than requiring a compile-time link to hbpgsql, the module uses a lazy-loading mechanism to resolve libpq function pointers at runtime, making the library dependency optional.

PGLinked() - Lazy-Loading Function Resolution

The internal static function PGLinked() is called automatically on the first invocation of any FWPG_ function. It uses HB_FUNCPTR() to locate the Harbour-level libpq wrappers provided by the hbpgsql library. If PQEXEC is found, pointers are cached for the following operations:

These resolved function pointers are invoked via the HB_ExecFromArray() mechanism through #xtranslate macros (PQ_Exec, PQ_ExecParams, PQ_MetaData, etc.).

FWPG_Execute() - Query Execution

The central function for all PostgreSQL interaction is FWPG_Execute:

Signature:

FWPG_Execute( pDB, cSql, aParams )  && -> aResult | nAffected | .F.

Parameters:

• pDB - The database connection object (or its :pDB handle).
• cSql - The SQL statement to execute.
• aParams - Optional array of parameter values for parameterized queries.

Return values:

• For SELECT queries (PGRES_TUPLES_OK): Returns a 2D array (rows x columns) of Harbour-typed values.
• For DML commands (PGRES_COMMAND_OK): Returns the numeric count of affected rows.
• On error: Sets the error state on the connection object and returns .F..

When parameters are supplied via aParams, each value is converted to an SQL-safe string using FW_ValToSQL(u, , "PG") and the query is executed via PQ_ExecParams, preventing SQL injection.

PostgreSQL-to-Harbour Type Mapping

Query results arrive from libpq as strings. The internal GetFieldTypes() function inspects column metadata for specific keywords to determine the optimal Harbour type:

Metadata and Structure Funções

FWPG_MetaStruct()

FWPG_MetaStruct( res )  && -> aStruct

Returns an array of column metadata for a query result. Each element is an array indexed by the following constants:

FWPG_Structure()

FWPG_Structure( oQry )  && -> aStruct

Returns an enhanced structure array for a PostgreSQL query object. Enriches the base aStruct with table name, column name, and primary key information by querying information_schema.columns. Adjusts field types for bytea (mapped to "m"), timestamp (mapped to "T"), and auto-increment columns (mapped to "+").

FWPG_FieldGet()

FWPG_FieldGet( oQry, nFldNo )  && -> uVal

A specialized field getter that handles PostgreSQL-specific formatting:

• Converts \x hex prefixes (from bytea columns) to binary strings.
• Processes timestamp values with FW_STR2DT().
• Applies Unicode padding via FW_UTF8PADCHAR() when Unicode mode is active.
• Accepts field names (character) in addition to numeric field positions.
• Falls back to evaluating expression blocks for computed columns.

FWPG_Skipper()

FWPG_Skipper( oQry, nSkip )  && -> nSkipped

Navigation helper used by TXBrowse codeblocks for record skipping. Calculates the new record offset relative to the current position, clamping to valid range [1, nLastRec]. Returns the actual number of records skipped.

Utility Funções

TXBrowse Integration

TXBrowse consumes PostgreSQL data sets through standard codeblock interfaces. The FWPG_Skipper function provides the bSkip codeblock; the query object's bGoTop, bGoBottom, bEof, bBof, and bBookMark codeblocks are set directly on the query object. Direct in-cell editing is supported via FWPG_XBrSaveData( oCol, xValue ), which constructs and executes UPDATE SQL targeting the source table's primary key.

TDataRow Integration

TDataRow provides native PostgreSQL support through two internal methods:

• ReadPQQ: Reads field values and metadata from a PostgreSQL query object into the TDataRow edit buffer (aData/aOrg).
• SavePQQ: Generates and executes the necessary SQL UPDATE or INSERT statements via FWPG_Execute to persist changes made through the data row interface.

Example: Connect, Query, and Browse

#include "FiveWin.ch"

function Main()

   local oServer, oQry, oWnd, oBrw, aDbs

   // Open a PostgreSQL connection using hbpgsql
   oServer := TPostgreSQL():New( "localhost", "mydb", "postgres", "password", 5432 )

   if oServer:lError
      MsgStop( "Connection failed: " + oServer:cError )
      return nil
   endif

   // List available databases
   aDbs := FWPG_ListDbs( oServer )
   XBROWSER aDbs TITLE "Databases on Server"

   // Execute a query
   oQry := TPostgreSQLQuery():New( oServer, "SELECT id, name, email, city FROM customers ORDER BY name" )

   if oQry:nLastRec > 0
      DEFINE WINDOW oWnd TITLE "Customers"

      @ 0, 0 XBROWSE oBrw OF oWnd ;
         COLUMNS "id", "name", "email", "city" ;
         COLSIZES 50, 200, 250, 150

      oBrw:nMarqueeStyle   := 3
      oBrw:lCanPaste       := .t.

      // Connect XBrowse to PostgreSQL query via codeblocks
      oBrw:bSkip       := { |nSkip| FWPG_Skipper( oQry, nSkip ) }
      oBrw:bGoTop      := { || oQry:GoTop() }
      oBrw:bGoBottom   := { || oQry:GoBottom() }
      oBrw:bEof        := { || oQry:RecNo() > oQry:nLastRec }
      oBrw:bBof        := { || oQry:RecNo() == 0 }
      oBrw:bBookMark   := { || oQry:RecNo() }
      oBrw:bStrData    := { |n| FWPG_FieldGet( oQry, n ) }
      oBrw:bKeyNo      := { || oQry:RecNo() }

      oBrw:CreateFromCode()

      ACTIVATE WINDOW oWnd MAXIMIZED
   else
      MsgInfo( "No records found" )
   endif

   oQry:End()
   oServer:End()

return nil

Notes

• PostgreSQL support requires the Harbour hbpgsql library to be linked into your application. The PGLinked() function resolves the library's function pointers at runtime; if hbpgsql is not linked, all FWPG_ Funções return .F. gracefully.
• Parameterized queries via aParams are strongly recommended for dynamic SQL to prevent injection attacks. Values are escaped using FW_ValToSQL( u, , "PG" ).
• The TDataRow integration (ReadPQQ/SavePQQ) enables record-at-a-time editing with automatic change tracking and SQL generation.
• In-cell editing via XBrowse (FWPG_XBrSaveData) requires that the query's source table has a primary key defined and that FWPG_Structure() has been called to populate the structure metadata.
• The FWPG_Structure() function automatically queries information_schema.columns to resolve table and column names from OID references, providing full metadata for complex JOIN queries.

Veja Também

• Classes Alfabético
• Funções