Firebird “case” construct

This is a handy feature that has been in the Firebird database system a while (but I just learned about).

I needed to replace certain results in my select query. I use a SQL query to generate an Excel file that I upload to a stock listing service for aircraft parts. The problem is, this stock listing service does not like certain part condition codes. So I need to replace all instances of RP with SV.

So I use a query like this:

select stock.pn,
   case condition_code
        when 'RP' then 'SV'
        else condition_code
    end condition_code
from stock inner join part_condition_codes on
    (stock.pcc_auto_key=part_condition_codes.pcc_auto_key)
where STOCK.IFC_AUTO_KEY=4;

What this does is if the “condition_code” is RP then it changes it to SV. If it is anything else it just uses whatever that value is. The “condition_code” after “end” is not necessary – I wanted the field to be named condition_code.

You can add multiple “when …” clauses.

And of course RTFM! Here is the page in the manual:
http://www.firebirdsql.org/refdocs/langrefupd15-case.html

 

Firebird Automatic Database Restore on to Backup Server

I maintain a server running the Firebird database that backs-up the database on a daily basis. I also have a server that can be used as a backup in case the main server fails. I wanted to make it so the database is automatically restored on to the backup server every day so I always have the previous days’ data on that server ready to go.

This is specifically for Quantum Control software (which uses Firebird) but I hope it might be helpful to other users of Firebird as well.

This is mainly for users who have some knowledge of database backup and windows batch scripting since the script will require modification to fit your circumstances.

I have a batch file (cp_bak.bat) located in a folder on the desktop names “mainserver QC bak”.

If you use this script you will want to modify several things. First off update all of the locations and server names. Here I use “mainserver” for my database server that clients are using day to day.
For the “net use” command you will want to use appropriate credentials to access the backup file (which I back up with 7z).

@echo off

cd "\Documents and Settings\Administrator\Desktop\mainserver QC bak"

echo Accessing share (net use)...
net use \\mainserver\Quantum mikepassword /USER:mike
echo Copying QC.7z from mainserver...
copy \\mainserver\Quantum\bak\QC.7z "C:\Documents and Settings\Administrator\Desktop\mainserver QC bak\"

echo Removing old extract (Will print error if it does not exist)...
del QUANTUM.FDB.bak
echo Extracting...
C:\Admin\Bin\7za.exe e QC.7z

echo Stopping firebird
sc stop FirebirdGuardianDefaultInstance
timeout 4
echo Overwriting database with backup...
move QUANTUM.FDB.bak C:\Quantum\Database\QUANTUM.FDB
echo Starting firebird
sc start FirebirdGuardianDefaultInstance
timeout 3
echo "Unlocking database..."
"c:\quantum\firebird\bin\gfix.exe" -online "127.0.0.1:c:\quantum\database\quantum.fdb" -user sysdba -pass masterkey 2> "c:\quantum\database\error.txt"

timeout 1

echo "Setting correct security database, turning off maintenance broadcast"
cd "\Documents and Settings\Administrator\Desktop\mainserver QC bak"
"c:\quantum\firebird\bin\isql.exe" "127.0.0.1:c:\quantum\database\quantum.fdb" -i security_update.fbsql -u sysdba -p masterkey 

echo Done!

This script references a SQL file in the same directory named security_update.fbsql

update quantum set locked='F';

update quantum set serverdb='192.168.2.3:c:\quantum\firebird\security.fdb', broadcast='F';

Important things to not in this is that this is the security database on the backup server with the backup IP address. It also disables the database maintenance broadcast message (in Quantum Control).

QC: Querying for invoice detail with consignment breakdown

This post is for the Quantum Control system (inventory software made by Component Control).

Here is my query for getting sales for a particular consignment (so if you want more you will want to drop the two stock*.cnc_auto_key = 970). This works on Firebird (I haven’t tested it on Oracle). I use the “union all” since the credits need to be pulled separately.
970 is the cnc_auto_key of the consignment I am going for. This tends to run a whole lot faster for me than the Invoice Detail Report in Quantum.

I use this in PHP so for the date range you will want to replace the $begin_date and $end_date variables with appropriate dates.

 

 SELECT

 stockouter.pn,
 stockouter.description,
 invc_detail.unit_price,
 invc_detail.unit_cost,
 STOCK_RESERVATIONS.qty_invoiced qty_invoiced,
 (invc_detail.unit_price * STOCK_RESERVATIONS.qty_invoiced) total_price,
 companies.company_name,
 companies.country,
 invc_header.invoice_date
 FROM

 INVC_HEADER INVC_HEADER
 inner join companies on
 (companies.cmp_auto_key = invc_header.cmp_auto_key)

 INNER JOIN INVC_DETAIL INVC_DETAIL ON
     (INVC_DETAIL.INH_AUTO_KEY = INVC_HEADER.INH_AUTO_KEY)

 INNER JOIN SO_DETAIL SO_DETAIL ON
 (SO_DETAIL.SOD_AUTO_KEY = INVC_DETAIL.SOD_AUTO_KEY)

 INNER JOIN STOCK_RESERVATIONS STOCK_RESERVATIONS ON
 (STOCK_RESERVATIONS.SOD_AUTO_KEY = SO_DETAIL.SOD_AUTO_KEY
 and stock_reservations.ind_auto_key = invc_detail.ind_auto_key
 )

 INNER JOIN STOCK stockouter ON
 (stockouter.STM_AUTO_KEY = STOCK_RESERVATIONS.STM_AUTO_KEY)

 WHERE
 invc_header.invoice_date >= '$begin_date'
 and invc_header.invoice_date <= '$end_date'
 and stockouter.cnc_auto_key = 970
 and not invc_detail.route_code = 'M'

 union all

 SELECT
 stockouterc.pn,
 stockouterc.description,
 invc_detailc.unit_price ,
 invc_detailc.unit_cost,
 invc_detailc.qty_ship qty_invoiced,
 invc_detailc.unit_price * invc_detail.qty_ship total_price,
 companiesc.company_name,
 companiesc.country,
 invc_headerc.invoice_date
 FROM
 INVC_HEADER INVC_HEADERc
 inner join companies companiesc on
 (companiesc.cmp_auto_key = invc_headerc.cmp_auto_key)

 INNER JOIN INVC_DETAIL INVC_DETAILc ON
     (INVC_DETAILc.INH_AUTO_KEY = INVC_HEADERc.INH_AUTO_KEY)
 INNER JOIN STOCK stockouterc ON
 (stockouterc.ind_AUTO_KEY = invc_detailc.ind_auto_key)
 WHERE
 invc_headerc.invoice_date >= '$begin_date'
 and invc_headerc.invoice_date <= '$end_date'
 and stockouterc.cnc_auto_key = 970
 and invc_detailc.route_code = 'M'

 ;
I assume you’ll want an inner join to the consignment table on both queries here to pull in the consignment codes (instead of doing it by cnc_auto_key.
If anyone needs that let me know and I’ll update it.
To break it down: the first half of the query basically addresses the fact that for normal invoices you need to connect through stock reservations. And the second half addresses the fact that for credit memos they do not go through stock reservations but instead ind_auto_key on the stocklines.