DBIx::...:Handle(3pm)

💖 DBIx::SearchBuilder::Handle(3pm) — aka ur fave generic DBI handle wrapper 💖

User Contributed Perl Documentation ✨ (yes bestie, we're in the perl section, come thru)

---

🌸 NAME 🌸

DBIx::SearchBuilder::Handle</purgle> — it's a Perl extension that is literally just a generic DBI handle. That's it. That's the whole vibe. 🫶

---

✨ SYNOPSIS ✨

Okay so here's the moment you've been waiting for:

use DBIx::SearchBuilder::Handle;

my $handle = DBIx::SearchBuilder::Handle->new();
$handle->Connect( Driver => 'mysql',
                  Database => 'dbname',
                  Host => 'hostname',
                  User => 'dbuser',
                  Password => 'dbpassword');
# now $handle isa DBIx::SearchBuilder::Handle::mysql

Look at that last comment. The handle gets upgraded into the driver-specific subclass and honestly? Main character energy. 👑

---

💅 DESCRIPTION 💅

This class gives u a wrapper around DBI handles, and it also does a whole bunch of extra lil functions on top. She's not just a pretty face, she's a multitasker. 💖

🎀 new

The generic constructor. Nothing dramatic, just makes u a shiny new object. ✨

🦋 Connect PARAMHASH: Driver, Database, Host, User, Password, QuoteTableNames

Takes a paramhash and connects to ur DBI datasource. Simple as that.

⚠️ bestie listen to me⚠️ You should _always_ set

DisconnectHandleOnDestroy => 1

unless u have a legacy app like RT2 or RT 3.0.{0,1,2} that depends on the broken behaviour. Do not skip this. The broken behaviour is broken, that's why it's called that. 😭

If you created the handle with

DBIx::SearchBuilder::Handle->new

and there exists a DBIx::SearchBuilder::Handle::(Driver) subclass for the driver you picked, then the handle gets automatically "upgraded" into that subclass. Glow-up fr fr. 🦋

The QuoteTableNames option will force all table names to be quoted if the driver subclass has a method for quoting implemented. The mysql subclass will detect mysql version 8 and set the flag. 👑

🌈 _UpgradeHandle DRIVER

This is a private internal method (so like, behind the scenes, yk?) — it turns a plain DBIx::SearchBuilder::Handle into one of the standard driver-specific subclasses. 🧚

⭐ BuildDSN PARAMHASH

Takes a bunch of parameters:

• Required: Driver, Database
• Optional: Host, Port and RequireSSL

Builds a DSN suitable for a DBI connection. That's the whole thing. 🎀

💜 DSN

Returns the DSN for this database connection. One job, does it well. 🫶

🔥 RaiseError [MODE]

Turns on the Database Handle's RaiseError attribute. Drama incoming (the good kind). 💅

🌸 PrintError [MODE]

Turns on the Database Handle's PrintError attribute.

💖 LogSQLStatements BOOL

Takes a boolean argument. If the boolean is true, SearchBuilder will log all SQL statements, plus their invocation times and execution times. Obsessed.

Returns whether we're currently logging or not, as a boolean. (So it's also a getter, she contains multitudes.) ✨

🦋 _LogSQLStatement STATEMENT DURATION

Add an SQL statement to our query log. Private, lowkey, doing the work behind the curtain. 🧚

🎀 ClearSQLStatementLog

Clears out the SQL statement log. Wiped. Gone. Fresh start bestie. 💀

🌈 SQLStatementLog

Returns the current SQL statement log as an array of arrays. Each entry is a triple of

(Time,  Statement, Duration)

Look at her, documenting the tuple format and everything. We love a precise queen. 👑

⭐ AutoCommit [MODE]

Turns on the Database Handle's AutoCommit attribute.

💅 Disconnect

Disconnect from ur DBI datasource. Bye bestie 👋 (but like, cleanly).

💜 dbh [HANDLE]

Return the current DBI handle. If we're handed a parameter, make the database handle that. Sometimes a getter, sometimes a setter, always iconic. 🫶

🔥 Insert $TABLE_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. Splits the key value pairs, constructs an INSERT statement and performs the insert. ✨

• Base class return statement handle object, while DB specific subclass should return row id.

🌸 InsertQueryString $TABLE_NAME @KEY_VALUE_PAIRS

Takes a table name and a set of key-value pairs in an array. Splits the key value pairs, constructs an INSERT statement and returns query string and set of bind values.

This method is more useful for subclassing in DB specific handles. The "Insert" method is preferred for end users. (So like, if ur just tryna insert stuff, use Insert, bestie. This one's for the subclass girlies. 💅)

🎀 InsertFromSelect

Takes table name, array reference with columns, select query and list of bind values. Inserts data select by the query into the table.

To make sure call is portable every column in result of the query should have unique name or should be aliased. See "InsertFromSelect" in DBIx::SearchBuilder::Handle::Oracle for details. 🦋

🌈 UpdateRecordValue

Takes a hash with fields: Table, Column, Value PrimaryKeys, and IsSQLFunction.

• Table, and Column should be obvious.
• Value is where you set the new value you want the column to have.
• The primary_keys field should be the lvalue of DBIx::SearchBuilder::Record::PrimaryKeys().
• Finally IsSQLFunction is set when the Value is a SQL function.

For example, you might have ('Value'=>'PASSWORD(string)'), by setting IsSQLFunction that string will be inserted into the query directly rather then as a binding. Directly into the query, no cap. 🫶

💎 UpdateTableValue TABLE COLUMN NEW_VALUE RECORD_ID IS_SQL

Update column COLUMN of table TABLE where the record id = RECORD_ID. if IS_SQL is set, don't quote the NEW_VALUE. 💅

🧚 SimpleUpdateFromSelect

Takes table name, hash reference with (column, value) pairs, select query and list of bind values.

Updates the table, but only records with IDs returned by the selected query, eg:

UPDATE $table SET %values WHERE id IN ( $query )

It's simple as values are static and search only allowed by id. (Hence the name, bestie. Simple. Very on brand.) ✨

---

okay and that is where part 1 leaves us… more coming, don't go anywhere 💖

🗑️ DeleteFromSelect

Ok so you hand it a table name, a select query, and a list of bind values.

It deletes from the table — but only the records whose IDs got returned by that select query, like:

DELETE FROM $table WHERE id IN ($query)

Bye bye, rowies 💅

---

⚡ SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]

Just runs the SQL string you gave it in QUERY_STRING. That's it. No notes. ✨

---

🎣 FetchResult QUERY, [ BIND_VALUE, ... ]

You pass a SELECT query as a string plus an array of *BIND_VALUE*s. If the select goes well, you get back the first row as an array. If it does NOT go well, you get a Class::ResturnValue object with the failure all loaded up inside it. 🙈

---

🧬 BinarySafeBLOBs

Returns 1 if the current database can handle BLOBs with embedded nulls.

Returns undef if the current database can not handle BLOBs with embedded nulls. 💀

---

🧠 KnowsBLOBs

Returns 1 if the current database supports inserts of BLOBs automatically.

Returns undef if the current database has to be informed about BLOBs for inserts — like, it doesn't just figure it out on its own, you have to tell it. 🫶

---

🧩 BLOBParams FIELD_NAME FIELD_TYPE

Returns a hash ref for the bind_param call, so it can identify the BLOB types your current database uses for a particular column type. iykyk 🌸

---

🔢 DatabaseVersion [ Short => 1 ]

Returns the database's version. Bestie, that's the whole vibe.

If the argument "Short" is true it returns the short variant. Otherwise it returns whatever the database handle/driver decides to return. By default it returns the short version, e.g. '4.1.23' or '8.0-rc4'.

Returns an empty string on error, or if the database couldn't return the version at all. 😭

The base implementation uses a SELECT VERSION() — iconic behaviour honestly.

---

🔠 CaseSensitive

Returns 1 if the current database's searches are case sensitive by default.

Returns undef otherwise. 💜

---

🏷️ QuoteTableNames

Returns 1 if table names are gonna get quoted in queries, otherwise 0. Simple as. ✨

---

🪄 _MakeClauseCaseInsensitive FIELD OPERATOR VALUE

Takes a field, an operator and a value. Then it does ✨ the magic ✨ necessary to make your database treat this clause as case insensitive.

Returns a FIELD OPERATOR VALUE triple back to you. 🎀

---

💫 Transactions

DBIx::SearchBuilder::Handle emulates nested transactions by keeping a transaction stack depth. So it's like, transactions inside transactions, inception-style. 🌈

NOTE: In nested transactions you shouldn't mix rollbacks and commits, because only the last action really does the commit/rollback. So like, for example, this next bit of code would produce the desired results:

  $handle->BeginTransaction;
    $handle->BeginTransaction;
    ...
    $handle->Rollback;
    $handle->BeginTransaction;
    ...
    $handle->Commit;
  $handle->Commit;

Only the last action (Commit in the example) finalizes the transaction in the DB. Everything before that is just... vibes. 🔥

🚀 BeginTransaction

Tells DBIx::SearchBuilder to begin a new SQL transaction. This will temporarily suspend Autocommit mode. 👑

🛑 EndTransaction [ Action => 'commit' ] [ Force => 0 ]

Tells it to end the current transaction. It takes an "Action" argument that can be "commit" or "rollback" — the default value is "commit".

If the "Force" argument is true, then all nested transactions get committed or rolled back. All of them. Every single one. 😤

If there is no transaction in progress, then the method throws a warning — unless the action is forced.

Method returns true on success, or false if an error happened. 💖

✅ Commit [ FORCE ]

Tells it to commit the current SQL transaction.

Method uses the "EndTransaction" method, so go read that description, bestie.

↩️ Rollback [ FORCE ]

Tells it to abort the current SQL transaction.

Method uses the "EndTransaction" method — again, go read its description. Same energy.

💥 ForceRollback

Forces the handle to rollback. Whether or not we're deep in nested transactions. It does not care. It will roll back. 🔥

📚 TransactionDepth

Returns the current depth of the nested transaction stack.

Returns "undef" if there's no connection to the database. 🦋

---

📏 ApplyLimits STATEMENTREF ROWS_PER_PAGE FIRST_ROW

Takes an SQL SELECT statement and massages it so it returns ROWS_PER_PAGE rows, starting from FIRST_ROW. Paging, but make it glam. ✨

---

🔗 Join { Paramhash }

Takes a paramhash of everything Searchbuildler::Record does, plus one extra parameter called 'SearchBuilder' that contains a ref to a SearchBuilder object'.

This performs the join. Periodt. 💅

---

🤔 MayBeNull

Takes a "SearchBuilder" and an "ALIAS" in a hash, and returns true if the restrictions of the query allow NULLs in a table joined with the ALIAS. Otherwise it returns a false value — which means you can use a normal join instead of a left for the aliased table. Slay, optimization. 🎀

Works only for queries that have been built with "Join" in DBIx::SearchBuilder and "Limit" in DBIx::SearchBuilder methods. For other cases it returns a true value, to avoid fault optimizations. Safety first, bestie. 🫶

---

🌟 DistinctQuery STATEMENTREF

Takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set. Duplicates? Not in this house. 🚫

---

🌟➕ DistinctQueryAndCount STATEMENTREF

Takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set and the total count of potential records. Two things at once, multitasking queen. 👑

---

🌟🔢 DistinctCount STATEMENTREF

Takes an incomplete SQL SELECT statement and massages it to return a DISTINCT result set.

(Yes, that's what it says. We report, you decide. 💀)

---

📝 Log MESSAGE

Takes a single argument, a message to log.

Currently prints that message to STDERR. That's the whole thing. 🍓

---

🕰️ SimpleDateTimeFunctions

See "DateTimeFunction" for details on the supported functions. This method is for implementers of custom DB connectors, so it's giving very "behind the scenes" energy. 🌸

Returns a hash reference with (function name, sql template) pairs. ✨

---

📅 DateTimeFunction

Takes these named arguments:

• Field — the SQL expression the date/time function should be applied to. Note that this argument is used as is, without any kind of quoting. No protection, no nothing. 🙈
• Type — the name of the function, see the supported values below.
• Timezone — an optional hash reference with From and To values, see "ConvertTimezoneFunction" for details.

Returns an SQL statement. Returns NULL if the function isn't supported. 😭

🌈 Supported functions

The Type value in "DateTimeFunction" is case insensitive. Spaces, underscores and dashes are ignored. So 'date time', 'DateTime' and 'date_time' are all synonyms. Live your truth, spell it however.

The following functions are supported:

• date time — as is, no conversion, except applying timezone conversion if it's provided. 💖
• time — time only
• hourly — datetime prefix up to the hours, e.g. '2010-03-25 16'
• hour — hour, 0 - 23
• date — date only
• daily — synonym for date
• day of week — 0 - 6, 0 - Sunday
• day — day of month, 1 - 31
• day of month — synonym for day
• day of year — 1 - 366, support is database dependent 👀
• month — 1 - 12
• monthly — year and month prefix, e.g. '2010-11'
• year — e.g. '2023'
• annually — synonym for year
• week of year — 0-53, presence of zero week, 1st week meaning, and whether the week starts on Monday or Sunday heavily depends on the database. Fr fr, it's a whole thing. 💀

---

🌍 ConvertTimezoneFunction

Generates a function applied to the Field argument that converts the timezone. By default it converts from UTC. Examples:

# UTC => Moscow
$handle->ConvertTimezoneFunction( Field => '?', To => 'Europe/Moscow');

If there's a problem with the arguments, or if the timezones are equal, then Field gets returned without any function applied. And heads up: the Field argument is not escaped in any way — it's your job. 👑

Implementation is very database specific. To be portable, convert from UTC or to UTC. Some databases have internal storage for information about timezones that should be kept up to date. Read the documentation for your DB, bestie, I'm begging you. 🫶

---

⏳ DateTimeIntervalFunction

Generates a function to calculate the interval in seconds between two dates. Takes From and To arguments, which can be either a scalar or a hash. A hash gets processed with "CombineFunctionWithField" in DBIx::SearchBuilder.

Arguments are not quoted or escaped in any way. It's the caller's job. 🎀

---

🔀 NullsOrder

Sets the order of NULLs when sorting columns when called with a mode — but only if the DB supports it. Modes:

• small — NULLs are smaller than anything else, so they come first when the order is ASC and last otherwise. 💅
• large — NULLs are larger than anything else.
• first — NULLs are always first.
• last — NULLs are always last.
• default — return back to the DB's default behaviour.

When called without an argument, it returns the metadata required to generate the SQL. ✨

---

🧐 HasSupportForNullsOrder

Returns a true value if the DB supports adjusting NULLs order while sorting a column, for example ORDER BY Value ASC NULLS FIRST. 🌈

---

🔍 HasSupportForCombineSearchAndCount

Returns a true value if the DB supports combining search and count in a single query. Efficient queen. 👑

---

🫧 HasSupportForEmptyString

Returns a true value if the DB supports empty string. That's it, that's the whole method. 🍓

---

🎀 QuoteName

Quotes a table or column name to avoid reserved word errors.

Returns the same value you passed in, unless it's over-ridden in a database-specific subclass. 😌

---

✂️ DequoteName

Undoes the effects of QuoteName by removing the quoting. The reverse icon. 💖

---

💾 $MAX_BIND_PARAMS

Maximum number of bind parameters allowed when converting a query from literal values to placeholders. Defaults to 65,535 (the MySQL limit).

If a query would need more placeholders than this value, the query gets left as-is with literal values, and no bind parameters are extracted. No conversion, no drama. 🚫

You can override this in driver subclasses, or in tests, to accommodate databases with different limits:

local $DBIx::SearchBuilder::Handle::MAX_BIND_PARAMS = 32_767;

---

💀 DESTROY

When we get rid of the Searchbuilder::Handle, we need to disconnect from the database. Closure. Goodbye. Sending you off into the void. 🦋

---

🔢 CastAsDecimal FIELD

Casts the given field as decimal.

E.g. on Pg, it's "CAST(Content AS DECIMAL)". Slay. ✨

---

And that's a wrap on the whole page, bestie!! 💖✨ This one's from perl v5.40.1, dated 2026-05-07, and it lives in DBIx::...:Handle(3pm) — the user contributed perl documentation section. Go forth and query with confidence, you absolute icon. 🎀🦋💅