Re: [firebird-support] Re: Mailing list change?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

Personally, I like mailing groups.
The speed of getting answers is much greater than on regular web forums.
At the beginning I was skeptical but I have been using this form for many years 
and I see more pros than cons of this form of communication.

And as someone rightly stated, one can consider a change if the current form 
will work worse and worse.

But that's just my humble opinion.

Regards,
Karol Bieniaszewski

Re: [firebird-support] LOCALTIME and LOCALTIMESTAMP

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

But how timezone settup is configured in FB4?
Time zone is set in fbconfig (preffered) or is retrived from system?
If from config, then CURRENT_DATE and LOCAL_TIMESTAMP will get different date 
when different timezone setting is in config and on the server.

regards,
Karol Bieniaszewski

From: Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
Sent: Tuesday, July 17, 2018 5:38 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] LOCALTIME and LOCALTIMESTAMP

  
On 2018-07-17 16:43, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
>>> ANSI standard compliance is the answer for all questions
> 
> What about last question LOCAL_DATE missed?

The SQL standard only knows a single DATE type (there is no DATE WITH 
TIME ZONE type), so there is no need to distinguish between CURRENT_DATE 
and LOCALDATE.

Mark

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

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

is this your real query? I ask, because  below WHERE can be not intentionaly 
written by OR

WHERE ( 
heir.iaccttreeid = 47 
  AND   cast(tsi.tdt AS date) < '2018-04-01' ) 
OR( 
heir.iaccttreeid <> 47 
  AND   cast(tsi.tdt AS date) BETWEEN '2018-03-01' AND   
'2018-03-31' ) 
AND   tsi.cdoctype NOT LIKE 'PROFORMA%' 
AND   tsi.cdoctype NOT LIKE 'CANCELLED%' 
AND   ssifooter.cdrcr IN ('DR', 
  'CR') 

i suppose you need more parenthesis:

WHERE 
(
( 
heir.iaccttreeid = 47 
AND
cast(tsi.tdt AS date) < '2018-04-01' 
) 
OR
(
heir.iaccttreeid <> 47 
AND   
cast(tsi.tdt AS date) BETWEEN '2018-03-01' AND   '2018-03-31'
)
)
AND   tsi.cdoctype NOT LIKE 'PROFORMA%' 
AND   tsi.cdoctype NOT LIKE 'CANCELLED%' 
AND   ssifooter.cdrcr IN ('DR', 
  'CR') 

 
regards,
Karol Bieniaszewski

Re: [firebird-support] stored procedured with insert ... returning

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

returning ... INTO ..

regards,
Karol BIeniaszewski

From: talorigo...@yahoo.co.uk [firebird-support] 
Sent: Sunday, June 10, 2018 1:14 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] stored procedured with insert ... returning

  

When I try to create the following stored procedure I get sql error code = -206 
Column unknown EMPID




SET TERM ! ;
CREATE OR ALTER PROCEDURE TEST (
  ATITLE DOM_VARCHARTINY, 
  AFIRST_NAME DOM_VARCHARMEDIUM, 
  AINITIALS DOM_VARCHARTINY, 
  ASURNAME DOM_VARCHARMEDIUM, 
  AEMAIL DOM_VARCHARLARGE, 
  APHONE DOM_VARCHARTINY)
AS
begin
  INSERT INTO Employee (title, first_name, initials, surname,
 email, phone_ext)
  VALUES(:ATitle, :AFirst_Name, :AInitials, :ASurname, :AEmail, :APhone)
  RETURNING :EMPID;  
end !
SET TERM ; !





The table does have a EMPID column defined and if I type the insert statement 
and execute it, it does work.  

Re: [firebird-support] WITH RECURSIVE or not WITH RECURSIVE

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

better is walk throught tree

--

WITH RECURSIVE
R_TREE AS
(
SELECT TT.ID, TT.ID AS TOP_PARENT
FROM TABLE1 TT
WHERE TT.PARENT_ID IS NULL

UNION ALL

SELECT TT.ID, RT.TOP_PARENT
FROM TABLE1 TT JOIN R_TREE RT ON RT.ID = TT.PARENT_ID
)
SELECT
RT2.ID, CASE WHEN RT2.ID=RT2.TOP_PARENT THEN NULL ELSE RT2.TOP_PARENT END AS 
TOP_PARENT
FROM
R_TREE RT2

--
the result plan show obvious difference

above query
PLAN (R_TREE TT NATURAL, R_TREE TT NATURAL)
instead
PLAN (R NATURAL, HASH (TREE ROOT NATURAL, TREE T NATURAL), TREE M NATURAL)

--
and with indexes
--

above query
PLAN (R_TREE TT INDEX (IXA_TABLE1__PARENT_ID), R_TREE TT INDEX 
(IXA_TABLE1__PARENT_ID))

instead previus
PLAN (R INDEX (IXA_TABLE1__PARENT_ID), JOIN (TREE ROOT INDEX 
(IXA_TABLE1__PARENT_ID), TREE T INDEX (IXA_TABLE1__PARENT_ID)), TREE M INDEX 
(IXA_TABLE1__PARENT_ID))

Regards,
Karol Bieniaszewski 







++

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] FB3 external table

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>As far as I can tell: no. I suggest you create a ticket to introduce 
>>something like
>>ALTER TABLE  ALTER EXTERNAL [FILE] 

>>Mark
>>-- 
>>Mark Rotteveel
Thank you
created http://tracker.firebirdsql.org/browse/CORE-5834
regards,
Karol Bieniaszewski

[firebird-support] FB3 external table

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

previously it was possible to change external table filename

UPDATE
RDB$RELATIONS r
SET r.RDB$EXTERNAL_FILE='c.txt'
WHERE
r.RDB$EXTERNAL_FILE='b.txt'

in FB3 it is prohibited. 
Is there some DDL statment to alter such table only file name?

regards,
Karol Bieniaszewski

Re: [firebird-support] Upgrade from superclassic to superserver

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

few things:
1. instead FB3.0.3 use recent snapshot. It contain many fixes.

2. look at settings about buffers in your DB itself
if it utilize config then increase DefaultDbCachePages. 
Currently you set it to only to 8GB – but db size is much bigger. Is this 
server to serve only this database or it is also for other things?
If it is only for this database then increase value of DefaultDbCachePages.
If you do only backup on this server without test restore then you can utilize 
much more RAM.
But i say from Windows perspective not Centos...

3. TempBlockSize you set to only 2MB which is rather small. 
I know that you say about 3000 concurrent web users but with this value you 
will go with sort to the disc quite offten.
You must look if your queries are optimized 
and they do not use disk sorting.

You must balance this - use bigger DefaultDbCachePages or bigger sorting cache.

regards,
Karol Bieniaszewski

From: kragh.tho...@yahoo.com [firebird-support] 
Sent: Tuesday, May 22, 2018 10:58 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Upgrade from superclassic to superserver

  

Hey

I am in the process of upgrading our Firebird database from 2.5.7 to 3.0.3. 
While making the upgrade, I want to switch from SuperClassic to SuperServer in 
order to gain better scalability. However configuring SuperServer i new to me. 

Background information:

The database server is a 16 core virtual machine with 128Gb of RAM, and ssd 
SAN. The database is about 100Gb and growing by 20Gb/year. Daily load is about 
3000 concurrent web users. The server is running CentOs 7



Here is the configurations that i have come up with so far. Pagesize is 16K

Firebird config:

DefaultDbCachePages = 524288(8Gb) 


On the old server this was 1024. On the new server this value is the one that I 
am most concerned about, i hope that someone can guide me. I have seen values 
of up to 2M(32Gb) in other guides, and I tempted to do the same. 



FileSystemCacheThreshold = 1048576


The old server used defa ult for 2.5.x



TempCacheLimit = 4294967296


Same as the old server



TempBlockSize = 2097152


Same as the old server



LockMemSize = 9437184

Same as the old server




LockHashSlots = 30011

Same as the old server







Regarding Centos:


Number of open files has been configured to 64K



Reading this Guide: 
https://firebirdsql.org/file/community/tour-2017/firebird.tuning.2017.english.pdf
 vm.pagecache should be limited to 40-50% However it looks like vm.pagecache is 
not availabel in CentOs, should  vfs_cache_pressure be used instead? 




I hope that someone can point me in the right direction or give some feedback.

Re: [firebird-support] Choosing best server / processor for Firebird 3.03 64 bits database on Windows server 2016 64 bits

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

as always RAM is crucial for DB, but with such size of DB you can only benefit 
“a little”
I do not know the reall usage scenario – what data is used and how many data 
are “analyzed” by normal user.
Does database contain historical data not comonly accessed or user “scan” whole 
db every day?
You must check how the usage looks like – how many phisical reads you got every 
day.
If you see very big number with big RAM then your only way to speed things up 
is storage.

I have worked with 680 GB FB with 256 GB RAM and for me RAM was crucial, cache 
do the job.
But in my scenario there was “historical data” accessed only occasionally (once 
per day).

regards,
Karol Bieniaszewski

From: fabia...@itbizolutions.com.au [firebird-support] 
Sent: Sunday, May 20, 2018 7:08 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Choosing best server / processor for Firebird 
3.03 64 bits database on Windows server 2016 64 bits

  
Thank you Mark!!

So the key here would be RAM and SSD disks correct?

Cheers,
Fabian

Re: [firebird-support] PASCAL UDR - stored procedure BLOB param and return

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Anyone can point me in some direction?

regards,
Karol Bieniaszewski

From: 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Monday, May 21, 2018 9:35 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] PASCAL UDR - stored procedure BLOB param and return

  

Hi,

i can work in udr with near to all types of params but
how can i work with blob as input parameter  and output parameter 
for stored procedure?

should this be as BLOB or something else? 

InMessage = record
nr: Integer;
nrNull: WordBool;
dane: Blob;
daneNull: WordBool;
end;

InMessagePtr = ^InMessage;

OutMessage = record
result: Integer;
resultNull: WordBool;
dane: Blob;
daneNull: WordBool;
end;

OutMessagePtr = ^OutMessage;

how to consume this?

please advice

regards,
Karol Bieniaszewski

[firebird-support] PASCAL UDR - stored procedure BLOB param and return

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

i can work in udr with near to all types of params but
how can i work with blob as input parameter  and output parameter 
for stored procedure?

should this be as BLOB or something else? 

InMessage = record
nr: Integer;
nrNull: WordBool;
dane: Blob;
daneNull: WordBool;
end;

InMessagePtr = ^InMessage;

OutMessage = record
result: Integer;
resultNull: WordBool;
dane: Blob;
daneNull: WordBool;
end;

OutMessagePtr = ^OutMessage;

how to consume this?

please advice

regards,
Karol Bieniaszewski

Re: [firebird-support] Firebird ADO.NET: ExecuteNonQuery Returns -1 On Successful Insert

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>That being s aid, I produced a technical paper a few days ago on my own 
>>technical article site that I announced on this forum, which you may have 
>>read.  This article was designed with the intention of attracting other .NET 
>>development professionals >>who have similar backgrounds to mine by 
>>describing the inconsistencies they will find with Firebird when working with 
>>a db-manager as well as ADO.NET.  And such professionals with my type of 
>>background will see a number of issues with Firebird that >>will not be seen 
>>as expected database behavior.  I  hope that my paper will assist such 
>>professionals in understanding Firebird by demonstrating these 
>>inconsistencies while also providing them with the appropriate solutions to 
>>work with them in a >>manner that will make them more comfortable with this 
>>database engine.
I have read onece again your article and now i know why you thinked 
“Views could be considered somewhat redundant is something “
Your sample about procedure presented make me thinking that you do not catch 
full suspend statment maining (this is only assumption).
The biggest difference between firebird stored proc and other databases is 
simplicity to return e.g virtual fieldsand manipulate its content
e.g. look at your proc modified by me:
CREATE PROCEDURE SP_WORK_SIMPLE_SELECT
RETURNS(
  NUMBER INTEGER,
  PI_KEY BIGINT,
  PI_PARENT_KEY BIGINT,
  PI_ROOT_KEY BIGINT,
  PI_DEPTH INTEGER,
  PS_CATEGORY VARCHAR(500))
AS
BEGIN
   NUMBER = 1
   FOR
  SELECT CN_KEY,
   CN_PARENT_KEY,
   CN_ROOT_KEY,
   CN_DEPTH,
   CN_CATEGORY
FROM RI_CATEGORY_NODES
INTO :PI_KEY,
 :PI_PARENT_KEY,
 :PI_ROOT_KEY,
 :PI_DEPTH,
 :PS_CATEGORY
DO
  BEGIN
 SUSPEND;
 NUMBER = NUMBER + 1;
 SUSPEND;
 NUMBER = NUMBER * 5;
 SUSPEND;
 PS_CATEGORY = PS_CATEGORY || NUMBER;
 SUSPEND;
  END
SUSPEND;
SUSPEND;
SUSPEND;
END;

this sample show real powerfull of of SP in Firebird. I know that there are 
also wiknes but not so big.

regards,
Karol Bieniaszewski

Re: [firebird-support] FB3 alter domain colltion

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>I guess the reason is that user may have existing indexes on fields using the 
>>domain. Changing the collate would need to have the indexes rebuilt, 
>>otherwise they would be "corrupted". So, to avoid that, FB >>doesn't allow 
>>such changes.

Yes, that can be the reason but i think something like this 
will be also not possible - if you change datatype of domain not only collation 
– i think.
But this should be possible by introduce command like 
ALTER DOMAIN ALTER TYPE VARCHAR(150) COLLATION PXW_PLK REBUILD; <- here we add 
rebuild command.
But i think about BLR – is colltion compiled into blr of procedures and 
triggers?
If yes then also this objects should be rebuilded by “REBUILD” command.
Now doing something like this makes you have a headache 
regards,
Karol Bieniaszewski 

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

Re: [firebird-support] FB3 alter domain colltion

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

>>There is no way to change the default collation without dropping the domain 
>>and recreating it with the desired attributes. 
>>Perhaps tool like IBExpert could do this – alter and set collation? Not sure.

this was for FB2.5 where users could modify system tables but now in FB3 it is 
prohibited 
and i suppose it is fixed by some DDL statement?

regards,
Karol Bieniaszewski

Re: [firebird-support] Re: Udr GenRows sample Pascal

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Thank you botch Norbert and Dimitry for your help

problem was with fill method
the method is to use move index [1] of string and casting or StrLCopy
or simplest possible this one which does not require any casting:

StrPLCopy(outMessage.ss, s, High(outMessage.ss))

regards,
Karol Bieniaszewski


From: liviuslivius liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Tuesday, May 15, 2018 11:19 AM
To: firebird-support@yahoogroups.com 
Subject: Re: Re: [firebird-support] Re: Udr GenRows sample Pascal

  
> It depends on information in IMessageMetadata.
> 
> > I have tried:
> > ss: Array[0..199] of Char;
> > ss: Array[0..199] of Byte;
> > ss: Array[0..199] of AnsiChar;
> 
> This is a right declaration for CHAR(200).
> 

OK 

> > ss: String[200];
> > 
> > with last string[200] i got result prefixed with size byte
> 
> Because short strings in Delphi are prefixed with length byte.
> 
> -- 
> WBR, SD.
> 

Yes i know that i only "show" that with this declaration i got something 
but with all above i got empty string
I will look into fill procedure maybe i miss something obvious

regards,
Karol Bieniaszewski

Re: [firebird-support] Re: Udr GenRows sample Pascal

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what kind of buffer?
I have tried:
ss: Array[0..199] of Char;
ss: Array[0..199] of Byte;
ss: Array[0..199] of AnsiChar;
ss: String[200];

with last string[200] i got result prefixed with size byte

regards,
Karol Bieniaszewski


-Oryginalna wiadomość- 
From: Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]
Sent: Monday, May 14, 2018 11:06 PM
To: firebird-support@yahoogroups.com
Subject: Re: [firebird-support] Re: Udr GenRows sample Pascal

14.05.2018 22:18, 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
wrote:
> maybe this is wrong?
> GenIntOutMessage = record
> result: Integer;
> resultNull: WordBool;
> ss: PAnsiChar;
> ssNull: WordBool;
> end;


   Yes, this is wrong. Buffer must contain real data, not pointers to them.

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

Re: [firebird-support] Re: Udr GenRows sample Pascal

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

i am delphi developer but i never hear about CodeTyphon!
I hear about lazarus but not this one.

But i need to do this under Delphi (Win64).
I tried many things but i always got empty string.
Only integer work and null flag.
I tried GetMem, ib_util_malloc but maybe this is wrong?
GenIntOutMessage = record
result: Integer;
resultNull: WordBool;
ss: PAnsiChar;
ssNull: WordBool;
end;

below full code


{
* The contents of this file are subject to the Mozilla Public License 
Version
* 1.1 (the "License"); you may not use this file except in compliance with
* the License. You may obtain a copy of the License at
* http://www.mozilla.org/MPL/
*
* Software distributed under the License is distributed on an "AS IS" basis,
* WITHOUT WARRANTY OF ANY KIND, either express or implied. See the License
* for the specific language governing rights and limitations under the
* License.
*
* The Initial Developer of the Original Code is Adriano dos Santos 
Fernandes.
* Portions created by the Initial Developer are Copyright (C) 2015 the 
Initial Developer.
* All Rights Reserved.
*
* Contributor(s):
}

unit UdrGenInt;

interface

uses FbApi, System.Classes, System.SysUtils, System.Types;

{
recreate procedure gen_int_pascal (
nr Integer not null
) returns (
result Integer,
ss Char(5)
)
external name 'udr!gen_int'
engine udr;
}

type
GenIntInMessage = record
nr: Integer;
nrNull: WordBool;
end;

GenIntInMessagePtr = ^GenIntInMessage;

GenIntOutMessage = record
result: Integer;
resultNull: WordBool;
ss: PAnsiChar;
ssNull: WordBool;
end;

GenIntOutMessagePtr = ^GenIntOutMessage;

GenIntResultSet = class(ExternalResultSetImpl)
procedure dispose(); override;
function fetch(status: Status): Boolean; override;
public
currPoz: Integer;
inMessage: GenIntInMessagePtr;
outMessage: GenIntOutMessagePtr;
end;

GenIntProcedure = class(ExternalProcedureImpl)
procedure dispose(); override;

procedure getCharSet(status: Status; context: ExternalContext; name: 
PAnsiChar;
nameSize: Cardinal); override;

function open(status: Status; context: ExternalContext; inMsg: 
Pointer;
outMsg: Pointer): ExternalResultSet; override;
end;

GenIntFactory = class(UdrProcedureFactoryImpl)
procedure dispose(); override;

procedure setup(status: Status; context: ExternalContext; metadata: 
RoutineMetadata;
inBuilder: MetadataBuilder; outBuilder: MetadataBuilder); 
override;

function newItem(status: Status; context: ExternalContext;
metadata: RoutineMetadata): ExternalProcedure; override;
end;

implementation
uses System.StrUtils;

function ib_util_malloc(l: integer): pointer; cdecl; external 'ib_util.dll';

procedure GenIntResultSet.dispose();
begin
destroy;
end;

function GenIntResultSet.fetch(status: Status): Boolean;
Var s: String;
begin
if (currPoz > 5) then
Result := false
else
begin
outMessage.result:= currPoz;
outMessage.resultNull:= false;

s:= currPoz.ToString;
//GetMem(outMessage.ss, 3+1);
//outMessage.ss:= 'ABC' + #0;
outMessage.ss:= ib_util_malloc(3 + 1);
StrPCopy(outMessage.ss, 'ABC');

outMessage.ssNull:= false;

inc(currPoz);

Result := true;
end;
end;


procedure GenIntProcedure.dispose();
begin

destroy;
end;

procedure GenIntProcedure.getCharSet(status: Status; context: 
ExternalContext; name: PAnsiChar;
nameSize: Cardinal);
begin
end;

function GenIntProcedure.open(status: Status; context: ExternalContext; 
inMsg: Pointer;
outMsg: Pointer): ExternalResultSet;
var
Ret: GenIntResultSet;
begin
Ret:= GenIntResultSet.create();

Ret.inMessage := inMsg;
Ret.outMessage := outMsg;

  Ret.currPoz:= Ret.inMessage.nr;

Ret.outMessage.resultNull := false;
Ret.outMessage.result := 0;

Result := Ret;
end;


procedure GenIntFactory.dispose();
begin
destroy;
end;

procedure GenIntFactory.setup(status: Status; context: ExternalContext; 
metadata: RoutineMetadata;
inBuilder: MetadataBuilder; outBuilder: MetadataBuilder);
begin

end;

function GenIntFactory.newItem(status: Status; context: ExternalContext;
metadata: RoutineMetadata): ExternalProcedure;
begin
Result := GenIntProcedure.create;
end;


end.









++

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:

[firebird-support] Udr GenRows sample Pascal

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

is there more complicated sample then current GenRows for  Pascal?
Which return something more then only numbers?
I need to return char data of length 200.

Should i use PAnsiChar and allocate memory by ib_util_malloc?
How to declare this proc for such field Char(200) (Free it?) 

regards,
Karol Bieniaszewski

Re: [firebird-support] Article for experienced developers looking learn the Firebird Database

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

One more words:

about views – this is not true that this is redundant to SP.
Views are totally different objects. 
Views can be optimized by Firebird if you join with it with other tables or 
other views 
(with one exception if view contain order by).
Stored procedure work in the different way.
Also you can have trigers on views to make it editable or take different actions
look here:
https://firebirdsql.org/refdocs/langrefupd21-ddl-view.html

Also it is simple then to manage privileges.
Other difference – stored procedures are executed as LATERAL JOIN if you join 
table with procedure.

regards,
Karol Bieniaszewski



From: 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Friday, May 11, 2018 5:47 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Article for experienced developers looking 
learn the Firebird Database

  

Hi,

article is quite good but the title is something like someone should not try to 
read it..

regards,
Karol Bieniaszewski

From: blackfalconsoftw...@outlook.com [firebird-support] 
Sent: Friday, May 11, 2018 4:52 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Article for experienced developers looking learn 
the Firebird Database

  

For many years I have been tracking the development of the Firebird Database 
Engine and have watched it move from its 1.5 version to its most recent 
release, version 3.0.3.





On a number of occasions I have attempted to work with this database engine..  
And though I have many years of extensive database application development with 
quite a few database engines, Firebird always seem to allude my attempts at 
mastering it.  





>From major to minor "gotchas" from a lack of good introductory documentation 
>regarding the many differences between this database engine and its many 
>contemporaries, which more or less towed a standardized SQL line, even with 
>the many enhancements made to any one engine, Firebird frustrated my efforts 
>to the point on these occasions where I decided the efforts in learning it 
>were simply not justified .





Nonetheless, Firebird still beckoned as mastering it was an accomplishment I 
still wanted to achieve.  As a result, seeing the mostly negative aspects of 
using Microsoft's SQL Server LocalDB for a desktop application I have been 
developing, I decided to "bite the bullet" and somehow find a way to teach 
myself Firebird in order take advantage of its excellent embedded engine also 
known as the Firebird Embedded Server.





The result of this endeavor, which has been very successful so far, is a 
detailed article that attempts to make it much easier for experienced 
professionals to learn Firebird who also, like I, have many years of 
development experience with other database engines.  The article points out the 
many differences when using this engine for scripting within query and action 
screen s in a db-manager or creating database modules with Firebird PSQL that 
can drive even experienced professionals to the point of distraction.  
Hopefully, my experiences will help such professionals who want to master 
Firebird as well a much easier learning experience.





The article can be found at the following link for everyone's review...




https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/




Admittedly, there may be some mistakes in this piece since it was developed 
around my own learning experiences with this database engine. p; As a 
result, if anyone has any comments, corrections, or suggestions please let me 
know at my email address...  blackfalconsoftw...@outlook.com




In addition, if anyone would like a PDF copy of this piece, please let me know 
as well and I will prepare one and send it out...

Re: [firebird-support] Article for experienced developers looking learn the Firebird Database

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

article is quite good but the title is something like someone should not try to 
read it..

regards,
Karol Bieniaszewski

From: blackfalconsoftw...@outlook.com [firebird-support] 
Sent: Friday, May 11, 2018 4:52 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Article for experienced developers looking learn 
the Firebird Database

  

For many years I have been tracking the development of the Firebird Database 
Engine and have watched it move from its 1.5 version to its most recent 
release, version 3.0.3.





On a number of occasions I have attempted to work with this database engine..  
And though I have many years of extensive database application development with 
quite a few database engines, Firebird always seem to allude my attempts at 
mastering it.  





>From major to minor "gotchas" from a lack of good introductory documentation 
>regarding the many differences between this database engine and its many 
>contemporaries, which more or less towed a standardized SQL line, even with 
>the many enhancements made to any one engine, Firebird frustrated my efforts 
>to the point on these occasions where I decided the efforts in learning it 
>were simply not justified .





Nonetheless, Firebird still beckoned as mastering it was an accomplishment I 
still wanted to achieve.  As a result, seeing the mostly negative aspects of 
using Microsoft's SQL Server LocalDB for a desktop application I have been 
developing, I decided to "bite the bullet" and somehow find a way to teach 
myself Firebird in order take advantage of its excellent embedded engine also 
known as the Firebird Embedded Server.





The result of this endeavor, which has been very successful so far, is a 
detailed article that attempts to make it much easier for experienced 
professionals to learn Firebird who also, like I, have many years of 
development experience with other database engines.  The article points out the 
many differences when using this engine for scripting within query and action 
screen s in a db-manager or creating database modules with Firebird PSQL that 
can drive even experienced professionals to the point of distraction.  
Hopefully, my experiences will help such professionals who want to master 
Firebird as well a much easier learning experience.





The article can be found at the following link for everyone's review...




https://blackfalconsoftware.wordpress.com/2018/05/11/the-firebird-database-engine-the-frustrations-of-the-long-distance-database-application-developer/




Admittedly, there may be some mistakes in this piece since it was developed 
around my own learning experiences with this database engine. p; As a 
result, if anyone has any comments, corrections, or suggestions please let me 
know at my email address...  blackfalconsoftw...@outlook.com




In addition, if anyone would like a PDF copy of this piece, please let me know 
as well and I will prepare one and send it out...

Re: [firebird-support] Dynamic PSQL (Execute Statement) in Stored Procedure With "SELECT / INTO"

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

You misunderstand execute statement
execute statement exdeute sql from e.g. variable and it can return data to INTO
INTO is outside of select sql text

e.g. 
execute statement ‘select 1 from RDB$DATABASE’ INTO :MYPARAM
or
sql = ‘select 1 from RDB$DATABASE’;
execute statement(:sql) INTO :MYPARAM

look for samples in instalation directory

regards,
Karol Bieniaszewski

From: blackfalconsoftw...@outlook.com [firebird-support] 
Sent: Wednesday, April 25, 2018 10:33 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Dynamic PSQL (Execute Statement) in Stored 
Procedure With "SELECT / INTO"

  
Hello...

I have been doing fairly well at converting a project's database to Firebird 
Embedded, transposing all of the inline SQL to Firebird stored procedures..  
However, with the following procedure I am creating a slightly
complex SELECT statement based upon two incoming parameters.

It seems that no matter how I try to implement the "INTO" clause against the 
return variable, when I run the procedure from my DB-Manager it yields an 
error...

>>>
>>> procedure sql
>>>
CREATE PROCEDURE SP_GET_MSGLOG_REC_COUNT(
  PS_DATE VARCHAR(10),
  PS_MSG_TYPE CHAR(1))
RETURNS(
  PI_CNT INTEGER NOT NULL)
AS
DECLARE VARIABLE PS_SQL VARCHAR(1000) NOT NULL;
BEGIN
  PI_CNT = 0;

  PS_SQL = 'SELECT COUNT(*)';
  PS_SQL = PS_SQL || ' ' || 'FROM RI_MESSAGE_LOG';
 
  IF (CHAR_LENGTH(TRIM(:PS_DATE)) > ; 0) THEN
  BEGIN
  PS_SQL = PS_SQL || ' WHERE ' || '(TRIM(CAST(EXTRACT(MONTH FROM 
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(DAY FROM 
ML_CREATE_DATE) AS VARCHAR(2)))' || '/';
PS_SQL = PS_SQL || '   ' || '(TRIM(CAST(EXTRACT(YEAR FROM 
ML_CREATE_DATE) AS VARCHAR(4 = ' || :PS_DATE;
  END
  
  IF (CHAR_LENGTH(TRIM(:PS_MSG_TYPE)) > 0) THEN
  BEGIN
  If (POSITION('WHERE' IN PS_SQL) > 0) THEN
  PS_SQL = PS_SQL || '   AND ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
& nbsp;ELSE
PS_SQL = PS_SQL || ' WHERE ML_MESSAGE_TYPE = ' || :PS_MSG_TYPE;
END
  
  --PS_SQL = PS_SQL || ' INTO ' || :PI_CNT;
  PS_SQL = PS_SQL || ' INTO :PI_CNT';
  
  EXECUTE STATEMENT (:PS_SQL);
  
  SUSPEND;
END;
<<<

If someone could take a look at this code and give me an idea as to where I am 
going wrong it would very much appreciated...

Thank you...

Steve Naidamast







---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Alter column not null with constraint name FB3

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi Mark,

thank you for your analysis especially about SQL standard.
I have created http://tracker.firebirdsql.org/browse/CORE-5806

regards,
Karol Bieniaszewski

From: Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
Sent: Wednesday, April 25, 2018 8:27 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Alter column not null with constraint name FB3

  
On 24-4-2018 20:47, 'livius' liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> 
> 
> >
> > Hi,
> > I need to add Field not null constraint with my custom name.
> > in previous versions of Firebird we can do:
> > 1. ALTER TABLE XXX ADD FIELDX INTEGER CONSTRAINT NK_XXX__FIELDX NOT NULL;
> 
> >>Interestingly, that syntax is not documented in the Firebird 2.5
> >>language reference.
> it has been available for years. And we use it also in table creation
> Create table A
> (
> FIELD1 INTEGER CONSTRAINT NK_A_FIELD1 NOT NULL
> )
> and this syntax is interchangeably between Firebird and Interbase.
> There is difference in message when null is inserted into not null field.
> In Interbase there is constraint name in message in Firebird it is not 
> included.
> In Firebird we have “TABLE”.”FieldName” in message.
> Will be good if Firebird will also contain constraint name.
> And this is good if user can name all self created constraint in the 
> database like it is for all other PK, FK, UK, CK.
> You can then have in app standardized handling about e.g. error messages.

Given named not null constraints are an undocumented feature, you can't 
expect too much from it. I suggest you create tickets to get this 
documented, and maybe to extend support to allow naming the constraint 
when using alter table xxx alter yyy set not null.

I'd suggest something like expanding ALTER TABLE ADD  by 
adding the option to tconstraint:

[CONSTRAINT ] NOT NULL ()

Or maybe

ALTER TABLE  ALTER  SET [CONSTRAINT 
] NOT NULL

Although that might conflict with the oddity of also supporting ALTER 
TABLE  ALTER  SET NULL, which is not defined in 
the SQL standard and is not a real constraint, and shouldn't get named.

Interestingly, the SQL standard also supports named not null 
constraints, but there also naming it using ALTER COLUMN ... SET NOT 
NULL is not supported.

Mark
-- 
Mark Rotteveel




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Alter column not null with constraint name FB3

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

> 
> Hi,
> I need to add Field not null constraint with my custom name.
> in previous versions of Firebird we can do:
> 1. ALTER TABLE XXX ADD FIELDX INTEGER CONSTRAINT NK_XXX__FIELDX NOT NULL;

>>Interestingly, that syntax is not documented in the Firebird 2.5 
>>language reference.
it has been available for years. And we use it also in table creation
Create table A
(
FIELD1 INTEGER CONSTRAINT NK_A_FIELD1 NOT NULL
)
and this syntax is interchangeably between Firebird and Interbase.
There is difference in message when null is inserted into not null field.
In Interbase there is constraint name in message in Firebird it is not included.
In Firebird we have “TABLE”.”FieldName” in message.
Will be good if Firebird will also contain constraint name.
And this is good if user can name all self created constraint in the database 
like it is for all other PK, FK, UK, CK.
You can then have in app standardized handling about e.g. error messages.
regards,
Karol Bieniaszewski

[firebird-support] Re: Mapping Windows group to FB role

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
anybody can help with - point 4?
1. enable windows authentication is working ok by
CREATE GLOBAL MAPPING TRUSTED_AUTH
  USING PLUGIN WIN_SSPI
  FROM ANY USER
  TO USER;

2. mapping specific windows domain user to internal Firebird user is working ok 
by:
CREATE MAPPING TRUSTED_KBIENIASZEWSKI
  USING PLUGIN WIN_SSPI
  FROM 'xCORP\KAROL.BIENIASZEWSKI'
  TO USER KBIENIASZEWSKI;

3. granting specific role to windows user inside databse is working ok by:
grant WSZYSCY TO 'xCORP\KAROL.BIENIASZEWSKI'

4. mapping windows group to Firebird role is not working 
CREATE MAPPING TRUSTED_WSZYSCY 
USING PLUGIN WIN_SSPI 
FROM predefined_group 'rFirebird-Firebird-DBRido-ReadWrite' 
TO ROLE WSZYSCY;

this also is not working
CREATE MAPPING TRUSTED_WSZYSCY 
USING PLUGIN WIN_SSPI 
FROM predefined_group 'xCorp\rFirebird-Firebird-DBRido-ReadWrite' 
TO ROLE WSZYSCY;

i am missing something obvious?

regards,
Karol Bieniaszewski



>Hi,

>can someone point me how can i map windows group to the Firebird role?
>I have tried already

>CREATE MAPPING TRUSTED_WSZYSCY USING PLUGIN WIN_SSPI FROM predefined_group 
>'rFirebird-Firebird-DBRido-ReadWrite' TO ROLE WSZYSCY

>I have also tested with prefix xcorp\rFirebird... mapping was created but this 
>not work for me. 

>Can someone help?

>Regards, 
>Karol Bieniaszewski

---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


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

Re: [firebird-support] select *at least* N rows

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Yes,

but remember about good join condition then and if needed sort condition
especially you can use system table e.g. RDB$RELATION_FIELDS (it contain many 
records also in empty database)
and JOIN on RDB$RELATION_NAME=’RDB$DATABASE’ AND RDB$FIELD_ID=0 instead of 
“G.OUT_NO=1”

regards,
Karol Bieniaszewski


From: shg_siste...@yahoo.com.ar [firebird-support] 
Sent: Friday, March 23, 2018 9:03 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] select *at least* N rows

  

That's a very interesting solution as well Karol! Thanks!

I've just realized that I can "left join" my table against *any* table which I 
know it already has enough records for my purpose


---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] select *at least* N rows

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

i know that you got the answer but, maybe this trick is interesting for you
because it is simplest for use in any query but require creation of one simple 
procedure.
I use it always in situation like you with fixed numbers of rows with nulls

-
CREATE PROCEDURE GEN_ROWS(IN_COUNT INTEGER) RETURNS (OUT_NO INTEGER)
AS 
DECLARE VARIABLE VAR_I INTEGER; 
BEGIN
  VAR_I=1;
  WHILE (VAR_I<=IN_COUNT) DO
BEGIN
OUT_NO=VAR_I;
VAR_I=VAR_I + 1;
SUSPEND;
END
END
-

and now you can use it in select simply

-
SELECT 
W.*
FROM 
GEN_ROWS(10) G
LEFT JOIN YOUR_TABLE W ON G.OUT_NO=1
ORDER BY G.OUT_NO, your_other_fields
ROWS 10
-

Best Regards,
Karol Bieniaszewski


From: shg_siste...@yahoo.com.ar [firebird-support] 
Sent: Friday, March 23, 2018 7:29 PM
To: firebird-support@yahoogroups.com 
Subject: RE: [firebird-support] select *at least* N rows

  

Thank you very much Mark and András!!! 

I've tried András solution... I replaced (mechanically, without undestanding 
too much what was going on...) and it did the trick perfectly!

Now is time to study a bit more and try to understand András answer :)

Thanks a million again!!!


---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Firebird 3 Developer's Guide released

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Thank you,

really little correction needed DOC-133 – i still read it maybe i find more
Really well dev guide not only for Firebird users 

regards,
Karol Bieniaszewski

From: Mark Rotteveel m...@lawinegevaar.nl [firebird-support] 
Sent: Saturday, March 3, 2018 3:07 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Firebird 3 Developer's Guide released

  
On 3-3-2018 13:16, 'livius' liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> thank you very much for this guide.
> Where should i post info if i heve finded some issues in text? Here?

That is what the firebird-docs mailing list and 
http://tracker.firebirdsql.org/browse/DOC is for.

> And also if something was changed in future versions of software like 
> e.g. Delphi which is not compatibile with source code provided?

The documentation and example code is available on 
https://github.com/FirebirdSQL/firebird-documentation (in 
src/docs/refdocs/fbdevgd) pull requests with updates are always welcome 
(although depending on the scale of the changes, discussing it on 
firebird-docs first might be advisable).

Mark
-- 
Mark Rotteveel




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


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

Re: [firebird-support] Trying to figure out if CORE-5615 is on fb 3.0.3

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

Did you tested this fix?
It was included in 
https://github.com/FirebirdSQL/firebird/commit/9c66f3c8d018e9256042f7ad210562c93426a55c
but you have not confirmed if this commit fix your problem on the tracker.


Regards,
Karol Bieniaszewski

From: Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
Sent: Monday, February 26, 2018 8:14 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Trying to figure out if CORE-5615 is on fb 3.0.3

  
Hello

I’m trying to figure out if the changes made on core-5615 where pushed to
the stable 3.0.3 version (its not listed in the changelog).
If it were not, does the newest snapshot contain it?

Thanks

(im sorry for the duplicate topic)

Atenciosamente,

Rudi Feijó

Multidados Informática Ltda.
* (11) 2579-8789

*  rudi.fe...@multidadosti..com.br
*  www.multidadosti.com.br 

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

Re: [firebird-support] Re: Set custom value on connection system info

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Yes, i have assumed this 
Now it is much clearer. You have 1 DB user and many application user. 
Then yes, context var is your friend

regards,
Karol Bieniaszewski

From: marco andreolli dra...@gmail.com [firebird-support] 
Sent: Saturday, February 17, 2018 9:03 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Set custom value on connection system info

  

Hi, sorry Karol if I dont't reply until now.


I think you you assume I have a Firebird user for any user application, but I 
have a only one firebird user for all my application. So 
MON$ATTACHEMNTS..MON$USER is the same for all connections!




Il 10 feb 2018 16:46, "'livius' liviusliv...@poczta.onet.pl [firebird-support]" 
<firebird-support@yahoogroups.com> ha scritto:


  Hi,

  you store in your persistent table login (user_name) it is presistent 
information.
  You join your persistent table which should contain login name and you join 
your persistent table with mon$attachment an you know who is currently logged 
in.

  once again sample

  CREATE TABLE MY_USERS
  (USER_ID INTEGER NOT NULL PRIMARY KEY,
  NAME VARCHAR(32) NOT NULL,
  AGE INTEGER,
  .
  LOGIN_NAME VARCHAR(32) NOT NULL
  );

  sample record
  USER_ID=1
  NAME=’Karlos’
  AGE=52
  
  LOGIN_NAME=’SYSDBA’ 

  and you run sql

  SELECT * FROM MY_TABLE MT INNER JOIN MON$ATTACHEMNTS A ON 
A.MON$USER=MT..LOGIN_NAME

  regards,
  Karol Bieniaszewski

  From: marco andreolli dra...@gmail.com [firebird-support] 
  Sent: Saturday, February 10, 2018 9:15 AM
  To: firebird-support@yahoogroups.com 
  Subject: Re: [firebird-support] Re: Set custom value on connection system info

  Hi, 

  I have a table of users, but I wouldn't store in persistent table a volatile 
information (if it was possible).

  The link between a connection id and user id isn't a persistent information 
(like connections,transaction ecc...)

  2018-02-08 6:19 GMT+01:00 liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] <firebird-support@yahoogroups.com>:

  Hi,

  what do you mean by maintain here? Do you have custom user information table 
or not? If not then what do you need to store in this context var?

  I supposed that you have custom user information table and you need to match 
your user record with server user to know who is logged into the server..

  Regards, 
  Karol Bieniaszewski

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






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

Re: [firebird-support] Re: Set custom value on connection system info

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

you store in your persistent table login (user_name) it is presistent 
information.
You join your persistent table which should contain login name and you join 
your persistent table with mon$attachment an you know who is currently logged 
in.

once again sample

CREATE TABLE MY_USERS
(USER_ID INTEGER NOT NULL PRIMARY KEY,
NAME VARCHAR(32) NOT NULL,
AGE INTEGER,

LOGIN_NAME VARCHAR(32) NOT NULL
);

sample record
USER_ID=1
NAME=’Karlos’
AGE=52
...
LOGIN_NAME=’SYSDBA’ 


and you run sql

SELECT * FROM MY_TABLE MT INNER JOIN MON$ATTACHEMNTS A ON 
A.MON$USER=MT.LOGIN_NAME

regards,
Karol Bieniaszewski


From: marco andreolli dra...@gmail.com [firebird-support] 
Sent: Saturday, February 10, 2018 9:15 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Set custom value on connection system info

  

Hi, 

I have a table of users, but I wouldn't store in persistent table a volatile 
information (if it was possible).


The link between a connection id and user id isn't a persistent information 
(like connections,transaction ecc...)



2018-02-08 6:19 GMT+01:00 liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] :


  Hi,

  what do you mean by maintain here? Do you have custom user information table 
or not? If not then what do you need to store in this context var?

  I supposed that you have custom user information table and you need to match 
your user record with server user to know who is logged into the server..

  Regards, 
  Karol Bieniaszewski




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

Re: [firebird-support] Re: Set custom value on connection system info

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

I know that you solved your problem.
But why you store ID from your custom table into context var 
not oposite USER_NAME in your custom user table? Then you do not need var at 
all.

regards,
Karol Bieniaszewski

From: marco andreolli dra...@gmail.com [firebird-support] 
Sent: Monday, February 5, 2018 9:11 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Set custom value on connection system info

  

Wow, thanks!
This is what I really wanted 


I never known the existence of that system table. 

Actually I use RDB$SET_CONTEXT for store user name and id; with this table I 
could know the user really logged in 


Thanks Dmitry 



2018-02-04 13:46 GMT+01:00 Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] :


  03.02.2018 23:14, marco andreolli wrote:
  >
  > but RDB$SET_CONTEXT() it's not right for me, because it store 
  > information in actual connection (or transaction) and they can not be 
  > read out of this (with a monitor program for example).

  It can be, via MON$CONTEXT_VARIABLES.

  Dmitry

Re: [firebird-support] Re: Set custom value on connection system info

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what problem with MON$ATTACHMENTS? There you have info who is logged and from 
where.
you have:
- user
- IP + port
- application name
- Remote PID (process ID)
what more you need? You can bind user name with your “user” by some table.

If you really need more info, create table with ATTACHMENT_ID + USER_NAME + any 
info required and populate it with db trigger like ON CONNECT

regards,
Karol Bieniaszewski


From: marco andreolli dra...@gmail.com [firebird-support] 
Sent: Saturday, February 3, 2018 9:14 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Set custom value on connection system info

  

Thanks Dmitry for your answer,

but RDB$SET_CONTEXT() it's not right for me, because it store information in 
actual connection (or transaction) and they can not be read out of this (with a 
monitor program for example).


With my monitor application I would know not only the connection (with the 
remote process) but also the person who created them (who is logged).






2018-02-02 20:20 GMT+01:00 Dmitry Yemanov dim...@users.sourceforge.net 
[firebird-support] :


  02.02.2018 11:46, marco andreolli wrote:
  >
  > I use Firebird 2.5, I would known if is it possible attach a custom 
  > value on a connection info.

  
https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-functions-scalarfuncs.html#fblangref25-functions-workcontext

  > Actually I can see the number of connection on a database and other 
  > useful info with MON$ATTACHMENTS, but I would know If I could set a 
  > variable linked at the current connection.

  MON$CONTEXT_VARIABLES

  Dmitry







---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] SELECT GROUP BY

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

if you need only one record then why do you use GROUP BY?
GROUP BY divide your resultset into defined groups.
Remove whole GROUP BY and acquire sum from whole records as 1 record result


regards,
Karol Bieniaszewski

From: m_brahi...@yahoo.fr [firebird-support] 
Sent: Monday, December 25, 2017 9:06 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] SELECT GROUP BY

  

Hi all,




SELECT

CAST( COALESCE(CL.QUANTITE * CL.PRIX_UNITAIRE,0) AS NUMERIC(9,2)) AS TOTAL_HT,

SUM(CAST(CL.QUANTITE * CL.PRIX_UNITAIRE *( COALESCE(CL.TAUX_REMISE,0)/100.0) AS 
NUMERIC(9,2))) AS TOTAL_REMISE, SUM((CL.QUANTITE * CL.PRIX_UNITAIRE) - 
CL.QUANTITE * CL.PRIX_UNITAIRE * (COALESCE(CL.TAUX_REMISE,0)/100.0)) AS 
TOTAL_HT_NET,

SUM((COALESCE(CL.TAUX_TVA,0)/100.0)*CL.QUANTITE * 
CL.PRIX_UNITAIRE*(1.0-(COALESCE(CL.TAUX_REMISE,0)/100.0))) AS TOTAL_TVA,

SUM(CL.QUANTITE * CL.PRIX_UNITAIRE*(1.0-COALESCE(CL.TAUX_REMISE,0)/100) 
*(1+COALESCE(CL.TAUX_TVA,0)/100.0)) AS TOTAL_TTC



FROM COMMANDE_FOUR_LIGNE CL WHERE CL.COMMANDE_ID=1



GROUP BY


CL.QUANTITE,CL.PRIX_UNITAIRE,CL.TAUX_REMISE,CL.TAUX_TVA,CL.COMMANDE_ID







I need to sum records which have the same foreig n key value (CL.COMMANDE_ID=1) 
in order to obtain only one line of the fields I sum. The table contains 4 
records and I get 4 four records instead of one record. Is that something wrong 
with my query,can you please help me ?





---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Alternative to MySQL ELT() function ?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

same no, but similar yes
select decode(1, 0, 'deleted', 1, 'active', 2, ‘inserted’, null) from 
RDB$DATABASE

regards,
Karol Bieniaszewski

From: zilez2...@yahoo.com [firebird-support] 
Sent: Friday, November 24, 2017 3:12 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Alternative to MySQL ELT() function ?

  
As the tithe says, is there anything similar to MySQL ELT() function in 
Firebird ?

Regards, 

Zoran







---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Moving DB from 32 bits to 64 bits

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

bitness of the server does not matter only bitness of client library 
(fbclient.dll).
Client library bitness must be the same as your application.

regards,
Karol Bieniaszewski

From: m_brahi...@yahoo.fr [firebird-support]
Sent: Tuesday, November 14, 2017 5:54 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Moving DB from 32 bits to 64 bits


Hello,

I would like to know please, if we can use a DB in win64 but built under 
win32 . The client app is built with delphi 32 bits so the clients will 
connect to that DB in 64 bits. Is it possible ? Thanks




Posted by: m_brahi...@yahoo.fr

Re: [firebird-support] Sorting-Problem on recursive query (window functions)

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what about this?
with recursive cte as
  (select M.id, M.id_parent, M.green, CAST(M.SortText AS VARCHAR(100)) AS 
SortText
 from MyTable M
where M.Green = 'Yes'
union all
   select T2.id, T2.id_parent, T2.green, T2.SortText || '->' || C.SortText
 from MyTable T2
  join cte C on T2.ID = C.id_parent)
select distinct id, id_parent, green, SortText
  from cte
  order by SortText

regards,
Karol Bieniaszewski

From: josef.gschwendt...@quattro-soft.de [firebird-support] 
Sent: Monday, November 6, 2017 12:32 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Sorting-Problem on recursive query (window 
functions)

  

Hello,





I have a table which data is representing a tree:




CREATE TABLE MyTABLE (
ID INTEGER NOT NULL,
ID_PARENT INTEGER,
GREEN VARCHAR(3),
SortText VARCHAR(5),
CONSTRAINT PK_MYTABLE PRIMARY KEY (ID));



This is the data in this table:

  ID
 PARENT_ID
 GREEN
 SortText
 
  1
 
 No
 A3
 
  2
 1
 Yes
 B1
 
  3
 2
 No
 C6
 
  4
 2
 Yes
 C5
 
  5
 2
 Yes
 C4
 
  6
 1
 No
 B2
 
  7
 6
 No
 C4
 
  8
 6
 Yes
 C3
 
  9
 
 No
 A2
 
  10
 9
 No
 B3
 
  11
 10
 No
 C2
 
  12
 
 No
 A1
 
  13
 12
 Yes
 B4
 
  14
 13
 No
 C1
 



INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (1, NULL, 'No', 
'A3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (2, 1, 'Yes', 'B1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (3, 2, 'No', 'C7');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (4, 2, 'Yes', 'C6');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (5, 2, 'Yes', 'C5');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (6, 1, 'No', 'B2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (7, 6, 'No', 'C4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (8, 6, 'Yes', 'C3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (9, NULL, 'No', 
'A2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (10, 9, 'No', 'B3');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (11, 10, 'No', 
'C2');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (12, NULL, 'No', 
'A1');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (13, 12, 'Yes', 
'B4');

INSERT INTO MYTABLE (ID, ID_PARENT, GREEN, SORTTEXT) VALUES (14, 13, 'No', 
'C1');



Table-Data shown as Tree

  A3
 
 
 

 B1
 
 

 
 C7
 

 
 C6
 

 
 C5
 

 B2
 
 

 
 C4
 

 
 C3
 
  A2
 
 
 

 B3
 
 

 
 C2
 
  A1
 
 
 

 B4
 
 

 
 C1
 

Now I need a dataset which suspends the green tree-nodes with the complete 
tree-path for each green cell. This dataset should be ordered alphabetically 
(A1 before A3 and C5 before C6)

  A1
 
 
 

 B4
 
 
  A3
 
 
 

 B1
 
 

 
 C5
 

 
 C6
 

 B2
 
 

 
 C3
 



I (almost) get the result I want with this statement:

with recursive cte as

  (select id, id_parent, green, SortText

 from MyTable

where Green = 'Yes'

union all

   select T2.id, T2.id_parent, T2.green, T2.SortText

 from MyTable T2

  join cte on T2.ID = cte.id_parent)



select distinct id, id_parent, green, SortText

  from cte







The dataset is ok, but the ordering is not (because there is no “Depth First 
by” – Clause)



With FB3 I tried to use windows functions (something like below):

Rank() over(Partition by id_parent order by SortText)



But I could not solve my problem!  - For instance, I got the message: Recursive 
member of CTE cannot use aggregate or window function.




Could anybody give me a hint how to solve this.




Thank you,


Josef

Re: [firebird-support] MON$REMOTE_ADDRESS

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

client port number

regards,
Karol Bieniaszewski

From: m_brahi...@yahoo.fr [firebird-support] 
Sent: Tuesday, October 31, 2017 8:07 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] MON$REMOTE_ADDRESS

  

Hello,



Can you please, tell what mean the second part number of the IP (2711) after 
the slash when I select MON$REMOTE_ADDRESS FROM MON$ATTACHMENTS ? 
192.168.1.20/2711

Thank you.

Re: [firebird-support] CTE Recursive left join problem

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

this is normal sql behavior
if you do not need to implicit INNER JOIN condition then move filter to left 
join itself
in your situation:
instead “where mytable.id is not null” write “ AND mytable.id is not null”

...
select aux.n, mytable.id
from aux
   LEFT JOIN mytable on aux.n between mytable.id and mytable.id +1 and 
mytable.id is not null
...

regards,
Karol Bieniaszewski

From: Germán Balbi bal...@yahoo.com [firebird-support] 
Sent: Monday, October 2, 2017 7:37 PM
To: Firebird-support 
Subject: [firebird-support] CTE Recursive left join problem

  

Hello everyone
I'm having a problem, and I do not understand why.
I have a structure similar to:with recursive aux as (  select 0 as nfrom 
rdb$database  union all  select aux.n + 1from auxwhere aux.n <= 20 )  
select aux.n, mytable.id from aux   LEFT JOIN mytable on aux.n between 
mytable.id and mytable.id +1  where mytable.id is not null Where the left join 
behaves as join. Any condition that I put in the clause WHERE referred to 
mytable, converts the LEFT JOIN into JOIN

Re: [firebird-support] LIST gives inconsistent results

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what about this query 
It looks strange but is really simple

SELECT
R5.SUMING, COUNT(*)
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS 
SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY 
R4.ROUTE_INDEX ASC) R3), R2.NODE
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R5
WHERE
EXISTS(
SELECT 
*
FROM
(
SELECT
(SELECT CAST(LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) AS VARCHAR(1000)) AS 
SUMING FROM (SELECT * FROM ROUTES R4 WHERE R4.NODE=R2.NODE ORDER BY 
R4.ROUTE_INDEX ASC) R3)
FROM
(SELECT DISTINCT R.NODE from ROUTES R) R2
) R6
WHERE
R6.SUMING=CAST((SELECT LIST(R3.ROUTE_INDEX || '_' || R3.LINK_NODE) FROM (SELECT 
* FROM ROUTES R4 WHERE R4.NODE=R5.NODE ORDER BY R4.ROUTE_INDEX ASC) R3) AS 
VARCHAR(1000))
)
GROUP BY 1
HAVING COUNT(*)>1


for me it found all duplicates in sets

regards,
Karol Bieniaszewski


From: 'Daniel Miller' dmil...@amfes.com [firebird-support] 
Sent: Thursday, September 28, 2017 10:49 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] LIST gives inconsistent results

  

It's certainly possible my database is corrupted - I don't think it is.  But 
I'm willing to test if someone tells me how.  However...

At the moment, after several painful hours, I think I've determined the 
following:

CREATE TABLE NODES
(
  NODE smallint NOT NULL,
  ROUTE_UPDATED timestamp,
  CONSTRAINT PK_NODES PRIMARY KEY (NODE)
);

CREATE TABLE ROUTES
(
  NODE smallint NOT NULL,
  ROUTE_INDEX smallint NOT NULL,
  LINK_NODE smallint NOT NULL,
  QUALITY smallint,
  CONSTRAINT PK_ROUTES PRIMARY KEY (NODE,ROUTE_INDEX)
);

Entries in table ROUTES are inserted in primary key order - as well as sorted 
by primary key. So via both "raw & natural" order and an active ORDER BY it 
shouldn't be that difficult to have a sorted list of routes!

if I do:
select R.NODE, list(R.ROUTE_INDEX||R.LINK_NODE||R.QUALITY) LISTED_ROUTE
from ROUTES R
group by R.NODE

I get a computed column that is properly sorted. I don't even need to specify 
an ORDER BY.  But...

select N.NODE, list(ROUTE_INDEX||LINK_NODE||QUALITY) LISTED_ROUTE
from NODES N
join (select R.NODE, R.ROUTE_INDEX, R.LINK_NODE, R.QUALITY from ROUTES R 
order by R.NODE,R.ROUTE_INDEX) using (NODE)
group by N.NODE

Even though I'm explicitly sorting the source derived table for the LIST - I 
get a set of results that seems almost random. Absolutely maddening.
--
Daniel


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

Re: [firebird-support] Firebird performance vs PostgreSQL

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

without any sample you talking about nothing..
Show some sample which can be reproduced.
Without this i can say same Firebird is faster then Oracle and what someone can 
say about this – nothing 

regards,
Karol Bieniaszewski

From: zilez2...@yahoo.com [firebird-support] 
Sent: Thursday, September 28, 2017 11:17 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Firebird performance vs PostgreSQL

  
Hello community, 

I am Firebird user for many years, I think I started using Interbase in 2001, 
and after that I use Firebird from version 1.0.
I have a Firebird 2.5 Server in the company where I work, which stores some 
data for the purpose of business analysis.
Since now it is September, many queries works relatively slow, due to a lot of 
data in the database.
>From the curiosity, I installed PostgreSQL yesterday on my laptop, migrated 
>data from actual Firebird database to Postgresql, and compared time of 
>execution on both system. On my great surprise, PostgreSQL was much faster 
>tqan Firebird. I don't know nothing about tweak of PostgreSQL database, so I 
>only migrated data, nothing else. 
I know that some of my views / queries are not optimized 100% on Firebird, but 
same situation is with PostgreSQL database which was just converted version of 
firebird.
Query which gives me sales by product lines from begining of the year till 
yesterday was working on firebird server 22 seconds, and on PostgreSQL 2 
seconds.

My laptop has SSD disc, but even when i put Firebird database on my laptop, 
query takes 16 seconds in Firebird.

How is it possible that PostgreSQL is so much faster than Firebird?

Regards,

Zoran 








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

Re: [firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

look on this, only simple sample, by if join count increase you will see 
difference faster.
Also faster if you join by few fields not only one.
At first look you see clearly where is filter and where is join condition.
And then you can simply analyse if query use indexes on joined fields and if it 
is true you go forward
and you analyze if filter “where” can be also indexed

SELECT
...
FROM
TABLE_A A
INNER JOIN TABLE_B B ON A.ID=B.ID
WHERE
A.FIELDX>5


and

SELECT
...
FROM
TABLE_A A, TABLE_B B 
WHERE
A.ID=B.ID
AND A.FIELDX>5

regards,
Karol Bieniaszewski




From: dres...@tbinc.com [firebird-support] 
Sent: Monday, September 25, 2017 7:02 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Huge performance different from FB 2.5.2 vs FB 
3.02

  

Karol:

Not to highjack the thread, but a question. You stated:

> this is terible to read. Please change join style to explicite join style
>  
> instead SELECT .. FROM TABLE_A A, TABLE_B B WHERE A.ID=B.ID
> write SELECT .. FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID=B.ID


I'm not a DB expert, only as I have to but I read the first line easier than 
the second line, but not without much difference. Why do you state it is 
terrible?

Ed Dressel

Re: [firebird-support] Huge performance different FB 2.5.2 vs FB 3.02

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

show View (M_FNGD_VW) definition
and also what are the settings – especially PAGE buffers and Page size in both 
2.5 and 3

regards,
Karol Bieniaszewski

From: trsk...@yahoo.com [firebird-support] 
Sent: Friday, September 22, 2017 11:05 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Huge performance different FB 2.5.2 vs FB 3.02

  
Hello guys,

I have this query :
SELECT a.BB, a.PLUS, a.MINUS, a.ENDBLNC, vw.UNT,vw.SLS_PRC_DS,vw.SLS_PRC_DT
from GET_STCK_MUT_PSG_CNT('09-18-17','09-18-17','All') a, M_FNGD_VW vw
where a.ID = vw.FNGD_ID
and a.ENDBLNC >= 0
order by vw.NM,vw.cl, vw.SZ_CD, vw.ASST_CD





On SuperClasic FB 2.52 on Win 7 64, it took +/- 50 secs :




PLAN SORT (JOIN (JOIN (JOIN (JOIN (G_T_IN_OT3 NATURAL, A INDEX (IDX_M_FNGD3)), 
A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX 
(IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX 
(RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX 
(RDB$PRIMARY23))), A INDEX (IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H 
NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX 
(T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX 
( RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX (RDB$PRIMARY23, A INDEX 
(IDX_M_FNGD3), A INDEX (IDX_M_FNGD3), SORT (JOIN (H NATURAL, B INDEX 
(IDX_T_IN_OT11), J INDEX (IDX_G_TMP23), A INDEX (T_IN_OT2_T_IN_OT1), C INDEX 
(RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E INDEX (RDB$PRIMARY24), G INDEX 
(RDB$PRIMARY22), F INDEX (RDB$PRIMARY23, SORT (G_T_IN_OT3 NATURAL))(VW A 
INDEX (RDB$PRIMARY25))(VW C INDEX (RDB$PRIMARY28))(VW D INDEX 
(RDB$PRIMARY21))(VW E INDEX (RDB$PRIMARY22))(VW F INDEX (RDB$PRIMARY24))(VW J 
INDEX (RDB$PRIMARY23))(VW B INDEX (RDB$PRIMARY27))(VW H INDEX 
(RDB$PRIMARY47))(VW I INDEX (RDB$PRIMARY48))(VW G INDEX (RDB$PRIMARY26)))


Executing...
Done.
70901350 fetches, 52178 marks, 236349 reads, 19 writes.
25577 inserts, 0 updates, 0 deletes, 18067187 index, 33745 seq.
Delta memory: 19474744 bytes.
G_T_IN_OT3: 25577 inserts. 
Total execution time: 49.882s
Script execution finished.





On SuperServe r FB 3.02, it took about 4.5 minutes :





PLAN SORT (JOIN (JOIN (JOIN (JOIN (A NATURAL, VW A INDEX (RDB$PRIMARY25)), VW C 
INDEX (RDB$PRIMARY28), VW D INDEX (RDB$PRIMARY21), VW E INDEX (RDB$PRIMARY22), 
VW F INDEX (RDB$PRIMARY24), VW J INDEX (RDB$PRIMARY23)), VW B INDEX 
(RDB$PRIMARY27), VW H INDEX (RDB$PRIMARY47), VW I INDEX (RDB$PRIMARY48)), VW G 
INDEX (RDB$PRIMARY26)))


Executing...
Done.
290144826 fetches, 51085 marks, 756444 reads, 15 writes.
25187 inserts, 0 updates, 0 deletes, 93504000 index, 25607 seq.
Delta memory: 21251608 bytes.
G_T_IN_OT3: 25187 inserts. 
Total execution time: 0:04:31 (hh:mm:ss)
Script execution finished.





Huge different. Any idea how to fix this on FB 3.02 ?




Thanks & regards,

Anto

Re: [firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

this is terible to read. Please change join style to explicite join style

instead SELECT .. FROM TABLE_A A, TABLE_B B WHERE A.ID=B.ID
write SELECT .. FROM TABLE_A A INNER JOIN TABLE_B B ON A.ID=B.ID

regards,
Karol Bieniaszewski


From: trsk...@yahoo.com [firebird-support] 
Sent: Friday, September 22, 2017 11:48 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Huge performance different from FB 2.5.2 vs FB 3.02

  
Hello guys,

I have this Query

select c.ID , e.CD, f.CD, sum(a.STCK_CLC) as qty, e.UNT, d.ID, g.ID
from t_in_ot2 a, t_in_ot1 b, M_FNGD c, M_ART d, M_Sz e, M_ASST f, M_CLR g, 
m_trs_typ h, G_TMP j
where d.ID = c.ART_ID
and   g.ID = c.CLR_ID
and   e.ID = c.SZ_ID
and   f.ID = c.ASST_ID
and   c.id = a.id_gd
and   b.id = a.id_in_ot1
and   b.id_trs_typ = h.ID
and   h.STCK_CLC is not NULL
and   h.STCK_TYP = 'F'   
and   d.TYP = 'F'
and   b.is_cls = 'F'
and   b.trs_dt BETWEEN '12/31/11' and '09-18-17'
and   b.id_div_lc = j.ID 
and   e.UNT in('PSG','CT')
and   b.CHCK_BY is not null
and   b.cncl_by is null
and   a.IS_VW = 'Y'
and   f.ID = c.ASST_ID
an d   g.ID = c.CLR_ID
GROUP by c.ID, e.cd, f.cd, e.UNT, d.ID, g.ID

On SuperClasic FB 2.5.2 on Win 7 64, it took about 48 sec, here is the PLAN :

PLAN SORT (JOIN (H NATURAL, B INDEX (IDX_T_IN_OT11), J INDEX  (IDX_G_TMP23), A 
INDEX (T_IN_OT2_T_IN_OT1), C INDEX (RDB$PRIMARY25), D INDEX (RDB$PRIMARY21), E 
INDEX (RDB$PRIMARY24), G INDEX (RDB$PRIMARY22), F INDEX (RDB$PRIMARY23)))


Executing...
Done.
70013857 fetches, 20 marks, 235849 reads, 27 writes.
0 inserts, 0 updates, 0 deletes, 17905601 index, 8120 seq.
Delta memory: 74656432 bytes.
Total execution time: 48.672s
Script execution finished.




On SuperServer FB 3.0.2 on Win 7 64, it took about 4.5 minutes, here is the 
PLAN 


PLAN SORT (JOIN (JOIN (JOIN (JOIN (A NATURAL, VW A INDEX (RDB$PRIMARY25)), VW C 
INDEX (RDB$PRIMARY28), VW D INDEX (RDB$PRIMARY21), VW E INDEX (RDB$PRIMARY22), 
VW F INDEX (RDB$PRIMARY24), VW J INDEX (RDB$PRIMARY23)), VW B INDEX 
(RDB$PRIMARY27), VW H INDEX (RDB$PRIMARY47), VW I INDEX (RDB$PRIMARY48)), VW G 
INDEX (RDB$PRIMARY26)))




Executing...
Done.
290144826 fetches, 51085 marks, 756444 reads, 15 writes.
25187 inserts, 0 updates, 0 deletes, 93504000 index, 25607 seq.
Delta memory: 21251608 bytes.
G_T_IN_OT3: 25187 inserts. 
Total execution time: 0:04:31 (hh:mm:ss)
Script execution finished.



Huge different ! This is because FB 3.0.2 took different PLAN from FB 2.5.2.

When PLAN from FB 2.5.2 applied to FB 3.0.2, execution time almost the same.

Looks like FB 3.0.2 Query PLAN not as good as FB 2.5.2.

How to make sure that Query PLAN from FB 3.0.2 is the optimum one? Bad plan 
affect performance badly.

Thanks & regards,

Anto.

Re: [firebird-support] Firebird 3 crashes, unable to capture crash dumps ( terminated abnormally (4294967295) )

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

i do not remember your previous description
but did you checked your DB against corruption?
gfix –validate –full

any errors (also in firebird.log)?
Did you performed backup/restore process.
Do you use any udf? also system udf?
Do you have enough free mem, free disk space also for temp files like sorting?
Do you have recent Firebird version (which exactly) and also fbclient.dll 
version (if you use it or gds32.dll)


regards,
Karol Bieniaszewski


From: Rudi Feijó rudi.fe...@multidadosti.com.br [firebird-support] 
Sent: Thursday, September 14, 2017 8:26 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Firebird 3 crashes, unable to capture crash dumps ( 
terminated abnormally (4294967295) )

  

Hello

It’s been a while since I gave na update on my situation here.


To recap, firebird 3 is crashing randomly on my server. It may crash once, 
twice, or 10 times a day. Or not once during the day.

Server load is always minimal during the crashes, and so is the memory usage. 
With the help of the community, my latest attempts where to change the 
firebird.conf, and I tried a lot of different combinations, even returning to 
the default combination, but the problem still happens.


Then, I tried to capture the crash dumps.

I followed the instructions at Collecting User-Mode Dumps:

https://msdn.microsoft.com/en-us/library/windows/desktop/bb787181.aspx

However, I’ve had a lot of crashes since I changed the registry, but no crash 
dumps are collected at all.

I’ve made sure the dump directory is writable and readable by everyone (system, 
admin, user, etc).

I also turned off the guardian as required, and created a simple batch file to 
restart firebird every 5 seconds if needed to act as a guardian.



Before turning off the guardian, firebird.log always wrote a terminated 
abnormally (4294967295) error, not without the guardian it doesn’t write 
anything at all, it just crashes silently.


So this is the status now. 
I’m thinking on trying to install de debug version and attach Watson to it, if 
anyone have any other ideas I’d be glad to hear.



Specs : 

Firebird-3.0.2.32703_0_x64 on windows 2012 R2 VM (2 core 2.30gh xeon, 13gb ram)

Config :

ServerMode = Super

GuardianOption = 0

WireCrypt = Enabled

TempDirectories = C:\firebird-temp

AuthServer = Legacy_Auth, Srp, Win_Sspi

AuthClient = Legacy_Auth, Srp, Win_Sspi

UserManager = Legacy_UserManager, Srp


DefaultDbCachePages = 2

TempBlockSize = 2M

TempCacheLimit = 364M

LockMemSize = 9M

LockHashSlots = 30011

Registry changes made to capture dumps :

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows\Windows Error 
Reporting\LocalDumps]

"DumpType"=dword:0002

"DumpFolder"=hex(2):63,00,3a,00,5c,00,6c,00,6f,00,63,00,61,00,6c,00,2d,00,64,\

  00,75,00,6d,00,70,00,73,00,00,00

"DumpCount"=dword:

"CustomDumpFlags"=dword:

Re: [firebird-support] file size and transactions

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>Karol,

>>I was referring to the default transaction state of snapshot (repeatable 
>>read)
>>Other transaction states will have different effects. In your case
>>read commited needs only the newest record version (or record versions 
>>created by 
>>the transaction itself) so its not going to block anything.

>>Paul



Thank You Paul,

I asked to confirm if my knowledge is still up to date and luckily it is :)



regards,

Karol Bieniaszewski

Re: [firebird-support] file size and transactions

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

> I read somewhere that the "read only" transactions are absolutelly harmless 
> to the GC and sweeping. Is that so?

>>Read only transactions only physically have an impact on the TIP/Header 
>>page(s), however a long running read only transaction
>>will have the same effect as a long running read write transaction in that it 
>>will block GC and sweeps until it
>>commits.

>>Paul 
Hi,
may i ask why readonly readcommited transaction block GC?
Is this true?

regards,
Karol Bieniaszewski

Re: [firebird-support] COUNT DISTINCT issue in Fb 2.5

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

will be better to show sample script
create table
and insert into..
and db charset
then anyone can test this self

regards,
Karol Bieniaszewski

From: eadbha...@yahoo.com.mx [firebird-support] 
Sent: Monday, September 4, 2017 7:34 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] COUNT DISTINCT issue in Fb 2.5

  

Hi there,




I have this query:




select a.num_part, a.cve_prov, a.cve_impo, a.des_merc

from ctrac_clasif a


where a.num_part = '0630039046';



It yields this results:



NUM_PART CVE_PROV CVE_IMPO DES_MERC

= = = ==

0630039046 3400. 19 CUBIERTA

0630039046 3400. 19 CUBIERTA




As you can see,  the two resulting rows are identical. 




Now I run the following aggregate query:




select a.num_part,a.cve_prov,a.cve_impo,COUNT(DISTINCT a.des_merc)

from ctrac_clasif a

where a.num_part = '0630039046'

group by a.num_part,a.cve_prov,a.cve_impo


NUM_PART CVE_PROV CVE_IMPO COUNT

= = = ==

0630039046 3400. 19 2




Shouldn't COUNT be equals to 1? There's only one distinct value in the DES_MERC 
column.




Your help is much appreciated.




Regards,




Eduardo





---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Multi-column Foreign key

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

this is the feature – key is only valid(checked) for values but null is not 
“value”.
This work same in all databases i have worked.

regards,
Karol Bieniaszewski

From: Paweł Świerzko p_swier...@poczta.onet.pl [firebird-support] 
Sent: Wednesday, August 9, 2017 3:21 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Multi-column Foreign key

  
Hi, 
I have encountered the following situation. Can you tell me - is it bug or 
feature. Excuse me if it is a silly question.
I have two tables:
CREATE TABLE UNITES (
CODE VARCHAR(10) NOT NULL,
LOT VARCHAR(10) NOT NULL,
SITE VARCHAR(10) NOT NULL,
DESCRIPTION VARCHAR(100)
);

ALTER TABLE UNITES ADD CONSTRAINT PK_UNITES PRIMARY KEY (CODE, LOT, SITE);

CREATE TABLE LOCATION (
ID INTEGER NOT NULL,
UCODE VARCHAR(10),
ULOT VARCHAR(10),
USITE VARCHAR(10),
ZONE INTEGER,
WAREHOUSE INTEGER
);

ALTER TABLE LOCATION ADD CONSTRAINT PK_LOCATION PRIMARY KEY (ID);

ALTER TABLE LOCATION ADD CONSTRAINT FK_LOCATION_1 FOREIGN KEY (UCODE, ULOT, 
USITE) REFERENCES UNITES (CODE, LOT, SITE);

So location references units using foreign key.

Let's create two unites from one lot:

INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A1', 'L1', 'S1', 
'abc');
INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A2', 'L1', 'S1', 
'bcd');

I expected that the following instruction will not be exectued:
INSERT INTO LOCATION (ID, UCODE, ULOT, USITE, ZONE, WAREHOUSE) VALUES (1, 'A3', 
NULL, NULL, NULL, NULL);
because there is no A3 entry in unites table, but it IS! Firebird allows to add 
record with value which doesn't exist in master table.
Thanks in advance
Pawel





---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Firebird 2.5 rejecting connections

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,
put fbclient.dll / and/or/ gds32.dll into your application folder.
Remember that you need 32bit dll’s if your app is 32bit and 64bit dll if you 
have 64bit app (bitness of the server does not matter)
Check if you have not Interbase installed on you computer side (some apps can 
install it)
regards,
Karol Bieniaszewski 

Re: [firebird-support] Re: Detecting disk operations

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

thank you Vlad for clarify 
I have created ticket http://tracker.firebirdsql.org/browse/CORE-5577

regards,
Karol Bieniaszewski

From: hv...@users.sourceforge.net [firebird-support] 
Sent: Thursday, July 6, 2017 12:05 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Detecting disk operations

  

> 3. Page is read/write from temp object bigger then memory buffer e.g big 
> global temporary table, others?


  >> GTT's data go thru the common page cache as all other database operations.

What happen when GTT will be bigger then whole mem cache? It will go to disk?
If yes, how to catch such situation?


  Temporary files, used for GTT, is created with FILE_ATTRIBUTE_TEMPORARY 
attribute (on Windows),
thus OS could cache its contents more aggressively than for non-temp files. 
Firebird doesn't control when 
OS decides to write data to disk. Also, Firebird doesn't flush temp files 
buffers to disk on commit and 
ignores database FW setting for these files.


Is there some other situation where some “buffer” go to disk?


  Firebird uses temporary files for sorting and for the part of undo-log. 
Amount of temporary
data maintained in memory (before is will go to the disk file) is controlled by 
the TempCacheLimit
setting. Also, note, that OS could additionally cache temporary files data in 
memory. When OS
physically writes this data to disk is out of Firebird control.


P.S. Should i create ticket with feature request about collection of this stats?


  Yes, please.

Regards,
Vlad




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Re: Detecting disk operations

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

> 2. Page is read/write beacause of big sort operation

>>  Sort IO is directed into separate temporary files and it is nor cached by 
>> the engine, nor paged.
>> Unfortunately, engine doesn't collect Sort statistics.

Will be good to see this on trace output and accumulated stats on mon$tables

>>You could try to use OS utlities to look at performance stats at the file 
>>level, btw

this is not accurate 


> 3. Page is read/write from temp object bigger then memory buffer e.g big 
> global temporary table, others?

>>  GTT's data go thru the common page cache as all other database operations.
What happen when GTT will be bigger then whole mem cache? It will go to disk?
If yes, how to catch such situation?

Is there some other situation where some “buffer” go to disk?

P.S. Should i create ticket with feature request about collection of this stats?

regards,
Karol Bieniaszewski

---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus


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

Re: [firebird-support] Triggers - One big or more smaller?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

>>practical benefit to breaking up triggers?
1. separate code logically like procedure
2. you can simply disable it without altering content
3. simplify editing by minimize toxicity

regards,
Karol Bieniaszewski

-Oryginalna wiadomość- 
From: Gabor Boros mlngl...@bgss.hu [firebird-support]
Sent: Wednesday, June 28, 2017 2:27 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Triggers - One big or more smaller?

Hi All,

The table have a BEFORE UPDATE trigger. Now I need a check before the
existing trigger code and raise an exception if needed. I can write the
check into the existing trigger before the existing code. But can create
a new trigger with a lower position than the existing one. Is there any
practical benefit to breaking up triggers?

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




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus







++

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] Foreign key different field type

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>Why should it be forbidden?  Not everything that's dumb is disallowed. 

Because it can cause problem if someone do this not intentionally.
Also if your system must work with many databases it is then not compatibile. 
(e.g. not compatibile with MSSQL)

regards,
Karol Bieniaszewski


From: Ann Harrison aharri...@ibphoenix.com [firebird-support] 
Sent: Tuesday, June 20, 2017 4:46 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Foreign key different field type

  

On Mon, Jun 19, 2017 at 1:21 PM, Thomas Steinmaurer t...@iblogmanager.com 
[firebird-support]  wrote:

  ,
  >
  > Can someone show me example when it is usefull to have different field
  > type in [referenced and referencing keys of a foreign key relationship]?
  >
  >
  > Why this is not forbidden?


Why should it be forbidden?  Not everything that's dumb is disallowed. 

  Perhaps cause the referenced column in table test1 can only store a
  subset (SMALLINT) of the value range of the INTEGER used in test2?

  Even if this works at DDL time, I wonder how strict the optimizer then
  is in JOIN statements using an index. Haven't tried.


Nor have I tried, but Firebird uses the same key representation for most numeric
columns, so having mixed sizes of numbers - or different scales -  in a foreign 
key
shouldn't matter to the optimizer.  The exception - which may have been changed 
- 
was the representation of int64. Having a single key format for numbers makes 
it simple to increase the size of columns or change the scale.

Good luck,

Ann



---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Foreign key different field type

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>At DDL time: What happens if you flip the used data types for ID/ID1?

No difference – constraint created

regards,
Karol Bieniaszewski

From: Thomas Steinmaurer t...@iblogmanager.com [firebird-support] 
Sent: Monday, June 19, 2017 7:21 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Foreign key different field type

  
> Hi,
> 
> Can someone show me example when it is usefull to have different field 
> type in relation
> 
> Sample
> Create table test1 (id smallint not null primary key)
> 
> Create table test2 (id1 integer)
> 
> Alter table test2 Add foreign key (id1) references test1(id)
> 
> Why this is not forbidden?

Perhaps cause the referenced column in table test1 can only store a 
subset (SMALLINT) of the value range of the INTEGER used in test2?

Even if this works at DDL time, I wonder how strict the optimizer then 
is in JOIN statements using an index. Haven't tried.

At DDL time: What happens if you flip the used data types for ID/ID1?

-- 
With regards,
Thomas Steinmaurer
http://www.upscene.com/

Professional Tools and Services for Firebird
FB TraceManager, IB LogManager, Database Health Check, Tuning etc.




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Re: connecting to firebird server

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

To be more precised – Interbase(and IBX) support boolean datatype but only 
Interbase implementation
not Firebird implementation.

regards,
Karol Bieniaszewski



From: Tony Christiansen t...@adegroup.com.au [firebird-support] 
Sent: Wednesday, May 24, 2017 1:07 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: connecting to firebird server

  
My experience is that IBX (XE6) works fine with FB3. 

The only incompatibility I have found is that IBX does not support the new FB3 
boolean datatype (because IB does not support it)




On 23/05/2017 7:31 PM, safirh...@yahoo.fr [firebird-support] wrote:


  Hi,
  I think that IBX are not compatible with Firebird3!





---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Blob field to Array

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

first what do you mean it contain array of boolean
you mean e.g 5 bytes and first byte is first boolean, seconf byte is second 
boolean..?
And for this you probably need udf or convert to string 

regards,
Karol Bieniaszewski

From: maartenjanbak...@gmail.com [firebird-support] 
Sent: Thursday, May 11, 2017 1:03 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Blob field to Array

  
Hi,

I've got a blobfield which contains an array of boolean. I would like to get a 
value from a certain position from this array to use in a stored procedure.

Does someone know how this is done? I've searched the internet for it, but 
can't get it to work.

Thanks in advance,

Maarten







---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Potential Causes for Query to Slow Down

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

first potential thing – transaction management.
Probably you have transaction long running and it stop garbage collector to 
process old record versions

Look into MON$TRANSACTIONS

regards,
Karol Bieniaszewski

From: craig_...@coxcolvin.com [firebird-support] 
Sent: Thursday, May 4, 2017 7:56 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Potential Causes for Query to Slow Down

  
I have a Delphi Application that uses FireDac to communicate with Firebird 2.5. 
 I have a query that periodically slows down fro about 5 secs to many minutes.  
If I backup and restore the database, I get my original performance back.

What kind of things could cause this behavior.

Thanks in advance.  







---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] deadlock

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

first try to upgrade to recent Firebird version
i recomend you snapshot
http://web.firebirdsql.org/download/snapshot_builds/win/3.0/
but you can also try official
https://www.firebirdsql.org/en/firebird-3-0-2/

after that if you will have also problems – back here

regards,
Karol Bieniaszewski


From: 'Check_Mail' check_m...@satron.de [firebird-support] 
Sent: Thursday, April 13, 2017 8:00 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] deadlock

  

Hello @ll,


at the moment, we have some problems with deadlock-messages, Firebird 
3.01.32531.


gstat-h 


C:\ Programme\firebird\Firebird_3_0>gstat -h d:\interbase\ifls.gdb


Database "D:\INTERBASE\IFLS.GDB"

Database header page information:

Flags   0

Generation& nbsp; 8469221

System Change Number0

Page size   4096

ODS version 12.0

Oldest transaction p; 8456509

Oldest active   8456510

Oldest snapshot 8456487

Next transaction8469015

Sequence number 0

< span style="font-size:12.0pt;font-family:"Comic Sans MS";color:#2E2E6A;"> 
   Next attachment ID  48138

Implementation  HW=Intel/i386 little-endian OS=Windows CC=MSVC

Shadow count0

Page buffers65536

< span style="font-size:12.0pt;font-family:"Comic Sans MS";color:#2E2E6A;"> 
   Next header page0

Database dialect3

Creation date   Feb 15, 2017 12:43:30

Attributes  force write


Variable header data:

Sweep interval: 0

*END*


A sweep set we with the windows planned tasks.


The last transaction ist the garbage collector.


What can be wrong?



Many greetings


Olaf

Re: [firebird-support] Re: Firebird 3 execution plan

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi Dmitry,

if plan can go in direction like this
PLAN JOIN (SD ORDER IXDU_SENSOR_DATA__ID INDEX (FK_SENSOR_DATA__SENSOR), S 
INDEX (PK_SENSOR)) 
then this is totally optimal plan for case with First and index used in the 
same time
For “First” ORDER should have priority and if index can particullary filter 
data then it should be prefered :)

regards,
Karol Bieniaszewski


From: 'livius' liviusliv...@poczta.onet.pl [firebird-support] 
Sent: Saturday, February 18, 2017 8:30 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Re: Firebird 3 execution plan

  
>>I will think a little bit more, maybe some clever idea will come.

fingers crossed 

regards,
Karol Bieniaszewski

From: Dmitry Yemanov dim...@users.sourceforge.net [firebird-support] 
Sent: Friday, February 17, 2017 7:38 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Re: Firebird 3 execution plan

16.02.2017 21:20, liviuslivius wrote:
>
> can this plan be selected by Firebird in somehow automatically without
> going to left join or +0 solution?

In this particular case, I'm afraid you don't have any other choice. But 
I will think a little bit more, maybe some clever idea will come.

Dmitry

---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

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

Re: [firebird-support] Does any Delphi component exist to access a FB database without DLLs?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

>>Separately, I am not aware that FireMonkey/Delphi can be deployed as mobile 
>>apps if they require external DLLs. AFAIR, FireMonkey apps must be built from 
>>"native" code. 
FireMonkey apps are builded into native code. Firemonkey can work with external 
dll
but i do not know if FBclient for Android exists? For Interbase it exists and 
work with Firemonkey on Android.

regards,
Karol Bieniaszewski

From: 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 
Sent: Sunday, March 12, 2017 8:27 PM
To: firebird-support@yahoogroups.com 
Subject: RE: [firebird-support] Does any Delphi component exist to access a FB 
database without DLLs?

  
Andrea, 

I find your post contains contradictions. 

> I am looking to write a mobile application. 
> I don't want to use SQLite because I think it's crap. 

OK 


> Firebird seems just right, except that it comes with a massive DLL. 

"Massive"? Huh? 

The Firebird Client is ~1MB in size. 

What do you define as "massive"? 


> Because phones come in all sizes,I just can't be sure that it'll be 
> downloaded if it's too big, so the first obvious saving would be the DLL. 

My quick scan of my own Android phone finds that most apps (as compared to 
utilities) are 45-50 MB in size, with the Facebook app weighing in at 240MB in 
size. 

So, to my mind, the size of the Firebird client is quite reasonable. 


Separately, I am not aware that FireMonkey/Delphi can be deployed as mobile 
apps if they require external DLLs. AFAIR, FireMonkey apps must be built from 
"native" code. 


Sean 

Re: [firebird-support] Firebird web hosting ASP.NET Core

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

i am not an expert of .Net but what about this?
https://www.nuget.org/packages/EntityFramework.Firebird/

regards,
Karol Bieniaszewski

From: 'Mr. John' mr_joh...@yahoo.com [firebird-support] 
Sent: Monday, February 20, 2017 4:44 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Firebird web hosting ASP.NET Core

  

Hi,
thanks for the info
in this case I can't access db from winforms app,only from web.for a while I 
need to access data from both.
Firebird doesn't support Entity Framework Core at this moment so I can't use it 
with Asp Core  :(





From: "'livius' liviusliv...@poczta.onet.pl [firebird-support]" 
<firebird-support@yahoogroups.com>
To: "Dimitry Sibiryakov s...@ibphoenix.com [firebird-support]" 
<firebird-support@yahoogroups.com> 
Sent: Saturday, February 18, 2017 9:23 PM
Subject: Re: [firebird-support] Firebird web hosting ASP.NET Core


  
Hi,

you can use Firebird embeded – it work near on every hostings
i use Firebird embeded with ASP .Net from 1.1 version 

regards,
Karol Bieniaszewski




---
Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie 
antywirusowe Avast.
https://www.avast.com/antivirus

Re: [firebird-support] Re: Problems after FB3 upgrade

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

as Dmitry say problems can be already solved in snapshot build
e.g. http://tracker.firebirdsql.org/browse/CORE-5307 can be same as your 
problems – 
use napshot build from
http://www.firebirdsql.org/en/snapshot-builds/

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Tuesday, September 6, 2016 8:17 AM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Re: Problems after FB3 upgrade

  
06.09.2016 08:55, Tony Christiansen wrote:
>
> My client needed a server rebuild after a ransomware attack so I took
> the opportunity to upgrade from FB2.5 to FB3. My client application is
> Delphi using IBX components.
>
> Now they are experiencing repeated crashes with an array of exceptions
> - the most common being "Error writing data to the connection" and
> "invalid transaction handle (expecting explicit transaction start)"
> mostly on select queries.

It's worth trying a v3.0.1 snapshot before considering downgrade. It may 
have these issue fixed already.

Dmitry

Re: [firebird-support] Delete causes server to hang

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

your statement is incorrect
select count(* ) 
should by after autonomous transaction commit not inside it
if it is in the same transaction then garbage collector cannot remove versions

and also i do not suppose that 
delete from requests where created < :delete_all_before rows 1000;
is correct – i do not beleve that rows affect this query but i can be wrong
i supopose that it process all records not 1000

regards,
Karol Bieniaszewski


From: mailto:firebird-support@yahoogroups.com 
Sent: Friday, August 26, 2016 10:27 AM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Delete causes server to hang

  

Hey Tim
The code does not do anything active, but we run backup 4 times a day.  

I have tried batching the delete, so that one batch runs in a autonomous 
transaction, and made at count on the table after each batch, but with no luck. 
The idea was to reduce the number of deleted rows that the garbage collector 
should process. 


v_deletedcnt = 1000;
while (:v_deletedcnt = 1000) do
begin
in autonomous transaction do
begin
delete from requests where created < :delete_all_before rows 1000;
v_deletedcnt = row_count;
select count(*) from requests into :TEMP_CNT;

end
< div>end

Re: [firebird-support] gstat output about blobs - blob pages 0

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

08.07.2016 15:41, liviuslivius liviusliv...@poczta.onet.pl 
[firebird-support] wrote:
> Blobs: 1976786, total length: 37039763, blob pages: 0
> Level 0: 1976786,

>>   All BLOBs are level 0, they are kept on data pages with other record 
>> data. No separate
>>BLOB pages.


>>-- 
>>   WBR, SD.

Now i understand. Short blobs are on data pages, long are on different 
pages.
But:
Level 0 are on data pages
, Level 1 on different
, level 2 are ???

regards,
Karol Bieniaszewski









++

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] It works on FB 2.5 but does not work in FB 1.5

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

error message?

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, June 23, 2016 6:39 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] It works on FB 2.5 but does not work in FB 1.5

  


I created a view that works perfectly on FB 2.5, but now need to create the
same view on FB 1.5, but appears error in creation. Can help me?

CREATE OR ALTER VIEW ESPELHO(

DTMOVPROD,

TIPOMOV,

CODNAT,

CODVENDA,

CODCOMPRA,

CODPROD,

DESCPROD,

CODFISC,

TIPOMOVPROD,

DOCMOVPROD,

CODALMOX,

CODLOTE,

QTDMOVPROD,

PRECOMOVPROD,

ESTOQMOVPROD,

SLDMOVPRODAX,

TIPOPROD,

CUSTOMPMMOVPRODAX,

SLDMOVPROD,

CUSTOMPMMOVPROD,

CODMOVPROD,

CODEMPTM,

CODEMP,

CODFILIALTM,

CODFILIAL,

SALDO,

NOME)

AS

SELECT

MP.DTMOVPROD,

TM.TIPOMOV,

MP.CODNAT,

MP.CODVENDA,

MP.CODCOMPRA,

MP.CODPROD,

eq.descprod,

eq.codfisc,

MP.TIPOMOVPROD,

MP.DOCMOVPROD,

MP.CODALMOX,

MP.CODLOTE,

MP.QTDMOVPROD,

MP.PRECOMOVPROD,

MP.ESTOQMOVPROD,

MP.SLDMOVPRODAX,

EQ.TIPOPROD,

MP.CUSTOMPMMOVPRODAX,

MP.SLDMOVPROD,

MP.CUSTOMPMMOVPROD,

MP.CODMOVPROD,

MP.CODEMPTM,

TM.CODEMP,

MP.CODFILIALTM,

TM.CODFILIAL,

(MP.sldmovprod*MP.CUSTOMPMMOVPROD) SALDO,

CLI.NOMECLI

CODTIPOMOV

FROM EQMOVPROD MP, EQTIPOMOV TM

INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod

inner join vdvenda vd on mp.codvenda=vd.codvenda

inner join vdcliente cli on vd.codcli = cli.codcli

where MP.CODEMPTM=TM.CODEMP AND MP.CODFILIALTM=TM.CODFILIAL AND

MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codcompra is null

union all

SELECT MP.DTMOVPROD,

TM.TIPOMOV,

MP.CODNAT,

MP.CODVENDA,

MP.CODCOMPRA,

MP.CODPROD,

eq.descprod,

eq.codfisc,

MP.TIPOMOVPROD,

MP.DOCMOVPROD,

MP.CODALMOX,

MP.CODLOTE,

MP.QTDMOVPROD,

MP.PRECOMOVPROD,

MP.ESTOQMOVPROD,

MP.SLDMOVPRODAX,

EQ.TIPOPROD,

MP.CUSTOMPMMOVPRODAX,

MP.SLDMOVPROD,

MP.CUSTOMPMMOVPROD,

MP.CODMOVPROD,

MP.CODEMPTM,

TM.CODEMP,

MP.CODFILIALTM,

TM.CODFILIAL,

(MP.sldmovprod*MP.CUSTOMPMMOVPROD) SALDO,

FORN.RAZFOR

CODTIPOMOV

FROM EQMOVPROD MP, EQTIPOMOV TM

INNER JOIN EQPRODUTO eq on mp.codprod = eq.codprod

inner join cpcompra cp on mp.codcompra = cp.codcompra

inner join cpforneced forn on cp.codfor =forn.codfor

where MP.CODEMPTM=TM.CODEMP AND MP.CODFILIALTM=TM.CODFILIAL AND

MP.CODTIPOMOV=TM.CODTIPOMOV AND tipoprod='P' and mp.codvenda is null

;

---
Este email foi escaneado pelo Avast antivírus.
https://www.avast.com/antivirus

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

Re: [firebird-support] Schema pluses?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

but this feature is considered for FB4 – then i need to know why this is 
important feature..

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, June 15, 2016 4:16 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Schema pluses?

  

Firebird has no support for schemas, IIRC... or am I missing something?


Em dom, 12 de jun de 2016 às 18:18, 'livius' liviusliv...@poczta.onet.pl 
[firebird-support] <firebird-support@yahoogroups.com> escreveu:


  Hi,

  what are + and – with working with schema?
  What benefits are between 
  schema_name__table_name and real schema implementation?
  schema_name.table_name

  I near to always have worked without schema databases or with only one schema 
in schema supported databases.

  regards,
  Karol Bieniaszewski

Re: [firebird-support] Re: Schema pluses?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

thank you Dmitry – the last sentens show me some benefits
other sentenses show only complications for me.
we can write sql like
select * from table_name but really this is 
select * from schema.table_name

and if i write this in stored procedure – i suppose that “object_id” will be 
stored in blr(when schemas will be avaiable)
because if another user use this procedure and he have different default schema 
...

and also some task – user1 tell user2 “send me sql to retrive something” 
he send but user1 have different schema and in both schemas same tables exists 
...
I see only complication over benefits

reg

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, June 15, 2016 3:47 PM
To: firebird-support@yahoogroups.com 
Subject: [firebird-support] Re: Schema pluses?

  
13.06.2016 00:18, 'livius' wrote:
>
> what are + and – with working with schema?
> What benefits are between
> schema_name__table_name and real schema implementation?
> schema_name.table_name

Ability to have a default schema (per user, per connection). So that you 
may have multiple completely unrelated databases inside single physical 
database, and set up multiple "schema-less" users/applications of these 
different logical databases. E.g. one app works with USERS but actually 
it's CRM.USERS and a different app also works with USERS but actually 
it's HR.USERS. From the maintenance/administration POV it's a single 
database (configuration, backups, etc).

Dmitry

Re: [firebird-support] performance issue with firebird 3.0 embedded on linux

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

do you mean that you share one connection between multiple threads?

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, June 15, 2016 5:55 PM
To: firebird-support@yahoogroups.com 
Subject: RE: [firebird-support] performance issue with firebird 3.0 embedded on 
linux

  

Thanks, Helen. Please see my replies inline.  

I am sure it is not 3.0 specific, 2.5 is the same. and the main issue is 
scalability, sequential transaction performance is actually pretty good, 
comparable to ESE store on windows I was comparing firebird against.  but when 
running multiple transactions in parallel, ESE perf increased from 4 tx/sec to 
26 tx/sec (128 byte/record, 200 records per transaction, 6 transaction threads 
on 6 cores, which I expect the best, logical cpu/ht is not very useful 
performance-wise), while firebird decreased. These transactions do not 
write-conflict with each other. 

The basic flow of our code is like, first the main function creates a database 
connection, and spawns multiple threads, passing in the database connection. 
Each thread does this: create a transaction using the db connection, do 
insertion of data, and commit the transaction. Each thread measures its own 
elapsed time. sp;





To: firebird-support@yahoogroups.com
From: firebird-support@yahoogroups.com
Date: Wed, 15 Jun 2016 19:04:33 +1200
Subject: Re: [firebird-support] performance issue with firebird 3.0 embedded on 
linux

  
Wednesday, June 15, 2016, 5:58:39 PM, Karol B. wrote:

> test without details say nothing to me
> 1. Did you compare results with e.g. FB2.5 in on the same maschine with same 
> configuration (FBConfig)
My project migrated from FB2.5 to FB3.0. I am pretty sure it is not 3.0 
specific. 

> 2. What is your page size and type of HDD?
SSD. 512GB Samsung EVO 850. The file system is ext4, block size 4096. 


> 3. Do you have BOST feature enabled on CPU and HT? -- Boost?
Yes, Turbo boost and hyper threading are enabled. 

> 4. Did you compare results on GLOBAL TEMPORARY TABLE - i know this is not the 
> same but can show some info?
Could you give me some details about how to use GLOBAL TEMPORARY TABLE? 


> 5. How do you test this times about your threads and how do you start them?
I used the monolithic time in code i.e. clock_gettime(CLOCK_MONOTONIC, )  on 
each thread to calculate the elapsed time. should be high resolution. 


> 6. Exact version of FB
Firebird-3.0.0.32483-0

and 7. If on Windows, how did you go about configuring CpuAffinityMask?
- which processors did you intend to set it for and what was the value you 
configured?
It's on Linux, I read from document that multiple processors are by default 
enabled. but I anyway set CpuAffinityMask to 4095, but it seems no change to 
performance. 

- did you try to do this configuration whilst an engine instance was running, 
without restarting?
No. each time I change configuration, I restart the program. 

HB

[firebird-support] Schema pluses?

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

what are + and – with working with schema?
What benefits are between 
schema_name__table_name and real schema implementation?
schema_name.table_name

I near to always have worked without schema databases or with only one schema 
in schema supported databases.

regards,
Karol Bieniaszewski

Re: [firebird-support] FOR UPDATE [OF ...]] WITH LOCK

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

thanks you Paul

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Thursday, June 9, 2016 11:03 AM
To: firebird-support@yahoogroups.com 
Subject: RE: [firebird-support] FOR UPDATE [OF ...]] WITH LOCK

  


<>

"The clause can take an optional OF  to target updates
at specific cursor columns"

The Firebird Book Vol 2 p220

Regards
Paul

Re: [firebird-support] Stored Procedures Execution Plan

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

you must convert any procedure - for which you need plan - to execute block 
format

if you are interested in plan for proc 1 then you need proc 1 execte block
if this proc1 use proc2 you need to make proc2 as execute block..

regards,
Karol Bieniaszewski

From: mailto:firebird-support@yahoogroups.com 
Sent: Wednesday, June 1, 2016 2:27 PM
To: firebird-support@yahoogroups.com 
Subject: Re: Re: [firebird-support] Stored Procedures Execution Plan

  

Hi,




One problem with this approach (changing SP's to execute block) is that you 
still have the possibility of doesn't getting called SP plans (SP's called by 
the SP which you are converting to an execute block).

Att.
João Paulo



--
  DE: "liviuslivius liviusliv...@poczta.onet.pl [firebird-support]" 

  Para: "firebird-support@yahoogroups.com"  
  Enviadas: Quarta-feira, 1, Junho 2016 7:27:34
  Assunto: Re: Re: [firebird-support] Stored Procedures Execution Plan



  Hi,

  yes but this was "wrong"
  Consider this:
  inside procedure you have 5 different statements
  and you run query and only two of them are reached - do you really need to 
see all 5?

  Ok - now you got only "natural" word for procedure - i do not know if this is 
better now..
  But if you need plan for procedure - "change it" to execute block and you got 
all statements plan

  regards,
  Karol Bieniaszewski




  W dniu 2016-05-31 19:39:56 użytkownik Gabriel Frones grfro...@gmail.com 
[firebird-support]  napisał:
  
It was sent and received... but this is working for me out of the box in FB 
2.5.4. I just write the select * from SP and ask for plan and it gives me the 
plan for the inside statements. Haven't tested on non-selectable SPs.

Em qua, 25 de mai de 2016 às 08:20, João Paulo Carvalho 
joao_paul...@yahoo.com [firebird-support]  
escreveu:


  Hi.


  Since this was my first post in this list, I'm not sure if it was sent.

  If you already get it, or this is not the right place to discuss this 
kind of topic, I would be very grateful if you tell me where is.

  Att.
  João Paulo





DE: "joao_paul...@yahoo.com [firebird-support]" 

Para: firebird-support@yahoogroups.com 
Enviadas: Sexta-feira, 20, Maio 2016 15:54:07
Assunto: [firebird-support] Stored Procedures Execution Plan


  

Hi.


I would like to know if it is possible to display the plan of stored 
procedure statements.

I'm interested in the plan of the statements inside the stored 
procedure (SP), not the plan just informing that I'm executing an SP.

We are running also selectable SP's.

Att.

João Paulo

Re: [firebird-support] Question about few Firebird details

['livius' liviusliv...@poczta.onet.pl [firebird-support]]

Hi,

thanks you and others for correction
now “44” i am near to understand.

45. i must test – but how look sort for your sample?
ORDER BY T1.iSort 
and difference is because of existence of big varchar - BIGSTARTING ?
If yes then something must be wrong – this sould only sort pointers and 
inrelevant data should not affect speed.
But i do not know how this work internally – did you compare this with others 
RDBMS?

regards,
Karol Bieniaszewski


From: mailto:firebird-support@yahoogroups.com 
Sent: Monday, May 23, 2016 9:32 PM
To: firebird-support@yahoogroups.com 
Subject: Re: [firebird-support] Question about few Firebird details

  

Hi Karol,

44. This option turn off individual savepoints. Our tests proof that gives 
slight  increase in case of massive inserts or updates. Use it with caution, 
since the rollback will be forced (through TIP), so if you insert less than 50k 
records, there could be difference with and without NO_AUTO_UNDO  - Firebird 
will be not able to transform rollback into commit with savepoints. If you 
insert more than 50k records (actually 50-60k, it depends on memory, not exact 
##, if I remember correctly), rollback will be done through TIP anyway. 

45. Try it with table like 
CREATE TABLE T1(i1 integer not null primary key, iSort integer, BIGSTARTING 
VarchaR(2000));
insert there 10 mln records and do 2 queries as in the example. It will take 10 
mins, and and you'll see the difference by yourself.

Regards,
Alexey Kovyazin
IBSurgeon




  Hi,

  https://ib-aid.com/en/articles/45-ways-to-speed-up-firebird-database/
  i have few questions:
  44. Use NO_AUTO_UNDO - what happend when i do rollback? I do not understand 
what really do this option..
  30. Use derived tables to optimize SELECT with ORDER BY/GROUP BY - 
SELECT T.FIELD_KEY, T.FIELD1, T.FIELD2, ... T.FIELD_N
FROM (SELECT FIELD_KEY FROM T ORDER BY FIELD2) T2
JOIN T ON T.FIELD_KEY = T2.FIELD_KEYwhy this can be faster then?
SELECT FIELD_KEY, FIELD1, FIELD2, ... FIELD_N
FROM T
ORDER BY FIELD2Firebird sort whole resultset of "sorted key - Field2" or do 
different work?
  I see in FB3 plan sort width and it looks like not true but?

  regards,
  Karol Bieniaszewski