| 1 |
< |
{************************************************************************} |
| 2 |
< |
{ } |
| 3 |
< |
{ Borland Delphi Visual Component Library } |
| 4 |
< |
{ InterBase Express core components } |
| 5 |
< |
{ } |
| 6 |
< |
{ Copyright (c) 1998-2000 Inprise Corporation } |
| 7 |
< |
{ } |
| 8 |
< |
{ InterBase Express is based in part on the product } |
| 9 |
< |
{ Free IB Components, written by Gregory H. Deatz for } |
| 10 |
< |
{ Hoagland, Longo, Moran, Dunst & Doukas Company. } |
| 11 |
< |
{ Free IB Components is used under license. } |
| 12 |
< |
{ } |
| 13 |
< |
{ The contents of this file are subject to the InterBase } |
| 14 |
< |
{ Public License Version 1.0 (the "License"); you may not } |
| 15 |
< |
{ use this file except in compliance with the License. You } |
| 16 |
< |
{ may obtain a copy of the License at http://www.Inprise.com/IPL.html } |
| 17 |
< |
{ Software distributed under the License is distributed on } |
| 18 |
< |
{ an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either } |
| 19 |
< |
{ express or implied. See the License for the specific language } |
| 20 |
< |
{ governing rights and limitations under the License. } |
| 21 |
< |
{ The Original Code was created by InterBase Software Corporation } |
| 22 |
< |
{ and its successors. } |
| 23 |
< |
{ Portions created by Inprise Corporation are Copyright (C) Inprise } |
| 24 |
< |
{ Corporation. All Rights Reserved. } |
| 25 |
< |
{ Contributor(s): Jeff Overcash } |
| 26 |
< |
{ } |
| 27 |
< |
{ IBX For Lazarus (Firebird Express) } |
| 28 |
< |
{ Contributor: Tony Whyman, MWA Software http://www.mwasoftware.co.uk } |
| 29 |
< |
{ Portions created by MWA Software are copyright McCallum Whyman } |
| 30 |
< |
{ Associates Ltd 2011 } |
| 31 |
< |
{ } |
| 32 |
< |
{************************************************************************} |
| 33 |
< |
|
| 34 |
< |
unit IBStoredProc; |
| 35 |
< |
|
| 36 |
< |
{$Mode Delphi} |
| 37 |
< |
|
| 38 |
< |
interface |
| 39 |
< |
|
| 40 |
< |
uses SysUtils, Classes, DB, IB, IBDatabase, IBCustomDataSet, |
| 41 |
< |
IBHeader, IBSQL, IBUtils; |
| 42 |
< |
|
| 43 |
< |
{ TIBStoredProc } |
| 44 |
< |
type |
| 45 |
< |
|
| 46 |
< |
TIBStoredProc = class(TIBCustomDataSet) |
| 47 |
< |
private |
| 48 |
< |
FIBLoaded: Boolean; |
| 49 |
< |
FStmtHandle: TISC_STMT_HANDLE; |
| 50 |
< |
FProcName: string; |
| 51 |
< |
FParams: TParams; |
| 52 |
< |
FPrepared: Boolean; |
| 53 |
< |
FNameList: TStrings; |
| 54 |
< |
procedure SetParamsList(Value: TParams); |
| 55 |
< |
procedure FreeStatement; |
| 56 |
< |
function GetStoredProcedureNames: TStrings; |
| 57 |
< |
procedure GetStoredProcedureNamesFromServer; |
| 58 |
< |
procedure CreateParamDesc; |
| 59 |
< |
procedure SetParams; |
| 60 |
< |
procedure SetParamsFromCursor; |
| 61 |
< |
procedure GenerateSQL; |
| 62 |
< |
procedure FetchDataIntoOutputParams; |
| 63 |
< |
procedure ReadParamData(Reader: TReader); |
| 64 |
< |
procedure WriteParamData(Writer: TWriter); |
| 65 |
< |
|
| 66 |
< |
protected |
| 67 |
< |
|
| 68 |
< |
procedure DefineProperties(Filer: TFiler); override; |
| 69 |
< |
procedure SetFiltered(Value: Boolean); override; |
| 70 |
< |
procedure InitFieldDefs; override; |
| 71 |
< |
function GetParamsCount: Word; |
| 72 |
< |
procedure SetPrepared(Value: Boolean); |
| 73 |
< |
procedure SetPrepare(Value: Boolean); |
| 74 |
< |
procedure SetProcName(Value: string); |
| 75 |
< |
procedure Disconnect; override; |
| 76 |
< |
procedure InternalOpen; override; |
| 77 |
< |
|
| 78 |
< |
public |
| 79 |
< |
constructor Create(AOwner: TComponent); override; |
| 80 |
< |
destructor Destroy; override; |
| 81 |
< |
procedure CopyParams(Value: TParams); |
| 82 |
< |
procedure ExecProc; |
| 83 |
< |
function ParamByName(const Value: string): TParam; |
| 84 |
< |
procedure Prepare; |
| 85 |
< |
procedure UnPrepare; |
| 86 |
< |
property ParamCount: Word read GetParamsCount; |
| 87 |
< |
property StmtHandle: TISC_STMT_HANDLE read FStmtHandle; |
| 88 |
< |
property Prepared: Boolean read FPrepared write SetPrepare; |
| 89 |
< |
property StoredProcedureNames: TStrings read GetStoredProcedureNames; |
| 90 |
< |
|
| 91 |
< |
published |
| 92 |
< |
property StoredProcName: string read FProcName write SetProcName; |
| 93 |
< |
property Params: TParams read FParams write SetParamsList; |
| 94 |
< |
property Filtered; |
| 95 |
< |
|
| 96 |
< |
property BeforeDatabaseDisconnect; |
| 97 |
< |
property AfterDatabaseDisconnect; |
| 98 |
< |
property DatabaseFree; |
| 99 |
< |
property BeforeTransactionEnd; |
| 100 |
< |
property AfterTransactionEnd; |
| 101 |
< |
property TransactionFree; |
| 102 |
< |
property OnFilterRecord; |
| 103 |
< |
end; |
| 104 |
< |
|
| 105 |
< |
implementation |
| 106 |
< |
|
| 107 |
< |
uses |
| 108 |
< |
IBIntf; |
| 109 |
< |
|
| 110 |
< |
{ TIBStoredProc } |
| 111 |
< |
|
| 112 |
< |
constructor TIBStoredProc.Create(AOwner: TComponent); |
| 113 |
< |
begin |
| 114 |
< |
inherited Create(AOwner); |
| 115 |
< |
FIBLoaded := False; |
| 116 |
< |
CheckIBLoaded; |
| 117 |
< |
FIBLoaded := True; |
| 118 |
< |
FParams := TParams.Create (self); |
| 119 |
< |
FNameList := TStringList.Create; |
| 120 |
< |
end; |
| 121 |
< |
|
| 122 |
< |
destructor TIBStoredProc.Destroy; |
| 123 |
< |
begin |
| 124 |
< |
if FIBLoaded then |
| 125 |
< |
begin |
| 126 |
< |
Destroying; |
| 127 |
< |
Disconnect; |
| 128 |
< |
FParams.Free; |
| 129 |
< |
FNameList.Destroy; |
| 130 |
< |
end; |
| 131 |
< |
inherited Destroy; |
| 132 |
< |
end; |
| 133 |
< |
|
| 134 |
< |
procedure TIBStoredProc.Disconnect; |
| 135 |
< |
begin |
| 136 |
< |
Close; |
| 137 |
< |
UnPrepare; |
| 138 |
< |
end; |
| 139 |
< |
|
| 140 |
< |
procedure TIBStoredProc.ExecProc; |
| 141 |
< |
var |
| 142 |
< |
DidActivate: Boolean; |
| 143 |
< |
begin |
| 144 |
< |
CheckInActive; |
| 145 |
< |
if StoredProcName = '' then |
| 146 |
< |
IBError(ibxeNoStoredProcName, [nil]); |
| 147 |
< |
ActivateConnection; |
| 148 |
< |
DidActivate := ActivateTransaction; |
| 149 |
< |
try |
| 150 |
< |
SetPrepared(True); |
| 151 |
< |
if DataSource <> nil then SetParamsFromCursor; |
| 152 |
< |
if FParams.Count > 0 then SetParams; |
| 153 |
< |
InternalExecQuery; |
| 154 |
< |
FetchDataIntoOutputParams; |
| 155 |
< |
finally |
| 156 |
< |
if DidActivate then |
| 157 |
< |
DeactivateTransaction; |
| 158 |
< |
end; |
| 159 |
< |
end; |
| 160 |
< |
|
| 161 |
< |
procedure TIBStoredProc.SetProcName(Value: string); |
| 162 |
< |
begin |
| 163 |
< |
if not (csReading in ComponentState) then |
| 164 |
< |
begin |
| 165 |
< |
CheckInactive; |
| 166 |
< |
if Value <> FProcName then |
| 167 |
< |
begin |
| 168 |
< |
FProcName := Value; |
| 169 |
< |
FreeStatement; |
| 170 |
< |
FParams.Clear; |
| 171 |
< |
if (Value <> '') and |
| 172 |
< |
(Database <> nil) then |
| 173 |
< |
GenerateSQL; |
| 174 |
< |
end; |
| 175 |
< |
end else begin |
| 176 |
< |
FProcName := Value; |
| 177 |
< |
if (Value <> '') and |
| 178 |
< |
(Database <> nil) then |
| 179 |
< |
GenerateSQL; |
| 180 |
< |
end; |
| 181 |
< |
end; |
| 182 |
< |
|
| 183 |
< |
function TIBStoredProc.GetParamsCount: Word; |
| 184 |
< |
begin |
| 185 |
< |
Result := FParams.Count; |
| 186 |
< |
end; |
| 187 |
< |
|
| 188 |
< |
procedure TIBStoredProc.SetFiltered(Value: Boolean); |
| 189 |
< |
begin |
| 190 |
< |
if(Filtered <> Value) then |
| 191 |
< |
begin |
| 192 |
< |
inherited SetFiltered(value); |
| 193 |
< |
if Active then |
| 194 |
< |
begin |
| 195 |
< |
Close; |
| 196 |
< |
Open; |
| 197 |
< |
end; |
| 198 |
< |
end |
| 199 |
< |
else |
| 200 |
< |
inherited SetFiltered(value); |
| 201 |
< |
end; |
| 202 |
< |
|
| 203 |
< |
procedure TIBStoredProc.InitFieldDefs; |
| 204 |
< |
begin |
| 205 |
< |
if SelectSQL.Text = '' then |
| 206 |
< |
GenerateSQL; |
| 207 |
< |
inherited InitFieldDefs; |
| 208 |
< |
end; |
| 209 |
< |
|
| 210 |
< |
procedure TIBStoredProc.GenerateSQL; |
| 211 |
< |
|
| 212 |
< |
var Params: TStringList; |
| 213 |
< |
|
| 214 |
< |
function FormatParameter(Dialect: Integer; Value: String): String; |
| 215 |
< |
var j: integer; |
| 216 |
< |
begin |
| 217 |
< |
Value := Trim(Value); |
| 218 |
< |
if Dialect = 1 then |
| 219 |
< |
Result := AnsiUpperCase(Value) |
| 220 |
< |
else |
| 221 |
< |
begin |
| 222 |
< |
j := 1; |
| 223 |
< |
Value := Space2Underscore(AnsiUpperCase(Value)); |
| 224 |
< |
Result := Value; |
| 225 |
< |
while Params.IndexOf(Result) <> -1 do |
| 226 |
< |
begin |
| 227 |
< |
Result := Value + IntToStr(j); |
| 228 |
< |
Inc(j) |
| 229 |
< |
end; |
| 230 |
< |
Params.Add(Result) |
| 231 |
< |
end; |
| 232 |
< |
end; |
| 233 |
< |
|
| 234 |
< |
var |
| 235 |
< |
Query : TIBSQL; |
| 236 |
< |
input : string; |
| 237 |
< |
begin |
| 238 |
< |
input := ''; |
| 239 |
< |
if FProcName = '' then |
| 240 |
< |
IBError(ibxeNoStoredProcName,[nil]); |
| 241 |
< |
ActivateConnection; |
| 242 |
< |
Database.InternalTransaction.StartTransaction; |
| 243 |
< |
Params := TStringList.Create; |
| 244 |
< |
Query := TIBSQL.Create(self); |
| 245 |
< |
try |
| 246 |
< |
Query.Database := DataBase; |
| 247 |
< |
Query.Transaction := Database.InternalTransaction; |
| 248 |
< |
Query.SQL.Text := 'SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_TYPE ' + {do not localize} |
| 249 |
< |
'FROM RDB$PROCEDURE_PARAMETERS ' + {do not localize} |
| 250 |
< |
'WHERE RDB$PROCEDURE_NAME = ' + {do not localize} |
| 251 |
< |
'''' + FormatIdentifierValue(Database.SQLDialect, FProcName) + '''' + |
| 252 |
< |
' ORDER BY RDB$PARAMETER_NUMBER'; {do not localize} |
| 253 |
< |
Query.Prepare; |
| 254 |
< |
Query.GoToFirstRecordOnExecute := False; |
| 255 |
< |
Query.ExecQuery; |
| 256 |
< |
while (not Query.EOF) and (Query.Next <> nil) do begin |
| 257 |
< |
if (Query.Current.ByName('RDB$PARAMETER_TYPE').AsInteger = 0) then begin {do not localize} |
| 258 |
< |
if (input <> '') then |
| 259 |
< |
input := input + ', :' + |
| 260 |
< |
FormatParameter(Database.SQLDialect, Query.Current.ByName('RDB$PARAMETER_NAME').AsString) else {do not localize} |
| 261 |
< |
input := ':' + |
| 262 |
< |
FormatParameter(Database.SQLDialect, Query.Current.ByName('RDB$PARAMETER_NAME').AsString); {do not localize} |
| 263 |
< |
end |
| 264 |
< |
end; |
| 265 |
< |
SelectSQL.Text := 'Execute Procedure ' + {do not localize} |
| 266 |
< |
FormatParameter(Database.SQLDialect, FProcName) + ' ' + input; |
| 267 |
< |
{ writeln(SelectSQL.Text);} |
| 268 |
< |
finally |
| 269 |
< |
Query.Free; |
| 270 |
< |
Params.Free; |
| 271 |
< |
Database.InternalTransaction.Commit; |
| 272 |
< |
end; |
| 273 |
< |
end; |
| 274 |
< |
|
| 275 |
< |
procedure TIBStoredProc.CreateParamDesc; |
| 276 |
< |
var |
| 277 |
< |
i : integer; |
| 278 |
< |
DataType : TFieldType; |
| 279 |
< |
begin |
| 280 |
< |
DataType := ftUnknown; |
| 281 |
< |
for i := 0 to QSelect.Current.Count - 1 do begin |
| 282 |
< |
case QSelect.Fields[i].SQLtype of |
| 283 |
< |
SQL_TYPE_DATE: DataType := ftDate; |
| 284 |
< |
SQL_TYPE_TIME: DataType := ftTime; |
| 285 |
< |
SQL_TIMESTAMP: DataType := ftDateTime; |
| 286 |
< |
SQL_SHORT: |
| 287 |
< |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 288 |
< |
DataType := ftSmallInt |
| 289 |
< |
else |
| 290 |
< |
DataType := ftBCD; |
| 291 |
< |
SQL_LONG: |
| 292 |
< |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 293 |
< |
DataType := ftInteger |
| 294 |
< |
else if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 295 |
< |
DataType := ftBCD |
| 296 |
< |
else |
| 297 |
< |
DataType := ftFloat; |
| 298 |
< |
SQL_INT64: |
| 299 |
< |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 300 |
< |
DataType := ftLargeInt |
| 301 |
< |
else if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 302 |
< |
DataType := ftBCD |
| 303 |
< |
else |
| 304 |
< |
DataType := ftFloat; |
| 305 |
< |
SQL_DOUBLE, SQL_FLOAT, SQL_D_FLOAT: DataType := ftFloat; |
| 306 |
< |
SQL_BOOLEAN: |
| 307 |
< |
DataType := ftBoolean; |
| 308 |
< |
SQL_TEXT: DataType := ftString; |
| 309 |
< |
SQL_VARYING: |
| 310 |
< |
if ((QSelect.Fields[i].AsXSQLVar)^.sqllen < 1024) then |
| 311 |
< |
DataType := ftString |
| 312 |
< |
else DataType := ftBlob; |
| 313 |
< |
SQL_BLOB, SQL_ARRAY, SQL_QUAD: DataType := ftBlob; |
| 314 |
< |
end; |
| 315 |
< |
FParams.CreateParam(DataType, Trim(QSelect.Fields[i].Name), ptOutput); |
| 316 |
< |
end; |
| 317 |
< |
|
| 318 |
< |
DataType := ftUnknown; |
| 319 |
< |
for i := 0 to QSelect.Params.Count - 1 do begin |
| 320 |
< |
case QSelect.Params[i].SQLtype of |
| 321 |
< |
SQL_TYPE_DATE: DataType := ftDate; |
| 322 |
< |
SQL_TYPE_TIME: DataType := ftTime; |
| 323 |
< |
SQL_TIMESTAMP: DataType := ftDateTime; |
| 324 |
< |
SQL_SHORT: |
| 325 |
< |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 326 |
< |
DataType := ftSmallInt |
| 327 |
< |
else |
| 328 |
< |
DataType := ftBCD; |
| 329 |
< |
SQL_LONG: |
| 330 |
< |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 331 |
< |
DataType := ftInteger |
| 332 |
< |
else if ((QSelect.Params[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 333 |
< |
DataType := ftBCD |
| 334 |
< |
else DataType := ftFloat; |
| 335 |
< |
SQL_INT64: |
| 336 |
< |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 337 |
< |
DataType := ftLargeInt |
| 338 |
< |
else if ((QSelect.Params[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 339 |
< |
DataType := ftBCD |
| 340 |
< |
else DataType := ftFloat; |
| 341 |
< |
SQL_DOUBLE, SQL_FLOAT, SQL_D_FLOAT: DataType := ftFloat; |
| 342 |
< |
SQL_BOOLEAN: |
| 343 |
< |
DataType := ftBoolean; |
| 344 |
< |
SQL_TEXT: DataType := ftString; |
| 345 |
< |
SQL_VARYING: |
| 346 |
< |
if ((QSelect.Params[i].AsXSQLVar)^.sqllen < 1024) then |
| 347 |
< |
DataType := ftString |
| 348 |
< |
else DataType := ftBlob; |
| 349 |
< |
SQL_BLOB, SQL_ARRAY, SQL_QUAD: DataType := ftBlob; |
| 350 |
< |
end; |
| 351 |
< |
FParams.CreateParam(DataType, Trim(QSelect.Params[i].Name), ptInput); |
| 352 |
< |
end; |
| 353 |
< |
end; |
| 354 |
< |
|
| 355 |
< |
procedure TIBStoredProc.SetPrepared(Value: Boolean); |
| 356 |
< |
begin |
| 357 |
< |
if Prepared <> Value then |
| 358 |
< |
begin |
| 359 |
< |
if Value then |
| 360 |
< |
try |
| 361 |
< |
if SelectSQL.Text = '' then GenerateSQL; |
| 362 |
< |
InternalPrepare; |
| 363 |
< |
if FParams.Count = 0 then CreateParamDesc; |
| 364 |
< |
FPrepared := True; |
| 365 |
< |
except |
| 366 |
< |
FreeStatement; |
| 367 |
< |
raise; |
| 368 |
< |
end |
| 369 |
< |
else FreeStatement; |
| 370 |
< |
end; |
| 371 |
< |
|
| 372 |
< |
end; |
| 373 |
< |
|
| 374 |
< |
procedure TIBStoredProc.Prepare; |
| 375 |
< |
begin |
| 376 |
< |
SetPrepared(True); |
| 377 |
< |
end; |
| 378 |
< |
|
| 379 |
< |
procedure TIBStoredProc.UnPrepare; |
| 380 |
< |
begin |
| 381 |
< |
SetPrepared(False); |
| 382 |
< |
end; |
| 383 |
< |
|
| 384 |
< |
procedure TIBStoredProc.FreeStatement; |
| 385 |
< |
begin |
| 386 |
< |
InternalUnPrepare; |
| 387 |
< |
FPrepared := False; |
| 388 |
< |
end; |
| 389 |
< |
|
| 390 |
< |
procedure TIBStoredProc.SetPrepare(Value: Boolean); |
| 391 |
< |
begin |
| 392 |
< |
if Value then Prepare |
| 393 |
< |
else UnPrepare; |
| 394 |
< |
end; |
| 395 |
< |
|
| 396 |
< |
procedure TIBStoredProc.CopyParams(Value: TParams); |
| 397 |
< |
begin |
| 398 |
< |
if not Prepared and (FParams.Count = 0) then |
| 399 |
< |
try |
| 400 |
< |
Prepare; |
| 401 |
< |
Value.Assign(FParams); |
| 402 |
< |
finally |
| 403 |
< |
UnPrepare; |
| 404 |
< |
end else |
| 405 |
< |
Value.Assign(FParams); |
| 406 |
< |
end; |
| 407 |
< |
|
| 408 |
< |
procedure TIBStoredProc.SetParamsList(Value: TParams); |
| 409 |
< |
begin |
| 410 |
< |
CheckInactive; |
| 411 |
< |
if Prepared then |
| 412 |
< |
begin |
| 413 |
< |
SetPrepared(False); |
| 414 |
< |
FParams.Assign(Value); |
| 415 |
< |
SetPrepared(True); |
| 416 |
< |
end else |
| 417 |
< |
FParams.Assign(Value); |
| 418 |
< |
end; |
| 419 |
< |
|
| 420 |
< |
function TIBStoredProc.ParamByName(const Value: string): TParam; |
| 421 |
< |
begin |
| 422 |
< |
Prepare; |
| 423 |
< |
Result := FParams.ParamByName(Value); |
| 424 |
< |
end; |
| 425 |
< |
|
| 426 |
< |
function TIBStoredProc.GetStoredProcedureNames: TStrings; |
| 427 |
< |
begin |
| 428 |
< |
FNameList.clear; |
| 429 |
< |
GetStoredProcedureNamesFromServer; |
| 430 |
< |
Result := FNameList; |
| 431 |
< |
end; |
| 432 |
< |
|
| 433 |
< |
procedure TIBStoredProc.GetStoredProcedureNamesFromServer; |
| 434 |
< |
var |
| 435 |
< |
Query : TIBSQL; |
| 436 |
< |
begin |
| 437 |
< |
if not (csReading in ComponentState) then begin |
| 438 |
< |
ActivateConnection; |
| 439 |
< |
Database.InternalTransaction.StartTransaction; |
| 440 |
< |
Query := TIBSQL.Create(self); |
| 441 |
< |
try |
| 442 |
< |
Query.GoToFirstRecordOnExecute := False; |
| 443 |
< |
Query.Database := DataBase; |
| 444 |
< |
Query.Transaction := Database.InternalTransaction; |
| 445 |
< |
Query.SQL.Text := 'Select RDB$PROCEDURE_NAME from RDB$PROCEDURES'; {do not localize} |
| 446 |
< |
Query.Prepare; |
| 447 |
< |
Query.ExecQuery; |
| 448 |
< |
while (not Query.EOF) and (Query.Next <> nil) do |
| 449 |
< |
FNameList.Add(TrimRight(Query.Current.ByName('RDB$PROCEDURE_NAME').AsString)); {do not localize} |
| 450 |
< |
finally |
| 451 |
< |
Query.Free; |
| 452 |
< |
Database.InternalTransaction.Commit; |
| 453 |
< |
end; |
| 454 |
< |
end; |
| 455 |
< |
end; |
| 456 |
< |
|
| 457 |
< |
procedure TIBStoredProc.SetParams; |
| 458 |
< |
var |
| 459 |
< |
i : integer; |
| 460 |
< |
j: integer; |
| 461 |
< |
begin |
| 462 |
< |
i := 0; |
| 463 |
< |
for j := 0 to FParams.Count - 1 do |
| 464 |
< |
begin |
| 465 |
< |
if (Params[j].ParamType <> ptInput) then |
| 466 |
< |
continue; |
| 467 |
< |
if not Params[j].Bound then |
| 468 |
< |
IBError(ibxeRequiredParamNotSet, [Params[j].Name]); |
| 469 |
< |
if Params[j].IsNull then |
| 470 |
< |
SQLParams[i].IsNull := True |
| 471 |
< |
else begin |
| 472 |
< |
SQLParams[i].IsNull := False; |
| 473 |
< |
case Params[j].DataType of |
| 474 |
< |
ftString: |
| 475 |
< |
SQLParams[i].AsString := Params[j].AsString; |
| 476 |
< |
ftSmallint, ftWord: |
| 477 |
< |
SQLParams[i].AsShort := Params[j].AsSmallInt; |
| 478 |
< |
ftBoolean: |
| 479 |
< |
SQLParams[i].AsBoolean := Params[j].AsBoolean; |
| 480 |
< |
ftInteger: |
| 481 |
< |
SQLParams[i].AsLong := Params[j].AsInteger; |
| 482 |
< |
ftLargeInt: |
| 483 |
< |
SQLParams[i].AsInt64 := Params[j].AsLargeInt; |
| 484 |
< |
ftFloat, ftCurrency: |
| 485 |
< |
SQLParams[i].AsDouble := Params[j].AsFloat; |
| 486 |
< |
ftBCD: |
| 487 |
< |
SQLParams[i].AsCurrency := Params[j].AsCurrency; |
| 488 |
< |
ftDate: |
| 489 |
< |
SQLParams[i].AsDate := Params[j].AsDateTime; |
| 490 |
< |
ftTime: |
| 491 |
< |
SQLParams[i].AsTime := Params[j].AsDateTime; |
| 492 |
< |
ftDateTime: |
| 493 |
< |
SQLParams[i].AsDateTime := Params[j].AsDateTime; |
| 494 |
< |
ftBlob, ftMemo: |
| 495 |
< |
SQLParams[i].AsString := Params[j].AsString; |
| 496 |
< |
else |
| 497 |
< |
IBError(ibxeNotSupported, [nil]); |
| 498 |
< |
end; |
| 499 |
< |
end; |
| 500 |
< |
Inc(i); |
| 501 |
< |
end; |
| 502 |
< |
end; |
| 503 |
< |
|
| 504 |
< |
procedure TIBStoredProc.SetParamsFromCursor; |
| 505 |
< |
var |
| 506 |
< |
I: Integer; |
| 507 |
< |
DataSet: TDataSet; |
| 508 |
< |
begin |
| 509 |
< |
if DataSource <> nil then |
| 510 |
< |
begin |
| 511 |
< |
DataSet := DataSource.DataSet; |
| 512 |
< |
if DataSet <> nil then |
| 513 |
< |
begin |
| 514 |
< |
DataSet.FieldDefs.Update; |
| 515 |
< |
for I := 0 to FParams.Count - 1 do |
| 516 |
< |
with FParams[I] do |
| 517 |
< |
if (not Bound) and |
| 518 |
< |
((ParamType = ptInput) or (ParamType = ptInputOutput)) then |
| 519 |
< |
AssignField(DataSet.FieldByName(Name)); |
| 520 |
< |
end; |
| 521 |
< |
end; |
| 522 |
< |
end; |
| 523 |
< |
|
| 524 |
< |
procedure TIBStoredProc.FetchDataIntoOutputParams; |
| 525 |
< |
var |
| 526 |
< |
i,j : Integer; |
| 527 |
< |
begin |
| 528 |
< |
j := 0; |
| 529 |
< |
for i := 0 to FParams.Count - 1 do |
| 530 |
< |
with Params[I] do |
| 531 |
< |
if ParamType = ptOutput then begin |
| 532 |
< |
Value := QSelect.Fields[j].Value; |
| 533 |
< |
Inc(j); |
| 534 |
< |
end; |
| 535 |
< |
end; |
| 536 |
< |
|
| 537 |
< |
procedure TIBStoredProc.InternalOpen; |
| 538 |
< |
begin |
| 539 |
< |
IBError(ibxeIsAExecuteProcedure,[nil]); |
| 540 |
< |
end; |
| 541 |
< |
|
| 542 |
< |
procedure TIBStoredProc.DefineProperties(Filer: TFiler); |
| 543 |
< |
|
| 544 |
< |
function WriteData: Boolean; |
| 545 |
< |
begin |
| 546 |
< |
if Filer.Ancestor <> nil then |
| 547 |
< |
Result := not FParams.IsEqual(TIBStoredProc(Filer.Ancestor).FParams) else |
| 548 |
< |
Result := FParams.Count > 0; |
| 549 |
< |
end; |
| 550 |
< |
|
| 551 |
< |
begin |
| 552 |
< |
inherited DefineProperties(Filer); |
| 553 |
< |
Filer.DefineProperty('ParamData', ReadParamData, WriteParamData, WriteData); {do not localize} |
| 554 |
< |
end; |
| 555 |
< |
|
| 556 |
< |
procedure TIBStoredProc.WriteParamData(Writer: TWriter); |
| 557 |
< |
begin |
| 558 |
< |
Writer.WriteCollection(Params); |
| 559 |
< |
end; |
| 560 |
< |
|
| 561 |
< |
procedure TIBStoredProc.ReadParamData(Reader: TReader); |
| 562 |
< |
begin |
| 563 |
< |
Reader.ReadValue; |
| 564 |
< |
Reader.ReadCollection(Params); |
| 565 |
< |
end; |
| 566 |
< |
|
| 567 |
< |
end. |
| 1 |
> |
{************************************************************************} |
| 2 |
> |
{ } |
| 3 |
> |
{ Borland Delphi Visual Component Library } |
| 4 |
> |
{ InterBase Express core components } |
| 5 |
> |
{ } |
| 6 |
> |
{ Copyright (c) 1998-2000 Inprise Corporation } |
| 7 |
> |
{ } |
| 8 |
> |
{ InterBase Express is based in part on the product } |
| 9 |
> |
{ Free IB Components, written by Gregory H. Deatz for } |
| 10 |
> |
{ Hoagland, Longo, Moran, Dunst & Doukas Company. } |
| 11 |
> |
{ Free IB Components is used under license. } |
| 12 |
> |
{ } |
| 13 |
> |
{ The contents of this file are subject to the InterBase } |
| 14 |
> |
{ Public License Version 1.0 (the "License"); you may not } |
| 15 |
> |
{ use this file except in compliance with the License. You } |
| 16 |
> |
{ may obtain a copy of the License at http://www.Inprise.com/IPL.html } |
| 17 |
> |
{ Software distributed under the License is distributed on } |
| 18 |
> |
{ an "AS IS" basis, WITHOUT WARRANTY OF ANY KIND, either } |
| 19 |
> |
{ express or implied. See the License for the specific language } |
| 20 |
> |
{ governing rights and limitations under the License. } |
| 21 |
> |
{ The Original Code was created by InterBase Software Corporation } |
| 22 |
> |
{ and its successors. } |
| 23 |
> |
{ Portions created by Inprise Corporation are Copyright (C) Inprise } |
| 24 |
> |
{ Corporation. All Rights Reserved. } |
| 25 |
> |
{ Contributor(s): Jeff Overcash } |
| 26 |
> |
{ } |
| 27 |
> |
{ IBX For Lazarus (Firebird Express) } |
| 28 |
> |
{ Contributor: Tony Whyman, MWA Software http://www.mwasoftware.co.uk } |
| 29 |
> |
{ Portions created by MWA Software are copyright McCallum Whyman } |
| 30 |
> |
{ Associates Ltd 2011 } |
| 31 |
> |
{ } |
| 32 |
> |
{************************************************************************} |
| 33 |
> |
|
| 34 |
> |
unit IBStoredProc; |
| 35 |
> |
|
| 36 |
> |
{$Mode Delphi} |
| 37 |
> |
|
| 38 |
> |
interface |
| 39 |
> |
|
| 40 |
> |
uses SysUtils, Classes, DB, IB, IBDatabase, IBCustomDataSet, |
| 41 |
> |
IBHeader, IBSQL, IBUtils; |
| 42 |
> |
|
| 43 |
> |
{ TIBStoredProc } |
| 44 |
> |
type |
| 45 |
> |
|
| 46 |
> |
TIBStoredProc = class(TIBCustomDataSet) |
| 47 |
> |
private |
| 48 |
> |
FIBLoaded: Boolean; |
| 49 |
> |
FStmtHandle: TISC_STMT_HANDLE; |
| 50 |
> |
FProcName: string; |
| 51 |
> |
FParams: TParams; |
| 52 |
> |
FPrepared: Boolean; |
| 53 |
> |
FNameList: TStrings; |
| 54 |
> |
procedure SetParamsList(Value: TParams); |
| 55 |
> |
procedure FreeStatement; |
| 56 |
> |
function GetStoredProcedureNames: TStrings; |
| 57 |
> |
procedure GetStoredProcedureNamesFromServer; |
| 58 |
> |
procedure CreateParamDesc; |
| 59 |
> |
procedure SetParams; |
| 60 |
> |
procedure SetParamsFromCursor; |
| 61 |
> |
procedure GenerateSQL; |
| 62 |
> |
procedure FetchDataIntoOutputParams; |
| 63 |
> |
procedure ReadParamData(Reader: TReader); |
| 64 |
> |
procedure WriteParamData(Writer: TWriter); |
| 65 |
> |
|
| 66 |
> |
protected |
| 67 |
> |
|
| 68 |
> |
procedure DefineProperties(Filer: TFiler); override; |
| 69 |
> |
procedure SetFiltered(Value: Boolean); override; |
| 70 |
> |
procedure InitFieldDefs; override; |
| 71 |
> |
function GetParamsCount: Word; |
| 72 |
> |
procedure SetPrepared(Value: Boolean); |
| 73 |
> |
procedure SetPrepare(Value: Boolean); |
| 74 |
> |
procedure SetProcName(Value: string); |
| 75 |
> |
procedure Disconnect; override; |
| 76 |
> |
procedure InternalOpen; override; |
| 77 |
> |
|
| 78 |
> |
public |
| 79 |
> |
constructor Create(AOwner: TComponent); override; |
| 80 |
> |
destructor Destroy; override; |
| 81 |
> |
procedure CopyParams(Value: TParams); |
| 82 |
> |
procedure ExecProc; |
| 83 |
> |
function ParamByName(const Value: string): TParam; |
| 84 |
> |
procedure Prepare; |
| 85 |
> |
procedure UnPrepare; |
| 86 |
> |
property ParamCount: Word read GetParamsCount; |
| 87 |
> |
property StmtHandle: TISC_STMT_HANDLE read FStmtHandle; |
| 88 |
> |
property Prepared: Boolean read FPrepared write SetPrepare; |
| 89 |
> |
property StoredProcedureNames: TStrings read GetStoredProcedureNames; |
| 90 |
> |
|
| 91 |
> |
published |
| 92 |
> |
property StoredProcName: string read FProcName write SetProcName; |
| 93 |
> |
property Params: TParams read FParams write SetParamsList; |
| 94 |
> |
property Filtered; |
| 95 |
> |
|
| 96 |
> |
property BeforeDatabaseDisconnect; |
| 97 |
> |
property AfterDatabaseDisconnect; |
| 98 |
> |
property DatabaseFree; |
| 99 |
> |
property BeforeTransactionEnd; |
| 100 |
> |
property AfterTransactionEnd; |
| 101 |
> |
property TransactionFree; |
| 102 |
> |
property OnFilterRecord; |
| 103 |
> |
end; |
| 104 |
> |
|
| 105 |
> |
implementation |
| 106 |
> |
|
| 107 |
> |
uses |
| 108 |
> |
IBIntf; |
| 109 |
> |
|
| 110 |
> |
{ TIBStoredProc } |
| 111 |
> |
|
| 112 |
> |
constructor TIBStoredProc.Create(AOwner: TComponent); |
| 113 |
> |
begin |
| 114 |
> |
inherited Create(AOwner); |
| 115 |
> |
FIBLoaded := False; |
| 116 |
> |
CheckIBLoaded; |
| 117 |
> |
FIBLoaded := True; |
| 118 |
> |
FParams := TParams.Create (self); |
| 119 |
> |
FNameList := TStringList.Create; |
| 120 |
> |
end; |
| 121 |
> |
|
| 122 |
> |
destructor TIBStoredProc.Destroy; |
| 123 |
> |
begin |
| 124 |
> |
if FIBLoaded then |
| 125 |
> |
begin |
| 126 |
> |
Destroying; |
| 127 |
> |
Disconnect; |
| 128 |
> |
FParams.Free; |
| 129 |
> |
FNameList.Destroy; |
| 130 |
> |
end; |
| 131 |
> |
inherited Destroy; |
| 132 |
> |
end; |
| 133 |
> |
|
| 134 |
> |
procedure TIBStoredProc.Disconnect; |
| 135 |
> |
begin |
| 136 |
> |
Close; |
| 137 |
> |
UnPrepare; |
| 138 |
> |
end; |
| 139 |
> |
|
| 140 |
> |
procedure TIBStoredProc.ExecProc; |
| 141 |
> |
var |
| 142 |
> |
DidActivate: Boolean; |
| 143 |
> |
begin |
| 144 |
> |
CheckInActive; |
| 145 |
> |
if StoredProcName = '' then |
| 146 |
> |
IBError(ibxeNoStoredProcName, [nil]); |
| 147 |
> |
ActivateConnection; |
| 148 |
> |
DidActivate := ActivateTransaction; |
| 149 |
> |
try |
| 150 |
> |
SetPrepared(True); |
| 151 |
> |
if DataSource <> nil then SetParamsFromCursor; |
| 152 |
> |
if FParams.Count > 0 then SetParams; |
| 153 |
> |
InternalExecQuery; |
| 154 |
> |
FetchDataIntoOutputParams; |
| 155 |
> |
finally |
| 156 |
> |
if DidActivate then |
| 157 |
> |
DeactivateTransaction; |
| 158 |
> |
end; |
| 159 |
> |
end; |
| 160 |
> |
|
| 161 |
> |
procedure TIBStoredProc.SetProcName(Value: string); |
| 162 |
> |
begin |
| 163 |
> |
if not (csReading in ComponentState) then |
| 164 |
> |
begin |
| 165 |
> |
CheckInactive; |
| 166 |
> |
if Value <> FProcName then |
| 167 |
> |
begin |
| 168 |
> |
FProcName := Value; |
| 169 |
> |
FreeStatement; |
| 170 |
> |
FParams.Clear; |
| 171 |
> |
if (Value <> '') and |
| 172 |
> |
(Database <> nil) then |
| 173 |
> |
GenerateSQL; |
| 174 |
> |
end; |
| 175 |
> |
end else begin |
| 176 |
> |
FProcName := Value; |
| 177 |
> |
if (Value <> '') and |
| 178 |
> |
(Database <> nil) then |
| 179 |
> |
GenerateSQL; |
| 180 |
> |
end; |
| 181 |
> |
end; |
| 182 |
> |
|
| 183 |
> |
function TIBStoredProc.GetParamsCount: Word; |
| 184 |
> |
begin |
| 185 |
> |
Result := FParams.Count; |
| 186 |
> |
end; |
| 187 |
> |
|
| 188 |
> |
procedure TIBStoredProc.SetFiltered(Value: Boolean); |
| 189 |
> |
begin |
| 190 |
> |
if(Filtered <> Value) then |
| 191 |
> |
begin |
| 192 |
> |
inherited SetFiltered(value); |
| 193 |
> |
if Active then |
| 194 |
> |
begin |
| 195 |
> |
Close; |
| 196 |
> |
Open; |
| 197 |
> |
end; |
| 198 |
> |
end |
| 199 |
> |
else |
| 200 |
> |
inherited SetFiltered(value); |
| 201 |
> |
end; |
| 202 |
> |
|
| 203 |
> |
procedure TIBStoredProc.InitFieldDefs; |
| 204 |
> |
begin |
| 205 |
> |
if SelectSQL.Text = '' then |
| 206 |
> |
GenerateSQL; |
| 207 |
> |
inherited InitFieldDefs; |
| 208 |
> |
end; |
| 209 |
> |
|
| 210 |
> |
procedure TIBStoredProc.GenerateSQL; |
| 211 |
> |
|
| 212 |
> |
var Params: TStringList; |
| 213 |
> |
|
| 214 |
> |
function FormatParameter(Dialect: Integer; Value: String): String; |
| 215 |
> |
var j: integer; |
| 216 |
> |
begin |
| 217 |
> |
Value := Trim(Value); |
| 218 |
> |
if Dialect = 1 then |
| 219 |
> |
Result := AnsiUpperCase(Value) |
| 220 |
> |
else |
| 221 |
> |
begin |
| 222 |
> |
j := 1; |
| 223 |
> |
Value := Space2Underscore(AnsiUpperCase(Value)); |
| 224 |
> |
Result := Value; |
| 225 |
> |
while Params.IndexOf(Result) <> -1 do |
| 226 |
> |
begin |
| 227 |
> |
Result := Value + IntToStr(j); |
| 228 |
> |
Inc(j) |
| 229 |
> |
end; |
| 230 |
> |
Params.Add(Result) |
| 231 |
> |
end; |
| 232 |
> |
end; |
| 233 |
> |
|
| 234 |
> |
var |
| 235 |
> |
Query : TIBSQL; |
| 236 |
> |
input : string; |
| 237 |
> |
begin |
| 238 |
> |
input := ''; |
| 239 |
> |
if FProcName = '' then |
| 240 |
> |
IBError(ibxeNoStoredProcName,[nil]); |
| 241 |
> |
ActivateConnection; |
| 242 |
> |
Database.InternalTransaction.StartTransaction; |
| 243 |
> |
Params := TStringList.Create; |
| 244 |
> |
Query := TIBSQL.Create(self); |
| 245 |
> |
try |
| 246 |
> |
Query.Database := DataBase; |
| 247 |
> |
Query.Transaction := Database.InternalTransaction; |
| 248 |
> |
Query.SQL.Text := 'SELECT RDB$PARAMETER_NAME, RDB$PARAMETER_TYPE ' + {do not localize} |
| 249 |
> |
'FROM RDB$PROCEDURE_PARAMETERS ' + {do not localize} |
| 250 |
> |
'WHERE RDB$PROCEDURE_NAME = ' + {do not localize} |
| 251 |
> |
'''' + FormatIdentifierValue(Database.SQLDialect, FProcName) + '''' + |
| 252 |
> |
' ORDER BY RDB$PARAMETER_NUMBER'; {do not localize} |
| 253 |
> |
Query.Prepare; |
| 254 |
> |
Query.GoToFirstRecordOnExecute := False; |
| 255 |
> |
Query.ExecQuery; |
| 256 |
> |
while (not Query.EOF) and (Query.Next <> nil) do begin |
| 257 |
> |
if (Query.Current.ByName('RDB$PARAMETER_TYPE').AsInteger = 0) then begin {do not localize} |
| 258 |
> |
if (input <> '') then |
| 259 |
> |
input := input + ', :' + |
| 260 |
> |
FormatParameter(Database.SQLDialect, Query.Current.ByName('RDB$PARAMETER_NAME').AsString) else {do not localize} |
| 261 |
> |
input := ':' + |
| 262 |
> |
FormatParameter(Database.SQLDialect, Query.Current.ByName('RDB$PARAMETER_NAME').AsString); {do not localize} |
| 263 |
> |
end |
| 264 |
> |
end; |
| 265 |
> |
SelectSQL.Text := 'Execute Procedure ' + {do not localize} |
| 266 |
> |
FormatParameter(Database.SQLDialect, FProcName) + ' ' + input; |
| 267 |
> |
{ writeln(SelectSQL.Text);} |
| 268 |
> |
finally |
| 269 |
> |
Query.Free; |
| 270 |
> |
Params.Free; |
| 271 |
> |
Database.InternalTransaction.Commit; |
| 272 |
> |
end; |
| 273 |
> |
end; |
| 274 |
> |
|
| 275 |
> |
procedure TIBStoredProc.CreateParamDesc; |
| 276 |
> |
var |
| 277 |
> |
i : integer; |
| 278 |
> |
DataType : TFieldType; |
| 279 |
> |
begin |
| 280 |
> |
DataType := ftUnknown; |
| 281 |
> |
for i := 0 to QSelect.Current.Count - 1 do begin |
| 282 |
> |
case QSelect.Fields[i].SQLtype of |
| 283 |
> |
SQL_TYPE_DATE: DataType := ftDate; |
| 284 |
> |
SQL_TYPE_TIME: DataType := ftTime; |
| 285 |
> |
SQL_TIMESTAMP: DataType := ftDateTime; |
| 286 |
> |
SQL_SHORT: |
| 287 |
> |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 288 |
> |
DataType := ftSmallInt |
| 289 |
> |
else |
| 290 |
> |
DataType := ftBCD; |
| 291 |
> |
SQL_LONG: |
| 292 |
> |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 293 |
> |
DataType := ftInteger |
| 294 |
> |
else if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 295 |
> |
DataType := ftBCD |
| 296 |
> |
else |
| 297 |
> |
DataType := ftFloat; |
| 298 |
> |
SQL_INT64: |
| 299 |
> |
if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale = 0) then |
| 300 |
> |
DataType := ftLargeInt |
| 301 |
> |
else if ((QSelect.Fields[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 302 |
> |
DataType := ftBCD |
| 303 |
> |
else |
| 304 |
> |
DataType := ftFloat; |
| 305 |
> |
SQL_DOUBLE, SQL_FLOAT, SQL_D_FLOAT: DataType := ftFloat; |
| 306 |
> |
SQL_BOOLEAN: |
| 307 |
> |
DataType := ftBoolean; |
| 308 |
> |
SQL_TEXT: DataType := ftString; |
| 309 |
> |
SQL_VARYING: |
| 310 |
> |
if ((QSelect.Fields[i].AsXSQLVar)^.sqllen < 1024) then |
| 311 |
> |
DataType := ftString |
| 312 |
> |
else DataType := ftBlob; |
| 313 |
> |
SQL_BLOB, SQL_ARRAY, SQL_QUAD: DataType := ftBlob; |
| 314 |
> |
end; |
| 315 |
> |
FParams.CreateParam(DataType, Trim(QSelect.Fields[i].Name), ptOutput); |
| 316 |
> |
end; |
| 317 |
> |
|
| 318 |
> |
DataType := ftUnknown; |
| 319 |
> |
for i := 0 to QSelect.Params.Count - 1 do begin |
| 320 |
> |
case QSelect.Params[i].SQLtype of |
| 321 |
> |
SQL_TYPE_DATE: DataType := ftDate; |
| 322 |
> |
SQL_TYPE_TIME: DataType := ftTime; |
| 323 |
> |
SQL_TIMESTAMP: DataType := ftDateTime; |
| 324 |
> |
SQL_SHORT: |
| 325 |
> |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 326 |
> |
DataType := ftSmallInt |
| 327 |
> |
else |
| 328 |
> |
DataType := ftBCD; |
| 329 |
> |
SQL_LONG: |
| 330 |
> |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 331 |
> |
DataType := ftInteger |
| 332 |
> |
else if ((QSelect.Params[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 333 |
> |
DataType := ftBCD |
| 334 |
> |
else DataType := ftFloat; |
| 335 |
> |
SQL_INT64: |
| 336 |
> |
if ((QSelect.Params[i].AsXSQLVar)^.sqlscale = 0) then |
| 337 |
> |
DataType := ftLargeInt |
| 338 |
> |
else if ((QSelect.Params[i].AsXSQLVar)^.sqlscale >= (-4)) then |
| 339 |
> |
DataType := ftBCD |
| 340 |
> |
else DataType := ftFloat; |
| 341 |
> |
SQL_DOUBLE, SQL_FLOAT, SQL_D_FLOAT: DataType := ftFloat; |
| 342 |
> |
SQL_BOOLEAN: |
| 343 |
> |
DataType := ftBoolean; |
| 344 |
> |
SQL_TEXT: DataType := ftString; |
| 345 |
> |
SQL_VARYING: |
| 346 |
> |
if ((QSelect.Params[i].AsXSQLVar)^.sqllen < 1024) then |
| 347 |
> |
DataType := ftString |
| 348 |
> |
else DataType := ftBlob; |
| 349 |
> |
SQL_BLOB, SQL_ARRAY, SQL_QUAD: DataType := ftBlob; |
| 350 |
> |
end; |
| 351 |
> |
FParams.CreateParam(DataType, Trim(QSelect.Params[i].Name), ptInput); |
| 352 |
> |
end; |
| 353 |
> |
end; |
| 354 |
> |
|
| 355 |
> |
procedure TIBStoredProc.SetPrepared(Value: Boolean); |
| 356 |
> |
begin |
| 357 |
> |
if Prepared <> Value then |
| 358 |
> |
begin |
| 359 |
> |
if Value then |
| 360 |
> |
try |
| 361 |
> |
if SelectSQL.Text = '' then GenerateSQL; |
| 362 |
> |
InternalPrepare; |
| 363 |
> |
if FParams.Count = 0 then CreateParamDesc; |
| 364 |
> |
FPrepared := True; |
| 365 |
> |
except |
| 366 |
> |
FreeStatement; |
| 367 |
> |
raise; |
| 368 |
> |
end |
| 369 |
> |
else FreeStatement; |
| 370 |
> |
end; |
| 371 |
> |
|
| 372 |
> |
end; |
| 373 |
> |
|
| 374 |
> |
procedure TIBStoredProc.Prepare; |
| 375 |
> |
begin |
| 376 |
> |
SetPrepared(True); |
| 377 |
> |
end; |
| 378 |
> |
|
| 379 |
> |
procedure TIBStoredProc.UnPrepare; |
| 380 |
> |
begin |
| 381 |
> |
SetPrepared(False); |
| 382 |
> |
end; |
| 383 |
> |
|
| 384 |
> |
procedure TIBStoredProc.FreeStatement; |
| 385 |
> |
begin |
| 386 |
> |
InternalUnPrepare; |
| 387 |
> |
FPrepared := False; |
| 388 |
> |
end; |
| 389 |
> |
|
| 390 |
> |
procedure TIBStoredProc.SetPrepare(Value: Boolean); |
| 391 |
> |
begin |
| 392 |
> |
if Value then Prepare |
| 393 |
> |
else UnPrepare; |
| 394 |
> |
end; |
| 395 |
> |
|
| 396 |
> |
procedure TIBStoredProc.CopyParams(Value: TParams); |
| 397 |
> |
begin |
| 398 |
> |
if not Prepared and (FParams.Count = 0) then |
| 399 |
> |
try |
| 400 |
> |
Prepare; |
| 401 |
> |
Value.Assign(FParams); |
| 402 |
> |
finally |
| 403 |
> |
UnPrepare; |
| 404 |
> |
end else |
| 405 |
> |
Value.Assign(FParams); |
| 406 |
> |
end; |
| 407 |
> |
|
| 408 |
> |
procedure TIBStoredProc.SetParamsList(Value: TParams); |
| 409 |
> |
begin |
| 410 |
> |
CheckInactive; |
| 411 |
> |
if Prepared then |
| 412 |
> |
begin |
| 413 |
> |
SetPrepared(False); |
| 414 |
> |
FParams.Assign(Value); |
| 415 |
> |
SetPrepared(True); |
| 416 |
> |
end else |
| 417 |
> |
FParams.Assign(Value); |
| 418 |
> |
end; |
| 419 |
> |
|
| 420 |
> |
function TIBStoredProc.ParamByName(const Value: string): TParam; |
| 421 |
> |
begin |
| 422 |
> |
Prepare; |
| 423 |
> |
Result := FParams.ParamByName(Value); |
| 424 |
> |
end; |
| 425 |
> |
|
| 426 |
> |
function TIBStoredProc.GetStoredProcedureNames: TStrings; |
| 427 |
> |
begin |
| 428 |
> |
FNameList.clear; |
| 429 |
> |
GetStoredProcedureNamesFromServer; |
| 430 |
> |
Result := FNameList; |
| 431 |
> |
end; |
| 432 |
> |
|
| 433 |
> |
procedure TIBStoredProc.GetStoredProcedureNamesFromServer; |
| 434 |
> |
var |
| 435 |
> |
Query : TIBSQL; |
| 436 |
> |
begin |
| 437 |
> |
if not (csReading in ComponentState) then begin |
| 438 |
> |
ActivateConnection; |
| 439 |
> |
Database.InternalTransaction.StartTransaction; |
| 440 |
> |
Query := TIBSQL.Create(self); |
| 441 |
> |
try |
| 442 |
> |
Query.GoToFirstRecordOnExecute := False; |
| 443 |
> |
Query.Database := DataBase; |
| 444 |
> |
Query.Transaction := Database.InternalTransaction; |
| 445 |
> |
Query.SQL.Text := 'Select RDB$PROCEDURE_NAME from RDB$PROCEDURES'; {do not localize} |
| 446 |
> |
Query.Prepare; |
| 447 |
> |
Query.ExecQuery; |
| 448 |
> |
while (not Query.EOF) and (Query.Next <> nil) do |
| 449 |
> |
FNameList.Add(TrimRight(Query.Current.ByName('RDB$PROCEDURE_NAME').AsString)); {do not localize} |
| 450 |
> |
finally |
| 451 |
> |
Query.Free; |
| 452 |
> |
Database.InternalTransaction.Commit; |
| 453 |
> |
end; |
| 454 |
> |
end; |
| 455 |
> |
end; |
| 456 |
> |
|
| 457 |
> |
procedure TIBStoredProc.SetParams; |
| 458 |
> |
var |
| 459 |
> |
i : integer; |
| 460 |
> |
j: integer; |
| 461 |
> |
begin |
| 462 |
> |
i := 0; |
| 463 |
> |
for j := 0 to FParams.Count - 1 do |
| 464 |
> |
begin |
| 465 |
> |
if (Params[j].ParamType <> ptInput) then |
| 466 |
> |
continue; |
| 467 |
> |
if not Params[j].Bound then |
| 468 |
> |
IBError(ibxeRequiredParamNotSet, [Params[j].Name]); |
| 469 |
> |
if Params[j].IsNull then |
| 470 |
> |
SQLParams[i].IsNull := True |
| 471 |
> |
else begin |
| 472 |
> |
SQLParams[i].IsNull := False; |
| 473 |
> |
case Params[j].DataType of |
| 474 |
> |
ftString: |
| 475 |
> |
SQLParams[i].AsString := Params[j].AsString; |
| 476 |
> |
ftSmallint, ftWord: |
| 477 |
> |
SQLParams[i].AsShort := Params[j].AsSmallInt; |
| 478 |
> |
ftBoolean: |
| 479 |
> |
SQLParams[i].AsBoolean := Params[j].AsBoolean; |
| 480 |
> |
ftInteger: |
| 481 |
> |
SQLParams[i].AsLong := Params[j].AsInteger; |
| 482 |
> |
ftLargeInt: |
| 483 |
> |
SQLParams[i].AsInt64 := Params[j].AsLargeInt; |
| 484 |
> |
ftFloat, ftCurrency: |
| 485 |
> |
SQLParams[i].AsDouble := Params[j].AsFloat; |
| 486 |
> |
ftBCD: |
| 487 |
> |
SQLParams[i].AsCurrency := Params[j].AsCurrency; |
| 488 |
> |
ftDate: |
| 489 |
> |
SQLParams[i].AsDate := Params[j].AsDateTime; |
| 490 |
> |
ftTime: |
| 491 |
> |
SQLParams[i].AsTime := Params[j].AsDateTime; |
| 492 |
> |
ftDateTime: |
| 493 |
> |
SQLParams[i].AsDateTime := Params[j].AsDateTime; |
| 494 |
> |
ftBlob, ftMemo: |
| 495 |
> |
SQLParams[i].AsString := Params[j].AsString; |
| 496 |
> |
else |
| 497 |
> |
IBError(ibxeNotSupported, [nil]); |
| 498 |
> |
end; |
| 499 |
> |
end; |
| 500 |
> |
Inc(i); |
| 501 |
> |
end; |
| 502 |
> |
end; |
| 503 |
> |
|
| 504 |
> |
procedure TIBStoredProc.SetParamsFromCursor; |
| 505 |
> |
var |
| 506 |
> |
I: Integer; |
| 507 |
> |
DataSet: TDataSet; |
| 508 |
> |
begin |
| 509 |
> |
if DataSource <> nil then |
| 510 |
> |
begin |
| 511 |
> |
DataSet := DataSource.DataSet; |
| 512 |
> |
if DataSet <> nil then |
| 513 |
> |
begin |
| 514 |
> |
DataSet.FieldDefs.Update; |
| 515 |
> |
for I := 0 to FParams.Count - 1 do |
| 516 |
> |
with FParams[I] do |
| 517 |
> |
if (not Bound) and |
| 518 |
> |
((ParamType = ptInput) or (ParamType = ptInputOutput)) then |
| 519 |
> |
AssignField(DataSet.FieldByName(Name)); |
| 520 |
> |
end; |
| 521 |
> |
end; |
| 522 |
> |
end; |
| 523 |
> |
|
| 524 |
> |
procedure TIBStoredProc.FetchDataIntoOutputParams; |
| 525 |
> |
var |
| 526 |
> |
i,j : Integer; |
| 527 |
> |
begin |
| 528 |
> |
j := 0; |
| 529 |
> |
for i := 0 to FParams.Count - 1 do |
| 530 |
> |
with Params[I] do |
| 531 |
> |
if ParamType = ptOutput then begin |
| 532 |
> |
Value := QSelect.Fields[j].Value; |
| 533 |
> |
Inc(j); |
| 534 |
> |
end; |
| 535 |
> |
end; |
| 536 |
> |
|
| 537 |
> |
procedure TIBStoredProc.InternalOpen; |
| 538 |
> |
begin |
| 539 |
> |
IBError(ibxeIsAExecuteProcedure,[nil]); |
| 540 |
> |
end; |
| 541 |
> |
|
| 542 |
> |
procedure TIBStoredProc.DefineProperties(Filer: TFiler); |
| 543 |
> |
|
| 544 |
> |
function WriteData: Boolean; |
| 545 |
> |
begin |
| 546 |
> |
if Filer.Ancestor <> nil then |
| 547 |
> |
Result := not FParams.IsEqual(TIBStoredProc(Filer.Ancestor).FParams) else |
| 548 |
> |
Result := FParams.Count > 0; |
| 549 |
> |
end; |
| 550 |
> |
|
| 551 |
> |
begin |
| 552 |
> |
inherited DefineProperties(Filer); |
| 553 |
> |
Filer.DefineProperty('ParamData', ReadParamData, WriteParamData, WriteData); {do not localize} |
| 554 |
> |
end; |
| 555 |
> |
|
| 556 |
> |
procedure TIBStoredProc.WriteParamData(Writer: TWriter); |
| 557 |
> |
begin |
| 558 |
> |
Writer.WriteCollection(Params); |
| 559 |
> |
end; |
| 560 |
> |
|
| 561 |
> |
procedure TIBStoredProc.ReadParamData(Reader: TReader); |
| 562 |
> |
begin |
| 563 |
> |
Reader.ReadValue; |
| 564 |
> |
Reader.ReadCollection(Params); |
| 565 |
> |
end; |
| 566 |
> |
|
| 567 |
> |
end. |
