Firebird ODBC: Difference between revisions

From eStream Software
No edit summary
 
(39 intermediate revisions by the same user not shown)
Line 1: Line 1:
==Introduction==
==Introduction==
Is an External Utilities for other Application to Access Firebird Database
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;"
{| style="margin: 1em auto 1em auto;"
|-
|-
Line 10: Line 21:
: Using this Application is at your own risk.
: Using this Application is at your own risk.
: Estream will not responsible on any data corrupt or missing due to usage of this application.  
: 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==
==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==
==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.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==
==Setup==
:01. Open '''Control Panel.'''
:02. Double Click '''Administrative Tools | '''
:: - 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.
[[File:FB_ODBC-02.jpg|center]]
: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;"
|-
! 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 <span style="color:#0000ff">SQLAccounting\DB</span>  or <span style="color:#0000ff">estream\DB</span> Folder
|-
| 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''')
|-
| 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
|}
{| 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]]

Latest revision as of 08:25, 15 June 2022

Introduction

Is an External Utilities for other Application to Access Firebird Database

Pros

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

Cons

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

Template.Warning-01.jpg
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

Program

Setup

01. Open Control Panel.
02. Double Click Administrative Tools |
- ODBC Data Sources (32-bit), or
- ODBC Data Sources (64-bit)
FB ODBC-01.jpg
03. Click on System DSN tab
04. Click Add button.
FB ODBC-02.jpg
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 :
FB ODBC.jpg
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

Template.Tips-01.jpg
  • 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 FB ODBC-11.gif

Example Access From Microsoft Excel

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

Steps

01. Create New Worksheet
FB ODBC-03.jpg
02. Click Data Tab
03. Clcik Get External Data | From Other Source | From Microsoft Query
FB ODBC-04.jpg
04. Select FB* (The Data Source Name entered in ODBC)
05. Click OK button
FB ODBC-05.jpg
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
FB ODBC-06.jpg
09. Select View data or edit query in Microsoft Query option
10. Click Finish button
FB ODBC-07.jpg
11. Click SQL button to edit your Query
12. Edit the Query Statement here
13. Click OK button when done
FB ODBC-08.jpg
14. Click File | Return Data to Microsoft Excel
FB ODBC-09.jpg
15. Click Ok button
FB ODBC-10.jpg

See also