 | Lars Fosdal on Security alarm provider Woonve… |
 | Thomas Mueller on Question got closed in May 202… |
 | Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
| Thaddy de Koning on Formulier voor bewindvoerders… |
Posted by jpluimers on 2019/04/25
I totally forgot that Firebird as of 2.5 supports [WayBack] UPDATE OR INSERT, so as a reminder to self:
UPDATE OR INSERT INTO
{target} [({column_list})]
VALUES ({value_list})
[MATCHING ({column_list})]
[RETURNING {values} [INTO {variables}]]
{column_list} ::= colname [, colname ...]
{value_list} ::= value [, value ...]
{ret_values} ::= ret_value [, ret_value ...]
{variables} ::= varname [, varname ...]
–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2019/04/23
I was a bit baffled at the below results because in other other databases.
You need to use this kind of notation ####.0E0 to force an integer to be evaluated as floating point, which is an extra .0 than explained by [WayBack] sql – Why does Firebird truncate decimal places when dividing? – Stack Overflow
I’m not the only person confused by this: [WayBack] [#CORE-2849] Simple mathematics gone wrong – Firebird RDBMS Issue Tracker.
I know that as of Firebird 2.1 you can use the [WayBack] dateadd() function for calculations on timestamps, date or time values (hence the confusing name dateadd), but you don’t always have the delta readily available in one unit (second, minutes, etc) and for a mixed Firebird/InterBase deployment having one kind of logic is often preferred.
select
cast('now' as timestamp) as NowTimeStamp
, (15.0*(1.0/24.0/60.0)) as A15MinutesInDays
, (15.0/24.0/60.0) as B15MinutesInDays
, (15.0E0/24/60) as C15MinutesInDays
, cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)) as AIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0))) as ADifferenceInDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24 as ADifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0*(1.0/24.0/60.0)))) * 24*60 as ADifferenceMinutes
, cast('now' as timestamp) + (15.0/24.0/60.0) as BIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0)) as BDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24 as BDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0/24.0/60.0))) * 24*60 as BDifferenceMinutes
, cast('now' as timestamp) + (15.0E0/24/60) as CIn15MinutesTimeStamp
, cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60)) as CDifferenceDays
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24 as CDifferenceHours
, (cast('now' as timestamp) - (cast('now' as timestamp) + (15.0E0/24/60))) * 24*60 as CDifferenceMinutes
-- Firebird 2.1 and up: use dateadd , dateadd(minute, 15, cast('now' as timestamp)) as DIn15MinutesTimeStamp , cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp)) as DDifferenceInDays , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24 as DDifferenceHours , (cast('now' as timestamp) - dateadd(minute, 15, cast('now' as timestamp))) * 24*60 as DDifferenceMinutes from rdb$database /* Transposed results are below. Excected values for: A15MINUTESINDAYS 0.0104166666666667 B15MINUTESINDAYS 0.0104166666666667 Transposed results: NOWTIMESTAMP 20-9-2017 20:31:40 A15MINUTESINDAYS 0 B15MINUTESINDAYS 0.01 C15MINUTESINDAYS 0.0104166666666667 AIN15MINUTESTIMESTAMP 20-9-2017 20:31:40 ADIFFERENCEINDAYS 0 ADIFFERENCEHOURS 0 ADIFFERENCEMINUTES 0 BIN15MINUTESTIMESTAMP 20-9-2017 20:46:04 BDIFFERENCEDAYS -0.01 BDIFFERENCEHOURS -0.24 BDIFFERENCEMINUTES -14.4 CIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 CDIFFERENCEDAYS -0.010416667 CDIFFERENCEHOURS -0.250000008 CDIFFERENCEMINUTES -15.00000048 DIN15MINUTESTIMESTAMP 20-9-2017 20:46:40 DDIFFERENCEDAYS -0.010416667 DDIFFERENCEHOURS -0.250000008 DDIFFERENCEMINUTES -15.00000048 */
–jeroen
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Posted by jpluimers on 2019/01/16
A while back I wrote on Generating a million sequential numbers on the fly in a Firebird query – some solutions and speed measurements.
SQL Server has different features and performance characteristics so here are some links on doing similar things in SQL Server:
As always: if performance is important, measure before starting to optimise!
Via: [WayBack] sql – All hour of day – Stack Overflow
–jeroen
Posted in Database Development, Development, Firebird, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2019/01/04
Just in case I ever run into this again: [WayBack] Lock conflict on no wait transaction
–jeroen
Posted in Database Development, Development, Firebird, Power User | Leave a Comment »
Posted by jpluimers on 2018/12/20
Lovely: people that perform incomplete Firebird deployments…
This means the firebird.msg file on the client is either not found or is a different version than the server.
Source: [WayBack] Can’t format message nn:mmm — message text not found
–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2018/11/21
Converting string literals to to date/time/timestamp related data is always tricky in many Database environments.
Firebird is no exception, especially because sometimes it truncates a zero time portion from a date-time/timestamp.
So you can get this:
select cast('30-12-1899' as TimeStamp)
from rdb$databaseThrowing an error:
conversion error from string "30-12-1899"
And this:
select cast('30.12.1899' as TimeStamp)
from rdb$databaseReturning
CAST
30-12-1899 0:00:00
–jeroen
Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »
Posted by jpluimers on 2018/07/26
Change at will:
select 'select * from ' || r.rdb$relation_name as query
from rdb$relations r
where 1=1
and r.rdb$system_flag <> 1 -- no system relations
and r.rdb$view_source is null -- only tables
order by r.rdb$relation_name
For the EMPLOYEE demo database, this results in:
select * from COUNTRY
select * from CUSTOMER
select * from DEPARTMENT
select * from EMPLOYEE
select * from EMPLOYEE_PROJECT
select * from JOB
select * from PROJECT
select * from PROJ_DEPT_BUDGET
select * from SALARY_HISTORY
select * from SALES
–jeroen
Posted in Database Development, Development, Firebird, InterBase, Software Development | Leave a Comment »
Posted by jpluimers on 2018/07/25
Since Firebird 2.1, you can get the first 3 numbers of the version with this SQL statement:
SELECT rdb$get_context('SYSTEM', 'ENGINE_VERSION')
from rdb$database;
You get it in this format:
RDB$GET_CONTEXT --------------- 2.5.7
Which means you will not get the build number, which for most purposes is still quite OK.
–jeroen
via: [WayBack] How to detect the server version?
Posted in Database Development, Development, Firebird | Leave a Comment »
Posted by jpluimers on 2018/07/19
The testing was done with Firebird 2.5.x x86 on Windows 7 x64.
Where other relational database platforms have plenty of opportunities to generate sequences (see for instance the below links on Oracle and SQL Server), with Firebird you can use a WITH RECURSIVE query construct that normally is being used to manage tree structures ([WayBack] PkLab – Firebird: Tree data mangement with recursive CTE).
However, that uses query stack which has a depth limit of 1024 levels. When you reach the limit, Firebird gives you an error like this:
with
recursive
sequence(n) as (
-- When you select more than 1024 values, this error occurs:
-- Error while fetching data: Too many concurrent executions of the same request
select 0 -- start
from rdb$database
union all
select sequence.n + 1
from sequence
where sequence.n < 1023 -- finish
)
select sequence.n
from sequence
--where sequence.n in (24, 38)
order by sequence.n
It however is a pretty quick and CU bound solution: on my system ([WayBack] AMD A8-7600 @ 3.1 Ghz), it runs 1000 records within ~0.1 seconds.
In such a short time, it’s hard to see how the speed is bound, so I wanted to go for some orders of magnitude more. In ~0.1 seconds, the processor executes about 0.3 * 10^9 cycles generating 1000 numbers which is ~ 300-thousand cycles per number. That sounds like a lot of cycles for so few numbers. Would this become a better ratio for more numbers?
Posted in Database Development, Development, Firebird, IKEA hacks, OracleDB, SQL, SQL Server | Leave a Comment »
Posted by jpluimers on 2018/07/18
I was amazed the influence on how you compare dates would be a factor 1000 change in fetch times even though I wrote about date, time and timestamp conversions before in Source: Firebird – adjusting Delphi stored TDateTime (or OLE Automation date) fields that contain Dates, Times or DateTimes.
All queries were ran at 20170410.
Slow
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- slow: prepared 0.066 sec; fetched 10.549 sec 250 rows
and date '1899-12-30' + data.datadate >= '2017-04-08' -- last 2 days
and date '1899-12-30' + data.datadate <= '2017-04-10' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.063 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date 'Now' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date 'Now' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.070 sec; fetched 0.011 sec 250 rows
and data.DATADATE >= date '2017-04-10' - date '1899-12-30' - 2 -- last 2 days
and data.DATADATE <= date '2017-04-10' - date '1899-12-30' -- today
Fast
select id, date '1899-12-30' + data.datadate as datestamp
from data
where 1=1
-- fast: prepared 0.073 sec; fetched 0.009 sec 250 rows
and data.DATADATE >= date '2016-04-08' - date '1899-12-30' -- specific date range start
and data.DATADATE <= date '2016-04-10' - date '1899-12-30' -- specific date range end
The table looks like this:
CREATE TABLE "DATA" ( ID INTEGER NOT NULL, DATADATE DOUBLE PRECISION, DATATIME DOUBLE PRECISION ); CREATE ASC INDEX DATA_DATADATE ON "DATA" (DATADATE);
–jeroen
Posted in Database Development, Development, Firebird | Leave a Comment »
The Wiert Corner - irregular stream of stuff 