[firebird-support] How can I prevent that my database can be opened with another security.fdb.

2016-04-07 Thread 'Mueller, Roland (GE87)' roland.muel...@honeywell.com [firebird-support] 
Hi all
How can I prevent that my database can be opened with another security.fdb.


For testing we use firebird 3.


We use our database as its own security database.
If we copy our database to another Firebird installation it

is possible to open it with sysdba and masterkey.
Is there a way to prevent The use of another security.fdb.


Roland Müller
Dipl.Ing. (FH)
Honeywell | Security and Fire
Novar GmbH
Johannes-Mauthe-Straße 14
72458 Albstadt
Büro: 07431 801-0
Fax: 07431 801-1512


roland.muel...@honeywell.com
www.honeywell.com/security/de








Novar GmbH
Rechtsform: GmbH
Sitz: Albstadt
Registergericht: AG Stuttgart, HRB 401195
Geschäftsführung: Martin Bemba, Klaus Hirzel, Marcus Lindenlaub, Marcus 
Ostländer
Aufsichtsratsvorsitzender:  Martin Göth



THIS E-MAIL AND ATTACHED FILES ARE CONFIDENTIAL, PROTECTED BY COPYRIGHT AND MAY 
BE LEGALLY PRIVILEGED. If you are not the intended addressee or have received 
this e-mail in error, any use of this e-mail or any copying, distribution or 
other dissemination of it is strictly prohibited. If you have received this 
transmission in error, please notify the sender immediately and then delete the 
e-mail. E-mail cannot be guaranteed to be secure, error free or free from 
viruses. Neither the sending company nor its group of companies accepts any 
liability whatsoever for any loss or damage that may be caused as a result of 
the transmission of this message by e-mail. If verification is required, please 
request a hard copy version.

[firebird-support] user on embedded

2016-04-07 Thread Marcel Nitzke marcelnit...@gmx.net [firebird-support] 
Hello,


i have a question of usersettings in version 3.0 of firebird.


i would create a user at database, because i use firebird as server and 
embedded. i want to protect my database.
on version 2.5 i can´t create a user on embedded database. every user 
can read the embedded database on a server with sysdba.


had version 3.0 a better userconcept?


best regards
Marcel


---
Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft.
https://www.avast.com/antivirus

RE: Re: [firebird-support] Firebird V3RC2 Primary Key question

2016-04-07 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Would you be so kind to provide an example SQL which shows how to do primary 
key with a case insensitive collation?

 

Thanks.

 

Ertan

 

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, April 7, 2016 8:54 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Firebird V3RC2 Primary Key question

 

  

This is the default and expected behavior. If you want case insensitive, then 
you need to create the primary key column with a case insensitive collation.

 

Mark

 

 

- Bericht beantwoorden -
Van: "Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr 
  [firebird-support]" 
 >
Aan:  >
Onderwerp: [firebird-support] Firebird V3RC2 Primary Key question
Datum: do, apr. 7, 2016 19:46

 

Hi,

 

It’s been a while since I last used Firebird. I am testing new release 
candidate at the moment. Just found something I am not so sure.

 

I have a database created using “CharSet WIN1254” parameter.

 

Below is the SQL I used to create a table in that database:

CREATE TABLE KULLANICI

(

  KODU VARCHAR(30)  NOT NULL PRIMARY KEY,

  ADI VARCHAR(50),

  SIFRE VARCHAR(64),

  EMAIL VARCHAR(60),

);

 

And, all below executes without error:

INSERT INTO KULLANICI(KODU) VALUES(‘EK’);

INSERT INTO KULLANICI(KODU) VALUES(‘ek’);

INSERT INTO KULLANICI(KODU) VALUES(‘Ek’);

INSERT INTO KULLANICI(KODU) VALUES(‘eK’);

 

I can insert “EK”, “ek”, “Ek”, and “eK” as new records in the KODU column of 
that table. Somehow primary key of that table is case-sensitive.

 

I couldn’t find anything about primary key defaults to case-sensitive in the 
“Firebird-3.0.0_RC2-ReleaseNotes.pdf”. I don’t remember if that was the case in 
earlier versions, too. Just want to be sure about it. Can anybody confirm if 
that is a feature?

 

On my part, I would like to avoid it if possible.

Thanks.

 

Regards,

Ertan Küçükoğlu

Re: [firebird-support] Re: First-In-First-Out (FIFO) stock valuation

2016-04-07 Thread setysvar setys...@gmail.com [firebird-support] 

Den 07.04.2016 08:42, skrev venussof...@gmail.com [firebird-support]:

Hi all

Sorry for resurrecting this very old thread but I have come across a 
situation (hopefully Jose would be able to answer).  I have found a 
bug in the bQty column it was not able to reduce itself to the current 
stock, eg. 3, and still it returns two row with a qty of 3+2 = 5 qty 
is valued which is wrong


SELECT qty, bRate, (qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= 3 /*current stock */
, (3 /*current stock */ - qtyOut)
, bQty) as qty
, bRate
FROM (SELECT sPurchaseBillItem.bQty
, sPurchaseBillItem.bRate
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
FROM sPurchaseBillItem AS
PurchaseBillItemInner
JOIN tPurchaseBill AS
PurchaseBillInner
ON
PurchaseBillInner.iID = PurchaseBillItemInner.iPID
WHERE (PurchaseBillInner.tDt >
tPurchaseBill.tDt AND PurchaseBillItemInner.iItemID = 342 /* item id */))
, 0) as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = 342 /*item id */) AS 
PurchaseBillsIntermediate

WHERE (qtyOut <= 3 /*current stock */)) AS PurchaseBillsFIFO

returns
3.0067.19201.57
2.0067.19134.38

the expected would have been
1.0067.1967.19
2.0067.19134.38

the original bqty in the top row is 11 but now only 1 of it remains, 
yet the above.  The only reason this might be happening is that both 
the rows belongs to the same tPurchaseBill parent record, because the 
same purchase bill had stock coming in from different medicine 
batches, thus the user needed to enter the same item in the same 
purchase bill twice but with different qty and batch nos.


Please advise.

Thanks and regards
Bhavbhuti


Hi Bhavbhuti!

I don't quite understand, could you show us the rows (only fields 
mentioned in the query) of tPurchaseBill and sPurchaseBillItem? Then we 
should at least be able to reproduce your incorrect result, and 
hopefully think of a different way to obtain the result you want...


Set

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Thu, Apr 7, 2016 at 3:41 PM, Ann Harrison 
wrote:

> On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborbo...@yahoo.com
> [firebird-support]  wrote:
>
>> 2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl
>> [firebird-support] írta:
>>
>
>
Just in case you think Firebird should be able to ignore parts of an outer
join,
let me disillusion you.


>  select d.DepartmentName from Departments d
> *left outer* join Employees e on e.DeptID = d.DeptID
>
> Suppose you have 10 Departments and 10 Employees, but all the
> Employees have the same DeptID.  Then you should get the same
> DepartmentName ten times.
>

The left outer join will return the nine unmatched Departments, returning
one DepartmentName for each unmatched Department.  The name of the
Department with 10 Employees will be returned 10 time.

Good luck,

Ann

>
>
>
>
>

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Thu, Apr 7, 2016 at 2:27 PM, Gabor Boros gaborbo...@yahoo.com
[firebird-support]  wrote:

> 2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl
> [firebird-support] írta:
> >
> >
> > For a join (inner join), the rows must exist in both tables, so both
> > tables need to be evaluated.
> >
>
> I understand this. Try describe my problem with other words.
> Is the VIEW an atomic (or compiled like a STORED PROCEDURE) thing?
>

For views, only the view definition is stored.  When a query references a
view,
the view is compiled and optimized like any other query.  If possible, it
is
integrated into the larger query.


> I have a VIEW with 4 fields (FIELD1..4), 1 come from the "FROM table"
> (FIELD1) and 3 from JOINs (FIELD2..4). If execute a SELECT FIELD1 FROM
> ... PLAN contains the JOINs. Why? If I write a SELECT manually not
> include JOINs if not needed for the resulted fields, because want
> maximum performance.
>
>
As Mark and others explained, both sides of a join must always be
evaluated, even if you reference only fields from one table.  Consider
this case:

 select d.DepartmentName from Departments d
join Employees e on e.DeptID = d.DeptID

Suppose there are no Employees at all, but 10 Departments.  That
query should return no results because what was asked was to return
the DepartmentName of Departments with Employees.

Suppose you again have 10 Departments, but only 1 Employee and
that Employee has a DeptID that matches one of the Departments.
Then the query should return one DepartmentName.

Suppose you have 10 Departments and 10 Employees, but all the
Employees have the same DeptID.  Then you should get the same
DepartmentName ten times.

You're thinking of a case where there is a (possibly unstated) referential
relationship between the table that you're getting fields from and the other
table in the view.  The table you're getting data from is the referencing
table and the other is the referenced table.  In that particular case, the
join doesn't matter, but there's no way that Firebird can know that.

Good luck,


Ann

Re: [firebird-support] FB on LAN

2016-04-07 Thread Hugo Eyng hugoe...@msn.com [firebird-support] 

Hello Mark.

I was like you showed 192.168.0.158:C:\DIR1\BASEDIR\MYDATABASE.FDB

Than I changed from FB 32 to FB 64 and it stopped working. After that,
after that even turnning to FB 32 it doesn´t work using this structure. 
I only works
using \\192.168.0.158\C:\DIR1\BASEDIR\MYDATABASE.FDB or 
C:\DIR1\BASEDIR\MYDATABASE.FDB


I open the 3050 port in FW. But it keep not connecting.

Hugo


Em 07/04/2016 11:45, Mark Rotteveel m...@lawinegevaar.nl 
[firebird-support] escreveu:


On 2016-04-07 14:41, Hugo Eyng hugoe...@msn.com [firebird-support]
wrote:
> Hello.
>
> Thanks for answer my question.
>
> I changed the connection (Deplhi XE10 / DBX) to
> \\192.168.0.158\C:\DIR1\BASEDIR\MYDATABASE.FDB and it works.

With this change you are using a different protocol than you were
using earlier. With this change you changed from the TCP/IP protocol to
the WNET protocol, to continue to use the TCP/IP protocol, you should
switch to:

192.168.0.158:C:\DIR1\BASEDIR\MYDATABASE.FDB

Mark




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] test

2016-04-07 Thread Hugo Eyng hugoe...@msn.com [firebird-support] 

Checked  :)


Em 07/04/2016 13:33, Z T Minhas ztmin...@yahoo.com [firebird-support] 
escreveu:


Just checking




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] help to repair a corrupted database

2016-04-07 Thread Hugo Eyng hugoe...@msn.com [firebird-support] 

Hi.

Are you able to backup and restore the database instead of "gfixing"?


Em 07/04/2016 12:28, korkl...@yahoo.it [firebird-support] escreveu:


hi, i'm trying to repair a corrupted db for firebird 2.5.2,  the 
database file is of about 30 gb.


i have disconnected all the user, i did a copy of the file and then i 
executed the command
gfix -validate 'dbname' with the credentials of the user that created 
the db (not the sysdba)


now the gfix process is 90 minutes that is on 0 cpu usage and 1 mb of 
ram but the firebird process sometime works,


is the gfix process freezed or it's normal? can i terminate it and 
retry or maybe this can add other problems to my database file?


thanks




--


Atenciosamente,

Hugo Eyng

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2016. 04. 07. 19:31 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
>
>
> For a join (inner join), the rows must exist in both tables, so both
> tables need to be evaluated.
>
> Mark

I understand this. Try describe my problem with other words.
Is the VIEW an atomic (or compiled like a STORED PROCEDURE) thing?
I have a VIEW with 4 fields (FIELD1..4), 1 come from the "FROM table" 
(FIELD1) and 3 from JOINs (FIELD2..4). If execute a SELECT FIELD1 FROM 
... PLAN contains the JOINs. Why? If I write a SELECT manually not 
include JOINs if not needed for the resulted fields, because want 
maximum performance.

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] Firebird V3RC2 Primary Key question

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
This is the default and expected behavior. If you want case insensitive, then 
you need to create the primary key column with a case insensitive collation.
Mark

- Bericht beantwoorden -
Van: "Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] Firebird V3RC2 Primary Key question
Datum: do, apr. 7, 2016 19:46

Hi, It’s been a while since I last used Firebird. I am testing new release 
candidate at the moment. Just found something I am not so sure. I have a 
database created using “CharSet WIN1254” parameter. Below is the SQL I used to 
create a table in that database:CREATE TABLE KULLANICI(  KODU VARCHAR(30)  NOT 
NULL PRIMARY KEY,  ADI VARCHAR(50),  SIFRE VARCHAR(64),  EMAIL VARCHAR(60),); 
And, all below executes without error:INSERT INTO KULLANICI(KODU) 
VALUES(‘EK’);INSERT INTO KULLANICI(KODU) VALUES(‘ek’);INSERT INTO 
KULLANICI(KODU) VALUES(‘Ek’);INSERT INTO KULLANICI(KODU) VALUES(‘eK’); I can 
insert “EK”, “ek”, “Ek”, and “eK” as new records in the KODU column of that 
table. Somehow primary key of that table is case-sensitive. I couldn’t find 
anything about primary key defaults to case-sensitive in the 
“Firebird-3.0.0_RC2-ReleaseNotes.pdf”. I don’t remember if that was the case in 
earlier versions, too. Just want to be sure about it. Can anybody confirm if 
that is a feature? On my part, I would like to avoid it if possible. Thanks. 
Regards,Ertan Küçükoğlu

[firebird-support] Firebird V3RC2 Primary Key question

2016-04-07 Thread Ertan Küçükoğlu ertan.kucuko...@1nar.com.tr [firebird-support] 
Hi,

 

It's been a while since I last used Firebird. I am testing new release
candidate at the moment. Just found something I am not so sure.

 

I have a database created using "CharSet WIN1254" parameter.

 

Below is the SQL I used to create a table in that database:

CREATE TABLE KULLANICI

(

  KODU VARCHAR(30)  NOT NULL PRIMARY KEY,

  ADI VARCHAR(50),

  SIFRE VARCHAR(64),

  EMAIL VARCHAR(60),

);

 

And, all below executes without error:

INSERT INTO KULLANICI(KODU) VALUES('EK');

INSERT INTO KULLANICI(KODU) VALUES('ek');

INSERT INTO KULLANICI(KODU) VALUES('Ek');

INSERT INTO KULLANICI(KODU) VALUES('eK');

 

I can insert "EK", "ek", "Ek", and "eK" as new records in the KODU column of
that table. Somehow primary key of that table is case-sensitive.

 

I couldn't find anything about primary key defaults to case-sensitive in the
"Firebird-3.0.0_RC2-ReleaseNotes.pdf". I don't remember if that was the case
in earlier versions, too. Just want to be sure about it. Can anybody confirm
if that is a feature?

 

On my part, I would like to avoid it if possible.

Thanks.

 

Regards,

Ertan Küçükoğlu

Re: [firebird-support] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
Also consider the situation with multiple rows on the other side of the join: 
in that case the one side repeats as many times as there are rows

- Bericht beantwoorden -
Van: "'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:31

For a join (inner join), the rows must exist in both tables, so both tables 
need to be evaluated.
Mark



- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:06

2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

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

Re: [firebird-support] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
For a join (inner join), the rows must exist in both tables, so both tables 
need to be evaluated.
Mark

- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 19:06

2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

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



https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
2016. 04. 07. 18:48 keltezéssel, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] írta:
> Why wouldn't it be necessary, this is an inner join, so the joined table
> needs to be evaluated if the matches row is present.
>

> SELECT RDB$FIELD_NAME FROM VIEW1

SELECT RDB$FIELD_NAME FROM RDB$RELATION_FIELDS

For me the two SELECT is equal. Is not? I don't understand why the JOIN 
is in the PLAN when not needed for the result. My original problem is...
I have a complex SELECT and build up it at runtime in my application 
include only the needed fields, JOINs in it. If create a VIEW from the 
whole SELECT all JOIN etc executed even if only a single field needed 
which can available without any JOIN or other thing.

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] VIEW optimization

2016-04-07 Thread 'Mark Rotteveel' m...@lawinegevaar.nl [firebird-support] 
Why wouldn't it be necessary, this is an inner join, so the joined table needs 
to be evaluated if the matches row is present.
Mark

- Bericht beantwoorden -
Van: "Gabor Boros gaborbo...@yahoo.com [firebird-support]" 

Aan: 
Onderwerp: [firebird-support] VIEW optimization
Datum: do, apr. 7, 2016 18:34

Hi All,

VIEW:
CREATE VIEW VIEW1 AS
SELECT RDB$RELATION_FIELDS.RDB$RELATION_NAME, RDB$FIELD_NAME FROM 
RDB$RELATION_FIELDS
JOIN RDB$RELATIONS ON
RDB$RELATION_FIELDS.RDB$RELATION_NAME=RDB$RELATIONS.RDB$RELATION_NAME;

SELECT:
SELECT RDB$FIELD_NAME FROM VIEW1

PLAN:
PLAN JOIN (VIEW1 RDB$RELATION_FIELDS NATURAL, VIEW1 RDB$RELATIONS INDEX 
(RDB$INDEX_0))


Plan contains the JOIN but that not needed for the selected field. Is 
this an SQL standard thing or just Firebird's optimizer not smart enough?

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



https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] help to repair a corrupted database

2016-04-07 Thread Alexey Kovyazin a...@ib-aid.com [firebird-support] 

Hi,

Gfix visits each page of the database, so 90 minutes is not so long if 
your disk subsystem is not based SSD.


In general, follow these steps
http://ib-aid.com/en/articles/how-to-repair-a-corrupt-firebird-database/

Check firebird.log too - gfix reports there its findings.

Regards,
Alexey Kovyazin
IBSurgeon



hi, i'm trying to repair a corrupted db for firebird 2.5.2,  the 
database file is of about 30 gb.


i have disconnected all the user, i did a copy of the file and then i 
executed the command
gfix -validate 'dbname' with the credentials of the user that created 
the db (not the sysdba)


now the gfix process is 90 minutes that is on 0 cpu usage and 1 mb of 
ram but the firebird process sometime works,


is the gfix process freezed or it's normal? can i terminate it and 
retry or maybe this can add other problems to my database file?


thanks

Re: [firebird-support] VIEW optimization

2016-04-07 Thread Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] 
07.04.2016 18:34, Gabor Boros gaborbo...@yahoo.com [firebird-support] wrote:
> Plan contains the JOIN but that not needed for the selected field.

   But it is needed to find out how many records with this value you'll get.

-- 
   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] VIEW optimization

2016-04-07 Thread Gabor Boros gaborbo...@yahoo.com [firebird-support] 
Hi All,

VIEW:
CREATE VIEW VIEW1 AS
SELECT RDB$RELATION_FIELDS.RDB$RELATION_NAME, RDB$FIELD_NAME FROM 
RDB$RELATION_FIELDS
JOIN RDB$RELATIONS ON
RDB$RELATION_FIELDS.RDB$RELATION_NAME=RDB$RELATIONS.RDB$RELATION_NAME;

SELECT:
SELECT RDB$FIELD_NAME FROM VIEW1

PLAN:
PLAN JOIN (VIEW1 RDB$RELATION_FIELDS NATURAL, VIEW1 RDB$RELATIONS INDEX 
(RDB$INDEX_0))


Plan contains the JOIN but that not needed for the selected field. Is 
this an SQL standard thing or just Firebird's optimizer not smart enough?

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] test

2016-04-07 Thread Z T Minhas ztmin...@yahoo.com [firebird-support] 
Just checking 

Re: [firebird-support] help to repair a corrupted database

2016-04-07 Thread Ann Harrison aharri...@ibphoenix.com [firebird-support] 
On Thu, Apr 7, 2016 at 11:28 AM, korkl...@yahoo.it [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> hi, i'm trying to repair a corrupted db for firebird 2.5.2,  the database
> file is of about 30 gb.
>
> i have disconnected all the user, i did a copy of the file and then i
> executed the command
> gfix -validate 'dbname' with the credentials of the user that created the
> db (not the sysdba)
>
> now the gfix process is 90 minutes that is on 0 cpu usage and 1 mb of ram
> but the firebird process sometime works,
>
> is the gfix process freezed or it's normal?
>

Probably normal.  Gfix itself doesn't do much, it just connects to the
database with options
that cause Firebird to validate the database.



> can i terminate it and retry or maybe this can add other problems to my
> database file?
>

I wouldn't terminate it for another couple of hours.  It's unlikely that
stopping gfix will
have adverse effects on the database, though stopping the Firebird server
could.

What are the symptoms of the corruption?

Good luck,

Ann

[firebird-support] help to repair a corrupted database

2016-04-07 Thread korkl...@yahoo.it [firebird-support] 
hi, i'm trying to repair a corrupted db for firebird 2.5.2,  the database file 
is of about 30 gb.

i have disconnected all the user, i did a copy of the file and then i executed 
the command
gfix -validate 'dbname' with the credentials of the user that created the db 
(not the sysdba)

now the gfix process is 90 minutes that is on 0 cpu usage and 1 mb of ram but 
the firebird process sometime works,

is the gfix process freezed or it's normal? can i terminate it and retry or 
maybe this can add other problems to my database file?

thanks

Re: [firebird-support] FB on LAN

2016-04-07 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-04-07 14:41, Hugo Eyng hugoe...@msn.com [firebird-support] 
wrote:
> Hello.
>
>  Thanks for answer my question.
>
>  I changed the connection (Deplhi XE10 / DBX) to
> \\192.168.0.158\C:\DIR1\BASEDIR\MYDATABASE.FDB and it works.

With this change you are  using a different protocol than you were 
using earlier. With this change you changed from the TCP/IP protocol to 
the WNET protocol, to continue to use the TCP/IP protocol, you should 
switch to:

192.168.0.158:C:\DIR1\BASEDIR\MYDATABASE.FDB

Mark

[firebird-support] Re: FB on LAN

2016-04-07 Thread chmere...@gmail.com [firebird-support] 
If you access a database from a mapped drive on a share, please read:

Can I access a database from a mapped drive on a share? 
http://www.firebirdfaq.org/faq46/

Greetings.
 
 
 Can I access a database from a mapped drive on a share? 
http://www.firebirdfaq.org/faq46/ Collection of answers to questions about 
Firebird mapped, network, share, server, drive, access, database, cd, dvd, lan, 
client.
 
 
 
 View on www.firebirdfaq.org http://www.firebirdfaq.org/faq46/ 
 Preview by Yahoo

Re: [firebird-support] FB on LAN

2016-04-07 Thread Hugo Eyng hugoe...@msn.com [firebird-support] 

Hello.

Thanks for answer my question.

I changed the connection (Deplhi XE10 / DBX) to 
\\192.168.0.158\C:\DIR1\BASEDIR\MYDATABASE.FDB and it works.




Em 06/04/2016 18:21, 'Mark Rotteveel' m...@lawinegevaar.nl 
[firebird-support] escreveu:
Have you checked the difference between the two Firebird.conf? You 
seem to be using relative paths to connect the database; I believe 
that depends on the RootDirectory setting.


Mark

- Bericht beantwoorden -
Van: "Hugo Eyng hugoe...@msn.com [firebird-support]" 


Aan: 
Onderwerp: [firebird-support] FB on LAN
Datum: wo, apr. 6, 2016 22:59

Hello.

FB 2.5.5 32 bits SuperClassic installed on windows server 2008 Standard
64 bits

Database: 192.168.0.158:\DIR1\BASEDIR\MYDATABASE.FDB working fine

Then I changed to FB 2.5.5 64 bits SuperServer in the same server

Database: 192.168.0.158:\DIR1\BASEDIR\MYDATABASE.FDB   My connection to
database stopped working.

but  Database: C:\DIR1\BASEDIR\MYDATABASE.FDB turned working


Any hint?

--


Atenciosamente,

Hugo Eyng







++

Visithttp://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 athttp://www.ibphoenix.com/resources/documents/  


++


Yahoo Groups Links






--


Atenciosamente,

Hugo Eyng

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

2016-04-07 Thread 'stwizard' stwiz...@att.net [firebird-support] 
Arno,

 

Thanks for your pointing out the issue with my SQL statement.

 

Mike

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Wednesday, April 06, 2016 4:36 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this 
rather simply SQL?

 

  

Hi,

 

The message doesn’t tell you which expression is not contained in an aggregate 
function or GROUP BY clause, but is very clear that it is “P.PHONE_ID”

 

Not sure what you trying to achieve, but your SQL is indeed invalid, because 
there can be multiple PHONE_ID’s per AREA_CODE, PHONE_NO

 

Kind Regards,
Arno Brinkman

 

 

 

From: mailto:firebird-support@yahoogroups.com 

Sent: Wednesday, April 6, 2016 10:03 PM

To: firebird-support@yahoogroups.com 

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

 






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  

HAVING (SELECT COUNT(*) 

  FROM PHONE P3 

 WHERE P3.PHONE_ID = P.PHONE_ID

   AND P3.AREA_CODE IS NOT NULL) > 1

 

Dynamic SQL Error SQL error code = -104 Invalid expression in the select list 
(not contained in either an aggregate function or the GROUP BY clause)





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

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

2016-04-07 Thread 'stwizard' stwiz...@att.net [firebird-support] 
Tomasz,

Thanks for your guidance and explanation for a much simpler solution.

Mike

 

From: firebird-support@yahoogroups.com 
[mailto:firebird-support@yahoogroups.com] 
Sent: Thursday, April 07, 2016 2:58 AM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] SQL Error Code -104: What is wrong with this 
rather simply SQL?

 

  

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





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

Re: [firebird-support] Timestamp conversion issue

2016-04-07 Thread Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
On 2016-04-07 5:26, Alex Castillo acr_k...@yahoo.com [firebird-support] 
wrote:
> I've no doubt this is a bug for the linux release. It should be
> filtering the string literals before doing the conversion as it does
> on Windows.

I don't think it is a bug. Firebird supports the SQL standard 
ISO-8601(-ish) format for date and time, and in addition it supports a 
number of conversions that are supported by the platform (and may depend 
on regional settings). This means that except for the format mandated by 
the SQL standard, you shouldn't expect anything else (and everything 
else that does work is nice to have).

The format in your initial question BTW as "10:00:00 a.m.  ", even on 
Windows I suspect this will fail if your locale is set to a 24 hour 
clock (but I haven't tried).

> The way I've solved was by creating a global temporary table and
> copying the values from a view, converting the varchar to time. So
> now, when I perform DATA + TIME = TIMESTAMP in a right way. This is
> not optimal, but it does work meantime the issue can be evaluated by
> the firebird team.

Did you create a ticket in the tracker? Otherwise nothing is going to 
happen.

Mark

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

2016-04-07 Thread 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

Re: [firebird-support] Re: First-In-First-Out (FIFO) stock valuation

2016-04-07 Thread venussof...@gmail.com [firebird-support] 
I have confirmed that the part
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)

If I remove the SUM() I get an error that there are multiple records returned 
in a singleton select.  There is an iID field on each row of each table (I have 
tried to use it to no avail) that can distinguish both these rows being 
returned but I am not able to figure out how.

Please advise

Kind regards
Bhavbhuti

Re: [firebird-support] Re: First-In-First-Out (FIFO) stock valuation

2016-04-07 Thread venussof...@gmail.com [firebird-support] 
Hi all

Sorry for resurrecting this very old thread but I have come across a situation 
(hopefully Jose would be able to answer).  I have found a bug in the bQty 
column it was not able to reduce itself to the current stock, eg. 3, and still 
it returns two row with a qty of 3+2 = 5 qty is valued which is wrong

SELECT qty, bRate, (qty * bRate) AS bValuation
FROM (SELECT IIF(bQty + qtyOut >= 3 /*current stock */
, (3 /*current stock */ - qtyOut)
, bQty) as qty
, bRate
FROM (SELECT sPurchaseBillItem.bQty
, sPurchaseBillItem.bRate
, COALESCE((SELECT SUM(PurchaseBillItemInner.bQty)
FROM sPurchaseBillItem AS
PurchaseBillItemInner
JOIN tPurchaseBill AS
PurchaseBillInner
ON
PurchaseBillInner.iID = PurchaseBillItemInner.iPID
WHERE (PurchaseBillInner.tDt >
tPurchaseBill.tDt AND PurchaseBillItemInner.iItemID = 342 /* item id */))
, 0) as qtyOut
FROM tPurchaseBill
JOIN sPurchaseBillItem
ON sPurchaseBillItem.iPID = tPurchaseBill.iID
AND sPurchaseBillItem.iItemID = 342 /*item id */) AS PurchaseBillsIntermediate
WHERE (qtyOut <= 3 /*current stock */)) AS PurchaseBillsFIFO

returns
3.0067.19201.57
2.0067.19134.38

the expected would have been
1.0067.1967.19
2.0067.19134.38

the original bqty in the top row is 11 but now only 1 of it remains, yet the 
above.  The only reason this might be happening is that both the rows belongs 
to the same tPurchaseBill parent record, because the same purchase bill had 
stock coming in from different medicine batches, thus the user needed to enter 
the same item in the same purchase bill twice but with different qty and batch 
nos.

Please advise.

Thanks and regards
Bhavbhuti