I was much in the same situation you are. My answer might seem like a short
tutorial, but it's really more like an autobiography of pain, misery,
denial, and then acceptance :) I hope it helps you

First off, if your project is big (more than 100K record inserts, updates
per day or even week) then abandon all hope of building it with .Net and
just check out the final paragraph. If it is not, then count your self lucky
and what i wrote below will probably help you out just fine.

I had no problem using the ODBC drivers for viewing the database, although I
required the OLE DB Provider from SIBProvider to work with the SQL code. To
my knowledge, there is no way to create, open, begin, commit (or rollback),
close, and then destroy a connection/transaction to Firebird without OLE DB.

As for executing the SQL code, I myself just created a connection to the
Firebird DB using a ADODB Connection object. Opened and Began a
transaction, executed SQL statements, then commited and closed the
transaction. Afterwards, I always "destroyed" the connection object by
setting it to NOTHING. I am sure you are familiar with that.

Whether or not you wish to use Stored Procedures or the SQL statements
themselves is up to you and really depends on the project itself. In my
case I did, and still do, have the vasy majority of the code running on the
server. So I rely on Stored Procedures to do most of my work. To call a
stored procedure from VB you just execute the SQL statement to call the
procedure. Ex. "EXECUTE PROCEDURE PROC_A(45,15,'ALPHA');". The statement
itself is a STRING being passed, so you can construct the parameters quite
easily. It depends on the OLE DB provider (SIBProvider did not initally
provide support), but you can also create a parameterized SP object and
create the parameters and assign them their values directly. If you have a
selectable SP (one that returns rows), you can just as easily assign an
ADODB recordset the results that SP either by SQL statement, or SP object.
Hope that provides you the "nudge" you needed. Additionally, all the other
statements will work as well, like INSERT, DELETE, ALTER, CREATE, etc. All
things considered, working with Firebird via OLE DB is not that different
from working with any other transactional database via OLE DB. That is what
they intended I beleive.

This you might not want to hear. You are at a SEVERE disadvantage with VB,
.Net or basically anything other than C++. In my experience so far, you
should just give up now and start coding your project in C++ Builder. I had
to largely abandon my own VB project since I could never meet the
performance requirements. As it is I have to create SQL scripts and run
them directly on the server. so my frontend is limited to management
functions and cannot perform or automate the work it was intended for yet.
C++ has native objects built in that are far far faster and easier than any
OLE DB provider could ever be. You will have to accept, that through ANY
OLE DB Provider, you WILL be limited to a ceiling of around 250 "operations"
per second. By operation, I mean any kind of SQL statement, or execution of
a SP, regardless of how many per each transaction that you perform. I tried
for over a year to break that performance barrier and never succeeded by any
method at all. If your DB is small and inserts and updates are few, then
.NET may be okay. If you are doing 100K plus inserts per day or per week,
get ready for PAIN. The bottom line is that through any other than C++ and
IBO/IBX objects, there are too many damned layers to go through.

P.S - I have never written .Net code yet, but if still want some VB 6
examples of ADODB code, I will be happy to give it you if ask.

-----Original Message-----
From: Will Freeman [mailto:wfreeman@...]
Sent: Sunday, February 27, 2005 5:52 PM
To: firebird-support@yahoogroups.com
Subject: [firebird-support] Really Newbie Question





Hello All,
I am a long time programmer, but not a database programmer. I am
using Firebird and have had good success acquiring data with standard
SELECT queries in .NET using the ODBC drivers and the .NET provider.
But I wanted to really manage Firebird databases so I bought "The
Firebird Book" (Great Book!!) and "Mastering SQL". But am at a
complete loss as to how to implement the SQL code inside a RAD
environment. Should I be storing the code as stored procedures and
calling those, implementing the code directly using something like a
CommandBuilder, or something else??? I am not looking for anyone to
give me a tutorial, just a swift nudge in the right direction, so I am
not spinning my wheels. Any links to examples of implementing SQL
code this way (or a good book on the topic), would be greatly appreciated.

Thanks, Will.









Yahoo! Groups Links