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
|
<?php
// File stored_procedures.inc.php / ibWebAdmin
// Purpose handle the stored procedures ibWebAdmin is using for its own needs
// Author Lutz Brueckner <irie@gmx.de>
// Copyright (c) 2000, 2001, 2002, 2003, 2004 by Lutz Brueckner,
// published under the terms of the GNU General Public Licence v.2,
// see file LICENCE for details
// Created <00/12/22 17:08:38 lb>
//
// $Id: stored_procedures.inc.php,v 1.9 2004/02/07 15:29:17 lbrueckner Exp $
//
// create the stored procedure for the WT_STORED_PROCEDURE mode
//
function sp_limit_create($table, $cols, $order, $dir, $condition, $start, $num) {
global $s_login, $s_fields, $message, $MESSAGES, $binary_output, $binary_error;
if (sp_exist(SP_LIMIT_NAME)) {
sp_remove(SP_LIMIT_NAME);
}
else {
$GLOBALS['message'] .= $MESSAGES['SP_CREATE_INFO'];
}
$cstr = ($condition != '') ? 'WHERE '.$condition : '';
$istr = '';
$k = 0;
$sp = 'CREATE PROCEDURE '.SP_LIMIT_NAME."\n";
$sp .= 'RETURNS (';
for ($i =0; $i<count($s_fields); $i++) {
if (in_array($s_fields[$i]['name'] , $cols) && $s_fields[$i]['table'] == $table) {
$rvar = chr($k / 65 + 65) . chr($k+65);
$sp .= $rvar . ' ' . get_type_string($s_fields[$i]).', ';
$istr .= ":$rvar, ";
$k++;
}
}
$istr = substr($istr, 0, -2);
$sp = substr($sp, 0, -2);
$sp .= ")\nAS\n";
$sp .= "DECLARE VARIABLE cnt INTEGER;\n";
if ($start < 0) {
$sp .= "DECLARE VARIABLE nr INTEGER;\n";
}
$sp .= "BEGIN\n";
$sp .= " cnt = 0;\n";
if ($start < 0) {
$sp .= " SELECT COUNT(*) FROM $table $cstr INTO :nr;\n";
}
$sp .= ' FOR SELECT '.implode(', ', $cols)."\n";
$sp .= " FROM $table $cstr\n";
if (!empty($order)) {
$sp .= " ORDER BY $order $dir\n";
}
$sp .= " INTO $istr\n";
$sp .= " DO\n";
$sp .= " BEGIN\n";
$sp .= " cnt = cnt + 1;\n";
if ($start < 0) {
$sp .= " IF ((cnt > nr + $start) AND (cnt < nr + $start + $num + 1)) THEN\n";
$sp .= " SUSPEND;\n";
} else {
$end = $start + $num;
$sp .= " IF ((cnt >= $start) AND (cnt < $end)) THEN\n";
$sp .= " SUSPEND;\n";
$sp .= " if (cnt = $end) THEN\n";
$sp .= " EXIT;\n";
}
$sp .= " END\n";
$sp .= "END !!\n";
$sp = prepare_for_isql($sp);
list($binary_output, $binary_error) = isql_execute($sp, $s_login['user'], $s_login['password'], $s_login['database'], $s_login['host']);
return empty($binary_output) && empty($binary_error);
}
//
// check wether the stored procedure $name exists
//
function sp_exist($name) {
global $dbhandle;
$sql = 'SELECT RDB$PROCEDURE_NAME'
.' FROM RDB$PROCEDURES'
." WHERE RDB\$PROCEDURE_NAME='".$name."'";
$res = ibase_query($dbhandle, $sql) or ib_error();
if (ibase_fetch_row($res)) {
ibase_free_result($res);
return TRUE;
} else {
ibase_free_result($res);
return FALSE;
}
}
//
// remove the stored procedure $name from database
//
function sp_remove($name) {
global $dbhandle, $ib_error;
$sql = 'DROP PROCEDURE '.$name;
$trans = ibase_trans(TRANS_WRITE, $dbhandle);
$res = ibase_query($trans, $sql)
or die(ibase_errmsg());
ibase_commit($trans);
}
function prepare_for_isql($cmd) {
$cmd = "SET TERM !! ;\n".$cmd."SET TERM ; !!\n";
return $cmd;
}
?>
|
