[firebird-support] complicated recursion or other option?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

in advance, many thanks!

 

I would like to combine all properties like this:

 

Property1  Property2 Property3
Property4..

IP54 Yellow   With lamp 30W

IP20 Blue  Without lamp40W

Red
50W

 
60W

 

In the first step I would like get this:

IP54-Yellow-With lamp-30W

IP54-Yellow-With lamp-40W

.

IP54-Yellow-Without lamp-30W.

 

(in the next step, I would like to set Properties which are not possible,
for example 60W is not possible if the Color is red)

 

For this, I have two tables:

 

Table A

ID Property (color could be value 1, lamp, IPXX, Watt..) integer

POS (in this sequence I would like to generatet he result) integer

ID itself, Integer, self generated.

 

Table B

ID itself auto generated integer

ID_Table A references to Table A

Property - (yellow, red..)

 

For example, 

Table A

ID Property  POSID itself

1 (Color)1 10

2(lamp) 2 11

3(Watt)3 12

 

 

Table B

ID itself ID_Table_A Property

101  10 RED

102 10 BLUE

103 10 White

104 11  With Lamp

105 11  Without Lamp

105 12 30 Watt

105 12 40 Watt

105 12 50 Watt

 

Now I would get:

 

Red-With Lamp-30 Watt

Red-With Lamp-40 Watt

Red-With Lamp-50 Watt

Red-Without Lamp-30 Watt

Red-Without Lamp-40 Watt

Red-Without Lamp-50 Watt

Blue-With Lamp-30 Watt.. and so on

 

How can I realize with firebird?

 

Thank you.

 

Best regards

 

Olaf

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] Re: FB3 - Limbo and consistency check

[Gabor Boros mlngl...@bgss.hu [firebird-support]]

2017. 04. 26. 16:44 keltezéssel, hv...@users.sourceforge.net 
[firebird-support] írta:
>Hi Gabor
> 
>Do you have reproducible example ? Could you send it to me (or 
> regiser at tracker), please ?

I send a test case to your address in this minutes. Hope that helps.

Gabor






++

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/

[firebird-support] complicated recursion or other option?

['Check_Mail' check_m...@satron.de [firebird-support]]

Hello,

 

in advance, many thanks!

 

I would like to combine all properties like this:

 

Property1  Property2 Property3
Property4..

IP54 Yellow   With lamp 30W

IP20 Blue  Without lamp40W

Red
50W

 
60W

 

In the first step I would like get this:

IP54-Yellow-With lamp-30W

IP54-Yellow-With lamp-40W

.

IP54-Yellow-Without lamp-30W.

 

(in the next step, I would like to set Properties which are not possible,
for example 60W is not possible if the Color is red)

 

For this, I have two tables:

 

Table A

ID Property (color could be value 1, lamp, IPXX, Watt..) integer

POS (in this sequence I would like to generatet he result) integer

ID itself, Integer, self generated.

 

Table B

ID itself auto generated integer

ID_Table A references to Table A

Property - (yellow, red..)

 

For example, 

Table A

ID Property  POSID itself

1 (Color)1 10

2(lamp) 2 11

3(Watt)3 12

 

 

Table B

ID itself ID_Table_A Property

101  10 RED

102 10 BLUE

103 10 White

104 11  With Lamp

105 11  Without Lamp

105 12 30 Watt

105 12 40 Watt

105 12 50 Watt

 

Now I would get:

 

Red-With Lamp-30 Watt

Red-With Lamp-40 Watt

Red-With Lamp-50 Watt

Red-Without Lamp-30 Watt

Red-Without Lamp-40 Watt

Red-Without Lamp-50 Watt

Blue-With Lamp-30 Watt.. and so on

 

How can I realize with firebird?

 

Thank you.

 

Best regards

 

Olaf

Re: [firebird-support] Performing bulk update taking time.

[Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]]

27.04.2017 9:03, 'Joje' j...@codework-solutions.com [firebird-support] wrote:
> Is there a way through which this query can be further optimised as this 
> query taking time
> for completion more than 3 -4 hours.  Could I use batch based update in this 
> qu ery…?

   Query in FOR SELECT does not need JOIN.
   MERGE will do that in one shoot.


-- 
   WBR, SD.






++

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] Firebird 2.5 - Alter table - drop default - bug or feature

[Lester Caine les...@lsces.co.uk [firebird-support]]

On 26/04/17 16:34, Ann Harrison aharri...@ibphoenix.com
[firebird-support] wrote:
> If Firebird were to convert a record from its stored format through each 
> intervening format, the result would be more logical but it would be a change 
> to a behavior that's over 30 years old. And there are advantages to the 
> current behavior.  If you alter a table in a way that invalidates existing 
> values, another alter table can undo the damage.  
That I think is my main 'objection' here ... having databases that
predate the arrival of Firebird ... if the change to metadata results in
a conflict, then *I* expect to resolve the conflict. Just as if I have
added just a NOT NULL and now need to populate the null fields.

>> The argument that other engines put forward is this idea that a record
>> does not need to store a full set of fields, some can be 'virtual' and
>> only exist when something is stored in them. I HOPE that this is not
>> something that Firebird plans to adopt? In my book the 'original value'
>> is always 'NULL' unless other rules require something replaces it, and
>> an empty field magically showing some default value is not a safe way of
>> working.
> Firebird doesn't store null fields, instead it stores an array of bits that 
> indicate whether or not a field is null.  Between that, compression, and 
> computed fields, there's a lot of magic going on. 
The field in the record indicates if it is null or not, and the metadata
indicates if that field is allowed to be flagged as null. The magic I'm
flagging here is if the field metadata provides a default value, and the
field does not actually store that value, but rather a new flag which
says 'default'. DEFAULT NOT NULL magically displays the current default
value for a null flagged field, while DEFAULT needs the extra flag to
distinguish between a null value and a default one. Returning to the 30
year old standard, once YOU have updated the table with a new value that
will be locked down in the stored records and the idea that a change to
the DEFAULT value will also change OLD default records is simply not
consistent with the way Firebird works?

-- 
Lester Caine - G8HFL
-
Contact - http://lsces.co.uk/wiki/?page=contact
L.S.Caine Electronic Services - http://lsces.co.uk
EnquirySolve - http://enquirysolve.com/
Model Engineers Digital Workshop - http://medw.co.uk
Rainbow Digital Media - http://rainbowdigitalmedia.co.uk






++

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] Firebird 2.5 - Alter table - drop default - bug or feature

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

>  Apr 25, 2017, at 12:21 PM, Lester Caine les...@lsces.co.uk 
> [firebird-support]  wrote:

> 
> On 25/04/17 16:50, Mark Rotteveel m...@lawinegevaar.nl
> [firebird-support] wrote:
>> The SQL standard is quite clear at what needs to happen: adding a new 
>> column with a default should behave as if that column has existed from 
>> the original create table (btw: irrespective of the NOT NULL constraint, 
>> something that if I'm not mistaken also Firebird doesn't do correctly).

Right.  When a record is read Firebird converts it from its declared format to 
the current format ignoring intervening formats.  ALTER TABLE does not cause 
changes to existing rows in the table (in general, there may be exceptions).  
The result is the appearing and disappearing default values. 
> 
> Exactly the problem in my book. If you add 'DEFAULT' without a 'NOT
> NULL', then in my book the unpopulated fields should remain NULL unless
> I populate them. When you now add creating the field later then things
> should be consistent but there is no consistent way of handling things
> based on the SQL standard inconsistencies.
> 
>> This has the effect that all existing records should get the default 
>> value as if they had that value from the start (either stored or 
>> virtual). It also means that a subsequent alteration of the default (new 
>> value or dropping the default), should not lead to changes to the value 
>> of those rows that existed before the column was added with; the columns 
>> need to retain the original default.

If Firebird were to convert a record from its stored format through each 
intervening format, the result would be more logical but it would be a change 
to a behavior that's over 30 years old. And there are advantages to the current 
behavior.  If you alter a table in a way that invalidates existing values, 
another alter table can undo the damage.  
> 
> The argument that other engines put forward is this idea that a record
> does not need to store a full set of fields, some can be 'virtual' and
> only exist when something is stored in them. I HOPE that this is not
> something that Firebird plans to adopt? In my book the 'original value'
> is always 'NULL' unless other rules require something replaces it, and
> an empty field magically showing some default value is not a safe way of
> working.

Firebird doesn't store null fields, instead it stores an array of bits that 
indicate whether or not a field is null.  Between that, compression, and 
computed fields, there's a lot of magic going on. 

Good luck,

Ann

Re: [firebird-support] Performing bulk update taking time.

[Mark Rotteveel m...@lawinegevaar.nl [firebird-support]]

On 27-4-2017 09:03, 'Joje' j...@codework-solutions.com 
[firebird-support] wrote:
> Is there anyway using which we could perform bulk update in firebird.
>
>  I have two tables A & B. Both tables are referenced by a *varchar(100)*
> *AR_ID* column.
>
> -  Total number of rows in each table is around *1 million*. Now
> in order to get faster result I created a *bigint* column *ref_A_ID* in
> Table B that is referenced to *A_ID* column of *table A.*
>
> -  Now, to reference existing data in table B. I want to update
> column *ref_A_ID* of table B with value of *A_ID* of table A for proper
> linking to table A.
>
> -  I created a query for updating table B is as below
>
[..]
>
> -  Is there a way through which this query can be further
> optimised as this query taking time for completion more than 3 -4 hours.
>  Could I use batch based update in this query…?
>
> Thanks.

You should try MERGE instead of that EXECUTE BLOCK, it is likely (but 
not guaranteed) to perform better.

eg something like:

merge into table_b as b
using table_a as a
   on b._id = a._id and b.ar_id = a.ar_id and b.ref_a_id is null
when matched then
   update set b.ref_a_id = a.ref_a_id

See 
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-merge.html

How well that really performs depends on the presence of a suitable 
index in table_b.

-- 
Mark Rotteveel

[firebird-support] Performing bulk update taking time.

['Joje' j...@codework-solutions.com [firebird-support]]

Hello everyone,

 

Is there anyway using which we could perform bulk update in firebird. 

 I have two tables A & B. Both tables are referenced by a varchar(100) AR_ID
column.  

-  Total number of rows in each table is around 1 million. Now in
order to get faster result I created a bigint column ref_A_ID in Table B
that is referenced to A_ID column of table A.

-  Now, to reference existing data in table B. I want to update
column ref_A_ID of table B with value of A_ID of table A for proper linking
to table A.

-  I created a query for updating table B is as below 

 

-  EXECUTE BLOCK 

-   AS 

-   DECLARE VARIABLE REF_A_ID BIGINT;

-   DECLARE VARIABLE AR_ID VARCHAR(200);

-   DECLARE VARIABLE _ID BIGINT;

-   

-   BEGIN

-   FORSELECT   A. AR_ID,   A.A_ID,   A. _ID  

-FROM  TABLE  A 

-INNER  JOIN   TABLE  B   ON   B. AR_ID   =  A.
AR_ID

-INTO :AR_ID,: REF_A_ID,:_ID 

-  DO 

-BEGIN

-  UPDATE   TABLEB 

-  SETB. REF_A_ID=: REF_A_ID 

-  WHERE   ( B. REF_A_IDISNULL  AND  B. _ID
= :_ID)  AND   (B. AR_ID = :AR_ID);

-END 

-   END

 

-  Is there a way through which this query can be further optimised
as this query taking time for completion more than 3 -4 hours.  Could I use
batch based update in this query.?

Thanks. 

 

 

With Regards,

Joje