![Page 1: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/1.jpg)
IBExpert
Advanced IDE Usage
![Page 2: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/2.jpg)
Firebird Conference 2019Berlin, 17-19 October
![Page 3: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/3.jpg)
Products
● Personal Edition● Day Edition● Developer Studio● CYE/ESYE● OEM
![Page 4: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/4.jpg)
Modules
● IBExpert.exe● IBEscript.exe/IBEScript.dll● hkscc
![Page 5: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/5.jpg)
Firebird Demo Database
● db1_30.sql demodb script● Table Editor
– Field dependencies
● Procedure Editor– Version History
● DB Registration– Show Systemtables
![Page 6: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/6.jpg)
Package Editor
● Putils Package– Procedure Psoundex
● Header● Body
![Page 7: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/7.jpg)
Functions
● Soundex– Lazy mode off
– Function uses SP for implementation
● IBExpert FreeAdhoc Function Library– Replacement for FreeAdhoc UDF dll/so
– Complete Implementation available for example for averp.fdb database on request
● Almost any existing dll/so udf can be converted– Ask IBExpert team for details
![Page 8: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/8.jpg)
Version History
● Also available for – Functions– Trigger
– Packages
– Stored procs
![Page 9: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/9.jpg)
SQL Editor
● Drag and drop objects from db explorer– Db registration
– Table object● Alias● Var prefix● Perform Code formatting
● Code Completion ● Keyboard Templates
![Page 10: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/10.jpg)
SQL Editor <> Script Executive
● Multiline SQL Statements →Script executive● Singleline SQL Statements → SQL Editor● Both can only execute parts when parts are selected● Db Query Organizer● execute procedure initall(10000)
– Message– Performance Analysis
![Page 11: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/11.jpg)
Debugging
● Open SP DELETEALL– Debugger
– Params, Breakpoints etc
– Debug mode:Collect Statistics
– F9 Run
– Goto Statistics Page ● Order by columns that took longest time
– Set breakpoint with condition
![Page 12: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/12.jpg)
V_TOP100SALES View and how it works● Select on a stored proc● select * from v_top100sales in sql editor
– Runs massive operation only once per minute
– See sp for details
● Basic concept Materialized View
![Page 13: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/13.jpg)
Using Version Control
● Tools-extract metadata– Vcs files
● Extract all to C:\conf\ex\● Open windows explorer, show file content● One file per object
– Separate Files● All meta objects and all data incl. Blobs● Optional Where clause● C:\conf\ex2\● Open windows explorer, show folder content
![Page 14: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/14.jpg)
Stored Procedure/Trigger View Analyzer● Red lines → bad
– Unused variable– Compatibilty
– No pk
![Page 15: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/15.jpg)
Dependencies Viewer
● Tools-dependencies viewer– Drag and drop product table– Expand all
![Page 16: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/16.jpg)
Tools - Table data compare
● Master db1● Target db2● Select product table● Online sync● Options
![Page 17: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/17.jpg)
Trace API
● Real world example● How to set up?● Grid mode
![Page 18: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/18.jpg)
Benchmark
● How to measure speed of firebird hardware/software– Ibexpert benchmark– Params
● Ext● Async
![Page 19: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/19.jpg)
Database inside
● What is database inside● How to use?● Db repair and export from corrupt databases
![Page 20: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/20.jpg)
IBExpert
●Advanced IBExpert Scripting
![Page 21: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/21.jpg)
IBEBlock Script Language
● Basic compatibilty to SP and Trigger language● A lot of extension
– Script editor– Drag and drop connection to editor
● Ibeblock create connection● ibec_createconnection F1● Odbc example
– Ibec_*
![Page 22: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/22.jpg)
DLL and exe
● Exe parameter– Use params for
● Connectionstring● Username and password● Global variable● -e encrypt script file to unreadable mode
● DLL– Example
– Lazarus source code
![Page 23: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/23.jpg)
Tools Extract Metadata
● Commandline based example– downgrade/upgrade db with global changes
● Charset● Owner
● Create script for extract● Show basics to search/replace in ibe$*.sql files
– ibec_stringreplace
– ibec_loadfromfile
– ibec_savetofile
![Page 24: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/24.jpg)
More examples
● Email processing● Url Downloading● Reporting and pdf creation● Running ibescript from Windows Scheduler
![Page 25: IBExpert - Firebird...Firebird Demo Database db1_30.sql demodb script Table Editor – Field dependencies Procedure Editor – Version History DB Registration – Show Systemtables](https://reader030.vdocuments.us/reader030/viewer/2022040100/5eb678ff2d76ef560269ec72/html5/thumbnails/25.jpg)
Other tools
● Ibegbak.exe● Ufw.exe● Hkscc.exe● Watchdog