Subject Re: Beginner questions: from access to firebird (using vb and odbc driver)
Author Jeff Lynn

I has just started using FB only a couple of weeks via C/C++ API, so
may be I can share some of my learning with you to ease the learning.
I didn't use VB but I think the concept is similar.

1) You have to attach to the database, in C API,
isc_attach_database(...) will do the trick. Be aware that if you are
using embedded database, the standard OS specific database file name
(with full path) is required. If you are using the client/server
model, then you have to provide "localhost:C:\aPath\dbfilename" or

2) By default, FB database use default userId "SYSDBA" and password
"masterkey", so you have to define a DPB to define the connection
parameter for userId and password, to be used in the
isc_expand_dbp(...) api call prior to calling the

3) Then you need to start a transaction using the
isc_start_transaction(...) call.

4) Next depends on whether you want to execute a simnple SQL that does
not required any parameter marker or not. For non parameter marker
stmt, say you dynamically construct you SQL on the fly and only
execute it once, they you can use the isc_dsql_execute_immediate(...)
or its variant. You can still use this execute immediate with
parameter marker, provided you construct your sqlda block.

5) Then you can commit the trans using isc_commit_transaction(...) call,
followed by freeing the sqlda, if allocated, and the database handle
resulted from the attach database. A word of caution if you use
sqlda, you must make a copy of the allocated memory to be used to free
them later on because after passing sqlda to any of the apis that use
it, the address will be modified such that free the sqlda using the
used pointer (in C/C++, I am not sure about in VB), will crash you
program due to memory access exception.

6) Or else you can allocate a statment, using the
isc_dsql_allocate_statement(...), then prepare it using the
isc_dsql_prepare(...) api before you can use the isc_dsql_execute(...)
api to execute.

7) You can use either input sqlda or output sqlda or both in these stmt.

8) You can use isc_dsql_sql_info(...) and walk the returned clustered
info to determine affected row count.

9) There is no explicit result set, just like any non-object oriented
API interface, but you can directly call the isc_dsql_fetch(...) to
iterate thru the implicit result set. Here you will fine the use of
output sqlda much more convenient to extract attribute for each
fetched row. You also have to take care of date, time or timestamp
datatype column using the isc_decode_timestamp(...) and its variant to
map the FB date/time/timestamp encoding into you native programming
language datatype.

The overall API architecture is different from other database you may
used to, but that is a fact of life that products evolve over time
that the architecture and implementation that no two databases are
alike unless the developers consciously make it so, which is a big
effort by itself.

So good luck and read up all the doc/books you can find, play with
shipped examples, if in doubt, post a question to this forum.
Everyone in this forum are very nice and do not laugh at "stupid

So take care!


--- In, Adriano <fadrianoc@...> wrote:
> Waiting for Helen's book,
> i've some very elementary questions (sorry for that).
> I've developed some programs in visual basic 6.x and use access
> Now i'd like to migrate to firebird.
> I've pass all my database structure and data into a firebird 2.x
> In vb - access i use to:
> 'Set a new connection (is it necessary also with fb, isn't it?)
> Set cnDB = New ADODB.Connection
> ' Set the connection string
> 'cnDB.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & PercorsoDatabase & ";"
> And with fb i use
> cnStudioLexDB.ConnectionString = "DRIVER=Firebird/InterBase(r)
> driver;UID=SYSDBA;PWD=masterkey;DBNAME=C:\TEST.GDB"
> 'Open the database (is also necessary with FB isn't it ?)
> cnDB.Open
> questions:
> 1) can i leave the database connection open through all the program
> process and close at the end of my program ?
> 2) the recordset: i've to define them as i use to do with access ?
> Public rsTable As ADODB.Recordset
> 3) I need to open recordset ?
> rsTable.Open "SELECT Utenti.* FROM Utenti WHERE Utenti.Utente = '" +
> UtenteSel + "'"
> 4) The above query how can translate into fb syntax ?
> 5) how i can retreive the value for example of a field (field_text)
> contained in a table (table_demo) ?
> in vb - access i use:
> Dim test as string
> test = rsTable_demo("field_text")
> thanks
> adriano