RE: [firebird-support] Help required
Thanks From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] On Behalf Of Thomas Steinmaurer Sent: Wednesday, August 01, 2012 12:15 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] Help required Mahesh, 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. If you are storing binary data (images, ...) use a sub-type of 0. If you intend to store text data, use sub-type 1. E.g. column BLOB SUB_TYPE 0 column BLOB SUB_TYPE 1 -- With regards, Thomas Steinmaurer http://www.upscene.com/ [Non-text portions of this message have been removed]
[firebird-support] Re: Possible interaction between Firebird and Skype?
Svein and Reinier, Thanks for replying to my query and for your suggestions. Sorry for not being quick to reply to you both - I'm travelling at the moment, with intermittent access to broadband. The first thing to say is that the problem hasn't recurred. I have also borrowed a netbook, and set up Skype on it, so now I can run the netbook and my laptop next to each other. Short story - perhaps this was a wild goose chase; there doesn't seem to be any interaction of the kind I was suspecting. Either there was something odd going on with my son's computer (possible - he's a developer) or it was just one of those things. I suppose I was thinking about port conflict, but didn't know how Skype acted: the link you sent was helpful, Svein, so were your comments, Reinier. I am very confident it wasn't a CPU/memory resource issue - my laptop was performing normally in every other respect; I had Firefox open at the same time and that didn't show any reduction in performance. Reinier wrote: Try running a tool like Sysinternals TCPView and then start up your application. Does it start listening on ports, possibly blocking inbound connections (if you have listening ports open to the internet at all, which you didn't mention). Monitor CPU/memory usage and see what happens. Try calling the Skype echo service while your application is running. Try calling somebody again on Skype and let people call you while the application is running. I haven't had time to try TCPView yet, but I will, just out of interest. Over the next few days I'll double-check with your other suggestions in as many combinations as possible, but I'll only report back if I find anything definite and repeatable. Again, thanks to you both. Rob
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
[firebird-support] Re: FB Silent install Exe
I am extremely sorry. Today the command Firebird-2.0.3.12981-1-Win32.exe /SILENT ... is working as it is supposed to. I have no idea what went wrong yesterday. Again sorry for wasting your time. [Non-text portions of this message have been removed]
[firebird-support] matrix report
Dear all, I need your help to create this report AQNOJan Feb Mar Apr May Jun Jul..Dec. Total_paid --- --- --- --- --- --- --- ----- 10012000 2000 2000 2000 8000 CHQNO 214 215216 217 it will fill the amount(PAYMNT) in each month (PAYDATE) and its cheque number (CHQNO) table structure is CREATE TABLE RNTSTAT ( STATNO INTEGER NOT NULL, RNTDUE DOUBLE PRECISION, DUEDATEDATE, PAYMNT DOUBLE PRECISION, PAYTYP VARCHAR(30), CHQNO VARCHAR(30), PAYDATEDATE, INVOICEVARCHAR(30), EXPNS DOUBLE PRECISION, XPNSDATE DATE, XPENSTYP VARCHAR(30), MENAINTYP VARCHAR(30), CHRGVALDOUBLE PRECISION, CHRGTYPVARCHAR(30), RNTNO INTEGER, EXNSNO INTEGER, AQNO INTEGER, PANNO INTEGER, CONTNO VARCHAR(30), CHRGPERFLOAT, FROM_DATE DATE, TO_DATEDATE, AWQAFNONEW_DOMAIN ); any hint will be helpful. thanks regards, AHMAD
RES: [firebird-support] Re: How to list the tables and field wich use the primary key from one table as FK
Thank you , I will also try to verify memory ( I know memtest , it's efficient the difficulty is that I am not behind the server but distant and it eed also to be run before Windows , not easy for my client ) . But I think memory is not the problem for all , and after a repair the base usually work normally , if there was a memory problem the corruption would come back certainly and I would see some instability on the machien too , for now I have no case like that . Have a nice day , regards , --- In firebird-support@yahoogroups.com, Fabiano fabianoaspro@... wrote: Try a program named memtest86 It runs at system startup and detects defective memory modules. As you have minor corruption problems it appears to be a memory problem. Maybe a F8 in windows initialization have a memory test program too. De: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Em nome de harveypekkar Enviada em: terça-feira, 31 de julho de 2012 10:33 Para: firebird-support@yahoogroups.com Assunto: [firebird-support] Re: How to list the tables and field wich use the primary key from one table as FK Very nice ! the script list exactly what I need , thank you very much . About the influence about dev team I agree too , but they keep saying that the corruption problem do not come from update or new script , another problem is that our new deployement system work with patch , before we did backup/restore for each major update , that was a little constraint but there was less errors , at least corruption problem were discover before going too big . I hope to find someday the cause of corruption with no doubt about it source , for now i am not experienced enough to do this , I keep working on my repair tool and after that maybe I will find out why . Many thank for your help and Alan too , have a nice day . regards , --- In firebird-support@yahoogroups.com mailto:firebird-support%40yahoogroups.com , Svein Erling Tysvær svein.erling.tysvaer@ wrote: I think you are 100 % right , the problem is that i am not working in the developpement team but in customer support , I suspect some software update to cause the problem but I have no access on that , the only thing I can do is to try to repair the best way for my client and I can't only validate and mend , I have also to put back the records from a backup with new ID or the problem will not be solve completely . We have about 500 servers too, the structure is from 2 up to 20 clients , little workgroup under windows XP and 7 I don't think the problem concern the firebird side , I know it's very strenght. You also ought to try to influence your development team somehow. Normally, it would not be OK for you to have 100 corruptions (assuming an average of 2 per week) when Alan hasn't experienced any corruption at all. Hardware problems and faulty UDFs used to be ways to make Firebird fail. The following may or may not help you: select i.rdb$relation_name, iseg.rdb$field_name from rdb$relation_constraints rec join rdb$ref_constraints rfc on rec.rdb$constraint_name = rfc.rdb$constraint_name join rdb$relation_constraints rec2 on rfc.rdb$const_name_uq = rec2.rdb$constraint_name join rdb$index_segments iseg2 on rec2.rdb$index_name = iseg2.rdb$index_name join rdb$indices i2 on rec2.rdb$index_name = i2.rdb$index_name join rdb$indices i on rec.rdb$index_name = i.rdb$index_name join rdb$index_segments iseg on rec.rdb$index_name = iseg.rdb$index_name where i2.rdb$relation_name = :MyTableName and iseg2.rdb$field_name = :MyPKFieldName It is not tested, I know it does prepare and that it is possible to get some results, but I don't know if they are correct or complete (I'm assuming one field for each primary key). HTH, Set [Non-text portions of this message have been removed]
Re: [firebird-support] matrix report
Em 1/8/2012 08:23, mahdoom_a escreveu: Dear all, I need your help to create this report AQNOJan Feb Mar Apr May Jun Jul..Dec. Total_paid --- --- --- --- --- --- --- ----- 10012000 2000 2000 2000 8000 CHQNO 214 215216 217 it will fill the amount(PAYMNT) in each month (PAYDATE) and its cheque number (CHQNO) table structure is CREATE TABLE RNTSTAT ( STATNO INTEGER NOT NULL, RNTDUE DOUBLE PRECISION, DUEDATEDATE, PAYMNT DOUBLE PRECISION, PAYTYP VARCHAR(30), CHQNO VARCHAR(30), PAYDATEDATE, INVOICEVARCHAR(30), EXPNS DOUBLE PRECISION, XPNSDATE DATE, XPENSTYP VARCHAR(30), MENAINTYP VARCHAR(30), CHRGVALDOUBLE PRECISION, CHRGTYPVARCHAR(30), RNTNO INTEGER, EXNSNO INTEGER, AQNO INTEGER, PANNO INTEGER, CONTNO VARCHAR(30), CHRGPERFLOAT, FROM_DATE DATE, TO_DATEDATE, AWQAFNONEW_DOMAIN ); any hint will be helpful. thanks regards, AHMAD I would usually do this on the client-side, it's called cross-tab report. You could implement it on the server-side using sub-queries, in-line views or CTE's. I would post a sample query that I used on a talk I did last July on Firebird Developers Day to show how to use CTE's with ProdutoVendaAnual(Ano, ProdutoID, TotalVendido) as (select extract(year from DataEntrega) Ano, ProdutoID, Sum(QuantidadeOriginal) from PedidoVendaItem group by 1, 2) select P.Codigo, P.Descricao, V_2006.TotalVendido, V_2007.TotalVendido, V_2008.TotalVendido, V_2009.TotalVendido from Produto P left join ProdutoVendaAnual V_2006 on (V_2006.ProdutoID = P.ProdutoID and V_2006.Ano = 2006) left join ProdutoVendaAnual V_2007 on (V_2007.ProdutoID = P.ProdutoID and V_2007.Ano = 2007) left join ProdutoVendaAnual V_2008 on (V_2008.ProdutoID = P.ProdutoID and V_2008.Ano = 2008) left join ProdutoVendaAnual V_2009 on (V_2009.ProdutoID = P.ProdutoID and V_2009.Ano = 2009) HTH
[firebird-support] Firebird 2.5 performance slower then 2.1 especially SuperClassic...
Hello all, I recently upgraded FB from 2.1.5 to 2.5.1. In order to port data we essentially write the contents of the database to a file, then read it back in again. In short, this amounts to many thousands of INSERT statements. (Yes I know how to use GBak, this process was designed to make metadata mods easy). Anyway, this process has been working nicely for many years, and I expected that the performance of 2.5 would be more or less on par with 2.1, possibly better (SMP, SuperClassic). But it isn't. In fact it ranges from 25% worse to 4x worse (400%). So I thought I'd raise the issue. I'm sure a bunch of nay-sayers will argue that my tests are invalid, blah blah. But rest assured I ran my tests many times over on the same machine to ensure the results are accurate. And the only things varied in the tests were the backend server version / architecture / Forced-Writes. Front-end app and import file always the same. Here are the results of the import runs: FB 2.1.5 SuperServer Force-Writes Off 3 min 40 sec FB 2.1.5 SuperServer Force-Writes On 7 min 15 sec FB 2.5.1 SuperServer Force-Writes Off 4 min 30 sec FB 2.5.1 SuperServer Force-Writes On NOT TESTED FB 2.5.1 SuperClassic Force-Writes Off 17 min 35 sec FB 2.5.1 SuperClassic Force-Writes On 20 minutes 4 seconds So a pure upgrade from 2.1 to 2.5 (same SuperServer architecture) made the process 25% slower (looking at Forced Writes Off only). The real killer above is the new SuperClassic architecture. OK sure this test should have absoluely no benefit from SuperClassic since it is a single threaded app throwing INSERT statements to the server. But over 4x worse performance?!?! That's a serious performance hit! FWIW, the firebird.conf is the same for all test cases (well the same less changes made between 2.1 and 2.5). The critical settings (modified from their default values) are these: CpuAffinityMask = 7 MaxUnflushedWrites = 200 MaxUnflushedWriteTime = 10 Has anyone else experienced performance degredation with FB 2.5? Although I would love to take advantage of some of the new features of 2.5, the poor performance has me going back to 2.1 for now. regards, -randall sell
[firebird-support] Re: Firebird 2.5 performance slower then 2.1 especially SuperClassic...
01.08.2012 19:40, randallsell wrote: Has anyone else experienced performance degredation with FB 2.5? This may explain your experience: http://tracker.firebirdsql.org/browse/CORE-3792 Dmitry
Re: [firebird-support] Recursive query (get all employees under master department)
this is exactly what I needed. Thank you :) If what you're asking is what I think, then the answer is very simple: (same CTE as you already have) SELECT * FROM fs_tree fs JOIN employee e on fs.dept_id = e.dept_id Of course, you could also put this in the CTE itself (then you might have to do it both before and after UNION ALL), but you haven't mentioned any employee recursion (e.g. list all employees whose leaders or their leaders belong to the department, regardless of whether the employee or his closest leader belong to the department), so there isn't much reason to complicate things. HTH, Set [Non-text portions of this message have been removed]
[firebird-support] Re: Firebird 2.5 performance slower then 2.1 especially SuperClassic...
--- In firebird-support@yahoogroups.com, Dmitry Yemanov dimitr@... wrote: 01.08.2012 19:40, randallsell wrote: Has anyone else experienced performance degredation with FB 2.5? This may explain your experience: http://tracker.firebirdsql.org/browse/CORE-3792 Dmitry Possibly. Although I am noticing far worse then a 50% loss. But shall retest when 2.5.2 is publicly available. Got a rough idea when that might be? regards, -randall
Re: [firebird-support] Saving / Retreving Chinese chrs from database
Andy Gable schrieb am 26.07.2012 um 03:21 (+0100): KITCHENDESCRIPTION_ENGVARCHAR(40), KITCHENDESCRIPTION_OTHER VARCHAR(100) CHARACTER SET UTF8, (programming language VB6 and interface is ODBC) I can display when adding a new product the Chinese chars but when I recall it from the database I get My guess: Some buffer or whatnot on the way from the character data column to is not ready to accept wide characters, so the replacement character (frequently just the question mark) is substituted for the real ones. Check your VB6 code. -- Michael Ludwig
[firebird-support] Re: Firebird 2.5 performance slower then 2.1 especially SuperClassic...
02.08.2012 3:14, randallsell wrote: Possibly. Although I am noticing far worse then a 50% loss. But shall retest when 2.5.2 is publicly available. Got a rough idea when that might be? In a couple of weeks. But you may use a snapshot build for testing. Dmitry
