Re: [firebird-support] Writing to Aliases.conf in non-admin mode
Re: [firebird-support] Writing to Aliases.conf in non-admin mode
Re: [firebird-support] Writing to Aliases.conf in non-admin mode
I did what (I think is what) you need many years ago. I don´t remember exactly the details, but this is still working. What I did was the following: When my application initiates, it checks if there exists a database named MYAPPLICATIONCONFIGURACION.FDB. If it doesn't exist, it creates it. When the application creates the database, it creates 3 UDFs executing SQLs begining with "DECLARE EXTERNAL FUNCTION ..." The UDFs are the following (the names are in spanish): MMudfUbicacionDelAlias It is called with a parameter "Alias". It opens the file aliases.conf, look for the alias and returns a string with the path of the database. MMudfPudeEstablecerAlias It is called with a parameter "Alias" and a parameter "Path". It opens the file aliases.conf, and adds a new line with Alias=Path. MMudfPudeCrearDirectorio It is called with a parameter "Directory". It creates a folder in the hard disk with the name indicated. In the file aliases.conf, there is a line MYAPPLICATIONCONFIGURACION = C:\...\MYAPPLICATIONCONFIGURACION.FDB. When a user wants to create a new database, the application opens the database MYAPPLICATIONCONFIGURACION and executes the following SQL: SELECT MMudfPudeEstablecerAlias('Alias','Path') FROM RDB$DATABASE This can be done using the application in the server or in any client PC of the newtork. Regards Eduardo Mensaje original *Asunto: *Re: [firebird-support] Writing to Aliases.conf in non-admin mode *De: *Stefan Heymann li...@stefanheymann.de [firebird-support] *Para: *firebird-support@yahoogroups.com *Fecha: *23/10/2015 12:28 Bhavbhuti, > My application allows users to create a database and it's entry in > aliases.conf is created by the application. This essentially is > fine and works well in Admin mode, as soon as the same is tried to > be done using the normal non-admin mode I am not able to update aliases.conf > Question is, in normal user mode, is there a way to update > aliases.conf using some FireBird API? or any other way? I don't know of any method that allows this. The aliases.conf file belongs to the administration/configuration of Firebird and that's why it can only be modified by an administrator. It should be possible to change to access rights (once, by an administrator) so "everybody" can have write access to the aliases.conf file. Regards Stefan --- El software de antivirus Avast ha analizado este correo electrónico en busca de virus. https://www.avast.com/antivirus
Re: [firebird-support] Re: Firebird Indexing problem
On Fri, Oct 23, 2015 at 2:40 AM, drci...@yahoo.com [firebird-support] < firebird-support@yahoogroups.com> wrote: > > > I don't think I understand you fully. Indexed reads a hudreds of times > faster than non indexed. > Yes, when you're looking for a specific value. When you're looking for something that is NOT a specific value, then it's often faster to read the table in its storage order rather than reading through an index. In fact, if I remember correctly, the Firebird optimizer will never choose indexed access when the lookup is based on non-equality. > And the table will have millions of records, so I do need them. > Reading millions of records through an index is unlikely to be faster than reading the same millions in storage order. Normally, when Firebird reads records through an index, it constructs a bit map of the record numbers of records that match the indexed condition. The record numbers include the page on which the record is located. If the bitmap include 90% of the pages in the table, Firebird will then read the table in storage order (which is also record number order) skipping 10% of the pages, and the work that went into building the bitmap is completely wasted. "then this is cheaper to scan table (500 records) then use 400 indexed > reads" > What do you mean scan table? How do I do that? Like a stored procedure > with for select? I don't understand, please explain. > You don't have to do anything. The Firebird optimizer analyzes your query, attempting to find indexed paths to data. If it doesn't find usable indexed access, it resorts to a table scan. Good luck, Ann
Re: [firebird-support] Writing to Aliases.conf in non-admin mode
Bhavbhuti, > My application allows users to create a database and it's entry in > aliases.conf is created by the application. This essentially is > fine and works well in Admin mode, as soon as the same is tried to > be done using the normal non-admin mode I am not able to update aliases.conf > Question is, in normal user mode, is there a way to update > aliases.conf using some FireBird API? or any other way? I don't know of any method that allows this. The aliases.conf file belongs to the administration/configuration of Firebird and that's why it can only be modified by an administrator. It should be possible to change to access rights (once, by an administrator) so "everybody" can have write access to the aliases.conf file. Regards Stefan
Re: [firebird-support] Restore without constraints
Köditz, Martin martin.koed...@it-syn.de [firebird-support] wrote: > is it possible to restore a database without constraints? Or is there > another way to do this? I just want do delete each primary key in the db. Is this on a healthy DB? I'm not sure if -I disables the constraints in that case. You can use -I -N. (-N for disabling the constraints). After that you can remove the primary records. See http://www.firebirdsql.org/manual/gbak-cmdline.html But then what If you activate the indexes and constraints again you face the same problem (you can't because of the sub-records). If the DB was build correctly there should have been a ON DELETE CASCADE on the constraint. Maybe you still can change the constraints with a ON DELETE CASCADE. Removing the records from the primary table will delete all sub-records too. Either way... with this you can activate all indexes and constraints again (but only if you also deleted the sub-records with the foreign keys): EXECUTE BLOCK AS DECLARE VARIABLE stmt VARCHAR(1000); BEGIN for select 'ALTER INDEX '||rdb$index_name ||' ACTIVE;' from rdb$indices where (rdb$system_flag is null or rdb$system_flag = 0) order by rdb$foreign_key nulls first into :stmt do EXECUTE STATEMENT :stmt; END ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
RE: [firebird-support] Re: How to do a running total in SQL
Norbert wrote: >execute block >returns (reference varchar(10), due double precision, balance double >precision) >as >begin >balance = 0; >for select >reference,due >from invoices into :reference, :due >do begin >balance = balance+due; >suspend; >end >end Thank you Norbert, that does look like the simplest solution till FB 3 is available.
[firebird-support] Re: How to do a running total in SQL
Hi, execute block returns (reference varchar(10), due double precision, balance double precision) as begin balance = 0; for select reference,due from invoices into :reference, :due do begin balance = balance+due; suspend; end end Norbert ++ Visit http://www.firebirdsql.org and click the Documentation item on the main (top) menu. Try FAQ and other links from the left-side menu there. Also search the knowledgebases at http://www.ibphoenix.com/resources/documents/ ++ Yahoo Groups Links <*> To visit your group on the web, go to: http://groups.yahoo.com/group/firebird-support/ <*> Your email settings: Individual Email | Traditional <*> To change settings online go to: http://groups.yahoo.com/group/firebird-support/join (Yahoo! ID required) <*> To change settings via email: firebird-support-dig...@yahoogroups.com firebird-support-fullfeatu...@yahoogroups.com <*> To unsubscribe from this group, send an email to: firebird-support-unsubscr...@yahoogroups.com <*> Your use of Yahoo Groups is subject to: https://info.yahoo.com/legal/us/yahoo/utos/terms/
Re: [firebird-support] How to do a running total in SQL
This will become simpler with Firebird 3, which implements windowing functions. Simple cases can also be possible with older versions and some imagination, e.g. with tmp (Reference, SumDue) as (Select Reference, Sum(Due) From Invoices Group by 1) Select Reference, SumDue, (select sum(SumDue) From tmp t2 where t1.Reference <= t2.Reference) RunningTotal >From tmp t1 Order by 1 Though sometimes this can be time consuming, Set 2015-10-23 8:40 GMT+02:00 Maya Opperman m...@omniaccounts.co.za [firebird-support] : > > > Hi, > > > > I’d like to add a running total to my result set. For example: > > > > Table: Invoices > > > > Reference Due > > Invoice1 50.00 > > Invoice2 30.00 > > Invoice3 20.00 > > > > I’m guessing SQL would be something like: > > Select > > Reference, > > Due, > > Sum_Total(Due) as Balance > > From Invoices > > > > Desired Result: > > Reference DueBalance > > Invoice1 50.00 50.00 > > Invoice2 30.00 80.00 > > Invoice3 20.00 100.00 > > > > I know I can do this quite easily from within a selectable stored > procedure, but the problem there is the running total won’t be correct if a > different sort order is specified, which happens often in my real world > application. > > > > Is it possible to do this using just a simple SQL statement? > > > > Thanks > > Maya > > > > > >