Skip site navigation (1)Skip section navigation (2)

FreeBSD Manual Pages


home | help
DBIx::SearchBuilder::HUsereContributed Perl DocuDBIx::SearchBuilder::Handle(3)

       DBIx::SearchBuilder::Handle - Perl extension which is a generic DBI

	 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

       This class provides a wrapper for DBI handles that can also perform a
       number of additional functions.

       Generic constructor

   Connect PARAMHASH: Driver, Database,	Host, User, Password
       Takes a paramhash and connects to your DBI datasource.

       You should _always_ set

	    DisconnectHandleOnDestroy => 1

       unless you have a legacy	app like RT2 or	RT 3.0.{0,1,2} that depends on
       the broken behaviour.

       If you created the handle with
	    DBIx::SearchBuilder::Handle->new and there is a
       DBIx::SearchBuilder::Handle::(Driver) subclass for the driver you have
       chosen, the handle will be automatically	"upgraded" into	that subclass.

   _UpgradeHandle DRIVER
       This private internal method turns a plain DBIx::SearchBuilder::Handle
       into one	of the standard	driver-specific	subclasses.

       Takes a bunch of	parameters:

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

       Builds a	DSN suitable for a DBI connection

       Returns the DSN for this	database connection.

   RaiseError [MODE]
       Turns on	the Database Handle's RaiseError attribute.

   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, as well as their	invocation times and execution

       Returns whether we're currently logging or not as a boolean

       Add an SQL statement to our query log

       Clears out the SQL statement log.

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

       (Time,  Statement, Duration)

   AutoCommit [MODE]
       Turns on	the Database Handle's AutoCommit attribute.

       Disconnect from your DBI	datasource

   dbh [HANDLE]
       Return the current DBI handle. If we're handed a	parameter, make	the
       database	handle that.

       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

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

       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.
       "Insert"	method is preferred for	end users.

       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.

       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.

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

       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.

       Takes table name, select	query and list of bind values.

       Deletes from the	table, but only	records	with IDs returned by the
       select query, eg:

	   DELETE FROM $table WHERE id IN ($query)

   SimpleQuery QUERY_STRING, [ BIND_VALUE, ... ]
       Execute the SQL string specified	in QUERY_STRING

   FetchResult QUERY, [	BIND_VALUE, ...	]
       Takes a SELECT query as a string, along with an array of	BIND_VALUEs If
       the select succeeds, returns the	first row as an	array.	Otherwise,
       returns a Class::ResturnValue object with the failure loaded up.

       Returns 1 if the	current	database supports BLOBs	with embedded nulls.
       Returns undef if	the current database doesn't support BLOBs with
       embedded	nulls

       Returns 1 if the	current	database supports inserts of BLOBs
       automatically.  Returns undef if	the current database must be informed
       of BLOBs	for inserts.

       Returns a hash ref for the bind_param call to identify BLOB types used
       by the current database for a particular	column type.

   DatabaseVersion [Short => 1]
       Returns the database's version.

       If argument "Short" is true returns short variant, in other case
       returns whatever	database handle/driver returns.	By default returns
       short version, e.g. '4.1.23' or '8.0-rc4'.

       Returns empty string on error or	if database couldn't return version.

       The base	implementation uses a "SELECT VERSION()"

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

   _MakeClauseCaseInsensitive FIELD OPERATOR VALUE
       Takes a field, operator and value. performs the magic necessary to make
       your database treat this	clause as case insensitive.

       Returns a FIELD OPERATOR	VALUE triple.

       DBIx::SearchBuilder::Handle emulates nested transactions, by keeping a
       transaction stack depth.

       NOTE: In	nested transactions you	shouldn't mix rollbacks	and commits,
       because only last action	really do commit/rollback. For example next
       code would produce desired results:


       Only last action(Commit in example) finilize transaction	in DB.


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

       EndTransaction [Action =_ 'commit'] [Force =_ 0]

       Tells to	end the	current	transaction. Takes "Action" argument that
       could be	"commit" or "rollback",	the default value is "commit".

       If "Force" argument is true then	all nested transactions	would be
       committed or rolled back.

       If there	is no transaction in progress then method throw	warning	unless
       action is forced.

       Method returns true on success or false if an error occurred.

       Commit [FORCE]

       Tells to	commit the current SQL transaction.

       Method uses "EndTransaction" method, read its description.

       Rollback	[FORCE]

       Tells to	abort the current SQL transaction.

       Method uses "EndTransaction" method, read its description.


       Force the handle	to rollback.  Whether or not we're deep	in nested


       Returns the current depth of the	nested transaction stack.  Returns
       "undef" if there	is no connection to database.

       takes an	SQL SELECT statement and massages it to	return ROWS_PER_PAGE
       starting	with FIRST_ROW;

   Join	{ Paramhash }
       Takes a paramhash of everything Searchbuildler::Record does plus	a
       parameter called	'SearchBuilder'	that contains a	ref to a SearchBuilder

       This performs the join.

       Takes a "SearchBuilder" and "ALIAS" in a	hash and resturns true if
       restrictions of the query allow NULLs in	a table	joined with the	ALIAS,
       otherwise returns false value which means that you can use normal join
       instead of left for the aliased table.

       Works only for queries have been	built with "Join" in
       DBIx::SearchBuilder and "Limit" in DBIx::SearchBuilder methods, for
       other cases return true value to	avoid fault optimizations.

   DistinctQuery STATEMENTREF
       takes an	incomplete SQL SELECT statement	and massages it	to return a
       DISTINCT	result set.

   DistinctCount STATEMENTREF
       takes an	incomplete SQL SELECT statement	and massages it	to return a
       DISTINCT	result set.

       Takes a single argument,	a message to log.

       Currently prints	that message to	STDERR

       See "DateTimeFunction" for details on supported functions.  This	method
       is for implementers of custom DB	connectors.

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

       Takes named arguments:

       o   Field - SQL expression date/time function should be applied to.
	   Note	that this argument is used as is without any kind of quoting.

       o   Type	- name of the function,	see supported values below.

       o   Timezone - optional hash reference with From	and To values, see
	   "ConvertTimezoneFunction" for details.

       Returns SQL statement. Returns NULL if function is not supported.

       Supported functions

       Type value in "DateTimeFunction"	is case	insesitive. Spaces,
       underscores and dashes are ignored. So 'date time', 'DateTime' and
       'date_time' are all synonyms. The following functions are supported:

       o   date	time - as is, no conversion, except applying timezone
	   conversion if it's provided.

       o   time	- time only

       o   hourly - datetime prefix up to the hours, e.g. '2010-03-25 16'

       o   hour	- hour,	0 - 23

       o   date	- date only

       o   daily - synonym for date

       o   day of week - 0 - 6,	0 - Sunday

       o   day - day of	month, 1 - 31

       o   day of month	- synonym for day

       o   day of year - 1 - 366, support is database dependent

       o   month - 1 - 12

       o   monthly - year and month prefix, e.g. '2010-11'

       o   year	- e.g. '2023'

       o   annually - synonym for year

       o   week	of year	- 0-53,	presence of zero week, 1st week	meaning	and
	   whether week	starts on Monday or Sunday heavily depends on

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

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

       If there	is problem with	arguments or timezones are equal then Field
       returned	without	any function applied. 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 documentation for
       your DB.

       Generates a function to calculate interval in seconds between two
       dates. Takes From and To	arguments which	can be either scalar or	a
       hash. Hash is processed with "CombineFunctionWithField" in

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

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

       o   small

	   NULLs are smaller then anything else, so come first when order is
	   ASC and last	otherwise.

       o   large

	   NULLs are larger then anything else.

       o   first

	   NULLs are always first.

       o   last

	   NULLs are always last.

       o   default

	   Return back to DB's default behaviour.

       When called without argument returns metadata required to generate SQL.

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

       When we get rid of the Searchbuilder::Handle, we	need to	disconnect
       from the	database

       Jesse Vincent,

       perl(1),	DBIx::SearchBuilder

perl v5.32.0			  2020-07-06	DBIx::SearchBuilder::Handle(3)

NAME | SYNOPSIS | DESCRIPTION | SimpleUpdateFromSelect | DeleteFromSelect | AUTHOR | SEE ALSO

Want to link to this manual page? Use this URL:

home | help