AW: AW: [firebird-support] Simply bad, new is not always better. FB3 and ODBC

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

Hello Mark,

 

within 2.5 we uses classic server (problematic superservers does’nt use more 
cores), since 3 we installed the superserver-version (which supports more cores 
too).

 

The isolation-level is given by the odbc-driver and should be not the problem. 
I have actually no idea, sometimes it works very well, then today I got the 
message “Again I must close the access to get the inserted records”. Not a 
win-win-situation. Just a stored-procedure-call, not who should firebird take 
to the border. We had test if the sp takes more time, access waits (fb 2.5). 
This test we could next week do with 3.0.

 

I would only more reliability.

 

Thanks.

 

 

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 12. Mai 2016 20:15
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Simply bad, new is not always better. FB3 
and ODBC

 

  

On 11-5-2016 16:04, 'Checkmail'   
check_m...@satron.de [firebird-support] 
wrote:
> I have already tested the new version of firebird, but I think it is not my
> part to understand everything how firebird works. MSSQL I can install in
> every version, except new functions everything works in a new version like
> before.

That is the difference between a multi-billion dollar company with 
dedicated teams of testers, and a small, underfunded, open source 
project. Welcome to the open-source world, where the community is 
expected to lift part of that burden.

And yes, you can have some expectation of quality, but all too often 
people only start to really test when the first official release is done.

> Yes, I have posted it in the odbc dev, but I got no answer. If MSACCESS call
> a stored procedure (which inserts records) and Access waits until this
> process ends, it can refresh and get the new data. But why works it in the
> most cases and sometimes not? Wrong thinking of me? I hope I can expect that
> firebird fundamental works like before, or isn't it? The new architecture
> should not have an effect of a stored procedure who inserts some records.

It depends. We can't possibly tell from the few details you have provided.

> That not all can be perfect have I realized, I have posted some issues how I
> see after migrating. Hundreds of tables and stored procedures I cannot test
> again and again, if there some more clients connected it should work how one
> client. The odbc-driver is the critical component, the old version works
> nearly perfect but with fb3 I can determine some problems. The new one works
> not good, the performance is bad (slowly compared with the old one) and the
> refresh works not in every case.

I strongly recommend creating automated tests. It removes the drudgery 
from testing, and it is great to flush out regressions.

> Thank you Thomas for the link, the post_event sounds like a bug in fb3. This
> can happen, but I cannot expect the unexpected and believe in firebird. You
> all make a great job, but I only will develop the database, not be a tester
> or develop and understand the whole firebird-system. A table should work
> like a table and the odbc-connection should not work worse like before. We
> uses the newest version of MSAccess and as it appears, the very old gemini
> odbc driver works better than the newest of firebird-development.
>
> How can I post a "rational" problem when it is random one? I test it, in my
> freetime too, will make a great job, but how can I win when even the simply
> things don’t work again? All other components (mfc, c++) works, expect the
> post_event. Yes, we are using the fbclient.dll from the fb3.0.
>
> I already read the documentation, but once more, It works mostly, but there
> are some things how works unreliable. And I have brought a helping hand -
> fb-support. The actual complications could I not see by testing without the
> real environment by using a few connections. Mostly it works good, but how I
> said, not reliable.

If it happens with a lot of connections, it sounds more like a race 
condition (either in Firebird or in your software), or possibly wrong 
use of transaction isolation levels.

What Firebird model did you use with 2.5, and what model do you use now 
(SuperServer, SuperClassic or Classic)?

Mark
-- 
Mark Rotteveel

AW: AW: [firebird-support] Simply bad, new is not always better. FB3 and ODBC

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

Hello Mark,

 

for some firebird-problems – yes, one of them is fixed. About odbc I got no 
reaction.

 

We are using some stored procedures and tables, I thought, it is possible that 
fb 3 works every time correct how fb 2.1 and my tests without many clients ends 
great..

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 12. Mai 2016 09:21
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Simply bad, new is not always better. FB3 
and ODBC

 

  

On 2016-05-12 7:37, 'Checkmail'   
check_m...@satron.de [firebird-support] 
wrote:
> Hello again,
>
> the most funkctionality are very simply, just tables who linked to 
> msaccess.
> I thought that firebird in every version can do this without any 
> problems -
> how I said is this not in every case true. Also the stored procedures 
> -
> simplel sql - can do something and should work in fb 3 too. But the 
> main
> problem is the odbc driver, the old works not perfect again and the 
> new has
> performance- and refresh-problems.

Have you already created tickets for these problems with in-depth 
description of the problem and the steps necessary to reproduce them?

Mark

AW: [firebird-support] Simply bad, new is not always better. FB3 and ODBC

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

Hello again,

the most funkctionality are very simply, just tables who linked to msaccess.
I thought that firebird in every version can do this without any problems -
how I said is this not in every case true. Also the stored procedures -
simplel sql - can do something and should work in fb 3 too. But the main
problem is the odbc driver, the old works not perfect again and the new has
performance- and refresh-problems. 

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 11. Mai 2016 00:35
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Simply bad, new is not always better. FB3
and ODBC

Hello 'Checkmail',

Wednesday, May 11, 2016, 2:03:37 AM, you wrote:

> We had FB 2.5 CS before our migration to fb 3 SS. After this, the 
> Frontend (MS-ACCESS) calls a stored procedure and sometimes the 
> odbc-driver does not wait until the procedure finished and writes the 
> new records.

Impossible.  The driver does not "write records".  It simply processes a DML
request and passes that through the API to the server.  And no Firebird
server can make the results of an operation in a stored procedure available
to other transactions until the transaction in which the SP is running
commits.  So whatever is happening here, it is not what you think it is.

> In this case the user must close the
> access-formular to get the new data, if he pushed the button again, 
> there are two new records present. Some other complications I have 
> posted, two in the tracker.

Ummmso you are telling us that you upgraded a production system from Fb
2.5 to Fb 3 without reading the release notes or testing anything.

> Not fine, we have now some problems we never had and the customer does 
> not pad for the solutions.

Developer screws up and customer won't pay to fix it.  How sad...

> Now I have de-installed the firebird odbc 2.0.4 Driver and install the 
> 2.0.0.150, the problem with the refresh after calling 
> adodb-stored-procedure we get a little less, but if I connect to the 
> odbc-tables again (tables are linked) I get:

> Odbc firebird driver firebird attempt to reclose a closed cursor #-501
> Without any changes we   have now some problems and I’m frustrated!

First, this is not the right list for your ODBC problems.  Subscribe to the
firebird-odbc-devel list and try to post a *rational* problem description
there.

You switched a production system to a new major version of the database
server without comprehending what you were doing and without testing whether
it was going to work.  "Without any changes" is not the operative expression
here.

> There are no one in this community who uses odbc and firebird 3.0?

Ask that question in firebird-odbc-devel.  That is where you will find those
people who have been testing the driver against Fb 3.0.

> All the good features from the firebird 2.1 (we updated to 2.5 with 
> similar problems),

-- using the same upgrade strategy?

> where are they? We don’t can close our eyes and leave the new 
> versions, but we would like to work, just work!

So - you want to take advantage of new features and new architecture but you
don't want to make any effort to understand, test and control the changes?
Sounds heavenly to me. ;-)

> Also the post_event behaves different than the old firebird, the never 
> changed mfc (c++) application gets it sometimes, in many cases not. 
> Only the version of firebird is changed.

Post_event was not changed in Firebird 3.  Are you sure that your customer's
applications are loading the correct version of fbclient.dll?  This question
is applicable to your other problems, too.

> I’m stupid or what can I do to solve?

Change control - FAIL.  "Eine rechtzeitige Stich spart neun Stiche."

To appease your unhappy customer, restore the production system to what was
working before. [This won't "just work", either, if your customer has
already been posting production data into an ODS 12 database!]

Read the documentation (Release notes and Quick Start Guide).  Then read
them again as many times as it takes to understand the new architecture and
the other major changes.

Set up a test system for Firebird 3 and establish what the issues are for
your software **by testing everything**.

Don't throw a major upgrade at your customer until you have solved all the
issues.

Helen









++

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

AW: [firebird-support] Simply bad, new is not always better. FB3 and ODBC

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

Hello Helen,

sorry for my phrase in English, I cannot describe the problems very perfect.

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 11. Mai 2016 00:35
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Simply bad, new is not always better. FB3
and ODBC

>mpossible.  The driver does not "write records".  It simply processes a DML
request and passes that through the API to the server.  And no Firebird
server can make the >results of an operation in a stored procedure available
to other transactions until the transaction in which the SP is running
commits.  So whatever is happening here, it is >not what you think it is.

I have already tested the new version of firebird, but I think it is not my
part to understand everything how firebird works. MSSQL I can install in
every version, except new functions everything works in a new version like
before. Unfortunately I cannot test everything like in the production
environment, the customer can work but I cannot understand why it works in
some cases and in the same situation just not. To test this is very
complicated.

Yes, I have posted it in the odbc dev, but I got no answer. If MSACCESS call
a stored procedure (which inserts records) and Access waits until this
process ends, it can refresh and get the new data. But why works it in the
most cases and sometimes not? Wrong thinking of me? I hope I can expect that
firebird fundamental works like before, or isn't it? The new architecture
should not have an effect of a stored procedure who inserts some records.

That not all can be perfect have I realized, I have posted some issues how I
see after migrating. Hundreds of tables and stored procedures I cannot test
again and again, if there some more clients connected it should work how one
client. The odbc-driver is the critical component, the old version works
nearly perfect but with fb3 I can determine some problems. The new one works
not good, the performance is bad (slowly compared with the old one) and the
refresh works not in every case.

Thank you Thomas for the link, the post_event sounds like a bug in fb3. This
can happen, but I cannot expect the unexpected and believe in firebird. You
all make a great job, but I only will develop the database, not be a tester
or develop and understand the whole firebird-system. A table should work
like a table and the odbc-connection should not work worse like before. We
uses the newest version of MSAccess and as it appears, the very old gemini
odbc driver works better than the newest of firebird-development.

How can I post a "rational" problem when it is random one? I test it, in my
freetime too, will make a great job, but how can I win when even the simply
things don’t work again? All other components (mfc, c++) works, expect the
post_event. Yes, we are using the fbclient.dll from the fb3.0.

I already read the documentation, but once more, It works mostly, but there
are some things how works unreliable. And I have brought a helping hand -
fb-support. The actual complications could I not see by testing without the
real environment by using a few connections. Mostly it works good, but how I
said, not reliable.

Helen, thank you for your support.

[firebird-support] Simply bad, new is not always better. FB3 and ODBC

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

Hello @ll.

 

We had FB 2.5 CS before our migration to fb 3 SS. After this, the Frontend
(MS-ACCESS) calls a stored procedure and sometimes the odbc-driver does not
wait until the procedure finished and writes the new records. In this case
the user must close the access-formular to get the new data, if he pushed
the button again, there are two new records present. Some other
complications I have posted, two in the tracker. Not fine, we have now some
problems we never had and the customer does not pad for the solutions.

 

Now I have de-installed the firebird odbc 2.0.4 Driver and install the
2.0.0.150, the problem with the refresh after calling adodb-stored-procedure
we get a little less, but if I connect to the odbc-tables again (tables are
linked) I get:

 

Odbc firebird driver firebird attempt to reclose a closed cursor #-501

 

Without any changes we have now some problems and I'm frustrated!

 

There are no one in this community who uses odbc and firebird 3.0? All the
good features from the firebird 2.1 (we updated to 2.5 with similar
problems), where are they? We don't can close our eyes and leave the new
versions, but we would like to work, just work! 

 

Also the post_event behaves different than the old firebird, the never
changed mfc (c++) application gets it sometimes, in many cases not. Only the
version of firebird is changed. I'm stupid or what can I do to solve?

 

Many thanks.

 

Best regards

 

Olaf

 

AW: AW: [firebird-support] How to interprete gstat-results

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

Hi Thomas, I will study this :)

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 14. April 2016 16:12
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] How to interprete gstat-results

 

  

Hi Olaf,

> Database "D:\INTERBASE\IFLS.GDB"
> 
> Database header page information:
> 
> Flags 0
> 
> Generation 2939162
> 
> System Change Number 0
> 
> Page size 4096

You might consider increasing to 8K, especially when the tree depth of an
index starts to become > 3. To be checked with gstat -i.

> ODS version 12.0

Indeed, the database was created with Firebird 3. ;-)

> Oldest transaction 2893677

The "oldest interesting" transaction (OIT) in a state other than committed,
e.g. also rolled back. There are no record versions for transactions <
2893677 available (cause they have been garbage collected), thus one could
say why the marker is called "interesting".

> Oldest active 2929833

The oldest transaction currently active/running. This is the lowest
transaction ID which should be visible in MON$TRANSACTIONS.

> Oldest snapshot 2878316

This is the oldest transaction started in snapshot mode. Hope I remembered
that correctly.

> Next transaction 2938824

Transaction id for the next new transaction.

> Sequence number 0
> 
> Next attachment ID 87910
> 
> Implementation HW=Intel/i386 little-endian OS=Windows
> CC=MSVC
> 
> Shadow count 0
> 
> Page buffers 0

The default value in firebird.conf is used for the size of the page cache of
the database. Might be too small, depending on your use case.

> Next header page 0
> 
> Database dialect 3
> 
> Creation date Mar 7, 2016 18:33:37
> 
> Attributes force write
> 
> 
> 
> Variable header data:
> 
> Sweep interval: 5000

That's not a good idea. Because if the difference of OIT and OST (oldest
snapshot) reaches that threshold, a sweep will kick in, visits all records
in the database and removes out-dated record versions which are not
interesting for other transactions anymore.

Regarding OIT/OAT movement/gaps etc., commit retaining is one of your #1
enemies. Usually used behind the scene by drivers with auto commit = ON.

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

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

> 
> *END*
> 
> 
> 
> 
> 
> 
> 
> Von: firebird-support@yahoogroups.com
 
> [mailto:firebird-support@yahoogroups.com] 
> Gesendet: Donnerstag, 14. April 2016 14:03
> An:  
firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] How to interprete gstat-results
> 
> 
> 
> 
> 
>> Hello @ll,
>> 
>> 
>> 
>> we are using firebird 3.0 RC2 and have some questions about open
>> transactions. (Clients connect over odbc) The gstat output gives 7
>> transactions between the oldest and the current transaction, if we look
> into
>> the monitoring table, we see only 10 transactions. The "oldest one" we
>> cannot find in this table. What is the oldest transaction and which roll
>> does the garbage collector play? If we have many open transactions, the
>> performance is bad. What role plays the attachments? What is the best way
> to
>> check the performance bottleneck in such cases?
> 
> Please post an entire gstat -h output.
> 
> --
> With regards,
> Thomas Steinmaurer
> <  http://www.upscene.com>
 http://www.upscene.com
> 
> Professional Tools and Services for Firebird
> FB TraceManager, IB LogManager, Database Health Check, Tuning etc.
> 
> 
> 
> 

[firebird-support] Update with group by

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

Hello,

 

each record gets a barcode number (generated with a generator), everything
is fine. But if the Position Number is the same, both records should get the
same value. How can I realize this?

 

Update table a set field = gen_id(xxx,1) where field is not null group by
pos?

 

Thanks.

 

 

AW: [firebird-support] How to interprete gstat-results

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

Hello Thomas..

 

 

Database "D:\INTERBASE\IFLS.GDB"

Database header page information:

Flags   0

Generation  2939162

System Change Number0

Page size   4096

ODS version 12.0

Oldest transaction  2893677

Oldest active   2929833

Oldest snapshot 2878316

Next transaction2938824

Sequence number 0

Next attachment ID  87910

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

Shadow count0

Page buffers0

Next header page0

Database dialect3

Creation date   Mar 7, 2016 18:33:37

Attributes  force write

 

Variable header data:

Sweep interval: 5000

*END*

 

 

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 14. April 2016 14:03
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] How to interprete gstat-results

 

  

> Hello @ll,
> 
> 
> 
> we are using firebird 3.0 RC2 and have some questions about open
> transactions. (Clients connect over odbc) The gstat output gives 7
> transactions between the oldest and the current transaction, if we look
into
> the monitoring table, we see only 10 transactions. The "oldest one" we
> cannot find in this table. What is the oldest transaction and which roll
> does the garbage collector play? If we have many open transactions, the
> performance is bad. What role plays the attachments? What is the best way
to
> check the performance bottleneck in such cases?

Please post an entire gstat -h output.

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

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

[firebird-support] How to interprete gstat-results

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

Hello @ll,

 

we are using firebird 3.0 RC2 and have some questions about open
transactions. (Clients connect over odbc) The gstat output gives 7
transactions between the oldest and the current transaction, if we look into
the monitoring table, we see only 10 transactions. The "oldest one" we
cannot find in this table. What is the oldest transaction and which roll
does the garbage collector play? If we have many open transactions, the
performance is bad. What role plays the attachments? What is the best way to
check the performance bottleneck in such cases?

 

Thank you

 

Regards

 

Olaf

 

 

[firebird-support] monitoring Table

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

Hello,

 

I've a problem with our database, firebird 3.0 RC2, some Clients (C++,
Microsoft Access over Firebird ODBC). There are 7 open transactions. But
in the monitoring Table, I cannot see the one who is the oldest was shown.
Why?

 

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2834893, 85000, 1, '2016-04-13 14:34:31',
2834893, 2774214, 2771000, 2, 0, 0, 0, 1, 201);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2208191, 68114, 0, '2016-04-07 05:03:44',
2208191, 2768721, 2771000, 2, -1, 1, 0, 1, 319);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2833276, 83617, 1, '2016-04-13 07:00:29',
2771000, 2768721, 2769594, 2, -1, 0, 0, 1, 1267);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2834878, 84239, 1, '2016-04-13 10:38:25',
2792941, 2768721, 2771000, 2, -1, 0, 0, 1, 1677);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2834800, 84681, 1, '2016-04-13 14:21:39',
2832348, 2768721, 2771000, 2, -1, 0, 0, 1, 1933);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2833230, 84857, 1, '2016-04-13 14:25:23',
2833010, 2768721, 2771000, 2, -1, 0, 0, 1, 2196);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2829258, 84866, 1, '2016-04-13 14:06:50',
2829258, 2768721, 2771000, 2, -1, 0, 0, 1, 2293);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2834822, 84891, 1, '2016-04-13 14:13:14',
2829911, 2768721, 2771000, 2, -1, 0, 0, 1, 2358);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2833355, 84977, 1, '2016-04-13 14:27:03',
2833355, 2768721, 2771000, 2, -1, 0, 0, 1, 2450);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2833491, 84980, 1, '2016-04-13 14:28:18',
2833491, 2768721, 2771000, 2, -1, 0, 0, 1, 2527);

INSERT INTO MON$TRANSACTIONS (MON$TRANSACTION_ID, MON$ATTACHMENT_ID,
MON$STATE, MON$TIMESTAMP, MON$TOP_TRANSACTION, MON$OLDEST_TRANSACTION,
MON$OLDEST_ACTIVE, MON$ISOLATION_MODE, MON$LOCK_TIMEOUT, MON$READ_ONLY,
MON$AUTO_COMMIT, MON$AUTO_UNDO, MON$STAT_ID)

  VALUES (2834880, 85006, 1, '2016-04-13 14:34:02',
2834880, 2774214, 2771000, 2, -1, 0, 0, 1, 2664);

 

COMMIT WORK;

 

Thank you

 

Best regards

 

Olaf

 

 

AW: [firebird-support] String function extract last word from a string

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

Thanks, I had a more complicated version tested now

  for select arbeitsgang from tarbg into :agbefore do
  begin
agafter = reverse(agbefore);
agafter = left(agafter, position(' ',agafter));
agafter = reverse(agafter);
suspend;
  end

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 12. April 2016 17:08
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] String function extract last word from a
string

12.04.2016 17:00, 'Checkmail' check_m...@satron.de [firebird-support] wrote:
> is it possible to extract the last complete word from a string until 
> the last blank? For example “I would like to eat a bacon” In this case I
would like to have the “bacon” as result.

   RIGHT(string, POSITION(' ' in REVERSE(string)))

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

[firebird-support] String function extract last word from a string

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

Hello,

 

is it possible to extract the last complete word from a string until the
last blank? For example "I would like to eat a bacon" In this case I would
like to have the "bacon" as result.

 

Thank you.

 

 

AW: [firebird-support] install from zip Firebird-3.0.0.32383-0_Win32

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

Ibexpert told me, this gds32 is the 64 Bit Version of dll?

 

In the program list (windows) I can see only the old fb version rc2. I need
to update.

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 23. März 2016 14:12
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] install from zip Firebird-3.0.0.32383-0_Win32

 

  

Hello,

 

I have installed the fb 3 rc2 before and now I would update to 3.0.0.32383.
I have deactivated the fb service and copied all content to the firebird
install directory – replace. After this, start the install_service.bat and
instclient.exe with copy fbclient and gds32 to the syswow64. 

 

Fbclient.dll works, but I cannot connect with gds32.dll “username and
passwort not set, ask database administrator..” What is wrong? 

 

Thank you.

 

Regards

 

Olaf

 

 

[firebird-support] install from zip Firebird-3.0.0.32383-0_Win32

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

Hello,

 

I have installed the fb 3 rc2 before and now I would update to 3.0.0.32383.
I have deactivated the fb service and copied all content to the firebird
install directory - replace. After this, start the install_service.bat and
instclient.exe with copy fbclient and gds32 to the syswow64. 

 

Fbclient.dll works, but I cannot connect with gds32.dll "username and
passwort not set, ask database administrator.." What is wrong? 

 

Thank you.

 

Regards

 

Olaf

 

 

[firebird-support] request synchronisation error

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

Hello,

 

we traced to find performance problems and we saw some messages output for
example the following:

 

2016-03-17T09:01:52.5030 (2544:0C661670) ERROR AT JRequest::receive

 D:\INTERBASE\IFLS.GDB (ATT_21976, SYSDBA:NONE, NONE,
TCPv4:192.168.0.70/50182)

 C:\WINDOWS\system32\gbak.exe:2340

335544364 : request synchronization error

 

 

 

2016-03-17T09:02:06.5480 (2544:0C663060) ERROR AT JResultSet::fetchNext

 D:\INTERBASE\IFLS.GDB (ATT_21539, SYSDBA:NONE, ISO88591,
TCPv4:192.168.0.165/52623)

 \\fls\AP\FAP.exe:4296

335544364 : request synchronization error

 

Is this only the trace or a general problem with the database?

 

Thanks

 

[firebird-support] Events, any changing in fb 3 RC2?

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

Hello,

 

since FB 3 RC2 our C++ Application gets only the first post_event, some
following there are not notice. Now we have insert a timer (sleep(500) after
initevent) and it works (at the moment).

 

Many Thanks

 

 

AW: [firebird-support] Firebird lock conflict

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

Hello again,

 

there are some problems which are present since the migration of firebird
3.0 RC2. Two of them I have reportet, does anyone know where now some
records locked and ibexpert can change them? 

 

thanks

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 14. März 2016 12:33
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Firebird lock conflict

 

  

Hello,

 

fb3 rc2, newest odbc driver.. If I have to change some records, it works
fine with ibexpert, but with odbc connection (linked tables) I get for he
same records an lock conflict. No problem with the old version 2.5 or 2.1.

 

Do you know which time firebird 3 final will realeased?

 

Thanks

 

 

[firebird-support] Firebird lock conflict

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

Hello,

 

fb3 rc2, newest odbc driver.. If I have to change some records, it works
fine with ibexpert, but with odbc connection (linked tables) I get for he
same records an lock conflict. No problem with the old version 2.5 or 2.1.

 

Do you know which time firebird 3 final will realeased?

 

Thanks

 

 

AW: Re: [firebird-support] timestamp of the firebird database (modified date) does not change in fb 3.0 RC2

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

Hi,

 

my third ticked within three days. What should I do, windows ticket? E-Mail an 
microsoft? ;)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 11. März 2016 13:26
An: firebird-support@yahoogroups.com
Betreff: Re: Re: [firebird-support] timestamp of the firebird database 
(modified date) does not change in fb 3.0 RC2

 

  

> 11.03.2016 12:59, liviuslivius liviusliv...@poczta.onet.pl 
>   [firebird-support] wrote:
> > i can confirm this - database modified datatime is frozen when first 
> > connection has started.
> > i suppose that you should raport ticket into the firebird tracker.
> 
> This ticket rather belongs to Windows tracker.
> 
> -- 
> WBR, SD.
> 

Hi,

you have right ;-)

regards,
Karol Bieniaszewski

[firebird-support] timestamp of the firebird database (modified date) does not change in fb 3.0 RC2

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

Hello,

 

since 3 days during production the modified date does not chenge, is there a
reason why? Its the right database.

 

Thanks.

 

 

AW: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

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

Hello,

 

I have created an example:

 

SET SQL DIALECT 3;

 

CREATE TABLE T_MAIN (

TNR   VARCHAR(16) NOT NULL,

MINB  INTEGER

);

 

ALTER TABLE T_MAIN ADD CONSTRAINT PK_T_MAIN PRIMARY KEY (TNR);

 

SET SQL DIALECT 3;

 

 

CREATE TABLE T_STOCK (

TNR VARCHAR(16),

AMOUNT  INTEGER

);

 

 

ALTER TABLE T_STOCK ADD CONSTRAINT FK_T_STOCK_1 FOREIGN KEY (TNR) REFERENCES 
T_MAIN (TNR) ON DELETE CASCADE ON UPDATE CASCADE;

 

 

Redords:

 

INSERT INTO T_MAIN (TNR, MINB)

VALUES ('AAA', 0);

INSERT INTO T_MAIN (TNR, MINB)

VALUES ('BBB', 10);

INSERT INTO T_MAIN (TNR, MINB)

VALUES ('CCC', 10);

INSERT INTO T_MAIN (TNR, MINB)

VALUES ('DDD', 10);

 

COMMIT WORK;

 

INSERT INTO T_STOCK (TNR, AMOUNT)

 VALUES ('AAA', 100);

INSERT INTO T_STOCK (TNR, AMOUNT)

 VALUES ('BBB', 5);

INSERT INTO T_STOCK (TNR, AMOUNT)

 VALUES ('CCC', 15);

 

COMMIT WORK;

 

The sql query

 

SELECT

a.tnr, a.minb, coalesce(b.amount,0) as m

FROM

t_main a

left join t_stock b on (a.tnr = b.tnr)

WHERE

((a.minb > coalesce(b.amount,0)))

ORDER BY

a.tnr

 

_-

 

I get ccc too. Why? Allthough I have a stock from 15.

 

If I delete the foreign key from table t_stock, CCC does not display! But with 
this foreign key it does not work, I get CCC but CCC has a stock greater the 
minimum amount (minb) and should not be displayed (like in firebird 2.5, 2.1, 
2.0)

 

Thanks

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 9. März 2016 13:25
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

Hello,

> your are right. But in FB 3, it will be handled different.
> I need the left join tlager_sum, because I’d like to have all records too 
> there no records in tlager_sum.
> But in this case I get now in fb 3 the tteile.teilenr clean without the 
> amaount of tlager_sum (the a.minb is greater than the coalesce(b.menge,0) 
> and in this case, I get no referenced Record from tlager_sum), the one 
> from tteile now in every case (left join).
> Finally, I would get all records with a.minb > b.menge and all records, 
> where a.minb is present an there is no record from the teilenr in 
> tlager_sum.

Please, give some DDL with example data.
I've tried to reproduce your issue on FB3.0 RC2, but i could not reproduce 
it.

Kind Regards,
Arno Brinkman

[firebird-support] AW: different behavior firebird 3.0 RC2

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

One hint,

 

the trigger is required because the user can delete the one record, in this
case the trigger deletes the referenced too. But if I delete the entire
Order, the record will be delete twice. How can I solve this in fb 3?

 

Von: Checkmail [mailto:check_m...@satron.de] 
Gesendet: Mittwoch, 9. März 2016 11:53
An: 'firebird-support@yahoogroups.com' (firebird-support@yahoogroups.com)

Betreff: different behavior firebird 3.0 RC2

 

Hello again,

 

now I have in FB3 a problem. A stored Procedure should delete some records.

 

ID some fields  ID_AUTOINSERT

123xxx null

234   xxx null

255   xxx 266

266   xxx null

 

Now there is a trigger after delete in this table who deletes:

 if(old.ap = 69) then

  begin

delete from tplan_kw_pos where id_autoinsert = old.id;

  end

 

In this case, FB will delete the record with ID 266 during deleting ID 255
and the other statement intends to delete the record 266 too.

 

Now in FB 3 RC 2 I get this error message:

 

can't format message 13:492 -- message file C:\WINDOWS\SYSTEM32\firebird.msg
not found.

no current record for fetch operation.

 

In FB 2.55, FB2.1 this was not a problem.

 

How can I fix?

 

Thanks

 

[firebird-support] different behavior firebird 3.0 RC2

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

Hello again,

 

now I have in FB3 a problem. A stored Procedure should delete some records.

 

ID some fields  ID_AUTOINSERT

123xxx null

234   xxx null

255   xxx 266

266   xxx null

 

Now there is a trigger after delete in this table who deletes:

 if(old.ap = 69) then

  begin

delete from tplan_kw_pos where id_autoinsert = old.id;

  end

 

In this case, FB will delete the record with ID 266 during deleting ID 255
and the other statement intends to delete the record 266 too.

 

Now in FB 3 RC 2 I get this error message:

 

can't format message 13:492 -- message file C:\WINDOWS\SYSTEM32\firebird.msg
not found.

no current record for fetch operation.

 

In FB 2.55, FB2.1 this was not a problem.

 

How can I fix?

 

Thanks

 

AW: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

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

Hello,

 

your are right. But in FB 3, it will be handled different.

 

I need the left join tlager_sum, because I’d like to have all records too there 
no records in tlager_sum. But in this case I get now in fb 3 the tteile.teilenr 
clean without the amaount of tlager_sum (the a.minb is greater than the 
coalesce(b.menge,0) and in this case, I get no referenced Record from 
tlager_sum), the one from tteile now in every case (left join).

 

Finally, I would get all records with a.minb > b.menge and all records, where 
a.minb is present an there is no record from the teilenr in tlager_sum.

 

Thanks

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 9. März 2016 09:04
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

Hello,

> sorry, outlook corrects me in German. At the moment, I have no sulution 
> for the problem. Anything ideas?

Query:

SELECT
a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ
FROM
tteile a
left join tlager_sum b on (a.teilenr = b.teilenr)
WHERE
((a.minb > coalesce(b.menge,0)) and (a.typ = 1))
ORDER BY
a.teilenr

Result:

TEILENR BEZEICHNUNG MINB M TYP
PrSt110x44x3 Profilrohr E235,S2 110x44x3,0 mm gebeizt 5600mm 280 
420 1

I would say your TLAGER_SUM contains a record with:

TEILENR MENGE
PrSt110x44x3 420

So results are correct?

If not give sample records for tteile and tlager_sum with DDL

Kind Regards,
Arno Brinkman
ABVisie

AW: [firebird-support] coalesce bug in fb 3.0 rc2?

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

Hello again,

 

sorry, outlook corrects me in German. At the moment, I have no sulution for the 
problem. Anything ideas?

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:39
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

Hell Mark,

 

I would get all material, where the minimum inventory amount iss maller the the 
inventory amount. 

 

Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum 
amount it should be.

 

Now, the simplified query gets all records where the amount is to small and 
with the „where a.minb > coalesce(b.menge)) I get also the records, where is no 
inventory amount present.

 

If I let the code:

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

I get m = 0 for records, who has a inventory amount greater than the minimal 
inventory amount. If I change the

 

where ((a.minb > coalesce(b.menge,0))

 

to 

 

where ((a.minb > b.menge)..

 

the records who has a greater inventory amount than the minimal one will be not 
displayed, but also not these who has no inventory amount (null in tlager_sum)

 

In firebird 2.5 and earlier versions it works fine in both situations.

 

The result of the unchanged code:

 

TEILENRBEZEICHNUNGMINBMTYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   0  
 1

 

Inventory amount = 0

 

The result without coalesce:

 

The record is no present

 

Without the condition >..

 

TEILENRBEZEICHNUNGMINB  M   TYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   
420   1

 

I get 420 as inventory amount, 280 should it be as minimal inventory amount, 
aka minb

 

What can be the problem?

 

Von: firebird-support@yahoogroups.com  
 [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:26
An: firebird-support@yahoogroups.com  
Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de 
  
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and 
> get
> the real value of m (exists in tlager_sum)
>
> select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m,
> a.typ
>
> from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)
>
> where ((a.minb > coalesce(b.menge,0))
>
> and (a.typ = 1)) order by a.teilenr
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
> a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?

It is not clear to me what your problem is. Could you describe it in 
more detail and provide a sample dataset, expected results and actual 
results?

Mark

AW: [firebird-support] coalesce bug in fb 3.0 rc2?

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

Hell Mark,

 

I would get all material, where the minimum inventory amount iss maller the the 
inventory amount. 

 

Tlager_sum contains the inventory amount, tteile.minb the theoretical minimum 
amount it should be.

 

Now, the simplified query gets all records where the amount is to small and 
with the „where a.minb > coalesce(b.menge)) I get also the records, where is no 
inventory amount present.

 

If I let the code:

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

I get m = 0 for records, who has a inventory amount greater than the minimal 
inventory amount. If I change the

 

where ((a.minb > coalesce(b.menge,0))

 

to 

 

where ((a.minb > b.menge)..

 

the records who has a greater inventory amount than the minimal one will be not 
displayed, but also not these who has no inventory amount (null in tlager_sum)

 

In firebird 2.5 and earlier versions it works fine in both situations.

 

The result of the unchanged code:

 

TEILENRBEZEICHNUNGMINBMTYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   0  
 1

 

Inventory amount = 0

 

The result without coalesce:

 

The record is no present

 

Without the condition >..

 

TEILENRBEZEICHNUNGMINB  M   TYP

PrSt110x44x3   Profilrohr E235,S2 110x44x3,0 mm gebeizt  5600mm280   
420   1

 

I get 420 as inventory amount, 280 should it be as minimal inventory amount, 
aka minb

 

What can be the problem?

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 8. März 2016 16:26
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] coalesce bug in fb 3.0 rc2?

 

  

On 2016-03-08 16:18, 'Checkmail' check_m...@satron.de 
  
[firebird-support] wrote:
> Hello,
>
> in the further version of firebird I can execute the following and 
> get
> the real value of m (exists in tlager_sum)
>
> select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m,
> a.typ
>
> from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)
>
> where ((a.minb > coalesce(b.menge,0))
>
> and (a.typ = 1)) order by a.teilenr
>
> In any case when it is no amount in the table tlager_sum (null), it
> should seen as 0 (not present) and if the minimum amount iss higher..
>
> But, since fb 3 I get 0, why? The tlager_sum has for many records a
> mass. If I change to
>
> a.minb > b.menge
>
> it works for these records, but not for any with no amount.
>
> What can be the issue?

It is not clear to me what your problem is. Could you describe it in 
more detail and provide a sample dataset, expected results and actual 
results?

Mark

[firebird-support] coalesce bug in fb 3.0 rc2?

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

Hello,

 

in the further version of firebird I can execute the following and get the
real value of m (exists in tlager_sum)

 

select a.teilenr, a.bezeichnung, a.minb, coalesce(b.menge,0) as m, a.typ

from tteile a left join tlager_sum b on (a.teilenr = b.teilenr)

   where ((a.minb > coalesce(b.menge,0))

  and (a.typ = 1)) order by a.teilenr

 

In any case when it is no amount in the table tlager_sum (null), it should
seen as 0 (not present) and if the minimum amount iss higher..

 

But, since fb 3 I get 0, why? The tlager_sum has for many records a mass. If
I change to 

 

   a.minb > b.menge 

 

it works for these records, but not for any with no amount.

 

What can be the issue?

 

Thanks

 

Best regards

 

Olaf

 

 

AW: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 ss

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

Many Thanks, Ann :)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Samstag, 5. März 2016 20:29
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 
ss

 

  

On Fri, Mar 4, 2016 at 3:22 AM, 'Checkmail' check_m...@satron.de 
  [firebird-support] 
 > 
wrote:

 

 

If I must go back tot he 2.5 version of firebird, how can I gbak the new 
backup-file to the old version?

 

If you haven't used V3 features in data definitions or the new authorization 
options, reverting is pretty easy.  Leave V3 installed for the backup.   Find a 
copy of gbak from a 2.5 distribution and use it to create a backup file.  
Install Firebird 2.5.x and use the 2.5.x gbak to restore the database.  If 
you've used 3.0 features including the various authorization options, you'll 
probably need to extract the database metadata, edit out references to new 
features and create a new V2.5.x database in a separate environment.  You'll 
need to have both versions of Firebird available simultaneously.  Use one of 
the database migration tools to copy the data from thoe V3 database to the V 
2.5  You can find migration tools here: 
http://ibphoenix.com/download/tools/migration

 

Good luck,

 

Ann

AW: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 ss

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

Ann, another question.

 

If I must go back tot he 5.5 version of firebird, how can I gbak the new 
backup-file to the old version?

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 3. März 2016 17:53
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 
ss

 

  

On Thu, Mar 3, 2016 at 11:16 AM, 'Checkmail' check_m...@satron.de 
  [firebird-support] 
 > 
wrote:

 

 

I’ve postet my problems with the new odbc driver and firebird 2.5 cs, the slow 
queries and all other issus. Now I have installed the firebird 3.0 rc2, the 
performance is great, superservers takes all cpu-cores and the problems now 
banned. I have not changed anything, is this a result of the superserver or the 
version 3 of firebird?

 

Both.  Firebird V3 introduces parallel execution of queries in SuperServer.  
SuperServer was always multi-threaded but only one thread could run at any one 
time even on a multi-processor server.  The benefit of threading in earlier 
versions was that queries could be interleaved, so a long report didn't block 
all other requests.  With V3, thread do run in parallel taking advantage of all 
the processing power of a multi-core machine.

 

Glad things are working better for you!

 

Good luck,

 

Ann 

 

AW: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 ss

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

Thank you, Ann :)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 3. März 2016 17:53
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] differences between firebird 2.5 cs and 3.0 rc2 
ss

 

  

On Thu, Mar 3, 2016 at 11:16 AM, 'Checkmail' check_m...@satron.de 
  [firebird-support] 
 > 
wrote:

 

 

I’ve postet my problems with the new odbc driver and firebird 2.5 cs, the slow 
queries and all other issus. Now I have installed the firebird 3.0 rc2, the 
performance is great, superservers takes all cpu-cores and the problems now 
banned. I have not changed anything, is this a result of the superserver or the 
version 3 of firebird?

 

Both.  Firebird V3 introduces parallel execution of queries in SuperServer.  
SuperServer was always multi-threaded but only one thread could run at any one 
time even on a multi-processor server.  The benefit of threading in earlier 
versions was that queries could be interleaved, so a long report didn't block 
all other requests.  With V3, thread do run in parallel taking advantage of all 
the processing power of a multi-core machine.

 

Glad things are working better for you!

 

Good luck,

 

Ann 

 

[firebird-support] differences between firebird 2.5 cs and 3.0 rc2 ss

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

Hello,

 

I've postet my problems with the new odbc driver and firebird 2.5 cs, the
slow queries and all other issus. Now I have installed the firebird 3.0 rc2,
the performance is great, superservers takes all cpu-cores and the problems
now banned. I have not changed anything, is this a result of the superserver
or the version 3 of firebird?

 

Thanks

 

Olaf

 

 

AW: [firebird-support] Firebird odbc 2.0.0.150 vs 2.0.3.154

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

Hello,

Unfortunately until today I have no answer. What can I do? With the newest
ODBC-Driver I have this issue, the old RC2 not..

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 18. Februar 2016 10:36
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Firebird odbc 2.0.0.150 vs 2.0.3.154

 

  

Hello @all,

 

Unfortunately, I get not answer of my question on the odbc devel list,
that's why I tell ths one here again.

 

After I updated the ODBC form 2.0.0 to 2.0.3, the most queries in MS Access
are very slow. From 2 seconds bevore to 1 minute with the new one! I have
updated all linked tables in both cases, the new one ist very slow, why?

 

Thanks in advance.

 

Best regards.

 

Olaf

 

 

[firebird-support] Firebird odbc 2.0.0.150 vs 2.0.3.154

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

Hello @all,

 

Unfortunately, I get not answer of my question on the odbc devel list,
that's why I tell ths one here again.

 

After I updated the ODBC form 2.0.0 to 2.0.3, the most queries in MS Access
are very slow. From 2 seconds bevore to 1 minute with the new one! I have
updated all linked tables in both cases, the new one ist very slow, why?

 

Thanks in advance.

 

Best regards.

 

Olaf

 

 

AW: [firebird-support] Problem Firebird 2.5

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

Thanks Paul, I have found the issue :)

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 17. Februar 2016 15:38
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Problem Firebird 2.5

 

  


<>

Usually caused (AFAIK) by a restore done with Firebird 2.1 or an earlier
gbak, or if a Firebird 2.5 backup was
restored to Firebird 2.1, backed up and then restored on Firebird 2.5

On the original database you can do

DROP ROLE RDB$ADMIN;
COMMIT;

When you restore, gbak.exe should recreate the RDB$ADMIN role.

Paul

AW: [firebird-support] Problem Firebird 2.5

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

Oh shit, thank our so much! There was an old gbak in the system32 directory
which were the task planer has make a backup.

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 17. Februar 2016 15:25
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Problem Firebird 2.5

17.02.2016 15:14, 'Checkmail' check_m...@satron.de [firebird-support] wrote:
> .it is not the gbak from firebird 2.1

   The error shows that the backup was created with gbak 2.1 but from server
2.5. Nothing you can do but create database from scratch and pump data.

> Now, gfix shows me „single user maintenance“ in attributes
>
> I have no access to the database

   You can bring the database online with gfix to pump data from it.

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

AW: [firebird-support] Problem Firebird 2.5

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

.it is not the gbak from firebird 2.1 and I'm using the -user sysdba -pas
masterkey switches. What can I do?

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 17. Februar 2016 14:32
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Problem Firebird 2.5

  
Hello @ll,
 
during a restore of a firebird gbk on a fres installed system with the same
firebird 2.5 cs server like the original server, I get this error:
attemp to store duplicate value (visible to active transactions) inn unique
index „RDB$Index_39“ Problematic key value is („RDB$ROLE_NAME“ =
‚RDB$ADMIN‘)
 
Now, gfix shows me „single user maintenance“ in attributes
 
I have no access to the database, what goes wrong? 
 
Thank you
 

[firebird-support] Problem Firebird 2.5

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

Hello @ll,

 

during a restore of a firebird gbk on a fres installed system with the same
firebird 2.5 cs server like the original server, I get this error:

attemp to store duplicate value (visible to active transactions) inn unique
index "RDB$Index_39" Problematic key value is ("RDB$ROLE_NAME" =
,RDB$ADMIN')

 

Now, gfix shows me "single user maintenance" in attributes

 

I have no access to the database, what goes wrong? 

 

Thank you

 

AW: [firebird-support] Foreign Key on update and after update trigger

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

Okay, Thank you..

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 9. Februar 2016 13:16
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Foreign Key on update and after update trigger

 

  

 

No, it does not seem difficult, Olaf. Assuming that twarehouse_sum contains 
some summation of twarehouse, I'd say the reason is as simple as two separate 
transactions modifying twarehouse simultaneously in a way that makes the change 
of the other transaction invisible.

 

Generally, I'd say the idea of deleting and inserting into a summation table in 
a trigger on a detail table is something that works well only with maximum one 
concurrent user per row of the summation table. It simply contradicts 
concurrency!

 

What I'd rather recommend, is that the trigger always inserts into the 
summation table, but never deletes from it. If you insert a new record into 
twarehouse, the trigger should insert +1 into twarehouse_sum, if you delete a 
record from twqarehouse, the trigger should insert -1 into twarehouse_sum 
(change +1/-1 appropriately if the summation table contains sums rather than 
counts). Then you should have a separate process that at set times (e.g. once 
every hour, day or week), does something like:

 

for select DistinctField, sum(MySummationField) from twarehouse_sum group by 1

into :DistinctField, :MySum do

begin

  delete from twarehouse_sum where DistinctField = :DistinctField;

  insert into twarehouse_sum(DistinctField, SummationField)

  values(:DistinctField, :MySum);

end

 

That would require you to generally change all your

 

  select DistinctField, MySummationField

  from twarehouse_sum

 

to 

 

  select DistinctField, sum(MySummationField)

  from twarehouse_sum

  group by 1

 

whenever you want to find the sum, but it is scalable (i.e. a lot better for 
concurrency) and avoids the problem you've observed.

 

Set

AW: [firebird-support] Foreign Key on update and after update trigger

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

Hello,

 

is my question too complicated?

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 5. Februar 2016 12:27
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Foreign Key on update and after update trigger

 

  

Hello @ll,

 

we have a table tarbg (primary key teilenr and agnr) and two child tables
(twarehouse and twarehouse_sum), both with on update foreign keys to tarbg.
In the first child table twarehouse is located an after update trigger which
updates the second child table twarehouse_sum with the new value (deletes
the old and inserts the new data).

 

Now we have a problem with duplicated records in twarehouse_sum and we think
about, how this could be.

 

What about the execution order with the foreign key updates and the after
update trigger? We saw that the trigger executes only ones which as
expected, but what about the execution point? I guess, it is inline after
the table is updated but not after the child table is updated. So is the
behavior a kind of undefined which table is the first where updated of the
both child tables?

 

Do you know the execution order of the key updates and trigger updates, what
rules I have to follow to found behavior on this updates?

 

Best regards

 

Olaf

 

 

[firebird-support] trigger, changed code, old session

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

Hello,

 

I'm using Firebird 2.5 cs. Yesterday I had inserted a new column and changed
some Trigger-Code. Some of the clients connected before to the database. The
bevore insert trigger set the new column with a generator-value. How can I
change the behavior that the old connections don't uses this changed code
without a new connect? Some of the records has not set the new value even
though the code has changed and the new field should set in fact.

 

Thanks a lot

 

Best regards

 

Olaf

 

 

[firebird-support] curious problem after migration fb 2.1 to fb 2.5 cs

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

Hello @all,

 

the database server, fb 2.5.5 cs on Win 2008 Server 32 Bit, The Frontend MS
Access linked tables. Before the migration, it works all very well. With the
newest odbc driver I get performance Problems, therefore I have installed
the old driver again.

 

But also with this, there is a little problem.. Some Tables were linked in a
Access Query, one Field from a table is related (left joined) to the other
table. In this case, access does not found the reference in any time. Some
"teilenr" (example: 'F512' (this works), F512/1" works not, but 'F5122/1'
works again). If I set the relation to inner join, it works. But I don't
know why, an other Formular takes while open now some more time. I have
taken the old version and write the changes again, now it works the other
formular fine, like before. There are some changes in firebird who can
explain this behavior? 

 

Thank you.

 

Best regards.

 

Olaf

 

 

[firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86

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

Hello and good morning,

 

the situation, firebird classic server 2.1 before, firebird classic server
2.5 (newest) after. The frontend is Microsoft Access, the tables odbc
linked. Now, after the migration I open a Formular in Access, everything is
fast, in less then one second the Data were displayed. But if I open the
Formular the second time, I must wait 20 seconds. All Fields are indexed. 

 

The sql-code in Access

SELECT TBESTELLUNGEN.JAHR, TBESTELLUNGEN.NR, TBESTPOS.LIEFERTERMIN,
TBESTPOS.LIEFERZEIT, TBESTPOS.GELIEFERT, TTEILE.TEILENR, TTEILE.BEZEICHNUNG,
TKUNDEN.FIRMA, TBESTELLUNGEN.GELIEFERT AS komplett, TBESTPOS.ANZAHL,
TBESTPOS.GEWOGEN, TBESTPOS.BESTAETIGT, TBESTPOS.ABGESCHLOSSEN,
TBESTPOS.TATSLT,
IIf([einheitnr]=6,[tbestpos].[geliefert],[tbestpos].[geliefert]*[tteile].[ge
wicht]) AS gewicht, UCase([tteile].[teilenr]) AS TNRG

FROM TKUNDEN INNER JOIN (TTEILE INNER JOIN (TBESTPOS INNER JOIN
TBESTELLUNGEN ON (TBESTPOS.NR = TBESTELLUNGEN.NR) AND (TBESTPOS.JAHR =
TBESTELLUNGEN.JAHR)) ON TTEILE.TEILENR = TBESTPOS.TEILENR) ON
TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR

WHERE (((TBESTPOS.ABGESCHLOSSEN)=-1) AND
((TBESTPOS.TATSLT)>=[forms]![fbestelleingang].[sa] And
(TBESTPOS.TATSLT)<=[forms]![fbestelleingang].[sb]) AND
((UCase([tteile].[teilenr])) Like
nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")) AND
((TKUNDEN.KUNDENNR) Like nz([Formulare]![fbestelleingang]![sl],"*")))

ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;

 

It works fine with fb 2.1, but now.. What has been changed? The last odbc
driver is installed

 

Best regards

 

Olaf

 

 

AW: [firebird-support] Performance Problem after Migration fb 2.1 cs to fb 2.5 cs both X86

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

It looks like a problem with the odbc driver. The 2.0.0.150_32 works fine,
the 2.0.3 buggy.

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 20. Januar 2016 07:07
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Performance Problem after Migration fb 2.1 cs to
fb 2.5 cs both X86

 

  

Hello and good morning,

 

the situation, firebird classic server 2.1 before, firebird classic server
2.5 (newest) after. The frontend is Microsoft Access, the tables odbc
linked. Now, after the migration I open a Formular in Access, everything is
fast, in less then one second the Data were displayed. But if I open the
Formular the second time, I must wait 20 seconds. All Fields are indexed. 

 

The sql-code in Access

SELECT TBESTELLUNGEN.JAHR, TBESTELLUNGEN.NR, TBESTPOS.LIEFERTERMIN,
TBESTPOS.LIEFERZEIT, TBESTPOS.GELIEFERT, TTEILE.TEILENR, TTEILE.BEZEICHNUNG,
TKUNDEN.FIRMA, TBESTELLUNGEN.GELIEFERT AS komplett, TBESTPOS.ANZAHL,
TBESTPOS.GEWOGEN, TBESTPOS.BESTAETIGT, TBESTPOS.ABGESCHLOSSEN,
TBESTPOS.TATSLT,
IIf([einheitnr]=6,[tbestpos].[geliefert],[tbestpos].[geliefert]*[tteile].[ge
wicht]) AS gewicht, UCase([tteile].[teilenr]) AS TNRG

FROM TKUNDEN INNER JOIN (TTEILE INNER JOIN (TBESTPOS INNER JOIN
TBESTELLUNGEN ON (TBESTPOS.NR = TBESTELLUNGEN.NR) AND (TBESTPOS.JAHR =
TBESTELLUNGEN.JAHR)) ON TTEILE.TEILENR = TBESTPOS.TEILENR) ON
TKUNDEN.KUNDENNR = TBESTELLUNGEN.LIEFERANTNR

WHERE (((TBESTPOS.ABGESCHLOSSEN)=-1) AND
((TBESTPOS.TATSLT)>=[forms]![fbestelleingang].[sa] And
(TBESTPOS.TATSLT)<=[forms]![fbestelleingang].[sb]) AND
((UCase([tteile].[teilenr])) Like
nz(UCase([Formulare]![fbestelleingang]![st]) & "*","*")) AND
((TKUNDEN.KUNDENNR) Like nz([Formulare]![fbestelleingang]![sl],"*")))

ORDER BY TBESTPOS.LIEFERTERMIN DESC , TBESTPOS.LIEFERZEIT DESC;

 

It works fine with fb 2.1, but now.. What has been changed? The last odbc
driver is installed

 

Best regards

 

Olaf

 

 

[firebird-support] Architecture

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

Hello,

 

I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to Firebird
2.5. Is there a 64 Bit OS recommend and a 64 Bit installation of Firebird
too? I connect with odbc. Known Issues while migration, metadata etc. ?

 

Thanks in advance.

 

Best regards.

 

Olaf

 

 

 

 

AW: [firebird-support] Small Database with very very bad performance on LAN

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

Verbose :)

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 16:30
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Small Database with very very bad
performance on LAN

Hello,

> Hallo,
> I have a small database (less 100MB)  that works on Firebird 2.5.5 
> SS64bit over a machine with Win7 64bit with 4GB Ram, CPU Pentium G620.
>
> I seems to work very well in local but with the 3 clients on LAN works 
> very bad. It is very very slow to open and navigate a simple table 
> with less of 5.000 rows!
>
> What i can do?

You have to sort out where your current bottleneck is.

Can you pin-point it to a particular query or is performance in general bad?
With Firebird 2.5, you have quite some monitoring amory, e.g. 
monitoring tables (tables starting with MON$...) or the Trace API.

Try to get a big picture with that tooling to see what's going, statement
execution plans etc..., I/O statistics, e.g. is data being fetched from disk
or served from the Firebird cache, which I doubt with your page buffers
value below.

>
> I very novice in Firebird.
>
> The result of gstat is:
> Database header page information:
>   Flags   0
>   Checksum12345
>   Generation  26613
>   Page size   4096
>   ODS version 11.2
>   Oldest transaction  26603
>   Oldest active   26604
>   Oldest snapshot 26604
>   Next transaction26605
>   Bumped transaction  1
>   Sequence number 0
>   Next attachment ID  139
>   Implementation ID   26
>   Shadow count0
>   Page buffers0
>   Next header page0
>   Database dialect3
>   Creation date   Jan 4, 2016 15:44:10
>   Attributes  force write
>
>   Variable header data:
>   Sweep interval: 2
>   *END*

With SuperServer and a page size of 4096 and a page buffers set of 0, which
gets overriden with a default value of 2048 from firebird.conf (if not
specified otherwise), you end up with a Firebird page cache of 8MB
(!) only for your database.

Increase "Page buffers" to e.g. 1 (e.g. with gfix command-line tool),
then re-connect your client application and see if this helps.

There is other tuning stuff, but as a first step increasing the Firebird
page cache + getting a big picture on your slow statements, their execution
plan probably pointing to missing indexes ... is a good start.

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

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






++

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

AW: [firebird-support] Architecture

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

Hello Thomas,

Thanks. For some Years, we have had in use the superserver, but since we
have a quadcore CPU, we are using cs.

UDFs can I replace with build in functions

Now, I have restored the database with 2.5, but.. Errors :( Now I have seen
the new switches -fix_fss_d and m, but now I get another error, no character
set were found. Is there another way to migrate? With the scripts while
running 2.1?

thanks

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 16:37
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Architecture

Hello Olaf,

> Hello,
>
> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to 
> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit installation 
> of Firebird too?

 From an operational POV, a main factor with moving from 32-bit to 64-bit is
if you are using (third-party) UDF libraries. You need them to be compiled
with a 64-bit compiler if you want to run Firebird 64-bit.

Other than that, I guess it depends on your planned load, especially
addressable RAM by the Firebird server process. If still intend to run with
ClassicServer, then each connection spawns its own process, thus you
probably won't hit the addressable RAM limit with Firebird 32-bit. 
That might be different with Firebird SuperClassic (32-bit), because this is
a single process architecture.

I connect with odbc. Known Issues while migration,
> metadata etc. ?

The usual path is creating a gbak-based backup with Firebird 2.1 and restore
it under Firebird 2.5. The resulting ODS will be 11.2 (you can check with
gstat -h afterwards).

On the client side, make sure that you are using a version of the Firebird
client library matching the server version. Bitness (32-bit vs. 
64-bit) of the client library is driven by your application and not by the
Firebird server. So, if you are application is 32-bit (Delphi
32-bit?) use a Firebird 32-bit client library and ODBC driver.

Hope this helps.

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

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






++

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

AW: AW: AW: AW: [firebird-support] Architecture

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

-verbose :) I remember me. 

So I can see what table is he problematic one.

Have a nice weekend, Thomas, many Thanks :)

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 17:28
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: AW: [firebird-support] Architecture

> Yes, Thomas,
>
> it woks with 2.1. In the release notes of 2.5.X is written:
>
> Adding a value to a timestamp earlier than '16.11.1858 00:00:01' would 
> throw the error "value exceeds the range for valid timestamp".
>
> But where can I search 200 Tables...

Read Paul's blog on using -v for the restore etc ...


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

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


> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 17:07
> An: firebird-support@yahoogroups.com
> Betreff: Re: AW: AW: [firebird-support] Architecture
>
>> now, the switches works fine, I had forget the Charset -fix_fss_d
>> ISO8859_1
>>
>> It works fine, but there is another problem to fix. Now I get the 
>> error, that value exceeds the range for valid timestamps. How can I 
>> fix
> this issue?
>
> * When does that happen during the restore?
> * Does a backup *AND* restore with Firebird 2.1 works?
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB 
> LogManager, Database Health Check, Tuning etc.
>
>> I have installed fb as cs without superclassic under 32 Bit Win7 the
>> 32 Bit Version
>>
>> -Ursprüngliche Nachricht-
>> Von: firebird-support@yahoogroups.com 
>> [mailto:firebird-support@yahoogroups.com]
>> Gesendet: Freitag, 8. Januar 2016 16:51
>> An: firebird-support@yahoogroups.com
>> Betreff: Re: AW: [firebird-support] Architecture
>>
>>> Thanks. For some Years, we have had in use the superserver, but 
>>> since we have a quadcore CPU, we are using cs.
>>>
>>> UDFs can I replace with build in functions
>>
>> I guess you can imagine that you first need to do that before backing 
>> up and restoring?
>>
>>> Now, I have restored the database with 2.5, but.. Errors :( Now I 
>>> have seen the new switches -fix_fss_d and m, but now I get another 
>>> error, no character set were found.
>>
>> * What is the character set of your database used when you have 
>> created the database in the past?
>> * What is the exact error message?
>> * How did you install Firebird 2.5?
>>
>> Is there another way to migrate? With the scripts while
>>> running 2.1?
>>
>> backup/restore via gbak is the preferred solution, because it also 
>> moves data. Of course, you still can create an empty Firebird 2.5 
>> based database with a DDL script and then pump your data with a tool 
>> from your source into the destination database.
>>
>>
>> --
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com/
>>
>> Professional Tools and Services for Firebird FB TraceManager, IB 
>> LogManager, Database Health Check, Tuning etc.
>>
>>
>>
>>>
>>> thanks
>>>
>>> -Ursprüngliche Nachricht-
>>> Von: firebird-support@yahoogroups.com 
>>> [mailto:firebird-support@yahoogroups.com]
>>> Gesendet: Freitag, 8. Januar 2016 16:37
>>> An: firebird-support@yahoogroups.com
>>> Betreff: Re: [firebird-support] Architecture
>>>
>>> Hello Olaf,
>>>
 Hello,

 I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to 
 Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit 
 installation of Firebird too?
>>>
>>> From an operational POV, a main factor with moving from 32-bit 
>>> to 64-bit is if you are using (third-party) UDF libraries. You need 
>>> them to be compiled with a 64-bit compiler if you want to run Firebird
64-bit.
>>>
>>> Other than that, I guess it depends on your planned load, especially 
>>> addressable RAM by the Firebird server process. If still intend to 
>>> run with ClassicServer, then each connection spawns its own process, 
>>> thus you probably won't hit the addressable RAM limit with Firebird
> 32-bit.
>>> That might be different with Firebird SuperClassic (32-bit), because 
>>> this is a single process architecture.
>>>
>>> I connect with odbc. Known Issues while migration,
 metadata etc. ?
>>>
>>> The usual path is creating a gbak-based backup with Firebird 2.1 and 
>>> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you 
>>> can check with gstat -h afterwards).
>>>
>>> On the client side, make sure that you are using a version of the 
>>> Firebird client library matching the server version. Bitness (32-bit vs.
>>> 64-bit) of the client library is driven by your application and not 
>>> by the Firebird server. So, if you are application is 32-bit (Delphi
>>> 32-bit?) use a Firebird 32-bit client library and ODBC 

AW: AW: AW: [firebird-support] Architecture

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

Yes, Thomas,

it woks with 2.1. In the release notes of 2.5.X is written:

Adding a value to a timestamp earlier than '16.11.1858 00:00:01' would throw
the error "value exceeds the range for valid timestamp".

But where can I search 200 Tables...

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 17:07
An: firebird-support@yahoogroups.com
Betreff: Re: AW: AW: [firebird-support] Architecture

> now, the switches works fine, I had forget the Charset -fix_fss_d 
> ISO8859_1
>
> It works fine, but there is another problem to fix. Now I get the 
> error, that value exceeds the range for valid timestamps. How can I fix
this issue?

* When does that happen during the restore?
* Does a backup *AND* restore with Firebird 2.1 works?


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

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

> I have installed fb as cs without superclassic under 32 Bit Win7 the 
> 32 Bit Version
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:51
> An: firebird-support@yahoogroups.com
> Betreff: Re: AW: [firebird-support] Architecture
>
>> Thanks. For some Years, we have had in use the superserver, but since 
>> we have a quadcore CPU, we are using cs.
>>
>> UDFs can I replace with build in functions
>
> I guess you can imagine that you first need to do that before backing 
> up and restoring?
>
>> Now, I have restored the database with 2.5, but.. Errors :( Now I 
>> have seen the new switches -fix_fss_d and m, but now I get another 
>> error, no character set were found.
>
> * What is the character set of your database used when you have 
> created the database in the past?
> * What is the exact error message?
> * How did you install Firebird 2.5?
>
> Is there another way to migrate? With the scripts while
>> running 2.1?
>
> backup/restore via gbak is the preferred solution, because it also 
> moves data. Of course, you still can create an empty Firebird 2.5 
> based database with a DDL script and then pump your data with a tool 
> from your source into the destination database.
>
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB 
> LogManager, Database Health Check, Tuning etc.
>
>
>
>>
>> thanks
>>
>> -Ursprüngliche Nachricht-
>> Von: firebird-support@yahoogroups.com 
>> [mailto:firebird-support@yahoogroups.com]
>> Gesendet: Freitag, 8. Januar 2016 16:37
>> An: firebird-support@yahoogroups.com
>> Betreff: Re: [firebird-support] Architecture
>>
>> Hello Olaf,
>>
>>> Hello,
>>>
>>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to 
>>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit 
>>> installation of Firebird too?
>>
>>From an operational POV, a main factor with moving from 32-bit to 
>> 64-bit is if you are using (third-party) UDF libraries. You need them 
>> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit.
>>
>> Other than that, I guess it depends on your planned load, especially 
>> addressable RAM by the Firebird server process. If still intend to 
>> run with ClassicServer, then each connection spawns its own process, 
>> thus you probably won't hit the addressable RAM limit with Firebird
32-bit.
>> That might be different with Firebird SuperClassic (32-bit), because 
>> this is a single process architecture.
>>
>> I connect with odbc. Known Issues while migration,
>>> metadata etc. ?
>>
>> The usual path is creating a gbak-based backup with Firebird 2.1 and 
>> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you 
>> can check with gstat -h afterwards).
>>
>> On the client side, make sure that you are using a version of the 
>> Firebird client library matching the server version. Bitness (32-bit vs.
>> 64-bit) of the client library is driven by your application and not 
>> by the Firebird server. So, if you are application is 32-bit (Delphi
>> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>>
>> Hope this helps.
>>
>> --
>> With regards,
>> Thomas Steinmaurer
>> http://www.upscene.com/
>>
>> Professional Tools and Services for Firebird FB TraceManager, IB 
>> LogManager, Database Health Check, Tuning etc.
>>
>>
>> 
>>
>> 
>>
>> ++
>>
>> 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/
>>
>> ++
>> 
>>

AW: AW: [firebird-support] Architecture

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

Hello,

now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1

It works fine, but there is another problem to fix. Now I get the error,
that value exceeds the range for valid timestamps. How can I fix this issue?

I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit
Version

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 16:51
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Architecture

> Thanks. For some Years, we have had in use the superserver, but since 
> we have a quadcore CPU, we are using cs.
>
> UDFs can I replace with build in functions

I guess you can imagine that you first need to do that before backing up and
restoring?

> Now, I have restored the database with 2.5, but.. Errors :( Now I have 
> seen the new switches -fix_fss_d and m, but now I get another error, 
> no character set were found.

* What is the character set of your database used when you have created the
database in the past?
* What is the exact error message?
* How did you install Firebird 2.5?

Is there another way to migrate? With the scripts while
> running 2.1?

backup/restore via gbak is the preferred solution, because it also moves
data. Of course, you still can create an empty Firebird 2.5 based database
with a DDL script and then pump your data with a tool from your source into
the destination database.


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

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



>
> thanks
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:37
> An: firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Architecture
>
> Hello Olaf,
>
>> Hello,
>>
>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to 
>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit 
>> installation of Firebird too?
>
>   From an operational POV, a main factor with moving from 32-bit to 
> 64-bit is if you are using (third-party) UDF libraries. You need them 
> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit.
>
> Other than that, I guess it depends on your planned load, especially 
> addressable RAM by the Firebird server process. If still intend to run 
> with ClassicServer, then each connection spawns its own process, thus 
> you probably won't hit the addressable RAM limit with Firebird 32-bit.
> That might be different with Firebird SuperClassic (32-bit), because 
> this is a single process architecture.
>
> I connect with odbc. Known Issues while migration,
>> metadata etc. ?
>
> The usual path is creating a gbak-based backup with Firebird 2.1 and 
> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can 
> check with gstat -h afterwards).
>
> On the client side, make sure that you are using a version of the 
> Firebird client library matching the server version. Bitness (32-bit vs.
> 64-bit) of the client library is driven by your application and not by 
> the Firebird server. So, if you are application is 32-bit (Delphi
> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>
> Hope this helps.
>
> --
> With regards,
> Thomas Steinmaurer
> http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB 
> LogManager, Database Health Check, Tuning etc.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
> 
> Posted by: "checkmail" 
> 
>
> ++
>
> 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
>
>
>








++

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 

AW: AW: [firebird-support] Architecture

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

200 Tables :( Looking for timestamps before '16.11.1858 00:00:01'

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 17:01
An: firebird-support@yahoogroups.com
Betreff: AW: AW: [firebird-support] Architecture

 

  

Hello,

now, the switches works fine, I had forget the Charset -fix_fss_d ISO8859_1

It works fine, but there is another problem to fix. Now I get the error,
that value exceeds the range for valid timestamps. How can I fix this issue?

I have installed fb as cs without superclassic under 32 Bit Win7 the 32 Bit
Version

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
 
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 8. Januar 2016 16:51
An:  
firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] Architecture

> Thanks. For some Years, we have had in use the superserver, but since 
> we have a quadcore CPU, we are using cs.
>
> UDFs can I replace with build in functions

I guess you can imagine that you first need to do that before backing up and
restoring?

> Now, I have restored the database with 2.5, but.. Errors :( Now I have 
> seen the new switches -fix_fss_d and m, but now I get another error, 
> no character set were found.

* What is the character set of your database used when you have created the
database in the past?
* What is the exact error message?
* How did you install Firebird 2.5?

Is there another way to migrate? With the scripts while
> running 2.1?

backup/restore via gbak is the preferred solution, because it also moves
data. Of course, you still can create an empty Firebird 2.5 based database
with a DDL script and then pump your data with a tool from your source into
the destination database.

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

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

>
> thanks
>
> -Ursprüngliche Nachricht-
> Von: firebird-support@yahoogroups.com
 
> [mailto:firebird-support@yahoogroups.com]
> Gesendet: Freitag, 8. Januar 2016 16:37
> An:  
firebird-support@yahoogroups.com
> Betreff: Re: [firebird-support] Architecture
>
> Hello Olaf,
>
>> Hello,
>>
>> I plan an update of firebird 2.1 cs 32 Bit with 32 Bit Server OS to 
>> Firebird 2.5. Is there a 64 Bit OS recommend and a 64 Bit 
>> installation of Firebird too?
>
> From an operational POV, a main factor with moving from 32-bit to 
> 64-bit is if you are using (third-party) UDF libraries. You need them 
> to be compiled with a 64-bit compiler if you want to run Firebird 64-bit.
>
> Other than that, I guess it depends on your planned load, especially 
> addressable RAM by the Firebird server process. If still intend to run 
> with ClassicServer, then each connection spawns its own process, thus 
> you probably won't hit the addressable RAM limit with Firebird 32-bit.
> That might be different with Firebird SuperClassic (32-bit), because 
> this is a single process architecture.
>
> I connect with odbc. Known Issues while migration,
>> metadata etc. ?
>
> The usual path is creating a gbak-based backup with Firebird 2.1 and 
> restore it under Firebird 2.5. The resulting ODS will be 11.2 (you can 
> check with gstat -h afterwards).
>
> On the client side, make sure that you are using a version of the 
> Firebird client library matching the server version. Bitness (32-bit vs.
> 64-bit) of the client library is driven by your application and not by 
> the Firebird server. So, if you are application is 32-bit (Delphi
> 32-bit?) use a Firebird 32-bit client library and ODBC driver.
>
> Hope this helps.
>
> --
> With regards,
> Thomas Steinmaurer
>   http://www.upscene.com/
>
> Professional Tools and Services for Firebird FB TraceManager, IB 
> LogManager, Database Health Check, Tuning etc.
>
>
> 
>
> 
>
> ++
>
> 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
>
>
>
>
>
> 
> Posted by: "checkmail" < 
check_m...@satron.de>
> 
>
> ++
>
> Visit   http://www.firebirdsql.org and click

AW: [firebird-support] string difficulty

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

Hello,

 

but this ist almost realized in my code, isn't it?

 

>From right until there is no * present

 

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 23:28
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] string difficulty

 

  

How about working backwards through the string?

 

Andrew Zenz

 

  _  

From: firebird-support@yahoogroups.com

[mailto:firebird-support@yahoogroups.com] 
Sent: Tuesday, 8 December 2015 6:59 AM
To: firebird-support@yahoogroups.com
 
Subject: Re: [firebird-support] string difficulty

 

  

Den 07.12.2015 13:55, skrev 'checkmail'  
check_m...@satron.de 
[firebird-support]:
> .. and if the ID has the length of 15 chars and only the las * is for 
filling out to 16, i get a wrong result too.
>
> Can I compact my code?
>
> if(char_length(str)>16) then
> begin
> str = left(:str,16);
> while (k = 1) do
> begin
> if(right(str, 1) = '*') then
> begin
> str = left(str,char_length(str)-1);
> end
> else
> begin
> teil = str;
> k = 0;
> end
> end
> end
>
> (exept the one begin-end (only one command can be without..)
>

 
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-trim.html

Hence, I think your 16 lines above can be replaced by one simple line 
(if you use Fb 2.5):

str = trim (trailing '*' from left(:str, 16));

HTH,
Set

AW: [firebird-support] string difficulty

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

Thank you Set :)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 20:59
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] string difficulty

 

  

Den 07.12.2015 13:55, skrev 'checkmail'   
check_m...@satron.de 
[firebird-support]:
> .. and if the ID has the length of 15 chars and only the las * is for 
filling out to 16, i get a wrong result too.
>
> Can I compact my code?
>
> if(char_length(str)>16) then
> begin
> str = left(:str,16);
> while (k = 1) do
> begin
> if(right(str, 1) = '*') then
> begin
> str = left(str,char_length(str)-1);
> end
> else
> begin
> teil = str;
> k = 0;
> end
> end
> end
>
> (exept the one begin-end (only one command can be without..)
>

  
http://www.firebirdsql.org/refdocs/langrefupd25-intfunc-trim.html

Hence, I think your 16 lines above can be replaced by one simple line 
(if you use Fb 2.5):

str = trim (trailing '*' from left(:str, 16));

HTH,
Set

AW: [firebird-support] string difficulty

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

Hi,

 

first, thank you!

 

But it should only delete the right *

 

For example, the ID is ABCDEFG, the interface will tell me ABCDEFG*01 
In this case, from right, all * should deleted. 

 

But If the ID is ABC*DEFG, I get ABC*DEFG01 and in this case only all * 
from right should be deleted.

 

I have create this, is there a simpler way?

 

  begin

str = left(:str,16); -- only 16 digits, the other I don’t use here

while (k = 1) do

begin

  if(right(str, 1) = '*') then

  begin

str = left(str,char_length(str)-1);

  end

  else

  begin

   teil = str;

   k = 0;

  end

end

  end

 

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 09:56
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] string difficulty

 

  

How can I get ABC*DEFG, leave the * between C*D, delete all * from right?





 

 

SET TERM ^ ;

CREATE PROCEDURE DEL_CHAR (

M_SIR varchar(500),

M_CHAR varchar(10) )

RETURNS (

M_STR varchar(500) )

AS

declare ii smallint; 

declare iy smallint;

BEGIN

m_str='';

iy=char_length(m_sir);

ii=1;

while (ii<=iy) do

begin

if (position(substring(m_sir from ii for 1),m_char)=0) then

m_str=m_str||substring(m_sir from ii for 1);

ii=ii+1;

end

END^

SET TERM ; ^

 

m_sir='ABC*DEFG'

m_char='*'

 

[firebird-support] string difficulty

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

Hello @ll,

 

I have an input string ABCDEFG, filled with * to 16 chars, ABCDEFG*,
followed with zwo digits, f. e. 01

ABCDEFG*01

01 can I cut for an other function, now I have ABCDEFG*

The filled * I can delete for my real ID (ABCDEFG)

left(:str,  position('*' in :str)-1));

 

It works find, but there are IDs available with * in the middle of the ID :(
For example ABC*DEFG and I receive this: ABC*DEFG01 

In this case the function will return ABC

 

How can I get ABC*DEFG, leave the * between C*D, delete all * from right?

 

Thank you.

 

Best regards

 

Olaf

 

 

AW: [firebird-support] string difficulty

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

Hi,

it is a sub-function an I get a string, I dont select it from a table. Our
customer should not use the * in the id, but he did. In the interface to the
cheer is set to it with filling *.

Thank you

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 10:09
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] string difficulty

Hi, using your code, how about left(:str,  position('**' in :str)-1));
select left(:str, position('**' in :str)-1)) from yourtable However, to make
things easier for future users, think about changing the single '*' to e.g.
'-' and then you can keep your existing select statements and not have this
problem when a new ABC*DEFG01 becomes ABC*DEFGHIJKLMN*01

Alan J Davies
Aldis


On 07/12/2015 08:29, 'checkmail' check_m...@satron.de [firebird-support]
wrote:
> Hello @ll,
>
> I have an input string ABCDEFG, filled with * to 16 chars, 
> ABCDEFG*, followed with zwo digits, f. e. 01
>
> ABCDEFG*01
>
> 01 can I cut for an other function, now I have ABCDEFG*
>
> The filled * I can delete for my real ID (ABCDEFG)
>
> left(:str,  position('*' in :str)-1));
>
> It works find, but there are IDs available with * in the middle of the 
> ID LFor example ABC*DEFG and I receive this: ABC*DEFG01
>
> In this case the function will return ABC
>
> How can I get ABC*DEFG, leave the * between C*D, delete all * from right?
>
> Thank you.
>
> Best regards
>
> Olaf
>
> 






++

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

AW: AW: [firebird-support] string difficulty

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

But in this case The comparision should be for two chars

 

while (ii<=iy) do

begin

if (position(substring(m_sir from ii for 2),m_char)=0) then

m_str=m_str||substring(m_sir from ii for 1);

ii=ii+1;

end

 

But, if the original ID like TEIL***ABC Then will be the three *** deletet, 
only the right * should be cut, this are the * who filled…

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 11:22
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] string difficulty

 

  

a quick answer :

m_char='**'

 

 

 

On Monday, December 7, 2015 12:12 PM, "'checkmail'  
 check_m...@satron.de [firebird-support]" < 
 firebird-support@yahoogroups.com> 
wrote:

 

But If the ID is ABC*DEFG, I get ABC*DEFG01 and in this case only all * 
from right should be deleted.

 

I have create this, is there a simpler way?

  

AW: AW: [firebird-support] string difficulty

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

.. and if the ID has the length of 15 chars and only the las * is for filling 
out to 16, i get a wrong result too.

 

Can I compact my code?

 

  if(char_length(str)>16) then

  begin

str = left(:str,16);

while (k = 1) do

begin

  if(right(str, 1) = '*') then

  begin

str = left(str,char_length(str)-1);

  end

  else

  begin

   teil = str;

   k = 0;

  end

end

  end

 

(exept the one begin-end (only one command can be without..)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 7. Dezember 2015 11:32
An: firebird-support@yahoogroups.com
Betreff: Re: AW: [firebird-support] string difficulty

 

  

select replace('ABC*DEFG01','**','') from RDB$DATABASE

 

 

On Monday, December 7, 2015 12:26 PM, "Virna Constantin  
 costel...@yahoo.com [firebird-support]" < 
 firebird-support@yahoogroups.com> 
wrote:

 

 

sorry, wrong response :(

 

 

 

On Monday, December 7, 2015 12:22 PM, "Virna Constantin  
 costel...@yahoo.com [firebird-support]" < 
 firebird-support@yahoogroups.com> 
wrote:

 

 

a quick answer :

m_char='**'

 

 

 

On Monday, December 7, 2015 12:12 PM, "'checkmail'  
 check_m...@satron.de [firebird-support]" < 
 firebird-support@yahoogroups.com> 
wrote:

 

But If the ID is ABC*DEFG, I get ABC*DEFG01 and in this case only all * 
from right should be deleted.

 

I have create this, is there a simpler way?

  

 

 

 

 

[firebird-support] normalization and placeholder

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

Hello,

currently I save a path to a picture like this in my database:
s:\20150201101005_1\r0001.tif - firebird 2.5

 

But this path I can compose from the other informations. First I have a
timestamp 2015-02.,, _1 is the line where it is created, 1 is the picture
Number.

 

Now I would create a config Table with which I can set the Path dynamically.
If there a change, I can create a new configuration an set it to the
picture.

 

But how could I save this configuration? S:\ || :TimestampVar || _ ||
:linevar ,,,  Is there only possible with an sql statement (set the String
in a procedure and execute statement..) How can I realize this? There are
more options?

 

Thanks in advance

 

Best regards

 

Olaf

 

 

[firebird-support] in() in firebird

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

Hello,

 

I would like to a variable for the in-function

 

Declare str_var varchar(20)

While.

Str_var = str_var || integer

 

(str_var = 4,7,8)

 

Select 1 from rdb$database where (select . where field in (:str_var)

 

Is there a way to realize it? I know, I can use a statement.. execute
statement.. but perhaps I can do it without?

 

I cant select it (where field in(select..) because I get an input string

 

Thankss

 

 

[firebird-support] Performance question

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

Hello,

 

the situation: 

state for each element (can be 0 or 1 for Example) = 1001000..

Is it faster to insert this in a varchar-field or is it better to store it
in zwenty smallint separate database fields? I would like to analyze it
later.

(read substring or each field)

 

Thanks in advance

 

Best regards

 

Olaf

 

[firebird-support] Firebird, ODBC, MSACCESS

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

Hello @ll,

 

I have already a big Problem with this combination, msaccess (all versions)
and firebird 2 (testet with 2, 2.1, 2.5) and the actually odbc driver.

 

First the constellation:

 

Table a

Primary Key year and number, so 2015 1, 2015 2, unique with main data for
orders, one field is the number of the supplier, so 1 = supplier a from
Germany, 2 is supplier b.

 

And there is a table 2

 

Foreign key Year and Number to Table 1, ID primary key and unique. There can
be some more than 1 positions for every order, 1 to n relation.

 

I have already tried some things, so I have create the number not with
(select max(nr)+1) but now with a generator.

 

The Problem: If the last order has the same supplier-id like the new
created, MS Access shows me in some cases the Orderpositions in the subform
(table 2) from the order before (with the same supplier-id).. And this at
the point of refresh. So there is a new position createt, I have now three
(if the order before have had 2), If I delete the order positions, I delete
all positions, already the 2 from the order before.

 

Is there a workaround to solve this issue? In Access there is a main form
with a subform, connectet from year and number to year and number.

 

Thanks a lot

 

Best regards.

 

Olaf

 

 

 

 

 

AW: [firebird-support] coalesce

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

Okay,

 

Thanks @ll! It was in fact a char instead a varchar item.

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Mittwoch, 25. März 2015 22:11
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] coalesce

 

  



 one short question, why is the Result of
 coalesce(:variableVarchar(10),'NULL') not ,NULL' but rather 'NULL  '
with
 blanks from varchar(10) length?

What tool/application are you using to evaluate the result?

As Dmitry and Mark have already pointed out, the result should be a
VarChar(10) (i.e. without any trailing spaces)

It may be the tool/application handling the result which is adding the extra
blanks.

Sean

[firebird-support] coalesce

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

Hey everyone,

 

one short question, why is the Result of
coalesce(:variableVarchar(10),'NULL') not ,NULL' but rather 'NULL  '
with blanks from varchar(10) length?

 

Thank you.

 

Best regards.

 

Olaf

 

 

AW: [firebird-support] Index

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

Thank you set :)

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 3. März 2015 10:07
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Index

 

  

Hello everybody,

I'm looking in a table for an existing record bevore I insert it. Now I
would set some indices. There are 4 fields I compare. 
It is better I create one index with all 4 fields or for every field one?

Difficult question with several potential answers.

Generally, one combined index is a bit faster. So, if this is the only query
with any of these four fields in the WHERE or JOIN clause OR the fields have
lousy selectivity by themselves OR this is the one query where performance
is vital, then go for the combined index.

However, there are a few benefits from using single field indexes. 

-It is easy to understand which fields are used for a particular PLAN (hence
easier to optimize)
-It is easy to see whether you should consider adding new indexes
-Each index can be used in more scenarios (the first field of a multifield
index can be used for all queries with this field, the following fields only
if the first field is compared for equality by the query)

Generally, I find it simpler to generally have single field indexes and only
rarely use combined indexes myself.

So, I'm sorry to say there's no general answer for your question and that
you have to consider for yourself what is most important in your particular
case.

now I have insert an index for the first both fields, the ibexpert analysis
shows me non indexed reads, why? 

I don't know, sorry...

The stored procedure also looking for two other fields.
SELECT 1 FROM rdb$database WHERE EXISTS(SELECT * FROM t_errordata WHERE
kd_id = :t_timestamp
 and li_id = :i_li and ftyp = :i_ftyp and ((fnr = :i_fnr) and (fnr =
:i_fnr + (:fanzds-1 INTO :idexists;

kd_id and li_id now indexed (in one index) ftyp and fnr not. The analysis
said there are 1 indexed read, 
bevore I inserted the index there are thousends non indexed.

I assume kd_id or li_id are fairly selective. My guess is that without any
index, Firebird had to scan through thousands of pages before it found one
that matched your criteria, with the index, Firebird can go straight to the
correct kd_id and li_id and then the first matches with respect to i_fnr.

HTH,
Set

AW: [firebird-support] Index

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

Thank you :)

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 3. März 2015 09:35
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] Index

 

  

Hello Olaf,

 

A combined index, with the field that has the largest number of different 
values as the first indexed field.

 

With regards,

Martijn Tonies
Upscene Productions
 http://www.upscene.com http://www.upscene.com

Download Database Workbench for Oracle, MS SQL Server, Sybase SQL
Anywhere, MySQL, InterBase, NexusDB and Firebird!

 

 

From: mailto:firebird-support@yahoogroups.com 

Sent: Tuesday, March 03, 2015 9:29 AM

To: firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com  

Subject: [firebird-support] Index

 






Hello everybody,

 

I’m looking in a table for an existing record bevore I insert it. Now I would 
set some indices. There are 4 fields I compare… It is better I create one index 
with all 4 fields or for every field one?

 

Thank your

Olaf

 

 

[firebird-support] nesting - weight calculation

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

Hello,

 

there is a table with all Material (stock), each one has a number. Now, in a
second table there is listed the material that is a part of the Material.

 

For example:

 

Material ABC

Material BCD

Material XXX

Material OMV

Material KOM

 

Material OMV can be a Part of XXX and XXX a Part of ABC, of course the BCD
can assigned to XXX, OMV, ABC. 

 

Now I would like to know what is the total weight of the main material,
(total weight from abc + amount of bcd + amount of xxx

 

ABC has 2 XXX, XXX has 2 OMV

Now we have 1 X ABC, 2 times XXX and 4 X OMV, OMV weighs 0,5kg and so on

 

The structure

 

Table Mainmaterial

PartNo

Description

 

Table Material

PartNo of Mainmaterial

PartNoA = assigned Material

Amount (of PartNoA)

 

Is there a simpler statement possible than my stored procedure (recursive)

 

Thank you.

 

 

 

 

 

 

AW: [firebird-support] Performance optimation?

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

Hello Mark,

-That is not surprising. You are executing a query and for each row you are
-executing another query. Use joins and/or subqueries instead.

-Mark

Sometimes, there is the normalization of the database a disadvantage. I should 
save the timestamp and data in one table, so I save the time in one and the ID 
of the time in the second table with data, because for each time I save 20+ 
records (values).

I need for each time (per hour, per day, per month) only one row returned and 
in the data-field all items like (item1:sumofitem1;item2:sumofitem2;…) Very 
fast is the following statement:

for select extract (day from a.messzeit) || '.' || extract (month from 
a.messzeit) || '.' || extract(year from a.messzeit) || ' ' ||

extract(hour from a.messzeit) || ':00:00' as mz, b.id_zaehler, sum(b.wert) from 
te_messzeiten a left join te_werte b on a.id =b.id_messzeit

where a.messzeit = :zr_von and a.messzeit  :zr_von + 1

group by mz, b.id_zaehler

into :messzeit, :messwerte, :messwerte2 do

suspend;

(zr_von is the condition (timestamp))

But in this case I get for each item and each time a record returned. This is 
not what I need.

What solution can I use for a faster result?

(It should be, vor example group by day:

2014-01-01 01:00:00 01:100,0;02:199,1;03:222,9 (grouped for each hour for the 
day for each item)
2014-01-01 02:00:00 01:300,0;02:198,1;03:211,7

Source:

Table a

ID;Timestamp
1; 2014-01-01 01:00:00
2; 2014-01-01 01:10:00
3; 2014-01-01 01:20:00

Table b

ID;ID_TableA;Item;Value

1;1;10;0.32
2;1;11;0.44
3;1;12;1.22
4;2;10;0.39
5;2;11;0.45
6;2;12;1.21

Thank you

  _  

Posted by: Mark Rotteveel  mailto:m...@lawinegevaar.nl m...@lawinegevaar.nl 

  _  


 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125865;_ylc=X3oDMTJyMmNjNTQ4BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyNTg2NQRzZWMDZnRyBHNsawNycGx5BHN0aW1lAzE0MTg2NTYwOTQ-?act=replymessageNum=125865
 Reply via web post 

•

 
mailto:m...@lawinegevaar.nl?subject=Re%3A%20%5Bfirebird-support%5D%20Performance%20optimation%3F
 Reply to sender 

•

 
mailto:firebird-support@yahoogroups.com?subject=Re%3A%20%5Bfirebird-support%5D%20Performance%20optimation%3F
 Reply to group 

•

 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/newtopic;_ylc=X3oDMTJlbnZydWRiBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA250cGMEc3RpbWUDMTQxODY1NjA5NA--
 Start a New Topic 

•

 
https://groups.yahoo.com/neo/groups/firebird-support/conversations/topics/125864;_ylc=X3oDMTM4ZDhnazMxBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BG1zZ0lkAzEyNTg2NQRzZWMDZnRyBHNsawN2dHBjBHN0aW1lAzE0MTg2NTYwOTQEdHBjSWQDMTI1ODY0
 Messages in this topic (2) 

++

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/ 

++ 

 
https://groups.yahoo.com/neo/groups/firebird-support/info;_ylc=X3oDMTJlY2tvZm8yBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTQxODY1NjA5NA--
 Visit Your Group 

*  
https://groups.yahoo.com/neo/groups/firebird-support/members/all;_ylc=X3oDMTJmbzJvcXRoBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZtYnJzBHN0aW1lAzE0MTg2NTYwOTQ-
 New Members 5 

 
https://groups.yahoo.com/neo;_ylc=X3oDMTJkYXMycnV2BF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxNDE4NjU2MDk0
 

•  https://info.yahoo.com/privacy/us/yahoo/groups/details.html Privacy •  
mailto:firebird-support-unsubscr...@yahoogroups.com?subject=Unsubscribe 
Unsubscribe •  https://info.yahoo.com/legal/us/yahoo/utos/terms/ Terms of Use 





.

  
http://geo.yahoo.com/serv?s=97359714/grpId=2442406/grpspId=1705115386/msgId=125865/stime=1418656094
 
  
http://y.analytics.yahoo.com/fpc.pl?ywarid=515FB27823A7407Ea=10001310322279js=noresp=img
 

AW: [firebird-support] group by date

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

Hello Mark,

 

I don’t know why, but I haven’t get this e-mail. Thank you very much!

 

Von: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] 
Gesendet: Montag, 15. Dezember 2014 09:00
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] group by date

 

  

On Sun, 14 Dec 2014 15:46:52 +0100, Olaf Kluge  mailto:check_m...@satron.de 
check_m...@satron.de
[firebird-support]  mailto:firebird-support@yahoogroups.com 
firebird-support@yahoogroups.com wrote:
 Any ideas? 
 
 Thank you 
 
 
 Am 12. Dezember 2014 15:15:40 MEZ, schrieb 'checkmail'
  mailto:check_m...@satron.de check_m...@satron.de [firebird-support]
  mailto:firebird-support@yahoogroups.com firebird-support@yahoogroups.com:
Hello @ll,

I would like to group my records by day or month. The date is saved as
timestamp. If I do the following, 

SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages
FROM te_messzeiten m
GROUP BY EXTRACT(day FROM m.messzeit)

I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. -
daily
bzw. 1 2014, 2 2014 - monthly

How can I realize this?

Thank you

Best regards

Olaf

I already answered this, 5 minutes after your initial question. See
https://groups.yahoo.com/neo/groups/firebird-support/conversations/messages/125829

Mark

[firebird-support] Performance optimation?

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

Hello,

 

I save values in some tables (simpler description) 

 

First a Table who saved the timestamp of the mensuration

Table A timestamps

ID primary key

TS timestamp

 

Second a Table with the measured data (25 records/measured sensors will be
saved every 10 Minutes, one record in Table A, 25 in Table B)

Table B mensuration

ID primary key

ID_counter integer of item to measure

ID_Timestamp foreign key of Table A

Value (double precision)

 

Now I would like to make an analysis. At the time, I do this:

 

for select cast(ts as date) as mz from tablea where ts = criteria from
and ts  criteria to

group by mz)

into :messzeit do

begin

  f_messwert = null;

  MESSWERTE = '';

  for select a.id_counter, sum(a.value) from tableb a left join tablea b
on a.id_timestamp = b.id

  where cast(b.ts as date) = :messzeit

  group by a.id_counter

  into :i_zae, :f_messwert do

  begin

if(f_messwert is null) then f_messwert = 0;

MESSWERTE = MESSWERTE || cast(:i_zae as varchar(4)) || '=' ||
cast(:f_messwert as varchar(8)) || ';';

  end

  suspend;

 

end

 

The Result is one returned record for each day (day, conter 1 = 123; counter
2 = 222;.)

 

It takes a long time but I must integrate the tablea on the second part of
the statement. How can I optimize this in firebird?

 

Thank you

 

Regards

 

Olaf

[firebird-support] group by date

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

Hello @ll,

 

I would like to group my records by day or month. The date is saved as
timestamp. If I do the following, 

 

SELECT EXTRACT(day FROM m.messzeit), COUNT(*) as CountMessages

FROM te_messzeiten m

 

GROUP BY EXTRACT(day FROM m.messzeit)

 

I get all days (1 to 31), but I need group by 1.1.2014, 2.1.2014.. - daily
bzw. 1 2014, 2 2014 - monthly

 

How can I realize this?

 

Thank you

 

Best regards

Olaf

 

 

 

 

[firebird-support] Difficult condiion question

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

Hello,

 

the following sql statement is almost done, just one thing is problematic.

 

SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB,
TLAGER_SUM.TYP, TTEILE.TYP

FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR

WHERE (((TLAGER_SUM.MENGE)[minb]) AND ((TLAGER_SUM.TYP)=1) AND
((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)0) AND
((TTEILE.TYP)=1))

ORDER BY TTEILE.TEILENR;

 

In the table tlager_sum I have two types of amounts, one of typ 1, one of
typ 2. I just need the one of typ1. But if the material not in the table
listet or just für typ 2, and there is a minimum inventory in the tteile
set, then should I get this record too.

 

Expect for one constellation it works fine: It was set a minimum inventory
and in the table tlager_sum It was present a record with an amount of typ 2
– then I don’t get this record. How can I solve this problem in firebird? In
Microsoft Access I can create a subquery how gets me only typ 1, whats in
firebird the best way?

 

Thank you

 

Best regards

 

Olaf

 

 

AW: [firebird-support] Difficult condiion question

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

Hello SET,

 

first, thank you!

 

[Minb] should be tteile.minb – the minimum inventory amount. 

 

Can you tell me what about the statements before the where conditions? Until
now I don’t know about.

 

Your syntax returns me too many records, some of them has entries in the
table tlager_sum, but the amount and the present typ (1) returns me this
statement, it is null. 

 

I have a table with stock = tlager_sum. Only records with typ 1 I need, many
of them has two records for each material, typ 1 and typ 2. In my case I
just need the records with typ 1 and now I have the problem, If there is an
entry for typ 2, my code does not return this record. For example:

 

Tteile.teilenr = ‘PIECE1’, tteile.minb = 1000

In the table tlager_sum is a record, teilenr ‘PIECE1’, Menge (amount) = 100,
typ = 2

 

In this case I would return this Teilenr because it is not present and the
minb (minimum inventory amount) is set.

 

Thans J

  

Hello,

the following sql statement is almost done, just one thing is problematic.

In the table tlager_sum I have two types of amounts, one of typ 1, one of
typ 2. I just need the one of typ1. But if the material not in the table
listet 
or just für typ 2, and there is a minimum inventory in the tteile set, then
should I get this record too.

Expect for one constellation it works fine: It was set a minimum inventory
and in the table tlager_sum It was present a record with an amount of typ 2
- 
then I don't get this record. How can I solve this problem in firebird? In
Microsoft Access I can create a subquery how gets me only typ 1, whats in 
firebird the best way?

SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB,
TLAGER_SUM.TYP, TTEILE.TYP
FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR
WHERE (((TLAGER_SUM.MENGE)[minb]) AND ((TLAGER_SUM.TYP)=1) AND
((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)0) AND
((TTEILE.TYP)=1))
ORDER BY TTEILE.TEILENR;

Hi Olaf, I don't quite understand your question, but it is possible that you
just need to add 'AND TLAGER_SUM.TYP = 1' to your left join. Below is the
sql after I tried to tidy it a bit and simplify to see if that helped me
understand your question (by the way, I don't understand [minb], is that
some kind of parameter?).

HTH,
Set

SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP
FROM TTEILE TT
LEFT JOIN TLAGER_SUM LS 
ON TT.TEILENR = LS.TEILENR 
AND LS.TYP = 1
AND LS.MENGE[minb]
AND LS.TYP=1
WHERE TT.TYP=1 
AND (LS.MENGE IS NOT NULL
OR TT.MINB0)
ORDER BY TT.TEILENR;

AW: [firebird-support] Difficult condiion question

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

I think I have it:

 

SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP

FROM TTEILE TT

LEFT JOIN TLAGER_SUM LS 

ON TT.TEILENR = LS.TEILENR 

AND LS.TYP = 1

 

WHERE TT.TYP=1 

AND (LS.MENGE IS NOT NULL

AND LS.MENGEtt.minb

OR TT.MINB0 and ls.menge is null)

ORDER BY TT.TEILENR;

 

The and ls.typ = 1 should exclude all records from tlager_-sum there not typ
1? It was fine! Thank you

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 28. August 2014 14:55
An: firebird-support@yahoogroups.com
Betreff: AW: [firebird-support] Difficult condiion question

 

  

Hello SET,

 

first, thank you!

 

[Minb] should be tteile.minb – the minimum inventory amount. 

 

Can you tell me what about the statements before the where conditions? Until
now I don’t know about.

 

Your syntax returns me too many records, some of them has entries in the
table tlager_sum, but the amount and the present typ (1) returns me this
statement, it is null. 

 

I have a table with stock = tlager_sum. Only records with typ 1 I need, many
of them has two records for each material, typ 1 and typ 2. In my case I
just need the records with typ 1 and now I have the problem, If there is an
entry for typ 2, my code does not return this record. For example:

 

Tteile.teilenr = ‘PIECE1’, tteile.minb = 1000

In the table tlager_sum is a record, teilenr ‘PIECE1’, Menge (amount) = 100,
typ = 2

 

In this case I would return this Teilenr because it is not present and the
minb (minimum inventory amount) is set.

 

Thans J

  

Hello,

the following sql statement is almost done, just one thing is problematic.

In the table tlager_sum I have two types of amounts, one of typ 1, one of
typ 2. I just need the one of typ1. But if the material not in the table
listet 
or just für typ 2, and there is a minimum inventory in the tteile set, then
should I get this record too.

Expect for one constellation it works fine: It was set a minimum inventory
and in the table tlager_sum It was present a record with an amount of typ 2
- 
then I don't get this record. How can I solve this problem in firebird? In
Microsoft Access I can create a subquery how gets me only typ 1, whats in 
firebird the best way?

SELECT TTEILE.TEILENR, TTEILE.BEZEICHNUNG, TLAGER_SUM.MENGE, TTEILE.MINB,
TLAGER_SUM.TYP, TTEILE.TYP
FROM TTEILE LEFT JOIN TLAGER_SUM ON TTEILE.TEILENR = TLAGER_SUM.TEILENR
WHERE (((TLAGER_SUM.MENGE)[minb]) AND ((TLAGER_SUM.TYP)=1) AND
((TTEILE.TYP)=1)) OR (((TLAGER_SUM.MENGE) Is Null) AND ((TTEILE.MINB)0) AND
((TTEILE.TYP)=1))
ORDER BY TTEILE.TEILENR;

Hi Olaf, I don't quite understand your question, but it is possible that you
just need to add 'AND TLAGER_SUM.TYP = 1' to your left join. Below is the
sql after I tried to tidy it a bit and simplify to see if that helped me
understand your question (by the way, I don't understand [minb], is that
some kind of parameter?).

HTH,
Set

SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP
FROM TTEILE TT
LEFT JOIN TLAGER_SUM LS 
ON TT.TEILENR = LS.TEILENR 
AND LS.TYP = 1
AND LS.MENGE[minb]
AND LS.TYP=1
WHERE TT.TYP=1 
AND (LS.MENGE IS NOT NULL
OR TT.MINB0)
ORDER BY TT.TEILENR;

AW: [firebird-support] Difficult condiion question

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

Set, many thanks for your kind support :)

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Donnerstag, 28. August 2014 15:33
An: firebird-support@yahoogroups.com
Betreff: RE: [firebird-support] Difficult condiion question

 

  

I think I have it:

SELECT TT.TEILENR, TT.BEZEICHNUNG, LS.MENGE, TT.MINB, LS.TYP, TT.TYP
FROM TTEILE TT
LEFT JOIN TLAGER_SUM LS 
ON TT.TEILENR = LS.TEILENR 
AND LS.TYP = 1

WHERE TT.TYP=1 
AND (LS.MENGE IS NOT NULL
AND LS.MENGEtt.minb
OR TT.MINB0 and ls.menge is null)
ORDER BY TT.TEILENR;

The and ls.typ = 1 should exclude all records from tlager_-sum there not
typ 1? It was fine! Thank you

Can you tell me what about the statements before the where conditions?
Until now I don't know about.
 
Your syntax returns me too many records, some of them has entries in the
table tlager_sum, but the amount and the present typ (1) returns me this
statement, it is null. 

Good to hear that you figured it out, Olaf. Sorry for misleading you and
writing code that returned records if TT.MINB  0 regardless of ls.menge.
You can still reduce your query by one line if you want, LS.MENGE IS NOT
NULL can simply be removed.

The statements before the WHERE condition are part of the LEFT JOIN.
Typically, that's where you put criteria relating to the right table. If you
write such criteria in the WHERE clause, they could (logically speaking)
turn the LEFT JOIN into an INNER JOIN.

Set

[firebird-support] where condition

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

Hello,

 

1st, the following condition does not work unfortunately. (call a stored
procedure in the where-condition in a coalesce..)

and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and
d.bsnr  coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)

 

I have some article, several with worksteps, other without this.

 

For example

ArticleA =lamp

ArticleB  Workstep2= bicycle mill-cut

ArticleB  Workstep2= bicycle varnish

 

Now I have a stored procedure where I can get the entire stock, input
parameter the type (material (no workstep), unfinish products (workstep 
the last workstep) = typ2 and finished products (last workstep = typ 3 input
parameter)

If the input typ = 1, I should get all material, typ = 2 all unfinished
products.

 

And this I will write in a sql where condition, preferably without an
execute statement. IIF and Case do not work in the condition, neither the
procedure call. (p_getmaxag)

 

How can I realize this simply?

 

Thanks in advance

 

Best regards

 

Olaf

 

 

AW: [firebird-support] where condition

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

This works:

and (d.bsnr = coalesce((select ag from
p_getmaxag(a.teilenr,:typ,1)),d.bsnr))

but how can I get the records without a bsnr, without an workstep, no record
in the table d.bsnr..)

 

Thanks



Hello,

 

1st, the following condition does not work unfortunately. (call a stored
procedure in the where-condition in a coalesce..)

and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and
d.bsnr  coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100)

 

I have some article, several with worksteps, other without this.

 

For example

ArticleA =lamp

ArticleB  Workstep2= bicycle mill-cut

ArticleB  Workstep2= bicycle varnish

 

Now I have a stored procedure where I can get the entire stock, input
parameter the type (material (no workstep), unfinish products (workstep 
the last workstep) = typ2 and finished products (last workstep = typ 3 input
parameter)

If the input typ = 1, I should get all material, typ = 2 all unfinished
products.

 

And this I will write in a sql where condition, preferably without an
execute statement. IIF and Case do not work in the condition, neither the
procedure call. (p_getmaxag)

 

How can I realize this simply?

 

Thanks in advance

 

Best regards

 

Olaf

 

 

AW: [firebird-support] Skip records

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

and mod(id,:teiler)=0)

it works

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 22. Juli 2014 14:47
An: firebird-support@yahoogroups.com
Betreff: [firebird-support] Skip records

 

  

Hello,

 

I would like to read every n record, depending on number of records.

 

Mod() does not work in a condition, how can I realize it with an for loop?
Or should I take a while-loop?

 

Thanks

 

Best regards.

 

Olaf

 

 

 

 

AW: [firebird-support] Skip records

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

Hello again,

is there another solution to get every 5th record? Mod is problematic,
because the starting record is not in every case the first number mod (x) =
0 and I would take all records to calculate.

 

Thank you 

 

--

  

and mod(id,:teiler)=0)

it works

 

Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Dienstag, 22. Juli 2014 14:47
An:  mailto:firebird-support@yahoogroups.com
firebird-support@yahoogroups.com
Betreff: [firebird-support] Skip records

 

  

Hello,

 

I would like to read every n record, depending on number of records.

 

Mod() does not work in a condition, how can I realize it with an for loop?
Or should I take a while-loop?

 

Thanks

 

Best regards.

 

Olaf

 

 

 

 

AW: AW: [firebird-support] substring

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

Hello

wrote:
 Hello Mark,

 Here the code of the sub-Procedure:

 create or alter procedure P_U_CN_TO_INT (
 CN_IN varchar(30))
 returns (
 INT_OUT integer,
 STATUS smallint)
 AS
 declare variable I_ISNUMERIC smallint;
 BEGIN
 select true_param from p_u_isnumeric(:cn_in) into :i_isnumeric;
 if(i_isnumeric = 1) then
 begin
 int_out = cast(cn_in as integer);
 status = 1;
 end
 else
 begin
 int_out = 0;
 status = 0;
 if(cn_in is null) then cn_in = 'NULL!';
 execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in,11);
 end
 SUSPEND;
 when any do
 begin
 execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in || '
 SQL-Fehler: ' || sqlcode ,10);
 status = 0;
 int_out = 0;
 suspend;
 end
 END

 If I call it with '1234', I get 1234 as integer. For example 'AB12' - and
I
 get as status 0 an not an integer value.

 Dat is a string with many digits. I would spit it. When I do it with:
 s_digits = substring(:dat from 1 for 4); and give the stored (sub)
procedure
 the :s_digits for input parameter, it works. Otherwise I give the stored
 (sub) procedure the substring... directly, I get a sql-error -802. But it
is
 the same content in the variable. And then I get not an integer value, for
 example 1234, it ends with this error.

-It looks like you register the actual value of cn_in before storing it, 
-what is the problematic value. Have you checked if that is actually the 
-value that you exepected? You might also want to consider removi-ng the 
-error handling from your stored procedure to check out if the error 
-message contains more information.

-You don't show p_u_isnumeric, have you checked if the error occurs there?

-Mark
-- 
-Mark Rotteveel

it looks like an conversation problem. If I call the stored procedure with
the input parameter (substring(:var from 1 for 4) it doesn't work, but If I
call it with (cast(substring(:var from 1 for 4)as varchar(20)) it does work!
The var is a char variable, the input var from the stored procedure an
varchar - can it be the problem?

 

Thanks a lot.

[firebird-support] concurrency in firebird

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

Hello everybody,

 

I need to split an input string and import some data in tables, like this
(simplified) '123423453456'

In this case, the value 1 is 1234, value 2 = 2345. With a while loop the
values were inserted, if I get an error (value is not a integer), then I
will delete all records with this import-id. 

 

Now, value 1 should be add to an total counter, f. e. old value = 1, new
= 11234. If I get an error, I would like to return to the old value. Now I
have the following idea, I create a new column named tmp_insert an enter the
value to add. If all records were inserted without any errors, I can add the
new value to the total one and reset the column to 0. Now my question, how
handled firebird this with concurrency, if I call the stored procedure more
than once a time. Will ervery step worked to the end before the new call
runs? Can it be problematic? (column tmp_insert and add to the total value)

 

Thank you.

[firebird-support] substring

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

Hello,

 

If I use the following code to extract a part of a string, it works fine:

 

s_datum = substring(:dat from 13 for 4);

s_datum = char(4)

 

Now I would give the same a stored procedure, the first input parameter is
XXX varchar(30), it returns me the integer-value and the status (passed or
not)

select int_out, status from P_u_cn_to_int(substring(:dat from 13 for 4))
into :i_z1,  i_status1;

 

The second thing - it does not work - why? If I set the input-parameter to
'1234', it works, but the substring?

 

Thanks in Advance

 

Best regards

 

Olaf

 

 

AW: [firebird-support] substring

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

Hello Mark,

Here the code of the sub-Procedure:

create or alter procedure P_U_CN_TO_INT (
CN_IN varchar(30))
returns (
INT_OUT integer,
STATUS smallint)
AS
declare variable I_ISNUMERIC smallint;
BEGIN
  select true_param from p_u_isnumeric(:cn_in) into :i_isnumeric;
  if(i_isnumeric = 1) then
  begin
int_out = cast(cn_in as integer);
status = 1;
  end
  else
  begin
int_out = 0;
status = 0;
if(cn_in is null) then cn_in = 'NULL!';
execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in,11);
  end
  SUSPEND;
  when any do
  begin
execute procedure p_db_ins_error('P_U_CN_TO_INT','CN_IN: '|| :cn_in || '
SQL-Fehler: ' || sqlcode ,10);
status = 0;
int_out = 0;
suspend;
  end
END

If I call it with '1234', I get 1234 as integer. For example 'AB12' - and I
get as status 0 an not an integer value.

Dat is a string with many digits. I would spit it. When I do it with:
s_digits = substring(:dat from 1 for 4); and give the stored (sub) procedure
the :s_digits for input parameter, it works. Otherwise I give the stored
(sub) procedure the substring... directly, I get a sql-error -802. But it is
the same content in the variable. And then I get not an integer value, for
example 1234, it ends with this error.

Thanks in Advance.

Best regards

Olaf

-Ursprüngliche Nachricht-
Von: firebird-support@yahoogroups.com
[mailto:firebird-support@yahoogroups.com] 
Gesendet: Freitag, 18. Juli 2014 14:33
An: firebird-support@yahoogroups.com
Betreff: Re: [firebird-support] substring

On 18-7-2014 14:00, 'checkmail' check_m...@satron.de [firebird-support]
wrote:
 If I use the following code to extract a part of a string, it works fine:

 s_datum = substring(:dat from 13 for 4); s_datum = char(4)

 Now I would give the same a stored procedure, the first input 
 parameter is XXX varchar(30), it returns me the integer-value and the 
 status (passed or not)

 select int_out, status from P_u_cn_to_int(substring(:dat from 13 for 
 4)) into :i_z1,  i_status1;

 The second thing – it does not work – why? If I set the 
 input-parameter to ‘1234’, it works, but the substring?

It does not work is a bit vague. What is the value of dat, and what is the
result of the substring? Does it match the expectations of the stored
procedure?

Mark
--
Mark Rotteveel






++

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