PerlでDB接続

(8.0.22)
接続元:CentOS7
接続先:CentOS7

https://tutorial.perlzemi.com/blog/20161122147981.html
https://qiita.com/yusuke_dev/items/7f0ca12ced72363f9448

-- 1. 環境設定
-- 1.1. perl-DBIをインストール
yum update
yum install perl-DBI perl-DBD-MySQL

-- 1.2. 接続先DBユーザの認証プラグインの変更

select host, user, plugin from mysql.user;
alter user 'test'@'%' identified with mysql_native_password by 'test';
select host, user, plugin from mysql.user;


-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',current_timestamp());
insert into tab1 values(2,'B',current_timestamp());
select * from tab1;

 

-- 3. DB接続確認

vim a.pl

---

#!/usr/bin/perl

use DBI;
use strict;

my $dbh = DBI->connect('dbi:mysql:dbname=test;host=mmm066;port=3306', 'test', 'test',{AutoCommit => 0}) or die "Error\n";

print "---- select(バインド変数有) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= ?");
$sth->bind_param(1,1);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
print "---- \n";
$sth->bind_param(1,2);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- select(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= 1");
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- insert(バインド変数有) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(?,?,?)");
$sth->bind_param(1,100);
$sth->bind_param(2,"X");
$sth->bind_param(3,"2021/01/01");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

print "---- insert(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

$dbh->disconnect();

---

perl a.pl

 

(19c)
接続元:CentOS7
接続先:CentOS7

https://engineeringnote.hateblo.jp/entry/database/oracle/perl-DBI-install
http://ozuma.o.oo7.jp/works/code/perl-dbi.html


-- 1. 環境設定
-- 1.1. Oracle Instant Clientのダウンロードとインストール

rpm -ivh oracle-instantclient19.9-basic-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-devel-19.9.0.0.0-1.x86_64.rpm
rpm -ivh oracle-instantclient19.9-sqlplus-19.9.0.0.0-1.x86_64.rpm


-- 1.2. perl-DBIをインストール
yum update
yum install perl-DBI perl-ExtUtils-MakeMaker

-- 1.3. DBD-Oracleをインストール

export ORACLE_HOME='/usr/lib/oracle/19.9/client64'
export C_INCLUDE_PATH='/usr/include/oracle/19.9/client64'
export LD_LIBRARY_PATH='/usr/lib/oracle/19.9/client64/lib'

tar xvf DBD-Oracle-1.80.tar.gz
cd DBD-Oracle-1.80
perl Makefile.PL -m /usr/share/oracle/19.9/client64/demo/demo.mk
yum install gcc
make
make install
man DBD::Oracle


-- 2. テストテーブル作成

drop table tab1 purge;
create table tab1(col1 int,col2 varchar2(10),col3 timestamp);
insert into tab1 values(1,'A',sysdate);
insert into tab1 values(2,'B',sysdate);
commit;
select * from tab1;


-- 3. DB接続確認

vim a.pl

---

#!/usr/bin/perl

use DBI;
use strict;

my $dbh = DBI->connect('dbi:Oracle://mmm065:1521/pdb1.example.com', 'test', 'test',{AutoCommit => 0}) or die "Error\n";

print "---- select(バインド変数有) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= ?");
$sth->bind_param(1,1);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
print "---- \n";
$sth->bind_param(1,2);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- select(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= 1");
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- insert(バインド変数有) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(?,?,?)");
$sth->bind_param(1,100);
$sth->bind_param(2,"X");
$sth->bind_param(3,"2021/01/01");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

print "---- insert(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

$dbh->disconnect();

---

perl a.pl

 

 

(13)
接続元:CentOS7
接続先:CentOS8

http://ozuma.o.oo7.jp/works/code/perl-dbi.html

-- 1. 環境設定
-- 1.1. perl-DBIをインストール
yum update
yum install perl-DBI perl-DBD-Pg

-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 timestamp);
insert into tab1 values(1,'A',clock_timestamp());
insert into tab1 values(2,'B',clock_timestamp());
select * from tab1;

-- 3. DB接続確認

vim a.pl

---

#!/usr/bin/perl

use DBI;
use strict;

my $dbh = DBI->connect('dbi:Pg:dbname=test;host=mmm067;port=5432', 'postgres', 'postgres',{AutoCommit => 0}) or die "Error\n";

print "---- select(バインド変数有) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= ?");
$sth->bind_param(1,1);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
print "---- \n";
$sth->bind_param(1,2);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- select(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= 1");
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- insert(バインド変数有) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(?,?,?)");
$sth->bind_param(1,100);
$sth->bind_param(2,"X");
$sth->bind_param(3,"2021/01/01");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

print "---- insert(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

$dbh->disconnect();

---

perl a.pl

 

(2019)
接続元:CentOS7
接続先:Windows Server 2016

https://tutorial.perlzemi.com/blog/20101218130016.html
https://qiita.com/shirok/items/c57482f26e1d1a595814
https://docs.microsoft.com/ja-jp/sql/connect/odbc/linux-mac/installing-the-microsoft-odbc-driver-for-sql-server?view=sql-server-ver15
https://ptsv.jp/2017/06/21/centos-%E3%81%8B%E3%82%89-windows%E4%B8%8A%E3%81%AEsqlserver%E3%81%B8%E3%81%AE%E9%81%93%E3%81%AE%E3%82%8A/


-- 1. 環境設定
-- 1.1. UnixODBCのインストール
yum update -y
curl https://packages.microsoft.com/config/rhel/7/prod.repo > /etc/yum.repos.d/mssql-release.repo

yum remove unixODBC-utf16 unixODBC-utf16-devel
ACCEPT_EULA=Y yum install msodbcsql17
yum install unixODBC-devel


ls /usr/lib64/libodbc.so


-- 1.2. DBD::ODBCモジュールのインストール
yum install perl-CPAN gcc
cpan DBD::ODBC


-- 1.3. UnixODBCの設定
odbcinst -j

cat /etc/odbcinst.ini

vim /etc/odbc.ini

[sqlserver]
Driver = ODBC Driver 17 for SQL Server
Description = Microsoft SQL Server
Server = mmm064
Port = 1433
Database = test


-- 1.4. UnixODBCの設定の確認

isql sqlserver sa 'Aaa!1234' -v

※エンターで終了


-- 2. テストテーブル作成

drop table tab1;
create table tab1(col1 int,col2 varchar(10),col3 datetime2);
insert into tab1 values(1,'A',getdate());
insert into tab1 values(2,'B',getdate());
select * from tab1;


-- 3. DB接続確認

vim a.pl

---

use strict;
use warnings;

use DBI;

my $dbh = DBI->connect('dbi:ODBC:sqlserver', 'sa', 'Aaa!1234',{AutoCommit => 0}) or die "Error\n";

print "---- select(バインド変数有) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= ?");
$sth->bind_param(1,1);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
print "---- \n";
$sth->bind_param(1,2);
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- select(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("select * from tab1 where col1 >= 1");
$sth->execute();
while (my $ary_ref = $sth->fetchrow_arrayref) {
my ($col1, $col2, $col3) = @$ary_ref;
print "$col1\t$col2\t$col3\n";
}
$sth->finish();

print "---- insert(バインド変数有) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(?,?,?)");
$sth->bind_param(1,100);
$sth->bind_param(2,"X");
$sth->bind_param(3,"2021/01/01");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

print "---- insert(バインド変数なし) ---- \n";
my $sth = $dbh->prepare("insert into tab1(col1,col2,col3) values(200,'Y','2022/01/01')");
my $ret = $sth->execute();
$dbh->commit;
print "\$ret=$ret\n";
$sth->finish();

$dbh->disconnect();

---

perl a.pl