[firebird-support] Info

2017-12-19 Thread Nico Speleers nico.spele...@carfac.com [firebird-support] 
Hi,

Ex.
CREATE TABLE ADDRESSES (
  IDINTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
  ADDRESSNAME   VARCHAR(100) COLLATE UNICODE_CI_AI,
  STREETNAMEVARCHAR(100) COLLATE UNICODE_CI_AI,
  STREETNUMBER  VARCHAR(100) COLLATE UNICODE_CI_AI,
  STREETNUMBERBOX   VARCHAR(100) COLLATE UNICODE_CI_AI,
  EXTRAADDRESSLINE  VARCHAR(100) COLLATE UNICODE_CI_AI,
  WEBSITE   VARCHAR(100) COLLATE UNICODE_CI_AI,
  EMAILADDRESS  VARCHAR(100) COLLATE UNICODE_CI_AI,
  CALLINGCODEHOMEPHONE  VARCHAR(10) COLLATE UNICODE_CI_AI,
  HOMEPHONE VARCHAR(100) COLLATE UNICODE_CI_AI,
  CALLINGCODEBUSINESSPHONE  VARCHAR(10) COLLATE UNICODE_CI_AI,
  BUSINESSPHONE VARCHAR(100) COLLATE UNICODE_CI_AI,
  CALLINGCODEFAXVARCHAR(10) COLLATE UNICODE_CI_AI,
  FAX   VARCHAR(100) COLLATE UNICODE_CI_AI,
  POSTALCODEID  INTEGER NOT NULL,
  ROWVERSIONTIMESTAMP,
  /* Keys */
  CONSTRAINT PK_ADDRESSES
PRIMARY KEY (ID),
  /* Foreign keys */
  CONSTRAINT FK_ADDRESSES_POSTALCODEID
FOREIGN KEY (POSTALCODEID)
REFERENCES POSTALCODES(ID)
);

Id = 1 / AddressName = Test / ..
Id = 2 / AddressName = Test2 / ..

I want to know the next Id number in my table Addresses without an insert. In 
my example it will be 3. I want to reserve (without an insert) Id number 3 
because someone else can also ask the next Id number. In that case it must be 
id  number = 4, because you've got Number 1 and 2 in the table and number 3 is 
reserved.

How can I do that ?

Thanks.

Nico Speleers
Team Lead Analyse & Consultancy





Carfac bvba

[Beschrijving: Beschrijving: Beschrijving: CarfacAS 
(klein)]Driving on experience


Ambachtstraat 8 | 9700 Oudenaarde
Tel. +32 55 23 00 00 | Fax +32 55 31 00 95
nico.spele...@carfac.com | 
www.carfac.be

[Facebook][LinkedIn][Twitter]





[Non-text portions of this message have been removed]

[firebird-support] Problem with CONTAINING and COLLATE - UTF8

2017-12-19 Thread Gabor Boros mlngl...@bgss.hu [firebird-support] 
Hi All,

After the original "Problem with CONTAINING and COLLATE" thread I 
switched to UTF8 but not happy with it. The optional COLLATE after 
CONTAINING have no effect, the database default COLLATION used. See the 
below examples. Tried with 3.0.3.32854 snapshot and Linux 64bit (Ubuntu 
14.04.1).


1.Database defaults: UTF8+UNICODE_CI
CREATE DATABASE 'CHARSET_TEST' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8 
COLLATION UNICODE_CI;

isql CHARSET_TEST -ch UTF8 -u SYSDBA

CREATE TABLE TABLE1 (NAME VARCHAR(60));

INSERT INTO TABLE1 (NAME) VALUES ('od');
INSERT INTO TABLE1 (NAME) VALUES ('őb');
INSERT INTO TABLE1 (NAME) VALUES ('Őc');
INSERT INTO TABLE1 (NAME) VALUES ('Oa');

SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o';

NAME

od
Oa


SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő';

NAME

őb
Őc


SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI_AI;

NAME 
 
 

 
 
 

od 
 
 

Oa


SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő' COLLATE 
UNICODE_CI_AI; 
 

 
 
 

NAME 
 
 

 
 
 

őb 
 
 

Őc





2.Database defaults: UTF8+UNICODE_CI_AI
CREATE DATABASE 'CHARSET_TEST' PAGE_SIZE 4096 DEFAULT CHARACTER SET UTF8 
COLLATION UNICODE_CI_AI;

isql CHARSET_TEST -ch UTF8 -u SYSDBA

CREATE TABLE TABLE1 (NAME VARCHAR(60));

INSERT INTO TABLE1 (NAME) VALUES ('od');
INSERT INTO TABLE1 (NAME) VALUES ('őb');
INSERT INTO TABLE1 (NAME) VALUES ('Őc');
INSERT INTO TABLE1 (NAME) VALUES ('Oa');


SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o';

NAME

od
őb
Őc
Oa

SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő';

NAME

od
őb
Őc
Oa

SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI;

NAME

od
őb
Őc
Oa

SQL> SELECT * FROM TABLE1 WHERE NAME CONTAINING 'ő' COLLATE UNICODE_CI;

NAME

od
őb
Őc
Oa


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/

Re: [firebird-support] Info

2017-12-19 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
19.12.2017 16:34, Nico Speleers nico.spele...@carfac.com [firebird-support] 
wrote:
> I want to know the next Id number in my table Addresses without an insert. In 
> my example 
> it will be 3. I want to reserve (without an insert) Id number 3 because 
> someone else can 
> also ask the next Id number. In that case it must be id number = 4, because 
> you've got 
> Number 1 and 2 in the table and number 3 is reserved.
> 
> How can I do that ?

   Use sequence.


-- 
   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/

[firebird-support] Firebird 3.0.3 no longer compatible with InterBase 7.5.1 clients

2017-12-19 Thread FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] 
Hi,


I've been testing a set of apps that use the InterBase 7.5.1 client library 
against Firebird 3.0.2 and have found them to be sufficiently compatible (with 
minimal changes).  However after updating a server to Firebird 3.0.3, InterBase 
7.5.1 clients (including isql) hang when executing a simple SELECT query.


I understand that this is not explicitly supported, but wonder if it was 
intended that this update would break the existing de-facto compatibility.


Wireshark traces show that the server is sending all the data from the query, 
but the client is not recognising it and waits forever - I can provide more 
detailed examples, logs etc if anyone is interested.


Background: I'm planning to upgrade a point-of-sales network of ~1000 lanes 
from InterBase 7.5.1 to Firebird 3.  Since each lane has a local db, and there 
are multiple apps that connect to lane and central dbs, I want to ensure the 
apps can work before and after migration, so as to avoid having to update 
everything all at once.  I originally planned to leave the clients on gds32.dll 
from InterBase 7.5.1 until after all databases had been migrated to Firebird, 
then update the clients too.  It seems another possibility could be to update 
the clients first to fbclient.dll from an older version of Firebird (FB2.5.7 
seems to connect to IB7.5.1 ok) before migrating the databases.


Cheers,
Steve



Note:
This e-mail message has been inspected for malicious content.


Attention:
The information contained in this message and or attachments is intended only 
for the person
or entity to which it is addressed and may contain confidential and/or 
privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon,
this information by persons or entities other than the intended recipient is 
prohibited. If you
received this in error, please contact the sender and delete the material from 
any system and
destroy any copies.
Please note that the views and opinions expressed in this message may be those 
of the
individual and not necessarily those of Foodstuffs South Island Ltd.


Thank you.

Re: [firebird-support] Problem with CONTAINING and COLLATE - UTF8

2017-12-19 Thread setysvar setys...@gmail.com [firebird-support] 
 >After the original "Problem with CONTAINING and COLLATE" thread I 
switched to UTF8 but not happy with it.
 >The optional COLLATE after CONTAINING have no effect, the database 
default COLLATION used. See the below
 >examples. Tried with 3.0.3.32854 snapshot and Linux 64bit (Ubuntu 
14.04.1).



Why would you want collate AFTER containing? I must admit I haven't 
tried your queries in Fb 3.0 (I'm still on 2.5), but I'd say the 
sensible way to specify what you want, would be

SELECT * FROM TABLE1 WHERE NAME COLLATE UNICODE_CI_AI CONTAINING 'o'; 
//returns four rows on an old 2.5 installation

and not

SELECT * FROM TABLE1 WHERE NAME CONTAINING 'o' COLLATE UNICODE_CI_AI; 
//gives a 'data type unknown' error message

I read your statement as "'o' COLLATE UNICODE_CI_AI", not the entire 
where clause being told to use that collation. Though, as I said, I 
don't have Fb 3.0 on this computer and don't know whether or not Fb 
agrees with me.

HTH,
Set






++

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 3.0.3 no longer compatible with InterBase 7.5.1 clients

2017-12-19 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
19.12.2017 4:32, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] 
wrote:
> Wireshark traces show that the server is sending all the data from the query, 
> but the 
> client is not recognising it and waits forever

   So, you, actually, should ask Embarcadero why their client cannot work with 
Firebird 
anymore.


-- 
   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 3.0.3 no longer compatible with InterBase 7.5.1 clients

2017-12-19 Thread FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] 
Haha, yes that's fair enough.  I suppose after this long it's mostly just luck 
that the IB7.5.1 client library is able to connect to FB3.0.2 at all.

Perhaps a better question is whether anyone has experience with this kind of 
migration where it is difficult to change all clients and servers from 
InterBase to Firebird at once.  I still have two paths forward:

A. Migrate all servers to FB3.0.2 (or earlier), then update clients to 
corresponding fbclient.dll.  After that it is easier to incrementally upgrade 
Firebird servers and clients.

B. Update all clients to fbclient.dll 2.5.7 (or earlier), then migrate all 
servers to latest Firebird, then update clients to corresponding fbclient.dll.

In each case I understand that compatibility between InterBase and Firebird in 
either direction is not assured or supported.  We will have to carry out our 
own testing to satisfy ourselves that our apps will operate acceptably through 
the transition (but so far either scenario above looks feasible).

I'd be interested to hear if anyone has tried what I'm proposing or has any 
tips on the best way to proceed.

Thanks,
Steve


-Original Message-
From: firebird-support@yahoogroups.com 
[mailto:firebird-supp...@yahoogroups..com]
Sent: Wednesday, 20 December 2017 9:07 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird 3.0.3 no longer compatible with 
InterBase 7.5.1 clients

19.12.2017 4:32, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] 
wrote:
> Wireshark traces show that the server is sending all the data from the
> query, but the client is not recognising it and waits forever

   So, you, actually, should ask Embarcadero why their client cannot work with 
Firebird anymore.


--
   WBR, SD.


Note:
This e-mail message has been inspected for malicious content.

Attention:
The information contained in this message and or attachments is intended only 
for the person
or entity to which it is addressed and may contain confidential and/or 
privileged material. Any
review, retransmission, dissemination or other use of, or taking of any action 
in reliance upon,
this information by persons or entities other than the intended recipient is 
prohibited. If you
received this in error, please contact the sender and delete the material from 
any system and
destroy any copies.
Please note that the views and opinions expressed in this message may be those 
of the
individual and not necessarily those of Foodstuffs South Island Ltd.

Thank you.

Re: [firebird-support] getting generated if before insert (was: Info)

2017-12-19 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2017-12-19 16:34, Nico Speleers nico.spele...@carfac.com 
[firebird-support] wrote:
> Hi,
> 
> Ex.
> CREATE TABLE ADDRESSES (
>   IDINTEGER GENERATED BY DEFAULT AS IDENTITY 
> NOT NULL,
[..]
>   /* Keys */
>   CONSTRAINT PK_ADDRESSES
> PRIMARY KEY (ID),
>   /* Foreign keys */
>   CONSTRAINT FK_ADDRESSES_POSTALCODEID
> FOREIGN KEY (POSTALCODEID)
> REFERENCES POSTALCODES(ID)
> );
> 
> Id = 1 / AddressName = Test / ..
> Id = 2 / AddressName = Test2 / ..
> 
> I want to know the next Id number in my table Addresses without an
> insert. In my example it will be 3. I want to reserve (without an
> insert) Id number 3 because someone else can also ask the next Id
> number. In that case it must be id  number = 4, because you've got
> Number 1 and 2 in the table and number 3 is reserved.
> 
> How can I do that ?

Why do you want to do that? This sounds a bit like an XY-problem, you 
might want to start with describing the underlying problem you are 
trying to solve.

The whole point of using an identity column is that it is assigned at 
insert time. Trying to know it before hand defeats the purpose, and 
can't be guaranteed transactionally (if a concurrent transaction would 
insert a row, that transaction would get the 'next' id value, not your 
transaction).

Instead, use insert ... returning id to insert a row and obtain the 
generated identifier, and then do whatever needs to be done with that 
identifier.

Alternatively, as suggested by Dmitry, use a sequence instead.

I hope you are also aware that sequences (and identity columns for that 
matter) are atomic, but not transactional: once a value has been 
'generated', even a transaction rollback will not revert the value.

So:

start transaction
insert row (generated id = 1)
rollback
start transaction
insert row (generated id = 2, and not 1!)


Mark

Re: [firebird-support] Re: Deadlock error from firebird during midnight

2017-12-19 Thread LtColRDSChauhan rdsc1...@gmail.com [firebird-support] 
On Fri, Dec 15, 2017 at 4:25 AM, LtColRDSChauhan  wrote:

> Ok, i will.
> Thank you.
>
> On 15 Dec 2017 12:34 am, "Helen Borrie hele...@iinet.net.au
> [firebird-support]"  wrote:
>
>
>
> Hello LtColRDSChauhan,
>
> The net-provider list that you have been posting to on Sourceforge is
> inactive. Any postings there are ignored.
>
> You need to go to this URL:
>
> https://groups.google.com/forum/#!forum/firebird-net-provider
>
> and join the "new" group. It has been "new" for more than two months.
>
> HB
>
> Hello Helen,

1. Posted the issue to the group as suggested by you. Jiri responded
" I'm seeing this with
3.0.2 very often in production environment. Some issue in Firebird
itself."

2. The issue remains unresolved.

3. I am putting some desperate efforts and would seek to share some
observations with Dimitry, Hvlad and Jiri. Please advice how can I do this
/ where do I post.

Regards,
Rajiv



>
>
>

Re: [firebird-support] Info

2017-12-19 Thread Helen Borrie hele...@iinet.net.au [firebird-support] 
Hello Nico,

Wednesday, December 20, 2017, 4:34:09 AM, you wrote:

> CREATE TABLE ADDRESSES (
>   IDINTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,
>   ADDRESSNAME   VARCHAR(100) COLLATE UNICODE_CI_AI,


> Id = 1 / AddressName = Test / ..
> Id = 2 / AddressName = Test2 / ..

> I want to know the next Id number in my table Addresses without an
> insert. In my example it will be 3. I want to reserve (without an
> insert) Id number 3 because someone else can also ask the next Id
> number. In that case it must be id  number = 4, because you've got
> Number 1 and 2 in the table and number 3 is reserved.

IDENTITY is just lexical candy, useful when you have a generated id
that fires only on inserts.  You can mess around with the start number
to some degree, using DDL commands, but otherwise the underlying
generator (sequence) cannot be manipulated, as the name of the internal
generator for the column is unknown to clients. This is what IDENTITY
is about, in fact.

For any id's that you want to manipulate the way you describe, create
an explicit generator for it, along with a BI trigger for the table to
set the conditions for firing the generator.  You can use the NEXT
VALUE FOR  command when you want to capture the next
value without firing the trigger.  Generators fire outside the
transaction context of the request so, once a value is fetched, the
generator cannot return the same value again.

Of course, you don't want the trigger to fetch another new value and
overwrite the one you reserved, so you must make your BI trigger
detect whether the INSERT command supplies a value.

Helen Borrie