1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
|
use strict;
use DBI;
use Spreadsheet::ParseExcel::FmtJapan2;
my $oFmtJ = Spreadsheet::ParseExcel::FmtJapan2->new( Code => 'euc');
my $hDb = DBI->connect("DBI:Excel:file=dbdtest.xls", undef, undef,
{ xl_fmt => $oFmtJ,
xl_vtbl =>
{TESTV =>
{
sheetName => 'TEST_V',
ttlRow => 5,
startCol => 1,
colCnt => 4,
datRow => 6,
datLmt => 4,
}
}
});
print<<"----";
#--------------------------------------------------------------
# 1. SELECT(with no params)
----
my $hSt = $hDb->prepare(q/SELECT * FROM TEST/);
$hSt->execute();
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 2. SELECT(with params)
----
$hSt = $hDb->prepare(q/SELECT * FROM TEST WHERE No > ? AND Age < ?/);
$hSt->execute(1, 50);
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 3. INSERT(with params)
----
$hSt = $hDb->prepare(q/INSERT INTO TEST VALUES (?, ?, ?, ?, ?)/);
$hSt->execute(4, 'Newman 4', 'New Dept', 30, 'newman4@hippo2000.net');
$hSt->execute(5, 'Newman 5', 'New Dept', 32, 'newman5@hippo2000.net');
print<<"----";
#--------------------------------------------------------------
# 4. DELETE(with params)
----
$hSt = $hDb->prepare(q/DELETE FROM TEST WHERE No = ?/);
$hSt->execute(1);
$hSt->execute(3);
print<<"----";
#--------------------------------------------------------------
# 5. UPDATE(with params)
----
$hSt = $hDb->prepare(q/UPDATE TEST SET Mail = ? WHERE No = ?/);
$hSt->execute('Mail Upd', 2);
print<<"----";
#--------------------------------------------------------------
# 6. SELECT(again)
----
$hSt = $hDb->prepare(q/SELECT * FROM TEST/);
$hSt->execute();
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 7. CREATE TABLE, DROP TABLE
----
$hDb->do(q/CREATE TABLE NEW_TBL (ID CHAR(10), NO INTEGER, NAME VARCHAR(200))/);
$hDb->do(q/DROP TABLE DEL_TEST/);
print<<"----";
#--------------------------------------------------------------
# 1. SELECT(with no params): VTBL
----
$hSt = $hDb->prepare(q/SELECT * FROM TESTV/);
$hSt->execute();
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 2. SELECT(with params)
----
$hSt = $hDb->prepare(q/SELECT * FROM TESTV WHERE No > ? /);
$hSt->execute(1);
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 3. UPDATE(with params)
----
$hSt = $hDb->prepare(q/UPDATE TESTV SET Age = ? WHERE No = ?/);
$hSt->execute(50, 3);
print<<"----";
#--------------------------------------------------------------
# 4. DELETE(with params)
----
$hSt = $hDb->prepare(q/DELETE FROM TESTV WHERE No = ?/);
$hSt->execute(2);
print<<"----";
#--------------------------------------------------------------
# 5. INSERT(with params)
----
$hSt = $hDb->prepare(q/INSERT INTO TESTV VALUES (?, ?, ?, ?, ?)/);
$hSt->execute(4, 'Newman 4', 'New Dept', 30, 'KABA');
$hSt->execute(5, 'Newman 5', 'New Dept', 32, 'DESUYO');
print<<"----";
#--------------------------------------------------------------
# 6. SELECT(again)
----
$hSt = $hDb->prepare(q/SELECT * FROM TESTV/);
$hSt->execute();
while(my $raRes = $hSt->fetchrow_arrayref()) {
print "DATA:", join(',', @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 7. List tables, data sources
----
foreach my $sTbl ($hDb->func('list_tables')){
print "TABLE: $sTbl\n";
}
my $hDr = DBI->install_driver("Excel");
foreach my $sDsn ($hDr->data_sources({xl_data => '.'})) {
print "DSN: $sDsn\n";
}
print<<"----";
#--------------------------------------------------------------
# 8. Japanese test
----
my $hStJ = $hDb->prepare(q/SELECT * FROM TEST_JAPAN/);
$hStJ->execute();
while(my $raRes = $hStJ->fetchrow_arrayref()) {
print "DATA:", join(',', map {$_||=''} @$raRes), "\n";
}
print<<"----";
#--------------------------------------------------------------
# 9. Save this Excel file
----
$hDb->func('newxlj.xls', 'save');
|
