Home

Firebird 2.1 Release Notes

Contents

1. 2 UPDATE OR INSERT INTO EMPLOYEE ID NAME VALUES ID NAME RETURNING ID 3 UPDATE OR INSERT INTO T1 F1 F2 VALUES F1 F2 MATCHING F1 4 53 Data Manipulation Language DML UPDATE OR INSERT INTO EMPLOYE ID NAME VALUES ID NAME RETURNING OLD NAME Usage Notes 1 When MATCHING is omitted the existence of a primary key is required 2 INSERT and UPDATE permissions are needed on lt table or view gt 3 If the RETURNING clause is present then the statement is described as isc_info_sql_stmt_exec_ procedure by the API otherwise it is described as isc_info_sql_stmt_insert Note A multiple rows in singleton select error will be raised if the RETURNING clause is present and more than one record matches the search condition MERGE Statement Adriano dos Santos Fernandes v 2 1 This syntax has been introduced to enable a record to be either updated or inserted according to whether or not a stated condition is met The statement is available in both DSQL and PSQL Syntax Pattern lt merge statement gt MERGE INTO lt table or view gt AS lt correlation name gt USING lt table or view or derived table gt AS lt correlation name gt ON lt condition gt lt merge when matched gt lt merge when not matched gt lt merge when matched gt WHEN MATCHED TH
2. SELECT FRO SELECT RDBSRELATION_NAME RDBSRELATION_ID FRO RDBSRELATIONS AS R RELATION_NAME RELATION_ID b Aggregate on a derived table which also contains an aggregate SELECT DT FIELDS ount Q FRO SELECT R RDBSRELATION_NAME Count RDBSRELATIONS R JOIN RDBSRELATION_FIELDS RF ON RF RDBSRELATION_NAME R RDBSRELATION_NAME GROUP BY R RDBSRELATION_NAME AS DT RELATION_NAME FIELDS GROUP BY DT FIELDS c UNION and ORDER BY example R RDBSRELATION_NAME R RDBSRELATION_ID DBSRELATIONS R ON ALL RDBSOWNER_NAME RDBSRELATION_ID DBSRELATIONS R ORDER BY AS DT WHERE DT RDBSRELATION_ID lt 4 Points to Note e Every column in the derived table must have a name Unnamed expressions like constants should be added with an alias or the column list should be used The number of columns in the column list should be the same as the number of columns from the query expression e The optimizer can handle a derived table very efficiently However if the derived table is involved in an inner join and contains a subquery then no join order can be established and performance will suffer 66 Data Manipulation Language DML ROLLBACK RETAIN Syntax D Yemano
3. FROM RDBSDATABASE EXECUTE BLOCK Statement V Khorsun The SQL language extension EXECUTE BLOCK makes dynamic PSQL available to SELECT specifications It has the effect of allowing a self contained block of PSQL code to be executed in dynamic SQL as if it were a stored procedure Syntax pattern EXECUTE BLOCK param datatype param datatype RETURNS param datatype param datatype AS DECLARE VARIABLE var datatype BEGIN END For the client the call isc_dsql_sql_info with the parameter isc_info_sql_stmt_type returns e isc_info_sql_stmt_select if the block has output parameters The semantics of a call is similar to a SELECT query the client has a cursor open can fetch data from it and must close it after use e isc_info_sql_stmt_exec_procedure if the block has no output parameters The semantics of a call is similar to an EXECUTE query the client has no cursor and execution continues until it reaches the end of the block or is terminated by a SUSPEND The client should preprocess only the head of the SQL statement or use instead of as the parameter indicator because in the body of the block there may be references to local variables or arguments with a colon prefixed Example The user SQL is EXECUTE BLOCK X INTEGER X RETURNS Y VARCHAR AS DECLARE V INTEGER 64 Data M
4. MaxFileSystemCache V Khorsun V 2 1 Sets a threshold determining whether Firebird will allow the page cache to be duplicated to the filesystem cache or not If this parameter is set to any integer value greater than zero its effect depends on the current default size of the page cache if the default page cache in pages is less than the value of MaxFileSystemCache in pages then filesystem caching is enabled otherwise it is disabled Note This applies both when the page cache buffer size is set implicitly by the DefaultDBCachePages setting or explicitly as a database header attribute Thus e To disable filesystem caching always set MaxFileSystemCache to zero e To enable filesystem caching always set MaxFileSystemCache an integer value that is sufficiently large to exceed the size of the database page cache Remember that the effect of this value will be affected by subsequent changes to the page cache size Important The default setting for MaxFileSystemCache is 65536 pages i e filesystem caching is enabled DatabaseGrowthincrement V Khorsun V 2 1 For better control of disk space preallocation the new parameter DatabaseGrowthIncrement has been added to firebird conf It represents the upper limit for the size in bytes of the chunk of disk that will be requested for preallocation as pages for writes from the cache Default 134 217 728 bytes 128 MB For background information p
5. A NULL B gt NULL A NULL B NULL Note All such expressions evaluate to NULL The change does not alter nullability aware semantics of the engine it simply relaxes the syntax restrictions a little NULLs Ordering Changed to Comply with Standard N Samofatov Placement of nulls in an ordered set has been changed to accord with the SQL standard that null ordering be consistent i e if ASC ENDING order puts them at the bottom then DESC ENDING puts them at the top or vice versa This applies only to databases created under an on disk structure of 11 or higher since it needs to use the index changes in order to work Important If you override the default nulls placement no index can be used for sorting That is no index will be used for an ASCENDING sort if NULLS LAST is specified nor fora DESCENDING sort if NULLS FIRST is specified Examples 70 Data Manipulation Language DML Database proc fdb SQL gt SQL gt SQL gt SQL gt A lt null gt 1 SQL gt select a from A lt null gt 1 SQL gt select a from A 1 lt null gt SQL gt select a from A lt null gt 1 SQL gt select a from A 1 lt null gt SQL gt select a from A 1 lt null gt SQL gt select a from Subqueries and INSERT Statements Can Now Accept UNION Sets create table gnull a int insert into gnull values null insert into gnull values 1 select a from gnull order by a lt null gt
6. 59 Data Manipulation Language DML returns 123 DATEADD MILLISECOND 100 timestamp 01 01 2000 01 00 00 0000 DATEDIFF MILLISECOND timestamp 01 01 2000 02 00 00 0000 timestamp 01 01 2000 01 00 00 For more explanatory examples of using DATEADD and DATEDIFFO refer to the alphabetical listing of functions in Appendix A Functions Enhanced in V 2 0 x Some function enhancements were already available in the V 2 0 x releases IIFQ Expression O Loa V 2 0 x An UFO expression can be used as a shortcut for a CASE expression that tests exactly two conditions It returns the value of the first sub expression if the given search condition evaluates to TRUE otherwise it returns a value of the second sub expression IIF lt search_condition gt lt valuel gt lt value2 gt is implemented as a shortcut for CASE WHEN lt search_condition gt THEN lt valuel gt SE lt value2 gt END Example SELECT IIF VAL gt 0 VAL VAL FROM OPERATION Improvement in CAST Behaviour D Yemanov V 2 0 x The infamous Datatype unknown error SF Bug 1371274 when attempting some castings has been eliminated It is now possible to use CAST to advise the engine about the data type of a parameter Example SELECT CAST AS INT FROM RDBSDATABASE 60 Data Manipulation Language DML CAST x as lt domain name gt A do
7. 1 isc_info_tra_consistency isc_info_tra_concurrency 2 isc_info_tra_read_committed isc_info_tra_no_rec_version isc_info_tra_rec_version That is for Read Committed transactions two items are returned isolation level and record versioning policy while for other transactions one item is returned isolation level isc_info_tra_access Returns the access mode read only or read write of the current transaction The format of the returned clumplets is isc_info_tra_access 1 isc_info_tra_readonly isc_info_tra_readwrite isc_info_tra_lock_timeout Returns the lock timeout set for the current transaction Improved Services API The following improvements have been added to the Services API Parameter isc_spb_trusted_auth V 2 1 ODS 11 1 isc_spb_trusted_auth applies only to Windows and is used to force Firebird to use Windows trusted authentication for the requested service Parameter isc_spb_dbname V 2 1 ODS 11 1 For any services related to the security database provides the ability to supply the name of the security database when invoking a security service remotely It is equivalent to supplying the database switch when calling the gsec utility remotely Task Execution Optimized D Yemanov Services are now executed as threads rather than processes on some threadable CS builds currently 32 bit Windows and Solaris 21 Changes to the Firebird API and ODS New Function for Delivering
8. RDBSFIRST_NAME RDBSMIDDLE_NAME RDBSLAST_NAME RDB first_name _UNICODE_FSS RDB middle_name _UNICODE_FSS RDBSlast_name FROM RDBSUSERS WHERE CURRENT_USER SYSDBA OR CURRENT_USER RDBSUSERS RDBSUSER_NAME COMMIT GRANT ALL ON RDBSUSERS to VIEW USERS GRANT SELECT ON USERS to PUBLIC GRAN UPDA PASSWD GROUP_NAME UID GID FIRST_NAM MIDDLE_NAME LAST_NAME ON USERS TO PUBLIC COMMIT E Ej 5 move data from temporary table and drop it INSERT INTO RDBSUSERS RDBSUSER_NAME RDBSSYS_USER_NAME RDBSGROUP_NAME RDBSUID RDBSGID RDBSPRIVILEGE RDBSCOMMENT RDBSFIRST_NAME RDBSMIDDLE_NAME RDBSLAST_NAME RDBSPASSWD SELECT USER_NAME SYS_USER_NAME GROUP_NAME UID GID PRIVILEGE COMMENT FIRST_NAME MIDDLE_NAME LAST_NAME PASSWD FROM UTMP COMMIT b E F H T E DROP TABLE UTMP COMMIT Note This field should be constrained as NOT NULL For information about this see Nullability of RDB PASSWD in the Security chapter 176
9. 140 Command line Utilities Therefore printing the column headers previously a fixed feature can now be enabled disabled interactively or from a script by means of the SET HEADing ON OFF command in the isql shell As is the case with other SET commands simply using SET HEAD will toggle the state between activated and deactivated Note There is no command line option to toggle headings off Using SET will display the state of SET HEAD along with other switches that can be toggled on off in the isql shell SET SQLDA_DISPLAY ON OFF A dos Santos Fernandes This SQLDA_DISPLAY command shows the input SQLDA parameters of INSERTs UPDATEs and DELETE Ss It was previously available only in DEBUG builds and has now been promoted to the public builds It shows the information for raw SQLVARs Each SQLVAR represents a field in the XSQLDA the main structure used in the FB API to talk to clients transferring data into and out of the server Note The state of this option is not included in the output when you type SET in isql to see the current settings of most options SET TRANSACTION Enhanced C Valderrama The SET TRANSACTION statement has been enhanced so that now all TPB options are supported s NO AUTO UNDO s IGNORE LIMBO e LOCK TIMEOUT lt number gt Example SET TRANSACTION WAIT SNAPSHOT NO AUTO UNDO LOCK TIMEOUT 10 See also the document doc sql extens
10. It is not realistic to attempt to describe all of the SPB parameters in release notes In the InterBase 6 0 beta doc umentation it takes about 40 pages The next section highlights some known differences between the operation of fbsvemgr and what you might otherwise infer from the old beta documentation fbsvcmgr Syntax Specifics Do s and Don ts With fbsvcmgr you can perform a single action and get its results if applicable or you can use it to retrieve multiple information items from the Services Manager You cannot do both in a single command For example T fobsvcmgr service_mgr user sysdba password masterke action_display_user will list all current users on the local firebird server SYSDBA Sql Server Administrator QA_USER1 QA_USER2 QA_USER3 QA_USER4 QA_USER5 GUEST SHUT1 SHUT2 QATEST 00 00 65 0 6 010 06 O OO O 0 0 0 OO 0 and fobsvcmgr service_mgr user sysdba password masterke info_server_version info_implementation will report both the server version and its implementation Server version LI T2 1 0 15740 Firebird 2 1 Alpha 1 Server implementation Firebird linux AMD64 But an attempt to mix all of this in single command line fobsvcmgr service_mgr user sysdba password masterke action _display_user info_server_version info_implementation raises an error Unknown switch info_server_version 132 Command line Utilities
11. D Yemanov Feature request CORE 819 and CORE 682 v 2 1 64 bit Windows platform AMD64 and Intel EM64T ports of Classic Superserver and Embedded mod els Administrative Database Monitoring via SQL v 2 1 Implementation of run time database snapshot monitoring transactions tables etc via SQL over some new virtualized system tables See Monitoring Tables Included in the set of tables is one named MONSDATABASE that provides a lot of the database header infor mation that could not be obtained previously via SQL such details as the on disk structure ODS version SQL dialect sweep interval OIT and OAT and so on It is possible to use the information from the monitoring tables to cancel a rogue query See Cancel a Running Query More Context Information Context information providing the server engine version has been added for retrieving via SELECT calls to the RDB GET_CONTEXT function See More Context Information New Command line Utility fbsvcmgr V 2 1 The new utility fbsvcmgr provides a command line interface to the Services API enabling access to any service that is implemented in Firebird Although there are numerous database administration tools around that surface the Services API through graph ical interfaces the new tool addresses the problem for admins needing to access remote Unix servers in broad networks through a text only connection Previously meeting such a requirement needed a programmer
12. Gotcha According to grammar rules since v 1 5 ORDER BY lt value_expression gt is allowed and lt value_expression gt could be a variable or a parameter It is tempting to assume that ORDER BY lt degree_number gt could thus be validly represented as a replaceable input parameter or an expression containing a parameter However while the DSQL parser does not reject the parameterised ORDER BY clause expression if it resolves to an integer the optimizer requires an absolute constant value in order to identify the position in the output list of the ordering column or derived field If a parameter is accepted by the parser the output will undergo a dummy sort and the returned set will be unsorted NEXT VALUE FOR Expression D Yemanov Added SQL 99 compliant NEXT VALUE FOR lt sequence_name gt expression as a synonym for GEN_ID lt generator name gt 1 complementing the introduction of CREATE SEQUENCE syntax as the SQL standard equivalent of CREATE GENERATOR Examples 1 SELECT GEN_ID S_EMPLOYEE 1 FROM RDBSDATABASE se INSERT INTO EMPLOYEE ID NAME VALUES NEXT VALUE FOR S_EMPLOYEE John Smith 79 Data Manipulation Language DML Note 1 Currently increment step values not equal to 1 one can be used only by calling the GEN_ID function Future versions are expected to provide full support for SQL 99 sequence
13. UCS_BASIC Unicode 4 0 support with UTF8 character et and UCS_BASIC collation A dos Santos Fer nandes UNICODE Unicode 4 0 support with UTF8 character set and UNICODE collation A dos Santos Fer nandes UNICODE_CI Unicode 4 0 support with UTF8 character set and case insensitive collation A dos Santos Fer nandes ISO8859_ 1 FR FR CL AI V 2 1 French language case insensitive and accent insensitive collation A dos Santos Fer nandes CP943C CP943C_UNICODE V 2 1 Japanese character set TIS620 TIS620_UNICODE V 2 1 Thai character set single byte Narrow Character Sets CYRL DOS437 DOS737 DOS775 DOS850 DOS852 DOS857 DOS858 DOS860 DOS861 DOS862 DOS863 DOS864 DOS865 DOS866 DOS869 IS0O8859_1 IS08859_13 I508859_2 ISO8859_3 ISO08859_4 ISO8859_5 IS08859_6 ISO8859_7 ISO8859_8 ISO8859_9 KOI8R KOI8U NEXT TIS620 WIN1250 WIN1251 WIN1252 WIN1253 WIN1254 WIN1255 WIN1256 WIN1257 and WIN1258 ICU Character Sets UTF 8 ibm 1208 ibm 1209 ibm 5304 ibm 5305 windows 65001 cp1208 UTF 16 ISO 10646 UCS 2 unicode csUnicode ucs 2 UTF 16BE x utf 16be ibm 1200 ibm 1201 ibm 5297 ibm 13488 ibm 17584 windows 1201 cp1200 cp1201 UTF16_BigEndian UTF 16LE x utf 161le ibm 1202 ibm 13490 ibm 17586 UTF16_LittleEndian windows 1200 UTF 32 ISO 10646 UCS 4 csUCS4 ucs 4 UTF 32BE UTF32_BigEndian ibm 1232 ibm 1233 UTF 32LE UTF32_LittleEndian ibm 1234 UTF16_PlatformE
14. ibm 862_P100 1 windows 862 ibm 863_P100 1 ibm 864_X110 1 ibm 865_P100 1 ibm 866_P100 1 DOS 862 1995 ibm 862 1995 1999 1995 1995 ibm 8 63 ibm 8 64 ibm 865 ibm 866 IB IB IB IB IB 862 cp862 863 864 865 866 cp863 cp864 cp865 cp866 862 csPC862LatinHebrew 863 csIBM863 csIBM864 865 csIBM865 866 csIBM866 windows 866 ibm 867_P100 1998 ibm 868_P100 1995 ibm 869_P100 1995 windows 869 ibm 878_P100 1996 ibm 901_P100 1999 ibm 902_P100 1999 ibm 922_P100 1999 ibm 4909_P1 ibm 5346_P1 ibm 5347_P1 ibm 5348_P1 ibm 5349_P1 ibm 5350_P1 ibm 9447_P1 100 1999 100 1998 100 1998 100 1997 100 1998 100 1998 100 2002 ibm 867 ibm 868 ibm 869 cp IB IB ibm 878 ibm 901 ibm 902 ibm 922 cp ibm 4909 ibm 534 ibm 534 ibm 534 ibm 534 KO ibm 944 67 868 869 CP868 cp869 I8 R 922 922 windows 1250 windows 1251 windows 1252 windows 1253 windows 1254 windows 1255 windows 1256 2000 windows 1256 cp1256 ibm 9449_P1 ibm 5354_P1 ibm 1250_P1 ibm 1251_P1 ibm 1252_P1 ibm 1253_P1 ibm 1254_P1 ibm 1255_P1 ibm 5351_P1 ibm 1256_P1 ibm 5352_P1 ibm 1257_P1 ibm 5353_P1 ibm 1258_P1 macos 0_2 1 macos 6 10 macos 7_3 1 macos 29 10 2 x mac centraleurroman windows 10029 x mac c 100 2002 100 1998 100 1995 100 1995 100 2000 100 1995 100 1995 100 1995 100 1998 10 1997 100 1998 100 1995 100 1998 100 1997 ibm 9449 ibm 5354 ibm 1250 ibm
15. lt indexed_retrieval gt lt navigational_scan gt 76 Data Manipulation Language DML lt natural_scan gt lt stream_alias gt NATURAL lt indexed_retrieval gt lt stream_alias gt INDEX lt index_name gt lt index_name gt lt navigational_scan gt lt stream_alias gt ORDER lt index_name gt INDEX lt index_name gt lt index_name gt lt sorted_streams gt SORT lt stream_retrieval gt lt joined_streams gt JOIN lt stream_retrieval gt lt stream_retrieval gt lt stream_retrieval gt SORT MERGE lt sorted_streams gt lt sorted_streams gt Details Natural scan means that all rows are fetched in their natural storage order Thus all pages must be read before search criteria are validated Indexed retrieval uses an index range scan to find row ids that match the given search criteria The found matches are combined in a sparse bitmap which is sorted by page numbers so every data page will be read only once After that the table pages are read and required rows are fetched from them Navigational scan uses an index to return rows in the given order if such an operation is appropriate s The index b tree is walked from the leftmost node to the rightmost one e If any search criterion is used on a column specified in an ORDER BY clause the navigation is limited to some subtree path depending on a predicate e If any s
16. 1 a hash valid for user A is invalid for user B 2 when a user changes his password even to exactly the same string as before the data stored in RDB USERS RDB PASSWD is new Although this situation does not increase resistance to a brute force attempt to crack the password it does make visual analysis of a stolen password database much harder The New Security Database The structure of security database was changed In general now it contains a patch by Ivan Prenosil with some minor differences enabling any user to change his her own password e In firebird 1 5 the table USERS has to be readable by PUBLIC an engine requirement without which the password validation process would fail Ivan s patch solution used a view with the condition WHERE USER 123 Security That worked due to another bug in the engine that left the SQL variable USER empty not authenticator as it might seem from engine s code Once that bug was fixed it was certainly possible to add the condition USER authenticator For the short term that was OK because the username is always converted to upper case e A better solution was found that avoids making user authentication depend on an SQL trick The result is that the non SYSDBA user can see only his own login in any user management tool gsec or any graphical interface that use the Services API SYSDBA continues to have full access to manage users accounts New se
17. Better password encryption A Peshkov Password encryption decryption now uses a more secure password hash calculation algorithm Users can modify their own passwords A Peshkov The SYSDBA remains the keeper of the security database However users can now modify their own passwords 121 Security Non server access to security database is rejected A Peshkov gsec now uses the Services API The server will refuse any access to security2 fdb except through the Services Manager Active protection from brute force attack A Peshkov Attempts to get access to the server using brute force techniques on accounts and passwords are now detected and locked out e Login with password is required from any remote client e Clients making too many wrong login attempts are blocked from further attempts for a period Support for brute force attack protection has been included in both the attachment functions of the Firebird API and the Services API For more details see Protection from Brute force Hacking Vulnerabilities have been closed A Peshkov C Valderrama Several known vulnerabilities in the API have been closed Caution It must be noted that the restoration of the server redirection multi hop capability to Firebird 2 potentially throws up a new vulnerability For that reason it is controlled by a parameter Redirection in firebird conf which you should not enable unless you really understand its implications
18. Firebird 2 1 Release Notes Helen Borrie Collator Editor 4 December 2014 Document v 0217_00 for Firebird 2 1 7 Firebird 2 1 Release Notes 4 December 2014 Document v 0217_00 for Firebird 2 1 7 Helen Borrie Collator Editor Table of Contents Te General Notes voc de srches sd secede a te ad Recess a Seat tees a ea a e E AE E Aea taaa 1 Patch Release 2 iSi veg sevtees cases v Steed ii ii di 1 LACA A NN 1 Improvements A NS 1 Firebird 2 1 5 Security Update li Adel cana 2 Patch Release 2 Ll Dastcooio tesina E I ANE O EEEE an its EE EAN 2 Improvements iiye ET e AAS E E EAT E E o r Ea an s vac sees 2 Patch Release 2 A4 TTT 2 Additions in this Sub release cisneros re e aT a E ER E A Eii 3 PLONE MENE cuit Aa Wat te aii Bae ei a ae ae 3 Bugs Bixee s 03 0 50 c ee dened deen cedide seb aaia satus dank pode boa EEEE cebive daende dude AEAEE ea Ee aeaa 3 Patch Release 2 Lit A A TA IA ca 3 AA A A NN 3 Patch A AAA RO 4 Patch ReleaseZol lc ti LD AI ii sl east 5 Bug Reportin rustica tddi 6 Additional Documentation ssie s dico loteo decenas TETE E Ky Ad TRS de Seen dec TES 27 KO alse 92842 Try KTV rizos 6 2 New atv Firebird TTT 7 New Features Implemented cui ida 7 n Disky Structures 2 sch wetei uct aE aae tected hts toi hu oils howe A oy etd iene er 7 Database Trig SOs tit ri e e ea dat dirias 7 SOL and Objects eieo ii EOE a An EEIE 7 Proceduital SOUL oes ed Ba ee eens Lata e age oot ETA ose tedds deat
19. lt string gt the beginning of string1 until the length of the result string becomes equal to length 1 If the second string is omitted the default value is one space 2 Ifthe result string would exceed the length the second string is truncated select lpad Example x 10 from y MAXVALUE MAXVALUE lt value gt lt value gt Returns the maximum value of a list of values select maxvalue vl v2 10 from x MINVALUE MINVALUE lt value gt lt value gt Returns the minimun value of a list of values select minvalue vl v2 10 from x MOD MOD lt number gt lt number gt Modulo MOD X Y returns the remainder part of the division of X by Y 161 New Built in Functions Function Format Description select mod x 10 from y OCTET_LENGTH OCTET_LENGTH lt string gt Returns the length of a string or expression result in lt string_expr gt bytes select rdb relation_name octet_length rdb relation_name octet_length trim rdbSrelation_name from rdbSrelations OVERLAY See below Returns string replacing the substring FROM start FOR length by string2 Format ERLAY lt stringl gt PLACING lt string2 gt FROM lt start gt FOR lt length gt The OVERLAY function is equivalent to TRING lt stringl gt 1 FOR lt start gt 1 lt string2 gt TRING lt st
20. 2 merge MON STAT_ID statistics ID MONSATTACHMENTS connected attachments MONSATTACHMENT_ ID attachment ID MONSSERVER_PID server process ID MONSSTATE attachment state 0 idle 1 active MONSATTACHMENT_NAME connection string MONSUSER user name 113 Administrative Features MONSROLE role name MON REMOTE_PROTOCOL remote protocol name MONSREMOTE_ADDRESS remote address MON REMOTE_PID remote client process ID MONSREMOTE_PROCESS remote client process pathname MON CHARACTER_SET_ID attachment character set MONSTIMESTAMP connection date time MON GARBAGE_COLLECTION garbage collection flag MONSSTAT_ ID statistics ID s columns MON REMOTE_PID and MON REMOTE_PROCESS contains non NULL values only if the client library is version 2 1 or higher s column MON REMOTE_PROCESS can contain a non pathname value if an application has specified a custom process name via DPB column MON GARBAGE_COLLECTION indicates whether GC is allowed for this attachment as spec ified via the DPB in isc_attach_database MONSTRANSACTIONS started transactions MONSTRANSACTION_ ID transaction ID MONSATTACHMENT_ ID attachment ID MONSSTATE transaction state U idle state after prepare until execution begins 1 active state during execution and fetch Idle state returns after cursor is closed MONSTIMESTAMP transaction start date time MON TOP_TR
21. Altering and dropping procedures in use Examples CONNECT 5 E ERM REATE PROCEDURE P1 X INTEGER 123 S Y INTEGER Y Jo E G O SUSPEND non O Ss H gt EXECUTE PROCEDURE P1 SE ERM CREATE PROCEDURE P2 RETURNS Y INTEGER A B FOR SELECT Y FROM P1 INTO Y DO SUSPEND END COMMIT SET TERM 87 Procedural SQL PSQL ALTER PROCEDURE Pl X INTEGER CURRENT_TRANSACTION RETURNS Y INTEGER 5875 COMMIT CONNECT 5 SELECT FROM P2 Note 1 The source and BLR for the argument defaults are stored in RDB FIELDS 2 As was pointed out in a Tracker entry the examples above should not be taken as a recommendation to use a SUSPEND statement to handle return values in an executable stored procedure The author used SUSPEND here in order to illustrate the aspects of the new feature LEAVE lt label gt Syntax Support D Yemanov New LEAVE lt label gt syntax now allows PSQL loops to be marked with labels and terminated in Java style The purpose is to stop execution of the current block and unwind back to the specified label After that execution resumes at
22. Immediate Release of External Files V Khorsun Feature request CORE 961 The engine will now release external table files as soon as they are no longer in use by user requests 32 Global Improvements in Firebird 2 1 Synchronization of DSQL metadata cache objects in Classic server A dos Santos Fernandes Feature request CORE 976 No details BLOB Improvements A dos Santos Fernandes Feature request CORE 1169 Conversion of temporary blobs to the destination blob type now occurs when materializing Type Flag for Stored Procedures D Yemanov Feature request CORE 779 Introduced a type flag for stored procedures adding column RDB PROCEDURE_TYPE to the table RDB PROCEDURES Possible values are 0 or NULL legacy procedure no validation checks are performed 1 selectable procedure one that contains a SUSPEND statement Ja executable procedure no SUSPEND statement cannot be selected from Help for Getting Core Dumps on Linux A Peshkov Feature request CORE 1558 The configuration parameter BugcheckAbort provides the capability to make the server stop trying to continue operation after a bugcheck and instead to call abort immediately and dump a core file Since a bugcheck usually occurs as a result of a problem the server does not recognise continuing operation with an unresolved problem is not usually possible anyway and the core dump can provide useful debug information I
23. and earlier User table scan requests will not remove unused record versions but will cause the GC thread to be notified about any page where an unused record version is detected The engine will also remember those page numbers where UPDATE and DELETE statements created back versions e combined the installation default for Superserver both background and cooperative garbage collection are performed Note The Classic server ignores this parameter and always works in cooperative mode Porting of the Services API to Classic is Complete N Samofatov Porting of the Services API to Classic architecture is now complete All Services API functions are now available on both Linux and Windows Classic servers with no limitations Known issues with gsec error reporting in previous versions of Firebird are eliminated Lock Timeout for WAIT Transactions A Karyakin D Yemanov All Firebird versions provide two transaction wait modes NO WAIT and WAIT NO WAIT mode means that lock conflicts and deadlocks are reported immediately while WAIT performs a blocking wait which times out only when the conflicting concurrent transaction ends by being committed or rolled back The new feature extends the WAIT mode by making provision to set a finite time interval to wait for the con current transactions If the timeout has passed an error isc_lock_timeout is reported Timeout intervals are specified per transaction using the new TP
24. see the accompanying release of the Cumulative Bug Fixes document or view the document on line Patch Release 2 1 6 This sub release fixes bugs addressed since V 2 1 5 and the security update of that sub release that was issued in March 2013 This latest release benefits from fixes to bugs detected not just in older sub releases but also from bug fixes and some improvements backported from the ongoing development of versions 2 5 x and the alpha v 3 The bug fixes are detailed in the accompanying release of the Cumulative Bug Fixes document You can also download this document or read it on line by visiting this resource page at the Firebird website Improvements Notable improvements in this sub release are e More details are reported for object in use errors By D Yemanov Details see tracker ticket CORE 4386 General Notes e Error information returned for index and constraint violations has been extended to include the problematic key value By D Yemanov Details see tracker ticket CORE 3881 e Some newer DSQL features were implemented in Embedded SQL ESQL by Dmitry Yemanov UPDATE OR INSERT Details see tracker ticket CORE 4438 Support for the RETURNING Details see tracker ticket CORE 4437 Firebird 2 1 5 Security Update 1 A remote stack buffer overflow was discovered in the Firebird Server during March 2013 that allows an unau thenticated user to crash
25. were a single segment index segments A and B combined as it would be if it were a double segment index and the full three segment match A B C i e all the ways a compound index can be used This opens more opportunities to the optimizer for clever access path decisions in cases involving partial index matches The per segment selectivity values are stored in the column RDB STATISTICS of table RDB INDEX_SEGMENTS The column of the same name in RDB INDICES is kept for compatibility and still represents the total index selectivity that is used for a full index match 101 International Language Support INTL Adriano dos Santos Fernandes This chapter describes the new international language support interface that was introduced with Firebird 2 Since then a number of additions and improvements have been added including the ability to implement UNI CODE collations from external libraries generically New DDL syntax has been introduced to assist with this task in the form of the CREATE COLLATION statement New INTL Interface for Non ASCIl Character Sets A dos Santos Fernandes Originally described by N Samofatov Firebird 2 s new interface for international character sets features many enhancements that have been implemented by me Architecture Firebird allows character sets and collations to be declared in any character field or variable declaration The default character set can also be specified at database crea
26. 1251 ibm 1252 ibm 1253 ibm 1254 ibm 1255 ibm 5351 ibm 1256 ibm 5352 ibm 1257 ibm 5353 ibm 1258 windows 1257 windows 1258 windows 1250 windows 1251 windows 1252 windows 1253 windows 1254 windows 1255 windows 1256 windows 1257 windows 1258 cpl cpl cpl cpl cpl cpl cpl cpl koi8 cSKOI8R cp878 250 251 252 253 254 255 257 258 868 csIBM868 cp ar 869 cp gr csIBM869 10 2 macintosh mac csMacintosh windows 10000 2 x mac greek windows 10006 macgr 10 2 x mac cyrillic windows 10007 maccy macos 35 10 2 x mac turkish ibm 1051_P100 1995 ibm 1276_P100 1995 csAdobeStandardE ibm 1277_P100 1995 ibm 1006_P100 1995 ibm 1098_P100 1995 ibm 1124_P100 1996 ibm 1125_P100 1997 ibm 1129_P100 1997 ibm 1131_P100 1997 ibm 1133_P100 1997 ibm 1381_P110 1999 ibm 1051 ibm 1276 ncoding ibm 1277 ibm 1006 ibm 1098 ibm 1124 ibm 1125 ibm 1129 ibm 1131 ibm 1133 ibm 1381 windows 10081 mactr hp roman8 roman8 r8 csHPRoman8 Adobe Standard Adobe Latinl cp1006 cp1098 cp1124 cp1125 1006 1098 1124 cp1131 cp1381 1381 Encoding Encoding macc ibm 37_P100 1995 ibm 37 IBM037 ibm 037 bedic cp wt bcdic cp us bedic cp nl csIBM037 cp037 037 cpibm37 cp37 bcdic cp ca ibm 273_P100 1995 ibm 273 IBM273 CP273 csIBM273 ebcdic de cpibm273 273 ibm 277_P100 1995 ibm 277 IBM277 cp277 csIBM277 ebedic dk cpibm277 277 ibm 278_P100 1995 ibm 278 IB
27. 151 viii Firebird 2 1 Release Notes Tee acy ashy cats iia o AEE E a tts ate 151 Redirection RN 151 About Multihop comicidad i add idas 151 LZ POG 1 555 Repeat 4 ERa Tae e t estat lagtaaseuansdahet a a dana Ia Rss a Ea OEE Ea 152 OldColummN amin S ee rerin aoee ou a E E E E A E E E a eila 152 UsePriorityScheduler iris iia 152 TCPNoNagle has changed asssg agg cage Tieri EEEE rR ROOT KTTO rs 152 IPC Name Behaviour Has Changed 0 00 ciclcicecesscneccbesseeeescbeoessbescesdebbectees i iiano io iani iaie 152 Renamed Parameters iia ese Te K debs ah aT EEE Soye Aa ai T 153 SortMemBlockSize Changed to TempCacheBlockSize sss sese 153 SortMemUpperLimit Changed to TempCacheUpperLimit sss esse ee eee 153 Removed or Deprecated Parameters ssssaszcsrac2i sz sg agad ac Kag 24 e 2829427592427599 nono no nono T nono no nono i aE 153 Createlntermal Wind Ow lt Eez enee IA aT OTa E A 9 ZPO cad ok Tea ara 20T rdg 153 DeadThreadsCollection is no longer Used sss sees eee 153 16 Firebird 2 Project Teams coi dd ainda dam 154 Appendix A New Built in Functions scd SYR Z Tene az a ee RETER E E SET a ac RET KR ZEOT gd Taze 156 Appendix B International Character Sets oreren erii inre aa dnie e i ea e ie 167 New Character Sets and Collations Implemented sss sss sese ee eee 167 Narrow Character Sets sr sai en eene itargan dinn cles Ta TOREK ieiet didia neria 168 ICU Character Sets si csessitovsacsssbatteeshoneabatabdhebssdoveatanabetdbsstousa
28. Error Text C Valderrama The new function fb_interpret replaces the former isc_interprete for extracting the text for a Firebird error message from the error status vector to a client buffer Important isc_interprete is vulnerable to overruns and is deprecated as unsafe The new function should be used instead Accommodation of New Shutdown lt state gt Parameters D Yemanov API Access to database shutdown is through flags appended to the isc_dpb_shutdown parameter in the DBP argument passed to isc_attach_database The symbols for the lt state gt flags are define isc_dpb_shut_cache 0x1 define isc_dpb_shut_attachment 0x2 define isc_dpb_shut_transaction 0x4 define isc_dpb_shut_force 0x8 define isc_dpb_shut_mode_mask 0x70 define isc_dpb_shut_default 0x0 define isc_dpb_shut_normal 0x10 define isc_dpb_shut_multi 0x20 define isc_dpb_shut_single 0x30 define isc_dpb_shut_full 0x40 Example of Use in C C char dpb_buffer 256 dpb p ISC_STATUS status_vector ISC_STATUS_LENGTH isc_db_ handle handle NULL dpb dpb_buffer dpb isc_dpb _versionl const char user_name SYSDBA const int user_name_length strlen user_name dpb isc_dpb_user_name dpb user_name_length memcpy dpb user_name user_name_length dpb user_name_length const char user_password masterkey const int user_password_length strlen u
29. Firebird 1 5 Authentication In Firebird 1 5 the DES algorithm is used twice to hash the password first by the client then by the server before comparing it with the hash stored in security database However this sequence becomes completely broken when the SYSDBA changes a password The client performs the hash calculation twice and stores the resulting hash directly in the security database Therefore hash management is completely client dependent or actually client defined Firebird 2 Server side Hashing To be able to use stronger hashes another approach was called for The hash to be stored on the server should always be calculated on the server side Such a schema already exists in Firebird in the Services API This led to the decision to use the Services API for any client activity related to user management Now gsec and the isc_user_add modify delete API functions all use services to access the security database Embedded access to Classic server on POSIX is the exception see below It became quite easy to make any changes to the way passwords are hashed it is always performed by the server It is no longer gsec s problem to calculate the hash for the security database it simply asks services to do the work It is worth noting that the new gsec works successfully with older Firebird versions as long as the server s architecture supports services The SHA 1 Hashing Algorithm This method leads to the situation where
30. GB GB18030 lt charset gt The stored procedure takes two arguments a string that is the character set s identifier as declared in the config uration file and a smallint that is the maximum number of bytes a single character can occupy in the encoding For our example execute procedure sp_register_character_set GB 4 The CREATE COLLATION Statement Syntax for CREATE COLLATION CREATE COLLATION lt name gt FOR lt charset gt FROM lt base gt FROM EXTERNAL lt name gt NO PAD PAD SPACE CASE SENSITIVE CASE INSENSITIVE ACCENT SENSITIVE ACCENT INSENSITIVE lt specific attributes gt Note Specific attributes should be separated by semicolon and are case sensitive Examples feel ES CREATE COLLATION UNICODE_ENUS_CI FOR UTF8 FROM UNICODE CASE INSENSITIVE LOCALE en_US 107 International Language Support INTL 2 CREATE COLLATION NEW_COLLATION FOR WIN1252 PAD SPACE NEW_COLLATION should be declared in conf file in the Sroot intl directory The UNICODE Collations The UNICODE collations case sensitive and case insensitive can be applied to any character set that is present in fbintl They are already registered in fbint1 conf but you need to register them in the databases with the desired associations and att
31. More Registry Search on Win32 Servers eee 16 More Optimizer Improvement 1974 haz E eg cece Za iiipin airi iaeei Ea eia r aiei E 17 iv Firebird 2 1 Release Notes 3 Changes to the Firebird AP and GDS saranno eaa sett e aeea eeren e EEEa oTe pA nera ea SE SEE EEEa ra 18 API Application Programming Interface sse eee eee eee 18 User Restrictions in the DPB aves coz erea cecaeeedeevinessccedeseedebene i ia iiaa i a 18 Cl an p of Ibase haa eei Ta tatu AT NL Rea Tagi 19 Lock Timeout for WAIT Transactions osteo nieo i eeraa ada ee erh Er iNe e Esa asi 19 isc_dsql_sql_info Now Includes Relation Aliases sss sees 19 Enhancement to isc_blob_lookup_desc sees ee eee 19 API Tdentifies Clent Versione e E eR OTER dS eT 19 Additions to the isc database infol Structure chaa evade ocre nenir iri aR eok Taha araa 20 Additions to the isc_transaction_info Structure se ee ee ee e eee 20 Improved Services API eocenie i e i a ei ar iaa 21 New Function for Delivering Error Text sss sees 22 Accommodation of New Shutdown lt state gt Parameters sss sees sees ee eee eee ee ee eee eee 22 ODS On Disk Structure Changes oiie aaa aE ASEA AAEE RKA EEN ESEESE E EaR ANERE 23 New ODS Number eeii necssesaeh sues cet he ida da a iee tiiin 23 Size limit for exception messages increased eee 23 New Description Field for Generators svec sx gvg cc Te cyg os Yay ct ce vg cg rR Ogg O YRO 0 n 23 New Description Field for SQL Roles 00ooooococonononoconononananono nono
32. OF can now step through a cursor set selected from a view set just as 1t does in a cursor set output from a SELECT on a table For example FOR SELECT sss FROM MY_VIEW INTO AS CURSOR VIEW_CURSOR DO BEGIN DELETE FROM MY_VIEW WHERE CURRENT OF VIEW_CURSOR 84 Procedural SQL PSQL END Context Variable ROW_COUNT Enhanced D Yemanov ROW_COUNT has been enhanced so that it can now return the number of rows returned by a SELECT state ment For example it can be used to check whether a singleton SELECT INTO statement has performed an assignment BEGIN SELECT COL FROM TAB INTO VAR IF ROW_COUNT 0 THEN EXCEPTION NO_DATA_FOUND END See also its usage in the examples below for explicit PSQL cursors Explicit Cursors D Yemanov It is now possible to declare and use multiple cursors in PSQL Explicit cursors are available in a DSQL EXE CUTE BLOCK structure as well as in stored procedures and triggers Syntax pattern DECLARE VARIABLE lt cursor_name gt CURSOR FOR lt select_statement gt OPEN lt cursor_name gt FETCH lt cursor_name gt INTO lt var_name gt lt var_name gt CLOSE lt cursor_name gt Examples 1 DECLARE RNAME CHAR 31 DECLARE C CURSOR FOR SELECT RDBSRELATION_NAME FROM RDBSRELATIONS BEGIN OPE
33. These days the ability to redirect requests to other servers is dangerous Suppose you have one careful ly protected firebird server access to which is possible from the Internet In a situation where this serv er has unrestricted access to your internal LAN it will work as a gateway for incoming requests like firebird your domain com internal_server private database fdb Knowing the name or IP address of some internal server on your LAN is enough for an intruder he does note even need login access to the external server Such a gateway easily overrides a firewall that is protecting your LAN from outside attack Details of the Security Changes in Firebird 2 Security focus was directed at some recognised weaknesses in Firebird s security from malicious attacks e the lack of brute force resistant passwords encryption in the security database e the ability for any remote user with a valid account to open the security database and read hashes from it especially interesting in combination with the first point e the inability for users to change their own passwords 122 Security e the lack of protection against remote brute forcing of passwords on the server directly Authentication Firebird authentication checks a server wide security database in order to decide whether a database or server connection request is authorised The security database stores the user names and passwords of all authorised login identities
34. Undocumented Items The function isc_spb_rpr_list_limbo_trans was omitted from the IB6 beta documentation It is supported in fbsvcmgr Support for New Services API Items in v 2 1 Two new items that were added to the Services API in Firebird 2 1 are supported by fbsvcmgr e isc_spb_trusted_auth type it as t rusted_auth applies only to Windows It forces Firebird to use Windows trusted authentication e isc_spb_dbname gives the ability to set a database name parameter type as dbname in all service actions related to accessing the security database from a remote client equivalent to supplying the database switch to the gsec utility Note For gsec the database switch is mostly used to specify a remote server you want to administer In Jbsvcmgr the name of the server is already given in the first parameter via the service_mgr symbol so the isc_spb_ dbname parameter is mostly unnecessary Documentation Bugs The format described for some parameters in the InterBase 6 beta documentation are buggy When in trouble treat ibase h as the primary source for the correct form Unsupported functions e Everything to do with licensing was removed from the original InterBase 6 open source code and is therefore not supported either in Firebird or by fbsvcmgr e The old Config file view modification functions have been unsupported since Firebird 1 5 and are not implemented by fbsvcmgr Backup Service Misbehaviour Fixed A P
35. Yemanov Considered more appropriate terminology Removed or Deprecated Parameters CreatelnternalWindow D Yemanov The option CreateInternal Window is no longer required to run multiple server instances and it has been removed DeadThreadsCollection is no longer used A Peshkov The DeadThreadsCollection parameter is no longer used at all Dead threads are now efficiently released on the fly making configuration unnecessary Firebird 2 0 silently ignores this parameter 153 Chapter 16 Firebird 2 Project Teams Developer Table 16 1 Firebird Development Teams Country Major Tasks Dmitry Yemanov Russian Federation Full time database engineer implementor core team leader Alex Peshkov Russian Federation Security features coordinator buildmaster porting authority Claudio Valderrama Chile Code scrutineer bug finder and fixer ISQL enhancements UDF fixer designer and implementor Vladislav Khorsun Arno Brinkman Ukraine The Nether lands DB engineer SQL feature designer implementor Indexing and Optimizer enhancements new DSQL features Adriano dos San tos Fernandes Brazil New international character set handling text and text BLOB enhancements new DSQL features code scrutineer ing Nickolay Samofatov Russian Federa tion Canada Designed and implemented new inline NBackup code fixer and DB engineer during part of V 2 0 development
36. also averts the possibility that those pages will be missed because they are seldom read A sweep of course would find those unused record versions and clear them but the next sweep is not necessarily going to happen soon A further benefit is the reduction in I O because of the higher probability that subse quently requested pages still reside in the buffer cache Between the point where the engine notifies the garbage collector about a page containing unused versions and the point when the garbage collector gets around to reading that page a new transaction could update a record on it The garbage collector cannot clean up this record if this later transaction number is higher than the Oldest Snapshot or is still active The engine again notifies the garbage collector about this page number overriding the earlier notification about it and the garbage will be cleaned at some later time In Firebird 2 0 Superserver both cooperative and background garbage collection are now possible To manage it the new configuration parameter GCPolicy was introduced It can be set to e cooperative garbage collection will be performed only in cooperative mode like Classic and the engine will not track old record versions This reverts GC behaviour to that of IB 5 6 and earlier It is the only option for Classic 12 New in Firebird 2 e background garbage collection will be performed only by background threads as is the case for Firebird 1 5
37. an error if the first character is mul ti byte The argument may be a text BLOB of 32 767 bytes or less select ascii_val x from y ASIN ASIN lt number gt Returns the arc sine of a number The argument to ASIN must be in the range 1 to 1 It returns a result in the range PI 2 to PI 2 select asin x from y ATAN ATAN lt number gt 156 New Built in Functions Function Description Returns the arc tangent of a number Returns a value in the range PI 2 to PI 2 select atan x from y ATAN2 ATAN2 lt number gt lt number gt Returns the arc tangent of the first number the sec ond number Returns a value in the range PI to PI select atan 2 x y from z BIN_AND BIN_AND lt number gt lt number gt Returns the result of a binary AND operation per formed on all arguments select bin_and flags BIN_NOT 1 from x BIN_NOT lt number gt Added in V 2 5 Returns the result of a bitwise NOT operation performed on its argument select bin_not flags from x BIN_OR BIN_OR lt number gt lt number gt Returns the result of a binary OR operation per formed on all arguments select bin_or flagsl flags2 from x BIN_SHL BIN_SHL lt number gt lt number gt Returns the result of a binary shift left operation per formed on the argume
38. available in some cases We prefer that you research such issues in the documentation before you consider posting support questions or bug reports about them From the QA Team Although this is a designated stable release intended for production environments it does introduce much that is new We encourage you to see what you can achieve with these new features and let us know about any deficiency sooner rather than later You are enthusiastically invited to post to the firebird devel list good descriptions of any bugs or beasts you encounter or post bug reports directly to our Issue Tracker Regular sub releases will follow whose quality and timeliness depend heavily on the reports that come in from the field General Notes Bug Reporting e If you think you have discovered a new bug in this release please make a point of reading the instructions for bug reporting in the article How to Report Bugs Effectively at the Firebird Project website e If you think a bug fix hasn t worked or has caused a regression please locate the original bug report in the Tracker reopen it if necessary and follow the instructions below Follow these guidelines as you attempt to analyse your bug 1 Write detailed bug reports supplying the exact build number of your Firebird kit Also provide details of the OS platform Include reproducible test data in your report and post it to our Tracker 2 You are warmly encouraged to make yourse
39. cursors which were not explicitly closed will be closed automatically on exit from the current PSQL block procedure trigger e The ROW_COUNT system variable can be used after each FETCH statement to check whether any row was returned Defaults for Stored Procedure Arguments V Khorsun Defaults can now be declared for stored procedure arguments The syntax is the same as a default value definition for a column or domain except that you can use in place of DEFAULT keyword 86 Procedural SQL PSQL Arguments with default values must be last in the argument list that is you cannot declare an argument that has no default value after any arguments that have been declared with default values The caller must supply the values for all of the arguments preceding any that are to use their defaults For example it is illegal to do something like this supply argl arg2 miss arg3 set arg4 Substitution of default values occurs at run time If you define a procedure with defaults say P1 call it from another procedure say P2 and skip some final defaulted arguments then the default values for P1 will be substituted by the engine at time execution P1 starts This means that if you change the default values for P1 it is not necessary to recompile P2 However it is still necessary to disconnect all client connections as discussed in the Borland InterBase 6 beta Data Definition Guide DataDef pdf in the section
40. declared in the domain by including the TYPE OF keyword in the declaration see the syntax below Syntax data_type lt builtin_data_type gt lt domain_name gt TYPE OF lt domain_name gt Examples CREATE DOMAIN DOM AS INTEGER 83 Procedural SQL PSQL CREATE PROCEDURE SP 11 TYPE OF DOM 12 DOM RETURNS O1 TYPE OF DOM 02 DOM AS DECLARE VARIABLE V1 TYPE OF DOM DECLARE VARIABLE V2 DOM BEGIN END Note A new field RDB VALID_BLR was added in RDB PROCEDURES and RDB TRIGGERS to indicate whether the procedure trigger is valid after an ALTER DOMAIN operation The value of RDB VALID_BLR 1s shown in the ISQL commands SHOW PROCEDURE or SHOW TRIGGER COLLATE in Stored Procedure Parameters and Variables A dos Santos Fernandes V 2 1 Collations can now be applied to PSQL variables including stored procedure parameters NOT NULL Supported in Stored Procedure Parameters and Variables A dos Santos Fernandes V 2 1 The NOT NULL constraint can now be applied to PSQL variables including stored procedure param eters Important If you use this option remember to include adequate exception handling for blocks that have the potential to return NULL to the variables so declared WHERE CURRENT OF Now Allowed for Views Feature request CORE 1213 V 2 1 The cursor operator WHERE CURRENT
41. dev sda7 tmp lv1 0 Further Advice about Raw Devices Although no other specific issues are known at this point about the use of raw device storage for databases keep in mind that e the growth and potential growth of the database is less obvious to end users than one that lives as a file within a file system If control of the production system s environment is out of your direct reach be certain to deploy adequate documentation for any monitoring that will be required 27 Global Improvements in Firebird 2 1 e the very Windows knowledgeable might want to try out the concept of raw device storage on Windows systems It has not been a project priority to explore how it might be achieved on that platform However if you think you know a way to do it please feel welcome to test the idea in your Windows lab and report your observations good or bad or indifferent back to the firebird devel list Tip Maintain your raw devices in aliases conf That way in the event of needing to reconfigure the storage hardware there will be no need to alter any connection strings in your application code Remote Interface Improvements V Khorsun D Yemanov Feature request CORE 971 The remote protocol has been slightly improved to perform better in slow networks In order to achieve this more advanced packets batching is now performed along with some buffer transmission optimizations In a real world test scenario these c
42. e New Built in Functions e Enhancements to Built in Functions s UFO Expression e Improvement in CASTO Behaviour e CAST x as lt domain name gt Expression Arguments for SUBSTRING e DSQL Parsing of Table Names is Stricter e EXECUTE BLOCK Statement e Derived Tables e ROLLBACK RETAIN Syntax e ROWS Syntax 46 Data Manipulation Language DML e a NION DISTINCT mproved Type Coercion in UNIONs NIONS Allowed in ANY ALL IN Subqueries ew NOT DISTINCT Predicate ULL Comparison Rule Relaxed ULLs Ordering Changed e UNION Sets in Subquery Constructs Extended Context Variables e Query Plans Improvements e GROUP or ORDER by Alias Name e GROUP BY Arbitrary Expressions e Order Sets by Implicit Degree Number s NEXT VALUE FOR e Articles 1 Select Statement amp Expression Syntax 2 Data Type of an Aggregation Result 3 A Useful Trick with Date Literals e ZZ Z C Common Table Expressions Vlad Khorsun Based on work by Paul Ruizendaal for Fyracle project v 2 1 A common table expression CTE is like a view that is defined locally within a main query The engine treats a CTE like a derived table and no intermediate materialisation of the data is performed Benefits of CTEs Using CTEs allows you to specify dynamic queries that are recursive The engine begins execution from a non recursive member e For each row evaluated it starts executing each recursive member one by one using th
43. e isc_dpb_verify e isc_dpb_no_db_triggers e isc_dpb_set_db_sql dialect e isc_dpb_sweep_interval e isc_dpb_force_write e isc_dpb_no reserve e isc_dpb_set_db_readonly e isc_dpb_set_page_buffers on Superserver The parameter isc_dpb_set_page_buffers can still be used by ordinary users on Classic and it will set the buffer size temporarily for that user and that session only When used by the SYSDBA on either Superserver or Classic it will change the buffer count in the database header 1 e make a permanent change to the default buffer size 18 Changes to the Firebird API and ODS Important Note for Developers and Users of Data Access Drivers and Tools This change will affect any of the listed DPB parameters that have been explicitly set either by including them in the DPB implementation by default property values or by enabling them in tools and applications that access databases as ordinary users For example a Delphi application that included RESERVE PAGE SPACE TRUE and FORCED WRITES TRUE in its database Params property which caused no problems when the application connected to Firebird 1 x 2 0 x or 2 1 0 2 1 1 now rejects a connection by a non SYSDBA user with ISC ERROR CODE 335544788 Unable to perform operation You must be either SYSDBA or owner of the database Cleanup of ibase h D Yemanov A Peshkov The API header file ibase h has been subjected to a cleanup with the result that public
44. ee de eee 9 KIT O Bikini ele 9 International Language Support oocccccnncnnnnnnnnnnnnnnnnnnnnnnonnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnos 9 Platform SUpport veraces iia dd Meads ieee ach etic take ii A deidad 10 Administrative secies ee ead sae e bag a diodes odia 10 Remote Interface HTHH 10 Derived Tables 7 tscssedisensiei aa tidad 11 PSQL Now Supports Named Cursors 0 c ccsssecececececeeneeeeeecececeaeaaeeeeeececeeeaeuaeeeeneeeeeeaeaqegaes 11 Reimplemented Protocols On Windows sse eee eee 11 Reworking of Garbage Collection sss sese sese eee eee 12 Porting of the Services API to Classic is Complete sese eee eee 13 Lock Timeout for WAIT Transactions opine norteia i aeania aai aiaa aaa 13 New Implementation of String Search Operators sese ee ee ee ee ee ee eee 13 Reworking 0f Updatable Views tico ii et telson SEE 13 Additional Database Shutdown Modes Introduced cococononononononononononononononononononononononenininenes 14 UDEs Improved re NULL Handling comision ari 14 Run time Checking for Concatenation Overflow eee 14 Changes to Synchronisation Logic esse eee 15 Support for G4 bit N Pe T 15 Record Enumeration Limits Increased sss sese sese 15 Debugging Improvement sss esse ee ee ee ee eee eee eee 15 Improved Connection Handling on POSIX Superserver esse eee eee e e e eee 16 PSQL Invariant Tracking Reworked aesae ie Karata dhe RR EaR R chaea GT paea 16 ROLLBACK RETAIN Syntax Support esisiini a o a E a aas 16 No
45. generators which allows the required increment values to be specified at the DDL level Unless there is a vital need to use a step value that is not 1 use of a NEXT VALUE FOR value expression instead of the GEN_ID function is recommended 2 GEN_ID lt name gt 0 allows you to retrieve the current sequence value but it should never be used in insert update statements as it produces a high risk of uniqueness violations in a concurrent environment Articles SELECT Statement amp Expression Syntax Dmitry Yemanov About the semantics e A select statement is used to return data to the caller PSQL module or the client program e Select expressions retrieve parts of data that construct columns that can be in either the final result set or in any of the intermediate sets Select expressions are also known as subqueries Syntax rules lt select statement gt lt select expression gt FOR UPDATE WITH LOCK lt select expression gt lt query specification gt UNION ALL DISTINCT lt query specification gt lt query specification gt SELECT FIRST lt value gt SKIP lt value gt lt select list gt FROM lt table expression list gt WHERE lt search condition gt GROUP BY lt group value list gt HAVI FP O AVING lt group condition gt LAN lt plan item list gt RDER BY lt sort value list gt ROWS lt value gt TO lt value gt lt table expression gt l
46. ibm 1156_P100 1999 ibm 1156 cpibm1156 ibm 1157_P100 1999 ibm 1157 cpibm1157 ibm 1158_P100 1999 ibm 1158 cpibm1158 172 International Character Sets ibm 1160_P100 1999 ibm 1160 cpibm1160 ibm 1164_P100 1999 ibm 1164 cpibm1164 ibm 1364_P110 1997 ibm 1364 cp1364 ibm 1371_P100 1999 ibm 1371 cpibm1371 ibm 1388_P103 2001 ibm 1388 ibm 9580 ibm 1390_P110 2003 ibm 1390 cpibm1390 ibm 1399_P110 2003 ibm 1399 ibm 16684_P110 2003 ibm 16684 ibm 4899_P100 1998 ibm 4899 cpibm4899 ibm 4971_P100 1999 ibm 4971 cpibm4971 ibm 12712_P100 1998 ibm 12712 cpibm12712 ebcdic he ibm 16804_X110 1999 ibm 16804 cpibm16804 ebcdic ar ibm 1137_P100 1999 ibm 1137 ibm 5123_P100 1999 ibm 5123 ibm 8482_P100 1999 ibm 8482 ibm 37_P100 1995 swaplfnl ibm 37 s390 ibm037 s390 ibm 1047_P100 1995 swaplfnl ibm 1047 s390 ibm 1140_P100 1997 swaplfnl ibm 1140 s390 ibm 1142_P100 1997 swaplfnl ibm 1142 s390 ibm 1143_P100 1997 swaplfnl ibm 1143 s390 ibm 1144_P100 1997 swaplfnl ibm 1144 s390 ibm 1145_P100 1997 swaplfnl ibm 1145 s390 4 4 4 ibm 1146_P100 1997 swaplfnl ibm 1146 s390 ibm 1147_P100 1997 swaplfnl ibm 1147 s390 ibm 1148_P100 1997 swaplfnl ibm 1148 s390 ibm 1149_P100 1997 swaplfnl ibm 1149 s390 ibm 1153_P100 1999 swaplfnl ibm 1153 s390 ibm 12712_P100 1998 swaplfnl ibm 12712 s390 ibm 16804_X110 1999 swaplfnl ibm 16804 s390 ebcdic xml us 173 Appendix C Security Database Upgrade for Firebird 2 A P
47. iii en ects waite aks Gece see eek eee ada eft dete Soe eset ida 106 The UNICODE Collation econo ito iaa edo e dd sods 108 Specific Attributes for Collations iociconononinonenoninonenononanetononorononenono i aeia e e i eia RHS 108 Collation Changes Im V Ll indi 109 Metadata Text Conversion iaa A das 110 Repairing Your Metadata Text sss 110 Supported Character TTT 111 11 Administrative Features T 112 Monitoring Tables diia ii ii ii iii eds 112 The Conceptual 112 Scopesand Security ia ie 112 Metadata ies cave eet et sa ord ii Ge e ahi aa aa aia ibi aa 113 Usina A A A tadas 116 Cancel a Running Query sierici i iii iaeiei in 118 Performance Guidelines for Using MON Under Load sss esse 118 More Context Information rS 27T sag te 20T SS arta tous ZES ob HTS dT OTa EEA EE EEEE 119 1D A SCCUEILY ad ate aera a aaah eee ae ue at ts 121 Summary OL Changes munici dida die did ada 121 New security database ti di A A e he ii 121 Using Windows Security to Authenticate Users sese eee eee eee 121 Better password encryption eii ii iq 121 Users can modify their own PasswordS sese 121 Non server access to security database is rejected sse eee 122 vii Firebird 2 1 Release Notes Active protection from brute force attack ese eee eee 122 Vulnerabilities Have Deen closed miii iii e ii E E 122 Details of the Security Changes in Firebird 2 sese 122 AUTHE NH CATON It ciec sah tegscteneetabausyacdosbvensnatetunscbepeetedaes anb
48. interrupting the current innermost loop OLD Context Variables Now Read only D Yemanov The set of OLD context variables available in trigger modules is now read only An attempt to assign a value to OLD something will be rejected Note NEW context variables are now read only in AFTER triggers as well PSQL Stack Trace V Khorsun The API client can now extract a simple stack trace Error Status Vector when an exception occurs during PSQL execution stored procedures or triggers A stack trace is represented by one string 2048 bytes max and consists of all the stored procedure and trigger names starting from the point where the exception occurred out to the outermost caller If the actual trace is longer than 2Kb it is truncated Additional items are appended to the status vector as follows isc_stack_trace isc_arg_string lt string length gt lt string gt isc_stack_trace is a new error code with value of 335544842L Examples Metadata creation CREATE TABLE ERR ID INT NOT NULL PRIMARY KEY NAME VARCHAR 16 CREATE EXCEPTION EX SET TERM CREA OR ALTER PROCEDURE ERR_1 AS BEGIN EXCEPTION EX ID 3 END CREATE OR ALTER TRIGGER ERR_BI FOR ERR BEFORE INSERT AS IF NEW ID 2 THEN EXCEPTION EX ID 2 90 Procedural SQL PS
49. iso 8859 9 latin5 csISOLatin5 iso ir 148 ISO_8859 9 1989 15 8859_9 cp920 920 windows 28599 ECMA 128 ibm 921_P100 1995 ibm 921 iso 8859 13 8859_13 cp921 921 ibm 923_P100 1998 ibm 923 iso 8859 15 Latin 9 19 8859_15 latino csisolatin0 csisolatin9 iso8859_15_fdis cp923 923 windows 28605 ibm 942_P12A 1999 ibm 942 ibm 932 cp932 shift_jis78 sjis78 ibm 942_ VSUB_VPUA ibm 932_ VSUB_VPUA ibm 943_P15A 2003 ibm 943 Shift_JIS MS_Kanji csShiftJIS windows 313 csWindows31J x sjis x ms cp932 cp932 windows 932 cp943c IBM 943C ms932 pck sjis ibm 943_VSUB_VPUA ibm 943_P130 1999 ibm 943 Shift_JIS cp943 943 ibm 943_VASCII_VSUB_VPUA ibm 33722_P12A 1999 ibm 33722 ibm 5050 EUC JP Extended_UNIX_Code_Packed_Format_for_Japanese csEUCPkdFmt Japanese X EUC JP eucjis windows 51932 ibm 33722_VPUA IBM eucJP ibm 33722_P120 1999 ibm 33722 ibm 5050 cp33722 33722 ibm 33722_VASCII_VPUA ibm 954_P101 2000 ibm 954 EUC JP ibm 1373_P100 2002 ibm 1373 windows 950 windows 950 2000 Big5 csBig5 windows 950 x big5 ibm 950_P110 1999 ibm 950 cp950 950 macos 2566 10 2 Big5 HKSCS big5hk HKSCS BIG5 ibm 1375_P100 2003 ibm 1375 Big5 HKSCS ibm 1386_P100 2002 ibm 1386 cp1386 windows 936 ibm 1386_VSUB_VPUA windows 936 2000 GBK CP936 MS936 windows 936 ibm 1383_P110 1999 ibm 1383 GB2312 csGB2312 EUC CN ibm eucCN hp15CN cp1383 1383 ibm 1383_VPUA ibm 5478_P100 1995 ibm 5478 GB_2312 80 chinese iso ir 58 csISO58GB231280 gb2312 1980 GB2312 1980 0 ibm 964_P110 1999 ibm 964 EUC TW ibm eucTW cns11643 cp964 964
50. lt ATAN2 v 2 1 lt lt BACKUP v 2 0 BIN AND v 2 1 lt lt BIN_OR v 2 1 lt lt BIN_SHL v 2 1 lt lt BIN_SHR v 2 1 lt lt BIN_XOR v 2 1 lt lt BLOCK v 2 0 CELL v 2 1 lt lt CEILING v 2 1 lt lt COLLATION v 2 0 COMMENT v 2 0 COS GT D lt lt COSH va 2 1 lt COT E il KE DATEADD v 2 1 lt lt DATEDIFF v 2 1 lt lt DECODE v 2 1 lt lt DIFFERENCE v 2 0 EXP v 2 1 lt lt FLOOR v 2 1 lt lt GEN_UUID v 2 1 lt lt GENERATED v 2 1 lt lt HASH v 2 1 lt lt TIE v 2 0 LIST vs2 1 lt lt LN v 2 1 lt lt LOG v 2 1 lt lt LOG10 v 2 1 lt lt LPAD v 2 1 lt lt MATCHED v 2 1 lt lt ATCHING v 2 1 lt lt MAXVALUE v 2 1 lt lt ILLISECOND v 2 1 lt lt INVALUE v 2 1 lt lt OD v 2 1 lt lt NEXT v 2 0 OVERLAY v 2 1 lt lt PAD v 2 1 lt lt PL PsA Ly lt PLACING v 2 1 lt lt POWER v 2 1 lt lt PRESERVE v 2 1 lt lt RAND v 2 1 lt lt REPLACE v 2 1 lt lt RESTART v 2 0 RETURNING v 2 0 REVERSE v 2 1 lt lt ROUND v 2 1 lt lt RPAD v 2 1 lt lt SCALAR_ARRAY v 2 0 SEQUENCE v 2 0 SIGN v 2 1 lt lt SIN v 2 1 lt lt SINH v 2 1 lt lt SPACE v 2 1 lt lt 94 New Reserved Words an
51. metadata To fix the metadata you need to know in what character set the objects were created The upgrade script will work correctly only if all your metadata was created using the same character set isql path to your database fdb SQL gt input fbroot misc upgrade metatdata metadata_charset_create sql SQL gt select from rdb fix_metadata WIN1252 replace WIN1252 by your charset SQL gt commit LO b KA d d ss The rdb fix_metadata procedure will return the same data as rdb check_metadata but it will change the meta data texts Important It should be run once After this you can remove the upgrade procedures Remove the upgrade procedures 1 isql path to your database fdb 2 SQL gt input Sfbroot misc upgrade metadata metadata_charset_drop sql Supported Character Sets See Appendix B at the end of these notes for a full listing of the supported character sets 111 Chapter 11 Administrative Features Firebird is gradually adding new features to assist in the administration of databases Firebird 2 1 sees the in troduction of a new set of system tables through which administrators can monitor transactions and statements that are active in a database These facilities employ a new v 2 1 DDL feature Global Temporary Tables to provide snapshots Monitoring Tables Dmitry Yemanov Firebird 2 1 introduces the ability to monitor server side activity happening inside a particular da
52. monitoring table the worker process of that connection sends a broadcast notification to other processes requesting an up to date information Those processes react to this request by updating their clusters inside the shared memory region and clearing their ready flags Once every notified process has finished its update the requesting process reads the shared memory region filters the necessary tags according to its user permissions transforms the internal representation into records and fields and populates the in memory monitoring tables cache 118 Administrative Features Any processes that have been idle since the last monitoring exchange have their ready flag clear indicating that they have nothing to update in the shared memory The clear flag means they will be ignored by the notification broadcast and will be exempt from making the next round trip The ready flag is set as soon as something significant changes inside the process and the process starts re sponding to monitoring requests again Locking While coordinating the write read operations the requester holds an exclusive lock that affects all user connec tions that are currently active as well as any connections with attachments in the process of being established Multiple simultaneous monitoring requests are serialized Limitations and Known Issues 1 In a heavily loaded system running Classic monitoring requests may take noticeable
53. rdb relation_id from rdbSdatabase This query performs only one fetch from rdb database instead of evaluating the subquery for every row of rdb relations ROLLBACK RETAIN Syntax Support D Yemanov Firebird 2 0 adds an optional RETAIN clause to the DSQL ROLLBACK statement to make it consistent with COMMIT RETAIN See ROLLBACK RETAIN Syntax in the chapter about DML No More Registry Search on Win32 Servers D Yemanov The root directory lookup path has changed so that server processes on Windows no longer use the Registry 16 New in Firebird 2 Important The command line utilities still check the Registry More Optimizer Improvements A Brinkman Better cost based calculation has been included in the optimizer routines 17 Changes to the Firebird API and ODS API Application Programming Interface Some needed changes have been performed in the Firebird API They include User Restrictions in the DPB A Peshkov From v 2 1 2 onward Several DPB parameters have been made inaccessible to ordinary users closing some dangerous loopholes In some cases they are settings that would alter the database header settings and potentially cause corruptions if not performed under administrator control in others they initiate operations that are otherwise restricted to the SYSDBA They are e isc_dpb_shutdown and isc_dpb_online e isc_dpb_gbak_attach isc_dpb_gfix_attach and isc_dpb_gstat_attach
54. that made implementing enhanced password hashes an absolute requirement A malicious user with user level access to Firebird could easily steal a copy of the security database take it home and quietly brute force the old DES hashes Afterwards he could change data in critical databases stored on that server Firebird 2 is much less vulnerable to this kind of compromise But the embedded POSIX server had one more problem with security its implementation of the Services API calls the command line gsec as normal users do Therefore an embedded user maintenance utility must have full access to security database 126 Security The main reason to restrict direct access to the security database was to protect it from access by old versions of client software Fortuitously it also minimizes the exposure of the embedded Classic on POSIX at the same time since it is quite unlikely that the combination of an old client and the new server would be present on the production box For Any Platform Caution The level of Firebird security is still not satisfactory in one serious respect so please read this section carefully before opening port 3050 to the Internet An important security problem with Firebird still remains unresolved the transmission of poorly encrypted passwords in clear across the network It is not possible to resolve this problem without breaking old clients To put it another way a user who has set his her
55. the implementation is server side only it requires a V 2 1 server and any client Even old clients will work with Firebird 2 1 and see some benefit from the reduction of round trips although the old remote interface unlike the new will still send back big packets for isc_dsql_prepare d Another round trip saver termed defer execute whereby SELECT requests will be held at the point just before execution of the isc_dsql_execute until the next API call on the same statement The benefit of the saved round trip becomes most visible where there is a bunch of SELECT requests whose result set fits into one or two network packets This enhancement takes effect only if both client and server are v 2 1 or higher 28 Global Improvements in Firebird 2 1 Note A faintly possible side effect is that if isc_dsql_execute should happen to fail with a certain excep tion this exception is returned to the client in the response to the API call that was actually responsible 1 e instead of being returned by isc_dsql_execute it would be returned by isc_dsql_fetch isc_dsql_info or whichever API call actually dispatched the op_execute call In most cases the side effect would be transparent it might show up in a case where some error occurred with default values for PSQL parameters or variables and would be noticed as an exception array where the exceptions were delivered in an unusual sequence The changes work with either TC
56. the statement following the terminated loop Syntax pattern lt label_name gt lt loop_statement gt 88 Procedural SQL PSQL LEAVE lt label_name gt where lt loop_statement gt is one of WHILE FOR SELECT FOR EXECUTE STATEMENT Examples 1 FOR SELECT COALESCE RDB SYSTEM_FLAG 0 RDBSRELATION_NAM FROM RDBSRELATIONS ORDER BY 1 INTO RTYPE RNAME DO BEGIN IF RTYPE 0 THEN SUSPEND Fl LEAVE exits current loop CNT 100 Ll WHILE CNT gt 0 DO BEGIN IF CNT lt 50 THEN LEAVE L1 exists WHILE loop CNT CNT 1 END STMT1 SELECT RDBSRELATION_NAME FROM RDBSRELATIONS Tay FOR EXECUTE STATEMENT STMT1 INTO RNAME DO BEGIN STMT2 SELECT RDBSFIELD NAME FROM RDBSRELATION_FIELDS WHERE RDBSRELATION_NAME L2 FOR EXECUTE STATEMENT STMT2 RNAME INTO FNAME H DO BEGIN IF RNAME RDBSDATABASE THEN FAVE L1 exits the outer loop IF RNAME RDBSRELATIONS THEN FAVE L2 exits the inner loop T L LH O 9 bp USPEND END END 89 Procedural SQL PSQL Note Note that LEAVE without an explicit label means
57. triggers on the false assumption that you can incorporate some kind of last rites that will be execute just as your temporary data breathes its last The data and index pages of all GTT instances are placed in separate temporary files Each connection has its own temporary file created the first time the connection references some GTT Note These temporary files are always opened with Forced Writes OFF regardless of the database setting for Forced Writes No limit is placed on the number of GTT instances that can coexist If you have N transactions active simulta neously and each transaction has referenced some GTT then you will have N instances of the GTT Views Enhancements D Yemanov A couple of enhancements were made to view definitions in v 2 1 38 Data Definition Language DDL Use Column Aliases in CREATE VIEW Feature requestCORE 831 v 2 1 Column aliases can now be processed as column names in the view definition Example CREATE VIEW V_TEST AS SELECT ID COL1 AS CODE COL2 AS NAME FROM TAB SQL2003 compliance for CREATE TRIGGER A dos Santos Fernandes Feature request CORE 711 v 2 1 Alternative syntax is now available for CREATE TRIGGER that complies with SQL2003 Syntax Patterns Existing Form create trigger t1 FOR atable active before insert or update as begin end SQL2003 Form create trigger t2 active bef
58. used by the restore process full state full exclusive shutdown no attachments are allowed Note Multi is the default state for shut normal is the default state for online The modes can be switched sequentially 143 Command line Utilities normal lt gt multi lt gt single lt gt full Examples gfix shut single force 0 gfix shut full force 0 gfix online single gfix online You cannot use shut to bring a database one level more online and you cannot use online to make a database more protected an error will be thrown For example these sequence pairs are prohibited fix shut single force 0 gfix shut multi force 0 Q fix online Q gfix online full fix shut force 0 gfix online singl Q Timeout As before the timeout is in seconds In the case of the attach and tran timeouts the timeout determines how long the engine will wait for any attached clients to complete their work and log off The shutdown request should return the SQLCode 902 message shut fail ISC code 335544557 Database shutdown unsuccessful if there are still active attachments when the timeout expires However there is a known issue with the implementation of the new modes A regression occurred whereby the said message is returned but the engine does not revert the database to the online state as it should It affects all versions of Firebird
59. 0 bytes i e overflow This check was performed during the statement prepare using the declared operand sizes and would throw an error for an expressions such as CAST qwe AS VARCHAR 30000 CAST rty AS VARCHAR 30000 From Firebird 2 0 onward this expression throws only a warning at prepare time and the overflow check is repeated at runtime using the sizes of the actual operands The result is that our example will be executed without errors being thrown The isc_concat_overflow exception is now thrown only for actual overflows thus bringing the behaviour of overflow detection for concatenation into line with that for arithmetic operations Changes to Synchronisation Logic N Samofatov 1 Lock contention in the lock manager and in the SuperServer thread pool manager has been reduced sig nificantly 2 Ararerace condition was detected and fixed that could cause Superserver to hang during request processing until the arrival of the next request 3 Lock manager memory dumps have been made more informative and OWN_hung is detected correctly 4 Decoupling of lock manager synchronization objects for different engine instances was implemented Support for 64 bit Platforms A Peshkov N Samofatov Firebird 2 0 will support 64 bit platforms Record Enumeration Limits Increased N Samofatov 40 bit 64 bit internally record enumerators have been introduced to overcome the 30GB table size limit imposed by 32 bit record enumerat
60. 1 D Yemanov SELECT specifications used in subqueries and in INSERT INTO lt insert specification gt SELECT statements gnull gnull gnull gnull gnull gnull can now specify a UNION set order order order order order order by by by by by by asc desc asc nulls first asc nulls last desc nulls last desc nulls first 71 Data Manipulation Language DML New Extensions to UPDATE and DELETE Syntaxes O Loa ROWS specifications and PLAN and ORDER BY clauses can now be used in UPDATE and DELETE state ments Users can now specify explicit plans for UPDATE DELETE statements in order to optimize them manually It is also possible to limit the number of affected rows with a ROWS clause optionally used in combination with an ORDER BY clause to have a sorted recordset Syntax Pattern UPDATE SET WHERE PLAN lt plan items gt ORDER BY lt value list gt ROWS lt value gt TO lt value gt or DELETE FROM PLAN lt plan items gt D ORDER BY lt value list gt ROWS lt value gt TO lt value gt Extended Context Variables A number of new facilities have been added to extend the context information that can be retrieved Sub second Values Enabled for Time and DateTime Variables D Yemanov CURRENT_TIMESTAMP NOW Now Return Milliseconds The context variable CURRENT_TIMES
61. 1047 IBM1047 cpibm1047 ibm 1097_P100 1995 ibm 1097 cp1097 1097 ibm 1112_P100 1995 ibm 1112 cp1112 1112 ibm 1122_P100 1999 ibm 1122 cp1122 1122 ibm 1123_P100 1995 ibm 1123 cp1123 1123 cpibm1123 ibm 1130_P100 1997 ibm 1130 ibm 1132_P100 1998 ibm 1132 ibm 1140_P100 1997 ibm 1140 IBM01140 CCSIDO1140 CPO1140 cp1140 cpibm1140 ebedic us 37 euro ibm 1141_P100 1997 ibm 1141 IBM01141 CCSIDO1141 CPO1141 cp1141 cpibm1141 ebcdic de 273 euro ibm 1142_P100 1997 ibm 1142 IBM01142 CCSIDO1142 CP01142 cp1142 cpibm1142 ebcdic dk 277 euro ebcdic no 277 euro ibm 1143_P100 1997 ibm 1143 IBM01143 CCSID01143 CP01143 cp1143 cpibm1143 ebecdic fi 278 teuro ebcdic se 278 euro ibm 1144_P100 1997 ibm 1144 IBM01144 CCSIDO1144 CP01144 cp1144 cpibm1144 ebcdic it 280 euro ibm 1145_P100 1997 ibm 1145 IBM01145 CCSIDO1145 CP01145 cp1145 cpibm1145 ebcdic es 284 euro ibm 1146_P100 1997 ibm 1146 IBM01146 CCSIDO1146 CP01146 cp1146 cpibm1146 ebcdic gb 285 euro ibm 1147_P100 1997 ibm 1147 IBM01147 CCSIDO1147 CP01147 cp1147 cpibm1147 ebcdic fr 297 euro ibm 1148_P100 1997 ibm 1148 IBM01148 CCSIDO1148 CP01148 cp1148 cpibm1148 ebecdic international 500 euro ibm 1149_P100 1997 ibm 1149 IBM01149 CCSIDO1149 CP01149 cp1149 cpibm1149 ebcdic is 871 euro ibm 1153_P100 1999 ibm 1153 cpibm1153 ibm 1154_P100 1999 ibm 1154 cpibm1154 ibm 1155_P100 1999 ibm 1155 cpibm1155
62. ANSACTION top transaction MONSOLDEST_TRANSACTION local OIT number MONSOLDEST_ACTIVE local OAT number MONSISOLATION_MODE isolation mode 0 consistency 1 concurrency 2 read committed record version 3 read committed no record version MON LOCK_TIMEOUT lock timeout 1 infinite wait 0 no wait N timeout N MONSREAD_ONL Y read only flag MONSAUTO_COMMIT auto commit flag MON AUTO_UNDO auto undo flag MONSSTAT_ ID statistics ID e MONSTOP_TRANSACTION is the upper limit used by the sweeper transaction when advancing the global OIT All transactions above this threshold are considered active It is normally equivalent to the MONS TRANSACTION_ID but COMMIT RETAINING or ROLLBACK RETAINING will cause MON TOP_TRANSACTION to remain unchanged stuck when the transaction ID is incremented 114 Administrative Features MON AUTO_UNDO indicates the auto undo status set for the transaction i e whether a transac tion level savepoint was created The existence of the transaction level savepoint allows changes to be undone if ROLLBACK is called and the transaction is then just committed If this savepoint does not exist or it does exist but the number of changes is very large then an actual ROLLBACK is executed and the the transaction is marked in the TIP as dead MONS STATEMENTS prepared statements MON STATEMENT_ID statement ID MON ATTACHMENT_ID attachment ID MON TRANSACTION_ID transact
63. ASH lt string gt Returns a HASH of a string select hash x from y LEFT LEFT lt string gt lt number gt Returns the substring of a specified length that ap pears at the start of a left to right string select left name char_length name 10 from people where name lik k oe FERNANDES 1 The first position in a string is 1 not 0 2 If the lt number gt argument evaluates to a non integer banker s rounding is applied LN LN lt number gt Returns the natural logarithm of a number select ln x from y LOG LOG lt number gt lt number gt LOG x y returns the logarithm base x of y select log x 10 from y 160 New Built in Functions Function Format Description LOGI0 LOG10 lt number gt Returns the logarithm base ten of a number select logl0 x from y LOWER LOWER lt string gt v 2 0 x Returns the input argument converted to all lower case characters isgql q ch dos850 SQL gt create database test fdb SQL gt create table t c char 1 character set dos850 SQL gt insert into t values A SQL gt insert into t values E SQL gt insert into t values A SQL gt insert into t values E SQL gt select c lower c from t C LOWER A a E e A LPAD LPAD lt string gt lt num LPAD string1 length string2 prepends string2 to ber gt
64. B constant isc_tpb_lock_timeout in the API or in DSQL the LOCK TIMEOUT lt value gt clause of the SET TRANSACTION statement New Implementation of String Search Operators N Samofatov 1 The operators now work correctly with BLOBs of any size Issues with only the first segment being searched and with searches missing matches that straddle segment boundaries are now gone 2 Pattern matching now uses a single pass Knuth Morris Pratt algorithm improving performance when com plex patterns are used 3 The engine no longer crashes when NULL is used as ESCAPE character for LIKE Reworking of Updatable Views D Yemanov A reworking has been done to resolve problems with views that are implicitly updatable but still have update triggers This is an important change that will affect systems written to take advantage of the undocumented mis behaviour in previous versions 13 New in Firebird 2 For details see the notes in the Compatibility chapter of the separate Installation Notes document Additional Database Shutdown Modes Introduced N Samofatov Single user and full shutdown modes are implemented using new state parameters for the gfix shut and gfix online commands Syntax Pattern gfix lt command gt lt state gt lt options gt lt command gt gt shut online lt state gt normal multi single full lt options gt force lt timeout gt tran attach s normal state onlin
65. Changes to Results Returned from SUBSTRING V 2 1 x To conform with standards the character length of the result of applying SUBSTRINGO to a VAR CHAR or CHAR is now a VARCHAR of the same character length declared for or deduced from the value in the first argument In Firebird 2 0 and 1 5 the returned value was a CHAR with the same character length as the declared or implied value of the first argument too That implementation could become a bug in Firebird 2 0 under conditions where the input string was a CHAR and the FOR argument was presented as an expression whose result was not known at the point where memory was prepared to receive the result string The V 2 1 change addresses that It is not necessary to redefine any PSQL variables you have declared to receive the results from SUBSTRINGO It is still correct to declare its size just big enough to accommodate the actual data returned Just be sure that any FOR argument that is an expression cannot resolve to an integer that is larger than the number of characters declared for your variable GOTCHA for BLOBs Clearly a text BLOB being of indeterminate character length cannot fit into a paradigm that populates a string of known maximum dimension Therefore the result returned from applying SUBSTRING to a text BLOB is nota VARCHAR O as previously but a text BLOB This change can break existing PSQL and expression code e Watch out for overflows Take particular care with CASTs and
66. DER BY COL5 ROWS 1 Points to Note 1 When lt expr2 gt is omitted then ROWS lt expr1 gt is semantically equivalent to FIRST lt expr1 gt When both lt expr1 gt and lt expr2 gt are used then ROWS lt expr1 gt TO lt expr2 gt means the same as FIRST lt expr2 gt lt expr1 gt 1 SKIP lt expr1 gt 1 2 There is nothing that is semantically equivalent to a SKIP clause used without a FIRST clause Enhancements to UNION Handling The rules for UNION queries have been improved as follows UNION DISTINCT Keyword Implementation D Yemanov UNION DISTINCT is now allowed as a synonym for simple UNION in accordance with the SQL 99 specifi cation It is a minor change DISTINCT is the default mode according to the standard Formerly Firebird did not support the explicit inclusion of the optional keyword DISTINCT Syntax Pattern UNION DISTINCT ALL Improved Type Coercion in UNIONs A Brinkman Automatic resolution of the data type of the result of an aggregation over values of compatible data types such as case expressions and columns at the same position in a union query expression now uses smarter rules Syntax Rules Let DTS be the set of data types over which we must determine the final result data type 1 All of the data types in DTS shall be comparable 2 Case a If any of the data types in DTS is character string then 1 If any of the data types in DTS is variable length character str
67. Details here Remote Interface v 2 1 The remote protocol has been slightly improved to perform better in slow networks once drivers are updated to utilise the changes Testing showed that API round trips were reduced by about 50 percent resulting in about 40 per cent fewer TCP round trips See Remote Interface Improvement 10 New in Firebird 2 Derived Tables A Brinkman Implemented support for derived tables in DSQL subqueries in FROM clause as defined by SQL200X A derived table is a set derived from a dynamic SELECT statement Derived tables can be nested if required to build complex queries and they can be involved in joins as though they were normal tables or views More details under Derived Tables in the DML chapter PSQL Now Supports Named Cursors D Yemanov Multiple named i e explicit cursors are now supported in PSQL and in DSQL EXECUTE BLOCK statements More information in the PSQL chapter Explicit Cursors Reimplemented Protocols on Windows D Yemanov Two significant changes have been made to the Windows only protocols Local Protocol XNET Firebird 2 0 has replaced the former implementation of the local transport protocol often referred to as IPC or IPServer with a new one named XNET It serves exactly the same goal to provide an efficient way to connect to server located on the same machine as the connecting client without a remote node name in the connection string The new implementati
68. EAD DEPT D DEPARTMENT H INDENT FROM DEPARTMENT D JOIN DEPT_TREE ON D HEAD_DEPT H DEPT_NO SELECT D DEPT_NO D INDENT D DEPARTMENT AS DEPARTMENT B_1993 BUDGET AS B_1993 B_1994 BUDGET AS B_1994 B_1995 BUDGET AS B_1995 B_1996 BUDGET AS B_1996 FROM DEPT_TREE D LEFT JOIN DEPT_YEAR_BUDGET B_1993 ON D DEPT_NO B_1993 DEPT_NO AND B_1993 FISCAL_YEAR 1993 LEFT JOIN DEPT_YEAR_BUDGET B_1994 ON D DEPT_NO B_1994 DEPT_NO AND B_1994 FISCAL_YEAR 1994 LEFT JOIN DEPT_YEAR_BUDGET B_1995 ON D DEPT_NO B_1995 DEPT_NO AND B_1995 FISCAL_YEAR 1995 LEFT JOIN DEPT_YEAR_BUDGET B_1996 ON D DEPT_NO B_1996 DEPT_NO AND B_1996 FISCAL YEAR 1996 The LIST Function Oleg Loa Dmitry Yemanov v 2 1 This function returns a string result with the concatenated non NULL values from a group It returns NULL if there are no non NULL values 50 Data Manipulation Language DML Format lt list function gt LIST ALL DISTINCT lt value expression gt lt delimiter value gt lt delimiter value gt lt string literal gt lt parameter gt lt variable gt Syntax Rules 1 If neither ALL nor DISTINCT is specified ALL is implied 2 If lt delimiter value gt is
69. EEEE A 32 Immediate Release of External Files sss sese 32 Synchronization of DSQL metadata cache objects in Classic Server sese ee eee eee 33 BEOB Improvement saaTs TER TTS tt in tara 33 Type Fl g for Stored e UT 33 Help for Getting Core Dumps On Linux sese sees ee ee eee eee eee 33 gt Data Definition Language DDD aia e Tea eyes Rr 3 QUICK EINK Sis a A E E wade ode vad A te chats Backes wis cents Biss 35 Database RT A NT 35 Global Temporary Tables egas coronan detay oR Ys YER dane R ORN VRA dOd VE 37 Views Enhancement 45 54 252 oeeo elpanna tapea riain iaa aapa aoai pa iaoei bipi 38 Firebird 2 1 Release Notes SQL2003 compliance for CREATE TRIGGER sss 39 SQL2003 Compliant Alternative for Computed Fields sss sese 40 CREATE SEQUENCE T 40 REVOKE ADMIN OPTION updated cd la 41 SET DROP DEFAULT Clauses for ALTER TABLE sese 41 Syntaxes for Changing Exceptions seisine a ta iras id 42 ALTER EXTERNAL FUNCTION ntir atenei reira aaa ia caged a ees 42 COMMENT Statements eeir ieie pi byes e E A ER AERA EET E A E T E ld 42 Extensions to CREATE VIEW Specification sese ee ee ee ee ee eee 43 RECREATE TRIGGER Statement Implemented sss sees eee ee eee eee 43 PESA cdeduancestantsereiane 43 6 Data Manipulation Language DML sss sese eee 46 QUICK E ista rta Is oh be Deas dae Ia A Sethu was Ai iaa iria 46 Common Table Expressions ui ias id idilio 47 The LIST Function siii pare dganas TTT 50 The RETURNING Clausen A He Nata a toes 51
70. EN UPDATE SET lt assignment list gt lt merge when not matched gt WHEN NOT MATCHED THEN INSERT lt left paren gt lt column list gt lt right paren gt VALUES lt left paren gt lt value list gt lt right paren gt Rules for MERGE 1 Atleast one of lt merge when matched gt and lt merge when not matched gt should be specified 2 Neither should be specified more than once 54 Data Manipulation Language DML Note A right join is made between the INTO and USING tables using the condition UPDATE is called when a matching record exists in the left INTO table otherwise INSERT is called If no record is returned from the join INSERT is not called Example MERGE INTO customers c USING SELECT FROM customers_delta WHERE id gt 10 cd ON c id cd id WHEN MATCHED THEN UPDATE SE name cd name WHEN NOT MATCHED THEN INSERT id name VALUES cd id cd name New JOIN Types Adriano dos Santos Fernandes v 2 1 Two new JOIN types are introduced the NAMED COLUMNS join and its close relative the NATURAL join Syntax and Rules lt named columns join gt lt table reference gt lt join type gt JOIN lt table reference gt USING lt column list gt lt natural join gt lt table reference gt NATURAL lt join type gt JOIN
71. Firebird 2 1 2 File fragmentation Allocating disk space in relatively small chunks can lead to significant fragmentation of the database file at file system level impairing the performance of large scans as during a backup for example The Solution The solution is to introduce some rules and rationales to govern page writes according to the state of available disk space as follows a Each newly allocated page writes to disk immediately before returning to the engine If the page cannot be written then the allocation does not happen the PIP bit remains uncleared and the appropriate I O error is raised Corruption cannot arise since it is guaranteed that all dirty pages in cache have disk space allocated and can be written safely Because this change adds an extra write for each newly allocated page some performance penalty is to be expected To mitigate the effect writes of newly allocated pages are performed in batches of up to 128 Kb and Firebird keeps track of the number of these initialized pages in the PIP header Note A page that has been allocated released and re allocated is already space in hand meaning that no further verification is required in order to initialize it Hence a newly allocated page is subjected to this double write only if it is a block that has never been allocated before To address the issue of file fragmentation Firebird now uses the appropriate call to the API of
72. From v 2 0 Superserver has active protection to make a brute force attack more difficult After a few failed attempts to log in the user and IP address are locked for a few seconds denying any attempt to log in with that particular user name OR from that particular IP address for a brief period No setup or configuration is required for this feature It is active automatically as soon as the SuperServer starts up Using Windows Security to Authenticate Users Alex Peshkov V 2 1 From Firebird 2 1 onward Windows Trusted User security can be applied for authenticating Firebird users on a Windows host The Trusted User s security context is passed to the Firebird server and if it succeeds it is used to determine the Firebird security user name 124 Security Simply omitting the user and password parameters from the DPB SPB will automatically cause Windows Trust ed User authentication to be applied in almost all cases See the Environment section below for exceptions Illustration Suppose you have logged in to the Windows server SRV as user John If you connect to server SRV with isql without specifying a Firebird user name and password isql srv employee and do SQL gt select CURRENT_USER from rdb database you will get something like USER SRV John SQL Privileges Windows users can be granted rights to access database objects and roles in the same way as regular Firebird us
73. GTT with ON COMMIT DELETE ROWS option has RDB RELATION_TYPE 5 Note For the full list of values see RDB STYPES Structural Feature Support The same structural features that you can apply to regular tables indexes triggers field level and table level constraints are also available to a GTT with certain restrictions on how GTTs and regular tables can interrelate a references between persistent and temporary tables are forbidden b A GTT with ON COMMIT PRESERVE ROWS cannot have a reference on a GTT with ON COMMIT DELETE ROWS c A domain constraint cannot have a reference to any GTT Implementation Notes An instance of a GTT a set of data rows created by and visible within the given connection or transaction 1s created when the GTT is referenced for the first time usually at statement prepare time Each instance has its own private set of pages on which data and indexes are stored The data rows and indexes have the same physical storage layout as permanent tables When the connection or transaction ends all pages of a GTT instance are released immediately It is similar to what happens when a DROP TABLE is performed except that the metadata definition is retained of course This is much quicker than the traditional row by row delete garbage collection of deleted record versions Note This method of deletion does not cause DELETE triggers to fire so do not be tempted to define Before or After Delete
74. M278 cp278 EBCDIC CP DK EBCDIC CP NO csIBM278 ebcdic sv cpibm278 278 bedic cp fi bcdic cp s 171 International Character Sets ibm 280_P100 1995 ibm 280 IBM280 CP280 ebcdic cp it csIBM280 cpibm280 280 ibm 284_P100 1995 ibm 284 IBM284 CP284 ebcdic cp es csIBM284 cpibm284 284 ibm 285_P100 1995 ibm 285 IBM285 CP285 ebcdic cp gb csIBM285 ebcdic gb cpibm285 285 ibm 290_P100 1995 ibm 290 IBM290 cp290 EBCDIC JP kana csIBM290 ibm 297_P100 1995 ibm 297 IBM297 cp297 ebcdic cp fr csIBM297 cpibm297 297 ibm 420_X120 1999 ibm 420 IBM420 cp420 ebcdic cp arl csIBM420 420 ibm 424_P100 1995 ibm 424 IBM424 cp424 ebcdic cp he csIBM424 424 ibm 500_P100 1995 ibm 500 IBM500 CP500 ebcdic cp be csIBM500 ebcdic cp ch cpibm500 500 ibm 803_P100 1999 ibm 803 cp803 ibm 838_P100 1995 ibm 838 IBM Thai csIBMThai cp838 838 ibm 9030 ibm 870_P100 1995 ibm 870 IBM870 CP870 ebcdic cp roec ebcdic cp yu csIBM870 ibm 871_P100 1995 ibm 871 IBM871 ebcdic cp is csIBM871 CP871 ebcdic is cpibm871 871 ibm 875_P100 1995 ibm 875 IBM875 cp875 875 ibm 918_P100 1995 ibm 918 IBM918 CP918 ebcdic cp ar2 csIBM918 ibm 930_P120 1999 ibm 930 ibm 5026 cp930 cpibm930 930 ibm 933_P110 1995 ibm 933 cp933 cpibm933 933 ibm 935_P110 1999 ibm 935 cp935 cpibm935 935 ibm 937_P110 1999 ibm 937 cp937 cpibm937 937 ibm 939_P120 1999 ibm 939 ibm 931 ibm 5035 cp939 939 ibm 1025_P100 1995 ibm 1025 cp1025 1025 ibm 1026_P100 1995 ibm 1026 IBM1026 CP1026 csIBM1026 1026 ibm 1047_P100 1995 ibm
75. MT MONSTIMESTAMP FROM MONSATTACHMENTS AT JOIN MONSSTA NTS STMT ON ATT MONSATTACHMENT_ID STMT MONSATTACHMENT_ID WHERE ATT MONSATTACHMENT_ID lt gt CURRENT_CONNECTION AND STMT MONSSTATE Retrieve call stacks for all connections WITH RECURSIVE HEAD AS ECT CAL CAL CAL CAL ONSC ERE FRO CALL MONSSTATEMENT_ID L MONSCALL_ID L MONSOBJECT_NAME L MONSOBJECT_TYPE ALL STACK CALL MONSCALLER_ID IS NULL UNION ALL SELECT CALL CALL CALL CALL ONSSTATEMENT_ID MONSCALL_ID O O NSOBJECT_NAME NSOBJECT_TYPE Mi FROM MONSCALL JOIN HEAD ON CALL STAC ELECT MONSATTACHMENT_1 MONSOBJECT_NAME ONSCALL K CALL ER_ID HI EAD MONSCALL_ ID 117 Administrative Features MONSOBJECT_TYPE FROM HEAD JOIN MONSSTATEMENTS STMT ON STMT MONSSTATEMENT_ID HEAD MONSSTATEMENT_ID WHERE STMT MONSATTACHMENT_ID lt gt CURRENT_CONNECTION Cancel a Running Query Runaway and long running queries can now be cancelled from a separate connection There is no API function call directed at this feature It will be up to the SysAdmin SYSDBA or Owner to make use of the data available in the monitoring tables and devise an appropriate mechanism for
76. N C WHILE 1 1 DO BEGIN E FETCH C INTO RNAMI IF ROW_COUNT 0 THEN HAVE SUSPEND END 85 Procedural SQL PSQL CLOSE END za a se LC EC LARE RNAME CHAR 31 EC LARE FNAME CHAR 31 ECLARE C CURSOR FOR SELECT RDBSFIELD_NAME FROM RDBSRELATION_FIELDS iw is WHERE RDBSRELATION_NAME RNAME ORDER BY RDBSFIELD_POSITION BEGIN FOR SELECT RDBSRELATION_NAME FROM RDBSRELATIONS INTO RNAME DO BEGIN OPEN C FETCH C INTO FNAME CLOSE C SUSPEND END END Note e Cursor declaration is allowed only in the declaration section of a PSQL block procedure trigger as with any regular local variable declaration e Cursor names are required to be unique in the given context They must not conflict with the name of another cursor that is announced via the AS CURSOR clause by a FOR SELECT cursor However a cursor can share its name with any other type of variable within the same context since the operations available to each are different e Positioned updates and deletes with cursors using the WHERE CURRENT OF clause are allowed e Attempts to fetch from or close a FOR SELECT cursor are prohibited e Attempts to open a cursor that is already open or to fetch from or close a cursor that is already closed will fail e All
77. NET are performed in the context of the client security token Since the server serves every connection according to its client security credentials this means that if the client machine is running some OS user from an NT domain that user should have appropriate permissions to access the physical database file UDF libraries etc on the server filesystem This situation is contrary to what is generally regarded as proper for a client server setup with a protected database Such impersonation has been removed in Firebird 2 0 WNET connections are now truly client server and behave the same way as TCP ones i e with no presumptions with regard to the rights of OS users Reworking of Garbage Collection V Khorsun Since Firebird 1 0 and earlier the Superserver engine has performed background garbage collection maintain ing information about each new record version produced by an UPDATE or DELETE statement As soon as the old versions are no longer interesting i e when they become older than the Oldest Snapshot transaction seen in the gstat header output the engine signals for them to be removed by the garbage collector Background GC eliminates the need to re read the pages containing these versions viaa SELECT COUNT FROM aTable or other table scanning query from a user as occurs in Classic and in versions of InterBase prior to v 6 0 This earlier GC mechanism is known as cooperative garbage collection Background GC
78. P IP or NetBEUI They are backward compatible so existing client code will not be broken However when you are using a driver layer that implements its own interpretation of the remote protocol such as the Jaybird JDBC and the FirebirdClient NET drivers your existing code will not enable the enhancements unless you usedrivers are updated API Changes XSQLVAR A dos Santos Fernandes The identifier of the connection character set or when the connection character set is NONE the BLOB character set is now passed in the XSOLVAR sqlscale item of text BLOBs Optimization Optimization for Multiple Index Scans V Khorsun Feature request CORE 1069 An optimization was done for index scanning when more than one index is to be scanned with AND conjunctions Optimize sparse bitmap operations V Khorsun Feature request CORE 1070 Optimization was done for sparse bitmap operations set test and clear when values are mostly consecutive 29 Global Improvements in Firebird 2 1 Configuration and Tuning Increased Lock Manager Limits amp Defaults D Yemanov Feature requests CORE 958 and CORE 937 e the maximum number of hash slots is raised from 2048 to 65 536 Because the actual setting should be a prime number the exact supported maximum is 65 521 the biggest prime number below 65 536 The minimum is 101 s the new default number of hash slots is 1009 e the default lock table size has been increase
79. PART AS VARCHAR 32 CHARACTER SE UNICODE_FSS DEFAULT _UNICODE_FSS CREATE DOMAIN RDB GID AS INTEGER CREATE DOMAIN RDBSPASSWD AS VARCHAR 64 CHARACTER SET BINARY CREATE DOMAIN RDBSUID AS INTEGER CREATE DOMAIN RDBSUSER_NAME AS VARCHAR 128 CHARACTER SET UNICODE_FSS CREATE DOMAIN RDBSUSER_PRIVILEGE AS INTEGER COMMIT CREATE TABLE RDBSUSERS RDBSUSER_NAME RDBSUSER_NAME NOT NULL PRIMARY KEY local system user name for setuid for file permissions RDB SYS_USER_NAME RDBSUSER_NAME RDB GROUP_NAME RDBSUSER_NAME RDBSUID RDBSUID RDB GID RDBSGID RDBSPASSWD RDBSPASSWD SEE NOTE BELOW Privilege level of user mark a user as having DBA privilege RDBSPRIVIL EG G RDBSCOMMEN RDBSFIRST_ T NAM El RDBSMIDD E N D RDB LAST_N AM E RDBSUSER_PRIVILEGE RDBSCOMMENT RDBSNAME_PART RDBSNAME_PART RDBSNAME_PART 175 Security Database Upgrade for Firebird 2 COMMIT CREATE VIEW USERS USER_NAME SYS_USER_NAME GROUP_NAME UID GID PASSWD PRIVILEGE COMMENT FIRST_NAME MIDDLE _NAME LAST_NAME FULL_NAME AS E T T SELECT RDBSUSER_NAME RDBSSYS_USER_NAME RDBSGROUP_NAME RDBSUID RDBSGID RDBSPASSWD RDBSPRIVILEGE RDBSCOM fs zZ H
80. Paul Beach France Release Manager HP UX builds MacOS Builds Solaris Builds Pavel Cisar Czech Re public QA tools designer coordinator maintainer and developer of Python drivers for Firebird Philippe Makowski France QA tester Paul Reeves France Win32 installers and builds Sean Leyne Canada Bugtracker organizer Dimitrios Ioannides Greece New Jira bugtracker implementor Ann Harrison U S A Frequent technical advisor Jim Starkey U S A Frequent architectural advisor occasional bug fixer Roman Rokytskyy Germany Jaybird implementor and co coordinator Ryan Baldwin U K Jaybird Type 2 driver developer Evgeny Putilin Russian Java stored procedures implementation Federation 154 Firebird 2 Project Teams Developer Country Major Tasks Carlos Guzman Alvarez Spain Developer and coordinator of NET providers for Firebird until 2007 Jiri Cincura Czech Re public Developer and coordinator of NET providers from January 2008 Alexander Potapchenko Russia Coordinator of ODBC JDBC driver for Firebird David Rushby d U S A Developer and coordinator of KInterbase Python interface for Firebird until his accidental death in July 2007 Konstantin Kuznetsov Russian Federation Solaris Intel builds Paul Vinkenoog Norman Dunbar The Nether lands U K Coordinator Firebird
81. QL HEN EXECUTE PROCEDURE ERR_1 END CREATE OR ALTER PROCEDURE ERR_2 AS BEGIN INSERT INTO ERR VALUES 3 333 END User exception from a trigger SQL INSERT INTO ERR VALUES 2 2 Statement failed SQLCODE 836 exception 3 1D 2 At trigger ERR_BI User exception from a procedure called by a trigger SQL INSERT INTO ERR VALUES 3 3 Statement failed SOLCODE 836 exception 3 ID 3 At procedure ERR_1 At trigger ERR_BI Run time exception occurring in trigger division by zero SQL INSERT INTO ERR VALUES 4 4 Statement failed SQLCODE 802 arithmetic exception numeric overflow or string truncation At trigger ERR_BI User exception from procedure SQL EXECUTE PROCEDURE ERR_1 Statement failed SQLCODE 836 exception 3 ID 3 At procedure ERR_1 User exception from a procedure with a deeper call stack SQL EXECUTE PROCEDURE ERR_2 Statement failed SQLCODE 836 exception 3 ID 3 At procedure ERR_1 At trigger ERR_BI 91 Procedural SQL PSQL At procedure ERR_2 Call a UDF as a Void Function Procedure N Samofatov In PSQL supported UDFs e g RDB SET_CONTEXT can be called as though they
82. R MINUTE SECOND MILLISECOND 1 Returns a positive value if the second value is greater than the first one negative when the first one is greater or zero when they are equal 2 Comparison of date with time values is invalid 3 YEAR MONTH and DAY cannot be used with time values 4 HOUR MINUTE SECOND and MILLISECOND cannot be used with date values 5 All timestamp_part values can be used with timestamp values Example select datediff DAY cast TOMORROW as date 10 current_date as datediffresult from rdb database DECODE See below DECODE is a shortcut for a CASE WHEN ELSE expression Format DECODE lt expression gt lt search gt lt result gt 159 New Built in Functions Function Format Description lt search gt lt result gt lt default gt Example select decode state 0 deleted 1 active unknown from things EXP EXP lt number gt Returns the exponential e to the argument select exp x from y FLOOR FLOOR lt number gt Returns a value representing the largest integer that is less than or equal to the input argument 1 select floor val from x 2 select floor 2 1 floor 2 1 from rdb database returns 2 3 GEN_UUID GEN_UUIDO no arguments Returns a universal unique number insert into records id value gen_uuid HASH H
83. RANDO no argument Returns a random number between 0 and 1 select from x order by rand REPLACE REPLACE lt stringtosearch gt Replaces all occurrences of lt findstring gt in lt string lt findstring gt lt replstring gt tosearch gt with lt replstring gt select replace x from y REVERSE REVERSE lt value gt Returns a string in reverse order Useful function for creating an expression index that indexes strings from right to left create index people_email on people computed by reverse email select from people where reverse email starting with reverse br RIGHT RIGHT lt string gt lt number gt Returns the substring of the specified length from the right hand end of a string select right rdb relation_name char_length rdb relation_name 4 from rdb relations where rdb relation_name like RDBS 163 New Built in Functions Function Format Description ROUND ROUND lt number gt Returns a number rounded to the specified scale lt number gt Example select round salary 1 1 0 from people If the scale second parameter is negative or is omitted the integer part of the value is rounded E g ROUND 123 456 1 returns 120 000 RPAD lt string1 gt Appends lt string2 gt to the end of lt string1 gt until the lt length gt lt string2 gt length of the result string becomes
84. TAMP and the date time literal NOW will now return the sub second time part in milliseconds Seconds Precision Enabled for CURRENT_TIME and CURRENT_TIMESTAMP CURRENT_TIME and CURRENT_TIMESTAMP now optionally allow seconds precision The feature is available in both DSQL and PSQL Syntax Pattern 72 Data Manipulation Language DML CURREN IME lt seconds precision gt CURREN IMESTAMP lt seconds precision gt Examples 1 SELECT CURREN IME FROM RDBSDATABASE CURRENT_TIME 3 FROM RDBSDATABASE 3 SELECT CURREN IMESTAMP 3 FROM RDBSDATABASE N un HA Q se Note 1 The maximum possible precision is 3 which means accuracy of 1 1000 second one millisecond This accuracy may be improved in the future versions 2 If no seconds precision is specified the following values are implicit e 0 for CURRENT_TIME e 3 for CURRENT_TIMESTAMP New System Functions to Retrieve Context Variables N Samofatov Values of context variables can now be obtained using the system functions RDB GET_CONTEXT and RDB SET_CONTEXT These new built in functions give access through SQL to some information about the current connection and current transaction They also provide a mechanism to retrieve user context data and associate it with the transaction or connection Syntax Pattern RDBSSET_CONTEXT lt namespace gt lt variable gt lt value
85. TI LEVEL Uses more than one level for ordering purposes Valid for collations of narrow character sets Format MULTI LEVEL 01 1 Example MULTI LEVEL 1 SPECIALS FIRST Order special characters spaces symbols etc before alphanumeric characters Valid for collations of narrow character sets Format SPECIALS FIRST 0 1 Example SPECIALS FIRST 1 Collation Changes in V 2 1 Spanish 109 International Language Support INTL ES ES as well as the new ES_ES_CI_AI collation automatically uses attributes DIS ABLE COMPRESSIONS 1 SPECIALS FIRST 1 Note The attributes are stored at database creation time so the changes do not apply to databases with ODS lt 11 1 The ES_ES_CL AI collation was standardised to current usage UTF 8 Case insensitive collation for UTF 8 See feature request CORE 972 Metadata Text Conversion Firebird versions 2 0 x had two problems related to character sets and metadata extraction 1 When creating or altering objects text associated with metadata was not transliterated from the client char acter set to the system UNICODE_FSS character set of these BLOB columns Instead raw bytes were stored there The types of text affected were PSQL sources descriptions text associated with constraints and defaults and so on Note Even in the current version 2 1 x the problem can still occur if CREATE or ALTER operations are performed with the connection characte
86. The default value is 1 true Redirection A Peshkov Parameter for controlling redirection of remote requests It controls the multi hop capability that was broken in InterBase 6 and is restored in Firebird 2 About Multi hop When you attach to some database using multiple hosts in the connection string only the last host in this list is the one that opens the database The other hosts act as intermediate gateways on port gds_db Previously when working this feature was available unconditionally Now it can be configured Remote redirection is turned off by default Caution If you are considering enabling multi hop capability please study the Warning text in the chapter on Security and in the documentation for this parameter in the firebird conf file 151 New Configuration Parameters and Changes GCPolicy V Khorsun Garbage collection policy It is now possible to choose the policy for garbage collection on SuperServer The possible settings are cooperative background and combined as explained in the notes for GPolicy in firebird conf Not applicable to Classic which supports only cooperative garbage collection OldColumnNaming P Reeves The parameter OldColumnNaming has been ported forward from Firebird 1 5 3 This parameter allows users to revert to pre V1 5 column naming behaviour in SELECT expressions The installation default is 0 disabled If it is enabled the engine will not attempt t
87. UPDATE OR INSERT Statement iii in dt 53 MERGE Statement ice iaa ad aclara Polares adds ER E e 54 NARNIA EE e 55 INSERT with Defaults ci ccsccisuete sio SastokeetidusbiotousyeansbadstuashorsebacacdtustsatepatcsaSatabechoaee 56 BLOB Subtype 1 Compatibility with VARCHAR sse esse eee 57 Full Equality Comparisons Between BLOBS oococcccnonononononononenononononononononononononononononinineneninos 57 RDB DB_KEY Returns NULL in Outer Joins sse 57 Sorting on BLOB and ARRAY Columns is Restored sss 58 Built in PUN CtrOms esti cied cava e voiitn auld da aa idad patrias atada or cis ages 58 Functions Enhanced in V20 E 5 wg A A aia wd eee at 60 DSQL Parsing of Table Names is Stricter sese eee eee 62 EXECUTE BLOCK Statement vice senorita tena eps onnpedeasonseeessentabdcccasteescdshenedesastpescenes 64 Derived A O 65 ROLEBACK RETAIN Syntax tica nas isis di a in dia id 67 SONS O A hi ta iea cnbdeccnassccbdahbveddeshodebdesnsvdeaaapeceblaghovesdte 67 Enhancements to UNION Handling ccccccncnononononononononononononononononononononononononononononononinineninenos 68 Enhancements to NULE 6816 curia laa dd fi tadas es 69 Subqueries and INSERT Statements Can Now Accept UNION Sets sese ee eee eee 71 New Extensions to UPDATE and DELETE Syntaxes sese eee eee 72 Ext nded Context Variables sooner cinste eee ie a eaa ipee liika 72 Improvements in Handling User specified Query Plans sss 76 Improvements in Sorts ricas a ria y a Ea 78 NEXT VALUE FOR Expres
88. _NAME VARCHAR 32 CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS LAST NAME VARCHAR 32 CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS 174 Security Database Upgrade for Firebird 2 COMMIT 2 Save users data INSERT INTO UTMP USER_NAME SYS_USER_NAME GROUP_NAME UID GID PRIVILEGE COMMENT FIRST_NAME MIDDLE_NAME LAST_NAME PASSWD SELECT USER_NAME SYS_USER_NAME GROUP_NAME UID GID PRIVILEGE COMMENT FIRST_NAME MIDDLE_NAME LAST_NAME PASSWD FROM USERS COMMIT 3 drop old tables and domains DROP TABLE USERS DROP TABLE HOST_INFO COMMIT DROP DOMAIN COMMENT DROP DOMAIN NAME PART DROP DOMAIN GID DROP DOMAIN HOST_KEY DROP DOMAIN HOST_NAME DROP DOMAIN PASSWD DROP DOMAIN UID DROP DOMAIN USER_NAME DROP DOMAIN PRIVILEGE COMMIT 4 create new objects in database CREATE DOMAIN RDBS COMMENT AS BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SE UNICODE_FSS CREATE DOMAIN RDBS NAME_
89. a This is a command line option M2 to send the statistics and plans to the same output file as the other output via the o utput switch When the user specifies that the output should be sent to a file two possibilities have existed for years either e at the command line the switch o followed by a file name is used e the command OUT put followed by a file name is used either in a batch session or in the interactive isql shell In either case simply passing the command OUTput is enough to have the output returned to the console However although error messages are shown in the console they are not output to the file The m command line switch was added to meld mix the error messages with the normal output to wherever the output was being redirected This left still another case statistics about operations SET STATs command and SQL plans as the server returns them SET PLAN and SET PLANONLY commands have been treated as diagnostic messages and as such were always sent to the console What the m2 command line switch does is to ensure that stats and plans information go to the same file the output has been redirected to Note Neither m nor m2 has an interactive counterpart through a SET command They are for use only as com mand line isql options 139 Command line Utilities r2 to Pass a Case Sensitive Role Name C Valderrama The sole objective of this parameter is to specify a case sensi
90. anced to enable NULL handling to be requested explicitly The solution therefore is to restrict a request for SQL NULL signaling to UDFs that are known to be capable of dealing with the new scenario To avoid adding more keywords the NULL keyword is appended to the UDF parameter type and no other change is required Example declare external function sample int null returns int by value 146 External Functions UDFs If you are already using functions from ib_udf and want to take advantage of null signaling and null recognition in some functions you should connect to your desired database run the script misc up grade ib_udf_upgrade sq l that is in the Firebird directory and commit afterwards Caution It is recommended to do this when no other users are connected to the database The code in the listed functions in that script has been modified to recognize null only when NULL is signaled by the engine Therefore starting with FB v2 rtrim 1trim and several other string functions no longer assume that an empty string means a NULL string The functions won t crash if you don t upgrade they will simply be unable to detect NULL If you have never used ib_udf in your database and want to do so you should connect to the database run the script udf ib_udf2 sql preferably when no other users are connected and commit afterwards Note e Note the 2 at the end of the name e The origina
91. anipulation Language DML BEGIN INSERT INTO T VALUES X SELECT FROM T INTO Y SUSPEND END The preprocessed SQL is EXECUTE BLOCK X INTEGER RETURNS Y VARCHAR AS DECLARE V INTEGER BEGIN INSERT INTO T VALUES X SELECT FROM T INTO Y SUSPEND END Derived Tables A Brinkman Implemented support for derived tables in DSQL subqueries in FROM clause as defined by SQL200X A derived table is a set derived from a dynamic SELECT statement Derived tables can be nested if required to build complex queries and they can be involved in joins as though they were normal tables or views Syntax Pattern SELECT select list gt A FRO lt table reference list gt lt table reference list gt lt table reference gt lt comma gt lt table reference gt lt table reference gt lt table primary gt lt joined table gt lt table primary gt lt table gt AS lt correlation name gt lt derived table gt lt derived table gt lt query expression gt AS lt correlation name gt lt left paren gt lt derived column list gt lt right paren gt lt derived column list gt lt column name gt lt comma gt lt column name gt Examples a Simple derived table 65 Data Manipulation Language DML
92. as not reached Existential predicators IN ANY SOME ALL e Search predicators CONTAINING STARTING WITH LIKE Important The predicating expression must not resolve to more than 32 767 bytes e A LIST expression Note that prior to v 2 1 4 the last part of the result may be truncated an effect that applies to native VARCHAR columns also Full Equality Comparisons Between BLOBs v 2 0 x Comparison can be performed on the entire content of a text BLOB RDB DB_KEY Returns NULL in Outer Joins A dos Santos Fernandes Feature request CORE 979 v 2 1 By some anomaly the physical RDB DB_KEY has always returned a value on every output row when specified in an outer join thereby making a test predicated on the assumption that a non match returns NULL in all fields return False when it ought to return True Now RDB DB_KEY returns NULL when it should do so 57 Data Manipulation Language DML Sorting on BLOB and ARRAY Columns is Restored Dmitry Yemanov v 2 1 In earlier pre release versions of Firebird 2 1 changes were introduced to reject sorts ORDER BY GROUP BY and SELECT DISTINCT operations at prepare time if the sort clause implicitly or explicitly involved sorting on a BLOB or ARRAY column That change was reversed in the RC2 pre release version not because it was wrong but because so many users complained that it broke the behaviour of legacy applications Important Thi
93. ations and UNIONs for output columns are performed over a mix of comparable data types the engine has to choose one data type for the result The developer often has to prepare a variable or buffer for such results and is mystified when a request returns a data type exception The rules followed by the engine in determining the data type for an output column under these conditions are explained here 1 Let DTS be the set of data types over which we must determine the final result data type 2 All of the data types in DTS shall be comparable 3 In the case that a any of the data types in DTS is character string i If all data types in DTS are fixed length character strings then the result is also a fixed length character string otherwise the result is a variable length character string The resulting string length in characters is equal to the maximum of the lengths in characters of the data types in DTS ii The character set and collation used are taken from the data type of the first character string in DTS b all of the data types in DTS are exact numeric the result data type is exact numeric with scale equal to the maximum of the scales of the data types in DTS and precision equal to the maximum precision of all data types in DTS 81 Data Manipulation Language DML c any data type in DTS is approximate numeric each data type in DTS must be numeric otherwise an error is thrown d any data type in DTS is a date t
94. bquery and from the updating table UPDATE TableA SE FieldA SELECT SUM A FieldB FROM TableA A WHERE A FieldID TableA FieldID Note In Firebird it is possible to provide an alias in an update statement Although many other database vendors do not support it this capability should help those developers who have requested it to make Firebird s SQL more interchangeable with SQL database products that do support it This example did not run correctly in Firebird 1 5 and earlier RDBSRELATIONS RDBSRE R2 RDBSRELATION_NAME FRO RDBSRELATIONS JOIN RDBSRELATIONS R2 ON R2 RDBSRELATION_NAME RDBSRELATIONS RDBSRELATION_NAME LATION_NAME If RDBSRELATIONS contained 90 records it would return 90 90 8100 records but in Firebird 2 it will correctly return 90 records This would except with a syntax error in Firebird 1 5 but is possible in Firebird 2 SELECT RDBSRELATION_NAME FROM RDBSDATABASE Fl RDBSRELATIONS 63 Data Manipulation Language DML b Ambiguity checking in subqueries the query below would run in Firebird 1 5 without reporting an ambiguity but will report it in Firebird 2 SELECT SELECT FIRST 1 RDBSRELATION_NAME FROM RDBSRELATIONS R1 JOIN RDBSRELATIONS R2 ON R2 RDBSRELATION_NAME R1 RDBSRELATION_NAME
95. che If Firebird is configured to use a cache that is large relative to the available RAM and Forced Writes is on this cache duplication drains resources for little or no benefit Often when the operating system tries to cache a big file it moves the Firebird page cache to the swap causing intensive unnecessary paging In practice if the Firebird page cache size for Superserver is set to more than 80 per cent of the available RAM resource problems will be extreme Note Filesystem caching is of some benefit on file writes but only if Forced Writes is OFF which is not recom mended for most conditions Now Superserver on both Windows and POSIX can be configured by a new configuration parameter Max FileSystemCache to prevent or enable filesystem caching It may provide the benefit of freeing more memory for other operations such as sorting and where there are multiple databases reduce the demands made on host resources Note For Classic there is no escaping filesystem caching For details of the MaxFileSystemCache parameter see MaxFileSystemCache Other Global Improvements Garbage Collector Rationalisation V Khorsun Feature request CORE 1071 The background garbage collector process was reading all back versions of records on a page including those created by active transactions Since back versions of active records cannot be considered for garbage collection it was wasteful to read them
96. cii tlg ISCII version 7 x iscii ka windows 57008 iscii knd ISCII version 8 x iscii ma windows 57009 iscii mlm gb18030 ibm 1392 windows 54936 LMBCS 1 lmbcs LMBCS 2 LMBCS 3 LMBCS 4 LMBCS 5 LMBCS 6 LMBCS 8 LMBCS 11 LMBCS 16 LMBCS 17 LMBCS 18 LMBCS 19 ibm 367_P100 1995 ibm 367 IBM367 ibm 912_P100 1995 ibm 912 iso 8859 2 ISO_8859 2 1987 latin2 csISOLatin2 iso ir 101 ibm 913_P1 100 2000 ibm 91 12 88592 cp912 912 windows 28592 13 iso 8859 3 ISO_8859 3 1988 latin3 csISOLatin3 iso ir 109 13 8859_3 cp913 913 windows 28593 ibm 914_P1 iso ir 1 ibm 915_P1 iso ir 1 100 1995 ibm 91 100 1995 ibm 91 ibm 1089_P100 1995 ibm 1 iso ir 1 ibm 813_P1 cp813 81 ibm 916_P1 iso ir 1 cp916 91 100 1995 ibm 81 100 1995 ibm 91 110 150_8859 4 144 150_8859 5 127 150 9899 6 1987 14 iso 8859 4 latin4 csISOLa 1988 14 8859_4 cp914 914 windows 28594 15 iso 8859 5 cyrillic csISOLatinCyrillic 1988 8859_5 cp915 915 windows 28595 1089 iso 8859 6 arabic csISOLatinArabic ECMA 114 ASMO 708 8859_ 1089 windows 28596 ISO 8859 6 I ISO 8859 6 E k8 E 13 iso 8859 7 gr ECMA 118 csISOLatinGreek iso ir 126 ISO_8859 7 1987 8859_7 13 windows 28597 L6 iso 8859 8 hebrew csISOLa tin4 6 cp1089 kenge 0T_928 tinHebrew 138 ISO_8859 8 1988 ISO 8859 8 I ISO 8859 8 L6 windows 28598 E 8859_8 169 International Character Sets ibm 920_P100 1995 ibm 920
97. collation for ISO8859_1 character set A dos Santos Fer nandes Brazil Portuguese collation for ISO8859_1 character set A dos Santos Fer nandes P H Al banez 1508859_2 ISO_PLK Polish collation for ISO8859_2 character set J Glowacki A dos Santos Fernandes KOI8R KOI8_RU Russian language character set and dictio nary collation O Loa A Karyakin KOI8U KOI8U_UA Ukrainian language character set and dic tionary collation O Loa A Karyakin WIN1250 BS_BA Bosnian language collation for WIN1250 character set F Hasovic WIN_CZ_AI Czech language accent insensitive colla tion for WIN1250 character set I Prenosil A dos Santos Fernandes WIN_CZ_CL Al Czech language case and accent insensi tive collation for WIN1250 character set I Prenosil A dos Santos Fernandes WIN1252 WIN_PTBR Brazil Portuguese collation for WIN1252 character set A dos Santos Fer nandes P H Al banez WIN1257 WIN1257_LV Latvian dictionary collation O Loa A Karyakin WIN1257_LT Lithuanian dictionary collation O Loa A Karyakin N WIN1257_EE Estonian dictionary collation O Loa A Karyakin 167 International Character Sets Character Set Collation Description Implemented By WIN1258 n a Vietnamese language subset of charset WIN1258 Nguyen The Phuong A dos Santos Fernandes
98. concatenations e Inv 2 1 x sub releases prior to v 2 1 4 pay attention to memory usage when assigning to temporary BLOBs in loops The engine formerly allocated a minimum of one database page of memory for each tempo rary BLOB regardless of its actual size The implementation was improved in v 2 1 4 see tracker ticket CORE 1658 DSQL Parsing of Table Names is Stricter A Brinkman Alias handling and ambiguous field detecting have been improved In summary 1 When a table alias is provided for a table either that alias or no alias must be used It is no longer valid to supply only the table name 2 Ambiguity checking now checks first for ambiguity at the current level of scope making it valid in some conditions for columns to be used without qualifiers at a higher scope level Examples 1 When an alias is present it must be used or no alias at all is allowed a This query was allowed in FB1 5 and earlier versions RDBSRELATIONS RDBSRELATION_NAME FRO RDBSRELATIONS R 62 Data Manipulation Language DML but will now correctly report an error that the field RDB RELATIONS RDBSRELATION_NAME could not be found Use this preferred SELECT R RDBSRELATION_NAME FRO RDBSRELATIONS R or this statement RDBSRELATION_NAME FRO RDBSRELATIONS R The statement below will now correctly use the FieldID from the su
99. curity database structure The Firebird 2 security database is named security2 fdb For user authentication it has a new table named RDBSUSERS that stores the new hashed passwords A view over this table replaces the old USERS table and enables users to change their own passwords The DDL for the new structures can be found in Appendix C gsec in Firebird 2 Special measures were thus taken to make remote connection to the security database completely impossible Don t be surprised if some old program fails on attempting direct access this is by design Users information may now be accessed only through the Services API and the equivalent internal access to services now implemented in the isc_user_ API functions Protection from Brute force Hacking Current high speed CPUs and fast WAN connections make it possible to try to brute force Firebird server users passwords This is especially dangerous for Superserver which since Firebird 1 5 performs user authentication very fast Classic is slower since it has to create new process for each connection attach to the security database within that connection and compile a request to the table RDB USERS before validating login and password Superserver caches the connection and request thus enabling a much faster user validation Given the 8 byte maximum length of the traditional Firebird password the brute force hacker had a reasonable chance to break into the Firebird installation
100. d Changes SORT v 2 1 TAN v 2 1 TANH v 2 1 lt lt EMPORARY v 2 1 TRUNC v 2 1 WEEK v 2 1 lt lt lt lt lt lt lt lt Keywords No Longer Reserved ACTION CASCADE REE_IT ESTRICT v 2 0 OLE v 2 0 YPE v 2 0 v 2 0 v 2 0 v 2 0 EKDAY w 2 0 EARDAY v 2 0 43 Hw yy No Longer Reserved as Keywords BASENAME v 2 0 GROUP_COMMIT_WAIT NUM_LOG_BUFS CACHE v 2 0 OGP TLE v 2 0 RAW_PARTITIONS CHECK_POINT_LEN LOG_BUF_SIZE v 2 0 v 2 0 95 Chapter 9 Indexing amp Optimizations Optimizations in V 2 1 Optimization improvements in v 2 1 include v 2 1 Economising on Indexed Reads for MIN and MAX Indexed MIN MAX aggregates would produce three indexed reads instead of the expected single read So with an ASC index on the non nullable COL the query SELECT MIN COL FROM TAB should be completely equivalent to SELECT FIRST 1 COL FROM TAB RDER BY 1 ASC O with both performing a single record read However formerly the first query required three indexed reads while the second one required just the expected single read Now they both resolve to a single read The same optimization applies to the MAX function when mapped to a DESC index Improved PLAN Clause D Yemanov V 2 0 x A PLAN clause optional
101. d to 1 Mb on all platforms Page sizes of 1K and 2K Deprecated D Yemanov Feature request CORE 969 Page sizes of 1K and 2K are deprecated as inefficient Note The small page restriction applies to new databases only Old ones can be attached to regardless of their page size Enlarge Disk Allocation Chunks V Khorsun Feature request CORE 1229 Until v 2 1 Firebird had no special rules about allocating disk space for database file pages Because of depen dencies between pages that it maintains itself to service its careful write strategy it has just written to new ly allocated pages in indeterminate order For databases using ODS 11 1 and higher Firebird servers from v 2 1 onward use a different algorithm for allocating disk space to address two recognised problems associated with the existing approach 1 Corruptions resulting from out of space conditions on disk The indeterminate order of writes can give rise to a situation that at a point where the page cache contains a large number of dirty pages and Firebird needs to request space for a new page in the process of writing them out there is insufficient disk space to fulfil the request Under such conditions it often happens that the administrator decides to shut down the database in order to make some more disk space available causing the remaining dirty pages in the cache to be lost This leads to serious corruptions 30 Global Improvements in
102. ded on non leaf level pages and duplicates key entries are sorted by this number Expression Indexes O Loa D Yemanov A Karyakin Arbitrary expressions applied to values in a row in dynamic DDL can now be indexed allowing indexed access paths to be available for search predicates that are based on expressions Syntax Pattern CREATE UNIQUE ASC ENDING DESC ENDING INDEX lt index name gt ON lt table name gt COMPUTED BY lt value expression gt Examples 1 99 Indexing amp Optimizations CREATE INDEX IDX1 ON T1 COMPUTED BY UPPER COL1 COLLATE PXW_CYRL COMMIT SELECT FROM T1 WHERE UPPER COL1 COLLATE PXW_CYRL OUAA PLAN T1 INDEX 1DX1 2 CREATE INDEX IDX2 ON T2 COMPUTED BY EXTRACT YEAR FROM COL2 EXTRACT MONTH FROM COL2 COMMIT IR a SELECT FROM T2 ORDER BY EXTRACT YEAR FROM COL2 EXTRACT MONTH FROM COL2 PLAN T2 ORDER IDX2 Note 1 The expression used in the predicate must match exactly the expression used in the index declaration in order to allow the engine to choose an indexed access path The given index will not be available for any retrieval or sorting operation if the expressions do not match 2 Expression indices have exactly the same features and limitations a
103. documentation project documentation writer and tools developer implementor Documentation writer Pavel Menshchikov Russian Federation Documentation translator Tomneko Hayashi Japan Documentation translator Umberto Mimmo Masotti Italy Documentation translator Olivier Mascia Belgium IBPP C interface developer re implementor of Win32 in stallation services Oleg Loa Russian Federation Contributor Grzegorz Prokopski Hungary Debian builds Erik Kunze Germany SINIX Z port raw device enablement Helen Borrie Australia Release notes editor Chief of Thought Police 155 Appendix A New Built in Functions Firebird 2 1 Function Format Description ABS ABS lt number gt Returns the absolute value of a number select abs amount from transactions ACOS ACOS lt number gt Returns the arc cosine of a number Argument to ACOS must be in the range 1 to 1 Returns a value in the range 0 to PI select acos x from y ASCII_CHAR ASCII_CHAR lt number gt Returns the ASCII character with the specified code The argument to ASCIL_ CHAR must be in the range 0 to 255 The result is returned in character set NONE select ascii_char x from y ASCII_VAL ASCI_VAL lt string gt Returns the ASCII code of the first character of the specified string 1 Returns 0 if the string is empty 2 Throws
104. e Cannot attach to password 127 Security database It is not a bug it is by design A security database from an earlier Firebird version cannot be used directly in Firebird 2 0 or higher The newly structured security database is named security2 fdb In order to be able to use an old security database it is necessary to run the upgrade script security_database sql that is inthe upgrade sub directory of your Firebird server installation Note A copy of the script appears in Appendix C Doing the Security Database Upgrade To do the upgrade follow these steps 1 Put your old security database in some place known to you but not in Firebird s new home directory Keep a copy available at all times 2 Start Firebird 2 using its new native security2 fdb 3 Convert your old security database to ODS11 i e backup and restore it using Firebird 2 0 Without this step running the security_database sql script will fail 4 Connect the restored security database as SYSDBA and run the script 5 Stop the Firebird service 6 Copy the upgraded database to the Firebird 2 home directory as security2 fdb 7 Restart Firebird Now you should be able to connect to the Firebird 2 server using your old logins and passwords Nullability of RDB PASSWD In pre 2 0 versions of Firebird it was possible to have a user with NULL password From v 2 0 onward the RDB PASSWD field in the security database is constrain
105. e current values from the outer row as parameters e Ifthe currently executing instance of a recursive member produces no rows execution loops back one level and gets the next row from the outer result set The memory and CPU overhead of a recursive CTE is much less than that of an equivalent recursive stored procedure Recursion Limit Currently the recursion depth is limited to a hard coded value of 1024 Syntax and Rules for CTEs select select_expr for_update_clause lock_clause select_expr 47 Data Manipulation Language DML with_clause select_expr_body order_clause rows_clause select_expr_body order_clause rows_clause with_clause WITH RECURSIVE with_list WITH with_list with_list with_item with_item with_list with_item symbol_table_alias_name derived_column_list AS select_expr select_expr_body query_term select_expr_body UNION distinct_noise query_term select_expr_body UNION ALL query_term A less formal representation Hy E O a feds E 2 Q E B TAB1 TAB2 WHERE Rules for Non Recursive CTEs e Multiple table expressions can be defined in one query e Any clause legal in a SELECT specification is legal in table expressions e Table expressions can reference one another e References between expressions should not have loops e Table expressions can be used within any part of the main query o
106. e database e multi state multi user shutdown mode the legacy one unlimited attachments of SYSDBA owner are al lowed e single state single user shutdown only one attachment is allowed used by the restore process e full state full exclusive shutdown no attachments are allowed For more details refer to the section on Gfix New Shutdown Modes in the Utilities chapter For a list of shutdown state flag symbols and an example of usage see Shutdown State in the API UDFs Improved re NULL Handling C Valderrama Signalling SQL NULL e Ability to signal SQL NULL via a NULL pointer see Signal SQL NULL in UDFs e External function library ib_udf upgraded to allow the string functions ASCIL CHAR LOWER LPAD LTRIM RPAD RTIM SUBSTR and SUBSTRLEN to return NULL and have it interpreted correctly The script ib_udf_upgrade sql can be applied to pre v 2 databases that have these functions declared to upgrade them to work with the upgraded library This script should be used only when you are using the new ib_udf library with Firebird v2 and operation requests are modified to anticipate nulls Run time Checking for Concatenation Overflow D Yemanov Compile time checking for concatenation overflow has been replaced by run time checking 14 New in Firebird 2 From Firebird 1 0 onward concatenation operations have been checked for the possibility that the resulting string might exceed the string length limit of 32 00
107. e in both DSQL and PSQL e Complete database monitoring is available to SYSDBA and the database owner 112 Administrative Features e Regular users are restricted to the information about their own attachments only other attachments are invisible to them Monitor Multiple Attachments D Yemanov Under v 2 1 0 and 2 1 1 a non SYSDBA user with more than one attachment could monitor only the one that was CURRENT_CONNECTION From v 2 1 2 Improvement CORE 2233 a non SYSDBA user with more than one attachment can monitor all its own attachments Metadata MONS DATABASE connected database MON DATABASE_NAME database pathname or alias MON PAGE_SIZE page size MONSODS_MAJOR major ODS version MONSODS_MINOR minor ODS version MONSOLDEST_TRANSACTION OIT number MONSOLDEST_ACTIVE OAT number MONSOLDEST_SNAPSHOT OST number MONSNEXT_TRANSACTION next transaction number MON PAGE_BUFFERS number of pages allocated in the cache MON SQL_DIALECT SQL dialect of the database MONSSHUTDOWN_MODE current shutdown mode 0 online 1 multi user shutdown 2 single user shutdown 3 full shutdown MON SWEEP_INTERVAL sweep interval MONSREAD_ONL Y read only flag MON FORCED_WRITES sync writes flag MONSRESERVE_SPACE reserve space flag MONSCREATION_DATE creation date time MON PAGES number of pages allocated on disk MON BACKUP_STATE current physical backup state 0 normal 1 stalled
108. e metadata of the GTT are shared among all connections and transactions There are two kinds of GTT e with data that persists for the lifetime of connection in which the specified GTT was referenced and e with data that persists only for the lifetime of the referencing transaction Syntax and Rules for GTTs CREATE GLOBAL TEMPORARY TABLE ON COMMIT lt DELETE PRESERVE gt ROWS Creates the metadata for the temporary table in the system catalogue The clause ON COMMIT sets the kind of temporary table ON COMMIT PRESERVE ROWS Data left in the given table after the end of the transaction remain in database until the connection ends ON COMMIT DELETE ROWS Data in the given table are deleted from the database immediately after the end of the transaction ON COM MIT DELETE ROWS is used by default if the optional clause ON COMMIT is not specified CREATE GLOBAL TEMPORARY TABLE is a regular DDL statement that is processed by the engine the same way as a CREATE TABLE statement is processed Accordingly it not possible to create or drop a GTT within a stored procedure or trigger Relation Type GTT definitions are distinguished in the system catalogue from one another and from permanent tables by the value of RDBSRELATIONS RDBSRELATION_TYPE A GTT with ON COMMIT PRESERVE ROWS option has RDB RELATION_TYPE 4 37 Data Definition Language DDL A
109. e to let isql continue executing a batch of commands after an error has happened Therefore the b ail option will cause script execution to stop at the first error it detects No further statements in the input script will be executed and isql will return an error code to the operating system Most cases have been covered but if you find some error that is not recognized by isql you should inform the project as this is a feature in progress Currently there is no differentiation by error code any non zero return code should be interpreted as failure Depending on other options like o m and m2 isql will show the error message on screen or will send it to a file Some Features Even if isql is executing nested scripts it will cease all execution and will return to the operating system when it detects an error Nested scripts happen when a script A is used as isql input but in turn A contains an INPUT command to load script B an so on Isql doesn t check for direct or indirect recursion thus if the programmer makes a mistake and script A loads itself or loads script B that in turn loads script A again isql will run until it exhaust memory or an error is returned from the database at whose point bail if activated will stop all activity DML errors will be caught when being prepared or executed depending on the type of error In many cases 1sql will return the line number of a DML statement that fails during execution of a scrip
110. eadavansnabe lagsshou sted sthassuseeecenaSeteussy 123 gsec im A NO 124 Protection from Brute force Hacking sese eee eee 124 Using Windows Security to Authenticate Users sse eee eee 124 SOE S Ne HTHH 125 Administrators E R aged E A d ad vane bay peu AR Bra y cand aay o 125 Configuration Parameter Authentication sss sees ee ee ee ee ee ee eee eee eee 125 Forcing Trusted Authentication sese 126 Classic Server on POSIX moneam ee on aeaiee iaa eNA tot AEE ae cie Dees R ARER 126 For Any Platform NN 127 RTE HITESH T TTT 127 isc_service_query wrongly revealed the full database file spec sese 127 Any user could view the server log through the Services API 127 Dealing with the New Security Database i e oiei Reyhan reaR e iani enient ERT t 127 Doing the Security Database Upgrade sss see eee 128 13 Command line BIST T 130 General Enhancements 2 ccsssasietedsaiteataSaetiuetsasapeninasubugdstoteat Sadi ea e i ae ip arteei enap eost 130 Utilities Support for Database Triggers sese ee ee ee eee 130 Password Hiding more diesen ites TTT 130 Firebird ale TTT 130 New Command line Utility fbsvemgr sese eee ee eee eee 130 Backup Tools unid A eaa E 134 New On line Incremental Backup miii id a idas 134 gbak Backup Porting Restore Utility sese 136 ISOE Query Wty onolike e odisea E EE EE ido is icases ss 137 NA NN 138 New Commands and Enhancements sese eee 140 gsec Authentication Manager see 143 PSEC return Code si ede was A A A AAA A ot
111. earch criterion is used on other columns which are indexed then a range index scan is performed in advance and every fetched key has its row id validated against the resulting bitmap Then a data page is read and the required row is fetched Note Note that a navigational scan incurs random page I O as reads are not optimized A sort operation performs an external sort of the given stream retrieval A join can be performed either via the nested loops algorithm JOIN plan or via the sort merge algorithm MERGE plan e An inner nested loop join may contain as many streams as are required to be joined All of them are equivalent e An outer nested loops join always operates with two streams so you ll see nested JOIN clauses in the case of 3 or more outer streams joined A sort merge operates with two input streams which are sorted beforehand then merged in a single run Examples n miri ELECT RDB RELATION_NAME ROM RDB RELATIONS rd 77 Data Manipulation Language DML WHERE RDBSRELATION_NAME LIKE RDBS PLAN RDBSRELATIONS NATURAL ORDER BY RDBSRELATION_NAME SELECT R RDBSRELATION_NAME RF RDBSFIELD_NAME FROM RDBSRELATIONS R JOIN RDBSRELATION_FIELDS RE ON R RDBSRELATION_NAME RE RDBSRELATION_NAME PLAN MERGE SORT R NATURAL SORT RF NATURAL N
112. ecified BOTH is assumed If lt trim character gt is not specified is assumed If lt trim specification gt and or lt trim character gt is specified FROM should be specified If lt trim specification gt and lt trim character gt is not specified FROM should not be specified If a text BLOB substring is specified as lt value expres sion gt the value returned must not exceed 32 767 bytes Example A select rdb relation_name trim leading RDB from rdb relation_name from rdb relations where rdbSrelation_name starting with RDBS Example B select trim rdb relation_name is a system table from rdb relations where rdb system_flag 1 TRUNC lt number gt Returns the integral part up to the specified scale of lt number gt a number 1 select trunc x from y 2 select trunc 2 8 trunc 2 8 165 New Built in Functions Function Format Description from rdb database returns 2 2 3 select trunc 987 65 1 trunc 987 65 1 from rdb database returns 987 60 980 00 166 Appendix B International Character Sets A dos Santos Fernandes amp Others New Character Sets and Collations Implemented The following new character set and or collation implementations have been added in Firebird 2 releases Character Set Collation Description Implemented By 1508859_1 ES_ES_CL Al Spanish language case and accent insen sitive
113. ections are still alive when the specified timeout expires the engine returns a message indicating that the shutdown was unsuccessful However instead of leaving the database in the online state as it should it puts the database into some uncertain off line state and further connections are refused It affects all versions of Firebird up to and including v 2 0 5 and v 2 1 3 and all v 2 5 alphas betas and release candidates See Tracker ticket CORE 2846 Patch Release 2 1 2 Firebird 2 1 2 corrects a significant number of bugs that have shown up since the v 2 1 and 2 1 1 releases Important Change to API DPB Parameters A long standing legacy loophole in the handling of DPB parameters enabled ordinary users to make connection settings that could lead to database corruptions or give them access to SYSDBA only operations The loophole has been closed a change that could affect several existing applications database tools and connectivity layers drivers components Details are in Chapter 3 Changes to the Firebird API and ODS It also includes three minor improvements that have come from user requests Requested in Tracker CORE 2243 was an improvement to reduce the duplication and confusion of the Mi crosoft Visual C 8 runtime assembly rules for XP Server2003 and Vista platforms that have wracked the installation of the v 2 1 x Windows packages so far Vlad Khorsun has met that request for this sub release For more in
114. ed as NOT NULL However to avoid exceptions during the upgrade process the field is created as nullable by the upgrade script If you are really sure you have no empty passwords in the security database you may modify the script yourself For example you may edit the line RDBSPASSWD RDBSPASSWD to be RDBSPASSWD RDBSPASSWD NOT NULL 128 Security Caution with LegacyHash As long as you configure LegacyHash 1 in firebird conf Firebird s security does not work com pletely To set this right it is necessary to do as follows 1 2 Change the SYSDBA password Have the users change their passwords in 2 0 each user can change his or her own password Set LegacyHash back to default value of 0 or comment it out Stop and restart Firebird for the configuration change to take effect 129 Chapter 13 Command line Utilities General Enhancements Utilities Support for Database Triggers V 2 1 A new parameter was added to gbak nbackup and isql to suppress Database Triggers from running It is available only to the database owner and SYSDBA gbak nodbtriggers isql nodbtriggers nbackup T Password Hiding Alex Peshkov Command line utilities that take a password parameter are vulnerable to password sniffing especially when the utility is run from a script As a step towards hardening against this on POSIX platforms the PASSWORD argument now displays the process list as an asterisk where
115. ed tables in an attempt to find matches in empty tables INSERT with Defaults D Yemanov Feature request v 2 1 It is now possible to INSERT without supplying values if Before Insert triggers and or declared defaults are available for every column and none is dependent on the presence of any supplied NEW value Example 56 Data Manipulation Language DML INSERT INTO lt table gt DEFAULT VALUES RETURNING lt values gt BLOB Subtype 1 Compatibility with VARCHAR A dos Santos Fernandes v 2 1 At various levels of evaluation the engine now treats text BLOBs that are within the 32 765 byte string size limit as though they were VARCHARs Operations that now allow text BLOBs to behave like strings are e Assignments conversions and concatenations Il operator e Operators lt gt gt lt gt lt BETWEEN IS NOT DISTINCT FROM e Functions CAST BIT_LENGTH CHAR ACTER _LENGTH OCTET_LENGTH LEFT RIGHT HASH LOWER UPPER LPAD RPAD TRIM OVERLAY REPLACE POSITION REVERSE MINVALUE MAXVALUE SUBSTRING Note Carefully SUBSTRING when applied to a text BLOB now returns a text BLOB as its result instead of the VAR CHAR result that was implemented previously This change has the potential to break expressions in exist ing client and PSQL code If the FOR argument is absent the BLOB returned will be no greater than 32 767 bytes even if the end of the string w
116. ements 5 SHOW COLLATION and collation extraction in ISQL 6 Verifying that text blobs are well formed 7 Transliterating text blobs automatically ICU Character Sets All non wide and ASCII based character sets present in ICU can be used by Firebird 2 1 To reduce the size of the distribution kit we customize ICU to include only essential character sets and any for which there was a specific feature request If the character set you need is not included you can replace the ICU libraries with another complete module found at our site or already installed in your operating system Registering an ICU Character Set Module To use an alternative character set module you need to register it in two places 1 in the server s language configuration file int 1 fbintl conf 2 in each database that is going to use it Registering a Character Set on the Server Using a text editor register the module in int 1 fbint1 conf as follows lt charset NAME gt 106 International Language Support INTL intl_module fbintl collation NAME REAL NAME lt charset gt Registering a Character Set in a Database To register the module in a database run the procedure sp_register_character_set the source for which can be found in misc int1 sql beneath your Firebird 2 1 root Using the Stored Procedure A Sample Here is the sample declaration in fbint1l conf lt charset GB gt intl_module fbintl collation
117. en InterBase and Firebird databases Smarter DSQL Error Reporting C Valderrama The DSQL parser will now try to report the line and column number of an incomplete statement New Column in RDB Index_Segments D Yemanov A Brinkman A new column RDB STATISTICS has been added to the system table RDB INDEX_SEGMENTS to store the per segment selectivity values for multi key indexes Note The column of the same name in RDB INDICES is kept for compatibility and still represents the total index selectivity that is used for a full index match 24 Global Improvements in Firebird 2 1 Some global improvements and changes have been implemented in Firebird 2 1 as engine development moves towards the architectural changes planned for Firebird 3 Note Unless otherwise indicated these improvements apply from v 2 1 forward Forced Writes on Linux Now Works A Peshkov For maximum database safety we configure databases for synchronous writes a k a Forced Writes ON This mode strongly recommended for normal production usage makes the write system call return only after the physical write to disk is complete In turn it guarantees that after a COMMIT any data modified by the transaction is physically on the hard drive not waiting in the operating system s cache Its implementation on Linux was very simple invoke fentl dbFile F_SETFL O_SYNC Yet databases on Linux were sometimes corrupted any
118. equal to lt length gt Example select rpad x 10 from y If the second string is omitted the default value is one space 2 If the result string would exceed the length the final application of lt string2 gt will be truncated SIGN SIGN lt number gt Returns 1 0 or 1 depending on whether the input value is positive zero or negative respectively select sign x from y SIN SIN lt number gt Returns the sine of an input number that is expressed in radians select sin x from y SINH SINH lt number gt Returns the hyperbolic sine of a number select sinh x from y SQRT SQRT lt number gt Returns the square root of a number select sqrt x from y TAN TAN lt number gt Returns the tangent of an input number that is ex pressed in radians 164 New Built in Functions Function Description select tan x from y TANH TANH lt number gt Returns the hyperbolic tangent of a number select tanh x from y TRIM See below V 2 0 x Trims characters default blanks from the left and or right of a string TRIM lt left paren gt lt trim specification gt lt trim character gt FROM lt value expression gt lt right paren gt lt trim specification gt LEADING TRAILING BOTH lt trim character gt lt value expression gt Rules If lt trim specification gt is not sp
119. er expressions The following example illustrates a date time arithmetic operation on a predefined literal update mytable set OVERDUE where DATE YESTERDAY DATE_DUE gt 10 1 82 Chapter 7 Procedural SQL PSQL A handful of improvements was added to the collection of PSQL extensions that came with Firebird 2 The highlights are new capabilities to use domains and collation sequences when declaring variables and arguments in procedures and triggers It is also now possible to apply a NOT NULL constraint to variables and arguments Quick Links e Domains in PSQL e COLLATE in Stored Procedures e NOT NULL in Stored Procedures e WHERE CURRENT OF for Views e ROW COUNT Counts Rows Returned by SELECT e Explicit Cursors e Stored Procedure Arguments Can Take Defaults e LEAVE lt label gt Flow Control Operator e OLD Variables Now Read only e Stack Trace for PSQL Exceptions e Call UDFs as Procedures Domains in PSQL Adriano dos Santos Fernandes V 2 1 It is now possible to use a domain when declaring the data types of arguments and variables in PSQL modules Depending on your requirements you can declare the argument or variable using e the domain identifier alone in lieu of the native data type identifier to have the variable inherit all of the attributes of the domain or e the data type of the domain without inheriting CHECK constraints and the DEFAULT value if
120. ers emulating the capability that has been always been available users of Unix and Linux hosted Firebird databases Administrators If a local Adminstrator or a member of the built in Domain Admins group connects to Firebird using trusted authentication he she will be connected as SYSDBA Configuration Parameter Authentication The new parameter Authentication has been added to firebird conf for configuring the authentication method on Windows Possible values are Authentication Native Provides full compatibility with previous Firebird versions avoiding trusted authentication Authentication Trusted The Security database is ignored and only Windows authentication is used In some respects on Windows this is more secure than Native in the sense that it is no less and no more secure than the security of the host operating system Authentication Mixed This is the default setting To retain the legacy behaviour when the ISC_USER and ISC_PASSWORD variables are set in the environ ment they are picked and used instead of trusted authentication 125 Security Note Trusted authentication can be coerced to override the environment variables if they are set refer to the notes below Forcing Trusted Authentication For the situation where trusted authentication is needed and there is a likelihood that ISC_USER and ISC_ PASSWORD are set there is a new DPB parameter that you can add to t
121. eshkov Feature request CORE 1232 V 2 1 Some misbehaviours that could occur when the Services Manager was doing backup restore operations and some parameter items were missing or in the wrong sequence The problem still affects lower versions including v 2 0 x so care should be taken to specify all required switches and supply the database name and backup file spec in the correct order when using the se vice_mgr switch Disable Non SYSDBA Access to Privileged Services A Peshkov Feature request CORE 787 Non SYSDBA access to parts of the Services API that return information about users and database paths has been disabled A non privileged user can retrieve information about itself however 133 Command line Utilities Backup Tools Firebird 2 brings plenty of enhancements to backing up databases a new utility for running on line incremental backups and some improvements to gbak to avoid some of the traps that sometimes befall end users New On line Incremental Backup N Samofatov Fast on line page level incremental backup facilities have been implemented The backup engine comprises two parts NBak the engine support module e NBackup the tool that does the actual backups Nbak The functional responsibilities of NBAK are 1 to redirect writes to difference files when asked ALTER DATABASE BEGIN BACKUP statement 2 to produce a GUID for the database snapshot and write it i
122. eshkov Security Upgrade Script Script security_database sql The contents of this file are subject to the Initial Developer s Public License Version 1 0 the License you may not use this file except in compliance with the License You may obtain a copy of the License at http www ibphoenix com main nfs a ibphoenixs page ibp_idpl Software distributed under the License is distributed AS IS WITHOUT WARRANTY OF ANY KIND either express or implied See the License for the specific language governing rights and limitations under the Licens The Original Code was created by Alex Peshkov on 16 Nov 2004 for the Firebird Open Source RDBMS project Copyright c 2004 Alex Peshkov and all contributors signed below All Rights Reserved Contributor s 1 temporary table to alter domains correctly CREATE TABLE UTMP USER_NAME VARCHAR 128 CHARACTER SET ASCII SYS_USER_NAME VARCHAR 128 CHARACTER SET ASCII GROUP_NAME VARCHAR 128 CHARACTER SET ASCII UID INTEGER GID INTEGER PASSWD VARCHAR 64 CHARACTER SET BINARY PRIVILEGE INTEGER COMMENT BLOB SUB_TYPE TEXT SEGMENT SIZE 80 CHARACTER SET UNICODE_FSS FIRST_NAME VARCHAR 32 CHARACTER SET UNICODE_FSS DEFAULT _UNICODE_FSS MIDDLE
123. ets Collations Two character sets introduced in Firebird 2 will be of particular interest if you have struggled with the short comings of UNICODE_FSS in past versions UTF8 character set The UNICODE_FSS character set has a number of problems it s an old version of UTF8 that accepts malformed strings and does not enforce correct maximum string length In FB 1 5 X UTF8 is an alias to UNICODE FSS Now UTF8 is a new character set without the inherent problems of UNICODE_FSS UNICODE collations for UTF8 UCS_BASIC works identically to UTF8 with no collation specified sorts in UNICODE code point order The UNICODE collation sorts using UCA Unicode Collation Algorithm Sort order sample isql SQI SQI SQI SQ SQI SQ L gt L gt L gt L gt L gt L gt q Ch create create insert insert insert insert insert select dos850 database test fdb table t c char 1 character set utf8 into into into into into t values a t values A t values t values b t values B from t order by c collate ucs_basic 105 International Language Support INTL a SQL gt select from t order by c collate unicode Developments in V 2 1 The 2 1 release sees further capabilities implemented for 1 using ICU charsets through fbintl 2 UNICODE collation charet UNICODE being available for all fbintl charsets 3 using collation attributes 4 CREATE DROP COLLATION stat
124. formation refer to Windows installation section of the accompanying Firebird 2 Migration amp Installation document Dmitry Yemanov has improved database monitoring to enable a non SYSDBA user to monitor all of its own attachments not just the one which is that user s CURRENT_CONNECTION This improvement was requested in Tracker CORE 2233 See also Monitor Multiple Attachments in the Administrative Features chapter General Notes e Dmitry has addressed a complaint about slowness in executing cross joins across three or more tables par ticularly when one or more tables are empty See the notes here and Tracker item CORE 2200 Patch Release 2 1 1 Firebird 2 1 1 is a patch release that corrects a number of bugs regressions or shortcomings that were reported following the v 2 1 final release Included are fixes for the problems that made the nBackup utility unserviceable in that release some changes to improve the new database monitoring feature s handling of a heavily loaded system and other changes to address complaints of slowness in some DML operations For details refer to the latest version of the Bugfix document accompanying these notes Important It was discovered that until now Firebird s security database had been created with Forced Writes OFF Like any database with FW disabled the security database is vulnerable to corruption in an environment where the power supply is not guaranteed to be stable The v 2 1 1 sub re
125. g on whether it already exists RECREATE EXCEPTION RECREATE EXCEPTION is exactly like CREATE EXCEPTION if the exception does not already exist If it does exist its definition will be completely replaced if there are no dependencies on it CREATE OR ALTER EXCEPTION CREATE OR ALTER EXCEPTION will create the exception if it does not already exist or will alter the defi nition if it does without affecting dependencies ALTER EXTERNAL FUNCTION C Valderrama ALTER EXTERNAL FUNCTION has been implemented to enable the ent ry_point or the module_name to be changed when the UDF declaration cannot be dropped due to existing dependencies COMMENT Statement C Valderrama The COMMENT statement has been implemented for setting metadata descriptions Syntax Pattern COMMENT ON DATABASE IS txt NULL COMMENT ON lt basic_type gt name IS txt NULL COMMENT ON COLUMN tblviewname fieldname IS txt NULL COMMENT ON PARAMETER procname parname IS txt NULL An empty literal string will act as NULL since the internal code DYN in this case works this way with blobs lt basic_type gt DOMAIN ABLE VIEW PROCEDURE RIGGER EXTERNAL FUNCTION FILTER EXCEPTION GENERATOR 42 Data Definition Language DDL EQUENCE DEX O JE HARACTER SET OLLATION ECURITY CLASS thot implemented beca
126. ge DML Enhancements to Functions A dos Santos Fernandes EXTRACT WEEK FROM DATE Feature request CORE 663 The EXTRACTO function is extended to support the ISO 8601 ordinal week numbers For example EXTRACT WEEK FROM date 30 09 2007 returns 39 alter table xyz add WeekOfTheYear computed by case when extract month from CertainDate and extract week from CertainDate 1 then Week extract WEEK from CertainDate of year 1 extract year from CertainDate lse Week extract WEEK from CertainDate of year extract year from CertainDate end 12 Specify the Scale for TRUNC Feature request CORE 1340 In Beta 1 the implementation of the TRUNC function supported only one argument the value to be trun cated From Beta 2 an optional second argument can be supplied to specify the scale of the truncation For example select trunc 987 65 1 trunc 987 65 1 from rdb database returns 987 60 980 00 For other examples of using TRUNCO with and without the optional scale argument refer to the alphabetical listing of functions in Appendix A Milliseconds Handling for EXTRACT DATEADD and DATEDIFF Feature request CORE 1387 From v 2 1 Beta 2 EXTRACTO DATEADDO and DATEDIFF can operate with milliseconds represent ed as an integer number For example EXTRACT MILLISECOND FROM timestamp 01 01 2000 01 00 00 1234
127. gt RDBSGET_CONTEXT lt namespace gt lt variable gt These functions are really a form of external function that exists inside the database instead of being called from a dynamically loaded library The following declarations are made automatically by the engine at database creation time Declaration DECLARE EXTERNAL FUNCTION RDB GET_CONTEXT VARCHAR 80 VARCHAR 80 RETURNS VARCHAR 255 FREE_IT DECLARE EXTERNAL FUNCTION RDB SET_CONTEXT VARCHAR 80 VARCHAR 80 VARCHAR 255 RETURNS INTEGER BY VALUE 73 Data Manipulation Language DML Usage RDB SET_CONTEXT and RDB GET_CONTEXT set and retrieve the current value of a context variable Groups of context variables with similar properties are identified by Namespace identifiers The namespace determines the usage rules such as whether the variables may be read and written to and by whom Note Namespace and variable names are case sensitive s RDBS GET_CONTEXT retrieves current value of a variable If the variable does not exist in namespace the function returns NULL s RDB SET_CONTEXT sets a value for specific variable if it is writable The function returns a value of 1 if the variable existed before the call and 0 otherwise e To delete a variable from a context set its value to NULL Pre defined Namespaces A fixed number of pre defined na
128. hanges showed about 50 per cent fewer API round trips thus incurring about 40 per cent fewer TCP roundtrips In Firebird 2 1 the remote interface limits the packet size of the response to various isc_XXX_info calls to the real used length of the contained data whereas before it sent the full specified buffer back to the client buffer even if only 10 bytes were actually filled Firebird 2 1 remote interface sends back only 10 bytes in this case Some of our users should see a benefit from the changes especially two tier clients accessing databases over the Internet The changes can be summarised as a Batched packets delivery Requires both server and client of version v2 1 enabled upon a successful pro tocol handshake Delays sending packets of certain types which can be deferred for batched transfer with the next packet Allocate deallocate statement operations come into this category for example b Pre fetching some pieces of information about a statement or request and caching them on the client side for probable following API calls Implemented on the client side only but relies partly on the benefits of reduced round trips described in a It works with any server version even possibly providing a small benefit for badly written client applica tions although best performance is not to be expected if the client is communicating with a pre V 2 1 server c Reduced information responses from the engine no trailing zeroes As
129. he DPB isc_dpb_trusted_auth Most of the Firebird command line utilities support parameter by means of the switch tru sted the ab breviated form is available according to the usual rules for abbreviating switches Note The gli and nbackup utilities do not follow the pattern they use single letter switches that are somewhat arcane The switch of interest for gli is K For nbackup watch this space The facility to force trusted authentication is yet to be implemented for it Example C Pr bin gt isql srv db log in using trusted authentication C Pr bin gt set ISC_USER userl C Pr bin gt set ISC_PASSWORD 12345 C Pr bin gt isql srv db log in as userl from environment C Pr bin gt isql trust srv db log in using trusted authentication Important Windows rules for full domain user names allow names longer than the maximum 31 characters allowed by Firebird for user names The 31 character limit is enforced and from V 2 1 logins passing longer names are disabled This will remain the situation until the mapping of OS objects to database objects is implemented in a later Firebird version Classic Server on POSIX For reasons both technical and historical a Classic server on POSIX with embedded clients is especially vul nerable to security exposure Users having embedded access to databases MUST be given at least read access to the security database This is the main reason
130. headers no longer contain private declarations Lock Timeout for WAIT Transactions A Karyakin D Yemanov The new feature extends the WAIT mode by making provision to set a finite time interval to wait for the con current transactions If the timeout has passed an error isc_lock_timeout is reported Timeout intervals can now be specified per transaction using the new TPB constant isc_tpb_lock_timeout in the API Note The DSQL equivalent is implemented via the LOCK TIMEOUT lt value gt clause of the SET TRANSACTION statement isc_dsql_sql_info Now Includes Relation Aliases D Yemanov The function call isc_dsql_sql_info has been extended to enable relation aliases to be retrieved if required Enhancement to isc_blob_lookup_desc A dos Santos Fernandes isc_blob_lookup_desc now also describes blobs that are outputs of stored procedures API Identifies Client Version N Samofatov The macro definition FB_API_VER is added to ibase h to indicate the current API version The number corre sponds to the appropriate Firebird version 19 Changes to the Firebird API and ODS The current value of FB_API_VER is 20 two digit equivalent of 2 0 This macro can be used by client appli cations to check the version of ibase h its being compiled with Additions to the isc_database_info Structure V Khorsun The following items have been added to the isc_database_info function call st
131. ia 143 enix Server UUUtY nirestore er eee ee ei ai ea Ee et 143 New Shutdown States Mode cccccccseeececcesesceceeeeececeeseseeeeeeeeeeeeeesseeeeeeseeseeeeseeeeeeeseeeeees 143 Builds and InstallS cinc A eye codenosUudvige pe Sdeudevdvebige as 144 Parameter for Instance name added to instSVC eX sese eee eee 144 Revised Win32 Installer DoCS rorssz 12742 ET ogi CEE PEE estes aldea dde rien Ere VER Er anih 144 Gentoo FreeBSD detection during install sese 145 14 External Functions UDES 5 452251 cies O sab cido ele del desde 146 Ability to Signal SQL NULL via a Null Pointer sese eee eee eee 146 UDF library diagnostic messages improved sss sese sese eee eee 147 UDFs Added and Changed ancora aooe ia eee toe 147 IB_UDF_rand vs IB_LUDF_srand acc ona sc tehar hN Yd Ka aS aR Tadi gX aa aN Sd 147 TB WDE DOWER icaichacctocdaleadecassiu than sans coandavedosoaevs dees us bees a0 ule boanka vednecadeacuasueas teabalarecccdaretosaaerede 148 General UDF Changes erer tear eene E suse edees EAE aE caveats EE EEEE ia nas E Ea EECa iii 148 Build eT ae a a a ieee A e E A E A A aa a dutta a 148 15 New Configuration Parameters and Changes sss sese ee eee eee 149 A RT 149 RelaxedAltasCh ckiM Siro id da ei a a id ate a iar ea 149 MaxFileSystemCache me agne neee E T TE E ENE dae E 150 DatabaseGrowthIncrementt concencia ii e iia iee Eei NR a iiaii 150 ExternalFile Access vicoticotoceralcoolocinccleeaclaciianeolerpebcigiolinidto leader aa aaa arae erea
132. ibm 964_VPUA ibm 949_P110 1999 ibm 949 cp949 949 ibm 949_VASCII_VSUB_VPUA ibm 949 P11A 1999 ibm 949 cp949c ibm 949 VSUB_VPUA ibm 970_P110 1995 ibm 970 EUC KR KS_C_5601 1987 windows 51949 cSEUCKR ibm eucKR KSC_5601 5601 ibm 970_VPUA ibm 971_P100 1995 ibm 971 ibm 971_VPUA ibm 1363_P11B 1998 ibm 1363 KS_C_5601 1987 KS_C_5601 1989 KSC_5601 csKSC56011987 korean iso ir 149 5601 cp1363 Kac windows 949 ibm 1363_VSUB_VPUA ibm 1363_P110 1997 ibm 1363 ibm 1363_VASCII_VSUB_VPUA windows 949 2000 windows 949 KS_C_5601 1987 KS_C_5601 1989 KSC_5601 csKSC56011987 korean iso ir 149 ms949 ibm 1162_P100 1999 ibm 1162 ibm 874_P100 1995 ibm 874 ibm 9066 cp874 TIS 620 tis620 2533 eucTH cp9066 windows 874 2000 TIS 620 windows 874 MS874 ibm 437_P100 1995 ibm 437 IBM437 cp437 437 csPC8CodePage437 windows 437 ibm 850_P100 1995 ibm 850 IBM850 cp850 850 csPC850Multilingual windows 850 ibm 851_P100 1995 ibm 851 IBM851 cp851 851 csPC851 ibm 852_P100 1995 ibm 852 IBM852 cp852 852 csPCp852 windows 852 ibm 855_P100 1995 ibm 855 IBM855 cp855 855 csIBM855 csPCp855 ibm 856_P100 1995 ibm 856 cp856 856 ibm 857_P100 1995 ibm 857 IBM857 cp857 857 csIBM857 windows 857 ibm 858_P100 1997 ibm 858 IBM00858 CCSIDOO858 CP00858 PC Multilingual 850 euro cp858 ibm 860_P100 1995 ibm 860 IBM860 cp860 860 csIBM860 ibm 861_P100 1995 ibm 861 IBM861 cp861 861 cp is csIBM861 windows 861 170 International Character Sets
133. ime data type every data type in DTS must be a date time type having the same date time type otherwise an error is thrown e any data type in DTS is BLOB each data type in DTS must be BLOB and all with the same sub type A Useful Trick with Date Literals H Borrie In days gone by before the advent of context variables like CURRENT_DATE CURRENT_TIMESTAMP et al we had predefined date literals such as NOW TODAY YESTERDAY and so on These predefined date literals survive in Firebird s SQL language set and are still useful In InterBase 5 x and lower the following statement was legal and returned a DATE value remembering that the DATE type then was what is now TIMESTAMP select NOW from rdb database returns system date and time In a database of ODS 10 or higher that statement returns the string NOW We have had to learn to cast the date literal to get the result we want select cast NOW as TIMESTAMP from rdbSdatabase For a long time probably since IB 6 there has been an undocumented short expression syntax for casting not just the predefined date time literals but any date literals Actually it is defined in the standard Most of us were just not aware that it was available It takes the form lt data type gt lt date literal gt Taking the CAST example above the short syntax would be as follows select TIMESTAMP NOW from rdb database This short syntax can participate in oth
134. ing then the result data type is variable length character string with maximum length in characters equal to the largest maximum amongst the data types in DTS ii Otherwise the result data type is fixed length character string with length in characters equal to the maximum of the lengths in characters of the data types in DTS 68 Data Manipulation Language DML iii The characterset collation is used from the first character string data type in DTS b Ifall of the data types in DTS are exact numeric then the result data type is exact numeric with scale equal to the maximum of the scales of the data types in DTS and the maximum precision of all data types in DTS Note NOTE Checking for precision overflows is done at run time only The developer should take measures to avoid the aggregation resolving to a precision overflow c If any data type in DTS is approximate numeric then each data type in DTS shall be numeric else an error is thrown d If some data type in DTS is a date time data type then every data type in DTS shall be a date time data type having the same date time type e If any data type in DTS is BLOB then each data type in DTS shall be BLOB and all with the same sub type UNIONs Allowed in ANY ALL IN Subqueries D Yemanov The subquery element of an ANY ALL or IN search may now be a UNION query Enhancements to NULL Logic The following features involving NULL in DSQL have been impleme
135. ion Debugging Improvements Various Contributors Improved Reporting from Bugchecks BUGCHECK log messages now include file name and line number A Brinkman Updated Internal Structure Reporting Routines that print out various internal structures DSQL node tree BLR DYN etc have been updated N Samofatov 15 New in Firebird 2 New Debug Logging Facilities Thread safe and signal safe debug logging facilities have been implemented N Samofatov Diagnostic Enhancement Syslog messages will be copied to the user s tty if a process is attached to it A Peshkov Improved Connection Handling on POSIX Superserver A Peshkov Posix SS builds now handle SIGTERM and SIGINT to shutdown all connections gracefully A Peshkov PSOL Invariant Tracking Reworked N Samofatov Invariant tracking in PSQL and request cloning logic were reworked to fix a number of issues with recursive procedures for example SF bug 627057 Invariant tracking is the process performed by the BLR compiler and the optimizer to decide whether an in variant an expression which might be a nested subquery is independent from the parent context It is used to perform one time evaluations of such expressions and then cache the result If some invariant is not determined we lose in performance If some variant is wrongly treated as invariant we see wrong results Example select from rdbSrelations where rdb relation_id lt select
136. ion ID MONSSTATE statement state 0 idle 1 active MONSTIMESTAMP statement start date time MON SQL_TEXT statement text if appropriate MON STAT_ID statistics ID s column MON SQL_TEXT contains NULL for GDML statements s columns MON TRANSACTION_ID and MONSTIMESTAMP contain valid values for active state ments only e The execution plan and the values of parameters are not available MONS CALL_STACK call stack of active PSOL requests MON CALL_ID call ID MON STATEMENT_ID top level DSQL statement ID MON CALLER_ID caller request ID MONSOBJECT_NAME PSQL object name MONSOBJECT_TYPE PSQL object type MON TIMESTAMP request start date time MON SOURCE_LINE SQL source line number MON SOURCE_COLUMN SQL source column number MON STAT_ID statistics ID e column MON STATEMENT_ID groups call stacks by the top level DSQL statement that initiated the call chain This ID represents an active statement record in the table MON STATEMENTS s columns MON SOURCE_LINE and MON SOURCE_COLUMN contain line column information re lated to the PSQL statement currently being executed MON IO_STATS I O statistics MON STAT_ID statistics ID MON STAT_GROUP statistics group 0 database 1 attachment 2 transaction 3 statement 4 call 115 Administrative Features MON PAGE_READS number of page reads MONSPAGE_WRITES number of page writes MON PAGE_FETCHES number of
137. ions README set_transaction txt SHOW DATABASE now Returns ODS Version Number C Valderrama ODS On Disk Structure version is now returned in the SHOW DATABASE command C Valderrama 141 Command line Utilities Ability to show the line number where an error happened in a script C Valderrama In previous versions the only reasonable way to know where a script had caused an error was using the switch e for echoing commands o to send the output to a file and m to merge the error output to the same file This way you could observe the commands sq executed and the errors if they exist The script continued executing to the end The server only gives a line number related to the single command statement that it s executing for some DSQL failures For other errors you only know the statement caused problems With the addition of b for bail as described in 1 the user is given the power to tell isql to stop executing scripts when an error happens but you still need to echo the commands to the output file to discover which statement caused the failure Now the ability to signal the script related line number of a failure enables the user to go to the script directly and find the offending statement When the server provides line and column information you will be told the exact line of DML in the script that caused the problem When the server only indicates a failure you will be told the starting line of the statement
138. l script for FB v1 5 is still available in the same directory UDF library diagnostic messages improved A Peshkov Diagnostics regarding a missing unusable UDF module have previously made it hard to tell whether a module was missing or access to it was being denied due to the UDFAccess setting in firebird conf Now we have separate understandable messages for each case UDFs Added and Changed UDFs added or enhanced in Firebird 2 0 s supplied libraries are IB_UDF_rand vs IB_UDF_srand F Schlottmann Goedde In previous versions the external function rand sets the random number generator s starting point based on the current time and then generates the pseudo random value srand unsigned time NULL return float rand float RAND_MAX The problem with this algorithm is that it will return the same value for two calls done within a second To work around this issue rand was changed in Firebird 2 0 so that the starting point is not set explicitly This ensures that different values will always be returned 147 External Functions UDFs In order to keep the legacy behaviour available in case somebody needs it srand has been introduced It does exactly the same as the old rand did IB_UDF_ lower The function IB_UDF_lower in the IB_UDF library might conflict with the new internal function 1ow er if you try to declare it in a database using the 1b_udf sql script from a previous Fi
139. lease and all future builds rectify this situation and create security2 fdb with FW ON If you are importing an existing security database from any older release it is recommended that you use the gfix utility to activate Forced Writes Note however that setting Forced Writes ON in any database on a Firebird 1 5 x or lower version server on Linux will have no effect FW does not work on Linux in these lower Firebird servers About These Notes Editions of the v 2 1 release notes are merged with the notes developed over the course of developments and releases from V 2 0 to this v 2 1 x release Note that the separate v 2 0 x documentation is not distributed with the v 2 1 binaries The Installation Migration Compatibility and Bug Fixes sections have been removed from the release notes document and are now distributed in the fbroot doc subdirectory as separate documents Like the release notes they cover both the v 2 0 x and v 2 1 release series To help smoothe the transition from older versions it will be essential to study both the release notes and the migration installation guide thoroughly We encourage you to take your time testing this release with your applications and stressing it with real world data and loads Some legacy queries might not work or might not perform as they did previously due to a number of logic corrections that have been implemented Temporary workarounds for legacy applications in such situations are
140. lease refer to the topic Enlarge Disk Allocation Chunks in the chapter Global Improvements in Firebird 2 1 When the engine needs to initialize more disk space it allocates a block that is 1 16th of the space already allocat ed but not less than 128 KB and not greater than the DatabaseGrowthIncrement value The DatabaseGrowthIn crement value can be raised to increase the maximum size of newly allocated blocks to more than the default 128 MB Set it to zero to disable preallocation 150 New Configuration Parameters and Changes Note e The lower limit of the block size is purposely hard coded at 128 KB and cannot be reconfigured e Space is not preallocated for database shadow files e Preallocation is disabled for a database that has the No reserve option set ExternalFileAccess A Peshkov Modified in Firebird 2 to allow the first path cited in ExternalFilesAccess to be used as the default when a new external file is created LegacyHash A Peshkov This parameter enables you to configure Firebird 2 to reject an old DES hash always in an upgraded security database If you don t use the security database upgrade procedure this parameter does not affect Firebird op eration A DES hash cannot arrive in the new security2 fdb Refer to the Security DB Upgrade Security section for instructions on upgrading your existing Firebird 1 5 security fdb or a renamed isc4 gdb to the new security database layout
141. lf known as a field tester by subscribing to the field testers list and posting the best possible bug description you can 3 Ifyou want to start a discussion thread about a bug or an implementation please do so by subscribing to the firebird devel list In that forum you might also see feedback about any tracker ticket you post regarding this release Additional Documentation You will find README documents for many of the new v 2 and v 2 1 features in your installation kit installed by default in the doc sub directory An automated Release Notes page in the Tracker provides access to lists and links for all of the Tracker tickets associated with this version and its various builds Use this link For your convenience the many bug fixes and regressions fixed during the development of Firebird 2 0 x and 2 1 are listed in descending chronological order in the separate Bugfixes document Firebird 2 Project Team Chapter 2 New in Firebird 2 New Features Implemented This chapter summarises the new features implemented in Firebird 2 encompassing both v 2 1 and the v 2 0 x series Important Change to API DPB Parameters in V 2 1 2 A long standing legacy loophole in the handling of DPB parameters enabled ordinary users to make connection settings that could lead to database corruptions or give them access to SYSDBA only operations The loophole has been closed a change that could affect several existing applicatio
142. lid Options L lt database gt Lock database for filesystem copy 135 Command line Utilities N lt database gt Unlock previously locked database F lt database gt Fixup database after filesystem copy B lt level gt lt database gt lt filename gt Create incremental backup R lt database gt lt file0 gt lt filel gt Restore incremental backup lt U lt user gt User name P lt password gt Password Note 1 lt database gt may specify a database alias 2 incremental backup of multi file databases is not supported yet 3 stdout may be used as a value of lt filename gt for the B option Improvement in V 2 1 3 A Peshkov In the Firebird 2 5 beta an improvement was done for POSIX versions to address a problem whereby the full backup tool of nBackup would hog I O resources when backing up large databases bringing production work to a standstill This improvement was backported to V 2 1 3 Now nBackup tries to read from the operating system cache before attempting to read from disk thus reducing the I O load substantially Note The cost may be a 10 to 15 percent increase in the time taken to complete the full backup under high load conditions Tracker reference CORE 23 16 User Manual P Vinkenoog A user manual for NBak NBackup has been prepared It can be downloaded from the documentation area at the Firebird website www firebirdsql org pdfmanual the file na
143. lity nbackup must be supplied with an explicit file path and name for its difference file in order to avoid this file being written into the dev directory You can achieve this with the following statement using isql 26 Global Improvements in Firebird 2 1 isql dev sda7 SQL gt alter database add difference file tmp dev_sda7 To keep the size of the nbak copy within reasonable bounds it is of benefit to know how much storage on the device is actually occupied The s switch of nbackup will return the size of the database in database pages nbackup s 1 dev sda7 TILT Don t confuse the result here with the block size of the device The figure returned 77173 is the number of pages occupied by the database Calculate the physical size in bytes as number of pages page size If you are unsure of the page size you can query it from the database header using gstat h gstat h dev sda7 Database dev sda7 Database header page information Flags 0 Checksum 12345 Generation 43 Page size 4096 lt ODS version 11 1 Examples of nbackup Usage with a Raw Device 1 A backup can be performed in a script using the output from the s switch directly For example DbFile dev sda7 DbSize nbackup L DbFile S exit 1 dd if SDbFile ibs 4k count DbSize compress and record DVD nbackup N DbFile Se de e Se 2 A physical backup using nbackup directly from the command line nbackup B 0
144. lt table primary gt Named columns join 1 All columns specified in lt column list gt should exist in the tables at both sides 2 An equi join lt left table gt lt column gt lt right table gt lt column gt is automatically created for all columns ANDed 3 The USING columns can be accessed without qualifiers in this case the result is equivalent to COALESCE lt left table gt lt column gt lt right table gt lt column gt 4 In SELECT USING columns are expanded once using the above rule Natural join 1 A named columns join is automatically created with all columns common to the left and right tables 55 Data Manipulation Language DML 2 If there is no common column a CROSS JOIN is created Examples Je 1 select from employee join department using dept nol PQ X select from employee_project natural join employee natural join project CROSS JOIN D Yemanov V 2 0 x CROSS JOIN is now supported Logically this syntax pattern A CROSS JOIN B is equivalent to either of the following A INNER JOIN B ON 1 1 or simply FROM A B Performance Improvement at V 2 1 2 D Yemanov In the rare case where a cross join of three or more tables involved table s that contained no records extremely slow performance was reported CORE 2200 A performance improvement was gained by teaching the opti mizer not to waste time and effort on walking through populat
145. lumn Aliases in CREATE VIEW e CREATE TRIGGER SQL2003 Variant e Alternative Syntax for Computed Fields e CREATE SEQUENCE e REVOKE ADMIN OPTION SET DROP DEFAULT Clauses e Syntaxes for Changing Exceptions e ALTER EXTERNAL FUNCTION COMMENT Statement e CREATE VIEW Extensions e Create FKs Without Exclusive Access e Changed Logic for View Updates e Descriptive Identifiers for BLOB Subtypes e CREATE COLLATION statement Database Triggers Adriano dos Santos Fernandes v 2 1 A database trigger is a PSQL module that is executed when a connection or transaction event occurs The events and the timings of their triggers are as follows CONNECT e Database connection is established e A transaction is started e Triggers are fired uncaught exceptions roll back the transaction disconnect the attachment and are re turned to the client s The transaction is committed 35 Data Definition Language DDL DISCONNECT s A transaction is started e Triggers are fired uncaught exceptions roll back the transaction disconnect the attachment and are swal lowed s The transaction is committed s The attachment is disconnected TRANSACTION START Triggers are fired in the newly created user transaction uncaught exceptions are returned to the client and the transaction is rolled back TRANSACTION COMMIT Triggers are fired in the committing transaction uncaught exceptions roll back the trigger s savepoint the commit command i
146. ly allows you to provide your own instructions to the engine and have it ignore the plan supplied by the optimizer Firebird 2 enhancements allow you to specify more possible paths for the engine For example PLAN A ORDER IDX1 INDEX IDX2 IDX3 For more details please refer to the topic Query Plans Improvements in the DML chapter Optimizer Improvements This section represents a collection of changes done in Firebird 2 to optimize many aspects of performance 96 Indexing amp Optimizations For All Databases The first group of changes affect all databases including those not yet upgraded to ODS 11 x Some General Improvements O Loa D Yemanov e Much faster algorithms to process the dirty pages tree Firebird 2 offers a more efficient processing of the list of modified pages a k a the dirty pages tree It affects all kinds of batch data modifications performed in a single transaction and eliminates the known issues with performance getting slower when using a buffer cache of gt 10K pages This change also improves the overall performance of data modifications e Increased maximum page cache size to 128K pages GB for 16K page size Faster Evaluation of IN and OR O Loa Constant IN predicate or multiple OR booleans are now evaluated faster Sparse bitmap operations were optimized to handle multiple OR booleans or an IN lt constant list gt predicate more efficiently improving performance of the
147. me is Firebird nbackup pdf gbak Backup Porting Restore Utility A number of enhancements have been added to gbak Changed Behaviours New Switches V Khorsun The new gbak switch RECREATE_DATABASE OVERWRITE Kd 136 Command line Utilities is a separate switch designed to make harder for the unsuspecting to overwrite a database accidentally as could occur easily with the shortened form of the old switch R EPLACE_DATABASE ra In summary e gbak R or gbak r now applies to the new R ECREATE_DATABASE switch and will never overwrite an existing database if the O VERWRITE argument is absent e The short form of the old gbak R EPLACE_DATABASE is now REP LACE_DATABASE This switch does not accept the O VERWRITE argument e The REP LACE_DATABASE switch should be considered as deprecated i e it will become unavailable in some future Firebird release This change means that if you have any legacy batch or cron scripts that rely on gbak r or gbak R without modification then the operation will except if the database exists If you want to retain the ability of your script to overwrite your database unconditionally you will need to modify the command to use either the new switch with the OVERWRITE argument or the new short form for the old REPLACE_ DATABASE switch gbak Made More Version friendly C Valderrama V 2 1 In its latest evolu
148. mespaces is available USER_SESSION Offers access to session specific user defined variables You can define and set values for variables with any name in this context USER_TRANSACTION Offers similar possibilities for individual transactions SYSTEM Provides read only access to the following variables e NETWORK PROTOCOL The network protocol used by client to connect Currently used values TCPv4 WNET XNET and NULL e CLIENT_ADDRESS The wire protocol address of the remote client represented as a string The value is an IP address in form Xxx xxx xxx xxx for TCPv4 protocol the local process ID for XNET protocol and NULL for any other protocol s DB_NAME Canonical name of the current database It is either the alias name if connection via file names is disallowed DatabaseAccess NONE or otherwise the fully expanded database file name s ISOLATION_LEVEL The isolation level of the current transaction The returned value will be one of READ COMMITTED SNAPSHOT CONSISTENCY e TRANSACTION _ID The numeric ID of the current transaction The returned value is the same as would be returned by the CURRENT_TRANSACTION pseudo variable 74 Data Manipulation Language DML s SESSION_ID The numeric ID of the current session The returned value is the same as would be returned by the CURRENT_CONNECTION pseudo variable e CURRENT_USER The current user The returned value is the
149. n on expressions For ODS 11 Databases only This group of optimizations affects databases that were created or restored under Firebird 2 or higher Segment level Selectivities are Used See Selectivity Maintenance per Segment Better Support for IS NULL and STARTING WITH Previously IS NULL and STARTING WITH predicates were optimized separately from others thus causing non optimal plans in complex ANDed ORed boolean expressions From v2 0 and ODS11 these predicates are optimized in a regular way and hence benefit from all possible optimization strategies 98 Indexing amp Optimizations Matching of Both OR and AND Nodes to Indexes Complex boolean expressions consisting of many AND OR predicates are now entirely mapped to available indices if at all possible Previously such complex expressions could be optimized badly Better JOIN Orders Cost estimations have been improved in order to improve JOIN orders Indexed Order Enabled for Outer Joins It is now possible for indexed order to be utilised for outer joins i e navigational walk Enhancements to Indexing 252 byte index length limit is gone A Brinkman New and reworked index code is very fast and tolerant of large numbers of duplicates The old aggregate key length limit of 252 bytes is removed Now the limit depends on page size the maximum size of the key in bytes is 1 4 of the page size 512 on 2048 1024 on 4096 etc A 40 bit record number is inclu
150. n the more recent Linux distributions the default setups no longer dump core automatically when an application crashes Users often have troubles trying to get them working Differing rules for Classic and Superserver combined with a lack of consistency between the OS setup tools from distro to distro make it difficult to help out with any useful general rule Code has been added for Classic and Superserver on Linux to bypass these problems and automate generation of a core dump file when an abort on BUGCHECK occurs The Firebird server will make the required cwd change 33 Global Improvements in Firebird 2 1 working directory to an appropriate writable location tmp and set the core file size limit so that the soft limit equals the hard limit Note In a release version the automated core dumping is active only when the BugcheckAbort parameter in firebird conf is set to true 1 In a debug version it is always active If you need to enable the facility don t forget that the server needs to be restarted to activate a parameter change 34 Chapter 5 Data Definition Language DDL In this chapter are the additions and improvements that have been added to the SQL data definition language subset in the Firebird 2 development cycle Those marked as introduced in v 2 1 are available only to ODS 11 1 and higher databases Quick Links e Database Triggers e Global Temporary Tables e Co
151. n_library gt module_name lt library_name gt The alternative new syntax is declare filter lt name gt input_type lt mnemonic gt output_type lt mnemonic gt entry_point lt function_in_library gt module_name lt library_name gt where lt mnemonic gt refers to a subtype identifier known to the engine Initially they are binary text and others mostly for internal usage but an adventurous user could write a new mnemonic in rdb types and use it since it is parsed only at declaration time The engine keeps the numerical value Remember only negative subtype values are meant to be defined by users To get the predefined types do select RDBSTYPE RDBSTYPE_NAME RDBSSYSTEM_FLAG from rdb types where rdb field_name RDBSFIELD_SUB_TYP Fl RDBSTYPE RDBSTYPE_NAME RDBSSYSTEM_FLAG BINARY RANGES SUMMARY E T ORMAT RANSACTION_DESCRIPTION EXTERNAL FILE DESCRIPTION COIDTOKRWNEFO LA a L A a a A A LA Examples Original declaration declare filter pesh input_type 0 output_type 3 entry_point f module_name p Alternative declaration declare filter pesh input_type binary output_type acl entry_point f module_name p Declaring a name for a user defined blob subtype remember to commit after the insertion SQL gt insert into rdb types CON gt values RDBSFIELD_SUB_TYP SOL gt commit Ej 72100 XDR tes
152. nal RETURNING clause for all singleton operations update insert and delete operations See RE TURNING Clause UPDATE OR INSERT Statements v 2 1 Now you can write a statement that is capable of performing either an update to an existing record or an insert depending on whether the targeted record exists See UPDATE OR INSERT Statement MERGE Statement v 2 1 New statement syntax that performs either an update to an existing record if a condition is met or an insert if the condition is not met See MERGE Statement LIST function v 2 1 A new aggregate function LIST lt SOMETHING retrieves all of the SOMETHINGs in a group and aggregates them into a comma separated list See LIST Function Lots of New Built in Functions v 2 1 Built in functions replacing many of the UDFs from the Firebird distributed UDF libraries For a full list with examples see Built in Functions Short BLOBs Can Masquerade as Long VARCHARs v 2 1 At various levels of evaluation the engine now treats text BLOBs that are within the 32 765 byte size limit as though they were varchars Now functions like cast lower upper trim and substring will work with these BLOBs as well as concatenation assignment to string types and numerous other operations See Text BLOB Compatibility Important Note about Use of SUBSTRING with a BLOB The SUBSTRING function now returns a BLOB not a VARCHAR as previously New in Firebird 2 P
153. ndian UTF16_OppositeEndian UTF32_PlatformEndian UTF32_OppositeEndian A dos Santos Fer nandes Not known 168 International Character Sets UTF 7 windows 65000 IMAP mailbox name SCSU BOCU 1 csBOCU 1 CESU 8 ISO 8859 1 ibm 819 IBM819 cp819 latinl 8859_1 csISOLatinl iso ir 100 ISO_8859 1 1987 11 819 US ASCII ASCII ANSI_X3 4 1968 ANSI_X3 4 1986 ISO_646 irv 1991 iso_646 irv 1983 ISO646 US us csASCII iso ir 6 cp367 ascii7 646 windows 20127 150_2022 locale ja version 0 ISO 2022 JP csISO2022JP ISO_2022 locale ja version 1 ISO 2022 JP 1 JIS JIS_Encoding 150_2022 locale ja version 2 ISO 2022 JP 2 csISO2022J7P2 ISO_2022 locale ja version 3 JIS7 csJISEncoding ISO_2022 locale ja version 4 JIS8 ISO_2022 locale ko version 0 ISO 2022 KR csISO2022KR ISO_2022 locale ko version 1 ibm 25546 ISO_2022 locale zh version 0 ISO 2022 CN 150_2022 locale zh version 1 ISO 2022 CN EXT HZ HZ GB 2312 ISCII version x iscii de windows 57002 iscii dev ISCII version x iscii be windows 57003 iscii bng windows 57006 x iscii as ISCII version 2 x iscii pa windows 57011 iscii gur ISCII version 3 x iscii gu windows 57010 iscii guj ISCII version 4 x iscii or windows 57007 iscii ori ISCII version 5 x iscii ta windows 57004 iscii tml ISCII version 6 x iscii te windows 57005 is
154. nient way to issue ALTER DATABASE BEGIN END BACKUP 2 to fix up the database after filesystem copy physically change nbak_state_diff to nbak_state_normal in the database header 3 to create and restore incremental backups Incremental backups are multi level That means if you do a Level 2 backup every day and a Level 3 backup every hour each Level 3 backup contains all pages changed from the beginning of the day till the hour when the Level 3 backup is made Backing Up Creating incremental backups has the following algorithm 1 Issue ALTER DATABASE BEGIN BACKUP to redirect writes to the difference file 2 Look up the SCN and GUID of the most recent backup at the previous level 3 Stream database pages having SCN larger than was found at step 2 to the backup file 4 Write the GUID of the previous level backup to the header to enable the consistency of the backup chain to be checked during restore 5 Issue ALTER DATABASE END BACKUP 6 Add a record of this backup operation to RDB BACKUP_HISTORY Record current level SCN snapshot GUID and some miscellaneous stuff for user consumption Restoring Restore is simple we reconstruct the physical database image for the chain of backup files checking that the backup_guid of each file matches prev_guid of the next one then fix it up change its state in header to nbak_state_normal Usage nbackup lt options gt Va
155. nitoring tables return information about the attached database only If multiple databases are being accessed on the server each of them has to be connected to and monitored separately The system variables CURR ENT_CONNI ECTION and CURR ENT_TRANSACTION could be used to select data about the caller s current connection and transaction respectively These variables correspond to the ID columns of the appropriate monitoring tables Examples 1 Retrieve IDs of all CS processes loading CPU at the moment 116 Administrative Features ECT MONSSE ROM MONSATTACHMENTS HERE MONSATTACH D MONSSTATE 1 RVER_PID b oso C ENT_ID lt gt CURRENT_CONNECTION Retrieve information about client applications SELECT MONSUSER MONSREMOTE_ADDRESS ONSREMOTE_PID ONS TIMESTAMP FROM MONSATTACHMENTS WHERE MONSATTACHMENT_ID lt gt CURRENT_CONNECTION Get isolation level of the current transaction n HA H ROM MONSTRANSACTIONS WHERE ECT MONSISOLATION_MODE MONSTRANSACTION_ID CURRENT_TRANSACTION Get statements that are currently active SELECT ATT MONSUSER ATT MONSREMOTE_ADDRESS STMT MONSSQL_TEXT ST
156. nonononn nono iii nn nn nn nn nnnn i 23 ODS Type eT Bls 24 Smarter DSQE Error Reporting cucstidctt tinglado erit daetuaoseatepecenabeeegecasbyesedabe 24 New Column in RDB Index_Segments sese eee eee eee 24 4 Global Improvements in Firebird 2 1 6 sss eee ER Z aR TREZ ERER ER TERE ERRER Ha 25 Forced Writes on Linux Now Works esse eee eee 25 Loa nA nPI TEE E A E ol Ei tubule T 25 Instant Fix foran Older Firebird sses esera e Ea Tea EREE tit aida 26 Databases on Raw DEVICES ena sre iyidere iee E EEEa e ERE AEE IERE ELTRA SEERA ie REEE NE ENE aiii 26 Moving a Database to a Raw Device sss sees eee 26 Special Issues for nbak nbackup sese ee eee e e e e e e eee 26 Further Advice about Raw Devices onenaren i EE e nia Erri Ep e T CEE 27 Remote Interface Improyements arnoia aA Ean Hae tee eee eee 28 API Changes nosio riietad T 29 UA e E E E das ane abe ote NE ATA E EAE 29 Optimization A a Rae ee Bo ae ds 29 Optimization for Multiple Index Scans sss sees esse ee eee 29 Optimize sparse bitmap operations see ee ee ee ee ee ee ee eee 20 Configuration and N 1 T TTT 30 Increased Lock Manager Limits amp Defaults sss sese 30 Page sizes of 1 K and 2K Deprecated ui ti ao 30 Enlarge Disk Allocation Chunks sss sese ee eee 30 Bypass Filesystem Caching on Superserver esse eee eee eee 32 Other Global Improvements ciutat a vas soutien a end sen ec eee patie 32 Garbage Collector Rationalisation mecres r eosde ZR egg Zd nono no nono no nono no nono no nono
157. ns database tools and connectivity layers drivers components Details are in Chapter 3 Changes to the Firebird API and ODS On Disk Structure Databases created or restored under Firebird 2 have an on disk structure ODS of 11 or higher s Firebird 2 1 creates databases with an ODS of 11 1 It can read databases of lower ODS but most of its new features will be unavailable to such databases e Firebird 2 0 x servers create databases with an ODS of 11 sometimes expressed as 11 0 If you wish to have the full range of v 2 1 features available you should upgrade ODS 11 and lower databases by backing them up and restoring them under v 2 1 Database Triggers v 2 1 Newly implemented database triggers are user defined PSQL modules that can be designed to fire in various connection level and transaction level events See Database Triggers SQL and Objects Global Temporary Tables v 2 1 SQL standards compliant global temporary tables have been implemented These pre defined tables are instantiated on request for connection specific or transaction specific use with non persistent data which the Firebird engine stores in temporary files See Global Temporary Tables New in Firebird 2 Common Table Expressions Recursive DSQL Queries v 2 1 Standards compliant common table expressions which make dynamic recursive queries possible are introduced See Common Table Expressions RETURNING Clause v 2 1 Optio
158. nted New NOT DISTINCT Test Treats Two NULL Operands as Equal O Loa D Yemanov A new equivalence predicate behaves exactly like the equality inequality predicates but instead of testing for equality it tests whether one operand is distinct from the other Thus IS NOT DISTINCT treats NULL equals NULL as if it were true since one NULL or expression re solving to NULL is not distinct from another It is available in both DSQL and PSQL Syntax Pattern lt value gt IS NOT DISTINCT FROM lt value gt Examples 1 SELECT FROM T1 JOIN T2 ON T1 NAME IS NOT DISTINCT FROM T2 NAME 69 Data Manipulation Language DML SELECT FROM T WHERE T MARK IS DISTINCT FROM test Points to note 1 Because the DISTINCT predicate considers that two NULL values are not distinct it never evaluates to the truth value UNKNOWN Like the IS NOT NULL predicate it can only be True or False Read More About NULL For more understanding of the way NULL comparisons are evaluated please read the Fire bird Null Guide available through the Documentation Index at the Firebird website 2 The NOT DISTINCT predicate can be optimized using an index if one is available NULL Comparison Rule Relaxed D Yemanov A NULL literal can now be treated as a value in all expressions without returning a syntax error You may now specify expressions such as
159. nto the database header before the ALTER DATABASE BEGIN BACKUP statement returns 3 to merge differences into the database when asked ALTER DATABASE END BACKUP statement 4 to mark pages written by the engine with the current SCN page scan counter value for the database 5 to increment SCN on each change of backup state The backup state cycle is nbak_state_normal gt nbak_state_stalled gt nbak_state_merge gt nbak_state_normal e Tn normal state writes go directly to the main database files e Installed state writes go to the difference file only and the main files are read only s In merge state new pages are not allocated from difference files Writes go to the main database files Reads of mapped pages compare both page versions and return the version which is fresher because we don t know if it is merged or not Note This merge state logic has one quirky part Both Microsoft and Linux define the contents of file growth as undefined i e garbage and both zero initialize them This is why we don t read mapped pages beyond the original end of the main database file and keep them current in difference file until the end of a merge This is almost half of NBak fetch and write logic tested by using modified PIO on existing files containing garbage 134 Command line Utilities NBackup The functional responsibilities of NBackup are 1 to provide a conve
160. nts first lt lt second select bin_ shl flagsl 1 from x BIN_SHR BIN_SHR lt number gt lt number gt Returns the result of a binary shift right operation performed on the arguments first gt gt second select bin_ shr flags1 1 from x BIN_XOR BIN_XOR lt number gt lt number gt Returns the result of a binary XOR operation per formed on all arguments select bin_ xor flagsl flags2 from x BIT_LENGTH BIT_LENGTH lt string gt lt string_expr gt Returns the length of a string in bits 157 New Built in Functions Function Description select rdb relation_name bit_length rdb relation_name bit_length trim rdb relation_name from rdb relations CEIL CEIL CEILING tC lt number gt Returns a value representing the smallest integer that CEILING 1s greater than or equal to the input argument 1 select ceil val from x 2 select ceil 2 1 ceil 2 1 from rdb database returns 3 2 CHAR_LENGTH CHAR_LENGTH lt string gt Returns the number of characters in a string or ex lt string_expr gt pression result ARACTER_LENGTH select rdb relation_name char_length rdb relation_name char_length trim rdb relation_name from rdb relations COS COS lt number gt Returns the cosine of a number The angle is speci fied in radians and returns a value in
161. o no nono nono no nono no nono a 95 No Longer Reserved as Keywords isis A Ai aa 95 9 Indexing amp OPtIMIZAtiONS osson iiini ie tornando E E andado de e ideada 96 Optimizations in V2 TTT 96 Improved PLAN Clause cerne ereen eaor ee pi iene Ae tee 96 Optimizer Improvements ne a a aa Uae ae di dis 96 For All Databases sssini siinne aene din ee a Seinna hR LORH TRT 97 For ODS 11 Databases only tdi ds id cs GE Nae eh ae a 98 Enhancements to Indexing cion tiran ATT ERE eor tilda 99 252 byte index length limit 18 LONE vax CEST EE pat y fait daniel TE Cyder tr 99 Expression Indexes erir cece ee svccede ols ccenen lennon obs edd bees catbegeodenensdecdestse ceddenevecetacpueseuseseeeeeacge ces 99 Changes to Null keys handling 20 0 0 eee eee ceeeceeeceeeeeeeeeceeeeeeeceeeceseeeeeeeceeeeeeeeseeeeeeeeeeees 100 Improved Index Compression rrene 27 EL ces tag ses eee KT doa denen sseeseegedsvssevsseset sees teaeesssteseeserevesd 100 Selectivity Maintenance per Segment ocococoncnononononononononononononononononnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnininos 101 10 International Language Support INTL sese 102 New INTL Interface for Non ASCII Character Sets sese eee 102 Architecture cis oye ni ase A sees eed ee 102 Enhancements eve ON 102 INTL TTT 104 New Character Sets Collations ooccocccononononononononononononononononononononononononononononononononenenenenenes 105 Developments ani A gs sea Target re sjesuccsds by va NN 106 ICU Character
162. o supply run time identifiers e g CONCATENATION for derived fields where the developer has neglected to provide identifiers Important This setting affects all databases on the server and will potentially produce exceptions or unpredicted results where mixed applications are implemented UsePriorityScheduler A Peshkov Setting this parameter to zero now disables switching of thread priorities completely It affects only the Win32 SuperServer TCPNoNagle has changed K Kuznetzov The default value for TcpNoNagle is now TCP_NODELAY IPCName Behaviour Has Changed N Samofatov V 2 1 undocumented prior to V 2 1 3 I PCName defaulting to FIREBIRD since V 2 0 is the kernel names pace where the XNET instance for direct local connection on Windows is created On Vista and some other Windows platforms it was usually necessary to edit this parameter to add the prefix Global in order to ensure that the local client running under a restricted account would have the authority to create this namespace 152 New Configuration Parameters and Changes A change in V 2 1 made it so that the connection routine would apply the prefix to the default IpcName uncon ditionally if the user s first attempt failed due to restricted permissions Renamed Parameters SortMemBlockSize Changed to TempCacheBlockSize D Yemanov Considered more appropriate terminology SortMemUpperLimit Changed to TempCacheUpperLimit D
163. ocumented bug evolves into a feature Instant Fix for an Older Firebird Here s a tip if you want to do an instant fix for the problem in an older version of Firebird use the sync option when mounting any partition with a Firebird database on board An example of a line in etc fstab dev sda9 usr database ext3 noatime sync 1 2 Databases on Raw Devices A Peshkov File system I O can degrade performance severely when a database in Forced Writes mode grows rapidly On Linux which lacks the appropriate system calls to grow the database efficiently performance with Forced Writes can be as much as three times slower than with asynchronous writes When such conditions prevail performance may be greatly enhanced by bypassing the file system entirely and restoring the database directly to a raw device A Firebird database can be recreated on any type of block device Moving a Database to a Raw Device Moving your database to a raw device can be as simple as restoring a backup directly to an unformatted partition in the local storage system For example gbak c my fbk dev sda7 will restore your database on the third logical disk in the extended partition of your first SCSI or SATA hard drive disk0 Note The database does not have a database name other than the device name itself In the example given the name of the database is dev sda7 Special Issues for nbak nbackup The physical backup uti
164. olay ru CNT gt from rdb database RDBSSET_CONTEXT 3 75 Data Manipulation Language DML U means not defined already we have set it to ru SQL gt select rdb set_context USER_SESSION Nickolay ca CNT gt from rdb database RDBSSET_CONTEXT 1 means it was defined already we have changed it to ca SQL gt select rdb set_context USER_SESSION Nickolay NULL CNT gt from rdb database RDBSSET_CONTEXT 1 says it existed before we have changed it to NULL 1 e undefined it SQL gt select rdb set_context USER_SESSION Nickolay NULL CNT gt from rdb database RDBSSET_CONTEXT 0 since nothing actually happened this time it was already undefined Improvements in Handling User specified Query Plans D Yemanov 1 Plan fragments are propagated to nested levels of joins enabling manual optimization of complex outer joins 2 A user supplied plan will be checked for correctness in outer joins 3 Short circuit optimization for user supplied plans has been added 4 A user specified access path can be supplied for any SELECT based statement or clause Syntax rules The following schema describing the syntax rules should be helpful when composing plans PLAN lt stream_retrieval gt lt sorted_streams gt lt joined_streams gt lt stream_retrieval gt lt natural_scan gt
165. omitted a comma is used to separate the concatenated values Other Notes 1 Numeric and date time values are implicitly converted to strings during evaluation 2 The result value is of type BLOB with SUB_TYPE TEXT for all cases except list of BLOB with different subtype 3 Ordering of values within a group is implementation defined Examples PE AXI SELECT LIST ID FROM MY_TABLE B SELE TAG_TYPE LIST TAS VALURE ROM TAGS GROUP BY TAG_TYPE Q rd The RETURNING Clause Dmitry Yemanov Adriano dos Santos Fernandes v 2 1 The purpose of this SQL enhancement is to enable the column values stored into a table as a result of the NSERT UPDATE OR INSERT UPDATE and DELETE statements to be returned to the client The most likely usage is for retrieving the value generated for a primary key inside a BEFORE trigger The RETURNING clause is optional and is available in both DSQL and PSQL although the rules differ slightly In DSQL the execution of the operation itself and the return of the set occur in a single protocol round trip Because the RETURNING clause is designed to return a singleton set in response to completing an operation on a single record it is not valid to specify the clause in a statement that inserts updates or deletes multiple records 51 Data Manipulation Language DML Note In DSQL the statement always retu
166. on is different and addresses the known issues with the old protocol Like the old PServer implementation the XNET implementation uses shared memory for inter process com munication However XNET eliminates the use of window messages to deliver attachment requests and it also implements a different synchronization logic Benefits of the XNET Protocol over IPServer Besides providing a more robust protocol for local clients the XNET protocol brings some notable benefits e it works with Classic Server e it works for non interactive services and terminal sessions e it eliminates lockups when a number of simultaneous connections are attempted Performance The XNET implementation should be similar to the old IPServer implementation although XNET is expected to be slightly faster 11 New in Firebird 2 Disadvantages The one disadvantage is that the XNET and PServer implementations are not compatible with each other This makes it essential that your fbclient dll version should match the version of the server binaries you are using fbserver exe or fb_inet_server exe exactly It will not be possible to to establish a local connection if this detail is overlooked A TCP localhost loopback connection via an ill matched client will still do the trick of course Change to WNET NetBEUI Protocol WNET a k a NetBEUD protocol no longer performs client impersonation In all previous Firebird versions remote requests via W
167. ore insert or update ON atable as begin end Note the different positions of the clause identifying the table and the different keywords pointing to the table identifier existing FOR SQL2003 ON Both syntaxes are valid and are available also for all CREATE TRIGGER RECREATE TRIGGER and CRE ATE OR ALTER TRIGGER statements 39 Data Definition Language DDL SQL2003 Compliant Alternative for Computed Fields D Yemanov Feature request CORE 1386 v 2 1 SQL compliant alternative syntax GENERATED ALWAYS AS was implemented for defining a com puted field in CREATE ALTER TABLE Syntax Pattern lt column name gt lt type gt GENERATED ALWAYS AS lt expr gt It is fully equivalent semantically with the legacy form lt column name gt lt type gt COMPUTED BY lt expr gt Example CREATE TABLE T PK INT EXPR GENERATED ALWAYS AS PK 1 CREATE SEQUENCE D Yemanov SEQUENCE has been introduced as a synonym for GENERATOR in accordance with SQL 99 SEQUENCE is a syntax term described in the SQL specification whereas GENERATOR is a legacy InterBase syntax term Use of the standard SEQUENCE syntax in your applications is recommended A sequence generator is a mechanism for generating successive exact numeric values one at a time A sequence generator is a named schema object In dialect 3 it is a BIGINT in dialect 1 it is an INTEGER Syntax
168. otes 1 A PLAN clause may be used in all select expressions including subqueries derived tables and view defi nitions It can be also used in UPDATE and DELETE statements because they re implicitly based on select expressions 2 Ifa PLAN clause contains some invalid retrieval description then either an error will be returned or this bad clause will be silently ignored depending on severity of the issue 3 ORDER lt navigational_index gt INDEX lt filter_indices gt kind of plan is reported by the engine and can be used in the user supplied plans starting with FB 2 0 Improvements in Sorting A Brinkman Some useful improvements have been made to SQL sorting operations Order By or Group By lt alias name gt Column aliases are now allowed in both these clauses Examples 1 ORDER BY SELECT RDBSRELATION_ID AS ID ROM RDBSRELATIONS ORDER BY ID rj 2 GROUP BY SELECT RDB RELATION_NAME AS ID COUNT ROM RDB RELATION_FIELDS GROUP BY ID H GROUP BY Arbitrary Expressions A GROUP BY condition can now be any valid expression 78 Data Manipulation Language DML Example GROUP BY SUBSTRING CAST LA B 2 AS VARCHAR 15 FROM 1 FOR 2 Order Sets by Implicit Degree Number Order by degree ordinal column position now works on a select list Example SELECT FROM RDBSRELATIONS RDER BY 9 O Parameters and Ordinal Sorts a
169. page fetches MON SPAGE_MARKS number of pages with changes pending MON RECORD_STATS record level statistics MONSSTAT_ID s tatistics ID MON STAT_GROUP statistics group 0 database 1 attachment 2 transaction 3 statement 4 call MON RECORD_SEQ_READS number of records read sequentially MON RECORD_IDX_READS number of records read via an index MON RECORD_INSERTS number of inserted records MON RECORD_UPDATES number of updated records MON RECORD_DELETES number of deleted records MON RECORD_BACKOUTS number of records where a new primary record version or a change to an existing primary record version is backed out due to rollback or savepoint undo MON RECORD_PURGES number of records where record version chain is being purged of versions no longer needed by OAT or younger transactions MON RECORD_EXPUNGES number of records where record version chain is being deleted due to deletions by transactions older than OAT Note Textual descriptions of all state and mode values can be found in the system table RDB TYPES Usage Creation of a snapshot is usually quite a fast operation but some delay could be expected under high load especially in the Classic Server For guidelines to minimising the impact of monitoring under such conditions see Performance Guidelines below A valid database connection is required in order to retrieve the monitoring data The mo
170. password using a new secure method would be unable to use an older client to attach to the server Taking this into account with plans to upgrade some aspects of the API in the next version the decision was made not to change the password transmission method in Firebird 2 0 The immediate problem can be solved easily by using any IP tunneling software such as ZeBeDee to move data to and from a Firebird server for both 1 5 and 2 0 It remains the recommended way to access your remote Firebird server across the Internet Other Security Improvements isc_service_query wrongly revealed the full database file spec Feature request CORE 1091 V 2 1 When the server is configured DatabaseAccess None isc_service_query would return the full database file path and name It has been corrected to return the database alias one more argument in favour of making the use of database aliases standard practice Any user could view the server log through the Services API Feature request CORE 1148 This was a minor security vulnerability Regular users are now blocked from retrieving the server log using the Services API Requests are explicitly checked to ensure that the authenticated user is SYSDBA Dealing with the New Security Database A Peshkov If you try to put a pre Firebird 2 security database security fdb or a renamed isc4 gdb into Firebird s new home directory and then try to connect to the server you will get the messag
171. patterns REATE SEQUENCE GENERATOR lt name gt ROP SEQUENCE GENERATOR lt name gt GENERATOR lt name gt TO lt start_value gt TER SEQUENCE lt name gt RESTART WITH lt start_value gt EN ID lt name gt lt increment_value gt EXT VALUE FOR lt name gt S H D O bh Hop Examples 1 CREATE SEQUENCE S_EMPLOYEE 40 Data Definition Language DDL ALTER SEQUENCE S_EMPLOYEE RESTART WITH 0 See also the notes about NEXT VALUE FOR Warning ALTER SEQUENCE like SET GENERATOR is a good way to screw up the generation of key values REVOKE ADMIN OPTION D Yemanov SYSDBA the database creator or the owner of an object can grant rights on that object to other users However those rights can be made inheritable too By using WITH GRANT OPTION the grantor gives the grantee the right to become a grantor of the same rights in turn This ability can be removed by the original grantor with REVOKE GRANT OPTION FROM user However there s a second form that involves roles Instead of specifying the same rights for many users soon it becomes a maintenance nightmare you can create a role assign a package of rights to that role and then grant the role to one or more users Any change to the role s rights affect all those users By using WITH ADMIN OPTION
172. previously it showed in clear Firebird Services New Command line Utility fbsvcmgr Alex Peshkov V 2 1 The new utility fbsvcmgr provides a command line interface to the Services API enabling access to any service that is implemented in Firebird Although there are numerous database administration tools around that surface the Services API through graph ical interfaces the new tool addresses the problem for admins needing to access remote Unix servers in broad networks through a text only connection Previously meeting such a requirement needed a programmer Using fbsvcmgr fbsvcmgr does not emulate the switches implemented in the traditional g utilities Rather it is just a front end through which the Services API functions and parameters can pass Users therefore need to be familiar with 130 Command line Utilities the Services API as it stands currently The API header file ibase h in the include directory of your Firebird installation should be regarded as the primary source of information about what is available backed up by the InterBase 6 0 beta API Guide Parameters Specify the Services Manager The first required parameter for a command line call is the Services Manager you want to connect to e Fora local connection use the simple symbol service_mgr s To attach to a remote host use the format hostname service_mgr Specify subsequent service parameter blocks SPBs Subsequent SPBs with value
173. r another table expression e The same table expression can be used more than once in the main query e Table expressions as subqueries can be used in INSERT UPDATE and DELETE statements e Table expressions are legal in PSQL code e WITH statements can not be nested Example of a non recursive CTE WITH DEPT_YEAR_BUDGET AS SELECT FISCAL YEAR DEPT_NO SUM PROJECTED_BUDGET AS BUDGET FROM PROJ_DEPT_BUDGET GROUP BY FISCAL YEAR DEPT_NO 48 Data Manipulation Language DML SELECT D DEPT_NO D DEPARTMENT B_1993 BUDGET AS B_1993 B_1994 BUDGET AS B_1994 B_1995 BUDGET AS B_1995 B_1996 BUDGET AS B_1996 FROM DEPARTMENT D EFT JOIN DEPT_YEAR_BUDGET B_1993 ON D DEPT_NO B_1993 DEPT_NO AND B_1993 FISCAL_YEAR 1993 EFT JOIN DEPT_YEAR_BUDGET B_1994 ON D DEPT_NO B_1994 DEPT_NO AND B_1994 FISCAL_YEAR 1994 EFT JOIN DEPT_YEAR_BUDGET B_1995 ON D DEPT_NO B_1995 DEPT_NO AND B_1995 FISCAL_YEAR 1995 EFT JOIN DEPT_YEAR_BUDGET B_1996 ON D DEPT_NO B_1996 DEPT_NO AND B_1996 FISCAL YEAR 1996 WHERE EXISTS SELECT FROM PROJ_DEPT_BUDGET B WHERE D DEPT_NO B DEPT_NO Rules for Recursive CTEs A recu
174. r reference CORE 2376 e trusted makes use of Windows trusted authentication which under the right conditions may be the most secure way to authenticate on Windows e native sets the traditional Firebird server authentication mode requiring users to log in using a user name and password defined in the security database e mixed allows both RelaxedAliasChecking V Khorsun V 2 1 RelaxedAliasChecking is a new configuration parameter added to permit a slight relaxation of the Firebird 2 0 x restrictions on mixing relation aliases and table names in a query For example with Re laxedAliasChecking set to true 1 in firebird conf the following query will succeed in Firebird 2 1 whereas it would fail in v 2 0 x or in v 2 1 with the parameter set to its default of 0 SELECT ATABLE FIELD1 B FIELD2 FROM ATABLE A JOIN BTABLE B ON A ID BTABLE ID 149 New Configuration Parameters and Changes Caution Understand that this is a temporary facility whose purpose is to provide some headspace for migrating systems using legacy code that exploited the tolerance of InterBase and older Firebird server versions to non standard SQL usage e Don t enable this parameter if you have no offending code in your applications or PSQL modules It is not intended as an invitation to write sloppy code e Regard it as a time bomb It will be permanently removed from a future release
175. r set as NONE or UNICODE_FSS and you are using non UNICODE_FSS data 2 In reads from text BLOBs transliteration from the BLOB character set to the client character set was not being performed Repairing Your Metadata Text If your metadata text was created with non ASCII encoding you need to repair your database in order to read the metadata correctly after upgrading it to v 2 1 Important The procedure involves multiple passes through the database using scripts It is strongly recommended that you disconnect and reconnect before each pass The database should already have been converted to ODS11 1 by way of a gbak backup and restore Before doing anything make a copy of the database In the examples that follow the string broot represents the path to your Firebird installation root directory e g opt firebird 110 International Language Support INTL Create the procedures in the database 1 isql path to your database fdb 2 SQL gt input Sfbroot misc upgrade metadata metadata_charset_create sql Check your database 1 isql path to your database fdb 2 SQL gt select from rdb check_metadata The rdb check_metadata procedure will return all objects that are touched by it e Ifnoexception is raised your metadata is OK and you can go to the section Remove the upgrade procedures e Otherwise the first bad object is the last one listed before the exception Fixing the
176. rebird version ib_udf sql declaration that now causes conflict DECLARE EXTERNAL FUNCTION lower CSTRING 255 RETURNS CSTRING 255 FREE_IT ENTRY_POINT IB_UDF_lower MODULE NAME ib_udf The problem will be resolved in the latest version of the new ib_udf2 sql script where the old UDF is declared using a quoted identifier New declaration in ib_udf2 sql DECLARE EXTERNAL FUNCTION LOWER CSTRING 255 NULL RETURNS CSTRING 255 FREE_IT ENTRY_POINT IB_UDF_lower MODULE_NAME ib_udf Tip It is preferable to use the internal function LOWER than to call the UDF General UDF Changes Build Changes C Valderrama Contributors The FBUDF library no longer depends on lib fbclient to be built 148 Chapter 15 New Configuration Parameters and Changes Authentication A Peshkov V 2 1 On Windows server platforms from V 2 1 forward Authentication is used for configuring the server authentication mode if you need it to be other than the default native Important In Firebird 2 1 releases prior to v 2 1 3 the default was mixed If you are updating to v 2 1 3 from v 2 1 v 2 1 1 or v 2 1 2 and you did not set this parameter explicitly trusted user authentication will not work To revert to the pre 2 1 3 setting uncomment this parameter and set it to mixed Tracke
177. reining in the rogue statements Example As avery rough example the following statement will kill all statements currently running in the database other than any that belong to the separate connection that the SysAdmin is using himself delete from monSstatements where monSattachment_id lt gt current_connection Performance Guidelines for Using MON Under Load The monitoring is implemented around two cornerstones shared memory and notifications Shared Memory All server processes share some region of memory where the information about current activity is stored The information consists of multiple variable length items describing the details of various activities All items be longing to a particular process are grouped into a single cluster enabling them to be processed as a whole Collection of the monitoring information and population of the tables do not happen in real time Instead server processes write their data into the shared memory only when explicitly asked to Upon writing the newer clusters supersede the old ones Then when the shared memory region is being read the reading process scans all the clusters and performs garbage collection removing clusters that belong to dead processes and compacting the shared memory space Notifications Every server process has a flag that indicates its capability to react to someone s monitoring request as soon as it arrives When some user connection runs a query against some
178. ributes Naming Conventions The naming convention you should use is charset_collation For example create collation winl252_unicode for win1252 create collation win1252_unicode_ci for winl252 from winl252_ unicode case insensitive Note The character set name should be as in fbintl conf i e ISO8859_1 instead of ISO88591 for example Specific Attributes for Collations Note Some attributes may not work with some collations even though they do not report an error DISABLE COMPRESSIONS Disable compressions aka contractions changing the order of a group of characters Valid for collations of narrow character sets Format DISABLE COMPRESSIONS 0 1 Example DISABLE COMPRESSIONS 1 DISABLE EXPANSIONS Disable expansions changing the order of a character to sort as a group of characters Valid for collations of narrow character sets 108 International Language Support INTL Format DISABLE EXPANSIONS 0 1 1 Example DISABLE EXPANSIONS 1 ICU VERSION Specify what version of ICU library will be used Valid values are the ones defined in the config file intl fbintl conf in entry intl_module icu_versions Valid for UNICODE and UNICODE CI Format ICU VERSION default major minor Example ICU VERSION 3 0 LOCALE Specify the collation locale Valid for UNICODE and UNICODE_CI Requires complete version of ICU libraries Format LOCALE xx_XX Example LOCALE en_US MUL
179. ringl gt lt start gt lt length gt 1 The first position in a string is 1 not 0 2 If the lt start gt and or lt length gt argument evaluates to a non integer banker s rounding is applied 3 Fails with text BLOBs in a multi byte character set if greater than 1024 bytes If lt length gt is not specified CHAR_LENGTH lt string2 gt is implied PI PI no arguments Returns the PI constant 3 1459 val PI POSITION See below Returns the start position of the first string inside the second string relative to the beginning of the outer string In the second form an offset position may be supplied so that the function will ignore any match es occuring before the offset position and return the first match following that 162 New Built in Functions Function Format Description POSITION lt string gt IN lt string gt POSITION lt string gt lt string gt lt offset position gt select rdbSrelation_name from rdbSrelations where position RDB IN rdb relation_name 1 PR Ky position be To be or not to be 10 returns 17 The first occurrence of be occurs within the offset and is ignored position be To buy or not to buy 10 returns 0 because the searched substring was not found POWER POWER lt number gt lt number gt POWER X Y returns X to the power of Y select power x 10 from y RAND
180. rnings o utput lt file gt output file set output pag elength lt size gt page length p assword lt password gt connection password q uiet do not show the message Use CONNECT r ole lt role gt role name r2 lt role gt role uses quoted identifier sqldialect lt dialect gt SQL dialect set sql dialect 142 Command line Utilities t erminator lt term gt u ser lt user gt E 2 command terminator set term user name extract metadata show program and server version gsec Authentication Manager Changes to the gsec utility include gsec return code C Valderrama gsec now returns an error code when used as a non interactive utility Zero indicates success any other code indicates failure gfix Server Utility Changes to the gfix utility include New Shutdown States Modes N Samofatov D Yemanov The options for gfix shut down have been extended to include two extra states or modes to govern the shutdown New Syntax Pattern gfix lt command gt lt state gt lt options gt lt command gt shut online lt state gt normal multi single full lt options gt force tran attach lt timeout gt normal state online database multi state multi user shutdown mode the legacy one unlimited attachments of SYSDBA owner are allowed single state single user shutdown only one attachment is allowed
181. rns the set even if the operation has no effect on any record Hence at this stage of implementation the potential exists to return a set that is all nulls This may be changed in future In PSQL if no row was affected by the statement nothing is returned and values of the receiving variables are unchanged Support for this feature in Embedded SQL ESQL was added in v 2 1 6 Syntax Patterns INSERT INTO VALUES RETURNING lt column_list gt INTO lt variable_list gt INSERT INTO SELECT RETURNING lt column_list gt INTO lt variable_list gt UPDATE OR INSERT INTO VALUES RETURNING lt column_list gt INTO lt variable_list gt UPDATE RETURNING lt column_list gt INTO lt variable_list gt DELETE FROM RETURNING lt column_list gt INTO lt variable_list gt Rules for Using a RETURNING Clause 1 The INTO part i e the variable list is allowed in PSQL only for assigning the output set to local variables It is rejected in DSQL 2 The presence of the RETURNING clause causes an INSERT statement to be described by the API as isc_ info_sql_stmt_exec_procedure rather than isc_info_sql_stmt_insert Existing connec tivity drivers should already be capable of supporting this feature without special alterations 3 The RETURNING clause ignores any e
182. rocedural SQL Domains for Defining PSQL Variables and Arguments v 2 1 PSQL local variables and input and output arguments for stored procedures can now be declared using domains in lieu of canonical data types See Domains in PSQL NOT NULL Constraint on Stored Procedure Parameters and Variables v 2 1 Input and output arguments and PSQL local variables can be constrained as non nullable using the optional keywords NOT NULL in their declarations See NOT NULL in Stored Procedures COLLATE in Stored Procedure Parameters and Variables v 2 1 Collations can now be applied to PSQL variables and arguments See COLLATE in Stored Procedures Enhancement to PSQL error stack trace V Khorsun Feature request CORE 970 v 2 1 A PSQL error stack trace now shows line and column numbers Security Windows Security to Authenticate Users v 2 1 Windows Trusted User security can be applied for authenticating Firebird users on a Windows host See Windows Trusted User Security International Language Support The CREATE COLLATION Command v 2 1 The DDL command CREATE COLLATION has been introduced for implementing a collation obviating the need to use the script for it See CREATE COLLATION statement Unicode Collations Anywhere v 2 1 Two new Unicode collations can be applied to any character set using a new mechanism See UNICODE Collations New in Firebird 2 Platform Support Ports to Windows 2003 64 bit
183. rsive CTE is self referencing has a reference to itself A recursive CTE is a UNION of recursive and non recursive members At least one non recursive member anchor must be present Non recursive members are placed first in the UNION Recursive members are separated from anchor members and from one another with UNION ALL clauses 1 e non recursive member anchor UNION ALL DISTINCT non recursive member anchor UNION ALL DISTINCT non recursive member anchor UNION ALL recursive member UNION ALL recursive member References between CTEs should not have loops Aggregates DISTINCT GROUP BY HAVING and aggregate functions SUM COUNT MAX etc are not allowed in recursive members A recursive member can have only one reference to itself and only in a FROM clause A recursive reference cannot participate in an outer join Example of a recursive CTE 49 Data Manipulation Language DML WITH URSIVE DEPT_YEAR_BUDGET AS E ta Q SELECT FISCAL YEAR DEPT_NO SUM PROJECTED_BUDGET AS BUDGET FROM PROJ_DEPT_BUDGET GROUP BY FISCAL YEAR DEPT_NO G EPT TREE AS SELECT DEPT_NO HEAD DEPT DEPARTMENT CAST AS VARCHAR 255 AS INDENT FROM DEPARTMENT WHERE HEAD DEPT IS NULL UNION ALL SELECT D DEPT_NO D H
184. ructure isc_info_active_tran_count Returns the number of currently active transactions isc_info_creation_date Returns the date and time when the database was re created To decode the returned value call isc_vax_integer twice to extract first the date and second the time portions of the ISC_TIMESTAMP Then use isc_decode_timestamp as usual Additions to the isc_transaction_info Structure V Khorsun The following items have been added to the isc_transaction_info function call structure isc_info_tra_oldest_interesting Returns the number of the oldest interesting transaction when the current transaction started For snapshot transactions this is also the number of the oldest transaction in the private copy of the transaction inventory page TIP isc_info_tra_oldest_active s For aread committed transaction returns the number of the current transaction s For all other transactions returns the number of the oldest active transaction when the current transaction started isc_info_tra_oldest_snapshot Returns the number of the lowest tra_oldest_active of all transactions that were active when the current transaction started Note This value is used as the threshold high water mark for garbage collection 20 Changes to the Firebird API and ODS isc_info_tra_isolation Returns the isolation level of the current transaction The format of the returned clumplets is isc_info_tra_isolation
185. s refer to the v 2 0 1 release notes 120 Chapter 12 Security In this chapter are details of the changes to Firebird security that came with the release of Firebird 2 and its successors Further changes and enhancements introduced in V 2 1 are highlighted Summary of Changes Improving security has had a lot of focus in Firebird 2 0 development The following is a summary of the major changes New security database The new security database is renamed as security2 fdb Inside the user authentication table where user names and passwords are stored is now called RDBSUSERS There is no longer a table named users but a new view over RDB USERS that is named USERS Through this view users can change their passwords For details of the new database see New Security Database in the section about authentication later in this chapter For instructions on updating previous security databases refer to the section Dealing with the New Security Database at the end of this chapter Using Windows Security to Authenticate Users V 2 1 From Firebird 2 1 onward Windows Trusted User security can be applied for authenticating Firebird users on a Windows host The Trusted User s security context is passed to the Firebird server instead of the Firebird user name and password and if it succeeds it is used to determine the Firebird security user name For details see the section below Windows Trusted User Security
186. s Santos Fernandes V 2 1 x Casting of compatible values or expressions can now be made to a domain after the manner of variable declarations in procedural SQL Syntax Pattern CAST lt value gt lt expression gt AS lt builtin data type gt lt domain name gt TYPE OF lt domain name gt Examples CREATE DOMAIN DOM AS INTEGER SELECT CAST 10 44 AS TYPE OF DOM AN_INTEGER FROM RDBSDATABASE SELECT CAST 3 142 2 AS DOM AN_INTEGER FROM RDBSDATABASE Note Directly casting to lt domain name gt applies any default or constraint defined for the domain TYPE OF lt do main name gt gets only the data type of the domain and ignores any other attributes Expression Arguments for SUBSTRING O Loa D Yemanov V 2 0 x The built in function SUBSTRING can now take arbitrary expressions in its parameters Formerly the inbuilt SUBSTRING function accepted only constants as its second and third arguments start position and length respectively Now those arguments can be anything that resolves to a value including host parameters function results expressions subqueries etc Tip If your attempts to use this feature fail with invalid token errors bear in mind that expression arguments often need to be enclosed in brackets 61 Data Manipulation Language DML
187. s aborted and the exception is returned to the client Note For two phase transactions the triggers are fired in the prepare not in the commit TRANSACTION ROLLBACK Triggers are fired during the roll back of the transaction Changes done will be rolled back with the trans action Exceptions are swallowed Syntax lt database trigger gt CREATE RECREATE CREATE OR ALTER TRIGGER lt name gt ACTIVE INACTIVE ON lt event gt POSITION lt n gt AS BEGIN Gl Uo CONNECT DISCONNECT TRANSACTION START TRANSACTION COMMIT TRANSACTION ROLLBACK Rules and Restrictions 1 Database triggers type cannot be changed 2 Permission to create recreate create or alter or drop database triggers is restricted to the database owner and SYSDBA 36 Data Definition Language DDL Utilities Support for Database Triggers New parameters were added to gbak nbackup and isql to suppress database triggers from running They are available only to the database owner and SYSDBA gbak nodbtriggers isql nodbtriggers nbackup T Global Temporary Tables Vlad Khorsun v 2 1 Global temporary tables GTTs are tables that are stored in the system catalogue with permanent meta data but with temporary data Data from different connections or transactions depending on the scope are isolated from each other but th
188. s if required follow Any SPB can be optionally prefixed with a single symbol For the long command lines that are typical for fbsvcmgr use of the improves the readability of the command line Compare for example the following each a single command line despite the line breaks printed here fobsvcmgr service_mgr user sysdba password masterke action_db_stats dbname employee sts_hdr_pages and T fbsvemgr service_mgr user sysdba password masterke action_db_stats dbnam mploy sts_hdr_pages SPB Syntax The SPB syntax that fbsvcmgr understands closely matches with what you would encounter in the ibase h include file or the InterBase 6 0 API documentation except that a slightly abbreviated form is used to reduce typing and shorten the command lines a little Here s how it works All SPB parameters have one of two forms 1 isc_spb_VALUE or 2 isc_VALUE1_svc_VALUE2 For fbsvcmgr you just need to pick out the VALUE VALUE1 or VALUE2 part s when you supply your parameter T Accordingly for 1 you would type simply VALUE while for 2 you would type VALUE1_VALUE2 For example isc_spb_dbname gt dbname isc_action_svc_backup gt action_backup isc_spb_sec_username gt sec_usernam isc_info_svc_get_env_lock gt info_get_env_lock and so on Note An exception is isc_spb_user_name it can be specified as either user_name or simply user 131 Command line Utilities
189. s regular indices except that by def inition they cannot be composite multi segment Changes to Null keys handling V Khorsun A Brinkman e Null keys are now bypassed for uniqueness checks V Khorsun If a new key is inserted into a unique index the engine skips all NULL keys before starting to check for key duplication It means a performance benefit as from v 1 5 on NULLs have not been considered as duplicates NULLs are ignored during the index scan when it makes sense to ignore them A Brinkman Prevously NULL keys were always scanned for all predicates Starting with v 2 0 NULL keys are usually skipped before the scan begins thus allowing faster index scans Note The predicates IS NULL and IS NOT DISTINCT FROM still require scanning of NULL keys and they disable the aforementioned optimization Improved Index Compression A Brinkman A full reworking of the index compression algorithm has made a manifold improvement in the performance of many queries 100 Indexing amp Optimizations Selectivity Maintenance per Segment D Yemanov A Brinkman Index selectivities are now stored on a per segment basis This means that for a compound index on columns A B C three selectivity values will be calculated reflecting a full index match as well as all partial matches That is to say the selectivity of the multi segment index involves those of segment A alone as it would be if it
190. s reversion to bad old behaviour does not in any way imply that such queries will magically return correct results A BLOB cannot be converted to a sortable type and so as previously DISTINCT sortings and ORDER BY arguments that involve BLOBs will use the BLOB_ID GROUP BY arguments that are BLOB types will prepare successfully but the aggregation will be non existent Built in Functions v 2 1 Some existing built in functions have been enhanced while a large number of new ones has been added New Built in Functions Adriano dos Santos Fernandes Oleg Loa Alexey Karyakin A number of built in functions has been implemented in V 2 1 to replace common UDFs with the same names The built in functions will not be used if the UDF of the same name is declared in the database Note The choice between UDF and built in function is decided when compiling the statement If the statement is compiled in a PSQL module whilst the UDF is available in the database then the module will continue to require the UDF declaration to be present until it is next recompiled The new built in function DECODE does not have an equivalent UDF in the libraries that are distributed with Firebird The functions are detailed in Appendix A Note Several of these built in functions were already available in Firebird 2 ODS 11 viz LOWERO TRIMO BIT_LENGTH CHAR_LENGTHO and OCTET_LENGTHO 58 Data Manipulation Langua
191. s used if no collation is specified For example isql q ch dos850 SQL gt create database test fdb SQL gt create table t c char 1 character set dos850 SOL gt insert into t values a SOL gt insert into t values e SOL gt insert into t values SOL gt insert into t values SQL gt SQL gt select c upper c from t C UPPER DD w D v fH PR In Firebird 2 the result is LC UPPER MoD mh eA D Maximum String Length In v 1 5 x the engine does not verify the logical length of multi byte character set MBCS strings Hence a UNICODE FSS field takes three times as many characters as the declared field size three being the maximum length of one UNICODE_FSS character This has been retained for compatibility for legacy character sets However new character sets UTF8 for example do not inherit this limitation 103 International Language Support INTL sqlsubtype and Attachment Character Set When the character set of aCHAR or VARCHAR column is anything but NONE or OCTETS and the attachment character set is not NONE the sq subtype member of an XSQLVAR pertaining to that column now contains the attachment connection character set number instead of the column s character set Enhancements for BLOBs Several character set related enhancements have been added for text BLOBs COLLATE clauses for BLOBs A DML COLLATE clause is now allowed with BLOBs E
192. same as would be returned by the CURRENT_USER pseudo variable or the predefined variable USER e CURRENT_ROLE Current role for the connection Returns the same value as the CURRENT_ROLE pseudo variable Notes To avoid DoS attacks against the Firebird Server the number of variables stored for each transaction or session context is limited to 1000 Example of Use A set term create procedur set_context User_ ID varchar 40 begin RDBSSET_CONTEXT USER_TRANSACTION Trn_1D Trn_ID RDBSSET_CONTEXT USER_TRANSACTION User_ID User 1D end create table journal jrn_id integer not null primary key jrn_lastuser varchar 40 jrn_lastaddr varchar 255 jrn_lasttransaction integer A CREATE TRIGGER UI_JOURNAL FOR JOURNAL BEFORE INSERT OR UPDATI as begin new jrn_lastuser new jrn_lastaddr end commit A Ej Trn_ID integer as rdb get_context USER_TRANSACTION User_ID rdb get_context SYSTEM CLIENT_ADDR new jrn_lasttransaction rdb get_context USER_TRANSACTION Trn_ID xecute procedur insert into journal jrn_id A set term set_context skidder 1 values 0 A ESS Since rdb set_context returns or zero it can be made to work with a simple SELECT statement Example SQL gt select rdb set_context USER_ ESSION Nick
193. se The Linux sh4 Renesas SH platform is now supported By N Iwamatsu A Peshkov Details see tracker ticket CORE 2655 Improvements The optimizer now estimates the actual record compression ratio By D Yemanov Details see tracker ticket CORE 3295 On Windows the XNET_CONNECT_TIMEOUT in xnet h has been made user configurable By D Yemanov Details see tracker ticket CORE 2769 Memory consumption of lt page size gt bytes for each temporary blob is now avoided By A dos Santos Fernandes Details see tracker ticket CORE 1658 When checking privileges more information is available about the table that a column belongs to By A Peshkov Details see tracker ticket CORE 1234 Bugs Fixed A large number of bug fixes has been performed These are detailed in the first section of the accompanying Bug Fixes document for Firebird 2 1 x Patch Release 2 1 3 Important Firebird 2 1 3 corrects several bugs discovered soon after the release of V 2 1 2 This sub release completely supersedes V 2 1 2 as well as V 2 1 1 and the original V 2 1 The previous 2 1 x releases should be replaced with V 2 1 3 once it is in full release Issues to Note The default authentication method on Windows platforms is no longer mixed It has been changed to native For more information see the notes on the configuration parameter Authentication A fix in Firebird 2 5 that alleviates the hogging beha
194. se operations Improved UNIQUE Retrieval A Brinkman The optimizer will now use a more realistic cost value for unique retrieval More Optimization of NOT Conditions D Yemanov NOT conditions are simplified and optimized via an index when possible Example Distribute HAVING Conjunctions to the WHERE Clause If a HAVING clause or any outer level select refers to a field being grouped by this conjunct is distributed deeper in the execution path than the grouping thus allowing an index scan to be used In other words it allows the HAVING clause not only be treated as the WHERE clause in this case but also be optimized the same way 97 Indexing amp Optimizations Examples select rdb relation_id count from rdbSrelations group by rdb relation_id having rdb relation_id gt 10 select from select rdb relation_id count from rdbSrelations group by rdbSrelation_id as grp id cnt where grp id gt 10 In both cases an index scan is performed instead of a full scan Distribute UNION Conjunctions to the Inner Streams Distribute UNION conjunctions to the inner streams when possible Improved Handling of CROSS JOIN and Merge SORT Improved cross join and merge sort handling Better Choice of Join Order for Mixed Inner Outer Joins reasonable join order for intermixed inner and outer joins Equality Comparison on Expressions MERGE PLAN may now be generated for joins using equality comparsio
195. ser_password dpb isc_dpb_password dpb user_password_length memcpy dpb user_password user_password_length dpb user_password_length 22 Changes to the Firebird API and ODS Force an immediate full database shutdown dpb isc_dpb_shutdown dpb 1 dpb isc_dpb_shut_force isc_dpb _shut_full const int dpb_length dpb dpb_buffer isc_attach_database status_vector 0 employee db handle dpb_length dpb_buffer if status_vector 0 1 amp amp status_vector 1 isc_print_status status_vector else isc_detach_database status_vector amp handle ODS On Disk Structure Changes On disk structure ODS changes include the following New ODS Number Firebird 2 1 creates databases with an ODS On Disk Structure version of 11 1 Size limit for exception messages increased V Khorsun Maximum size of exception messages raised from 78 to 1021 bytes New Description Field for Generators C Valderrama Added RDB DESCRIPTION to RDB GENERATORS so now you can include description text when creating generators New Description Field for SQL Roles C Valderrama Added RDB DESCRIPTION and RDB S YSTEM_FLAG to RDB ROLES to allow description text and to flag user defined roles respectively 23 Changes to the Firebird API and ODS ODS Type Recognition N Samofatov Introduced a concept of ODS type to distinguish betwe
196. sion sss sss secsssesecesebessse vesdetseuetedensbesccbsessbecdesvesdetseuebecdensteseceoess 79 A A RN 80 SELECT Statement amp Expression Syntax esse eee 80 Data Type of an Aggregation Result ooococcconcnononononnnnnnnnnnnnnnnnonnnononononononnnnnnnnnnnnnnnnnnnnnnnos 81 A Useful Trick with Date BI T 82 7 Procedural SOL PSOL uinti n in EE Ea EA EE AEREE EEEE EAA EATE ENERE 83 ure k Er eoa Dra as A R A A E TTT 83 Domains an PSOL e aaa A IA hae ec eta 83 COLLATE in Stored Procedure Parameters and Variables sss sese sese 84 NOT NULL Supported in Stored Procedure Parameters and Variables sss sss sees sese 84 WHERE CURRENT OF Now Allowed for Views sss sees eee 84 Context Variable ROW COUNT Enhanced sees eee 85 Explicit Cursors ci n ideada E pa dde Sevens 85 Defaults for Stored Procedure Arguments sss esse esse eee ee eee ee eee 86 vi Firebird 2 1 Release Notes LEAVE lt label gt Syntax Support rarse a TRE nd 88 OLD Context Variables Now Read only sese 90 PSOL Stack Traci A aaa 90 Call a UDF as a Void Function Procedure 0 0 0 0 eass x xag zo sa sg egaa zat eoat siis ah yeaa yan 04434 92 8 New Reserved Words and Changes vcccccc cscecccceccgecseceacdeonteenseseabecceesceadeacdbaectendeendeswauesceonenenseascubedeens 93 Newly Reserved STS D 93 Changed from Non reserved to Reserved sese 93 Keywords Added as Non reserved TT 93 Keywords No Longer Reserved c oooooooccococococoooooononono nono conoce nono nono non
197. t More about error line numbers DDL errors will be caught when being prepared or executed by default since isql uses AUTODDL ON by default However if AUTO DLL is OFF the server only complains when the script does an explicit COMMIT and this may involve several SQL statements The feature can be enabled disabled interactively or from a script by means of the command SET BAIL ON OFF As is the case with other SET commands simply using SET BAIL will toggle the state between activated and deactivated Using SET will display the state of the switch among many others 138 Command line Utilities e Even if BAIL is activated it doesn t mean it will change isql behavior An additional requirement should be met the session should be non interactive A non interactive session happens when the user calls isql in batch mode giving it a script as input Example isql b i my_fb sql o results log m m2 Tip However if the user loads isql interactively and later executes a script with the input command this is considered an interactive session even though isql knows it is executing a script Example isql Use CONNECT or CREATE DATABASE to specify a database SQL gt set bail SQL gt input my_fb sql SQL gt Z Whatever contents the script has it will be executed completely errors and all even if the BAIL option is enabled m2 to Output Stats and Plans C Valderram
198. t null was not normally recognized Several UDFs including the ib_udf library distributed with Firebird assumed that an empty string was more likely to signal a null parameter than a string of length zero The trick may work with CHAR type since the minimum declared CHAR length is one and would contain a blank character normally hence binary zero in the first position would have the effect of signalling NULL However but it is not applicable to VARCHAR or CSTRING where a length of zero is valid The other solution was to rely on raw descriptors but this imposes a lot more things to check than they would want to tackle The biggest problem is that the engine won t obey the declared type for a parameter it will simply send whatever data it has for that parameter so the UDF is left to decide whether to reject the result or to try to convert the parameter to the expected data type Since UDFs have no formal mechanism to signal errors the returned value would have to be used as an indicator The basic problem was to keep the simplicity of the typical declarations no descriptors while at the same time being able to signal null The engine normally passed UDF parameters by reference In practical terms that means passing a pointer to the data to tell the UDF that we have SQL NULL However we could not impose the risk of crashing an unknown number of different existing public and private UDFs that do not expect NULL The syntax had to be enh
199. t table name gt lt joined table gt lt derived table gt lt joined table gt lt cross join gt lt qualified join gt lt cross join gt lt table expression gt CROSS JOIN lt table expression gt lt qualified join gt lt table expression gt INNER LEFT RIGHT FULL OUTER JOIN lt table expression gt ON lt join condition gt 80 Data Manipulation Language DML lt derived table gt lt select expression gt Conclusions e FOR UPDATE mode and row locking can only be performed for a final dataset they cannot be applied to a subquery e Unions are allowed inside any subquery e Clauses FIRST SKIP PLAN ORDER BY ROWS are allowed for any subquery Notes e Either FIRST SKIP or ROWS is allowed but a syntax error is thrown if you try to mix the syntaxes An INSERT statement accepts a select expression to define a set to be inserted into a table Its SELECT part supports all the features defined for select statments expressions UPDATE and DELETE statements are always based on an implicit cursor iterating through its target table and limited with the WHERE clause You may also specify the final parts of the select expression syntax to limit the number of affected rows or optimize the statement Clauses allowed at the end of UPDATE DELETE statements are PLAN ORDER BY and ROWS Data Type of an Aggregation Result Arno Brinkman When aggregations CASE evalu
200. t type 0 44 Data Definition Language DDL SQL gt declare filter pesh2 input_type xdr output_type text CON gt entry_point p2 module_name p SQL gt show filter pesh2 BLOB Filter PESH2 Input subtype 100 Output subtype 1 Filter library is p Entry point is p2 45 Data Manipulation Language DML In this chapter are the additions and improvements that have been added to the SQL data manipulation language subset in the Firebird 2 development cycle Those marked as introduced in v 2 1 are available only to ODS 11 1 and higher databases Important A new configuration parameter named RelaxedAliasChecking was added to the firebird conf in Firebird 2 1 to permit a slight relaxation of the Firebird 2 0 x restrictions on mixing relation aliases and table names in a query see DSQL Parsing of Table Names is Stricter below This parameter will not be a permanent fixture in Firebird but is intended as a migration aid for those needing time to adjust existing code More information under RelaxedAliasChecking in the chapter New Configuration Parameters Quick Links e Common Table Expressions e LIST Function e RETURNING Clause e UPDATE OR INSERT Statement e MERGE Statement e New JOIN Types NAMED COLUMNS amp NATURAL JOIN CROSS JOIN s INSERT with Defaults e Text BLOB Compatibility e Compare BLOB BLOB e Sorting on BLOBs s RDBS DB_KEY Returns NULL in Outer Joins
201. tabase The engine offers a set of so called virtual tables that provides the user with a snapshot of the current activity within the given database The word virtual means that the table data is not materialised until explicitly asked for However the metadata of the virtual table is stable and can be retrieved from the schema Note Virtual monitoring tables exist only in ODS 11 1 and higher databases so a migration via backup restore is required in order to use this feature The Concept The key term of the monitoring feature is an activity snapshot It represents the current state of the database comprising a variety of information about the database itself active attachments and users transactions prepared and running statements and more A snapshot is created the first time any of the monitoring tables is being selected from in the given transaction and it is preserved until the transaction ends in order that multiple table queries e g master detail ones will always return a consistent view of the data In other words the monitoring tables always behave like a snapshot table stability consistency transaction even if the host transaction has been started with a lower isolation level To refresh the snapshot the current transaction should be finished and the monitoring tables should be queried in a new transaction context Scope and Security e Access to the monitoring tables is availabl
202. tasartyapssssevatenahat oair a arion a aost 168 Appendix C Security Database Upgrade for Firebird 2 sss 174 Security Upgrade Script aiii dd OaE DE ER 174 ix List of Tables 16 1 Firebird Development Teams Chapter 1 General Notes Firebird 2 1 is a full version release that builds on the architectural changes introduced in the V 2 0 series Thanks to all who field tested the Alphas and Betas during 2007 and the first quarter of 2008 we have a release that is bright with new features and improvements including the long awaited global temporary tables a catalogue of new run time monitoring mechanisms database triggers and the injection of dozens of internal functions into the SQL language set Firebird 2 1 x End of Life Patch release 2 1 7 is the final sub release of the v 2 1 series That means there will be no further updates to fix either bugs or security vulnerabilities Patch Release 2 1 7 This last sub release of the v 2 1 series fixes bugs addressed since V 2 1 6 including a recently exposed vul nerability in the Superserver that could make it crash with a segmentation fault caused by a malformed network packet It does not affect the Classic server model The vulnerability was patched by Alex Peshkov All Firebird 2 1 x Superserver binaries released with build numbers 18547 or lower and any SS snapshot builds before 2014 12 03 have this vulnerability For details of this and other patches in v 2 1 7
203. te time to cause every CHAR VARCHAR declaration that does not specifically include a CHARACTER SET clause to use this default At attachment time you normally specify the character set that the client is to use to read strings If no client or connection character set is specified character set NONE is assumed Two special character sets NONE and OCTETS can be used in declarations However OCTETS cannot be used as a connection character set The two sets are similar except that the space character of NONE is ASCII 0x20 whereas the space character OCTETS is 0x00 NONE and OCTETS are special in the sense that they follow different rules from those applicable to other character sets regarding conversions e With other character sets conversion is performed as CHARSET1 gt UNICODE gt CHARSET2 e With NONE OCTETS the bytes are just copied NONE OCTETS gt CHARSET2 and CHARSET1 gt NONE OCTETS Enhancements Enhancements that the new system brings include 102 International Language Support INTL Well formedness checks Some character sets especially multi byte do not accept just any string Now the engine verifies that strings are well formed when assigning from NONE OCTETS and when strings sent by the client the statement string and parameters Uppercasing In Firebird 1 5 x only the ASCI equivalent characters are uppercased in any character set s default binary collation order which is the one that i
204. that caused the failure related to the whole script This feature works even if there are nested scripts namely if script SA includes script SB and SB causes a failure the line number is related to SB When SB is read completely isql continues executing SA and then isql continues counting lines related to SA since each file gets a separate line counter A script SA includes SB when SA uses the INPUT command to load SB Lines are counted according to what the underlying IO layer considers separate lines For ports using EDITLINE a line is what readline provides in a single call The line length limit of 32767 bytes remains unchanged Enhanced Command line Help M Kubecek When unknown parameters are used isql now shows all of the command line parameters and their explanations instead of just a simple list of allowed switches opt firebird bin isql Unknown switch 7 usage isql options lt database gt a all extract metadata incl legacy non SQL tables b ail bail on errors set bail on c ache lt num gt number of cache buffers ch arset lt charset gt connection charset set names d atabase lt database gt database name to put in script creation e cho echo commands set echo on ex tract extract metadata i nput lt file gt input file set input m erge merge standard error m2 merge diagnostic n oautocommit no autocommit DDL set autoddl off now arnings do not show wa
205. the file system to preallocate disk space in relatively large chunks Preallocation also gives room to avoid corruptions in the event of an out of disk space condition Chances are that the database will have enough space preallocated to continue operating until the administrator can make some disk space available Important Windows Only for Now Currently only Windows file systems publish such API calls which means that for now this aspect of the solution is supported only in the Windows builds of Firebird However similar facilities have recently been added to the Linux API allowing the prospect that a suitable API function call will appear in such popular file systems as ext3 in future DatabaseGrowthIncrement Configuration Parameter For better control of disk space preallocation the new parameter DatabaseGrowthIncrement has been added to firebird conf It represents the upper limit for the preallocation chunk size in bytes Important Please be sure to read the details regarding this configuration under DatabaseGrowthIncrement in the chapter entitled New Configuration Parameters and Changes 31 Global Improvements in Firebird 2 1 Bypass Filesystem Caching on Superserver V Khorsun Feature requests CORE 1381 and CORE 1480 Firebird uses and maintains its own cache in memory for page buffers The operating system in turn may re cache Firebird s cache in its own filesystem ca
206. the grantor typically the role creator gives the grantee the right to become a grantor of the same role in turn Until FB v2 this ability couldn t be removed unless the original grantor fiddled with system tables directly Now the ability to grant the role can be removed by the original grantor with REVOKE ADMIN OPTION FROM user SET DROP DEFAULT Clauses for ALTER TABLE C Valderrama Domains allow their defaults to be changed or dropped It seems natural that table fields can be manipulated the same way without going directly to the system tables Syntax Pattern ALTER TABLE t ALTE ALTER TABLE t ALTE COLUMN c SET DEFAULT default_value COLUMN c DROP DEFAULT Note e Array fields cannot have a default value e Ifyou change the type of a field its default may remain in place This is because a field could be changed to a defaulted domain while the field definition itself could override the domain s default On the other hand 1f the field is given a new type directly any default belongs logically to the field and is maintained on the implicit domain created for 1t behind the scenes 41 Data Definition Language DDL Syntaxes for Changing Exceptions D Yemanov The DDL statements RECREATE EXCEPTION and CREATE OR ALTER EXCEPTION feature request SF 1167973 have been implemented allowing either creating recreating or altering a custom exception depend in
207. the range 1 to 1 select cos x from y COSH COSH lt number gt Returns the hyperbolic cosine of a number select cosh x from y COT COT lt number gt Returns 1 tan argument select cot x from y DATEADD See below Returns a date time timestamp value increased or decreased when negative by the specified amount of time Format DATEADD lt number gt lt timestamp_part gt TO lt date_time gt DATEADD lt timestamp_part gt lt number gt lt date_time gt timestamp_part YEAR MONTH DAY HOUR MINUTE SECOND MILLISECOND 158 New Built in Functions Function Description 1 YEAR MONTH and DAY cannot be used with time values 2 HOUR MINUTE SECOND and MILLISECOND cannot be used with date values 3 All timestamp_part values can be used with timestamp values Example select dateadd day 1 current_date as yesterday from rdb database or expanded syntax select dateadd 1 day to current_date as yesterday from rdb database DATEDIFF See below Returns an exact numeric value representing the in terval of time from the first date time timestamp val ue to the second one Format DATEDIFF lt timestamp_part gt FROM lt date_time gt TO lt date_time gt DATEDIFF lt timestamp_part gt lt date_time gt lt date_time gt timestamp_part YEAR MONTH DAY HOU
208. the server and opens a gate for remote code execution The vulnerability was patched by Alex Peshkov All Firebird 2 1 x binaries released with build numbers 18496 or lower and all snapshot builds before 2013 03 08 have this vulnerability Patch Release 2 1 5 This sub release fixes bugs addressed since Patch Release 2 1 4 in March 2011 This latest release benefits from fixes to bugs detected not just in older sub releases but also from bug fixes and some improvements backported from the ongoing development of versions 2 5 x and the alpha v 3 The bug fixes are detailed in the accompanying release of the Cumulative Bug Fixes document You can also download this document or read it on line by visiting this resource page at the Firebird website Improvements Two notable improvements in this sub release are The query optimizer now estimates the actual record compression ratio By D Yemanov Details see tracker ticket CORE 3457 e Performance of gbak during the record insertion phase of restores has been improved By A dos Santos Fernandes Details see tracker ticket CORE 3433 Patch Release 2 1 4 As usual with patch releases this sub release fixes bugs addressed since the previous patch release During the 18 months that have passed since Firebird 2 1 3 this latest release takes benefits from some improvements deriving from the ongoing development of forward full release versions General Notes Additions in this Sub relea
209. time to execute In the meantime other activity both running statements and new connection attempts may be blocked until the monitoring request completes Note Improved in v2 1 3 Sometimes an out of memory condition can cause monitoring requests to fail or cause other worker pro cesses to swap A major source of this problem is the fact that every record in MON STATEMENTS has a blob MONSSQL_TEXT which is created for the duration of the monitoring transaction In V 2 1 x every blob occupies lt page size gt bytes of memory even if its actual content is smaller When the number of prepared statements in the system is huge it becomes possible to exhaust memory and get this failure Another possible source of this memory exhaustion could be the temporary in practice very short lived growth of the transaction pool into which the monitoring data are cached while the clusters are being merged into a single fragment Note There is no relief for this condition in v 2 1 3 However it has been improved for v 2 5 0 More Context Information More context information about the server and database SYSTEM is available via SELECT calls to the RDB GET_CONTEXT function including the engine version Example ELECT RDBSGET_CONTEXT SYSTEM ENGINE_VERSION 119 Administrative Features FROM RDBSDATABAS Gl For detailed information about using these context call
210. tion gbak can be used to restore a database on any version of Firebird Hide User Name amp Password in Shell A Peshkov Feature request CORE 867 V 2 1 gbak now changes param0 to prevent the user name and password from being displayed in ps axf gbak V and the Counter Parameter During Firebird 1 development an optional numeric lt counter gt argument was added to the V erbose switch of gbak for both backup and restore It was intended to allow you to specify a number and get a running count of rows processed as the row counter passed each interval of that number of rows It caused undesirable side effects and was removed before Firebird 1 0 was ever released So although it never happened it was documented as implemented in the release notes and other places ISQL Query Utility Work on ISQL has involved a lot of bug fixing and the introduction of a few new useful features 137 Command line Utilities One trick to note is that CHAR and VARCHAR types defined in character set OCTETS alias BINARY now display in hex format Currently this feature cannot be toggled off New Switches The following command line switches were added b ail Bail out D Ivanov C Valderrama Command line switch b to instruct isql to bail out on error when used in non interactive mode returning an error code to the operating system When using scripts as input in the command line it may be totally unappropriat
211. tive role name e The default switch for this parameter is r Roles provided in the command line are uppercased e With r2 the role is passed to the engine exactly as typed in the command line New Commands and Enhancements The following commands have been added or enhanced Ctrl C to cancel query output M Kubecek A dos Santos Fernandes Feature request CORE 704 V 2 1 Output from a SELECT in an interactive isql session can now be stopped using Ctrl C Note this merely stops fetching rows from the buffer it does not cancel the query Extension of isql SHOW SYSTEM command A dos Santos Fernandes Feature request CORE 978 V 2 1 The SHOW lt object_type gt command is meant to show user objects of that type The SHOW SYSTEM commmand is meant to show system objects but until now it only showed system tables Now it lists the predefined system UDFs incorporated into FB 2 It may be enhanced to list system views if we create some of them in the future SHOW COLLATIONS command A dos Santos Fernandes V 2 1 Lists all the character set collation pairs declared in the database SET HEAD ing toggle C Valderrama Some people consider it useful to be able to do a SELECT inside isql and have the output sent to a file for additional processing later especially if the number of columns makes isql display impracticable However isql by default prints column headers and in this scenario they are a nuisance
212. up to and including v 2 0 5 and v 2 1 3 and all v 2 5 alphas betas and release candidates Builds and Installs Parameter for Instance name added to instsvc exe D Yemanov Feature request CORE 673 V 2 1 instsvc exe now supports multi instance installations Revised Win32 Installer Docs P Reeves V 2 1 The documentation for command line setup on Windows has been revised See doc install_win dows_manually txt 144 Command line Utilities Help on command line switches Feature request CORE 548 V 2 1 On line help is now available on the switches for command line setup on Windows Gentoo FreeBSD detection during install A Peshkov Feature request CORE 1047 Gentoo or FreeBSD is now detected during configuration making it more likely that the binary install will work out of the box on these platforms 145 Chapter 14 External Functions UDFs Ability to Signal SQL NULL via a Null Pointer C Valderrama Previous to Firebird 2 UDF authors only could guess that their UDFs might return a null but they had no way to ascertain it This led to several problems with UDFs It would often be assumed that a null string would be passed as an empty string a null numeric would be equivalent to zero and a null date would mean the base date used by the engine For a numeric value the author could not always assume null if the UDF was compiled for an environment where it was known tha
213. use this type is hidden Extensions to CREATE VIEW Specification D Yemanov FIRST SKIP and ROWS syntaxes and PLAN and ORDER BY clauses can now be used in view specifications From Firebird 2 0 onward views are treated as fully featured SELECT expressions Consequently the clauses FIRST SKIP ROWS UNION ORDER BY and PLAN are now allowed in views and work as expected Syntax For syntax details refer to Select Statement amp Expression Syntax in the chapter about DML RECREATE TRIGGER Statement Implemented D Yemanov The DDL statement RECREATE TRIGGER statement is now available in DDL Semantics are the same as for other RECREATE statements Usage Enhancements The following changes will affect usage or existing pre Firebird 2 workarounds in existing applications or databases to some degree Creating Foreign Key Constraints No Longer Requires Exclusive Access V Horsun Now it is possible to create foreign key constraints without needing to get an exclusive lock on the whole database Changed Logic for View Updates Apply NOT NULL constraints to base tables only ignoring the ones inherited by view columns from domain definitions Descriptive Identifiers for BLOB Subtypes A Peshkov C Valderrama Previously the only allowed syntax for declaring a blob filter was 43 Data Definition Language DDL declare filter lt name gt input_type lt number gt output_type lt number gt entry_point lt function_i
214. v The ROLLBACK RETAIN statement is now supported in DSQL A rollback retaining feature was introduced in InterBase 6 0 but this rollback mode could be used only via an API call to isc_rollback_retaining By contrast commit retaining could be used either via an API call to isc_commit_retaining or by using a DSQL COMMIT RETAIN statement Firebird 2 0 adds an optional RETAIN clause to the DSQL ROLLBACK statement to make it consistent with COMMIT RETAIN Syntax pattern follows that of COMMIT RETAIN ROWS Syntax D Yemanov ROWS syntax is used to limit the number of rows retrieved from a select expression For an uppermost level select statement it would specify the number of rows to be returned to the host program A more understandable alternative to the FIRST SKIP clauses the ROWS syntax accords with the latest SQL standard and brings some extra benefits It can be used in unions any kind of subquery and in UPDATE or DELETE statements It is available in both DSQL and PSQL Syntax Pattern SELECT ORDER BY lt expr_list gt ROWS lt expr1 gt TO lt expr2 gt Examples 1 SELECT FROM T1 UNION ALL SELECT FROM T2 ORDER BY COL ROWS 10 TO 100 SELECT COL1 COL2 ELECT COL3 FROM T3 ORDER BY COL4 DESC ROWS 1 LD 3 7 H 67 Data Manipulation Language DML DELETE FROM T5 OR
215. viour of the nBackup utility has been backported to this patch release For more information see the Improvement in V 2 1 3 note in the nBackup section of the Utilities chapter General Notes By popular request Dmitry Yemanov adds some helpful performance guidelines pertaining to the internals of the database monitoring system a k a MONS The purpose is to assist those using this facility on heavily loaded systems to employ it in ways that provide the information you want while minimising its impact on the end user experience Known Issue At some point in V 2 1 development a breakage occurred in the behaviour of TempDirectories the config uration parameter by which you can configure a list of spaces where the engine can write the intermediate sets for sorting when it finds it does not have enough RAM available for these sets Since V 2 1 and still although the engine will use the first space listed in TempDirectories it fails to hand on sets to the any of the other configured spaces if the first space is exhausted Tracker reference CORE 2422 Because of the impact on related code this regression cannot be fixed for the V 2 1 3 patch release The workaround for now is to ensure that you configure enough space in the single location that the engine can address A regression issue surfaced with the implementation of the new gfix shutdown modes when shutdown is called with the attach or tran options If conn
216. way Forensics Speed tests on Linux showed that setting O_SYNC on a file has no effect at all on performance Fine fast operating system we may think Alas no it s a documented bug in the Linux kernel According to the Linux manual On Linux this command i e fentl fd F_SETEL flags can only change the O_APPEND O_ASYNC O_DIRECT O_NOATIME and O_NONBLOCK flags Though it is not document ed in any place known to me it turns out that an attempt to set any flag other than those listed in the manual such as O_SYNC for example won t work but it does not cause fentl to return an error either For Firebird and for InterBase versions since Day One it means that Forced Writes has never worked on Linux It certainly works on Windows It seems likely that this is not a problem that affects other operating systems although we cannot guarantee that To make sure you can check whether the implementation of fentlQ on your OS is capable of setting the O_SYNC flag The technique used currently introduced in the Beta 2 release of Firebird 2 1 is to re open the file It should guarantee correct operation on any OS provided the open system call works correctly in this respect It appears that no such problems are reported 25 Global Improvements in Firebird 2 1 The Firebird developers have no idea why such a bug would remain unfixed almost two years after getting into the Linux kernel s bug tracker Apparently in Linux a d
217. were void functions a k a procedures in Object Pascal For example BEGIN RDBSSET_CONTEXT USER_TRANSACTION MY_VAR 123 END 92 Chapter 8 New Reserved Words and Changes The following keywords have been added or have changed status since Firebird 1 5 Those marked with an asterisk are not present in the SQL standard Newly Reserved Words BIT_LENGTH v 2 0 BOTH v 2 0 CHAR_LENGTH v 2 0 CHARACTER_LENGTH v 2 0 OSE v 2 0 ONNEC v 2 1 lt lt ROSS v 2 0 ISCONNECT v 2 1 lt lt ETCH v 2 0 LOBAL v 2 1 lt lt NSENSITIVE v 2 1 lt lt FEADING v 2 0 OWER v 2 0 OCTET_LENGTH v 2 0 OPEN v 2 0 RECURSIVE v 2 1 lt lt ROWS v 2 0 SENSITIVE v 2 1 lt lt START v 2 1 lt lt TRAILING v 2 0 TRIM v 2 0 940340000 Changed from Non reserved to Reserved USING v 2 0 Keywords Added as Non reserved ABS v 2 1 lt lt ACCENT v 2 1 lt lt ACOS v 2 1 lt lt 93 New Reserved Words and Changes ALWAYS v 2 1 lt lt ASCII_CHAR v 2 1 lt lt ASCII_VAL v 2 1 lt lt ASIN v 2 1 lt lt ATAN v 2 1 lt
218. xample select blob_column from table where blob_column collate unicode foo Full equality comparisons between BLOBs Comparison can be performed on the entire content of a text BLOB Character set conversion for BLOBs Conversion between character sets is now possible when assigning to a BLOB from a string or another BLOB INTL Plug ins Character sets and collations are installed using a manifest file The manifest file should be put in the rootdir intl with a conf extension It is used to locate character sets and collations in the libraries If a character set collation is declared more than once it is not loaded and the error 1s reported in the log The file int1 fbint1 conf is an example of a manifest file The following snippet is an excerpt from intl fbintl conf lt intl_module fbintl gt filename this fbintl lt intl_module gt lt charset ISO8859_1 gt intl_module fbintl collation TSO8859_1 collation DA_DA collation DE_DE collation EN_UK collation EN_US 104 International Language Support INTL collation S_ES collation PT_BR collation PT_PT lt charset gt lt charset WIN1250 gt intl_module fbintl collation WIN1250 collation PXW_CSY collation PXW_HUN collation PXW_HUNDC lt charset gt Note The symbol this is used to indicate the same directory as the manifest file and the library extension should be omitted New Character S
219. xplicit record change update or delete that occurs as a result of the execution of an AFTER trigger 4 OLD and NEW context variables can be used in the RETURNING clause of UPDATE and INSERT OR UPDATE statements 5 In UPDATE and INSERT OR UPDATE statements field references that are unqualified or qualified by table name or relation alias are resolved to the value of the corresponding NEW context variable Examples 1 INSERT INTO Tl Fl F2 VALUES F1 F2 RETURNING F1 F2 INTO V1 V2 52 Data Manipulation Language DML INSERT INTO T2 Fl F2 VALUES 1 2 RETURNING ID INTO PK 3 DELETE FROM T1 WHERE F1 1 RETURNING F2 4 UPDATE T1 SET F2 F2 10 RETURNING OLD F2 NEW F2 UPDATE OR INSERT Statement Adriano dos Santos Fernandes v 2 1 This syntax has been introduced to enable a record to be either updated or inserted according to whether or not it already exists checked with IS NOT DISTINCT The statement is available in both DSQL and PSQL Support for this feature in Embedded SQL ESQL was added in v 2 1 6 Syntax Pattern UPDATE OR INSERT INTO lt table or view gt lt column_list gt VALUES lt value_list gt MATCHING lt column_list gt RETURNING lt column_list gt INTO lt variable_list gt Fl D Examples 1 UPDATE OR INSERT INTO T1 F1 F2 VALUES F1 F2

Download Pdf Manuals

Related Search

Related Contents

クレステック（JQ：7812）  DP Féminité 2010.211  RS87_Spanish Manual - Simrad Professional Series    U727 取扱説明書  LAR-08MB - Skyfood Equipment LLC  NP PROJET D.`établissement  Bluetooth Car-Kit HCC-250  Oreck Professional Air Purifier 21057-03 User's Manual  TechniSat HD-Vision DVB-S User's Manual  

Copyright © All rights reserved. Failed to retrieve file