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
|
use strict;
# $Id$
use warnings;
use DBI;
use Data::Dumper;
$Data::Dumper::Maxdepth = 4;
use constant LONG_READ_LEN => 8000;
my %options = (
DbSrcServer => '(local)',
DbSrcDatabase => 'Helpdesk2',
DbSrcLoginName => 'sa',
DbSrcPassword => '',
);
my @dbhPool;
##########################################
### Functions
##########################################
sub newDbh()
{my $dbh;
if(defined($options{DbSrcServer}) && defined($options{DbSrcLoginName}) && defined($options{DbSrcDatabase}))
{ my $dsn = "DRIVER={SQL Server};SERVER=$options{DbSrcServer};DATABASE=$options{DbSrcDatabase};NETWORK=dbmssocn;UID=$options{DbSrcLoginName};PWD=$options{DbSrcPassword}";
# print "DSN: $dsn\n\n";
$dbh = DBI->connect("DBI:ODBC:$dsn") || die "DBI connect failed: $DBI::errstr\n";
$dbh->{AutoCommit} = 0; # enable transactions, if possible
$dbh->{RaiseError} = 0;
$dbh->{PrintError} = 1; # use RaiseError instead
$dbh->{ShowErrorStatement} = 1;
push @dbhPool, $dbh;
return($dbh);
}
}
sub test($)
{ my ($outputTempate) = @_;
my $dbh = newDbh();
my $sth = $dbh->prepare('select ID from (select 1 as ID union select 2 as ID union select 3 as ID) tmp order by ID');
$sth->execute();
# print '$sth->{Active}: ', $sth->{Active}, "\n";
do
{ for(my $rowRef = undef; $rowRef = $sth->fetchrow_hashref('NAME'); )
{ #print '%$rowRef ', Dumper(\%$rowRef), "\n";
innerTest($outputTempate);
}
} while($sth->{odbc_more_results});
}
my $innerTestSth;
sub innerTest($)
{ my ($outputTempate) = @_;
my %outputData;
my $queryInputParameter1 = 2222;
my $queryOutputParameter = $outputTempate;
my $sth;
if(!defined $innerTestSth)
{ my $dbh = newDbh();
$innerTestSth = $dbh->prepare('{? = call testPrc(?) }');
}
$sth = $innerTestSth;
$sth->bind_param_inout(1, \$queryOutputParameter, 30, { TYPE => DBI::SQL_INTEGER });
$sth->bind_param(2, $queryInputParameter1, { TYPE => DBI::SQL_INTEGER });
# $sth->trace(1);#, 'DbiTest.txt');
$sth->execute();
print '$sth->{Active}: ', $sth->{Active}, "\n";
do
{ for(my $rowRef = undef; $rowRef = $sth->fetchrow_hashref('NAME'); )
{ print '%$rowRef2 ', Dumper(\%$rowRef), "\n";
}
} while($sth->{odbc_more_results});
print '$queryOutputParameter: \'', $queryOutputParameter,
'\' expected: (', $queryInputParameter1 + 1, ")\n\n";
}
##########################################
### Test
##########################################
#test(0);
#test(10);
#test(100);
#test(' ');
test(10);
##########################################
### Cleanup...
##########################################
foreach my $dbh (@dbhPool)
{ $dbh->rollback();
$dbh->disconnect();
}
|
