I forgot to schedule the post below. It is still relevant if you create a machine with lots of Delphi versions on it.
Archive for the ‘Firebird’ Category
VM disk sizes
Posted by jpluimers on 2018/06/29
Posted in .NET, .NET 2.0, .NET 3.0, .NET 3.5, Database Development, Delphi, Delphi 2007, Delphi XE, Delphi XE2, Delphi XE3, Delphi XE4, Delphi XE5, Development, Firebird, InterBase, Power User, Software Development, Windows, Windows 8 | 2 Comments »
Some notes on what errors you get when using a gds32.dll not matching your Firebird or InterBase
Posted by jpluimers on 2018/06/28
These were some of the errors and error fragments I got when I had the wrong gds32.dll or wrong Database engine:
file C:\PROGRAMDATA\EMBARCADERO\INTERBASE\GDS_DB\EXAMPLES\DATABASE\EMPLOYEE.GDB is not a valid databaseI/O error during "CreateFile (open)" operation for file "C:\PROGRAMDATA\EMBARCADERO\INTERBASE\GDS_DB\EXAMPLES\DATABASE\EMPLOYEE.GDB"connection rejected by remote interface
Since I used IBX, they were all inside EInterbaseError exception instances.
The bad thing: with IBX you cannot specify your gds32.dll: you have to ensure the right version/architecture is loaded by your executable.
–jeroen
via: Source: Delphi, IBX and the Turkish I problem
Share this:
Posted in Database Development, Development, Firebird, InterBase | Leave a Comment »
SQL: “where not exists … having” formulation; anti-join alternative
Posted by jpluimers on 2018/06/26
I need to write up some notes, but there are some links that will help me:
- [WayBack] progress db – Exclude rows based on other rows (SQL) – Stack Overflow
- [WayBack] sql – EXISTS vs JOIN and use of EXISTS clause – Stack Overflow
- [WayBack] sql – is there something faster than “having count” for large tables? – Stack Overflow
- [WayBack] sql server – EXISTS (SELECT 1 …) vs EXISTS (SELECT * …) One or the other? – Database Administrators Stack Exchange has even more elaborate answers than [WayBack] sql – What is easier to read in EXISTS subqueries? – Stack Overflow
It’s a question of readability. There is no difference in performance.
Old versions of SQL Server were silly enough to look up meta data, but not any more.SELECT foo FROM bar WHERE EXISTS (SELECT * FROM baz WHERE baz.id = bar.id); SELECT foo FROM bar WHERE EXISTS (SELECT 1 FROM baz WHERE baz.id = bar.id);I am not considering NULL or “fun variants” which don’t seem intuitive to me.
SELECT foo FROM bar WHERE EXISTS (SELECT NULL FROM baz WHERE baz.id = bar.id);SELECT foo FROM bar WHERE EXISTS (SELECT 1/0 FROM baz WHERE baz.id = bar.id);
The question popped up in comments just now. I researched the manuals of the most popular RDBMS:
- MS SQL seems to favor
SELECT *in the manual.- The example in the PostgreSQL 9.4 manual uses
SELECT 1.- Oracle 11g has
SELECT *in the language reference.- MySQL 5.7 has
SELECT *in the reference manual but alsoSELECT 1in the comments.- SQLite has no example in the language reference.
A search on SO for
code:"EXISTS (SELECT 1"yields 5,048 results.
A search on SO forcode:"EXISTS (SELECT *"yields 5,154 results.
Updated links and counts 07.2015.So
SELECT *has the popular vote and the big commercial RDBMS on its side.
I findSELECT 1more intuitive. It’s like saying “if at least one exists”.
IsSELECT *more intuitive?
–jeroen
Share this:
Posted in Database Development, Development, Firebird, InterBase, MySQL, PostgreSQL, SQL, SQL Server | Leave a Comment »
Firebird error “Operating system call _beginthreadex failed. Error code 8” can mean your server process ran out of memory
Posted by jpluimers on 2018/06/14
On a production system we had this error occurring without warning:
The cause was running out of private bytes or virtual size as they were almost 2 gigabyte which is too much for a 32-bit process:
The not so nice thing is that there were no memory warnings in the Firebird.log file at all.
In the client application the DAC (Data Access Layer) was getting lots of “unable to allocate memory from operating system” errors back from Firebird (and logging them in the client log file), so the client didn’t run out of memory: the server did.
This was with 2.5.2.26540 (at that time the most recent version) and it looks like more people suffer from this:
- [WayBack] [#CORE-2978] Operating system call _beginthreadex failed. Error code 8 … – Firebird RDBMS Issue Tracker
- [WayBack] firebird-devel – 32-bit Firebird attempting to create large amount of thread on 64-bit server
We put a monitor to watch the fbserver.exe process and warn us if it was reaching 1.5 gigabyte so we could re-start it before running out of memory.
–jeroen
Share this:
Posted in Database Development, Development, Firebird, Power User | 2 Comments »
Firebird – generate time/date/timestamp values
Posted by jpluimers on 2017/10/31
I needed the script in [WayBack] Firebird – generate time/date/timestamp values to reproduce a problem in Database Workbench <= 5.3.2 with exporting TIME and TIMESTAMP values to Excel xlsx format: the time portion got lost so
- TIME columns all became a date
1900-01-00(I think the00is because of the infamous 1900 problem that also makes this hard: Excel worksheet function – How do I get the weekday name of a date?) - TIMESTAMP columns all got the midnight timestamp of the date value specified
The bug is fixed so will appear in the next beta and release versions: [WayBack] 0001506: Timestamp export to Excel XLSX only shows date, Time fails, CSV shows both date and time – Bug & Feature Tracker @ Upscene.com
–jeroen
Share this:
Posted in Database Development, Development, Firebird, Software Development, SQL | Leave a Comment »
Visual Representation of SQL Joins – CodeProject
Posted by jpluimers on 2017/08/02
I thought I posted a reference to this a long time ago, but didn’t.
It’s one of the things I show when explaining joins to people. Sometimes I need it myself too (:
The article explains these in greater detail:
- INNER JOIN
- LEFT JOIN
- RIGHT JOIN
- OUTER JOIN
- LEFT JOIN EXCLUDING INNER JOIN
- RIGHT JOIN EXCLUDING INNER JOIN
- OUTER JOIN EXCLUDING INNER JOIN
Note:
- the opposite of INNER JOIN is not OUTER JOIN. It’s OUTERJOIN EXCLUDING INNER JOIN
- the opposite of OUTER JOIN is empty set.
But the diagram is usually speaks for itself.
–jeroen
Source: Visual Representation of SQL Joins – CodeProject
Share this:
Posted in Access, Database Development, DB2, Development, Firebird, InterBase, MySQL, OracleDB, PostgreSQL, SQL, SQL Server | Leave a Comment »
Firebird 3.0 – Monitor Need to monitor you firebird 3.0 database? This just works…
Posted by jpluimers on 2017/07/20
Interesting tool as it just works magically:
Firebird 3.0 – MonitorNeed to monitor you firebird 3.0 database? We have the right tool for you!
–jeroen
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
Firebird: reset statistics for all indices using `set statistics index :index_name` – via: What is index selectivity?
Posted by jpluimers on 2017/07/12
A while ago, I needed to reset the index statistics for all indices in one our databases: a Firebird database.
I hadn’t done work like this in a while, so I made the cardinal mistake of Googling and using the first matching StackOverflow answer that seemed a perfect fit.
Boy was I wrong (:
In sql – My firebird query plan does not use correct index – Stack Overflow I found the accepted answer (no less!) has this neat block to update index statistics for all indices:
EXECUTE BLOCK
AS
DECLARE VARIABLE IDX VARCHAR(31);
BEGIN
FOR
SELECT rdb$index_name FROM rdb$indices
WHERE NOT rdb$index_name LIKE 'RDB$%'
INTO :idx
DO BEGIN
EXECUTE STATEMENT 'update statistics ' || :idx
WITH AUTONOMOUS TRANSACTION;
END
END
The code consistently failed with this error:
Script Error:
Dynamic SQL Error
SQL error code = -104
Unexpected end of command - line 1, column 19
When I removed the lines inside the do begin … end block, then the outer statements execute fine.
Incidentally, column 19 is right at the space at the end of after the 'update statistics '. Which meant that either the string concatenation was wrong, or the statement itself.
And yes, I should have Googled the Firebird documentation instead, as What is index selectivity? has the correct statement that recalculates statistics for all indexes:
set term !! ; EXECUTE BLOCK AS declare variable index_name VARCHAR(31); BEGIN for select RDB$INDEX_NAME from RDB$INDICES into :index_name do execute statement 'SET statistics INDEX ' || :index_name || ';'; END!! set term ; !!
–jeroen
Source: What is index selectivity?
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
Firebird; testing for NULL and equality in practice: IS [NOT] DISTINCT FROM
Posted by jpluimers on 2017/06/14
Boy, I totally missed that this was introduced back then in Firebird 2.0:
In Firebird 2 and higher, you test for null-encompassing equality with DISTINCT. This has already been discussed, but here’s a quick recap. Two expressions are considered:
- DISTINCT if they have different values or if one of them is
NULLand the other isn’t;- NOT DISTINCT if they have the same value or if both of them are
NULL.[NOT] DISTINCT always returns
trueorfalse, neverNULLor something else. Examples:if (A is distinct from B) then...if (Buyer1 is not distinct from Buyer2) then...
Splendid!
Source: Testing for NULL and equality in practice
–jeroen
Share this:
Posted in Database Development, Development, Firebird | Leave a Comment »
Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes
Posted by jpluimers on 2017/02/15
Delphi programmers often store TDateTime (be it date, time or date+time values) as floating points in the database. They take the easy way out basically because TDateTime is nothing but a typed alias for the Delphi Double type (which is equivalent to the IEEE 754 double-precision binary floating-point format: binary64 type) .
Actually, TDateTime is equivalent to the OLE Automation date (which is described in the remarks of DateTime.ToOADate Method (System)) which has an epoch of midnight at 1899-12-30 (to be compatible with Lotus-123 including its leap-year-bug, background on that is at Why are the Microsoft Office file formats so complicated? (And some workarounds) – Joel on Software).
Anyway: the definition is this:
An OLE Automation date is implemented as a floating-point number whose integral component is the number of days before or after midnight, 30 December 1899, and whose fractional component represents the time on that day divided by 24. For example, midnight, 31 December 1899 is represented by 1.0; 6 A.M., 1 January 1900 is represented by 2.25; midnight, 29 December 1899 is represented by -1.0; and 6 A.M., 29 December 1899 is represented by -1.25.
The base OLE Automation Date is midnight, 30 December 1899. The minimum OLE Automation date is midnight, 1 January 0100. The maximum OLE Automation Date is the same as DateTime.MaxValue, the last moment of 31 December 9999.
The sample values are remarkably the same as the ones for TDateTime:
Value Description 0 December 30, 1899; 12:00 A.M. 2.75 January 1, 1900; 6:00 P.M. -1.25 December 29, 1899; 6:00 A.M. 35065 January 1, 1996; 12:00 A.M.
The below SQL example uses the Firebird date/time/timestamp casting shortcuts:
select date '1899-12-30' + 42452.670590278 as Date20160323,
timestamp '1899-12-30 00:00:00.000' + 42452.670590278 as DateTime20160323160539,
time '00:00:00' + 60*60*24 * 0.5 as Noon,
timestamp '1899-12-30 00:00:00.000' + 0 as DateTime18991230Midnight,
timestamp '1899-12-30 00:00:00.000' + 2.75 as DateTime190001011800,
timestamp '1899-12-30 00:00:00.000' + -1.25 as DateTime189912290600,
timestamp '1899-12-30 00:00:00.000' + 35065 as DateTime19960101Midnight
from rdb$database
Which – using DMY date format and 24 hour clock format settings – results in:
DATE20160323 DATETIME20160323160539 NOON DATETIME18991230MIDNIGHT DATETIME190001011800 DATETIME189912290600 DATETIME19960101MIDNIGHT CONSTANT --------------------------------------------------------------------------------------------------------------------------------------------------- 24-3-2016 23-3-2016 16:05:39 12:00:00 30-12-1899 1-1-1900 18:00:00 28-12-1899 18:00:00 1-1-1996
Which basically taught me a new thing about firebird: Times are calculated in seconds, so date fractions need to be multiplied by 60 * 60 * 24.
You can see this in the following query and results:
select (cast('Now' as time) - cast('00:00:00.000' as time)) / (24 * 60 * 60) as DelphiTime, cast('Now' as time) - cast('00:00:00.000' as time) as SecondsSinceMidnight, cast('Now' as date) - cast('1899-12-30' as date) as DelphiDate, cast('Now' as timestamp) - cast('1899-12-30 00:00:00.000' as timestamp) as DelphiDateTime, 24 * 60 * 60 as SecondsPerDay, cast('Now' as date) as "Date", cast('Now' as time) as "Time", cast('Now' as timestamp) as "TimeStamp" from rdb$databaseDELPHITIME SECONDSSINCEMIDNIGHT DELPHIDATE DELPHIDATETIME SECONDSPERDAY Date Time TimeStamp ------------------------------------------------------------------------------------------------------------------------------------------------ 0,4366 37722,284 42835 42835,436600509 86400 10-4-2017 10:28:42 10-4-2017 10:28:42
Note this post is complementary to Date format converter from Text or Unix/Mac/Filetime/Microsoft to virtually any readable form (which mentions the wrong Microsoft epoch as it should be 1899-12-30 midnight): the above shows how to do the conversion to readable dates in Firebird (might work in InterBase as well, but I’ve not used that for a long time).
–jeroen
PS: for Microsoft SQL Server: passing dates to the sql server: pass it as float – 2. -2 is the difference between delphi tdatetime start date and mssql start date.
Share this:
Posted in Algorithms, Database Development, Development, Firebird, Floating point handling, Software Development | 2 Comments »
The Wiert Corner - irregular stream of stuff 