Re: [firebird-support] Use all space or not on large restore?
You seem to not be solving a problem but only looking to improve something. What are you trying to improve? I say you are just looking for trouble by changing something that is not a problem.
Re: [firebird-support] Have a lots memory, but FB could not take advantages
What are you seeing that makes you think your goal has not already been met? On Mon, Jul 15, 2013 at 12:38 PM, trskopo trsk...@yahoo.com wrote: Hi all, I have a 16GB RAM and since my database file is small size, it can completely runs on RAM. The problem is I can not set FB SuperClasic 2.5.1 on WinXp 64 to take advantages of it. Setting Page Size and Page Buffers is useless. Performance seems just the same whether Page Size set to maximum (16384) or minimum (4096). Same with DefaultDbCachePages, I even set it to zero in firebird.conf. When Page Size and Page Buffers set to minimum value, memory consumption for firebird server also minimum, without any decrease in performance. Page Size and Page Buffers setting only useful when database file set on disk. Any hints, how to take advantage these memory? Thanks and regards Anto ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Have a lots memory, but FB could not take advantages
On Mon, Jul 15, 2013 at 12:54 PM, trskopo trsk...@yahoo.com wrote: Simple, i still have a lot of free RAM. As Sean pointed out, that suggests you may not have achieved your goal but does not definitely indicate that you haven't. Better indication would be file I/O that you would not expect. [Non-text portions of this message have been removed]
Re: [firebird-support] Help on this SQL statment
The problem is that you have asked to summarize the data by grouping on product description but you have also asked for a product code. The server does not know if there is a one-to-one relationship between product description and product code. Therefore, it does not know what product code to use for each product description and,therefore, for each row in the resulting data set. The solution is to add product code to the GROUP BY clause. That way you will get a resulting record for every unique product code-product description combination and the server will know which product code to return because it is the one being paired with product description in that specific grouping. The rule you violated is the one that requires all requested fields in the output result set to be either the result of an aggregation function, such as SUM(), or it must appear in the GROUP BY clause (so the server will know what value to return). On Sat, Jun 15, 2013 at 3:03 PM, Andrew gable andrew.ga...@tesco.netwrote: I am sorry I Really do not understand :( -Original Message- From: firebird-support@yahoogroups.com [mailto: firebird-support@yahoogroups.com] On Behalf Of Nagy Szilveszter Sent: 15 June 2013 5:57 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help on this SQL statment GROUP BY PRODUCTDESCRIPTION there is PRODUCTCODE missing here if you use aggregate functions u have to add all fields to GROUP BY that are not used with aggregate functions From: Andrew gable andrew.ga...@tesco.net To: firebird-support@yahoogroups.com Sent: Saturday, June 15, 2013 7:54 PM Subject: [firebird-support] Help on this SQL statment Hi everyone, Can someone please advise me as to what is happing with this SQL Select statement I am trying to use SQLCommand = vbNullString SQLCommand = SQLCommand SELECT SQLCommand = SQLCommand PRODUCTCODE, SQLCommand = SQLCommand PRODUCTDESCRIPTION, SQLCommand = SQLCommand SUM(PRODUCTQTY), SQLCommand = SQLCommand SUM(PRODUCTTOTAL) SQLCommand = SQLCommand From ProductHistory SQLCommand = SQLCommand WHERE PRODUCTDATE Between ' StartDate ' AND ' EndDate ' SQLCommand = SQLCommand GROUP BY PRODUCTDESCRIPTION SQLCommand = SQLCommand ORDER BY PRODUCTQTY DESC SQLCommand = SQLCommand ROWS 1 TO 25 I am getting the following error -2147467259 - [ODBC Firebird Driver][Firebird]Dynamic SQL Error SQL error code =-104 Invalid expression in the select list (not contained in either an aggregate function or the GROUP BY clause) If someone could explain that to me I would be most greatful. [Non-text portions of this message have been removed] [Non-text portions of this message have been removed] ++ 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 - No virus found in this message. Checked by AVG - www.avg.com Version: 2013.0.3345 / Virus Database: 3199/6413 - Release Date: 06/15/13 ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] How do I get the differences between 2 timestamps as a timestamp.
On Mon, Jun 3, 2013 at 8:01 PM, cornievs corni...@gmail.com wrote: I know I can the get difference with DATEDIFF, but I need the result as a TimeStamp. That does not make much sense. A timestamp is a point in time. A time period is a duration of time, not a point in time. The appropriate type for a duration of time is a numeric type with whatever units you care to measure the duration in. [Non-text portions of this message have been removed]
Re: [firebird-support] Problem with Sharp s and UNICODE_CI_AI
The collation being used should be case insensitive. Test 4 results show it is not, so all is not well. Or am I reading this wrong? On Wed, May 22, 2013 at 10:27 AM, liviusliv...@poczta.onet.pl liviusliv...@poczta.onet.pl wrote: In test 4you compare lower letter f with upper letter F then you got difference. All is ok in this test case [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Refreshing MON@ATTACHMENTS: Limiting the number of users
You obtain the data from the MON$ATTACHMENTS table using a simple SELECT query. Refreshing the query that reads the MON$ATTACHMENTS table is done by comiting the transaction within which the query was issued and then repeating the whole SELECT operation again.This will ensure you see the latest info from the MON$ATTACHMENTS table. However, it sounds like you want the server process to more quickly conclude that other connections have been severed. That is another problem altogether. If the server has not yet concluded that some attachment has been severed, refreshing the query (as described above) will NOT force the server to look again at the current attachments. It does that on its own time. I believe I recently saw someone post the details on how to change your configuration so that the server takes less time to detect severed attachments. Look back in this thread to see if it was there. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: AGAIN (i know, but different) Max optimized... or not?
I would also strongly advise against this design. It is a design for failure. Not immediate failure but eventual failure. On Wed, Apr 17, 2013 at 8:12 AM, chris.waldmann christian.waldm...@rte-ag.ch wrote: As a senior software and database developer, this is my experience: If anything can go wrong, it will! also known as Murphy's law Happy coding Christian --- In firebird-support@yahoogroups.com, skander_sp skander_sp@... wrote: Is not a problem the concurrence, because this specifica case, only a dozen of user working toghether and not probably they insert in the same milisecond, but thks for the notice. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Does anyone know ExitCode/ErrorLevel values for gfix?
I agree. Over the years I've used and built dozens of utility programs that use the error code return value to reflect various status values unrelated to the basic utility operation. Typically 0 means no problems, positive values mean basic ops problem (serious operating errors), negative values mean result status (operating normally). On Wed, Apr 10, 2013 at 7:45 AM, un_spoken brucedickin...@wp.pl wrote: You think wrong. Command line utilities set non-zero exit code if error happens in utility itself. So, even for havy corrupted database gfix will not set error code to non-zero if server can validate database and API call's returns without errors. Because *gfix itself* works without failure. Actually it is totally up to the developer to decide what and in which circumstances an exit code will be returned. ++ 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 [Non-text portions of this message have been removed]
Re: [firebird-support] Permission denied error creating firebird embedded database in OSX
On Thu, Apr 4, 2013 at 10:19 PM, Helen Borrie hele...@iinet.net.au wrote: How do you plan to work a Delphi application with an embedded database and database server on MacOSX? The last few versions of Delphi can create native MacOSX executable apps. [Non-text portions of this message have been removed]
Re: [firebird-support] Re: Firebird 2.5, FibPlus, Delphi 2010, DateTime field set to Null
Wolfgang- The same idea should work with FIBPlus components. The class that represents a field in a table or query should have a Clear method you can call to set the field to NULL. Every DB component set I have seen has this. [Non-text portions of this message have been removed]
Re: [firebird-support] Sweep causes slow hdd
What OS is hosting this setup? I suspect Windows 7.
Re: [firebird-support] Application users and FireBird users
On 12/7/12 2:36 AM, Venus Software Operations wrote: Does it mean that a single user can login using various roles that may have been setup? Yes. If a user has been granted several roles then any one of those roles can be used by that user when connecting to the database. The user can also have several connections to the database, each using a different role. The idea is that a single user can act in different ways at different times by specifying the role they will be playing each time. When they are performing tasks that do not require some privileges they can connect using a role that does not have those privileges granted to it. Later they can connect with another role that does have those privileges to perform some other tasks that require them. What does the default blank role for sysdba mean? That if the data-entry user if they login without a role will have the same powers as sysdba? No. Roles attach additional privileges to the user's account. With no role specified the user is left with the privileges granted to their user account only. Roles provide a pretty good system. I wish multiple roles could be employed at the same time over the same connection but that's about the only improvement I would like to see. ++ 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://docs.yahoo.com/info/terms/
Re: [firebird-support] Application users and FireBird users
On 12/6/12 2:29 AM, Venus Software Operations wrote: Thanks Andre your suggestions are appreciated. I have created the role and provided the privileges to this role. Now it should be a matter to transfering this role and it's privileges (which I am presently working on in the utility) to the production database and assign users there. Don't forget you also have to change the application to have each user login using the new role. ++ 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://docs.yahoo.com/info/terms/
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it.
Re: [firebird-support] How do you 0/null results to a GROUP BY query
On 10/24/12 6:25 PM, Alexandre Benson Smith wrote: Em 24/10/2012 20:21, Doug Chamberlin escreveu: On 10/24/12 5:54 PM, cornievs wrote: I have query which extract the sales per day from a table Select EXTRACT(YEAR from DT) as YEARNO, EXTRACT(YEARDAY from DT) AS DAYNO, SUM(DUE) from CLIENT_INVOICES group by YEARNO, WEEKNO. It works 100%, but only returns the dates with sales, I need it to also include the dates with zero sales, for example: YEARNO DAYNO SUM 2012 015000 2012 026000 2012 030 (or null will be fine) 2012 047000 I would crate a reference table that contains all the years and days that you want to report on. Then join the data you have to that table using an outer join so that all dates in the reference table are in the result set and those that have data from the client invoices will show it. I used to do this way, but with CTE you create that table dynamically, another option is using EXECUTE STATEMENT or a SP to loop trough the days I saw that. However, I don't see the advantage. Using a reference table is simpler and clearer than using CTE or other code to generate the reference values.
Re: [firebird-support] Re: Is there a CHARACTERDIFF() function in Firebird?
On 9/23/12 9:22 PM, red_october2009 wrote: I used UDFs for years until FB2x came along. I view UDFs as a stop gap non-standard solution until a built-in standard solution can be implemented. I would much rather dump all my UDFs and use FB functions instead. Of course, that is one reason for providing the UDF capability. Another reason is to allow database users to implement functionality that does not have widespread appeal or need and which, therefore, will probably not be implemented as built-in functions. That is why I think Andrea suggested UDF was the best solution available to you. In any event, if Firebird were to acquire a text diff capability, I would hope it would conform to some standard. Or at least some widely used convention such as how source code patches are often described/offered/documented/shared. Doug C.
Re: [firebird-support] How to make queries to a temporary result set
On 9/21/12 3:12 PM, HECTOR RIOS wrote: I´m trying to save some sql parsing in a Delphi application by using temporary result sets. Is there such thing in Firebird? For example: some user makes a query with some filters and then wants to make another query over results from previous query. It would be easy if added the filters over the result set without having to mix previous filters plus last query´s filters. Anybody has an easy alternative for this? Sounds like you need to use global temporary tables. These can have a lifetime of the connection to the database which sounds like what would be appropriate. See http://www.firebirdsql.org/refdocs/langrefupd21-ddl-table.html Doug C.
Re: [firebird-support] Can non-sysdba really alter users?
Thanks for posting the summary, Tomasz. Excellent job of sleuthing! I'm glad you figured it out in the end. Doug C.
Re: [firebird-support] Data Import from CSV
On 6/28/12 1:14 AM, jwcane2003 wrote: Looking for a safe and reliable import process from csv data to firebird db. Any pointers or other info appreciated. Database Workbench does a good job. I use the CSV import all the time. Also check out www.cvsexpress.com
Re: [firebird-support] Left join and computed columns
On 6/21/12 3:39 PM, Rick Debay wrote: Correct. There is no row, but we are getting data in the non-existing row. So how do I file a bug report on this? Go to the Firebird main web site http://www.firebirdsql.org Select Development. Look for the Tracker in the upper right corner of the Development page.
Re: [firebird-support] Dealing with inserts from multiple transactions
I think I would challenge whether adding a necessary entity (to satisfy the FK requirement) needs to be in the same transaction as the overall dataset processing. If an Employee, for instance, can be added by any of several ETL processes, does it really matter if one is added outside the main transaction of the ETL dataset? If not, then make the adding of a required entity happen in a separate transaction and commit that transaction before continuing with the main ETL process. That way the ETL process can continue and other concurrent ETL processes can also see that new entity when they get to the point of needing it. If one of the ETL processes ended up failing at some point perhaps a few new entities were created and not removed during the rollback of the ETL process but is that really such a problem?
Re: [firebird-support] Dealing with inserts from multiple transactions
On 6/7/12 3:57 PM, Michael Ludwig wrote: Doug Chamberlin schrieb am 07.06.2012 um 14:07 (-0400): I think I would challenge whether adding a necessary entity (to satisfy the FK requirement) needs to be in the same transaction as the overall dataset processing. Another possibility might be to screen the dataset (which apparently has to be processed in one fell swoop) to detect any missing entities and then to quickly create them, or rather, assert their existence, creating them as necessary. Yes. On further thought I think I would make the whole ETL process a two-pass affair. First pass check for existence of all needed entities and creates ones that are missing. Second pass should do the inserting proper without errors.
Re: [firebird-support] FDB networking
On 3/15/12 6:36 AM, marc wrote: My IT person is away on spring break and I am having trouble getting a my network to access the FDB from another computer. I am getting an error similar to : can not open FDB All permissions are have been given. The database opens fine on the server. Any help would be appreciated. Sounds like you have a conceptual error here. You do not open the FDB from another computer. You access the Firebird server machine from another computer and the server opens the FDB file locally. What program are you using to try to access the database?
Re: [firebird-support] Re: Why it's soo slow ? it's just a very simple select ...
Top posting is when your reply is above the quoted message you are replying to. This message from me uses top posting. In your message in response to Thomas, which is quoted below, you used bottom posting. Bottom posting is strongly preferred by many people because it is more organized to add your reply tot he bottom of a chronological list of messages. On 3/8/12 9:05 AM, nathanelrick wrote: Can you please stop top-posting, because this removes the context of your reply. Or do you read a book from the end to the beginning? ;-) sorry i not understand ? i simply in yahoo.com go to the message and click reply to the message that all ? what i do wrong ? what is top-posting ?
Re: [firebird-support] Re: Timestamp of Database File
On 2/29/12 2:50 PM, todderamaa wrote: Maybe with the possibility of corruption, I should tell him to exclude the database files from backup entirely and only backup the gbk files that are created in the evening. This is the usual way to backup Firebird databases in situations like your clients have. Operating on the live database files is a quick way to corrupted backup copies. If you have not experienced corruption either you have been lucky or have not actually tried restoring from many of those backup copies. The client network admins should be aware of this special case when databases are being used. For this reason some database vendors have an API that backup software can use to ensure the database activity does not interfere with the backup operation. The vendors of backup systems usually charge extra for these modules that handle various databases. Firebird, however, does not have such an API. Steps for a safe, reliable Firebird backup: 1) Run GBAK 2) Copy the GBAK output to your backup media 3) Periodically restore from the GBAK output to a test database to ensure there are no errors.
Re: [firebird-support] Re: Timestamp of Database File
On 2/29/12 3:30 PM, todderamaa wrote: --- In firebird-support@yahoogroups.com, Doug Chamberlin chamberlin.doug@... wrote: On 2/29/12 2:50 PM, todderamaa wrote: Maybe with the possibility of corruption, I should tell him to exclude the database files from backup entirely and only backup the gbk files that are created in the evening. This is the usual way to backup Firebird databases in situations like your clients have. Operating on the live database files is a quick way to corrupted backup copies. If you have not experienced corruption either you have been lucky or have not actually tried restoring from many of those backup copies. I thought that the corruption could actually happen to the live database you were copying, not just the backup. Is the risk only to the backup? The architecture in use has some bearing on the situation. With Firebird's superserver architecture it is my understanding that the server process opens the database files for exclusive, read-write access. If that prevents the backup software from reading the file I generally consider that a good thing. After all, that's what exclusive access is for. But if the backup process has enough privilege to bypass an exclusive lock, who knows what will happen. If Firebird's' classic architecture is being used then each process accessing the file can read it. However, it's hard to imagine a read-only file access would corrupt the file being read. Perhaps better experts than I can give you a more definitive answer.
Re: [firebird-support] Re: Timestamp of Database File
On 2/29/12 3:28 PM, todderamaa wrote: But with this latest issue, we needed the database file and not the backup. The restore of the backup failed, because we had a stored procedure that was selectable that didn't include a 'suspend' statement. I think this must be something that was allowed in Firebird 2.0 but not with 2.1. To fix this, I had to have the database itself. So I could alter the procedure and add the 'suspend' statement. The restore of the gbk file resulted in a half restored database with no procedures or triggers. Yeah, that's why the test restore is so important. It's like reading the backup tape and comparing it to the original. Verifies not only is the content the same but that the tape, itself, is readable. Unfortunately this is one of the biggest weaknesses of Firebird. One of the recurring issues on the support list is backups that will not restore. Very, very messy.
Re: [firebird-support] Re: Timestamp of Database File
On 2/29/12 3:47 PM, Leyne, Sean wrote: The NBackup functionality allows a database file to be quiesced (with changes written to a delta file) to allow for OS level backups to be performed (thru Lock and Unlock options). But doesn't that just move the potential conflict to the delta file, leaving you with the same issue but in a different place? I don't see that it really improves things all that much. Well, on further thought I suppose the original database file could then be thought of as internally consistent as of the time NBackup was started. Is that what you were getting at? Nbackup can be invoked thru a BAT or script file, as well as via SQL statements. But none of those are really an API designed for this specific purpose. Do any backup systems make use of NBackup to control a Firebird database during backup operations? Anyone know?
Re: [firebird-support] What structure would fit better?
On 12/18/11 6:50 AM, ehaerim wrote: Two possible structures. [1] (Symbol, Date, Blob) = Symbol primary and Date is secondary key [2] (Symbol_Date, Blob) = Symbol_Date is single primary key Relational databases nearly always work best when each individual data item (column) is stored separately and is, therefore, individually addressable. Combining values into compound values should only be done when there is a specific reason to do it. Your scenario is well within the design and implementation goals of all current RDBMS systems, so there is no good reason to combine any table columns. It will require extra work on your part and on the server's part to process the compound items. Why do it? Go with [2].
Re: [firebird-support] What structure would fit better?
On 12/18/11 1:10 PM, Ann Harrison wrote: Doug, I was with you up to the last line. Go with [2]. Oops! I meant to say [1]!
Re: [firebird-support] firebird 2.5.1 on Lion Server 10.7.2
On 11/4/11 5:47 AM, Mark Rotteveel wrote: Hi Helen, OSX Lion is OSX 10.7. So that matches the requirement. Yes, the OP's (OSX Lion Server 10.27.2, was a typo. It should have been (OSX Lion Server 10.7.2,
Re: [firebird-support] Why does Firebird write so much data for simple select queries?
On 10/27/11 11:11 AM, dsaunders1971 wrote: I have a question about why Firebird writes / or changes so much data in the database file when you run a simple select query. Two things come immediately to mind: 1) Everything gets read and written to the file system in blocks. So even if you just update one byte in one record at least one whole block is read and written. Of course, that is subject to caching, etc, but if the monitor you are watching is counting bytes you will see lots more moving around than you might have expected. 2) Everything takes place in a transaction and the server needs to track transactions so there is housekeeping done to record when transactions start and stop. This applies to simple transactions that just read data as well as those that change data. So consider an additional block or two being read and written for each transaction. (For example, there is a Transaction Inventory Page which is the most frequently read and written block in the whole database.) I'm sure Ann and others will add more to this.
