[firebird-support] Number of the Next Transaction

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Windows 7, 32 bits, SuperServer, Firebird 2.5.2

When nobody was using a database, writing the command GSTAT -h I saw the
following numbers:
OIT = 30287
OAT = 30288
OST = 30288
NT = 30289

After a cycle backup/restore (using GBAK -b and GBAK -c) in the new
database the numbers were:
OIT = 1
OAT = 2
OST = 2
NT = 304

Why the Next Transaction is 304? I was expecting 3

After connecting to the new database with ISQL.EXE and writing a SELECT
with its COMMIT, the numbers were:
OIT = 306
OAT = 307
OST = 307
NT = 309

Why?

I can not understand those numbers.

Why the Next Transaction was 304 and not 3?
Why after a SELECT the OIT, OAT and OST had changed so much?

Someone can explain me?

Thanks in advance.

Greetings.

Walter.

Re: [firebird-support] Re: Number of the Next Transaction

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much for your answer Dmitry. However my first question
remains: why after a cycle backup/restore the Next Transaction was 304 and
not 3?

My understanding so far is that after a cycle backup/restore the ID of all
transactions in the backup is put in 1. So, the Next Transaction would be 3
or a number very close to 3.

Why it was 304?

Greetings.

Walter.






On Sun, Oct 12, 2014 at 3:47 PM, Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> 12.10.2014 23:25, 'Walter R. Ojeda Valiente' wrote:
>
> > I can not understand those numbers.
> >
> > Why the Next Transaction was 304 and not 3?
> > Why after a SELECT the OIT, OAT and OST had changed so much?
> >
> > Someone can explain me?
>
> Without connections to the database, OAT/OIT/OST means virtually
> nothing. They may be outdated as nothing depend on them. Only the Next
> Transaction counter is always actual. However, when a new transaction is
> started, OAT/OIT/OST are recalculated and updated on the header page,
> hence they start to match the Next Transaction counter.
>
> This explanation is a bit simplified but you should get the idea.
>
> Dmitry
>
>  
>

Re: [firebird-support] Re: Number of the Next Transaction

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much again Dmitry.

I now understand. It is very clear now.

Greetings.

Walter.




On Sun, Oct 12, 2014 at 4:58 PM, Dmitry Yemanov dim...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> 13.10.2014 00:22, 'Walter R. Ojeda Valiente' wrote:
>
> > Thank you very much for your answer Dmitry. However my first question
> > remains: why after a cycle backup/restore the Next Transaction was 304
> > and not 3?
> >
> > My understanding so far is that after a cycle backup/restore the ID of
> > all transactions in the backup is put in 1. So, the Next Transaction
> > would be 3 or a number very close to 3.
>
> Nope. The Next Transaction number is reset to 1 when a new database is
> created. This is a very beginning of the restore process. Then gbak
> restores metadata and data, and it can be done in multiple transactions,
> depending on switches. IIRC, -o[nce] starts transaction per every
> restored table and -v[erbose] starts transaction per every restored
> index. There may be other side effects I'm not aware of.
>
> Dmitry
>
>  
>

Re: [firebird-support] Re: Gbak Restore - SuperServer vs. SuperClassic

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hmmm, database dialect is 1.

No issues with that?

Greetings.

Walter.


On Mon, Nov 17, 2014 at 7:41 AM, 'Thomas Steinmaurer' t...@iblogmanager.com
[firebird-support]  wrote:

>
>
> > Isnt the transaction per second wrongly calculated?
>
> Oops, sorry!!! I have been one year off by using 2014 instead of 2013.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
> > DB is created november 2013.
> > Approcimatly 365 days ago.
> > Transaction is at 96.356.705.
> >
> >
> > Per day is approx: 263.990
> > Per hour: 10.999
> > Per minut: 183.
> > Per second: 3
> >
> >
> >
> >
> >
>
>  
>

Re: [firebird-support] Selecting arbitrary records in an arbitrary order

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Caroline

If you can read Spanish, then at this article:

http://firebird21.wordpress.com/2013/11/10/enviando-y-recibiendo-una-cantidad-variable-de-parametros-en-los-stored-procedures/

you will see a technique for achieving that you want.

Greetings.

Walter.


On Wed, Nov 19, 2014 at 5:50 PM, Caroline Beltran
caroline.d.belt...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> I hope that someone can advise how I can efficiently arbitrarily select
> data from a table.  I am provided with a list of IDs to display, for
> example:
>
>
> Employee ID Numbers: 1032, 624, and 2841.
>
>
> This would be the output (in the same order as the list above):
>
>
> Employee ID FName LName
>
> --  - --
>
> 1032 Frank  Burns
>
> 624   Jill  Applegate
>
> 2841 Angie  Cullens
>
>
> The easiest and most obvious way to get this output is by using the SELECT
> statement 3 times:
>
>
> SELECT id, fname, lname FROM employees WHERE id = 1032
>
> SELECT id, fname, lname FROM employees WHERE id = 624
>
> SELECT id, fname, lname FROM employees WHERE id = 2841
>
>
> Please keep in mind that there is no criteria other than the ID and order
> provided in the list above.
>
>
> Although this works, I would like to find the most efficient way to do
> this.  My first though was to create a temporary table where I insert the
> 3 IDs and link them to the Employees table but that looks even less
> efficient.
>
>
> What came to mind next was a STORED PROCEDURE.
>
>
> What I would like to know is if it is possible to pass a variable amount
> of IDs and have the procedure parse each id and return the output as shown
> above?
>
>
> If so, would this be the most efficient way to have Firebird fulfill this
> requirement?
>
>
> Thank you
>
>  
>

Re: [firebird-support] Why this similar to is wrong?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello

I use the following SELECT when want to validate an e-mail address and
works fine for me:

SELECT
   IIF('em...@testdomain.com' SIMILAR TO
'[[:ALNUM:]-*_*.*]*@[[:ALNUM:]-*_*.*]*.[[:ALNUM:]-*_*.*]*', 'ok', 'fail')
FROM
   RDB$DATABASE

Of course, that is not perfect, but works.

Greetings.

Walter.


On Tue, Nov 25, 2014 at 9:29 AM, sirhamac...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> select
>
>iif('em...@testdomain.com' similar to
> '([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+)','ok','fail')
>
> from rdb$database
>
>
> Says 'invalid string' and 'invalid pattern', but the pattern it´s the same
> in other languages for validate email address.
>
>
>  
>

Re: [firebird-support] Why this similar to is wrong?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Yet more simple:

SELECT
   IIF('em...@testdomain.com' SIMILAR TO '[[:ALNUM:]-_.]*@[[:ALNUM:]-_.]*',
'ok', 'fail')
FROM
   RDB$DATABASE

Greetings.

Walter.


On Tue, Nov 25, 2014 at 10:48 AM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Hello
>
> I use the following SELECT when want to validate an e-mail address and
> works fine for me:
>
> SELECT
>IIF('em...@testdomain.com' SIMILAR TO
> '[[:ALNUM:]-*_*.*]*@[[:ALNUM:]-*_*.*]*.[[:ALNUM:]-*_*.*]*', 'ok', 'fail')
> FROM
>RDB$DATABASE
>
> Of course, that is not perfect, but works.
>
> Greetings.
>
> Walter.
>
>
> On Tue, Nov 25, 2014 at 9:29 AM, sirhamac...@gmail.com [firebird-support]
>  wrote:
>
>>
>>
>> select
>>
>>iif('em...@testdomain.com' similar to
>> '([_a-zA-Z\d\-\.]+@[_a-zA-Z\d\-]+(\.[_a-zA-Z\d\-]+)+)','ok','fail')
>>
>> from rdb$database
>>
>>
>> Says 'invalid string' and 'invalid pattern', but the pattern it´s the
>> same in other languages for validate email address.
>>
>>
>>  
>>
>
>

Re: [firebird-support] Question about a package

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

He also can have several versions of Firebird running on the same computer.
Of course using different ports.

Greetings.

Walter.


On Thu, Dec 4, 2014 at 5:06 AM, Markov Dmitri markovdmi...@yahoo.com
[firebird-support]  wrote:

>
>
> Hi!
> I can't answer on your question, but did you try use zip-install?
> If I right you can stop FB, replace current files of FB by files from
> archive and then start FB(I do such things when test one of nightly
> snapshots of FB). Not .MSI but it may work for you.
>
> 04.12.2014 11:41, 'Romain BOUSSY' r.bou...@teractem.fr [firebird-support]
> пишет:
>
>
>
> Hi !
>
> I'm a student in IT and in my company, i have to install your software via
> GPO. But i've a problem... The GPO only work with .MSI and not .EXE so i
> want to know if it's possible for you to release a.MSI of Firebird 2.5.3 on
> your website or send it to me via email ?
>
> Thanks a lot
>
> Best regards
>
> [www.teractem.fr] 
>
> Romain BOUSSY - Adjoint administrateur réseau
> Tél. +33(0) 4 50 08 31 00
> r.bou...@teractem.fr
>
> [www.annecyfrenchtech.fr]
> 
>
> [Non-text portions of this message have been removed]
>
>
> --
> Regards,
> Markov Dmitri
>
>  
>

Re: [firebird-support] Re: performance of subselect with group by

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Well done Set

Greetings.

Walter.

On Tue, Dec 16, 2014 at 4:30 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support] <
firebird-support@yahoogroups.com> wrote:
>
>
>
> >> I don't think there is any simple way to make a delete with a
> >> subselect as the only part of a where clause perform great on
> >> largish tables. That is, using EXECUTE BLOCK (which doesn't exist on
> >> older Firebird versions) should perform OK:
> >>
> >> execute block as
> >> declare variable id integer;
> >> begin
> >> for select min(t.Id) FROM test t
> >> group by t.reference, t.key
> >> having count(*) > 1
> >> into :id do
> >> delete from test where Id = :id;
> >> end
> >>
> >> HTH,
> >> Set
> >>
> > well, that's not the answer I wanted to get, but I've to deal with
> > that fact.
> >
> > I've to check wether EXECUTE STATEMENT can execute EXECUTE
> > BLOCKs, as the DELETE Statement is build dynamically in a proc.
>
> Interesting, Björn, I'd expect execute block to work wherever a query
> could be used, but hadn't tried it inside EXECUTE STATEMENT until you said
> you would have to try. However, it made me curious, so I wrote:
>
> execute block returns(myanswer varchar(32)) as
> declare variable es varchar(500);
> begin
> es = 'execute block returns(ma varchar(32)) as ' ||
> ' declare variable es2 varchar(500); ' ||
> ' begin ' ||
> ' es2 = ''execute block returns(ma2 varchar(32)) as ' ||
> ' begin ' ||
> ' select Hooray from rdb$database into ma2; ' ||
> ' suspend; '||
> ' end''; ' ||
> ' execute statement es2 into :ma; ' ||
> ' suspend; ' ||
> ' end';
> execute statement es into :myanswer;
> suspend;
> end
>
> just to see if it worked. It actually returned Hooray, so yes, EXECUTE
> STATEMENT can execute EXECUTE BLOCK and they can even be nested within each
> other!
>
> Set
>  
>

Re: [firebird-support] Re: Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Bruce, if I understand transactions rightly the things are the following:

- If you are updating or deleting just one row, then no conflict can happen
with your settings. But if you want to update or delete several rows then
conflicts can happens.

- Transaction T1 starts, transaction T2 starts, transaction T1 wants to
update some rows, transaction T2 wants to update some different rows. No
problems until now. Transaction T2 wants to update (and therefore blocks) a
row and after that transaction T1 wants to update that row. Deadlock
because transaction T1 is older than transaction T2.

If transaction T1 blocks a row and after that transaction T2 wants to
update that row then transaction T2 will wait. But if transaction T2 blocks
a row and after that transaction T1 wants to update that row will have a
deadlock.

Greetings.

Walter.


On Wed, Dec 24, 2014 at 10:39 AM, brucedickin...@wp.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Thank you Hugo, I was bit reluctant to use WITH LOCK since documentation
> discourages its usage.
> I've tried it but it did not help.
>
> Documentation says:
>
> "The main purposes of explicit locks are (1) to prevent expensive handling
> of update conflict errors in heavily loaded application"
>
> this is the exact reason I try to avoid conflicts. My threads can wait but
> they should not raise exceptions.
>
> I always though that it is possible to avoid such conflicts on the
> database level.
>  
>

Re: [firebird-support] Re: Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Bruce

Yes, transaction T1 can block a row and transaction T2 will wait. But if
transaction T2 blocks a row then transaction T1 can not wait and dead.

Greetings.

Walter.


On Thu, Dec 25, 2014 at 7:27 AM, brucedickin...@wp.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Walter,
>
> thank you very much for trying to explain that to me. Unfortunately I've
> got lost somewhere in the middle of your response...
>
> >>- If you are updating or deleting just one row, then no conflict can
> happen with your settings. But if you want >>to update or delete several
> rows then conflicts can happens.
>
> In my test case I have two threads (or applications, I do not think that
> does have matter) which are constantly updating the same row.
>
> >>- Transaction T1 starts, transaction T2 starts, transaction T1 wants to
> update some rows, transaction T2 >>wants to update some different rows. No
> problems until now.
>
> This is not my case because my transactions are working on the same row
> but so far I understand that, and I agree with you. Different rows, no
> deadlock should happen.
>
> >>Transaction T2 wants to update (and therefore blocks) a row and after
> that transaction T1 wants to update >>that row. Deadlock because
> transaction T1 is older than transaction T2.
>
> I do not understand, why deadlock? Why T1 does not wait for T2 to
> commit/rollback?
>
> >>If transaction T1 blocks a row and after that transaction T2 wants to
> update that row then transaction T2 will >>wait. But if transaction T2
> blocks a row and after that transaction T1 wants to update that row will
> have a >>deadlock.
>
> I do not understand this, why there will be a deadlock? Because the older
> transaction could not be first to block the row?
>
> I thought I've had all figured out:(
>
>
>
>  
>

Re: [firebird-support] Re: Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Strange, I will test it, too. Maybe I have a misconcept.

Greetings.

Walter.


On Thu, Dec 25, 2014 at 11:36 AM, brucedickin...@wp.pl [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Walter,
>
> I've tested what you've said. But it does not matter which transactions
> blocks record first (older or "younger"). When I do it slowly..it works ok.
> But if I start to spam those inserts with many inserts per second then I
> got deadlock.
>  
>

Re: [firebird-support] Re: Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Yes, you are right.

I had a misconcept. Sorry.

Greetings.

Walter.


On Thu, Dec 25, 2014 at 12:00 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Strange, I will test it, too. Maybe I have a misconcept.
>
> Greetings.
>
> Walter.
>
>
> On Thu, Dec 25, 2014 at 11:36 AM, brucedickin...@wp.pl [firebird-support]
>  wrote:
>
>>
>>
>> Walter,
>>
>> I've tested what you've said. But it does not matter which transactions
>> blocks record first (older or "younger"). When I do it slowly..it works ok.
>> But if I start to spam those inserts with many inserts per second then I
>> got deadlock.
>>  
>>
>
>

Re: [firebird-support] Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Ann

Maybe I am not understanding correctly, but:

*Case 1:*

   - Transaction T1 starts with settings: READ WRITE WAIT READ COMMITTED
   - Transaction T1 updates a record which ID is 23
   - Transaction T2 starts with settings: READ WRITE WAIT READ COMMITTED
   - Transaction T2 updates the same record which ID is 23
   - Transaction T2 is waiting, and waiting, and waiting
   - Transaction T1 COMMIT
   - Transaction T2 COMMIT
   - A SELECT shows the changes that transaction T2 did

*Case 2:*

   - Transaction T1 starts with settings: READ WRITE WAIT READ COMMITTED
   - Transaction T2 starts with settings: READ WRITE WAIT READ COMMITTED
   - Transaction T2 updates a record which ID is 23
   - Transaction T1 updates the same record which ID is 23
   - Transaction T1 is waiting, and waiting, and waiting
   - Transaction T2 COMMIT
   - Transaction T1 COMMIT
   - A SELECT shows the changes that transaction T1 did

So, I can not understand your phrase: "However the rules for update
conflicts were not changed at the same time, so even if you can see a
change that's committed now but wasn't when you started, you still can't
update that record."

Because I can update the record.

Greetings.

Walter.


On Fri, Dec 26, 2014 at 6:15 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
>
> > On Dec 24, 2014, at 3:22 AM, brucedickin...@wp.pl [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
> >
> > I have two threads which constantly and at the same time are writing to
> this table:
> >
> > UPDATE OR INSERT INTO PARAMS (NAME) VALUES(:P_NAME) MATCHING (NAME)
> RETURNING ID;
> >
> >
> > I've set my transaction parameters like this:
> >
> > FtraMain.TRParams.Add('isc_tpb_write');
> > FtraMain.TRParams.Add('isc_tpb_read_committed');
> > FtraMain.TRParams.Add('isc_tpb_wait');
> > FtraMain.TRParams.Add('isc_tpb_no_rec_version');
> >
> > As far as I understand, such configuration should prevent deadlock
> exception to occur. However, deadlock still occurs from time to time:
> >
> > Deadlock.
> > Deadlock.
> > Update conflicts with concurrent update.
> > Concurrent transaction number is 57258.
> >
>
> The "Deadlock" error is somewhat misleading. This is not a classic
> deadlock of the sort that databases that implement lock-based concurrency
> get. However, the solution is the same as for a deadlock (i.e. roll back
> and retry your update) so at a high level, deadlock isn't a bad description.
>
> What you're seeing is Firebird's way of avoiding dirty writes in a system
> with multiple record versions. The rule is that if the most recent version
> of a record was not committed when your transaction started, then you can't
> update that record. In "concurrency" mode, which provides a stable snapshot
> of the database, the rule is the minimum necessary to avoid losing
> concurrent writes.
>
> "Read_committed" mode was added later to meet some programmers'
> expectation that a transaction would always see the most recently committed
> version of a record, and to hell with consistency. However the rules for
> update conflicts were not changed at the same time, so even if you can see
> a change that's committed now but wasn't when you started, you still can't
> update that record.
>
> Good luck,
>
> Ann
>  
>

Re: [firebird-support] Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Vlad, your explanation is very good, but I still don't understand
something: Why before step 1 the engine does not blocks the record avoiding
such problem?

Greetings.

Walter.


On Sat, Dec 27, 2014 at 5:19 AM, hv...@users.sourceforge.net
[firebird-support]  wrote:

>
>
>   Update, internally, consists of following steps (simplified) :
>
> 1. read record
> 2. evaluate new record
> 3. write new record into data page
>
> really, it is a more complex, but for now we interesting only in steps
> above.
>
> At step 1, read-committed (RC) transaction waits for commit or rollback of
> concurrent active transaction and thus reads only comitted record version.
>
> At step 3, transaction re-reads original record version and must ensure
> that it is still the same record version as it was at step 1 (else we will
> silently replace other's work). If current record version was changed since
> step 1, update_conflict error will be reported - exactly as you see.
> Again, it is simplified description.
>
>   Note, time slice between steps 3 and 1 usually very small. When you test
> concurrent updates in isql, you can't reproduce such scenario. But in real
> life, when you have a lot of concurrent updates by applications it becomes
> much more probably and really happens sometimes.
>
> Hope it helps,
> Vlad
>  
>

Re: [firebird-support] Re: Deadlock exception occurs but it shouldn't?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you for your explanation Vlad, the proccess is more clear for me now.

Greatings.

Walter.


On Mon, Dec 29, 2014 at 4:55 AM, hv...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> Walter,
>
> there are few reasons:
>
> a) MVCC engine never block records - it just can't :)
>
> b) the only way to disable concurrent updates of the same record is...
> update :)
>   More exactly: engine must mark primary record version by current
> transaction and
> it will make concurrent writes wait or fail.
>
> c) therefore, i can't imagine how it is ever possible to block record
> *before* reading it
>
> d) Firebird allows developer to avoid such problem (see below) but doesn't
> force developer to do it.
>
>   To avoid exactly this kind of issues Firebird have ablity to "lock"
> record while reading (i enclosed
> "lock" into brackets because there is no true record locks in Firebird,
> see (a) above).
>
>   So, to "lock" record engine must update it and there is special
> statement to read-and-update:
> SELECT ... WITH LOCK
>
>   So, if you really don't want to handle update conflicts and decided to
> wait for concurrent updates,
> you should issue SELECT ... WITH LOCK before UPDATE (and, of course, use
> "read-commited no
> record version" transaction isolation mode with non-zero wait timeout).
>
> Regards,
> Vlad
>
> ---In firebird-support@yahoogroups.com, 
> wrote :
>
> Vlad, your explanation is very good, but I still don't understand
> something: Why before step 1 the engine does not blocks the record avoiding
> such problem?
>
> Greetings.
>
> Walter.
>
>
> On Sat, Dec 27, 2014 at 5:19 AM, hvlad@... [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>
>
>   Update, internally, consists of following steps (simplified) :
>
> 1. read record
> 2. evaluate new record
> 3. write new record into data page
>
> really, it is a more complex, but for now we interesting only in steps
> above.
>
> At step 1, read-committed (RC) transaction waits for commit or rollback of
> concurrent active transaction and thus reads only comitted record version.
>
> At step 3, transaction re-reads original record version and must ensure
> that it is still the same record version as it was at step 1 (else we will
> silently replace other's work). If current record version was changed since
> step 1, update_conflict error will be reported - exactly as you see.
> Again, it is simplified description.
>
>   Note, time slice between steps 3 and 1 usually very small. When you test
> concurrent updates in isql, you can't reproduce such scenario. But in real
> life, when you have a lot of concurrent updates by applications it becomes
> much more probably and really happens sometimes.
>
> Hope it helps,
> Vlad
>
>
>  
>

Re: [firebird-support] Unlock record

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hm, maybe I am doing something wrong, but

- I open 2 instances of ISQL.EXE

- In ISQL1 I write: SELECT * FROM CLIENTS;

- In ISQL2 I write: SELECT MON$TIMESTAMP, MON$TRANSACTION_ID FROM
MON$STATEMENTS;

- In ISQL1 I write: COMMIT;

- In ISQL2 I write: SELECT MON$TIMESTAMP, MON$TRANSACTION_ID FROM
MON$STATEMENTS;

- In ISQL1 I write: SELECT * FROM CLIENTS;

- In ISQL2 I write: SELECT MON$TIMESTAMP, MON$TRANSACTION_ID FROM
MON$STATEMENTS;

In all cases, ISQL2 shows me the same data, nothing change. So I can not
see the transaction's timestamp or Id of the transactions started by ISQL1.

ISQL2 absolutly ignores that happens in the ISQL1 instance.

Is there a way to know in ISQL2 the transaction's data started by ISQL1?

Version 2.5.2, SuperServer, Windows 7

Greetings.

Walter.


On Thu, Jan 22, 2015 at 1:50 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> >I looked in a database, and in MON$STATEMENTS tabla I have the following
> fields:
>
> MON$STATEMENT_ID
> MON$ATTACHMENT_ID
> MON$TRANSACTION_ID
> MON$STATE
> MON$TIMESTAMP
> MON$SQL_TEXT
> MON$STAT_ID
>
> >How can I detect which is the record I have to delete to unlock my
> record? This is the only table I have to modify?
>
> I typically just look at the mon$sql_text with a mon$timestamp that seems
> to be the correct one.
>
> HTH, Set
>
>  Mensaje original ---
> >> May be I can modify or delete some record in any MON$ table or do some
> other thing?
>
> >I typically locate and delete records from MON$STATEMENTS. Mind you, that
> is 2.5 and typically when I have a long-
> >running query. Don't know whether deleting from this table is possible or
> safe with 2.1 or sensible WITH LOCK.
>  
>

[firebird-support] Problem with a trigger

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

In a before insert or update trigger I have the following cases:

*Case 1:*
   IF (NEW.MVC_TIPMOV = 'SVT' AND INSERTING AND NEW.MVC_NRODOC = '1') THEN
BEGIN
   END

*Case 2:*
   IF (NEW.MVC_TIPMOV = 'SVT' AND INSERTING OR UPDATING AND NEW.MVC_NRODOC
= '1') THEN BEGIN
   END

*Case 3:*
   IF (NEW.MVC_TIPMOV = 'SVT' AND (INSERTING OR (UPDATING AND
NEW.MVC_NRODOC = '1'))) THEN BEGIN
   END

No problem with Case 1 and with Case 2, they both compiles well. However
Case 3 doesn't compile. Why?

Why it is not possible to use parentesis?

Firebird 2.5.2, SuperServer

Greetings.

Walter.

Re: [firebird-support] Multiple WHERE clauses

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Sean:

Which is the difference between it:

WHERE 1 =
CASE
WHEN (Param <> 0) THEN 1
WHEN (Some OtherTest) THEN 1
WHEN (Yet another Test) THEN 1
ELSE 0
END

and it:

Param <> 0 OR SomeOtherTest OR YetAnotherTest

?

Because I get the same result with both ways.

Greetings.

Walter.


On Wed, Feb 25, 2015 at 12:51 AM, Venus Software Operations
venussof...@gmail.com [firebird-support] 
wrote:

>
>
>
> On 23/02/2015 01:27 pm, Svein Erling Tysvær
> svein.erling.tysv...@kreftregisteret.no [firebird-support] wrote:
>
>
>
> You may consider
>
> WITH TMP(My72, cFrom, cTo, dValueFrom, dValueTo) as
> (SELECT CAST(:My72 as integer), US1.dValue, US2. dValue FROM RDB$DATABASE
> LEFT JOIN dSetValue('Current Year', 'From') US1 ON (1=1)
> LEFT JOIN dSetValue('Current Year', 'To') US2 ON (1=1))
> SELECT 
> FROM TMP t
> CROSS JOIN tDevelopment d
> WHERE d.iID = t.My72 OR
> (t.MyParam is null AND CAST(d.tDT AS DATE) BETWEEN t.dValueFrom,
> t.dValueTo)
>
> I’ve assumed the dSetValue procedure returns 0 or 1 record. I did this
> because the way you wrote things, the procedure would be executed twice for
> every row in tDevelopment, something not necessary unless result of the
> procedure depends on values in tDevelopment (which it doesn’t in your case,
> since ‘Current Year’, ‘From’ and ‘To’ are constants).
>
> HTH,
> Set
>
>
> Thanks a mill Set, I tried out your full suggestion.  It works the way I
> wanted it to, see below.
>
> WITH cteTemp(iID, dFromDt, dToDt) AS
> (SELECT CAST(72 as integer) AS iID,
> US1.dValue,
> US2.dValue
> FROM RDB$DATABASE
> LEFT JOIN dSetValue('Current Year', 'From') US1 ON (1 = 1)
> LEFT JOIN dSetValue('Current Year', 'To') US2 ON (1 = 1))
>
> SELECT ...
> FROM cteTemp
> CROSS JOIN tDevelopment
> JOINs ...
> WHERE (cteTemp.iID > 0 AND tDevelopment.iID = cteTemp.iID)
> OR (cteTemp.iID = 0 AND CAST(tDevelopment.tDT AS DATE) BETWEEN
> cteTemp.dFromDt AND cteTemp.dToDt)
> ...
>
> Kind regards
> Bhavbhuti
>
>  
>

Re: [firebird-support] Get/set context in derived table context

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

If you can read Spanish, this article shows several methods for having row
numbers in the SELECT.

https://firebird21.wordpress.com/2013/05/13/numerando-las-filas-de-un-select/

Greetings.

Walter.


On Thu, Mar 19, 2015 at 5:28 AM, masb...@za-management.com
[firebird-support]  wrote:

>
>
> Hi all,
> I have the need to implement something like a row number and read the last
> thread about the autoincrement number which also led me to this FAQ:
> http://www.firebirdfaq.org/faq343/
>
>
> I now tried something like this:
>
> select
> cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as
> row_number,
> rdb$set_context('USER_TRANSACTION', 'row#2',
> coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0)
> + 1) as SET_CONTEXT,
> 
> from mytable
>
> Which works fine. The first thing I now did is to create a derived table
> to get rid of the SET_CONTEXT part. I now experienced something very
> strange to me.
>
> When I did this:
>
> select s.* from
> (select
> cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as
> row_number,
> rdb$set_context('USER_TRANSACTION', 'row#2',
> coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0)
> + 1) as SET_CONTEXT,
> 
> from mytable) s
>
> all is fine and row numbers are fine.
>
> But when I explicitely call the individual rows like this;
>
> select
> s.row_number,
> s.
> from
> (select
> cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer) as
> row_number,
> rdb$set_context('USER_TRANSACTION', 'row#2',
> coalesce(cast(rdb$get_context('USER_TRANSACTION', 'row#2') as integer), 0)
> + 1) as SET_CONTEXT,
> 
> from mytable)
>
> then the row numbers have all the same value namely the last counter
> position (highest last value).
>
> Am I doing something wrong?
>
> Kind regards Christian
>
>
>
>  
>

Re: [firebird-support] Re: Awaiting Garbage Collector

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Alexey

Your presentation is an excellent work, as ever.

I had wrote an article in my blog about it:

https://firebird21.wordpress.com/2015/03/23/presentacion-sobre-transacciones-de-alexey-kovyazin/

You had explained very well everything and all can be easily understood.
Thank you very much for such presentation.

I have just one doubt, and it is with page 46.

Can you explain me that page, please?

Thanks in advance.

Walter.








On Mon, Mar 23, 2015 at 8:27 AM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
>
> Hi,
>
> Unfortunately, your experiments are not useful at all, since you are using
> complex GUI tools, which run background queries in the frames of implicit
> transactions to get metadata information, etc.
> If you really want to do clean experiments with Firebird transactions, use
> only isql.exe, and run queries to your tables and MON$ tables there.
>
> Also, look into this presentation
> http://www.slideshare.net/ibsurgeon/3-how-transactionswork
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
> Regarding the Awaiting GC issue I've done another test:
>
>  1- Connect to the database in a test environment, isolated from the rest
> of users from production environment. with IBExpert or another database
> manager. Only one connection.
>
>  2- Monitor tha database with Sinatica Monitor to get number of active
> transactions, attachments and statements. At this moment only 1 connection
> is showed in Sinatica. 2 statements that are querys to system tables, I
> supose form IBExpert connection, 0 awaiting GC and 1 awaiting Sweep.
>
>  3- Run a query SELECT * FROM CALENDAR WHERE EVENT_ID=132465 which only
> returns a record. I did not COMMIT that.
>
>  4- Now SINATICA shows how AWAITING GC is increasing by 1 or 2 every
> second. In a minute it shows more than 100 Awaiting GC.
>
>  5- COMMIT and now SINATICA shows AWAITING GC is again 0.
>
>  I did the same thing with EMS SQL MANAGER and the result is the same.
> Leaving an interesting transaction on a single record table generates a big
> amount of garbage.
>
>  Is that normal?
>
>
>  
>

Re: [firebird-support] Re: Awaiting Garbage Collector

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hi Alexey

Yes, that's right, I can not understand well the page 46. I suppose that R1
means "record version 1", R2 means "record version 2", etc.

Tx16 did the INSERT?
Tx12 can do a COMMIT although it had started before Tx16 and Tx18 is still
active?
Tx25 can change what Tx14 did although Tx14 is still active?

These things had confused me.

Of course, I think that I am not understanding rightly the graph, that's
why some words can help to do it clear.

Greetings.

Walter.


On Wed, Mar 25, 2015 at 5:10 AM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
>
> Hi Walter,
>
> >I have just one doubt, and it is with page 46.
>
> Do you mean "Record versions visibility"?
>
> Regards,
> Alexey
>
>  
>

Re: [firebird-support] Re: Awaiting Garbage Collector

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much for your answer Alexey, I was very bussy these days.
Looking at the page 46 it seems more understandable now.

Greetings.

Walter.


On Wed, Mar 25, 2015 at 2:02 PM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
>
> Hi Walter,
>
> On slide 46 of http://www.slideshare.net/ibsurgeon/3-how-transactionswork
> we consider how transaction 20 view record versions.
> It's important to note that transaction 20 is a snapshot.
>
>  >Tx16 did the INSERT?
> Yes, it created original version of Record 1.
>
> >Tx12 can do a COMMIT although it had started before Tx16 and Tx18 is
> still active?
>
> Sure, why not?
>
>  >Tx25 can change what Tx14 did although Tx14 is still active?
>
> Yes, it looks a bit confusing, but this is a snapshot view which
> highlights that Tx 20 CANNOT see record versions from a) active
> transactions, b) transactions with Number > 20.
>
> It means that for snapshot transaction 20 all transactions above its
> number look like active, and as a result, it cannot view record versions
> from both from Tx 14, which was active at the moment when snapshot copy of
> TIP for Tx25 was taken, and it also cannot view record version from Tx 25,
> which, obviously was created after Tx 14 was committed. But, again, for  Tx
> 20 these both record versions are invisible. Tx20, as a snapshot, thinks
> that Tx is active, but in reality it was committed.
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
>
>
>
>
> Hi Alexey
>
>  Yes, that's right, I can not understand well the page 46. I suppose that
> R1 means "record version 1", R2 means "record version 2", etc.
>
>  Tx16 did the INSERT?
> Tx12 can do a COMMIT although it had started before Tx16 and Tx18 is still
> active?
> Tx25 can change what Tx14 did although Tx14 is still active?
>
>  These things had confused me.
>
>  Of course, I think that I am not understanding rightly the graph, that's
> why some words can help to do it clear.
>
>
>
>  
>

Re: [firebird-support] Test VARCAHR for numeric

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

You can use SIMILAR TO, something like it:

SELECT
   MyColumn1,
   MyColumn2,,
   MyColumn3
FROM
   MyTable
WHERE
   MyColumn1 SIMILAR TO '[[:DIGIT:]]*'

Where just the digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 are valid.

If you need the decimal point too, try something like it, where the
decimail point is required:

SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'

And if you can have integers and floats (decimal point can be, but not
required), you can try:

SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'

And, finally, if you can read Spanish, take a look at:

https://firebird21.wordpress.com/2014/04/27/usando-similar-to/

https://firebird21.wordpress.com/2014/11/25/validando-un-e-mail/

Greetings.

Walter.


On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> Is there any way, within a stored procedure, to test a VARCHAR to see if
> it contains a valid number? I have a parameter that can contain different
> types of value and it would be nice to be able to know if CAST(param as
> BIGINT) is going to fail before an exception is thrown.
>
>
>
>  
>

Re: [firebird-support] Test VARCAHR for numeric

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Sorry, typo in the SELECT, just one comma after MyColumn2.

Greetings.

Walter.


On Thu, Apr 23, 2015 at 8:00 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> You can use SIMILAR TO, something like it:
>
> SELECT
>MyColumn1,
>MyColumn2,,
>MyColumn3
> FROM
>MyTable
> WHERE
>MyColumn1 SIMILAR TO '[[:DIGIT:]]*'
>
> Where just the digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 are valid.
>
> If you need the decimal point too, try something like it, where the
> decimail point is required:
>
> SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'
>
> And if you can have integers and floats (decimal point can be, but not
> required), you can try:
>
> SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'
>
> And, finally, if you can read Spanish, take a look at:
>
> https://firebird21.wordpress.com/2014/04/27/usando-similar-to/
>
> https://firebird21.wordpress.com/2014/11/25/validando-un-e-mail/
>
> Greetings.
>
> Walter.
>
>
> On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>>
>>
>> Is there any way, within a stored procedure, to test a VARCHAR to see if
>> it contains a valid number? I have a parameter that can contain different
>> types of value and it would be nice to be able to know if CAST(param as
>> BIGINT) is going to fail before an exception is thrown.
>>
>>
>>
>>  
>>
>
>

Re: [firebird-support] Test VARCAHR for numeric

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Again, if you can read Spanish then the following article has more examples:

https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/

Greetings.

Walter.


On Thu, Apr 23, 2015 at 8:03 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Sorry, typo in the SELECT, just one comma after MyColumn2.
>
> Greetings.
>
> Walter.
>
>
> On Thu, Apr 23, 2015 at 8:00 PM, Walter R. Ojeda Valiente <
> sistemas2000profesio...@gmail.com> wrote:
>
>> You can use SIMILAR TO, something like it:
>>
>> SELECT
>>MyColumn1,
>>MyColumn2,,
>>MyColumn3
>> FROM
>>MyTable
>> WHERE
>>MyColumn1 SIMILAR TO '[[:DIGIT:]]*'
>>
>> Where just the digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 are valid.
>>
>> If you need the decimal point too, try something like it, where the
>> decimail point is required:
>>
>> SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'
>>
>> And if you can have integers and floats (decimal point can be, but not
>> required), you can try:
>>
>> SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'
>>
>> And, finally, if you can read Spanish, take a look at:
>>
>> https://firebird21.wordpress.com/2014/04/27/usando-similar-to/
>>
>> https://firebird21.wordpress.com/2014/11/25/validando-un-e-mail/
>>
>> Greetings.
>>
>> Walter.
>>
>>
>> On Thu, Apr 23, 2015 at 2:34 PM, sboyd...@gmail.com [firebird-support] <
>> firebird-support@yahoogroups.com> wrote:
>>
>>>
>>>
>>> Is there any way, within a stored procedure, to test a VARCHAR to see if
>>> it contains a valid number? I have a parameter that can contain different
>>> types of value and it would be nice to be able to know if CAST(param as
>>> BIGINT) is going to fail before an exception is thrown.
>>>
>>>
>>>
>>>  
>>>
>>
>>
>

Re: [firebird-support] Test VARCAHR for numeric

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Mark

This article:

https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/

Also has examples con minus signs, inverse conditions, etc.

Exponential notation not, because it is not usually used with commercial
applications. Any way, the idea is to show fastly whether there are o not
columns CHAR or VARCHAR with numeric values. No matter if they are
smallint, integer, bigint, etc.

Using exceptions you can know that there are problematic rows, but not
which are. (Well, at least not so easily as a SELECT ... SIMILAR TO does)

Greetings.

Walter.


On Fri, Apr 24, 2015 at 2:53 AM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support]  wrote:

>
>
> On Thu, 23 Apr 2015 20:00:32 -0400, "'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]"
>  wrote:
> > You can use SIMILAR TO, something like it:
> >
> > SELECT
> > MyColumn1,
> > MyColumn2,,
> > MyColumn3
> > FROM
> > MyTable
> > WHERE
> > MyColumn1 SIMILAR TO '[[:DIGIT:]]*'
> >
> > Where just the digits 0, 1, 2, 3, 4, 5, 6, 7, 8, 9 are valid.
>
> I'd also check for minus signs, and take into account the maximum number
> of digits in a number (eg 10 in an INTEGER, 19 or 20 in a BIGINT), but even
> then there is no guarantee (eg 2,147,483,648 doesn't fit in an INTEGER,
> while 2,147,483,647 does). The only sure way is too actually attempt the
> conversion and handle the exception.
>
> > If you need the decimal point too, try something like it, where the
> > decimail point is required:
> >
> > SIMILAR TO '[[:DIGIT:]]*.[[:DIGIT:]]*'
> >
> > And if you can have integers and floats (decimal point can be, but not
> > required), you can try:
> >
> > SIMILAR TO '[[:DIGIT:]]*.?[[:DIGIT:]]*'
>
> Don't forget the exponent notation (eg 5.203E-5)
>
> > And, finally, if you can read Spanish, take a look at:
> >
> > https://firebird21.wordpress.com/2014/04/27/usando-similar-to/
> >
> > https://firebird21.wordpress.com/2014/11/25/validando-un-e-mail/
>
> Mark
>  
>

Re: [firebird-support] Test VARCAHR for numeric

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Very interesting Mark, I shall remember that for the time when Firebird3 is
coming here.

Greetings.

Walter.


On Sat, Apr 25, 2015 at 3:14 AM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support]  wrote:

>
>
> On 25-4-2015 08:08, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] wrote:
> > This article:
> >
> >
> https://firebird21.wordpress.com/2015/04/23/validando-que-el-contenido-de-un-char-o-varchar-sea-numerico/
> >
> > Also has examples con minus signs, inverse conditions, etc.
> >
> > Exponential notation not, because it is not usually used with commercial
> > applications. Any way, the idea is to show fastly whether there are o
> > not columns CHAR or VARCHAR with numeric values. No matter if they are
> > smallint, integer, bigint, etc.
> >
> > Using exceptions you can know that there are problematic rows, but not
> > which are. (Well, at least not so easily as a SELECT ... SIMILAR TO does)
>
> With Firebird 3 functions you can define a function isvalidint:
>
> CREATE FUNCTION isvalidint(stringValue VARCHAR(25))
> RETURNS BOOLEAN
> AS
> DECLARE intValue INT;
> BEGIN
> BEGIN
> intValue = CAST(stringValue AS INT);
> RETURN TRUE;
> END
> WHEN ANY DO
> RETURN FALSE;
> END
>
> Example table:
> CREATE TABLE tableIntAsVarchar (
> ID INT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
> intAsVarchar VARCHAR(25) NOT NULL
> );
>
> Sample data:
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('123');
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('-123');
> INSERT INTO tableIntAsVarchar (intAsVarchar) VALUES ('Not a valid int');
>
> SELECT * FROM tableIntAsVarchar WHERE NOT isvalidint(intAsVarchar)
>
> Will only return the last inserted row (with value "Not a valid int").
>
> Similarly you could create a isValidBigint, isValidDoublePrecision etc.
>
> Mark
> --
> Mark Rotteveel
>  
>

Re: [firebird-support] GBAK - validation error for column X, value "*** null ***"

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

If you only have a backup and the backup is corrupted then  very bad
news.

The best politic is always to do a backup/restore cycle inmediatly. So, if
an error is detected when the backup is being restored you can take actions
at that right moment.

If your operating system is Windows, you can open a command window and
write:

*GBAK -b -user SYSDBA -password masterkey MyDataBase.FDB stdout | GBAK
-user SYSDBA -password masterkey -r stdin MyRestoredDatabase.FDB*

Any error will be inmediatly detected.

And, if you can read Spanish, the following page has more info about this
subject:

https://firebird21.wordpress.com/2013/03/05/backup-y-restauracion-al-mismo-tiempo/

Greetings.

Walter.


On Fri, May 15, 2015 at 4:41 AM, Josef Kokeš j.ko...@apatykaservis.cz
[firebird-support]  wrote:

>
>
> Hi!
>
> Thanks for the answer. Luckily, I still have the FDB file, so I can get
> rid of the bad records easily. What has me concerned is, what if I only
> had the backup? Your trick with pumping seems plausible, I will have to
> give it a try. IBBackupSurgeon looks promising, too.
>
> Josef
>
> On 15.5.2015 9:14, Alexey Kovyazin a...@ib-aid.com [firebird-support] wrote:
> >
> > Hi Josef,
> >
> > Such error is the consequence of the corruption - in the original
> > database there was a NULL in the field (most likely, all fields in
> > that record are NULL).
> > gbak does not check constraints when reading.
> >
> > The best way to fix it is to find record with NULLs in the original
> > database and delete it, then repeat backup/restore.
> > If you don't have original records, it can be a problem.
> > Try to restore database till the privileges error and then pump data
> > from partially restored database to the new empty database with the
> > same structure - you can use free tool IBDataPump for it.
> > If problems continue, you can export data using our proprietary
> > IBBackupSurgeon tool (http://ib-aid.com/en/ibbackupsurgeon/), which
> > can export data on low-level.
> >
> > Regards,
> > Alexey Kovyazin
> > IBSurgeon
>
>  
>

Re: [firebird-support] Database restore speed with IBExpert and Gbak

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Thomas

That seems an interesting idea. Can you explain it with more details?

Greetings.

Walter.


On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support]  wrote:

>
>
> Halim,
>
> > Thank you for your reply.
> > I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It
> > took about 8 minutes. Restoring the same database using IBExpert took
> > about 3 minutes.
> > I'm looking for a faster restore time because I want to automate the
> > process using a batch file. Our DB is over 50 GB.
>
> What is the size of table vs. index data?
>
> Restore is basically limited by single core throughput and I've hardly
> seen restore being IO bound.
>
> What you could try is to provide a much larger (temporary) page buffers
> value (which you have to reduce before the restored database is going to
> be used in production!) during the restore, which might help during
> index re-creation.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>  
>

Re: [firebird-support] Database restore speed with IBExpert and Gbak

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much, Thomas. Very useful.

Greetings.

Walter.


On Tue, May 26, 2015 at 1:02 PM, Thomas Steinmaurer t...@iblogmanager.com
[firebird-support]  wrote:

>
>
> Hello Walter,
>
> > Hello Thomas
> >
> > That seems an interesting idea. Can you explain it with more details?
>
> For the restore process, gbak supports a -BU(FFERS) switch to override
> the database page buffer value. While page buffers tends to be rather
> small for Classic/SuperClassic hosted databases, you could try to
> increase that value by up to a factor of 100 through the -BU switch for
> the restore process.
>
> This gives the restore connection a much higher Firebird page cache. But
> this setting is persisted in the header page after the restore, thus
> before going back to production, you have to reset to the original value.
>
> I can't recall my exact test results from the past. There was also some
> sort of sweet spot where further increasing didn't help anymore, so run
> your own tests before applying that in your environment.
>
> Possible further enhancements during index re-creation would be to
> re-create several indexes in parallel becoming more and more IO bound,
> especially with low latency storage. AFAIK InterBase added something
> like that in a recent version. Potentially Firebird has that on the
> roadmap as well.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
>
> > Greetings.
> >
> > Walter.
> >
> >
> > On Tue, May 26, 2015 at 12:25 PM, Thomas Steinmaurer t...@iblogmanager.com
> >  [firebird-support]
> >  > > wrote:
> >
> > __
>
> >
> > Halim,
> >
> > > Thank you for your reply.
> > > I just tested a GBAK restore using -se(rvice) switch on a 1 GB DB. It
> > > took about 8 minutes. Restoring the same database using IBExpert took
> > > about 3 minutes.
> > > I'm looking for a faster restore time because I want to automate the
> > > process using a batch file. Our DB is over 50 GB.
> >
> > What is the size of table vs. index data?
> >
> > Restore is basically limited by single core throughput and I've hardly
> > seen restore being IO bound.
> >
> > What you could try is to provide a much larger (temporary) page buffers
> > value (which you have to reduce before the restored database is
> > going to
> > be used in production!) during the restore, which might help during
> > index re-creation.
> >
> > --
> > With regards,
> > Thomas Steinmaurer
> > http://www.upscene.com/
> >
> > Professional Tools and Services for Firebird
> > FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> >
> >
> >
> >
> >
>
>  
>

Re: [firebird-support] Altering a domain

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Excellent explanation Ann, as always. Just one doubt:

After a cycle backup/restore: the restored database will have all the rows
with the new domain?

All columns with domain D_CURRENCY that were created with DECIMAL(9, 2) now
will be DECIMAL(18, 2) ?

Greetings.

Walter.


On Tue, Jun 16, 2015 at 2:58 PM, Aldo Caruso aldo.car...@argencasas.com
[firebird-support]  wrote:

>
>
> Ann,
>
> thank you very much for your clear explanation.
>
> Aldo
>
> El 16/06/15 a las 12:44, Ann Harrison aharri...@ibphoenix.com
> [firebird-support] escibió:
>
>
>  On Tue, Jun 16, 2015 at 11:15 AM, Aldo Caruso aldo.car...@argencasas.com
> [firebird-support]  wrote:
>
>>
>>
>>  I have defined the following domain :
>>
>> CREATE DOMAIN CURRENCY AS decimal(9,2)
>>
>>I would like to alter it as the following :
>>
>> ALTER DOMAIN CURRENCY AS decimal(18,2)
>>
>>  I have many fields in tables that depend on this domain and they
>> are filled with values.
>>
>>  Will they be restructured in their internal representation ( from
>> INTEGER to BIGINT in dialect 3 ) with no harm for the stored data ?
>>
>>
>>
>  Initially, only the domain and column definitions will change, creating
> new
> format versions for the tables affected.  When you insert new records into
> those tables, the new records will have the new format.  Old records will
> be changed in memory when you read them, so they appear to be in the
> new format.  The stored format of existing records change when the
> records are modified.
>
>  Good luck,
>
>  Ann
>
>
>
>  
>

Re: [firebird-support] Altering a domain

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you Ann.

Greetings.

Walter.


On Sat, Jun 20, 2015 at 9:04 AM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
> > On Jun 19, 2015, at 3:32 PM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
> >
> >
> > After a cycle backup/restore: the restored database will have all the
> rows with the new domain?
> >
> > All columns with domain D_CURRENCY that were created with DECIMAL(9, 2)
> now will be DECIMAL(18, 2) ?
>
> Yes. Gbak is really an unload/recreate/reload. A newly restored database
> will have all records at the same format version. The format version number
> is a single byte, so it's possible to exhaust the format version numbers by
> altering a table repeatedly (that's a hedge because I don't remember if the
> byte is treated as signed). A gbak backup/restore resets the format version
> numbers. (Again, a hedge because it may be necessary for gbak to alter
> tables when there are cross references, so the restore may not create
> exclusively format version 1 records).
>
> Good luck,
>
> Ann
>  
>

[firebird-support] Firebird 3, execution modes

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Reading the excellent (as usual) document of Helen Borrie: Firebird 3.0
Release Notes (for Firebird 3.0 Beta 2) I had some doubts:

First, it says that for choicing the working modes (models) I need to put
values 0 or 1 at SharedCache and SharedDatabase. Ok with that.

But then it says that the execution modes depends (on Windows) of a -m
switch.

So, really if I want a determinated working mode I need to do 2 things:
1. Execute FIREBIRD.EXE with or without the -m switch
2. Put values at the entries SharedCache and SharedDatabase

¿Am I right?

Besides it, if I want Database1 using Classic and Database2 using
SuperClassic, it is impossible with just one instance of FIREBIRD.EXE

¿Am I right?

Greetings.

Walter.

[firebird-support] Re: Firebird 3, execution modes

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

¿Nobody has answers to my questions?

Greetings.

Walter.


On Fri, Jul 10, 2015 at 10:39 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Reading the excellent (as usual) document of Helen Borrie: Firebird 3.0
> Release Notes (for Firebird 3.0 Beta 2) I had some doubts:
>
> First, it says that for choicing the working modes (models) I need to put
> values 0 or 1 at SharedCache and SharedDatabase. Ok with that.
>
> But then it says that the execution modes depends (on Windows) of a -m
> switch.
>
> So, really if I want a determinated working mode I need to do 2 things:
> 1. Execute FIREBIRD.EXE with or without the -m switch
> 2. Put values at the entries SharedCache and SharedDatabase
>
> ¿Am I right?
>
> Besides it, if I want Database1 using Classic and Database2 using
> SuperClassic, it is impossible with just one instance of FIREBIRD.EXE
>
> ¿Am I right?
>
> Greetings.
>
> Walter.
>
>
>
>

Re: [firebird-support] Re: Firebird 3, execution modes

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Ah, ok, I don't knew that. Thank you for the info.

Greetings.

Walter.




On Tue, Jul 14, 2015 at 10:50 AM, 'Mark Rotteveel' m...@lawinegevaar.nl
[firebird-support]  wrote:

>
>
> You might get a quicker response on firebird-devel. Questions about beta
> versions are off topic on firebird-support.
>
> Mark
>
>
> ----- Reply message -
> Van: "'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com
> [firebird-support]" 
> Aan: 
> Onderwerp: [firebird-support] Re: Firebird 3, execution modes
> Datum: di, jul. 14, 2015 16:29
>
> ¿Nobody has answers to my questions?
>
> Greetings.
>
> Walter.
>
>
> On Fri, Jul 10, 2015 at 10:39 PM, Walter R. Ojeda Valiente <
> sistemas2000profesio...@gmail.com> wrote:
>
>> Reading the excellent (as usual) document of Helen Borrie: Firebird 3.0
>> Release Notes (for Firebird 3.0 Beta 2) I had some doubts:
>>
>> First, it says that for choicing the working modes (models) I need to put
>> values 0 or 1 at SharedCache and SharedDatabase. Ok with that.
>>
>> But then it says that the execution modes depends (on Windows) of a -m
>> switch.
>>
>> So, really if I want a determinated working mode I need to do 2 things:
>> 1. Execute FIREBIRD.EXE with or without the -m switch
>> 2. Put values at the entries SharedCache and SharedDatabase
>>
>> ¿Am I right?
>>
>> Besides it, if I want Database1 using Classic and Database2 using
>> SuperClassic, it is impossible with just one instance of FIREBIRD.EXE
>>
>> ¿Am I right?
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
>>
>   
>

[firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everbody

Always that a transaction that did an UPDATE ends with a COMMIT or a
ROLLBACK this left back a row.

So, my question is: There is some way to know those previous rows?

This can be very useful for me because I shall know how many garbage a
table has.

For example, if a row has the ID 1234, it would be great a SELECT which
show me something like that:

1234 INSERT COMMIT
1234 UPDATE COMMIT
1234 UPDATE ROLLBACK
1234 UPDATE COMMIT
1234 UPDATE COMMIT
1234 UPDATE COMMIT

Saying me some "story" of that row.

Is that possible?

Thanks in advance.

Greetings.

Walter.

Re: [firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Yes, of course that I can use triggers but that will have 2 problems:

1. Just will work with *new* data, not with the old ones. The data commited
or rolled back *before* I write the triggers will be ignored.
2. With a database trigger I can know if a transaction has ends with a
COMMIT or a ROLL BACK but not which table and which rows were used. If I
write table's triggers then I will need to write a lot of them. If the
database has 216 tables then I will need to write 216 triggers.

The idea is to know how many garbage a table has. Or several tables. Or the
whole database.

Greetings.

Walter.







On Fri, Aug 7, 2015 at 1:32 AM, liviuslivius liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> What about triggers?
>
> Regards,
> Karol Bieniaszewski
>
>
>  Oryginalna wiadomość --------
> Od: "'Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com
> [firebird-support]" 
> Data: 06.08.2015 20:12 (GMT+01:00)
> Do: firebird-support@yahoogroups.com
> Temat: [firebird-support] Knowing the previous rows
>
>
>
> Hello everbody
>
> Always that a transaction that did an UPDATE ends with a COMMIT or a
> ROLLBACK this left back a row.
>
> So, my question is: There is some way to know those previous rows?
>
> This can be very useful for me because I shall know how many garbage a
> table has.
>
> For example, if a row has the ID 1234, it would be great a SELECT which
> show me something like that:
>
> 1234 INSERT COMMIT
> 1234 UPDATE COMMIT
> 1234 UPDATE ROLLBACK
> 1234 UPDATE COMMIT
> 1234 UPDATE COMMIT
> 1234 UPDATE COMMIT
>
> Saying me some "story" of that row.
>
> Is that possible?
>
> Thanks in advance.
>
> Greetings.
>
> Walter.
>
>
> 
>

Re: [firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you Ann, I shall investigate that.

Greetings.

Walter.


On Fri, Aug 7, 2015 at 1:10 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
>
>
> The idea is to know how many garbage a table has. Or several tables. Or
> the whole database.
>
>
> That you can find out with gstat.  It won't tell you how bad each record
> is, but it will tell you the total number of back version, average, min,
> and max chain lenght.
>
> Then you can set up triggers (if you want) on troublesome tables.
>
> Good luck,
>
> Ann
>
>
> 
>

Re: [firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Well, after run GSTAT and reading the output I can see how many garbage a
whole table has, but it don't shows me the "story" of a row. ¿How many
COMMITs and how many ROLLBACKs the row with ID 1234 of the table CLIENTS
had had?

Greetings.

Walter.


On Fri, Aug 7, 2015 at 1:42 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Thank you Ann, I shall investigate that.
>
> Greetings.
>
> Walter.
>
>
> On Fri, Aug 7, 2015 at 1:10 PM, Ann Harrison aharri...@ibphoenix.com
> [firebird-support]  wrote:
>
>>
>>
>>
>>
>>
>> The idea is to know how many garbage a table has. Or several tables. Or
>> the whole database.
>>
>>
>> That you can find out with gstat.  It won't tell you how bad each record
>> is, but it will tell you the total number of back version, average, min,
>> and max chain lenght.
>>
>> Then you can set up triggers (if you want) on troublesome tables.
>>
>> Good luck,
>>
>> Ann
>>
>>
>> 
>>
>
>

Re: [firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you Ann, your answer is very clear, as always.

I just was a bit curious about that possibility, which seems interesting
for audit tables and more yet, users.

By design, very few UPDATEs and DELETEs there are on my databases because I
don't like them. As you know each UPDATE or DELETE add garbage to the
database so I try to avoid using those commands and so all works fine.
Happy users, happy my employees, happy me. :-)

But I have a blog about Firebird and one reader did that question and as I
don't knew the answer was curious.

Greetings.

Walter.




On Fri, Aug 7, 2015 at 4:14 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
> > On Aug 7, 2015, at 1:56 PM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
> >
> > Well, after run GSTAT and reading the output I can see how many garbage
> a whole table has, but it don't shows me the "story" of a row. ¿How many
> COMMITs and how many ROLLBACKs the row with ID 1234 of the table CLIENTS
> had had?
>
> Firebird doesn't track that information. I suggested that you start with
> gstat to figure out which tables are worth instrumenting, then instrument
> them.
>
> You can track committed inserts (one per record, obviously) and updates
> and deletes (again, one per record) with triggers. Updates that roll back
> will be hard to track because Firebird cleans up rolled back changes
> immediately if it possibly can - and the triggered changes to internal are
> included in the clean-up. If you write your trigger to an external table
> the changes won't be rolled back, but you won't be able to tell the
> difference between a successful update and a failed update.
>
> I don't have easy access to the Firebird release notes, but suspect that
> sometime someone added a system variable that will give you access to
> transaction ids. With that, and a transaction triggger that writes the
> terminal state of a transaction to an external table, you should be able to
> get the information you want.
>
> But why? I understand caring about long strings of back versions. But why
> do you care about failed updates? Firebird removes them immediately unless
> the server (or inet-server for Classic) has crashed. Server crashes should
> be rare.
>
> Good luck,
>
> Ann
> 
>

[firebird-support] Problem with AUTONOMOUS TRANSACTION

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

Do you know why this simple trigger "hang-up" the database?

CREATE TRIGGER TRANSACCION_INICIA
ACTIVE ON TRANSACTION START
POSITION 0
AS
BEGIN

   IN AUTONOMOUS TRANSACTION DO BEGIN

   END

END;


Always when I put "IN AUTONOMOUS TRANSACTION" the database crash. Without
those words everything works fine.

It is not possible to use autonomous transactions inside a database trigger?

The same problem happens with SuperServer and Classic.

Windows 7, 32 bits
Firebird 2.5.4

Greetings.

Walter.

Re: [firebird-support] Problem with AUTONOMOUS TRANSACTION

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Ha, ha, ha, ha, ha, ha, ha, ha.

Thank you very much Helen, sometimes the memory is not so good as was some
years ago.

Ok, I now know and hope not forget again: "NEVER USE IN AUTONOMOUS
TRANSACTION INSIDE A TRANSACTION RELATED DATABASE TRIGGER".

Greetings.

Walter.


On Fri, Aug 7, 2015 at 11:20 PM, Helen Borrie hele...@iinet.net.au
[firebird-support]  wrote:

>
>
> At 02:51 p.m. 8/08/2015, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [f wrote:
>
> >Hello everybody
> >
> >Do you know why this simple trigger "hang-up" the database?
> >
> >CREATE TRIGGER TRANSACCION_INICIA
> >ACTIVE ON TRANSACTION START
> >POSITION 0
> >AS
> >BEGIN
> >Â Â
> >Â Â IN AUTONOMOUS TRANSACTION DO BEGIN
> >Â Â
> >Â Â END
> >Â Â
> >END;
> >
> >
> >Always when I put "IN AUTONOMOUS TRANSACTION" the database crash. Without
> those words everything works fine.
> >
> >It is not possible to use autonomous transactions inside a database
> trigger?
>
> From the forthcoming Firebird 2.5 Language Reference manual (yes, people,
> it is coming!)
>
> Some Caveats
> 1. The use of the IN AUTONOMOUS TRANSACTION DO statement in the database
> event triggers related to transactions (TRANSACTION START, TRANSACTION
> ROLLBACK, TRANSACTION COMMIT) may cause the autonomous transaction to enter
> an infinite loop
>
> Helen
>
> 
>

Re: [firebird-support] Knowing the previous rows

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Mark

My transactions are always extremely short running because I work hard to
keep them that way. But I have a lot of clients with a great variety of
environments, someones still using Windows XP, someones using Windows 10,
with dedicated servers, with not dedicated servers, with employees that
understand database administrative tasks, with employees who think I am
talking swahili and so on.

Therefore I need to avoid in advance any kind of possible problems or
troubles. No always I can trust in the knowledge of other people who maybe
are at hundreds of kilometers and using TeamViewer or similar is not the
best option because it is still a waste of time.

I don't like receive calls of clients complaining about something. Happy
clients means more money, today or later.

So, all my applications should be good in all senses.

Greetings.

Walter.






On Sat, Aug 8, 2015 at 6:36 AM, Mark Rotteveel m...@lawinegevaar.nl
[firebird-support]  wrote:

>
>
> On 7-8-2015 22:50, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] wrote:
> > I just was a bit curious about that possibility, which seems interesting
> > for audit tables and more yet, users.
> >
> > By design, very few UPDATEs and DELETEs there are on my databases
> > because I don't like them. As you know each UPDATE or DELETE add garbage
> > to the database so I try to avoid using those commands and so all works
> > fine. Happy users, happy my employees, happy me. :-)
>
> Why go through extremes to avoid garbage? It is not as if the garbage
> stays there indefinitely; garbage is cleaned up eventually, and assuming
> you don't use extremely long running transactions, that happens pretty
> quick.
>
> Mark
>
> --
> Mark Rotteveel
> 
>

Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Set

Why writing: PP.STATUS_CODE || '' will improve the SELECT, can you explain
me?

As far I know, adding '' is for use NATURAL in the PLAN, why the use of
NATURAL will do a better response time?

Thanks in advance.

Greetings.

Walter.


On Thu, Aug 13, 2015 at 4:49 PM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>
>
> >>Two more questions, Karol:
>
> Sorry, I meant to write Mike.
>
> >>How large percentage of the records in PER_PHONE have status_code G or V?
> >>What's the definition of REFPERSON15?
>
> >The PER_PHONE table currently contain 579,873 rows
> >91455 or 15.771% is ‘G’
> >166075 or 28.639% is ‘V’
> >For a total of 44.41%
>
> Then I'd expect a small improvement if you generally changed to
>
> AND PP.STATUS_CODE||'' IN ('G','V')
> and
> ORDER BY PP.STATUS_CODE||''
>
> I don't know whether the improvement would be from 2:45 hours to 2:40
> hours or 1:45 hours, but it would still be slowish and I'd be very
> surprised if it was enough to return what you want in less than 1 hour. I
> say slowish rather than slow, because executing a stored procedure 42000
> times in 2:45 hours would mean that the stored procedure is executed more
> than 4 times per second.
>
> I thought about rewriting your stored procedure to a view, but failed (I
> think that would require windowing functions, which aren't available in
> Firebird 2.5). So, if this is something reasonably frequently executed, I
> would have considered rewriting it to be a new table maintained through one
> or more triggers on the PER_PHONE tables (alternatively, you could have a
> new field in the PER_PHONE table that contained SEQUENCE_NO or something
> similar, but that still was populated through a trigger). That would make
> insert/update/delete into PER_PHONE slightly slower and could cause
> problems if several users inserted records for the same PERSON_ID
> simultaneously. However, your select would be very quick.
>
> Sorry,
> Set
>
> 
>

Re: [firebird-support] What is the best way to re-write this Stored Procedure that seems to be SLOW processing?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Set

I am not who originally post the question, just a curious reader.

And I use Firebird 2.5.4 only, I don't like to have several versions
because they can have different problems. Just one version for all clients
and all servers and all databases, it is the better, according to my point
of view.

Of course that NATURAL is the faster way when you want read all the rows of
a table, but going from 2 hours and 45 minutes to 12 minutes is a extremely
great improvement and I was very interested for understand the reason.

Greetings.

Walter.


On Sat, Aug 15, 2015 at 9:42 AM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>
>
> Den 15.08.2015 06:14, skrev 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]:
> > Hello Set
> >
> > Why writing: PP.STATUS_CODE || '' will improve the SELECT, can you
> > explain me?
> > As far I know, adding '' is for use NATURAL in the PLAN, why the use
> > of NATURAL will do a better response time?
> > Thanks in advance.
> >
> > Greetings.
> > Walter
> Sure Walter.
>
> Generally, NATURAL is the quickest way to go through all records in a
> table, whereas indexes are quicker if you're only considering a few of
> them. As I expected, the threshold when an index becomes sensible for a
> particular query, is below 44% (I think I've said before that I don't
> know where the threshold is, just guessed somewhere between 5 and 20 %
> (it's purely a wild guess, I know nothing about the Fb code and have not
> done any measurements)). However, going from 2 hours 45 minutes to 12
> minutes is simply too much of an improvement if the only change in the
> plan is whether this particular index is used or not. Although Fb 2.5 is
> considerably better than 1.5, Fb 1.5 wasn't that bad in utilizing
> indexes. Did the plan change in some other way, or was the only
> difference that this index was replaced by natural?
>
> I cannot explain your improvement. Fb 1.5 (and before) has problems with
> many duplicates in an index, but that problem manifest itself with
> update and delete, not select. That particular problem is also simple to
> fix, just add your primary key field to the end of the index. Another
> problem with Fb 1.5 is that it sometimes have problems to choose which
> index to use if you have to identical indexes, but you haven't indicated
> that you have that.
>
> Set
> 
>

[firebird-support] Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

For add a Primary Key to a table we can write:

ALTER TABLE MyTable ADD PRIMARY KEY (ID);

or we can write:

ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);

In the first case, the Firebird puts the name of the Primary Key, in the
second case we choose that name, but...

Why sometimes the first case fails and the second case always work?

Of course that the table has not a Primary Key yet and the ID column has
not nulls neither duplicated values.

But (just sometimes) the first case fails.

Greetings.

Walter.

[firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Nobody knows?

Nobody has any idea?

Greetings.

Walter.


On Thu, Aug 20, 2015 at 10:45 AM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Hello everybody
>
> For add a Primary Key to a table we can write:
>
> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>
> or we can write:
>
> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>
> In the first case, the Firebird puts the name of the Primary Key, in the
> second case we choose that name, but...
>
> Why sometimes the first case fails and the second case always work?
>
> Of course that the table has not a Primary Key yet and the ID column has
> not nulls neither duplicated values.
>
> But (just sometimes) the first case fails.
>
> Greetings.
>
> Walter.
>
>

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Ricardo

Thank you for your answer, but that is not the problem.

Without using CONSTRAINT doesn't work and in such case the name is choosed
by Firebird, not for me.

Greetings.

Walter.




On Fri, Aug 21, 2015 at 1:43 PM, Ricardo Uzcategui ricardou...@cantv.net
[firebird-support]  wrote:

>
>
> hi. Maybe you post an ilegal name or this name are not unique for entery
> database objects name's. every object in firebird database must be unique.
> no matters if the same name is used on diferent tables.
>
> On Fri, Aug 21, 2015 at 12:24 PM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>>
>>
>> Nobody knows?
>>
>> Nobody has any idea?
>>
>> Greetings.
>>
>> Walter.
>>
>>
>> On Thu, Aug 20, 2015 at 10:45 AM, Walter R. Ojeda Valiente <
>> sistemas2000profesio...@gmail.com> wrote:
>>
>>> Hello everybody
>>>
>>> For add a Primary Key to a table we can write:
>>>
>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>
>>> or we can write:
>>>
>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>
>>> In the first case, the Firebird puts the name of the Primary Key, in the
>>> second case we choose that name, but...
>>>
>>> Why sometimes the first case fails and the second case always work?
>>>
>>> Of course that the table has not a Primary Key yet and the ID column has
>>> not nulls neither duplicated values.
>>>
>>> But (just sometimes) the first case fails.
>>>
>>> Greetings.
>>>
>>> Walter.
>>>
>>>
>>
>
>
> --
> Ricardo...
>
> 
>

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Ann

Well, the world has not falled out, nothing so dangerous, the error message
is:

"Cannot commit transaction:
The insert failed because a column definition includes validation
constraints. validation error for column ID, value "*** null ***"."

The question is:

Why without using CONSTRAINT appears that message but using CONSTRAINT all
works fine?

And there are not row/s with a NULL value in the column ID.

At least, they are not showed with the following query:

SELECT
   *
FROM
   MyTable
WHERE
   ID IS NULL

So, it seems very strange to me. The logic for me is: both works or both
fails, but why one fails and the other works?

Greetings.

Walter.






On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
> On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>
> Without using CONSTRAINT doesn't work and in such case the name is choosed
> by Firebird, not for me.
>
>>
>>>
>>>> For add a Primary Key to a table we can write:
>>>>
>>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>>
>>>> or we can write:
>>>>
>>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>>
>>>> In the first case, the Firebird puts the name of the Primary Key, in
>>>> the second case we choose that name, but...
>>>>
>>>> Why sometimes the first case fails and the second case always work?
>>>>
>>>> But (just sometimes) the first case fails.
>>>>
>>>>
> How does it fail?  What's the error message? Or does it kill the
> connection?  Crash the server?  Freeze the O/S?  Set the machine on fire?
>
> Cheers,
>
> Ann
>
> 
>

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Nobody knows the answer?

Well, then I shall add this to the "Firebird's mysteries".

Greetings.

Walter.


On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente <
sistemas2000profesio...@gmail.com> wrote:

> Hello Ann
>
> Well, the world has not falled out, nothing so dangerous, the error
> message is:
>
> "Cannot commit transaction:
> The insert failed because a column definition includes validation
> constraints. validation error for column ID, value "*** null ***"."
>
> The question is:
>
> Why without using CONSTRAINT appears that message but using CONSTRAINT all
> works fine?
>
> And there are not row/s with a NULL value in the column ID.
>
> At least, they are not showed with the following query:
>
> SELECT
>*
> FROM
>MyTable
> WHERE
>ID IS NULL
>
> So, it seems very strange to me. The logic for me is: both works or both
> fails, but why one fails and the other works?
>
> Greetings.
>
> Walter.
>
>
>
>
>
>
> On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com
> [firebird-support]  wrote:
>
>>
>>
>>
>> On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente'
>> sistemas2000profesio...@gmail.com [firebird-support] <
>> firebird-support@yahoogroups.com> wrote:
>>
>>
>> Without using CONSTRAINT doesn't work and in such case the name is
>> choosed by Firebird, not for me.
>>
>>>
>>>>
>>>>> For add a Primary Key to a table we can write:
>>>>>
>>>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>>>
>>>>> or we can write:
>>>>>
>>>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>>>
>>>>> In the first case, the Firebird puts the name of the Primary Key, in
>>>>> the second case we choose that name, but...
>>>>>
>>>>> Why sometimes the first case fails and the second case always work?
>>>>>
>>>>> But (just sometimes) the first case fails.
>>>>>
>>>>>
>> How does it fail?  What's the error message? Or does it kill the
>> connection?  Crash the server?  Freeze the O/S?  Set the machine on fire?
>>
>> Cheers,
>>
>> Ann
>>
>> 
>>
>
>

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Ann

Well, that was not the case, because without the word CONSTRAINT has failed
and with that word has worked fine and I was the only user of the database
at that moment.

But no problem, I know the solution: always use the word CONSTRAINT.

Greetings.

Walter.




On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> have you sample database to reproduce this.
> How you test this difference? Have you some backup of database and restore
> it and then test this two options?
>
> regards,
> Karol Bieniaszewski
>
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Sunday, August 23, 2015 5:48 AM
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Re: Differences when adding a Primary
> Key
>
>
>
> Nobody knows the answer?
>
> Well, then I shall add this to the "Firebird's mysteries".
>
> Greetings.
>
> Walter.
>
>
> On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente <
> sistemas2000profesio...@gmail.com> wrote:
>
>> Hello Ann
>>
>> Well, the world has not falled out, nothing so dangerous, the error
>> message is:
>>
>> "Cannot commit transaction:
>> The insert failed because a column definition includes validation 
>> constraints.
>> validation error for column ID, value "*** null ***"."
>>
>> The question is:
>>
>> Why without using CONSTRAINT appears that message but using CONSTRAINT
>> all works fine?
>>
>> And there are not row/s with a NULL value in the column ID.
>>
>> At least, they are not showed with the following query:
>>
>> SELECT
>>*
>> FROM
>>MyTable
>> WHERE
>>ID IS NULL
>>
>> So, it seems very strange to me. The logic for me is: both works or both
>> fails, but why one fails and the other works?
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
>>
>>
>>
>> On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com
>> [firebird-support]  wrote:
>>
>>>
>>>
>>>
>>> On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente'
>>> sistemas2000profesio...@gmail.com [firebird-support] <
>>> firebird-support@yahoogroups.com> wrote:
>>>
>>>
>>> Without using CONSTRAINT doesn't work and in such case the name is
>>> choosed by Firebird, not for me.
>>>
>>>>
>>>>>
>>>>>> For add a Primary Key to a table we can write:
>>>>>>
>>>>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>>>>
>>>>>> or we can write:
>>>>>>
>>>>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>>>>
>>>>>> In the first case, the Firebird puts the name of the Primary Key, in
>>>>>> the second case we choose that name, but...
>>>>>>
>>>>>> Why sometimes the first case fails and the second case always work?
>>>>>>
>>>>>> But (just sometimes) the first case fails.
>>>>>>
>>>>>>
>>>>>
>>> How does it fail?  What's the error message? Or does it kill the
>>> connection?  Crash the server?  Freeze the O/S?  Set the machine on fire?
>>>
>>> Cheers,
>>>
>>> Ann
>>>
>>
>>
>
>
> 
>

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Karol

No, I don't have a backup previous to add the Primary Key then I can use
for reproduce the problem.

Using ISQL, one time I try to add the Primary Key without using the word
CONSTRAINT, seconds later I try again but using the word CONSTRAINT. The
first time had failed, the second time worked fine.

I was curious ... why?

I think that a possibility is that the name INTEG_47 was previously used
but not eliminated of the metadata.

INTEG_47 was the name of the failed Primary Key, as ISQL shows me.

The table had not NULL neither duplicated values in the column ID.

Greetings.

Walter.



On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> have you sample database to reproduce this.
> How you test this difference? Have you some backup of database and restore
> it and then test this two options?
>
> regards,
> Karol Bieniaszewski
>
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Sunday, August 23, 2015 5:48 AM
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Re: Differences when adding a Primary
> Key
>
>
>
> Nobody knows the answer?
>
> Well, then I shall add this to the "Firebird's mysteries".
>
> Greetings.
>
> Walter.
>
>
> On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente <
> sistemas2000profesio...@gmail.com> wrote:
>
>> Hello Ann
>>
>> Well, the world has not falled out, nothing so dangerous, the error
>> message is:
>>
>> "Cannot commit transaction:
>> The insert failed because a column definition includes validation 
>> constraints.
>> validation error for column ID, value "*** null ***"."
>>
>> The question is:
>>
>> Why without using CONSTRAINT appears that message but using CONSTRAINT
>> all works fine?
>>
>> And there are not row/s with a NULL value in the column ID.
>>
>> At least, they are not showed with the following query:
>>
>> SELECT
>>*
>> FROM
>>MyTable
>> WHERE
>>ID IS NULL
>>
>> So, it seems very strange to me. The logic for me is: both works or both
>> fails, but why one fails and the other works?
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
>>
>>
>>
>> On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com
>> [firebird-support]  wrote:
>>
>>>
>>>
>>>
>>> On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente'
>>> sistemas2000profesio...@gmail.com [firebird-support] <
>>> firebird-support@yahoogroups.com> wrote:
>>>
>>>
>>> Without using CONSTRAINT doesn't work and in such case the name is
>>> choosed by Firebird, not for me.
>>>
>>>>
>>>>>
>>>>>> For add a Primary Key to a table we can write:
>>>>>>
>>>>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>>>>
>>>>>> or we can write:
>>>>>>
>>>>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>>>>
>>>>>> In the first case, the Firebird puts the name of the Primary Key, in
>>>>>> the second case we choose that name, but...
>>>>>>
>>>>>> Why sometimes the first case fails and the second case always work?
>>>>>>
>>>>>> But (just sometimes) the first case fails.
>>>>>>
>>>>>>
>>>>>
>>> How does it fail?  What's the error message? Or does it kill the
>>> connection?  Crash the server?  Freeze the O/S?  Set the machine on fire?
>>>
>>> Cheers,
>>>
>>> Ann
>>>
>>
>>
>
>
> 
>

[firebird-support] Expression index for use with the GROUP BY clause

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

I had the following query:

SELECT
   D.ASI_ANOEJE,
   D.ASI_CODSUC,
   EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES,
   D.ASI_NUMCUE,
   D.ASI_NUMSUB
FROM
   ASIENTOSDET D
JOIN
   ASIENTOSCAB C
  ON D.ASI_CODSUC = C.ASC_CODSUC AND
 D.ASI_IDECAB = C.ASC_IDENTI
GROUP BY
   D.ASI_ANOEJE,
   D.ASI_CODSUC,
   EXTRACT(MONTH FROM C.ASC_FECHAX),
   D.ASI_NUMCUE,
   D.ASI_NUMSUB

Is it possible to have an expression index for use here with the GROUP BY
clause?

The PLAN is the following:

PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB)))

As you can see there is a SORT there caused by the GROUP BY clause.

ASI_ANOEJE is SMALLINT
ASI_CODSUC is SMALLINT
ASC_FECHAX is DATE
ASI_NUMCUE is VARCHAR(16)
ASI_NUMSUB is CHAR(5)

I'm using Firebird 2.5.4

Greetings.

Walter.

Re: [firebird-support] Re: Differences when adding a Primary Key

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Karol

As I had said before, it is a mystery for me. The Primary Keys created by
me always begin with the PK_ characters, if they begin with INTEG_ is
because were created by ISQL.

But not problem at all, using CONSTRAINT it is solved.

Greetings.

Walter.


On Mon, Aug 24, 2015 at 12:51 PM, liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> what about drop this PK
> do backup restore and try again?
>
> P.S.
> If you previously name some constraint as INTEG... then conflict with name
> can appear
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Monday, August 24, 2015 5:00 PM
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Re: Differences when adding a Primary
> Key
>
>
>
> Hello Karol
>
> No, I don't have a backup previous to add the Primary Key then I can use
> for reproduce the problem.
>
> Using ISQL, one time I try to add the Primary Key without using the word
> CONSTRAINT, seconds later I try again but using the word CONSTRAINT. The
> first time had failed, the second time worked fine.
>
> I was curious ... why?
>
> I think that a possibility is that the name INTEG_47 was previously used
> but not eliminated of the metadata.
>
> INTEG_47 was the name of the failed Primary Key, as ISQL shows me.
>
> The table had not NULL neither duplicated values in the column ID.
>
> Greetings.
>
> Walter.
>
>
>
> On Sun, Aug 23, 2015 at 12:37 PM, liviusliv...@poczta.onet.pl
> [firebird-support]  wrote:
>
>>
>> Hi,
>>
>> have you sample database to reproduce this.
>> How you test this difference? Have you some backup of database and
>> restore it and then test this two options?
>>
>> regards,
>> Karol Bieniaszewski
>>
>>
>> *From:* mailto:firebird-support@yahoogroups.com
>> 
>> *Sent:* Sunday, August 23, 2015 5:48 AM
>> *To:* firebird-support@yahoogroups.com
>> *Subject:* Re: [firebird-support] Re: Differences when adding a Primary
>> Key
>>
>>
>> Nobody knows the answer?
>>
>> Well, then I shall add this to the "Firebird's mysteries".
>>
>> Greetings.
>>
>> Walter.
>>
>>
>> On Fri, Aug 21, 2015 at 4:47 PM, Walter R. Ojeda Valiente <
>> sistemas2000profesio...@gmail.com> wrote:
>>
>>> Hello Ann
>>>
>>> Well, the world has not falled out, nothing so dangerous, the error
>>> message is:
>>>
>>> "Cannot commit transaction:
>>> The insert failed because a column definition includes validation 
>>> constraints.
>>> validation error for column ID, value "*** null ***"."
>>>
>>> The question is:
>>>
>>> Why without using CONSTRAINT appears that message but using CONSTRAINT
>>> all works fine?
>>>
>>> And there are not row/s with a NULL value in the column ID.
>>>
>>> At least, they are not showed with the following query:
>>>
>>> SELECT
>>>*
>>> FROM
>>>MyTable
>>> WHERE
>>>ID IS NULL
>>>
>>> So, it seems very strange to me. The logic for me is: both works or both
>>> fails, but why one fails and the other works?
>>>
>>> Greetings.
>>>
>>> Walter.
>>>
>>>
>>>
>>>
>>>
>>>
>>> On Fri, Aug 21, 2015 at 2:43 PM, Ann Harrison aharri...@ibphoenix.com
>>> [firebird-support]  wrote:
>>>
>>>>
>>>>
>>>>
>>>> On Aug 21, 2015, at 2:13 PM, 'Walter R. Ojeda Valiente'
>>>> sistemas2000profesio...@gmail.com [firebird-support] <
>>>> firebird-support@yahoogroups.com> wrote:
>>>>
>>>>
>>>> Without using CONSTRAINT doesn't work and in such case the name is
>>>> choosed by Firebird, not for me.
>>>>
>>>>>
>>>>>>
>>>>>>> For add a Primary Key to a table we can write:
>>>>>>>
>>>>>>> ALTER TABLE MyTable ADD PRIMARY KEY (ID);
>>>>>>>
>>>>>>> or we can write:
>>>>>>>
>>>>>>> ALTER TABLE MyTable ADD CONSTRAINT MyPK PRIMARY KEY (ID);
>>>>>>>
>>>>>>> In the first case, the Firebird puts the name of the Primary Key, in
>>>>>>> the second case we choose that name, but...
>>>>>>>
>>>>>>> Why sometimes the first case fails and the second case always work?
>>>>>>>
>>>>>>> But (just sometimes) the first case fails.
>>>>>>>
>>>>>>>
>>>>>>
>>>> How does it fail?  What's the error message? Or does it kill the
>>>> connection?  Crash the server?  Freeze the O/S?  Set the machine on fire?
>>>>
>>>> Cheers,
>>>>
>>>> Ann
>>>>
>>>
>>>
>>
>>
>
>
> 
>

Re: [firebird-support] Expression index for use with the GROUP BY clause

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Karol

Thank you for your answer.

The query is a very simplified one, just for show the idea.

I can create an index as the following:

CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY (
   CAST(ASI_ANOEJE AS CHAR(5)) ||
   CAST(ASI_CODSUC AS CHAR(5)) ||
   CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE
ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) ||
   ASI_NUMCUE ||
   ASI_NUMSUB
);

But is is not used in the PLAN. I had tried several alternatives but
without success until now. Using CAST(), without using CAST() and so on.

I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can
avoid create it, but I need rows grouped by that column.

ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where
ASIENTOSCAB is the parent.

Greetings.

Walter.






On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> No – you can not create single index on more then one table
> But you really need it?
> How big is resultset? I do not see any filter in this query no HAVING nor
> WHERE
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Monday, August 24, 2015 7:28 PM
> *To:* firebird-support@yahoogroups.com
> *Subject:* [firebird-support] Expression index for use with the GROUP BY
> clause
>
>
>
> Hello everybody
>
> I had the following query:
>
> SELECT
>D.ASI_ANOEJE,
>D.ASI_CODSUC,
>EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES,
>D.ASI_NUMCUE,
>D.ASI_NUMSUB
> FROM
>ASIENTOSDET D
> JOIN
>ASIENTOSCAB C
>   ON D.ASI_CODSUC = C.ASC_CODSUC AND
>  D.ASI_IDECAB = C.ASC_IDENTI
> GROUP BY
>D.ASI_ANOEJE,
>D.ASI_CODSUC,
>EXTRACT(MONTH FROM C.ASC_FECHAX),
>D.ASI_NUMCUE,
>D.ASI_NUMSUB
>
> Is it possible to have an expression index for use here with the GROUP BY
> clause?
>
> The PLAN is the following:
>
> PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB)))
>
> As you can see there is a SORT there caused by the GROUP BY clause.
>
> ASI_ANOEJE is SMALLINT
> ASI_CODSUC is SMALLINT
> ASC_FECHAX is DATE
> ASI_NUMCUE is VARCHAR(16)
> ASI_NUMSUB is CHAR(5)
>
> I'm using Firebird 2.5.4
>
> Greetings.
>
> Walter.
>
>
>
>
> 
>

Re: [firebird-support] Expression index for use with the GROUP BY clause

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Ok, thank you Karol.

Greetings.

Walter.


On Mon, Aug 24, 2015 at 2:32 PM, liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> as i say previously you can not do this
> as index can not be updated if something was changed in ASIENTOSCAB.
> And any subseclect is not recognized by plan parser as expression index def
>
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Monday, August 24, 2015 7:55 PM
> *To:* firebird-support@yahoogroups.com
> *Subject:* Re: [firebird-support] Expression index for use with the GROUP
> BY clause
>
>
>
> Hello Karol
>
> Thank you for your answer.
>
> The query is a very simplified one, just for show the idea.
>
> I can create an index as the following:
>
> CREATE INDEX IDX_ASIENTOSDET1 ON ASIENTOSDET COMPUTED BY (
>CAST(ASI_ANOEJE AS CHAR(5)) ||
>CAST(ASI_CODSUC AS CHAR(5)) ||
>CAST(EXTRACT(MONTH FROM (SELECT ASC_FECHAX FROM ASIENTOSCAB WHERE
> ASC_CODSUC = ASI_CODSUC AND ASC_IDENTI = ASI_IDECAB)) AS CHAR(2)) ||
>ASI_NUMCUE ||
>ASI_NUMSUB
> );
>
> But is is not used in the PLAN. I had tried several alternatives but
> without success until now. Using CAST(), without using CAST() and so on.
>
> I don't want to add a column ASI_FECHAX to my table ASIENTOSDET if I can
> avoid create it, but I need rows grouped by that column.
>
> ASIENTOSCAB and ASIENTOSDET have a parent-child relationship, where
> ASIENTOSCAB is the parent.
>
> Greetings.
>
> Walter.
>
>
>
>
>
>
> On Mon, Aug 24, 2015 at 1:40 PM, liviusliv...@poczta.onet.pl
> [firebird-support]  wrote:
>
>>
>> Hi,
>>
>> No – you can not create single index on more then one table
>> But you really need it?
>> How big is resultset? I do not see any filter in this query no HAVING nor
>> WHERE
>>
>> regards,
>> Karol Bieniaszewski
>>
>> *From:* mailto:firebird-support@yahoogroups.com
>> 
>> *Sent:* Monday, August 24, 2015 7:28 PM
>> *To:* firebird-support@yahoogroups.com
>> *Subject:* [firebird-support] Expression index for use with the GROUP BY
>> clause
>>
>>
>> Hello everybody
>>
>> I had the following query:
>>
>> SELECT
>>D.ASI_ANOEJE,
>>D.ASI_CODSUC,
>>EXTRACT(MONTH FROM C.ASC_FECHAX) AS ASI_NUMMES,
>>D.ASI_NUMCUE,
>>D.ASI_NUMSUB
>> FROM
>>ASIENTOSDET D
>> JOIN
>>ASIENTOSCAB C
>>   ON D.ASI_CODSUC = C.ASC_CODSUC AND
>>  D.ASI_IDECAB = C.ASC_IDENTI
>> GROUP BY
>>D.ASI_ANOEJE,
>>D.ASI_CODSUC,
>>EXTRACT(MONTH FROM C.ASC_FECHAX),
>>D.ASI_NUMCUE,
>>D.ASI_NUMSUB
>>
>> Is it possible to have an expression index for use here with the GROUP BY
>> clause?
>>
>> The PLAN is the following:
>>
>> PLAN SORT (JOIN (D NATURAL, C INDEX (PK_ASIENTOSCAB)))
>>
>> As you can see there is a SORT there caused by the GROUP BY clause.
>>
>> ASI_ANOEJE is SMALLINT
>> ASI_CODSUC is SMALLINT
>> ASC_FECHAX is DATE
>> ASI_NUMCUE is VARCHAR(16)
>> ASI_NUMSUB is CHAR(5)
>>
>> I'm using Firebird 2.5.4
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
>>
>
>
> 
>

Re: [firebird-support] Strategy to get a sequence in a strict ascending order for a log table

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Why you don't use IN AUTONOMOUS TRANSACTION DO?

So, when the sequence get its value you insert the row in the log table.

Greetings.

Walter.


On Tue, Sep 1, 2015 at 12:26 PM, kokok_ko...@yahoo.es [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> I am trying to create a log table. This table has a "version" column, it
> is an incremental number.
>
> In each transaction (ie a purchase, sale,...) , I add a record in this log
> table. The "version" column is assigned in the insert trigger using the
> value of a sequence.
>
>
> This version number is used to update mobile devices. They ask something
> like "send me all changes until version x".
>
>
>
> My problem is I need that the log table shows the records in a strict
> ascending order. It is not important if there are gaps or not.
>
>
> Example of the problem:
>
>
> 1-Star transaction A
>
> 2-Changes
>
> 3-Insert LOG record, version=1 (get it from sequence)
>
> 4-More changes
>
> 5-Commit A
>
>
> Now, between the 3 and 5 of A transaction, a concurrent connection do a
> smaller transaction:
>
>
> 1-Star transaction B
>
> 2-Changes
>
> 3-Insert LOG record, version=2  (get it from sequence)
>
> 4-Commit B
>
>
> And the commit B is done before the transaction A finishes. Then, in the
> log table, for an instant, I have  a record with the "2" as value. The
> table will not have the "1" until transaction A finishes.
>
>
> If before the A finishes, a mobile device asks for any changes, the
> server sends only "2" ("1" is not visible yet). The device updates its
> internal state and now it is updated to "2". The "1" log will never be
> applied.
>
>
> When transaction A finished, the "1" is added in the log, but now it is
> too late because the mobile device has been updated to "2" version.
>
>
>
> Which strategy can I use to solve this problem?
>
>
>
>
>
> 
>

Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Sean, and everybody

That's a very interesting and very important theme: "the new record version
will only include the updated fields -- not a full copy of the row"

Do you know where I can find a document, web-page, book or video that
explains that? If it has images or graphics, much better.

Thanks in advance.

Greetings.

Walter.


On Wed, Sep 16, 2015 at 11:40 AM, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
>
> > They will generate a comparable amount of garbage, but I think updating
> the
> > existing records is better than delete + insert, especially if there are
> also
> > foreign key references to those records.
>
> I don't think so.
>
> Unless you are updating every column in the row, the UPDATE will generate
> a smaller disk footprint, as the new record version will only include the
> updated fields -- not a full copy of the row.
>
> Then there is the impact on index structures that Vlad has pointed out.
>
>
> From a performance point of view, it would be *much faster*, if you are
> able to:
> - disable all indexes for the target table,
> - delete all rows,
> - import new rows and
> - reactivate all indexes for the target table
>
> This would likely require that you have near-exclusive access/use of the
> target table.
>
>
> Sean
>
> 
>

Re: [firebird-support] What is better: UPDATE or DELETE + INSERT?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much, Alexey.

Greetings.

Walter.


On Wed, Sep 16, 2015 at 1:36 PM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
>
> Hi,
>
> See page 20-21 here
>
> http://ru.scribd.com/doc/20128182/The-InterBase-and-Firebird-Developer-Magazine-Issue-2-2005
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
> Hello Sean, and everybody
>
> That's a very interesting and very important theme: "the new record
> version will only include the updated fields -- not a full copy of the row"
>
> Do you know where I can find a document, web-page, book or video that
> explains that? If it has images or graphics, much better.
>
> Thanks in advance.
>
> Greetings.
>
> Walter.
>
>
> 
>

[firebird-support] Firebird 2.5 Language Reference

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

Do you know what has happened with the translation of the document
"Firebird 2.5 Language Reference" from Russian to English?

They had requested for donation with the promise that their work would be
ready in February 2015.

The donation was made, now it is September 2015, but without news about the
document translated.

I want to translate from English to Spanish (and for free, I don't want to
receive money for that). I don't know much Russian and the Google
translator is not good from Russian to Spanish.

Greetings.

Walter.

Re: [firebird-support] Firebird 2.5 Language Reference

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much, Alexey

Is there an estimated date for the publication?

Greetings.

Walter.


On Wed, Sep 16, 2015 at 2:34 PM, Alexey Kovyazin a...@ib-aid.com
[firebird-support]  wrote:

>
>
> Hi,
>
> Translation was completed several weeks ago, now Helen Borrie performs
> proof-reading and editing of the text.
> Recent preview version of LR looks very promising.
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
> Hello everybody
>
> Do you know what has happened with the translation of the document
> "Firebird 2.5 Language Reference" from Russian to English?
>
> They had requested for donation with the promise that their work would be
> ready in February 2015.
>
> The donation was made, now it is September 2015, but without news about
> the document translated.
>
> I want to translate from English to Spanish (and for free, I don't want to
> receive money for that). I don't know much Russian and the Google
> translator is not good from Russian to Spanish.
>
> Greetings.
>
> Walter.
>
>
>
> 
>

[firebird-support] SELECT for get the number of contexts

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

Is there some SELECT that can give me the number of context a stored
procedure has?

As you know the maximum number of contexts is 255.

And it would be interesting to know how many contexts a stored procedure
has, without counting with the finger.

Other question:

Which are the comands that count as context?

I know: INSERT, UPDATE, DELETE, SELECT, UNION. Is there some more, JOIN or
FETCH maybe?

And the last question:

That number of 255, will be incremented in Firebird 3?

Greetings.

Walter.

Re: [firebird-support] Re: SELECT for get the number of contexts

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Dmitry for your answer, it is more clear now, however I
still have some doubts.

According to Vlad Khorsun, each INSERT, UPDATE or DELETE has 2 contexts,
cause of the variables NEW and OLD of the triggers. Is that not true?

The only way to know how many contexts a stored procedure has is looking at
the source code and counting handly them?

Greetings.

Walter.


On Sun, Sep 27, 2015 at 12:16 PM, Dmitry Yemanov
dim...@users.sourceforge.net [firebird-support] <
firebird-support@yahoogroups.com> wrote:

>
>
> 26.09.2015 05:59, 'Walter R. Ojeda Valiente' wrote:
> >
> > Is there some SELECT that can give me the number of context a stored
> > procedure has?
>
> No, it's impossible.
>
> > Other question:
> >
> > Which are the comands that count as context?
> >
> > I know: INSERT, UPDATE, DELETE, SELECT, UNION. Is there some more, JOIN
> > or FETCH maybe?
>
> It's not about commands, context is a table reference. A trivial INSERT
> statement has one context, join of three tables has three contexts, etc.
> Additionally, every UNION and GROUP BY produce one more context.
>
> > And the last question:
> >
> > That number of 255, will be incremented in Firebird 3?
>
> No.
>
> Dmitry
>
> 
>

[firebird-support] Firebird 3 compression data

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

According to this (very good) news,

http://www.firebirdnews.org/firebird-3-protocol-benchmark/

Firebird 3 can have compression of data.

Excellent!!!

However neither in the above page neither in the "Firebird 3.0 Release
Notes"

http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Beta2-ReleaseNotes.pdf

says how to compress data. Just that was implemented by Alex Peshkov.

So, the evident question is:

How to have data compressed?

Of course, I am talking about Firebird 3.

Greetings.

Walter.

Re: [firebird-support] Re: SELECT for get the number of contexts

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Ann, it's very clear now.

Greetings.

Walter.


On Sun, Sep 27, 2015 at 5:23 PM, Ann Harrison aharri...@ibphoenix.com
[firebird-support]  wrote:

>
>
>
>
> On Sun, Sep 27, 2015 at 1:37 PM, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com> wrote:
>
>>
>>
>> The only way to know how many contexts a stored procedure has is looking
>> at the source code and counting handly them?
>>
>>
> The limit on contexts is per statement, not for an entire stored
> procedure, trigger, or whatever.  A statement is a SELECT, INSERT, DELETE,
> or UPDATE.
>
> Each table reference is a context.  So, for example, a reflexive join has
> more than one context for the same table.  An update with lots of
> subselects has contexts for each part of the subselect.   A statement that
> references a view has contexts for every table reference in the view.
>
> So, don't worry about the total number of contexts in a stored procedure,
> but do worry about the use of complex views in apparently simple
> statements.
>
> Good luck,
>
> Ann
>
>
>
>
>>
>>>
>>>
>>
>>
>> --
>>
>
> 
>

Re: [firebird-support] Firebird 3 compression data

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Carlos

Thank you for your answer.

In your article you has written about the benchmark with and without
compression.

How you did that?

You have "FB 3 padrao" and "FB 3 compressao".

If I want to compare FB 3 against MySQL, Postegre, or whatever: how I can
have compression enabled or disabled?

Thanks in advance.

Greetings.

Walter.


On Sun, Sep 27, 2015 at 8:33 PM, 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support]  wrote:

>
>
> My article at fbnews talks only about wire protocol compression.
>
> I guess you are talking about data compression on the database file,
> right? If so, afaik, some "new" algorithms are being tested/benchmarked and
> may be available in FB in the future, resulting in a better compression
> than the actual "RLE", but I guess it will not be in FB 3.0
>
> Carlos
> Firebird Performance in Detail - http://videos.firebirddevelopersday.com
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
>
> According to this (very good) news,
>
> http://www.firebirdnews.org/firebird-3-protocol-benchmark/
>
> Firebird 3 can have compression of data.
>
> Excellent!!!
>
> However neither in the above page neither in the "Firebird 3.0 Release
> Notes"
>
>
> http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Beta2-ReleaseNotes.pdf
>
> says how to compress data. Just that was implemented by Alex Peshkov.
>
> So, the evident question is:
>
> How to have data compressed?
>
> Of course, I am talking about Firebird 3.
>
> Greetings.
>
> Walter.
>
>
>
> 
>

Re: [firebird-support] Firebird 3 compression data

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Carlos.

In the document:

http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Beta2-ReleaseNotes.pdf

Which I take as my reference, there is not a single word about the
parameter WireCompression.

It's clear now.

Greetings.

Walter.


On Mon, Sep 28, 2015 at 7:48 AM, 'Carlos H. Cantu' lis...@warmboot.com.br
[firebird-support]  wrote:

>
>
> Hi!
>
> Look at firebird.conf for the parameter WireCompression.
>
>
> Carlos
> Firebird Performance in Detail -
> http://videos.firebirddevelopersday.com
>
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
> Hello Carlos
>
> Thank you for your answer.
>
> In your article you has written about the benchmark with and without
> compression.
>
> How you did that?
>
> You have "FB 3 padrao" and "FB 3 compressao".
>
> If I want to compare FB 3 against MySQL, Postegre, or whatever: how I can
> have compression enabled or disabled?
>
> Thanks in advance.
>
> Greetings.
>
> Walter.
>
>
> On Sun, Sep 27, 2015 at 8:33 PM, 'Carlos H. Cantu' lis...@warmboot.com.br
> [firebird-support]  wrote:
>
> My article at fbnews talks only about wire protocol compression.
>
> I guess you are talking about data compression on the database file,
> right? If so, afaik, some "new" algorithms are being tested/benchmarked and
> may be available in FB in the future, resulting in a better compression
> than the actual "RLE", but I guess it will not be in FB 3.0
>
> Carlos
> Firebird Performance in Detail - http://videos.firebirddevelopersday.com
> www.firebirdnews.org - www.FireBase.com.br
>
>
>
>
>
>
>
> According to this (very good) news,
>
> http://www.firebirdnews.org/firebird-3-protocol-benchmark/
>
> Firebird 3 can have compression of data.
>
> Excellent!!!
>
> However neither in the above page neither in the "Firebird 3.0 Release
> Notes"
>
>
> http://web.firebirdsql.org/download/prerelease/rlsnotes/Firebird-3.0.0_Beta2-ReleaseNotes.pdf
>
> says how to compress data. Just that was implemented by Alex Peshkov.
>
> So, the evident question is:
>
> How to have data compressed?
>
> Of course, I am talking about Firebird 3.
>
> Greetings.
>
> Walter.
>
>
>
>
>
> 
>

Re: [firebird-support] Firebird 2.5 Language Reference

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Germán

Greetings.

Walter.


On Tue, Sep 29, 2015 at 10:10 PM, Germán Balbi bal...@yahoo.com
[firebird-support]  wrote:

>
>
> Para una mucho mejor traducción usa el traductor de Microsoft
> http://www.bing.com/translator/
>
>
> [image: image] 
>
>
>
>
>
> Traductor 
> Descargar aplicaciones Translator Translator Apps Skype Translator
> Ver en www.bing.com 
> Vista previa por Yahoo
>
>
>
>
>
> El Miércoles, 16 de septiembre, 2015 16:43:26, "Alexey Kovyazin
> a...@ib-aid.com [firebird-support]" 
> escribió:
>
>
>
> Hi,
>
> Translation was completed several weeks ago, now Helen Borrie performs
> proof-reading and editing of the text.
> Recent preview version of LR looks very promising.
>
> Regards,
> Alexey Kovyazin
> IBSurgeon
>
>
>
> Hello everybody
>
> Do you know what has happened with the translation of the document
> "Firebird 2.5 Language Reference" from Russian to English?
>
> They had requested for donation with the promise that their work would be
> ready in February 2015.
>
> The donation was made, now it is September 2015, but without news about
> the document translated.
>
> I want to translate from English to Spanish (and for free, I don't want to
> receive money for that). I don't know much Russian and the Google
> translator is not good from Russian to Spanish.
>
> Greetings.
>
> Walter.
>
>
>
>
> 
>

Re: Re: [firebird-support] How To Get Entire Linked Group Details

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

If you want to learn about recursivity with Firebird and you can read
Spanish, there are several articles that can teach you:

https://firebird21.wordpress.com/2015/08/22/usando-recursividad-con-cte/

https://firebird21.wordpress.com/2015/08/25/entendiendo-la-recursividad-en-los-select/

https://firebird21.wordpress.com/2013/10/10/stored-procedures-recursivos/

https://firebird21.wordpress.com/2015/08/27/ejemplo-de-recursion-1/

https://firebird21.wordpress.com/2015/08/28/ejemplo-de-recursion-2/

https://firebird21.wordpress.com/2015/08/29/ejemplo-de-recursion-3/

https://firebird21.wordpress.com/2015/08/30/ejemplo-de-recursion-4-actualizando-filas-recursivamente/

https://firebird21.wordpress.com/2015/08/31/ejemplo-de-recursion-5-saldos-acumulados/

https://firebird21.wordpress.com/2015/09/05/ejemplo-de-recursion-6-repitiendo-las-filas/

Greetings.

Walter.


On Fri, Oct 2, 2015 at 7:52 AM, Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support]  wrote:

>
>
> Hi Karol Bieniaszewski,
>
>
> Yess, YOU ARE THE MAN OF THE MOMENT.
>
> Your SQL just ROCKS, JUST ROCKS. AWESOME KAROL, JUST AWESOME.
>
>
> I am going to next level of my code where I need to find out that the
> deepest leaf, if it is not used in certain table then I would like to
> delete it.
>
> For time being one more questions (Please expect more in upcoming time on
> this issue :) ), is it possible to get the records for one entire leaf and
> it dependent leafs and then another leaf and its dependent and so on via
> your SQL ?
>
>
> Result order like:
>
> Vishal Group
> Vishal Group1
> Vishal Group1.1
> Vishal Group1.1.1
> Vishal Group2
> Vishal Group2.1
> Vishal Group2.1.1
> Vishal Group3
> Vishal Group4
> Vishal Group4.1
>
>
> With Best Regards.
>
> Vishal
>
>
>
>
> On Friday, 2 October 2015 4:59 PM, "liviuslivius
> liviusliv...@poczta.onet.pl [firebird-support]" <
> firebird-support@yahoogroups.com> wrote:
>
>
>
> Hi,
>
> i do not know if you describe your problem precisely.
> But i understand it like this:
> looks like you need all leaf from same parent (with parent included) as is
> for "Vishal Group1" and all its childs
>
> try this
>
>  WITH RECURSIVE
>  G1_PARENT AS
>  (
>  SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON
> MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1'
>  ),
>   R_TREE AS
>  (
>  SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION
> THEN GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT
>  FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
>  WHERE TT.LINKED_TO_GROUP IS NULL
>
>  UNION ALL
>
>  SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN
> GP.DESCRIPTION ELSE RT.PARENT END AS PARENT
>  FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
>  JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP
>  )
>  SELECT
>  *
>
>  FROM
>  R_TREE RT2
>  INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION
>  INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A
>
> with this query i got the same result as you showed in table below
>
> regards,
> Karol Bieniaszewski
>
>
> W dniu 2015-10-02 06:36:16 użytkownik Vishal Tiwari
> vishuals...@yahoo.co.in [firebird-support] <
> firebird-support@yahoogroups.com> napisał:
>
>
> Hi All,
>
> There is some change in my previous SQL. Please consider below SQLs.
>
> CREATE TABLE MYGROUP
> (
>   PK_GROUP GUID DEFAULT 'newid()' NOT NULL,
>   DESCRIPTION Varchar(255),
>   LINKED_TO_GROUP GUID,
>   PRIMARY KEY (PK_GROUP)
> );
>
> COMMIT;
>
> INSERT INTO MYGROU P (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{-111---} ', 'My Items', NULL);
>
>
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{CD1E33D1-1666-49B9-83BE-067687E4DDD6}', 'Vishal Group',
> '{-111---}');
>
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}', 'Vishal Group1',
> '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
> IN SERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{A87E921D-0468-497D-92C5-19AB63751EE8}', 'Vishal Group1.1',
> '{4B42E7A5-B14C-451B-ACF5-83DD8A983A58}');
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{0FDC729A-8FCC-4D23-8619-436A459835DD}', 'Vishal Group1.1.1',
> '{A87E921D-0468-497D-92C5-19AB63751EE8}');
>
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{2E15A2A9-7E40-422E-A5D6-C3F6C63F8591}', 'Vishal Group2',
> '{CD1E33D1-1666-49B9-83BE-067687E4DDD6}');
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}', 'Vishal Group2.1',
> '{2E15A2A9-7E40-422E-A5D6-C3F6C63F859 1}');
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{A326E6E3-030E-493B-AA0E-DC5D90DB080F}', 'Vishal Group2.1.1',
> '{5EAC9866-F406-4BBD-B7B3-5CEEC3877C9B}');
>
> INSERT INTO MYGROUP (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{3CF1FE37-EEC0-4E79-A3C5-DB78F6A9BC05}', 'Vishal Gr

Re: Re: [firebird-support] How To Get Entire Linked Group Details

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

No, sorry, just in Spanish. You can use some translation software, maybe
Google translator, and if you have some doubts just ask for help.

Greetings.

Walter.


On Fri, Oct 2, 2015 at 8:33 AM, Vishal Tiwari vishuals...@yahoo.co.in
[firebird-support]  wrote:

>
>
> Is it available in Engilsh ? so would be easy to learn for me. I am very
> much interested to learn recursive SQL.
>
> Karol, but please help me in getting base leaf also in the output.
>
> Thank You to both of You.
>
> With Best Regards.
>
> Vishal
>
>
>
> On Friday, 2 October 2015 5:55 PM, "'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]" <
> firebird-support@yahoogroups.com> wrote:
>
>
>
> If you want to learn about recursivity with Firebird and you can read
> Spanish, there are several articles that can teach you:
>
> https://firebird21.wordpress.com/2015/08/22/usando-recursividad-con-cte/
>
>
> https://firebird21.wordpress.com/2015/08/25/entendiendo-la-recursividad-en-los-select/
>
> https://firebird21.wordpress.com/2013/10/10/stored-procedures-recursivos/
>
> https://firebird21.wordpress.com/2015/08/27/ejemplo-de-recursion-1/
>
> https://firebird21.wordpress.com/2015/08/28/ejemplo-de-recursion-2/
>
> https://firebird21.wordpress.com/2015/08/29/ejemplo-de-recursion-3/
>
>
> https://firebird21.wordpress.com/2015/08/30/ejemplo-de-recursion-4-actualizando-filas-recursivamente/
>
>
> https://firebird21.wordpress.com/2015/08/31/ejemplo-de-recursion-5-saldos-acumulados/
>
>
> https://firebird21.wordpress.com/2015/09/05/ejemplo-de-recursion-6-repitiendo-las-filas/
>
> Greetings.
>
> Walter.
>
>
> On Fri, Oct 2, 2015 at 7:52 AM, Vishal Tiwari vishuals...@yahoo.co.in
> [firebird-support]  wrote:
>
>
> Hi Karol Bieniaszewski,
>
>
> Yess, YOU ARE THE MAN OF THE MOMENT.
>
> Your SQL just ROCKS, JUST ROCKS. AWESOME KAROL, JUST AWESOME.
>
>
> I am going to next level of my code where I need to find out that the
> deepest leaf, if it is not used in certain table then I would like to
> delete it.
>
> For time being one more questions (Please expect more in upcoming time on
> this issue :) ), is it possible to get the records for one entire leaf and
> it dependent leafs and then another leaf and its dependent and so on via
> your SQL ?
>
>
> Result order like:
>
> Vishal Group
> Vishal Group1
> Vishal Group1.1
> Vishal Group1.1.1
> Vishal Group2
> Vishal Group2.1
> Vishal Group2.1.1
> Vishal Group3
> Vishal Group4
> Vishal Group4.1
>
>
> With Best Regards.
>
> Vishal
>
>
>
>
> On Friday, 2 October 2015 4:59 PM, "liviuslivius
> liviusliv...@poczta.onet.pl [firebird-support]" <
> firebird-support@yahoogroups.com> wrote:
>
>
>
> Hi,
>
> i do not know if you describe your problem precisely.
> But i understand it like this:
> looks like you need all leaf from same parent (with parent included) as is
> for "Vishal Group1" and all its childs
>
> try this
>
>  WITH RECURSIVE
>  G1_PARENT AS
>  (
>  SELECT MGP.DESCRIPTION FROM MYGROUP MG INNER JOIN MYGROUP MGP ON
> MGP.PK_GROUP=MG.LINKED_TO_GROUP WHERE MG.DESCRIPTION='Vishal Group1'
>  ),
>   R_TREE AS
>  (
>  SELECT TT.PK_GROUP AS A, CAST(CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION
> THEN GP.DESCRIPTION ELSE '' END AS VARCHAR(255)) AS PARENT
>  FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
>  WHERE TT.LINKED_TO_GROUP IS NULL
>
>  UNION ALL
>
>  SELECT TT.PK_GROUP AS A, CASE WHEN TT.DESCRIPTION=GP.DESCRIPTION THEN
> GP.DESCRIPTION ELSE RT.PARENT END AS PARENT
>  FROM MYGROUP TT LEFT JOIN G1_PARENT GP ON 1=1
>  JOIN R_TREE RT ON RT.A = TT.LINKED_TO_GROUP
>  )
>  SELECT
>  *
>
>  FROM
>  R_TREE RT2
>  INNER JOIN G1_PARENT GP ON RT2.PARENT=GP.DESCRIPTION
>  INNER JOIN MYGROUP TT2 ON TT2.PK_GROUP=RT2.A
>
> with this query i got the same result as you showed in table below
>
> regards,
> Karol Bieniaszewski
>
>
> W dniu 2015-10-02 06:36:16 użytkownik Vishal Tiwari
> vishuals...@yahoo.co.in [firebird-support] <
> firebird-support@yahoogroups.com> napisał:
>
>
> Hi All,
>
> There is some change in my previous SQL. Please consider below SQLs.
>
> CREATE TABLE MYGROUP
> (
>   PK_GROUP GUID DEFAULT 'newid()' NOT NULL,
>   DESCRIPTION Varchar(255),
>   LINKED_TO_GROUP GUID,
>   PRIMARY KEY (PK_GROUP)
> );
>
> COMMIT;
>
> INSERT INTO MYGROU P (PK_GROUP, DESCRIPTION, LINKED_TO_GROUP) VALUES
> ('{-111---} ', 'My Items', NULL);
>
>
> INSERT INTO MYGROUP (

Re: [firebird-support] Order by

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Germán, as you can read Spanish, this article can be useful:

https://firebird21.wordpress.com/2014/11/20/mostrando-los-resultados-ordenados-por-cualquier-criterio/

Greetings.

Walter.


On Tue, Oct 6, 2015 at 6:17 PM, Germán Balbi bal...@yahoo.com
[firebird-support]  wrote:

>
>
> Excellent, very simple. Thank Karol
>
>
>
>
> El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl
> [firebird-support]"  escribió:
>
>
>
> Hi,
>
> ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Tuesday, October 06, 2015 5:20 PM
> *To:* Firebird Support 
> *Subject:* [firebird-support] Order by
>
>
> Hello everyone
>
> There is the possibility of ordering the result of a SELECT in an order
> specified by the user.
> For example
> having a field NAME
> in the clause ORDER BY 'PAUL, JOHN, PETER, MARIA'
>
> Of course, that should be all the options set
>
> Thank you.
>
> Hola a todos
>
> Existe la posibilidad de ordenar el resultado de un SELECT en un order
> especificado por el usuario.
> Por ejemplo
> teniendo un campo NOMBRE
> en la clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA'
>
> Claro esta, que se deben tener todas las opciones establecidas
>
> gracias.
>
>
> 
>

Re: [firebird-support] Order by

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Ok Germán.

Just for your advice, we have a forum too, you can ask your questions there
in Spanish, Portuguese or English. It is about Firebird, SQL and database
design. Usually you receive an answer in the next 24 hours.

http://yoforeo.com/firebird21

Greetings.

Walter.




On Wed, Oct 7, 2015 at 8:32 AM, Germán Balbi bal...@yahoo.com
[firebird-support]  wrote:

>
>
> Walter, conosco tu pagina me encanta, siempre compartiendo información de
> los problemas cotidianos.
> En general es el primer lugar a consultar, esta vez no fue así. Te pido
> disculpas [image: *;) guiño]. no volverá a pasar.
> Para tu tranquilidad al recivir la respuesta Karol, lo primero que hice
> fue buscar "ORDER BY CASE" y adivina a donde fui a caer. Si a Firebird21.
>
> Muchas gracias por tu dedicación.
> Te saludo desde Buenos Aires
> Germán
>
>
>
>
> El Martes, 6 de octubre, 2015 21:02:31, "'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]" <
> firebird-support@yahoogroups.com> escribió:
>
>
>
>
>
> Germán, as you can read Spanish, this article can be useful:
>
>
> https://firebird21.wordpress.com/2014/11/20/mostrando-los-resultados-ordenados-por-cualquier-criterio/
>
> Greetings.
>
> Walter.
>
>
> On Tue, Oct 6, 2015 at 6:17 PM, Germán Balbi bal...@yahoo.com
> [firebird-support]  wrote:
>
>
> Excellent, very simple. Thank Karol
>
>
>
>
> El Martes, 6 de octubre, 2015 16:32:08, "liviusliv...@poczta.onet.pl
> [firebird-support]"  escribió:
>
>
>
> Hi,
>
> ORDER BY CASE USER_NAME WHEN ‘PAUL’ THEN 1 WHEN ‘JOHN’ THEN 2 .. END
>
> regards,
> Karol Bieniaszewski
>
> *From:* mailto:firebird-support@yahoogroups.com
> 
> *Sent:* Tuesday, October 06, 2015 5:20 PM
> *To:* Firebird Support 
> *Subject:* [firebird-support] Order by
>
>
> Hello everyone
>
> There is the possibility of ordering the result of a SELECT in an order
> specified by the user.
> For example
> having a field NAME
> in the clause ORDER BY 'PAUL, JOHN, PETER, MARIA'
>
> Of course, that should be all the options set
>
> Thank you.
>
> Hola a todos
>
> Existe la posibilidad de ordenar el resultado de un SELECT en un order
> especificado por el usuario.
> Por ejemplo
> teniendo un campo NOMBRE
> en la clausula ORDER BY 'PABLO, JUAN, PEDRO, MARIA'
>
> Claro esta, que se deben tener todas las opciones establecidas
>
> gracias.
>
>
>
>
>
> 
>

Re: [firebird-support] Sort hierarchical data

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

If you can read Spanish, there are several articles about that topic:

https://firebird21.wordpress.com/2013/10/10/stored-procedures-recursivos/

https://firebird21.wordpress.com/2015/08/22/usando-recursividad-con-cte/

https://firebird21.wordpress.com/2015/08/27/ejemplo-de-recursion-1/

https://firebird21.wordpress.com/2015/08/28/ejemplo-de-recursion-2/

https://firebird21.wordpress.com/2015/08/29/ejemplo-de-recursion-3/

https://firebird21.wordpress.com/2015/08/30/ejemplo-de-recursion-4-actualizando-filas-recursivamente/

https://firebird21.wordpress.com/2015/08/31/ejemplo-de-recursion-5-saldos-acumulados/

https://firebird21.wordpress.com/2015/09/05/ejemplo-de-recursion-6-repitiendo-las-filas/

Greetings.

Walter.




On Wed, Oct 21, 2015 at 6:37 AM, josef.gschwendt...@quattro-soft.de
[firebird-support]  wrote:

>
>
> Hi,
>
> I have a table with hierarchical
> data.
>
> There is a numeric sortfield where the user sets the sort-order of the
> children of a particular parent (values from 1 to n für each parent).
>
>
> How can I write a recursive query which selects the whole tree and honors
> these sortnumbers.
>
>
> TIA,
>
> Josef
>
> 
>

Re: [firebird-support] Writing to Aliases.conf in non-admin mode

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Well, the more easy answer is not install Firebird under the folder
"Program files", because Windows since its Vista version has protected that
folder.

You can install Firebird in C:\FIREBIRD\ or something so and no problems
writing to any of its files.

Greetings.

Walter.



On Sat, Oct 24, 2015 at 1:47 AM, Venus Software Operations
venussof...@gmail.com [firebird-support] 
wrote:

>
>
>
>
> On 23/10/2015 10:24 pm, Eduardo guse...@gmail.com [firebird-support]
> wrote:
>
>
>
> I did what (I think is what) you need many years ago. I don´t remember
> exactly the details, but this is still working. What I did was the
> following:
>
> When my application initiates, it checks if there exists a database named
> MYAPPLICATIONCONFIGURACION.FDB. If it doesn't exist, it creates it. When
> the application creates the database, it creates 3 UDFs executing SQLs
> begining with "DECLARE EXTERNAL FUNCTION ..."
>
> The UDFs are the following (the names are in spanish):
>
> MMudfUbicacionDelAlias
> It is called with a parameter "Alias". It opens the file aliases.conf,
> look for the alias and returns a string with the path of the database.
>
> MMudfPudeEstablecerAlias
> It is called with a parameter "Alias" and a parameter "Path". It opens the
> file aliases.conf, and adds a new line with Alias=Path.
>
> MMudfPudeCrearDirectorio
> It is called with a parameter "Directory". It creates a folder in the hard
> disk with the name indicated.
>
> In the file aliases.conf, there is a line MYAPPLICATIONCONFIGURACION =
> C:\...\MYAPPLICATIONCONFIGURACION.FDB.
>
>
> When a user wants to create a new database, the application opens the
> database MYAPPLICATIONCONFIGURACION and executes the following SQL:
>
> SELECT MMudfPudeEstablecerAlias('Alias','Path') FROM RDB$DATABASE
>
> This can be done using the application in the server or in any client PC
> of the newtork.
>
> Regards
>
> Eduardo
>
> Thanks Eduardo.  I would never have thought of going this way, not good
> with FireBird procedural coding.  Like I wrote to Stefan above I found a
> different way to go about this.  I took a copy of the Aliases.conf to a
> temp folder and updated it as necessary.  Then I used ShellExecute Windows
> API with "runas" parameter to run a custom batch file that does the replace
> of the Alias.conf file with the updated one in the temp folder.  That
> brings up the UAC prompt but that is fine I don't mind announcing that to
> the user of a system change.
>
> Kind regards
> Bhavbhuti
>
> 
>

Re: [firebird-support] error writing data to the connection

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

You can know that the connection is active sending a request to the Server,
something like:

SELECT
   CURRENT_TIME
FROM
   RDB$DATABASE

In your application, using a control Timer, you can send such command each
minute or so. If there is not response then connection is dead.

For connecting again with the Server, use Classic, not SuperServer. You can
download a .ZIP file from FIREBIRDSQL.ORG, there are .BAT files there that
you can use for run the Server.

Greetings.

Walter.




On Sun, Dec 20, 2015 at 4:18 AM, arash_iranb...@yahoo.com
[firebird-support]  wrote:

>
>
> hi
>
> My application connects to a database at the start and stays connected
> while the application is running. The problem occurs when the connection is
> lost. It just stays disconnected until I restart my application.
>
> The error message is :
>
> Unable to complete network request to host "...".
> Error writing data to the connection.
> An existing connection was forcibly closed by the remote host.
>
> If I disconnect and connect that solves the problem as well, but how can I
> do that automatically? How can I detect that the connection has been lost?
>
>
> 
>

Re: [firebird-support] Numeration without hole, Is right Before Insert Trigger?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello Luigi

If you can read Spanish, this article can be useful:

https://firebird21.wordpress.com/2014/09/11/algo-mas-sobre-transacciones-optimistas-y-transacciones-pesimistas/

Greetings.

Walter.


On Thu, Dec 24, 2015 at 7:05 AM, Svein Erling Tysvær setys...@gmail.com
[firebird-support]  wrote:

>
>
> Also, take a look at this ancient document that used to be the standard
> answer to people asking the same question as yours:
> http://ibobjects.com/docs/ti_AuditableSeries.ZIP
>
> HTH,
> Set
>
> 2015-12-22 20:26 GMT+01:00 Ann Harrison aharri...@ibphoenix.com
> [firebird-support] :
>
>>
>>
>> On Tue, Dec 22, 2015 at 9:40 AM, Luigi Siciliano luigi...@tiscalinet.it
>> [firebird-support]  wrote:
>>
>>>
>>>I must assign a serial number, without hole, in a column of a fiscal
>>> document.  I must assign the number only when I know if the document is
>>> complete
>>> and I think the right moment is on a Before Insert Trigger for the table.
>>>
>>
>> Yes that's a good place, but you've got to be very careful.
>> Generators/Sequences won't
>> work because they're deliberately non-transactional.  Once you take one
>> its gone and if
>> your operation fails, you'll have a hole.
>>
>>>
>>> Is right or the insertion can fail? If not right, when I must assign the
>>> number to be sure of not have a hole in numeration?
>>>
>>
>> One way to get numbers without holes is to create a table with one field
>> that contains
>> the seed for  your numbers.  In your before insert trigger update that
>> field adding one to it,
>> then read to get the new value.  Unfortunately, if someone else has
>> inserted a record
>> concurrently, your transaction will wait then get an error and you'll
>> need to re-run the
>> whole thing.
>>
>>  Check the FAQ's at FirebirdSQL.org for other ways of handling this
>> problem.
>>
>>
>> Good luck,
>>
>> Ann
>>
>>
>>>
>>>
>>
>>
> 
>

Re: [firebird-support] access column by column name

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

You can use EXECUTE STATEMENT for that.

Greetings.

Walter.


On Sat, Dec 26, 2015 at 1:50 PM, slucas slu...@iquanta.com
[firebird-support]  wrote:

>
>
> hello one and all,
>
> i would like to write a stored procedure which looks something like:
>
> CREATE PROCEDURE IC_INITIATE(d CHAR(3), CN CHAR(20))
> RETURNS (IC CHAR(22))
> AS
> BEGIN
> SELECT "CN" FROM TABLE WHERE (DIV = :d) INTO :IC;
> ...MAYBE OTHER STUFF to IC...
> UPDATE TABLE SET "CN" = :IC WHERE (DIV = :d);
> SUSPEND;
> END
>
> so CN is in quotes because i don't know how to pass a field/column name
> as the CN parameter and then use that CN as an actual field in a table.
>
> how can i do that? thanx in advance and merry christmas. lucas
> 
>

Re: [firebird-support] Update table column from another table

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Floating point values are not exact values, there is not guarantee.

If you need exact values, use NUMERIC or DECIMAL instead.

Greetings.

Walter.


On Sat, Jan 2, 2016 at 11:28 AM, Sonya Blade sonyablade2...@hotmail.com
[firebird-support]  wrote:

>
>
>
> Yes I tried that it wasn't selecting at all, it was due to the floating
> point comparison. It seems that hit the wall about that
> floating point precision I eliminate that with subtracting the coumns and
> taking the absolute value if it is less than 0.01 epsilon
> reference value.
>
> It worked for now, but I'd like to know what is the decent way of solving
> such issues.
>
> Regards,
>
>
>
> hi,
>
> did you try select instead of update to see if select return all records
> which you try to update?
>
> regards,
> Karol Bieniaszewski
>
>
>  Oryginalna wiadomość 
> Od: "Sonya Blade sonyablade2...@hotmail.com [firebird-support]" <
> firebird-support@yahoogroups.com>
> Data: 02.01.2016 08:24 (GMT+01:00)
> Do: firebird-support@yahoogroups.com
> Temat: [firebird-support] Update table column from another table
>
>
>
> Dear All,
>
> I have difficulty with updating column records of one table from another
> table columns, which have common columns for relation.
> Executed query is as follow :
>
> update elements E set E.END_I = (select n.node_num from nodes N
> where (E.X_I =N.XI and E.Y_I = N.YI and E.Z_I=N.ZI) )
> where exists (select 1 from nodes N where (E.X_I =N.XI and E.Y_I = N.YI
> and E.Z_I=N.ZI))
>
> The problem with that is , not all the columns of Elements are updated,
> only one portion is correctly updated and rest
> remains intact. I tried to follow instructions here
> http://www.firebirdfaq.org/faq323/ but without success. I use old version
> of
> Firebird which is ver 2.1.
>
> What is rong with the code above,
>
> Regards,
>
> 
>

Re: [firebird-support] Database design. Simple question!

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

To have a separate table for a single column is a good idea if the column
will be of type BLOB and will be used just sometimes.

Greetings.

Walter.




On Mon, Jul 25, 2016 at 5:51 PM, shg_siste...@yahoo.com.ar
[firebird-support]  wrote:

>
>
> Hello! I  have a FB 2.5 database with a tabla "invoices". I need to add a
> field in that table which will be null most of the times. Lefts say, that
> 90% of the records will have this new field in null.
>
>
> What is your advice? To create a new table with a FK to "invoices" and
> save the needed field in a separate table (only when the value is not null
> of course) of just create the new field in the main "invoices" table?
>
>
> I think I read somewhere that null fields use very little space in the
> database. Is that true?
>
>
> Thanks for any input!!!
>
>
> Sergio
>
> 
>

Re: [firebird-support] Speed of comparing one field to another

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Yes, you can, using an expression index

CREATE INDEX IDX_MYINDEX ON MY_TABLE COMPUTED BY (INVOICED - RECEIVED);

and your SELECT would be:

SELECT
   *
FROM
   MY_TABLE
WHERE
   INVOICED - RECEIVED < 0

That way you will not need to create an additional column neither to do an
UPDATE neither to create a trigger.

Greetings.

Walter.


On Tue, Sep 13, 2016 at 9:26 AM, Maya Opperman m...@omniaccounts.co.za
[firebird-support]  wrote:

>
>
> Hi,
>
>
>
> I have a large table, and I’m only extracting a few records using:
>
>
>
> Select t.ID
>
> From my_table t
>
> Where t.Invoiced < t.Received
>
>
>
> Invoice and received are of type decimal(18,5)
>
>
>
> IBExpert is reporting these as unindexed reads.
>
>
>
> Is there an index I could create to speed up the loading?
>
>
>
> Thanks in advance
>
> Maya
>
>
>
>
>
> 
>

[firebird-support] Data pages and data page slots

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

When I run GSTAT with the switch -data it shows me numbers after "data
pages" and "data page slots".

Usually these numbers are the same, but ... what happen when they are
different?

I understood that the difference between them means the count of pages
without any relevant data. Pages allocated some time and not more useful
for that table at this moment because they just have garbage.

However, if I want to delete those pages, it is not possible, neither with
GFIX -sweep neither with a SELECT COUNT(*) FROM MyTable

In both cases GSTAT -data continues showing the same numbers.

For example, I have a table whose "data pages" is 72 and their "data page
slots" is 975,

Is there something wrong there? Is possible (and useful) make these numbers
equals? which is the meaning of those numbers?

Someone can explain me that or tell me where I can find information? Mr
Google was not polite with me.

Of course, I know a cycle backup/restore put both numbers equals, but ...
is that the only way? or I can make "data pages" equal to "data page slots"
without a cycle backup/restore?

Greetings.

Walter.

Re: [firebird-support] Re: Data pages and data page slots

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Vlad, that is what I was thinking but always is very
better to have the explanation of one expert.

Greetings.

Walter.

On Tue, Dec 13, 2016 at 11:25 AM, hv...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> ---In firebird-support@yahoogroups.com, 
> wrote :
>
> > When I run GSTAT with the switch -data it shows me numbers after "data
> pages" and "data page slots".
> >
> > Usually these numbers are the same, but ... what happen when they are
> different?
>
>   Physical numbers of data pages stored at kind of array and data of this
> array are split over
> pointer pages. Every item of array also called "slot". Slot could be empty
> or contain data page
> number. When table grows, engine allocates new data pages and put its
> numbers into empty
> slots (allocating pointer pages when necessary). When data pages is freed,
> slots become empty
> but they are still present. Pointer pages freed only when table is
> dropped. Therefore number of
> "data page slots" could be much larger than number of "data pages".
>
> Hope it helps,
> Vlad
>
> 
>

[firebird-support] Is this a bug of Firebird?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

CREATE PROCEDURE TEST
RETURNS(
  lcName VARCHAR(40))
AS
BEGIN
   FOR SELECT
  ALU_NOMBRE,
  ALU_APELLD
   FROM
  ALUMNOS
   INTO
  :lcName,
  :lcName
   DO
  SUSPEND;

END;

In the above stored procedure there are 2 columns in the FOR SELECT but
just 1 variable name.

It seems a bug for me. Or there are some reasons for allowing to do such
thing?

Of course, the fault is of the programmer, but it can be detected at
compilation time.

Firebird version: 2.5.5

Greetings.

Walter.

Re: [firebird-support] Is this a bug of Firebird?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Yes, but I can not know the value of the column X.ALU_NOMBRE

And the idea, of course, is know that value, that's why it appears in the
FOR SELECT. If not, I can do nothing with X.ALU_NOMBRE

Greetings.

Walter.



On Wed, Feb 8, 2017 at 12:02 PM, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support]  wrote:

> 08.02.2017 16:55, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com
> [firebird-support] wrote:
> > Of course, the fault is of the programmer, but it can be detected at
> compilation time.
>
>Why? It is expanded into code like this:
>
> FOR SELECT ALU_NOMBRE, ALU_APELLD  AS CURSOR X DO
>lcName = X.ALU_NOMBRE;
>lcName = X.ALU_APELLD;
>
>Pointless, but valid.
>
> --
>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] Is this a bug of Firebird?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

The error is of the programmer, I agree with you, but to repeat the name of
a variable without the Firebird showing any message of error is, at least
for me, a bug.

To have 2 or more variables with the same name after the INTO clause is
useless. The compiler can be smart enough to detect such thing.

Or not?

Greetings.

Walter.

On Wed, Feb 8, 2017 at 2:36 PM, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
>
>
> > Yes, but I can not know the value of the column X.ALU_NOMBRE
> >
> > And the idea, of course, is know that value, that's why it appears in
> the FOR
> > SELECT. If not, I can do nothing with X.ALU_NOMBRE
>
> You are asking for the system to evaluate the *intent* of logic.
>
> That is completely outside the purview of any application environment that
> I know.
>
> The only thing that a system can check/enforce is the correctness of the
> code, not to check whether the developer has 2 brain cells.
>
>
> Sean
>
> 
>

Re: [firebird-support] Is this a bug of Firebird?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

A warning would be enough.

If after the INTO clause there are 2 or 3 variables, no problem, it will be
very easy to detect the mistake. But sometimes that is not the case because
there are 20+ variables and if variable 3 is called "lcName" and variable
24 is called "lcName" too, the programmer can have a hard time detecting
the problem.

Greetings.

Walter.

On Thu, Feb 9, 2017 at 12:19 PM, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
> Doug,
>
> > Delphi's latest compiler provides a hint in these cases:
> >
> > var
> >   x: Int32;
> > begin
> >   try
> > x := 0;
> > x := 1;
> > writeln(x);
> > end.
> >
> > [dcc32 Hint] Project1.dpr(14): H2077 Value assigned to 'x' never used
> > (Line 14 is the one assigning zero.)
>
> In hindsight my post should have read more like:
>
> - No compiler would treat the condition as an error. A warning, Yes. But
> not an error.
>
> - There are plenty of DB tools (IBExpert for one) which perform their own
> "syntax checking" and report warnings (though I would need to test if IBE
> reports a warning for this case).
>
> - AFAIK, the FB engine only reports errors. So, a new set of
> functions/methods would need to be developed to report and handle warnings.
>
>
> Sean
>
> 
>

[firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

If I connect to a database "Database1" using ISQL, I can write something
like:

OUTPUT MyFile.DAT;
SELECT * FROM MyTable;
OUTPUT;

And all the rows of "MyTable" will go to the text file "MyFile.DAT". That's
ok and works fine.

But now, I want to connect to "Database2", which also have a table called
"MyTable" and with the same structure.

After that, I want to insert into "MyTable" (of "Database2") the rows
contained in "MyFile.DAT"

How can I do such thing without a lot of effort writing an INSERT command
in each line of "MyFile.DAT"?

Greetings.

Walter.

Re: [firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much Set, I will try your advice.

Off topic: can you say me what the letters HTH means?

Greetings.

Walter.


On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com
[firebird-support]  wrote:

>
>
> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support]:
>
> Hello everybody
>
> If I connect to a database "Database1" using ISQL, I can write something
> like:
>
> OUTPUT MyFile.DAT;
> SELECT * FROM MyTable;
> OUTPUT;
>
> And all the rows of "MyTable" will go to the text file "MyFile.DAT".
> That's ok and works fine.
> But now, I want to connect to "Database2", which also have a table called
> "MyTable" and with the same structure.
> After that, I want to insert into "MyTable" (of "Database2") the rows
> contained in "MyFile.DAT"
> How can I do such thing without a lot of effort writing an INSERT command
> in each line of "MyFile.DAT"?
>
> Greetings.
> Walter.
>
>
> Hi Walter. I'm not directly answering your question (mainly due to not
> using isql myself, so I don't know the answer), but I can think of two
> alternative ways.
>
> (1) (the more standard answer) Make sure your output file is in a fixed
> length format, then create an external table for this file, transfer the
> data using INSERT INTO "MyTable" SELECT * FROM
>  and then finally drop the external table.
>
> (2) Use qli rather than isql to transfer data directly using something
> similar to this recipe:
> https://www.ibphoenix.com/resources/documents/how_to/doc_42
> qli is an ancient part of InterBase, probably used before SQL was
> invented, you can find it in the same directory as isql.
>
> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5), but
> it worked nicely when I needed it. Though I don't think qli has been
> updated for the last few Firebird versions, so maybe it doesn't work
> anymore. And you may have a hard time finding information about it beyond
> the document referred to above. Though I suppose:
>
> ready "Database1" as src;
> ready "Database2" as trg;
> trg."MyTable" = src."MyTable";
>
> doesn't need much explanation and should be simple for you to try on a
> test database. I expect it to replace the target rather than add to it
> (though I don't know), but the one time I used qli, I think I was
> transferring to an empty table.
>
> HTH,
> Set
>
> 
>

Re: [firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you, Sean

Greetings.

Walter.


On Wed, Feb 15, 2017 at 6:43 PM, 'Leyne, Sean' s...@broadviewsoftware.com
[firebird-support]  wrote:

>
>
> Walter,
>
> > If I connect to a database "Database1" using ISQL, I can write something
> like:
> >
> > OUTPUT MyFile.DAT;
> > SELECT * FROM MyTable;
> > OUTPUT;
> >
> > And all the rows of "MyTable" will go to the text file "MyFile.DAT".
> That's ok
> > and works fine.
> >
> > But now, I want to connect to "Database2", which also have a table
> called
> > "MyTable" and with the same structure.
> >
> > After that, I want to insert into "MyTable" (of "Database2") the rows
> > contained in "MyFile.DAT"
> >
> > How can I do such thing without a lot of effort writing an INSERT
> command in
> > each line of "MyFile.DAT"?
>
> The Firebird ISQL tool does not support such a feature.
>
> You could create 2 output files and then use command line utility to
> combine results into single file.
>
> There are some third-party tools which could allow you to script this
> process (I know that IBExpert supports it)
>
>
> Sean
> 
>

Re: [firebird-support] Re: Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you Vlad, but is not for me, is for an article of my blog, I am
showing the alternatives for exporting data.

firebird21.wordpress.com

Greetings.

Walter.


On Thu, Feb 16, 2017 at 3:44 AM, hv...@users.sourceforge.net
[firebird-support]  wrote:

>
>
> ---In firebird-support@yahoogroups.com, 
> wrote :
>
> Hello everybody
>
> If I connect to a database "Database1" using ISQL, I can write something
> like:
>
> OUTPUT MyFile.DAT;
> SELECT * FROM MyTable;
> OUTPUT;
>
> And all the rows of "MyTable" will go to the text file "MyFile.DAT".
> That's ok and works fine.
>
> But now, I want to connect to "Database2", which also have a table called
> "MyTable" and with the same structure.
>
> After that, I want to insert into "MyTable" (of "Database2") the rows
> contained in "MyFile.DAT"
>
> How can I do such thing without a lot of effort writing an INSERT command
> in each line of "MyFile.DAT"?
>
> If you need to export some data from one Firebird database to another
> Firebird database, use EXECUTE STATEMENT ... ON EXTERNAL and forget about
> files.
>
> Regards,
> Vlad
>
> 
>

Re: [firebird-support] Is there an easy way for input rows from a script file?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you Set, now I know the meaning of HTH.

Greetings.

Walter.


On Thu, Feb 16, 2017 at 4:09 AM, Svein Erling Tysvær setys...@gmail.com
[firebird-support]  wrote:

>
>
> HTH = Hope this (alternatively: that) helps
>
> 2017-02-16 3:06 GMT+01:00 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com>:
>
>>
>>
>> Thank you very much Set, I will try your advice.
>>
>> Off topic: can you say me what the letters HTH means?
>>
>> Greetings.
>>
>> Walter.
>>
>>
>> On Wed, Feb 15, 2017 at 6:31 PM, setysvar setys...@gmail.com
>> [firebird-support]  wrote:
>>
>>>
>>>
>>> Den 15.02.2017 17:07, skrev 'Walter R. Ojeda Valiente'
>>> sistemas2000profesio...@gmail.com [firebird-support]:
>>>
>>> Hello everybody
>>>
>>> If I connect to a database "Database1" using ISQL, I can write something
>>> like:
>>>
>>> OUTPUT MyFile.DAT;
>>> SELECT * FROM MyTable;
>>> OUTPUT;
>>>
>>> And all the rows of "MyTable" will go to the text file "MyFile.DAT".
>>> That's ok and works fine.
>>> But now, I want to connect to "Database2", which also have a table
>>> called "MyTable" and with the same structure.
>>> After that, I want to insert into "MyTable" (of "Database2") the rows
>>> contained in "MyFile.DAT"
>>> How can I do such thing without a lot of effort writing an INSERT
>>> command in each line of "MyFile.DAT"?
>>>
>>> Greetings.
>>> Walter.
>>>
>>>
>>> Hi Walter. I'm not directly answering your question (mainly due to not
>>> using isql myself, so I don't know the answer), but I can think of two
>>> alternative ways.
>>>
>>> (1) (the more standard answer) Make sure your output file is in a fixed
>>> length format, then create an external table for this file, transfer the
>>> data using INSERT INTO "MyTable" SELECT * FROM
>>>  and then finally drop the external table.
>>>
>>> (2) Use qli rather than isql to transfer data directly using something
>>> similar to this recipe:
>>> https://www.ibphoenix.com/resources/documents/how_to/doc_42
>>> qli is an ancient part of InterBase, probably used before SQL was
>>> invented, you can find it in the same directory as isql.
>>>
>>> I've only used qli once many years ago (probably on Fb 0.9.4 or 1.5),
>>> but it worked nicely when I needed it. Though I don't think qli has been
>>> updated for the last few Firebird versions, so maybe it doesn't work
>>> anymore. And you may have a hard time finding information about it beyond
>>> the document referred to above. Though I suppose:
>>>
>>> ready "Database1" as src;
>>> ready "Database2" as trg;
>>> trg."MyTable" = src."MyTable";
>>>
>>> doesn't need much explanation and should be simple for you to try on a
>>> test database. I expect it to replace the target rather than add to it
>>> (though I don't know), but the one time I used qli, I think I was
>>> transferring to an empty table.
>>>
>>> HTH,
>>> Set
>>>
>>>
>>
>>
>>
> 
>

[firebird-support] Entity Relationship Diagram

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everyone

Excuse me if it is an off-topic.

Do you know some good program for Entity Relationship Diagram using
Firebird databases?

I mean, having a Firebird database get its ERD

Mister Google can answer that question but is not the same as comments of
people who are using such programs.

Greetings.

Walter.

[firebird-support] How to get the the tables and the columns of a Foreign Key?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

I had defined a Foreign Key as:

ALTER TABLE ADHERENTES
ADD CONSTRAINT FK_ADHERENTES
FOREIGN KEY (ADH_SERVID, ADH_IDECAB)
REFERENCES CLIENTES(CLI_SERVID, CLI_IDENTI)
ON DELETE CASCADE
ON UPDATE CASCADE;

Of course, that work very well.

However, after watching the system tables I can not find a simple way to
build a SELECT what can give me the name of the tables and the columns
involved. Something as:

SELECT ... something

Result:
ADHERENTES, ADH_SERVID, ADH_IDECAB, CLIENTES, CLI_SERVID, CLI_IDENTI

Can somebody tell me how to get that result?

Thank you very much in advance.

Greetings.

Walter.

Re: [firebird-support] How to get the the tables and the columns of a Foreign Key?

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Thank you very much SET.

Yes, it will be useful. No exactly what I was looking for but good enough
for know the names of the tables and the columns involved.

Greetings.

Walter.


On Tue, May 30, 2017 at 3:38 AM, Svein Erling Tysvær setys...@gmail.com
[firebird-support]  wrote:

>
>
> Hopefully this can get you started (though since your key consists of two
> fields, I would expect it to return four rows, not one row with two tables
> and four fields):
>
> select iChild.rdb$Relation_name, isChild.rdb$field_name,
> iMain.rdb$relation_name, isMain.rdb$field_name
> from rdb$indices iChild
> join rdb$index_segments isChild on iChild.rdb$index_name =
> isChild.rdb$index_name
> join rdb$indices iMain on iChild.rdb$Foreign_key = iMain.rdb$index_name
> join rdb$index_segments isMain on iMain.rdb$index_name =
> isMain.rdb$index_name
> where iChild.rdb$index_name = 'FK_ADHERENTES'
>
> HTH,
> Set
>
> 2017-05-30 7:10 GMT+02:00 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com [firebird-support] <
> firebird-support@yahoogroups.com>:
>
>>
>>
>> Hello everybody
>>
>> I had defined a Foreign Key as:
>>
>> ALTER TABLE ADHERENTES
>> ADD CONSTRAINT FK_ADHERENTES
>> FOREIGN KEY (ADH_SERVID, ADH_IDECAB)
>> REFERENCES CLIENTES(CLI_SERVID, CLI_IDENTI)
>> ON DELETE CASCADE
>> ON UPDATE CASCADE;
>>
>> Of course, that work very well.
>>
>> However, after watching the system tables I can not find a simple way to
>> build a SELECT what can give me the name of the tables and the columns
>> involved. Something as:
>>
>> SELECT ... something
>>
>> Result:
>> ADHERENTES, ADH_SERVID, ADH_IDECAB, CLIENTES, CLI_SERVID, CLI_IDENTI
>>
>> Can somebody tell me how to get that result?
>>
>> Thank you very much in advance.
>>
>> Greetings.
>>
>> Walter.
>>
>>
>>
> 
>

[firebird-support] Connected databases to the Server

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everyone

There is some way to know which databases are connected actually at the
Server?

Maybe 3 databases are connected now, or maybe 17, or maybe 83, how can I
know that?

Which databases are connected, right now?

Thanks in advance.

Greetings.

Walter.



Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>

Re: [firebird-support] Connected databases to the Server

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Doesn't work, always shows just 1 database, the current one. Sometimes with
several instances, but always the same database name.

Greetings.

Walter.


On Sun, Oct 8, 2017 at 5:25 AM, Dimitry Sibiryakov s...@ibphoenix.com
[firebird-support]  wrote:

> 07.10.2017 22:57, 'Walter R. Ojeda Valiente'
> sistemas2000profesio...@gmail.com
> [firebird-support] wrote:
> > Which databases are connected, right now?
>
>Use MON$ATTACHMENTS with SYSDBA rights.
>
>
> --
>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] detect if database has changed

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

There are several ways for doing that.

One of them, is to create a table, called CHANGES, by example, and with the
columns: TABLENAME, USERNAME, DATEANDTIME, etc.

Then, for each table of your interest, you create a trigger which inserts a
row in the table CHANGES when a user do an INSERT, UPDATE, or DELETE in
such table.

This way, you can always know when was the last time someone did an INSERT,
UPDATE, or DELETE in the interesting tables.

If you want a table with few rows, then you can have in your table CHANGES
a row for each table and the columns LASTINSERT, LASTUPDATE, LASTDELETE.
So, if you are interested in 20 tables, your table CHANGES will have 20
rows. The problem will be the frequents deadlocks if you don't manage well
your transactions.

Greetings.

Walter.

On Mon, Oct 9, 2017 at 5:06 AM, liviuslivius liviusliv...@poczta.onet.pl
[firebird-support]  wrote:

>
>
> Hi,
>
> You can also look at sample at
>
> http://itstop.pl/pl-pl/porady/Firebird/FAQ1/czas-ostatniej-
> modyfikacji-tabeli
>
> It is in PL lang but triggers are fully descriptive. I prefer generator
> based approach
>
>
> Regards,
> Karol Bieniaszewski
>
> 
>

Re: Re[2]: [firebird-support] select column if it exists

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

What about:

SELECT
   A,
   B,
   COALESCE(C, 'some value here')
FROM
   MyTable

Greetings.

Walter.


On Tue, Oct 10, 2017 at 2:56 PM, 'Daniel Miller' dmil...@amfes.com
[firebird-support]  wrote:

>
>
> I don't think you could express it in the SELECT directly (someone else
> will probably build a statement refuting that) but you could do:
>
> SELECT r.RDB$FIELD_NAME FROM RDB$RELATION_FIELDS r where
> r.RDB$RELATION_NAME=
>
> and then process that result set to build your appropriate SELECT for
> the target table. So I suppose you could build a statement with a
> quantity of derived sets using IIF and WHERE EXISTS - the question is do
> you need to express this as single SELECT, or can you accomplish your
> goal either via your calling program or a stored procedure?
> --
> Daniel
>
> On 10/9/2017 1:36:20 AM, "Elmar Haneke el...@haneke.de
> [firebird-support]"  wrote:
>
> >
> >>Is there a way to include a column in a SELECT but substitute a value
> >>if
> >>it doesn't? I need my code to work with different versions of my db
> >>schema.
> >>
> >>eg I want to SELECT A, B, C, ... but C might not exist.
> >>
> >>SELECT * would work of course, except it will fetch a bunch of columns
> >>I
> >>don't need.
> >
> >You can read the list of fields available in advance and modify your
> >query ommitting missing columns.
> >
> >
> >Elmar
> >
> >
> >
> >
> >Posted by: Elmar Haneke 
> >
> >
> >++
> >
> >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
> >
> >
> >
>
> 
>

[firebird-support] Impossible to create a new database from a script

['Walter R. Ojeda Valiente' sistemas2000profesio...@gmail.com [firebird-support]]

Hello everybody

I am using Firebird 2.5.8 in Windows 7.

I have a script file which contains the lines for create a database and
theirs domains, tables, triggers, and so on.

The problem is in the lines of comments.

CREATE DATABASE '127.0.0.1/3050:E
:\SQL\SQL_MUAN\DATABASES\MUAN_MASTER_21.FDB'
  USER 'SYSDBA'
  PASSWORD 'masterkey'
  PAGE_SIZE = 4096
  DEFAULT CHARACTER SET ISO8859_1
  COLLATION ES_ES;

SET AUTODDL ON;

-- More lines here

COMMENT ON COLUMN ADICIONALES.ADI_SERVID IS 'Servidor Nº';

-- More lines here


The database is created, no problem there. But on the line: COMMENT ON
COLUMN the folowing error appears:

*This operation is not defined for system tables.Unsuccessful metadata
update.*
*MODIFY RDB$RELATION_FIELDS failed.*
*Malformed string.*

Thinking that the problem would be the CHARACTER SET, I had changed it to
WIN1252, ASCII, NONE, and the same error continues. It is impossible to
create the database.

If I delete the lines which start with COMMENT ON COLUMN (there are a lot
of them, because each column of each table has a comment) then the database
can be created. But I don't want to delete the comments, it is a team's
work and people needs to read those comments.

So, there is a solution?

Is this a bug of Firebird?

Somebody knows how to solve this problem?

Thanks in advance.

Walter.



Libre
de virus. www.avast.com

<#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>