Re: [firebird-support] Preparing for upgrade from 1.56 to 3 or 4

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 19.12.2018 at 17:33, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:
> Although I haven't read it myself, consider buying "Migration Guide to
> Firebird 3" by Carlos Cantu, see
> https://www.firebirdnews.org/migration-guide-to-firebird-3/

Just taking the occasion of this topic being discussed, I'd like to 
mention one thing that might get overlooked but may as well become a 
real issue in some cases.
There's a short note on the firebirdnews.org site
https://www.firebirdnews.org/how-to-migrate-existing-users-from-firebird-2-x-to-firebird-3/
saying, that upgrading existing users from 2.x to 3.x will generate a 
new random password for each user.
Is that really the only option? With a few dozens of systems deployed, a 
few dozens to a few hundreds users each (real FB users), notifying a 
couple of thousand employees of our customers about new passwords might 
be a real no-go.
That's one of the main reasons we're delaying the upgrade from 2.5 to 3 
at our customers' sites.

Any suggestions much appreciated.

thanks
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Does GRANT... TO PUBLIC has no more effect anymore?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 09.11.2018 at 16:03, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:
> On 2018-11-09 15:19, jonatan.laurit...@yahoo.dk [firebird-support]
> wrote:
>> I used to execute GRANT... TO PUBLIC for every new database object I
>> had created in the past, but for some time (I can not tell exactly -
>> whether starting from Firebird 2.1 or from Firebird 3.0 only) this has
>> not effect. I can see in the metadata tables, that PUBLIC has been
>> granted new rights, but individual users (who should inherited all the
>> assigned privilegies from the PUBLIC) have no access to the new
>> objects. It worked as expected in Firebird 1.5. Does something changed
>> here, is it by design now or is it error or I am doing something wrong
>> technically?
> 
> It should still work, although possibly some form of metadata-caching
> may be involved (not sure). Please provide a reproduction recipe.
> 

I can confirm it works correctly in 2.5.x (can't say anything about 3.x 
though). We're using the same approach (the users of our system are 
actually created as FB users, so granting access to PUBLIC is more 
convenient than having to execute dozens of grants after adding each new 
user; I know it has some downsides, but that's not the point here).

Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Working with SQL (might be using loop)

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 04.10.2018 at 10:34, Vishal Tiwari vishuals...@yahoo.co.in 
[firebird-support] wrote:
> Hi,
> I am trying to get below stuff using SQL only.
> 1. If I have any statement like "World is good enough to enjoy..." then in I 
> need to get SQL out put as"WorlD IS GooD EnougH TO EnjoY..." that is first 
> and last character of each word should be capital letter and rest should be 
> in small letters.
> 
> 2. If I give any number like 007, 10002, 5645 then if the first digit of the 
> integer value is 0 or 1 then a minus sign should be prefixed and if it is not 
> 0 or 1 then it should prefix + sign. We can enter integer value in string 
> format as well if needed. like -007, -10002, +5645...
> 3. Also, I need to make odd position character in capital letter in a given 
> statement like  "World is good enough to enjoy..." should be output as  
> "WoRlD Is GoOd EnUuGh To EnJoY..."
> SQL for every above points could be sepearte.
> 
> Thanks In Advance.
> 
> With Best Regards.
> Vishal

Don't take offense, but it looks to me like an academic homework rather 
than a real world problem (and shouldn't you do your homework by yourself?).
Can you give some justification for this kind of text transformation via 
SQL?
1 and 3 can be done in a stored procedure or execute block statement, 
where you have loops at your disposal and you can traverse a string 
character by character, I can't think of a simple query doing that.
2 is an easy one, a simple iif and substring should suffice, e.g.:
select
iif(substring(AFIELD from 1 for 1) in ('0','1'), '-', '+') || AFIELD
from SOMETABLE

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Prevent overlaping dates in concurent environment

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 12.09.2018 at 14:40, Omacht András aoma...@mve.hu [firebird-support] 
wrote:
 > [...]
>while (curr_date <= new.date_to) do
> 
>  begin
> 
>insert into reserved_date (res_date) values (:curr_date);
> 
>curr_date = dateadd(1 day to curr_date);
> 
>  end
> 
> end

On a second thought, I don't like this idea after all. What if instead 
of dates you wanted to process timestamps (date + time)? Would you 
insert all possible timestamps from the interval with a 1 second step? 
Or a millisecond?
There has to be a more elegant solution.

Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Prevent overlaping dates in concurent environment

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 12.09.2018 at 15:04, Svein Erling Tysvær setys...@gmail.com 
[firebird-support] wrote:
> Why wouldn't my suggestion work, Tomasz? When Karol inserts his first row
> into his main table it will contain DATE_FROM 2018-09-10 and DATE_TO
> 2018-09-20 and the trigger would insert 11 rows (one for each date) into my
> suggested table. When the next row is inserted with DATE_FROM 2018-09-15
> and DATE_TO 2018-09-22, the trigger would fail inserting 8 rows due to the
> unique constraint.
> 
> The one place where I know my suggestion was incorrect, is that the trigger
> needs to be AFTER UPDATE as well (I only said AFTER INSERT/DELETE), with a
> change of dates, both DELETE and INSERT must be done. Other than that I
> would expect it to work (although it does make things a bit slower than not
> having this additional table).
> 
> Set

Yes, I've already admitted I didn't get the idea that you wanted to 
insert all dates from within the interval (and not just the boundaries), 
sorry for that.
However, when you swap dates to timestamps (date + time) or just the 
intervals of real numbers, the whole idea breaks apart.
Nonetheless, the problem itself doesn't seem very artificial (I can 
imagine many situations when this kind of constraint would be useful) 
and it would be strange if there was no systematic solution.
In fact what is needed here is a check that gets executed on commit ;)

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Prevent overlaping dates in concurent environment

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 12.09.2018 at 14:40, Omacht András aoma...@mve.hu [firebird-support] 
wrote:
> Tested on 2.5.8, dialect 1:
> 
> 
> 
> CREATE TABLE RESERVED_DATE (
> 
>  RES_DATE  DATE NOT NULL
> 
> );
> 
> ALTER TABLE RESERVED_DATE ADD CONSTRAINT RESERVED_DATE_PK PRIMARY KEY 
> (RES_DATE);
> 
> 
> 
> CREATE TABLE MEETINGS (
> 
>  DATE_FROM  DATE NOT NULL,
> 
>  DATE_TODATE NOT NULL
> 
> );
> 
> 
> 
> CREATE OR ALTER TRIGGER MEETINGS_BI FOR MEETINGS
> 
> ACTIVE BEFORE INSERT POSITION 0
> 
> as
> 
> declare variable curr_date date;
> 
> begin
> 
>curr_date = new.date_from;
> 
>while (curr_date <= new.date_to) do
> 
>  begin
> 
>insert into reserved_date (res_date) values (:curr_date);
> 
>curr_date = dateadd(1 day to curr_date);
> 
>  end
> 
> end
> 
> 
> 
> run on first transaction:
> 
> 
> 
> insert into MEETINGS (DATE_FROM, DATE_TO)
> 
> values ('2018.09.01', '2018.09.10')
> 
> 
> 
> run parallel on secound transaction:
> 
> insert into MEETINGS (DATE_FROM, DATE_TO)
> 
> values ('2018.09.08', '2018.09.15')
> 
> 
> 
> violation of PRIMARY or UNIQUE KEY constraint "RESERVED_DATE_PK" on table 
> "RESERVED_DATE".
> 
> Problematic key value is ("RES_DATE" = '8-SEP-2018').
> 
> At trigger 'MEETINGS_BI' line: 9, col: 7.

OK, what I didn't get was that you intended to put _all_ subsequent 
dates from within an interval to the auxiliary table.
Well, that way it should work. And I wouldn't be too worried about the 
size of the table, unless the intervals would be hundreds of years long.

Tomasz


-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Prevent overlaping dates in concurent environment

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 12.09.2018 at 13:29, Omacht András aoma...@mve.hu [firebird-support] 
wrote:
> Create a (reserved_dates) table with date field, and make that field unique.. 
> When a user inserts a date into the reservation table a trigger immadiate 
> inserts this date to the reserved_dates table too. Then the unique key will 
> stop secound insert instead of the first transaction is not commited.

This won't work. All dates may be different and the intervals may still 
overlap.
Karol, that's an interesting issue and I'm really curious if there's a 
clever solution.
So far I've checked the check (pun intended):

create table TST1 (
 d1 timestamp,
 d2 timestamp,

 constraint no_overlap check (
 not exists (
 select * from TST1 t1
 where exists (
 select * from TST1 t2
 where t1.d1 between t2.d1 and t2.d2
 or t1.d2 between t2.d1 and t2.d2
 )
 )
 )
);

insert into TST1 values('01.01.2018', '30.09.2018'); -- in transaction A
insert into TST1 values('01.02.2018', '30.10.2018'); -- in transaction B
-- commit A (no errors)
-- commit B (no errors)

and it doesn't work. You can still insert two overlapping pairs and both 
transactions get committed without errors, resulting in overlapping 
intervals being inserted. So, unless you change the transaction 
isolation level (I always use read committed), I don't have more ideas 
at the moment.

have a good one
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Cannot create new database: "Can not access lock files directory /tmp/firebird/"

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

> So, try what the rest of the error message suggests and do:

Sorry, didn't read carefully enough. What I took as a part of the error 
message was actually what Paul Beach was suggesting as a fix (so I 
hereby give due credit to him). So I just gave you an example of what 
Paul described.

Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Cannot create new database: "Can not access lock files directory /tmp/firebird/"

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 23.08.2018 at 17:53, 'Paul Beach' pbe...@mail.ibphoenix.com 
[firebird-support] wrote:
> 
> < command followed by the path and filename, hitting Enter and then typing in 
> my username and password (the only user is sysdba and I did change the 
> password) I get the following error message:
> Statement failed, SQLSTATE = 08006
> Can not access lock files directory /tmp/firebird/>>
> 
> [...]

It all depends on the path you provide in CREATE DATABASE. If it is just 
a local filesystem path, e.g. CREATE DATABASE "/somefolder/dbname.fdb", 
then the FB instance handling your request is running with your current 
user account (somewhat like embedded FB).
So, try what the rest of the error message suggests and do:
CREATE DATABASE "localhost:/somefolder/dbname.fdb" USER "SYSDBA" 
PASSWORD "yourpassword";
This way you access the FB server via the network stack and the request 
gets handled by the FB inet server, running with "firebird" account 
priviledges.

good luck
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

[firebird-support] Multiple copies of the same post

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

I know it's off-topic, but does anyone else experience receiving 
multiple copies of some posts on this list, or is it just me?
Sometimes I have 2, 3 or 4 identical copies in my inbox (like Hugo's 
from yesterday - 2 copies). They seem identical except for their X-UIDL, 
but AFAIK UIDLs are being assigned by the receving e-mail server, so 
that doesn't prove anything.
Sorry to bother you with this, maybe someone just clicks "send" with a 
shaky hand, but maybe my mailbox is messed up. No duplicates from other 
lists, though.

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Multiple copies of the same post

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 09.08.2018 at 14:28, James Woodard woody-...@gt.rr.com 
[firebird-support] wrote:
> I've been getting the same thing. It's not all posts but a lot of them
> get duplicated.

I've just discovered I've been getting duplicates from one more list 
(IBObjects), which is also hosted on yahoogroups. So it's not specific 
to firebird-support, but might be specific to yahoogroups.

cheers
Tomasz

Re: [firebird-support] How to store a string in Firebird which includes #0's in it?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 08.08.2018 o 11:49, 'River~~' river14ap...@gmail.com 
[firebird-support] wrote:
> [...]
> So to go back to the original point I disputed, I personally intend to go
> on referring to the byte terminating a C string as being x00 not as the
> more pedantically correct chr(0).
 > [...]

To be precise, chr(0) is x00 for ANSI or UTF-8 encoded C-style strings, 
but it might as well be x00 x00 for UTF-16 (it's not always a single byte).
I really enjoyed your post, though. IMHO people starting their 
programming career with "npm install" miss much of the numerical methods 
basics (like fl arithmetic), which often leads to misunderstanding of 
terms and exaggregated expectations ;).

cheers
Tomasz

Re: [firebird-support] install.sh not working in Ubuntu 18.04?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 19.07.2018 o 01:31, erickdeoliveiral...@gmail.com [firebird-support] 
wrote:
> Hello. I want to test Firebird in Ubuntu 18.04.
>   I Downloaded FirebirdCS-2.5.8.27089-0.amd64.tar.gz 
> https://github.com/FirebirdSQL/firebird/releases/download/R2_5_8/FirebirdCS-2.5.8.27089-0.amd64.tar.gz
>   I run install.sh, and in terminal it asked me only to set a password to 
> database, after this, it prints the installation is complete, but service is 
> not started and no service is installed in startup. It only copied to 
> opt/firebird.

As far as I can see, you've installed FB classic. The instances of the 
classic FB are being started by system's inetd (see if the FB installer 
created a corresponding entry in /etc/inetd.conf - there should be an 
entry starting with "gds_db").
So, my guess is, you don't have any inetd-compatible daemon installed. 
Try to install openbsd-inetd (run: sudo apt install openbsd-inetd) and 
see if it helps.
It looks like modern Debians and Ubuntus don't install any inetd daemon 
by default, and the CS installer doesn't check if the daemon is present 
(and doesn't remind you to install one if it's not).

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Segmentation fault while trying to restore Firebird 2.1 database on Linux

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 04.07.2018 at 10:26, jonatan.laurit...@yahoo.dk [firebird-support] wrote:
> I am trying to restore Firebird 2.1 database on Linux but I am receiving 
> error message:
>   
> 
>   Command '/opt/firebird02/bin/gbak –c –v –p 4096 –user SYSDBA –pass 
> masterkey /backup/BIG_DATABASE /backup/BIG_DATABASE.FDB'
> failed with return code 139 and error message -bash: line 56: 11100 
> Segmentation fault
>   
> 
>   I am sure that this is not a Firebird error and I am not trying to complain 
> about any error, I am just thinking that I am doing something wrong on my 
> machine but what could be wrong? Upon repeated call the line number and 
> segmentation fault number could be different. Previously I could restore 
> database successfully on this machine but then I installed both Firebird 2.1 
> and Firebird 3.0 on this machine and now the request to Firebird 2.1 is 
> failing.

Just a wild guess, but maybe there's some kind of conflict between v2 
and v3 shared libraries (libfb*.so) and your v2 gbak is trying to load 
v3 shared libs and/or vice versa. Upon installation, FB makes some 
symlinks in /usr/lib (or /usr/lib64, depending on your distro) and maybe 
your second installation overwrote some files/symlinks made by the first 
one, causing Linux dynamic loader to see the wrong set of libraries.
Hard to tell without a closer look, but maybe it will point you in the 
right direction.
Checking your RAM, as suggested by Dimitry, definitely won't hurt, so 
you'd better do it anyway.

cheers
Tomasz

Re: [firebird-support] How can I correctly write this WHERE

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 20.06.2018 o 13:41, Venus Software Operations venussof...@gmail.com 
[firebird-support] wrote:
> Hi Thomasz
> 
> You have understood me correctly, that is what I want to do and your 
> suggestion
> works thanks.
> 
> Please see my reply to SET as the query has slowed down to 10 minutes with 
> your
> code.  The fault is in my setup but if have anything to suggest that would be 
> great

Probably an indexing issue. First, make sure TRIM is really needed 
(usually it isn't, padding is omitted in string comparison anyway, but 
IIRC only right padding - someone correct me please if my memory is 
playing tricks on me).
Second, create indices on vwTA.cNameCity01 and tSI.tDt.
Third, if possible, try to get rid of the CASTs (unless tSI.tDt is not a 
date field, in which case try to pass your condition values in a form 
matching the data in tSI.tDt instead of converting the column to DATE; 
e.g. if it's a timestamp, maybe pass '2018-03-01 00:00:00' and 
'2018-03-31 23:59:59' and throw the CAST away [pun intended ;)]).
If you really need to transform the data on the fly in WHERE conditions, 
you make it virtually impossible for Firebird to use indices to your 
advantage.
If nothing of the above helps, send your query execution plan (plus the 
record counts of the tables involved and index defs).

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] How can I correctly write this WHERE

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

As I understand it, what you meant is:

where
(
TRIM(vwTA.cNameCity01) = 'Balance Sheet' and CAST(tSI.tDt AS DATE) < 
'2018-04-01'
)
or
(
TRIM(vwTA.cNameCity01) is distinct from 'Balance Sheet'
and CAST(tSI.tDt AS DATE) BETWEEN '2018-03-01' AND '2018-03-31'
)

If vwTA.cNameCity01 is sure to be not null, you can put a plain <> in 
place of "is distinct from".
This condition is different from what Svein posted an hour ago, but the 
above is how I understand your _intention_ in your WHEN condition (I 
might be wrong, though ;) ).

cheers
Tomasz

On 19.06.2018 o 10:25, venussof...@gmail.com [firebird-support] wrote:
> Hi all
>   
> 
>   I am trying to write a conditional WHERE clause but it fails.  Please 
> suggest a correct way to write this
>   
> 
>   WHERE CASE WHEN TRIM(vwTA.cNameCity01) = 'Balance Sheet'
>   THEN CAST(tSI.tDt AS DATE) < '2018-04-01'
>   ELSE CAST(tSI.tDt AS DATE) BETWEEN '2018-03-01' AND '2018-03-31' END
> 
>   
> 
>   vwTA and tSI are all joined into the query and the error I receive is
>   
> Message: isc_dsql_prepare failed
> 
> SQL Message : -104
> Invalid token
> 
> Engine Code: 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 35, column 29
> <
> 
> 
>   This is the < in the THEN clause
> 
>   
> 
>   Please advise
>   
> 
>   Kind regards
>   Bhavbhuti
>   
> 


-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Strange behaviour with char '#'

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 15.12.2017 at 09:17, Köditz, Martin martin.koed...@it-syn.de 
[firebird-support] wrote:

> I stumbled upon the following problem:
> I have a field of type varchar(7). When inserting the string '#ee' the 
> field value remains zero.
> 
> Does the character '#' have a special role?

No it doesn't. I've made a quick experiment a moment ago, just to make 
sure, and '#ee' was inserted just fine. I'd suspect some client-side 
processing of the string (you didn't provide any info about the client) 
or just a plain bug in the client code.
Try it with isql (I've tried both isql and flamerobin). If it works, 
check your client code.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Issue with GBAK and upgrading old DB (1.5 -> 3.x)

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 15.11.2017 at 13:48, stihpos...@yahoo.com [firebird-support] wrote:
>   When I try to restore, it asks for next volume... so, I suppose backup 
> failed. (When looking gbak with -v it works fine and finally gets to certain 
> table, says something like:
>   gbak: 16 records written
>   gbak: 18 records written
>   gbak: 20 records written
>   gbak: 22 records written
>   gbak: 24 records written
>   gbak: 26 records written
>   gbak: 28 records wri
>   
> 
>   ...and brings me back to my terminal, without any failure notice.

Have you tried to run gfix (e.g. -validate -full) on the database? What 
does it say? If the DB file is physically damaged, the only solution 
might be to gfix -mend it and then do a backup/restore (and even then 
there's no guarantee), but you need to be aware that some data may be 
lost (make a side copy of the DB and give it a shot).

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Creating a conditional "order by" statement

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 23.10.2017 at 14:37, j...@acousticdesign.co.uk [firebird-support] wrote:
> I have a stored procedure along the lines of
>   SELECT ID, Amount1, Amount2 ..
>   FROM Table1.
>   UNION
>   SELECT ID, Amount1, Amount2...
>   FROM Table2
>   ORDER BY 1
>   Is it possible to construct a conditional ORDER By clause that orders by 
> Amount1 under certain conditions, and order by Amount2 under other conditions?
>   

Try something like this:

select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1, 
Amount2, ...
from Table1
union
select iif(your condition, Amount1, Amount2) as SORTCOL, ID, Amount1, 
Amount2, ...
from Table2
order by 1

If there are more than two possible candidates for the ordering column, 
you can switch from iif to case - when - end.

have a good one
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] "Invalid Cursor State"

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 19.10.2017 at 16:46, Jorge Andres Brugger jorge.brug...@gmail.com 
[firebird-support] wrote:
> I´m not sure which was the solution that finally worked, as I´ve made
> several changes, mostly on the execution order in my program (windows exe,
> not FB).
> I suppose there is some issues with ODBC driver _and_ Clarion, but I´m not
> really sure about it.

The real nightmare of a programmer is not when the program doesn't work 
and she/he doesn't know why; it's when the program does work and she/he 
doesn't know why :)

have a good one
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Automatic Firing of Query.

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 17.08.2017 at 07:59, anshuman.na...@airtelmail.in [firebird-support] 
wrote:
> 
>   I would like some help to identify as to why and from where the following 
> query is getting executed on its own at particular intervals.   We have Thick 
> Client Application written using Delphi and Firebird (2.5.2).  The query is:
>
>   Select F.RDB$COMPUTED_BLR, F.RDB$DEFAULT_VALUE, R.RDB$FIELD_NAME from 
> RDB$RELATION_FIELDS R, RDB$FIELDS F where R.RDB$RELATION_NAME = ? and 
> R.RDB$FIELD_SOURCE = F.RDB$FIELD_NAME and ((not F.RDB$COMPUTED_BLR is NULL) 
> or  (not F.RDB$DEFAULT_VALUE is NULL)).
>
>   How can I make this execution stop as its causing me performance issues.

That query is probably being fired by the database access library you 
use in your application. Depending on which one it is (IBX, IBO, BDE, 
other), I'd suggest looking for the answer in places related to the 
particular DB access components. IMO Firebird itself has nothing to do 
with this query. Most of the DB component libraries query the system 
tables to determine DB structure and types of fields. In your particular 
case the query seems to get all fields from a particular table, which 
have default values, but why it is needed for is hard to tell.
Does it ask every time for the same table (can you peek at the actual 
value provided for "?" in R.RDB$RELATION_NAME = ?)? If so, maybe try to 
look for inserts/updates your app is making to that table and try to 
correlate them with appearance of this query - maybe you omit a field in 
an insert and the DB library tries to check whether the omitted field 
has a default value. But that's jus pure guessing.
When you wrote the query was being executed on its own, did you mean it 
gets fired even when your app is doing nothing, or just that it's an 
excess query not originating from your code?

Sorry I can't be more specific, but maybe my remarks will point you in 
the right direction.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] permissions nbackup

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

> Absolutely right. My point was having root should is not crucial.

It should read: having root is not crucial
Typing and having dinner at the same time is not healthy for your 
stomach and for your readers. Apologies.

Tomasz






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] permissions nbackup

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 14.08.2017 at 18:03, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> If you have sysdba permissions, you can do nbackup via services.
> Security database is small enough for gbak too.

Absolutely right. My point was having root should is not crucial.

regards
Tomasz






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] permissions nbackup

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 14.08.2017 at 17:41, Dimitry Sibiryakov s...@ibphoenix.com 
[firebird-support] wrote:
> 14.08.2017 17:21, Nick Upson n...@telensa.com [firebird-support] wrote:
>> if I do nbackup . it fails, telling me I cannot access the security2.fdb 
>> (as a normal
>> user)
> 
> Why can't you use su to change current user to root or firebird?

That's a bit off the point, isn't it? I manage a couple of databases 
located on servers I don't have root accounts on, and it's possible, 
although more complicated. So, su-ing is not always an option.

reagrds
Tomasz






++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] permissions nbackup

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 14.08.2017 at 15:51, Nick Upson n...@telensa.com [firebird-support] wrote:
> I'm looking at using nbackup but the file permissions on security2.fdb mean
> only firebird or root can do so, is that the way it is supposed to be?

Definitely. You wouldn't like any third party to access the password / 
security database directly, would you?
On the other hand, in what way does it stop you from using nbackup? I 
suppose what you actually need to do is to backup / restore a normal 
database, not the system-wide security db.

regards
Tomasz

Re: [firebird-support] Changing collation

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 29.06.2017 at 19:15, Tomasz Tyrakowski t.tyrakow...@sol-system.pl 
[firebird-support] wrote:
> On 28.06.2017 at 22:58, Ann Harrison aharri...@ibphoenix.com
> [firebird-support] wrote:
> [...] > Or is 3 strictly bound
> to PXW_PLK and always means PXW_PLK, regardless of the character set of
> the database? I suppose this is not the case, 'cause setting a column
> collation to WIN1250 in a database with the default character set
> WIN1250 results in RDB$COLLATION_ID = 0 (and I don't think WIN1250 is so
> special as to be given the value of 0).
> On a second thought, don't waste your time on it. I'll just create those
> two DBs, see for myself, and post a quick update.

OK, I probably had it coming ;)
Obviously you can't create a database with the character set WIN1252 and 
then set the collation of a column to WIN1250 or PXW_PLK. No wonder the 
only result you get is an error message.
So my last question was plain stupid, but updating RDB$COLLATION_ID and 
then a backup-restore is still a temptation...

thanks and best regards
Tomasz

Re: [firebird-support] Changing collation

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 28.06.2017 at 22:58, Ann Harrison aharri...@ibphoenix.com 
[firebird-support] wrote:
> Not if you have indexes, a declared primary key, or a declared unique
> constraint.  The index keys are a transformation of the value into a format
> that produces the desired ordering through binary comparisons.

Any chance that setting the RDB$RELATION_FIELDS.RDB$COLLATION_ID and 
then backing up and restoring the whole database would re-create the 
index trees properly?

> Not safe, won't work, and will produce very odd results on range queries on
> any
> index.  Or, say, a query that causes Firebird to return rows in index
> order.

Agreed, but again... restoring the database from a backup rebuilds all 
indices anyway, the piece of information I lack is: would they be 
rebuilt in the proper (new) collation?

>> Also, does the RDB$RELATION_FIELDS.RDB$COLLATION somehow depend on the
>> default character set of the database, or is it universal (i.e. does the
>> collation code from RDB$RELATION_FIELDS.RDB$COLLATION always mean the
>> same, or does a single code mean different collations in databases with
>> different default character sets)?
>>
> The format of the keys depends on the character set - different character
> sets
> have different meanings for some byte values, which collate differently.

Yes, that's clear, but my question concerned the collation id values 
kept in RDB$COLLATION_ID. Suppose you have a database with the default 
character set WIN1250. Setting the PXW_PLK collation results in 
RDB$COLLATION_ID set to 3 (checked that). Is it possible that the same 
value 3 would correspond to a different collation in another database, 
e.g. one with the default character set WIN1252? Or is 3 strictly bound 
to PXW_PLK and always means PXW_PLK, regardless of the character set of 
the database? I suppose this is not the case, 'cause setting a column 
collation to WIN1250 in a database with the default character set 
WIN1250 results in RDB$COLLATION_ID = 0 (and I don't think WIN1250 is so 
special as to be given the value of 0).
On a second thought, don't waste your time on it. I'll just create those 
two DBs, see for myself, and post a quick update.

> Good luck,
> 
> Ann

Thank you for your time and valuable information.

Best regards
Tomasz

Re: [firebird-support] Changing collation

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 27.06.2017 at 19:39, Tomasz Tyrakowski t.tyrakow...@sol-system.pl 
[firebird-support] wrote:
> Is it possible to change the collation of a varchar column in place
> (without making a temporary column, copying data, dropping the original
> column, making it anew and copying the data back)? Would setting
> RDB$RELATION_FIELDS.RDB$COLLATION do the trick (and is it safe to do)?

Just for the record: the actual field name is 
RDB$RELATION_FIELDS.RDB$COLLATION_ID.

Tomasz

[firebird-support] Changing collation

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

Is it possible to change the collation of a varchar column in place 
(without making a temporary column, copying data, dropping the original 
column, making it anew and copying the data back)? Would setting 
RDB$RELATION_FIELDS.RDB$COLLATION do the trick (and is it safe to do)? 
I've got several old production databases which have different 
collations on different columns (probably by mistake) and would like to 
sort it out.
Also, does the RDB$RELATION_FIELDS.RDB$COLLATION somehow depend on the 
default character set of the database, or is it universal (i.e. does the 
collation code from RDB$RELATION_FIELDS.RDB$COLLATION always mean the 
same, or does a single code mean different collations in databases with 
different default character sets)?
Apart from that, I've got a production database with the default 
character set NONE (sigh). I assume there's no way changing it, other 
than a fresh DB + pump, but just in case... has anyone come up with 
another idea? (the DB is almost 100 gigs)

Thanks in advance for any hints.

Best regards
Tomasz

Re: [firebird-support] Re: UDF and server crashes on linux

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 03.05.2017 o 09:28, hv...@users.sourceforge.net [firebird-support] wrote:
> ---In firebird-support@yahoogroups.com,  wrote :
>   
>   Tested on FB 2.5.7 and 3.02 ubuntu_x64 14.04 (two different machines);
>   On windows fbx64 works ok.
>   
>   udf return integer by value.
>   
>   scenario:
>   more than 2 connections to db
>   sql : select count(*) from table where validfield(field) = 1 on each 
> connection
>   table with > 2 milions record
>   
>   udf written in object pascal ( no memory leaks and other problems)
>   
>   other implementation this udf in C++ works fine on linux and windows...
>   
>   stacktrace:
>   [Thread 0x7fa737789700 (LWP 28692) exited]
>   [New Thread 0x7fa737789700 (LWP 28693)]
>   [New Thread 0x7fa736dd7700 (LWP 28694)]
>   [New Thread 0x7fa73603f700 (LWP 28698)]
>   An unhandled exception occurred at $7FA736178E2C:
>   EStackOverflow: Stack overflow
> 
> 
>This is object pascal exception, not Firebird's one.
> 
>Start isql, connect in embedded (aka local) mode, attach gdb to the 
> process and run
> query with problem UDF. It should help you to find a real reason of the issue.
> 
> Regards,
> Vlad

If Vlad's suggestion doesn't help (it definitely should, unless you're 
not familiar with gdb), the first thing I'd do is to extract the 
problematic function from the UDF, put it into a simple app and test 
with different inputs if it actually works (the stack overflow exception 
suggests there might be something wrong with the algorithm, like unbound 
recursion or an infinite loop; I know you wrote "no memory leaks and 
other problems", I would only add "that I'm aware of" ;) ). Also, 
assuming you're compiling the UDF with FPC (free pascal), take into 
account some multithreading specifics related to free pascal (take a 
look at
http://wiki.freepascal.org/Multithreaded_Application_Tutorial
), in particular remember to include cthreads and cmem units in your 
uses list on linux. Keep in mind that superserver and superclassic 
flavors of FB are multithreaded, so your UDF is in fact being loaded 
into a multithreaded process, even if the UDF doesn't do any 
multithreading itself.

have a good one
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Immediate Detection of Disconnected Clients on Classic

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 25.04.2017 o 17:03, hv...@users.sourceforge.net [firebird-support] wrote:
 > [...]
>   I don't actually think Firebird can detect client disconnection
>   "immediately" when there is no data exchange between the server and the
>   client (it would require some kind of heartbeat increasing traffic and
>   would be based on timeouts, which are tricky), otherwise byzantine
>   network partitions wouldn't be so nasty.
> 
> 
> Firebird can do it even if there is no data exchange because it always 
> listens all
> known client socket. When OS closed socket it is detected by Firebird 
> listener.
> 
> Regards,
> Vlad

OK, that would require another rather lengthy (and probably boring for 
most readers) explanation, but skpping the details: when a TCP 
connection (a socket) is closed "normally", there's some handshaking 
performed between the endpoints (FIN and FIN ACK packets being 
exchanged). And in that case a TCP socket can detect "immediately" that 
the other party has just closed its socket.
Another scenario: the client simply crashes without properly closing its 
socket and the client OS reclaims the resources. As soon as the server 
(which doesn't know yet that there is no client app any more) tries to 
send a packet to a nonexistent client socket, it gets a response from 
the client OS (not the client app - there is none), that the endpoint is 
invalid (a RST packet, followed by a SYN-RST sequence, if I remember 
correctly, but that's irrelevant). That's another case when the server 
can detect the client failure, but the detection takes place at the 
moment of the next data transmission to the client (and there might be 
none for quite a long time - unless the server does some kind of 
heartbeat with all its clients).
However, when you have a client - server connection, and the packets 
travel via many routers in between, and one of the routers fail, there's 
just no traffic going either way (no FIN, ACK, RESET packets get 
through). In this case neither side knows what's going on - is there a 
network failure, has the other side crashed, or just the network got 
really slow. The server sends some data and nothing comes back (neither 
ACK nor RESET), so the TCP protocol retries the transmission after some 
time (again no response), it tries again etc., and after the final 
timeout (which is implementation-dependent) it considers the connection 
to be unusable and closes the socket (and that's the moment the server 
process gets notified).
If you've started to yawn in the middle of my argument, don't worry - 
I'm used to that ;) Just wanted to point out there's no magic in TCP and 
if you partition the network and no control packets can be exchanged, 
there's no way you can detect what really happened. So let's not blame 
Firebird for keeping open connections to dead clients for some time, 
that's not its fault.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Immediate Detection of Disconnected Clients on Classic

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 24.04.2017 o 10:41, atuncce...@duzen.com.tr [firebird-support] wrote:

>   We have some desktop applications which are connecting to firebird, started 
> crashing for a while.
>   These applications are mostly on our wireless link connected building so 
> I'm suspicious about the network.
>   Then I created a new test project. When I run the application, connect to 
> the database, disconnect the client network just for a second and reconnect 
> the network again,  the database connection is broken and the application 
> can't continue.
>   Then I saw a new feature on firebird 2.5 like this:
>   Immediate Detection of Disconnected Clients on Classic Vladyslav Khorsun
>   The Classic server now detects immediately when a Classic process has been 
> broken by a client disconnection. Its response is to terminate any pending 
> activity, roll back the active transaction and close the network connection. 
> Tracker reference CORE-818.
>   Can this be the reason that a short interruption on the network causing the 
> problems on the application?
>   Can this feaure be disabled so that I can see if this is the problem or not?

Hi,

My answer is based more on the way the networs work than the actual 
knowledge of FB internals. If you work on a wired network and the 
network interface is set up to auto detect the link availability (in 
most cases it is), pulling the network plug out and connecting it back 
causes the network interface to reset (all sockets are closed, the 
interface is set up from zero). However, if the network is disrupted 
somewhere in between the end points (your server and client) but both of 
them have the physical link up, both the server and the client keep the 
sockets open (eventually re-transmitting TCP packets after some 
timeouts), because they simply can't know why there's no traffic on the 
link - failure or just nothing to say ;). When the network gets 
operatinal again, it's possible that the database connection actually is 
still valid and the app keeps working like nothing happened.
In case of wireless, every disconnection is like pulling the plug - the 
interface resets and all sockets are closed. That might explain why your 
crashes occur mostly on clients connected via wifi.
I don't actually think Firebird can detect client disconnection 
"immediately" when there is no data exchange between the server and the 
client (it would require some kind of heartbeat increasing traffic and 
would be based on timeouts, which are tricky), otherwise byzantine 
network partitions wouldn't be so nasty.

hope this helps

cheers
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Re: Which connection causes object in use - is there a way to find that out?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 25.10.2016 o 10:20, hv...@users.sourceforge.net [firebird-support] wrote:
>> From time to time, when I try to alter a stored procedure in a
>> production database, I get "object in use" error.

>   Run DDL statement in wait transaction. Better to set some timeout to not 
> wait too much, but not no-wait mode
[...]
>   No, sorry (one can use lock manager but it is not trivial)

Thanks Vlad. I'll play a bit with wait transactions and see if it helps 
at least in some cases. At least I know there's no easy way which my 
ignorance prevents me from seeing ;)

regards
Tomasz

[firebird-support] Which connection causes object in use - is there a way to find that out?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

 From time to time, when I try to alter a stored procedure in a 
production database, I get "object in use" error. I was wandering, is 
there a way to find the connections (users) that prevent a particular 
procedure from being altered?
Querying MON$STATEMENTS is a no-go, no statement currently being 
executed uses the procedure (directly) and yet an alter results in 
"object in use".

thanks in advance
Tomasz

Re: [firebird-support] Character sets and collations of columns

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 26.08.2016 o 14:35, robert rottermann rob...@redcor.ch 
[firebird-support] wrote:
> I have no idea at on how to fix it "correctly", but would it not be an
> idea just to dump the tables with weird character into a text file, and
> search replace them.
>
> The wrong chars are probably uniformly wrong, so you should be able to
> fix them using sed or some such tool (on linux, I am not well versed
> with windows)

The funny part is, I don't believe the data is actually corrupted. I 
think the database tries to convert it incorrectly, using the info about 
the encoding of the columns (which might be incorrect) and the encoding 
used by the client. The actual data, when I display it via Flame Robin 
or isql seems to be OK, it's the sorting or uppercasing that goes weird.
Thanks for the suggestions anyway.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

[firebird-support] Character sets and collations of columns

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

One of the legacy databases I happen to maintain has character sets and 
collations messed up. As far as I know, the database was created with 
default character set "NONE" instead of "WIN1250", which should have 
been set back then (and it's over 10 years old).
Then, some columns have been added with the character set option set 
explicitly to WIN1250 (alter table X add Y varchar(n) character set 
WIN1250), while the old columns still remained as "NONE".
The clients were windows apps and used WIN1250 when connecting to the 
database.
Later on, another maintainer changed the RDB$COLLATION_ID flags for some 
of the columns in RDB$RELATION_FIELDS to indicate WIN1250 (value 3 as 
far as I know, although I'm not entirely sure if this flag actually 
concerns character sets, or only collation order - maybe the maintainer 
was convinced he was clever, while in fact nothing relevant happened), 
and set the default character set of the database to WIN1250 (in a way 
unknown to me - maybe even via binary editing the GDB file and changing 
some byte-flags inside).
So now the DB is kind of a mess (despite reporting dutifully WIN1250 as 
the default character set), with some columns created when the DB had 
"NONE" character set, some other create with character set WIN1250 set 
explicitly upon creation, yet another created without explicit character 
set but when the database has already had the "WIN1250" default 
character set, and possibly some columns, for which the RDB$COLLATION_ID 
was set by hand.
So my question is (in fact I'm not even sure what the right question is 
:( ): assuming the strings in the database are in fact WIN1250-encoded 
(all the clients used WIN1250), is setting the RDB$COLLATION_ID on all 
text columns a good idea and would actually make any difference?
All strange quirks that happen from time to time with this database are 
related to national characters. Sometimes a sort doesn't work as 
expected, sometimes a client function (like strtoupper in PHP) doesn't 
work on data from some of the columns (but works for other data). Is 
there a chance that setting the collation flag might help? Or is there 
another way to set it right?
I know the "right" right thing to do (pun intended) is to create a new 
DB and pump the data, but it has about 400 interconnected tables and 
over 60GB of size, so if there is another way, I would be grateful to 
hear about it first.

Thanks in advance and sorry for this lengthy story.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] SQL Error Code -104: What is wrong with this rather simply SQL?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 06.04.2016 o 22:03, 'stwizard' stwiz...@att.net [firebird-support] wrote:
> SELECT DISTINCT P.AREA_CODE, P.PHONE_NO,
>
>(SELECT COUNT(*)
>
>  FROM PHONE P2
>
> WHERE P2.PHONE_ID = P.PHONE_ID) AS CNT
>
>FROM PHONE P
>
>   WHERE P.AREA_CODE IS NOT NULL
>
>   GROUP BY 1, 2

[ ... ]

select A, B, (select count(*) from ...)
from ...

is not the same as

select A, B, count(*)
from ...

In the former case, the sub-select is not an aggregate function. It 
simply calculates and returns a scalar (a single number), which is 
treated just like one more "field" in selected records.
Therefore, for the GROUP BY to work correctly, you'd have to include the 
third selected column (sub-select in your case) in the GROUP BY.
But that's not what you wanted, I suppose.
I guess what you really need is a normal grouped query:

select AREA_CODE, PHONE_NO, count(*)
from PHONE
where AREA_CODE is not null
group by 1, 2
having count(*) > 1

And that'll do the job.

Best regards
Tomasz

[firebird-support] Gbaked and restored database larger than the original?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

Hi,

Just for my cursiosity and for a better understanding of Firebird: what 
can be a possible reason for a gbaked and restored (to a different file) 
database being _larger_ than the original one?
I've got a db of size 104521728 bytes. I do gbak -B -T database.fdb 
database.gbk, then gbak -C database.gbk newdatabase.fdb. The size of 
newdatabase.fdb is 104751104 (~ 200kB more).
I'm the only one messing with these databases, the platform is Linux 
x64, Firebird 2.5.2, all operations performed on the same machine, in 
the same file system. Before the backup-restore, a quite large chunk of 
metadata was added/altered in the original database (procedures, 
fields), which is the only clue I can think of at the moment (adding a 
default value to a field which had none?).
It's quite obvious why a restored database might be smaller than the 
original one (no garbage), but the other way around is a bit puzzling.
Both databases seem to work fine, so I'm in no trouble, but my ignorance 
in this matter bothers me nonetheless ;)
I'd be grateful for any plausible explanations.

thanks in advance and best regards
Tomasz

Re: [firebird-support] Re: Gbaked and restored database larger than the original?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

W dniu 2015-07-08 o 14:50, Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] wrote:
 08.07.2015 15:18, Mark Rotteveel wrote:

 Another reason could be restoring with a different page size than the
 original database.

 Or some tables were altered to a longer record format after filling them
 with data. The original database contains records in the old (shorter)
 format while the restored database contains records in the new (longer)
 format. Sometimes record-level compression compensates that difference,
 but not always.

That's an interesting insight. I took a closer look at the actual 
metadata changes I mentioned in my first post. Apart from a few fields, 
which got assigned default values, there doesn't seem to be anything 
that could affect database size after restore. No field size changes at all.
But thanks for this suggestion anyway, it's worth remebmering that 
changing the type doesn't affect the data that's already there.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Gbaked and restored database larger than the original?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

W dniu 2015-07-08 o 14:18, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] wrote:
 Another reason could be restoring with a different page size than the
 original database.

Both databases have 8k pages.
Tomasz


-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Gbaked and restored database larger than the original?

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 2015-07-08 o 14:14, Svein Erling Tysvær 
svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote:
 Take a look here: http://www.firebirdfaq.org/faq361/

Yes, I've come across this on another occasion, but it doesn't seem to 
apply to my case (I didn't switch to a different version of FB).

 I'd add that restore also has a -use_all_space option. If you don't specify 
 this, then pages are filled to approximately 80% (80% is preferable over 100% 
 unless it is a read-only database). See 
 http://www.firebirdsql.org/manual/gbak-cmdline.html or 
 http://www.firebirdsql.org/manual/gfix-pagespace.html for more information.

And that's most probably the bull's eye! After restoring with 
-use_all_space, the database size is 94445568 bytes.

Thanks a lot Svein.

regards
Tomasz

Re: [firebird-support] Firebird 2.5.x slow queries problem

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 2015-05-20 o 10:51, Sylvain Mazaudier smaza...@yahoo.fr 
[firebird-support] wrote:
  [...]
 Query select 1: SELECT ROW1 FROM TEST;

 Query select 2: SELECT ROW2 FROM TEST;

 Résults in IBExpert monitor :
 Query select 1:1.5 sec
 Query select 2:5.3 sec

 [...]
 
 We don't understand why are the internal Firebird Queries very different and 
 why does it takes 4 more time to execute on ROW2 ?

It's been a couple of days without any answer to this, so even though I 
feel I'm not the most competent person, I'll give you my guess (I use 
many different object abstractions over FB client library and experience 
similar behaviour from time to time).
My point is, your question contains the answer. There's no such thing as 
internal Firebird queries. If you type a query A in your preferred 
client application and instead a query B gets executed by the FB server, 
it means your client application modifies your query (or executes 
additional queries). In this case, I suppose IBExpert needs some extra 
information about the tables and columns involved in your query, so it 
queries system tables to get the metadata. Why it does with one query 
and not with the other is the matter of its inner workings, of which I 
have no knowledge.
Try to execute your queries with Fierbird's isql and compare the 
results. If it behaves differently than IBExpert, the latter is to blame :).
Sorry for not being more specific, I don't use IBExpert and I'm not 
familiar with its internals, but there's a lot of people on this list 
who can give you better hints, just wait a while.

regards
Tomasz

-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] Query to update multiple records in same Table

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

On 2015-05-19 o 13:59, millsjac...@yahoo.com [firebird-support] wrote:
   Is there a single query that allows multiple row updates from multiple row 
 selects (e.g. like Copy  paste)
   i.e. Select row 1 column 7   update row 101 column 7
Select row 2 column 7  update row 102 column 7
   Etc for say 20 rows

   Column 1 (row) is ‘TAG_NO’ pk ascending,  Column 7 Is’ NAME’

   At the moment I have used Execute block that works ok but wonder if 
 possible using a query

   execute block  -- Copy 19 words from MMH UOP to 
 another UOP section
   as
   declare variable i integer;
   begin
   i = 2433; -- start Tag No of MMH UOP
   while (i  2452)   -- 19 words to be copied that all 
 UOP's have. Phase No, Brand etc.
   do
   begin
   update analog a1 set a1.name = (select a2.name from analog a2 where 
 a2.tag_no = :i)
   where a1.tag_no = :i + 1238; -- 1238 is Offset from 2433 to 
 start address of CIP UOP = 2433 +1238
   i = i+1;

   end
   end;

   By accident I came up with a query that appears to work but have not 
 finished fully testing  understanding,  so would like the help of this 
 skilled group for a known solution.

Usually the simplest solutions work best, so I would go for:

update ANALOG a
set a.NAME=(select NAME from ANALOG where TAG_NO=a.TAG_NO-1238)
where a.TAG_NO = 3671;

(3671 = 2433 + 1238)

Eventually you can further filter the rows being updated by adding more 
terms to the where clause.

regards
Tomasz






   Thanks for any help
   Jack




-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__

Re: [firebird-support] working days between two dates

[Tomasz Tyrakowski t.tyrakow...@sol-system.pl [firebird-support]]

If you mean week days only, probably you can come up with a clever 
stored procedure or UDF (shouldn't be that hard), but if you have to 
take various bank holidays into account, most of which are 
state-specific or even mobile (e.g. easter), a general, elegant solution 
doesn't come to mind.
However, I can share with you a dirty hack I did years ago and it still 
works like a charm: I pre-generated all holidays (including mobile ones, 
like easter - consult wikipedia how to calculate easter) for a couple of 
hundred years in advance and stored them in a table. Then I just had to 
exclude the dates from this table from any date spans I wanted to be 
holiday-free. The disadvantage of this solution is that when your 
government passes a bill constituting a new bank holiday, you have to 
update this table in all your production databases, or you're toast. On 
the other hand, you'd need to update the procedure/UDF/whatever anyway, 
so it's not that big a disadvangage after all.
Please post a note to the list if you find a better solution.

regards
Tomasz


On 2015-01-16 o 18:51, shg_siste...@yahoo.com.ar [firebird-support] wrote:
 Hello, using FB 2.5 is there an easy way to get the workings days between to 
 dates? Or should I do an UDF for that?


   Thanks!!


-- 
__--==--__
__--== Tomasz Tyrakowski==--__
__--==SOL-SYSTEM==--__
__--== http://www.sol-system.pl ==--__
__--==--__