blib/lib/Win32/ASP/DB.pm | |||
---|---|---|---|
Criterion | Covered | Total | % |
statement | 1 | 3 | 33.3 |
branch | n/a | ||
condition | n/a | ||
subroutine | 1 | 1 | 100.0 |
pod | n/a | ||
total | 2 | 4 | 50.0 |
line | stmt | bran | cond | sub | pod | time | code |
---|---|---|---|---|---|---|---|
1 | ############################################################################ | ||||||
2 | # | ||||||
3 | # Win32::ASP::DB - an abstract parent class for database access | ||||||
4 | # in the Win32-ASP-DB system | ||||||
5 | # | ||||||
6 | # Author: Toby Everett | ||||||
7 | # Revision: 0.02 | ||||||
8 | # Last Change: | ||||||
9 | ############################################################################ | ||||||
10 | # Copyright 1999, 2000 Toby Everett. All rights reserved. | ||||||
11 | # | ||||||
12 | # This file is distributed under the Artistic License. See | ||||||
13 | # http://www.ActiveState.com/corporate/artistic_license.htm or | ||||||
14 | # the license that comes with your perl distribution. | ||||||
15 | # | ||||||
16 | # For comments, questions, bugs or general interest, feel free to | ||||||
17 | # contact Toby Everett at teverett@alascom.att.com | ||||||
18 | ############################################################################ | ||||||
19 | |||||||
20 | package Win32::ASP::DB; | ||||||
21 | 1 | 1 | 2573 | use Error qw/:try/; | |||
0 | |||||||
0 | |||||||
22 | use Win32::ASP::Error; | ||||||
23 | use Win32::OLE::Variant; | ||||||
24 | |||||||
25 | use strict vars; | ||||||
26 | |||||||
27 | sub new { | ||||||
28 | my $class = shift; | ||||||
29 | my($provider, $connectstring) = @_; | ||||||
30 | |||||||
31 | my $self = { | ||||||
32 | db => undef, | ||||||
33 | }; | ||||||
34 | |||||||
35 | bless $self, $class; | ||||||
36 | |||||||
37 | $self->{db} = $main::Server->CreateObject('ADODB.Connection') or | ||||||
38 | throw Win32::ASP::Error::DB::init; | ||||||
39 | |||||||
40 | $self->{db}->{Provider} = $provider; | ||||||
41 | $self->{db}->Open($connectstring); | ||||||
42 | $self->{db}->State or | ||||||
43 | throw Win32::ASP::Error::DB::connect (username => Win32::LoginName()); | ||||||
44 | |||||||
45 | return $self; | ||||||
46 | } | ||||||
47 | |||||||
48 | sub exec_sql { | ||||||
49 | my $self = shift; | ||||||
50 | my($SQL, %params) = @_; | ||||||
51 | |||||||
52 | my $results = $self->{db}->Execute($SQL) or | ||||||
53 | throw Win32::ASP::Error::SQL::exec (SQL => $SQL, DB_obj => $self); | ||||||
54 | |||||||
55 | $params{error_no_records} and $results->EOF and | ||||||
56 | throw Win32::ASP::Error::SQL::no_records (SQL => $SQL); | ||||||
57 | |||||||
58 | return $results; | ||||||
59 | } | ||||||
60 | |||||||
61 | sub get_sql_errors { | ||||||
62 | my $self = shift; | ||||||
63 | |||||||
64 | my $errors = $self->{db}->Errors; | ||||||
65 | my $retval; | ||||||
66 | foreach my $i (0..$errors->Count-1) { | ||||||
67 | $retval .= "Error $i:\n"; | ||||||
68 | foreach my $j (qw/Number Description Source SQLState NativeError/) { | ||||||
69 | $retval .= " $j: ".$errors->Item(0)->{$j}."\n"; | ||||||
70 | } | ||||||
71 | $retval .= "\n"; | ||||||
72 | } | ||||||
73 | return $retval; | ||||||
74 | } | ||||||
75 | |||||||
76 | sub insert { | ||||||
77 | my $self = shift; | ||||||
78 | my($tablename, @data) = @_; | ||||||
79 | |||||||
80 | scalar(@data) or return; | ||||||
81 | |||||||
82 | my $recSet = $main::Server->CreateObject('ADODB.Recordset') or | ||||||
83 | throw Win32::ASP::Error::SQL::insert | ||||||
84 | (error_type => 'recordset', tablename => $tablename, DB_obj => $self); | ||||||
85 | |||||||
86 | $recSet->Open($tablename, $self->{db}, 1, 3, 512); # adOpenKeyset, adLockOptimistic, adCmdTableDirect | ||||||
87 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::insert | ||||||
88 | (error_type => 'tableopen', tablename => $tablename, DB_obj => $self); | ||||||
89 | |||||||
90 | $recSet->AddNew; | ||||||
91 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::insert | ||||||
92 | (error_type => 'addnew', tablename => $tablename, DB_obj => $self); | ||||||
93 | |||||||
94 | foreach my $i (@data) { | ||||||
95 | $recSet->Fields->Item($i->{field})->{Value} = defined $i->{value} ? $i->{value} : Variant(1); | ||||||
96 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::insert | ||||||
97 | (error_type => 'setvalue', tablename => $tablename, DB_obj => $self, write_pair => $i); | ||||||
98 | } | ||||||
99 | |||||||
100 | $recSet->Update; | ||||||
101 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::insert | ||||||
102 | (error_type => 'update', tablename => $tablename, DB_obj => $self); | ||||||
103 | |||||||
104 | return $recSet; | ||||||
105 | } | ||||||
106 | |||||||
107 | sub update { | ||||||
108 | my $self = shift; | ||||||
109 | my($tablename, $condition, @data) = @_; | ||||||
110 | |||||||
111 | scalar(@data) or return; | ||||||
112 | |||||||
113 | my $recSet = $main::Server->CreateObject('ADODB.Recordset') or | ||||||
114 | throw Win32::ASP::Error::SQL::update | ||||||
115 | (error_type => 'recordset', tablename => $tablename, DB_obj => $self); | ||||||
116 | |||||||
117 | $recSet->Open("SELECT * FROM $tablename WHERE $condition", $self->{db}, 3, 3); # adOpenStatic, adLockOptimistic | ||||||
118 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::update | ||||||
119 | (error_type => 'tableopen', tablename => $tablename, DB_obj => $self); | ||||||
120 | |||||||
121 | $recSet->{recordCount} != 1 and | ||||||
122 | throw Win32::ASP::Error::SQL::update | ||||||
123 | (error_type => 'condition', tablename => $tablename, DB_obj => $self, condition => $condition); | ||||||
124 | |||||||
125 | foreach my $i (@data) { | ||||||
126 | $recSet->Fields->Item($i->{field})->{Value} = defined $i->{value} ? $i->{value} : Variant(1); | ||||||
127 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::update | ||||||
128 | (error_type => 'setvalue', tablename => $tablename, DB_obj => $self, write_pair => $i); | ||||||
129 | } | ||||||
130 | |||||||
131 | $recSet->Update; | ||||||
132 | Win32::OLE->LastError and throw Win32::ASP::Error::SQL::update | ||||||
133 | (error_type => 'update', tablename => $tablename, DB_obj => $self); | ||||||
134 | |||||||
135 | return $recSet; | ||||||
136 | } | ||||||
137 | |||||||
138 | sub begin_trans { | ||||||
139 | my $self = shift; | ||||||
140 | |||||||
141 | $self->{translevel}++; | ||||||
142 | $self->{translevel} == 1 and $self->{db}->BeginTrans; | ||||||
143 | } | ||||||
144 | |||||||
145 | sub commit_trans { | ||||||
146 | my $self = shift; | ||||||
147 | |||||||
148 | $self->{translevel}--; | ||||||
149 | $self->{translevel} == 0 and $self->{db}->CommitTrans; | ||||||
150 | } | ||||||
151 | |||||||
152 | |||||||
153 | |||||||
154 | #################### Error Classes ############################ | ||||||
155 | |||||||
156 | |||||||
157 | package Win32::ASP::Error::DB; | ||||||
158 | @Win32::ASP::Error::DB::ISA = qw/Win32::ASP::Error/; | ||||||
159 | |||||||
160 | |||||||
161 | package Win32::ASP::Error::DB::init; | ||||||
162 | @Win32::ASP::Error::DB::init::ISA = qw/Win32::ASP::Error::DB/; | ||||||
163 | |||||||
164 | sub _as_html { | ||||||
165 | my $self = shift; | ||||||
166 | return "Unable to create ADODB.Connection object. ASP server is incorrectly setup."; | ||||||
167 | } | ||||||
168 | |||||||
169 | |||||||
170 | package Win32::ASP::Error::DB::connect; | ||||||
171 | @Win32::ASP::Error::DB::connect::ISA = qw/Win32::ASP::Error::DB/; | ||||||
172 | |||||||
173 | #Parameters: username | ||||||
174 | |||||||
175 | sub _as_html { | ||||||
176 | my $self = shift; | ||||||
177 | |||||||
178 | my $username = $self->username; | ||||||
179 | return "Unable to login to database as $username."; | ||||||
180 | } | ||||||
181 | |||||||
182 | |||||||
183 | |||||||
184 | package Win32::ASP::Error::SQL; | ||||||
185 | @Win32::ASP::Error::SQL::ISA = qw/Win32::ASP::Error/; | ||||||
186 | |||||||
187 | sub _error_msg { | ||||||
188 | my $self = shift; | ||||||
189 | |||||||
190 | my $error_type = $self->error_type; | ||||||
191 | |||||||
192 | if ($error_type eq 'recordset') { | ||||||
193 | return "Couldn't create RecordSet object."; | ||||||
194 | } elsif ($error_type eq 'tablename') { | ||||||
195 | return "Couldn't open table."; | ||||||
196 | } elsif ($error_type eq 'addnew') { | ||||||
197 | return "Couldn't add new record to table."; | ||||||
198 | } elsif ($error_type eq 'condition') { | ||||||
199 | return "The condition '".$self->condition."' did not uniquely specify a record."; | ||||||
200 | } elsif ($error_type eq 'setvalue') { | ||||||
201 | return "Couldn't set field '".$self->write_pair->{field}."' to value ". | ||||||
202 | (defined $self->write_pair->{value} ? "'".($self->write_pair->{value})."'" : 'NULL')."."; | ||||||
203 | } elsif ($error_type eq 'update') { | ||||||
204 | return "Couldn't write changes to table."; | ||||||
205 | } | ||||||
206 | } | ||||||
207 | |||||||
208 | |||||||
209 | package Win32::ASP::Error::SQL::exec; | ||||||
210 | @Win32::ASP::Error::SQL::exec::ISA = qw/Win32::ASP::Error::SQL/; | ||||||
211 | |||||||
212 | #Parameters: DB_obj, SQL | ||||||
213 | |||||||
214 | sub _as_html { | ||||||
215 | my $self = shift; | ||||||
216 | |||||||
217 | my $SQL = $self->SQL; | ||||||
218 | my $errors = $self->DB_obj->get_sql_errors; | ||||||
219 | return < | ||||||
220 | There was an error executing the following SQL:
|
||||||
221 | |
||||||
222 | $SQL | ||||||
223 | |||||||
224 | The errors encountered were:
|
||||||
225 | |
||||||
226 | $errors | ||||||
227 | |||||||
228 | ENDHTML | ||||||
229 | } | ||||||
230 | |||||||
231 | |||||||
232 | package Win32::ASP::Error::SQL::insert; | ||||||
233 | @Win32::ASP::Error::SQL::insert::ISA = qw/Win32::ASP::Error::SQL/; | ||||||
234 | |||||||
235 | #Parameters: tablename, , error_type, DB_obj | ||||||
236 | |||||||
237 | sub _as_html { | ||||||
238 | my $self = shift; | ||||||
239 | |||||||
240 | my $tablename = $self->tablename; | ||||||
241 | my $error_msg = $self->error_msg; | ||||||
242 | my $errors = $self->DB_obj->get_sql_errors; | ||||||
243 | return < | ||||||
244 | There were errors encountered inserting a record into the table '$tablename'.
|
||||||
245 | The error type was: $error_msg
|
||||||
246 | The ADO errors were:
|
||||||
247 | |
||||||
248 | $errors | ||||||
249 | |||||||
250 | ENDHTML | ||||||
251 | } | ||||||
252 | |||||||
253 | |||||||
254 | package Win32::ASP::Error::SQL::no_records; | ||||||
255 | @Win32::ASP::Error::SQL::no_records::ISA = qw/Win32::ASP::Error::SQL/; | ||||||
256 | |||||||
257 | #Parameters: SQL | ||||||
258 | |||||||
259 | sub _as_html { | ||||||
260 | my $self = shift; | ||||||
261 | |||||||
262 | my $SQL = $self->SQL; | ||||||
263 | return < | ||||||
264 | There was an error executing the following SQL:
|
||||||
265 | |
||||||
266 | $SQL | ||||||
267 | |||||||
268 | There were no records returned and there should have been.
|
||||||
269 | ENDHTML | ||||||
270 | } | ||||||
271 | |||||||
272 | |||||||
273 | package Win32::ASP::Error::SQL::update; | ||||||
274 | @Win32::ASP::Error::SQL::update::ISA = qw/Win32::ASP::Error::SQL/; | ||||||
275 | |||||||
276 | #Parameters: tablename, error_type, DB_obj | ||||||
277 | |||||||
278 | sub _as_html { | ||||||
279 | my $self = shift; | ||||||
280 | |||||||
281 | my $tablename = $self->tablename; | ||||||
282 | my $error_msg = $self->error_msg; | ||||||
283 | my $errors = $self->DB_obj->get_sql_errors; | ||||||
284 | return < | ||||||
285 | There were errors encountered updating a record into the table '$tablename'.
|
||||||
286 | The error type was: $error_msg
|
||||||
287 | The ADO errors were:
|
||||||
288 | |
||||||
289 | $errors | ||||||
290 | |||||||
291 | ENDHTML | ||||||
292 | } | ||||||
293 | |||||||
294 | 1; |