Firebird ODBC: Difference between revisions

Latest revision as of 08:25, 15 June 2022

Introduction

Is an External Utilities for other Application to Access Firebird Database

Pros

Can direct Retrieve/Get, Delete and/or Modified the Information/Data From Connected Database.

Cons

User might lost concurrent license (i.e. need to buy double)
Might corrupt the database if not careful
Might cause unable to upgrade database due to database is "modified"
Setting need to reset if user change the database file name
Setting need to reset if estream change firebird version or table/field name

Using this Application is at your own risk.

Estream will not responsible on any data corrupt or missing due to usage of this application.

No further support other then this but we will try our best to help

Requirement

Firebird Server 3.0 & above

- 32 bits

- 64 bits

SQL Accounting

Program

Setup

01. Open Control Panel.

02. Double Click Administrative Tools |

- ODBC Data Sources (32-bit), or

- ODBC Data Sources (64-bit)

03. Click on System DSN tab

04. Click Add button.

05. Select Firebird/InterBase(r) Driver

06. Click Finish button.

07. Under Firebird ODBC Setup, define the following info only and the rest by default :

Field Name	Description
Data Source Name (DSN)	Can be any Name (eg. FB)
Description	Can be any Description (eg. FB)
Database	The Firebird FDB file path Can get it under SQLAccounting\DB or estream\DB Folder
Client	The Firebird fbclient.dll path Can get it under SQLAccounting\firebird 3.0 or Firebird\Firebird 3_0 Folder For 64 Bit can Leave it empty
Database Account	Firebird user name (Default is SYSDBA)
Password	Firebird password (Default is masterkey)
Character Set	Set to UTF8 (For FB3.0 & above)
Dialect	Set to 3
Read(default write)	Check/Tick to make transactions read-only

Make sure when click Test Connection is shown Connection successful! then only continue others Steps
To access the FDB with network environment you can enter the ServerName or ServerIPAddress:Database Path at Database field

Example

ServerPC:C:\Program Files\estream\SQLAccounting\DB\ACC-0001.FDB or

192.168.0.18:C:\Program Files\estream\SQLAccounting\DB\ACC-0001.FDB

Below is example where to get the ServerName, Path & FDB

[top]

Example Access From Microsoft Excel

Below example is using Microsoft Excel 2016 to get Firebird Data using ODBC

Steps

01. Create New Worksheet

02. Click Data Tab

03. Clcik Get External Data | From Other Source | From Microsoft Query

04. Select FB* (The Data Source Name entered in ODBC)

05. Click OK button

06. Select the table at the Available tables and columns field

07. Click the > button to select the Columns

08. Click Next button till you see below dialog

09. Select View data or edit query in Microsoft Query option

10. Click Finish button

11. Click SQL button to edit your Query

12. Edit the Query Statement here

13. Click OK button when done

14. Click File | Return Data to Microsoft Excel

15. Click Ok button

[top]

@@ Line 1: / Line 1: @@
 ==Introduction==
 Is an External Utilities for other Application to Access Firebird Database
+===Pros===
+# Can direct Retrieve/Get, Delete and/or Modified the Information/Data From Connected Database.
+===Cons===
+# User might lost concurrent license (i.e. need to buy double)
+# Might corrupt the database if not careful
+# Might cause unable to upgrade database due to database is "modified"
+# Setting need to reset if user change the database file name
+# Setting need to reset if estream change firebird version or table/field name
 {| style="margin: 1em auto 1em auto;"
 |-
@@ Line 10: / Line 21: @@
 : Using this Application is at your own risk.
 : Estream will not responsible on any data corrupt or missing due to usage of this application.
+: No further support other then this but we will try our best to help
 |}
 ----------------------------------------------------------------------------------------------------------------------------------------------------
@@ Line 15: / Line 27: @@
 ==Requirement==
-* Firebird Server
+* Firebird Server 3.0 & above
+:- [https://github.com/FirebirdSQL/firebird/releases/download/R3_0_4/Firebird-3.0.4.33054_0_Win32.exe 32 bits]
+:- [https://github.com/FirebirdSQL/firebird/releases/download/R3_0_4/Firebird-3.0.4.33054_0_x64.exe 64 bits]
+* [https://www.sql.com.my/download/demo/sqlacc-setup.exe SQL Accounting] <br />
 ==Program==
 * [https://sourceforge.net/projects/firebird/files/firebird-ODBC-driver/2.0.4-Release/Firebird_ODBC_2.0.4.155_Win32.exe/download Firebird ODBC 2.0.4.155 Win32]
+* [https://sourceforge.net/projects/firebird/files/firebird-ODBC-driver/2.0.5-Release/Firebird_ODBC_2.0.5.156_x64.exe/download Firebird ODBC 2.0.5.156 Win64]
 ==Setup==
 :01. Open '''Control Panel.'''
-:02. Double '''Click Administrative Tools | ODBC Data Sources (32-bit)'''
+:02. Double Click '''Administrative Tools | '''
-:03. Click on '''User DSN''' tab
+:: - ODBC Data Sources (32-bit), or
+:: - ODBC Data Sources (64-bit)
+[[File:FB_ODBC-01.jpg|center]]
+:03. Click on '''System DSN''' tab
 :04. Click Add button.
-:05. Select '''Firebird/InterBase(r) Driver'''  and click finish to Firebird ODBC Setup.
+[[File:FB_ODBC-02.jpg|center]]
-:06. Under Firebird ODBC Setup, define the following info only and the rest by default :
+:05. Select '''Firebird/InterBase(r) Driver'''
+:06. Click Finish button.
+:07. Under Firebird ODBC Setup, define the following info only and the rest by default :
 [[File:FB_ODBC.jpg|center]]
 {| class="wikitable" style="margin: 1em auto 1em auto;"
@@ Line 36: / Line 57: @@
 | Description || Can be any Description (eg. FB)
 |-
-| Database || The Firebird FDB file path
+| Database ||
+* The Firebird FDB file path
+* Can get it under <span style="color:#0000ff">SQLAccounting\DB</span>  or <span style="color:#0000ff">estream\DB</span> Folder
 |-
-| Client || The Firebird fbclient.dll path
+| Client ||
+* The Firebird <span style="color:#0000ff">fbclient.dll</span> path
+* Can get it under <span style="color:#0000ff">SQLAccounting\firebird 3.0</span>  or <span style="color:#0000ff">Firebird\Firebird 3_0</span> Folder
+* For 64 Bit can Leave it empty
 |-
 | Database Account || Firebird user name (Default is '''SYSDBA''')
@@ Line 47: / Line 73: @@
 |-
 | Dialect || Set to '''3'''
+|-
+| Read(default write) || Check/Tick to make transactions read-only
 |}
+{| style="margin: 1em auto 1em auto;"
+|-
+|
+----------------------------------------------------------------------------------------------------------------------------------------------------
+{|
+|-
+| [[File:Template.Tips-01.jpg|60px]]||
+* Make sure when click '''Test Connection''' is shown <span style="color:#0000ff">Connection successful!</span> then only continue others Steps
+* To access the FDB with network environment you can enter the <span style="color:#0000ff">ServerName or ServerIPAddress:Database Path</span> at Database field <br>
+:Example <br>
+:ServerPC:C:\Program Files\estream\SQLAccounting\DB\ACC-0001.FDB or <br>
+:192.168.0.18:C:\Program Files\estream\SQLAccounting\DB\ACC-0001.FDB
+|}
+----------------------------------------------------------------------------------------------------------------------------------------------------
+|}
+Below is example where to get the ServerName, Path & FDB
+[[File:FB ODBC-11.gif]]
+<div style="float: right;">   [[#top|[top]]]</div>
+==Example Access From Microsoft Excel==
+Below example is using Microsoft Excel 2016 to get Firebird Data using ODBC
+===Steps===
+:01. Create New Worksheet
+[[File:FB_ODBC-03.jpg|center]]
+:02. Click '''Data''' Tab
+:03. Clcik '''Get External Data | From Other Source | From Microsoft Query'''
+[[File:FB_ODBC-04.jpg|center]]
+:04. Select '''FB*''' (The ''Data Source Name'' entered in ODBC)
+:05. Click OK button
+[[File:FB_ODBC-05.jpg|center]]
+:06. Select the table at the '''Available tables and columns''' field
+:07. Click the > button to select the Columns
+:08. Click Next button till you see below dialog
+[[File:FB_ODBC-06.jpg|center]]
+:09. Select '''View data or edit query in Microsoft Query''' option
+:10. Click Finish button
+[[File:FB_ODBC-07.jpg|700px|center]]
+:11. Click '''SQL''' button to edit your Query
+:12. Edit the Query Statement here
+:13. Click OK button when done
+[[File:FB_ODBC-08.jpg|center]]
+:14. Click '''File | Return Data to Microsoft Excel'''
+[[File:FB_ODBC-09.jpg|center]]
+:15. Click Ok button
+[[File:FB_ODBC-10.jpg|center]]
+<div style="float: right;">   [[#top|[top]]]</div>
+==See also==
+* Others [[Customisation]]