date:20170831

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

2017-08-31 Thread Svein Erling Tysvær setys...@gmail.com [firebird-support]

Did you remember to also remove AS? As far as I can see from the syntax
description, AS should be used before USER, but not before ROLE, so try:

execute statement (lsql_update_cv_itens_sub1)
  (:lvl_base, :lid_cv_itens_sub1)
  role 'perfil_vendas';

HTH,
Set


2017-08-30 23:08 GMT+02:00 hamacker sirhamac...@gmail.com
[firebird-support] :

>
>
> Something wrong in my test because 'sintax error' when I try role name
> without username/password.
> Can you help me?
>
>
> EXECUTE BLOCK
> returns (result_value Integer)
> AS
> declare variable lid_cv bigint=3;
> declare variable lcoditem varchar(30)='(C120P2AC)';
> declare variable lvl_base NUMERIC(18,2)=100.00;
> declare variable lid_cv_item bigint;
> declare variable lid_cv_itens_sub1 bigint;
> declare variable lid_cv_itens_sub2 bigint;
> declare variable lcvi_coditem varchar(30);
> declare variable lsub1_coditem varchar(30);
> declare variable lsub2_coditem varchar(30);
> declare variable lsql_update_cv_itens varchar(4096);
> declare variable lsql_update_cv_itens_sub1 varchar(4096);
> declare variable lsql_update_cv_itens_sub2 varchar(4096);
> begin
>   lsql_update_cv_itens='update cv_itens set vl_base=? where
> (id_cv_item=?);';
>   lsql_update_cv_itens_sub1='update cv_itens_sub1 set vl_base=? where
> (id_cv_itens_sub1=?);';
>   lsql_update_cv_itens_sub2='update cv_itens_sub2 set vl_base=? where
> (id_cv_itens_sub2=?);';
>   result_value=0;
>   for select id_cv_item, coditem from cv_itens
>   where id_cv=:lid_cv
>   into :lid_cv_item, :lcvi_coditem do
>   begin
> if (:lcvi_coditem=:lcoditem) then
> begin
>   execute statement (lsql_update_cv_itens)
> (:lvl_base, :lid_cv_item)
> as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
>   result_value=(result_value+1);
> end
> for select id_cv_itens_sub1, coditem from cv_itens_sub1
> where id_cv_item=:lid_cv_item
> into :lid_cv_itens_sub1, :lsub1_coditem  do
> begin
>   if (:lsub1_coditem=:lcoditem) then
>   begin
> execute statement (lsql_update_cv_itens_sub1)
>   (:lvl_base, :lid_cv_itens_sub1)
>   as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
> result_value=(result_value+1);
>   end
>   for select id_cv_itens_sub2, coditem from cv_itens_sub2
>   where id_cv_itens_sub1=:lid_cv_itens_sub1
>   into :lid_cv_itens_sub2, :lsub2_coditem do
>   begin
> if (:lsub2_coditem=:lcoditem) then
> begin
>   execute statement (lsql_update_cv_itens_sub2)
> (:lvl_base, :lid_cv_itens_sub2)
> as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
> sintaxe error when remove user and password
>   result_value=(result_value+1);
> end
>   end
> end
>   end
>   suspend;
> end
>
>
> 2017-08-30 16:33 GMT-03:00 Dimitry Sibiryakov s...@ibphoenix.com
> [firebird-support] :
>
>> 30.08.2017 21:15, hamacker sirhamac...@gmail.com [firebird-support]
>> wrote:
>> > Here in fb3, to put role name in execute, Its mandatory put username
>> and password too.
>>
>>I was wrong a little, but still you should read the documentation:
>>
>> > - if ON EXTERNAL DATA SOURCE clause is omitted then
>> >   a) statement will be executed against current (local) database
>> >   b) if AS USER clause is omitted or  equal to
>> CURRENT_USER
>> >   and if ROLE clause is omitted or  equal to CURRENT_ROLE
>> >   then the statement is executed in current connection context
>> >   c) if  is not equal to CURRENT_USER or  not
>> equal to CURRENT_ROLE
>> >   then the statement is executed in separate connection established
>> inside the same
>> >   engine instance (i.e. created new internal connection without
>> Y-Valve and remote layers).
>>
>>
>
> 
>

[firebird-support] Firebird 2.5: Nested querys help

2017-08-31 Thread duque.herna...@yahoo.com [firebird-support]

Hi to all.
 

 I have a table with these columns:
 

 table_id, date_time, customer, other_fields
 

 One customer could have several records in the same date and I have to make a 
SELECT only to the first table_id for a given date for each customer_id. I have 
worked around the query with something like this but I haven't succeed:
 

 SELECT
   xx0.table_id
   FROM
 my_table xx0
   WHERE
 xx0.date_time >= CAST('today' AS DATE) + 1
 AND xx0.date_time < CAST('today' AS DATE) + 2
 AND xx0.table_id IN (
 SELECT FIRST 1
   xx2.table_id
   FROM
 my_table xx2
   WHERE
 xx2.date_time >= CAST('today' AS DATE) + 1
 AND xx2.date_time < CAST('today' AS DATE) + 2
 AND xx2.customer_id IN (
 SELECT DISTINCT
   xx1.customer_id
   FROM
 my_table xx1
   WHERE
 xx1.date_time >= CAST('today' AS DATE) + 1
 AND xx1.date_time < CAST('today' AS DATE) + 2
 )
   ORDER BY
 xx2.date_time
 )
   ORDER BY
 xx0.date_time
 

 Any help will be appreciated.
 

 Best regards,
 

 Hernando.

Re: [firebird-support] Firebird 2.5: Nested querys help

2017-08-31 Thread setysvar setys...@gmail.com [firebird-support]

 >One customer could have several records in the same date and I have to 
make a SELECT only to the first table_id for a given date for
 >each customer_id. I have worked around the query with something like 
this but I haven't succeed:

Hi Hernando!

Admittedly I have tried to guess what you mean by the above statement (I 
didn't understand anything of what you want from the query itself - 
except that you only want the result for tomorrow). If my guess is 
correct, you simply want:

SELECT cast(date_time as Date) mydate, customer, min(table_id) table_id
FROM my_table
WHERE date_time >= current_date + 1
   AND date_time < current_date + 2
GROUP BY 1, 2

A different way to write something similar, would be:
SELECT xx0.table_id
FROM my_table xx0
WHERE xx0.date_time >= current_date + 1
   AND xx0.date_time < current_date + 2
   AND NOT EXISTS(SELECT *
  FROM my_table xx1
  WHERE XX0.customer = XX1.customer
AND CAST(XX0.date_time AS DATE) = CAST(XX1.date_time 
AS DATE)
AND XX0.table_id > XX1.table_id)

(you may add AND XX1.date_time between XX0.date_time - 1 and 
XX0.date_time + 1 if you have an index for date_time and your query is slow)

If this is not what you're looking for, then please try to explain your 
problem in the way you would explain it to someone that doesn't know 
anything about your system - that could include an example dataset and 
an example of the desired output.

HTH,
Set

[firebird-support] Re: Firebird 2.5: Nested querys help

2017-08-31 Thread duque.herna...@yahoo.com [firebird-support]

Set, thank you for your help. 

 I'll like to explain my self better. If having these records:
 

   table_id  date_time customer_id  other_fields
       ---  
   001   08/30/2017 08:00  0015 whatever
   002   08/30/2017 08:10  0025 whatever
   003   08/30/2017 08:20  0025 whatever
   004   08/30/2017 08:30  0011 whatever
   005   08/30/2017 08:40  0014 whatever
   006   08/30/2017 08:50  0025 whatever
   007   08/30/2017 09:00  0021 whatever
   008   08/30/2017 09:10  0024 whatever
   009   08/30/2017 09:20  0017 whatever
   010   08/30/2017 09:30  0025 whatever
   011   08/30/2017 09:40  0026 whatever
   012   08/30/2017 09:50  0026 whatever
   013   08/30/2017 10:00  0018 whatever
   014   08/30/2017 10:10  0015 whatever
   015   08/30/2017 10:20  0013 whatever
   016   08/30/2017 10:30  0025 whatever
   017   08/30/2017 10:40  0011 whatever
   018   08/30/2017 10:50  0011 whatever
   019   08/30/2017 11:00  0032 whatever
 


 

 What I need is to select only the first record (based on date_time) from each 
customer_id. Like this:
 

   table_id  date_time customer_id  other_fields
       ---  
   001   08/30/2017 08:00  0015 whatever


   002   08/30/2017 08:10  0025 whatever
   004   08/30/2017 08:30  0011 whatever
   005   08/30/2017 08:40  0014 whatever
   007   08/30/2017 09:00  0021 whatever
   008   08/30/2017 09:10  0024 whatever
   009   08/30/2017 09:20  0017 whatever
   011   08/30/2017 09:40  0026 whatever
   013   08/30/2017 10:00  0018 whatever
   015   08/30/2017 10:20  0013 whatever
   019   08/30/2017 11:00  0032 whatever
 


 Regards,
 

 Hernando.

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

2017-08-31 Thread hamacker sirhamac...@gmail.com [firebird-support]

Now I remove 'AS ' and sintaxe error message is gone, thanks.

But another problem, my role 'perfil_vendas' have all access to all objects
and 'perfil_vendas', but not for 'for select...' :(
when I try to execute my sample code, IBExpert says:

'no permition for SELECT access TABLE  at block line'

as expected.

So, to my sample run file, all block code need to be a unique execute
statement... and you know it´s a mess.

Better I think right and start over.

Thanks a lot.


Code sample:
EXECUTE BLOCK
returns (result_value Integer)
AS
declare variable lid_cv bigint=3;
declare variable lcoditem varchar(30)='(C120P2AC)';
declare variable lvl_base NUMERIC(18,2)=100.00;
declare variable lid_cv_item bigint;
declare variable lid_cv_itens_sub1 bigint;
declare variable lid_cv_itens_sub2 bigint;
declare variable lcvi_coditem varchar(30);
declare variable lsub1_coditem varchar(30);
declare variable lsub2_coditem varchar(30);
declare variable lsql_update_cv_itens varchar(4096);
declare variable lsql_update_cv_itens_sub1 varchar(4096);
declare variable lsql_update_cv_itens_sub2 varchar(4096);
begin
  lsql_update_cv_itens='update cv_itens set vl_base=? where
(id_cv_item=?);';
  lsql_update_cv_itens_sub1='update cv_itens_sub1 set vl_base=? where
(id_cv_itens_sub1=?);';
  lsql_update_cv_itens_sub2='update cv_itens_sub2 set vl_base=? where
(id_cv_itens_sub2=?);';
  result_value=0;
  for select id_cv_item, coditem from cv_itens -- no role for this select
  where id_cv=:lid_cv
  into :lid_cv_item, :lcvi_coditem do
  begin
if (:lcvi_coditem=:lcoditem) then
begin
  execute statement (lsql_update_cv_itens)
(:lvl_base, :lid_cv_item)
role 'perfil_orcamentos';
  result_value=(result_value+1);
end
for select id_cv_itens_sub1, coditem from cv_itens_sub1 -- no role for
this select
where id_cv_item=:lid_cv_item
into :lid_cv_itens_sub1, :lsub1_coditem  do
begin
  if (:lsub1_coditem=:lcoditem) then
  begin
execute statement (lsql_update_cv_itens_sub1)
  (:lvl_base, :lid_cv_itens_sub1)
  role 'perfil_orcamentos';
result_value=(result_value+1);
  end
  for select id_cv_itens_sub2, coditem from cv_itens_sub2  --- no role
for this select
  where id_cv_itens_sub1=:lid_cv_itens_sub1
  into :lid_cv_itens_sub2, :lsub2_coditem do
  begin
if (:lsub2_coditem=:lcoditem) then
begin
  execute statement (lsql_update_cv_itens_sub2)
(:lvl_base, :lid_cv_itens_sub2)
role 'perfil_vendas';
  result_value=(result_value+1);
end
  end
end
  end
  suspend;
end

2017-08-31 3:58 GMT-03:00 Svein Erling Tysvær setys...@gmail.com
[firebird-support] :

>
>
> Did you remember to also remove AS? As far as I can see from the syntax
> description, AS should be used before USER, but not before ROLE, so try:
>
> execute statement (lsql_update_cv_itens_sub1)
>   (:lvl_base, :lid_cv_itens_sub1)
>   role 'perfil_vendas';
>
> HTH,
> Set
>
>
> 2017-08-30 23:08 GMT+02:00 hamacker sirhamac...@gmail.com
> [firebird-support] :
>
>>
>>
>> Something wrong in my test because 'sintax error' when I try role name
>> without username/password.
>> Can you help me?
>>
>>
>> EXECUTE BLOCK
>> returns (result_value Integer)
>> AS
>> declare variable lid_cv bigint=3;
>> declare variable lcoditem varchar(30)='(C120P2AC)';
>> declare variable lvl_base NUMERIC(18,2)=100.00;
>> declare variable lid_cv_item bigint;
>> declare variable lid_cv_itens_sub1 bigint;
>> declare variable lid_cv_itens_sub2 bigint;
>> declare variable lcvi_coditem varchar(30);
>> declare variable lsub1_coditem varchar(30);
>> declare variable lsub2_coditem varchar(30);
>> declare variable lsql_update_cv_itens varchar(4096);
>> declare variable lsql_update_cv_itens_sub1 varchar(4096);
>> declare variable lsql_update_cv_itens_sub2 varchar(4096);
>> begin
>>   lsql_update_cv_itens='update cv_itens set vl_base=? where
>> (id_cv_item=?);';
>>   lsql_update_cv_itens_sub1='update cv_itens_sub1 set vl_base=? where
>> (id_cv_itens_sub1=?);';
>>   lsql_update_cv_itens_sub2='update cv_itens_sub2 set vl_base=? where
>> (id_cv_itens_sub2=?);';
>>   result_value=0;
>>   for select id_cv_item, coditem from cv_itens
>>   where id_cv=:lid_cv
>>   into :lid_cv_item, :lcvi_coditem do
>>   begin
>> if (:lcvi_coditem=:lcoditem) then
>> begin
>>   execute statement (lsql_update_cv_itens)
>> (:lvl_base, :lid_cv_item)
>> as user 'SYSDBA' password 'masterkey' role 'perfil_vendas'; --
>> sintaxe error when remove user and password
>>   result_value=(result_value+1);
>> end
>> for select id_cv_itens_sub1, coditem from cv_itens_sub1
>> where id_cv_item=:lid_cv_item
>> into :lid_cv_itens_sub1, :lsub1_coditem  do
>> begin
>>   if (:lsub1_coditem=:lcoditem) then
>>   begin
>> execute statement (lsql_update_cv_itens_sub1)
>>   (:lvl_base, :lid_cv_itens

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

2017-08-31 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]

31.08.2017 21:36, hamacker sirhamac...@gmail.com [firebird-support] wrote:
> So, to my sample run file, all block code need to be a unique execute 
> statement... and you 
> know it´s a mess.

   Just in case: are you aware that you can grant rights to stored procedure 
instead of user?

-- 
   WBR, SD.

[firebird-support] Sort error No free space found in temporary...

2017-08-31 Thread kragh.tho...@yahoo.com [firebird-support]

Hey
 The other day we had a major slowdown on a database - one server with a single 
database. Some of our queries received this error:
 Sort error No free space found in temporary directories operating system 
directive open failed
 All queries that received this error used union in some way.
 

 The Firebird log is shows a lot of errors at the same time. 
 Cannot dump the monitoring data operating system directive open failed Too 
many open files
 And
 Cannot initialize the shared memory region operating system directive open 
failed Too many open files
 

 After this the following changes was made to the config
 TempBlockSize = 2048576(changed from default value)
 TempCacheLimit = 567108864(changed from default value)
 (I took the values from the firebird_25_superclassic_64bit.conf from ib-aid)
 

 However today same thing happened once again. Does anyone know the solution to 
this? 
 

 TempDirectories in config is set to default, so I guess /tmp/firebird i used, 
this disk has about 12Gb free space at the time of writing. Could all this 
space have been consumed and causing the errors?
 

 A few weeks back we moved the database from a Windows Server 2008R2 to CentOs  
to accommodate an increased load, on the database, before this we hand never 
seen this kind of error. The windows server used a default firebird config file 
except for DefaultDbCachePages that was configured to 1024.
 

 Firebird: SuperClassic 2.5.7
 Server: Virtual server, 16 cores, 128Gb of Ram, Multi disk SSD SAN.

Re: [firebird-support] Re: Firebird 2.5: Nested querys help

2017-08-31 Thread setysvar setys...@gmail.com [firebird-support]


Den 31.08.2017 21:01, skrev duque.herna...@yahoo.com [firebird-support]:

Set, thank you for your help.

I'll like to explain my self better. If having these records:

  table_id  date_time customer_id  other_fields
      ---  
  001   08/30/2017 08:00  0015 whatever
  002   08/30/2017 08:10  0025 whatever
  003   08/30/2017 08:20  0025 whatever
  004   08/30/2017 08:30  0011 whatever
  005   08/30/2017 08:40  0014 whatever
  006   08/30/2017 08:50  0025 whatever
  007   08/30/2017 09:00  0021 whatever
  008   08/30/2017 09:10  0024 whatever
...

What I need is to select only the first record (based on date_time) 
from each customer_id. Like this:


  table_id  date_time customer_id  other_fields
      ---  
  001   08/30/2017 08:00  0015 whatever
  002   08/30/2017 08:10  0025 whatever
  004   08/30/2017 08:30  0011 whatever
  005   08/30/2017 08:40  0014 whatever
  007   08/30/2017 09:00  0021 whatever

...

OK Hernando, then it is simply

SELECT xx0.*
FROM my_table xx0
WHERE NOT EXISTS(SELECT *
 FROM my_table xx1
 WHERE XX0.customer_id = XX1.customer_id
   AND (XX0.date_time > XX1.date_time
  OR (XX0.date_time = XX1.date_time
  AND XX0.table_id > XX1.table_id)))

What's after the OR is fairly unlikely to happen, it covers the case of 
identical timestamps. It is up to you whether you want the last two 
lines to cover this or only keep the first six lines.


HTH,
Set

Re: [firebird-support] PThread error

2017-08-31 Thread camrad cam...@internode.on.net [firebird-support]

On 11/07/2017 9:27 PM, Elmar Haneke el...@haneke.de [firebird-support] 
wrote:
> after some time my fbserver does refuse to open new connections:
>
>> elmar (Server)  Tue Jul 11 13:05:36 2017
>>  Operating system call pthread_create failed. Error code 11
>>
>>
>> elmar (Server)  Tue Jul 11 13:05:36 2017
>>  Database: /usr/local/fbdata/maindb.fdb
>>  internal Firebird consistency check (cannot start thread)
> I'm running an v2.5.6 on Debian/sid/AMD64.
>
> What might cause that Problem?
>
> Currently the only solution is to restart fbserver.
>
>
> Elmar
>
>

Hi Elmar,

Did you ever get a solution to this problem?

I've now got a similar problem running gbak.

Gbak backs up without errors but doing a restore with gbak produces the 
following errors in the log.

FLT002 (Server)    Fri Sep 01 10:52:27 2017
     Operating system call _beginthreadex failed. Error code 8


FLT002 (Server)    Fri Sep 01 10:52:27 2017
     Database: C:\TEMP\DIARYQ.FDB
     internal Firebird consistency check (cannot start thread)

cheers, Paul

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

[firebird-support] Firebird 2.5: Nested querys help

Re: [firebird-support] Firebird 2.5: Nested querys help

[firebird-support] Re: Firebird 2.5: Nested querys help

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?

[firebird-support] Sort error No free space found in temporary...

Re: [firebird-support] Re: Firebird 2.5: Nested querys help

Re: [firebird-support] PThread error

9 matches

Site Navigation

Mail list logo

Footer information