[firebird-support] Connecting with ODBC driver
I am using a Firebird 2.5.8 64 bit server with the ODBC 2.0.5 64 bit driver. I do get the connection from MS Excel and I get a list of tables, but I don't see the fields in the tables and thus not the data. Using Qlik Sense Desktop I have the same problem, however there I am able to open the system tables and see their contents. I tried also with 32 bit driver (and client) with the same result. I know it was working with an earlier version, but I don't know the exact versions of ODBC and Server. Any help will be appreciated Regards, Jeroen
Re: [firebird-support] Show Columns
That...is an unbelievably helpful page and that query should be made part of the official docs! Daniel On 3/23/2018 9:18 AM, edmende...@gmail.com [firebird-support] wrote: Daniel, Not sure if this answers your question but I’ve had situations where I needed a way to get column details and table ddl within our application. We ended up creating a stored procedure that would return that information. The following linked helped me with understanding what needed to be done in order to create that stored procedure. https://www.alberton.info/firebird_sql_meta_info.html HTH, Edward *From:* firebird-support@yahoogroups.com bird-supp...@yahoogroups.com> *Sent:* Thursday, March 22, 2018 7:53 PM *To:* firebird-support@yahoogroups.com *Subject:* [firebird-support] Show Columns Using some system table queries, is there a view or stored procedure that can, in "full", replicate the output of "show columns from table" available from other servers? Ideally this would be a "drop in" alternative to the "show columns" sql statement that returns the same data in the same structure. There are a few examples I can draw from, including firebirdwebadmin, but I don't want to reinvent it if I don't have to. -- Daniel
Re: [firebird-support] select *at least* N rows
Yes, but remember about good join condition then and if needed sort condition especially you can use system table e.g. RDB$RELATION_FIELDS (it contain many records also in empty database) and JOIN on RDB$RELATION_NAME=’RDB$DATABASE’ AND RDB$FIELD_ID=0 instead of “G.OUT_NO=1” regards, Karol Bieniaszewski From: shg_siste...@yahoo.com.ar [firebird-support] Sent: Friday, March 23, 2018 9:03 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] select *at least* N rows That's a very interesting solution as well Karol! Thanks! I've just realized that I can "left join" my table against *any* table which I know it already has enough records for my purpose --- Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast. https://www.avast.com/antivirus
Re: [firebird-support] select *at least* N rows
That's a very interesting solution as well Karol! Thanks! I've just realized that I can "left join" my table against *any* table which I know it already has enough records for my purpose
Re: [firebird-support] select *at least* N rows
Hi, i know that you got the answer but, maybe this trick is interesting for you because it is simplest for use in any query but require creation of one simple procedure. I use it always in situation like you with fixed numbers of rows with nulls - CREATE PROCEDURE GEN_ROWS(IN_COUNT INTEGER) RETURNS (OUT_NO INTEGER) AS DECLARE VARIABLE VAR_I INTEGER; BEGIN VAR_I=1; WHILE (VAR_I<=IN_COUNT) DO BEGIN OUT_NO=VAR_I; VAR_I=VAR_I + 1; SUSPEND; END END - and now you can use it in select simply - SELECT W.* FROM GEN_ROWS(10) G LEFT JOIN YOUR_TABLE W ON G.OUT_NO=1 ORDER BY G.OUT_NO, your_other_fields ROWS 10 - Best Regards, Karol Bieniaszewski From: shg_siste...@yahoo.com.ar [firebird-support] Sent: Friday, March 23, 2018 7:29 PM To: firebird-support@yahoogroups.com Subject: RE: [firebird-support] select *at least* N rows Thank you very much Mark and András!!! I've tried András solution... I replaced (mechanically, without undestanding too much what was going on...) and it did the trick perfectly! Now is time to study a bit more and try to understand András answer :) Thanks a million again!!! --- Ta wiadomość została sprawdzona na obecność wirusów przez oprogramowanie antywirusowe Avast. https://www.avast.com/antivirus
RE: [firebird-support] select *at least* N rows
Thank you very much Mark and András!!! I've tried András solution... I replaced (mechanically, without undestanding too much what was going on...) and it did the trick perfectly! Now is time to study a bit more and try to understand András answer :) Thanks a million again!!!
RE: [firebird-support] select *at least* N rows
Hi Sergio and Mark, a bit more general solution (for example max. 100 empty rows without 100 union all): with recursive last_empty_row as ( select 100 rownum -- set expected row number here from rdb$database), empty_rows as ( select 1 rownum from rdb$database union all select tr.rownum + 1 rownum from empty_rows tr where tr.rownum < 100) -- set expected row number here select first 100 rownum, field1, field2 -- set expected row number here, replace your field names here from ( -- your real select is coming here… select first 100 0 rownum, 'A' field1, 'B' field2 -- set expected row number here, replace your field names here from rdb$database-- replace your table name here union all select t.rownum, null field1, null field2-- replace your field names here from empty_rows t cross join last_empty_row l where t.rownum <= l.rownum order by 1) András From: firebird-support@yahoogroups.com [mailto:firebird-supp...@yahoogroups..com] Sent: Friday, March 23, 2018 6:51 PM To: firebird-support@yahoogroups.com Subject: Re: [firebird-support] select *at least* N rows On 23-3-2018 18:26, shg_siste...@yahoo.com.ar [firebird-support] wrote: > Hello! is there any trick to select a fixed number of rows? I mean, no > matter if I have 2 rows which match the select, I need to always return > 10 rows. Of course the last 8 would be all null in this example... > > I hope I'm clear with my question! Not sure if I'm in the "right path", > but if I can do that I can fix very easily a stored procedure I'm > working on now. There is nothing directly in Firebird to do that, you could try something like this (Firebird 3, for earlier versions use ROWS 10 instead of "fetch first 10 rows only"): select ID, NAME from ( -- Need to nest to avoid limitation in the Firebird SQL grammar select ID, NAME from ( select ID, NAME from ITEMS order by id fetch first 10 rows only ) -- as many null columns is in the above query -- repeat the union all as many times as you need guaranteed rows union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database ) order by id nulls last fetch first 10 rows only Technically the order by is not necessary, but leaving it out makes you rely on an implementation detail. If you do add it, the "nulls last" is required. Mark -- Mark Rotteveel __ Information from ESET Mail Security, version of virus signature database 17106 (20180323) __ The message was checked by ESET Mail Security. http://www.eset.com [Non-text portions of this message have been removed]
Re: [firebird-support] select *at least* N rows
On 23-3-2018 18:26, shg_siste...@yahoo.com.ar [firebird-support] wrote: > Hello! is there any trick to select a fixed number of rows? I mean, no > matter if I have 2 rows which match the select, I need to always return > 10 rows. Of course the last 8 would be all null in this example... > > I hope I'm clear with my question! Not sure if I'm in the "right path", > but if I can do that I can fix very easily a stored procedure I'm > working on now. There is nothing directly in Firebird to do that, you could try something like this (Firebird 3, for earlier versions use ROWS 10 instead of "fetch first 10 rows only"): select ID, NAME from ( -- Need to nest to avoid limitation in the Firebird SQL grammar select ID, NAME from ( select ID, NAME from ITEMS order by id fetch first 10 rows only ) -- as many null columns is in the above query -- repeat the union all as many times as you need guaranteed rows union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database union all select null, null from rdb$database ) order by id nulls last fetch first 10 rows only Technically the order by is not necessary, but leaving it out makes you rely on an implementation detail. If you do add it, the "nulls last" is required. Mark -- Mark Rotteveel
[firebird-support] select *at least* N rows
Hello! is there any trick to select a fixed number of rows? I mean, no matter if I have 2 rows which match the select, I need to always return 10 rows. Of course the last 8 would be all null in this example... I hope I'm clear with my question! Not sure if I'm in the "right path", but if I can do that I can fix very easily a stored procedure I'm working on now. Thanks!! Sergio
RE: [firebird-support] Show Columns
Daniel, Not sure if this answers your question but I've had situations where I needed a way to get column details and table ddl within our application. We ended up creating a stored procedure that would return that information. The following linked helped me with understanding what needed to be done in order to create that stored procedure. https://www.alberton.info/firebird_sql_meta_info.html HTH, Edward From: firebird-support@yahoogroups.com Sent: Thursday, March 22, 2018 7:53 PM To: firebird-support@yahoogroups.com Subject: [firebird-support] Show Columns Using some system table queries, is there a view or stored procedure that can, in "full", replicate the output of "show columns from table" available from other servers? Ideally this would be a "drop in" alternative to the "show columns" sql statement that returns the same data in the same structure. There are a few examples I can draw from, including firebirdwebadmin, but I don't want to reinvent it if I don't have to. -- Daniel
[firebird-support] Fwd: SourceForge Project of the Month Vote
Přeposlaná zpráva Předmět: SourceForge Project of the Month Vote Datum: Fri, 23 Mar 2018 13:30:24 +0800 Od: Joan Uy Ang Hi, If you’re receiving this email it is because one of your projects is up for Project of the Month for May 2018. The blog post is here: https://sourceforge.net/blog/community-choice-project-month-vote-may-2018/ and you should encourage people to vote on your project. Here are the projects up for Project of the Month: *newfmd**fast-mouse-clicker-pro**infozip**reactos**clamwin**ditto-cp* *systemrescuecd**pmd**firebird* If you win, you will be notified for an exclusive SourceForge interview, and your project will be listed at the top of the SourceForge homepage. Thanks, Joan Nadene SourceForge Community Coordinator
