00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { Interbase Database Connectivity Classes }
00005 { }
00006 { Originally written by Sergey Merkuriev }
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 ZDbcASAStatement;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses Classes, SysUtils, ZDbcIntfs, ZDbcStatement, ZDbcASA, ZDbcASAUtils,
00061 ZDbcASAResultSet, ZPlainASADriver, ZCompatibility, ZDbcLogging, ZVariant,
00062 ZMessages;
00063
00064 type
00065
00066 {** Implements Generic ASA Statement. }
00067 TZASAStatement = class(TZAbstractStatement)
00068 private
00069 FCachedBlob: Boolean;
00070 FStmtNum: SmallInt;
00071 FASAConnection: IZASAConnection;
00072 FSQLData: IZASASQLDA;
00073 FMoreResults: Boolean;
00074 public
00075 constructor Create(Connection: IZConnection; Info: TStrings);
00076 destructor Destroy; override;
00077
00078 procedure Close; override;
00079 procedure Cancel; override;
00080 function GetWarnings: EZSQLWarning; override;
00081 procedure ClearWarnings; override;
00082 function GetMoreResults: Boolean; override;
00083 function ExecuteQuery(const SQL: string): IZResultSet; override;
00084 function ExecuteUpdate(const SQL: string): Integer; override;
00085 function Execute(const SQL: string): Boolean; override;
00086 end;
00087
00088 {** Implements Prepared SQL Statement. }
00089 TZASAPreparedStatement = class(TZAbstractPreparedStatement)
00090 private
00091 FCachedBlob: boolean;
00092 FStmtNum: SmallInt;
00093 FASAConnection: IZASAConnection;
00094 FParamSQLData: IZASASQLDA;
00095 FSQLData: IZASASQLDA;
00096 FMoreResults: Boolean;
00097 FPrepared: Boolean;
00098 public
00099 constructor Create(Connection: IZConnection; const SQL: string; Info: TStrings);
00100 destructor Destroy; override;
00101
00102 procedure Close; override;
00103 procedure Cancel; override;
00104 function GetWarnings: EZSQLWarning; override;
00105 procedure ClearWarnings; override;
00106 function GetMoreResults: Boolean; override;
00107 function ExecuteQuery(const SQL: string): IZResultSet; override;
00108 function ExecuteUpdate(const SQL: string): Integer; override;
00109 function Execute(const SQL: string): Boolean; override;
00110
00111 function ExecuteQueryPrepared: IZResultSet; override;
00112 function ExecuteUpdatePrepared: Integer; override;
00113 function ExecutePrepared: Boolean; override;
00114 end;
00115
00116 TZASACallableStatement = class(TZAbstractCallableStatement)
00117 private
00118 FCachedBlob: boolean;
00119 FStmtNum: SmallInt;
00120 FASAConnection: IZASAConnection;
00121 FParamSQLData: IZASASQLDA;
00122 FSQLData: IZASASQLDA;
00123 FMoreResults: Boolean;
00124 FPrepared: Boolean;
00125 protected
00126 procedure FetchOutParams( Value: IZASASQLDA);
00127 function GetProcedureSQL: String;
00128 procedure TrimInParameters;
00129 public
00130 constructor Create(Connection: IZConnection; const SQL: string; Info: TStrings);
00131 destructor Destroy; override;
00132
00133 procedure Close; override;
00134 procedure Cancel; override;
00135 function GetWarnings: EZSQLWarning; override;
00136 procedure ClearWarnings; override;
00137 function GetMoreResults: Boolean; override;
00138 function ExecuteQuery(const SQL: string): IZResultSet; override;
00139 function ExecuteUpdate(const SQL: string): Integer; override;
00140 function Execute(const SQL: string): Boolean; override;
00141
00142 function ExecuteQueryPrepared: IZResultSet; override;
00143 function ExecuteUpdatePrepared: Integer; override;
00144 function ExecutePrepared: Boolean; override;
00145 end;
00146
00147 implementation
00148
00149 uses ZSysUtils, ZDbcUtils;
00150
00151 { TZASAStatement }
00152
00153 {**
00154 Constructs this object and assignes the main properties.
00155 @param Connection a database connection object.
00156 @param Handle a connection handle pointer.
00157 @param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
00158 @param Info a statement parameters.
00159 }
00160 constructor TZASAStatement.Create(Connection: IZConnection;
00161 Info: TStrings);
00162 begin
00163 inherited Create(Connection, Info);
00164
00165 FASAConnection := Connection as IZASAConnection;
00166 FetchSize := BlockSize;
00167 ResultSetConcurrency := rcUpdatable;
00168 ResultSetType := rtScrollSensitive;
00169 FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
00170 CursorName := RandomString(12);
00171 FSQLData := TZASASQLDA.Create( FASAConnection.GetPlainDriver,
00172 FASAConnection.GetDBHandle, CursorName);
00173 end;
00174
00175 destructor TZASAStatement.Destroy;
00176 begin
00177 FSQLData := nil;
00178 inherited;
00179 end;
00180
00181 procedure TZASAStatement.Close;
00182 begin
00183 if not Closed then
00184 begin
00185 FASAConnection.GetPlainDriver.db_close( FASAConnection.GetDBHandle,
00186 PChar( CursorName));
00187 Closed := false;
00188 end;
00189 if FStmtNum <> 0 then
00190 begin
00191 FASAConnection.GetPlainDriver.db_dropstmt( FASAConnection.GetDBHandle, nil,
00192 nil, @FStmtNum);
00193 FStmtNum := 0;
00194 end;
00195 inherited;
00196 end;
00197
00198 procedure TZASAStatement.Cancel;
00199 begin
00200 with FASAConnection do
00201 begin
00202 GetPlainDriver.db_cancel_request( GetDBHandle);
00203 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute);
00204 end;
00205 end;
00206
00207 function TZASAStatement.GetWarnings: EZSQLWarning;
00208 begin
00209 Result := inherited GetWarnings;
00210 end;
00211
00212 procedure TZASAStatement.ClearWarnings;
00213 begin
00214 inherited;
00215 end;
00216
00217 function TZASAStatement.GetMoreResults: Boolean;
00218 var
00219 SQLData: IZASASQLDA;
00220 begin
00221 Result := FMoreResults;
00222 if FMoreResults then
00223 begin
00224 with FASAConnection do
00225 begin
00226 GetPlainDriver.db_resume( GetDBHandle, PChar( CursorName));
00227 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute);
00228 if GetDBHandle.sqlcode = SQLE_PROCEDURE_COMPLETE then
00229 Result := false
00230 else begin
00231 SQLData := TZASAResultSet( LastResultSet).SQLData;
00232 DescribeCursor( FASAConnection, TZASASQLDA( SQLData), CursorName, '');
00233 end;
00234 end;
00235 end;
00236 end;
00237
00238 {**
00239 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00240 @param sql typically this is a static SQL <code>SELECT</code> statement
00241 @return a <code>ResultSet</code> object that contains the data produced by the
00242 given query; never <code>null</code>
00243 }
00244 {$HINTS OFF}
00245 function TZASAStatement.ExecuteQuery(const SQL: string): IZResultSet;
00246 var
00247 Cursor: string;
00248 CursorOptions: SmallInt;
00249 begin
00250 Close;
00251
00252 with FASAConnection do
00253 begin
00254 try
00255 GetPlainDriver.db_prepare_describe( GetDBHandle, nil, @FStmtNum,
00256 PChar( SQL), FSQLData.GetData, SQL_PREPARE_DESCRIBE_STMTNUM +
00257 SQL_PREPARE_DESCRIBE_OUTPUT + SQL_PREPARE_DESCRIBE_VARRESULT, 0);
00258 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00259
00260 FMoreResults := GetDBHandle.sqlerrd[2] = 0;
00261 if not FMoreResults then
00262 begin
00263 if FSQLData.GetData^.sqld <= 0 then
00264 raise EZSQLException.Create( SCanNotRetrieveResultSetData)
00265 else if ( FSQLData.GetData^.sqld > FSQLData.GetData^.sqln) then
00266 begin
00267 FSQLData.AllocateSQLDA( FSQLData.GetData^.sqld);
00268 GetPlainDriver.db_describe( GetDBHandle, nil, @FStmtNum,
00269 FSQLData.GetData, SQL_DESCRIBE_OUTPUT);
00270 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute,
00271 SQL);
00272 end;
00273 FSQLData.InitFields;
00274 end;
00275 if ResultSetConcurrency = rcUpdatable then
00276 CursorOptions := CUR_OPEN_DECLARE + CUR_UPDATE
00277 else
00278 CursorOptions := CUR_OPEN_DECLARE + CUR_READONLY;
00279 if ResultSetType = rtScrollInsensitive then
00280 CursorOptions := CursorOptions + CUR_INSENSITIVE;
00281 Cursor := CursorName;
00282 GetPlainDriver.db_open( GetDBHandle, PChar( Cursor), nil, @FStmtNum,
00283 nil, FetchSize, 0, CursorOptions);
00284 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute,
00285 SQL);
00286 Closed := false;
00287 if FMoreResults then
00288 DescribeCursor( FASAConnection, FSQLData, Cursor, SQL);
00289
00290 LastUpdateCount := -1;
00291 Result := GetCachedResultSet( SQL, Self,
00292 TZASAResultSet.Create( Self, SQL, FStmtNum, Cursor, FSQLData, nil,
00293 FCachedBlob));
00294
00295 { Logging SQL Command }
00296 DriverManager.LogMessage( lcExecute, GetPlainDriver.GetProtocol, SQL);
00297 except
00298 on E: Exception do
00299 begin
00300 Self.Close;
00301 raise;
00302 end;
00303 end;
00304 end;
00305 end;
00306 {$HINTS OFF}
00307
00308 {**
00309 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00310 <code>DELETE</code> statement. In addition,
00311 SQL statements that return nothing, such as SQL DDL statements,
00312 can be executed.
00313
00314 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00315 <code>DELETE</code> statement or an SQL statement that returns nothing
00316 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00317 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00318 }
00319 {$HINTS OFF}
00320 function TZASAStatement.ExecuteUpdate(const SQL: string): Integer;
00321 begin
00322 Close;
00323 Result := -1;
00324 with FASAConnection do
00325 begin
00326
00327 GetPlainDriver.db_execute_imm( GetDBHandle, PChar( SQL));
00328 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00329
00330 Result := GetDBHandle.sqlErrd[2];
00331 LastUpdateCount := Result;
00332
00333 { Logging SQL Command }
00334 DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
00335 end;
00336 end;
00337 {$HINTS ON}
00338
00339 {**
00340 Executes an SQL statement that may return multiple results.
00341 Under some (uncommon) situations a single SQL statement may return
00342 multiple result sets and/or update counts. Normally you can ignore
00343 this unless you are (1) executing a stored procedure that you know may
00344 return multiple results or (2) you are dynamically executing an
00345 unknown SQL string. The methods <code>execute</code>,
00346 <code>getMoreResults</code>, <code>getResultSet</code>,
00347 and <code>getUpdateCount</code> let you navigate through multiple results.
00348
00349 The <code>execute</code> method executes an SQL statement and indicates the
00350 form of the first result. You can then use the methods
00351 <code>getResultSet</code> or <code>getUpdateCount</code>
00352 to retrieve the result, and <code>getMoreResults</code> to
00353 move to any subsequent result(s).
00354
00355 @param sql any SQL statement
00356 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00357 <code>false</code> if it is an update count or there are no more results
00358 @see #getResultSet
00359 @see #getUpdateCount
00360 @see #getMoreResults
00361 }
00362 function TZASAStatement.Execute(const SQL: string): Boolean;
00363 begin
00364 try
00365 LastResultSet := ExecuteQuery( SQL);
00366 Result := true;
00367 except
00368 ExecuteUpdate( SQL);
00369 Result := false;
00370 end;
00371 end;
00372
00373 { TZASAPreparedStatement }
00374
00375 {**
00376 Constructs this object and assignes the main properties.
00377 @param Connection a database connection object.
00378 @param Handle a connection handle pointer.
00379 @param Dialect a dialect Interbase SQL must be 1 or 2 or 3.
00380 @param Info a statement parameters.
00381 }
00382 constructor TZASAPreparedStatement.Create(Connection: IZConnection;
00383 const SQL: string; Info: TStrings);
00384 begin
00385 inherited Create(Connection, SQL, Info);
00386
00387 FASAConnection := Connection as IZASAConnection;
00388 FetchSize := BlockSize;
00389 ResultSetConcurrency := rcUpdatable;
00390 ResultSetType := rtScrollSensitive;
00391 FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
00392 CursorName := RandomString(12);
00393 FParamSQLData := TZASASQLDA.Create( FASAConnection.GetPlainDriver,
00394 FASAConnection.GetDBHandle, CursorName);
00395 FSQLData := TZASASQLDA.Create( FASAConnection.GetPlainDriver,
00396 FASAConnection.GetDBHandle, CursorName);
00397 Prepare( FASAConnection, FSQLData, FParamSQLData, SQL, @FStmtNum, FPrepared,
00398 FMoreResults);
00399 end;
00400
00401 destructor TZASAPreparedStatement.Destroy;
00402 begin
00403 FSQLData := nil;
00404 FParamSQLData := nil;
00405 inherited;
00406 end;
00407
00408 procedure TZASAPreparedStatement.Close;
00409 begin
00410 if not Closed then
00411 begin
00412 FASAConnection.GetPlainDriver.db_close( FASAConnection.GetDBHandle,
00413 PChar( CursorName));
00414 Closed := false;
00415 end;
00416 if FStmtNum <> 0 then
00417 begin
00418 FASAConnection.GetPlainDriver.db_dropstmt( FASAConnection.GetDBHandle, nil,
00419 nil, @FStmtNum);
00420 FStmtNum := 0;
00421 end;
00422 inherited;
00423 end;
00424
00425 procedure TZASAPreparedStatement.Cancel;
00426 begin
00427 with FASAConnection do
00428 begin
00429 GetPlainDriver.db_cancel_request( GetDBHandle);
00430 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00431 end;
00432 end;
00433
00434 function TZASAPreparedStatement.GetWarnings: EZSQLWarning;
00435 begin
00436 Result := inherited GetWarnings;
00437 end;
00438
00439 procedure TZASAPreparedStatement.ClearWarnings;
00440 begin
00441 inherited;
00442 end;
00443
00444 function TZASAPreparedStatement.GetMoreResults: Boolean;
00445 begin
00446 Result := FMoreResults;
00447 if FMoreResults then
00448 begin
00449 with FASAConnection do
00450 begin
00451 GetPlainDriver.db_resume( GetDBHandle, PChar( CursorName));
00452 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute);
00453 if GetDBHandle.sqlcode = SQLE_PROCEDURE_COMPLETE then
00454 Result := false
00455 else
00456 DescribeCursor( FASAConnection, TZASASQLDA( FSQLData), CursorName, '');
00457 end;
00458 end;
00459 end;
00460
00461 {**
00462 Executes an SQL statement that may return multiple results.
00463 Under some (uncommon) situations a single SQL statement may return
00464 multiple result sets and/or update counts. Normally you can ignore
00465 this unless you are (1) executing a stored procedure that you know may
00466 return multiple results or (2) you are dynamically executing an
00467 unknown SQL string. The methods <code>execute</code>,
00468 <code>getMoreResults</code>, <code>getResultSet</code>,
00469 and <code>getUpdateCount</code> let you navigate through multiple results.
00470
00471 The <code>execute</code> method executes an SQL statement and indicates the
00472 form of the first result. You can then use the methods
00473 <code>getResultSet</code> or <code>getUpdateCount</code>
00474 to retrieve the result, and <code>getMoreResults</code> to
00475 move to any subsequent result(s).
00476
00477 @param sql any SQL statement
00478 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00479 <code>false</code> if it is an update count or there are no more results
00480 @see #getResultSet
00481 @see #getUpdateCount
00482 @see #getMoreResults
00483 }
00484
00485 function TZASAPreparedStatement.Execute(const SQL: string): Boolean;
00486 begin
00487 if Self.SQL <> SQL then
00488 begin
00489 Close;
00490 Self.SQL := SQL;
00491 Prepare( FASAConnection, FSQLData, FParamSQLData, SQL, @FStmtNum, FPrepared,
00492 FMoreResults);
00493 end;
00494 Result := ExecutePrepared;
00495 end;
00496
00497 {**
00498 Executes any kind of SQL statement.
00499 Some prepared statements return multiple results; the <code>execute</code>
00500 method handles these complex statements as well as the simpler
00501 form of statements handled by the methods <code>executeQuery</code>
00502 and <code>executeUpdate</code>.
00503 @see Statement#execute
00504 }
00505 function TZASAPreparedStatement.ExecutePrepared: Boolean;
00506 begin
00507 if FMoreResults or ( FSQLData.GetData.sqld > 0) then
00508 begin
00509 LastResultSet := ExecuteQueryPrepared;
00510 Result := true;
00511 end else begin
00512 ExecuteUpdatePrepared;
00513 Result := false;
00514 end;
00515 end;
00516
00517 {**
00518 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00519 @param sql typically this is a static SQL <code>SELECT</code> statement
00520 @return a <code>ResultSet</code> object that contains the data produced by the
00521 given query; never <code>null</code>
00522 }
00523 function TZASAPreparedStatement.ExecuteQuery(const SQL: string): IZResultSet;
00524 begin
00525 if Self.SQL <> SQL then
00526 begin
00527 Close;
00528 Self.SQL := SQL;
00529 Prepare( FASAConnection, FSQLData, FParamSQLData, SQL, @FStmtNum, FPrepared,
00530 FMoreResults);
00531 end;
00532 Result := ExecuteQueryPrepared;
00533 end;
00534
00535 {**
00536 Executes the SQL query in this <code>PreparedStatement</code> object
00537 and returns the result set generated by the query.
00538
00539 @return a <code>ResultSet</code> object that contains the data produced by the
00540 query; never <code>null</code>
00541 }
00542 {$HINTS OFF}
00543 function TZASAPreparedStatement.ExecuteQueryPrepared: IZResultSet;
00544 var
00545 Cursor: string;
00546 CursorOptions: SmallInt;
00547 begin
00548 with FASAConnection do
00549 begin
00550 PrepareParameters( GetPlainDriver, InParamValues, InParamTypes,
00551 InParamCount, FParamSQLData);
00552 if ResultSetConcurrency = rcUpdatable then
00553 CursorOptions := CUR_OPEN_DECLARE + CUR_UPDATE
00554 else
00555 CursorOptions := CUR_OPEN_DECLARE + CUR_READONLY;
00556 if ResultSetType = rtScrollInsensitive then
00557 CursorOptions := CursorOptions + CUR_INSENSITIVE;
00558 Cursor := CursorName;
00559 GetPlainDriver.db_open( GetDBHandle, PChar( Cursor), nil, @FStmtNum,
00560 FParamSQLData.GetData, FetchSize, 0, CursorOptions);
00561 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute,
00562 SQL);
00563 Closed := false;
00564 try
00565 if FMoreResults then
00566 DescribeCursor( FASAConnection, TZASASQLDA( FSQLData), Cursor, '');
00567
00568 LastUpdateCount := -1;
00569 Result := GetCachedResultSet( SQL, Self,
00570 TZASAResultSet.Create( Self, SQL, FStmtNum, Cursor, FSQLData, nil,
00571 FCachedBlob));
00572
00573 { Logging SQL Command }
00574 DriverManager.LogMessage( lcExecute, GetPlainDriver.GetProtocol, SQL);
00575 except
00576 on E: Exception do
00577 begin
00578 Self.Close;
00579 raise;
00580 end;
00581 end;
00582 end;
00583 end;
00584 {$HINTS ON}
00585
00586 {**
00587 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00588 <code>DELETE</code> statement. In addition,
00589 SQL statements that return nothing, such as SQL DDL statements,
00590 can be executed.
00591
00592 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00593 <code>DELETE</code> statement or an SQL statement that returns nothing
00594 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00595 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00596 }
00597 function TZASAPreparedStatement.ExecuteUpdate(const SQL: string): Integer;
00598 begin
00599 if Self.SQL <> SQL then
00600 begin
00601 Close;
00602 Self.SQL := SQL;
00603 Prepare( FASAConnection, FSQLData, FParamSQLData, SQL, @FStmtNum, FPrepared,
00604 FMoreResults);
00605 end;
00606 Result := ExecuteUpdatePrepared;
00607 end;
00608
00609 {**
00610 Executes the SQL INSERT, UPDATE or DELETE statement
00611 in this <code>PreparedStatement</code> object.
00612 In addition,
00613 SQL statements that return nothing, such as SQL DDL statements,
00614 can be executed.
00615
00616 @return either the row count for INSERT, UPDATE or DELETE statements;
00617 or 0 for SQL statements that return nothing
00618 }
00619 {$HINTS OFF}
00620 function TZASAPreparedStatement.ExecuteUpdatePrepared: Integer;
00621 begin
00622 Result := -1;
00623 with FASAConnection do
00624 begin
00625
00626 PrepareParameters( GetPlainDriver, InParamValues, InParamTypes,
00627 InParamCount, FParamSQLData);
00628 GetPlainDriver.db_execute_into( GetDBHandle, nil, nil, @FStmtNum,
00629 FParamSQLData.GetData, nil);
00630 if ( GetDBHandle.SqlCode <> -185) or ( FSQLData.GetData^.sqld = 0) then
00631 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00632
00633 Result := GetDBHandle.sqlErrd[2];
00634 LastUpdateCount := Result;
00635
00636 { Logging SQL Command }
00637 DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
00638 end;
00639 end;
00640 {$HINTS ON}
00641
00642
00643 { TZASACallableStatement }
00644
00645 {**
00646 Constructs this object and assignes the main properties.
00647 @param Connection a database connection object.
00648 @param Handle a connection handle pointer.
00649 @param Info a statement parameters.
00650 }
00651 constructor TZASACallableStatement.Create(Connection: IZConnection;
00652 const SQL: string; Info: TStrings);
00653 begin
00654 inherited Create(Connection, SQL, Info);
00655
00656 FASAConnection := Connection as IZASAConnection;
00657 FetchSize := BlockSize;
00658 ResultSetConcurrency := rcUpdatable;
00659 ResultSetType := rtScrollSensitive;
00660 FCachedBlob := StrToBoolEx(DefineStatementParameter(Self, 'cashedblob', 'true'));
00661 CursorName := RandomString(12);
00662 FParamSQLData := TZASASQLDA.Create( FASAConnection.GetPlainDriver,
00663 FASAConnection.GetDBHandle, CursorName);
00664 FSQLData := TZASASQLDA.Create( FASAConnection.GetPlainDriver,
00665 FASAConnection.GetDBHandle, CursorName);
00666 end;
00667
00668 destructor TZASACallableStatement.Destroy;
00669 begin
00670 FSQLData := nil;
00671 FParamSQLData := nil;
00672 inherited;
00673 end;
00674
00675 procedure TZASACallableStatement.Close;
00676 begin
00677 if not Closed then
00678 begin
00679 FASAConnection.GetPlainDriver.db_close( FASAConnection.GetDBHandle,
00680 PChar( CursorName));
00681 Closed := false;
00682 end;
00683 if FStmtNum <> 0 then
00684 begin
00685 FASAConnection.GetPlainDriver.db_dropstmt( FASAConnection.GetDBHandle, nil,
00686 nil, @FStmtNum);
00687 FStmtNum := 0;
00688 end;
00689 inherited;
00690 end;
00691
00692 procedure TZASACallableStatement.Cancel;
00693 begin
00694 with FASAConnection do
00695 begin
00696 GetPlainDriver.db_cancel_request( GetDBHandle);
00697 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00698 end;
00699 end;
00700
00701 function TZASACallableStatement.GetWarnings: EZSQLWarning;
00702 begin
00703 Result := inherited GetWarnings;
00704 end;
00705
00706 procedure TZASACallableStatement.ClearWarnings;
00707 begin
00708 inherited;
00709 end;
00710
00711 function TZASACallableStatement.GetMoreResults: Boolean;
00712 begin
00713 Result := FMoreResults;
00714 if FMoreResults then
00715 begin
00716 with FASAConnection do
00717 begin
00718 GetPlainDriver.db_resume( GetDBHandle, PChar( CursorName));
00719 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute);
00720 if GetDBHandle.sqlcode = SQLE_PROCEDURE_COMPLETE then
00721 Result := false
00722 else
00723 DescribeCursor( FASAConnection, TZASASQLDA( FSQLData), CursorName, '');
00724 end;
00725 end;
00726 end;
00727
00728 {**
00729 Executes an SQL statement that may return multiple results.
00730 Under some (uncommon) situations a single SQL statement may return
00731 multiple result sets and/or update counts. Normally you can ignore
00732 this unless you are (1) executing a stored procedure that you know may
00733 return multiple results or (2) you are dynamically executing an
00734 unknown SQL string. The methods <code>execute</code>,
00735 <code>getMoreResults</code>, <code>getResultSet</code>,
00736 and <code>getUpdateCount</code> let you navigate through multiple results.
00737
00738 The <code>execute</code> method executes an SQL statement and indicates the
00739 form of the first result. You can then use the methods
00740 <code>getResultSet</code> or <code>getUpdateCount</code>
00741 to retrieve the result, and <code>getMoreResults</code> to
00742 move to any subsequent result(s).
00743
00744 @param sql any SQL statement
00745 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00746 <code>false</code> if it is an update count or there are no more results
00747 @see #getResultSet
00748 @see #getUpdateCount
00749 @see #getMoreResults
00750 }
00751
00752 function TZASACallableStatement.Execute(const SQL: string): Boolean;
00753 var
00754 ProcSQL: string;
00755 begin
00756 TrimInParameters;
00757 ProcSQL := GetProcedureSQL;
00758 if not FPrepared or ( Self.SQL <> ProcSQL) then
00759 begin
00760 Close;
00761 Self.SQL := ProcSQL;
00762 Prepare( FASAConnection, FSQLData, FParamSQLData, Self.SQL, @FStmtNum,
00763 FPrepared, FMoreResults);
00764 end;
00765 Result := ExecutePrepared;
00766 end;
00767
00768 {**
00769 Executes any kind of SQL statement.
00770 Some prepared statements return multiple results; the <code>execute</code>
00771 method handles these complex statements as well as the simpler
00772 form of statements handled by the methods <code>executeQuery</code>
00773 and <code>executeUpdate</code>.
00774 @see Statement#execute
00775 }
00776 function TZASACallableStatement.ExecutePrepared: Boolean;
00777 begin
00778 if not FPrepared then
00779 Result := Execute( SQL)
00780 else begin
00781 if FMoreResults or ( ( FSQLData.GetData.sqld > 0) and
00782 ( FSQLData.GetData.sqlVar[0].sqlInd^ and DT_PROCEDURE_OUT = 0)) then
00783 begin
00784 LastResultSet := ExecuteQueryPrepared;
00785 Result := true;
00786 end else begin
00787 ExecuteUpdatePrepared;
00788 Result := false;
00789 end;
00790 end;
00791 end;
00792
00793 {**
00794 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00795 @param sql typically this is a static SQL <code>SELECT</code> statement
00796 @return a <code>ResultSet</code> object that contains the data produced by the
00797 given query; never <code>null</code>
00798 }
00799 function TZASACallableStatement.ExecuteQuery(
00800 const SQL: string): IZResultSet;
00801 var
00802 ProcSQL: string;
00803 begin
00804 TrimInParameters;
00805 ProcSQL := GetProcedureSQL;
00806 if not FPrepared or ( Self.SQL <> ProcSQL) then
00807 begin
00808 Close;
00809 Self.SQL := ProcSQL;
00810 Prepare( FASAConnection, FSQLData, FParamSQLData, Self.SQL, @FStmtNum,
00811 FPrepared, FMoreResults);
00812 end;
00813 Result := ExecuteQueryPrepared;
00814 end;
00815
00816 {**
00817 Executes the SQL query in this <code>PreparedStatement</code> object
00818 and returns the result set generated by the query.
00819
00820 @return a <code>ResultSet</code> object that contains the data produced by the
00821 query; never <code>null</code>
00822 }
00823 {$HINTS OFF}
00824 function TZASACallableStatement.ExecuteQueryPrepared: IZResultSet;
00825 var
00826 Cursor: string;
00827 CursorOptions: SmallInt;
00828 begin
00829 if not FPrepared then
00830 Result := ExecuteQuery( SQL)
00831 else begin
00832 with FASAConnection do
00833 begin
00834 PrepareParameters( GetPlainDriver, InParamValues, InParamTypes,
00835 InParamCount, FParamSQLData);
00836 if ResultSetConcurrency = rcUpdatable then
00837 CursorOptions := CUR_OPEN_DECLARE + CUR_UPDATE
00838 else
00839 CursorOptions := CUR_OPEN_DECLARE + CUR_READONLY;
00840 if ResultSetType = rtScrollInsensitive then
00841 CursorOptions := CursorOptions + CUR_INSENSITIVE;
00842 Cursor := CursorName;
00843 GetPlainDriver.db_open( GetDBHandle, PChar( Cursor), nil, @FStmtNum,
00844 FParamSQLData.GetData, FetchSize, 0, CursorOptions);
00845 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute,
00846 SQL);
00847 Closed := false;
00848 try
00849 if FMoreResults then
00850 DescribeCursor( FASAConnection, TZASASQLDA( FSQLData), Cursor, SQL);
00851
00852 LastUpdateCount := -1;
00853 Result := GetCachedResultSet( SQL, Self,
00854 TZASAResultSet.Create( Self, SQL, FStmtNum, Cursor, FSQLData, nil,
00855 FCachedBlob));
00856
00857 { Logging SQL Command }
00858 DriverManager.LogMessage( lcExecute, GetPlainDriver.GetProtocol, SQL);
00859 except
00860 on E: Exception do
00861 begin
00862 Self.Close;
00863 raise;
00864 end;
00865 end;
00866 end;
00867 end;
00868 end;
00869 {$HINTS ON}
00870
00871 {**
00872 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00873 <code>DELETE</code> statement. In addition,
00874 SQL statements that return nothing, such as SQL DDL statements,
00875 can be executed.
00876
00877 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00878 <code>DELETE</code> statement or an SQL statement that returns nothing
00879 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00880 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00881 }
00882 function TZASACallableStatement.ExecuteUpdate(const SQL: string): Integer;
00883 var
00884 ProcSQL: string;
00885 begin
00886 TrimInParameters;
00887 ProcSQL := GetProcedureSQL;
00888 if not FPrepared or ( Self.SQL <> ProcSQL) then
00889 begin
00890 Close;
00891 Self.SQL := ProcSQL;
00892 Prepare( FASAConnection, FSQLData, FParamSQLData, Self.SQL, @FStmtNum,
00893 FPrepared, FMoreResults);
00894 end;
00895 Result := ExecuteUpdatePrepared;
00896 end;
00897
00898 {**
00899 Executes the SQL INSERT, UPDATE or DELETE statement
00900 in this <code>PreparedStatement</code> object.
00901 In addition,
00902 SQL statements that return nothing, such as SQL DDL statements,
00903 can be executed.
00904
00905 @return either the row count for INSERT, UPDATE or DELETE statements;
00906 or 0 for SQL statements that return nothing
00907 }
00908 function TZASACallableStatement.ExecuteUpdatePrepared: Integer;
00909 begin
00910 if not FPrepared then
00911 Result := ExecuteUpdate( SQL)
00912 else begin
00913
00914 with FASAConnection do
00915 begin
00916
00917 PrepareParameters( GetPlainDriver, InParamValues, InParamTypes,
00918 InParamCount, FParamSQLData);
00919 GetPlainDriver.db_execute_into( GetDBHandle, nil, nil, @FStmtNum,
00920 FParamSQLData.GetData, FSQLData.GetData);
00921 ZDbcASAUtils.CheckASAError( GetPlainDriver, GetDBHandle, lcExecute, SQL);
00922
00923 Result := GetDBHandle.sqlErrd[2];
00924 LastUpdateCount := Result;
00925 { Fetch data and fill Output params }
00926 FetchOutParams( FSQLData);
00927
00928 { Logging SQL Command }
00929 DriverManager.LogMessage(lcExecute, GetPlainDriver.GetProtocol, SQL);
00930 end;
00931 end;
00932 end;
00933
00934 {**
00935 Set output parameters values from IZResultSQLDA.
00936 @param Value a IZASASQLDA object.
00937 }
00938 procedure TZASACallableStatement.FetchOutParams( Value: IZASASQLDA);
00939 var
00940 I: Integer;
00941 L: LongWord;
00942 Temp: TZVariant;
00943 TempBlob: IZBlob;
00944 P: Pointer;
00945 begin
00946 SetOutParamCount(Value.GetFieldCount);
00947 for I := 0 to Value.GetFieldCount-1 do
00948 begin
00949 if Value.IsNull(I) then
00950 DefVarManager.SetNull(Temp)
00951 else
00952 case Value.GetFieldSqlType(I) of
00953 stBoolean:
00954 DefVarManager.SetAsBoolean(Temp, Value.GetBoolean(I));
00955 stByte:
00956 DefVarManager.SetAsInteger(Temp, Value.GetByte(I));
00957 stShort:
00958 DefVarManager.SetAsInteger(Temp, Value.GetShort(I));
00959 stInteger:
00960 DefVarManager.SetAsInteger(Temp, Value.GetInt(I));
00961 stLong:
00962 DefVarManager.SetAsInteger(Temp, Value.GetLong(I));
00963 stFloat:
00964 DefVarManager.SetAsFloat(Temp, Value.GetFloat(I));
00965 stDouble:
00966 DefVarManager.SetAsFloat(Temp, Value.GetDouble(I));
00967 stBigDecimal:
00968 DefVarManager.SetAsFloat(Temp, Value.GetBigDecimal(I));
00969 stString:
00970 DefVarManager.SetAsString(Temp, Value.GetString(I));
00971 stUnicodeString:
00972 DefVarManager.SetAsUnicodeString(Temp, Value.GetString(I));
00973 stBytes:
00974 DefVarManager.SetAsString( Temp, BytesToStr( Value.GetBytes( I)));
00975 stDate:
00976 DefVarManager.SetAsDateTime(Temp, Value.GetDate(I));
00977 stTime:
00978 DefVarManager.SetAsDateTime(Temp, Value.GetTime(I));
00979 stTimestamp:
00980 DefVarManager.SetAsDateTime(Temp, Value.GetTimestamp(I));
00981 stAsciiStream,
00982 stUnicodeStream,
00983 stBinaryStream:
00984 begin
00985 GetMem( P, PZASABlobStruct( Value.GetData.sqlvar[I].sqlData).untrunc_len);
00986 Value.ReadBlobToMem( I, P, L);
00987 TempBlob := TZASABlob.CreateWithData( P, L);
00988 DefVarManager.SetAsInterface( Temp, TempBlob);
00989 end;
00990 end;
00991 OutParamValues[I] := Temp;
00992 end;
00993 end;
00994
00995 {**
00996 Create sql string for calling stored procedure.
00997 @param SelectProc indicate use <b>EXECUTE PROCEDURE</b> or
00998 <b>SELECT</b> staement
00999 @return a Stored Procedure SQL string
01000 }
01001 function TZASACallableStatement.GetProcedureSql: string;
01002
01003 function GenerateParamsStr(Count: integer): string;
01004 var
01005 I: integer;
01006 begin
01007 for I := 0 to Count - 1 do
01008 begin
01009 if Result <> '' then
01010 Result := Result + ',';
01011 Result := Result + '?';
01012 end;
01013 end;
01014
01015 var
01016 InParams: string;
01017 begin
01018 InParams := GenerateParamsStr( High( InParamValues) + 1);
01019 if InParams <> '' then
01020 InParams := '(' + InParams + ')';
01021 Result := 'call ' + SQL + InParams;
01022 end;
01023
01024 {**
01025 Function remove stUnknown paramters from InParamTypes and InParamValues
01026 }
01027 procedure TZASACallableStatement.TrimInParameters;
01028 var
01029 I: integer;
01030 ParamValues: TZVariantDynArray;
01031 ParamTypes: TZSQLTypeArray;
01032 ParamCount: Integer;
01033 begin
01034 ParamCount := 0;
01035 SetLength(ParamValues, InParamCount);
01036 SetLength(ParamTypes, InParamCount);
01037
01038 for I := 0 to High(InParamTypes) do
01039 begin
01040 if InParamTypes[I] = ZDbcIntfs.stUnknown then
01041 Continue;
01042
01043 ParamTypes[ParamCount] := InParamTypes[I];
01044 ParamValues[ParamCount] := InParamValues[I];
01045 Inc(ParamCount);
01046 end;
01047 if ParamCount = InParamCount then
01048 Exit;
01049 InParamTypes := ParamTypes;
01050 InParamValues := ParamValues;
01051 SetInParamCount(ParamCount);
01052 end;
01053
01054 end.
01055
01056