Re: [firebird-support] Strange behavior on very large table

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Myles Wakeham wrote:

> The issue is with one very large table that contains about 900,000
> rows.  This table is used as a temporary stora  ge of data that is
> loaded every 24 hours from a CSV file, via an external program.  The
> loading takes about an hour to run, but works reliably.  We are not
> seeing this process changing in terms of time when it is run.

> What we are seeing is that for a period of about 3-4 hours after
> the morning data load is completed (about 4AM our time), any attempt
> to use that table seems to be triggering some very long loading or
> reindexing process.

Given that this table is "temporary" storage, one supposes that you
are deleting rows from it regularly.  Do you happen to be deleting
900,000 rows each day before you load up the latest batch of 900,000?
With Classic, you have cooperative garbage collection, which means the
first transaction that selects on that table following the completion
of the bulk delete will get hit with GC of all those old back
versions.

> This appears on doing certain queries that
> involve indexes, or to re-create an index.

It would be hard to say whether the dog is wagging its tail or the
tail is wagging the dog.  You don't exactly say how this table ebbs
and flows but bulk inserts of course will cause the indexes to be
updated, along with clearing out the junk in the indexes that was
created by deletes.  Indexes are not recreated;  nor are they created
until the engine is instructed to do so.  Indexes are *rebuilt* when a
database is restored from backup and also by
  ALTER INDEX  INACTIVE
followed by
  ALTER INDEX  ACTIVE
which you are probably doing regularly if it's true that this table is
constantly being subjected to bulk deletes and inserts.

The engine doesn't otherwise mess with indexes.

If you're not doing any particular housework on it (restoring from
backup and/or resetting the indexes periodically), then it would be
normal to expect degrading performance until the next time that
housekeeping is done.

> It seems to show some
> sort of caching between RAM and disk going on.  I have set the sort
> size in firebird.conf from the default of 500mb to about 5GB to
> handle this, but that does not seem to have made a difference.

On Classic, there's no point in this.  If anything, it will make it
worse if you have more than a handful of concurrent users.  The sort
files on a big table are going to go to disk regardless, one way or
another.  The engine can't split a sort file so that part of it is in
RAM and part in TempDirectories.  The operating system might decide to
page out some RAM to disk - I seem to recall that's possible on some
systems with older Fb versions but I can't swear to it.

> All other tables are working just fine.  It is only this one table
> and this problem only has appeared in the past 3 weeks or so.& 
> nbsp; Prior to that, it ran without issue.  This suggests to me that
> it has something to do with us reaching some critical volume (as the
> row count has increased at about 20% per year and continues to).

There's nothing like that and 900K rows is not an excessive size.
Rebuilding the indexes on that table would be an easy thing to try
during some time when the table isn't in use. It would be worth
considering how long it has been since the last backup-and-restore
cycle, too.

Cheers,
Helen



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> However, could you clarify what you mean between a session or a transaction?

Session == connection
I guess you know what a transaction is.

You can define a GTT with a life that lasts as long as the transaction
in which it is instantiated, i.e.,

CREATE GLOBAL TEMPORARY TABLE name
  ( [, { | } ...])
  [ON COMMIT {DELETE | PRESERVE} ROWS]

So, ON COMMIT DELETE ROWS empties the GTT instance when the
trasnaction commits (the default), while ON COMMIT PRESERVE ROWS keeps
the data until the session (connection) ends.

For more info, see

https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-tbl.html#fblangref25-ddl-tbl-gtt

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Query optimization on FB3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> By using a global table in Firebird, could not multiple users cause
> a conflict if two such users were to issue the same query against the global 
> table?

No.  A GTT definition is persistent, of course, but persistent data
are not stored in it. A GTT instance is created for use within a
single session or transaction and dies when the session or transaction
ends.  One instance has no knowledge of another - other than multiple
GTTs within the same session or transaction - depending on the life
defined for that GTT.

> Coming from a SQL Server background, I am used to using local
> temporary tables, which are isolated on a query by query basis...

Not the same thing.  The life of a GTT instance can be transaction or
session. (Life depends on a property in the definition.)

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] change a trigger that I create with RDB $ SYSTEM_FLAG = 1.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

luisalej...@hotmail.com,

> how can I change a trigger that I create with RDB $ SYSTEM_FLAG = 1.

You can't.  RDB$SYSTEM_FLAG is an internal setting and, as you have
discovered, trying to set it yourself by modifying the metadata table
has corrupted the metadata.

> He believes it in this way so as not to allow the modification of
> the data of a table that stores the log.

He believes wrongly.  The flag indicates to the engine whether the
object (in this case, the trigger) was created by the engine in
support of some metadata operation.  It has nothing to do with
logging.

> Now I know it's a mistake to place the field RDB $ SYSTEM_FLAG = 1,
> but I need to reset it to 0 to be able to modify it.

Get all users off line (to empty the cache), then log in as the table
owner.  Save the trigger code to a text file, then DROP the trigger.
Recreate the trigger using the PSQL code you saved.

If you are using Firebird prior to Fb3 then GET THE MESSAGE that you
should **NEVER** try to modify the system tables.  From FB3 onwards,
those tables are read-only but with older versions you can still shoot
yourself in the foot.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 3.0 client does not work with remote Firebird 2.5 database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Mark wrote:

> On 20-1-2019 22:35, Helen Borrie hele...@iinet.net.au [firebird-support]
> wrote:
>> Firebird 3 allows configuration at both client and server for some
>> parameters, including these two.  Changes to these parameters must be
>> done at the client side for your situation. If you plan to use the
>> same Firebird setup on the Linus server as a client for databases on
>> both 2.5 and 3.0 servers, you'll need to configure records there in
>> databases.conf, specific to each.  (You could set them globally in
>> firebird.conf but that would, of course, restrict the options for
>> accessing diverse remote databases.)

> Testing on Windows, the Firebird client makes no attempt to read any 
> databases.conf (checked with procmon). As far as I can tell, 
> databases.conf is purely for configuring Firebird server and has no 
> influence on configuring the client.

> Why do you think that databases.conf can also be used to configure the
> client?

I thought so, because that was my understanding from Alex P. when we
were writing that Authentication article.  It's long enough since
then, that I can't recall the discussions in detail.  It possibly came
up in response to a question regarding a scenario where the same Fb
client file structure (e.g., a Fb3 server root dir) was being used to
access local and remote databases of differing ODS and one didn't want
to limit those options by configuring those client-side parameters globally.

It is well possible that I've misunderstood that and configuring each
option in databases.conf is *not* the solution;  meaning that the
client side files, including a custom firebird.conf, should be located
in the client's application root dir, as ever and it is only for local
databases that databases.conf is available to override settings in
firebird.conf.

> And if it should, but doesn't seem read it on Windows, is that a
> bug?

I guess that, if it is a bug, we'd know about it by now.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 3.0 client does not work with remote Firebird 2.5 database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Michael Fox wrote:

> It’s the default package of firebird-dev 3.0.1.32609.ds4-14 on
> Stretch which doesn’t work.

Ancient.  Current sub-release is 3.0.4. Given that there were some
humps and bumps in the early releases, you might well be encountering
bugs that have since been fixed.

> The previous version of firebird-dev
> 2.5.3.26778.ds4-5+deb8u1 on Jessie works fine with the same
> credentials (note: I’m using PHP’s Interbase extension to connect
> but I don’t think it’s the cause of the issue).
>
Maybe, maybe not.  There are some bugs around with that driver.  But
if it is working OK for you with 2.5.3 (also ancient!!) it might not
be a factor in this issue.

> The config settings are the default ones. For the two settings you mentioned:

> AuthClient = Srp, Win_Sspi, Legacy_Auth
> WireCrypt = Enabled (for client) / Required (for server)

Here's the thing.  The defaults shown here for these two parameters
represent the default configs for a Fb3 client and a Fb3 server. Since
they are Fb 3 settings, the 2.5 server doesn't know about them.
Specifically, they are telling the Fb3 client to encrypt the wire
transmissions but of course Fb 2.5 doesn't support wire encryption.
Likewise, Fb 2.5 doesn't support SRP authentication;  nor does the
Linux cclient support Win_Sspi at all.

Firebird 3 allows configuration at both client and server for some
parameters, including these two.  Changes to these parameters must be
done at the client side for your situation. If you plan to use the
same Firebird setup on the Linus server as a client for databases on
both 2.5 and 3.0 servers, you'll need to configure records there in
databases.conf, specific to each.  (You could set them globally in
firebird.conf but that would, of course, restrict the options for
accessing diverse remote databases.)

> I’ve played around with different permutations without success.

So - you have done settings in databases.conf?

> Maybe worth noting that the credentials for the remote server are in the 
> format:

> XX.XXX.X.XXX/45731:d:/Database/P2Gold data.ib

It no longer makes good sense to avoid using aliases.

On the 2.5 server side, have the third party make an alias in
aliases.conf, e.g.,

p2gold = "d:/Database/P2Gold data.ib"

(You may or may not need the double quotes, try it both ways.)

At the Fb3 client side, make an entry in databases.conf for the
same alias.  That's just for syntactic completeness: the client
doesn't do anything with the path, though of course this would be an
invalid entry if you tried to use it locally!  You can study the notes
in databases.conf for the syntax of an entry.  For this alias you will
need to set AuthClient = Legacy_Auth and WireCrpyt = Disabled.

Once saved, the config should work.  The same isn't true for reconfigs
of server-side parameters, though - that requires a server restart.
Likewise, on the Fb 2.5 side, the alias should be available once
aliases.conf is saved, whereas anything reconfigured in firebird.conf
will necessitate a server restart before it is visible.

You can find more details in this article:
https://www.ibphoenix.com/files/Authentication_FB3.pdf

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Problems with creating new users (no permission for INSERT access to TABLE PLG$VIEW_USERS)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Bryan,

Maybe something in here will provide that ah-haa moment. ;-)

C:\Programs64\Firebird_3_0_embedded>isql employee -user sysdba
Database: employee, User: SYSDBA
SQL> show version;
ISQL Version: WI-V3.0.4.33054 Firebird 3.0
Server version:
Firebird/Windows/AMD/Intel/x64 (access method), version "WI-V3.0.4.33054 
Firebird 3.0"
on disk structure version 12.0
SQL> create user test password 'testuser' grant admin role;
SQL> -- This statement looks like DDL, so isql should auto-commit it, right?
CON> -- But, under the surface, it is DML (on the security database, natch!)
CON> -- which isql does not autocommit, so
CON> -- we need to commit that DML explicitly before it can be available
CON> -- to grant it the RDB$ADMIN role in the current database;
SQL> --;
SQL> commit;
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;
SEC$USER_NAME   SEC$ACTIVE SEC$ADMIN SEC$PLUGIN 
=== == = 
===
SYSDBA   Srp
DEVELOPER   Srp
TEST Srp
 

SQL> -- Now the user exists, we grant the role to it; 
SQL> grant rdb$admin to test;
SQL> commit;
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'test'; 
SQL> -- no result there:
CON> -- RDB$USER in RDB$USER_PRIVILEGES is stored here as string.  Engine 
CON> -- always stores it in upper case and of course it is case-sensitive.; 
SQL> --
SQL> select rdb$user, rdb$privilege, rdb$relation_name from rdb$user_privileges
CON> where rdb$user = 'TEST';

RDB$USERRDB$PRIVILEGE RDB$RELATION_NAME
=== = ===
TESTM RDB$ADMIN

SQL>quit;

C:\Programs64\Firebird_3_0_embedded>isql employee -user test2 -role rdb$admin
Database: employee, User: TEST, Role: RDB$ADMIN

SQL> create user test2 password 'test2user';
SQL> select sec$user_name, sec$active, sec$admin, sec$plugin from sec$users;

SEC$USER_NAME   SEC$ACTIVE SEC$ADMIN SEC$PLUGIN
=== == = 
===
SYSDBA   Srp
DEVELOPER   Srp
TEST Srp
TEST2   Srp

Take careful note:  the GRANT ADMIN ROLE gives user management privileges to 
the user in ANY database on the server, whereas the RDB$ADMIN role applies 
only in the database where it was granted.  In other words, the user must

1) be committed first into the security database with the 
   GRANT ADMIN ROLE (and don't forget, you have to commit 
   it if you add it later with ALTER USER as well) 
2) be granted RDB$ADMIN in all the databases where you want it 
   to have user management privileges
3) be logged in to a database with the RDB$ADMIN role

In Fb 3 and Fb 4-alpha, you must have *both* and perform your login with 
the role.  In Fb 4 beta 1, a user with the GRANT ADMIN ROLE (SEC$ADMIN TRUE) 
does not need to log in with the RDB$ADMIN role.  I don't know whether this 
is going to be backported to Fb 3.0.5.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Informing the devs about optimizer issues / alternative plans

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Rudi Feijó wrote:

> We have been trying to optimize one of our largest databases (firebird
> 3.0.4) , and in doing so, we managed to greatly improve the execution of one
> specific query by manually changing the PLAN.

>  

> I am wondering if the firebird team is interested in receiving detailed
> feedback of such cases for dev purposes, and if that’s the case, what is the
> procedure I should follow to share the database and the query.

1. Create an account at http://tracker.firebirdsql.org and create an
"improvement" ticket in the CORE category. Provide as much information
there as you can.

and

2. Subscribe to firebird-devel list - you can do it from this page:
https://www.firebirdsql.org/en/mailing-lists/ (scroll down) for
discussion of your ideas.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Converting dates and the ISO-8601 format

[Helen Borrie hele...@iinet.net.au [firebird-support]]

i...@synapsesoftware.co.uk wrote:

  >I use Firebird primarily with Delphi and have had no problems with
  >using and displaying dates (suitable for my country - UK.)

  First thing to understand is that Firebird does not store dates
  as strings.  It can recognise string inputs for dates according to a
  number of conventions but, at the back, it converts them to one or a
  pair of numbers calculated from starting points on timescales.

  What gets returned to the client when dates are read depends, as you
  rightly guessed, on what the operating system is set up to display
  and what the client interface offers for massaging date/time data
  into strings.  Most (if not all) Delphi interfaces use the Windows
  regional settings.

  >However, it seems to be a big problem with Firebird and dates when
  >working with PhP with  firebird driver. I have a number of fields that
  >hold information in a field defined to be of type  "date" in Firebird.
  >When I use PhP to query my database I get the fields formatted in the
  >ISO-8601 format (year/month/day.. etc).

Other than a UDF, there is nothing you can do in your SQL to influence
what string the client delivers to your interface.  You should ask on the
firebird-php list for advice about this.  PHP has so many little
functions to massage data that I'd be surprised if you didn't have
at least one convert option!

  >I really dont know how to format the results so that they appear
  >"normal" for my region - ie the dd/mm/ format. I also am not sure
  >where the problem originates. It seems to depend on the client
  >software I am using to view the data (which seems to imply its the
  >client software that is responsible for rendering the data.)

True.  Although there is the (not highly recommended) option of
using an external function ("UDF") to convert your dates to strings.
There are various UDF libraries around:  look at
   https://www.ibphoenix.com/download/tools/udf
for links to a selection of such libraries.  You might find one that
does what you want.  Of course, strings are not going to be a lot of
use to you if your applications want to do calculations on dates.

  >Here is a summary of softwa re I have tried and the results

  >Programresult
  >FlameRobin   Formatted correctly (except using the "." notation - so dd.mm.yy
Written in C++ using an interface called IBPP, which probably has a
function to massage dates into a format that is compatible with the
regional context of the host (or maybe the client).

  >DBeaverFormatted incorrect for my region ( ISO-8601 format)
Depends on the driver you have behind this, I guess, and probably also
the regional settings on client or host or both. Firebird has lists
for both Java and ODBC/JDBC so it's worth asking on the appropriate
list.

  >PhP  Formatted incorrect for my region ( ISO-8601 format)
As above.
  >Delphi  (VCL) Formatted correctly dd/mm/
Follows the regional setting.  This is easy to test.

  >My question can be  sumerised as - How to format the results so they
  >are correct for a specific region (in this case the UK )? Should the
  >client software (PhP/DBeaver etc) have a setting that allows you to
  >format the results correctly ? Becasue I cant find any global setting
  >in the various clients that will allow this.

  >If we do have to explicitly cast / format the data - this adds a lot
  >of work on the server / software and surely must have performance issues ?

Cast - no.  But if you go the UDF route and export your dates as
strings then data over the wire will be fatter but work at the client
will be thinner.  That's true of anything you convert, of course.
Can't avoid it.

  >My goal is to format the result from a PhP request so the dates are
  >displayed correclty. I understand that Firebird does not have
  >the"convert" function - so I have tried using the "CAST t;
  >function - but this seems to do very little
  >
  >for example..
  >cast(TRANSDATE AS DATE) as TRANS_CREATED
  >
  >This seems to do nothing. I have tried looking through the PhP.ini
  >file for some way to influence how the data is formatted and cannot
  >find anything. I have even tried using ..
  >
  >ini_set('date.timezone', 'Europe/London');
  >
  >But this also did nothing.

I hope you understand a bit more now about what's going on in these
interfaces. Good luck.

Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com

[firebird-support] Off-topic discussions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

All,

Please be reminded that the firebird-support list is for topics that
are about your questions or problems concerning your work with
released versions of Firebird.

The recent thread "Firebird vs. PostgreSQL" should have been launched
in firebird-general.  It's too late to divert it now, but please be
mindful of this for future discussions.

If it is not directly a support topic, then please take it to
firebird-general.

If it is a technical question about a feature in a future release,
including alphas and betas, take it to firebird-devel.

Helen (Moderator)


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Possible bug in 2.5.1.26351

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Mark,

Tuesday, October 30, 2018, 8:20:51 AM, you wrote:

> On 29-10-2018 02:06, Helen Borrie hele...@iinet.net.au 
> [firebird-support] wrote:
>> Character set NONE is a bare-bones ASCII set and does not provide
>> intrinsic support for any characters beyond the 128 characters that
>> provide the US-Ascii upper and lower case, numerals and the basic
>> diacritic symbols.  You can store anything in charset NONE but nothing
>> in the least useful can be done with those incompatible characters.

> NONE only means store the bytes accepted and return them as is, and if a
> connection character set is specified, try to convert the stored bytes
> to that character set.

That much is true - but only if the first 128 characters of the
incoming set are compatible with those of US-Ascii.
> It does not mean or assume ASCII at all.

"At all" is too sweeping.  The recognised alpha characters in NONE are
the unaccented a-z and A-Z of us-Ascii.  Hence, if you are feeding in
strings that involve only those characters, you can UPPER and LOWER
them, and concatenate them.

> It just
> happens that most (all?) Firebird supported character sets (including 
> UTF-8) use ASCII as the base for the first 128 characters.

Considering all supported character sets, not "most", not "all".  It's
only true of character sets that can recognise those 7-bit characters
and can read the bytes in left-to-right order.
 So -- most (if not all) Western character sets, few if any Eastern
 ones that are stored in NONE.

 Helen




---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird 2.5: first insert into table takes ages to complete

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Dominik Psenner wrote:

> we are observing an interesting issue and hope for interesting
> insights what could cause the symptoms. First of all a few numbers
> about the database and the firebird instance in question:


> · Database contains several hundred tables

> · Few tables contain up to 10.000.000 records

> · Database size on disk is roughly 3GB

> · Firebird is 2.5 with SuperServer flavour

Which sub-release?

> · Page size is 16384

> · Page buffers is configured to 2048

> · Forced writes is enabled

> We noticed that the very first insert after a large migration that
> changed the table layout (new columns and such) takes a large amount
> of time to complete. About 50 seconds on a laptop machine with an
> i7, 8gb memory and an SSD. This first insert reads more than 6
> pages from disk to cache and writes almost the same amount of pages
> from cache to disk. Following insert commands, even after a restart
> or rollback of the previous insert, read only about 70 pages and
> write only 3 to 5 pages from cache to disk and take 5ms to 40ms to
> complete. Following inserts behave, regardless of whether the first
> insert command transaction was rolled back or the firebird process
> was restarted. Doing a backup of the database and restoring the
> database changes the observed behaviour of the first insert
> statement to behave like the previously mentioned subsequent insert 
> statements.

> Observations:


> · This issue does not appear related to disk page caching
> or firebird page caches because it survives computer reboots and firebird 
> server restarts.

> · This issue appears to be related to the persistent state
> of the firebird database because after a backup to gbk and restore to fdb the 
> symptom disappears.

Yes.  The symptoms suggest you have a large number of record versions
that are waiting for garbage collection.  New records cannot be
written to existing pages until after that garbage has been cleared.

> Is it known and considered "normal" that some insert statements may
> cause the firebird server to read and write several thousand pages
> and taking ages (50 seconds vs 40ms)? Are there any known causes for this 
> symptom?

See above. After a backup and restore, there is no garbage.  However,
the first operation on a dirty table will cause a garbage collection -
hence the long time taken for this first insert.

Run gstat -h on the database when you start to notice these delays.
Check the values of the various ' ... Transaction' reports and copy
them back here.

As to the cause, it is totally due to inadequate management of
transactions.  This style of poor management commonly comes from
applications that keep read-write transaction open for long periods
and never committing them.  Are your apps written in Delphi?

HB

p.s. Would you please strip out your company's footer details when you
post to the lists.  The warnings have absolutely no point in a
mailserve list and they take up a lot of space on subscribers' disks.



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Custom error message from procedure/trigger code in Firebird 3.x dialect 3

[Helen Borrie hele...@iinet.net.au [firebird-support]]

jonatan.laurit...@yahoo.dk wrote:

> Sometimes I need to write extended error message from the SQL
> procedure and trigger code. So far I have used the following procedure:

> create or alter procedure REPORT_ERROR (MSG varchar(400))as
> BEGIN

>   update rdb$exceptions set 

> rdb$message=:MSG  where rdb$exception_name='MY_ERROR'; 
>   exception ERRMSG_;
> END

This was always a hack...

> But in Firebird 3.x UTF8/dialect 3 I am receiving the error message
> while using this function (even as SYSDBA user):

> UPDATE operation is not allowed for system table RDB$EXCEPTIONS.

And here's where the hack comes back to bite you.  System tables from
v.3.0 onward are read-only, meaning, amongst other things, you can no
longer circumvent the restriction on performing DDL operations in PSQL
by hitting the system tables directly.

> So - how can I report custom errors from the SQL procedures and
> triggers, it would be nice the generate some information in
> procedures and triggers and let the Delphi error-handling code to
> process this information for the user presentation.

Since around v.2.0, you have been able to replace the default message
associated with an exception with a run-time message of up to approx.
1000 characters (ascii) or around 250 (UTF-8).  See
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql-handleexceptions.html#fblangref25-psql-exception



---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Very slow response after massive delete on table. (FB3 on Win 64)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

fabia...@itbizolutions.com.au wrote:

> I've noticed the following strange behavior on Firebird 3 latest
> release, 64 bits, on Windows 2012, 24 processor server, 200 GB ram, magnetic 
> HDDs (no SSDs):

> 1) After executing and commiting a massive delete on a table, any
> select or insert takes time to execute, as if FB was not reclaiming
> the deleted "space" on the table, and had to read each deleted
> record to filter and find the response.

Well, that's not quite the process but it's certainly the result.
Nothing happens, of course, until the transaction commits and the
engine finishes writing the delete stubs for those records.
"Reclaiming the deleted space" doesn't happen at all until a garbage
collection process has been through and flagged them all.  The timing
of that depends on how GCPolicy is configured: background or
cooperative. For background, which applies only to Servermode Super, a
GC thread starts up periodically to do that flagging. For cooperative,
the first transaction that hits that table after the bulk delete
transaction commits gets the load.

If the Servermode is Super then by default GCPolicy is combined, i.e.,
the engine will try both ways - useful with short transactions, not so
much with bulk updates and deletes.  For Classic and SC modes, only
cooperative is possible, i.e., background and combined are ignored.

The quickest way to clear the decks after a bulk delete is to do a
SELECT COUNT(*) FROM , which will touch every record in
the table and flag the delete stubs for GC.  If the bulk delete is
deleting ALL of the rows, then dropping and recreating the table is
faster.

> 2) While a big transaction is occurring (inserting into a table the
> results of a big select via an SP), another DB on the same server
> becomes unresponsive for about a minute. I thought each database had
> it's own "deamon / process / thread" assigned and the locks were
> exclusive to each DB, however it appears there is some interaction
> between the two DB performance other than just the shared 
> OS/HDDs/Memory/Processors.

Again, the locking behaviour depends on the server mode.  Super is one
single engine process with connections running in threads of the main
process.  Lock files are not shared across databases, though.  Classic
is your one-process-per-connection mode, with each connection having
its own lock file.  SC is a listener process that starts a thread for
each connection, each having its own lock file.

The reason for the slowdown in the second database probably has to do
with the availability of resources on the machine while your bulk
delete in the other database is either still under way or is being
cleaned up by a GC process.

> And   a question for the experts would it be possible to install
> multiple instances of FB 3 on the same OS instance, to ensure a
> better level of independence between the DBs response time? This was
> possible on FB2.5

The mechanism is the same in both versions.  Only Superserver (Fb3
Servermode Super) has shared resources.  If you were running two
instances of SS in 2.5 and you observed some benefit from doing so,
there's no reason, architecturally, for SS on 3.0 to behave
differently.  Still talking about SS, if you have plenty of RAM, do
you have pages caches in both databases that are big enough to keep
things flowing?  Or too big to be accommodated in RAM while you have
something big going on in one or both databases?

> however I believe on FB 3 it did not make sense
> because FB3 assigns a processor per "request / connection",
> perhaps I got that wrong?

Both v.2.5 and v.3.0 will assign a connection to an available CPU,
if CpuAffinityMask (in SS) is set to zero. SC and Classic don't care
about CPUAffinityMask.
Neither version assigns CPU at request level (i.e., requests within a
connection).

There's probably more to be said - that's just a few things I can
think of now.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird - GRANT edit/recreate access to specific users and block some users from editing/recreating the procedure

[Helen Borrie hele...@iinet.net.au [firebird-support]]

antoedinchar...@gmail.com wrote:

> Have planned the upgrade of FB, and the works are going on.
>
Firebird 2.5.2 to Fb 2.5.8 is not an upgrade you have to plan.  It is
a point release, i.e., the same software with patches.  You need to do
this. Just keep safe copies of security2.fdb, firebird.conf and
aliases.conf, so you can copy them back over the new ones.

Regarding your original question, see
https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-security-auth.html#fblangref25-security-rdbadmin

Grant the RDB$ADMIN role to the user(s) you want to have SYSDBA rights
in the database.  When that user logs in with that role s/he can do
what you asked for.  If s/he logs in without that role, s/he will have
just the rights that have been otherwise granted.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Firebird - GRANT edit/recreate access to specific users and block some users from editing/recreating the procedure

[Helen Borrie hele...@iinet.net.au [firebird-support]]

antoedinchar...@gmail.com :

Not enough information.  What version of Firebird are you using?

> I am trying to GRANT edit/recreate procedures for manager level
> users and blocking the same from normal users.

Explain exactly what you mean by "manager level users".

> I was able to do the same in the case of Tables, but for triggers
> and procedures i am not able to limit the modification rights.

Procedures can be modified only by the owner (the user that created the
procedure) and the SYSDBA or a user with elevated privileges.

Likewise triggers;  although the owner in the case of triggers is the
user that created the table (for table-level triggers) or the database
owner (for database-level triggers).

> please help me on this.

You can help others to help you by providing sufficient information
about your environment.  There may be more you can do to set up what
you want - it depends on the Firebird version.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Antonio,

> I don't understand as you have been able to believe that I have treated
> a database as was a spreadsheet!!! I know what is a table, a database 
> (database as "set of tables", database as "file of type database"), a 
> relation, a key, an inedex, a... I've studied on your book.

> When I speak "to draw a table" for a program, I intend to set various 
> "tables" with the appropriate fields (name and type) and the 
> relationships with the fields of other "tables": so the database is the
> whole organized of these "tables" and relations. Right?

So far, so good.

> if it is not correct, I go to be a carpenter...
> else
> - a client ask me to write a program for a  single scope, the archives
> (the "tables" in the database!) must be able to contain the data related
> to that type of activity and I draw (create, write) a dabatase with the
> correct tables;

Yes, one client (Company A) one database

> - different clients ask me different types of programs: FOR ME, a 
> database must be created (drawn) for each type of program;

For everyone else, a database is created for each client organisation
(one database for Company A, one database for Company B, and so on.

> - two or more programs can have some identical "table" (e.g. 
> municipalities);
> - (1) I can create apart a unique database of "common tables" for the 
> "tables" used by all the programs;

The database for Company A should have all the tables used by the
users in Company A, including the low-volatility ones like your
Municipalities table.  If Comapny B needs Municipalities, it should
have its own copy.  You can create a DML script for this
low-volatility table and run it over each database;  or use a
replication tool if it is called for.

> (2) the same "table" can be repeated
> in all the databases;

That is what I referred to as "spreadsheet".  Those old-style desktop
fielsystems predated spreadsheets and lent themselves to that model of
application, due to explicit table locking.  That does not make it a
sensible or correct approach for a transactional RDBMS such as
Firebird.

> - it could be created an only database for all the programs; for every
> new program, new "tables" can be added in this unique database, while 
> the "table" existing can be used by the new program;

No, all tables should be available to all progrms and to one another.
If you have the same data in more than one table, you have redundancy,
the big enemy in data management. Related to this, if you are using
actual data as keys, you have intrinsic redundancy.

If you want to prevent certain USERs or ROLEs from accessing certain
tables, you use SQL privileges.

> - if you write dozens of programs (very small, normally, very great), 
> the only database becomes not manageable, *but it doesn't behave problems*.
> "not manageable" FOR ME as administration of the variations to the 
> single fields: Firebird is able of to manage millions of tables and 
> relationships, I can't, is its purpose!!

Firebird is designed to ensure the ACID rules (Atomicity, Consistency,
Isolation, Durability). it won't prevent you from breaking those rules
but it won't heal your wounded data for you, either.

> You pursue a purpose of general relations, that is the purpose of DBRMS;
> I pursue a purpose of management USING Firebird and its capability to 
> relate: it is not the same thing! FOR ME, naturally!!

Actually, every database is part of a management system of one kind or
another, so you are not alone in this world.  You can design a system
to store data according to rules and structures that make it safe and
smart: THAT is the purpose of a RDBMS.  Apparently, you want to follow
the spreadsheet model, in which a single set of data is exclusive to a
single application.  So - you maintain multiple instances of the same
data by hand and say your prayers.

>  From this my problem is born, but I can change my formulations.
> I need to think.

If you are unfamiliar with the ACID rules and normalization, look them up.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: ODP: [firebird-support] Using Union and Join (of two tables residing in different databases)in a Query

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Antonio,

> I'm agree with you: all tables in a unique firebird file is the best choice.

> My problem is that I've numerous programs that utilize one firebird file
> each.

That problem is easy to solve.  Place all of the tables in one
database and use an alias for that database in every application.

> There are tables that are drawn for a specific program, but there are 
> some tables that are "common" to all programs (example: 
> "municipalities").  An user can utilize one program, two programs, four
> programs, and so on, on the same computer and each program hav its 
> database.fdb: if I must vary a value of one "common tables", I must send
> an update for every program and to each user; but if the "common tables"
> are in a unique, separate database, I can update one file only.

This is not a database design.  You are treating "database" and
"spreadsheet" as though they were the same animal.  Very definitely,
they are not the same.

Think "relational", because Firebird is a relational database system.
Tables (a.k.a. relations) can be *related* to one another by way of
foreign keys.  Data from tables containing compatible fields (columns)
can be connected during run-time queries by JOINs or UNIONs.

> There are also "static" tables containing storical movements, and is not
> necessary periodically to back-up them.

At the same time, it does no harm for them to be included in the
backup of your current data.  It makes sense for them to be in the
same database as the active tables if you need to refer to them from
your applications.

> These are the reasons that push me to look for a solution, I hope to 
> have been clear.

The thing that seems clear to me is your confusing "tables" with
"databases".  The whole point of using a relational database is to
have all of the interrelated data available to each individual client
connection.

> For release 2.5 and not 3.o, I have been studyng Firebird for some 
> months, and I've not experience: I now plan the job to develop, later I
> will verify the new releases.

The question of whether you use 2.5 or 3.0 is not relevant to your
problem.  Spend some more time understanding how a relational database
works - and forget your preconceptions from previous work you have
done using spreadsheets or fiel-based data storage systems.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Sequential auto incremental numbering

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Lester wrote:

> On 03/09/18 07:29, 'Christian Giesen' ch...@xt.co.za [firebird-support]
> wrote:
>>    NextInvNo = (SELECT MAX(INVOICE_NO) FROM INVOICE) + 1;

> Christian ... there was many years ago a very nice article on this very
> problem.

I think this is the article Lester was referring to:

http://www.ibobjects.com/docs/ti_AuditableSeries.ZIP

I wrote it.  It was a long time ago but it still applies. The thing
about generators is not that you can't rely on uniqueness (you
absolutely can!) but that you can't go back and *reuse* a number if your
transaction is rolled back for some reason.  Back then, accountants
used to insist that accounting documents have unique numbers *in an
unbroken sequence*.  Some countries still require this, so this
article will help you if you are stuck with this limitation.

But definitely, the moment you start meddling with the values of
generators, you are making a bed of nails.  Incidentally, there *are*
ways to turn back generators so, theoretically, you could "re-use" a
missing value.  In practice, you must not try to.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Unable to connect to the employee database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

karen_den...@ymail.com wrote:

> Following the Quick Start Guide's instructions on pg. 26, I typed the 
> following into isql:

>
SQL>> connect xnet://employee user sysdba password mynewpassword;


> And it responded, thus:


> Statement failed, SQLSTATE = 08006
> Unable to complete network request to host "xnet".
> -Failed to establish a connection.

The QSG may need some correction if it is offering that exact string
as an example.  Missing here is a hostname, viz.

INET://machost:employee user sysdba password mynewpassword;

Depending on your network configuration, or if you don't know your
Mac's network name, you could do

INET://localhost:employee user sysdba password mynewpassword;

or, more simply:

localhost:employee user sysdba password mynewpassword;

> What should I be doing differently?

For an embedded ("hostless") connection using the syntax you chose,
you need an extra forward slash in the string:

connect inet:///employee user sysdba password mynewpassword;

But you can do the same thing without that extra layer and you won't
need the password:

connect employee user sysdba;

> I am on a Mac, if that makes a difference.

It doesn't, provided you have taken care of the filesystem
permissions.


Quoting from another of your posts:

> My reason for attempting to use Firebird is that LO is transitioning
> to an embedded Firebird database, which is still, as I understand,
> fairly buggy. A knowledgable person on their forum suggested I try
> the Firebird 3 Server.

OK, the "buggy" thing would be the Firebird driver and interface that
is under development by some LO guys and a Firebird guy, Marius Popa.
Firebird Embedded itself is simply one of several "server modes"
available for deploying Firebird.  It's not something special that has
been churned out specifically for Libre Office.

Perhaps I can help you to position yourself in some kind of developer
spectrum by explaining a little about the structure of a database
application.

At the top level is the client application, typically written in a
computer language such as C++, ObjectPascal, C#, Java.

At the next level is a language-specific driver.  This layer acts as a
"translator" between the language of the client application and the
application programming interface (API).  This layer lives on the
client machine.  On a Mac, its name is fbclient.dylib.  You will find
a copy of it in your /bin directory.  For some languages, such as Java
or the .NET languages, this layer is not loaded dynamically but it
coded natively in the driver layer.

Right at the back-end - on the server that hosts Firebird - is the
Firebird Server, which can be configured to accept either network
connections or embedded connections or both. Embedded connections are
possible only from the same machine where the Firebird server and the
databases are located.

LO Base is a client application.  The transition project is to use the
Firebird server in embedded mode:  I doubt there is a plan to use LO
Base as a remote client - but I really do not know!

So, essentially, the LO model is a local client application intended
to give you an interface through which you can create and work with
Firebird databases in embedded mode.  I don't know anything about the
design details but it seems you are expecting that it will give you
some kind of interface that avoids your having to know anything about
databases, nor how to write database applications, in order to create
and maintain database objects. If so, it seems to me that by going the
route you are going, you are showing great courage!

To get "with it" and stick with it, it seems you might have some work
to do to get down and dirty and get your head around the file systems,
user/group permissions, network configurations and text editors, along
with the bash shell and some system tools at the command-line level.

Helen






---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] How does a Mac OS user get started?

[Helen Borrie hele...@iinet.net.au [firebird-support]]

 kam3...@gmail.com wrote:

> I downloaded Firebird 3.0.3 and installed it on my Mac. In Library
> > Frameworks, I do see Firebird.framework and it has many folders inside.

That's the Firebird root on MacOSX.

> I read the Quick Start Guide, but all the instructions pertain to
> Windows and Unix users. Is there any documentation for Mac users?

Regarding...what?  The instructions for Firebird are the same on both
Windows and POSIX. Linux and MacOSX are both POSIX. It's not a
different application, pertaining to Mac in particular, though of
course the various kits are compiled for the particular platforms.
Just follow the Quick Start Guide (in the /doc folder). You will also
find it useful to have the release notes on hand as there are particular
instructions for configuration that might not be fully covered in the
QSG.

BTW, we don't provide a how-to for working with a Mac framework...or a
Linux installation...or any particular release of Windows...or any
particular platform.  It's assumed you know how to work with the
platform-specific tools and text editors. Anything in Firebird's
workings that is specific to a platform will be covered somewhere - if
not in the QSG then in the release notes. Apart from the obvious
differences like file and network systems and their specific syntaxes,
operational differences are rare.

The tool you'll want for setting up your user accounts, including the
initial password for the sysadmin (SYSDBA user) is isql, a
command-line app that you'll find in the /bin directory.  You can use
that to connect to the employee database (which is aliased in
databases.conf), as you need to be connected to a database to work on
user accounts. Documentation for isql can be found in the
documentation library of the Firebird web site.  You might want to
pick up the Mac version of Flamerobin at some point, if you prefer a
graphical interface.

Hope this helps.  Use this list if you bump into something that
doesn't seem to work properly for you.

Helen










>   
>
>  
> 



-- 
Kind regards,
 Helen Borrie


---
This email has been checked for viruses by AVG.
https://www.avg.com

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Doug wrote:

> In any event, the admins have made it clear they are not looking to
> move so we can drop it for the moment.

Actually, I'm the admin and I haven't said a lot about the current
glitches so far. ;-)  And you're an occasional moderator of this list
too, so it's fair you should have your say.

> I just do not think Yahoo has
> been a reliable or easy to use platform over the years.

I don't agree that Yahoo hasn't been reliable.  Sure, over the years,
they have made changes to the admin interface without announcing them,
but overall it has been reliable. I mind all the lists, including the
newer Google ones, and they all have their glitches.

> Adter being
> hosted here for so long it seems prudent to take a serious look at
> the alternatives, especially since these glitches keep coming back
> to Yahoo. Lots has changed over the past few years.

These glitches are infrequent, although I can't disagree that they
have happened.  This current one of duplicating messages and of having
replies appear before the orginal posts is annoying but, like Mark, I
think we'd have to be seriously put out to consider shifting a list
with an 18-year archive.

The likelihood of leaping out of the frying pan into the fire is
fairly strong. The firebird-net-provider list was moved from
Sourceforge to Google Groups six months ago and the Subscribe entry on
the website was updated at the time.  It's a low-traffic list compared
to this one but I'm still having to moderate subscribe requests and
first posts.

Let's suppose that River's post yesterday put a fairly accurate finger
on the current problems.  Let's weather it for a week or two and see
how things shake out.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Multiple copies of the same post

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tomasz wrote:

> I know it's off-topic, but does anyone else experience receiving
> multiple copies of some posts on this list, or is it just me?
> Sometimes I have 2, 3 or 4 identical copies in my inbox (like Hugo's 
> from yesterday - 2 copies). They seem identical except for their X-UIDL,
> but AFAIK UIDLs are being assigned by the receving e-mail server, so 
> that doesn't prove anything.
> Sorry to bother you with this, maybe someone just clicks "send" with a
> shaky hand, but maybe my mailbox is messed up. No duplicates from other
> lists, though.

I've been seeing the same thing in this and other Yahoo lists.
Also, as list admin, some other behaviour, such as getting a 404 error
when I click through the supplied link to a post that requires
moderation.  The latter problem went on for a week but it seems to
have been resolved now.  I wonder whether Mark saw the same thing with
the Firebird-Java list.

Yahoo recently underwent an ownership change: it belongs to Verisign
now.  I concluded that our list problems were probably due to new
hands on old software as the Verisign people gradually move the Groups
archives to their own servers.  There's also the activities of
U.S.-based list providers having to become compliant with the EU
privacy laws by the July 30 deadline.  It's probably a mix of
circumstances.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] GRANT Privileges

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Lee,

> I’m struggling to grant any privileges on a .fdb file database.

> I have created a separate user as the SYSDBA reported “Your login
> SYSDBA is same as one of the SQL role name. Ask your database
> administrator to set up a valid Firebird Login”.

The SYSDBA user exists already.  The password is 'masterkey' but you
should change this immediately.

> When I use ISQL to run a select statement on the database in question I get 
> the response:

> “Statement failed, SQLSTATE = 28000
> No permission for read/select access to TABLE ZIEKTE”

> Then I try to GRANT SELECT on TABLE ZIEKTE to USER DW_EXTRACT” however I get 
> the response:

> “Statement failed, SQLSTATE = 28000
> Unsuccessful metadata update
> -no S privilege with grant option on table/view ZIEKTE”

You need to be logged in as SYSDBA or the database owner.  An ordinary
user can't grant permissions to self nor to any other user.

> I’ve tried numerous tables but I get the same response.
> I have tested the user on the Example database “EMPLOYEE” and have
> no problems accessing any table on that.

AFAIR, all the tables in the employee database have rights granted to
PUBLIC.  This isn't something you should do with a real-life database,
though.

> Can you possibly help me or point me in the right direction?

If you don't know the db owner's credentials then log in as SYSDBA.

If you post again, would you please exclude all the company stuff in
the sig of your posting.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

wobble...@yahoo.co.uk,

> is there a good and up to date official source of documentation on
> firebird.conf

The firebird.conf file itself, version current for the Fb version you
are using.  You won't get more up to date than that anywhere.
For Fb3, which allows per-database config for a lot of
parameters, see the release notes.  Some things are (or can be)
configured at the client side via a config file now, too.

> and other admin?

What's "other admin"?  The command-line tools are all documented but
some could do with updating, admittedly.  You can catch up with any
new switches in those apps by running them with the -? switch.

Vol. 3 of TFB2e covers them all in more detail, up to the earlier
releases of Fb 2.5, but it's not free.

HB




---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Ian wrote:

> We got the idea from The Firebird Book, and after that I can't find
> mention of it being deprecated in 2.0 release notes or later.

Not deprecated but abolished. If something is "deprecated", it is
still valid but likely to be dropped in a subsequent release.  From Fb
2 onward, Fb was made more tolerant of directory names with spaces,
hence the need to drop such embellishments.

(Not that made anything like sufficientt impact on my consciousness in
2013-4 to remember it affected TempDirectories - q.v.)

> From
> the 2004 book, page 745 (I know it's older than my daughter ;-):


> "TempDirectories


> Version 1.5 forward


> Supply a list of one or more directories, separated by semicolons
> (;), under which sort files may be stored. Each item may include an
> optional size argument. in bytes, to limit its storage.


> TempDirectories = userdata\sortfiles 500
> "

It has subsequently become a myth that has survived into TFB Second
Edition. I can fix that.

> And also in the 1.5.6 release notes
> https://firebirdsql.org/rlsnotesh/config-fb-conf.html, and then
> hanging around in something that claims to be the Firebird 2
> Administrators manual, but now I look closer it isn't obvi  ously a
> core fb site:
> http://www.janus-software.com/fbmanual/manual.php?book=admin=42

No, it's not.


> I can't find it mentioned anywhere else though, except in things
> like http://tracker.firebirdsql.org/browse/CORE-2151 where the author was 
> still using it...

The author of that ticket was the developer who wrote the parser code.
;-)  It looks as though he'd discovered unintended behaviour when the
parser landed on a space in a directory name...the older code to
truncate out anything following a space (i.e., assumed to be the
now-invalid allocation size) was gaily truncating forward from the
first space in the directory name itself. (That was an amazing piece
of searching that dug that up!)

Helen





---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 27.07.2018 21:41, Helen Borrie hele...@iinet.net.au [firebird-support] wrote:
>> I don't know where you got the idea that you could specify the size of
>> a temp directory.

Dmitry replied

>It is an Interbase feature.

Uh...ok... @_@

If you want to allocate specific spaces for your temp directories on
the hard disks, just create a couple of partitions of the sizes you
had in mind.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: tempdirectories

[Helen Borrie hele...@iinet.net.au [firebird-support]]

wobble...@yahoo.co.uk wrote:


> I've just tried 


> TempDirectories = /location1 5000 ;/location2  ; /location3


> And location1 is ignored, but 2 and 3 used!

Which tells you something. ;-)
The engine doesn't recognise the format of the first member of the
list but it's OK with the second and third because they are legal.

I don't know where you got the idea that you could specify the size of
a temp directory.  It's never been a thing and it wouldn't make
sense if it were.  The idea of being able to specify multiple
locations is that you can avoid an out-of-space crash if the engine
has somewehere else to go when it needs to put temp files to disk and
the first (or default) temp space is used up.  Another point is that,
if your user environment has a lot of users doing lots of ordered
queries and keeping them alive for long periods, you can consider
having a dedicated disk, such as a fast SSD, for TempDirectories.

The engine holds the temp file structures in RAM if it can: it only
puts them to disk if available RAM is too low.  Hence, allocating a
RAM disk for TempDirectories doesn't make sense, either.

Helen


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] characterset issue in asp.net core

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Issam Boughanmi wrote:

> hi,
> i have some issues related to character sets in an asp.net core
> application using 6.1 provider and EF .

> special characters like 'µ' or french characters for example don't show up 
> correctly .

> i have another web application using 4.10 provider and ef with the
> same database that work perfectly .
> the charset of the database is NONE .

> any help is welcome, because i am out of ideas now ;)

This is a driver issue, not a database support matter.  Please join
the Firebird-net-provider group and post your question there:

https://groups.google.com/forum/#!forum/firebird-net-provider

Helen (Moderator)


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] firebird 3.0 Not binding on ipv4 on windows xp machine

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Taylor Stewart wrote:

> Installed firebird 3.0.3.32900 on windows xp with ipv6 enabled. and it will 
> only listen on :: ipv6

> if i manually adjust RemoteBindAddress to 0.0.0.0 it listens on ipv4 fine.

Looks as if you have discovered a workaround for behaviour on an
obsolete operating system.  It would be safer to use the host's own
Ipv4 address or host address, though.

You could also try the alternative syntax

 INET4://winserver:d:/databases/mydatabase.fdb

to avoid your NOS even polling for an IPv6 address.

> Is this normal? 
> On windows 10 it listens on all by default with no configuration changes.

Perhaps.  I don't think our project-based QA testing extends to
testing on XP, Windows 98 or Windows 3 these days. ;-)  It might be just
what you need to do to achieve what you want on that old OS.

Make sure the clients are using the Fb3 client to ensure that
they are using a network protocol version that supports Fb3's
behaviour.

It's also worth checking whether you inadvertently configured
IPv6V6Only to true in firebird.conf.  You want that false ( = 0) to
allow clients to poll for IPv4. Don't forget that, if you
reconfigure anything in there, you need to restart the Firebird
service for it to take effect.

Helen









-- 
Kind regards,
 Helen Borrie


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] URGENT - Database performance is very slow

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Vishal Tiwari wrote:

> It's urgent...
> We are facing a database performance issue. Currently, we are using firebird 
> version 2.5.2 ODS 11.2.
> If we try to run a simple SQL i.e. Select * from  using
> FlameRobin, it is taking time and next time the time span increases.
> Same happens from all of our applications side as well. We have
> Desktop applications build using Delphi.
> Kindly refer to below snap.

It's no good trying to send screenshots or any kind of attachments to
the list. They are dropped automatically.

First, v.2.5.2 is buggy and very old.  Replace it with v.2.5.8.  You
don't need any migration for this;  just keep copies of your security
database, firebird.conf and aliases.conf so you can copy them back
into your new installation.

Then, run gstat -h on the database and copy the output into your next
post. (Rt-click on the title bar of the command window, select Edit
then Select All.  Hit enter to copy, Ctrl-V to paste into your email.)

A typical reason for Delphi applications to exhibit progressive
slowdowns is poor transaction management, specifically those written
with long-running read-write transactions that never get committed.
Garbage builds up and builds up in the database just from everyday
usage, in the form of thousands of old record versions that can't be
flagged "uninteresting" because of all those uncommitted transactions.
You observe the slowdown in FlameRobin, isql, et al., because all that
garbage is trapped in the database.

You can improve that permanently by rewriting the offending
application code. In the meantime, do regular sweeps or gbak backups
in (gfix) shut -single mode to clear out the trash. If you are already
doing that, then you probably need to do it more often.

So first things first - update Firebird and let's look at those
transaction statistics from the database header.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Re: Firebird 3: Not able to UPDATE RDB$Procedures

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> Also, apparently, this is not a blanket restriction, since I can   
> successfully run the UPDATE RDB$PROCEDURES on at least 1 other  
> converted DB (that is essentially the same as the DB I cannot runthis 
> UPDATE on).

Smells like a privileges problem.  The updater has to be SYSDBA,
rdb$owner_name or (on POSIX) root.  Could also be a regular user that
has been granted and is logged in under the RDB$ADMIN role, or one that
has been granted metadata privs on procedures through user name or
role.

HB


---
This email has been checked for viruses by AVG.
https://www.avg.com

Re: [firebird-support] Error -902 after Update Windows 10

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Mark wrote:


> The problem I have with this solution, is that when using a Firebird 
> database, you shouldn't be accessing a Firebird dstabase through a 
> share, and Firebird should disallow that by default.

And it does.  It would require RemoteFileOpenAbility to be true.  By
default it is false.  It should NOT be set true for any database that
is not in ReadOnly mode, nor, as you pointed out, for a database that
you want to keep safe.

> In other words: did you really access a Firebird database through a
> share? Or where you using the WNET (NetBEUI) protocol to connect to 
> Firebird?

> Because if that is the actual problem,



Having the database is on a share does not seem to be the problem.
Most people know not to do that. But it is a pretty common practice in
networks to place the client application software on a share and have
all the users run it from the same place.  This is where things have
come unstuck for at least some of those reporting a similar problem.

HB

Re: [firebird-support] Error -902 after Update Windows 10

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Eduard Calveras wrote:

> Since few days ago, after Windows 10 update, some applications
> writes in Delphi can not connect to network  Firebird 2.5.5 server.
> These applications can connect with BDE, InterBase, FIB, FireDac... but all 
> are using the GDS32.dll

> In other side, applications as FlameRobin, are using the same
> library GDS32.dll and connects successfully.
> Clients with previous Windows versions works fine too.

> These are the errors messages:  Interbase:

> 22/05/2018 17:48:03  DBUG:  ; ¬[Application: ]¬[Error] 
> -902 335544721 Unable to complete network request to host   
> "192.168.1.100".¬Failed to establish a connection


> BDE:

> OpenConfigDatabase DbConnection Exception. Error Opening   
> Configured Database $AUTO_DATA, Error was: Unknown   database..
> Unable to complete network request to host "192.168.1.100".
> Failed to establish a connection.


> The problem is a kind of local issue, because other Pc-clients
> works fine. But certainly is the gds32.dll that cannot connect
> with server and I not known why. 

Last week, Martijn Tonies found this blog, which helped some people
with this problem:
""
Could this be related to:
https://blog.mertech.com/windows-10-version-1803-breaks-some-shared-folder-applications

"This means that applications started from these environments can’t open a 
connection to a database server."
""

>From the forensics (sketchy as they are!) it appears that, if your
network was using Homegroup for file sharing, then SMB would have been
already disabled *before* the update.  The Fall Creators' Update has
removed Homegroup file sharing from Win 10 completely:
https://www.howtogeek.com/fyi/microsoft-just-removed-homegroups-from-windows-10/

If your network is relying on a Homegroup for sharing files, there is
nothing you can do to fix this, as it is gone forever.

Meanwhile, of course, your Win 10 clients are left without file
sharing until SMB is (re-)enabled.

Go to
  Control Panel
 Programs and Features
   Turn Windows features On or Off

and check on 'SMB 1.0/CIFS file sharing support'.

How Microsoft lights up our lives!

HB

Re: [firebird-support] FB3 alter domain colltion

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Karol Bieniaszewski wrote:

> when we create dommain we can do:
> CREATE DOMAIN XXX TYPE VARCHAR(50) COLLATE PXW_PLK;
>  
> but how to alter this domain to have still collation?
>  
> ALTER DOMAIN XXX TYPE VARCHAR(150) COLLATE PXW_PLK;
>  
>  
> Engine Code: 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 1, column 38
> COLLATE
>  
> is this possible or i should create ticket/or it already exists?

It is not clear why you included a COLLATE clause in an ALTER DOMAIN
statement.  You can't change the COLLATE property of a domain, which
is why you got the error: COLLATE is not expected in an ALTER DOMAIN
statement.  But you were not trying to change that, anyway.

An ALTER DOMAIN statement does not drop the existing domain.  It
allows valid changes to properties that can be changed.  All you
needed here was:

ALTER DOMAIN XXX TYPE VARCHAR(150)

It is allowed, because (1) VARCHAR and VARCHAR are compatible and (2)
the new size is longer than the old size.  No other properties are
affected.

HB

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> I am using the Firebird 2.5 Embedded Edition with the Firebird
> ADO.NET provider version 5.5 or 5.7.  I am not sure which, since it
> has been so long since I set up the references for my project and
> the assembly being used is not taken from the actual provider
> library that has all the files in it..


> In any event, during a test of my current application development I
> successfully inserted a record into a table using a Firebird stored
> procedure with the ADO.NET ExecuteNonQuery method, which should
> return a "records affected" count of "1".


> Instead, I am finding that the returned "records affected" count is "-1".

You posted this to the database support list.  You want to post it to
the firebird-net-provider list (google group, not sourceforge) so that
someone who knows can tell you what the expectations are in ADO for
RecordsAffected by an insert from a PSQL module.

> I have never seen this before with all the other database engines I
> have worked with over the years.  Nonetheless, I believe I have seen
> this issue raised with Firebird ADO.NET before but some time ago.


> Does anyone have any idea as to what is causing this?

Well, I don't do ADO but to me, logically, a record can't be affected
if it didn't exist before the DML operation, so only reows affectd by
update or delete operations would be counted, if any such count were
available. I think that's how it works in MSSQL.
The context variable ROW_COUNT is available in PSQL for you to do
something with *inside* the module, e.g., to pass it to an output
parameter.  It does give you a row count for each DML operation as it
completes. Of course, this means its value changes in a module that
has multiple DML statements.

I don't know of any other way to get row counts from executing a PSQL
module.  Maybe the Firebird ADO.NET provider is able to wrap such
output from an ExecuteNonQuery into this RowsAffected valuewild
guess here. Ask on the right forum.

HB

Re: [firebird-support] Doubts regarding statistics of indexes and tables...

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Javier wrote:
>
> Ok, but how do I restore the statistics?  Using "update" statements?

You do not "restore the statistics".  The statistic that gets updated
by a SET STATISTICS call is the selectivity of the index.  Low value =
high selectivity = Good;  high value = low selectivity = bad.  The
value immediately after the call reflects the state of the index at
that point.  So, at that point, the optimizer has the best chance to
prepare the most efficient plan by deciding which indexes will be the
most helpful.

After that, the actual condition of an index selected for the plan may
degrade gradually as rows are inserted and deleted. When you start to
notice a decline in the performance of queries over a table with a
high level of inserts and deletes, it is probably time to run SET
STATISTICS again.  Selectivity is not static - it changes
infinitessimally with each insertion and deletion, or dramatically
with a major bulk insert or delete.  Eventually, the statistic may
tend to become too outdated to obtain the same level of performance as
when the numbers were fresh.

The optimizer always calculates a plan based on the most recently
calculated statistics. What you can do is run the queries in isql with
SET PLANONLY while the statistics are fresh.  That will tell you the
optimizer's choices based on the current selectivity and record count.

You can save that plan in a text file.  If you decide from your test
results that you like it, you can copy it into your application
queries using the optional PLAN clause. If you supply a plan in your
select query, the optimizer will not try to create one.  That might or
might not work positively over time, of course.

On the whole, the optimizer is going to produce the most effective
plan, since it is aware of the current size if the table and may be
able to make dynamic adjustments that your static plan cannot take
into account.

Hth.
Helen

Re: [firebird-support] Re: Firebird Embedded - Will a 3.0 version be released???

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Steve Naidamast wrote:

> I am currently using Firebird Embedded edition for a military
> simulation I have been working on.

> I have seen recently that Firebird 3.0 has just been released with
> a brand new User-Guide.

Actually, Firebird 3.0 was released two years ago.  The latest
sub-release is 3.0.3.

> Along with this, I noticed that Firebird
> 4.0 is now on the books for development.

True:  it is in Alpha.

> However, in the interim will the Firebird Team be releasing a 3.0
> version of their embedded database engine to match the internals of
> their recent release for their server engine?

With the fully unified engine, Firebird 3 embedded is no longer
distributed as a separate application.  You use configuration and
protocol to set up and use embedded and/or any of the other models
from the single installation.

You really need to study the release notes closely to see how things
work now.

> Or has Firebird Embedded Edition reached a level of maturity that
> it requires no further development at this time?

Embedded always was one choice from four server models.  That has not
changed.

Helen

Re: [firebird-support] order by 1, 2 - fb 32990

[Helen Borrie hele...@iinet.net.au [firebird-support]]

acgomes2...@yahoo.com.br wrote:

> select

> A.Codigo

> 'DINHEIRO' FORMA_PAGTO

> FROM NF A

> where A.dataEmissao between '01.01.2018' and '31.01.2018'

>  and A.Modelo = '55'

This should work fine if you place a comma after A.Codigo.  It does
not make any sense to ORDER BY 2 (nor ORDER BY 1,2) as your literal
field FORMA_PAGTO has the same value in every row.  So:

 select
 A.Codigo,   /* << */
 'DINHEIRO' FORMA_PAGTO
 FROM NF A
 where A.dataEmissao between '01.01.2018' and '31.01.2018'
  and A.Modelo = '55'
 order by 1

HB

Re: [firebird-support] no permission for INSERT access to TABLE PLG$SRP_VIEW

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Todd,

Todd Brasseur wrote:

> Having Issues with 'create user' with Firebird 3.0

> It works fine on one computer where we are testing but not the other. 
> We think we did the same thing on both computers.

> Installed Firebird

> Created SYSDBA Account

> Created PRIVATEADMIN Account

> Granted Role RDB$ADMIN to PRIVATEADMIN (in security3.fdb)

> Log into our database as PRIVATEADMIN with ROLE RDB$ADMIN. Where 
> committing the Create User command, I get the error in the subject line

> no permission for INSERT access to TABLE PLG$SRP_VIEW

> The other computer adds the user without a problem.

Don't know why it does.

> What am I doing wrong?

>From the Language Reference:

Granting the RDB$ADMIN Role in the Security Database

Since nobody—not even SYSDBA— can connect to the security database,
the GRANT and REVOKE statements are of no use for this task. Instead,
the RDB$ADMIN role is granted and revoked using the SQL statements for
user management:

CREATE USER new_user
PASSWORD 'password'
GRANT ADMIN ROLE

or

ALTER USER existing_user
GRANT ADMIN ROLE

ALTER USER existing_user
REVOKE ADMIN ROLE
  

Note

GRANT ADMIN ROLE and REVOKE ADMIN ROLE are not statements in the GRANT
and REVOKE lexicon. They are three-word parameters to the statements
CREATE USER and ALTER USER.

HB

Re: [firebird-support] BLOB Not Found error

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 16.03.2018 14:47, shg_siste...@yahoo.com.ar [firebird-support] wrote:
>> Since some days ago I'm getting (it seems very occasionally and randomly) a 
>> "blob not 
>> found" error.
>> As far as I remember the problem happens during read operations: with 
>> "locate" commands or 
>> inside a "while not eof" loop...

Dmitry S. wrote

>You should use "Repeatable Read" or "Snapshot" transaction isolation mode. 
> In "Read
> Committed" you can get this error if record has been deleted and garbage 
> collected before
> you start reading the BLOB.

Also bear in mind that:

1.  If no blob has ever been stored for that field in a record, then
no blob id exists for it.  For editing, you should test for NULL at
some appropriate point in your query or your client code and handle it.

2.  When you "edit" a blob, the original blob is copied in memory to a
new blob with a temporary blob id.  The temporary blob is visible only
to the transaction in which it is created and, within that
transaction, the original blob content is invalidated as garbage by
the Post action from the client application.

3. On commit, the temporary blob is copied to a new location, with a
new blob id, and becomes visible to other transactions from that
point. It will not go to the same physical location as the original
blob, because that location remains "interesting" to the garbage
collector until it is able to be released by a future GC.

4. Be aware that, if you try to edit the same blob field more than
once in the same transaction, the initial view of the original blob is
not there any more.  AFAIR, the Firebird engine began enforcing
read-only on changed records from v.2.5 onward, so you would encounter
a different error if your app was trying to post multiple edits of
non-blob fields from the same transaction.  I can't say for sure
whether that restriction could apply to blob edits since the new blob
id is not known until the commit has occurred.  Also, I don't know
whether your IBDAC components are aware of the restriction on multiple
edits. It is probably something you should ask Devart.

Helen

Re: [firebird-support] External Tables

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, March 10 alansbr...@gmail.com wrote:

> I'm trying to create an external table with a normal user and I'm getting 
> this error:

> This operation is not defined for system tables.

> unsuccessful metadata update.

> CREATE TABLE EXT_CTB_LANCAMENTO failed.

> There is no privilege for this operation.

> How do I grant permission to a regular user to create external
> tables? During my tests only sysdba could create them.

Access to the database via external tables is a potential
vulnerability.  If a non-privileged user is allowed to create and
manipulate the metadata of database objects, the vulnerabilty
escalates.  Hence the restriction you are encountering when a
non-privileged user tries to create a table.

If you are using Firebird 3, you can grant permission to any ordinary
user (or a user with a specific role) to create tables.  With that
privilege, that user can create and manipulate metadata for *any*
table. You would have to feel very sure that the appointed user, or
users with that role, would not mess with any other tables or abuse
the privilege in any way.

You can read about metadata privileges (sometimes nicknamed "DDL
privileges") here:

https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-access-sql.html#rnfb30-security-metadataprivs

You should restrict the location of external files as heavily as
possible, by setting the allowed location in firebird.conf, via the
ExternalFileAccess parameter with the RESTRICT argument.  Don't forget
that you have to restart Firebird after changes to firebird.conf or
databases.conf.

Helen

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, March 3, 2018, 3:20:25 PM, DougC wrote:

> Very glad this got done! Thanks to everyone who made it possible.


> However, the PDF option yields a 404 error.

Fixed - sorry!

HB

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

The Documentation team is proud to announce that the Firebird 3
Developer's Guide, by Denis Simonov, is now available in English:

https://www.firebirdsql.org/en/reference-manuals/#fbdevgd30

This volume consists of chapters that walk through the development of
a simple application for several language platforms, notably Delphi,
Microsoft Entity Framework and MVC.NET (“Model-View-Controller”) for
web applications, PHP and Java with the Spring framework.

Translating this guide from Russian and preparing it for publication
in our library were made possible by a crowd funding campaign last
year.  Warm thanks to all of you who contributed.

---
Firebird Documentation Team







++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Cannot open database file in local hard disk

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>  I use Firebird 2.1 and in some computers I cannot open a  
> database file which is in the local hard disk.
>   
>  For example, let's suppose I have the database
> C:\Test\MYDATABASE.FDB and I try to open it with IB_SQL (from
> IBObjects).
>   
>  If I use C:\Test\MYDATABASE.FDB I get "unavailable database".
>  If I use SERVERNAME:C:\Test\MYDATABASE.FDB I get "unavailable
>  database".
>  If I use \\SERVERNAME\C:\Test\MYDATABASE.FDB I get "unavailable  
>database".
>   
>  If I modify [firebird.conf] aliases.conf creating the alias
> MYDATABASE=C:\Test\MYDATABASE.FDB, then using MYDATABASE I can   open the 
> database.

This is the intended behaviour if you have DatabaseAccess = None
configured in firebird.conf.

HB

Re: [firebird-support] Firebird 3 - Auto Garbage collection with Sweep interval = 0

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Arnaldo,
Friday, February 16, 2018, 2:19:19 AM, Arnaldo wrote:

> maybe i have always misunderstood the garbage collection mechanism, the
> sweep interval, etc.. and i'm realising only now, but:

> I have this database (Firebird 3.03 SuperServer x64)

> My question is : why garbage collection mechanism starts automatically
> when i browse these tables, despite the fact that i have Sweep Interval
> = 0 ? What is the point that i'm missing ?

Some more thoughts to add to Norm's comments...

Garbage collection (GC) and sweeping are separate, although
interrelated, processes.  By setting the sweep interval to 0, you
disable automatic sweeping (according to the conditions described by
Norm) but you get GC regardless of the sweep interval setting.  The
sweep interval setting has no effect on the GC that is being performed
constantly by the engine.

A GC run updates the record statistics but it does not remove the
stubs of deleted records.

There are three possible settings for the GcPolicy parameter in
firebird.conf to govern how GC is performed: background, cooperative
and combined.  The GC mechanism described by Norm is 'cooperative':
each time a user opens a table, old record versions from others' work
will be flagged if they are no longer "interesting" and previously
flagged versions will be removed.

Superserver can also do 'background':  that is, a worker thread that
wakes up periodically and runs in the background, doing the same
tasks that cooperative does. Background GC is not available in Classic
or Superclassic.

For SS, the default GcPolicy setting is 'combined':  both background and
cooperative mechanisms are used.  You can change this to 'background' or
'cooperative' if you want to test whether unexpected slowdowns are being
caused by one or other of the two GC mechanisms.

The GcPolicy setting has no effect on either the sweep interval or the
way the engine performs a sweep.

Periodic sweeping will be needed if auto sweeping is disabled.  Watch
the statistics to determine how often you might need to do it. An "ad
hoc" manual sweep might be needed after a large batch of deletes, to
get rid of those delete stubs.

A gbak backup does a sweep by default, unless you run it with the -g
switch ( [no_]g[arbage-collection] ).   If you have a regular backup
routine with the default settings, you might not need to run manual
sweeps at all. However, you will need them if you are relying only on
nBackup for your backups, as nBackup does not touch garbage.

HB

Re: [firebird-support] Re: Running with two different embedded versions of Firebird DB

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Ed Dressel wrote:

> Any suggestions?
>
Several days ago, you wrote:

> We are in the middle of letter our users upgrade their database from
> 1.56 to 3.x at their will. Users can upgrade when they want to--so
> we install 1.56 embedded db as gds32.dll and 3.x as fblcinet.dll in
> the same directory (using IBObjects, we can select which DLL is
> used). Almost all of our users use the embedded version.

> The problem is when a database exception occurs--the "firebird.msg"
> file is used. Is there a way to configure the name that the DLL uses
> for the firebird.msg file? Or should the FB databases be installed
> in separate sub directories? Any suggestions here would be appreciated.

When I read that, I groaned and let it go.  This is not a sane way to
deploy an upgrade from a 15-year-old engine to Fb3 (or any full
upgrade, come to that!).  So, to avoid trying to write a small book,
I'll make a few suggestions in the hope that you'll start to see that
this isn't a task for the Tooth Fairy.

1) It's not a question of where the databases are installed.  The ODS
10.1 and ODS 12 dbs could be in the same subdir, if their names are
different.  Use aliases in the respective .conf files to distinguish
them, so that your application will connect to the right one.  But
deploying the databases in separate subdirs won't solve the problems
you're planning to create here.

2) I suppose you are aware that the Fb3 engine can't connect to a
sub-ODS 12 database.

3) The rules for deploying embedded apps haven't changed.  You still
have to have completely separate and self-contained file structures
for each embedded app, with the Firebird components in their correct
places relative to the application executable.  These relative places
are not the same for v.1.5 and v.3.0 so you'll need to study release
notes very thoroughly and TEST your assumptions.

> Almost all of our users use the embedded version

4) Taking 3) into account, if you are deploying the software in
company with full server versions, this whole thing could become a
total mess really quickly.

5) Give up on this idea of letting the users choose.  Give them test
versions beforehand if necessary, but have them make the choice before
you deploy;  test what they have chosen to make sure it works as
expected;   then deploy just that.

HB

Re: [firebird-support] Adding a field with NOT NULL constraint

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>   Your last advice concerns me a bit. Is it also valid for  
> changing stored procedures or triggers ?

As an abiding principle - yes.  But, for SPs and triggers, the effect
varies according to a few factors.  The BLR for these modules is
cached on first use.  Changes conducted whilst the module is in cache
will not take effect until the cached copy is removed.

For Classic and Superclassic, each user has a private cache that
disappears when that user detaches from the database.  For
Superserver, the cache is shared, so the changes will not take effect
for any user until all users detach.
>
>   Should I have always to disconnect every client before   executing DDL 
> sentences ?

My advice is "Yes, always".  There might be some conditions where
changing things while users are online is plain sailing but how would
you know for certain?  Whilst the engine may allow you to effect
changes without throwing errors or corrupting on-disk structures, it
would be difficult to assure yourself that you are not going to
corrupt the in-memory structures that users already have in place.

And, when all is said and done, assumptions about the structure of
the database objects are made in the client application and any active
request refers to the status quo when that client connected.

HB








++

Visit http://www.firebirdsql.org and click the Documentation item
on the main (top) menu.  Try FAQ and other links from the left-side menu there.

Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ 

++


Yahoo Groups Links

<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

<*> Your email settings:
Individual Email | Traditional

<*> To change settings online go to:
http://groups.yahoo.com/group/firebird-support/join
(Yahoo! ID required)

<*> To change settings via email:
firebird-support-dig...@yahoogroups.com 
firebird-support-fullfeatu...@yahoogroups.com

<*> To unsubscribe from this group, send an email to:
firebird-support-unsubscr...@yahoogroups.com

<*> Your use of Yahoo Groups is subject to:
https://info.yahoo.com/legal/us/yahoo/utos/terms/

Re: [firebird-support] Adding a field with NOT NULL constraint

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Aldo,

> My questions are the following:

> 1) Is the intended effect to fill behind the scenes a newly created 
> field with its default value when there is a not null constraint ?

No.  Only inserts subsequent to the commit of the DDL for the new
field will use the default in the case where no value is provided.

Note, also, that default values apply only to inserts and only where
the field is absent from the field list for the insert.

> 2) Could this behind the scenes filling fail because of an update or 
> insert of another concurrent transaction ?

There is no "behind the scenes filling".  If you add a NOT NULL field
to an existing table, or change a nullable field to NOT NULL, then you
are responsible for filling the field yourself, immediately after the
DDL is committed.

  update mytable set newfield = 1 where newfield is null

  update mytable set existingfield = 1 where existingfield is null

As for the effect on concurrent transactions, you should not be
attempting to change the structure of a table while it is in use.

HB








> 
> Posted by: Aldo Caruso 
> 

> ++

> 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






-- 
Kind regards,
 Helen Borrie

Re: [firebird-support] Miscalculation of floating points in Firebird

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Salim Naufal wrote:

> I have noticed the following inconsistencies in Firebird 3.02:

[..]

> I then tried:
> SELECT CAST(1 AS NUMERIC(18,2)) * (1.00 - (1.00/1.11)) FROM
> RDB$DATABASE and got a much better result of 991.0

> Finally, the correct result is retrieved using:

> SELECT CAST(1 AS NUMERIC(18,2)) * (CAST(1 AS DOUBLE PRECISION)
> - (CAST(1 AS DOUBLE PRECISION)/1.11)) FROM RDB$DATABASE


> I am not familiar with the details of the SQL standard, is this the
> proper was to round and calculate numeric values in SQL? 

You really do need to familiarise yourself with the way SQL handles
numbers, especially division.  In short, with implicit casting:

- the result of division of an integer by a number of any type is
always integer, with banker's rounding

- the result of fixed numeric/fixed numeric gives FN where the scale
of the decimal part is the sum of the scales of the operands

- the result of FN/floating point or FN/FP gives FP

Hence the reason why these last two examples got closer to the result
you wanted while performing that integer division messed it up.  I
suggest you play around with your formula in isql until you get the
precision and scale that you desire in your result.  Don't try to use
a GUI tool for such testing as it will probably "tidy up" the output
for you and confuse the outcome.

Note, if you match the scale of your operands throughout, you might
get away with not having to cast anything.   Implicit casting casts
non-integer numbers as double precision.  Any operands that are
database columns are not cast implicitly - they use the type defined
for them.

Helen

Re: [firebird-support]Engine Error - Partner Index

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Nico,

Friday, December 22, 2017, 10:29:05 PM, you wrote:

> Hello,

> I have a problem with inserting a row in table
> OrderTypeDescriptions. Here you can find the sql script for the
> tables OrderTypes and OrderTypeDescriptions.


> CREATE TABLE ORDERTYPES (

>   ID  INTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,

>   STOCKORDER  BOOLEAN DEFAULT FALSE NOT NULL,

>   ROWVERSION  TIMESTAMP,

>   /* Keys */

>   CONSTRAINT PK_ORDERTYPES

> PRIMARY KEY (ID)

> );



> CREATE TABLE ORDERTYPEDESCRIPTIONS (

>   IDINTEGER GENERATED BY DEFAULT AS IDENTITY NOT NULL,

>   ORDERTYPEID   INTEGER NOT NULL,

>   ORDERTYPEDESCRIPTION  VARCHAR(100) COLLATE UNICODE_CI_AI,

>   LANGUAGEIDINTEGER NOT NULL,

>   ROWVERSIONTIMESTAMP,

>   /* Keys */

>   CONSTRAINT PK_ORDERTYPEDESCRIPTIONS

> PRIMARY KEY (ID),

>   /* Foreign keys */

>   CONSTRAINT FK_ORDERTYPEDESC_LANGUAGEID

> FOREIGN KEY (LANGUAGEID)

> REFERENCES LANGUAGES(ID) <--

> ON DELETE CASCADE,

>   CONSTRAINT FK_ORDERTYPEDESC_ORDERTYPEID

> FOREIGN KEY (ORDERTYPEID)

> REFERENCES ORDERTYPES(ID)

> ON DELETE CASCADE

> );



> CREATE INDEX IX_ORDERTYPEDESC_DESCRIPTION

>   ON ORDERTYPEDESCRIPTIONS

>   (ORDERTYPEID, LANGUAGEID);

> If I insert a row in table OrderType there is no problem.
> Id = 1
> Stockorder = true

> If I post a new row in table OrderTypeDescriptions I got a problem

Insert into OrderTypeDescriptions (OrderTypeId, OrderTypeDescription, 
LanguageId)

values (1, 'Test', 1)

> Problem
> Engine Error (code = 335544333): internal Firebird consistency
> check (partner index description not found (175), file: idx.cpp line: 1271).
> SQL error (code = -902): Unsuccessful execution caused by a system
> error that precludes successful execution of subsequent statements.

Where is the insert for the master record in LANGUAGES with
LANGUAGEID=1?

Also, don't forget that you must commit the DDL for the master tables
before you can define foreign keys that refer to them.  If you are
using isql then the default behaviour is to autocommit DDL statements.
 The same is likely to be untrue if you are using a third-party tool.

 After that, if you have uncommitted inserts to a referenced table in
one transaction, then those uncommitted records are not visible to a
transaction that is trying to insert into the detail table.

Note that isql does not autocommit DML statements.

Helen

Re: [firebird-support] Info

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Nico,

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

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


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

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

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

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

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

Helen Borrie

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello LtColRDSChauhan,

The net-provider list that you have been posting to on Sourceforge is
inactive.  Any postings there are ignored.

You need to go to this URL:

https://groups.google.com/forum/#!forum/firebird-net-provider

and join the "new" group.  It has been "new" for more than two months.

HB

Re: [firebird-support] Trouble establishing a connection to Firebird database files.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, November 28, 2017, 12:06:27 PM, Justis wrote:

>I’m running firebird 3.0; I’m not sure what version of firebird the database 
>files were generated with.

You can find out, using another command line tool.  Open a command
shell and go to the Firebird root directory.

Then do

gstat -h d:\path\to\your\database

where d:\path\to\your\database is the absolute (i.e., not relative)
file path to your database

If you do that right, and the database was created or restored under
Fb 3, you should see output similar to this:

C:\Programs64\Firebird_3_0>gstat -h 
c:\programs64\firebird_3_0\examples\empbuild\employee.fdb

Database "C:\PROGRAMS64\FIREBIRD_3_0\EXAMPLES\EMPBUILD\EMPLOYEE.FDB"
Database header page information:
Flags   0
Generation  168
System Change Number0
Page size   8192
ODS version 12.0
Oldest transaction  160
Oldest active   161
Oldest snapshot 161
Next transaction161
Sequence number 0
Next attachment ID  21
Implementation  HW=AMD/Intel/x64 little-endian OS=Windows 
CC=MSVC
Shadow count0
Page buffers0
Next header page0
Database dialect3
Creation date   Mar 21, 2017 10:15:35
Attributes

Variable header data:
*END*

If it was created or restored under an older Firebird, you will see
this:

C:\Programs64\Firebird_3_0>gstat -h 
c:\programs64\firebird_2_5\examples\empbuild\employee.fdb

Wrong ODS version, expected 12, encountered 11

ODS means "on-disk structure".  Each major verson (1.0, 1,5, 2.0, 2.1,
2.5, 3.0, 4.0) has its own ODS:
ODS 10.0 ~~ Fb 1.0  ODS 10.1 ~~ Fb 1.5
ODS 11.0 ~~ Fb 2.0  ODS 11.1 ~~ Fb 2.1
ODS 11.2 ~~ Fb 2.5  ODS 12.0 ~~ Fb 3.0

So, take note of the 'encountered' ODS if you get that message, as it
will tell you what version of Fb you need to install instead.

(Actually, this just revealed a bug in Fb3's gstat.  It should have
reported '11.2', not '11'.)

BTW, Fb 2.5 and below *can* attach to databases with older ODS, so if
Fb 3 gives you the Wrong ODS version error, try installing V.2.5, as
its gstat -h and iqsl tools will read 11.0, 11.1 and 11.2.

However, if you need to make a gbak backup of your database, in order
ro restore it as ODS 12 under Fb 3, you will need to run the backup on
the actual Firebird server version that created it.

>I’m using the JDBC driver to connect from InfoCaptor.

>When using the ISQL tool to try and connect, this is what I see:
https://gyazo.com/b122c8c6de54be1d07a5240dc65c4ff6
>I don’t even get an error.

> I’m not sure if that means the connection was successful or unsuccessful.

 It means neither.  The CON> prompt there means isql is waiting for a
semi-colon, which is isql's line terminator.  But that is not a valid
CONNECT statement you have there, anyway, even with the semi-colon.
If you supply the user and/or password arguments to the CONNECT
statement, the syntax is:

SQL> CONNECT 'localhost:employee' USER sysdba PASSWORD 'masterkey';

Note that employee is an alias for
c:\programs64\firebird_3_0\examples\empbuild\employee.fdb. Read up
about database aliases - they are very friendly to have around.

As you did it, in Fb 3, with an embedded connect, you don't need
the PASSWORD argument at all.  You will need the USER argument as,
otherwise, you will be logged in under your Windows user name, which
may not, at this point, have the permissions you need to access
objects in the database.

>I was attempting to follow this tutorial:
>https://www.firebirdsql.org/pdfmanual/html/isql-connect-database.html

>When I try and connect with the same credentials with InfoCaptor via
>the JDBC driver, the error I get is simply “null”.
>The JDBC url I’m using is
>“jdbc:firebirdsqlLlocalhost/3050:T:/Database/ejdb.fdb”. And the user
>and password are the same as in the above screenshot.

3050 is the default port so you don't need it.  But that URI looks
weird, anyway.

Try

"jdbc:firebirdsql://localhost:T:/Database/ejdb.fdb"

But it can't work if drive T: is not a physical drive wired onto the
server that hosts Firebird.  What I'm saying is that, if you have only
drives C:, E: and F: on the machine, your databases must be on the
native path of one or more of those, not on some mapped network
address.  So if you have drive T: mapped to some network or local
address then drive T: is NOT a phsysical drive.

May I suggest that, given your uncertainty about the properties of
your databases, you uninstall Fb 3 and install v.2.5?
And don't try to run before you can walk.  Use isql or some Firebird-dedicated
third-party tool, rather than one from "out there" that treats all
database engines as created equal.

HB

Re: [firebird-support] Trouble establishing a connection to Firebird database files.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Monday, November 27, 2017, 11:35:47 PM, Justis wrote:

> I’m running the latest version of Firebird on my windows PC, and
> I’ve got some .fbd files on my F:// Drive.
> I’m trying to connect to them via Firebird’s JDBC driver, but I’m getting 
> absolutely nothing.

The JDBC driver is that - a driver.  It is for connecting a Java
application to a database, through the Firebird server.  Unless you
have such an application, you don't have a starting point.

> I’ve tried restarting firebird, the machine.

You don't have to restart Firebird or the machine if you fail to
connect.  Firebird will just keep waiting until to offer a connection
string it likes.

> This is my first time with Firebird, so I apologize if I’m a bit
> slow to understand where to even start with trying to explain what’s 
> happening.
> What should I be checking first?

Well, Java is only one driver out of many.  Your best place to start,
AFTER reading the Quick Start Guide that is in the doc directory of
your Firebird installation, will probably be with the isql
command-line tool.  It is a native application that doesn't need a
driver, as it its interface is stitched directly into the fringes of
the Firebird engine.

If you are a total newbie, you should also grab the isql manual from
the Firebird documentation library.

> What info am I missing from this description in order to make assisting me 
> easier?

If you read the guff and still can't connect, you can copy/paste any
error messages from isql into your next message to this list.  Then,
everyone will know what kind of help you need. If/when you do, tell us
what version of Firebird you are trying to use.  That really matters,
as Firebird 3 cannot connect to databases made by earlier Firebird
versions.

Also, provide the connection string you use to request the connection,
e.g.

localhost:f:\databases\mydb.fdb -user sysdba -password masterkey

For issues trying to connect an app with your database through the
JDBC driver, there is a dedicated list.

HB

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Wednesday, November 15, 2017, 5:54:05 AM, m_brahi...@yahoo.fr wrote:

> I would like to know please, if we can use a DB in win64 but built
> under win32 . The client app is built with delphi 32 bits so the
> clients will connect to that DB in 64 bits. Is it possible ? Thanks
>   
1. Your database should be fine if moved from win32 to win64.  It is
recommended that you back up the database on the old platform with
gbak and restore it with gbak on the new. as a good housekeeping
measure.

2.  Firebird servers don't care whether the client is 32-bit or
64-bit.

2.  Clients must use the fbclient.dll that matches the bitness of the
*application*.  If your database is going to be restored for use under
Fb3 then you will need to download the win32 zip kit and extract the
32-bit fbclient.dll from it.  In the Fb2.5 win64 kit, there should be
a copy of the 32-bit client in the system32 subdirectory.  I don't
know why it is omitted from the Fb3 kits.

Helen

Re: [firebird-support] string right truncation error with LIKE predicate and UTF8 DB

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, November 3, 2017, 3:12:37 AM, Rustam wrote:

> I got an error executing SELECT query with LIKE predicate in case of UTF8 
> database,
> non-UTF8 connection charset and national characters in LIKE
> argument without mask symbols '%' and '_'

> Error is

> "arithmetic exception, numeric overflow, or string truncation
> -string right truncation"

> 1) Create database with default character set = 'UTF8'
> 2) Connect to DB with ANSI character set, e.g. 'WIN1251'


> isql -ch WIN1251

> 3) Execute SELECT query on any table with VARCHAR fields of any length
> with LIKE predicate on VARCHAR field, that meet following criterias:
> - argument not contain mask symbols '%' and '_'
> -   argument length greater then 1 symbol
> -   argument contain national symbols

> EXAMPLE:

 > Change console window font to Lucida Console (to allow cp1251 characters in 
 > console)

> Start ISQL:
> isql -ch WIN1251

> create database 'c:\databases\bug.fdb' user 'SYSDBA' password 'masterkey'
> default character set UTF8 collation UNICODE_CI_AI;


> create table TABLE1 (
>  FIELD1 varchar(50)
>
> insert into TABLE1 (FIELD1) values ('абвгде'); -- WIN1251 characters string

'абвгде' is transliterated to charset UTF8.

> This queries work fine:

> select * from TABLE1 where FIELD1 like 'abc%';
> select * from TABLE1 where FIELD1 like 'abc';
> select * from TABLE1 where FIELD1 like 'абв%';

As expected.  Client's WIN1251 input is transliterated to UTF8.

> This query fails:

> select * from TABLE1 where FIELD1 like 'абв';

The "bug" here is that the message for all string input errors is the
same.  Transliteration of the input argument in the failing query
would occur as in your third query above if the LIKE argument was
valid.  IMHO, it should give an invalid DSQL error, not that generic
string error.

> In last 2 queries in LIKE argument used string with national
> characters (in this case - WIN1251)

The difference between the two results confirms that transliteration
is healthy when you provide a valid argument for LIKE, i.e., provide a
wildcard character.

If you wanted to pass a query like that, you should use STARTING
[WITH]:

select * from TABLE1 where FIELD1 STARTING WITH 'абв';

Helen

Re: [firebird-support] Creating a conditional "order by" statement

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, October 24, 2017, 1:37:01 AM, j...@acousticdesign.co.uk wrote:

> I have a stored procedure along the lines of

> SELECT ID, Amount1, Amount2 ..

> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> Is it possible to construct a conditional ORDER By clause that
> orders by Amount1 under certain conditions, and order by Amount2 under other 
> conditions?

Using DSQL, no, since direct DSQL statements are compiled in the BLR
code of the stored procedure.  You need to avail yourself of EXECUTE
STATEMENT. It's the sort of thing that this command is for.

> At the moment, the only way around it I have found (and I spent
> most of the night trying!) is to use an If clause and duplicate nearly all 
> the SQL

> IF (Condition1) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 1

> ELSE IF (Condition2) THEN
> SELECT ID, Amount1, Amount2 ..
> FROM Table1.
> UNION
> SELECT ID, Amount1, Amount2...
> FROM Table2
> ORDER BY 2

> This seems a crazy way of having to achieve my aim!

Yep.

> Any help gratefully received.

create procedure my proc
  (orderingcolumn char /* pass '2' or '3' depending on user input */
  )
  returns (
id bigint,
amount1 decimal,
etc. [columns you want in the left-to-right order you want]
)
as
declare variable execstring varchar (1000); /* whatever max. length you need */
begin
  execstring = 'SELECT ID, Amount1, Amount2 ..'
  || ' FROM Table1'
  || ' UNION'
  || ' SELECT ID, Amount1, Amount2...'
  || ' FROM Table2'
  || ' ORDER BY ' || :orderingcolumm ;

  FOR execute statement execstring
  into :ID, :AMOUNT1, etc. [your output variables]
  DO SUSPEND;
end

Looking at this problem from another angle...your examples are
just examples, of course...but on the surface it looks as if you would
better serve your need by using a DSQL statement directly, rather than
trying to bury it in a SP.  Or you could define a view, which would be
unordered, and supply the order by clause when selecting from it.

Helen

Re: [firebird-support] Wirecrypt

[Helen Borrie hele...@iinet.net.au [firebird-support]]

I wrote:
>> The Firebird 3 installer script didn't put it there but possibly
>> FlameRobin or Jaybird did.  Some client interfaces need to read
>> firebird.conf.  It won't conflict with the server's firebird.conf, per
>> se, but it would pay to keep the one used by the client app consistent
>> with the one used by the server.

Thursday, October 19, 2017, 9:13:39 PM, Mark wrote:

> Jaybird doesn't install anything, and AFAIK, neither does Flamerobin do
> anything with with firebird.conf. It is more likely the layout of the 
> specific firebird install on his Linux system.

More likely some other client app, then, as he seems to have installed
Firebird from the project package - since it is installed in
/opt/firebird.

FWIW, I'm on a Ubuntu-based Linux and their platform-specific Firebird
packages are a nightmare and usually horribly old.  I always install
from our own tar.gz because it just works, it's the latest sub-release
and I don't get files being spread all over the system.

Helen

Re: [firebird-support] Wirecrypt

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Wednesday, October 18, 2017, 9:52:36 AM, dferg...@gmail.com wrote:

> I am a newbie, like a total newbie just exploring databases for the
> first time.  I am using Lazarus and they all seem to prefer Firebird
> and lot of the documentation is geared that way.  I have
> successfully downloaded and installed Firebird onto ElementaryOS
> Loki.  I am trying to use DB Scema but can't connect to firebird
> from there.  I can connect to sqlite from dbscema and I can
>  connect  to firebird from Flamerobin.  I get the error:

> ncompatible wire encryption levels requested on client and server
> Incompatible wire encryption levels requested on client and server
> [SQLState:28000, ISC error code:335545064]
>
You CAN or you cannot connect from FlameRobin?

> I then followed these instruction and changed my firebird.conf file
> to "Wirecrypte = Enabled" as seen here:

Hopefully you didn't add that extraneous letter 'e' to the parameter
name.  And you removed the '#' character from that line.

>   - https://www.firebirdsql.org/ file/documentation/drivers_
> documentation/java/3.0.2/ release_notes.html#notes-on- firebird-3-support

If you want to enable wire encryption, you will need to have two
things in addition to enabling it in firebird.conf:

1.  the fbclient.so that comes with Firebird 3.  Older Firebird
versions do not support wire encryption.

2.  a language interface layer that knows about Fb3 wire encryption so
that the application can pass it in the API.

For FlameRobin, you need fbclient.so:  the 64-bit version if you are
using 64-bit FR;  otherwise, you need the 32-bit version.  You match
the bitness of the client to the bitness of your application, not the
bitness of the Firebird server.
Unfortunately, if you have installed the 64-bit Firebird server and
need the 32-bit client, you'll have to grab the x86 kit from the
download area and extract the client from it.  For some reason that
totally escapes me, we still do not provide any client-only kits in
the download area.

> But I still can't connect.  I also tested with DBerver but have the
> same problem.  I downloaded and installed the java driver to version
> 3.0.2.  But still no success at all.  I have no idea what t  o do. 
> All the programs I have tried to use to connect to Firebird don't
> work and they all tell me to come here.

You've named some applications I've never heard of.  Are you sure they
actually support Firebird?  If they claim to do so, check what driver
they need, e.g., ODBC, Python, etc.

In the case of Jaybird, you have both the client and the language
interface rolled into one.  Jaybird supports the API natively.
However, it appears Jaybird does not support wire encryption yet.  To
ask about that, subscribe to the firebird-java support list, see
https://www.firebirdsql.org/en/mailing-lists/

> I would attach the firebird.conf but there doesn't seem to be a way
> to do that.  the firebird conf that I found was in
> /etc/firebird/firebird3.0

The Firebird 3 installer script didn't put it there but possibly
FlameRobin or Jaybird did.  Some client interfaces need to read
firebird.conf.  It won't conflict with the server's firebird.conf, per
se, but it would pay to keep the one used by the client app consistent
with the one used by the server.

> I also found an other firebird.conf in  /opt/firebird/.

That's the one the installer put there.

> I h  ave no idea why there would be two of them.  I
> have only ever installed FB once.  I have tried changed the
> Wirecrypt on both, then on one then the other.

If you change a configuration setting you have to restart the Firebird
server, unless you are running Classic. (But, if you didn't change the
ServerMode parameter, then you are running Superserver and you DO need
that restart.)  If FR or another app is depending on the settings in
its own firebird.conf file then you;d probably need to restart that
app after changing that file, too.

Another possibility is that the FlameRobin package you installed is
pre-Firebird 3.0 and has installed an old fbclient.so in its WD.  So
check that as well.

I strongly recommend that you study the two PDF books in
/opt/firebird/doc.  There you have the Quick Start Guide and the
release notes.  Our release notes are very detailed.

Helen

Re: [firebird-support] embedded database with Chinese path

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Hamish,

Thursday, October 5, 2017, 4:53:24 PM, you wrote:

[...] all that

Have you considered trying a symbolic link?
https://www.howtogeek.com/howto/16226/complete-guide-to-symbolic-links-symlinks-on-windows-or-linux/

Re: [firebird-support] Cannot grant user by code

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, October 3, 2017, 5:02:21 AM, Pierre wrote:
> I want to add readonly user creation/granting after database initialisation.

> I create the database using a bunch of SQL scripts. After the last
> script runs, I create a user using "create user MYUSER password
> 'myPassword'" the I grant this user read only (select, references)
> access to every table of the database using EXECUTE BLOCK :

> https://gist.github.com/zedalaye/6300b679e56349c40b973d35bb4c91ee


> On Transaction Commit, I get this error :

> add record error violation of PRIMARY or UNIQUE KEY constraint
> "INTEG_2" on table "PLG$USERS" Problematic key value is
> ("PLG$USER_NAME" = 'RO_USER') Unsuccessful execution caused by
> system error that does not preclude succe  ssful execution of
> subsequent statements GDS Code: 336723987 - SQL Code: -901 - Error Code: 19.

CREATE USER adds users to the security database, whichever one you have
configured as security database.  The key violation occurs because
either (1) you already created that user using that plug-in or (2) the
user does not exist.

Regarding (1), you can have more than one user having the same user
name as long as they are created using different plug-ins.  For your
scripts it would be wise to specify the plug-in explicitly with a
USING PLUGIN clause, even if you want to use the default plug-in.

Regarding (2), remember that the CREATE USER command is run from the
connection to the application database, but under the surface, the
instructions are applied to PLG$USERS in the security database. Your
GRANT commands apply to the application database.  The CREATE (ALTER/DROP)
USER command needs to be committed for its effects to become visible
for reference by statements affecting the application database.

If your problem is caused by (2), I think you will need to isolate
your CREATE USER statement(s) into a different SP, to be run and
committed in a separate transaction, before you run the script
assigning the permissions in the application database.

Helen

Re: [firebird-support] Re: Installing Firebird on Fedora 26

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Allan,

Tuesday, October 3, 2017, 1:02:48 AM, Allan Jardine wrote:

> In the mean time I've tried installing the Firebird package from
> dnf (I actually tried this first and have just tried it again). It
> appears to install okay, but then any interaction I try to make with
> the database I get a "Install incomplete" message.


> $ isql-fb 
> Use CONNECT or CREATE DATABASE to specify a database
SQL>> connect localhost:employee user sysdba password masterkey;
> Statement failed, SQLSTATE = 28000
> Install incomplete, please read the Compatibility chapter in the release 
> notes for this version


> This happens with the cli, FlameRobin and PHP.

So - did you read the Compatibility chapter and follow the
instructions there?

HB

Re: [firebird-support] error 10054

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, September 21, 2017, 12:24:52 AM, "Sabbagh, Nariman" 
 wrote:

> I am using Ames application which is using firebird, Ames keeps
> hanging and it is because of firebird giving error 10054. Could you please 
> advise , thank you
>

Well, that is a network error, not coming from Firebird.  Firebird
will obligingly report and log network errors, but it has no control
over what happens in your network.

You say the AMES application "keeps hanging".  That is not "because of
Firebird" but will be due to one of two main sources of trouble:

1) The application crashes while a user at a client node is connected
to the database.  This may be due a bug in your software or it could
be just a careless or impatient user ending a slow session by just
switching off instead of exiting gracefully from the application.

2) Your network service is losing the connection between the Firebird
server and the user experiencing the problems, due to some fault in
the network (hardware or configuration).

>From your email address, I'm guessing that this "AMES" software is the
aircraft maintenance scheduling product from the Omega company in
Texas, U.S.A. If you are able to eliminate network faults then your
only option is to contact their software support, giving a better
description of the problem than the one you provided here.

Helen

Re: [firebird-support] file size and transactions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello shg_siste...@yahoo.com.ar,

Friday, September 15, 2017, 2:53:19 AM, you wrote:

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

It is true for READ ONLY transactions that are started in READ
COMMITTED isolation mode.  In other isolation modes, long-running
transactions in both READ ONLY and READ WRITE will block GC and evade
sweeps.

Helen

Re: [firebird-support] file size and transactions

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello shg_siste...@yahoo.com.ar,
 Thursday, September 14, 2017, 2:10:23 AM, you wrote:

> Hello! I'm noticing a small increase of my database size. Now it
> has 566,992 KB. After a backup/restore the size goes to 533,640 KB.
> I don't have long running read/write transactions (well... I think)

There is nothing wrong with your transaction statistics.
The reason why the database "grows" is this:

A Firebird database is stored in one file.  This file is made up of
"pages" - chunks of disk that the Fb engine keeps account of
individually.  In your case, each page is 4Kb.

The engine is constantly performing maintenance on these pages - this
is called garbage collection and sweeping.  GC and sweeping are
related to each other but they are not exactly the same.  Between
them, they identify pages that no longer contain record versions that
are needed for anything, so they release these pages for re-use.
Thus, changing or deleting records does NOT reduce the size of the
database file.

A newly restored database has no spare pages waiting for re-use.  That
is why the file has its "minimum" size immediately after a restore.
It has exactly the number of pages needed to store everything that was
backed up.  Garbage pages are not backed up at allwhich is why a
badly managed database can really only be properly cleaned up by a
full backup and restore.

At the same time as GC and sweeping are going on, the engine is
acquiring new pages (two at a time - in your case, 8 KB)) from the operating
system whenever it has insufficient empty pages available to store a
new record or record version from an insert or update.  Old record
versions, from updates and deletes, stay on their pages until the
GC/sweep process ultimately frees them.  When transactions are
not well managed, the housekeeping gets "stuck" on pages that the bad
management keeps in an "interesting" state.  If the programmer has not
taken good care of committing transactions that are finished, the
engine has no choice but to keep those pages in an "interesting"
state.  That is when you would see an increasingly large gap between
the oldest transaction (oldest interesting, or OIT) and the oldest
snapshot (OST).  Then, you can query MON$TRANSACTIONS to try to
discover where the problems are.  But your stats look fine.

Know, too, that the engine needs pages for other things besides data -
it maintains index pages, blob pages, etc.  Once a page has been used
as one type of page (data or index or blob) it cannot be reused for
another type of storage.  So some of your operations could be
consuming quite a lot of new pages and this is not an unhealthy sign,
either! ;-)

Helen

Re: [firebird-support] FB 3.0 Replacing ICU libraries

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Magnus,

Tuesday, September 12, 2017, 9:53:47 PM, you wrote:

> From what I have been able to read, the documentation that exists
> seems to be a mix on both 3.0, 2.x and even 1.5 making it a little unclear.

If you have the IBPhoenix DVD, you will find it documented in some
detail in Chapter 9 of The Firebird Book Second Edition eBook.  The
same in hard copy is in Chapter 8 of TFB2e Volume 1 (Firebird
Fundamentals).

Helen

Re: [firebird-support] Re: Firebird Alpha 4 Release Notes

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> 10.09.2017 02:13, Daniel Miller wrote:

>> The misunderstanding is probably mine - but I've never heard of 34-bit
>> values/precision before.  Is that a typo for either 32 or 64?  Or "34"
>> is correct?

DY replied:

> 34 *digit* precision. If "34-bit" is mentioned there, then it's a mistake.

It is a mistake.  It has been corrected in the online notes.

Any more discussion about anything in the Fb 4 Alpha, please take it
to firebird-devel.

^heLen^

Re: [firebird-support] FreeAdhocUDF and Firebird 3 not working

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, September 8, 2017, 9:08:00 PM, ke...@dojitraders.com wrote:

> Thank you for your reply. I mentioned that this problem was
> reported in the Tracker system (Bug CORE-5306) and my problem is just the 
> same.

>From that discussion, Mr da Costa and you are encountering the same
symptoms.  He was trying to use the Tracker to fix the symptoms.  It is
an old report and core devs were involved, meaning they investigated
it and couldn't reproduce it.

> Basically I get the following type of error

> invalid request BLR at offset 36.
> function EXTRACTDAY is not defined. 
> module name or entrypoint could not be found.

When you upgrade a database, objects are not recompiled (reconverted
to new BLR).  You have an error somewhere that 2.5 ignored, while 3.0
reacts to something in the legacy BLR that is not valid.  With that
error, a likely cause is wrong definition of the module name, i.e., it
was defined with the full name of the DLL instead of the 'name' portion
alone. That is not "newly wrong" - it always was - but it was not
necessarily enforced in the past.  My guess is that someone caught
this in some core code cleanup during implementation of stored
functions in Fb3 and you had historically faulty declarations that
became victim to an inherited hack.

Check that:
select * from rdb$functions

If you see anything except the name part of the DLL module, you have
found the problem.  Fixing it won't be so simple, as you cannot edit
the system tables in Fb 3.  You will have to find and eliminate any
dependencies, drop the offending functions and re-declare them.

If you have a trigger or a procedure that invokes the function, as a
reality check, you could apply a CREATE OR ALTER operation to it,
using the extracted source text.  If it refuses to compile, note the
error that is thrown.  Chances are, it will get you closer to the
source of your problem.

Make sure you give EXECUTE privs on the function to the trigger or
procedure first.

> I only have one user on all of my installs and that is SYSDBA.

So apparently you're not bothered by vulnerabilities.  Actually, if
you were, you would not even consider using UDFs.

Helen

Re: [firebird-support] Re: Problem with linked table

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, September 8, 2017, 9:31:43 PM, Adriano Novelli wrote:
> i use a odbc connection to connect to the database.

> on  every pc I have configured a odbc connection with the same name
> and  configured the database path by specifying the IP address of the server

> as I said before, if I do the db connection test from every single pc, 
> everything works.


> I repeat the steps to replicate the problem:

> i create a db access 2007 and i put it in a shared folder (every pc
> have a mapped network drive (Z) to access this database

> 1) from pc 1, i open the db and link some tables u  sing a odbc
> connection. if i open (in access 2007) the linked table, i read the data
> 2) from pc 2, i open the db shared and i view the linked tables. 
> if i open (in access 2007) the linked table, i have an odbc error "connection 
> failed".
> 3) from pc 2, i remove the linked tables. using a odbc connection,
> i re-link the tables and now (on pc 2) i read the data!
> 4) at the moment, on pc 1 i can not read it anymore data from the linked 
> tables

> in conclusion, I can read the tables data only on the pc that I use to link 
> the tables

This is the behaviour you would see if your clients are using Embedded
to connect to the database.  This works only for one connection:  all
others are blocked until the active client detaches from the database.

Please understand the architecture.  For multiple users, you need the
full Firebird server installed and running as a service on the host
machine where the database is physically located.

Each user must make a remote connection to the physical location, not
to a share.

Let's say this host machine has the network name 'nostroserver' and
the database bdati.fdb is located in c:\dati.  This must not be a
share.

The full path to your database from each remote client is:

nostroserver:C:\dati\bdati.fdb

On his/her own computer, each client needs
(1) a copy of your software
(2) The Firebird ODBC driver
(3) A data source configured to connect to the database using the full
path quoted above
(4) The Firebird client fbclient.dll, copied from the server
installation.

The client components can be accessed on a share, if you like.

Note:
You need the 32-bit client if your software and driver are 32-bit.
If your server installation is 64-bit then the fbclient.dll in the
\bin\ directory there will not work with 32-bit clients.  You can
extract the 32-bit client from the \bin\ directory of the 32-bit zip
kit.

Helen

Re: [firebird-support] FreeAdhocUDF and Firebird 3 not working

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello ke...@dojitraders.com,

Friday, September 8, 2017, 11:10:39 AM, you wrote:



> I am thinking FreeAdhocUDF is a dead project. It has not been updated for 
> many years.


> The main thing though is that these UDFs work ok in Firebird 2.5. I
> have also developed a UDF (DLL) in Delphi, and that does not work
> either. It used to in 2.5 (still does). I thought it better to
> mention the FreeAdhocUDF issue otherwise it would be my fault that my own UDF 
> does not work!


> There is obviously something different about V3 that is stopping
> some UDFs from working. In my own limited experience, it is stopping ALL UDFs 
> from working.

What does "not working" mean?  Do you get any exception messages?
Crash the server?  What?

It's possibly a permissions problem.  Fb3 introduced EXECUTE
privileges on UDFs.  The initial situation is that the user that
"owns" an externally defined UDF is the user that originally declared
it.  So, for example, if it was declared by SYSDBA, then only SYSDBA
can invoke it.  SYSDBA will need to grant the EXECUTE privilege on it
to any users who are going to need to invoke it.

You might care to check the setting for UDFAccess in the firebird.conf
of your V.2.5 installation.  It could be you configured that and then
forgot about it.  The default UDFAccess setting in Fb3 is Restrict
UDF.  That causes it to look only in Firebird's \UDF subdirectory for
the declared UDF modules.


> And this is not a trivial problem. I have spent days trying to
> remove UDFs from my existing 2.5 version databases, and it is not an easy job.

Well, it's hard to help with a solution to your non-trivial problem if
you provide only a trivial account of what goes wrong.

Helen

Re: [firebird-support] Implicit path for Database

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, September 2, 2017, 9:35:20 AM, Neagu wrote:

> In databases.conf I can put the lists of alias of Known databases, but I
> look for a way to specify only the alias of path where the databases are
> store.


> Now, If I not specify a path in connect string, the databases is search
> in C:\Windows\System32

And, on POSIX, it will search in the current working directory (cwd).

You can (and should) configure which directories the server is allowed
to search for databases to connect to.  The parameter for this, in
firebird.conf, is DatabaseAccess.  By default, it is set to 'Full',
meaning the server can accept connections to databases anywhere.  The
alternative settings are:

None.  This takes no arguments.  The server can connect only to
databases whose full paths are specified in databases.conf.

Restrict.  This takes a list of one or more filesystem paths where the
server may connect to databases located there.  If you have databases
in sub-directories of that path, you must list each path separately.
Use semicolons to separate multiple paths.

> Will be good also a way to specify the path where the databses are store
> if I not specify a path in connect string.

Something like this is possible, as long as you want to store the
database in Firebird's root directory *AND* that path is specified as
a parameter of DatabaseAccess Restrict (or DatabaseAccess FULL is the
current setting). In general, though, storing a database amongst the
software components is not a brilliant idea.

Read the built-in documentation for DatabaseAccess in firebird.conf.

Did you know that you can create an alias for a database that does not
yet exist?  For example.

myNewDB = d:\databases\development.fdb

After that is saved, you can create a new database named
'development.fdb' in d:\databases by passing the alias as the database
name parameter in your CREATE DATABASE statement.  In this case, your
DatabaseAccess configuration can be NONE (because the database is
known through databases.conf) or FULL (server does not care where a
database is located).  RESTRICT will work, too, if d:\databases is
included in the list of allowed directories.

Don't forget that any changes you do in firebird.conf will take effect
only after restarting the Firebird service.

Helen

Re: [firebird-support] OT: Firebird 4.0 xinetd Support on Linux Replaced

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, August 25, 2017, 3:36:51 AM, Ismael wrote:

> Sorry for the off topic, but I  have a doubt that maybe some of you can 
> clarify


> I was  reading Firebird 4.0 Release Notes and I found the following note:

Yes, any questions about alphas, betas, snapshots, pre-release
versions are right off topic here.  Take them to firebird-devel,
please, as instructed in the release notes.


> Tracker ticket CORE-5238
> On Linux, Firebird 4  uses the same network listener process
> (Firebird) for all architectures. For  Classic, the
> main (listener) process starts up via init/systemd, binds to the 
> 3050 port and spawns a worker firebird process
> for every connection—similarly  to what happens on Windows.

> This has caused me the following  doubt:
> With this modification Firebird 4.0 will work on  linux system that do not 
> use systemd? Yes or  no?

The notes say "...starts up via init/systemd..." which means one or
the other.  You're in trouble if your system does not use either one.
;-)

And no more Fb 4 questions here, please.

^heLen

Re: [firebird-support] Firebird 3 crashes ( terminated abnormally (4294967295) )

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> I’ve been having quite a few crashes on a new server with not much databases
> or requests. terminated abnormally (4294967295)

> It seems to be very random, there are not many different operations going
> on.

>  

> It’s a google cloud Windows vm, I’ve checked for hardware / driver problems
> but it seems to be ok.


> Firebird 3 Super

> Conf : 

> ServerMode = Super

> DefaultDbCachePages = 5

*EACH DATABASE* will be consuming up to (5 * page size) on cache.
Why do you set DefaultDbCachePages so high  The Firebird server
needs RAM for more things than just the cache.

Also, check whether you have enough space on disk to take the temp
files.  Even on a low-traffic system, one ordered or grouped query on
a long table could easily be too big to store intermediate files in
memory, especially as you are eating so much RAM with your caches.
Those files will go to disk and you simply might not have enough disk
space available.
Note, too, that on a VM it is strongly recommended that you configure
TempDirectories explicitly and not rely on a preconfigured temp or tmp
directory that might be missing or shared by greedy apps.

Helen

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

[Helen Borrie hele...@iinet.net.au [firebird-support]]

>> Let's create two unites from one lot:
>> 
>> INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A1', 'L1', 'S1',
>> 'abc'); INSERT INTO UNITES (CODE, LOT, SITE, DESCRIPTION) VALUES ('A2',
>> 'L1', 'S1', 'bcd');
>> 
>> I expected that the following instruction will not be exectued:
>> INSERT INTO LOCATION (ID, UCODE, ULOT, USITE, ZONE, WAREHOUSE)
>> VALUES (1, 'A3', NULL, NULL, NULL, NULL); because there is no A3 entry in
>> unites table, but it IS! Firebird allows to add record with value which 
>> doesn't
>> exist in master table.

> I believe that your schema for the LOCATION table is incorrect.  

> UCODE, ULOT and USITE should be described as NOT NULL, since a
> Location must relate to a UNITES (which has CODE, LOT and SITE described as 
> NOT NULL), no?

As Karol and Sean have noted.

Also, are you sure you have this relationship facing in the right
direction?  Do locations depend on units?  or do units depend on
locations?

Normally, a Location would be a primary entity and Unit a secondary
one. So a foreign key relationship wants to ensure that a Unit is not
booked in having a Location that does not exist, including any key
elements that are NULL.

Obviously, language differences are in play here, so your
conceptualization of the relationship might be correct for your case.
But if the dependency is as I described it, then the FK should be in
Unites, referencing the PK in Location.  And - simply - never allow
NULL to be written to a constraint element.

AFAIR, Firebird follows some standard that allows one record with one
NULL element, so with your 3-element key, you have the potential to
have three useless reference records.  It's one of those cases where
"just because you can does not mean you should".

Helen

Re: [firebird-support] Firebird 2.5 rejecting connections

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Monday, August 7, 2017, 5:54:06 AM, you wrote:


>>
>> Did you actually replace the client lib on the remote box with the
>> appropriate-bitness one from the installation?

> System is x64 but x86 version of firebird is required due to legacy UDF.
> I have tried deleting the DLLs from System32 and SysWOW64 to avoid PATH
> issues but that didn't have any effect.

So what are you talking about here?

You're running 32-bit Firebird on a 64-bit box.  That's fine.

Still talking about the server box:  you want the 32-bit client to run
the Fb system utilities because you installed 32-bit utilities.

For your own applications, running them on the server through TCP/IP
local loopback, you want the Fb client's bitness matching your
*application*, regardless of what bitness the Fb server is;  likewise
any driver layers such as ODBC.

On the remote client box, same story.  Match the client to the bitness
of the application and driver, not the remote server.  If you
installed a 32-bit kit, you would not get a copy of the 64-bit client.
 In the 64-bit kits you get both, with the 64-bit one in $firebird\bin
 directory and the 32-bit one in $firebird\WOW64.

 Also, considering the problem with localhost on the server box, have
 you checked your system variables to make sure FIREBIRD is
 pointing to the right place?  It's just a stray thought, as I've
 never heard of connection problems associated with that variable
 being missing or bad.  I'd think it would only be a problem if it was
 set previously for an older version;  while the newer version is
 located in a different subdir.

 Helen

Re: [firebird-support] Firebird 2.5 rejecting connections

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Sunday, August 6, 2017, 7:07:02 PM, you wrote:

> Hi all.

> On a brand new install for Firebird 2.5.7 (x86) I am having issues
> connecting to the server via TCP/IP.
> The error message is always the same "Connection rejected by remote
> interface" however I have tried several DB utilities and I can connect
> fine via local protocol but not via network address.

> I'm suspecting a library issue (conflicting fbclient.dll or gds32.dll) but
> for the love of God I have been unable to find it anywhere on the system.

> Anyone have any tips on the issue?

To ask the obvious, is port 3050 open on the server?

How would you recognise "a conflicting fbclient.dll or gds32.dll"?

Did you actually replace the client lib on the remote box with the
appropriate-bitness one from the installation?

Helen

Re: [firebird-support] unpacking backup with firebird version 1.5 (windows 7)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, July 28, 2017, 9:03:19 PM, robert rottermann wrote:

> I proceeded as Helene suggested

> I  executed gbak.
> However this produced zillions of warnings of the form
> gback: do not recognize table attribute 0 -- continuing

> this goes on now for some 15 minutes.
> A database VAS.FDB was created
> I can open it from my linux box using flamerobin.
> The tables seem to exist all, their structure is ok as far as I can 
> judge from a first glance.
> But alas, they are empty!!

> please advice.

Something is wrong with that backup.
In your original message, you said:

>when I do [a restore) on my linux box

>sudo gbak -c VAS.bak vas_db/VAS.fdb

>this seems to work fine (alltough i can not use the result)


Questions:

1. What did you mean "I can not use the result" ?

2. Are you able to connect to the original database on Linux and see data
in the tables?  Meaning - do you have a "good" database somewhere?

3.  On Linux, what information does FlameRobin provide about the
on-disk structure and the status of the database?  (You can also get
this information using gstat -h, or using the SHOW DATABASE command in
isql.)

4.  Have you tried making your own gbak backup of the "good" database
on the Linux box?

5.  Have you tried file-copying the good database onto the Windows box
and connecting to it?

Helen

Re: [firebird-support] using embedded

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, July 27, 2017, 7:33:43 AM, prosse...@yahoo.com wrote:

> I want to get a delphi7 program that works with the full server to
> work with embeded... I am a getting 'database unavailable' whatever I try

> I have followed the instructions to the diligently - copying and
> renaming the files so what am I missing?

First things first:  embedded works differently depending on what
version of Firebird you are using.  Since you mentioned "copying and
renaming files" one assumes it's not Fb 3.  As a general comment, it
makes no sense to post a question without the basic information.

Next, you won't be able to run your app from within the Delphi IDE
using an embedded connection.  That's because the IDE is already
connected to the database in its own workspace, thus precluding any
other connections.  So, try running the executable directly,
with the Delphi project closed down.  It's my bet this is your
problem.  Don't make the mistake of treating embedded as a development
option.  It's intended for deployment.  Use the full server for
development and watch out that you don't hard-compile the connection
protocol.

Another cause could be that your renamed client can't load because a
DLL named fbclient.dll is already loaded - just not the one you need!

Helen

Re: [firebird-support] unpacking backup with firebird version 1.5 (windows 7)

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello robert,

Tuesday, July 25, 2017, 4:32:12 PM, you wrote:

> When I try to unpack it using the following command:

> c:\Program Files\Firebird..\bin\gback.exe -c VAS.bak VAS.fdb

1. Make sure the server is running.
2. You need the file path and, if using Classic mode, the hostname.

> This produces errors:

> gbak: Error: unavailable database VAS

> gbak: Error: failed create database VAS

> gbak: Error: Exiting before completion due to errors.


> when I do the same on my linux box

> sudo gbak -c VAS.bak vas_db/VAS.fdb

> this seems to work fine (alltough i can not use the result)

> can somebody please tell me, how to unpack that file on windos?

Let's assume you want the database to be in a directory called
"vas_db" and you have created this directory on drive D.  For
simplicity, let's say you have placed the backup there as well.

The backup is just a FILE so you don't need the host name for that.
You are restoring to a DATABASE and for that you need a host name on
Classic.

You want

gbak -c d:\vas_db\/VAS.bak localhost:d:\vas_db\VAS.fdb -user sysdba
-password yourpwd

The default sysdba password on a new installation is masterke, in case
you don't know that already.

The drive MUST be one that is physically connected to the host box.
It can't be a share or anything like that.

Helen

Re: [firebird-support] Firebird Connection String

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Lee,

Sunday, July 23, 2017, 5:28:05 AM, you wrote:

> Am working on an old VB6 project and need to connect to some Firebird Data.
> Having problem Connecting to database when it's a network address.

[..]

That's because Firebird (by design) does not connect to databases on
network drives.  The database must be on a drive that is physically
connected to the machine that is hosting the Firebird server.

> If my data is on network share at \\MyNetShare\data\sprox.gdb then
> what value do I use for dbServer and dbFileName?

None.  From a remote client, use the server's host name or internal IP
address with the file path or (better) a database alias that you have
created in aliases.conf (pre Fb3) or databases.conf (Fb3 onwards).
Here's an example of an alias entry:

sampledb = d:\data\sprox.gdb
(no quotes)

> Tried dbServer="" and dbName="\\MyNetShare\data\sprox.gdb" and it
> works but only if running program As Admin (Win10)

> Any suggestions???
On Windows, you can use either the Windows networking protocol (WNET)
(noisy, outdated)

\\hostname\d:\data\sprox.gdb
\\hostname\sampledb

or TCP/IP (preferred):

hostname:d:\data\sprox.gdb
hostname:sampledb

There are optional elements for more complicated connections, too.  You
should find them in the Quick Start Guide, in the \doc\ folder of your
server installation or in the Documentation library at the Fb we site.

Note, there is a setting you can configure in firebird.conf to enable
access to network drives but it should never be used to attempt access
to a read/write database.  It's a recipe for corruption.

Kind regards,
 Helen Borrie

Re: [firebird-support] Firebird 1.5.6 not work with locla protocol

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Friday, April 21, 2017, 10:29:01 PM, blumy2...@yahoo.com wrote:

> I install Firebird 1.5.6 with administrator rights, WIn 10, x64,  but i have 
> a problem
> I can not use local protocol on my PC!

> All my firebird's application connect with FDB database, only i use
> run as administrator for my app...without Run as admin...a received 

> Unknown username or password
> Unavailable database

> BUTif a replace local protocol with COMPNAME:, if a
> use TCP protocol..al my apps work with normal rights + Run as admin...TCP 
> protocol works well

> Where is the problem??

As Dmitry said, you are using a very old Firebird with a modern
operating system.  Firebird uses an inter-process communication
channel for local connection on Windows. Before Fb2 it was a subsystem
known as 'IPServer'.  From Fb 2 forward, it is XNET and it is
different from its predecessor.

In the early days of Fb 2.0, XNET connections had the same problem on
Win Vista. The workaround at the time was to configure the parameter
'IpcName' in firebird.conf from its default value to use a global
access route to access XNET, changing

#IpcName = FIREBIRD

to

IpcName = Global\FIREBIRD

I don't think it would work with Fb 1.5 IPServer but it could be worth
trying.

In firebird.conf for Fb 1.5, you have

#IpcName = FirebirdIPI

So try changing it to

IpcName = Global\FirebirdIPI

It is case-sensitive. Notice that you have to delete the '#' marker.

Remember to stop and restart the Firebird server after the change.

Also make sure that your applications are using the correct version of
fbclient.dll for Fb 1.5.6.  You can copy it from the \bin\ directory
of the server into the directory where the application .exe runs.

Helen

Re: [firebird-support] Firebird issue.

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Sunday, March 5, 2017, 12:27:11 AM, soimah.r...@yahoo.com wrote:

> Firebird 2.1 and 2.5 i have installed as normal.Then when i open my
> entrypass( software for access card reading and programing) software
> ( this software im using requires firebird server and client) it say
> firebird database engine not found. How to solved this issue.

There is not enough information here to give you an exact solution.
What operating system is running on the server?

Broadly, the cause of your problem is probably a combination of
1) not uninstalling Fb 2.1 properly; and
2) not installing Fb 2.5 properly

The solution depends on the operating system and the model of Fb 2.5
you want to run (Superserver, Classic or Superclassic).

HB

Re: [firebird-support] Problems with database events

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Sandris,

Thursday, March 2, 2017, 5:24:03 AM, you wrote:

> Hello!


> Error occurs if i am trying to register database events using 3.0.2. 
> fbclient.dll to firebird 3.0.2. on linux.


> "Failed to establish a secondary connection for event processing."


> No error if server is on windows or server is on linux and using 2.5 client.

It is likely to be a firewall issue.  In firebird.conf:

#
# The TCP Port Number to be used for server Event Notification
# messages.  The value of 0 (Zero) means that the server will choose
# a port number randomly.
#
# Type: integer
#
#RemoteAuxPort = 0

So you will need to open a port (recommend a high number, e.g., 13050)
in the firewall of your Linux host and apply that number to
RemoteAuxPort.  Don't forget to restart the server after saving the
config.

If that doesn't work, you could post a Tracker ticket to have it
looked at before Fb 3.0.2 is released.

HB

Re: [firebird-support] Migrating a 2.5 server

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, February 7, 2017, 9:35:55 AM, I wrote:

> 1. 'SQLSTATE = m' is not a valid error message in Firebird. You should
> be seeing a 5-character alphanumeric code where the 'm' is. Other
> DBMS's  use the same name for their SQL command-line apps so make sure
> you are running isql from the same location as the binary.

I forgot to remind you to preface the isql command with dot-slash if
you're running it from the binary directory.  If you don't, you're
going to get MySQL's isql regardless.

H.

Re: [firebird-support] Firebird Migration from version 1.5 to version 2.1 help please !!!!

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, February 2, 2017, 12:07:37 PM, I wrote:


> Using Firebird's command-line tools:
> gstat -h path\to\database will return the first two.
> isql path\to\database -user sysdba -password masterkey
>   will return the default character set.

I omitted to add that you will get the default character set when
connected with isql using this command:

SHOW DATABASE;

HB

Re: [firebird-support] Firebird Migration from version 1.5 to version 2.1 help please !!!!

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Thursday, February 2, 2017, 3:20:10 AM,  d.picciotto wrote:

> I have some Trouble with the Migration of Firebird vers. 1.5 to Firebird 
> vers. 2.1

> First of all I made a Backup of my Database File *.gdb with the function gbak 
> using isql

No, you didn't.  Isql is an SQL language support program, not a backup
utility.  Gbak is a stand-alone program, not a function.  (The same
code can, however, be accessed through Services API calls...but it
doesn't sound as though you were attempting that.)  It looks as though
you possibly used isql -x to extract the metadata to a script.  That
is not a backup.

> Then I disinstalled the Old Version >> Firebird 1.5 because the
> Files won't be Updated and not Overwritten

You didn't need a full uninstall, as Firebird 2.1 would be written (by
default) to a separate sub-directory under Program Files\Firebird.

What you do need to do is stop the Firebird service, in the Services
applet.  Then you would run 'instsvc.exe r' (without the quotes) from
the \bin directory to remove the Firebird 1.5 service.

> Is it enough to disinstall the 1.5 Version from the Control Panel in Windows ?

Probably.

> Then I export The Metadata from *.gdb file as a SQL File using a Tool like 
> IBExpert
> How should I apply this Metadata to Firebird 2.1
> Just by running it on FlameRobin as a query or in isql ?
> Doesn't make any Sense to me
> When I run the script I get following error:
>
> Error: *** IBPP::SQLException ***

> Context: Statement::Prepare(
> /**/

> /*   Generated by IBExpert 2017.1.22.1 24/01/2017 16:44:44  */

> /**/

> SET SQL DIALECT 3 )

> Message: isc_dsql_prepare failed
> SQL Message : -104
> Invalid token
> Engine Code: 335544569
> Engine Message :
> Dynamic SQL Error
> SQL error code = -104
> Token unknown - line 5, column 5
> SQL

OK, that error refers to the right-bracket character in the SET SQL
DIALECT statement.  A syntax error - it should not be there.  The fact
that the metadata script is trying to set the dialect to 3 indicates
that the DATABASE is dialect 3.  In order for the script to create the
metadata, it needs to set the CLIENT dialect to 3, to match the
DATABASE dialect. That is what that statement was trying to do.  If
you intend to go by this route, it will be necessary to open the
extract script in a text editor and look for things like this.  

> Something wrong with the DIALECT 3 should it be DIALECT 1 or DIALECT 2 
> instead ?

Before you try anything, first find out what you are working with.
Use your preferred tool(s) to get the database header information.
The details you need are:  ODS version, database dialect and default
character set.

Using Firebird's command-line tools:
gstat -h path\to\database will return the first two.
isql path\to\database -user sysdba -password masterkey
  will return the default character set.

A database created or restored under Firebird 1.5 should have an ODS
of 10.1.  When you restore it under v.2.1 it should have an ODS of
11.1.
IBExpert probably has a tab that shows all of this information in one
place.


You do not need to pump the data UNLESS (one or both):
- the database dialect is 1 and you want it to be 3 (apparently not
necessary in this case)
- the default character set is NONE and you know that it is actually
in a directly supported character set.  In that case, you would need
to pump the data into an empty database with the desired default
character if you wanted to realise the true charset of the text data.

Otherwise, you need to:

1. Back up the v.1.5 database using the gbak.exe from the V.1.5
distribution.  If you no longer have it, you can download the v.1.5.6
zip kit from website and extract the program.

NOTE, you must make this backup using the v.1.5 gbak.exe.  You can run
this program under your v.2.1.7 installation.  Either (a) temporarily
reanme gbak.exe in your v.2.1.7 installation and copy the v.1.5
version to the \bin directory;  or you can run the v.1.5 gbak.exe from
a different location.

2. Restore the backup file using the **v.2.1.7 version*** of gbak.exe.
Regarding the notes above about database dialect, you might run into
some transliteration problems.  I won't go into that here;  but you
can read up about in the v.2.1.7 release notes. Gbak has some switches
you can use.

> Reading the Documentation i should create an Output File errors.txt
> to see the execution exceptions/errors
> With which Procedure/Function will I bring the errors in that File

I guess you are referring to script execution.  It's not clear.  But
you can pipe the screen output of isql to a file, as you are
apparently aware.  You can do the same with gbak screen output.

> Next I should apply the Database Patch and I immagine it's
> compatible with the Version 2.1 right ?

> Can I find the Patch here ??

I don't know what that means, sorry.  I've 

Re: [firebird-support] firebird schema changes during backup

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello ,

Monday, January 23, 2017, 11:08:40 PM, kragh.tho...@yahoo.com wrote:

> On a product of ours we use continuous deployment where code and
> database schema changes is deployed quite often. Sometimes we would
> like to make a new release during database backup. So my questions are:


> 1) How does Firebird handle schema changes during backup?

Presumably you are talking about gbak backup.

gbak runs in its own snapshot transaction, so all committed metadata
changes at the start of that transaction will go into the backup.

> 2) And is it safe to make schema changes during backup?
>
Well, it is "safe", insofar as it won't affect the state of the
metadata during backup.  But, of course, those changes won't appear in
the restored database.

> 3) Are procedures/tables locked for changes during backup?
>
During normal database operation, changes to metadata are not "locked"
but, for some object types, the new versions won't be available to new
transactions until the db cache is cleared.  Typically this affects
triggers, stored procedures and stored functions.  The extent of that
depends on whether the Fb model is using private or shared cache.

As far as gbak is concerned, since it is copying metadata, not
executing any PSQL, the backup won't be affected by this deferred
availability, provided the changes were committed when its transaction
started.

For all that, I don't think it is a good idea to be changing metadata
during a backup.  It's too easy for human error to put the states of
the current database and the backup out of synch inadvertently.
Still, it just boils down to the controls the team has in place to
make sure that what's in the backup really is what it is supposed to
be...

HB

Re: [firebird-support] FB 2.5 migrate to 3.0

[Helen Borrie hele...@iinet.net.au [firebird-support]]

> Thanks for your answer. I think I got help from you also during last time 
> migration test. :-)
> I tested it with your advice but it still doesn't work.

I am not convinced you have that authentication configured properly
nor that your application is really loading the Fb3 client library.

Some reality checks:

1. Make sure that you deleted the # symbol when you configured
AuthServer.

2. It might be worthwhile to change the ordering of the list in
AuthClient, so that Legacy_Auth appears first.

2. Get rid of SRP from the UserManager list.  Make it so that
Legacy_UserManager is only entry.

3. Don't forget to save firebird.conf, and to restart Firebird.

4.  Put fbclient.dll in the same folder as your application.  It would
probably be a good idea to reboot the client machine, while you are at
it, in case the old version is still loaded.

> I would like to test it with a fresh installation of FB 3.0 later.

> I should make my description more clear for my steps.


> 0. First of all, all applications are running in Windows 7. FB x32 versions 
> are used for testing.
> 1. Use gbak (FB 2.5) to backup Firebird 2.5 database file.
> 2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file.
> 3. Use isql, to run: SQL> alter user sysdba set password 'masterkey';
> respond: Database: myimport,  bsp;User: SYSDBA

Without the modifications to firebird.conf, that step operates on the
SYSDBA that is configured under UserManager SRP.  It will NOT work
with applications connecting via Legacy_UserManager.

You have to create SYSDBA for use with Legacy_UserManager.  You need
to use the USING PLUGIN clause, as indicated in my previous posting.

> 4. Open new database file in IBExpert with FB 3.0 client library 
> (fbclient.dll), it works.
> 5. Run my own applicatoin with FB 3.0 client library which works
> with FB2.5 client library, error message shows below:
> [FireDAC][Phys][FB]Your user name and password are not defined. Ask
> your database administrator to set up a Firebird login.

> 
> Connection string inside my code
> 
>   object dbcMain: TFDConnection
> Params.Strings = (
> ;   'DriverID=FB'
>   'User_Name=sysdba'
>   'Password=masterkey')
> 

But it will not work if either (or both)
1) the application is loading the Fb 2.5 client
2) firebird.conf configuration is a mismatch with the client

BTW, you are not REALLY using masterkey for your SYSDBA password, are
you?

HB

Re: [firebird-support] FB 2.5 migrate to 3.0

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Saturday, January 7, 2017, 3:02:03 PM, James wrote:

> I am trying to migrate my database from 2.5 to 3.0 now.

> Now I show all my steps  p;I have done for migration procedure.

> 1. Use gbak (FB 2.5) to backup Firebird 2.5 database file.
> 2. Use gbak (FB 3.0) to restore backuped FBK file to a new database file.
> 3. Use isql, to run: SQL> alter user sysdba set password 'masterkey';
> respond: Database: myimport, User: SYSDBA

This alters the user SYSDBA under the default UserManager, which is
SRP

> 4. Open new database file in IBExpert, it works.

IBExpert is apparently using the new client library.  You don't say
what platform you are on but, if it is Windows, the client library is
fbclient.dll.  On Linux, it is libfbclient.so.

> 5. Run my own applicatoin which works with FB2.5, error message shows below:
> [FireDAC][Phys][FB]Your user name and password are not defined. Ask
> your database administrator to set up a Firebird login.

If your application is connecting via the v.2.5 client library then it
doesn't know about SRP.  You'll need to go back to isql and create the
SYSDBA user for the Legacy_UserManager plug-in, viz.,

CREATE USER sysdba PASSWORD 'masterke' USING PLUGIN Legacy_UserManager;

Also look at firebird.conf, to make sure that Legacy_UserManager is
available.  This will probably mean changing from the default
settings, if you did not do that previously:

AuthServer = Srp, Legacy_Auth

UserManager = Legacy_UserManager

Don't forget to stop and restart the Firebird server to enable your
config changes.

On the other hand, if you want your application to use the new
security features, make sure that it is loading the FB3 client
library.  If the app is 32-bit, you will need the 32-bit client, even
if your server is running 64-bit Firebird.

HB

Re: [firebird-support] Re: FB3: Default create database location

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Dmitry,

Friday, December 30, 2016, 10:20:11 AM, DY wrote:

> 29.12.2016 23:05, Helen Borrie hele...@iinet.net.au wrote:
>>
>>> On windows systems, if you create a database without path specification,
>>> firebird tries to create it in the windows/system32 directory.
>>
>> No; it will use the current directory

> By default, for any Windows service (including Firebird running as a 
> service) the current directory is %system%.

With 2.5.6 on Win 10, it is as I described:  I was running isql in
Firebird's bin directory and the database was created there.

The story is similar with Fb3:  when DatabaseAccess is reverted to
Full, the database is written to Firebird's root dir, where isql now
resides.  Tested with Fb 3.0.1 release.

HB

Re: [firebird-support] FB3: Default create database location

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Wednesday, December 28, 2016, 11:19:24 PM, you wrote:

> On windows systems, if you create a database without path specification,
> firebird tries to create it in the windows/system32 directory.

No; it will use the current directory, e.g.,
C:\windows\Programs64\Firebird\Firebird_2_5\bin
in my setup for 2.5.6 on Windows 10 because I created the test
database in isql, while that directory was current.  It would be
strange if windows/system32 were the current directory for a CREATE
DATABASE operation.  Are you using some tool that is installed in that
directory?


> In FB2.5.x, you cat set the DatabaseAcces configuration parameter in 
> firebird.conf, and then this is used instead of windows/system32. 
> However, this feature seems to be lost in FB3.0 ... or there is another
> way to specify the default path for new databases?

It seems you have missed some steps.  On 3.0.1 on my 64-bit Win 7 box
it works exactly as it should.  Did you do all the steps?

1. Edit firebird.conf (it won't work in databases.conf):

Default
# DatabaseAccess = Full

Change to
DatabaseAccess = Restrict e:\databases;$dir_sampleDb

2. Save firebird.conf

3. Go to the Services applet and restart the Firebird 3 server - MUST DO THIS!!!

4. In Firebird 3 root directory:

isql -user blah -password blahblah

5. CREATE DATABASE 'TEST123.FDB';

6. CREATE TABLE TEST1 (ID BIGINT, DATA VARCHAR(25));

7. Look in e:\databases:  you will find TEST123.FDB there, as it is
the first location defined for DatabaseAccess.

HB

Re: [firebird-support] Re: NEXT VALUE FOR on Firebird 3.0.1 32bit Windows 7 32bit

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Roland,

Wednesday, December 21, 2016, 10:39:50 PM, Roland Turcan wrote:


> But  still, why this problem occurred? Why didn't some generators have
> it =1?

A generator that has never been triggered has a value of 0, not 1.

In the context of your complaint that NEXT VALUE FOR seemed to return
a wrong value, Dmitry suggested you query the column
RDB$GENERATOR_INCREMENT in RDB$GENERATORS, to check whether the
problem might be caused by 0 or null in that field.  If you didn't do
that, and you think you still have a problem, then look at it.

HB

Re: [firebird-support] Re: NEXT VALUE FOR on Firebird 3.0.1 32bit Windows 7 32bit

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Roland,

Wednesday, December 21, 2016, 9:11:36 PM, Roland Turcan wrote:

> But I cannot call this:

> update RDB$GENERATORS set rdb$generator_increment = 1 where
> rdb$generator_name not starting 'RDB$';

System Tables are read-only in Fb 3.

> how can I set the value to 1?

Read the release notes, page 71 (pdf page 81) and all will be
revealed.  (On-line:
http://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-ddl-enhance.html#rnfb30-ddl-altersequence

HB

Re: [firebird-support] Re: Setting up firebird for visual studio

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello Aleksa,

Thursday, December 15, 2016, 7:39:49 AM, you wrote:

> Last week I am trying to set up my new computer for Visual Studio
> programming with Firebird databases. Problem is I tried everything
> in my power but when i try to connect to Firebird database via
> Server Explorer in VS, window for Firebird just shut down.

> These are things I already tried:

[..]

As this is a driver question concerning the ADO.NET providers, you
need to post it in the firebird-net-provider list.

To subscribe there, go to this URL:

http://www.firebirdsql.org/en/mailing-lists/

Scroll down to the Drivers section and click on "Subscribe directly".

Helen (Moderator)

Re: [firebird-support] connecting to an existing firebird database using new user id/pw

['Helen Borrie' hele...@iinet.net.au [firebird-support]]

i have a database created and access on Firebird for years.
i've always used the id/pw sysdba/masterkey
all i want to do is:
change my delphi IBO Objects tIBConnection.username to 'mynewuser'
and .password to 'mynewpassword'
i am not sure what to use in .SQLROLE?  i've tried 'RDB$ADMIN'  and leaving it 
blank 
on the computer with the database? using gsec i've run:
gsec -user sysdba -pass masterkey -add mynewuser-pw mynewpassword -admin yes

< p="">now,
Helen Borrie: Don't know what you are expressing here, but a password is a 
string and double quotes are not valid characters in that string.

when i try and connect the TIB_Connection, i get this error message:
"could not open tableA table
isc error code 335544352
no permission for read/select access to table tableA"
Helen Borrie: It means exactly what it says.  You new user does not have the 
required privileges on tableA and obviously you didn't log him/her in with the 
RDB$ADMIN role.  You have to include that in the login credentials to access 
the privileges associated with it.  That form of granting the RDB$ADMIN role 
makes it available to the user in all databases, which might not be your 
intention.  Better to GRANT ROLE RDB$ADMIN TO  for a specific login, 
e.g., when the user is going to perform a Services operation such as gbak and 
make another role granting specific privileges on specific tables.

no surprise, i've got a customer that doesn't want to use the sysdba/masterkey 
connection id/pw 
Helen Borrie: I like it when customers clearly know what they're doing.  The 
whole world knows 'masterke' as the default password for full destructive 
access to Interbase and Firebird databases.  

so all i'm trying to do is add a new one...
i've google the heck outta this and can't figure it out.
Helen Borrie: Why not just read the documentation available on the Firebird 
website?  There's a user manual for gsec there.  There's also a language ref 
with the CREATE USER and ALTER USER syntax, for servers 2.5 and above and 
databases ODS 11.2 up.  If your database and/or server version is older, the 
RDB$ADMIN role isn't supported...well, AFAIR, some higher versions of 2.1.x did 
so, possibly due to a back-port.  In any case, use SQL privileges rather than 
throwing superuser privileges around willy-nilly.
Anyway, if you do have RDB$ADMIN support available, put it in the SqlRole 
property of TIB_Connection for any connection for which he needs it.  If you 
don't, then work out a nice neat scheme of roles.
what am i missing?
Helen Borrie: It's going to be of benefit to get your head around the 
differences between destructive rights and SQL privileges.  Note, too, that it 
is possible to grant privileges to a user directly.  My advice is to use roles 
ALWAYS, as it keeps the privilege in nice neat packages that you can apply on a 
per-connection basis.  It can drive you insane trying to sort out a privilege 
scheme that was thrown together as a melee of user-directed and role-directed.  
One does not overlap the other and it is easy to create a nightmare.


HB

Re: [firebird-support] Update Description

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Hello ,

LtColRDSChauhan wrote:

> In Firebird 3.0 I'm not able to update Description of Table /
> Stored Procedure through flamerobin.

WHEN you read the Fb 3 release notes, did you notice how often it was
mentioned that the system tables are now read-only?

To populate rdb$description in any table and wherever it is available
in other object types you need the COMMENT ON statement.  See
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-comment.html#fblangref25-ddl-comment-create

Incidentally, by this means your text remains persistent.

HB

Re: [firebird-support] reset autoincrement field

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Nico Speleers wrote:

> How can I reset an autoincrement field in firebird 3 ? I used
> identity to make an autoincrement field.

In your Fb 3 installation is the /doc/ directory, where you will find
the release notes in PDF form.  IDENTITY is documented on page 69
(page 79 by the PDF numbering), including the syntax for making the
(otherwise inaccessible) generator restart with a different value.

Syntax works ONLY with an IDENTITY column.

To re-initialise to zero:

alter table aTable
alter column aTable_ID RESTART

The next number generated will be 1.

To re-intialise to some other number:

alter table aTable
alter column aTable_id RESTART WITH n

The next number generated will be (n+1).

Remember to COMMIT if you are not using isql with autoddl on.

HB

Re: [firebird-support] Firebird 2.5.6 Newbee question

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Tuesday, November 22, 2016, 8:48:05 AM, ratdog1...@msn.com wrote:

> I am new to Firebird.  I have downloaded Firebird 2.5.6 (Client,
> Classic Server and SuperServer versions) but when I install them on
> my MAC, I cannot find any files even though my MAC tells me that the
> programs have been successfully installed.

> I am running OS 10.9.5 (Mavericks, I believe) hence the older version of 
> Firebird.

If you're not also a newbie on MacOSX, you'll know about frameworks.
https://developer.apple.com/library/content/documentation/MacOSX/Conceptual/BPFrameworks/Concepts/WhatAreFrameworks.html
Firebird installs in a framework:
/library/Frameworks/Firebird.framework

> I simply want to try and use the open-source software to create
> some databases.

You have two pinnacles to surmount here.  First, you need to know
which model of Firebird you installed (Classic, Superclassic or
Superserver) and get yourself connected to a database.  There's a
horrible but usable sample database called employee.fdb in
./Resources/examples.  In ./Resources/bin there is a command-line
application called isql;  or (I think) there is a Mac version of the
FlameRobin GUI client, which might be more use to you. Google it.

Note, if you installed Classic you need to have launchd installed and
running.

Some user documentation is in ./Resources/doc.  Start with the Quick
Start guide.  You'll need a PDF reader. The release notes also have
detailed documentation of new features added in v.2.5 but they are in
no sense the full story.  Use the resources in the Documentation zone
at the website.

The "God" user is SYSDBA, password 'masterke', which is known to the
world.

> I am moderately experienced in SQL, having used
> both Oracle and Microsoft Access in the past.

Once you get your head around what you're doing at the nuts-and-bolts
level, you can study the Firebird 2.5 Language Reference either online
at the website Documentation zone or download the PDF from there.
Firebird's SQL is highly standards-compliant so it has much more in
common with Oracle than with Access, which is a Microsoft invention
and about as non-standard as you'll find.

> Can anyone give me some pointers on what I am doing wrong?  I would 
> appreciate any help at all.

Probably at this stage the only thing you are doing wrong is avoiding
the documentation. ;-)

Helen

Re: [firebird-support] Digest Number 9396

[Helen Borrie hele...@iinet.net.au [firebird-support]]

Monday, November 21, 2016, 8:37:50 PM, tiberiu_horv...@yahoo.com wrote:

> This is how I create my temporary tables :

> create global temporary table TEMP_112233  
> (
>   id : INTEGER,
>   name : CHAR(20)
> )
> on commit preserve rows  

You should create your GTT only once in your life, not every time you
want to use it!  An instance of your GTT will be created as soon as
the client inserts some data into it.  An instance of this GTT will
survive as long as this client remains connected and no other client
can see this instance.

> I also do some create views :  

> CREATE OR ALTER VIEW CAUT_PRODUSE_VIEW_4982590
> (
> COD_PRODUS,
> ID_PRODUS
> )

> AS
> select 
>produse.cod_produs,  
>produse.id_produs 

> from produse  
>inner join sortiment on (sortiment.id_sortiment + 0 = produse.id_sortiment)
> where 

>denumire_sortiment <> 'N'

OK, but that JOIN criterion is strange. Do you not want the optimizer
to use the index on sortiment.id_sortiment if it would be useful? This
syntax deliberately blocks use of that index.

A view is not a temporary table.  Effectively, it is a "stored query"
that you define in order to get a pre-defined data set each time you
select from it or join to it.

> I do not know about any domain definitions, this should be an internal 
> FireBird thing.


> if I have RDB$1 defined this way : 


> CREATE DOMAIN RDB$1 AS INTEGER


> whenever I create another table with an Integer field, this domain should be 
> used, am I wrong ?

Yes: you are quite wrong.  You should not create any domains with
names that start 'RDB$' because Firebird uses that prefix to create
its internal domains, which are not intended for use by humans.  If
you want to use domains (a very good idea!) you should create your own
domains with useful names.  Do not "borrow" Firebird's internal domains.

> is there a command

> create table TEMP_112233  
> (
>   id : INTEGER use domain RDB$1
> )

This is what you do.  Suppose you want a domain called ID_BASE of type
integer that you want to use for creating primary keys everywhere.
You define this domain ID_BASE with the attributes you need for any
primary key.  You probably have some other types you would like to
have on hand, too.  For example,

CREATE DOMAIN D_ID_BASE INTEGER NOT NULL

CREATE DOMAIN D_INDICATOR CHAR(3)
   COLLATION ISO8859_2
   CHECK VALUE = UPPER(VALUE)

CREATE DOMAIN D_SHORT_DATA VARCHAR(99)

Now, you have have 3 domains - think of them as custom data types, if
you like.  Just use the domain name like a data type when you create
the columns that you want having those attributes:

create table aTable (
  id D_ID_BASE,
  description D_SHORT_DATA,
  status D_INDICATOR,
  {..other columns..},
  constraint pk_atable primary key(id)
  )

  You can also use these 'custom data types' in stored procedures,
  triggers, executable blocks and, in Fb3, stored functions, e.g.,

  create procedure ...
  ...
  ...
  declare aVariable D_INDICATOR;
  declare bVariable TYPE OF D_SHORT_DATA; <-- gets the data type
  without the other attributes

  The Firebird 2.5 Language Reference has a lot of detail about
  domains, GTTs and everything else about Firebird's SQL language.
  You need it. ;-)

  
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/Firebird_Language_Reference_25EN.pdf

  or you can study it on-line in html:

  
http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html

  Helen