[firebird-support] Using system tables in stored procedures

2019-08-12 Thread m_brahi...@yahoo.fr [firebird-support] 
Hi All,

I have many stored procedures and I'd like to include the code below in each 
one for doing parameters comparisons between a ps parameters and another table 
which contains user input settings for each parameter in a ps. I'd like to know 
please, if such code below will not reduce performance especially with a FOR 
SELECT because I heard that cursor reduce performance and for I don't know for 
system tables. Thanks
 

 

 The code:


 FOR SELECT  RDB$PROCEDURE_PARAMETERS.RDB$PARAMETER_NAME,  
RDB$FIELDS.RDB$FIELD_TYPE,
CASE RDB$FIELDS.RDB$FIELD_TYPE 
WHEN 7 THEN 'SMALLINT' 
WHEN 8 THEN 'INTEGER' 
WHEN 9 THEN 'QUAD' 
WHEN 10 THEN 'FLOAT' 
WHEN 11 THEN 'D_FLOAT' 
WHEN 12 THEN 'DATE' 
WHEN 13 THEN 'TIME' 
WHEN 14 THEN 'CHAR' 
WHEN 16 THEN 'INT64' 
WHEN 27 THEN 'DOUBLE' 
WHEN 35 THEN 'TIMESTAMP' 
WHEN 37 THEN 'VARCHAR' 
WHEN 40 THEN 'CSTRING' 
WHEN 261 THEN 'BLOB' 
END AS FIELD_TYPE 
 FROM RDB$PROCEDURE_PARAMETERS, 
  RDB$FIELDS 
 WHERE RDB$FIELDS.RDB$FIELD_NAME = RDB$PROCEDURE_PARAMETERS.RDB$FIELD_SOURCE  
AND RDB$PROCEDURE_NAME='EMPLOYE_UI'

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2019-08-12 11:53, 'Check_Mail' check_m...@satron.de 
[firebird-support] wrote:
> Hello,
> 
> I have created a CTE in a stored prodedure which gives me all terms of
> the next days, for example:
> 
> 01.10.2019   Meeting with..
> 
> 01.10.2019   Exkursion
> 
> 02.10.2019  Termin
> 
> 04.10.2019  Termin XX
> 
> Now I would set between every day a blank record, a kind of space.
> 
[..]
> 
> Can I select a blank record between the days?

That is not something you should fix in your query, you should do that 
in your presentation layer (eg add a blank line when the date changes)

Mark

Re: [firebird-support] Is there any logical difference?

2019-08-12 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2019-08-12 10:55, Svein Erling Tysvær setys...@gmail.com 
[firebird-support] wrote:
> I certainly agree with you that if EXISTS returns false, then NOT
> EXISTS should return true. It could make some sense if both EXISTS and
> NOT EXISTS returned  (though it should be documented somewhere),
> but not that one returns false and the other . When you write
> this in the tracker, I think it would be good if you included a very
> simple reproducible test case.

EXISTS (nor NOT EXISTS) cannot and should not ever produce NULL. If it 
does, then that is a bug.

SQL:2016 says:
"""
1) Let T be the result of the .
2) If the cardinality of T is greater than 0 (zero), then the result of 
the  is True; otherwise, the result of the  is False.
"""

Mark

AW: [firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello Set,

 

okay, I will test and check it, if I can take it for my situation.

 

Serie can be daily, weekly, monthly, half of a year or a quarter of a year, all 
this with reminder (days before). So it can be the situation, that I have 5 
records for one day (daily and montly entries for example) and one of weekly 
and if there is the next day with terms, I would like to show a blank line.

 

Actually I have realize this with

 

Do 

Begin

If(date <> save date)

Begin

Save output values

Set output values to null

Return

Restore output values

Set the counter + 1

end

 

Save the actually date

 

Thanks, it should work and it is okay.

 

Best regards

 

Olaf

 

Von: firebird-support@yahoogroups.com  
Gesendet: Montag, 12. August 2019 15:04
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Maybe you could add something like:

 

union all
select distinct cast(m.t || '.' || m.m || '.' || m.j as timestamp), null, null, 
null, null
from tkal_main m
where m.serie in ( 3, 6 ) and w.wek_id = :wek_team

 

It won't quite be a blank line, but a line only containing the date.

 

HTH,

Set

 

man. 12. aug. 2019 kl. 14:23 skrev 'Check_Mail' check_m...@satron.de 
  [firebird-support] 
mailto:firebird-support@yahoogroups.com> >:

 

Hello Karol,

 

I had the same idea, but it is a little bit complex. First, I must save the 
last date, if the new one ist different, I must save all return variables, set 
it to null, suspend the record and restore the saved return values and suspend 
this. So I have the old Date, the blank record and the new one. I thought, 
there is a simplier way.

 

Thank you.

 

Von: firebird-support@yahoogroups.com  
 mailto:firebird-support@yahoogroups.com> > 
Gesendet: Montag, 12. August 2019 12:17
An: firebird-support@yahoogroups.com  
; 'Check_Mail' check_m...@satron.de   
[firebird-support] mailto:firebird-support@yahoogroups.com> >
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Hi,

 

simply add loop inside

do

  begin

 suspend;

--put here some loop

while something do

  begin

  --modify output variables here

  suspend;

  end

  end;

 

regards,

Karol Bieniaszewski

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
Maybe you could add something like:

union all
select distinct cast(m.t || '.' || m.m || '.' || m.j as timestamp), null,
null, null, null
from tkal_main m
where m.serie in ( 3, 6 ) and w.wek_id = :wek_team

It won't quite be a blank line, but a line only containing the date.

HTH,
Set

man. 12. aug. 2019 kl. 14:23 skrev 'Check_Mail' check_m...@satron.de
[firebird-support] :

>
>
> Hello Karol,
>
>
>
> I had the same idea, but it is a little bit complex. First, I must save
> the last date, if the new one ist different, I must save all return
> variables, set it to null, suspend the record and restore the saved return
> values and suspend this. So I have the old Date, the blank record and the
> new one. I thought, there is a simplier way.
>
>
>
> Thank you.
>
>
>
> *Von:* firebird-support@yahoogroups.com 
>
> *Gesendet:* Montag, 12. August 2019 12:17
> *An:* firebird-support@yahoogroups.com; 'Check_Mail' check_m...@satron.de
> [firebird-support] 
> *Betreff:* Re: [firebird-support] CTE Spaces between every day
>
>
>
>
>
> Hi,
>
>
>
> simply add loop inside
>
> do
>
>   begin
>
>  suspend;
>
> --put here some loop
>
> while something do
>
>   begin
>
>   --modify output variables here
>
>   suspend;
>
>   end
>
>   end;
>
>
>
> regards,
>
> Karol Bieniaszewski
>
>
>
> 
>

AW: [firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello Karol,

 

I had the same idea, but it is a little bit complex. First, I must save the 
last date, if the new one ist different, I must save all return variables, set 
it to null, suspend the record and restore the saved return values and suspend 
this. So I have the old Date, the blank record and the new one. I thought, 
there is a simplier way.

 

Thank you.

 

Von: firebird-support@yahoogroups.com  
Gesendet: Montag, 12. August 2019 12:17
An: firebird-support@yahoogroups.com; 'Check_Mail' check_m...@satron.de 
[firebird-support] 
Betreff: Re: [firebird-support] CTE Spaces between every day

 

  

Hi,

 

simply add loop inside

do

  begin

 suspend;

--put here some loop

while something do

  begin

  --modify output variables here

  suspend;

  end

  end;

 

regards,

Karol Bieniaszewski

Re: {Disarmed} [firebird-support] System requirements for the Firebird 3.0

2019-08-12 Thread Marcelo Guimaraes mfguimar...@gmail.com [firebird-support] 
Hello people!

Here are my finds about Linux kernels and Firebird 3.0.4:

Below are the libs you need to have on each kernel and symbolic link you
should have to avoid the error "/opt/firebird/bin/gsec: error while loading
shared libraries: libtommath.so.0: cannot open shared object file: No such
file or directory" on install.

*KERNEL 2*

   - libicu44
   - libtommath0


*KERNEL 3*

   - libtommath0


*KERNEL 4*

   - libtommath1
   - ln -s /usr/lib/x86_64-linux-gnu/libtommath.so.1.0.0
   /usr/lib/x86_64-linux-gnu/libtommath.so.0


*KERNEL 5*

   - libtommath1
   - libncurses5
   - ln -s /usr/lib/x86_64-linux-gnu/libtommath.so.1.1.0
   /usr/lib/x86_64-linux-gnu/libtommath.so.0


Regards,
Marcelo Guimarães

Em sex, 2 de ago de 2019 às 11:48, Marcelo Guimaraes 
escreveu:

> One of my Debian its old, 6.0 and on repository the version of FB it's
> "firebird2.5".
> Trying to install FB 3.0 via install.sh it ask for library 'libicudata'
> and dont have it on repository to install it.
>
> Regards,
> Marcelo Guimarães
>
> Em sex, 2 de ago de 2019 às 11:10, Elmar Haneke el...@haneke.de
> [firebird-support]  escreveu:
>
>>
>>
>>
>> > I would like to know if someone can help me with the minimum system
>> > requirements for the Firebird 3.0, specific for Debian distribution.
>> > Distribution, kernel and so on.
>>
>> Current stable (buster) does have 3.0.5 in repository.
>>
>> Oldstable (stretch) does come with 3.0.1 only.
>>
>> For any older distribution you will have to compile from source.
>>
>> I would strongly suggest to use buster distribution and install compiled
>> binary from repository.
>>
>> Elmar
>>
>> 
>>
>
>
> --
>
> Atenciosamente,
> Marcelo Guimarães
>


-- 

Atenciosamente,
Marcelo Guimarães

Re: Re: Re: [firebird-support] Is there any logical difference?

2019-08-12 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
>>I certainly agree with you that if EXISTS returns false, then NOT EXISTS 
>>should return true.
>>It could make some sense if both EXISTS and NOT EXISTS returned  
>>(though it should be documented somewhere), but not that one returns false 
>>and the other .
>>When you write this in the tracker, I think it would be good if you included 
>>a very simple reproducible test case.
 
>>Set
 
on the tracker as 
http://tracker.firebirdsql.org/browse/CORE-6120
 
reproducible yes, simple - not yet ;-)
 
regards,
Karol Bieniaszewski
 

Re: [firebird-support] CTE Spaces between every day

2019-08-12 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Hi,
 
simply add loop inside
do
  begin
     suspend;
    --put here some loop
    while something do
      begin
          --modify output variables here
          suspend;
      end
  end;
 
regards,
Karol Bieniaszewski

[firebird-support] CTE Spaces between every day

2019-08-12 Thread 'Check_Mail' check_m...@satron.de [firebird-support] 
Hello,

 

I have created a CTE in a stored prodedure which gives me all terms of the
next days, for example:

 

01.10.2019   Meeting with..

01.10.2019   Exkursion

02.10.2019  Termin

04.10.2019  Termin XX

 

Now I would set between every day a blank record, a kind of space.

 

01.10.2019   Meeting with..

01.10.2019   Exkursion

 

02.10.2019  Termin

 

04.10.2019  Termin XX

 

That I can realize this, I have set a counter, each record, 1,2,3..

 

Here a part of my procedure:

 

  for with kalT as(

  select cast(m.t || '.' || m.m || '.' || m.j as timestamp) as ts, m.bez,
m.id, m.zeit, coalesce(w.b,0) - coalesce(w.q,0) as qself

  from tkal_main m left join tkal_wek w on m.id = w.main_id

  where m.serie = 3 and w.wek_id = :wek_team

 

… each other..

 

  -- halbjährlich Termin

  union all

  select cast(p.str as timestamp) as ts, m.bez, m.id, m.zeit,
coalesce(w.b,0) - coalesce(w.q,0) as qself from tkal_main m left join
tkal_wek w on m.id = w.main_id, p_sub_datum2(:vorschau,1,m.t,m.m,6) p

  where m.serie = 6 and w.wek_id = :wek_team -- noch überarbeiten, jährlich,
unterfunktion und vorwarnung extra

 

  -- halbjährlich Vorwarnung

  union all

  select cast(p.str as timestamp) - e.t_before as ts , m.bez || '
Vorwarnung', m.id, m.zeit, coalesce(w.b,0) - coalesce(w.q,0) as qself from
tkal_main m left join tkal_wek w on m.id = w.main_id left join tkal_erin e

  on m.id = e.main_id, p_sub_datum2(:vorschau,1,m.t,m.m,6) as p  where
m.serie = 6 and w.wek_id = :wek_team

  )

  select :ds + 1, ts, bez, id, zeit, qself, s.gverh from kalT,
p_sub_datums(id) s where ts >= current_date and ts < current_date +
:vorschau order by ts into :ds, :datum, :bez, :ds_id, :zeit, :qs, :gv do

  suspend;

 

Can I select a blank record between the days?

 

Thank you.

Re: Re: [firebird-support] Is there any logical difference?

2019-08-12 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support] 
I certainly agree with you that if EXISTS returns false, then NOT EXISTS
should return true. It could make some sense if both EXISTS and NOT EXISTS
returned  (though it should be documented somewhere), but not that
one returns false and the other . When you write this in the tracker,
I think it would be good if you included a very simple reproducible test
case.

Set

man. 12. aug. 2019 kl. 10:01 skrev liviuslivius liviusliv...@poczta.onet.pl
[firebird-support] :

>
>
>
> >>Thank you Set for the help. I must do one more test on monday. I must
> modify Case to comparision
> >>CASE WHEN EXISTS() IS NULL THEN -1 ...
> >>And we will see if exists can return something else then TRUE or FALSE
>
> i have tested it now and i suppose result is wrong
>
> 
>
> SELECT
> EXISTS() return False
> FROM
> WHERE
> ...
>
> 
>
> but
> SELECT
> NOT EXISTS() return NULL!!! instead of True
> FROM
> WHERE
>
> 
>
> i suppose it is an error and should be reported to the tracker?
>
>
> Regards,
> Karol Bieniaszewski
>
>
>

Re: Re: [firebird-support] Is there any logical difference?

2019-08-12 Thread liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
 
>>Thank you Set for the help. I must do one more test on monday. I must modify 
>>Case to comparision
>>CASE WHEN EXISTS() IS NULL THEN -1 ...
>>And we will see if exists can return something else then TRUE or FALSE
 
i have tested it now and i suppose result is wrong
 

 
SELECT
EXISTS() return False
FROM
WHERE

 

 
but
SELECT
NOT EXISTS() return NULL!!! instead of True
FROM
WHERE
 

 
i suppose it is an error and should be reported to the tracker?
 
 
Regards,
Karol Bieniaszewski