Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column
Hi Vishal, > I would also like to know that if I need to learn Triggers and Stored > Procedures then what document should I refer, I am am new for triggers > and SP. Googling is random way I see. Would you refer any good books > for this? You'll find lots of free documentation under http://www.firebirdsql.org/en/documentation/ More specifically, here's the Language Reference: http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25.html with the chapter on triggers: http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-ddl-trgr.html and on Procedural SQL (the language you use inside triggers and Stored Procedures): http://www.firebirdsql.org/file/documentation/reference_manuals/fblangref25-en/html/fblangref25-psql.html The Firebird FAQ is also very useful: http://www.firebirdfaq.org/ And Helen Borrie's Firebird Book is a great manual which also includes chapter(s) on SP's, triggers and PSQL. It's not free though! You'll find it here: https://www.ibphoenix.com/ (You may need to scroll down a bit.) Hope this helps! Cheers, Paul Vinkenoog
Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column
Vishal Tiwari vishuals...@yahoo.co.in [firebird-support] schreef op 09-08-2017 11:58: > I have put table name as Table_Name, which is actual table name. > > On Wednesday, 9 August 2017 3:27 PM, Vishal Tiwari > <vishuals...@yahoo.co.in> wrote: > > Hi Paul, > > I am getting below error while executing the trigger you shared. May > be because of new FB version? I am executing using FlamRobin tool. > > Please see error below: > > *** IBPP::SQLException *** > Context: Statement::Prepare( create trigger before update on > Table_Name > as > begin > if (new.col3 is distinct from old.col3) > then new.col5 = current_timestamp ) > Message: isc_dsql_prepare failed > > SQL Message : -104 > can't format message 13:896 -- message file > C:\Windows\system32\firebird.msg not found > > Engine Code: 335544569 > Engine Message : > Dynamic SQL Error > SQL error code = -104 > Token unknown - line 1, column 23 > update My fault! If forgot the trigger name. It should be: create trigger MyTrigger before update on Table_Name ... Instead of MyTrigger, you choose a more meaningful name of course. Cheers, Paul Vinkenoog
Re: [firebird-support] Re: On Updating One Column Value, Update Time Stamp in Another Column
Hello Vishal, > I have one table say "MyTable", which has five columns, say Col1, > Col2,...,Col5 and it has 10 rows. Col5 is of Timestamp. > > My issue is, whenever I am updating Col3, that time, only for that row > of Col5, Timestamp value should be updated to the current timestamp. > > What would be the best option for this? > > If trigger is the best way then how would I do it? As I never worked > on Triggers. Yes, a trigger is definitely the way to go, e.g. like this: set term #; create trigger before update on MyTable as begin if (new.col3 is distinct from old.col3) then new.col5 = current_timestamp; end# set term ;# If col3 is non-nullable you can simply use "new.col3 <> old.col3" in the test. Mind you, an explicit update that re-enters the existing value in col3 won't cause col5 to be updated! HTH, Paul Vinkenoog
Re: [firebird-support] Case and Accent insensitive compares
Hi Stefan, > > Also, it is questionable if you should consider a and ä different > > letters, even in German. See e.g. > > https://de.wikipedia.org/wiki/Alphabetische_Sortierung > > For sorting, treating them as "same" is correct. However, for things > like comparisons (in a unique key for example) they are not the same. That's right. I can't think of any circumstances where you would want to consider 'hatten' and 'hätten' the same, for instance. Or Mutter and Mütter. Actually, I couldn't think of any German word where this would be the case. So when it's strictly about German, AI collations seem useless, except for catching certain foreign words and names likes Gérard/Gerard, or misspellings of Kekulé. Cheers, Paul Vinkenoog
Re: [firebird-support] Case and Accent insensitive compares
Hello Stefan, > I expect that an accent insensitive compare treats accented characters > as the "same" as their un-accented counterparts because the accent > does not change the character itself but things like pronounciation or > stress. > > So in Frech, à is similar to a, é is similar to è and you use an > accent insensitive compare to find Gérard even though your search term > says Gerard (without the accent). > > However, in the German language, the letters Ö and O are two different > characters with a completely different pronounciation (the same is > true for A/Ä and U/Ü). As they look similar, the sorting is done so > that they stay together, but they can _not_ be treated as accented > versions of each other. UNICODE_CI_AI is a generic, language-independent collation. Since ö, ü and ä are not specific to German (they also exist in Dutch, for instance, and ö and ä in Swedish, and ö and ü in Hungarian, etc.) it will simply treat them as accented forms of o, u and a. Also, it is questionable if you should consider a and ä different letters, even in German. See e.g. https://de.wikipedia.org/wiki/Alphabetische_Sortierung DIN 5007 Variante 1 (für Wörter verwendet, etwa in Lexika; Abschnitt 6.1.1.4.1) ä und a sind gleich ö und o sind gleich ü und u sind gleich ß und ss sind gleich DIN 5007 Variante 2 (spezielle Sortierung für Namenslisten, etwa in Telefonbüchern; Abschnitt 6.1.1.4.2) ä und ae sind gleich ö und oe sind gleich ü und ue sind gleich ß und ss sind gleich If you do want to treat them as different letters, you need a German collation that does just that. However, this collation will not work correctly with words in some other languages containing ä, ö and ü. Cheers, Paul Vinkenoog
Re: [firebird-support] Select Distinct Column Pairs
Jack Kane wrote, One of my tables has about 140 distinct values in one column and 70 in another column. would like to design a query that selects distinct pairs of values, so that each pair is unique. If that is possible, would one of you please give me an example. select distinct col1, col2 from table HTH, Paul Vinkenoog
Re: [firebird-support] Re: nbackup strategy advice
Kjell Rilbe wrote: First day of year: N=0, initial complete backup. First day of each month: N=1, will contain all pages changed since first day of year. First day of each week: N=2, will contain all pages changed since first day of month. Each day: N=3, will contain all pages changed since first day of week. If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). Cheers, Paul Vinkenoog
Re: [firebird-support] Re: nbackup strategy advice
Hi Kjell, If two such days coincide, you still need to run both colliding levels (lower N first, higher N directly afterwards), or the sequence will be broken next day. That's not necessary, and the higher level backup will add nothing on that moment. Suppose you make a level-2 backup every Sunday. Then it makes sense to schedule the level-3 backups daily from Mon-Sat. If you want to make a point-in-time restore later, it will involve 4 files if that point in time is on Mon-Sat, and 3 files if it is on a Sunday. (And 2 files if it is the first day of the month.) All you have to do is determine the most recent backup before the chosen point in time. If that is a level N, you need N+1 files for the restore (levels 0-N, each one being the most recent file of that level before time 'T'). My idea was to avoid having to keep track of N for last backup. The script scheduling would be a bit simpler. And I do realize the N=1 backup right after the N=0 backup will store nothing. It will, however, make it possible to do the N=2 backup next day and have a complete N-chain. Skipping the N=1 backup and go right for N=2 won't work. or am I missing something? No, you're right. A level N backup is always based on the most recent level (N-1) backup. So, suppose the first of the month is on a Tuesday, and the level 1 backup is made. Level 2 backups are scheduled every Sunday and level 3 backups every working day. In that case, the level 3 backup of Wednesday the 2nd will be based on the level 2 backup of Sunday the 29th or 30th of the previous month. The most recent level 1 backup won't be in its restore chain. That's not intrinsically bad, but it's confusing. So after the monthly level 1, it makes sense to have it followed immediately by a 'weekly' backup, even if it's the 'wrong' day of the week. But the daily backups can start the next day, *unless* there are also hourly backups scheduled (N=4). I also realize now that I automatically assumed that nothing happens during weekends, but of course that depends entirely on the situation. Kind regards, Paul Vinkenoog
Re: [firebird-support] nbackup strategy advice
Hello Hugo, We have a POS applications with hundreds of clients and need some advice on how to backup. Each application has it's own Firebird database. Read about nbackup and thought that this could be a solution since the clients has low bandwidth. My first idea was to daily backup and begin with N=0 and count up the N each day. This would result in probably hundreds of files. Would this be an OK solution? Incrementing the backup level above 2 or 3 usually doesn't make sense. An nbackup scheme may look something like this: - A full (level-0) nbackup every month - A level-1 incremental nbackup every week - A level-2 incremental nbackup every day - Optionally: a level-3 incremental nbackup every hour Of course this is just an example, but you get the general idea. With the 4 levels in the example, any restore would involve at most 4 files. You should probably also script a deletion scheme. See also the nbackup guide, especially this page: http://www.firebirdsql.org/file/documentation/reference_manuals/user_manuals/html/nbackup-backups.html Is it possible to restore a database with 1000 of nbackup files? Theoretically, yes. But you don't need that many levels. And you can't put that many file names on the command line. Is it possible to tell nbackup to read all files in a folder? Not directly. If you want to nbackup multiple databases, you should write a shell script that calls nbackup within a loop, each time with a different database name. Hope this helps, Paul Vinkenoog
Re: [firebird-support] Basic sql alter table commands
Hi Jack, Achieved success using examples from the Razor site. If that is the best available, I'm fine with that. Thanks anyway. You can pick up the InterBase 6 Language Reference here: http://www.ibphoenix.com/files/60LangRef.zip, and visit the section SQL Statement and Function Reference :: ALTER TABLE. That's the basis. The document Thomas pointed you to contains the updates since Firebird was forked from the IB sources. Good luck, Paul Vinkenoog
Re: [firebird-support] OT: Vote for Firebird as Database of the Year 2013 at LinuxQuestions
Hello Carlos, This LinuxQuestions poll is even worse in such aspect, since not everyone uses FB on Linux, and even when they use, most people don't wanna waste 5 minutes registering to a site they will not use, just to be able to vote por FB. I would say that in the past, Firebird users were more passionate about the product. Unfortunately, this seems to not be true anymore (and I could list some possible reasons for that). What are they, in your opinion? Maybe we should discuss this in Firebird-general. I, too, have the impression that we used to have more momentum in the past, but maybe that's just me: back then I had much more time, so I contributed more to Firebird, followed all the newsgroups, etc. Cheers, Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Stefan Heymann wrote: It also requires you to stop the server, which may be a nuisance if there are also other databases in use. AFAIK, after a FULL shutdown you can also access the .fdb file on a file basis. So you don't have to stop the entire service. You're right; in Firebird 2.0 and up you can do a full shutdown, if necessary in combination with -force. Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Alan McDonald wrote: In general, moving Firebird database files is a definite no-no. You copy or move Firebird databases by gbak'ing them and restoring them at the new location. What? Is there more to knowing that if you stop the fbserver, it's safe to copy an .fdb file than there is to know how to manipulate a database file under the control of nbackup? Sorry, I'm not sure what you mean by this (maybe because English isn't my first language). Of course it's safe to copy an .fdb if the server is down (and no embedded connections exist), but the standard mantra is use gbak, simply because this is *always* safe. Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Maury Markowitz wrote: In general, moving Firebird database files is a definite no-no. You copy or move Firebird databases by gbak'ing them and restoring them at the new location. Now *that's* interesting, because this has been the most suggested method I have seen. In fact, I only learned about the nbackup method long after having done two moves the other way, which was the recommended method from the vendor. That recommendation makes sense, especially since gbak has been around for a long time and is known to do its job. Nbackup is relatively new; it ought to be just as safe as gbak but in your case, something went wrong; the database wasn't unlocked and the .delta not re-integrated. Even then, the database kept functioning and I assume (and hope for you) that no data were corrupted. I think maybe it would be a good idea to put this into the vendor's dox as well. You mean the FishBowl docs? Or the Firebird docs? BTW, this nbackup scheme you used: did it come with Fishbowl or did you set it up yourself? Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Maury Markowitz wrote: No, sorry, I meant to say that every reference I have ever found has suggested the correct way to move a FB database from one machine to another is to simply copy the FDB file while the server is down. Oops, that's not good advice. Although this may be safe, it may also not be (depending on platform, possibility of embedded connections, etc.) It also requires you to stop the server, which may be a nuisance if there are also other databases in use. Did you find this advice in any docs that came from the Firebird project itself? However, in my case, the presence of the failed nbackup meant that the delta file it left behind also had to be copied. I'm actually surprised that simply copying both files actually solved the problem, but I suppose it did so because the original FDB had some sort of pointer to the existence of the delta. This is definitely *not* something that the documentation covers. No, but it doesn't really need to. First, it is an implementation detail, and second, it goes without saying that if all modifications are sent to the delta, Firebird knows that if the main database is in nbak state, it also needs the delta. The default name is database file name.delta, but you can set another path + name in the database itself (and this is something that the manual *does* cover). Actually, from your post it would seem that the whole concept of simply coping the FDB is a bad idea anyway, and the proper solution would be to always use gbak? If in any doubt, use gbak. But as you can tell from Sean's post, there are situations where a straight file copy/move is just as safe. Just make sure you know what you are doing. Cheers, Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Hello Maury, When I tried to re-attach the FDB file on the new server I got a file not found error. But looking in some of the logging files I found... Reason: I/O error for file CreateFile (open) D:\FISHBOWL\DATABASE\DATA\ASSOLAR.FDB.delta Error while trying to open file null A. Notice *which* file it can't find? Well then, perhaps if I copy BOTH files over... and it works! So basically it seems that the FDB file contains some sort of information that points to the delta. When you attempt to connect to a FDB in this state, it also looks for that delta file, and if it's not there, you get file not found. Makes sense in retrospect! Of course it needs the delta. As long as the backup state lasts, the main database file is frozen and all changes are written to the delta. That is, the .fdb and the .fdb.delta *together* form the database. I then used isql to ALTER DATABASE END BACKUP. This took about 15 seconds to run (impressive!), and successfully merged the delta back into the FDB. Everything is back to normal. Yay! Great! This is definitely something that should be mentioned in the documentation! What exactly are you missing in the documentation? Notice that delta files are normally very short-lived. If you use nbackup -b, the delta has disappeared when the command completes (and if something went wrong, there'll be an error message). And if you use nbackup -l, you are supposed to know what you are doing ;-) I daresay that if you had read the nbackup manual from beginning to end, you could have solved this problem yourself. (This is not criticism, BTW; this group is called firebird-SUPPORT for a reason.) Is there a method for me to do the documentation change? I'm pretty good at that sort of thing. If you want to propose changes to a manual, the preferred way is to submit them to the firebird-docs list (to subscribe, mail to firebird-docs-requ...@lists.sourceforge.net) People who write or update documentation regularly can get CVS access. Kind regards, Paul Vinkenoog
Re: [firebird-support] Multiple Embedded Connections
Hi Alan, I notice that it is now possible to connect to a database via the embedded server and simultaneously connect via other embedded processes to the same database file. This was not possible in previous version - the second process would be locked out. Can someone lead me to an explanation of how these multiple connections are managed? Which embedded server will coordinate updates and modifications to the database? It appears that updates from one process are visible to the other(s) but I'm not sure how the second would know if a transaction is being managedin the first - is there a lock file being written to somewhere that I can't find? (WIN32) V2.5 From the 2.5 Quick Start Guide: Windows Embedded now contains a SuperClassic instead of a SuperServer engine. File locks are shared, so a database can be accessed by one or more Embedded servers and a regular Classic or SuperClassic server at the same time. Consult the Firebird 2.5 Release Notes for full details. Cheers, Paul Vinkenoog
Re: [firebird-support] Multiple Embedded Connections
Alan McDonald wrote: Windows Embedded now contains a SuperClassic instead of a SuperServer engine. File locks are shared, so a database can be accessed by one or more Embedded servers and a regular Classic or SuperClassic server at the same time. Consult the Firebird 2.5 Release Notes for full details. Yes - but where is this global lock table? It doesn't tell me if it's a file somewhere or in the memory of the first server loaded? Iirc, it's a lock file in ProgramData\Firebird. Paul Vinkenoog
Re: [firebird-support] Confused about delta files [SOLVED]
Maury Markowitz wrote: What exactly are you missing in the documentation? Notice that delta files are normally very short-lived. That's the issue right there. As it is clearly possible that these are not *always* short lived, this should be mentioned along with suggestions on what to do. That's right. Although your experience seems to be exceptional, it *did* happen and it may happen to others. So a warning in the nbackup manual would be in order. More specifically, there should be a mention in the sections on moving the databases that state that if a delta file is present, it must be moved as well. In general, moving Firebird database files is a definite no-no. You copy or move Firebird databases by gbak'ing them and restoring them at the new location. Including suggestions in the docs like if you move a Firebird database, check if there's a delta present and if so, move that too might give users the impression that moving database files around is a good idea. What's more, if there's a delta present, then the main .fdb file is usually locked - so it can be moved or copied without risk - but the delta is live, so it shouldn't be touched unless there's absolutely no other option. In the situation you described, the logical thing to do would have been ALTER DATABASE END BACKUP (or nbackup -N) on the original machine (once you found out the right credentials, which I believe you did). Cheers, Paul Vinkenoog
Re: [firebird-support] nbackup questions
Maury Markowitz wrote: But wait... what about... ALTER DATABASE END BACKUP If I am reading it correctly, this appears to do the same thing as nbackup -N. Is that correct? That is correct, but in order to issue that command you must connect to the database as SYSDBA or owner. From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. User GONE doesn't seem to be the owner, and you don't know the SYSDBA password. The fact that it isn't masterkey makes sense: either the installer generated a random password, or the password was left at masterkey. In the latter case, any database administrator in his right mind would immediately change it to something else. Why don't you contact the administrator of the system in question? After all, *someone* should know the SYSDBA password, shouldn't they? And how about the scripts that executed nbackup until they went broken last May? Do you have read access to them? What authentication method did they use? And if you can't access those scripts, who can? Kind regards, Paul Vinkenoog
Re: [firebird-support] RE: nbackup questions
Vlad wrote: From your earlier postings, I gather that in May 2013 nbackup failed to complete, so the main database file has been frozen for 8 months, and all the changes since May 2013 have accumulated in the delta. This is too strong statement. To verify it we need to know physical backup state of the database. gstat -h will show it (and it not requires admin privileges). I based my assumption on what Maury wrote in the earlier thread Confused about delta files: The date of the FDB is May 2nd, 2013. The date of the delta is today. ...combined with his later statement that the system's Scheduler function, without any warning or errors, stopped making these nbackups last May. Cheers, Paul Vinkenoog
Re: [firebird-support] nbackup questions
Hello Maury, Why don't you contact the administrator of the system in question? After all, *someone* should know the SYSDBA password, shouldn't they? I don't believe so. The installer process that created the DB, which comes from Fishbowl, not Firebird, never asked for a SYSDBA password, and did not report one. So Fishbowl installed the Firebird server and kept the SYSDBA password to itself. That makes (some) sense. The only user you know the password of doesn't seem to have access to the database. This suggests that either SYSDBA or another Firebird user account (created transparently by Fishbowl, with name and password kept internal) is the owner. I think you should contact Fishbowl Inventory support to clear this up. Some of the dox suggest the Firebird installer will leave behind a file saying what the password was randomly set to. No file of this type appears to be on my system. Yes, some installers on Posix systems do this. If this is the case, the generated password is in the file SYSDBA.password in the Firebird installation directory. Most keep it at masterkey though. In your case, I assume that the Fishbowl software has generated a secure SYSDBA password. Kind regards, Paul Vinkenoog
Re: [firebird-support] nbackup questions
Hello Maury, So a little Google-fu was all that took. Now armed with the credentials, what would be the next steps at this point? I suspect the next step is to END BACKUP. However, I wish to tread carefully... I don't have a backup newer than last May, the existing FDB is not a usable file as far as the new machine is concerned, etc. So, what exactly happens when you do the END BACKUP? Does the server have to fold the delta into the original FDB? If so, I suspect this is a long-running task given the size of the file? Or is this a much simpler task, simply renaming the files or such? If I first copy the original FDB and delta, if something goes wrong during END BACKUP will those files be useful to me? It does not appear the original FDB is a working database (at least I cannot connect to it), and I suspect the name delta is accurate so it is not a usable file by itself either. This seems to suggest (I know I know, ASSuME) that simply copying these files to offline storage gets me basically nothing? If you connect to the database and issue ALTER DATABASE END BACKUP: - The delta (containing the changes since May last year) will be re-integrated with the main database file (.fdb). - Upon success, the delta will be deleted and the state flag of the database file set back to normal. Then, you should make a regular backup with gbak and restore that on your target machine (using gbak on your target machine). I have no idea how much time re-integrating a 70 MB delta will take. Is it a problem if it takes several hours? (Not that I expect this.) As for copying the current .fdb and .delta to your target machine: - Copying the .fdb is safe - IN THIS CASE! - because the file is frozen and closed. (Under normal circumstances, copying an .fdb file is unsafe, unless you take special precautions.) - Copying the .fdb.delta may be unsafe because its a live database file. Even if no users are connected to it, the 'hidden' user (or SYSDBA) may be connected from within Fishbowl. Can you stop Fishbowl completely? And then check if no process has the .delta file open. Then it may be safe (and even wise) to copy the two files to your target machine, just in case. (Note: if the source and target machine have a different platform architecture, straight file copies may not work on the target machine. If they have different endianness, straight file copies are *guaranteed* not to work. Cheers, Paul Vinkenoog
Re: [firebird-support] Help with a query
Hi Fulvio, I have two tables, for example TableA and TableB. Each row of TableA contains a foreign key to a row of TableB. This a 1 to 1 relation, so each row of TableA is linked to one different row of TableB. The foreign key field can also be NULL, in this case the TableA row is not connected to a TableB row. All TableB rows depend logically on TableA rows, so all TableB rows should be linked by a TableA row. I would like to check for program errors during development, so I would like to know if there are orphans TableB rows. May somebody suggest me a good way to find the rows in TableB that are not connected from any row in TableA? Why not reverse the link then, and make sure the foreign key field in TableB is NOT NULL? But if that's not an option, you can periodically check for orphans with select columns from TableB b where not exists (select * from TableA a where a.fk = b.target) Kind regards, Paul Vinkenoog ++ Visit http://www.firebirdsql.org and click the Resources item on the main (top) menu. Try Knowledgebase and FAQ links ! Also search the knowledgebases at http://www.ibphoenix.com ++ Yahoo Groups Links * To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ * Your email settings: Individual Email | Traditional * To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) * To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com * To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com * Your use of Yahoo Groups is subject to: http://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] Number list without table
Hi Piotr, I need to get list of number (f.e 1,2,3,4,5) as rows but using only SQL query (without extra table or view). Something what after running query looks like: List_of_numbers --- 1 2 3 4 5 You can write a selectable stored procedure to achieve that, with the number of rows as a parameter. Or use EXECUTE BLOCK. Cheers, Paul Vinkenoog
Re: [firebird-support] Primary Key x Unique Key
Hallo Hugo, When should I use a Unique Key instead of a Index? I know that a Unique Key does not allow duplicated values like a Primary Key, but what more? That, and it creates an index automatically. Also, if you want a column to be referenced by a foreign key, it has to be either the PRIMARY KEY or UNIQUE. Kind regards, Paul Vinkenoog
Re: [firebird-support] Primary Key x Unique Key
Hallo Hugo, When should I use a Unique Key instead of a Index? I know that a Unique Key does not allow duplicated values like a Primary Key, but what more? That, and it creates an index automatically. Also, if you want a column to be referenced by a foreign key, it has to be either the PRIMARY KEY or UNIQUE. Notice that since Firebird 1.5, a unique key constraint does allow multiple null instances in the column. http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-ddl-table.html#langrefupd25-ct-unique-keys Cheers, Paul Vinkenoog
Re: AW: [firebird-support] NOT in Firebird
Olaf wrote: great, exactly what I was looking for. Only 1 and 0 is possible. Then you can also do Var2 = 1 - Var1 The outcome is the same, but perhaps this is more obvious than bitwise XORing with 1. And it might execute a wee little faster. Cheers, Paul Vinkenoog
Re: [firebird-support] RDB$ADMIN and Role Revocation
Hi Alan, I know in the past that the grantor must be the one who revokes that role. But now we have RDB$ADMIN a user with role RDB$ADMIN can create, edit and delete users and grant a role to another user. I would have thought SYSDBA or indeed any other RDB$ADMIN user could revoke any role. Firebird 2.5.2 - this is not the case. I get an exception unsuccessful metadata update SYSDBA is not grantor of Role on MANAGER to 0S0ASDFASDF. You have to use GRANTED BY here: revoke manager from 0S0ASDFASDF granted by rdb$admin Paul Vinkenoog
Re: [firebird-support] RDB$ADMIN and Role Revocation
Alan McDonald wrote: unsuccessful metadata update SYSDBA is not grantor of Role on MANAGER to 0S0ASDFASDF. You have to use GRANTED BY here: revoke manager from 0S0ASDFASDF granted by rdb$admin So we're saying SYSDBA has to first make system table enquiries to find out who granted the role and then make the adjustment to the revoke statement? That doesn't sound right or basically logical to me. It's tough enough already without SYSDBA being forced to jump thru all those hoops. SYSDBA can delete everyone from the security database, and delete all the objects no matter who made them but can't revoke a role until he finds out who granted it? Yes. AFAIU, the reasoning was that a user can be granted the same privilege multiple times, by different users, and REVOKE should only remove the privilege instance (== row in RDB$USER_PRIVILEGES) that was granted by the current user (or the user specified after GRANTED BY). FWIW, this complies with the SQL standard. If SYSDBA (or RDB$ADMIN, or the database owner) wants to remove this kind of multiple-grantor but otherwise equal privileges now without finding out the grantors first, it has to be done with a searched delete statement on RDB$USER_PRIVILEGES. Which is a hack of course, like any direct manipulation of metadata. It would be better if this were possible in SQL, e.g. by implementing CASCADE for REVOKE statements (like PostgreSQL has done). Cheers, Paul Vinkenoog
Re: [firebird-support] Script with 'Drop' in it
Hello Todd, I would like to add a script that does a DROP EXTERNAL FUNCTION SQRT. The problem I have is that some of the databases that will run this script already have the UDF dropped and so the script errors. Is there a way to run statements like DROP INDEX, DROP EXTERNAL FUNCTION, etc. in a script without generating an error of the object isn't found? In a pure SQL script, I wouldn't know. But your application could check if the UDF name exists in RDB$FUNCTIONS and if so, execute DROP EXTERNAL FUNCTION. Cheers, Paul Vinkenoog
Re: [firebird-support] Re: INSERT ... RETURNING and updatable view
Emil wrote: It might help others if a note is inserted somewhere in the documentation, that for the RETURNING clause to work correctly for updatable views with generated, default or computed fields, the respective NEW variables must be explicitly assigned. Good point. I'll put that in the tracker for the doc subproject. Paul Vinkenoog
Re: [firebird-support] Re: server version
Ann Harrison wrote: That works - if the difference between 1.0 and 1.5.6 doesn't matter to you - but the OP wanted a solution that didn't require opening a database connection first (and as we now know, he needs it for JDBC). That's a little like wanting to know the color of your prize without opening your eyes. You could have someone whisper it in your ear ;-) Anyway, you can get that information from the Services Manager without opening a _database_ connection. With JDBC it turns out to be not that simple, but Mark Rotteveel posted a solution in Firebird-Java. Cheers, Paul Vinkenoog
Re: [firebird-support] Re: server version
Hi Wolfgang,
I am using
Try
Qry.Close;
Qry.SQL.Text := 'select rdb$get_context(''SYSTEM'',
''ENGINE_VERSION'') cVer from rdb$database'; // fails on engine version 2
Qry.ExecQuery;
cEngineVer := Qry.FieldByName( 'cVer' ).AsString;
Qry.Close;
Except
cEngineVer := '1.5.6'; // default if engine version =2 cannot be
verified
End;
That works - if the difference between 1.0 and 1.5.6 doesn't matter to you -
but the OP wanted a solution that didn't require opening a database connection
first (and as we now know, he needs it for JDBC).
Cheers,
Paul Vinkenoog
Re: [firebird-support] server version
Hello Hugo, Is there a way to determine which version of Firebird is running before connection to a Database? From within your application, you can connect to the Services Manager and then call isc_service_query(), specifying isc_info_svc_server_version in the request buffer. This is documented in the IB6 API Guide. Many clients (e.g. FlameRobin) can also give you this information without having to connect to a database first. Or, from the OS command prompt: fbsvcmgr [hostname:]service_mgr -user username -password password -info_server_version This requires Firebird 2.1 or higher on the client machine. Hope this helps, Paul Vinkenoog
Re: [firebird-support] I need help-Syntax error - ;
Hi Farshid, What is the problem with the following code? Without examining it very closely, I see several problems: CREATE PROCEDURE SPTR You don't specify any output parameters. But the SUSPEND later on suggests that you want to return something to the caller. So you need a RETURNS clause. AS DECLARE VARIABLE price INTEGER; DECLARE VARIABLE price1 INTEGER; DECLARE VARIABLE eid SMALLINT; DECLARE VARIABLE bsum INTEGER DEFAULT 0; DECLARE VARIABLE SCTN CURSOR FOR ( Select ID,DBR,CDR From TBL_Transactions ORDER BY SDate); BEGIN OPEN SCTN; FETCH SCTN INTO eid, price,price1; WHILE (1=1) DO BEGIN bsum = bsum + (price-price1); update TBL_TRANSACTIONS set CBALANCE = bsum where Id = eId; FETCH SCTN INTO eid, price,price1; END The WHILE loop will never end, because there is no LEAVE statement and 1=1 will remain true forever. You probably want something like if (row_count = 0) then leave; after the fetch. BTW, what if the first fetch (before loop entry) returns no data? Your code doesn't seem prepared for that. Because of the endless loop, the code below will never be reached: CLOSE SCTN; --DEALLOCATE SCTN; SELECT ID,DBR,CDR FROM TBL_TRANSACTIONS ORDER BY SDate; You have to select those values INTO local variables and/or output parameters. SUSPEND; SUSPEND gives the caller the opportunity to fetch the current row of output parameters. But as said before, you haven't declared any. Not sure if this is all that's wrong, but it's a start! ;-) HTH, Paul Vinkenoog
Re: [firebird-support] Re: expression evaluation not supported
Jonathan wrote: Sorry you are forced to use something as great as Firebird :-). I assume LAST_MODIFIED_ON is a TIMESTAMP field in your table and is being updated via a trigger or some other mechanism as records are modified. Your DATEDIFF syntax is incorrect; try: ... WHERE LICENSE_PLATE_NO'No plate found' AND DATEDIFF(second,LAST_MODIFIED_ON,current_timestamp)120 or ... WHERE LICENSE_PLATE_NO'No plate found' AND DATEDIFF(minute,LAST_MODIFIED_ON,current_timestamp)2 This should work, but it's not Jupiter's syntax that was incorrect. The FROM .. TO syntax is fully supported. Point is, you're not allowed to mix TIME and TIMESTAMP arguments with DATEDIFF (DATE and TIMESTAMP is OK though). So, since LAST_MODIFIED_ON is obviously a TIMESTAMP, current_time should be current_timestamp - as in your corrected example. http://www.firebirdsql.org/file/documentation/reference_manuals/reference_material/html/langrefupd25-intfunc-datediff.html Cheers, Paul Vinkenoog
Re: [firebird-support] Multiple rows in a EXECUTE PROCEDURE
Hello Walter, With the following Stored Procedure: SET TERM ^ ; CREATE PROCEDURE TEST RETURNS( IDENTI TYPE OF COLUMN CLIENTS.CLI_IDENTI, NAME TYPE OF COLUMN CLIENTS.CLI_NAME) AS DECLARE VARIABLE MYCOMMAND VARCHAR(80) ; BEGIN MYCOMMAND = 'SELECT CLI_IDENTI, CLI_NAME FROM CLIENTS ORDER BY CLI_NAME' ; FOR EXECUTE STATEMENT :MYCOMMAND INTO :IDENTI, :NAME DO SUSPEND; END^ SET TERM ; ^ I get multiple rows when executing it with the Execute option of the SQL Manager. However just one row with ISQL or a program. EXECUTE PROCEDURE TEST; With ISQL or with a program the prior line show me just one row. Why that? And how can I get all the rows? Since this is a selectable stored procedure, you should retrieve its output (a dataset) like this: select identi, name from test HTH, Paul Vinkenoog
Re: [firebird-support] Help required
Hi, Could you please let me know what is the equivalent of varbinary( max) of SQL datatype in Firebird. In general, BLOB with an appropriate sub-type. I'd rather say varchar(max) character set octets, unless max is between 32K and 64K. In that case you need a BLOB in Firebird, whereas MySQL supports (var)chars up to 64K. Cheers, Paul Vinkenoog
Re: [firebird-support] Perl, Firebird, and empty Where clause
Hello Daniel, I'm trying to have an existing perl program, that is using the DBI system for theoretical database interoperability, to use Firebird. I've discovered that, as a standard, this program executes statements such as, select * from table where pkey='' That apparently works with Mysql - doing such a select with a specified empty where clause will return the full table. I can assure you that not even MySQL would do something as outrageous as that *. At least not current versions. It must be something in an application layer or library (DBI?). This kind of behaviour sometimes makes sense in the backend of a user interface, where the user may fill in filter terms. If he leaves a field open, the program assumes that any value for that field is OK (as opposed to only the empty string). * (unless, of course, every record in the table has pkey=''. But that doesn't seem likely, since pkey is probably the primary key.) BTW, where pkey='' is not an empty where clause. It's a perfectly valid search condition - provided that pkey is a text field. Firebird - probably more correctly - You can leave out the 'probably'. There is only one correct behaviour here. does a comparison for empty/null and returns...nothing. Firebird will look for '' (empty string) in this case, *not* for null, which is something different. If there are records where pkey = '', it will return them. Otherwise, it will return an empty result set. Without changing the perl script, is there a way I can have Firebird actually return the table using the above SQL? No. You would have to intercept the SQL at some point and change it. If you want the whole table, leave out the WHERE clause and if that isn't possible, use a tautology, e.g. ...where 1=1 (or append or 1=1 to the existing clause). Good luck, Paul Vinkenoog
Re: [firebird-support] Re: How to insert only if a matching row does not exist?
Hi Ed,
merge
into emp
using (select 'mango' fruits from rdb$database) src
on emp.fruits = src.fruits
when not matched then insert (fruits) values ('mango')
Nice trick! That seems to work.
Just realized you can make it even easier:
merge
into emp
using rdb$database
on emp.fruits = 'mango'
when not matched then insert (fruits) values ('mango')
Cheers,
Paul
Re: [firebird-support] Table alias with AS in SELECT statement supported?
Hello Reinier, The Interbase 6 Language Reference mentions column aliases using the AS notation, but doesn't show AS in table aliases, e.g. (...) Have I overlooked something, was it already supported in Interbase 6, or do the docs need updating? The InterBase 6 LangRef needs updating ;-) Frankly, I never noticed that the optional [AS] for table aliases was missing from the IB6 docs. Now that I know, I'll mention it in the next revision of the LangRef Update. Kind regards, Paul Vinkenoog
