why do numbers go in as string?

[bartvde]

Hi,

I am using a third party component which makes use of Dbx. All parameters seem to go in as strings, even the numbers which results in problems with the decimal separator. The third party component people say they put everything in as doubles to the dbx driver and not as strings.

E.g.:

Begin: user "synfonie", server "ONTW"
UPDATE BIS_LOC SET XMIN= :1 ,YMIN= :2 ,XMAX= :3 ,YMAX= :4 ,WKB_GEOMETRY=empty_blob() WHERE GID= :5 returning WKB_GEOMETRY into :6
:1 (String[16],IN) = '74123665597.0803'
:2 (String[16],IN) = '44591549234.0088'
:3 (String[16],IN) = '74173539785.5933'
:4 (String[15],IN) = '4459692030.0293'
:5 (String[4],IN) = '3458'
:6 (Blob,IN)

Why is this? How to solve it? Thanks in advance.

Bart

[bartvde]

It seems the param datatype they use is ftFMTBcd. Does the dbx driver have problems with this?

[Paul]

What components do you use? Try to use DbxOda in this situation. What is the value of TParam.DataType? If it is ftFMTBcd, please write abuot this to third party component people.

[bartvde]

Hi Paul,

the components I use are called TatukGIS (www.tatukgis.com). But it is highly likely you are unfamiliar with them since it is GIS (Geographic Information Systems).

Bart

[Paul]

Possibly they determined that you used it as ftString

[bartvde]

Hi, this is their reply:

Hi,

This is how we prepare an update statement:

We use parameterized:

SQL_UPDATE_DBX_GEO =
'=:,=:,'
+
'=:,=:,'
+
'=:,'
+
'= '
;
ID_SQL_UPDATE_DBX_GEO = ID_SQL_DELETETABLE + 1
;




Prepare update prepares parameterized SQL Query:



{@@ Return an SQL parametrized update command.
@param _column column name used as a filter
@return SQL command
}
function TGIS_LayerSqlAbstract.prepareUpdateCommand( const _table :
String ;
const _column :
String
) : String ;
var
i : Integer ;
param : String ;
tmp : String ;
begin
param := '' ;
if _table = TableGeometry then begin
param := getCmd( ID_SQL_UPDATE_DBX_GEO ) ;
if isOracle then
tmp := getCmd( ID_SQL_UPDATE_DBX_ORA )
else
tmp := '' ;
end
else begin
for i:=0 to Fields.Count - 1 do begin
if FieldInfo( i ).Deleted then continue ;
if FieldInfo( i ).FileFormat then continue ;

if param '' then
param := param + ',' ;
param := param +
FieldInfo( i ).ExportName + '=:' +
FieldInfo( i ).ExportName ;
end ;
tmp := '' ;
end ;

Result := Format( getCmd( ID_SQL_UPDATE_DBX ),
[_table, param, _column, _column, tmp ]
) ;
end ;


To finally set field value using parameters



{@@ Set a field to the table.
@param _name name of the field
@param _val value of the field
}
procedure TGIS_LayerSqlDbx.sqlTableSetField( const _id : Integer ;
const _name : String ;
const _val : OleVariant
);
begin
case VarType( _val ) of
varBoolean :
begin
try
myTable[ _id ].Params.ParamValues[ _name ] := _val ;
except
if _val then
myTable[ _id ].Params.ParamValues[ _name ] := 'Y'
else
myTable[ _id ].Params.ParamValues[ _name ] := 'N' ;
end ;
end ;
else
myTable[ _id ].Params.ParamValues[ _name ] := _val ;
end ;
end ;




So as you see we do not use string on any moment.



IMHO problem is on DBX architecture: to post parameterized query driver
must to convert float to the string and must know what format of string
is accepted by the server.

[bartvde]

Hi Paul,

it seems the Corelab driver sees the fields as ftFMTBcd, how come?

The TatukGIS code walks over the fields and then checks the datatype.

case myQuery.FieldList.Fields.DataType of

The datatype is ftFMTBcd. This is determined by the Dbx driver or not? How to overcome this?

Bart

[Paul]

We are not familiar with these components. We cannot give support of third-party components.

[bartvde]

Paul,

I think I could write up an example which demostrates this out of the component. Apparently something goes wrong with the field type recognition of the corelab dbx driver.

I could solve the problem by overriding the field type like:

TParam(myParams[0].Add).AssignField( myTable[ 0 ].Fields[ i ] ) ;
// BEN, 24-11-2005, to overcome problems with decimal separators
if (myTable[ 0 ].Fields.DataType = ftFMTBcd) then
myParams[0].DataType := ftFloat;

For the record, this is my Oracle table definition:

CREATE TABLE BIS_LOC
(
GID NUMBER(9) NOT NULL,
XMIN FLOAT(126),
YMIN FLOAT(126),
XMAX FLOAT(126),
YMAX FLOAT(126),
WKB_GEOMETRY BLOB
)

The first 5 fields are recognized as ftFMTBCD by Corelab.

[Paul]

You can configure what DataType to use for NUMBER or FLOAT fields in DbxOda. Please use TCRSQLConnection and set EnableBCD parameter to false. EnableBCD=true is default setting that is compatible with standard Borland driver for Oracle.
For more precise settings please see SmallIntPrecision, IntegerPrecision, FloatPrecision, BCDPrecision parameters in ReadMe.html.