00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Abstract StoredProc component }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { & Janos Fegyverneki }
00008 { }
00009 {*********************************************************}
00010
00011 {@********************************************************}
00012 { Copyright (c) 1999-2006 Zeos Development Group }
00013 { }
00014 { License Agreement: }
00015 { }
00016 { This library is distributed in the hope that it will be }
00017 { useful, but WITHOUT ANY WARRANTY; without even the }
00018 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00019 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00020 { Public License for more details. }
00021 { }
00022 { The source code of the ZEOS Libraries and packages are }
00023 { distributed under the Library GNU General Public }
00024 { License (see the file COPYING / COPYING.ZEOS) }
00025 { with the following modification: }
00026 { As a special exception, the copyright holders of this }
00027 { library give you permission to link this library with }
00028 { independent modules to produce an executable, }
00029 { regardless of the license terms of these independent }
00030 { modules, and to copy and distribute the resulting }
00031 { executable under terms of your choice, provided that }
00032 { you also meet, for each linked independent module, }
00033 { the terms and conditions of the license of that module. }
00034 { An independent module is a module which is not derived }
00035 { from or based on this library. If you modify this }
00036 { library, you may extend this exception to your version }
00037 { of the library, but you are not obligated to do so. }
00038 { If you do not wish to do so, delete this exception }
00039 { statement from your version. }
00040 { }
00041 { }
00042 { The project web site is located on: }
00043 { http:
00044 { http:
00045 { svn:
00046 { }
00047 { http:
00048 { http:
00049 { }
00050 { }
00051 { }
00052 { Zeos Development Group. }
00053 {********************************************************@}
00054
00055 unit ZStoredProcedure;
00056
00057 interface
00058
00059 {$I ZComponent.inc}
00060
00061 uses
00062 {$IFDEF MSWINDOWS}
00063 Windows,
00064 {$ENDIF}
00065 {$IFNDEF VER130BELOW}
00066 Types,
00067 {$ENDIF}
00068 SysUtils, DB, Classes, ZConnection, ZDbcIntfs,
00069 ZAbstractDataset, ZCompatibility;
00070
00071 type
00072
00073 {**
00074 Abstract dataset to access to stored procedures.
00075 }
00076 TZStoredProc = class(TZAbstractDataset)
00077 private
00078 procedure RetrieveParamValues;
00079 function GetStoredProcName: string;
00080 procedure SetStoredProcName(const Value: string);
00081 protected
00082 { property CallableResultSet: IZCallableStatement read FCallableStatement
00083 write FCallableStatement;}
00084
00085 function CreateStatement(const SQL: string; Properties: TStrings):
00086 IZPreparedStatement; override;
00087 procedure SetStatementParams(Statement: IZPreparedStatement;
00088 ParamNames: TStringDynArray; Params: TParams;
00089 DataLink: TDataLink); override;
00090 procedure InternalOpen; override;
00091 procedure InternalClose; override;
00092
00093 protected
00094 {$IFDEF WITH_IPROVIDER}
00095 function PSIsSQLBased: Boolean; override;
00096 procedure PSExecute; override;
00097 {$IFDEF BDS4_UP}
00098 function PSGetTableNameW: WideString; override;
00099 {$ELSE}
00100 function PSGetTableName: string; override;
00101 {$ENDIF}
00102 procedure PSSetCommandText(const ACommandText: string); override;
00103 {$ENDIF}
00104
00105 public
00106 procedure ExecProc; virtual;
00107
00108 published
00109 property Active;
00110 property ParamCheck;
00111 property Params;
00112 property ShowRecordTypes;
00113 property Options;
00114 property StoredProcName: string read GetStoredProcName
00115 write SetStoredProcName;
00116 end;
00117
00118 implementation
00119
00120 uses
00121 ZAbstractRODataset, ZMessages, ZDatasetUtils;
00122
00123 { TZStoredProc }
00124
00125 {**
00126 Creates a DBC statement for the query.
00127 @param SQL an SQL query.
00128 @param Properties a statement specific properties.
00129 @returns a created DBC statement.
00130 }
00131 function TZStoredProc.CreateStatement(const SQL: string; Properties: TStrings):
00132 IZPreparedStatement;
00133 var
00134 I: Integer;
00135 CallableStatement: IZCallableStatement;
00136 begin
00137 CallableStatement := Connection.DbcConnection.PrepareCallWithParams(
00138 Trim(SQL), Properties);
00139
00140 for I := 0 to Params.Count - 1 do
00141 begin
00142 if Params[I].ParamType in [ptResult, ptOutput, ptInputOutput] then
00143 CallableStatement.RegisterOutParameter(I + 1,
00144 Ord(ConvertDatasetToDbcType(Params[I].DataType)));
00145 end;
00146 Result := CallableStatement;
00147 end;
00148
00149 {**
00150 Fill prepared statement with parameters.
00151 @param Statement a prepared SQL statement.
00152 @param ParamNames an array of parameter names.
00153 @param Params a collection of SQL parameters.
00154 @param DataLink a datalink to get parameters.
00155 }
00156 procedure TZStoredProc.SetStatementParams(Statement: IZPreparedStatement;
00157 ParamNames: TStringDynArray; Params: TParams; DataLink: TDataLink);
00158 var
00159 I: Integer;
00160 Param: TParam;
00161 Stream: TStream;
00162 begin
00163 for I := 0 to Params.Count - 1 do
00164 begin
00165 Param := Params[I];
00166
00167 if Params[I].ParamType in [ptResult, ptOutput] then
00168 Continue;
00169
00170 if Param.IsNull then
00171 Statement.SetNull(I+1, ConvertDatasetToDbcType(Param.DataType))
00172 else begin
00173 case Param.DataType of
00174 ftBoolean:
00175 Statement.SetBoolean(I+1, Param.AsBoolean);
00176 ftSmallInt:
00177 Statement.SetShort(I+1, Param.AsSmallInt);
00178 ftInteger, ftAutoInc:
00179 Statement.SetInt(I+1, Param.AsInteger);
00180 ftFloat:
00181 Statement.SetDouble(I+1, Param.AsFloat);
00182 ftLargeInt:
00183 Statement.SetLong(I+1, StrToInt64(Param.AsString));
00184 ftString , ftFixedChar:
00185 Statement.SetString(I+1, Param.AsString);
00186 ftBytes:
00187 Statement.SetString(I+1, Param.AsString);
00188 ftDate:
00189 Statement.SetDate(I+1, Param.AsDate);
00190 ftTime:
00191 Statement.SetTime(I+1, Param.AsTime);
00192 ftDateTime{$IFNDEF VER130}, ftTimestamp{$ENDIF}:
00193 Statement.SetTimestamp(I+1, Param.AsDateTime);
00194 ftMemo:
00195 begin
00196 Stream := TStringStream.Create(Param.AsMemo);
00197 try
00198 Statement.SetAsciiStream(I+1, Stream);
00199 finally
00200 Stream.Free;
00201 end;
00202 end;
00203 ftBlob:
00204 begin
00205 Stream := TStringStream.Create(Param.AsBlob);
00206 try
00207 Statement.SetBinaryStream(I+1, Stream);
00208 finally
00209 Stream.Free;
00210 end;
00211 end;
00212 else
00213 raise EZDatabaseError.Create(SUnKnownParamDataType);
00214 end;
00215 end;
00216 end;
00217 end;
00218
00219 {**
00220 Retrieves parameter values from callable statement.
00221 }
00222 procedure TZStoredProc.RetrieveParamValues;
00223 var
00224 I: Integer;
00225 Param: TParam;
00226 FCallableStatement: IZCallableStatement;
00227 begin
00228 if not Assigned(FCallableStatement) then
00229 begin
00230 if Assigned(Statement) then
00231 Statement.QueryInterface(IZCallableStatement, FCallableStatement);
00232 if not Assigned(FCallableStatement) then
00233 Exit;
00234 end;
00235
00236 for I := 0 to Params.Count - 1 do
00237 begin
00238 Param := Params[I];
00239 if not (Param.ParamType in [ptResult, ptOutput, ptInputOutput]) then
00240 Continue;
00241
00242 if FCallableStatement.IsNull(I + 1) then
00243 Param.Clear
00244 else
00245 case Param.DataType of
00246 ftBoolean:
00247 Param.AsBoolean := FCallableStatement.GetBoolean(I + 1);
00248 ftSmallInt:
00249 Param.AsSmallInt := FCallableStatement.GetShort(I + 1);
00250 ftInteger, ftAutoInc:
00251 Param.AsInteger := FCallableStatement.GetInt(I + 1);
00252 ftFloat:
00253 Param.AsFloat := FCallableStatement.GetDouble(I + 1);
00254 ftLargeInt:
00255 {$IFNDEF VER130BELOW}
00256 Param.Value := FCallableStatement.GetLong(I + 1);
00257 {$ELSE}
00258 Param.AsInteger := FCallableStatement.GetLong(I + 1);
00259 {$ENDIF}
00260 ftString:
00261 Param.AsString := FCallableStatement.GetString(I + 1);
00262 ftBytes:
00263 Param.AsString := FCallableStatement.GetString(I + 1);
00264 ftDate:
00265 Param.AsDate := FCallableStatement.GetDate(I + 1);
00266 ftTime:
00267 Param.AsTime := FCallableStatement.GetTime(I + 1);
00268 ftDateTime:
00269 Param.AsDateTime := FCallableStatement.GetTimestamp(I + 1);
00270 else
00271 raise EZDatabaseError.Create(SUnKnownParamDataType);
00272 end;
00273 end;
00274 end;
00275
00276 {**
00277 Performs internal query opening.
00278 }
00279 procedure TZStoredProc.InternalOpen;
00280 begin
00281 inherited InternalOpen;
00282
00283 RetrieveParamValues;
00284 end;
00285
00286 {**
00287 Performs internal query closing.
00288 }
00289 procedure TZStoredProc.InternalClose;
00290 begin
00291 inherited InternalClose;
00292 end;
00293
00294 function TZStoredProc.GetStoredProcName: string;
00295 begin
00296 Result := Trim(SQL.Text);
00297 end;
00298
00299 procedure TZStoredProc.SetStoredProcName(const Value: string);
00300 var
00301 ResultSet: IZResultSet;
00302 OldParams: TParams;
00303 Catalog,
00304 Schema,
00305 ObjectName: string;
00306 begin
00307 if AnsiCompareText(Trim(SQL.Text), Trim(Value)) <> 0 then
00308 begin
00309 SQL.Text := Value;
00310 if ParamCheck and (Value <> '') and not (csLoading in ComponentState) and Assigned(Connection) then
00311 begin
00312 Connection.ShowSQLHourGlass;
00313 try
00314 SplitQualifiedObjectName(Value, Catalog, Schema, ObjectName);
00315 ObjectName := Connection.DbcConnection.GetMetadata.AddEscapeCharToWildcards(ObjectName);
00316 ResultSet := Connection.DbcConnection.GetMetadata.GetProcedureColumns(Catalog, Schema, ObjectName, '');
00317 OldParams := TParams.Create;
00318 try
00319 OldParams.Assign(Params);
00320 Params.Clear;
00321 while ResultSet.Next do
00322 if ResultSet.GetInt(5) >= 0 then
00323 Params.CreateParam(ConvertDbcToDatasetType(TZSqlType(ResultSet.GetInt(6))),
00324 ResultSet.GetString(4), TParamType(ResultSet.GetInt(5)));
00325 Params.AssignValues(OldParams);
00326 finally
00327 OldParams.Free;
00328 end;
00329 finally
00330 Connection.HideSQLHourGlass;
00331 end;
00332 end;
00333 end;
00334 end;
00335
00336 procedure TZStoredProc.ExecProc;
00337 begin
00338 Connection.ShowSQLHourGlass;
00339 try
00340 if Active then
00341 Close;
00342 ExecSQL;
00343 RetrieveParamValues;
00344 finally
00345 Connection.HideSQLHourGlass;
00346 end;
00347 end;
00348
00349 {$IFDEF WITH_IPROVIDER}
00350
00351 {**
00352 Checks if dataset can execute SQL queries?
00353 @returns <code>True</code> if the query can execute SQL.
00354 }
00355 function TZStoredProc.PSIsSQLBased: Boolean;
00356 begin
00357 Result := False;
00358 end;
00359
00360 {**
00361 Gets the name of the stored procedure.
00362 @returns the name of this stored procedure.
00363 }
00364 {$IFDEF BDS4_UP}
00365 function TZStoredProc.PSGetTableNameW: WideString;
00366 {$ELSE}
00367 function TZStoredProc.PSGetTableName: string;
00368 {$ENDIF}
00369 begin
00370 Result := StoredProcName;
00371 end;
00372
00373 {**
00374 Executes this stored procedure.
00375 }
00376 procedure TZStoredProc.PSExecute;
00377 begin
00378 ExecProc;
00379 end;
00380
00381 {**
00382 Assignes a new name for this stored procedure.
00383 @param ACommandText a new name for this stored procedure.
00384 }
00385 procedure TZStoredProc.PSSetCommandText(const ACommandText: string);
00386 begin
00387 StoredProcName := ACommandText;
00388 end;
00389
00390 {$ENDIF}
00391
00392 end.