MariaDB / MySQL

Source: source/classes/mariarec.prg (FWMariaRecord), source/classes/mariadb.prg (FWMariaConnection)

FiveWin provides native MariaDB/MySQL support through the libmariadb.dll (32-bit) or libmariadb64.dll (64-bit) client library. The two primary classes are FWMariaConnection (manages the TCP/IP connection and SQL execution) and FWMariaRecord (extends TDataRow for single-record CRUD operations with automatic Upsert support).

Architecture

flowchart TB subgraph "Application" A[FWMariaConnection] B[FWMariaRecord] C[XBrowse / UI] end subgraph "Native Library" D["libmariadb.dll
(C API)"] end subgraph "Server" E[MariaDB / MySQL Server] F[(Database)] end B --> A C --> A A --> D D -->|"TCP/IP"| E E --> F

FWMariaConnection

The connection class manages the link to the MariaDB/MySQL server. Key operations:

Method / DATA	Description
`New( cServer, cUser, cPassword, cDatabase, nPort )`	Create and open a connection
`Execute( cSql )`	Execute SQL and return results (field structure)
`QueryResult( cSql )`	Execute SELECT and return array of values
`Insert( cTable, aFields, aValues )`	INSERT a new row
`Update( cTable, aFields, aValues, cWhere )`	UPDATE existing rows
`Upsert( cTable, aFields, aValues )`	INSERT or UPDATE (auto-detect via primary key)
`TableExists( cTable )`	Check if a table exists
`TableStructure( cTable )`	Get table structure array
`Last_Insert_id`	ACCESS: last auto-increment value after INSERT
`nError`	Last error code (0 = success)
`ShowError()`	Display last error message
`lUnicode`	Unicode mode (UTF-8 charset)
`ValToSql( uValue )`	Convert Harbour value to SQL-safe string
`Close()`	Close the connection

FWMariaRecord

FWMariaRecord extends TDataRow to provide single-record access to a MariaDB table. It handles loading, editing, and saving individual records with automatic primary key detection.

Key DATA Members

DATA	Type	Description
`oCn`	Object	FWMariaConnection object
`cTable`	Character	Table name
`cWhere`	Character	WHERE clause identifying the current record
`aStruct`	Array	Table structure (columns, types, lengths, decimals, flags)
`aBlank`	Array	Blank values array for new records
`aPrimary`	Array	Indices of primary key columns
`nAutoIncFld`	Numeric	Position of auto-increment field (0 if none)

Methods

Method	Description
`New( oCn, cTable, cWhere )`	Create and load a record. If cWhere is nil, loads blank.
`Load( lBlank )`	Load record from server (or blank template)
`Read( cWhere )`	Load a specific record by WHERE clause
`Blank()`	Reset to blank record (for new entry)
`Save()`	Save changes (auto Upsert: INSERT if new, UPDATE if existing)
`MakePrimaryWhere()`	Build WHERE clause from primary key values

Data Flow: Save Operation

sequenceDiagram participant App as Application participant Rec as FWMariaRecord participant Cn as FWMariaConnection participant DB as MariaDB Server App->>Rec: oRec:Save() Rec->>Cn: oCn:Upsert(cTable, nil, aVals) Cn->>DB: INSERT ... ON DUPLICATE KEY UPDATE DB-->>Cn: OK (nError=0) alt New Record (auto-increment) Cn-->>Rec: Last_Insert_id Rec->>Rec: Update aData with new ID end Rec->>Rec: MakePrimaryWhere() Rec->>Rec: Load() -- refresh from server Rec-->>App: lSaved = .T.

Example: Connection Setup

#include "FiveWin.ch"

function Main()

   local oCn

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

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

   ? "Connected to MariaDB"
   ? "Tables:", oCn:GetTables()

   oCn:Close()

return nil

Example: Execute Queries

#include "FiveWin.ch"

function Main()

   local oCn, aResult, aRow

   oCn := FWMariaConnection():New( "localhost", "root", "pass", "mydb" )

   // Simple query - returns array of row arrays
   aResult := oCn:QueryResult( ;
      "SELECT id, name, email FROM customers WHERE active = 1 ORDER BY name" )

   if oCn:nError == 0
      for each aRow in aResult
         ? aRow[ 1 ], aRow[ 2 ], aRow[ 3 ]   // id, name, email
      next
   endif

   // Execute DDL/DML commands
   oCn:Execute( "CREATE TABLE IF NOT EXISTS logs (" + ;
      "id INT AUTO_INCREMENT PRIMARY KEY, " + ;
      "message VARCHAR(255), " + ;
      "created_at DATETIME DEFAULT CURRENT_TIMESTAMP)" )

   oCn:Execute( "INSERT INTO logs (message) VALUES ('Application started')" )
   ? "Inserted ID:", oCn:Last_Insert_id

   oCn:Close()

return nil

Example: CRUD with FWMariaRecord

#include "FiveWin.ch"

function Main()

   local oCn, oRec

   oCn := FWMariaConnection():New( "localhost", "root", "pass", "mydb" )

   // --- CREATE (Insert) ---
   oRec := FWMariaRecord():New( oCn, "customers" )
   // oRec starts as a blank record

   oRec:FieldPut( "name",  "John Smith" )
   oRec:FieldPut( "email", "john@example.com" )
   oRec:FieldPut( "phone", "555-1234" )

   if oRec:Save()
      ? "Created customer ID:", oRec:FieldGet( "id" )
   endif

   // --- READ ---
   oRec := FWMariaRecord():New( oCn, "customers", "`id` = 1" )
   ? oRec:FieldGet( "name" )    // "John Smith"
   ? oRec:FieldGet( "email" )   // "john@example.com"

   // --- UPDATE ---
   oRec:FieldPut( "phone", "555-9999" )
   oRec:Save()   // auto-detects UPDATE via primary key

   // --- DELETE ---
   oCn:Execute( "DELETE FROM customers WHERE id = 1" )

   // --- Read a different record ---
   oRec:Read( "`email` = 'jane@example.com'" )
   if oRec:lValidData
      ? "Found:", oRec:FieldGet( "name" )
   endif

   oCn:Close()

return nil

Example: XBrowse with MariaDB

#include "FiveWin.ch"

function Main()

   local oCn, oWnd, oBrw, cSql

   oCn := FWMariaConnection():New( "localhost", "root", "pass", "mydb" )

   cSql := "SELECT id, name, email, phone, city FROM customers ORDER BY name"

   DEFINE WINDOW oWnd TITLE "MariaDB Customers"

   @ 0, 0 XBROWSE oBrw OF oWnd

   oCn:SetXBrowse( oBrw, cSql )
   oBrw:CreateFromCode()

   ACTIVATE WINDOW oWnd MAXIMIZED

   oCn:Close()

return nil

DLL Setup

FiveWin's MariaDB support requires the native client library:

32-bit applications: libmariadb.dll
64-bit applications: libmariadb64.dll

Place the appropriate DLL in your application directory or in the system PATH. You can download the MariaDB Connector/C from mariadb.com. The same connector works for connecting to MySQL servers.

Tips

FWMariaRecord automatically detects primary keys using column flags from the table structure. This enables the Upsert() mechanism to decide between INSERT and UPDATE.
The nAutoIncFld DATA tracks the auto-increment column position. After an INSERT, the new auto-increment value is automatically assigned to the record.
Character fields are padded to their defined length when lUnicode is .T., using UTF-8 aware string functions.
Use oCn:ValToSql() to safely escape values for SQL strings, preventing SQL injection.