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