Re: [firebird-support] COALESCE() + CAST() converts timestamp format
Hi Bhavbhuti, I do not know the exact solution, but I'd say: COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt converts timestamp inside servers environment to a string - the returning client receives a string and does not know about it's datatype - yet does not a apply specific translations. With CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt it's just the other way round: While the server deals with string and timestamp, the client receives a timestamp an does know what to with it. I'd say, this behavior is expectable... Best regards, Thomas Am 10.05.2014 15:21, schrieb venussof...@gmail.com [firebird-support]: Hi all I have the following in my SELECT statement COALESCE(CAST(tPB.dBillDt AS TIMESTAMP), '') AS tDocDt The COALESCE() converts the CAST()ed TIMESTAMP to ANSI standard 2014-05-03 00:00:00. CAST(COALESCE(tPB.dBillDt, '') AS TIMESTAMP) AS tDocDt The CAST() converts the COALESCE()ed TIMESTAMP to my regional and expected format 03.05.2014, 00:00:00. Is this an expected behavior? Please advise. Thanks and regards Bhavbhuti -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Query optimization mystery
Hi Kevin, let me first answer one of your particular questions: I don't really understand statistics but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. with the first index, you've about 3% of records with the same value in index (these are going to be searched sequentially), with the second, only 0.04%. Depending on the number of records (cardinality), this can make a really big difference. Next: AFAIK, Firebird changes the order of executing join-members (or left join members) by selectivity and cardinality. Because procedures result values can not be narrowed by index by the engine at execution time, they usually are evaluated at the end of the chain. If you know, that the stored procedure returns view records, connect it first and the rest by left join (left and straight join don't get shifted between each other by optimizer). If stored procedures result can be narrowed down by parameters on select time, try this: the result of SP never concernces about indicies. Thomas Am 13.05.2014 21:29, schrieb Kevin Donn kd...@msedd.com [firebird-support]: I've got a query optimization mystery I need some help with. The short version is I've got two tables that are very similar, but when I join each of them to a third table, I get different plans - one runs fast and the other runs slow. Here are the queries: select sp.STUDENTSEQ, a.User_ID from schlhist sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 select sp.STUDENTSEQ, a.User_ID from supprog sp join Advocate a on sp.ADVOCATE_CODE=a.Advocate_Code where a.USER_ID=37 The first runs with plan PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_SCHLHIST_ADVOCATE_CODE)) which is fast. The second runs with plan PLAN JOIN (SP NATURAL, A INDEX (ADVOCATE_)) which is slow. If I change the plan on the second to PLAN JOIN (A INDEX (IDX_ADVOCATE1), SP INDEX (USV_supprog_ADVOCATE_CODE)) it also runs fast. I don't really understand statistics but USV_SUPPROG_ADVOCATE_CODE has 0.029412 and USV_SCHLHIST_ADVOCATE_CODE has 0.000422. That seems like a potentially important difference, but I'm not sure what it means or what to do about it. Here's the ddl (I've chopped out a lot fields I didn't think were relevant): CREATE TABLE ADVOCATE( ADVOCATE_CODE varchar(15), ADVOCATE varchar(20) COLLATE EN_US, TEACHINGCERT varchar(1) COLLATE EN_US, GENDATE timestamp, MODDATE timestamp, CHANGESTATUSFLAG smallint, REGIONCODE smallint, RETIREDCODE varchar(1), USER_ID integer ); CREATE UNIQUE INDEX ADVOCATE_ ON ADVOCATE (ADVOCATE_CODE); CREATE INDEX ADVOCATE_ADVOCATE ON ADVOCATE (ADVOCATE); CREATE INDEX ADVOCATE_REGIONCODE ON ADVOCATE (REGIONCODE); CREATE INDEX IDX_ADVOCATE1 ON ADVOCATE (USER_ID); CREATE TABLE SCHLHIST( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, FACILITYID varchar(6) COLLATE EN_US, LQMDATE date, RESDATE date, FUNDINGDATE date, ENROLLDATE date, WITHDRAWDATE date, GENDATE timestamp, ADVOCATE_CODE varchar(15), COMMENT blob sub_type 1 ); CREATE UNIQUE INDEX SCHLHIST_ ON SCHLHIST (STUDENTSEQ,DOMID,DBID,SHSEQ); CREATE INDEX SCHLHIST_FACILITYIDINDEX ON SCHLHIST (FACILITYID); CREATE INDEX SCHLHIST_MOSTRECENTINDEX ON SCHLHIST (STUDENTSEQ,RESDATE,FUNDINGDATE,GENDATE); CREATE INDEX SCHLHIST_STUDENTSEQINDEX ON SCHLHIST (STUDENTSEQ); CREATE INDEX SCHLHIST_STUFACINDEX ON SCHLHIST (STUDENTSEQ,FACILITYID); CREATE INDEX USV_SCHLHIST_ADVOCATE_CODE ON SCHLHIST (ADVOCATE_CODE); CREATE TABLE SUPPROG( STUDENTSEQ integer, DOMID varchar(2) COLLATE EN_US, DBID smallint, SHSEQ integer, SPKEY varchar(15) COLLATE EN_US, SPCODE varchar(3) COLLATE EN_US, ADVOCATE_CODE varchar(15), OWNER_USER_ID integer ); CREATE UNIQUE INDEX SUPPROG_ ON SUPPROG (STUDENTSEQ,DOMID,DBID,SHSEQ,SPKEY); CREATE INDEX USV_SUPPROG_ADVOCATE_CODE ON SUPPROG (ADVOCATE_CODE); CREATE INDEX USV_SUPPROG_OWNER_USER_ID ON SUPPROG (OWNER_USER_ID); -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Query optimization mystery
Oops: If you know, that the stored procedure returns view records, connect ... I meant: few records -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] database became broken for any reasons
Hi! 1. Sometimes without any reasons if I send database to shutdown mode, it's not happened without killing all pro cesses of fb_inet_server.exe(I hope it's ok, because no one use database in this moment) If the server can't set database in shutdown mode, it's still active in working on the database. If you kill the server process in this case, you can expect unwanted results. You might try a tool like Sinatica or use monitoring tables, figure out what's happening or to close all connections that are active at your maintenace time. Good look, Thomas
Re: [firebird-support] Re: database became broken for any reasons
Well, Dimitri, I don't know whether it's ok for the people at the other end of the killed connection, because their operation is cancled and rolled back, but at least, you don't get database corruptions, if you close all connections and shut down database regularly... Best regards, Thomas Am 15.05.2014 10:07, schrieb markovdmi...@yahoo.com [firebird-support]: So if I kill all connections(clear table MON$ATTACHMENT) and then I'll try to kill processes all will be ok(or myabe I don't need it)? I'll try to do such example: 1) I connect from remote computer to test server using SYSDBA, not my user 2) I start big select (it's take about 2-3 minutes, really big table) 3) On test server send database to shutdown - It's work, database in shutdown mode, and then return online What's wrong? I't seems to me that this connections kept by sleeping applications in terminal. I never see this bug on server without terminal(I may mistake, but it seems to me I right)
Re: [firebird-support] Re: Performance of Stored Procedures versus execute blocks
Hi Lafras, I'm sorry to not dig in that deep again, but your assumptions are starting to get a bit complex. Few thoughts: - Not only sorting, but narrowing procedure result sets (where, join, on) is not performed by indexed search. - The main software architectural argument to write a stored procedure is the idea to create reusable code, where maintenance tasks focuses on encapsulated body of a well defined interface of a procedure with a well defined behavior. If a stored procedure gets created for ervery atomic task or special behavior, there might be a good reason, but none of the usual ideas of software development. Thus, before any further guessing, you shold point out, what's your idea behind the question is. Cheers, Thomas Am 19.05.2014 20:20, schrieb Lafras Henning laf...@xietel.com [firebird-support]: Thanks Thomas, You make a good point about compile time SP optimisation becoming stale as the data changes, but this can simply become a maintenance task, along with monitoring of index efficiency, doing backups, and sweeping. This should be a proviso to the assumptions. But I stepped backThe optimizer can not combine the queries inside the procedure with the stuff, you join to it. Writing queries with joins out side the SP would then be the wrong approach as the combined query (SP and Join) should have been a new optimised SP, even if was inconvenient to recreate (considering the existing SP probibly did 99% of the job and all you may want is a bit more which kills the performance - very frustrating). You still make a good point though as I presume the same would apply executing sub procedures from other procedures, so to my assumption 3 is definitely not true, as it depends if the result set needs to sorted. I think rereading the assumptions I am trying to express the following assumption. Assumption 5) It is better (from a performance point of few - providing the logarithms are the same and the same plans will be executed) to have a lot of complex queries on a lot of tables running using a lot of stored procedures (for slight variations in the algorithm) finally returning a single result set than to have a high level language querying the same data with multiple queries and result sets and manipulating the same data outside of the DB engine and producing the same result set. Here I am not just talking about latency between application and server as the same could apply to embedded server that does not go via the network,I rather refer to the time it takes on both side to prepare and package the on the wire protocol and the time to compile the query on the fly. (sorry if I rant and rave a bit, but I do not mean to appear argumentative) Thanks Lafras Version 0.2 Assumptions: a) Presuming recompiling of the Stored procedures are done as the data profile changes: 1) There is no performance penalty for having many thousands of stored procedures (other than the storage space, and possibly manageability with GUI tools like flamerobin that reads the DDL). 2) There is performance gains in making use of stored procedures over execute blocks, especially when the statements are long and complex. 3a) There is no significant performance penalty in breaking up a large stored procedures into many sub stored procedures and invoking them from the original stored procedure provided their result set is used in the natural return order. 4) SQL statements run as byte code in a “virtual machine” and are just as as speed efficient(if not more so) at data and sting manipulation (Casting, concatenation, substing and replace) than other byte coded languages such as java, python etc. 5a) Conclusion: It is better (from a performance point of few - providing the logarithms are the same and the same plans will be executed) to have a lot of complex queries on a lot of tables running using a lot of stored procedures (for slight variations in the algorithm), finally returning a single result set; than to have a high level language querying the same data with multiple queries and result sets going to and from the database and manipulating the same data outside of the DB engine and producing the same result set. end ++ 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 -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET
Re: Odp: [firebird-support] Re: database became broken for any reasons
You might create an on connect trigger that trows an exception on certain condition, for example the value of a generator (bcause this gets visible to every transaction immediately) and for other users than yourself. That will prevent applcication from reconnecting successfully. Am 20.05.2014 22:18, schrieb Markov Dmitri markovdmi...@yahoo.com [firebird-support]: It's not resolve problem, because any my application can reconnect to database. 20.05.2014 23:45, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] пишет: Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Re: Odp: [firebird-support] Re: database became broken for any reasons
You might create an on connect trigger that trows an exception on certain condition, for example the value of a generator (bcause this gets visible to every transaction immediately) and for other users than yourself. That will prevent applcication from reconnecting successfully. Am 20.05.2014 22:18, schrieb Markov Dmitri markovdmi...@yahoo.com [firebird-support]: It's not resolve problem, because any my application can reconnect to database. 20.05.2014 23:45, 'liviusliv...@poczta.onet.pl' liviusliv...@poczta.onet.pl [firebird-support] пишет: Hi, Delete from mon$attachments excluding current connection After that you are only logged user Regards, Karol Bieniaszewski - Reply message - Od: Markov Dmitri markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Re: database became broken for any reasons Data: wt., maj 20, 2014 20:22 I turn off automatic sweep - only manual sweep. For today I try to change keepalive parameters - it's look like all ok. All processes named fb_inet_server.exe closed, but it's work if connection was lost. It's not work when the connection established and not closed for any reasons(for example the application waiting for some system process on client side and so don't close connection and don't react on my commands from server side). So how I can from server side close connection normally, so after that I may shutdown database and it's not create orphan records or pages? I use shutdown for absolute prohibition of connection to database using user accounts for database update process(changing metadata or changing only data). 20.05.2014 18:47, Ann Harrison aharri...@ibphoenix.com [firebird-support] пишет: On Mon, May 19, 2014 at 1:31 PM, markovdmi...@yahoo.com mailto:markovdmi...@yahoo.com [firebird-support] firebird-support@yahoogroups.com mailto:firebird-support@yahoogroups.com wrote: For preventing orphan pages or backversions I turn off autosweep. So for the last month I don't see any orphan pages or orphan backversions. Probably you were shutting the database down hard while a sweep was active. I know that shutdown during sweep can broke database, so I turn off autosweep and do only manual sweep. I've got to take exception to your statement. Shutdown during sweep doesn't break the database. It leaves some space (record or page) inaccessible, but, assuming you're running with forced writes turned on, the database is not corrupt. All the data is accessible and correct. My service of database looks like this: 1) try normally close all connections(all my applications get commands for starting of autoclose procedure) OK 2) kill all terminal instance of my applications OK, though that will leave errors in the log - but you caused them, so not a big deal. 3) shutdown database(my software use non root access, so it's guarantee single user access) OK. 4) killing all processes fb_inet_server.exe Not OK. Once the connections are gone, the fb_inet_servers will eventually stop, after they've written all their changes to disk. If you kill them, there's a chance that, for example, an old record version will have been removed and the page it was on will have been written, but the older versions that are chained to that record will not have been removed and their pages flushed to disk. Firebird uses a technique called careful write to maintain on-disk consistency without a separate log. Essentially that means that when new things are created, the thing is created first, then pointer to it follow. Conversely, when a thing is removed, all the pointers are removed first, then the thing itself. When done correctly and consistently, careful writes never leave broken pointers. However, it can lead to lost space. For example, when a table needs a new data page, Firebird looks for a free page - they're indicated on pages called PageInformationPages or PIPs. When it finds a free page on a PIP, Firebird marks the page as in use, before it starts the process of allocating that page to the table, If the operation is interrupted, the PIP will say that the page is in use, but it's not part of a table or any other part of the database - hence, orphaned. Orphans are a normal and benign result of a hard database shutdown with write pending. Good luck, Ann -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann
Re: [firebird-support] loading data from a .csv file?
To import csv, I usually use B_LOADFROMFILE from rfunc UDF library (http://rfunc.sourceforge.net/) with some recursive query to split lines and fields. If you need hints on this, I'll provide an example. If you can modify the format of the data to import, you might consider external tables. Thomas Am 22.05.2014 22:09, schrieb Lester Caine les...@lsces.co.uk [firebird-support]: I process mine through php ;) Although I have dropped mysql data as simple insert data. It would perhaps be nice as an option in flamerobin Sent from my android device so the quoting is crap! -Original Message- From: Alan Shank li...@notoriousshankbrothers.com [firebird-support] firebird-support@yahoogroups.com To: firebird-support@yahoogroups.com Sent: Thu, 22 May 2014 20:46 Subject: [firebird-support] loading data from a .csv file? Is there some way to load data into a table in Firebird, like LOAD DATA INFILE... in Mysql? I have comma-delimited files from Mysql that I want to load into Firebird tables. I have Firebird2.5-super on Ubuntu 14.04, 64-bit. Thanks, Alan Shank Woodland, CA Posted by: Alan Shank li...@notoriousshankbrothers.com mailto:li...@notoriousshankbrothers.com ++ 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 firebird-support-fullfeatu...@yahoogroups.com mailto:firebird-support-fullfeatu...@yahoogroups.com -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Firebird export/import tool
Hard to say, 'cause you're not pointing out what kind of problem you encounter... Cheers, Thomas Am 03.06.2014 13:30, schrieb milossi...@yahoo.com [firebird-support]: Hello, We are using a firebird database for our application programmed in .NET. We are using this tool for exporting database to sql (http://fbexport.sourceforge.net/) The problem is, that it always has problems with importing the data back, when there are decimal number used (double precision). Is there some another solution how to export and import data, that is more stable ? Thanks. -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Materialized views
Hi, if you can commit yourself in maintaining one or the other rule in writing the body of the viesws, it should not bee too complicate to find out what tables and fields you are refering and to query system tables for data types of you materialized views... Thomas Am 07.06.2014 14:36, schrieb kokok_ko...@yahoo.es [firebird-support]: Is there a plan to add materialized views in version 3.0 If not, which would be the workaround to do it in Firebird 2.5 without using triggers? Basically I would need to update the materialized views once each 2-3 days, so probably using a stored procedure would be enough. I thought using a INSERT SELECT way, but I am not clear how to create the table scheme from the view using DDL. I update the view scheme sometimes, so I would prefer to recreate the new table scheme each time that I would need to create the materialized view. Thank you -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Conversion error from string #Timestamp#
Hi Augusto, if the insert-string contains timestamp information in the form you specified, no error should occur. Are you sure, that the data does not contain the string 'Timestamp' (as the error message indicates)? Are you able to log your datastream during pump to the cloud? Are you able to query data from the datasource in a reviewable form? Can you modify queried data, for example on this timestamp field by having a left(FLD, 10) to just return the date part? Hope it helps, Thomas Am 11.06.2014 15:26, schrieb kabutoprogram...@yahoo.com [firebird-support]: Hello Good Morning, I’m with one error that I cannot found any help on the internet, and I Hope I could get any help... I’m migrating a database to the cloud, but, I’m getting the “conversion error from string #Timestamp#” problem when I’m making the insert on the cloud database. The date is like in the format '-MM-DD 00:00:00’, the database was created with Dialect 1. I changed it for 3 but the error keep happening. Could you help me? Thanks a lot Regards Augusto Souza -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] How to retrieve list of users granted with the admin role
Hi Robert, roles are database specific. Check RDB$ROLES and RDB$USER_PRIVILEGES, you'll probably find out what you need - if not, come back and ask, I'll dig in more details. Thomas Am 18.06.2014 15:23, schrieb r...@xs4all.nl [firebird-support]: As SYSDBA I like to know which users are granted in the past with the ADMIN ROLE (as in CREATE USER Test1 PASSWORD 'test' GRANT ADMIN ROLE). I guess those users have the system defined RDB$ADMIN role assigned in the security database, but I do not have direct access to the security databae to check this. Also the system table SEC$USERS does not give me a clue. Any other suggestions how to retrieve those users with admin role? Thanks! Robert -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Re: Execute SQL scrip (few sql statements) in one step in SP
The query I've been sending you could be inside a stored procedure: create porcedure P_EXEC_SCRPT(CMDS varchar(16)) as declare variable CMD varchar(16)) begin for with recursive... select CMD from ... into :CMD do execute statement :CMD; end If you are not executing the execute statement with autonomous transaction, they are rolled back all together, if one of them fails. Now, your stored procedure may collect all statements into a variable and than call execute procedure P_EXEC_SCRPT(:YOURVARIABLE); You might add some error detection if needed; I would have the procedure return the number of executed commands and an exception handling (when any) to tell me, if something failed - but that's fine tuning. Thomas Am 21.06.2014 09:56, schrieb majst...@yahoo.com [firebird-support]: Hello Thomas, My SP has and select part which will collect all SQL statement that need to execute, but I ca't use UNION ALL because it is not i same table in database, but what I need to be is to do it in one transaction, I can execute every statemement one by one but if third is failed I need to roll back all changes in all tables that was made by SP. Thanks for answer... -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Invalid argument in EXECUTE STATEMENT - cannot convert to string
userClause is probably null. Then the entire string will be null. Am 25.06.2014 16:08, schrieb Tim Ward t...@telensa.com [firebird-support]: Anyone know what this error message actually means? Yes I am trying to run an EXECUTE STATEMENT, but it's not clear to me what an argument means in this context, or what it is that something is trying to convert to a string, or why it's trying to do so, or why it can't be done. execute statement 'select first 1 MODULEID, ELEMENTID from TBLMODULE' || :tableType || ' ' || 'where ELEMENTID = ' ||:ELEMENTID || :userClause || ' order by 1 ASC' into :nModuleId, :nElementId; tableType is a varchar elementid is a bigint userclause is a varchar nmoduleid is a bigint nelementid is a bigint The only thing I can think of, after struggling for some time to understand the message, is that the thing between statement and into is the argument, and the only reason I can think of that it might not be a string - despite obviously looking like one!! - is that elementId is null (neither of the other variables can ever be null). Does that make sense? -- Tim Ward -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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] remove constraint
Hi Wolfgang, I think, you should alter table and alter the field definition to not required the check any more. Regards, Thomas Am 07.07.2014 08:22, schrieb Wolfgang Kluge wkl...@kb-labuan.com [firebird-support]: Good afternoon. Can anypme give me a hint how I can by code remove a cpnstraiant in a populated database? The constraint type is check and the constraint name is integ_485 regards Wolfgang Kluge -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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] no-wait vs wait transactions?
Not to make you jealous or anything, but we just came back into cell phone range o n our leisurely cruise down the Maine coast. I'd send a picture, but the list won't let me. Now it's 6:30. The drinks flag is out; the dog has had her run through the woods and a bit of swimming. Life is good. I really enjoy your footer - disregarding that I always enjoy your comments to the list anyways - see you in Prague, hopefully! Just came back from acrobatic class and had a lot of fun with eager scholars and after-training-beer... (while german soccer team obviously won current match - at least according to the noise on the streets). -- Thomas
Re: [firebird-support] gds32.dll failed to load ODBC32+FB64+App32
Hi Bhavbhuti, ASAIK, 32bit ODBC-driver can connect to 32bit Firebird only. Regards, Thomas Am 21.07.2014 08:21, schrieb venussof...@gmail.com [firebird-support]: Hi all I found that on my 64 bit i5, 64 bit Win7 that there were both 32 and 64 bit installations of FireBird. So I uninstalled the 32 bit. Now I cannot load the database via ODBC, it raises the error: Connectivity error: [ODBC Firebird Driver]Unable to connect to data source: library 'gds32.dll' failed to load The situation is as follows: 64 bit i5 processor 64 bit Win7 OS 32 bit Visual FoxPro app Thus I have to use 32 bit FireBird ODBC driver 64 bit FireBird server Am I limited to using 32 bit FireBird on this machine? Or 32 bit ODBC can talk with 64 bit FireBird? Please advise Thanks and regards Bhavbhuti -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Re: CrossTab Select
What do you need help for? Am 22.07.2014 15:14, schrieb sremula...@bol.com.br [firebird-support]: help -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Skip records
Oops, forgot the ordering and of course, it can be optimized like this: with recursive CTE_NR as (select 1 as NR, t.ID, (select first 1 skip 5 t1.ID from TBL t1 where t1.ID t.ID order by t1.ID) as NXT from (select first 1 t.ID from TBL t order by t.ID) t union all select NR + 1, t.ID, (select first 1 skip 5 t1.ID from TBL t1 where t1.ID t.ID order by t1.ID) as NXT from CTE_NR t0 join TBL t on t.ID = t0.NXT where NR 1024) select NR, ID from CTE_NR n0 returning 1024 records and still skipping 5... Thomas Am 29.07.2014 10:32, schrieb Thomas Beckmann thomas.beckm...@assfinet.de [firebird-support]: Hi Svein, hi Sean, my solution would look similar to what you pointed out, Sean, and I agree, execute block usually provides better readability and recursion depth is a problem: with recursive CTE_NR as (select 1 as NR, t.ID, (select first 1 t1.ID from TBL t1 where t1.ID t.ID) as NXT from (select first 1 t.ID from TBL t order by t.ID) t union all select NR + 1, t.ID, (select first 1 t1.ID from TBL t1 where t1.ID t.ID) as NXT from CTE_NR t0 join TBL t on t.ID = t0.NXT where NR 1024) select NR, ID from CTE_NR where mod(NR, 5) = 0 Thomas Am 29.07.2014 09:47, schrieb Svein Erling Tysvær svein.erling.tysv...@kreftregisteret.no [firebird-support]: Thomas, I usually solve this kind of problem with recusive CTEs providing a counter for the records returned, thus a simple select statement can nicely filter on this kind of property without row numbers functionality. Can you provide an example, please. I, for one, can't imagine how a CTE could be used for such a purpose. My imagination may be slightly more vivid than yours, Sean, I even think I answered something on this list with a similar recursive CTE a few years ago. WITH RECURSIVE TMP(Level, PK, fields) as (SELECT 1, min(PK), whatever FROM table WHERE whatever UNION ALL SELECT t.Level + 1, t1.PK, fields FROM TMP t JOIN table t1 on t.PK t1.PK LEFT JOIN table t2 on t2.PK TMP.PK and t2.PK t1.PK WHERE whatever and t2.PK is null) SELECT * FROM TMP WHERE MOD(Level, 5) = 0 This has the severe drawback that it doesn't work if there's more than 1024 records (I think, at least there's a very limited max depth of recursion), I consider it less intuitive than EXECUTE BLOCK, and expect it to be slower. But I agree that it would be interesting to see Thomas' solution. Don't know whether windowing functions can be used in the WHERE clause, but when Firebird 3 is released, it would be tempting to try things like WHERE MOD(ROW_NUMBER() OVER (ORDER BY something), 5) = 2 Set -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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] Triggers
Hi Hugo, in use means, the database is attached? You can change triggers at any time, but as far as I konw, the change gets visible only after transaction is committed. Thomas Am 13.08.2014 19:53, schrieb Hugo Eyng hugoe...@msn.com [firebird-support]: Hello. Is it possible to change/delete a trigger while it´s in use? -- Atenciosamente, Hugo Eyng -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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] where condition
and coalesce(d.bsnr, -1) = coalesce((select ag from p_getmaxag(a.teilenr,:typ,1)),d.bsnr,-1) seem to be a possible, but probably not very performant approach. Doing a left join with the procedure in case the stuff before your leading and holds true could be a solution... Thomas Am 14.08.2014 15:49, schrieb 'checkmail' check_m...@satron.de [firebird-support]: This works: and (d.bsnr = coalesce((select ag from p_getmaxag(a.teilenr,:typ,1)),d.bsnr)) but how can I get the records without a bsnr, without an workstep, no record in the table d.bsnr..) Thanks Hello, 1st, the following condition does not work unfortunately. (call a stored procedure in the where-condition in a coalesce..) and d.bsnr = coalesce(select ag from p_getmaxag(:teilenr,:typ,1),d.bsnr) and d.bsnr coalesce (select ag from p_getmaxag(:teilenr,:typ,2),100) I have some article, several with worksteps, other without this. For example ArticleA =lamp ArticleB Workstep2= bicycle mill-cut ArticleB Workstep2= bicycle varnish Now I have a stored procedure where I can get the entire stock, input parameter the type (material (no workstep), unfinish products (workstep the last workstep) = typ2 and finished products (last workstep = typ 3 input parameter) If the input typ = 1, I should get all material, typ = 2 all unfinished products… And this I will write in a sql where condition, preferably without an execute statement. IIF and Case do not work in the condition, neither the procedure call. (p_getmaxag) How can I realize this simply? Thanks in advance Best regards Olaf -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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] Insert existing record into same table several times
Hi Alan! The reason is very simple: The first select in your procedure is a singleton select, it can deal with one record only, because it's no for select Thus you get an error, if there are more than one record that match the condition (quantity1). Do for select Id, description, quantity from Test_table where quantity1 into :Id1, :description1, :quantity1 do while (quantity11) do begin insert into Test_table(Id, description, quantity) values(:Id1, :description1, 1); quantity1=quantity1-1; end Cheers, Thomas create or alter procedure test_table_update as declare id1 integer; declare description1 char(10); declare quantity1 integer; begin select Id, description, quantity from Test_table where quantity1 into :Id1, :description1, :quantity1; while (:quantity11) do begin insert into Test_table(Id, description, quantity) values(:Id1, :description1, 1); quantity1=quantity1-1; end update Test_table set quantity=1 where quantity1; end This works if there is one item with a quantity of more than 1 otherwise I get multiple rows in singleton select error Id Description quantity sequence_no 1 Beermat 3 1 2 Beer Mug 3 2 3 Whisky Glass 1 3 Any advice gratefully received. Alan -- Alan J Davies Aldis -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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: CrossTab Select
Hard to help, when there's no question... Looking back the thread, you got some answers but did not relate on them in your following questions. Does select sum(iif(extract(weekday from t.DATAB) = 0, PRODUTO, 0)) as W0, sum(iif(extract(weekday from t.DATAB) = 1, PRODUTO, 0)) as W1, sum(iif(extract(weekday from t.DATAB) = 2, PRODUTO, 0)) as W2, sum(iif(extract(weekday from t.DATAB) = 3, PRODUTO, 0)) as W3, sum(iif(extract(weekday from t.DATAB) = 4, PRODUTO, 0)) as W4, sum(iif(extract(weekday from t.DATAB) = 5, PRODUTO, 0)) as W5, sum(iif(extract(weekday from t.DATAB) = 6, PRODUTO, 0)) as W6 from TABELAB t where t.DATAB between date '23.06.2014' and date '28.06.2014' what you expect (if you add HORA, you'll need to group by the other columns)? Thomas Am 19.08.2014 21:36, schrieb sremula...@bol.com.br [firebird-support]: Help my frields! -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Export data to CSV file from stored procedure?
There are UDFs around to write files (example rfunc: http://rfunc.sourceforge.net/) and you can use freeadhocudf (http://freeadhocudf.org/dokumentation_deutsch/dok_deu_blob.html) to transform your data. Cheers, Thomas Am 20.08.2014 18:26, schrieb Tim Ward t...@telensa.com [firebird-support]: You want to be looking around your external CSV file being an external table - that's the only way to write to files from stored procedures AFAIK, so, no matter how convoluted the code ends up, that's probably the approach you'll have to take. (Unless there's new stuff in 2.5 I don't know about, I've only done this with 2.1.) On 20/08/2014 17:19, conver...@gmail.com [firebird-support] wrote: Hi there, As subject, is it possible to export a query result set to an external csv file from a Firebird stored procedure? I know this can be done with IBExpert but we are looking for a pure firebird sql solution. We are using Firebrd 2.5. Any pointers on how to do this will be much appreciated. Regards, -Ed -- Tim Ward -- Mit freundlichen Grüßen, Thomas Beckmann Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
[firebird-support] Execute statement on external does not enter default values in target table
Hi, it seems to me, that execute statement on external enters NULL in the target table in columns that are not mentioned in an insert statement - at least, the default values from domain definition are not used. Example: in test.fdb create domain D_TID as integer default current_transaction; recreate table TEST ( TEST integer, TID D_TID ); from another db execute statement 'insert into TEST (TEST) values (1)' on external 'test.fdb' with common transaction; commit; In test.fdb, column TID is NULL. If the insert statement is issued in test.fdb, it's the transaction ID. Is this a feature or a bug? Thank you for hints! Thomas -- Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
[firebird-support] Is there still a difference between sweep and gabage collection?
Hi everybody, in her book in chapter 39 (Housekeeping tool), Helen explains the difference between garbage collection and sweep: However, gbak doesn't perform a full sweep. Like the regular garbage collection, it leaves deleted and rolled-back versions alone. Sweeping is the only way to get rid of these, short of restoring database from a backup. I wonder whether this still holds true, 'cause reading in current how tos and web pages even on firebirdsql.org makes me assume sweeping and garbage collection are the same today. On the other hand, we have a database with automatic sweep off that was regularly gbaked with GC on where a manual sweep takes many hours... Can someone shed some light on this? Thanks alot, Thomas -- Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet.
Re: [firebird-support] Re: Is there still a difference between sweep and gabage collection?
Many thanks for your comments! Thomas -- Diplom-Informatiker Wielandstraße 14c • 23558 Lübeck Tel +49 (22 25) 91 34 - 545 • Fax +49 (22 25) 91 34 - 604 Mail thomas.beckm...@assfinet.de mailto:thomas.beckm...@assfinet.de ASSFINET-Logo *ASSFINET Dienstleistungs-GmbH* Max-Planck-Straße 14 • 53501 Grafschaft bei Bonn i...@assfinet.de mailto:i...@assfinet.de • www.assfinet.de http://www.assfinet.de/ Geschäftsführer: Dipl. Wirtschaftsinformatiker Marc Rindermann Registergericht Koblenz HRB 23331 Diese E-Mail enthält vertrauliche Informationen. Wenn Sie nicht der richtige Adressat sind oder diese E-Mail irrtümlich erhalten haben, informieren Sie bitte sofort den Absender und vernichten Sie diese Mail. Das unerlaubte Kopieren sowie die unbefugte Weitergabe dieser Mail ist nicht gestattet. ++ 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/
