use strict;
use DBI;
my $hDb = DBI->connect("DBI:Excel:file=dbdtest.xls", undef, undef,
{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. Save this Excel file
----
$hDb->func('newxl.xls', 'save');