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 ZDbcPostgreSql;
00055
00056 interface
00057
00058 {$I ZDbc.inc}
00059
00060 uses
00061 {$IFNDEF VER130BELOW}
00062 Types,
00063 {$ENDIF}
00064 ZCompatibility, Classes, SysUtils, ZDbcIntfs, ZDbcConnection,
00065 ZPlainPostgreSqlDriver, ZDbcLogging, ZTokenizer, ZGenericSqlAnalyser;
00066
00067 type
00068
00069 TZPgCharactersetType = (
00070 csSQL_ASCII, { SQL/ASCII }
00071 csEUC_JP, { EUC for Japanese }
00072 csEUC_CN, { EUC for Chinese }
00073 csEUC_KR, { EUC for Korean }
00074 csEUC_TW, { EUC for Taiwan }
00075 csJOHAB,
00076 csUTF8, { Unicode UTF-8 }
00077 csMULE_INTERNAL, { Mule internal code }
00078 csLATIN1, { ISO-8859 Latin 1 }
00079 csLATIN2, { ISO-8859 Latin 2 }
00080 csLATIN3, { ISO-8859 Latin 3 }
00081 csLATIN4, { ISO-8859 Latin 4 }
00082 csLATIN5, { ISO-8859 Latin 5 }
00083 csLATIN6, { ISO-8859 Latin 6 }
00084 csLATIN7, { ISO-8859 Latin 7 }
00085 csLATIN8, { ISO-8859 Latin 8 }
00086 csLATIN9, { ISO-8859 Latin 9 }
00087 csLATIN10, { ISO-8859 Latin 10 }
00088 csWIN1256, { Arabic Windows }
00089 csWIN1258, { Vietnamese Windows }
00090 csWIN874, { Thai Windows }
00091 csKOI8R, { KOI8-R/U }
00092 csWIN1251, { windows-1251 }
00093 csWIN866, { Alternativny Variant (MS-DOS CP866) }
00094 csISO_8859_5, { ISO-8859-5 }
00095 csISO_8859_6, { ISO-8859-6 }
00096 csISO_8859_7, { ISO-8859-7 }
00097 csISO_8859_8, { ISO-8859-8 }
00098 csSJIS, { Shift JIS }
00099 csBIG5, { Big5 }
00100 csGBK, { GBK }
00101 csUHC, { UHC }
00102 csWIN1250, { windows-1250 }
00103 csGB18030, { GB18030 }
00104 csUNICODE_PODBC,{ UNICODE ( < Ver8.1). Can't call it UNICODE as that's already used }
00105 csTCVN, { TCVN ( < Ver8.1) }
00106 csALT, { ALT ( < Var8.1) }
00107 csWIN, { WIN ( < Ver8.1) }
00108 csOTHER
00109 );
00110
00111 {** Implements PostgreSQL Database Driver. }
00112 TZPostgreSQLDriver = class(TZAbstractDriver)
00113 private
00114 FPostgreSQL7PlainDriver: IZPostgreSQLPlainDriver;
00115 FPostgreSQL8PlainDriver: IZPostgreSQLPlainDriver;
00116 protected
00117 function GetPlainDriver(const Url: string): IZPostgreSQLPlainDriver;
00118 public
00119 constructor Create;
00120 function Connect(const Url: string; Info: TStrings): IZConnection; override;
00121
00122 function GetSupportedProtocols: TStringDynArray; override;
00123 function GetMajorVersion: Integer; override;
00124 function GetMinorVersion: Integer; override;
00125
00126 function GetTokenizer: IZTokenizer; override;
00127 function GetStatementAnalyser: IZStatementAnalyser; override;
00128 end;
00129
00130 {** Defines a PostgreSQL specific connection. }
00131 IZPostgreSQLConnection = interface(IZConnection)
00132 ['{8E62EA93-5A49-4F20-928A-0EA44ABCE5DB}']
00133
00134 function IsOidAsBlob: Boolean;
00135
00136 function GetTypeNameByOid(Id: Oid): string;
00137 function GetPlainDriver: IZPostgreSQLPlainDriver;
00138 function GetConnectionHandle: PZPostgreSQLConnect;
00139 function GetServerMajorVersion: Integer;
00140 function GetServerMinorVersion: Integer;
00141 function GetCharactersetCode: TZPgCharactersetType;
00142 end;
00143
00144 {** Implements PostgreSQL Database Connection. }
00145 TZPostgreSQLConnection = class(TZAbstractConnection, IZPostgreSQLConnection)
00146 private
00147 FHandle: PZPostgreSQLConnect;
00148 FBeginRequired: Boolean;
00149 FTypeList: TStrings;
00150 FPlainDriver: IZPostgreSQLPlainDriver;
00151 FOidAsBlob: Boolean;
00152 FClientCodePage: string;
00153 FCharactersetCode: TZPgCharactersetType;
00154 FServerMajorVersion: Integer;
00155 FServerMinorVersion: Integer;
00156 FServerSubVersion: Integer;
00157 FNoticeProcessor: TZPostgreSQLNoticeProcessor;
00158 protected
00159 function BuildConnectStr: string;
00160 procedure StartTransactionSupport;
00161 procedure LoadServerVersion;
00162 public
00163 constructor Create(Driver: IZDriver; const Url: string;
00164 PlainDriver: IZPostgreSQLPlainDriver; const HostName: string; Port: Integer;
00165 const Database: string; const User: string; const Password: string; Info: TStrings);
00166 destructor Destroy; override;
00167
00168 function CreateRegularStatement(Info: TStrings): IZStatement; override;
00169 function CreatePreparedStatement(const SQL: string; Info: TStrings):
00170 IZPreparedStatement; override;
00171
00172 function CreateSequence(const Sequence: string; BlockSize: Integer): IZSequence; override;
00173
00174 procedure Commit; override;
00175 procedure Rollback; override;
00176
00177 procedure PrepareTransaction(const transactionid: string);override;
00178 procedure CommitPrepared(const transactionid:string);override;
00179 procedure RollbackPrepared(const transactionid:string);override;
00180
00181 procedure Open; override;
00182 procedure Close; override;
00183
00184 procedure SetTransactionIsolation(Level: TZTransactIsolationLevel); override;
00185
00186 function IsOidAsBlob: Boolean;
00187
00188 function GetTypeNameByOid(Id: Oid): string;
00189 function GetPlainDriver: IZPostgreSQLPlainDriver;
00190 function GetConnectionHandle: PZPostgreSQLConnect;
00191
00192 function GetHostVersion: Integer; override;
00193 function GetServerMajorVersion: Integer;
00194 function GetServerMinorVersion: Integer;
00195 function GetServerSubVersion: Integer;
00196
00197 function PingServer: Integer; override;
00198 function GetCharactersetCode: TZPgCharactersetType;
00199 end;
00200
00201 {** Implements a Postgres sequence. }
00202 TZPostgreSQLSequence = class(TZAbstractSequence)
00203 public
00204 function GetCurrentValue: Int64; override;
00205 function GetNextValue: Int64; override;
00206 function GetCurrentValueSQL:String;override;
00207 function GetNextValueSQL:String;override;
00208 end;
00209
00210
00211 var
00212 {** The common driver manager object. }
00213 PostgreSQLDriver: IZDriver;
00214
00215 implementation
00216
00217 uses
00218 ZMessages, ZSysUtils, ZDbcUtils, ZDbcPostgreSqlStatement,
00219 ZDbcPostgreSqlUtils, ZDbcPostgreSqlMetadata, ZPostgreSqlToken,
00220 ZPostgreSqlAnalyser;
00221
00222 procedure DefaultNoticeProcessor(arg: Pointer; message: PChar); cdecl;
00223 begin
00224 DriverManager.LogMessage(lcOther,'Postgres NOTICE',message);
00225 end;
00226 { TZPostgreSQLDriver }
00227
00228 {**
00229 Constructs this object with default properties.
00230 }
00231 constructor TZPostgreSQLDriver.Create;
00232 begin
00233 FPostgreSQL7PlainDriver := TZPostgreSQL7PlainDriver.Create;
00234 FPostgreSQL8PlainDriver := TZPostgreSQL8PlainDriver.Create;
00235 end;
00236
00237 {**
00238 Attempts to make a database connection to the given URL.
00239 The driver should return "null" if it realizes it is the wrong kind
00240 of driver to connect to the given URL. This will be common, as when
00241 the JDBC driver manager is asked to connect to a given URL it passes
00242 the URL to each loaded driver in turn.
00243
00244 <P>The driver should raise a SQLException if it is the right
00245 driver to connect to the given URL, but has trouble connecting to
00246 the database.
00247
00248 <P>The java.util.Properties argument can be used to passed arbitrary
00249 string tag/value pairs as connection arguments.
00250 Normally at least "user" and "password" properties should be
00251 included in the Properties.
00252
00253 @param url the URL of the database to which to connect
00254 @param info a list of arbitrary string tag/value pairs as
00255 connection arguments. Normally at least a "user" and
00256 "password" property should be included.
00257 @return a <code>Connection</code> object that represents a
00258 connection to the URL
00259 }
00260 function TZPostgreSQLDriver.Connect(const Url: string; Info: TStrings): IZConnection;
00261 var
00262 TempInfo: TStrings;
00263 HostName, Database, UserName, Password: string;
00264 Port: Integer;
00265 PlainDriver: IZPostgreSQLPlainDriver;
00266 begin
00267 TempInfo := TStringList.Create;
00268 try
00269 PlainDriver := GetPlainDriver(Url);
00270 ResolveDatabaseUrl(Url, Info, HostName, Port, Database,
00271 UserName, Password, TempInfo);
00272 Result := TZPostgreSQLConnection.Create(Self, Url, PlainDriver, HostName,
00273 Port, Database, UserName, Password, TempInfo);
00274 finally
00275 TempInfo.Free;
00276 end;
00277 end;
00278
00279 {**
00280 Gets the driver's major version number. Initially this should be 1.
00281 @return this driver's major version number
00282 }
00283 function TZPostgreSQLDriver.GetMajorVersion: Integer;
00284 begin
00285 Result := 1;
00286 end;
00287
00288 {**
00289 Gets the driver's minor version number. Initially this should be 0.
00290 @return this driver's minor version number
00291 }
00292 function TZPostgreSQLDriver.GetMinorVersion: Integer;
00293 begin
00294 Result := 3;
00295 end;
00296
00297 {**
00298 Gets a SQL syntax tokenizer.
00299 @returns a SQL syntax tokenizer object.
00300 }
00301 function TZPostgreSQLDriver.GetTokenizer: IZTokenizer;
00302 begin
00303 if Tokenizer = nil then
00304 Tokenizer := TZPostgreSQLTokenizer.Create;
00305 Result := Tokenizer;
00306 end;
00307
00308 {**
00309 Creates a statement analyser object.
00310 @returns a statement analyser object.
00311 }
00312 function TZPostgreSQLDriver.GetStatementAnalyser: IZStatementAnalyser;
00313 begin
00314 if Analyser = nil then
00315 Analyser := TZPostgreSQLStatementAnalyser.Create;
00316 Result := Analyser;
00317 end;
00318
00319 {**
00320 Get a name of the supported subprotocol.
00321 For example: postgresql74 or postgresql81
00322 }
00323 function TZPostgreSQLDriver.GetSupportedProtocols: TStringDynArray;
00324 begin
00325 SetLength(Result, 3);
00326 Result[0] := 'postgresql';
00327 Result[1] := FPostgreSQL7PlainDriver.GetProtocol;
00328 Result[2] := FPostgreSQL8PlainDriver.GetProtocol;
00329 end;
00330
00331 {**
00332 Gets plain driver for selected protocol.
00333 @param Url a database connection URL.
00334 @return a selected protocol.
00335 }
00336 function TZPostgreSQLDriver.GetPlainDriver(const Url: string): IZPostgreSQLPlainDriver;
00337 var
00338 Protocol: string;
00339 begin
00340 Protocol := ResolveConnectionProtocol(Url, GetSupportedProtocols);
00341
00342 if Protocol = FPostgreSQL7PlainDriver.GetProtocol then
00343 Result := FPostgreSQL7PlainDriver
00344 else if Protocol = FPostgreSQL8PlainDriver.GetProtocol then
00345 Result := FPostgreSQL8PlainDriver
00346 else
00347 Result := FPostgreSQL8PlainDriver;
00348 Result.Initialize;
00349 end;
00350
00351 { TZPostgreSQLConnection }
00352
00353 {**
00354 Constructs this object and assignes the main properties.
00355 @param Driver the parent ZDBC driver.
00356 @param PlainDriver a PostgreSQL plain driver.
00357 @param HostName a name of the host.
00358 @param Port a port number (0 for default port).
00359 @param Database a name pof the database.
00360 @param User a user name.
00361 @param Password a user password.
00362 @param Info a string list with extra connection parameters.
00363 }
00364 constructor TZPostgreSQLConnection.Create(Driver: IZDriver; const Url: string;
00365 PlainDriver: IZPostgreSQLPlainDriver; const HostName: string; Port: Integer;
00366 const Database, User, Password: string; Info: TStrings);
00367 begin
00368 inherited Create(Driver, Url, HostName, Port, Database, User, Password, Info,
00369 TZPostgreSQLDatabaseMetadata.Create(Self, Url, Info));
00370
00371 { Sets a default PostgreSQL port }
00372 if Self.Port = 0 then Self.Port := 5432;
00373
00374 { Define connect options. }
00375 if Info.Values['beginreq'] <> '' then
00376 FBeginRequired := StrToBoolEx(Info.Values['beginreq'])
00377 else FBeginRequired := True;
00378
00379 FPlainDriver := PlainDriver;
00380 TransactIsolationLevel := tiNone;
00381
00382 { Processes connection properties. }
00383 if Info.Values['oidasblob'] <> '' then
00384 FOidAsBlob := StrToBoolEx(Info.Values['oidasblob'])
00385 else FOidAsBlob := False;
00386 FClientCodePage := Trim(Info.Values['codepage']);
00387 FCharactersetCode := pg_CS_code(FClientCodePage);
00388
00389 FNoticeProcessor := DefaultNoticeProcessor;
00390 end;
00391
00392 {**
00393 Destroys this object and cleanups the memory.
00394 }
00395 destructor TZPostgreSQLConnection.Destroy;
00396 begin
00397 if FTypeList <> nil then
00398 FTypeList.Free;
00399 inherited Destroy;
00400 end;
00401
00402 {**
00403 Builds a connection string for PostgreSQL.
00404 @return a built connection string.
00405 }
00406 function TZPostgreSQLConnection.BuildConnectStr: string;
00407 var
00408 ConnectTimeout: Integer;
00409
00410 function EscapeValue(AValue: AnsiString):AnsiString;
00411 begin
00412 Result := StringReplace(AValue, '\', '\\', [rfReplaceAll]);
00413 Result := StringReplace(Result, '''', '\''', [rfReplaceAll]);
00414 end;
00415
00416
00417 procedure AddParamToResult(AParam, AValue: AnsiString);
00418 begin
00419 if Result <> '' then
00420 Result := Result + ' ';
00421
00422 Result := Result + AParam+'='+QuotedStr(EscapeValue(AValue));
00423 end;
00424 begin
00425
00426 Result := '';
00427
00428 If IsIpAddr(HostName) then
00429 AddParamToResult('hostaddr', HostName)
00430 else
00431 AddParamToResult('host', HostName);
00432
00433 AddParamToResult('port', IntToStr(Port));
00434 AddParamToResult('dbname', Database);
00435 AddParamToResult('user', User);
00436 AddParamToResult('password', Password);
00437
00438 If Info.Values['sslmode'] <> '' then
00439 begin
00440
00441
00442 AddParamToResult('sslmode', Info.Values['sslmode']);
00443 end
00444 else if Info.Values['requiressl'] <> '' then
00445 begin
00446
00447
00448 AddParamToResult('requiressl', Info.Values['requiressl']);
00449 end;
00450
00451 { Sets a connection timeout. }
00452 ConnectTimeout := StrToIntDef(Info.Values['timeout'], -1);
00453 if ConnectTimeout >= 0 then
00454 AddParamToResult('connect_timeout', IntToStr(ConnectTimeout));
00455 end;
00456
00457 {**
00458 Checks is oid should be treated as Large Object.
00459 @return <code>True</code> if oid should represent a Large Object.
00460 }
00461 function TZPostgreSQLConnection.IsOidAsBlob: Boolean;
00462 begin
00463 Result := FOidAsBlob;
00464 end;
00465
00466 {**
00467 Starts a transaction support.
00468 }
00469 procedure TZPostgreSQLConnection.StartTransactionSupport;
00470 var
00471 QueryHandle: PZPostgreSQLResult;
00472 SQL: PChar;
00473 begin
00474 if TransactIsolationLevel <> tiNone then
00475 begin
00476 if FBeginRequired then
00477 begin
00478 SQL := 'BEGIN';
00479 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00480 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00481 FPlainDriver.Clear(QueryHandle);
00482 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00483 end;
00484
00485 if TransactIsolationLevel = tiReadCommitted then
00486 begin
00487 SQL := 'SET TRANSACTION ISOLATION LEVEL READ COMMITTED';
00488 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00489 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00490 FPlainDriver.Clear(QueryHandle);
00491 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00492 end
00493 else if TransactIsolationLevel = tiSerializable then
00494 begin
00495 SQL := 'SET TRANSACTION ISOLATION LEVEL SERIALIZABLE';
00496 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00497 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00498 FPlainDriver.Clear(QueryHandle);
00499 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00500 end
00501 else
00502 raise EZSQLException.Create(SIsolationIsNotSupported);
00503 end;
00504 end;
00505
00506 {**
00507 Opens a connection to database server with specified parameters.
00508 }
00509 procedure TZPostgreSQLConnection.Open;
00510 var
00511 LogMessage: string;
00512 QueryHandle: PZPostgreSQLResult;
00513 SQL: PChar;
00514 begin
00515 if not Closed then Exit;
00516
00517 LogMessage := Format('CONNECT TO "%s" AS USER "%s"', [Database, User]);
00518
00519 { Connect to PostgreSQL database. }
00520 FHandle := FPlainDriver.ConnectDatabase(PChar(BuildConnectStr));
00521 if FPlainDriver.GetStatus(FHandle) = CONNECTION_BAD then
00522 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcConnect, LogMessage,nil)
00523 else
00524 DriverManager.LogMessage(lcConnect, FPlainDriver.GetProtocol, LogMessage);
00525
00526 { Set the notice processor (default = nil)}
00527
00528 FPlainDriver.SetNoticeProcessor(FHandle,FNoticeProcessor,nil);
00529
00530 { Sets a client codepage. }
00531 if FClientCodePage <> '' then
00532 begin
00533 SQL := PChar(Format('SET CLIENT_ENCODING TO ''%s''', [FClientCodePage]));
00534 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00535 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00536 FPlainDriver.Clear(QueryHandle);
00537 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00538 end;
00539
00540 { Turn on transaction mode }
00541 StartTransactionSupport;
00542 { Setup notification mechanism }
00543
00544
00545 inherited Open;
00546 end;
00547
00548 procedure TZPostgreSQLConnection.PrepareTransaction(const transactionid: string);
00549 var QueryHandle: PZPostgreSQLResult;
00550 SQL: PChar;
00551 Temp: string;
00552 begin
00553 if (TransactIsolationLevel <> tiNone) and not Closed then
00554 begin
00555 Temp:='PREPARE TRANSACTION '''+copy(transactionid,1,200)+'''';
00556 SQL := PChar(Temp);
00557 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00558 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00559 FPlainDriver.Clear(QueryHandle);
00560 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00561 StartTransactionSupport;
00562 end;
00563 end;
00564
00565 {**
00566 Creates a <code>Statement</code> object for sending
00567 SQL statements to the database.
00568 SQL statements without parameters are normally
00569 executed using Statement objects. If the same SQL statement
00570 is executed many times, it is more efficient to use a
00571 <code>PreparedStatement</code> object.
00572 <P>
00573 Result sets created using the returned <code>Statement</code>
00574 object will by default have forward-only type and read-only concurrency.
00575
00576 @param Info a statement parameters.
00577 @return a new Statement object
00578 }
00579 function TZPostgreSQLConnection.CreateRegularStatement(Info: TStrings):
00580 IZStatement;
00581 begin
00582 if IsClosed then Open;
00583 Result := TZPostgreSQLStatement.Create(FPlainDriver, Self, Info, FHandle);
00584 end;
00585
00586 {**
00587 Creates a <code>PreparedStatement</code> object for sending
00588 parameterized SQL statements to the database.
00589
00590 A SQL statement with or without IN parameters can be
00591 pre-compiled and stored in a PreparedStatement object. This
00592 object can then be used to efficiently execute this statement
00593 multiple times.
00594
00595 <P><B>Note:</B> This method is optimized for handling
00596 parametric SQL statements that benefit from precompilation. If
00597 the driver supports precompilation,
00598 the method <code>prepareStatement</code> will send
00599 the statement to the database for precompilation. Some drivers
00600 may not support precompilation. In this case, the statement may
00601 not be sent to the database until the <code>PreparedStatement</code> is
00602 executed. This has no direct effect on users; however, it does
00603 affect which method throws certain SQLExceptions.
00604
00605 Result sets created using the returned PreparedStatement will have
00606 forward-only type and read-only concurrency, by default.
00607
00608 @param sql a SQL statement that may contain one or more '?' IN
00609 parameter placeholders
00610 @param Info a statement parameters.
00611 @return a new PreparedStatement object containing the
00612 pre-compiled statement
00613 }
00614 function TZPostgreSQLConnection.CreatePreparedStatement(
00615 const SQL: string; Info: TStrings): IZPreparedStatement;
00616 begin
00617 if IsClosed then Open;
00618 Result := TZPostgreSQLPreparedStatement.Create(FPlainDriver,
00619 Self, SQL, Info, FHandle);
00620 end;
00621
00622 {**
00623 Makes all changes made since the previous
00624 commit/rollback permanent and releases any database locks
00625 currently held by the Connection. This method should be
00626 used only when auto-commit mode has been disabled.
00627 @see #setAutoCommit
00628 }
00629 procedure TZPostgreSQLConnection.Commit;
00630 var
00631 QueryHandle: PZPostgreSQLResult;
00632 SQL: PChar;
00633 begin
00634 if (TransactIsolationLevel <> tiNone) and not Closed then
00635 begin
00636 SQL := 'COMMIT';
00637 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00638 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00639 FPlainDriver.Clear(QueryHandle);
00640 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00641
00642 StartTransactionSupport;
00643 end;
00644 end;
00645
00646 procedure TZPostgreSQLConnection.CommitPrepared(const transactionid: string);
00647 var QueryHandle: PZPostgreSQLResult;
00648 SQL: PChar;
00649 Temp: string;
00650 begin
00651 if (TransactIsolationLevel = tiNone) and not Closed then
00652 begin
00653 Temp:='COMMIT PREPARED '''+copy(transactionid,1,200)+'''';
00654 SQL := PChar(Temp);
00655 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00656 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00657 FPlainDriver.Clear(QueryHandle);
00658 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00659 StartTransactionSupport;
00660 end;
00661 end;
00662
00663 {**
00664 Drops all changes made since the previous
00665 commit/rollback and releases any database locks currently held
00666 by this Connection. This method should be used only when auto-
00667 commit has been disabled.
00668 @see #setAutoCommit
00669 }
00670 procedure TZPostgreSQLConnection.Rollback;
00671 var
00672 QueryHandle: PZPostgreSQLResult;
00673 SQL: PChar;
00674 begin
00675 if (TransactIsolationLevel <> tiNone) and not Closed then
00676 begin
00677 SQL := 'ROLLBACK';
00678 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00679 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00680 FPlainDriver.Clear(QueryHandle);
00681 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00682
00683 StartTransactionSupport;
00684 end;
00685 end;
00686
00687 procedure TZPostgreSQLConnection.RollbackPrepared(const transactionid: string);
00688 var QueryHandle: PZPostgreSQLResult;
00689 SQL: PChar;
00690 Temp: string;
00691 begin
00692 if (TransactIsolationLevel = tiNone) and not Closed then
00693 begin
00694 Temp:='ROLLBACK PREPARED '''+copy(transactionid,1,200)+'''';
00695 SQL := PChar(Temp);
00696 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00697 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00698 FPlainDriver.Clear(QueryHandle);
00699 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00700 StartTransactionSupport;
00701 end;
00702 end;
00703
00704 {**
00705 Releases a Connection's database and JDBC resources
00706 immediately instead of waiting for
00707 them to be automatically released.
00708
00709 <P><B>Note:</B> A Connection is automatically closed when it is
00710 garbage collected. Certain fatal errors also result in a closed
00711 Connection.
00712 }
00713 procedure TZPostgreSQLConnection.Close;
00714 var
00715 LogMessage: string;
00716 begin
00717 if not Closed then
00718 begin
00719 FPlainDriver.Finish(FHandle);
00720 FHandle := nil;
00721 LogMessage := Format('DISCONNECT FROM "%s"', [Database]);
00722 DriverManager.LogMessage(lcDisconnect, FPlainDriver.GetProtocol, LogMessage);
00723 end;
00724 inherited Close;
00725 end;
00726
00727 {**
00728 Sets a new transact isolation level.
00729 @param Level a new transact isolation level.
00730 }
00731 procedure TZPostgreSQLConnection.SetTransactionIsolation(
00732 Level: TZTransactIsolationLevel);
00733 var
00734 QueryHandle: PZPostgreSQLResult;
00735 SQL: PChar;
00736 begin
00737 if not (Level in [tiNone, tiReadCommitted, tiSerializable]) then
00738 raise EZSQLException.Create(SIsolationIsNotSupported);
00739
00740 if (TransactIsolationLevel <> tiNone) and not Closed then
00741 begin
00742 SQL := 'END';
00743 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00744 CheckPostgreSQLError(nil, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00745 FPlainDriver.Clear(QueryHandle);
00746 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00747 end;
00748
00749 inherited SetTransactionIsolation(Level);
00750
00751 if not Closed then
00752 StartTransactionSupport;
00753 end;
00754
00755 {**
00756 Gets a reference to PostgreSQL connection handle.
00757 @return a reference to PostgreSQL connection handle.
00758 }
00759 function TZPostgreSQLConnection.GetConnectionHandle: PZPostgreSQLConnect;
00760 begin
00761 Result := FHandle;
00762 end;
00763
00764 {**
00765 Gets a PostgreSQL plain driver interface.
00766 @return a PostgreSQL plain driver interface.
00767 }
00768 function TZPostgreSQLConnection.GetPlainDriver: IZPostgreSQLPlainDriver;
00769 begin
00770 Result := FPlainDriver;
00771 end;
00772
00773 {**
00774 Gets a type name by it's oid number.
00775 @param Id a type oid number.
00776 @return a type name or empty string if there was no such type found.
00777 }
00778 function TZPostgreSQLConnection.GetTypeNameByOid(Id: Oid): string;
00779 var
00780 I, Index: Integer;
00781 QueryHandle: PZPostgreSQLResult;
00782 SQL: PChar;
00783 TypeCode, BaseTypeCode: Integer;
00784 TypeName: string;
00785 LastVersion: boolean;
00786 begin
00787 if Closed then Open;
00788
00789 if (GetServerMajorVersion < 7 ) or
00790 ((GetServerMajorVersion = 7) and (GetServerMinorVersion < 3)) then
00791 LastVersion := True
00792 else
00793 LastVersion := False;
00794
00795 { Fill the list with existed types }
00796 if not Assigned(FTypeList) then
00797 begin
00798 if LastVersion then
00799 SQL := 'SELECT oid, typname FROM pg_type WHERE oid<10000'
00800 else
00801 SQL := 'SELECT oid, typname, typbasetype FROM pg_type'
00802 + ' WHERE oid<10000 OR typbasetype<>0 ORDER BY oid';
00803
00804 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00805 CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00806 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00807
00808 FTypeList := TStringList.Create;
00809 for I := 0 to FPlainDriver.GetRowCount(QueryHandle)-1 do
00810 begin
00811 TypeCode := StrToIntDef(StrPas(
00812 FPlainDriver.GetValue(QueryHandle, I, 0)), 0);
00813 TypeName := StrPas(FPlainDriver.GetValue(QueryHandle, I, 1));
00814
00815 if LastVersion then
00816 BaseTypeCode := 0
00817 else
00818 BaseTypeCode := StrToIntDef(StrPas(
00819 FPlainDriver.GetValue(QueryHandle, I, 2)), 0);
00820
00821 if BaseTypeCode <> 0 then
00822 begin
00823 Index := FTypeList.IndexOfObject(TObject(BaseTypeCode));
00824 if Index >= 0 then
00825 TypeName := FTypeList[Index]
00826 else TypeName := '';
00827 end;
00828 FTypeList.AddObject(TypeName, TObject(TypeCode));
00829 end;
00830 FPlainDriver.Clear(QueryHandle);
00831 end;
00832
00833 I := FTypeList.IndexOfObject(TObject(Id));
00834 if I >= 0 then
00835 Result := FTypeList[I]
00836 else Result := '';
00837 end;
00838
00839 {**
00840 Gets the host's full version number. Initially this should be 0.
00841 The format of the version returned must be XYYYZZZ where
00842 X = Major version
00843 YYY = Minor version
00844 ZZZ = Sub version
00845 @return this server's full version number
00846 }
00847 function TZPostgreSQLConnection.GetHostVersion: Integer;
00848 begin
00849 Result := GetServerMajorVersion*1000000+GetServerMinorversion*1000+GetServerSubversion;
00850 end;
00851
00852 {**
00853 Gets a server major version.
00854 @return a server major version number.
00855 }
00856 function TZPostgreSQLConnection.GetServerMajorVersion: Integer;
00857 begin
00858 if (FServerMajorVersion = 0) and (FServerMinorVersion = 0) then
00859 LoadServerVersion;
00860 Result := FServerMajorVersion;
00861 end;
00862
00863 {**
00864 Gets a server minor version.
00865 @return a server minor version number.
00866 }
00867 function TZPostgreSQLConnection.GetServerMinorVersion: Integer;
00868 begin
00869 if (FServerMajorVersion = 0) and (FServerMinorVersion = 0) then
00870 LoadServerVersion;
00871 Result := FServerMinorVersion;
00872 end;
00873
00874 {**
00875 Gets a server sub version.
00876 @return a server sub version number.
00877 }
00878 function TZPostgreSQLConnection.GetServerSubVersion: Integer;
00879 begin
00880 if (FServerMajorVersion = 0) and (FServerMinorVersion = 0) then
00881 LoadServerVersion;
00882 Result := FServerSubVersion;
00883 end;
00884
00885 {**
00886 Loads a server major and minor version numbers.
00887 }
00888 procedure TZPostgreSQLConnection.LoadServerVersion;
00889 var
00890 Temp: string;
00891 List: TStrings;
00892 QueryHandle: PZPostgreSQLResult;
00893 SQL: PChar;
00894 begin
00895 if Closed then Open;
00896 SQL := 'SELECT version()';
00897 QueryHandle := FPlainDriver.ExecuteQuery(FHandle, SQL);
00898 CheckPostgreSQLError(Self, FPlainDriver, FHandle, lcExecute, SQL,QueryHandle);
00899 DriverManager.LogMessage(lcExecute, FPlainDriver.GetProtocol, SQL);
00900
00901 Temp := FPlainDriver.GetValue(QueryHandle, 0, 0);
00902 FPlainDriver.Clear(QueryHandle);
00903
00904 List := TStringList.Create;
00905 try
00906 { Splits string by space }
00907 PutSplitString(List, Temp, ' ');
00908 { first - PostgreSQL, second X.Y.Z}
00909 Temp := List.Strings[1];
00910 { Splits string by dot }
00911 PutSplitString(List, Temp, '.');
00912
00913 FServerMajorVersion := StrToIntDef(List.Strings[0], 0);
00914 if List.Count > 1 then
00915 FServerMinorVersion := GetMinorVersion(List.Strings[1])
00916 else
00917 FServerMinorVersion := 0;
00918 if List.Count > 2 then
00919 FServerSubVersion := GetMinorVersion(List.Strings[2])
00920 else
00921 FServerSubVersion := 0;
00922 finally
00923 List.Free;
00924 end;
00925 end;
00926
00927 {**
00928 Ping Current Connection's server, if client was disconnected,
00929 the connection is resumed.
00930 @return 0 if succesfull or error code if any error occurs
00931 }
00932 function TZPostgreSQLConnection.PingServer: Integer;
00933 const
00934 PING_ERROR_ZEOSCONNCLOSED = -1;
00935 var
00936 Closing: boolean;
00937 res: PZPostgreSQLResult;
00938 isset: boolean;
00939 begin
00940 Result := PING_ERROR_ZEOSCONNCLOSED;
00941 Closing := FHandle = nil;
00942 if Not(Closed or Closing) then
00943 begin
00944 res := FPlainDriver.ExecuteQuery(FHandle,'');
00945 isset := assigned(res);
00946 FPlainDriver.Clear(res);
00947 if isset and (FPlainDriver.GetStatus(FHandle) = CONNECTION_OK) then
00948 Result := 0
00949 else
00950 try
00951 FPlainDriver.Reset(FHandle);
00952 res := FPlainDriver.ExecuteQuery(FHandle,'');
00953 isset := assigned(res);
00954 FPlainDriver.Clear(res);
00955 if isset and (FPlainDriver.GetStatus(FHandle) = CONNECTION_OK) then
00956 Result := 0;
00957 except
00958 Result := 1;
00959 end;
00960 end;
00961 end;
00962
00963 {**
00964 Creates a sequence generator object.
00965 @param Sequence a name of the sequence generator.
00966 @param BlockSize a number of unique keys requested in one trip to SQL server.
00967 @returns a created sequence object.
00968 }
00969 function TZPostgreSQLConnection.CreateSequence(const Sequence: string;
00970 BlockSize: Integer): IZSequence;
00971 begin
00972 Result := TZPostgreSQLSequence.Create(Self, Sequence, BlockSize);
00973 end;
00974
00975 {**
00976 Get characterset in terms of enumerated number.
00977 @return characterset in terms of enumerated number.
00978 }
00979 function TZPostgreSQLConnection.GetCharactersetCode: TZPgCharactersetType;
00980 begin
00981 Result := FCharactersetCode;
00982 end;
00983
00984 {**
00985 Gets the current unique key generated by this sequence.
00986 @param the last generated unique key.
00987 }
00988 function TZPostgreSQLSequence.GetCurrentValue: Int64;
00989 var
00990 Statement: IZStatement;
00991 ResultSet: IZResultSet;
00992 begin
00993 Statement := Connection.CreateStatement;
00994 ResultSet := Statement.ExecuteQuery(
00995 Format('SELECT CURRVAL(''%s'')', [Name]));
00996 if ResultSet.Next then
00997 Result := ResultSet.GetLong(1)
00998 else
00999 Result := inherited GetCurrentValue;
01000 ResultSet.Close;
01001 Statement.Close;
01002 end;
01003
01004 {**
01005 Gets the next unique key generated by this sequence.
01006 @param the next generated unique key.
01007 }
01008 function TZPostgreSQLSequence.GetCurrentValueSQL: String;
01009 begin
01010 result:=Format(' CURRVAL(''%s'') ', [Name]);
01011 end;
01012
01013 function TZPostgreSQLSequence.GetNextValue: Int64;
01014 var
01015 Statement: IZStatement;
01016 ResultSet: IZResultSet;
01017 begin
01018 Statement := Connection.CreateStatement;
01019 ResultSet := Statement.ExecuteQuery(
01020 Format('SELECT NEXTVAL(''%s'')', [Name]));
01021 if ResultSet.Next then
01022 Result := ResultSet.GetLong(1)
01023 else
01024 Result := inherited GetNextValue;
01025 ResultSet.Close;
01026 Statement.Close;
01027 end;
01028
01029 function TZPostgreSQLSequence.GetNextValueSQL: String;
01030 begin
01031 result:=Format(' NEXTVAL(''%s'') ', [Name]);
01032 end;
01033
01034 initialization
01035 PostgreSQLDriver := TZPostgreSQLDriver.Create;
01036 DriverManager.RegisterDriver(PostgreSQLDriver);
01037 finalization
01038 if DriverManager <> nil then
01039 DriverManager.DeregisterDriver(PostgreSQLDriver);
01040 PostgreSQLDriver := nil;
01041 end.
01042