[firebird-support] Add 1 minute to a TimeStamp
Greetings All, What is the best way in a stored procedure to add a minute or a few seconds to a TimeStamp? Thanks, Mike --- This email has been checked for viruses by Avast antivirus software. http://www.avast.com
Re: Odp: [firebird-support] Cumulative queries
If you want to query (management asks for) the yearly turnover of the company from the start of the year to yesterday and you have 100+ invoices ... It is time and resource consuming, even with stored procedures. If I have monthly archives (tables) then I have to query each of the tables. Tiberiu
RE: [firebird-support] Cumulative queries
Are you running superserver ? If so i would recommend switching to classic or superclassic Regards Bogdan From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: Thursday, March 12, 2015 8:06 AM To: firebird-support@yahoogroups.com Subject: Odp: [firebird-support] Cumulative queries Hi, This look for me like wrong design. Query running minutes are not acceptable from my POV. I do not know how your system work, but in any stock system i see month work. Month is closed and data are calculated and stored. And in next month the same Regards, Karol Bieniaszewski - Reply message - Od: apos;Tiberiu Horvathapos; tiberiu_horv...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cumulative queries Data: czw., mar 12, 2015 07:19 Hi all, I have a Firebird stock management system with 100+ tables and 10+ clients. I have some cumulative queries in my Delphi program that generates reports. My problem is that while the queries are run (less than 5 minutes each), the whole system performance is poor, people complaining about the speed of the database. How do you deal with this ? Thinking about : 1. Create a backup database and have the queries run there (backup / restore during the night) ? This would be the obvious option. If data for the reports is one day behind the real system is not a big problem. 2. In FireBird do I have some switch to lower the priority of a query ? select * from table –low priority mode- some other ideas ? Thank you in advance , Tiberiu --- Ta e-pošta je bila pregledana z Avast protivirusnim programom. http://www.avast.com
Re: [firebird-support] How To Display Null Values At the End For More Than One Columns Specified in Order By Clause?
Hi SET,
I have already tried but still not getting the result. Last column displays
Null value in middle of some rows.
With Best Regards.
Vishal
On Thursday, 12 March 2015 6:26 PM, Svein Erling Tysvær
svein.erling.tysv...@kreftregisteret.no [firebird-support]
firebird-support@yahoogroups.com wrote:
I am using Firebird database with 2.1 version. I have one issue in Order
By clause.
I have SQL which gives multile records for multiple employees, i.e. one
employee has multiple records as an output in SQL.
I have Four columns in Order By Clause and I want every columns Null value
should be displayed after Not Null value in every column.
I googled and found that if I Use
Order By Column1 Asc Nulls Last
It works very well for the first columns but for the remaining two columns it
doesn't work i.e. for these two remaining columns the
Null value comes in middle i.e. first some values Not Null, then Null Values
and then again Not Null Values.
I need to display Null Values at the end of every Not Null value for every
Four columns specified in Order By Clause for every employee.
I tried Case When, then Asc for every column in Order By clause, but didn't
get the expected result.
This type of question is often best asked with an example. Let's say you have
these data:
MyTable
FieldA FieldB FieldC
1 2 3
1 2 null
1 null 2
2 3 4
2 null null
null 2 3
To get the result in the order it has above, I would use
ORDER BY FieldA NULLS LAST, FieldB NULLS LAST, FieldC NULLS LAST
If you for some strange reason want THIS order (the columns with the largest
number of nulls should be last):
FieldA FieldB FieldC
1 2 3
2 3 4
1 2 null
1 null 2
null 2 3
2 null null
then you need your CASE construct to be something like:
ORDER BY IIF(FieldA IS NULL, 2, 0)+IIF(FieldB IS NULL, 3, 0)+IIF(FieldC IS
NULL, 4, 0), FieldA, FieldB, FieldC
If there are four fields to order by, you have to use minimum 3, 4, 5 and 6
rather than 2, 3 and 4.
HTH,
Set
#yiv1404542990 #yiv1404542990 -- #yiv1404542990ygrp-mkp {border:1px solid
#d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}#yiv1404542990
#yiv1404542990ygrp-mkp hr {border:1px solid #d8d8d8;}#yiv1404542990
#yiv1404542990ygrp-mkp #yiv1404542990hd
{color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px
0;}#yiv1404542990 #yiv1404542990ygrp-mkp #yiv1404542990ads
{margin-bottom:10px;}#yiv1404542990 #yiv1404542990ygrp-mkp .yiv1404542990ad
{padding:0 0;}#yiv1404542990 #yiv1404542990ygrp-mkp .yiv1404542990ad p
{margin:0;}#yiv1404542990 #yiv1404542990ygrp-mkp .yiv1404542990ad a
{color:#ff;text-decoration:none;}#yiv1404542990 #yiv1404542990ygrp-sponsor
#yiv1404542990ygrp-lc {font-family:Arial;}#yiv1404542990
#yiv1404542990ygrp-sponsor #yiv1404542990ygrp-lc #yiv1404542990hd {margin:10px
0px;font-weight:700;font-size:78%;line-height:122%;}#yiv1404542990
#yiv1404542990ygrp-sponsor #yiv1404542990ygrp-lc .yiv1404542990ad
{margin-bottom:10px;padding:0 0;}#yiv1404542990 #yiv1404542990actions
{font-family:Verdana;font-size:11px;padding:10px 0;}#yiv1404542990
#yiv1404542990activity
{background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}#yiv1404542990
#yiv1404542990activity span {font-weight:700;}#yiv1404542990
#yiv1404542990activity span:first-child
{text-transform:uppercase;}#yiv1404542990 #yiv1404542990activity span a
{color:#5085b6;text-decoration:none;}#yiv1404542990 #yiv1404542990activity span
span {color:#ff7900;}#yiv1404542990 #yiv1404542990activity span
.yiv1404542990underline {text-decoration:underline;}#yiv1404542990
.yiv1404542990attach
{clear:both;display:table;font-family:Arial;font-size:12px;padding:10px
0;width:400px;}#yiv1404542990 .yiv1404542990attach div a
{text-decoration:none;}#yiv1404542990 .yiv1404542990attach img
{border:none;padding-right:5px;}#yiv1404542990 .yiv1404542990attach label
{display:block;margin-bottom:5px;}#yiv1404542990 .yiv1404542990attach label a
{text-decoration:none;}#yiv1404542990 blockquote {margin:0 0 0
4px;}#yiv1404542990 .yiv1404542990bold
{font-family:Arial;font-size:13px;font-weight:700;}#yiv1404542990
.yiv1404542990bold a {text-decoration:none;}#yiv1404542990 dd.yiv1404542990last
p a {font-family:Verdana;font-weight:700;}#yiv1404542990 dd.yiv1404542990last p
span {margin-right:10px;font-family:Verdana;font-weight:700;}#yiv1404542990
dd.yiv1404542990last p span.yiv1404542990yshortcuts
{margin-right:0;}#yiv1404542990 div.yiv1404542990attach-table div div a
{text-decoration:none;}#yiv1404542990 div.yiv1404542990attach-table
{width:400px;}#yiv1404542990 div.yiv1404542990file-title a, #yiv1404542990
div.yiv1404542990file-title a:active, #yiv1404542990
div.yiv1404542990file-title a:hover, #yiv1404542990 div.yiv1404542990file-title
a:visited {text-decoration:none;}#yiv1404542990 div.yiv1404542990photo-title a,
#yiv1404542990 div.yiv1404542990photo-title a:active, #yiv1404542990
div.yiv1404542990photo-title a:hover, #yiv1404542990
Re: [firebird-support] Distributing/deploying Stored Procedures
Hello Andrew, Welcome to the Firebird community. I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work. When modifying metadata, make sure everyone is logged out of your application. Although it’s possible to “do metadata stuff” with people connected, it’s not the preferred way. When you want to change a large number of procedures and triggers, it’s certainly possible to simply re-create them, certainly the easiest. However, when dropping, there will be dependencies from one procedure to another, so they have to be dropped in a specific order. There’s an alternative, however: 1) drop all triggers (that call procedures) 2) alter all procedures to an empty body, this keeps the parameter signature in place 3) drop all procedure 4) create all procedures with an empty body to establish parameter signatures 5) alter all procedures with complete body 6) create all triggers If you use a tool like our Database Workbench tool, you can alternatively compare your development to your production database and see what meta data has changed and extract a script, or automatically record a change script while doing all changes. Or extract a script for all procedures that does these create-with-empty-body-and-then-alter-cycle. Hope this helps. With regards, Martijn Tonies Upscene Productions http://www.upscene.com Download Database Workbench for Oracle, MS SQL Server, Sybase SQL Anywhere, MySQL, InterBase, NexusDB and Firebird! I’m a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites. We are in the process of redeveloping our software using FB as the back end (it was previously ISAM). We have a reasonably large client base and our application is installed on many sites which run independently. We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc. I have been tasked with finding a solution to distribute the update/upgrade SP. At the client end they will access the data via our application (via ODBC). They will have no direct access to the FB server and no ISQL command. I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work. Also, do I bother with ‘alter procedure …..’ or do I simply drop them and (re)create them (after all, at release time, the SP is the SP) I am trying to automate the task as much as possible, I don’t want our support staff to have to intervene in an upgrade. Surely I’m not the only one that has needed to do such a thing. Does such a mechanism exist? Perhaps some suitable suggestions?
Odp: [firebird-support] Cumulative queries
Hi, This look for me like wrong design. Query running minutes are not acceptable from my POV. I do not know how your system work, but in any stock system i see month work. Month is closed and data are calculated and stored. And in next month the same Regards, Karol Bieniaszewski - Reply message - Od: apos;Tiberiu Horvathapos; tiberiu_horv...@yahoo.com [firebird-support] firebird-support@yahoogroups.com Do: firebird-support@yahoogroups.com Temat: [firebird-support] Cumulative queries Data: czw., mar 12, 2015 07:19 Hi all, I have a Firebird stock management system with 100+ tables and 10+ clients. I have some cumulative queries in my Delphi program that generates reports. My problem is that while the queries are run (less than 5 minutes each), the whole system performance is poor, people complaining about the speed of the database. How do you deal with this ? Thinking about : 1. Create a backup database and have the queries run there (backup / restore during the night) ? This would be the obvious option. If data for the reports is one day behind the real system is not a big problem. 2. In FireBird do I have some switch to lower the priority of a query ? select * from table –low priority mode- some other ideas ? Thank you in advance , Tiberiu
RE: [firebird-support] Distributing/deploying Stored Procedures
Just in terms of DDL upgrades and releases, I take the following approach: - All DDL updates are scripted. Absolutely no click-click in a DB tool. This way I can also put my scripts in version control - I have only one stored proc in my DB. It is called GET_DATABASE_VERSION. It simply returns a string that indicates the DDL version of the DB. E.g. ‘2.3’ where 2 is the DDL version and 3 is the customer specific DDL version for e.g. reports and data fixes that have been run, etc. - With an update, say V2, I have one script file that runs all the others in correct sequence. It also updates the version SP to indicate the new DB DDL version An automated upgrade tool can then determine the actual DDL version of the DB (it could be different at various customers) and then run the appropriate updates to bring the DB to the correct version I would recommend against dropping / creating SPs to update because you will likely have dependencies. Just use CREATE OR ALTER PROCEDURE From: firebird-support@yahoogroups.com [mailto:firebird-support@yahoogroups.com] Sent: 12 March 2015 04:30 AM To: firebird-support@yahoogroups.com Subject: [firebird-support] Distributing/deploying Stored Procedures I’m a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites. We are in the process of redeveloping our software using FB as the back end (it was previously ISAM). We have a reasonably large client base and our application is installed on many sites which run independently. We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc. I have been tasked with finding a solution to distribute the update/upgrade SP. At the client end they will access the data via our application (via ODBC). They will have no direct access to the FB server and no ISQL command. I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work. Also, do I bother with ‘alter procedure …..’ or do I simply drop them and (re)create them (after all, at release time, the SP is the SP) I am trying to automate the task as much as possible, I don’t want our support staff to have to intervene in an upgrade. Surely I’m not the only one that has needed to do such a thing. Does such a mechanism exist? Perhaps some suitable suggestions? Cheers for now, Andrew I hope I explained my predicament sufficiently, basically I want to be able to duplicate SP etc from a development system to a production system easily with as much automation as possible. [Non-text portions of this message have been removed]
[firebird-support] Cumulative queries
Support for Users of Firebird ReleasesHi all, I have a Firebird stock management system with 100+ tables and 10+ clients. I have some cumulative queries in my Delphi program that generates reports. My problem is that while the queries are run (less than 5 minutes each), the whole system performance is poor, people complaining about the speed of the database. How do you deal with this ? Thinking about : 1. Create a backup database and have the queries run there (backup / restore during the night) ? This would be the obvious option. If data for the reports is one day behind the real system is not a big problem. 2. In FireBird do I have some switch to lower the priority of a query ? select * from table –low priority mode- some other ideas ? Thank you in advance , Tiberiu
[firebird-support] Distributing/deploying Stored Procedures
I'm a bit of a FB noob and am after some advice about Stored Procedures and the best way to distribute them to client sites. We are in the process of redeveloping our software using FB as the back end (it was previously ISAM). We have a reasonably large client base and our application is installed on many sites which run independently. We intend to make use of Stored Procedures, Triggers etc and between releases (during development) these objects will be created, dropped, modified etc. I have been tasked with finding a solution to distribute the update/upgrade SP. At the client end they will access the data via our application (via ODBC). They will have no direct access to the FB server and no ISQL command. I can pump SQL statements through the application to make certain changes the first time a new release is run (alter table etc) but am concerned at how I am supposed to update SPs (potentially a large number) without access to the FB server. I considered extracting the metadata(FlameRobin), deleting superfluous statements and pumping what is left through the app but that seems like A LOT of work. Also, do I bother with 'alter procedure .' or do I simply drop them and (re)create them (after all, at release time, the SP is the SP) I am trying to automate the task as much as possible, I don't want our support staff to have to intervene in an upgrade. Surely I'm not the only one that has needed to do such a thing. Does such a mechanism exist? Perhaps some suitable suggestions? Cheers for now, Andrew I hope I explained my predicament sufficiently, basically I want to be able to duplicate SP etc from a development system to a production system easily with as much automation as possible.
