When you run the program it will display all the records in the States table.

Getting a specific record

These two example so you how to edit a record and get a specific record. This example will select a specific record from the database. So start a new project and add 2 text boxes and command button. Then cut and past the code below.

Text1 = txtStateCode
Text2 = txtState -Enabled = False
Command1 = cmdGetState

Private Sub cmdGetState_Click()
Dim adoConn2 As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim constr As String
Dim sql As String

If txtStateCode = "" Then MsgBox "Please enter a State Code!": txtStateCode.SetFocus: Exit Sub

constr = "Provider=ZStyle IBOLE Provider; Data Source=" _
 & "c:\Program Files\Firebird\Firebird_1_5\Samples\Httplogs.gdb; UID=sysdba; password=pwd"

adoConn2.Open constr

sql = "SELECT * From STATES Where State_Code='" & UCase(txtStateCode) & "'"
Set rs = adoConn2.Execute(sql)

txtState = rs(1)

rs.Close
adoConn2.Close

End Sub

You will notice that we used the UCase function on the State Code text box. We did this because Firebird treats the records a case sensitive and the state codes were entered as capital letters. Instead of having a message box pop up if the user enters the state code in lower case letters I decided it would be easier to just make sure even if the used lower case the UCase would change it.

Editing a record

This example will show you how to add and edit a specific record in the database. So start a new VB project and add a 2 text boxes and 2 command buttons.

Private Sub cmdEdit_Click()
Dim cn1 As New ADODB.Connection
Dim sql As String

If txtEStateCode = "" Then MsgBox "Please enter a State Code!": _
txtEStateCode.SetFocus: Exit Sub

cn1.ConnectionString = "Provider=ZStyle IBOLE Provider; Data Source=" _
& "c:\Program Files\Firebird\Firebird_1_5\Samples\Httplogs.gdb; UID=sysdba; password=pwd"
cn1.Open

sql = "Update States Set State_Name='" & txtEStateName & "'" & _
 & " Where State_Code='" & UCase(txtEStateCode) & "'"

cn1.Execute sql

cn1.Close

txtEStateCode = ""
txtEStateName = ""

End Sub

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim sql As String

If txtEStateCode = "" Then MsgBox "Please enter a State Code!": _
txtEStateCode.SetFocus: Exit Sub

cn.ConnectionString = "Provider=ZStyle IBOLE Provider; Data Source=" _
& "c:\Program Files\Firebird\Firebird_1_5\Samples\Httplogs.gdb; UID=sysdba; password=pwd"
cn.Open

sql = "Insert Into States (State_Code, State_Name) Values" _
        & "('" & UCase(txtEStateCode) & "','" & txtEStateName & "')"
cn.Execute sql

cn.Close

txtEStateCode = ""
txtEStateName = ""
End Sub

You may have noticed that we used the SQL UpDate and Insert Into statements instead of using ADO, AddNew and Edit, Update. Well they would not work with Firebird, I have found it is best to use SQL to work with Firebird instead of using ADO statements. Remember that ADO is for Microsoft databases so while they understand the AddNew and Edit other database may not.

Adding multiple records

Our last examples showed you how to add and edit a single record. The example will show you how to add multiple items to the database. So start a new VB project and add a list box and command button, then cut and paste the code below.

Private Sub cmdAdd_Click()
Dim cn As New ADODB.Connection
Dim sql As String
Dim i, rowCount As Integer
Dim lineItem As String
Dim itemArray

cn.ConnectionString = "Provider=ZStyle IBOLE Provider; Data Source=" _
& "c:\Program Files\Firebird\Firebird_1_5\Samples\Httplogs.gdb; UID=sysdba; password=pwd"  

cn.Open

'Add items to database 
rowCount= List1.ListCount 'getthe number of rows in the list  box

Do Until i = rowCount
lineItem = List1.List(i)
itemArray = Split(lineItem, vbTab)
            
sql = "Insert Into States (State_Code, State_Name) Values" _
        & "('" & UCase(itemArray(0)) & "','" & itemArray(1) & "')"

i =  i + 1 
cn.Execute sql
Loop

cn.Close
List1.Clear 
End Sub

Sub Private Sub Form_Load()
List1.AddItem "CO" & vbTab & "Colorado"
List1.AddItem "CT" & vbTab & "Connecticut"
List1.AddItem "MN" & vbTab & "New Mexico"
List1.AddItem "WY" & vbTab & "Wyoming"

End Sub

When excuted the above code will add the 4 States into the Httplogs database.

You will notice that I used the Split function. Since I separated the State Code and the State Name with the VbTab I use that to split the two. If you are using VB6 then you don't have to do anything, however if you are using VB5 then you will have to create a module and add the Split and ReadUntil functions. With out these two functions the code will not work in VB5 since VB5 doesn't have a split function built in like VB6 does.

You can get the code for the Split and ReadUntil functions below.
http://home.pacbell.net/cetta/vb6string-fun-vb5.htm

Creating and calling a Stored Proedure

Yes that's right I said Stored Procedure, because unlike Access FB can do stored procedures, triggers and many other things Access cant do. But in all fairness to Access it is a desktop database and not a server database like FB. So these two examples will show you how to create and then call a stored procedure.

Creating a Stored Procedure

Creating a stored procedure and then adding it to the FB database is pretty straight forward. You can either use notepad and create it or you can use IB_SQL. For the purpose of this demo all you need to do is copy and paste the code below in the scripts window of IB_SQL and execute it.

Create Procedure SP_GETLIST

BEGIN
 SELECT State_Code, State_Name

 FROM States

 Where State_Code = 'VA'
 INTO :State_Code, :State_Name;


 SUSPEND;

END

To check and make sure that the procedure has been added click on the browse icon (the glasses) and then click the "Procedures" tab. If all went well you should see the stored procedure you just created.

Calling a Stored Procedure

Calling a stored procedure is easy since ADO has done all the work for us. So start a new VB project add two text boxes and a command button then copy and paste the code below.

Private Sub Command1_Click()
Dim cn As New ADODB.Connection
Dim cmd As ADODB.Command

cn.ConnectionString = "Provider=ZStyle IBOLE Provider; Data Source=" _
& "c:\Program Files\Firebird\Firebird_1_5\Samples\Httplogs.gdb; UID=sysdba; password=pwd"
cn.Open

Set cmd = New ADODB.Command
cmd.ActiveConnection = cn
cmd.CommandText = "SP_GETLIST"
cmd.CommandType = adCmdStoredProc

cmd.Execute

Text1 = cmd.Parameters(0).Value
Text2 = cmd.Parameters(1).Value

cn.Close
End Sub

See who easy it was to call that procedure? All we did was use the Command Text and Type properties! When we run the program VA will be in Text1 and Virginia will be in Text2.

Firebird and Crystal reports

Yes you can use Crystal reports with Firebird, its takes a little more steps but once you do it, it's really easy. When creating a report select New and Custom then SQL/ODBC Click "Log on to server" Select Active Data(ADO), then click OK. Click the ADO and OLE DB and enter the connection string below with out the quotes. The string below assumes that you are using the ZStyle driver, if however you are using a different driver then you will need to replace the driver name.

"Provider=ZStyle IBOLE Provider; Data Source=C:\DirPath\invgen.gdb; UID=sysdba; password=pwd"

Then click the next button and enter the user name and password. Once you are logged in you can use the Recordset or SQL option to select the table and records you need to create the reports.

If you select the  Recordset("Pick form list of Database Object") option, then you can select each table you want in the report. Just remember if you are using more the one table you will have to use the "Add Database to Report" then select "OK" and add the next table. Just remember you will have to change the table name using an alias, the first table will be ado so either make the next table ado1 or what ever name you wish.

If you click the SQL option then you will have to use a select statement to pull all the tables records you will need. The example below uses a select statement pulling from two tables to create a customer payment history.

Customer History
Select Customer.CustID, Customer.CustName, ARInvoice.CustInv, ARInvoice.InvDate, ARInvoice.DownPrePay,
ARInvoice.Total, ARInvoice.CheckNo, ARInvoice.PayDate, ARInvoice.Paid, ARInvoice.PostDate
From Customer, ARInvoice Where ARInvoice.CustID = Customer.CustID

Now that you have the tables and records you need then you would use Crystal the same way as if you were using Crystals built in selection features.

Firebird the Deployment

Now that you have completed your application its time to create the installation program for distribution. Since I use Inno setup I will be focusing on that, however I will provide a site that also has scripts for Wise, Installshield and Microsoft installers. Since Firebird can be use as a desktop or server database the installation setup will be different.

If you created a client installation then all you have to do is include the database, gds32.dll, firebird.msg and if needed MSVCRT.DLL. The gds32.dll can be put in the same directory as the firebird.msg which is in the Firebird\Firebird_1_5\ directory, or you can put it in the Windows\System directory.

If you created a application that needs the Firebird server then you will have to include the Firebird installation setup with your own. To do that you would setup your install then user the "Run" function of Inno Setup to have the Firebird installation run after yours. Below is a sample of an Inno Setup of A VB application and Firebird.

[Setup]
AppName= Test App
AppVerName= Test App 1.0
AppCopyright = Copyright © 2001-2003 Company Name
DefaultDirName={pf}\Test App
DefaultGroupName= Test App
UninstallDisplayIcon={app}\Test_App.exe
AdminPrivilegesRequired=1
LicenseFile=license.txt
; uncomment the following line if you want your installation to run on NT 3.51 too.
;MinVersion=4.0,3.51

[Tasks]
Name: "desktopicon"; Description: "Create a &desktop icon"; GroupDescription: "Additional icons:"; MinVersion: 4,4

[Files]
;Begin App Files
Source: "C:\VB Programs\Test_App.exe"; DestDir: "{app}"; CopyMode: alwaysoverwrite

[Icons]

[Run]
Filename: "{app}\ Firebird-setup.exe"; Description: "Running Firebird Server Setup";
Filename: "{app}\Test_App.exe"; Description: "Launch Test App?"; Flags: nowait postinstall skipifsilent

Of course this setup doesn't have any of the System or VB files but it's only purpose is to show you who to include the Firebird installation with your application. What this will do is install your application then run the Firebird setup there by installing Firebird on the end users machine. Then it will ask you if you want to run the Test App application.

Install Scripts downloads
http://ibinstall.defined.net/dl_scripts.htm

InterBase/Firebird Installation & Deployment Options
http://tinyurl.com/litq

Firebird and Web Development

Just like other database servers you can also use Firebird for web development, just like MySQL. In fact Firebird is a more robust database then MySQL it just hasn't had as much exposure as MySQL. Below are links and information to show you have to use Firebird on your web server.

The first then you will need is a database library to access Firebird. Just look at it as a driver that will allow you to work with Firebird. The link below provides a free library that will let you use ADO with PHP and a driver that will let you use ASP with ADO to access Firebird over the internet.

ADOdb Database Library for PHP
http://adodb.sourceforge.net/

ADOdb Library for PHP Manual
http://phplens.com/adodb/

Getting started with php and InterBase/Firebird
http://www.phpbuilder.com/columns/glodt20020212.php3?page=1

Firebird Resources

Now that we going over installing, using and deploying Firebird below are other resources that will help you get a better understanding of Firebird.

Firebird examples
http://tinyurl.com/liyr

Borland code central
http://tinyurl.com/lizc

MSSQL And Access To InterBase/Firebird Migration Wizard
http://tinyurl.com/liys

DataPump - Migration tool
http://tinyurl.com/liyx

Firebird Forum
http://tinyurl.com/liz0

Firebird WebRing
http://v.webring.com/

Firebird Summary