Re: [firebird-support] UPDATE OR INSERT in Firebird 3
On 25-11-2020 10:13, 'River~~' river14ap...@gmail.com [firebird-support] wrote: [..] > I am also interested to know if you find my syntax works on v2.5, if > you still have it installed to test it? Please don't go to the trouble > of re-installing it just to test it. > > It is arguable that v3 is correct in rejecting the syntax you have > previously used, because it does not make logical sense to tell the > database engine to insert a null when you mean something else. The > syntax I suggested will be more portable to other db engines because > it is closer to the standard. That is not the problem, the solution Walter used in Firebird 2.5 would work in Firebird 3 as well. The problem is that Walter switched from auto-increment columns generated by a trigger, to using identity columns. When you use a trigger, you can explicitly handle null to generate a value, but when using an identity column, then you either have to leave out the column so it is generated, or you need to specify a non-null value. Mark -- Mark Rotteveel
Re: [firebird-support] Support native JSON datatype for columns as MySQL / PostgreeSql
On 13-10-2020 18:42, 'P-Soft - Fabio Codebue' f.code...@p-soft.biz [firebird-support] wrote: > > > Support native JSON datatype for columns as MySQL / PostgreeSql > > Some idea when this feateures will be scheduled? > Or we are not be interested on it? There is a ticket in the tracker: http://tracker.firebirdsql.org/browse/CORE-5148 But it is not planned for any version so far. Mark -- Mark Rotteveel
Re: [firebird-support] is it a bug?
The firebird-support Yahoo Group has moved to Google Groups at https://groups.google.com/d/forum/firebird-support. Please subscribe and post your question there. Mark On 2020-07-28 15:23, hamacker sirhamac...@gmail.com [firebird-support] wrote: > If I try in FB3 (latest version):CREATE > USER 'ROBOT.CUSTOS' -- wont run > PASSWORD 'password' > FIRSTNAME 'ROBOT' > MIDDLENAME 'DE' > LASTNAME 'CUSTOS' ; > > Not run! but If I try: > CREATE > USER "ROBOT.CUSTOS" -- it´s run > PASSWORD 'password' > FIRSTNAME 'ROBOT' > MIDDLENAME 'DE' > LASTNAME 'CUSTOS' ; > > I think that double quotes are used only if you want case sensitive, > but I see that only method to create login with "." and maybe others > separators.
Re: [firebird-support] error when trying to open databases 2.5 on server 3.0.5
On 25-06-2020 16:46, 'Ismael L. Donis Garcia' sli...@natio.co.cu [firebird-support] wrote: > Is it possible to create databases on a 3.0.5 server with a user other > than sysdba? > when i try in flamerobin create a database with another user it gives me > the following error: > Context:Database::Create > Message:isc_dsql_execute_immediate failed > SQL Message:-551 > This user not have privilege to perform this operation on this object > Engine Code :335544352 > no permission for CREATE access to DATABASE /mnt/ccs/test.fdb See also https://firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/qsg3-databases.html#qsg3-databases-creating-nonsysdba -- Mark Rotteveel
Re: [firebird-support] error when trying to open databases 2.5 on server 3.0.5
On 26-06-2020 21:08, 'Ismael L. Donis Garcia' sli...@natio.co.cu [firebird-support] wrote: > I don't know if I will be such a novice, but I can't find the language > reference manual for firebird 3 It is documented in the release notes: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/rnfb30-access-sql.html#rnfb30-security-metadataprivs PS. Please move this discussion to the new firebird-support list on Googlegroups. -- Mark Rotteveel
Re: [firebird-support] error when trying to open databases 2.5 on server 3.0.5
You have sent your email to the old list, please post to the new list at firebird-supp...@googlegroups.com (or https://groups.google.com/d/forum/firebird-support). If you're not subscribed yet, you can do it on above link, or by sending an email to firebird-support+subscr...@googlegroups.com (if your email address is not associated with a Google account, make sure to confirm by replying to the email). Mark On 2020-06-25 16:46, 'Ismael L. Donis Garcia' sli...@natio.co.cu [firebird-support] wrote: > Is it possible to open database 2.5.9 on servers 3.0.5? > > when I try to open it with flamerobin it gives me the following error: > > Context:Database::Connect > Message:isc_attach_database failed > > SQL Message:-820 > wrong or obsolete version > > Engine Code :335544379 > Engine Message: > unsupported on-disk structure for file /mnt/ccs/libsc.fdb; found 11.2, > support 12.2 > IProvider::attachDatabase failed when loading mapping cache > > Is it possible to create databases on a 3.0.5 server with a user other > than sysdba? > > when i try in flamerobin create a database with another user it gives > me the following error: > Context:Database::Create > Message:isc_dsql_execute_immediate failed > > SQL Message:-551 > This user not have privilege to perform this operation on this object > > Engine Code :335544352 > no permission for CREATE access to DATABASE /mnt/ccs/test.fdb
Re: [firebird-support] SQL Error 303
The Firebird-support mailing list has moved from Yahoo Groups to Google Groups, https://groups.google.com/d/forum/firebird-support (or subscribe by emailing to firebird-support+subscr...@googlegroups.com and confirm by replying to the confirmation request). Could you please subscribe to the list on Google Groups and repost your question to that list? Mark On 2020-05-27 11:56, 'Check_Mail' check_m...@satron.de [firebird-support] wrote: > Hello, > > we have some problems since migration VC++ 2005 to VC++ 2016. The same > code, the same fbclient.dll but now we get the error after connect: > > Dynamic SQL Error > > SQL error code = -303 > > Implementation of text subtype 205 not located. > > What could it be? The charset is ISO8859_1, Firebird 3.0 superserver, > no UDFs. > > Thank you. Best regards. > > Olaf
Re: [firebird-support] Re: IMPORTANT MESSGAGE: This group is moving
On 05-05-2020 02:25, DougC d...@moosemail.net [firebird-support] wrote: > I tried to use the new google group and have it send you an invite to > join the group. What Google responded with was: > > Some email addresses cannot be added to this group because their > accounts are disabled or are blocked from Google Groups. > > So Google does not like your email address for some reason. According to https://answers.yahoo.com/question/index?qid=20131030040143AACtioj: """ Unfortunately, the issue is on your friends side. She has disabled the option to allow Moderators or Groups to 'Invite' her to a group. She will need to go into her email settings and change it to allow it again. """ In other words, if this emailaddress is associated with a Google account, then on groups.google.com under My settings (first icon top-right) > My global settings under "Add/Invite settings" the option "Allow group managers to invite me to their groups" is disabled. Mark -- Mark Rotteveel
Re: [firebird-support] Next attachment ID
On 2020-05-04 10:34, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > Hi > > How can i read Next Attachement ID? > > I know that i can read it by gstat -h but how to read it from > connection? > > Is there something simple like for OAT – read from MON$DATABASE or > API isc_transaction_info? > > Regards, > > Karol Bieniaszewski Hi Karol, could you repost to firebird-supp...@googlegroups.com? Mark
Re: ODP: [firebird-support] IMPORTANT MESSGAGE: This group is moving
On 03-05-2020 10:39, Karol Bieniaszewski liviusliv...@poczta.onet.pl [firebird-support] wrote: > I have joined but i cannot change name to show instead my email name. > > I have followed hint on the group to go to settings – but to change this > settings i must login. I do not have google accout to login. > > How to change it? You cannot change it if you have subscribed with an email address that is not associated with a Google account. And as far as I'm aware, it will use the display name of your email address when actually posting a message. Mark -- Mark Rotteveel
Re: [firebird-support] IMPORTANT MESSGAGE: This group is moving
On 02-05-2020 11:38, Lester Caine les...@lsces.uk [firebird-support] wrote: > On 02/05/2020 00:29, Helen Borrie hele...@tpg.com.au [firebird-support] > wrote: >> Unfortunately we cannot migrate the current Yahoo! subscribers, and you will >> need to resubscribe yourself, in one of two ways: > > The other problem with pigging Google is their drive to make us use a > Gmail account. They had removed my own email address and given me a > @gmail.com one which is what they have just tried to resubscribe me with > it IS possible to retain a real email address, but personally I was > trying to kill of using Google AT ALL because of the crap they keep > pushing. SO if things don't improve I will be simply closing my google > account completely! You don't need a Google account to join. Email to firebird-support+subscr...@googlegroups.com and confirm the join request by **replying** to the join request. But yes, if your email address is associated with a google account, then Google will default to your gmail address, even if you subscribed from a different address. To fix this you need to go to https://groups.google.com/forum/#!myforums, find the group, click **Edit** after your name, and change "Email used for membership" to your preferred address. Mark -- Mark Rotteveel
Re: [firebird-support] new Google groups
On 2020-05-01 02:20, Hamish Moffatt ham...@risingsoftware.com [firebird-support] wrote: > The firebirdsql.org site lists new Google groups to replace the yahoo > firebird-support and firebird-general lists, but the links don't work - > I get permission denied errors trying to visit them. > > Are these new lists set up and ready to go? I believe that right now those groups haven't been configured with the right (public) visibility yet. Mark
Re: [firebird-support] FB1 to FB3
On 2020-04-30 17:42, Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support] wrote: > I always tend to do back / restores not only in this migration but on > a regular basis. I’m maybe a little on the paranoid side. :) > I have noticed some extra lines in the restore log for FB3 (this is > true even on a completely new FB3 database): > > gbak:fixing views dbkey length > gbak:updating ownership of packages, procedures and tables > gbak:adding missing privileges > gbak:fixing system generators > gbak:finishing, closing, and going home > gbak:adjusting the ONLINE and FORCED WRITES flags > Those are just steps that gbak will apply, even if there is 'nothing to fix'. > > The script is very easily generated out of IB Expert. I cannot praise > this tool enough. > > I haven’t tried the script using isql but I would be surprised if it > didn’t work. I will give it a shot possibly over this coming weekend. > I’m slammed at the moment. > > I also use FlameRobin for FB1. Not sure if that is compatible with > FB3? There were some concerns in previous postings. FlameRobin works with Firebird 3, but it doesn't support the new boolean datatype, and it has no support for packages and PSQL function, which might incorrectly show procedures from packages, or functions. In addition, user management should now be done through SQL, so the user management options in FlameRobin will still work, but they will only work with the first (default) UserManager. There might be some more issues, but for general query work it will work fine. Mark
Re: [firebird-support] Re: Technical IT specialist Firebird corruption
On 18-04-2020 18:56, Shane delphi_ric...@yahoo.co.uk [firebird-support] wrote: > > > Yes sorry, this is from Linux. Since I'm a Linux Enthusiast . I did that > script, but it did produce errors. > > http://www.firebirdfaq.org/faq141/ This is the backup script I used. > > This page I used for the restore string > http://www.destructor.de/firebird/gbak.htm. But Like I said Errors where > produced with restoring, like size error Please be explicit with what you tried and the exact errors you get. Mark -- Mark Rotteveel
Re: [firebird-support] Technical IT specialist Firebird corruption
On 17-04-2020 13:35, Nick Upson nick.up...@gmail.com [firebird-support] wrote: > I've had a similar issue when the database code was looking for custom > UDF's that had not been installed on the destination, in some ways that > can appear as corruption even though it isn't In Firebird itself that would result in an error like "invalid request - is not defined - module name or entrypoint could not be found" error. Unless of course the application in question is hiding that error and returning unclear errors. Mark -- Mark Rotteveel
Re: [firebird-support] Technical IT specialist Firebird corruption
On 18-04-2020 12:19, Shane delphi_ric...@yahoo.co.uk [firebird-support] wrote: > The scenario that I've setup is Opensuse is the main OS. I'm running > Windows XP on a VirtualBox. So within Windows XP I communicate to the > Linux OS through samba, copying to the folder the database is running. I > do the systemctl stop to Firebird on the Linux Server then restart after > it's finished copying . What exactly are you copying? How are you moving the database from Windows to Linux, what are the exact steps and commands used? If you were using gbak to backup and restore, there would be no need to stop Firebird on your Linux server. So it sounds like you are copying the database file itself, instead of using gbak to back it up on Windows and then - using gbak - restore it on Linux. > I did use the gbak on all the backups. I even tried Flamerobin to > restore resent backups. which it didn't give any errors. > But it does want to be registered and the Defin support person says it > corrupted!!! Then possibly this might be a problem with the application and not so much with the database. You would need to contact support of your application for more help. Mark -- Mark Rotteveel
Re: [firebird-support] List Users after CREATE/DROP USER without commit
On 11-04-2020 11:41, Lukas Gradl fireb...@ssn.at [firebird-support] wrote: > Hi @all! > > I'm trying to implement some sort of user manager into an app and want > to use the firebird-users and roles as user and authentification > source (using FB 3). > > Most of it works perfectly. There's just one little problem: > If I create or drop a user via CREATE USER or DROP USER and do a > "SELECT * FROM SEC$USERS" afterwards the change is not visible - the > new user is missing, the dropped user is still in the list. > > I'm using the same transaction for all the commands but still I have > to issue a COMMIT for seeing the changes in the user list. > > Now I'd love to use the transaction-mechanism for giving the user of > my app a possibility to revoke the changes made. > > Can this be achived somehow? No, it cannot. Creation of the user happens in a different database, and changes in that database aren't visible until the commit happens (at which time the commit in the security database happens as well). Mark -- Mark Rotteveel
Re: [firebird-support] Forgot sysdba password
On 2020-04-09 18:05, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > On 2020-04-09 09:53, liviuslivius liviusliv...@poczta.onet.pl > [firebird-support] wrote: >> Hi >> >> Simply replace security3.fdb by ine from the zip install and >> initialize sysdba > > Doing that will lose all other users in the security database, so that > is not a good idea. > > Instead, stop Firebird server, and use > > isql -user sysdba employee > > and then > > alter user sysdba password 'new password'; > commit; > exit; And of course, start Firebird server again. Mark
Re: [firebird-support] Forgot sysdba password
On 2020-04-09 09:53, liviuslivius liviusliv...@poczta.onet.pl [firebird-support] wrote: > Hi > > Simply replace security3.fdb by ine from the zip install and > initialize sysdba Doing that will lose all other users in the security database, so that is not a good idea. Instead, stop Firebird server, and use isql -user sysdba employee and then alter user sysdba password 'new password'; commit; exit; Mark
Re: [firebird-support] error during the registratione of data on 2.5 FB database
On 04-04-2020 13:01, 'Stellarancia.com' ni...@stellarancia.com [firebird-support] wrote: > No, there is not a trigger. > > You say than the error is referred to another part (with mistaken value) > of the entire script... but the compiler reports me the part that > (maybe) run ok?!? How this is possible? That is not what I said. Please read what I wrote again. > Ok, print the script and study it. I suggest that you do the work to make it as easy for us to help you. That means creating a minimal reproducible example that does not require any other tools than ISQL to reproduce the problem. Very likely while doing that, you will already find the problem yourself. Mark -- Mark Rotteveel
Re: [firebird-support] error during the registratione of data on 2.5 FB database
On 04-04-2020 10:49, 'Stellarancia.com' ni...@stellarancia.com [firebird-support] wrote: > He, > I've an error that I don't undestrand. > I use Delphi 7 environment to create my applications, Zeos components > for connections and Firebird 2.5 Superserver for database management. > To register the data, my App uses the processor component; the > Connection.Autocommit parameter is set to False. > My App inserts numerous lines of data to be recorded in the Process > component (with Process.Script.Add('...') -> 'UPDATE XY SET...'; INSERT > INTO ZXD...'; UPDATE FGH SET ...'; and so on); > at the end, it adds the command "COMMIT;", then call "PARSE", and "EXECUTE". > The lines 4 and 5 of the processor script contains the data mistaken, > but I don't undestand where is the eror. > The exception raised by the server is: > --- > > SQL Error: conversion error from string "". Error Code: -413. Overflow > occurred during data type conversion. > The SQL: INSERT INTO ANA_DIPE_PRESORD (COD_REC, MATR, DATORE, POSIZ, > MESE, ANNO) > VALUES (30030003004601, 3, 46, 1, 03, 2020) > ; > > -- > This is the table declaration: > -- > > CREATE TABLE ANA_DIPE_PRESORD > ( > COD_REC Bigint NOT NULL, > ANNO Smallint DEFAULT 0, > MESE Smallint DEFAULT 0, > MATR Smallint DEFAULT 0, > DATORE Smallint DEFAULT 0, > POSIZ Smallint DEFAULT 0, > STATO Char(1) DEFAULT '' > CONSTRAINT PK_DIPPPREORD > PRIMARY KEY (COD_REC) > ); > > CREATE UNIQUE INDEX IND_ANADPPREORD_COD ON ANA_DIPE_PRESORD (COD_REC); > GRANT DELETE, INSERT, REFERENCES, SELECT, UPDATE > ON ANA_DIPE_PRESORD TO SYSDBA WITH GRANT OPTION; > > -- > > The Table has no trigger, no generators, no stored procedures connected. > The data in "COD_REC" must be unique. > > No data are sent as '' (void string), neither any value is sent as > "Quoted string", > because they are all of type smallint (and bigint). > I'm going crazy. > It's stupid, but I've changed the value for field 'MESE' from '03' to > '3', but the result is the same. > What can be the reason? > Thanks for reply. Do you have a trigger on the table? Given the STATO column has a default of '', maybe a trigger tries to convert that to a number somewhere? Also, are you absolutely sure this is the statement that causes the problem. Your trying to execute a script, which Firebird itself does not support, so your component is doing some transformations, which might mean that the error is not on the line that you think it is. Try executing a single statement instead of a script. I'd recommend trying to reproduce it in ISQL, and if possible provide us with a minimum reproducible example Mark -- Mark Rotteveel
Re: [firebird-support] Firebird vs Intebase
On 26-03-2020 11:37, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 26.03.2020 11:35, Tommi Prami tommi.pr...@suonentieto.fi [firebird-support] > wrote: >> Form Embarcadero marketing matriel > > Already was discussed here a couple of months ago. Look into archives. Which archives? Mark -- Mark Rotteveel
Re: [firebird-support] Firebird 4.0.0-1436 beta 1
On 2020-03-25 13:24, Gregor Kobler gregorkob...@yahoo.com [firebird-support] wrote: > Hello Mark > > Yes I am aware of that, but I have no experience with the snapshots. > How do I make the current snapshot work. I run the file > "Install_service.bat". The Firebird service is running, but when I try > to connect, the following message appears > > Install incomplete, please read the Compatibility chapter in the > release notes for this version. That is because your security database hasn't been initialized yet for the SRP authentication plugin. This isn't new, the same error would occur with Firebird 3. To fix this you need to create a SRP user. To do that, stop the firebird service, run isql -user sysdba employee create user sysdba password ''; exit; And start the Firebird service again. Mark
Re: [firebird-support] Firebird 4.0.0-1436 beta 1
On 2020-03-24 10:47, Gregor Kobler gregorkob...@yahoo.com [firebird-support] wrote: > Hello > > I try a little bit with the FB4 beta 1 > > two errors i got > 1. When i try to connect with my 64Bit program and using the > fbclient.dll (64Bit) my program crashes! when i use the 64bit dll from > FB3 it works, also when i use the 32bit version with my 32bit program. > I used the dll from "C:\Program Files\Firebird\Firebird_4_0" > > 2. When i try to execute a SQL-Statement like "select * from > mon$database" if got the follow error with EMS-Firebird Manager > Unknown SQL Data type (32754). > > And my program catches a exception with eurekalog like the follow > 2.5 Type : EIBNativeException > 2.6 Message : [FireDAC][Phys][FB]Incorrect values within SQLDA > structure. > > Has somebody a tip? Thanks If you want to test Firebird 4, consider using one of the snapshots. Firebird 4 beta 1 is already a year old, so testing against it is not really of value to the project, because chances are high some problems are already fixed, and you might miss different, new problems. Mark
Re: [firebird-support] Firebird and Sam Broadcaster
On 22-03-2020 03:06, Audio Buff npage@gmail.com [firebird-support] wrote: > During the install of Sam Broadcaster, there are a few database options, > Firebird being named as the most simple to get started. I used the Firebird > version from the official archive, the latest version. > When selecting Firebird with default options with in the Sam Broadcaster, > it errors; > > [image: image.png] > I've combed through all that I can find in Sam Broadcast forums. Any help > here would be amazing. Thanks. This list filters out attachments. Please provide the error message as text, or link to the image hosted elsewhere. Mark -- Mark Rotteveel
Re: [firebird-support] problemtic User management on firebird 3
The problem is that the order of clauses in your create database statement is wrong. The USER and PASSWORD clauses must follow *immediately* after the `CREATE `. See also [1]. I'm unsure why this doesn't produce a different error though. As a secondary problem, in a create database clause, the password *must* be enclosed in single quotes. Also, granting a user admin role in the security database, only makes that user admin in the security database. It does not grant global admin rights (which is why you need to grant create database as well). As documented in [2]: """ If the GRANT ADMIN ROLE clause is specified, the new user account is created with the privileges of the RDB$ADMIN role in the security database (security2.fdb). It allows the new user to manage user accounts from any regular database he logs into, but it does not grant the user any special privileges on objects in those databases. """ So, steps to do this: D:\DevSoft\Firebird\Firebird-3.0.5.33220-0_x64>isql Use CONNECT or CREATE DATABASE to specify a database SQL> connect 'localhost:employee' user sysdba password 'masterkey'; Database: 'localhost:employee', User: SYSDBA SQL> create user macky password '1234' grant admin role; SQL> grant create database to user macky; SQL> commit; SQL> create database 'localhost:e:\db\testcreatedb.fdb' user macky password '1234' default character set utf8; SQL> [1]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-db.html [2]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-security-auth.html#fblangref25-security-auth-create-user On 14-03-2020 18:07, Yannis Kozikopoulos jk...@freemail.gr [firebird-support] wrote: > this is a new installation no existing databases no legacy code to support. > > After installing the 64bit server on windows 7 machine file name > Firebird-3.0.5.33220_0_x64.exe then do the following > > c:> "c:\Program Files\Firebird\3_0\isql.exe" > Use CONNECT or CREATE DATABASE to specify a database > SQL> connect localhost:employee user sysdba password masterkey; > Database: localhost:employee, User: SYSDBA > SQL> create user macky password '1234' grant admin role; > SQL>quit; > > now I'm not interested in using sysdba for anything else just create my > own super user and use that for everything. > so next step create a new database > > c:> "c:\Program Files\Firebird\3_0\isql.exe" > Use CONNECT or CREATE DATABASE to specify a database > SQL> create database 'localhost:D:\data\firebird\macky.fdb' default > character set utf8 user macky password 1234; > Statement failed, SQLSTATE = 28000 > Your user name and password are not defined. Ask your database > administrator to set up a Firebird login. > SQL>quit; > > What the F? ok lets test that > > c:> "c:\Program Files\Firebird\3_0\isql.exe" > Use CONNECT or CREATE DATABASE to specify a database > SQL> connect localhost:employee user macky password 1234; > Database: localhost:employee, User: MACKY > SQL> select Sec$User_name from sec$users; > > SEC$USER_NAME > === > MACKY > > SQL> quit; > > erm there is something I'm missing here lets try to grant the database > create rights to the macky > > C:> "c:\Program Files\Firebird\3_0\isql.exe" > Use CONNECT or CREATE DATABASE to specify a database > SQL> connect localhost:employee user sysdba password masterkey; > Database: localhost:employee, User: SYSDBA > SQL> grant create database to macky; > Statement failed, SQLSTATE = 0A000 > unsuccessful metadata update > -GRANT failed > -feature is not supported > -Only grants to USER or ROLE are supported for CREATE DATABASE > SQL> grant create database to user macky; > SQL>quit; > > OK now that should do the trick, lets test it. > > C:> "c:\Program Files\Firebird\3_0\isql.exe" > Use CONNECT or CREATE DATABASE to specify a database > SQL> create database 'localhost:D:\data\firebird\macky.fdb' default > character set utf8 user macky password 1234; > Statement failed, SQLSTATE = 28000 > Your user name and password are not defined. Ask your database > administrator to set up a Firebird login. > SQL> > > I give up. any idea what I'm doing wrong here? for now I'm going to use > one of the free gui admin tools in embedded mode but if the user > management and stored proc thingy doesn't get better any time soon I'll > drop firebird altogether. > > thank you for your time. Your efforts are greatly appreciated regardless > of outcome. -- Mark Rotteveel
Re: [firebird-support] Ubuntu 3.0.5 version
On 13-03-2020 14:49, Jorge Andres Brugger jorge.brug...@gmail.com [firebird-support] wrote: > Mariuz's Ubuntu repository contains version 3.0.5.33100 and was released > in October 2019. The official version on the Firebird website is > 3.0.5.33220, released in January 2020. This is ok in terms of stability, > if I want to use the Ubuntu package? No, 3.0.5.33100 is an unreleased snapshot version of 3.0.5 and that means that by definition it is unstable. There were 120 builds between that snapshot and the official 3.0.5 release (3.0.5.33220), that is a lot of builds and a lot of changes and fixes. Also, build 33100 is from February 2, 2019[1], not from October 2019, so it is a lot older than you think. Mark [1]: https://github.com/FirebirdSQL/firebird/commit/51e28472f78e109fc1475799a3c12f5d2f900f69 -- Mark Rotteveel
Re: [firebird-support] mapping destroyed by backup / restore
This sounds like a bug to me. Can you create bug report in the tracker at http://tracker.firebirdsql.org/browse/CORE Mark On 09-03-2020 03:50, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] wrote: > > > Hi – I have another question re user mapping… > > If I create a new database with a user->role mapping, I can back up and > restore it and the mapping is still present. > > But if I back up the new copy and restore that, the mapping is gone… is > there an issue with the procedure I'm using or is this a bug? > > (for context, we have Firebird databases here that have inherited a > regular backup/restore cycle from when we were using InterBase 5 or 6… > apparently they had a tendency to slow down if not refreshed this way, > but that is from before my time!) > > Here's a demonstration using Firebird 3.0.5: > > _1. Create database with mapping_ > > PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe > > Use CONNECT or CREATE DATABASE to specify a database > > SQL> create role foo; > > SQL> create mapping sjd_foo using plugin win_sspi from user > "foodstuff\pgmrsd1" to role foo; > > SQL> exit; > > _2. Check mapping___ > > PS C:\Users\PGMRSD1> c:\apps\firebird\isql.exe > C:\temp\mapping-backup-restore\FOO.FDB > > Database: C:\temp\mapping-backup-restore\FOO.FDB, User: PGMRSD1 > > SQL> show users; > > Users in the database > > 1 #PGMRSD1 > > SQL> show roles; > > FOO > > SQL> show mapping; > > SJD_FOO USING PLUGIN WIN_SSPI FROM USER foodstuff\pgmrsd1 TO ROLE FOO > > *** Global mapping *** > > TRUSTED_AUTH USING PLUGIN WIN_SSPI FROM ANY USER TO USER > > SQL> exit; > > _3. Back up database – verbose output shows mapping saved___ > > PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -b > C:\temp\mapping-backup-restore\FOO.FDB > C:\temp\mapping-backup-restore\FOO.FDB.bak > > gbak: time delta reads writes > > gbak: 0.104 0.104 40 0 readied database > C:\temp\mapping-backup-restore\FOO.FDB for backup > > gbak: 0.107 0.002 0 0 creating file > C:\temp\mapping-backup-restore\FOO.FDB.bak > > gbak: 0.112 0.004 0 0 starting transaction > > gbak: 0.115 0.003 21 1 database > C:\temp\mapping-backup-restore\FOO.FDB has a page size of 8192 bytes. > > gbak: 0.118 0.003 0 0 writing domains > > gbak: 0.122 0.003 6 0 writing shadow files > > gbak: 0.123 0.001 0 0 writing character sets > > gbak: 0.125 0.001 0 0 writing collations > > gbak: 0.127 0.001 1 0 writing tables > > gbak: 0.128 0.001 0 0 writing types > > gbak: 0.131 0.002 5 0 writing filters > > gbak: 0.132 0.001 2 0 writing id generators > > gbak: 0.134 0.001 3 0 writing exceptions > > gbak: 0.135 0.001 2 0 writing functions > > gbak: 0.137 0.001 2 0 writing stored procedures > > gbak: 0.139 0.002 2 0 writing packages > > gbak: 0.141 0.001 2 0 writing triggers > > gbak: 0.143 0.002 0 0 writing trigger messages > > gbak: 0.145 0.002 12 0 writing security classes > > gbak: 0.148 0.002 2 0 writing table constraints > > gbak: 0.150 0.002 0 0 writing referential constraints > > gbak: 0.152 0.002 1 0 writing check constraints > > gbak: 0.154 0.001 0 0 writing SQL roles > > gbak: 0.155 0.001 0 0 writing SQL role: FOO > > gbak: 0.157 0.002 0 0 writing names mapping > > gbak: 0.159 0.001 3 0 writing map for SJD_FOO > > gbak: 0.199 0.002 104 1 total statistics > > _4. Restore database – verbose output shows mapping preserved___ > > PS C:\Users\PGMRSD1> c:\apps\firebird\gbak.exe -v -stat tdrw -c > C:\temp\mapping-backup-restore\FOO.FDB.bak > C:\temp\mapping-backup-restore\BAR.FDB > > gbak: time delta reads writes > > gbak: 0.004 0.004 0 0 opened file > C:\temp\mapping-backup-restore\FOO.FDB.bak > > gbak: 0.219 0.214 0 0 transportable backup -- data in XDR > format > > gbak: 0.222 0.002 0 0 backup file is compressed > > gbak: 0.224 0.001 0 0 backup version is 10 > > gbak: 0.362 0.137 0 666 created database > C:\temp\mapping-backup-restore\BAR.FDB, page_size 8192 bytes > > gbak: 0.365 0.003 0 2 started transaction > > gbak: 0.366 0.001 0 0 restoring SQL role: FOO > > gbak: 0.368 0.001 0 0 restoring names mapping > > gbak: 0.370 0.002 0 0 restoring map for SJD_FOO > > gbak: 0.375 0.004 0 13 creating indexes > > gbak: 0.402 0.027 0 34 committing metadata > > gbak: 0.404 0.002 0 10 fixing views dbkey length > > gbak: 0.407 0.002 0 0 updating ownership of packages, > procedures and tables >
Re: [firebird-support] Re: Cannot delete Firebird database file as it is in use by the application..
On 2020-03-10 22:04, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > Hello... > > I am developing a security extension, which is used as a loaded > assembly to my main application, all of which use the Firebird > Embedded Edition 2.59 for my application's database. > > As part of my security processes, I compress the file upon exiting > the application. When the compression process is completed, I want to > delete the Firebird FDB database file. However, I cannot do this as > the Firebird database file is in use by the application. > > I have checked all of my data access coding and in every case the > associated database connection is being properly closed upon > completion of any database access method. > > As a result, I have no idea why the Firebird FDB database file is > still being used by the master application. > > Does anyone have any ideas as to how I can get around this so I can > complete the delete process? IIRC, you are programming in C#, right? The Firebird ADO.net provider uses a connection pool, so although the logical connection used by your application maybe closed, the physical connection is still open. You can close unused connections in the pool using FbConnection.ClearAllPools() (or alternatively, use the connection property to not pool the connections). Mark
Re: [firebird-support] Firebird client connection timeout
On 2020-03-10 20:14, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > Hummm, I think Dimitry is right. I made some tests here on client > side: > > server IP: 10.0.2.2 > path: d:\database.fdb > > Using connection string: 10.0.2.2:d:\database.fdb Correct IP and > correct path, the connect is made instantly > > Using connection string: 10..0.2.2:d:\data.fdb Correct IP and wrong > path, instantly my application recognizes the error, and a error > message appears. > > Using connection string: 10.0.2.21:d:\database.fdb Wrong IP and > correct path, the application freezes for about 45 seconds, and just > after that the error message appears. > > So, even though this is not a Firebird issue, do anyone has a tip to > solve this delay on windows? Should it be set on the server or on the > clients? > > Thanks in advance. Maybe this can help: https://serverfault.com/questions/193160/which-is-the-default-tcp-connect-timeout-in-windows Mark
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 17:44, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 10.03.2020 15:57, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] > wrote: >> On 10-03-2020 15:46, Andrei Luís compuvale.softw...@gmail.com >> [firebird-support] wrote: >> > No, there wasn't. I copied fbclient.dll and firebird to the application >> > folder. Still the same! >> >> Well, as far as I know, this should work. > > No, it shouldn't and never could. TCP connection timeout is a completely > OS thing, no > Firebird settings can change it. Then the setting shouldn't be called ConnectionTimeout in my opinion. In any case, why can't Firebird do this, when for example Java has no problem with using a connection timeout for sockets? Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 15:46, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > No, there wasn't. I copied fbclient.dll and firebird to the application > folder. Still the same! Well, as far as I know, this should work. Is your Windows 32 bit or 64 bit, and is your application 32 bit or 64 bit? If your windows is 64 bit, but your application is 32 bit, it will probably be using a fbclient.dll in C:\Windows\SysWOW64 (which is for 32 bit libraries on Windows 64 bit), and in that case you will need to put the firebird.conf there. Also make sure that the setting is not prefixed with a #, which would make it a comment. And a final check question: is your application actually using fbclient.dll (and not for example Jaybird or the Firebird ADO.net provider, or some other connection library that doesn't use fbclient.dll). Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 15:40, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > Just did that. Still the same. Check if your client application has its own fbclient.dll, and put a firebird.conf in the same directory as that fbclient.dll. Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 15:33, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > > > Server side > fbclient.dll = C:\Program Files (x86)\Firebird\Firebird_2_5\bin and > c:\Windows\SysWOW64 > firebird.conf = C:\Program Files (x86)\Firebird\Firebird_2_5 > > > Client side > fbclient.dll = C:\Program Files\Firebird\Firebird_2_5\bin and > c:\Windows\System32 > firebird.conf = C:\Program Files\Firebird\Firebird_2_5 Put a firebird.conf with the timeout setting in C:\Windows\System32. Your client application is unlikely to use the fbclient.dll in your Firebird install, but instead will use the fbclient.dll in C:\Windows\System32, and so the firebird.conf file needs to be in that location. Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 15:21, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > Sorry for my lack of infos, I read your previous message before post my > message here. I'm testing this in the my dev machine, I configured > ConnectionTimeout in the firebird.conf file in the physical machine, and > configured it in the virtual machine I use (VirtualBox) too. > > In this scenario the physical machine is the server, and the virtual > machine is the client, right? Well, I tested again now, and running my > application in the server, the 5 secs timeout is working fine. But > inside the virtual machine, the timeout is not working as I set. It is > taking about 45 secs to timeout. > > Is there something I'm doing wrong? Thanks for your time. Where is your libfbclient.so (or fbclient.dll), and where is the firebird.conf file? Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 10-03-2020 14:41, Andrei Luís compuvale.softw...@gmail.com [firebird-support] wrote: > I'm having the same problem. Already set ConnectionTimeout = 5 in > firebird.conf, restarted the FB service, and the connect try is going so > far from that. I'm using FB 2.5. Is there anything else to set? To repeat my previous post: """ ConnectionTimeout isn't a server setting, it is in the section with the header "Client Connection Settings (Basic)". However, to apply to a fbclient, it must be configured in a firebird.conf in the same directory as the fbclient.dll/libfbclient.so """ Or in other words: changing the config on the *server* has no effect, because you must change the config of the *client*. The settings in firebird.conf are not only for the server, but some of them are (also or only) configuration options for the client. However, for those settings to apply to the client, they must be in a firebird.conf that is actually read by the client, which usually is not the firebird.conf of the server. Mark -- Mark Rotteveel
Re: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?
On 2020-03-06 04:46, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] wrote: > Aha, you've got a typo! > > In your create mapping statement (and verified by the output of show > mapping), you have "WINSSPI" instead of "WIN_SSPI". > I encountered the same issue when I accidentally typed "WIN_SPPI". > It seems that the plugin name is not validated against those currently > available (possibly for good reason) and the mapping is happily > created and then ignored. Good find! I assume plugin names are not validated because available plugins depend on configuration and available plugins on connect time, and for example global mappings don't necessarily know which actual plugins are available when connecting to a specific database. Though it might be helpful if a warning was issued when specifying an unknown plugin name. Mark
Re: [firebird-support] AW: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?
On 2020-03-05 13:38, 'Mathias Pannier (unitel)' pann...@ubsysteme.de [firebird-support] wrote: > I can’t get it to work… > > The user “MYDOMAIN\ADMINISTRATOR” is a member of the AD group > “MYDOMAIN\MY_GROUP” > > ROLE_TEST was created using this statement: CREATE ROLE ROLE_TEST; > > The mapping was created with the following statement: CREATE MAPPING > MY_MAPPING USING PLUGIN WinSSPI FROM GROUP "MYDOMAIN\MY_GROUP" TO ROLE > ROLE_TEST; Have you tried using single quotes instead of double quotes? Mark
Re: [firebird-support] How to "remap" the count datatype from BIGINT to INTEGER in firebird 3
On 02-03-2020 18:24, 'm...@spectrumpacsold.com' m...@spectrumpacs.com [firebird-support] wrote: > > > Hello, why do we keep getting so many copies of this email? Thanks. Looking at the headers of the emails, Yahoo Groups is repeatedly sending the emails. According to the headers, it was sent only once to Yahoo, but servers inside Yahoo are repeatedly sending that email. Mark -- Mark Rotteveel
Re: [firebird-support] Can we please be able to install *_without_* being the root or firebird user?
On 01-03-2020 07:07, Pól Ua Laoínecháin lineh...@tcd.ie [firebird-support] wrote: > TL;DR - I want to install FB without being the root or firebird user. > Tried some stuff - explained below - didn't work. Anybody got any > ideas? Your question about modifying the build of Firebird would probably be more on-topic on the firebird-devel mailing list than on firebird-support. However, have you considered using docker to run Firebird (eg using https://hub.docker.com/r/jacobalberty/firebird) instead of trying to compile/install from sources yourself? Mark -- Mark Rotteveel
Re: AW: [firebird-support] Scalability of connection numbers of client-server solution with Firebird 3.0?
On 19-02-2020 11:59, 'Mathias Pannier (unitel)' pann...@ubsysteme.de [firebird-support] wrote: > Firebird has no limit. But Windows Sockets. > > http://www.firebirdfaq.org/faq292/ That is actually a limitation in Firebird SuperServer and SuperClassic on Windows, and in Firebird 3 and 2.5.3 it was raised to 2048 connections, see http://tracker.firebirdsql.org/browse/CORE-4439. Mark -- Mark Rotteveel
Re: [firebird-support] RE: How can I use/map Active Directory Groups within my Firebird in Trusted Authentication mode?
On 28-02-2020 05:13, FSPAPA INCA Team i...@foodstuffs-si.co.nz [firebird-support] wrote: > I'm also using Firebird 3 (3.0.4), with a new test database containing its > own security database. > > The setup is similar: > > create global mapping trusted_auth using plugin win_sspi from any user to > user; > create role foo; > > Mapping a single user to the role works: > > PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe -u sysdba foo > Database: foo, User: SYSDBA > SQL> create mapping sjd_foo using plugin win_sspi from user > 'foodstuff\adm_sdrake' to role foo; > SQL> exit; > > PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe localhost/3051:foo > Database: localhost/3051:foo, User: FOODSTUFF\ADM_SDRAKE, Role: FOO > SQL> quit; > > But mapping a group does not: > PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe -u sysdba foo > Database: foo, User: SYSDBA > SQL> drop mapping sjd_foo; > SQL> create mapping inca_foo using plugin win_sspi from group > 'foodstuff\RBAC_INCA Support' to role foo; > SQL> exit; > > PS C:\Users\adm_sdrake> d:\apps\firebird\isql.exe localhost/3051:foo > Database: localhost/3051:foo, User: FOODSTUFF\ADM_SDRAKE > SQL> set trusted role; > Statement failed, SQLSTATE = 0P000 > Your attachment has no trusted role > SQL> quit; > > I've tried multiple groups that I'm a member of, with and without the domain > prefix, but no luck. > > Any clues… is this sort of mapping supported? According to the documentation in doc\sql.extensions\README.mapping.html it should be supported. However, I have never used it, and I don't have a domain to experiment on. Maybe Alex Peshkoff knows more, and otherwise I'd suggest creating a ticket in the tracker, because either something is broken, or the current documentation is insufficient. Mark -- Mark Rotteveel
Re: [firebird-support] How to "remap" the count datatype from BIGINT to INTEGER in firebird 3
On 28-02-2020 20:11, Roberto Vieweg jjw.roberto.fireb...@gmail.com [firebird-support] wrote: > Is possible to use the SET BIND feature > (https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.set_bind.md) > from Firebird 3 to remap the datatype of a count statement from BIGINT > to INTEGER? > > If yes, can you provide details and example how to do this? SET BIND is a new feature in Firebird 4, so you cannot use it in Firebird 3. In Firebird 4, you can do this with `SET BIND OF BIGINT AS INTEGER`. > Ps.: I have a very big old-application written in Delphi 7, with a lot > of TSQLQuery with hardcoded count queries mapped to TIntegerField. So > is impossible to do a migration right now. I need other options You could use casts, though I'm surprised that there is no automatic coercion within Delphi. Mark -- Mark Rotteveel
Re: [firebird-support] How to "remap" the count datatype from BIGINT to INTEGER in firebird 3
On 28-02-2020 21:33, Roberto Vieweg jjw.roberto.fireb...@gmail.com [firebird-support] wrote: > The problem of use CAST, is the result of the same work to convert the > TIntegerFields, so as so is to much work! > > I cannot use SET BIND? This command really exists? You cannot use it in Firebird 3, because it is new in Firebird 4. Mark -- Mark Rotteveel
Re: [firebird-support] double precision remove last 2 numbers
On 2020-02-26 19:41, hamacker sirhamac...@gmail.com [firebird-support] wrote: > When I started my test, I turn off my app. > > And do update(and select) using only ISQL tool (IBExpert) and > dbweaver. > in both I had to configure select to show float using mask > (0.##..), so the number can be different that I show you, but > always eat last numbers.I need to store long decimals numbers maybe > more than 15 decimals, is it possible without store as string? Firebird 3 supports a maximum precision of 18 using the fixed point types `NUMERIC(18,x)` and `DECIMAL(18,x)`, however then you most now the number decimals at declaration time. If you need floating-point-like behaviour, you need to wait for Firebird 4, where you can use datatype DECFLOAT (precision of 34), or maybe the extended precision DECIMAL/NUMERIC with a maximum precision of 38. Storing as string is an option, but that will make it very hard to do any calculation with them, and might force you to cast to `double precision` to perform that calculation, which throws any benefit of the stored precision out of the door. Mark
Re: [firebird-support] double precision remove last 2 numbers
On 2020-02-26 19:07, hamacker sirhamac...@gmail.com [firebird-support] wrote: > Hi All, > > I do this update: > UPDATE CV SET > perc_lucro=42.503064336972848 > WHERE (id_cv=57528) > > Then I do a select and 'perc_lucro' was change to 42,5030643369728 > after updated, two last numbers disappear. > Why? > perc_lucro is double precision type. > FB 3.0.5 32bits, Windows 64. A double precision is not an exact numeric type, it has roughly a precision of 15-17 decimals. Even that isn't entirely correct because of how a double precision works. In any case, a double precision cannot exactly store 42.503064336972848. However, when testing this (with `select cast(42.503064336972848 as double precision) from rdb$database`), I get a value of 42.50306433697285, which suggest that your software may do some additional rounding or truncation when presenting the value. Mark
Re: [firebird-support] (When to) recalculate statistics ?
On 2020-02-26 06:22, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > Hello Firebird-Group, > > With our device we recalculate statistics ALWAYS when booting up the > system. Now the DB grows bigger this can now take more than 10 minutes > and delays > the time until we can really work with the system. As it is not a > server and we expect the system to be restart from time to time this > is a problem. Recalculating statistics is not a blocking operation. If done correctly (eg in a separate thread or process), your system should be available immediately, with a (minor) impact on performance. > Is there a better way to find out when to recalculate statistics? > - After n inserts? > - After n days? > > And: Is it really such a big performance benefit to recalc the > statistics at all? The statistics in Firebird are pretty basic, and recalculating them daily will not add much value. The resulting statistics will likely be very similar to the previous and not change the optimizers decisions. After a database has been filled with a representative set of data, recalculating statistics might be of value after an order of magnitude change in volume (number of records), or if the selectivity of an index changes significantly (eg if previously an indexed column had only a few different values (low selectivity), and now has a lot of different values (high selectivity)), and even that might be debatable. Mark
Re: [firebird-support] How to set the locale of firebird process in linux
On 13-02-2020 15:11, 'Martin, Nicole (UK, Yeovil)' nicole.mar...@baesystems.com [firebird-support] wrote: > I have a feeling I have been added to a group that isn't right as the subject > doesn't match what I am asking in my email? This is the firebird-support mailing list for community support for the Firebird database system. You didn't ask a question, someone else did and you replied to the thread. As far as I know, it is not possible to subscribe someone else, so you should have subscribed yourself at some point. To unsubscribe, send an email to firebird-support-unsubscr...@yahoogroups.com See the footer of the emails from this list for more information. Mark -- Mark Rotteveel
Re: [firebird-support] ODBC driver with SQLLEN equal to 8
On 2020-01-30 16:02, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 27.01.2020 19:23, Daniel Wenzel danielwenz...@hotmail.com > [firebird-support] wrote: >> So my question is, there's a way of modifying SQLLEN of Firebird ODBC >> driver to match >> unixODBC configuration? > >I would suggest to build it from sources in your environment. It > looks like distributed > binary was built with older ODBC headers and follow older > specifications. Or possibly with wrong definitions for types. But I think this a question for the firebird-odbc mailing list. Mark
Re: [firebird-support] Statement freezes firebird
On 2020-01-24 14:39, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 24.01.2020 14:29, Matthias Winkler spmm...@gmail.com [firebird-support] > wrote: >> I see the very same issue with firebird 3.0.4. Any ideas? > >You made nested loop on 18000*18000 records with sorting on each > itaretion. It cannot > be fast. It sounds to me like this is should be optimized by executing the uncorrelated sub-queries only once. Mark
Re: [firebird-support] Statement freezes firebird
On 2020-01-24 13:29, Matthias Winkler spmm...@gmail.com [firebird-support] wrote: > Hello, > > With Firebird 2.1.7 this statement freezes my firebird server > completely. Firebird will > take 100% CPU. > >> select first(8) ID from TBL_TEST >> where ID not in (select first(2) ID from TBL_TEST order by ID desc) >> order by ID desc > > The used table has about 18`000 entries. > > If I remove ONE of the order statements the query runs fine and takes > around 3s. > > Can somebody explain what execution plan is made for this statement > and > why it might sent my server into 100% CPU? > > Note: The statement here is simplified from my real usecase. The > statement itself might > not look useful. But that is not the point. Firebird 2.1 has been end-of-life for more than five years. Can you reproduce this with Firebird 3.0? Mark
Re: [firebird-support] coding for services: invalid clumplet error
On 2020-01-14 00:40, 'Andrew Zenz' and...@aimsoftware.com.au [firebird-support] wrote: > I have tried various iterations of it, as a BYTE and as Pablo Sanchez > suggests in a later response, a CSTRING, eg: > > spb_buffer BYTE,DIM(128) > > or > > spb_buffer CSTRING(128) > > As a BYTE, I fill it thus: > > spb_buffer[1] = 2! which is also defined as > isc_spb_version, also tried 0010b and 02h I know nothing about Clarion, and Google doesn't give me a lot of results, but are you sure arrays in Clarion are 1-based? Mark
Re: [firebird-support] Is it safe to risk multiple Firebird nbackup -L?
On 13-01-2020 16:36, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > Please see my question here: > https://dba.stackexchange.com/questions/257259/is-it-safe-to-risk-multiple-firebird-nbackup-l > > Quoting here, but wish you will reply at the site, if acceptable to you. > > I'm using Firebird 3.0 nbackup to lock + copy + unlock each night. > > If some steps fail or are skipped e.g. due to system restart (updates), > it may happen that the master is not unlocked (-N). Next night, it will > already be in locked state, but the scheduled backup task will lock it > again (-L). > > Is this safe? > > Will the second -L have any effect, or is it a no-op, so a single unlock > (-N) will restore it to normal state? Or do I have to safeguard against > a second lock (-L) if it's already in locked state? As I also answered on dba.stackexchange.com: Executing nbackup -L when a database is already locked will produce an error (and the executable will return exit code 1 instead of 0): [ PROBLEM ON "begin backup". unsuccessful metadata update -ALTER DATABASE failed -Database is already in the physical backup mode SQLCODE:-607 ] In that case, Firebird (or nbackup) won't change anything in the state of the database. So the locked database will remain locked. Continuing would be fine in theory (as the database is locked), but I don't think it is a good idea to just ignore errors and continue, because it could be a different error than this. So, for proper error checking, you would need to check if the error is "Database is already in the physical backup mode and not some other error". As an aside, you can achieve the same effect with ALTER DATABASE BEGIN BACKUP and ALTER DATABASE END BACKUP[1], which will also work with SuperServer mode (as the nbackup tool needs direct file access, while SuperServer uses an exclusive lock on the file). [1]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-db.html#fblangref25-ddl-db-alter -- Mark Rotteveel
Re: [firebird-support] Database server Clock Incorrect
On 2020-01-09 23:42, Hugo Eyng hugoe...@msn.com [firebird-support] wrote: > Hello. > > Win10 64 and Win7 32 > > FB 2.5 32bits > > When I execute "select current_time from RDB$Database" FB returns 1 > (one) hour more then the current time. > > Did somebody faced this troble? Sounds like the Firebird server is in a different time zone than the client. Or possibly, this is that bug where a DST change isn't noticed by the server, and it continues to use the old time zone. In that case a restart of the Firebird server process should fix it. Mark
Re: [firebird-support] SEC$DESCRIPTION, how to change it?
On 2020-01-07 19:49, hamacker sirhamac...@gmail.com [firebird-support] wrote: > There is a SEC$DESCRIPTION field on security users table, but > CREATE/ALTER USER command does not any parameter to change it, is it > correct? Since Firebird 3, you can use: COMMENT ON USER IS '' This doesn't seem to have been documented in the release notes. Mark
Re: [firebird-support] Need help upgrading win 7 to win 10
On 05/01/2020 15:55, John M buff...@gmail.com [firebird-support] wrote: >> > My DB is very small at < 200MB. Is it better to upgrade to FB 2.5.9 >> > before the win upgrade? If so, what is the best way to do this upgrade? >> > >> > I have spent several hours already googling answers, but none I find >> > seem to give me a clear path. >> >> Upgrading Windows is not really related to upgrading Firebird, which is >> probably why you can't find a clear path by Googling for it. If you had >> kept up-to-date with Firebird 2.5 updates, this question wouldn't even >> have come up. >> >> I would recommend upgrading Firebird before the update, then if any >> problems arise during the Windows upgrade, you know they are not related >> to the Firebird upgrade. You could also do the reverse, but as 2.5.2 was >> built long before Windows 10 even existed, it is better to upgrade to >> 2.5.9 first. >> > ON this point. I didn't find FB upgrade instructions. When I did a > test upgrade of just FB on a Win 10 computer from 2.5.3 to 2.5.9, I > didn't see an "upgrade" option. Should I assume I need to uninstall FB > 2.5.2 and then install FB 2.5.9? Normally, between point releases, uninstalling the old version and installing the new version is sufficient, because the on-disk structure (ODS) is the same between those versions. However, some of the older versions of 2.5 had issues with indexes that require an index rebuild to fix, and backing up and restoring the database is the way to do that. >> When upgrading Firebird 2.5.2 to Firebird 2.5.9, it is probably a good >> idea to use backup and restore, especially when your database was >> created with 2.5.1. >> > I'm pretty sure I switched from IB to FB using 2.5.2. If so, would > this step be necessary? Is there a way to check the DB version? The problem in the earlier 2.5 versions is with the same database version, so checking the database version will not provide sufficient information. However for sake of completeness, you can check the ODS version using: gstat -h ODS 11.2 means it is a Firebird 2.5 database. Mark -- Mark Rotteveel
Re: [firebird-support] Need help upgrading win 7 to win 10
On 04/01/2020 23:04, John M buff...@gmail.com [firebird-support] wrote: > I'm have an very old app that is using FB 2.5.2 on windows 7. As many > know we are being compelled to upgrade to win 10. Will the win 10 > upgrade break during this upgrade? If so, what is the best way to avoid > this issue? Neither Windows 7 nor Windows 10 is a server OS. Consider whether you shouldn't use Windows Server (or switch to Linux). > My DB is very small at < 200MB. Is it better to upgrade to FB 2.5.9 > before the win upgrade? If so, what is the best way to do this upgrade? > > I have spent several hours already googling answers, but none I find > seem to give me a clear path. Upgrading Windows is not really related to upgrading Firebird, which is probably why you can't find a clear path by Googling for it. If you had kept up-to-date with Firebird 2.5 updates, this question wouldn't even have come up. I would recommend upgrading Firebird before the update, then if any problems arise during the Windows upgrade, you know they are not related to the Firebird upgrade. You could also do the reverse, but as 2.5.2 was built long before Windows 10 even existed, it is better to upgrade to 2.5.9 first. When upgrading Firebird 2.5.2 to Firebird 2.5.9, it is probably a good idea to use backup and restore, especially when your database was created with 2.5.1. I also hope you are aware that Firebird 2.5 is end-of-life, so consider upgrading to Firebird 3 as well (though going from 2.5 to 3 needs to be planned and tested more thoroughly than upgrading within 2.5.x). And as always, check the release notes for all changes and fixes between 2.5.2 and 2.5.9: https://www.firebirdsql.org/file/documentation/release_notes/html/en/2_5/rlsnotes25.html Mark -- Mark Rotteveel
Re: [firebird-support] Remote backup using gbak
On 02/01/2020 16:31, Kevin Stanton kevin.stan...@rdb-solutions.com [firebird-support] wrote: > The error was: > > Gbak error: I/O error for file “c:\fbThe system cannot find file specified.” > Gbak error: Error while trying to open file. > Gbak error: The system cannot find the file specified. Try a path without a space in it, or define an alias and use that alias to connect. Also, I think you should use gbak -b -user sysdba -password masterkey -service win2012server:service_mgr "c:/fb databases/lumber/lumber.fdb" "/common/lumber_remote.fbk" That is: - remove win2012server from the database path, you already specified the server in the `-service` - use forward slash instead of backslash However, when you create the backup this way, it will create it on the server. This doesn't seem to be what you want to do. AFAIK, Firebird 1.5 doesn't have streaming backup through the service manager, so you can't make a local backup directly to the client. Mark -- Mark Rotteveel
Re: [firebird-support] Firebird client connection timeout
On 31/12/2019 12:40, Nagy Szilveszter nagy_szilvesz...@yahoo.com [firebird-support] wrote: > Is there a way to set _connection timeout_ for firebird _at client side_? [..] > At server side there is a ConnectionTimeout parameter, but it does not > help me in this case (when i try to connect to an inexistent server, > caused by wrong configuration). ConnectionTimeout isn't a server setting, it is in the section with the header "Client Connection Settings (Basic)". However, to apply to a fbclient, it must be configured in a firebird.conf in the same directory as the fbclient.dll/libfbclient.so Alternatively, in Firebird 3 and higher, you should be able to pass the value in the DPB item isc_dpb_config (which basically takes a linebreak-separated list of key=value with the firebird.conf clientside configuration. There is also isc_dpb_connect_timeout, but as far as I know that only governs the attach handshake, as it is only applied after establishing the socket connection, which makes it unsuitable. Mark -- Mark Rotteveel
Re: [firebird-support] Database/Table Corruption
On 31/12/2019 18:27, Hugo Eyng hugoe...@msn.com [firebird-support] wrote: > Gfix didn´t work even using it as you explained. Please be more specific than "didn't work". What happened, did you get an error, did gfix find nothing wrong? What was the gfix output? Mark -- Mark Rotteveel
Re: [firebird-support] Plan problem en CTE
On 2019-12-30 17:57, Germán Balbi bal...@yahoo.com [firebird-support] wrote: > Hi. > I have this problem in FB 2.5 > > with x as ( > SELECT * > FROM tbl_x1 > JOIN tbl_fk1 ON cond_1 > WHERE cond_w1 > UNION > SELECT * > FROM tbl_x1 > JOIN tbl_fk1 ON cond_2 > WHERE cond_w2 > ) > > select tbl_2.pk_id > from tbl_2 > where tbl_2.pk_id in (select list(x.tbl2_pk_id) from x > > The plan in the X table is OK, but in tbl_2 is natural > > How can I specify the right plan? The condition `tbl_2.pk_id in (select list(x.tbl2_pk_id) from x` will be false unless there is only one row in x. As far as I'm aware, that condition cannot use an index, it can only use natural. What are you trying to do? Maybe using `where exists (select * from x where x.tbl2_pk_id = tbl2.pk_id)` will have the desired effect.
Re: [firebird-support] Database/Table Corruption
On 27/12/2019 20:49, Hugo Eyng hugoe...@msn.com [firebird-support] wrote: > FB 2.5.7 > Windows 10 64 bits > Page size 4096 I'd suggest upgrading to 2.5.9 and see if that fixes the problem. Both in 2.5.8 and 2.5.9, bugs with gbak were fixed. Those bug fixes don't match the description of this problem, but it never hurts to try with a newer version. > I am facing some dificulties to backup and restore a corrupted DB. I > searched the Internet to find some help and tried everthing I read. > > I already tried "gfix -full - mend" and did not work. It should be -mend, not - mend. However, did you read all the caveats about using -mend in the gfix documentation[1] as it can cause loss of data? However, did gfix output any information about problems? Preferably start with gfix -validate -full -no_update then move to gfix -validate -full and only use gfix -mend as a last resort (and make a copy of the database before running it!). [1]: https://firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/gfix-dbverify.html -- Mark Rotteveel
Re: [firebird-support] Re: Securing Firebird Embedded database
On 20/12/2019 16:43, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > I am curious regarding your suggestion to use Firebird Server instead of the > embedded edition. > > Is there a way to silently install the Firebird Server without any user > intervention during my application's installation? See https://github.com/FirebirdSQL/firebird/blob/B3_0_Release/builds/install/arch-specific/win32/installation_scripted.txt Mark -- Mark Rotteveel
Re: [firebird-support] Re: Securing Firebird Embedded database
On 2019-12-18 16:30, Steve Naidamast blackfalconsoftw...@outlook.com [firebird-support] wrote: > Is there any way to secure the FDB file from unauthorized access > (other than compression and encryption, which I am starting to > implement in my project) and if not, is there expected to be at least > password protection for the Embedded edition in Firebird 4.0? If you don't want a user to access a database, then use file system permission to prevent the user from opening the database. Otherwise, you should use Firebird server instead. Firebird 4 Embedded works the same as Firebird 3 Embedded, so there won't be password protection for Embedded in that version. Mark
Re: [firebird-support] unsubcribe
To unsubscribe, send an email to firebird-support-unsubscr...@yahoogroups.com Mark On 2019-12-18 16:34, David Wurdeman david.wurde...@mvpmt.com [firebird-support] wrote: > Please unsubscribe me from this group > > David A. Wurdeman > > Predictive Maintenance Technologies > > 914 East 17th Street > > Columbus, Nebraska 68601 > > 402 563 9609 > > david.wurde...@mvpmt.com > > > > Links: > -- > [1] > https://groups.yahoo.com/neo/groups/firebird-support/info;_ylc=X3oDMTJlZDY0YmlwBF9TAzk3MzU5NzE0BGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwN2dGwEc2xrA3ZnaHAEc3RpbWUDMTU3NjY4MzI2MA-- > [2] > https://groups.yahoo.com/neo;_ylc=X3oDMTJkODdpdTc3BF9TAzk3NDc2NTkwBGdycElkAzI0NDI0MDYEZ3Jwc3BJZAMxNzA1MTE1Mzg2BHNlYwNmdHIEc2xrA2dmcARzdGltZQMxNTc2NjgzMjYw > [3] https://info.yahoo.com/privacy/us/yahoo/groups/details.html > [4] https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] When opening an .FDB file, Firebird says the database is already open by the same computer
On 2019-12-17 15:36, David Wurdeman david.wurde...@mvpmt.com [firebird-support] wrote: > Thank you for the reply > > We use an application called Ascent, which is used to analyze > vibration data from industrial machines. It stores its data in .fdb > files. These files can either be on the local machine, or on a > network share. > > The application requires Firebird to be installed and running on both > the client computer (which also runs Firebird client), and the server > with the shared .fdb databases. Firebird is fully integrated with the > Ascent program.. The only reason for the shared directory on the > server, is to have a central repository for the .fdb files (as in an > office environment). This is simply not how Firebird databases should be handled. Sharing a database on a network drive can result in multiple clients modifying the database simultaneous with insufficient or incorrect locking, which can result in data loss or corruption. > When using the program to access a database on the server, it includes > a connection utility as per below: > > > [cid:image001.jpg@01D5B4B3.9D14CF30] Screenshots are not included on this list. > Just wanted to clarify that it’s not just a simple “accessing .fdb > files via a network share”. The Ascent program is designed natively > to work with Firebird. I think the fact that you need to share databases through a network drive is an indication it wasn't designed properly (or there is a lot more going on behind the scenes inside that application). The correct way to access a Firebird database on a network is through Firebird server. > Again—I’m not a Firebird expert. But did want to give some idea of > what we (and the application) are using it for. > > We were using this program with shared databases on a Windows 7 > computer for years with no problems. The problems started happening > when we upgraded to Windows 10. > > The Ascent program we are using is an older program, and rather > specialized.. The support for it is dwindling. I talked to their > support people, and they don’t seem too knowledgeable about Firebird. > That’s why I sought-out a Firebird support group, in hopes of maybe > getting some advice. Unfortunately, this behaviour is not part of Firebird itself, and therefor is probably a result of what your application does. The fact it works properly on Windows 7 and now has problems on Windows 10 may be a result of changes to network drive support between those versions. However, without intimate knowledge of the application, that is just guesswork. Mark
Re: [firebird-support] When opening an .FDB file, Firebird says the database is already open by the same computer
On 2019-12-16 22:00, David Wurdeman david.wurde...@mvpmt.com [firebird-support] wrote: > We have an application that uses Firebird to open .FDB databases, that > are stored on a shared directory on a server. Database should be 'shared' through a Firebird server instance. Users connect to the database through the Firebird server. What you seem to be doing right now is probably using Firebird Embedded (or locally hosted Firebird server instances) to access database through a shared directory. You should not perform concurrent access to a database through a shared directory, as it is a great way to corrupt things. > When we open a database with the app, there's a message that says: > > “COMPUTER XXX.XXX.XXX.XXX :X” > > “THE SIMULTANEOUS EDITING OF ONE DATABASE BY MULTIPLE USERS MAY > RESULT IN DATABASE ERROR MESSAGES, BUT WON’T CAUSE LOSS OF DATA. > CONTINUE?” > > We can click past the message and still get into the data. > > The IP address in the first line is the IP address of the computer > accessing the database. > > I'm assuming this message is coming from the Firebird server. > > The server is a Windows 10 pc, as is the client. The server is > running Firebird 2.5, and the client v2.1. > > Any ideas on what is causing this? Thanks for any advice! It is not a message from Firebird, so it is probably logic that is part of your software. Mark
Re: [firebird-support] SELECT IN wildcard
On 14/12/2019 06:13, Hugo Larson hugo.lar...@yahoo.com [firebird-support] wrote: > Is it possible to use wildcard somehow like this. > > SELECT * FROM PRODUCT > WHERE PRODUCT.ID IN (:param) > > param will be a list or IDs or a wildcard. > % does not work. > Goal is to get all PRODUCT with wildcard :param Neither will work. `IN` doesn't take wildcards, and if you want to use a list of values, you need to have individual parameters for each value in the list (although some frameworks will allow you to define single parameter and the framework will expand it to the right number of parameters at execution time). If you want to use wildcards, you need to use LIKE (or SIMILAR TO). Using SIMILAR TO you could simulate a list of values by separating them with pipes (|). Mark -- Mark Rotteveel
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 13/12/2019 19:07, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > Is a field I create, or is it part of all records? It is a field you create. Which is why I wrote "Where is the table in question, and is a suitable column (eg a timestamp) to determine what the newest record is." You might be able to use the pseudo-column RDB$RECORD_VERSION, but that is not necessarily in the right order of recency, because the value is the transaction-number that wrote the last version of the record. This means that updates to a record will also make it 'recent' and a record written by a long-running transaction might not show up as 'recent' (or at least as older than it may actually be). Mark -- Mark Rotteveel
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 13/12/2019 16:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > One table contains history. The old history is never replaced. New > history is added. What is relevant is the most recent history. Is there > a way to find the newest records without the KeyID? Yes, you do SELECT * FROM ORDER BY DESC FETCH NEXT 10 ROWS ONLY Where is the table in question, and is a suitable column (eg a timestamp) to determine what the newest record is. If you want that result in ascending order of time, then you need to add another order by: SELECT * FROM ( SELECT * FROM ORDER BY DESC FETCH NEXT 10 ROWS ONLY ) a ORDER BY ASC Or you can use ROW_NUMBER, although that is probably less efficient: SELECT * FROM ( SELECT .*, ROW_NUMBER OVER (ORDER BY DESC) AS ROWNR FROM ) a ORDER BY ASC WHERE ROWNR < 10 Mark -- Mark Rotteveel
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-13 12:48, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > I added the KeyID so I could sort from newest to oldest. Was it > necessary to add this KeyID, or is there another way to find the > newest records without the KeyID? That depends on what you're trying to achieve, what data you have and what results you want. Mark
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-13 08:14, Kjell Rilbe kjell.ri...@marknadsinformation.se [firebird-support] wrote: > Den 2019-12-12 kl. 22:36, skrev Clyde Eisenbeis cte...@gmail.com > [firebird-support]: >> Marcin, Minor tweak => works! I also added DESC to retrieve the >> newest records using the field key name (number is an auto increment). >> ` >> "SELECT FIRST " + stNumOfRecordsToRetrieve + " * FROM " + stTableName >> + " ORDER BY " + fstKeyID + " DESC"; >> ` >> It's interesting that the newest records are found first (DESC), then >> the number of records retrieved are from those DESC record results. > > > Yes, the "subset clause" is applied last of all, so specified ordering > will be respected. > > Unless I'm mistaken, FIRST N is Firebird specific. The SQL standard > syntax for this is: > > SELECT * > FROM T > ORDER BY YOUR_KEY DESC > ROWS N; > > where N would be the number of rows to retrieve. You also have: > > SELECT * > FROM T > ORDER BY YOUR_KEY DESC > ROWS N TO M; > > which will retrieve records N, N+1, N+2, ..., M. Useful for pagination > and not supported, as far as I know, by the Firebird specific syntax. The SQL standard (SQL:2008 and higher) clause is [FETCH][1] which was added in Firebird 3. ROWS is also a Firebird specific invention, or it might have been something that was in a SQL standard draft, but never made it in a final standard. The SQL Standard OFFSET/FETCH combination is IMHO better, though the same result can be achieved with the non-standard FIRST and SKIP. [1]: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch
Re: [firebird-support] How do I limit an SQL search list to 10 records?
On 2019-12-12 19:59, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > This finds all records: > `` > "SELECT * FROM " + stTableName > > `` > > This does not work: > > `` "SELECT * FROM " + stTableName > + " FETCH 10 ROWS" > `` Assuming you are using Firebird 3, you are missing the FIRST (or NEXT) keyword before the number and the ONLY keyword after ROWS. In Firebird 3 and higher you can use the SQL standard fetch clause: SELECT * FROM FETCH FIRST 10 ROWS ONLY See [SQL:2008-Compliant OFFSET and FETCH Clauses][1] In Firebird 2.0 and higher you can use: SELECT * FROM ROWS 10 See [ROWS][2] In Firebird 1.0 and higher you can use: SELECT FIRST 10 * FROM See [FIRST, SKIP][3] Mark [1]: https://www.firebirdsql.org/file/documentation/release_notes/html/en/3_0/bk02ch09s06.html#rnfb30-dml-offsetfetch [2]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-rows [3]: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-dml-select.html#fblangref25-dml-select-first-skip
Re: [firebird-support] Firebird 3.0 Column name length
On 09/12/2019 09:05, Nikolay Marinov kok...@yahoo.com [firebird-support] wrote: > using WIN1251 , this query works fine in FB 2.5 > > select '123' as "Това е едно дълго име на колона" from rdb$database; > > under FB 3.0 it doesn't: > can't format message 13:198 -- message file > C:\Windows\SYSTEM32\firebird.msg not found. > arithmetic exception, numeric overflow, or string truncation. > string right truncation. > expected length 31, actual 31. This is probably related to [CORE-2350][1], see also Firebird 3 release notes, section "Enforcement of Length Limit for Local Alias and Variable Names". I guess the problem is that column names internally in Firebird are UNICODE_FSS, and those 31 Cyrillic characters are more than 31 bytes in UNICODE_FSS. Mark [1]: http://tracker.firebirdsql.org/browse/CORE-2350 -- Mark Rotteveel
Re: [firebird-support] Firebird novice--help with application that uses Firebird
On 06/12/2019 17:41, David Wurdeman david.wurde...@mvpmt.com [firebird-support] wrote: > I opened the port, and now I can connect to the databases. > > However now I’m getting an error when connecting. It says that the > database is already open (with the IP address of the connecting > computer). Even though it is being opened for the first time. I can > still go ahead and click continue past the message, and work with the > database. > > Any idea how to get rid of this message? That doesn't sound like a Firebird problem, but a problem with your specific application. I'd recommend contacting the vendor of your application. Mark -- Mark Rotteveel
Re: [firebird-support] Nagle Algorithm
On 2019-12-03 23:20, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 03.12.2019 22:28, nvat...@yahoo.com [firebird-support] wrote: >> I see that NoNagle can be Enabled on the server by setting True in the >> Firebird Server Config file. > >On contrary, it can be Disabled because by default it is enabled. > >> However, I do not see anywhere to adjust TCP No Delay on the Firebird >> Client side. I see >> that most programs can specify t o open a socket to a server with the >> socket No Delay >> option. I can't even find this in the FBClient.dll. > >Firebird client also read and use firebird.conf file. To be clear: fbclient reads a firebird.conf that is in the same location as the library. So if your application uses C:\Windows\System32\fbclient.dll, it will read C:\Windows\System32\firebird.conf, not the firebird.conf from the Firebird installation. Mark
Re: [firebird-support] What key word specifies a search for an entire word?
On 29-11-2019 22:52, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > The fstName is the column title, not the value. I'm using embedded > Firebird. That you're using Firebird Embedded doesn't matter, it works exactly the same as Firebird Server for queries. The problem is that your using two conditions on column fstName using AND. That means for a single row both conditions must be true for that column. The conditions lower(fstName) LIKE lower(@p0) AND lower(fstName) LIKE lower(@p1) will only be true if both @p0 and @p1 can match the same value, which I think is unlikely for a column which contains first names. Maybe you meant OR instead? Mark -- Mark Rotteveel
Re: [firebird-support] Why I cannot use firebird services to validate a remote database
On 25-11-2019 15:25, Roberto Vieweg jjw.roberto.fireb...@gmail.com [firebird-support] wrote: > The problem was the "time" between perform the SHUTDOWN and the VALIDATION. > > I just add a 1 second "sleep" between the shutdown and validate, and it > works! > > FBMaintenanceManager mgr = .. ; > mgr.shutdownDatabase(FBMaintenanceManager.SHUTDOWN_FORCE, 0); > Thread.sleep(1000); // holy sleep > mgr.validateDatabase(FBMaintenanceManager.VALIDATE_READ_ONLY); Good to hear you managed to solve your problem. I have created a ticket (http://tracker.firebirdsql.org/browse/JDBC-600) to see if this is something that can be fixed or improved in Jaybird. Mark -- Mark Rotteveel
Re: [firebird-support] Why I cannot use firebird services to validate a remote database
On 25-11-2019 13:52, Roberto Vieweg jjw.roberto.fireb...@gmail.com [firebird-support] wrote: > I'm trying to use Jaybird to validate a database before start the > update process of my database definition. > > This validation is necessary to prevent the update of a corrupted database. What is the nature of your problem. If your problem is with Jaybird, then please ask on the firebird-java mailing list, and make sure to provide a minimal but reproducible example and errors (if any) or a description of the problem. Mark -- Mark Rotteveel
Re: [firebird-support] Grant execute on function.
On 22-11-2019 20:27, Alexandre Vinhaes - Tecnico - Tecnologia alexandre.vinh...@cipa.com.br [firebird-support] wrote: > Version 3.0.4 MOD is not custom function..it is a built-in function. Then as far as I know you shouldn't need to grant anything. I don't think you even can grant anything for built-in functions as they are handled in a different way than normal functions. Mark -- Mark Rotteveel
Re: [firebird-support] Grant execute on function.
On 22-11-2019 15:10, Alexandre Vinhaes - Tecnico - Tecnologia alexandre.vinh...@cipa.com.br [firebird-support] wrote: > I have a problem here during an update statement that call the function > MOD. Althougt I have done related grant command "grant execute on > function MOD to users1" the erro message persists: > > no permission for EXECUTE access to function MOD > > I´m not able to find out my mistake. Which Firebird version are you using? And is this the built-in function MOD (introduced in 2.1), or a custom function? For the built-in function, you shouldn't need a grant AFAIK, so that could indicate a bug. Mark -- Mark Rotteveel
Re: [firebird-support] Event handling, I think?
On 2019-11-22 11:02, Alan J Davies alan.dav...@aldis-systems.co.uk [firebird-support] wrote: > Hi all, is it possible to update a table when the current_date in > rdb$database changes to the next day? > > I need to reset a delivery counter to 0 in all the orders on a system. > I can presently do this two ways. > > When I print a delivery label I do it via a SP... > cut-down start of SP > select current_date > fromrdb$database > into:human_date; > select p.label_counter_date > fromparamfil p > into:counter_date; > if (:human_date>:counter_date) then -- it must be the next day > begin > update paramfil p > set p.label_counter_date=current_date; > update orditems oi > set oi.label_day_count=0; > end > then continue with SP to print labels as normal > This does cause a delay to the first set of delivery labels printed > each > day. > > I could write and run a small Delphi program which would only have the > above code as an active item. I could then use Windows (its a Windows > 2016 server) to run this program at midnight plus a minute, as a > scheduled task. > > But that seems a lot for something I think I should do within the > database itself. I've tried to create a trigger on rdb$database but > cannot, I don't have it available and I understand that I should not > 'mess' with it anyway. > > Any help or suggestions gratefully received. Firebird doesn't have a task scheduler, and there is no physical database change when the date changes, so you can't trigger an event. current_database is a function (or context variable), not a column in a database. Mark
Re: [firebird-support] Unable to complete network request to host "127.0.0.1" [SQLState:08006, ISC error code:335544721]
On 2019-11-21 20:29, Roberto Vieweg jjw.roberto.fireb...@gmail.com [firebird-support] wrote: > Full stack > > java.sql.SQLException: Unable to complete network request to host > "127.0.0.1". [SQLState:08006, ISC error code:335544721] > at > org.firebirdsql.gds.ng.FbExceptionBuilder$Type$1.createSQLException(FbExceptionBuilder.java:498) > at > org.firebirdsql.gds.ng.FbExceptionBuilder$ExceptionInformation.toSQLException(FbExceptionBuilder.java:454) > at > org.firebirdsql.gds.ng.FbExceptionBuilder.toSQLException(FbExceptionBuilder.java:218) > at > org.firebirdsql.gds.ng.wire.WireConnection.identify(WireConnection.java:331) > at [..] > Caused by: java.io.EOFException: null > at > org.firebirdsql.gds.impl.wire.XdrInputStream.readInt(XdrInputStream.java:181) > at > org.firebirdsql.gds.ng.wire.WireConnection.readNextOperation(WireConnection.java:418) > at > org.firebirdsql.gds.ng.wire.WireConnection.identify(WireConnection.java:273) > ... 21 common frames omitted This would mean that the connection was abruptly closed after Jaybird sends the connect packet, as this error was thrown when it tries to read the server response (specifically the operation code). Unfortunately, it doesn't tell us why. It could indicate that something other than Firebird is listening on port 3050, or that you have a firewall or something that terminates the connection. It is also possible, but I think unlikely, that there is a problem in the Firebird server process. I'd recommend that you check whether Firebird is actually listening on 127.0.0.1 port 3050. Mark
Re: [firebird-support] ISC ERROR CODE:335544721
On 2019-11-18 21:32, mlcvi...@yahoo.fr [firebird-support] wrote: > Hello, > > I have Firebird 2.58 32 bit version in the C:\Program Files > (x86)\Firebird\Firebird_2_5 We're not talking about your Firebird server install, we're talking about client libraries fbclient.dll or gds32.dll, and the version information of that. If you only have a client in C:\Program Files (x86)\Firebird\Firebird_2_5, your application wouldn't normally be able to connect. Check you %WINDIR%\System32 and %WINDIR%\SysWoW64 folders for gds32.dll and fbclient.dll and check their version information. Mark
Re: [firebird-support] What is the optimum pageSize?
On 2019-11-17 12:22, jerz...@o2.pl [firebird-support] wrote: > On Sat, 16 Nov 2019 10:29:44 +0100, Mark Rotteveel > m...@lawinegevaar.nl [firebird-support] wrote: >> Because it is hard to do. A lot of internals in Firebird and its >> clients >> rely on this maximum size (hint: the number itself fits in a 2-byte >> unsigned integer). It is not like you just need to update a >> MAX_ROW_SIZE >> constant and it is changed. > > I understand it's not trivial. But that's why there are new versions > like 3, 4 or now 5 to avoid such limitations. That is because there is a cost to everything, manpower is limited, and other things are considered more important to do. >> Consider that PostgreSQL has a limit of around 8KB (with its default >> page size). However, PostgreSQL has the advantage that variable length >> fields can be automatically stored out-of-line (ie basically as a >> blob, >> but that is transparent to the user). > > However, according to the documentation "row size" is much larger. But > maybe I am misreading this information. > > https://wiki.postgresql.org/wiki/FAQ#What_is_the_maximum_size_for_a_row.2C_a_table.2C_and_a_database.3F That is because that row size takes the out-of-line (toast) fields (max 1GB per field) into account. Out-of-line fields are similar to blobs, but they are less intrusive because - as far as I know - within the protocol they are communicated with the rowdata, instead of having to fetch them individually like you need to do with Firebird. Personally, I would prefer changes that make VARCHAR (and VARBINARY) practically unlimited and have Firebird do similar tricks like PostgreSQL to automatically store as a blob if a value is too long, but continue to communicate the data in-band in the protocol itself. Mark
Re: [firebird-support] What is the optimum pageSize?
On 16-11-2019 09:33, jerz...@o2.pl [firebird-support] wrote: > >>> But only for FB 2.5, not for FB3! >>> My question was about FB3. Where is info about new versions? > >> In Release Notes for these versions. >> Maximum size of record was not changed. > > > This is a very big limitation, a few larger char fields with multibyte coding > and we get an exception. I don't understand why this limitation hasn't been > removed. Because it is hard to do. A lot of internals in Firebird and its clients rely on this maximum size (hint: the number itself fits in a 2-byte unsigned integer). It is not like you just need to update a MAX_ROW_SIZE constant and it is changed. Consider that PostgreSQL has a limit of around 8KB (with its default page size). However, PostgreSQL has the advantage that variable length fields can be automatically stored out-of-line (ie basically as a blob, but that is transparent to the user). > And in FB4 it is also 64 kB? Yes. Mark -- Mark Rotteveel
Re: [firebird-support] Re: What is the optimum pageSize?
On 2019-11-14 23:47, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Doesn't the page size definition as per a database depend on one > designs their database tables and how such tables will be used? The page size doesn't restrict or limit your database design, with the exception of indexable items. The maximum keysize is slightly less than 1/4 of the page size. This means that with a page size of 4096, the max you can index is +/- VARCHAR(1020) in a single byte character set or +/- VARCHAR(256) UTF8 (I'm saying +/- here because exact size depends on collations etc), while with page size 16384, the maximum is +/- VARCHAR(4092) single byte character set / VARCHAR(1023) UTF8. That said, trying to avoid such oversized indexes would be better: the more keys fit on a single page, the more efficient the index. With a larger page size, more records will fit on a single page (or very wide records might fit on less pages). Page size can have influence on performance, and generally larger page sizes are 'better' (although that also depends on the underlying storage). However, if you use a lot of relatively small blobs, you could be wasting more diskspace. Mark
Re: [firebird-support] Re: What is the optimum pageSize?
On 2019-11-15 00:12, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > Thanks for clarifying! This is very useful info, especially the > ibexpert.net [1] site. > > Is there a Firebird site that provides details such as CHAR and > VARCHAR max sizes, pageSize size options, etc.? Most of it is described in the Firebird 2.5 Language Reference: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html Datatype sizes: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes.html Page sizes: https://firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-db.html If you're using Firebird 3, also consult the Firebird 3 release notes for things introduce or changed: https://firebirdsql.org/file/documentation/release_notes/html/en/3_0/rlsnotes30.html Mark
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
On 14-11-2019 19:02, Dimitry Sibiryakov s...@ibphoenix.com [firebird-support] wrote: > 14.11.2019 18:01, blackfalconsoftw...@outlook.com [firebird-support] wrote: >> However, the PDF language manual I have for version 2.5 does in fact state >> the following... >> >> >> "A fixed-length character data type. When its data is displayed, trailing >> spaces are added >> to the string up to the specified length. Trailing spaces are not stored in >> the database >> but are restored to match the defined >> length when the column is displayed on the client side. Network traffic is >> reduced by not >> sending spaces over the LAN. If the number of characters is not specified, 1 >> is used by >> default." > > No, it something completely different from what is written in Language > Reference and it > is plain wrong. Actually Dimitry, Steve was quoting from the Firebird 2.5 Language Reference: https://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-datatypes.html#fblangref25-dtyp-tbl-dtypinfo And that part of the documentation is unfortunately wrong (although I'm not sure why the language reference even cares to describe such an implementation detail). That table contains some more problems, for example for VARCHAR it says the length is in "two trailing bytes". Mark -- Mark Rotteveel
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
On 14-11-2019 18:01, blackfalconsoftw...@outlook.com [firebird-support] wrote: > I found what we have been arguing over in the Firebird 2.5 Language > Reference Manual since I don't seem to have the one for version 3.0. I > looked for it at the Firebird site but it does not appear to be listed... That is because there is no English version for 3.0 yet, unfortunately. > However, the PDF language manual I have for version 2.5 does in fact > state the following... > > "A fixed-length character data type. When its data is displayed, > trailing spaces are added to the string up to the specified length. > Trailing spaces are not stored in the database but are restored to match > the defined > length when the column is displayed on the client side. Network traffic > is reduced by not sending spaces over the LAN. If the number of > characters is not specified, 1 is used by default." Well, that quote is unfortunately wrong. Documentation is written by humans, and humans are fallible. This is probably an incorrect (re)interpretation of what the InterBase 6 Data Definition guide (page 74) says: """ *Trailing blanks* InterBase compresses trailing blanks when it stores fixed-length strings, so data with trailing blanks uses the same amount of space as an equivalent variable-length string. When the data is read, InterBase reinserts the blanks. This saves disk space when the length of the data items varies widely. """ Which seems to be an oversimplification of the RLE compression that is applied on the record when storing (and the fact VARCHAR is actually not much different from CHAR inside the Firebird engine). However for the observable effects for the user there is no difference. > If the CHAR data type is not being stored with the additional padded > characters but only done so in memory as Ann Harrison has stated than I > have so far only found this information to be at odds with other > information I have found regarding this data type. Ann did not say it is only padded in memory. She said "In memory, yes. But the entire record will be compressed before being written to disk.", meaning that on disk, a record can be shorter because of the RLE compression that is applied. However even in that shorter form, the value is still padded (but that padding will be compressed due to the RLE). > Now what is being said is that internally, a CHAR data type no longer > pads its field to the defined length at the point of creation. In this > case then, the field would then always be expanded to the length > required of updated data to the maximum defined at field definition > time. VARCHAR data on the other hand will act in the same manner except > to use its length-info bytes to base its updated storage field size on. No, that is not how it works. See also my other email that described the on-disk encoding form. > So when did this change? Is this a new difference between Firebird 2.5 > and 3.x.x? It did not change, it has been the same since before Firebird 1. You are inferring too much on a minor error in user documentation. > Here is a link to a detailed description as to how the major databases You forgot to post a link. > store CHAR and VARCHAR data internally. With the exception of SQL > Server, which specifies that ANSI_PADDING has to be set to "ON" to store > padded spaces with CHAR data types (which, to my knowledge, is how I > mostly worked with this database engine), all of the listed database > engines (with the exception of SQLite) say about the same thing for such > storage as I have been stating here. For SQLite, this would be expected > of since it is a not a strongly typed database engine. Your description of how SQL Server ANSI_PADDING works is not entirely correct. See https://docs.microsoft.com/en-us/sql/t-sql/statements/set-ansi-padding-transact-sql?view=sql-server-ver15 Specifically look at the table under Remarks and compare the effects for CHAR(n) NOT NULL vs CHAR(n) NULL. With ANSI_PADDING OFF, CHAR(n) NULL behaves as a VARCHAR(n) in SQL Server, while CHAR(n) NOT NULL behaves as a SQL standard CHAR(n). With ANSI_PADDING ON, CHAR(n) NULL behaves as CHAR(n) NOT NULL and as SQL standard CHAR(n). Similarly VARCHAR(n) in SQL Server will trim all spaces in OFF, but preserve significant whitespace (that is explicitly added spaces) when ON. But here also, the behaviour is described from the user-visible effects, and does not necessarily imply anything about the underlying storage implementation. > If in fact there was a significant change to how the Firebird engine > stores such CHAR than it would be mirroring with SQLite and not the > other major database engines available. The behaviour of CHAR and VARCHAR in Firebird are compliant with the SQL standard, the underlying storage implementation does not matter much when you **use** a database, it is about the effective behaviour. However, if you want to discuss internals, then
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
On 14-11-2019 17:06, blackfalconsoftw...@outlook.com [firebird-support] wrote: > What do you mean I am wrong? I have merely re-iterated how all database > engines I have worked operate. Telling us how other engines work is not very relevant when discussing Firebird, and when the **internal implementation** in Firebird is different. And if you do make assumption based on other database engines, then please at least say that you do that so it is clear where your assumptions are going wrong. Saying with conviction that something works a certain way when it is not is not only annoying for us who do know the internals, but potentially confusing for people who don't know the ins and outs of Firebird. > And what you are saying is that a CHAR field in Firebird acts in the > same manner as that of a VARCHAR field. If that were the case there > would be no difference in how they are used and no reason to have two > different data types. No, that was not what I said. You seem to be confusing user visible behaviour with internal implementation details inside the Firebird database engine. User visible behaviour: - CHAR: fixed width and shorter values are padded with spaces upto the declared length - VARCHAR: variable width (not padded) Internal in-memory implementation: - CHAR: fixed width and shorter values are padded with spaces up to the declared length - VARCHAR: 2 bytes with actual (byte) length and shorter values are padded with NUL bytes up to the declared length Internal storage implementation: - for both same as in-memory, but with RLE applied to compress size > Historically, in database development CHAR fields > were most often used for small string fields that would most often have > a predtermined length such as a unit code whereas VARCHAR fields were > used for data where that size at update was not generally known but > would not exceed an expected maximum. Yes, and that is the user visible behaviour of those datatypes. > Second, if you as Human, can observer the effects of processes being > performed in milliseconds than you must have some level of magic on your > side to be able to observe such differences. You were the first and only one to bring this up, in your reply to Ann. The way you reply suggests you are replying to something I said, which is not the case. But now that you bring it up again: a human can observe a performance difference of a millisecond. Say the choice between CHAR or VARCHAR could cause a millisecond difference per record (which it doesn't, but lets assume for argument's sake), then you can observe this difference when processing a sufficient amount of records (eg processing a 1000 records would yield a 1 second difference). [..] > And I was not telling Ann anything different. Yes, which means that effectively you were mansplaining Ann, who worked on InterBase and Firebird, and knows the internals, how you think Firebird works. And Firebird actually doesn't work that way. > This information is also within the links that were provided to me > regarding the explanation between CHAR and VARCHAR fields in the > Firebird developer guide... IBExpert.net is not a Firebird website, it is a third-party that sells Firebird and InterBase tools, and in this respect their documentation is wrong. Mark -- Mark Rotteveel
[firebird-support] L s
On 2019-11-14 03:40, Richard Damon rich...@damon-family.org [firebird-support] wrote: > On 11/13/19 9:51 AM, blackfalconsoftw...@outlook.com [firebird-support] > wrote: >> >> Thank you Anne, for your succinct comments regarding the differences >> between the efficiencies of a CHAR and VARCHAR field definitions. >> >> Human observation cannot actually elicit any observable effects >> between the two. >> >> However, internally, the CHAR field definition is more efficient as >> the database does not have to perform any field size calculations at >> the moment that data is being updated to a VARCHAR field. Again, this >> is not noticeable to a user unless the data that is being updated into >> a VARCHAR field is quite large. >> >> Steve Naidamast >> Sr. Software Engineer > > By the SQL standard, there are some noticeable effects. CHAR is defined > as a fixed width, so data is padded with blanks to reach that width, > and > then the blanks are removed on retrieval. Because of this, a CHAR field > can not store a value with trailing blanks. On the other hand, since > VARCHAR doesn't need to pad the field, it doesn't need to trim trailing > blanks, so the field CAN store data with trailing blanks. CHAR values in the SQL standard retain the padding: it is not removed on retrieval. Mark
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
On 2019-11-13 17:37, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Your statement, which may be true, does not sit well with a previous > statement that states that a VARCHAR field of 1000 characters is > stored in the table with 1000 characters. > > Your statement is suggesting a null length (until the field is > updated) with two bytes for an actual length, which is how VARCHAR > fields work in all other databases to my knowledge. Otherwise, to > follow the previous statement, a Firebird table could have a VARCHAR > field for 1000 characters, be stored as such with the initial > storage-info bytes holding the actual length, which would be 1000. > When the field is updated to lets say 20 characters of data, the field > would still have an actual size of 1000 characters but the > storage-info would be 20. > > How does this make any sense? A record on-disk is a compressed version of the in-memory image of the data of a row. A VARCHAR(1000) in server memory has 2 bytes for the actual data and 1000 bytes for the data, and 2 bytes padding to make it a multiple of 4. Bytes beyond the actual data length are zeroed-out. When written to disk, a RLE compression is applied, so a VARCHAR(1000) containing 'A' will be something like (in hexadecimal): 03 01 00 41 80 00 80 00 80 00 80 00 80 00 80 00 80 00 97 00 Where 03 means: next 3 bytes literally 01 00 means: actual length 1 41 means: literally A 80 00 means: repeat 128 times 00 (x7) 97 00 means: repeat 105 times 00 These last two give a total of 1001 0x00 bytes (zero-padded value + additional padding to make it a multiple of 4. A CHAR(1000) with value A would be encoded as: 01 41 80 20 80 20 80 20 80 20 80 20 80 20 80 20 99 20 Where: 01 menas: next 1 bytes literally 41 means: literally A 80 20 means: repeat 128 times 20 (space) (x7) 99 20 means: repeat 103 times 20 (space) These last two give a total of 999 0x20 bytes (space-padded value), no additional padding because value is already a multiple of 4. NOTE: I might have some details wrong, but this is how it basically work. The Firebird Internal docoument, section data page also describes this: https://firebirdsql.org/file/documentation/reference_manuals/reference_material/html/fbint-page-5.html Mark
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
On 2019-11-13 15:51, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Thank you Anne, for your succinct comments regarding the differences > between the efficiencies of a CHAR and VARCHAR field definitions. > > Human observation cannot actually elicit any observable effects > between the two. > > However, internally, the CHAR field definition is more efficient as > the database does not have to perform any field size calculations at > the moment that data is being updated to a VARCHAR field. Again, this > is not noticeable to a user unless the data that is being updated into > a VARCHAR field is quite large. Why are you trying to explain to Ann how Firebird works? Your explanation is wrong, and Ann is intimately familiar with the internals of Firebird and its predecessors as she was one of the original developers of InterBase. Mark
Re: [firebird-support] Re: What are the trade-offs of CHAR vs. VARCHAR?
On 2019-11-13 18:42, blackfalconsoftw...@outlook.com [firebird-support] wrote: > Here is the link to the definition of CHAR and VARCHAR field > definitions from the IB-Experts site... > > > https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR > https://www.ibexpert.net/ibe/index.php?n=Doc.CHAR > In neither the CHAR or VARCHAR definitions is it noted that unused > space is filled with nulls or blank data as both are stored as > variable length fields internally within a table. That is incorrect. A CHAR will be stored blank-padded (space for normal character sets, 0x00 for octets), while a VARCHAR will be stored 0x00 padded. However, due to a RLE (Run Lenght Encoding) applied, that padded will be stored 'compressed'. > However, the CHAR > data type, as would be expected, can only store as a maximum, the > number of characters that its original definition set forth. The > VARCHAR field can hold up to over 32,000+ bytes of data. That is incorrect. Both a VARCHAR(n) and a CHAR(n) will allow storing a maximum of n characters, the only difference is that the value (as seen by the consumer) of a CHAR(n) is right-padded with spaces, while a VARCHAR(n) is not padded. Mark
Re: [firebird-support] What are the trade-offs of CHAR vs. VARCHAR?
On 2019-11-12 23:06, Clyde Eisenbeis cte...@gmail.com [firebird-support] wrote: > As I understand, the FbConnection.CreateDatabase max pageSize for > VARCHAR is 8191. Does the page size change to less than 8191 if the > VARCHAR is less? > > What is the max pageSize for CHAR? Page size is about the size of database pages, Firebird supports page sizes of 4096, 8192, 16384, and - Firebird 4 - 32768 (earlier versions also supported 1024 and 2048). Page size is unrelated to datatypes sizes, except for things you want to index (because the page size does restrict the key size of an index. In any case, CHAR is limited to 32767 characters with a single byte character set and 8191 characters with UTF8. VARCHAR is limited to 32765 characters (single byte) or 8191 characters with UTF8. The only good reason to choose CHAR is for fixed width data, in all other cases you should use VARCHAR. Storage-wise it doesn't really matter in Firebird (although VARCHAR actually requires two bytes more than CHAR in storage format), but VARCHAR is smailler in transmission in the wire protocol. However, CHAR is padded with spaces, while VARCHAR is not. For most uses of string data, VARCHAR is simply better. Mark
Re: [firebird-support] words reserved by version
On 12-11-2019 10:31, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > On 2019-11-12 10:14, Mark Rotteveel m...@lawinegevaar.nl > [firebird-support] wrote: >> On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu >> [firebird-support] wrote: >>> Good morning all, >>> >>> Is there an exhaustive list of all the words reserved by version of >>> firebird (3.x, 4.x) somewhere? >>> >>> Thank you in advance. >> >> Yes, it is in the doc folder: >> https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords > > If you want an exhaustive list, I also have a database (created using > https://github.com/mrotteveel/firebird-reserved-words). I use that > database to generate metadata information in Jaybird (which needs a list > of keywords not in SQL:2003). I'll put a backup of the latest version of > that database online somewhere later today. I put a backup of the database (Firebird 3) on https://www.dropbox.com/s/ep17q5hv43iwkll/fb_reserverwords_20191112.7z?dl=0 This represents the state of 7 months ago, so it might not contain keywords introduced in 2.5.9 and recent Firebird 4 snapshots. Mark -- Mark Rotteveel
Re: [firebird-support] distinct on list() function
On 12-11-2019 13:37, Michal Kurczabinski michk...@gmail.com [firebird-support] wrote: > Is this old bug from FB 2.5.x or some kind of feature ? > > This still exists in FB 3.x It would be helpful to also describe the problem, and not only let people execute a query and try to guess what problem you're seeing. Mark -- Mark Rotteveel
Re: [firebird-support] words reserved by version
On 2019-11-12 10:14, Mark Rotteveel m...@lawinegevaar.nl [firebird-support] wrote: > On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu > [firebird-support] wrote: >> Good morning all, >> >> Is there an exhaustive list of all the words reserved by version of >> firebird (3.x, 4.x) somewhere? >> >> Thank you in advance. > > Yes, it is in the doc folder: > https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords If you want an exhaustive list, I also have a database (created using https://github.com/mrotteveel/firebird-reserved-words). I use that database to generate metadata information in Jaybird (which needs a list of keywords not in SQL:2003). I'll put a backup of the latest version of that database online somewhere later today. Mark
Re: [firebird-support] words reserved by version
On 2019-11-12 10:03, Norbert Saint Georges n...@tetrasys.eu [firebird-support] wrote: > Good morning all, > > Is there an exhaustive list of all the words reserved by version of > firebird (3.x, 4.x) somewhere? > > Thank you in advance. Yes, it is in the doc folder: https://github.com/FirebirdSQL/firebird/blob/master/doc/sql.extensions/README.keywords