### Get Available Database Drivers with DBI.Drivers

Source: https://context7.com/mwild1/luadbi/llms.txt

Returns a list of available database drivers that are installed and loadable on the system. This function is useful for runtime capability detection, allowing applications to check which databases they can connect to.

```lua
local DBI = require('DBI')

-- Get list of available drivers
local drivers = DBI.Drivers()

print('Available database drivers:')
for _, driver in ipairs(drivers) do
    print('  - ' .. driver)
end
-- Output might be: MySQL, PostgreSQL, SQLite3, DuckDB
```

--------------------------------

### Iterate Through Result Set Rows (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Provides examples of iterating through all rows of a result set using the `rows` method. It shows how to use the iterator with both named columns and indexed arrays, and the default behavior.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('SELECT id, name, price FROM products WHERE category = ?')
sth:execute('electronics')

-- Iterate with named columns
print('Products (named columns):')
for row in sth:rows(true) do
    print(string.format('  %d: %s - $%.2f', row.id, row.name, row.price))
end

-- Iterate with indexed columns
sth:execute('books')
print('Products (indexed):')
for row in sth:rows(false) do
    print(string.format('  %d: %s - $%.2f', row[1], row[2], row[3]))
end

-- Default is indexed (false)
sth:execute('clothing')
for row in sth:rows() do
    print(row[2])  -- name column
end

sth:close()
dbh:close()
```

--------------------------------

### Manage Autocommit Mode (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Explains how to enable or disable autocommit mode for database connections using the `autocommit` method. It shows examples of immediate commits with autocommit enabled and explicit transaction control when disabled.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Enable autocommit (each statement commits immediately)
dbh:autocommit(true)

local sth, _ = dbh:prepare('INSERT INTO logs (message) VALUES (?)')
sth:execute('Auto-committed entry')  -- Committed immediately

-- Disable autocommit for transaction control
dbh:autocommit(false)

sth:execute('Entry 1')
sth:execute('Entry 2')
-- Neither committed yet

dbh:commit()  -- Now both are committed

sth:close()
dbh:close()
```

--------------------------------

### Get Column Names from Result Set (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Demonstrates how to retrieve a list of column names from the metadata of a result set using the `columns` method. This is useful for dynamically accessing or displaying column data.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('SELECT id, name, email, created_at FROM users LIMIT 1')
sth:execute()

-- Get column names
local columns = sth:columns()
print('Columns in result set:')
for i, col in ipairs(columns) do
    print(string.format('  %d: %s', i, col))
end
-- Output:
--   1: id
--   2: name
--   3: email
--   4: created_at

sth:close()
dbh:close()
```

--------------------------------

### Get Affected Rows Count (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Illustrates how to retrieve the number of rows affected by the last INSERT, UPDATE, or DELETE operation using the `affected` method. Examples include checking after each type of DML statement.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Check affected rows after INSERT
local sth, _ = dbh:prepare('INSERT INTO logs (message) VALUES (?)')
sth:execute('Auto-committed entry')  -- Committed immediately

-- Check affected rows after UPDATE
local update_sth, _ = dbh:prepare('UPDATE users SET last_seen = ? WHERE active = ?')
update_sth:execute(os.time(), true)
local affected = update_sth:affected()
print('Users updated: ' .. affected)

-- Check affected rows after DELETE
local delete_sth, _ = dbh:prepare('DELETE FROM sessions WHERE expires_at < ?')
delete_sth:execute(os.time())
print('Expired sessions removed: ' .. delete_sth:affected())

sth:close()
update_sth:close()
delete_sth:close()
dbh:close()
```

--------------------------------

### Get Row Count of SELECT Result (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Shows how to obtain the number of rows returned by a SELECT statement using the `rowcount` method. It notes that this method's availability depends on the specific database driver.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('SELECT * FROM users WHERE role = ?')
sth:execute('admin')

-- Get total row count before iterating
local count = sth:rowcount()
print('Found ' .. count .. ' admin users')

-- Now iterate through results
for row in sth:rows(true) do
    print('Admin: ' .. row.name)
end

sth:close()
dbh:close()
```

--------------------------------

### Get Last Inserted ID with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Retrieves the last auto-generated ID from an INSERT statement using the last_id method. This is available on databases like MySQL and SQLite3 that support auto-increment. For PostgreSQL, using `INSERT ... RETURNING id` is recommended instead. It requires a successful INSERT operation.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('INSERT INTO users (name, email) VALUES (?, ?)')
sth:execute('Alice', 'alice@example.com')

-- Get the auto-generated ID
local new_id = dbh:last_id()
print('New user ID: ' .. new_id)

-- Use the ID for related inserts
local profile_sth, _ = dbh:prepare('INSERT INTO profiles (user_id, bio) VALUES (?, ?)')
profile_sth:execute(new_id, 'Hello, I am Alice!')

sth:close()
profile_sth:close()
dbh:close()
```

--------------------------------

### Connect to Databases with LuaDBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Establishes a connection to a database using the specified driver and connection parameters. It automatically loads the appropriate native driver module. Supports multiple database types including MySQL, PostgreSQL, SQLite3, and DuckDB.

```lua
local DBI = require('DBI')

-- Connect to MySQL database
local dbh, err = DBI.Connect('MySQL', 'mydb', 'username', 'password', 'localhost', 3306)
if not dbh then
    print('Connection failed: ' .. err)
    return
end

-- Connect to PostgreSQL database
local dbh, err = DBI.Connect('PostgreSQL', 'mydb', 'username', 'password', 'localhost', 5432)

-- Connect to SQLite3 database (only database name required)
local dbh, err = DBI.Connect('SQLite3', 'mydata.db')

-- Connect to DuckDB database
local dbh, err = DBI.Connect('DuckDB', 'analytics.duckdb')

-- Always check for connection errors
if dbh then
    print('Connected successfully')
    dbh:close()
else
    print('Error: ' .. err)
end
```

--------------------------------

### Prepare SQL Statements with connection:prepare

Source: https://context7.com/mwild1/luadbi/llms.txt

Creates a prepared statement from an SQL query string. Prepared statements are crucial for preventing SQL injection and can be reused with different parameters for improved performance. The function returns a statement handle or an error.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Prepare a SELECT statement with placeholder
local sth, err = dbh:prepare('SELECT id, name, email FROM users WHERE status = ?')
if not sth then
    print('Prepare failed: ' .. err)
    return
end

-- Prepare an INSERT statement
local insert_sth, err = dbh:prepare('INSERT INTO logs (user_id, action, timestamp) VALUES (?, ?, ?)')
if not insert_sth then error(err) end

-- PostgreSQL uses $1, $2 placeholders
-- local sth = dbh:prepare('SELECT * FROM users WHERE id = $1')

-- SQLite3 also uses $1, $2 placeholders
-- local sth = dbh:prepare('SELECT * FROM users WHERE name = $1')

sth:close()
insert_sth:close()
dbh:close()
```

--------------------------------

### Fetch Rows from Result Set (Lua DBI)

Source: https://context7.com/mwild1/luadbi/llms.txt

Demonstrates how to fetch rows from a prepared statement's result set. It shows fetching rows as associative arrays (named columns) or indexed arrays, and how to handle the end of the result set.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('SELECT id, name, email FROM users WHERE active = ?')
sth:execute(true)

-- Fetch as named columns (associative array)
local row = sth:fetch(true)
if row then
    print('ID: ' .. row.id)
    print('Name: ' .. row.name)
    print('Email: ' .. row.email)
end

-- Fetch as indexed array
sth:execute(true)
local row = sth:fetch(false)
if row then
    print('ID: ' .. row[1])
    print('Name: ' .. row[2])
    print('Email: ' .. row[3])
end

-- Fetch returns nil when no more rows
while true do
    local row = sth:fetch(true)
    if not row then break end
    print(row.name)
end

sth:close()
dbh:close()
```

--------------------------------

### Execute SQL Statements Directly with DBI.Do

Source: https://context7.com/mwild1/luadbi/llms.txt

A helper function that performs prepare and execute in a single step, returning the number of affected rows. This is ideal for INSERT, UPDATE, and DELETE statements that do not require fetching results. It takes the database handle, SQL query, and parameters as arguments.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Insert a record and get affected row count
local affected, err = DBI.Do(dbh, 'INSERT INTO users (name, email) VALUES (?, ?)', 'John Doe', 'john@example.com')
if affected then
    print('Rows inserted: ' .. affected)
else
    print('Insert failed: ' .. err)
end

-- Update records
local affected, err = DBI.Do(dbh, 'UPDATE users SET active = ? WHERE last_login < ?', true, '2024-01-01')
if affected then
    print('Rows updated: ' .. affected)
end

-- Delete records
local affected, err = DBI.Do(dbh, 'DELETE FROM sessions WHERE expired = ?', true)
print('Sessions deleted: ' .. (affected or 0))

dbh:close()
```

--------------------------------

### Execute Prepared Statements with statement:execute

Source: https://context7.com/mwild1/luadbi/llms.txt

Executes a prepared statement with the given bind parameters. Parameters are bound in order to the placeholders in the SQL query. This method is used after preparing a statement with `dbh:prepare` and can be reused with different parameters.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Prepare and execute a SELECT
local sth, err = dbh:prepare('SELECT * FROM products WHERE category = ? AND price < ?')
if not sth then error(err) end

local success, err = sth:execute('electronics', 100.00)
if not success then
    print('Execute failed: ' .. err)
    return
end

-- Execute with different data types
local sth2, _ = dbh:prepare('INSERT INTO events (name, count, active, data) VALUES (?, ?, ?, ?)')
local success, err = sth2:execute('click', 42, true, nil)  -- string, number, boolean, nil
if success then
    print('Insert successful')
end

-- Reuse statement with different parameters
sth:execute('clothing', 50.00)
sth:execute('books', 25.00)

sth:close()
sth2:close()
dbh:close()
```

--------------------------------

### statement:fetch

Source: https://context7.com/mwild1/luadbi/llms.txt

Fetches the next row from a result set as either an associative table (named columns) or an indexed array.

```APIDOC
## statement:fetch

### Description
Fetches the next row from the result set. When passed `true`, returns a table with column names as keys; when passed `false` or nothing, returns an array-indexed table.

### Method
Lua Method

### Parameters
#### Arguments
- **named_columns** (boolean) - Optional - If true, returns a table with column names as keys. Defaults to false (indexed array).

### Response
- **row** (table|nil) - The fetched row data, or nil if no more rows are available.
```

--------------------------------

### connection:autocommit

Source: https://context7.com/mwild1/luadbi/llms.txt

Configures the autocommit state for the database connection.

```APIDOC
## connection:autocommit

### Description
Enables or disables autocommit mode. When disabled, changes must be explicitly committed with `commit()` or rolled back with `rollback()`.

### Method
Lua Method

### Parameters
#### Arguments
- **enabled** (boolean) - Required - True to enable autocommit, false to disable it.
```

--------------------------------

### statement:columns

Source: https://context7.com/mwild1/luadbi/llms.txt

Retrieves the column names from the result set metadata.

```APIDOC
## statement:columns

### Description
Returns a table containing the column names from the result set metadata.

### Method
Lua Method

### Response
- **columns** (table) - An array of strings representing the column names.
```

--------------------------------

### Escape Strings for SQL Queries with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Escapes a string for safe use in SQL queries using the quote method. While prepared statements are preferred, this method is useful for constructing dynamic SQL where placeholders cannot be used, such as for table or column names. It requires an active database connection.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Escape potentially dangerous input
local user_input = "Robert'; DROP TABLE users;--"
local safe_string = dbh:quote(user_input)
print('Escaped: ' .. safe_string)
-- Output: Robert\'; DROP TABLE users;--

-- Use in dynamic SQL (prepared statements preferred)
local table_name = 'users'  -- Can't use placeholder for table names
local search = dbh:quote('John%')
local sql = string.format("SELECT * FROM %s WHERE name LIKE '%s'", table_name, search)

dbh:close()
```

--------------------------------

### Check Database Connection Health with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Checks if the database connection is still alive using the ping method. It returns true if connected and false otherwise. This is useful for connection pooling and long-running applications to ensure the connection is active before performing operations.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

-- Check connection health
if dbh:ping() then
    print('Database connection is active')
else
    print('Database connection lost, reconnecting...')
    dbh:close()
    dbh = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
end

-- Use in a long-running service
while true do
    if not dbh:ping() then
        dbh = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
    end
    -- ... do work ...
end

dbh:close()
```

--------------------------------

### Close Database Connection and Statements with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Closes the database connection or statement handle, releasing associated resources. While handles are garbage collected automatically, explicit closing using close() is recommended for better resource management. Attempting operations on closed handles will result in an error.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

local sth, _ = dbh:prepare('SELECT * FROM users')
sth:execute()

for row in sth:rows(true) do
    print(row.name)
end

-- Explicitly close statement when done
local closed = sth:close()
print('Statement closed: ' .. tostring(closed))  -- true

-- Close connection when done
local disconnected = dbh:close()
print('Connection closed: ' .. tostring(disconnected))  -- true

-- Attempting operations on closed handles will error
-- sth:execute()  -- Error: Invalid statement handle
-- dbh:prepare('SELECT 1')  -- Error: Database not available
```

--------------------------------

### Commit Transaction with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Commits the current transaction, making all changes permanent. This is only necessary when autocommit is disabled. It requires a valid database connection and prepared statements for the operations within the transaction.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

dbh:autocommit(false)

local sth, _ = dbh:prepare('UPDATE accounts SET balance = balance - ? WHERE id = ?')
local sth2, _ = dbh:prepare('UPDATE accounts SET balance = balance + ? WHERE id = ?')

-- Transfer funds between accounts
local ok1, err1 = sth:execute(100.00, 1)   -- Debit account 1
local ok2, err2 = sth2:execute(100.00, 2)  -- Credit account 2

if ok1 and ok2 then
    local success = dbh:commit()
    if success then
        print('Transfer completed successfully')
    end
else
    dbh:rollback()
    print('Transfer failed, rolled back')
end

sth:close()
sth2:close()
dbh:close()
```

--------------------------------

### statement:rowcount

Source: https://context7.com/mwild1/luadbi/llms.txt

Returns the total number of rows in the result set.

```APIDOC
## statement:rowcount

### Description
Returns the number of rows in the result set after executing a SELECT statement. Note: Not all drivers support this method.

### Method
Lua Method

### Response
- **count** (number) - The total number of rows in the result set.
```

--------------------------------

### statement:rows

Source: https://context7.com/mwild1/luadbi/llms.txt

Returns an iterator function to loop through all rows in a result set.

```APIDOC
## statement:rows

### Description
Returns an iterator function for looping through all rows in the result set. Pass `true` for named columns or `false` for indexed arrays.

### Method
Lua Method

### Parameters
#### Arguments
- **named_columns** (boolean) - Optional - If true, returns rows as tables with column names as keys. Defaults to false.

### Response
- **iterator** (function) - An iterator function that returns the next row on each iteration.
```

--------------------------------

### Rollback Transaction with Lua DBI

Source: https://context7.com/mwild1/luadbi/llms.txt

Rolls back the current transaction, discarding all uncommitted changes. This is used when autocommit is disabled and an error or condition requires undoing pending operations. It requires an active database connection.

```lua
local DBI = require('DBI')

local dbh, err = DBI.Connect('MySQL', 'mydb', 'user', 'pass', 'localhost', 3306)
if not dbh then error(err) end

dbh:autocommit(false)

local sth, _ = dbh:prepare('DELETE FROM orders WHERE id = ?')

-- Attempt to delete an order
local success, err = sth:execute(12345)

if not success then
    print('Delete failed: ' .. err)
    dbh:rollback()
elseif some_validation_failed then
    print('Validation failed, rolling back')
    dbh:rollback()
else
    dbh:commit()
    print('Order deleted')
end

sth:close()
dbh:close()
```

--------------------------------

### statement:affected

Source: https://context7.com/mwild1/luadbi/llms.txt

Returns the number of rows affected by the last DML statement.

```APIDOC
## statement:affected

### Description
Returns the number of rows affected by the last INSERT, UPDATE, or DELETE statement.

### Method
Lua Method

### Response
- **count** (number) - The number of rows affected.
```

=== COMPLETE CONTENT === This response contains all available snippets from this library. No additional content exists. Do not make further requests.