TODBC

Fonte: source/classes/todbc.prg

TODBC provides direct ODBC (Open Database Connectivity) access from Harbour applications. Unlike TRecSet which relies on ADO/COM, TODBC calls the Windows ODBC API Funções directly through Harbour's C-level interface. This makes it lightweight and suitable for connecting to any ODBC-compliant data source including SQL Server, MySQL, PostgreSQL, Oracle, SQLite, and more.

Architecture

flowchart LR subgraph "Harbour Application" A[TODBC] end subgraph "ODBC Layer" B[ODBC Driver Manager] C[ODBC Driver] end subgraph "Databases" D[SQL Server] E[MySQL/MariaDB] F[PostgreSQL] G[SQLite] H[Any ODBC Source] end A -->|"hEnv, hDbc"| B B --> C C --> D C --> E C --> F C --> G C --> H

Key DATA Members

DATA	Type	Default	Description
`hEnv`	Numeric		ODBC environment handle
`hDbc`	Numeric		ODBC connection handle
`cConnect`	Character		Full connection string (after successful connect)
`cDSN`	Character		Data Source Name
`cUser`	Character		Username for authentication
`cPassword`	Character		Password for authentication
`lSuccess`	Logical	`.T.`	Whether the last operation succeeded
`lShowError`	Logical	`.T.`	Show error dialogs automatically
`lAbort`	Logical	`.F.`	Abort on error
`lCsrDyn`	Logical	`.T.`	Use dynamic cursors
`lDateAsStr`	Logical	`.F.`	Return dates as strings
`aErrors`	Array	`{}`	Array of error messages

Methods

Constructor

METHOD New( cDSN, cUser, cPassword )

Creates a new ODBC connection. The cDSN parameter can be either a DSN name registered in the ODBC Data Source Administrator, or a full connection string starting with "DRIVER=". The constructor allocates the ODBC environment and connection handles and immediately attempts to connect. Check oOdbc:lSuccess after creation.

Statement Methods

Method	Description
`AllocStatement()`	Allocate a new SQL statement handle. Returns hStmt or 0 on error.
`DropStatement( hStmt )`	Free a statement handle
`Execute( cCommand, cOperation, hStmt, @nRet )`	Execute a SQL command. Returns hStmt.

Query Methods

Method	Description
`Query( cCommand )`	Execute a SELECT query. Returns array of { aFields, aData }.
`QueryRow( cCommand )`	Execute a query and return only the first row
`QueryData( cCommand )`	Execute a query and return data array only
`Fetch( hStmt )`	Fetch next row from statement. Returns .T. if successful.
`CursorFields( hStmt, lDateAsStr )`	Get field descriptors for a cursor
`GetValue( nField, hStmt, aFields )`	Get value from the current row

Table Management

Method	Description
`GetTables( cMask, lViews )`	Get list of tables (optionally including views)
`Create( cFileName, aFieldsInfo )`	Create a new table
`DelTable( cTable )`	Drop a table
`TruncateTable( cTable )`	Delete all rows from a table
`GetIndex( cTable )`	Get index information for a table
`IndexKey( cTable, nOrder )`	Get index key expression
`IsIndex( cTable, cIndex )`	Check if an index exists

Transaction Methods

Method	Description
`Commit()`	Commit the current transaction
`RollBack()`	Roll back the current transaction

Information & Error Handling

Method	Description
`GetInfo( nType )`	Get ODBC connection information
`SetOptions( nType, uBuffer )`	Set ODBC connection options
`GetOptions( nType )`	Get ODBC connection options
`IsError()`	Returns .T. if errors exist
`LastError()`	Returns the last error message
`ShowErrorList( cTitle )`	Display all errors in a dialog
`End()`	Disconnect and free all handles

Example: Connect via DSN

#include "FiveWin.ch"

function Main()

   local oOdbc, aResult, aFields, aData

   // Connect using a pre-configured DSN
   oOdbc := TOdbc():New( "MyDSN", "myuser", "mypass" )

   if ! oOdbc:lSuccess
      MsgStop( "Connection failed: " + oOdbc:LastError() )
      return nil
   endif

   ? "Connected to:", oOdbc:cDSN

   // List tables
   ? "Tables:", oOdbc:GetTables()

   // Execute a SELECT query
   aResult := oOdbc:Query( "SELECT * FROM Customers WHERE Country = 'USA'" )

   if ! Empty( aResult )
      aFields := aResult[ 1 ]   // field descriptors
      aData   := aResult[ 2 ]   // data rows
      ? "Found", Len( aData ), "records"
      ? "Fields:", Len( aFields )
   endif

   oOdbc:End()

return nil

Example: Connect via Driver String

#include "FiveWin.ch"

function Main()

   local oOdbc

   // Connect to SQL Server without a DSN
   oOdbc := TOdbc():New( ;
      "DRIVER={SQL Server};SERVER=localhost\SQLEXPRESS;" + ;
      "DATABASE=Northwind;Trusted_Connection=yes" )

   if oOdbc:lSuccess
      // Execute a command
      oOdbc:Execute( "UPDATE Products SET UnitPrice = UnitPrice * 1.10 " + ;
                     "WHERE CategoryID = 1" )
      oOdbc:Commit()
      ? "Prices updated"
   endif

   oOdbc:End()

return nil

Example: Fetch Row by Row

#include "FiveWin.ch"
#include "sql.ch"

function Main()

   local oOdbc, hStmt, aFields, n

   oOdbc := TOdbc():New( "MyDSN", "user", "pass" )

   if ! oOdbc:lSuccess
      return nil
   endif

   // Execute and get statement handle
   hStmt := oOdbc:Execute( "SELECT Name, Email, Balance FROM Clients" )

   if hStmt != 0
      // Get field information
      aFields := oOdbc:CursorFields( hStmt )

      // Fetch row by row
      do while oOdbc:Fetch( hStmt )
         for n := 1 to Len( aFields )
            ?? oOdbc:GetValue( n, hStmt, aFields ), " | "
         next
         ?   // new line
      enddo

      oOdbc:DropStatement( hStmt )
   endif

   oOdbc:End()

return nil

Example: Transactions

#include "FiveWin.ch"

function Main()

   local oOdbc

   oOdbc := TOdbc():New( "MyDSN", "user", "pass" )

   if oOdbc:lSuccess
      // Turn off auto-commit for manual transaction control
      // (auto-commit is ON by default after New())

      oOdbc:Execute( "INSERT INTO Orders (CustomerID, OrderDate) VALUES (1, '2026-03-24')" )
      oOdbc:Execute( "INSERT INTO OrderItems (OrderID, ProductID, Qty) VALUES (SCOPE_IDENTITY(), 42, 5)" )

      if oOdbc:IsError()
         oOdbc:RollBack()
         MsgStop( "Transaction failed" )
      else
         oOdbc:Commit()
         MsgInfo( "Order created" )
      endif
   endif

   oOdbc:End()

return nil

Common Connection Strings

// SQL Server (DSN-less)
"DRIVER={SQL Server};SERVER=myserver;DATABASE=mydb;UID=user;PWD=pass"

// MySQL via ODBC
"DRIVER={MySQL ODBC 8.0 Unicode Driver};SERVER=localhost;DATABASE=mydb;USER=root;PASSWORD=pass;PORT=3306"

// PostgreSQL
"DRIVER={PostgreSQL Unicode};SERVER=localhost;DATABASE=mydb;UID=user;PWD=pass;PORT=5432"

// SQLite
"DRIVER={SQLite3 ODBC Driver};Database=C:\data\mydb.sqlite"

// MS Access
"DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=C:\data\mydb.accdb"