FWMariaConnection + FRecSet

Source: source/internal/fwmaria.prg

FWMariaConnection provides a comprehensive native connection to MariaDB and MySQL servers through the libmariadb.dll C client library. The companion FRecSet class wraps SQL result sets with navigation, paging, sorting, and editing capabilities. Together they form the backbone of FiveWin's MariaDB/MySQL data access layer.

FWMariaConnection

The connection class manages TCP/IP connections to MariaDB/MySQL servers. It supports connection pooling, automatic reconnection, prepared statements, transactions, backup/restore, and full DDL/DML execution.

Key DATA Members

DATA	Type	Description
`pMySql`	Pointer	Internal C library connection handle (read-only)
`cServer`	Character	Server hostname or IP address
`cDB`	Character	Current database name
`cUser`	Character	Database user name
`nPort`	Numeric	TCP port (default 3306)
`lUnicode`	Logical	Unicode mode (UTF-8 charset)
`nError`	Numeric	Last error code (0 = success)
`cError`	Character	Last error message text
`lOpen`	Logical	ACCESS: .T. if connection handle is valid
`lConnected`	Logical	ACCESS: .T. if connected and not disconnected
`cServerInfo`	Character	Server version string (read-only)
`IsMariaDB`	Logical	ACCESS: .T. if server is MariaDB
`IsMySQL`	Logical	ACCESS: .T. if server is MySQL
`nLastPing`	Numeric	Timestamp of last successful ping

Methods

Method	Description
`New( cServer, cDB, cUser, cPassword, nPort, nFlags, chr_set, cMsgLang, cLocale )`	Create and open a connection to the server
`Connect( lShowErr )`	Establish (or re-establish) the TCP/IP connection
`QueryResult( cSql, aParams )`	Execute SELECT and return array of row arrays
`RowSet( cSql, aParams, nLimit, lReadOnly )`	Execute query and return an FRecSet object (see RowSet parameters)
`Query( cSql, aParams )`	Alias for RowSet() (same parameters)
`Execute( cSql, aParams )`	Execute DDL/DML and return result structure
`TableExists( cTableName, cDbName, cTableType )`	Check if a table exists
`TableStructure( cTable, lByteWidth )`	Get table column structure as an array
`ListTables( cMask )`	Return array of table names matching mask
`Insert( cTable, aFields, aValues, lUpdate, aBinary )`	Insert a row (with optional upsert via lUpdate)
`Update( cTable, aFields, aValues, cWhere )`	Update rows matching the WHERE clause
`Upsert( cTable, acFields, aValues )`	Insert or update (INSERT ... ON DUPLICATE KEY UPDATE)
`ValToSQL( uVal, lEmptyAsDefault, lNoExcept, lBinary, lUtf8 )`	Convert Harbour value to SQL-safe string
`Last_Insert_ID()`	Return the last auto-increment value inserted
`CreateTable( cTable, aStruct, lAddAutoInc, char_set )`	Create a new table from structure array
`DropTable( cTable )`	Drop a table from the database
`BeginTransaction()`	Start a SQL transaction
`CommitTransaction( cSP )`	Commit the current transaction
`RollBack( cSP )`	Roll back the current transaction
`ShowError( cTitle )`	Display the last error in a message box
`Close()`	Close the connection and free resources
`End()`	Alias for Close()

RowSet parameters

Signature: RowSet( cSql, [aParams], [nLimit], [lReadOnly] ). Only cSql has a fixed position. The other parameters are recognised by type, not by position: an array is aParams, a number is nLimit and a logical value is lReadOnly. So oCn:RowSet( cSql, .T. ) and oCn:RowSet( cSql, aParams, .T. ) both open a read-only RowSet.

Parameter	Type	Description
`cSql`	Character	SQL statement (SELECT, SHOW, EXPLAIN, WITH, ANALYZE/CHECK/OPTIMIZE/REPAIR TABLE), a table name (equivalent to `SELECT * FROM table`) or the name of a .dbf file.
`aParams`	Array	Values for the `?` placeholders in `cSql`.
`nLimit`	Numeric	Maximum number of rows per page. It is only applied when `cSql` has an ORDER BY clause.
`lReadOnly`	Logical	Default `.F.`. If `.T.`, the RowSet is read-only.

lReadOnly

With lReadOnly := .T., SetUp() skips the detection of the base table (table or view) and the preparation of the RowSet to edit and save. This avoids extra queries to the server and can make opening the RowSet noticeably faster, mainly with large queries or slow connections.

Statements other than SELECT (SHOW, EXPLAIN, WITH, ANALYZE, CHECK, OPTIMIZE and REPAIR TABLE) are always opened read-only.

Warning: a read-only RowSet can not be edited or saved. Update(), Append(), AppendBlank() and Gather() return .F., and changes made in an XBrowse are not written to the database. lReadOnly is unrelated to lShowError.

// Example
oRs := oCn:RowSet( "SELECT id, name FROM customers", .T. )
oRs := oCn:RowSet( "SELECT * FROM orders WHERE year = ?", { 2026 }, .T. )

FRecSet - MariaDB Result Set

FRecSet provides record-oriented navigation over a MariaDB/MySQL result set. It supports paging (controlled by nPageSize), field-level access, sorting, editing, and integration with XBrowse for grid display.

Key DATA Members

DATA	Type	Description
`aData`	Array	Current page of row data (array of arrays)
`aStruct`	Array	Field structure (name, type, length, decimals, flags)
`nAt`	Numeric	Current position within the current page
`nPageSize`	Numeric	Number of rows per page (default 1000)
`nLastRec`	Numeric	Total record count
`nFields`	Numeric	Number of fields in the result set
`oCn`	Object	Parent FWMariaConnection (read-only)
`lReadOnly`	Logical	.T. prevents modifications

Methods

Method	Description
`New( oCn, result, cSql, aParams, aCols, cTable )`	Create a result set from a SQL query
`GoTo( nRec )`	Navigate to record number nRec
`Skip( n )`	Move forward or backward by n records
`GoTop()`	Navigate to the first record (same as GoTo(1))
`GoBottom()`	Navigate to the last record
`RecNo()`	Return current record number
`RecCount()`	Return total record count
`FieldGet( n )`	Get field value by position (1-based) or name
`FieldPut( n, uVal )`	Set field value by position (1-based) or name
`FieldPos( cnFld )`	Return field position by name
`FieldName( n )`	Return field name at position n
`Save()`	Save pending changes to the database
`SetOrder( cSort, u, lDesc )`	Set sort order (re-queries with ORDER BY)
`SetXBrowse( oBrw, aCols, lAutoSort, lAutoCols )`	Bind to an XBrowse control for display
`ReadAll()`	Read entire result set into memory (bypasses paging)
`Close()`	Close the result set and free resources

Free Functions

Function	Description
`maria_Connect( cHost, cDB, cUser, cPass [, nPort, nFlags, chr_set, cMsgLang, cLocale ] )`	Quick-connect helper. Returns an `FWMariaConnection` object or nil on failure.

Example: Connect and Browse

#include "FiveWin.ch"

function Main()

   local oCn, oRs, oWnd, oBrw

   // Connect to MariaDB
   oCn := FWMariaConnection():New( ;
      "localhost", ;     // server
      "mydb", ;          // database
      "root", ;          // user
      "password", ;      // password
      3306 )             // port

   if oCn:nError != 0
      MsgStop( "Connection failed: " + oCn:cError )
      return nil
   endif

   // Open a result set
   oRs := oCn:RowSet( "SELECT id, name, email, city FROM customers ORDER BY name" )

   // Navigate records
   oRs:GoTop()
   while !oRs:Eof()
      ? oRs:FieldGet( "id" ), oRs:FieldGet( "name" ), oRs:FieldGet( "email" )
      oRs:Skip( 1 )
   end

   // Display in XBrowse
   DEFINE WINDOW oWnd TITLE "Customers"

   @ 0, 0 XBROWSE oBrw OF oWnd
   oRs:SetXBrowse( oBrw )
   oBrw:CreateFromCode()

   ACTIVATE WINDOW oWnd MAXIMIZED

   oRs:Close()
   oCn:Close()

return nil

Notes

FWMariaConnection uses the MariaDB Connector/C native library (libmariadb.dll or libmariadb64.dll). The DLL must be available in the application path or system PATH.
FRecSet uses automatic paging with the default page size of 1000 rows. Set nPageSize to adjust memory usage for large result sets.
The SetOrder() method re-issues the query with an ORDER BY clause, preserving the WHERE conditions and pagination.
Use oCn:ValToSQL() to safely escape values in dynamic SQL strings, preventing SQL injection.
Transaction support requires InnoDB (or other transactional) tables. Use BeginTransaction(), CommitTransaction(), and RollBack().

FWMariaConnection + FRecSet