Thank you very much.

Till now i normally used only the TIBODataset (TIBODatabase,
TIBOQuery, and so on ..)

In my Webapplicationservers written with RTC technolgy (very fast,
stable and usefull, with PHP integrated) i try to integrate a db pool
with IBO and Firebird 1.5.5 or later version. If i create for each
using a TIBODatabase and destroy it after usage (in any thread), it
works ok and stable, but its slow. Each new connect needs time.
Therfore i tried to realize a dbpool, where TIBODatabases are in a
Tlist or dynamic array, and i can reuse existing and valitated
connections (verifyconnection, connectionwaslost).

I think all instances of TIBODatabase use the same DefaultTransaction
or so. Therefore i also tried to create for each TIBODatabase a own
Transaction too ... but also no success.

example:
....................................................................
function TFbDBPool.SetUpDB:TIBODatabase;
begin
CS.Enter; //criticalsection
try
Result:= TIBODatabase.Create(nil);
try
with Result do
begin
Server:= db_server;
Path:= db_path;
CharSet:= db_charset;
Username:= db_UserName;
Password:= db_Password;
Protocol:= cpTCP_IP;
keepconnection:= true;
sqldialect:= db_sqldialect;
AutoCommit:= true;
connect;
xlog('DBPOOL->newConnection');
end;
except
freeandnil(result);
xlog('ERROR-DB-POOL-CreateNewCONNECTION');
raise;
end;
end;
finally
CS.Leave;
end;
end;

function TFbDBPool.GetDBConn:TIBODatabase;
begin
Result:=nil;
CS.Enter;
try
while (myPool.Count > 0) do begin
Result:= myPool.items[myPool.Count-1];
myPool.Delete(myPool.Count-1);
if ((result.VerifyConnection = false) or
(result.ConnectionWasLost = true))
then begin
CloseDBConn(result);
end; //while...
if result = nil then result:= SetupDB;
finally
CS.Leave;
end;
end;

//if in the using of the conn was an error i drop this conn........
procedure TFbDBPool.PutDBConn(conn:TIBODatabase; drop:boolean);
begin
CS.Enter;
try
if drop then begin
conn.ForceDisconnect;
freeandnil(conn);
xlog('DBPOOL->PutDBConn->destroyConnection');
end else mypool.Add(conn);
finally
CS.Leave;
end;
end;

All my tryings ended in a instable version with a lot of TList access
violatons (whether i uses CriticalSections and so on).
Therefore i think TIBODatabase is not designed to use it in a
multithreaded windows service and i give up to realize this.

My next try is now with a pool of TIB_Connections. Better a pair of
creating a TIB_Connection with a TIB_Transaction.

Example:
......................................................................
function TFbDBPool.SetUpDB:TIB_Connection;
begin
CS.Enter;
try
Result:= TIB_Connection.Create(nil);
try
with Result do begin
defaulttransaction.create(nil);
defaulttransaction.ib_connection:= result;
defaulttransaction.autocommit:= true;
Server:= db_server;
Path:= db_path;
CharSet:= db_charset;
Username:= db_UserName;
Password:= db_Password;
result.Protocol:= cpTCP_IP;
keepconnection:= true;
sqldialect:= db_sqldialect;
connect;
end;
except
on E:Exception do begin
xLog('ERROR:DBPOOL:'#13#10+E.Message);
FreeDB(result);
raise;
end;
end;
finally
CS.Leave;
end;
end;

function TFbDBPool.GetDBConn:TIB_Connection;
begin
Result:=nil;
CS.Enter;
try
if length(myPool) > 0 then begin
Result:= myPool[ length(myPool)-1 ];
SetLength(myPool, length(myPool)-1 );
end;
finally
CS.Leave;
end;
if result <> nil then begin
if result.ConnectionWasLost then begin
try
conn.forcedisconnect;
freeandnil(result);
except end;
end else if not result.VerifyConnection then begin
try
conn.forcedisconnect;
freeandnil(result);
except end;
end;
end;
if result = nil then Result:= SetupDB;
end;

procedure TFbDBPool.PutDBConn(conn:TIB_Connection; drop:boolean);
begin
if not drop then
begin
CS.Enter;
try
SetLength(mypool, length(mypool)+1);
mypool[length(mypool)-1]:=conn;
finally
CS.Leave;
end;
end else begin
try
conn.forcedisconnect;
freeandnil(result);
except end;
end;
end;

usage in a thread:

dbo := dbpool.getDBConn;
try
q:= TIBOQuery.create(nil);
try
q.IB_Connection:= dbo;
.....
finally
freeandnil(q);
finally
dbpool.putDBConn(dbo, false)
end;

--> have to test this, not know if it works till now.

i tried also to change to TIB_Query.

Now, i run a lot of incompatibilities in my working source code, like
BLOB handling (fixed, using TBLOBfield now), and now the FieldType thing.

Your SQLType can help, but how i can find out this types?
ftCurrency, ftBCD, e.g.?

With DIBOQuery i could use:

ds:= result.newDataset;
for i:= 0 to q.Fields.Count - 1 do begin
fname:= q.Fields[i].FieldName;
ds.FieldType[fname]:= get_rtc_fieldtype(q.FieldDefs.Items[i].DataType);
ds.FieldSize[fname]:= q.fielddefs.Items[i].Size;
end;

i need it to convert fieldtypes e.g. in my remotefunctons with rtc, like:

const
RTC_DB2FIELD_TYPE: array[TFieldType] of TRtcFieldTypes =
( ft_Unknown, ft_String, ft_Smallint, ft_Integer,
ft_Word,
ft_Boolean, ft_Float, ft_Currency, ft_BCD,
ft_Date, ft_Time, ft_DateTime,
ft_Bytes, ft_VarBytes, ft_AutoInc, ft_Blob,
ft_Memo, ft_Graphic, ft_FmtMemo,
ft_ParadoxOle, ft_DBaseOle, ft_TypedBinary,
ft_Cursor, ft_FixedChar, ft_WideString,
ft_Largeint, ft_ADT, ft_Array, ft_Reference,
ft_DataSet

{$IFNDEF IDE_0}
,ft_OraBlob, ft_OraClob, ft_Variant,
ft_Interface, ft_IDispatch,
ft_Guid
{$IFNDEF IDE_1}
, ft_TimeStamp, ft_FMTBcd
{$ENDIF}
{$ENDIF} );

RTC_FIELD2DB_TYPE: array[TRtcFieldTypes] of TFieldType =
( ftUnknown, ftString, ftSmallint, ftInteger, ftWord,
ftBoolean, ftFloat, ftCurrency, ftBCD, ftDate,
ftTime, ftDateTime,
ftBytes, ftVarBytes, ftAutoInc, ftBlob, ftMemo,
ftGraphic, ftFmtMemo,
ftParadoxOle, ftDBaseOle, ftTypedBinary,
ftCursor, ftFixedChar,

{$IFDEF IDE_1}
ftString, // TDataSet in D4 doesn't really
support WideString
{$ELSE}
ftWideString,
{$ENDIF}

ftLargeint, ftADT, ftArray, ftReference, ftDataSet

{$IFNDEF IDE_1}
,ftOraBlob, ftOraClob, ftVariant, ftInterface,
ftIDispatch,
ftGuid, ftTimeStamp, ftFMTBcd
{$ELSE}
,ftBlob, ftMemo, ftString, ftBlob, ftBlob,
ftString, ftDateTime, ftBcd
{$ENDIF});

I need this very often, therefore and other reasons i used
till now TIBOQuery.

My tests shows that a big advantage from TIB_Query is: it is very
fast. I think more as 10 times faster as TIBOQuery.

Sorry about this very long post.

br
gerhard

--- In IBObjects@yahoogroups.com, Helen Borrie <helebor@...> wrote:
>
> At 12:46 PM 12/01/2008, you wrote:
> >Hi,
> >
> >Wiht a TIBOQuery i can do:
> >
> >q :TIBOQuery
> >fieldtype:= q.FieldDefs.Items[i].DataType
> >
> >how this works with IB_Query?
> >q :TIB_Query
> >fieldtype:= q. ???
>
> The SQLType property of a TIB_Column returns the native SQL type,
which is a smallint represented by a symbol. As you see, the type is
different, depending on whether the column is nullable or not:
>
> (from IB_Header.pas)
> // not nullable nullable ;
> SQL_VARYING = 448; SQL_VARYING_ = 449; // varchar
> SQL_TEXT = 452; SQL_TEXT_ = 453; // char
> SQL_DOUBLE = 480; SQL_DOUBLE_ = 481; //double precision
> SQL_FLOAT = 482; SQL_FLOAT_ = 483; //
> SQL_LONG = 496; SQL_LONG_ = 497; // integer
> SQL_SHORT = 500; SQL_SHORT_ = 501; // smallint
> SQL_DATE = 510; SQL_DATE_ = 511; // dialect 1 date
> SQL_BLOB = 520; SQL_BLOB_ = 521; // blob
> SQL_D_FLOAT = 530; SQL_D_FLOAT_ = 531; // float
> SQL_ARRAY = 540; SQL_ARRAY_ = 541; // array
> SQL_QUAD = 550; SQL_QUAD_ = 551; // type with internal
uses, e.g. blob_ids
> SQL_TIMESTAMP = 510; SQL_TIMESTAMP_ = 511; // dialect 3 timestamp,
dialect 1 date
> SQL_TYPE_TIME = 560; SQL_TYPE_TIME_ = 561; // time
> SQL_TYPE_DATE = 570; SQL_TYPE_DATE_ = 571; // dialect 3 date
> SQL_INT64 = 580; SQL_INT64_ = 581; // bigint
> SQL_BOOLEAN = 590; SQL_BOOLEAN_ = 591; // not used
>
> But in practice you rarely need to use these, as TIB_Column has
other properties by which you can determine important attributes
associated with the column's data type, e.g. IsNumeric, as well as the
As... methods for reading and writing operations specific to data type.
>
> Helen
>