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
|
use strict;
use warnings;
use lib 't', '.';
require 'lib.pl';
use DBI;
use Test::More;
use vars qw($test_dsn $test_user $test_password);
my ($row, $vers, $test_procs, $dbh, $sth);
eval {$dbh = DBI->connect($test_dsn, $test_user, $test_password,
{ RaiseError => 1, AutoCommit => 1})};
if ($@) {
plan skip_all =>
"no database connection";
}
#
# DROP/CREATE PROCEDURE will give syntax error
# for versions < 5.0
#
if ($dbh->{mysql_serverversion} < 50000) {
plan skip_all =>
"You must have MySQL version 5.0 and greater for this test to run";
}
if (!CheckRoutinePerms($dbh)) {
plan skip_all =>
"Your test user does not have ALTER_ROUTINE privileges.";
}
plan tests => 31;
$dbh->disconnect();
ok ($dbh = DBI->connect($test_dsn, $test_user, $test_password,
{ RaiseError => 1, AutoCommit => 1}));
ok $dbh->do("DROP TABLE IF EXISTS dbd_mysql_t80procs");
my $drop_proc= "DROP PROCEDURE IF EXISTS dbd_mysql_t80testproc";
ok ($dbh->do($drop_proc), "DROP PROCEDURE") or diag "errstr=$DBI::errstr, err=$DBI::err";
my $proc_create = <<EOPROC;
create procedure dbd_mysql_t80testproc() deterministic
begin
declare a,b,c,d int;
set a=1;
set b=2;
set c=3;
set d=4;
select a, b, c, d;
select d, c, b, a;
select b, a, c, d;
select c, b, d, a;
end
EOPROC
ok $dbh->do($proc_create);
my $proc_call = 'CALL dbd_mysql_t80testproc()';
ok $dbh->do($proc_call);
my $proc_select = 'SELECT @a';
ok ($sth = $dbh->prepare($proc_select));
ok $sth->execute();
ok $sth->finish;
ok $dbh->do("DROP PROCEDURE dbd_mysql_t80testproc");
ok $dbh->do("drop procedure if exists test_multi_sets");
$proc_create = <<EOT;
create procedure test_multi_sets ()
deterministic
begin
select user() as first_col;
select user() as first_col, now() as second_col;
select user() as first_col, now() as second_col, now() as third_col;
end
EOT
ok $dbh->do($proc_create);
ok ($sth = $dbh->prepare("call test_multi_sets()"));
ok $sth->execute();
is $sth->{NUM_OF_FIELDS}, 1, "num_of_fields == 1";
my $resultset;
ok ($resultset = $sth->fetchrow_arrayref());
ok defined $resultset;
is @$resultset, 1, "1 row in resultset";
undef $resultset;
ok $sth->more_results();
is $sth->{NUM_OF_FIELDS}, 2, "NUM_OF_FIELDS == 2";
ok ($resultset= $sth->fetchrow_arrayref());
ok defined $resultset;
is @$resultset, 2, "2 rows in resultset";
undef $resultset;
ok $sth->more_results();
is $sth->{NUM_OF_FIELDS}, 3, "NUM_OF_FIELDS == 3";
ok ($resultset= $sth->fetchrow_arrayref());
ok defined $resultset;
is @$resultset, 3, "3 Rows in resultset";
ok $sth->more_results();
is $sth->{NUM_OF_FIELDS}, 0, "NUM_OF_FIELDS == 0"; +
local $SIG{__WARN__} = sub { die @_ };
ok $sth->finish;
ok $dbh->disconnect();
|
