Connecting to the database

DBIx::Custom
here

Use the connect method to connect to the database.

# connect method
DBIx::Custom->connect($dsn, $user, $password, $dbi_attr, $dbix_custom_attr);

The first argument is the data source name, the second argument is the user name, the third argument is the password, and the fourth argument is DBI attribute, the fifth argument is the DBIx::Custom attribute.

If the connection is successful, the DBIx::Custom object will be returned as the return value.

The following methods are also provided, but if you do not need them, use the above methods.

#connect method (other way)
my $dbi = DBIx::Custom->connect(
  dsn => $dsn,
  user => $user,
  password => $password,
  option => $dbi_attr,
  %dbix_custom_attr
);

Data source name

Try connecting to SQLite by specifying the data source name.

my $dsn = "dbi: SQLite: dbname = bookshop";
my $dbi = DBIx::Custom->connect($dsn);

To connect to the database, specify the data source name as the first argument. The data source will be different for each database. If the connection is successful, the DBIx::Custom object will be returned as the return value.

Username and password

If a user name and password are required to connect to the database, specify them in the second and third arguments.

my $user ='ken';
my $password ='utj4857';
my $dbi = DBIx::Custom->connect($dsn, $user, $password);

DBI attributes

DBI attributes can be specified with the 4th argument. Up to the 4th argument, it is the same as the argument of DBI's connect method. If you don't need a user and password, set undef.

my $dbi_attr = {sqlite_unicode => 1};
my $dbi = DBIx::Custom->connect($dsn, $user, $password, $dbi_attr);

The following values are set by default for DBI attributes. It throws an exception for the error and the commit is in autocommit mode. This can be overridden.

{
  RaiseError => 1,
  PrintError => 0,
  AutoCommit => 1
}

Database connection management

Connection management for DBIx::Custom is done by a proven and trusted DBIx::Connector.

So DBIx::Custom works fine even when the web application is run on the prefolk server.

Transactions can also be executed using the DBIx::Connector's txn method.

#Transaction execution
$dbi->connector->txn(sub {
  #Process 1
  ...

  #Process 2
  ...
});

Connect to MariaDB

To connect to MariaDB using the connect method:

# UTF-8
my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop",'ken','! LFKD%$&', {mysql_enable_utf8mb4 => 1});

# For 3-byte UTF-8
my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop",'ken','! LFKD%$&', {mysql_enable_utf8 => 1});

Specify the data source name in the first argument, the database user name in the second argument, and the database password in the third argument. Specify DBI attributes in the fourth argument. The return value will be a DBIx::Custom object.

If you set mysql_enable_utf8mb4 to 1 as DBI attribute, it will be a database string. It is convenient because it automatically converts Perl's decoded character string and UTF-8 byte string when updating / retrieving. For older MariaDB, which only supports 3 bytes of UTF-8, you can use mysql_enable_utf8.

MariaDB also allows you to easily connect to a remote MariaDB server by specifying a host name or port number.

my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop; host = somehost.com; port = 3306",'ken','! LFKD%$&');

Connect to MySQL

To connect to MySQL using the connect method:

# UTF-8
my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop",'ken','! LFKD%$&', {mysql_enable_utf8mb4 => 1});

# For 3-byte UTF-8
my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop",'ken','! LFKD%$&', {mysql_enable_utf8 => 1});

If you set mysql_enable_utf8mb4 to 1 as DBI attribute, it will be a database string. It is convenient because it automatically converts Perl's decoded character string and UTF-8 byte string when updating / retrieving. For older MySQL, which only supports 3 bytes of UTF-8, you can use mysql_enable_utf8.

In MySQL, you can easily connect to a remote MySQL server by specifying the host name and port number.

my $dbi = DBIx::Custom->connect("dbi: mysql: database = bookshop; host = somehost.com; port = 3306",'ken','! LFKD%$&');

Connect to PostgreSQL

To connect to PostgreSQL using the connect method:

my $dbi = DBIx::Custom->connect("dbi: Pg: dbname = bookshop",'ken','! LFKD%$&', {pg_enable_utf8 => 1});

If you set pg_enable_utf8 to 1 as DBI attribute, it will be a database string. It is convenient because it automatically converts Perl's decoded character string and UTF-8 byte string when updating / retrieving.

In PostgreSQL, you can easily connect to a remote PostgreSQL server by specifying the host name and port number.

my $dbi = DBIx::Custom->connect("dbi: Pg: dbname = $dbname; host = somehost.com; port = 5432;",'ken','! LFKD%$&');

Connect to SQLite

To connect to SQLite using the connect method:..

my $dbi = DBIx::Custom->connect("dbi: SQLite: dbname = $database", undef, undef, {sqlite_unicode => 1});

Specify the data source name in the first argument. Specify DBI attributes in the fourth argument. The return value will be a DBIx::Custom object.

If you set sqlite_unicode to 1 as DBI attribute, it will be a database string. It is convenient because it automatically converts Perl's decoded character string and UTF-8 byte string when updating / retrieving.

Also, with SQLite, it is convenient to be able to create a database in memory when conducting tests.

my $dbi = DBIx::Custom->connect("dbi: SQLite: dbname =: memory:", undef, undef, {sqlite_unicode => 1});

Connect to Oracle

To connect to Oracle using the connect method:

When connecting with a net service name

When connecting to Oracle with a net service name, the description is as follows.

#Connect to Oracle (Net servrice name)
my $dbi = DBIx::Custom->connect("dbi: Oracle: $net_service_name");

If you connect with the net service name, you need to define the net service name in the file tnsnames.ora.

tnsnames.ora should be placed in the following path:

$ORACLE_HOME / network / admin / tnsnames.ora

Below is a sample of tnsnames.ora.

mydb =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP) (HOST = localhost) (PORT = 1521))
    )
    (CONNECT_DATA =
      (SID = XE)
    )
  )

In this case, mydb is the net service name. Specify the database name in the SID part. In this example, XE is the database name.

For tnsnames.ora, see this explanation for details.

When directly specifying the database name

To specify the database name directly:

#Connect to Oracle (SID)
my $dbi = DBIx::Custom->connect("dbi: Oracle: host = localhost; port = 1521; sid = $database");

sid is the part that specifies the database name. For example, if you are using the Expression version of Oracle, you can connect by specifying EX. If you do not specify a port number, it will automatically try the ports in the order of 1526, 1521.

Oracle database name

In Oracle, there are a lot of things called database names. For more information on Oracle database names, see here.

Connect to Microsoft SQL Server via ODBC

To connect to Microsoft SQL Server via ODBC using the DBIx::Custom connect method:

For Windows authentication

#Data source name (Windows authentication)
my $dsn = "dbi: ODBC: driver = {SQL Server}; Server = {localhost \\ SQLEXPRESS};"
  . "Trusted_Connection = yes; AutoTranslate = No; Database = master;";

#Connect
my $dbi = DBIx::Custom->connect($dsn);

The data source name starts with "dbi: ODBC:". The value of driver is {SQL Server}. The value specified for Server must be "hostname \\ object name".

If you want to use Windows Authentication, set the value to Yes for Trusted_Connection. AutoTranslate is related to automatic data conversion, but is usually set to No. You can specify the database name you want to connect to Database.

If you want to specify the port number, set the value of Server as follows.

#With port number
Server = {localhost \\ SQLEXPRESS, 1433}

This setting is also true if you use a regular DBI instead of DBIx::Custom.

For SQL Server Authentication

#Data source name (SQL Server authentication)
my $dsn = "dbi: ODBC: driver = {SQL Server}; Server = {localhost \\ SQLEXPRESS};"
  . "Trusted_Connection = No; AutoTranslate = No; Database = master;";

#Connect
my $dbi = DBIx::Custom->connect($dsn,'kimoto','iejfid');

It is almost the same as the case of Windows authentication, but in the case of SQL Server authentication, set the Trusted_Connection part to No. Also, since SQL Server authentication is password-based authentication, specify password as well.

Also, if you want to connect with SQL Server Authentication, you cannot connect unless SQL Server Authentication is enabled, so you need to check what the settings are.

Switching between Windows authentication and SQL Server authentication

Remember to restart SQL Server after changing the settings.

Connect to Microsoft Access

To connect to Microsoft Access via ODBC using the connect method: (This connection method is the same when using Perl and raw DBI.)

mdb file

my $dbi = DBIx::Custom->connect("dbi: ODBC: driver = Microsoft Access Driver (* .mdb); dbq = hoge.mdb");

Specify the data source name in the first argument. The return value will be a DBIx::Custom object.

Note that joins are not supported in mdb files. This is a Microsoft Access limitation.

Microsoft Access 2007 accdb file

First Install the ODBC driver for 2007 from here a> Must be.

Then specify dsn as follows: Note that the above mdb file is slightly different (enclosed in {}).

my $dbi = DBIx::Custom->connect("dbi: ODBC: Driver = {Microsoft Access Driver (* .mdb, * .accdb)}; DBQ = hoge.accdb");

You can use join in the accdb file, but it seems that you can't include. (Dot) in the column. You can use it correctly by changing the separator to "-" etc. as shown below.

$dbi->separator('-');

Microsoft Access 2010 accdb file

Install the ODBC driver for 2010 from here > Must be.

The connection method will be the same as that of 2007.

Connect to DB2

To connect to DB2 using the connect method:

my $dbi = DBIx::Custom->connect("dbi: DB2: db_name", $username, $password);

Specify the data source name in the first argument. The return value will be a DBIx::Custom object.

DBIx::Custom