Thursday 24 September 2015

Firebird Interview Questions and Answers for freshers pdf free download

6. how do convert or display the date or time as string? 
Simply use CAST to appropriate CHAR or VARCI-TAR data type (big enough). Example:
CREATE TABLE t1 (t time, d date. ts timestamp);
INSERT INTO t1 (t,d,ts) VALUES (‘14:59:23’, ‘2007-12-3 1’, ‘2007-12-31 14:59’);
SELECT CAST(t as varchar(1 3)), CAST(d as varchar( 10)), CAST(ts as varchar(24)) FROM t1;
Firebird would output times in HH:MM:SS.mmmm format (hours, minutes, seconds, milliseconds), and dates in YYYY-MM-DD (year, month, day) format.
if you wish a different formatting you can either use SUBSTRING to extract the info from char column, or use EXTRACT to buld a different string:
SELECT extract(day from d)||’.’||extract(month from d)||’.‘||extract(year from d) FROMt1;

7. How to create a database from my program? 
Firebird doesn’t provide a way to create database using SQL You need to either use the Services API, or external tool. As API for database creation is often not available in libraries, you can call Firebird’s isql tool to do it for you.
Let’s first do it manually. Run the isql, and then type:
SQL>CREATE DATAB ASE ‘C :\dbases\database. Rib’ user ‘SYSDBA’ password
‘masterkey’;
That’s it, Database is created. Type exit; to leave isql.
To do it from program, you can either feed the text to execute to isql via stdin, or create a small file (ex. create sql) containing the CREATE DATABASE statement and then invoke isql with -i option:
isql -i create.sql

8. How to deactivate triggers? 
You can use these SQL commands:
ALTER TRIGGER trigger_name INACTIVE;
ALTER TRIGGER trigger_name ACTIVE;
Most tools have options to activate and deactivate all triggers for a table. For example, in
FlameRobin, open the properties screen for a table, click on Triggers at top and then
Activate or Deactivate All Triggers options at the bottom of the page.

9. How to debug stored procedures? 
Firebird still doesn’t offer hooks for stored procedure debugging yet. Here are some common workarounds:
* You can log values of your variables and trace the execution via external tables. External tables are not a subject of transaction control, so the trace won’t be lost if
transaction is rolled back.
* You can turn your non-selectable stored procedure into selectable and run it with ‘SELECT * FROM’ instead of ’EXECUTE PROCEDURE’ in order to trace the execution. Just make sure you fill in the variables and call SUSPEND often. It’s a common practice to replace regular variables with output columns of the same name - so that less code needs to be changed.
* Some commercial tools like IBExpert or Database Workbench parse the stored procedure body and execute statements one by one giving you the emulation of stored procedure run. While it does work properly most of the time, please note that the behaviour  you might see in those tools might not be exactly the same as one seen with actual Firebird stored procedure - especially if you have uninitialized variables or other events where behavior is undefined. Make sure you file the bug reports to tool makers and not to Firebird development team if you run such ‘stored procedure debuggers’.
* Since Firebird 2.0 you can also use EXECUTE BLOCK to simulate stored procedures. EXECUTE BLOCK does not support input parameters, so you need to convert all of those to local variables (with DECLARE VARIABLE)

10. How to detect applications and users that hold transactions open too long? 
To do this, you need Firebird 2.1 or a higher version. First, run gstat tool (from your Firebird installation’s bin directory), and you’ll get an output like this:
gstat -h faqs.gdb
Database “faqs.gdb”
Database header page information:
Flags 0
Checksum 12345
Generation 919
Page size 4096
ODS version Ii .1
Oldest transaction 812
Oldest active 813
Oldest snapshot 813
Next transaction 814
Now, connect to that database and query the MON$TRANSACTTONS table to get the
MON$ATTACHMENT_ID for that transaction, and then query the
MONSATTACHMENTS table to get the user name, application name, 1P address and
even PID on the client machine. We are looking for the oldest active transaction, so in this case, a query would look like:
SELECT ma.*
FROM MON$ATTACHMENTS ma
join MON$TRANSACTIONS mt
on ma.MON$ATTACHMENT ID - mt.MONSATTACHMENTID
where mt.MONSTRANSACTION_ID = 813,

More Questions & Answers:-

No comments:

Post a Comment