Re: [firebird-support] Any 'run as' in SQL/PSQL in FB3 ?
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
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
>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
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 ?
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 ?
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...
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
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
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