The Wiert Corner – irregular stream of stuff

Jeroen W. Pluimers on .NET, C#, Delphi, databases, and personal interests

  • My badges

  • Twitter Updates

  • My Flickr Stream

  • Pages

  • All categories

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 1,616 other followers

Archive for the ‘Firebird’ Category

Firebird CASE construct

Posted by jpluimers on 2019/06/12

Reminder to self: sort out why a while ago I had trouble with a nested [WayBackCASE construct causing parsing anomalies in 2.5.8.

–jeroen

Posted in Database Development, Development, Firebird, Software Development | Leave a Comment »

Testing for NULL and equality in practice

Posted by jpluimers on 2019/05/30

Great article: Testing for NULL and equality in practice [WayBack]

Most important table piece I learned:

 Testing (in)equality of A and B in different Firebird versions

Test type Firebird version
<= 1.5.x >= 2.0
Equality
A = B or A is null and B is null
A is not distinct from B
Inequality
A <> B
or A is null and B is not null
or A is not null and B is null
A is distinct from B

–jeroen

Posted in Database Development, Development, Firebird, SQL | Leave a Comment »

UPDATE OR INSERT

Posted by jpluimers on 2019/04/25

I totally forgot that Firebird as of 2.5 supports [WayBackUPDATE 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 »

InterBase/Firebird divisions: use E0 syntax to force floating point calculations

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 [WayBacksql – 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 [WayBackdateadd() 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 »

Generating a million sequential numbers on the fly in a SQL Server query

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 »

 
%d bloggers like this: