Re: [firebird-support] Temporary directories in firebird.conf not being used
sir_wally_lewis wrote: I am using Firebird 2.5 on linux ( CentOS 6 ) I have set the firebird.conf temporary directories variable to point to our large data storage folders. There was some bug regarding this in 2.0, I hope it is not a regression. Could you show us the exact line from firebird.conf? Also, make sure Firebird user has rwx privileges on the directory. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] UTF8 problems
Robert martin wrote: I have a field defined as UTF8. In my app I am trying to write and read data from the field. I am succeeding on some levels but need to need to confirm that the data being written and read from the field is correct. I thought I might put some data in using a tool I knew worked properly with unicode but am having problems. When I try and use flame robin (0.9.2.1851 Unicode) I get the following message... Error: *** IBPP::LogicException *** Context: Statement::Prepare Message: SQL statement can't be 0. FlameRobin shows that message when character set conversion fails and your SQL statement translates to an empty string. It's the underlying wxWidgets library way of working: if it cannot convert between character sets, it clears everything (instead of just the problematic character like some other libraries do). Are you sure that the text you entered in the SQL editor is valid UTF-8? Try changing the connection character set to match that of your system. This depends which operating system are you using? For example, you would set it to WIN1252 for West-European Windows system, or UTF-8 on most Linux systems. Also, try using the latest snapshot build of FlameRobin. Those are as stable as stable release, and have some important bug fixes. If none of this works, please ask further questions on flamerobin-devel mailing list. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: Hosting Firebird in cloud
hrefofficemanager wrote: Costs range from $75 to $400+ per month, plus setup fees. ...in the USA. If you're in Europe, you can get a pretty decent server from Hetzner from 20 EUR upwards with 10TB of free traffic per month. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Error using fbcopy
horushardware wrote: Copying table: TABLE1 Connecting to: '' as 'SYSDBA'...Connected. Reading charset: NONE. No need for reconnecting. Connecting to: '' as 'SYSDBA'...Connected. Reading charset: NONE. No need for reconnecting. Disabling triggers...done. New format detected. ERROR! *** IBPP::SQLException *** Context: Statement::Prepare( INSERT INTO TABLE1 (FIELD1,FIELD2,FIELD3) VALUES (?,?,?) ) Message: isc_dsql_prepare failed SQL Message : -104 Invalid token Engine Code: 335544569 Engine Message : Dynamic SQL Error SQL error code = -104 Token unknown - line 1, column 22 FIELD1 Looks like Dialect1 problem. FBCopy only supports Dialect 3 databases. You can use gfix to switch the dialect to 3 and then do the copy: http://www.firebirdsql.org/manual/gfix-dialect.html Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Tool that helps creating audit tables
Venus Software Operations wrote: I could not find anything beyond 0.9.3.2235 for Windows setup. Can you point me in the right direction as this version does not have Generate Code Audit Log Trigger For Table when I right-clicked a table. Sorry, I meant Create change trigger. BTW, I made a mistake because I do have this audit option in my copy of FR, but that's some rather custom code built for a specific database. It requires that each audited table has a single-column primary key column named ID with immutable value. I could share if someone is interested, just send me a direct e-mail. Please let's continue further flamerobin-related discussion on FR mailing list (I'm cross-posting there as well). Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] Tool that helps creating audit tables
Andrea Raimondi wrote: Quite some time ago, I have seen on this list(I think) someone talking about a tool(maybe FlameRobin?) which has an option to create DDL scripts for audit tables. FlameRobin can create triggers for such tables. Install the latest snapshot version, right-click the table and select Generate code, and then Audit log trigger for table HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: execute statement
Alexandre Benson Smith wrote: To achieve what he wants he will need to write an app (or stored procedure, or whatever) that loops trough the fields and generate the trigger PSQL code like No need to write anything. In FlameRobin, you can right-click the table, select Generate code and then Create change trigger for table. It will display a dialog to select the desired columns and it will generate to complete trigger code. HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Moving primary key to another column
Matthias Hanft wrote: let's say I have a table like this NBR INTEGER PRIMARY KEY SOMEFIELD1 VARCHAR SOMEFIELD2 VARCHAR [...] Now I don't want to be NBR the primary key any more (the numbers there have a special meaning where I need to have some 0 entries now); I'd like to insert another ID colum which should be the new primary key (and filling it automatically from 1 to N by a generator). Is this possible within the live database/table, or do I have to create a new table with the colums needed, and do some INSERT INTO NEWTABLE SELECT FROM OLDTABLE and finally rename the new table to the old name? The server is FB 2.0.3; I have IBExpert and FlameRobin available. All explained here: http://firebirdfaq.org/faq380 Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] 2 BDs compair
mahdoom_a wrote: I have 2 Batabase files with same stucture and almost same data. one of them was offline and other is still online,, some mistake happend now the online is not completed has all the data some of the missing data is in the other Database file. I want the changes only in one table not all database tables,, how can I do automaticly not manually, because the recoreds number is about 3000 records. You can do it with FBCopy. First run with D parameter to generate list of tables. Save that list to a text file and then edit it - remove all the tables you don't want to compare, leave only those you want. Next run it with X parameter and supply the text file as input. More details in FBCopy manual: http://fbexport.sourceforge.net/fbcopy-manual.html#compare HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Moving primary key to another column
Matthias Hanft wrote: 4. fill the new column using the generator: update MyTable set NewKey=(select gen_id(MyGen,1) from RDB$DATABASE) update MyTable set NewKey = gen_id(MyGen,1); Brilliant :-) (I already wondered how to fill up an incrementing column automagically.) Well, now you know of 2 ways to do it ;) -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] Restoring backup from 2.5 to 2.1
Aldo Caruso wrote: When I try to restore a backup done in a 2.5 firebird server to a 2.1 firebird server, I get the following error message: gbak:transportable backup -- data in XDR format gbak: ERROR:Expected backup version 1..8. Found 9 gbak:Exiting before completion due to errors Isn't it possible to restore a backup database from a 2.5 to a 2.1 firebird server ? It is not possible since 2.5 introduces incompatible changes. If you need to move from 2.5 back to 2.1, the only way would be to copy data from one database into another. I tried this in the past and met with another problem: which version to use to access the databases for copy. IIRC, 2.5 did alter the database structure so I could not use it with 2.1. The final solution was to start up the source database on 2.5 server and prepare a clean database on a different machine with 2.1 server. And then I copied the data using FBCopy tool. To prepare a clean database, you can use something like isql -x to dump the database schema from 2.5, and then run that script on an empty database with Firebird 2.1. Good luck. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: Query a table within its trigger
hanszorn2000 wrote: declare LOCATION integer; declare ARTICLEID varchar(20); BEGIN select first 1 NEWLOCATION, ARTICLEID from ARTICLE where ARTICLE.CUSTID = NEW.CUSTID and ARTICLE.ISACTIVE = 'F' and ARTICLE.NEWLOCATION is not null order by NEWLOCATION into :LOCATION, :ARTICLEID; NEW.NEWLOCATION = LOCATION; You have a potential bug here. What if select does not return a row? In that case variable LOCATION has uninitialized value, i.e. it could contain anything (perhaps a value from some previous run). Try adding... LOCATION = NULL; ...before the SELECT query. HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Return key
Mags Phangisa wrote: I have changed the field to a text blob but the hard return is still not being recognised. I use ibase_blob_echo($rec-FLDNM) to display the data. What could I be doing wrong? ibase_blob_echo() means you're probably using PHP, so you're probably using HTML as output. HTML does not render newline characters by default. You have two options: 1. wrap the output in pre/pre tags 2. use nl2br PHP function. In this case you cannot use ibase_blob_echo as you need to pre-fetch the blob data into string. To extend the BLOB example from PHP manual, you would use something like this: ?php $result= ibase_query(SELECT blob_value FROM table); $data = ibase_fetch_object($result); $blob_data = ibase_blob_info($data-BLOB_VALUE); $blob_hndl = ibase_blob_open($data-BLOB_VALUE); echo nl2br(ibase_blob_get($blob_hndl, $blob_data[0])); -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Object in use
arda wrote: Maybe the database can store the versions of stored procedures transparently? AFAICT, this already works that way, at least with Firebird Classic 2.1 on Linux which I mainly use in production. Which version of Firebird are you using? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Activity of the firebird-support group
arda wrote: I've just looked at the message count over years (jan-june) 2004 : 9713 messages 2005 : 9695 messages 2007 : 4497 messages 2009 : 3778 messages 2012 : messages What do you think? People are moving away from mailing lists, or, to be more precise, younger developers prefer other communication channels. Especially since there are many more websites and resources on the Internet that cover the basic questions beginners might ask. I met a few young developers some time ago, they asked some questions about Firebird. I directed them to this mailing list, but they were like Mailing list? That's so 90's. A few days later I found their questions posted on StackOverflow website. The way people communicate online has changes in past couple of years. USENET is practically dead, and mailing lists are slowly decaying. Even classic web forums are getting replaced with sites like SO or some sorts of social networks. If you want to track Firebird popularity, I would rather look at download counts. Although that is not very precise to get number of installations (you can download once and deploy thousands of copies), it does give a rough estimate of the number of developers, as they do download a couple of copies and that's about it. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] SOLVED (brute force) -Re: cannot restore db (time and timestamp)
pablosanchezuy wrote: I did the conversion in flamerobin. Selected the table on the tables tree, opened the form and changed the attribute types. I expected some trouble as i didn't know if there was some data in the columns, but no hassle then . When you do that in FlameRobin, it generates: ALTER TABLE tablename ALTER columnname TYPE TIME; Which in turn means that Firebird allowed the conversion without any errors. Maybe you should try with latest 2.1 and see if you can reproduce the bug and then submit to the bug tracker. I see that you backed up using 2.1 and trying to restore using 2.5. Does the restore work on 2.1? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] output from SQL
ktadimeti wrote: I wish to output text from a stored procedure. How do I do this? There are different way, depending what you really need: 1. You can create a selectable stored procedure: create procedure p1 returns (output_text varchar(3000)) as begin for select something into :output_text do suspend; end If you wish to get this into a text file, you can use ISQL or a tool like FBExport. FBExport could be used like this: fbexport -SC -Q select * from p1 -f- -d/path/to/database.fdb The result is a quoted string, but you can use a tool like sed to remove quotes. 2. You can create an external table (which almost like a regular textual file if you use CHAR data type) and INSERT into it. 3. If you only want to post events from the database, it's better to use the Events functionality. Maybe you can tell us why you need this feature, and we can tell you what is the best solution for that. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How to improve update performance with millions records?
firebird_jimmy wrote: Hi, I'm jimmy I have a problem, please help me! I have a table with 3,000,000 rows record, every row update 1 to 5 times in one day. The speed of update statement become slowly, about 30 records/second, but insert speed above 1000 records/second. Is my usage be bad or not? what should i do? Any foreign keys, triggers, secondary indexes or check constraints involving computed fields? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How to limit the number of concurrent users to a database
Fulvio Senore wrote: Do you think that this a reasonable solution, or are there better ones? How about this: Create ON CONNECT database trigger and read number of connected users from MON$ATTACHMENTS. If it's larger than what you want, throw an exception. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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 to limit the number of concurrent users to a database
Michael Ludwig wrote: Fulvio Senore schrieb am 13.06.2012 um 22:17 (+0200): My purpose is very simple. The users can install the program on any number of computers connected to a Firebird server, and I want to limit the number of simultaneous connections. Do you control the server? If you don't it'll be difficult to enforce anything anyway. But if you do you might investigate whether it's possible to configure this at the network level, like with iptables on Linux You use Firebird Classic, you can also limit the number of connections via xinetd: http://www.firebirdfaq.org/faq161/ HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: gbak to a non existing directory - Email found in subject
ma_golyo wrote: a. This is plan B. b. It's very creative, but ugly... :) How about: c. Modify Firebird source code, before it tries to create the database file, check for directory existence, and then create it. It seems exactly what you need and any decent C/C++ programmer should be able to implement this functionality quickly. ;) One question though. firebird user usually has no privileges on *any* directory in the filesystem by default. How would it be able to create the sub-directory then? After setting up firebird you have to give it rights to some directory, why not create the directory as well at that point? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: gbak to a non existing directory - Email found in subject
ma_golyo wrote: To stat a new customer we have to #1 : copy the app to new folder (win) #2 : copy the initial DB to new folder (linux) #3 : update inital DB metadata to current app version I'm writing (Delphi) app which does the steps above. App runs on the terminal (win) server. #1 : OK #2 : not OK. I cannot create new DB with FB, and I cannot restore a DB with GBAK to a non existing directory. I'm not sure about Delphi's capabilities here, but I see two possible solutions: a) log into Linux using SSH protocol and create directory using the system mkdir command with --parents switch. Then set up privileges with chown and chmod. b) write small Linux UDF that creates directories, register it in some database (employee.fdb for example) and call it from code: db.Query(select createDir(/path/on/linux) from rdb$database); HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: Embedded Firebird leaves a lot of temp files behind
Alec Swan wrote: I also confirmed that Flamerobin still works even if I delete firebird.conf from directory where flamerobin_unicode.exe resides. So, Flamerobin must have firebird.conf packaged internally, which sounds kind of weird. FlameRobin does not use firebird.conf at all. It uses IBPP library which has it's own logic of finding firebird client library (it searches the Registry, current directory, program files, etc., you can look into source code if you're really interested) -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] why the result is the number commas?
W O wrote: Never use double precision for money neither for count something, use it for measures. Actually, you can use it to _count_ as long as all individual items are integers. Double precision is built that way to keep integer arithmetics clean as long as all operands are integers and results of all intermediate operations are integers. So: integer * integer = ok integer + integer = ok integer - integer = ok integer / integer = could present problems Since _counting_ is only addition, it is safe to do even with double. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: AW: [firebird-support] statement and composite variable names
Olaf Kluge wrote: How can I realize: Fiels_name = 'COLUMNNAME''? Fiels_name = ''COLUMNNAME' I have test all combinations from one ' to four. How about five ' ? ;) -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Libs on Linux
Matthias Hanft wrote: is http://www.firebirdfaq.org/faq47/ still true? That FAQ is meant only for users that have problems with libstdc++. The way you think, it was not true for a large portion of users the day when it was written. And again, I don't seem to need it... do I? No, if you can start FB, you don't. Recompiling Firebird as well helped (does it incorporate links to glib and/or libffi at compile time? revdep-rebuild didn't find any). Is running Classic and option for you? That might work. Since Firebird is a binary package there (directly from the firebirdsql.org website), I can't recompile it. Of course you can, I did it many times. Just take the source code tarball, unpack, build and install. In the long run it might break when you upgrade system, but you can recompile it then once more. PS: Sometimes people are talking about the DLL hell on Windows. Seems that Linux isn't much better in this respect, eh?! Different religion - different kind of hell ;) Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] There is a way to format datetime directly in SQL ?
hamacker wrote: There is a way to format a datetime inside a query ? You can find some interesting ideas here, ranging from UDFs to SUBSTRING tricks: http://www.firebirdfaq.org/faq314 HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Is it possible to know if people are using any FDB (from command line?)
venussoftop wrote: I was wondering if there was a way to find out if people are using any of the FireBird Databases on a given computer. Define using. Do you mean using via Firebird server or reading/writing the file? Do you have Firebird server or use embedded? If you want to see if databases are used by the server, find the server process and look at open files. For example, using Firebird classic on Linux and databases with FDB extension you can use lsof with grep like this: lsof | grep fb_inet_server | grep .fdb Of course, you can also go step-by-step. Use ps to get the PID of FB server processes and constrain lsof to those. Or, if you know paths to database files, you can use fuser command to see which processes attach to it. (although, if you know all databases, you can connect and use monitoring tables as well). If this can be done from a command file, rather than interactively, that is all the more better. Which operating system are you using? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Assign Double Precision value to a varchar field in Stored Procedure
Ivy wrote: 1) I have a double precision variable ( e.g. myMoney ) which after some calculation, it ends up with figure -0.0199868555. 2) When I want to assign this value to a varchar field ( e.g. MoneyInStr) , I do the below statement :- MoneyInStr = myMoney; What are the types of MoneyInStr and myMoney. Can you show the DECLARE VARIABLE statements? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Backup and restore of users
Aldo Caruso wrote: Which are the steps to follow to backup and restore database users and passwords ? I usually just stop the server and copy security2.fdb file. Unlike the databases, it is rarely written to. I mean, how often do you open new user accounts. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: Newbie questions regarding FB Embedded
cniggeler wrote: The manual states, Under Linux, you always need a properly installed server, be it Classic or Super I don't know which manual you're referring to, but that's just plain wrong. All you need is libfbembed.so and couple of other helper files. c) root access is required, at least according to the manual That is not true even for Classic, let alone embedded. The lock manager would try to run as root, but if it fails it would run under user account without any problems. See this guide: http://www.firebirdfaq.org/Firebird-Embedded-Linux-HOWTO.html It's written for Firebird 1.5, but it all still aplies (you just need to add ICU library files from classic server tarball). -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Importing from spreadsheet
Mags Phangisa wrote: Is there a way I can import data from a spreadsheet into a table? Any help will be greatly appreciated. Save to CSV format and use XMLWizard: http://www.guacosoft.com/xmlwizard It can detect and suggest datatypes, table structure, etc. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] what the most efficient way to do update or insert with value ?
nathanelrick wrote: i want to do something like update or insert into mytable(ID, amount) values (123, amount + 100); how to do this ? Use MERGE: http://www.firebirdsql.org/refdocs/langrefupd25-merge.html If you have an older Firebird version and still want a single SQL command, you can use EXECUTE BLOCK as explained in http://www.firebirdfaq.org/faq220/ -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How reliable is the event feature of Firebird
ik wrote: The daemon use the information that the web provides. But instead of constantly checking for additions and changes made by the web, I want the daemon to use the events feature. I know that PostgreSQL is not very reliable with this feature, so I wish to know how much I can count on the events of Firebird, or if you have better ideas on how to do it. I'm running 100+ systems using events for more than 5 years, some of them 24/7 and I never lost a single event. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Create Log for tables change, which one is better via app or via firebird?
trskopo wrote: Or, I can record changes via my app, which one do you thing is better, regarding database performance and size? Database would work faster and have smaller size if logging is done in application. Therefore, if those are the most important parameters, you should do it in application. If you have multiple applications accessing the database this really depends whether you need separate logging for each client or not. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How do I convert a timestamp to a string from within a stored procedure?
SoftTech wrote: Does anyone know how I can accomplish this from within a stored procedure? I need to convert a timestamp 01/26/2012 05:38:32am to a string 01262012053832. Will actually be using CURRENT_TIMESTAMP within the stored procedure. You could use EXTRACT() to extract day, month, year, hour, etc. and use || operator to concatenate numbers into string. To make sure numbers are zero padded you can use STR_PAD or CASE like this: case when number 10 then '0'||number else number end I mention CASE because STR_PAD is not available in older versions of FB. Alternatively there are UDF libraries (rfunc for example) that provide date format functions. So you could write something like: DateToStr(current_timestamp, '%d%m%Y%h%M%S') Still using Firebird 1.5.3 (Hope to convert down the road) In that case, use DateToStr or CASE. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Sizing conversion project
Rick Debay wrote: We are using Firebird and I've been tasked with determining the costs for migrating to MS SQL Server. The last time I used the latter was ten years ago, and in the role of a Java programmer. Does anyone have any suggestions for how to come up with rough time and cost estimates? You mean beside licensing cost? That depends largely on your applications and databases, so we would need more details: Do you use some Firebird specific features like Events? What programming language/libraries are used? It's easier to make a switch if you use Java application than if you use IBPP C++ library for example. Java might require a simple reconfiguration, while C++ would require rewrite of database access layer. If code is not properly decoupled from database access it can mean rewrite of large portions of the codebase. Also, I'd like pointers to FB vs. SQL Server comparisons, so I add those to the analysis. Obviously if the benefits don't exceed the costs of the migration, it won't make sense. As someone who migrated from MSSQL to FB, I'd like you to first tell us why are you even considering going from rock-solid robust open source database to a closed one which has similar performance and feature set? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [Bulk] [firebird-support] Is Update or Insert more slower than an single Update (or insert)
Vander Clock Stephane wrote: Thanks to my paranoia, i was thinking like everyone that Update or insert OR Insert Well, it is called UPDATE OR INSERT, and not INSERT OR UPDATE. As you can see, update comes first. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Migrating from MySQL to Firebird
Go Green wrote: I have never used firebird and therefore wanted to know how it fits when compared to MySQL in terms of: If you use MyISAM, then the answers are: ANSI SQL compliance Firebird is much better. Triggers Firebird is much better. Foreign Keys Firebird is much better. Stored Procedures Firebird is much better. High Availability Firebird is much better, for example, you can do hot-backup while system is running. I can't tell about InnoDB, as I never used it myself. The only two reasons I still use MySQL on some projects are: 1. speed 2. availability of transaction-less storage (MyISAM) which saves you from having to backup/restore periodically to reset transaction counters Note that 1. and 2. are related, MyISAM is much faster than Firebird just because it has no transaction overhead. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Tools to migrate from MySQL to Firebird
Rohit wrote: I want a free tool to migrate an existing MySQL database to Firebird. Is there any tool? I wrote a quickdirty PHP script that works on SQL structure dump from PHPMyAdmin: https://github.com/mariuz/mysql2firebird It's still rough and does not support all MySQL datatypes, but it's open source, so you can modify it to fit your needs. I also wrote a simple data-pump in PHP, let me know if your're interested, I can put it for download as well. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How to return only a few rows from select sql statement
trskopo wrote: Suppose I have a sql statement : select * from table Is that possible to return only a few rows from that statement (not the whole result), just like MySQL? http://www.firebirdfaq.org/faq111 HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: How can I get a list of firebird db users?
Trond wrote: If you use Firebird version = 2.1 you can use something like this: SELECT ma.MON$USER, ma.MON$REMOTE_ADDRESS,ma.MON$TIMESTAMP, ma.MON$REMOTE_PROCESS, ma.mon$attachment_id, ma.mon$attachment_name, ma.mon$role, ma.mon$state, ma.mon$stat_id, ma.mon$server_pid,ma.mon$remote_pid FROM MON$ATTACHMENTS MA WHERE ma.MON$ATTACHMENT_ID CURRENT_CONNECTION Those are only connected users. He wants to get a list of all user accounts on the server. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] A query about foreign keys
Marco Aurelio Castro wrote: What is the query that I inform the name of the Foreign Key and it returns the names of the tables and fields related to this foreign key? You need to join system tables RDB$RELATION_CONSTRAINTS, RDB$INDICES and RDB$INDEX_SEGMENTS. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] UTF8 in firebird ?
Vander Clock Stephane wrote: let speak first about the utf8 UTF8 it's just a way to encode special character like è à etc .. for this utf8 will use combination of char upper than ascii #127 In this way, and it's not the less, UTF8 stay compatible with all software that work with 8 bit string. thank to this we can use a firebird database defined as iso8859_1 to store UTF8 That is not true. Some characters have different code points, for example British Pound symbol, Japanese Yen symbol, Copyright (c) symbol, etc. UTF8 is 100% compatible with ASCII, which is not the same as iso8859_1. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] ASCII OR ISO8859_1 to store UTF8 char ?
Vander Clock Stephane wrote: juste one question, i thing both are the same but with is the best to store UTF8 code point ? ASCII or ISO8859_1 ? If you really do not care about collations, then OCTETS. Stuff like UPPER, ORDER BY, etc. would not work properly anyway, so why bother. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Converting BLOB to long VARCHAR
Josef Kokeš wrote: I am trying to convert a BLOB (unlimited size, theoretically) to a long VARCHAR (say, VARCHAR(8192)). This should be easy enough to do: DECLARE VARIABLE b BLOB; /* input */ Which subtype of BLOB? IIRC, BLOB subtype 1 (text) should support character sets, so you don't have to lose non-ASCII characters. If it's a binary blob (subtype 0) you can also try to dump the contents to a file (using FBExport or your code) and then import it back into a new subtype 1 blob. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard == ++ 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] Re: Firebird 2.5 64bit for Intel Proc
trskopo wrote: I have installed CentOS 6.2 64 bit on Intel dual core 1.8 GHz Me too. It works. Error message was incompatible hardware. I have never seen a RPM package show incompatible hardware as error message. It could report kernel version or glibc version, etc. What was the exact error message? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: comments/advices on database design change please
Aage Johansen wrote: Milan, isn't it so that small blobs are stored on database pages with the rest of the record? Yes, but those are only blobs that fit into page. Since FB reads the whole page anyway those should not affect performance. can remember the advice given in a session at the Luxembourg conference was that keeping blobs in separate tables was (could be?) a good thing I fail to see any reason why? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Speed of an query with IN select
cornievs wrote: Is there a way I can speed up the following query? Select CODE, STKCODE, DESCRIPTION from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' and STKCODE in (Select STKCODE from (Select STKCODE, COUNT(STKCODE) as COUNT1 from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' GROUP BY STKCODE order by STKCODE ) where COUNT1 1) order by STKCODE First of all, you can remove inner ORDER BY, it only slows things down. Here are some faster queries: 1. Assuming different CODE for the same resulting STKCODE: select code, stkcode, description from count_sheets_detail d1 where code starting with 'TPL' and exists ( select 1 from count_sheets_detail d2 where d1.stkcode = d2.stkcode and d2.code starting with 'TPL' and d1.code d2.code ) order by 2; 2. If you use Firebird 2.1 or above: select CODE, STKCODE, DESCRIPTION from count_sheets_detail d1 join ( select STKCODE, COUNT(STKCODE) as COUNT1 from COUNT_SHEETS_DETAIL where code STARTING WITH 'TPL' GROUP BY STKCODE ) order by 2 HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Finding the differences
si_carter_987654321 wrote: I have two almost identical sets of records, what would be the most efficient way of detecting records that are different in the table based on a single column? If you only want to view the difference, I recommend using FBCopy with XH option. It creates a nice HTML table showing both old and new values. Otherwise, do the inner join on key columns and use IS DISTINCT FROM on all non-key columns. HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: comments/advices on database design change please
ehaerim wrote: 1) Can you tell me more how efficient by keeping blobs outside of the main table? It is not really any more efficient. The blob in main table would only keep blob ID there - actually blob data is stored on a separate database page. You would essentially be duplicating what Firebird is already doing. Depending on the database access library you are using, sure you don't SELECT * but rather name the columns when you don't need the blob. Some libraries fetch the blob data at once, and this is not what you want. 4) BTW, what's YMMV? Internet jargon: Your Mileage May Vary, i.e. the above is my opinion, things may be different in your particular case. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] 1000 databases per server ?
Vander Clock Stephane wrote: so is it a probleme to have on one server 1000 databases (does it's will use more memory or more power resource than a single database) ? As for Firebird, this depends on the server architecture used and available RAM and your expectation of concurrent users using different databases at the same time. Beside that, this could pose a problem for some filesystems if you stored them all in a single directory. I suggest you desing a hierarchy of subdirectories in such way that you do not have more than 100 database files in each. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Firebird 1.5 - Stored procedure error 335544348
Nando wrote: ISC ERROR CODE:335544348 no current record for fetch operation. Use LEFT JOIN: http://www.firebirdfaq.org/faq144 -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Copy a database to a blank one
Sergio H. Gonzalez wrote: Hello! I'm using FB2.5. Is there any command line to copy a database to a blank one? I mean, I need to create an empty copy of a given database, but I'd like to do it using a command in a bath file... I hope I'm clear! I'v been googling but had no luck!. -sergio Easiest way to do it with gbak: gbak -m database.fdb empty-backup.fbk gbak -c empty-backup.fbk empty-database.fdb If you already have a backup, you can use -m to only restore metadata. gbak -c -m some-backup.fbk empty-database.fdb -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] XML Data type
Mahesh Pratihari wrote: Could you please let me know the best fit data type in XML in firebird, as firebird doesn't support the xml data type? I need the alternative data type of it. If you later need to query this data, then you should really store it in tables instead of a single blob column. A useful tool to help you determine the structure is XMLWizard. It will suggest the table(s), columns and datatypes you should use: http://www.guacosoft.com/xmlwizard HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: SV: [firebird-support] Best Practice re null
homerjones1941 wrote:
I think I will implement the Default solution
Would there be a problem in using Coalesce(...) in addition to setting the
default values? I can't think of any, other than it may be overkill.
There would be no problem, and it is not overkill. Please note that
default is only used if column is not specified in INSERT. If you run
insert into t1 (field1, field2) values ('abc', NULL);
You would still have NULL in the field. Therefore, it might be best to
use COALESCE even if you have default set.
HTH
--
Milan Babuskov
==
The easiest way to import XML, CSV
and textual files into Firebird:
http://www.guacosoft.com/xmlwizard
==
Re: [firebird-support] Detecting default values
Tetram Corp wrote: I thought about this solution but hoped there was a better one because of, you're right, in my case the field has a restricted range but i've an other similar case where there's no limit in value range too bad. I will use this trick for my first case, and think again about app design for the second one Do you have a complete control over database and application? What is the datatype of column? Here are some ideas: If you have a varchar column, you can solve it by increasing the column size by 1 character and make sure default uses all characters. For example, if your field is normally char(3), you can increase it to char(4) and set '' as default. Restrict the data entry in application to only allow 3 characters. -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Importing with a Date type column
Tupy... nambá wrote: I´m experiencing problems trying to import a date column to a Firebird db. I used many possibilities, but with none of them I got to import this column. Import from what? Text file, CSV file, XML file, sql file? What does your input look like? I also tried FBExport but the orientations were not enough to resolve. FBExport is not really good for importing, it's meant to export data. To import data you should try XMLWizard instead: http://www.guacosoft.com/xmlwizard Does someone also have experienced this problem and got a solution ? I got a message wich says conversion error from string (#-413) Is this a real error message? Looks like you are trying to convert which is not really a date. From what you wrote, it seems like you are trying to run a batch of SQL statements. In that case, make sure date format is one that Firebird supports: http://www.firebirdfaq.org/faq137 HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] How can you change the transaction level to READ_ONLY in SQL
dsaunders1971 wrote: I have tried this sort of syntax but just get a dynamic sql error SET TRANSACTION READ ONLY; select * from Booking AFAIK, this is not available in DSQL. What's the full and proper format for this? It depends on the library you use to access Firebird? For example, in PHP it would be a parameter in ibase_prepare() function, and in C++ using IBPP library, it would be a parameter in TransactionFactory constructor. What programming language / data access layer are you using? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: How to insert only if a matching row does not exist?
gastrocus wrote: INSERT INTO T1 (type, name, sysid, flag) values (1, 'Z', 1, 0) WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z') You can apply select to rdb$database table which always returns one row: INSERT INTO T1 (type, name, sysid, flag) SELECT 1, 'Z', 1, 0 FROM RDB$DATABASE WHERE NOT EXISTS (SELECT 1 FROM T1 WHERE name = 'Z'); HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: granting persmissions
si_carter_987654321 wrote: I was after a utility to grant read/write/execute etc on all objects in one hit rather than individually Well, you could simply generate GRANTs by querying the system tables and then copy/paste results back to sql editor. For example, this grants all privileges on all tables and views to all users: select 'Grant all on '||x.RDB$RELATION_NAME||' to PUBLIC;' from RDB$RELATIONS x where not exists ( SELECT 1 FROM RDB$USER_PRIVILEGES a where a.RDB$USER = 'PUBLIC' and a.RDB$RELATION_NAME = x.RDB$RELATION_NAME ) and (x.RDB$SYSTEM_FLAG is null or x.RDB$SYSTEM_FLAG = 0) You can write a similar one for procedures. Regards, -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: wierd gbak behavour
Nick Upson wrote: looking at the logs for other processes, all of the last four occurrances happened at the same time as an isql session was run against the original database. I can't think why there is any need for this isql session and the gbak session creating the new database to interact via the lock manager but the timing is exact to the second for all four. I shall look further back to see if this holds true for earlier sessions. My guess is that isql also uses a connection string without localhost:. Take a look at process list (ps command) and you will probably see two lock managers running under different Linux user accounts. Try adding localhost: to ISQL as well and it should work fine. HTH -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
Re: [firebird-support] Re: wierd gbak behavour
Nick Upson wrote: the commandline used is gbak -V -CREATE 110831130601.fbk -user sysdba -pass genesis /opt/unb/archive/tmp/110831130601.fdb User/pass have no effect unless you give localhost: in front of connection string. Try: gbak -V -CREATE 110831130601.fbk -user sysdba -pass genesis localhost:/opt/unb/archive/tmp/110831130601.fdb Under which user account are you running gbak? -- Milan Babuskov == The easiest way to import XML, CSV and textual files into Firebird: http://www.guacosoft.com/xmlwizard ==
