00001 {*********************************************************}
00002 { }
00003 { Zeos Database Objects }
00004 { PostgreSQL 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 ZDbcPostgreSqlStatement;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 Classes, SysUtils, ZSysUtils, ZDbcIntfs, ZDbcStatement, ZDbcLogging,
00062 ZPlainPostgreSqlDriver, ZCompatibility, ZVariant, ZDbcGenericResolver,
00063 ZDbcCachedResultSet, ZDbcPostgreSql;
00064
00065 type
00066
00067 {** Defines a PostgreSQL specific statement. }
00068 IZPostgreSQLStatement = interface(IZStatement)
00069 ['{E4FAFD96-97CC-4247-8ECC-6E0A168FAFE6}']
00070
00071 function IsOidAsBlob: Boolean;
00072 end;
00073
00074 {** Implements Generic PostgreSQL Statement. }
00075 TZPostgreSQLStatement = class(TZAbstractStatement, IZPostgreSQLStatement)
00076 private
00077 FHandle: PZPostgreSQLConnect;
00078 FPlainDriver: IZPostgreSQLPlainDriver;
00079 FOidAsBlob: Boolean;
00080 protected
00081 function CreateResultSet(const SQL: string;
00082 QueryHandle: PZPostgreSQLResult): IZResultSet;
00083 public
00084 constructor Create(PlainDriver: IZPostgreSQLPlainDriver;
00085 Connection: IZConnection; Info: TStrings; Handle: PZPostgreSQLConnect);
00086 destructor Destroy; override;
00087
00088 function ExecuteQuery(const SQL: string): IZResultSet; override;
00089 function ExecuteUpdate(const SQL: string): Integer; override;
00090 function Execute(const SQL: string): Boolean; override;
00091
00092 function IsOidAsBlob: Boolean;
00093 end;
00094
00095 {** Implements Prepared SQL Statement. }
00096 TZPostgreSQLPreparedStatement = class(TZEmulatedPreparedStatement)
00097 private
00098 FHandle: PZPostgreSQLConnect;
00099 FPlainDriver: IZPostgreSQLPlainDriver;
00100 FCharactersetCode : TZPgCharactersetType;
00101 protected
00102 function CreateExecStatement: IZStatement; override;
00103 function PrepareSQLParam(ParamIndex: Integer): string; override;
00104 public
00105 constructor Create(PlainDriver: IZPostgreSQLPlainDriver;
00106 Connection: IZConnection; const SQL: string; Info: TStrings;
00107 Handle: PZPostgreSQLConnect);
00108 end;
00109
00110 {** Implements a specialized cached resolver for PostgreSQL. }
00111 TZPostgreSQLCachedResolver = class(TZGenericCachedResolver, IZCachedResolver)
00112 protected
00113 function CheckKeyColumn(ColumnIndex: Integer): Boolean; override;
00114 end;
00115
00116 implementation
00117
00118 uses
00119 ZMessages, ZDbcPostgreSqlResultSet, ZDbcPostgreSqlUtils;
00120
00121 { TZPostgreSQLStatement }
00122
00123 {**
00124 Constructs this object and assignes the main properties.
00125 @param PlainDriver a PostgreSQL plain driver.
00126 @param Connection a database connection object.
00127 @param Info a statement parameters.
00128 @param Handle a connection handle pointer.
00129 }
00130 constructor TZPostgreSQLStatement.Create(PlainDriver: IZPostgreSQLPlainDriver;
00131 Connection: IZConnection; Info: TStrings; Handle: PZPostgreSQLConnect);
00132 begin
00133 inherited Create(Connection, Info);
00134 FHandle := Handle;
00135 FPlainDriver := PlainDriver;
00136 ResultSetType := rtScrollInsensitive;
00137
00138 { Processes connection properties. }
00139 if Self.Info.Values['oidasblob'] <> '' then
00140 FOidAsBlob := StrToBoolEx(Self.Info.Values['oidasblob'])
00141 else
00142 FOidAsBlob := (Connection as IZPostgreSQLConnection).IsOidAsBlob;
00143 end;
00144
00145 {**
00146 Destroys this object and cleanups the memory.
00147 }
00148 destructor TZPostgreSQLStatement.Destroy;
00149 begin
00150 inherited Destroy;
00151 end;
00152
00153 {**
00154 Checks is oid should be treated as Large Object.
00155 @return <code>True</code> if oid should represent a Large Object.
00156 }
00157 function TZPostgreSQLStatement.IsOidAsBlob: Boolean;
00158 begin
00159 Result := FOidAsBlob;
00160 end;
00161
00162 {**
00163 Creates a result set based on the current settings.
00164 @return a created result set object.
00165 }
00166 function TZPostgreSQLStatement.CreateResultSet(const SQL: string;
00167 QueryHandle: PZPostgreSQLResult): IZResultSet;
00168 var
00169 NativeResultSet: TZPostgreSQLResultSet;
00170 CachedResultSet: TZCachedResultSet;
00171 begin
00172 NativeResultSet := TZPostgreSQLResultSet.Create(FPlainDriver, Self, SQL,
00173 FHandle, QueryHandle);
00174 NativeResultSet.SetConcurrency(rcReadOnly);
00175 if GetResultSetConcurrency = rcUpdatable then
00176 begin
00177 CachedResultSet := TZCachedResultSet.Create(NativeResultSet, SQL, nil);
00178 CachedResultSet.SetConcurrency(rcUpdatable);
00179 CachedResultSet.SetResolver(TZPostgreSQLCachedResolver.Create(
00180 Self, NativeResultSet.GetMetadata));
00181 Result := CachedResultSet;
00182 end else
00183 Result := NativeResultSet;
00184 end;
00185
00186 {**
00187 Executes an SQL statement that returns a single <code>ResultSet</code> object.
00188 @param sql typically this is a static SQL <code>SELECT</code> statement
00189 @return a <code>ResultSet</code> object that contains the data produced by the
00190 given query; never <code>null</code>
00191 }
00192 function TZPostgreSQLStatement.ExecuteQuery(const SQL: string): IZResultSet;
00193 var
00194 QueryHandle: PZPostgreSQLResult;
00195 begin
00196 Result := nil;
00197 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, PChar(SQL));
00198 CheckPostgreSQLError(Connection, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00199 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00200 if QueryHandle <> nil then
00201 Result := CreateResultSet(SQL, QueryHandle)
00202 else Result := nil;
00203 end;
00204
00205 {**
00206 Executes an SQL <code>INSERT</code>, <code>UPDATE</code> or
00207 <code>DELETE</code> statement. In addition,
00208 SQL statements that return nothing, such as SQL DDL statements,
00209 can be executed.
00210
00211 @param sql an SQL <code>INSERT</code>, <code>UPDATE</code> or
00212 <code>DELETE</code> statement or an SQL statement that returns nothing
00213 @return either the row count for <code>INSERT</code>, <code>UPDATE</code>
00214 or <code>DELETE</code> statements, or 0 for SQL statements that return nothing
00215 }
00216 function TZPostgreSQLStatement.ExecuteUpdate(const SQL: string): Integer;
00217 var
00218 QueryHandle: PZPostgreSQLResult;
00219 begin
00220 Result := -1;
00221 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, PChar(SQL));
00222 CheckPostgreSQLError(Connection, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00223 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00224
00225 if QueryHandle <> nil then
00226 begin
00227 Result := StrToIntDef(StrPas(FPlainDriver.GetCommandTuples(QueryHandle)), 0);
00228 FPlainDriver.Clear(QueryHandle);
00229 end;
00230
00231 { Autocommit statement. }
00232 if Connection.GetAutoCommit then
00233 Connection.Commit;
00234 end;
00235
00236 {**
00237 Executes an SQL statement that may return multiple results.
00238 Under some (uncommon) situations a single SQL statement may return
00239 multiple result sets and/or update counts. Normally you can ignore
00240 this unless you are (1) executing a stored procedure that you know may
00241 return multiple results or (2) you are dynamically executing an
00242 unknown SQL string. The methods <code>execute</code>,
00243 <code>getMoreResults</code>, <code>getResultSet</code>,
00244 and <code>getUpdateCount</code> let you navigate through multiple results.
00245
00246 The <code>execute</code> method executes an SQL statement and indicates the
00247 form of the first result. You can then use the methods
00248 <code>getResultSet</code> or <code>getUpdateCount</code>
00249 to retrieve the result, and <code>getMoreResults</code> to
00250 move to any subsequent result(s).
00251
00252 @param sql any SQL statement
00253 @return <code>true</code> if the next result is a <code>ResultSet</code> object;
00254 <code>false</code> if it is an update count or there are no more results
00255 }
00256 function TZPostgreSQLStatement.Execute(const SQL: string): Boolean;
00257 var
00258 QueryHandle: PZPostgreSQLResult;
00259 ResultStatus: TZPostgreSQLExecStatusType;
00260 begin
00261 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, PChar(SQL));
00262 CheckPostgreSQLError(Connection, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00263 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00264
00265 { Process queries with result sets }
00266 ResultStatus := FPlainDriver.GetResultStatus(QueryHandle);
00267 case ResultStatus of
00268 PGRES_TUPLES_OK:
00269 begin
00270 Result := True;
00271 LastResultSet := CreateResultSet(SQL, QueryHandle);
00272 end;
00273 PGRES_COMMAND_OK:
00274 begin
00275 Result := False;
00276 LastUpdateCount := StrToIntDef(StrPas(
00277 FPlainDriver.GetCommandTuples(QueryHandle)), 0);
00278 FPlainDriver.Clear(QueryHandle);
00279 end;
00280 else
00281 begin
00282 Result := False;
00283 LastUpdateCount := StrToIntDef(StrPas(
00284 FPlainDriver.GetCommandTuples(QueryHandle)), 0);
00285 FPlainDriver.Clear(QueryHandle);
00286 end;
00287 end;
00288
00289 { Autocommit statement. }
00290 if not Result and Connection.GetAutoCommit then
00291 Connection.Commit;
00292 end;
00293
00294 { TZPostgreSQLPreparedStatement }
00295
00296 {**
00297 Constructs this object and assignes the main properties.
00298 @param PlainDriver a PostgreSQL plain driver.
00299 @param Connection a database connection object.
00300 @param Info a statement parameters.
00301 @param Handle a connection handle pointer.
00302 }
00303 constructor TZPostgreSQLPreparedStatement.Create(
00304 PlainDriver: IZPostgreSQLPlainDriver; Connection: IZConnection;
00305 const SQL: string; Info: TStrings; Handle: PZPostgreSQLConnect);
00306 begin
00307 inherited Create(Connection, SQL, Info);
00308 FHandle := Handle;
00309 FPlainDriver := PlainDriver;
00310 ResultSetType := rtScrollInsensitive;
00311 FCharactersetCode := (Connection as IZPostgreSQLConnection).GetCharactersetCode;
00312 end;
00313
00314 {**
00315 Creates a temporary statement which executes queries.
00316 @param Info a statement parameters.
00317 @return a created statement object.
00318 }
00319 function TZPostgreSQLPreparedStatement.CreateExecStatement: IZStatement;
00320 begin
00321 Result := TZPostgreSQLStatement.Create(FPlainDriver, Connection, Info, FHandle);
00322 end;
00323
00324 {**
00325 Prepares an SQL parameter for the query.
00326 @param ParameterIndex the first parameter is 1, the second is 2, ...
00327 @return a string representation of the parameter.
00328 }
00329 function TZPostgreSQLPreparedStatement.PrepareSQLParam(
00330 ParamIndex: Integer): string;
00331 var
00332 Value: TZVariant;
00333 TempBytes: TByteDynArray;
00334 TempBlob: IZBlob;
00335 TempStream: TStream;
00336 WriteTempBlob: IZPostgreSQLBlob;
00337 begin
00338 TempBytes := nil;
00339 if InParamCount <= ParamIndex then
00340 raise EZSQLException.Create(SInvalidInputParameterCount);
00341
00342 Value := InParamValues[ParamIndex];
00343 if DefVarManager.IsNull(Value) then
00344 Result := 'NULL'
00345 else begin
00346 case InParamTypes[ParamIndex] of
00347 stBoolean:
00348 if SoftVarManager.GetAsBoolean(Value) then
00349 Result := 'TRUE'
00350 else Result := 'FALSE';
00351 stByte, stShort, stInteger, stLong, stBigDecimal, stFloat, stDouble:
00352 Result := SoftVarManager.GetAsString(Value);
00353 stString, stBytes:
00354 Result := EncodeString(FCharactersetCode,SoftVarManager.GetAsString(Value));
00355 stDate:
00356 Result := Format('''%s''::date',
00357 [FormatDateTime('yyyy-mm-dd', SoftVarManager.GetAsDateTime(Value))]);
00358 stTime:
00359 Result := Format('''%s''::time',
00360 [FormatDateTime('hh":"mm":"ss', SoftVarManager.GetAsDateTime(Value))]);
00361 stTimestamp:
00362 Result := Format('''%s''::timestamp',
00363 [FormatDateTime('yyyy-mm-dd hh":"mm":"ss',
00364 SoftVarManager.GetAsDateTime(Value))]);
00365 stAsciiStream, stUnicodeStream:
00366 begin
00367 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00368 if not TempBlob.IsEmpty then begin
00369 Result := EncodeString(TempBlob.GetString)
00370 end else begin
00371 Result := 'NULL';
00372 end;
00373 end;
00374 stBinaryStream:
00375 begin
00376 TempBlob := DefVarManager.GetAsInterface(Value) as IZBlob;
00377 if not TempBlob.IsEmpty then
00378 begin
00379 if (GetConnection as IZPostgreSQLConnection).IsOidAsBlob then
00380 begin
00381 TempStream := TempBlob.GetStream;
00382 try
00383 WriteTempBlob := TZPostgreSQLBlob.Create(FPlainDriver,
00384 nil, 0, FHandle, 0);
00385 WriteTempBlob.SetStream(TempStream);
00386 WriteTempBlob.WriteBlob;
00387 Result := IntToStr(WriteTempBlob.GetBlobOid);
00388 finally
00389 WriteTempBlob := nil;
00390 TempStream.Free;
00391 end;
00392 end else begin
00393 result:= FPlainDriver.EncodeBYTEA(TempBlob.GetString,FHandle); // FirmOS
00394 {
00395 Result := EncodeString(TempBlob.GetString);
00396 Result := Copy(Result, 2, Length(Result) - 2);
00397 Result := EncodeString(Result);
00398 }
00399 end;
00400 end else
00401 Result := 'NULL';
00402 end;
00403 end;
00404 end;
00405 end;
00406
00407 { TZPostgreSQLCachedResolver }
00408
00409 {**
00410 Checks is the specified column can be used in where clause.
00411 @param ColumnIndex an index of the column.
00412 @returns <code>true</code> if column can be included into where clause.
00413 }
00414 function TZPostgreSQLCachedResolver.CheckKeyColumn(ColumnIndex: Integer): Boolean;
00415 begin
00416 Result := (Metadata.GetTableName(ColumnIndex) <> '')
00417 and (Metadata.GetColumnName(ColumnIndex) <> '')
00418 and Metadata.IsSearchable(ColumnIndex)
00419 and not (Metadata.GetColumnType(ColumnIndex)
00420 in [stUnknown, stBinaryStream, stUnicodeStream]);
00421 end;
00422
00423 end.