| Related sites for http://home.pacbell.net/cetta/VB-Firebird-SAP.html |
| The_Sinclair_ZX_Spectrum Emulators, games, and information. | | Rex_handheld_poised_for_resurrection Xircom plans to unveil the $150 Rex 6000, which builds on the 1997 original by adding a touch screen and improved software for syncing data with a PC. [CNET.com] (November 5, 2000) | | WebmasterEdge_com Webmaster services includes HTML, scripting, hosting, and site promotion tools and tutorials. Built-in bookmark system. | | Perl_Beans Implementation of the Java Beans framework in Perl. | | The_Clarion_Handy_Tools_Page A fully integrated Clarion application development tool kit. | | Advanced_PowerPoint_Exporter A plugin that generates a layered PowerPoint page with all graphical elements in tact and in position from any standard PSD image. | | RFC_2867 RADIUS Accounting Modifications for Tunnel Protocol Support. G. Zorn, B. Aboba, D. Mitton. June 2000. | | Tim\'s_Code_Page Free multiple user screen saver utility that allows Windows 9x/ME to set different screensavers for different users. Source code included. | | TD_Industrial_Coverings Advanced protective covers for robots. Specialized industrial coverings for heat shields, wrist covers and other applications. | | WING Web IMAP NNTP Gateway, an open source Apache/mod_perl based system which allows users to access email held on an IMAP server via any web browser. [Free] | | RFC_3180 GLOP Addressing in 233/8. D. Meyer, P. Lothberg. September 2001. | | RFC_2102 Multicast Support for Nimrod: Requirements and Solution Approaches. R. Ramanathan. February 1997. | | Omega_Charting_Services Converts a Fortran 77 source code to its flowchart equivalent, which is then ported into an Excel file. | | dBSTAT Contains three major components: database (data manipulation, comparison, and display), Statistics Wizard (descriptive and comparative statistics, nonparametric tests, correlation, and regression), | | A_Fight_to_Ban_Cellphone_Spam Hundreds of unsuspecting AT&T wireless subscribers in April protested when spam trickled from their PCs to cellular phones. [Wired] (June 6, 2000) | | Platinum_Website_Design Design of graphics, logos and web as well as database construction. | | Newsstar A multithreaded NNTP transport designed to transfer Usenet articles as quickly as possible between a local server and multiple remote servers, with score-based killfiles. Open source software, support | | Opulent_Designs Provides personal and commercial web site design and custom graphic services. | | CSLA Offers design, hosting, flash, CGI, java, consulting, and internet marketing services. | | Punta_Creations Website design, hosting, promotion, and management for small business and personal. Located in Stamford, Connecticut. |
|
|
VB Programming with Firebird and Sap DB
VB Programming with Firebird
Your developing an application that requires
a database, which one will you use? If the application is only being accessed by
no more then 4-5 people at any given time, then Access can be a good choice. But
what if this application will be accessed by 20-50 people or more at any given
time and needs to be a server then what?
Well you can use MS SQL, Oracle, Sybase or InterBase among others. But what if
you or your customer is on a tight budget then what? What if you needed a powerful
database server but you don't have the big bucks to fork over on the major databases!
Well your in luck, because there are open source databases out there other then
MySQL. While MySQL is a very good database lets face it, you cant do Stored
Procedures, although these features may be available in a year, but you need them now.
This month we will be looking Firebird. Firebird will blow Access out of the water and can
compete with MS SQL, Oracle, Sybase and InterBase. As a matter of fact firebird is InterBase,
on July 25th, 2000 Borland decided to make InterBase open source, to the horror of Microsoft.
Later they decided to keep up a commercial versions, so the open source version of InterBase
became Firebird and the commercial version is stilled called InterBase.
While I wanted to provide in depth examples on both Firebird and SAP DB (another open source database),
I could only do this on Firebird. While I was able to install SAP DB on my NT machine, the only thing
I was able to do is test if the database was running and start and stop the database. I installed the
web tools but for some reason they did not get installed properly because I was not able to access
them. Now it could be that my NT system was not up to specs, so if you are able to run SAP DB on your
machine successfully please let us know. By the way SAP DB only works on NT/2000 and XP. So I will be
going in depth on working with Firebird but I will only be able to provide you with links and
information on SAP DB. It's to bad really I was really looking forward to testing SAP DB.
Oh, by the way did I tell you that Firebird is FREE! When I say free I mean free both for
personal and commercial use.
Lets talk about Firebird!
Firebird
Over the next few weeks we be taking a look at Firebird. We will
be going over its features and working with this SQL server using VB.
A little background, Firebird started out as InterBase a SQL server
developed by Borland. Well actually that's not exactly true, When Borland
bought Ashton-Tate in October, 1991, they also got InterBase with it (version 3 at that time).
Since then Borland has make InterBase a better SQL server. Then in 2000 Borland
decided to make InterBase open source and Firebird was born. Even though Borland
went on to keep up the commercial version, I believe its at version 7.1.
Firebird is based on InterBase 6.
The first thing you will need to do is download Firebird
and install it. While Firebird can be used on the command line, it is not necessary as
there are both free and commercial GUI interfaces to Firebird. You will also need to
download a GUI interface, this will allow you to create, edit, add records and
manipulate the database.
The on good thing about Firebird is its size, the
download is around 3.2MB and installs in seconds. You can download the database along
with the GUI interface below, the link below is for the Windows version
of Firebird.
The other good thing about Firebird is that it supports the following
platforms.
WINDOWS 9x,NT,2000& XP
LINUX
NETWARE
SCO UNIX
SOLARIS
HP-UX
FreeBSD
Mac-OS (Darwin)
Firebird
firebird-win32Firebird Statistics (Upper Limits)
http://www.firebirdsql.org/Free IB SQL
http://www.ibobjects.com/ibo_ib_sql.html IB Manager Lite
Edition
- free versionhttp://http://sqlmanager.net/products/ibfb/manager/download
Firebird
Installation
Now that you have downloaded Firebird is
time to install it and the GUI Interface. Installation is simple, just double
click on the file and it will install the database. If you are installing
Firebird on Windows 9x then you will have to decide if you want to have the
database startup when you start windows, if so make sure that you checked that
option, if not then uncheck this option. You must also make sure that "Run as
Application" is checked and use Firebird Guardin. If however you are installing
Firebird on NT, 2000 or XP then you have a choice to run as a service or as an
application, I would suggest that you run it as a service. That's it when the
installation is complete the database server should be running, you will see a
greenish icon that sort of looks like a tower in the system tray.
You should also install one of the GUI
interfaces at this time.
One more thing, if you
have Firebird running on Windows 9x and you choose not to have
the database start when Windows runs you can either download an control panel applet that
will let you start and stop the server. I have also included a bat file
that will start the server for you so you don't have to use the command line.
Or you can create a short cut using windows.
Firebird Control Panel Applet
http://www.achim-kalwa.de/fbcc.phtml
Bat file to start Firebird server - Save the file as start-fb.bat
@echo off
"c:\Program Files\Firebird\Firebird_1_5\bin\fbguard" "-x"
@cls
Testing the Firebird server
Now that you have installed the server its time to test it and
make sure that everything is running properly, I promise it will be painless. I
will be using IB_SQL as the GUI interface, so my instructions will be geared
that way.
Since this version of the Firebird database does not come with a
sample database, I have attached a copy of a test database called Employee.gdb.
You need to create a folder called Samples under the Firebird_1_5 directory, then
unzip the file and place the database there.
Employee Database - 32k
http://www.ettasoft.com/images/Employee.zip
There are two ways to test the database, one is to use the
command line the other is to use the IB_SQL interface I will show you both
methods.
Testing using the command line
Click Start|Run and
type the following in the text box do not click enter key until you
have entered the full path . This
assumes that you excepted the default directory when you installed the
server.
"C:\Program Files\FireBird\Firebird_1_5\bin\isql.exe" "C:\Program
Files\FireBird\Firebird_1_5\Samples\employee.gdb" -u sysdba -p
pwd
If everything went well you should receive the SQL
prompt.
SQL>
At the SQL prompt type the following and click the enter key,
don't for get to type the semi-colon after the statement if not you will get the
CON> prompt if you get that then enter the semi-colon and click the enter
key.
Show Tables;
The following should be displayed.
Employees
Now type the following.
Select * From
Employees;
It should display the following.
EmployeeID LastName FirstName
100 Crockett Marietta
101 Janes Bill
102 Doe Jane
103 Doe John
To exit out of the database type the following.
exit;
Then type exit again to close the dos window.
Testing using IB_SQL
To test the database using IB_SQL do the following, start the program then
click the button next to the "Database" text box then click the first
button and find the Firbird_1_5|Sample directory and select the employee.gdb file.
Make sure you change the password to pwd then click the connect icon,
its the second icon from the left. If you didn't get any error messages you
are not connected, you will know this because the connect and disconnect icons
will turn red. They are the first two icons to the left. To further test
this click the Browse Database icon , it looks like a pair of glasses. In
the left left fram of the browser window you will see Employee, then click the data
tab then click the black arrow, it will list all the records in the Employee
table.
To exit out of IB_SQL close the browse window and click the
disconnect icon.
That's it we now know that the database is working and running
properly.
Creating a Firebird Database
Now that we have tested the database and
know that it's working, I will show you how to create a database and tables
using the command line as well as using IB_SQL. Once we do this the next step
will be to start using VB to access your Firebird database.
Creating a
database using the command line
To create a database on the command line
you need to click "Start" then "Run" then
type the following in the text
box.
"C:\Program
Files\FireBird\Firebird_1_5\bin\isql.exe"
At the SQL prompt type the following, and
at the end of each semi-colon click the enter key.
CREATE DATABASE 'httplogs.gdb' USER 'sysdba' PASSWORD 'pwd';
CREATE TABLE states (state_code VARCHAR(2) NOT NULL PRIMARY KEY, state_name
VARCHAR(30) NOT NULL);
INSERT INTO STATES (state_code, state_name) VALUES ('VA', 'Virginia');
INSERT INTO STATES (state_code, state_name) VALUES ('MD', 'Maryland');
SELECT * FROM states;
COMMIT;
EXIT;
This will create a database called
httplogs.gdb with one table "States" and two records VA, Virginia and MD,
Maryland.
Creating a
Database using IB_SQL
To create a database using IB_SQL is really
easy. Start IB_SQL in the "Database" text box enter the path you want the the
database placed as well as the name you want to give it.
Example:
C:\Program
Files\Firebird\Firebird_1_5\Samples\httplogs.gdb
Use the user name and password below to set
the user name and password then select SQL Dialect 3 then click the "Create
Database" option. That will be the first blue icon with the check mark on top.
user name sysdba
password pwd
That's it you have just created a database,
now to create the table do the following. Copy the script below and name the
file createtable.sql. Then in IB_SQL select the "Execute Script" icon its the
fifth one from the right. Select "Script Commands" then select "Load from file".
Find the createtable.sql file that you just created, it will place the script in
the script window. Or you can just copy the script and paste it in the script
window. Then click the execute button, this will run the script and well create
the table States.
You can now add records by clicking on the
browse icon then click data then click the "Insert row" icon (the green +) then
enter a state abbreviation and the state name. Youcan add as many records as you
would like, just remember to click the "Post Insert" icon after each entry (the
green check mark). Then once your done click the "Commit" icon, its the one that
has the folder with a blue check mark.
To check that the records have been added
click the "Fields" tab then click the "Refresh all Records" icon, its the one
with the double yellow and black arrows. Then click back on the "Data" tab and
it should display all the records in the States table.
createtable.sql
Create Table States
(state_code VARCHAR(2) NOT NULL PRIMARY KEY,
state_name VARCHAR(30) NOT NULL)
Backing up and Restoring Firebird
At some point you will need to backup your database, and
this is a easy process. To backup and restore your database you will be using
GBAK.exe, it resides in your Firebird_1_5\Bin directory. You can use the command
line to backup but I would recommend that you create a batch file and use that
to backup and restore your database. If you use a batch file you can then use Windows Scheduling to backup the database on a regular
basis.
The first thing you need to do is add the Firebird\bin
directory to your autoexec.bat file. To do this in 95x open windows explorer
right click on the autoexec.bat file and click edit, then add the following to the end of the path
file.
C:\WINDOWS;C:\WINDOWS\COMMAND;C:\j2sdk1.4.0_01\BIN;C:\HTTPD\PHP;C:\Progra~1\Firebird\Firebird_1_5\bin
As you can see I have the command, Java sdk, PHP server
along with the Firebird bin. By doing this I can execute any command in the
Firebird\bin directory with out having to either be in that directory or typing out the full path
to the file.
Below is the bat file to backup and restore your database.
Backup.bat
@echo off
gbak c:\Progra~1\Firebird\Firebird_1_5\bin\dbname.gdb D:\Backup\dbname.gbk
-user sysdba -pas pwd -v -y c:\backup.txt
@cls
What the above bat file will do is backup the database
and save it to a zip drive in the Backup folder. It will then write what it did
in backup.txt file in the C: directory, if all went well it will tell you exactly what it did, if not then
it will write the error that it encountered.What the @echo off and @cls does is close
the window once the bat file excutes.
And lastly you can use VB to backup your database by using the Shell command
the code is below, of course you would replace the path name to you own. This
code uses a text box and command button.
Option Explicit Private
Declare Function GetShortPathName Lib "kernel32" _Alias
"GetShortPathNameA" (ByVal lpszLongPathAs String, _ ByVal
lpszShortPath As String, ByVal lBuffer As Long) As Long
Private
Sub cmdBackup_Click()
Dim retVal
Dim appPath As String 'Default backup database folderIf txtDrivePath = "" Then
S = GetShortPath("C:\VB
Programs\Invoice Generator - FB") backupPath = S &
"\invgen.gbk" appPath = "c:\vbprog~1\invoic~1\invgen.gdb "
& backupPath & " -user sysdba -pas pwd -v -y
c:\backup.txt"
retVal = Shell("command.com /c start gbak " & appPath,
0)Else 'User selected
folder S =
GetShortPath(txtDrivePath) If S = "" Then NotTopMost
Backup.hwnd: MsgBox "Please make sure you have typed in the directory path
correctly!" & vbNewLine _ & "C:\Directory
Name or C:\Directory Name\Sub Directory Name": MakeTopMost
Backup.hwnd: txtDrivePath.SetFocus: Exit Sub
backupPath = S & "\invgen.gbk" appPath =
"c:\vbprog~1\invoic~1\invgen.gdb " & backupPath & " -user sysdba -pas
pwd -v -y c:\backup.txt" retVal = Shell("command.com /c
start gbak " & appPath, 0)End If NotTopMost
Backup.hwnd MsgBox "The database has been backed
up" Unload MeEnd SubWhat this code will do
is backup the database and put the information in the backup.txt file.
To restore the database do the following.
Restore.bat
@echo off
rem if you want to over ride the database use the -r switch with out it the
database must not exsit
gbak -create -r -user sysdba -pas pwd d:\Backup\Employee.gbk
c:\Progra~1\Firebird\Firebird_1_5\bin\Employee.gdb -v -y c:\restore.txt
@cls
What this code will do is restore the database to the
specificed folder if the database that you want to restore exsits and you and
you want to over ride it use the -r switch as I did here. If you want to restore a database that doesn't exsit then remove
the -r switch.
Now that you know how to backup and restore a database using GBAK there is a free utility that you can use called
FIBS Scheduler 1.0.7.
FIBS Firebird-Interbase Backup Scheduler 1.0.7
Firebird Drivers
In order for us to connect to the Firebird database
using VB we will need a Provider or ODBC driver. There are several third party drivers out there,
below are just a few. You will notice some of these drivers are free and others are not
but they do give you a trial period to use them. I have also provided the connection
strings just in case you are able to register the other drivers.
Free drivers
ZStyleGroup -Tested
connection string - "Provider =ZStyle IBOLE Provider; Data Source= c:\dbpath\dbname.gdb; UID=username;
Password=pwd"
http://www.zstyle.dp.ua/eng/iboledb_prod.htm
XTG System
connectionstring-"DRIVER={XTGSystemsInterBase6ODBCdriver};DataSource=c:\mypath\mydb.gdb;UID=username;PWD=password
http://www.xtgsystems.com/linux/ofbodbc/download.php
IB Provider
connectionstring-"Provider="LCPI.IBProvider";Location= localhost;Data Source=c:\dbname.gdb", "user=username;"password=pwd"
Free Version of IBProvider
Firebird direct Access to
VBhttps://sourceforge.net/projects/fbdll4vb/
Commerical
drivers
Upscene Productions
http://www.upscene.com/
SIBPROVIDER
http://www.sibprovider.com/en_us/default.asp
Easysoft
http://www.easysoft.com/products/2201/
VB and Firebird our first connection
Now that we have installed the Firebird and ODBC/OLEDB drivers and
tested the system to make sure it works its now time to create our first
application using Firebird and VB. Since we will be using ADO to manipulate the
database I would suggest that if you are not familiar with SQL to check out the
Working with Databases tutorial that I wrote a couple of years back.
Working with Databases
http://home.pacbell.net/cetta/workingwithdatabases.html
So start up VB and the Firebird server and lets get started!
We will be using two different database on
these project, employee.gdb and httplogs.gdb. If you do not already have these
database you can download them at the link below and unzip them in to the
Firebird_1_5\Samples directory.
Employee database
Employee.zipHttplogs
database
Httplogs.zip
We will be connecting to the httplogs
database, so start a new VB project and add a list box and command button and
reference MS ADO 2.5 and ODBC driver IBOLE Library. If you don't have ADO 2.5 on
your computer then select the latest version you have on your system, or you can
go to Microsoft and download the latest version, I belive its
2.7.
Then cut and past the
code below:
FB-Connecting.vbp
Private sub Command1_Click()
Dim cn As New ADODB.Connection
Dim rs As New Recordset
Dim sql As String
cn.ConnectionString = "Provider=ZStyle IBOLE Provider; Data Source =
"_&"c:\ProgramFiles\Firebird\Firebird_1_5\Samples\Httplogs.gdb;UID=sysdba;password= pwd"
cn.Open sql= "Select* From States"
Set rs = cn.Execute(sql)
DoWhile Not rs.EOF
List1.AddItemrs(0) & vbTab & rs(1)
rs.MoveNext
Loop
rs.Close
cn.Close
End Sub
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.
Text1 = txtEStateCode
Text2 =
txtEStateName
Command1 = cmdEdit
Command2 = cmdAdd
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
Adding a record
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.
Client - Setup Installation
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.
Server - Setup Installation
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 News group
ttp://groups.yahoo.com/group/firebird-php/
Firebird WebRing
http://v.webring.com/
Firebird Summary
Since this will be my last post on Firebird
let's do a break down...
1. It's ported to many platforms Windows, Solaris, Linux, MaxOS X, FreeBSD
and more.
2. Offers many ANSI SQL-92 features.
3. offers excellent concurrency, high performance, and powerful language
support for stored procedures and triggers.
4. It has been used in production systems, under a variety of names since
1981.
5. It's scalable can be used as a server or desktop
6. Easy To Manage And Maintain
7. Small footprint - the server is about 1Mb before it starts allocating
dynamic
memory and it does not allocate much.
8. Installs In Minutes
9. Binary Large Objects (BLObs)
10. Maximum number of rows per table: approximately 2 billion.
Maximum size of a table: limited by system resources.
Maximum number of databases per system: limited by system resources.
Maximum number of active users possible per system: limited by system
resources.
Maximum number of tables per database: 64Kb.
Maximum row size (excluding BLOb): 64Kb.
What I found out about Firebird is that it
is easy to install and use. But what was really great was it's short learning
curve. Like I stated in my tutorial "Working with Databases", as long as you
know SQL you can work on just about any database. And other then the syntax
difference in creating stored procedure everything else worked as it should. You
put all of that with the fact that its free, and I know what database I will be
using in the future!
I hope this Hands On Firebird has been
helpful and enjoyable and that you learned a lot because I
did.
SAP DB
SAP DB was the other open source database that I looked at, unfortunately I was not able to get it
working. So instead of providing instructions on installing and running like I did with Firebird
all I can provide you is links. SAP DB open source version has now been taken over by the
makers of MySQL and is being renamed MaxDB.
SAP DB Article
http://www.infoworld.com/article/03/08/07/HNmaxdb_1.html
SAP DB Main site
http://www.sapdb.org/
SAP DB in Microsoft Windows
ttp://www.sapdb.org/7.4/sap_db_nt.htm
Web Tools Installation guide
http://tinyurl.com/lmo2
SAP DB Sample Database
http://www.sapdb.org/7.4/sampledb_tgz.htm
SAP DB PHP and ODBC
http://www.andreas-ditze.de/rcms_141.php
SAP FAQ
http://www.sapdb.org/sap_db_FAQ.htm
http://www.rscom.ru/doc_eng/sapdb/faq.php
http://sapdb.automatix.de/faq-sap-db.html
Other SAP Links
http://sapdb.icf.net/cgi/links.html
Copyright© 2003 Marietta Crockett
Disclaimer
|
|
