00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Oracle Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Seroukhov }
00007 { }
00008 {*********************************************************}
00009
00010 {@********************************************************}
00011 { Copyright (c) 1999-2006 Zeos Development Group }
00012 { }
00013 { License Agreement: }
00014 { }
00015 { This library is distributed in the hope that it will be }
00016 { useful, but WITHOUT ANY WARRANTY; without even the }
00017 { implied warranty of MERCHANTABILITY or FITNESS FOR }
00018 { A PARTICULAR PURPOSE. See the GNU Lesser General }
00019 { Public License for more details. }
00020 { }
00021 { The source code of the ZEOS Libraries and packages are }
00022 { distributed under the Library GNU General Public }
00023 { License (see the file COPYING / COPYING.ZEOS) }
00024 { with the following modification: }
00025 { As a special exception, the copyright holders of this }
00026 { library give you permission to link this library with }
00027 { independent modules to produce an executable, }
00028 { regardless of the license terms of these independent }
00029 { modules, and to copy and distribute the resulting }
00030 { executable under terms of your choice, provided that }
00031 { you also meet, for each linked independent module, }
00032 { the terms and conditions of the license of that module. }
00033 { An independent module is a module which is not derived }
00034 { from or based on this library. If you modify this }
00035 { library, you may extend this exception to your version }
00036 { of the library, but you are not obligated to do so. }
00037 { If you do not wish to do so, delete this exception }
00038 { statement from your version. }
00039 { }
00040 { }
00041 { The project web site is located on: }
00042 { http:
00043 { http:
00044 { svn:
00045 { }
00046 { http:
00047 { http:
00048 { }
00049 { }
00050 { }
00051 { Zeos Development Group. }
00052 {********************************************************@}
00053
00054 unit ZDbcOracleResultSet;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 Classes, SysUtils, ZSysUtils, ZDbcIntfs,
00062 ZDbcResultSet, ZPlainOracleDriver, ZDbcResultSetMetadata, ZDbcLogging,
00063 ZCompatibility, ZDbcOracleUtils;
00064
00065 type
00066
00067 {** Implements Oracle ResultSet. }
00068 TZOracleResultSet = class(TZAbstractResultSet)
00069 private
00070 FSQL: string;
00071 FStmtHandle: POCIStmt;
00072 FErrorHandle: POCIError;
00073 FPlainDriver: IZOraclePlainDriver;
00074 FOutVars: PZSQLVars;
00075 protected
00076 procedure Open; override;
00077 function GetSQLVarHolder(ColumnIndex: Integer): PZSQLVar;
00078 function GetAsStringValue(ColumnIndex: Integer;
00079 SQLVarHolder: PZSQLVar): string;
00080 function GetAsLongIntValue(ColumnIndex: Integer;
00081 SQLVarHolder: PZSQLVar): LongInt;
00082 function GetAsDoubleValue(ColumnIndex: Integer;
00083 SQLVarHolder: PZSQLVar): Double;
00084 function GetAsDateTimeValue(ColumnIndex: Integer;
00085 SQLVarHolder: PZSQLVar): TDateTime;
00086 public
00087 constructor Create(PlainDriver: IZOraclePlainDriver;
00088 Statement: IZStatement; SQL: string; StmtHandle: POCIStmt;
00089 ErrorHandle: POCIError);
00090 destructor Destroy; override;
00091
00092 procedure Close; override;
00093
00094 function IsNull(ColumnIndex: Integer): Boolean; override;
00095 function GetString(ColumnIndex: Integer): string; override;
00096 function GetBoolean(ColumnIndex: Integer): Boolean; override;
00097 function GetByte(ColumnIndex: Integer): ShortInt; override;
00098 function GetShort(ColumnIndex: Integer): SmallInt; override;
00099 function GetInt(ColumnIndex: Integer): Integer; override;
00100 function GetLong(ColumnIndex: Integer): Int64; override;
00101 function GetFloat(ColumnIndex: Integer): Single; override;
00102 function GetDouble(ColumnIndex: Integer): Double; override;
00103 function GetBigDecimal(ColumnIndex: Integer): Extended; override;
00104 function GetBytes(ColumnIndex: Integer): TByteDynArray; override;
00105 function GetDate(ColumnIndex: Integer): TDateTime; override;
00106 function GetTime(ColumnIndex: Integer): TDateTime; override;
00107 function GetTimestamp(ColumnIndex: Integer): TDateTime; override;
00108 function GetBlob(ColumnIndex: Integer): IZBlob; override;
00109
00110 function Next(): Boolean; override;
00111 end;
00112
00113 {** Represents an interface, specific for Oracle blobs. }
00114 IZOracleBlob = interface(IZBlob)
00115 ['{3D861AAC-B263-42F1-B359-2A188D1D986A}']
00116 function GetLobLocator: POCILobLocator;
00117 procedure CreateBlob;
00118 procedure ReadBlob;
00119 procedure WriteBlob;
00120 end;
00121
00122 {** Implements external blob wrapper object for Oracle. }
00123 TZOracleBlob = class(TZAbstractBlob, IZOracleBlob)
00124 private
00125 FHandle: IZConnection;
00126 FLobLocator: POCILobLocator;
00127 FPlainDriver: IZOraclePlainDriver;
00128 FBlobType: TZSQLType;
00129 FTemporary: Boolean;
00130 public
00131 constructor Create(PlainDriver: IZOraclePlainDriver; Data: Pointer;
00132 Size: Integer; Handle: IZConnection; LobLocator: POCILobLocator;
00133 BlobType: TZSQLType);
00134 destructor Destroy; override;
00135
00136 function GetLobLocator: POCILobLocator;
00137 procedure CreateBlob;
00138 procedure ReadBlob;
00139 procedure WriteBlob;
00140
00141 function IsEmpty: Boolean; override;
00142 function Clone: IZBlob; override;
00143
00144 function GetStream: TStream; override;
00145 end;
00146
00147 implementation
00148
00149 uses
00150 Math, ZMessages, ZDbcOracle;
00151
00152 { TZOracleResultSet }
00153
00154 {**
00155 Constructs this object, assignes main properties and
00156 opens the record set.
00157 @param PlainDriver a Oracle plain driver.
00158 @param Statement a related SQL statement object.
00159 @param SQL a SQL statement.
00160 @param Handle a Oracle specific query handle.
00161 }
00162 constructor TZOracleResultSet.Create(PlainDriver: IZOraclePlainDriver;
00163 Statement: IZStatement; SQL: string; StmtHandle: POCIStmt;
00164 ErrorHandle: POCIError);
00165 begin
00166 inherited Create(Statement, SQL, nil);
00167
00168 FSQL := SQL;
00169 FStmtHandle := StmtHandle;
00170 FErrorHandle := ErrorHandle;
00171 FPlainDriver := PlainDriver;
00172 ResultSetConcurrency := rcReadOnly;
00173
00174 Open;
00175 end;
00176
00177 {**
00178 Destroys this object and cleanups the memory.
00179 }
00180 destructor TZOracleResultSet.Destroy;
00181 begin
00182 inherited Destroy;
00183 end;
00184
00185 {**
00186 Opens this recordset.
00187 }
00188 procedure TZOracleResultSet.Open;
00189 var
00190 I: Integer;
00191 ColumnInfo: TZColumnInfo;
00192 Status: Integer;
00193 Connection: IZOracleConnection;
00194 CurrentVar: PZSQLVar;
00195 ColumnCount: ub4;
00196 TempColumnName: PChar;
00197 TempColumnNameLen: Integer;
00198 begin
00199 if ResultSetConcurrency = rcUpdatable then
00200 raise EZSQLException.Create(SLiveResultSetsAreNotSupported);
00201
00202 if not Assigned(FStmtHandle) or not Assigned(FErrorHandle) then
00203 raise EZSQLException.Create(SCanNotRetrieveResultSetData);
00204
00205 Connection := GetStatement.GetConnection as IZOracleConnection;
00206
00207 Status := FPlainDriver.StmtExecute(Connection.GetContextHandle, FStmtHandle,
00208 FErrorHandle, 1, 0, nil, nil, OCI_DESCRIBE_ONLY);
00209 CheckOracleError(FPlainDriver, FErrorHandle, Status, lcExecute, FSQL);
00210
00211 { Resize SQLVERS structure if needed }
00212 FPlainDriver.AttrGet(FStmtHandle, OCI_HTYPE_STMT, @ColumnCount, nil,
00213 OCI_ATTR_PARAM_COUNT, FErrorHandle);
00214 AllocateOracleSQLVars(FOutVars, ColumnCount);
00215 FOutVars.ActualNum := ColumnCount;
00216
00217 { Allocates memory for result set }
00218 for I := 1 to FOutVars.ActualNum do
00219 begin
00220 CurrentVar := @FOutVars.Variables[I];
00221 CurrentVar.Handle := nil;
00222
00223 FPlainDriver.ParamGet(FStmtHandle, OCI_HTYPE_STMT, FErrorHandle,
00224 CurrentVar.Handle, I);
00225 FPlainDriver.AttrGet(CurrentVar.Handle, OCI_DTYPE_PARAM,
00226 @CurrentVar.DataSize, nil, OCI_ATTR_DATA_SIZE, FErrorHandle);
00227 FPlainDriver.AttrGet(CurrentVar.Handle, OCI_DTYPE_PARAM,
00228 @CurrentVar.DataType, nil, OCI_ATTR_DATA_TYPE, FErrorHandle);
00229 CurrentVar.Scale := 0;
00230 CurrentVar.Precision := 0;
00231
00232 case CurrentVar.DataType of
00233 SQLT_CHR, SQLT_VCS, SQLT_AFC, SQLT_AVC, SQLT_STR, SQLT_VST:
00234 begin
00235 CurrentVar.ColType := stString
00236 end;
00237 SQLT_NUM:
00238 begin
00239 FPlainDriver.AttrGet(CurrentVar.Handle, OCI_DTYPE_PARAM,
00240 @CurrentVar.Precision, nil, OCI_ATTR_PRECISION, FErrorHandle);
00241 FPlainDriver.AttrGet(CurrentVar.Handle, OCI_DTYPE_PARAM,
00242 @CurrentVar.Scale, nil, OCI_ATTR_SCALE, FErrorHandle);
00243
00244 if (CurrentVar.Scale = 0) and (CurrentVar.Precision <> 0) then
00245 begin
00246 if CurrentVar.Precision = 0 then
00247 CurrentVar.ColType := stInteger
00248 else if CurrentVar.Precision <= 2 then
00249 CurrentVar.ColType := stByte
00250 else if CurrentVar.Precision <= 4 then
00251 CurrentVar.ColType := stShort
00252 else if CurrentVar.Precision <= 9 then
00253 CurrentVar.ColType := stInteger
00254 else CurrentVar.ColType := stLong;
00255 end else
00256 CurrentVar.ColType := stDouble;
00257 end;
00258 SQLT_INT, _SQLT_PLI:
00259 CurrentVar.ColType := stInteger;
00260 SQLT_LNG, SQLT_LVC:
00261 CurrentVar.ColType := stAsciiStream;
00262 SQLT_RID, SQLT_RDD:
00263 begin
00264 CurrentVar.ColType := stString;
00265 CurrentVar.DataSize := 20;
00266 end;
00267 SQLT_DAT, SQLT_DATE:
00268 CurrentVar.ColType := stDate;
00269 SQLT_TIME, SQLT_TIME_TZ:
00270 CurrentVar.ColType := stTime;
00271 SQLT_TIMESTAMP, SQLT_TIMESTAMP_TZ, SQLT_TIMESTAMP_LTZ:
00272 CurrentVar.ColType := stTimestamp;
00273 SQLT_BIN, SQLT_LBI:
00274 CurrentVar.ColType := stBinaryStream;
00275 SQLT_CLOB:
00276 begin
00277 CurrentVar.ColType := stAsciiStream;
00278 CurrentVar.TypeCode := CurrentVar.DataType;
00279 end;
00280 SQLT_BLOB:
00281 begin
00282 CurrentVar.ColType := stBinaryStream;
00283 CurrentVar.TypeCode := CurrentVar.DataType;
00284 end
00285 else
00286 CurrentVar.ColType := stUnknown;
00287 end;
00288
00289 InitializeOracleVar(FPlainDriver, Connection, CurrentVar,
00290 CurrentVar.ColType, CurrentVar.TypeCode, CurrentVar.DataSize);
00291
00292 Status := FPlainDriver.DefineByPos(FStmtHandle, CurrentVar.Define,
00293 FErrorHandle, I, CurrentVar.Data, CurrentVar.Length, CurrentVar.TypeCode,
00294 @CurrentVar.Indicator, nil, nil, OCI_DEFAULT);
00295 CheckOracleError(FPlainDriver, FErrorHandle, Status, lcExecute, FSQL);
00296 end;
00297
00298 { Fills the column info. }
00299 ColumnsInfo.Clear;
00300 for I := 1 to FOutVars.ActualNum do
00301 begin
00302 CurrentVar := @FOutVars.Variables[I];
00303 ColumnInfo := TZColumnInfo.Create;
00304
00305 with ColumnInfo do
00306 begin
00307 ColumnName := '';
00308 TableName := '';
00309
00310 TempColumnName := nil;
00311 FPlainDriver.AttrGet(CurrentVar.Handle, OCI_DTYPE_PARAM,
00312 @TempColumnName, @TempColumnNameLen, OCI_ATTR_NAME, FErrorHandle);
00313 if TempColumnName <> nil then
00314 ColumnLabel := BufferToStr(TempColumnName, TempColumnNameLen);
00315
00316 ColumnDisplaySize := 0;
00317 AutoIncrement := False;
00318 Signed := True;
00319 Nullable := ntNullable;
00320
00321 ColumnType := CurrentVar.ColType;
00322 Scale := CurrentVar.Scale;
00323 if (ColumnType = stString) or (ColumnType = stUnicodeString) then
00324 Precision := CurrentVar.DataSize
00325 else Precision := CurrentVar.Precision;
00326 end;
00327
00328 ColumnsInfo.Add(ColumnInfo);
00329 end;
00330
00331 inherited Open;
00332 end;
00333
00334 {**
00335 Releases this <code>ResultSet</code> object's database and
00336 JDBC resources immediately instead of waiting for
00337 this to happen when it is automatically closed.
00338
00339 <P><B>Note:</B> A <code>ResultSet</code> object
00340 is automatically closed by the
00341 <code>Statement</code> object that generated it when
00342 that <code>Statement</code> object is closed,
00343 re-executed, or is used to retrieve the next result from a
00344 sequence of multiple results. A <code>ResultSet</code> object
00345 is also automatically closed when it is garbage collected.
00346 }
00347 procedure TZOracleResultSet.Close;
00348 begin
00349 FreeOracleSQLVars(FPlainDriver, FOutVars);
00350 FreeOracleStatementHandles(FPlainDriver, FStmtHandle, FErrorHandle);
00351 inherited Close;
00352 end;
00353
00354 {**
00355 Indicates if the value of the designated column in the current row
00356 of this <code>ResultSet</code> object is Null.
00357
00358 @param columnIndex the first column is 1, the second is 2, ...
00359 @return if the value is SQL <code>NULL</code>, the
00360 value returned is <code>true</code>. <code>false</code> otherwise.
00361 }
00362 function TZOracleResultSet.IsNull(ColumnIndex: Integer): Boolean;
00363 var
00364 CurrentVar: PZSQLVar;
00365 begin
00366 {$IFNDEF DISABLE_CHECKING}
00367 CheckClosed;
00368 if (RowNo < 1) or (RowNo > LastRowNo) then
00369 raise EZSQLException.Create(SRowDataIsNotAvailable);
00370 if (ColumnIndex <=0) or (ColumnIndex > FOutVars.ActualNum) then
00371 begin
00372 raise EZSQLException.Create(
00373 Format(SColumnIsNotAccessable, [ColumnIndex]));
00374 end;
00375 {$ENDIF}
00376
00377 CurrentVar := @FOutVars.Variables[ColumnIndex];
00378 Result := (CurrentVar.Indicator < 0);
00379 end;
00380
00381 {**
00382 Gets a holder for SQL output variable.
00383 @param ColumnIndex an index of the column to read.
00384 @returns an output variable holder or <code>nil</code> if column is empty.
00385 }
00386 function TZOracleResultSet.GetSQLVarHolder(ColumnIndex: Integer): PZSQLVar;
00387 begin
00388 {$IFNDEF DISABLE_CHECKING}
00389 CheckClosed;
00390 if (RowNo < 1) or (RowNo > LastRowNo) then
00391 raise EZSQLException.Create(SRowDataIsNotAvailable);
00392 {$ENDIF}
00393
00394 Result := @FOutVars.Variables[ColumnIndex];
00395 LastWasNull := (Result.Indicator < 0) or (Result.Data = nil);
00396 if LastWasNull then
00397 Result := nil;
00398 end;
00399
00400 {**
00401 Gets the value of the designated column in the current row
00402 of this <code>ResultSet</code> object as a <code>String</code>.
00403
00404 @param ColumnIndex the first column is 1, the second is 2, ...
00405 @param SQLVarHolder a reference to SQL variable holder or <code>nil</code>
00406 to force retrieving the variable.
00407 @return the column value; if the value is SQL <code>NULL</code>, the
00408 value returned is <code>null</code>
00409 }
00410 function TZOracleResultSet.GetAsStringValue(ColumnIndex: Integer;
00411 SQLVarHolder: PZSQLVar): string;
00412 var
00413 OldSeparator: Char;
00414 Blob: IZBlob;
00415 begin
00416 if SQLVarHolder = nil then
00417 SQLVarHolder := GetSQLVarHolder(ColumnIndex);
00418 if SQLVarHolder <> nil then
00419 begin
00420 case SQLVarHolder.TypeCode of
00421 SQLT_INT:
00422 Result := IntToStr(PLongInt(SQLVarHolder.Data)^);
00423 SQLT_FLT:
00424 begin
00425 OldSeparator := DecimalSeparator;
00426 DecimalSeparator := '.';
00427 Result := FloatToSqlStr(PDouble(SQLVarHolder.Data)^);
00428 DecimalSeparator := OldSeparator;
00429 end;
00430 SQLT_STR:
00431 Result := StrPas(SQLVarHolder.Data);
00432 SQLT_LVB, SQLT_LVC:
00433 begin
00434 Result := BufferToStr(PChar(SQLVarHolder.Data) + SizeOf(Integer),
00435 PInteger(SQLVarHolder.Data)^);
00436 end;
00437 SQLT_DAT, SQLT_TIMESTAMP:
00438 begin
00439 Result := DateTimeToAnsiSQLDate(
00440 GetAsDateTimeValue(ColumnIndex, SQLVarHolder));
00441 end;
00442 SQLT_BLOB, SQLT_CLOB:
00443 begin
00444 Blob := GetBlob(ColumnIndex);
00445 Result := Blob.GetString;
00446 end;
00447 end;
00448 end else
00449 Result := '';
00450 end;
00451
00452 {**
00453 Gets the value of the designated column in the current row
00454 of this <code>ResultSet</code> object as a <code>LongInt</code>.
00455
00456 @param ColumnIndex the first column is 1, the second is 2, ...
00457 @param SQLVarHolder a reference to SQL variable holder or <code>nil</code>
00458 to force retrieving the variable.
00459 @return the column value; if the value is SQL <code>NULL</code>, the
00460 value returned is <code>0</code>
00461 }
00462 function TZOracleResultSet.GetAsLongIntValue(ColumnIndex: Integer;
00463 SQLVarHolder: PZSQLVar): LongInt;
00464 begin
00465 if SQLVarHolder = nil then
00466 SQLVarHolder := GetSQLVarHolder(ColumnIndex);
00467 if SQLVarHolder <> nil then
00468 begin
00469 case SQLVarHolder.TypeCode of
00470 SQLT_INT:
00471 Result := PLongInt(SQLVarHolder.Data)^;
00472 SQLT_FLT:
00473 Result := Trunc(PDouble(SQLVarHolder.Data)^);
00474 else
00475 begin
00476 Result := Trunc(SqlStrToFloatDef(
00477 GetAsStringValue(ColumnIndex, SQLVarHolder), 0));
00478 end;
00479 end;
00480 end else
00481 Result := 0;
00482 end;
00483
00484 {**
00485 Gets the value of the designated column in the current row
00486 of this <code>ResultSet</code> object as a <code>Double</code>.
00487
00488 @param ColumnIndex the first column is 1, the second is 2, ...
00489 @param SQLVarHolder a reference to SQL variable holder or <code>nil</code>
00490 to force retrieving the variable.
00491 @return the column value; if the value is SQL <code>NULL</code>, the
00492 value returned is <code>0.0</code>
00493 }
00494 function TZOracleResultSet.GetAsDoubleValue(ColumnIndex: Integer;
00495 SQLVarHolder: PZSQLVar): Double;
00496 begin
00497 if SQLVarHolder = nil then
00498 SQLVarHolder := GetSQLVarHolder(ColumnIndex);
00499 if SQLVarHolder <> nil then
00500 begin
00501 case SQLVarHolder.TypeCode of
00502 SQLT_INT:
00503 Result := PLongInt(SQLVarHolder.Data)^;
00504 SQLT_FLT:
00505 Result := PDouble(SQLVarHolder.Data)^;
00506 else
00507 begin
00508 Result := SqlStrToFloatDef(
00509 GetAsStringValue(ColumnIndex, SQLVarHolder), 0);
00510 end;
00511 end;
00512 end else
00513 Result := 0;
00514 end;
00515
00516 {**
00517 Gets the value of the designated column in the current row
00518 of this <code>ResultSet</code> object as a <code>DateTime</code>.
00519
00520 @param ColumnIndex the first column is 1, the second is 2, ...
00521 @param SQLVarHolder a reference to SQL variable holder or <code>nil</code>
00522 to force retrieving the variable.
00523 @return the column value; if the value is SQL <code>NULL</code>, the
00524 value returned is <code>0</code>
00525 }
00526 function TZOracleResultSet.GetAsDateTimeValue(ColumnIndex: Integer;
00527 SQLVarHolder: PZSQLVar): TDateTime;
00528 var
00529 Status: Integer;
00530 Year: SmallInt;
00531 Month, Day: Byte;
00532 Hour, Minute, Second: Byte;
00533 Millis: Integer;
00534 Connection: IZOracleConnection;
00535 begin
00536 if SQLVarHolder = nil then
00537 SQLVarHolder := GetSQLVarHolder(ColumnIndex);
00538 if SQLVarHolder <> nil then
00539 begin
00540 case SQLVarHolder.TypeCode of
00541 SQLT_DAT:
00542 Result := OraDateToDateTime(SQLVarHolder.Data);
00543 SQLT_TIMESTAMP:
00544 begin
00545 Connection := GetStatement.GetConnection as IZOracleConnection;
00546 if SQLVarHolder.ColType in [stDate, stTimestamp] then
00547 begin
00548 Status := FPlainDriver.DateTimeGetDate(
00549 Connection.GetConnectionHandle,
00550 FErrorHandle, PPOCIDescriptor(SQLVarHolder.Data)^,
00551 Year, Month, Day);
00552 // CheckOracleError(FPlainDriver, FErrorHandle, Status, lcOther, '');
00553 if Status = OCI_SUCCESS then
00554 Result := EncodeDate(Year, Month, Day)
00555 else Result := 0;
00556 end else
00557 Result := 0;
00558 if SQLVarHolder.ColType in [stTime, stTimestamp] then
00559 begin
00560 Status := FPlainDriver.DateTimeGetTime(
00561 Connection.GetConnectionHandle,
00562 FErrorHandle, PPOCIDescriptor(SQLVarHolder.Data)^,
00563 Hour, Minute, Second, Millis);
00564 // CheckOracleError(FPlainDriver, FErrorHandle, Status, lcOther, '');
00565 if Status = OCI_SUCCESS then
00566 begin
00567 if Result >= 0 then
00568 begin
00569 Result := Result + EncodeTime(
00570 Hour, Minute, Second, Millis);
00571 end
00572 else
00573 begin
00574 Result := Result - EncodeTime(
00575 Hour, Minute, Second, Millis);
00576 end;
00577 end;
00578 end;
00579 end;
00580 else
00581 begin
00582 Result := AnsiSQLDateToDateTime(
00583 GetAsStringValue(ColumnIndex, SQLVarHolder));
00584 end;
00585 end;
00586 end else
00587 Result := 0;
00588 end;
00589
00590 {**
00591 Gets the value of the designated column in the current row
00592 of this <code>ResultSet</code> object as
00593 a <code>String</code> in the Java programming language.
00594
00595 @param columnIndex the first column is 1, the second is 2, ...
00596 @return the column value; if the value is SQL <code>NULL</code>, the
00597 value returned is <code>null</code>
00598 }
00599 function TZOracleResultSet.GetString(ColumnIndex: Integer): string;
00600 begin
00601 {$IFNDEF DISABLE_CHECKING}
00602 CheckColumnConvertion(ColumnIndex, stString);
00603 {$ENDIF}
00604 Result := GetAsStringValue(ColumnIndex, nil);
00605 end;
00606
00607 {**
00608 Gets the value of the designated column in the current row
00609 of this <code>ResultSet</code> object as
00610 a <code>boolean</code> in the Java programming language.
00611
00612 @param columnIndex the first column is 1, the second is 2, ...
00613 @return the column value; if the value is SQL <code>NULL</code>, the
00614 value returned is <code>false</code>
00615 }
00616 function TZOracleResultSet.GetBoolean(ColumnIndex: Integer): Boolean;
00617 var
00618 Temp: string;
00619 begin
00620 {$IFNDEF DISABLE_CHECKING}
00621 CheckColumnConvertion(ColumnIndex, stBoolean);
00622 {$ENDIF}
00623 Temp := GetAsStringValue(ColumnIndex, nil);
00624 Result := (StrToIntDef(Temp, 0) <> 0) or StrToBoolEx(Temp);
00625 end;
00626
00627 {**
00628 Gets the value of the designated column in the current row
00629 of this <code>ResultSet</code> object as
00630 a <code>byte</code> in the Java programming language.
00631
00632 @param columnIndex the first column is 1, the second is 2, ...
00633 @return the column value; if the value is SQL <code>NULL</code>, the
00634 value returned is <code>0</code>
00635 }
00636 function TZOracleResultSet.GetByte(ColumnIndex: Integer): ShortInt;
00637 begin
00638 {$IFNDEF DISABLE_CHECKING}
00639 CheckColumnConvertion(ColumnIndex, stByte);
00640 {$ENDIF}
00641 Result := ShortInt(GetAsLongIntValue(ColumnIndex, nil));
00642 end;
00643
00644 {**
00645 Gets the value of the designated column in the current row
00646 of this <code>ResultSet</code> object as
00647 a <code>short</code> in the Java programming language.
00648
00649 @param columnIndex the first column is 1, the second is 2, ...
00650 @return the column value; if the value is SQL <code>NULL</code>, the
00651 value returned is <code>0</code>
00652 }
00653 function TZOracleResultSet.GetShort(ColumnIndex: Integer): SmallInt;
00654 begin
00655 {$IFNDEF DISABLE_CHECKING}
00656 CheckColumnConvertion(ColumnIndex, stShort);
00657 {$ENDIF}
00658 Result := SmallInt(GetAsLongIntValue(ColumnIndex, nil));
00659 end;
00660
00661 {**
00662 Gets the value of the designated column in the current row
00663 of this <code>ResultSet</code> object as
00664 an <code>int</code> in the Java programming language.
00665
00666 @param columnIndex the first column is 1, the second is 2, ...
00667 @return the column value; if the value is SQL <code>NULL</code>, the
00668 value returned is <code>0</code>
00669 }
00670 function TZOracleResultSet.GetInt(ColumnIndex: Integer): Integer;
00671 begin
00672 {$IFNDEF DISABLE_CHECKING}
00673 CheckColumnConvertion(ColumnIndex, stInteger);
00674 {$ENDIF}
00675 Result := Integer(GetAsLongIntValue(ColumnIndex, nil));
00676 end;
00677
00678 {**
00679 Gets the value of the designated column in the current row
00680 of this <code>ResultSet</code> object as
00681 a <code>long</code> in the Java programming language.
00682
00683 @param columnIndex the first column is 1, the second is 2, ...
00684 @return the column value; if the value is SQL <code>NULL</code>, the
00685 value returned is <code>0</code>
00686 }
00687 function TZOracleResultSet.GetLong(ColumnIndex: Integer): Int64;
00688 begin
00689 {$IFNDEF DISABLE_CHECKING}
00690 CheckColumnConvertion(ColumnIndex, stLong);
00691 {$ENDIF}
00692 Result := GetAsLongIntValue(ColumnIndex, nil);
00693 end;
00694
00695 {**
00696 Gets the value of the designated column in the current row
00697 of this <code>ResultSet</code> object as
00698 a <code>float</code> in the Java programming language.
00699
00700 @param columnIndex the first column is 1, the second is 2, ...
00701 @return the column value; if the value is SQL <code>NULL</code>, the
00702 value returned is <code>0</code>
00703 }
00704 function TZOracleResultSet.GetFloat(ColumnIndex: Integer): Single;
00705 begin
00706 {$IFNDEF DISABLE_CHECKING}
00707 CheckColumnConvertion(ColumnIndex, stFloat);
00708 {$ENDIF}
00709 Result := GetAsDoubleValue(ColumnIndex, nil);
00710 end;
00711
00712 {**
00713 Gets the value of the designated column in the current row
00714 of this <code>ResultSet</code> object as
00715 a <code>double</code> in the Java programming language.
00716
00717 @param columnIndex the first column is 1, the second is 2, ...
00718 @return the column value; if the value is SQL <code>NULL</code>, the
00719 value returned is <code>0</code>
00720 }
00721 function TZOracleResultSet.GetDouble(ColumnIndex: Integer): Double;
00722 begin
00723 {$IFNDEF DISABLE_CHECKING}
00724 CheckColumnConvertion(ColumnIndex, stDouble);
00725 {$ENDIF}
00726 Result := GetAsDoubleValue(ColumnIndex, nil);
00727 end;
00728
00729 {**
00730 Gets the value of the designated column in the current row
00731 of this <code>ResultSet</code> object as
00732 a <code>java.sql.BigDecimal</code> in the Java programming language.
00733
00734 @param columnIndex the first column is 1, the second is 2, ...
00735 @param scale the number of digits to the right of the decimal point
00736 @return the column value; if the value is SQL <code>NULL</code>, the
00737 value returned is <code>null</code>
00738 }
00739 function TZOracleResultSet.GetBigDecimal(ColumnIndex: Integer): Extended;
00740 begin
00741 {$IFNDEF DISABLE_CHECKING}
00742 CheckColumnConvertion(ColumnIndex, stBigDecimal);
00743 {$ENDIF}
00744 Result := GetAsDoubleValue(ColumnIndex, nil);
00745 end;
00746
00747 {**
00748 Gets the value of the designated column in the current row
00749 of this <code>ResultSet</code> object as
00750 a <code>byte</code> array in the Java programming language.
00751 The bytes represent the raw values returned by the driver.
00752
00753 @param columnIndex the first column is 1, the second is 2, ...
00754 @return the column value; if the value is SQL <code>NULL</code>, the
00755 value returned is <code>null</code>
00756 }
00757 function TZOracleResultSet.GetBytes(ColumnIndex: Integer): TByteDynArray;
00758 begin
00759 {$IFNDEF DISABLE_CHECKING}
00760 CheckColumnConvertion(ColumnIndex, stBytes);
00761 {$ENDIF}
00762 Result := StrToBytes(GetAsStringValue(ColumnIndex, nil));
00763 end;
00764
00765 {**
00766 Gets the value of the designated column in the current row
00767 of this <code>ResultSet</code> object as
00768 a <code>java.sql.Date</code> object in the Java programming language.
00769
00770 @param columnIndex the first column is 1, the second is 2, ...
00771 @return the column value; if the value is SQL <code>NULL</code>, the
00772 value returned is <code>null</code>
00773 }
00774 function TZOracleResultSet.GetDate(ColumnIndex: Integer): TDateTime;
00775 begin
00776 {$IFNDEF DISABLE_CHECKING}
00777 CheckColumnConvertion(ColumnIndex, stDate);
00778 {$ENDIF}
00779 Result := Trunc(GetAsDateTimeValue(ColumnIndex, nil));
00780 end;
00781
00782 {**
00783 Gets the value of the designated column in the current row
00784 of this <code>ResultSet</code> object as
00785 a <code>java.sql.Time</code> object in the Java programming language.
00786
00787 @param columnIndex the first column is 1, the second is 2, ...
00788 @return the column value; if the value is SQL <code>NULL</code>, the
00789 value returned is <code>null</code>
00790 }
00791 function TZOracleResultSet.GetTime(ColumnIndex: Integer): TDateTime;
00792 begin
00793 {$IFNDEF DISABLE_CHECKING}
00794 CheckColumnConvertion(ColumnIndex, stTime);
00795 {$ENDIF}
00796 Result := Frac(GetAsDateTimeValue(ColumnIndex, nil));
00797 end;
00798
00799 {**
00800 Gets the value of the designated column in the current row
00801 of this <code>ResultSet</code> object as
00802 a <code>java.sql.Timestamp</code> object in the Java programming language.
00803
00804 @param columnIndex the first column is 1, the second is 2, ...
00805 @return the column value; if the value is SQL <code>NULL</code>, the
00806 value returned is <code>null</code>
00807 @exception SQLException if a database access error occurs
00808 }
00809 function TZOracleResultSet.GetTimestamp(ColumnIndex: Integer): TDateTime;
00810 begin
00811 {$IFNDEF DISABLE_CHECKING}
00812 CheckColumnConvertion(ColumnIndex, stTimestamp);
00813 {$ENDIF}
00814 Result := GetAsDateTimeValue(ColumnIndex, nil);
00815 end;
00816
00817 {**
00818 Returns the value of the designated column in the current row
00819 of this <code>ResultSet</code> object as a <code>Blob</code> object
00820 in the Java programming language.
00821
00822 @param ColumnIndex the first column is 1, the second is 2, ...
00823 @return a <code>Blob</code> object representing the SQL <code>BLOB</code> value in
00824 the specified column
00825 }
00826 function TZOracleResultSet.GetBlob(ColumnIndex: Integer): IZBlob;
00827 var
00828 Connection: IZOracleConnection;
00829 CurrentVar: PZSQLVar;
00830 LobLocator: POCILobLocator;
00831 Stream: TStream;
00832 begin
00833 {$IFNDEF DISABLE_CHECKING}
00834 CheckBlobColumn(ColumnIndex);
00835 {$ENDIF}
00836
00837 GetSQLVarHolder(ColumnIndex);
00838 CurrentVar := @FOutVars.Variables[ColumnIndex];
00839 if CurrentVar.TypeCode in [SQLT_BLOB, SQLT_CLOB] then
00840 begin
00841 if CurrentVar.Indicator >= 0 then
00842 LobLocator := PPOCIDescriptor(CurrentVar.Data)^
00843 else LobLocator := nil;
00844
00845 Connection := GetStatement.GetConnection as IZOracleConnection;
00846 Result := TZOracleBlob.Create(FPlainDriver, nil, 0, Connection, LobLocator,
00847 CurrentVar.ColType);
00848 end
00849 else
00850 begin
00851 if CurrentVar.Indicator >= 0 then
00852 begin
00853 Stream := nil;
00854 try
00855 Stream := TStringStream.Create(
00856 GetAsStringValue(ColumnIndex, CurrentVar));
00857 Result := TZAbstractBlob.CreateWithStream(Stream);
00858 finally
00859 if Assigned(Stream) then
00860 Stream.Free;
00861 end;
00862 end else
00863 Result := TZAbstractBlob.CreateWithStream(nil);
00864 end;
00865 end;
00866
00867 {**
00868 Moves the cursor down one row from its current position.
00869 A <code>ResultSet</code> cursor is initially positioned
00870 before the first row; the first call to the method
00871 <code>next</code> makes the first row the current row; the
00872 second call makes the second row the current row, and so on.
00873
00874 <P>If an input stream is open for the current row, a call
00875 to the method <code>next</code> will
00876 implicitly close it. A <code>ResultSet</code> object's
00877 warning chain is cleared when a new row is read.
00878
00879 @return <code>true</code> if the new current row is valid;
00880 <code>false</code> if there are no more rows
00881 }
00882 function TZOracleResultSet.Next: Boolean;
00883 var
00884 Status: Integer;
00885 Connection: IZOracleConnection;
00886 begin
00887 { Checks for maximum row. }
00888 Result := False;
00889 if (RowNo > LastRowNo) or ((MaxRows > 0) and (RowNo >= MaxRows)) then
00890 Exit;
00891
00892 if RowNo = 0 then
00893 begin
00894 Connection := GetStatement.GetConnection as IZOracleConnection;
00895 Status := FPlainDriver.StmtExecute(Connection.GetContextHandle, FStmtHandle,
00896 FErrorHandle, 1, 0, nil, nil, OCI_DEFAULT);
00897 end
00898 else
00899 begin
00900 Status := FPlainDriver.StmtFetch(FStmtHandle, FErrorHandle,
00901 1, OCI_FETCH_NEXT, OCI_DEFAULT);
00902 end;
00903 if not (Status in [OCI_SUCCESS, OCI_NO_DATA]) then
00904 CheckOracleError(FPlainDriver, FErrorHandle, Status, lcOther, 'FETCH ROW');
00905
00906 if Status = OCI_SUCCESS then
00907 begin
00908 RowNo := RowNo + 1;
00909 if LastRowNo < RowNo then
00910 LastRowNo := RowNo;
00911 Result := True;
00912 end
00913 else
00914 begin
00915 if RowNo <= LastRowNo then
00916 RowNo := LastRowNo + 1;
00917 Result := False;
00918 end;
00919 end;
00920
00921 { TZOracleBlob }
00922
00923 {**
00924 Constructs this class and assignes the main properties.
00925 @param PlainDriver a Oracle plain driver.
00926 @param Data a pointer to the blobdata.
00927 @param Size the size of the blobdata.
00928 @param Handle a Oracle connection reference.
00929 @param LobLocator an Oracle lob locator reference.
00930 @param BlobType a blob type.
00931 }
00932 constructor TZOracleBlob.Create(PlainDriver: IZOraclePlainDriver;
00933 Data: Pointer; Size: Integer; Handle: IZConnection;
00934 LobLocator: POCILobLocator; BlobType: TZSQLType);
00935 begin
00936 inherited CreateWithData(Data, Size);
00937 FHandle := Handle;
00938 FLobLocator := LobLocator;
00939 FPlainDriver := PlainDriver;
00940 FTemporary := False;
00941 FBlobType := BlobType;
00942 end;
00943
00944 {**
00945 Destroys this object and cleanups the memory.
00946 }
00947 destructor TZOracleBlob.Destroy;
00948 var
00949 Connection: IZOracleConnection;
00950 begin
00951 if FTemporary then
00952 begin
00953 Connection := FHandle as IZOracleConnection;
00954 FPlainDriver.LobFreeTemporary(Connection.GetContextHandle,
00955 Connection.GetErrorHandle, FLobLocator);
00956 end;
00957
00958 inherited Destroy;
00959 end;
00960
00961 {**
00962 Gets the lob locator reference.
00963 @return the lob locator reference.
00964 }
00965 function TZOracleBlob.GetLobLocator: POCILobLocator;
00966 begin
00967 Result := FLobLocator;
00968 end;
00969
00970 {**
00971 Creates a temporary blob.
00972 }
00973 procedure TZOracleBlob.CreateBlob;
00974 var
00975 Status: Integer;
00976 Connection: IZOracleConnection;
00977 TempBlobType: ub1;
00978 begin
00979 Connection := FHandle as IZOracleConnection;
00980
00981 if FBlobType = stBinaryStream then
00982 TempBlobType := OCI_TEMP_BLOB
00983 else TempBlobType := OCI_TEMP_CLOB;
00984
00985 Status := FPlainDriver.LobCreateTemporary(Connection.GetContextHandle,
00986 Connection.GetErrorHandle, FLobLocator, OCI_DEFAULT, OCI_DEFAULT,
00987 TempBlobType, False, OCI_DURATION_DEFAULT);
00988 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
00989 Status, lcOther, 'Create Large Object');
00990
00991 FTemporary := True;
00992 end;
00993
00994 {**
00995 Reads the blob by the blob handle.
00996 }
00997 procedure TZOracleBlob.ReadBlob;
00998 var
00999 Status: Integer;
01000 Buffer: array[0..1024] of Char;
01001 ReadNum, Offset: ub4;
01002 ReadStream: TMemoryStream;
01003 Connection: IZOracleConnection;
01004 begin
01005 if not Updated and (FLobLocator <> nil)
01006 and (BlobData = nil) and (not FTemporary) then
01007 begin
01008 Connection := FHandle as IZOracleConnection;
01009
01010 { Opens a large object or file for read. }
01011 Status := FPlainDriver.LobOpen(Connection.GetContextHandle,
01012 Connection.GetErrorHandle, FLobLocator, OCI_LOB_READONLY);
01013 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01014 Status, lcOther, 'Open Large Object');
01015
01016 { Reads data in chunks. }
01017 ReadStream := TMemoryStream.Create;
01018 Offset := 0;
01019 repeat
01020 ReadNum := 1024;
01021 Status := FPlainDriver.LobRead(Connection.GetContextHandle,
01022 Connection.GetErrorHandle, FLobLocator, ReadNum, Offset + 1,
01023 @Buffer, 1024, nil, nil, 0, SQLCS_IMPLICIT);
01024 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01025 Status, lcOther, 'Read Large Object');
01026 if ReadNum > 0 then
01027 begin
01028 ReadStream.SetSize(ReadStream.Size + ReadNum);
01029 ReadStream.Write(Buffer, ReadNum);
01030 Inc(Offset, 1024);
01031 end;
01032 until ReadNum < 1024;
01033
01034 { Closes large object or file. }
01035 Status := FPlainDriver.LobClose(Connection.GetContextHandle,
01036 Connection.GetErrorHandle, FLobLocator);
01037 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01038 Status, lcOther, 'Close Large Object');
01039
01040 { Assigns a retrieved data stream. }
01041 ReadStream.Position := 0;
01042 SetStream(ReadStream);
01043 ReadStream.Free;
01044 end;
01045 end;
01046
01047 {**
01048 Writes the blob by the blob handle.
01049 }
01050 procedure TZOracleBlob.WriteBlob;
01051 var
01052 Status: Integer;
01053 Connection: IZOracleConnection;
01054 ContentSize: ub4;
01055 begin
01056 Connection := FHandle as IZOracleConnection;
01057
01058 { Opens a large object or file for read. }
01059 Status := FPlainDriver.LobOpen(Connection.GetContextHandle,
01060 Connection.GetErrorHandle, FLobLocator, OCI_LOB_READWRITE);
01061 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01062 Status, lcOther, 'Open Large Object');
01063
01064 { Checks for empty blob.}
01065 { This test doesn't use IsEmpty because that function does allow for zero length blobs}
01066 if (BlobSize > 0) then
01067 begin
01068 ContentSize := BlobSize;
01069 Status := FPlainDriver.LobWrite(Connection.GetContextHandle,
01070 Connection.GetErrorHandle, FLobLocator, ContentSize, 1,
01071 BlobData, BlobSize, OCI_ONE_PIECE, nil, nil, 0, SQLCS_IMPLICIT);
01072 end
01073 else
01074 begin
01075 Status := FPlainDriver.LobTrim(Connection.GetContextHandle,
01076 Connection.GetErrorHandle, FLobLocator, 0);
01077 end;
01078 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01079 Status, lcOther, 'Write Large Object');
01080
01081 { Closes large object or file. }
01082 Status := FPlainDriver.LobClose(Connection.GetContextHandle,
01083 Connection.GetErrorHandle, FLobLocator);
01084 CheckOracleError(FPlainDriver, Connection.GetErrorHandle,
01085 Status, lcOther, 'Close Large Object');
01086 end;
01087
01088 {**
01089 Checks if this blob has an empty content.
01090 @return <code>True</code> if this blob is empty.
01091 }
01092 function TZOracleBlob.IsEmpty: Boolean;
01093 begin
01094 ReadBlob;
01095 Result := inherited IsEmpty;
01096 end;
01097
01098 {**
01099 Clones this blob object.
01100 @return a clonned blob object.
01101 }
01102 function TZOracleBlob.Clone: IZBlob;
01103 begin
01104 Result := TZOracleBlob.Create(FPlainDriver, BlobData, BlobSize,
01105 FHandle, FLobLocator, FBlobType);
01106 end;
01107
01108 {**
01109 Gets the associated stream object.
01110 @return an associated or newly created stream object.
01111 }
01112 function TZOracleBlob.GetStream: TStream;
01113 begin
01114 ReadBlob;
01115 Result := inherited GetStream;
01116 end;
01117
01118 end.