why do numbers go in as string?

why do numbers go in as string?

Postby bartvde » Thu 24 Nov 2005 07:00

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
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

param datatype ftFMTBcd

Postby bartvde » Thu 24 Nov 2005 07:21

It seems the param datatype they use is ftFMTBcd. Does the dbx driver have problems with this?
bartvde
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

Postby Paul » Thu 24 Nov 2005 07:25

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.
Last edited by Paul on Thu 24 Nov 2005 07:28, edited 1 time in total.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

components

Postby bartvde » Thu 24 Nov 2005 07:28

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
bartvde
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

Postby Paul » Thu 24 Nov 2005 07:36

Possibly they determined that you used it as ftString
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

reply from third party

Postby bartvde » Thu 24 Nov 2005 07:47

Hi, this is their reply:

Hi,

This is how we prepare an update statement:

We use parameterized:

SQL_UPDATE_DBX_GEO =
'<#XMIN#>=:<#XMIN#>,<#XMAX#>=:<#XMAX#>,'
+
'<#YMIN#>=:<#YMIN#>,<#YMAX#>=:<#YMAX#>,'
+
'<#SHAPETYPE#>=:<#SHAPETYPE#>,'
+
'<#GEOMETRY#>=<#:GEOMETRY#> '
;
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
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

corelab driver determined ftFMTBcd as fieldtype

Postby bartvde » Thu 24 Nov 2005 08:10

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[i].DataType of

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

Bart
bartvde
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

Postby Paul » Thu 24 Nov 2005 08:14

We are not familiar with these components. We cannot give support of third-party components.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06

fix

Postby bartvde » Thu 24 Nov 2005 09:22

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[i].DataType = ftFMTBcd) then
myParams[0][i].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.
bartvde
 
Posts: 6
Joined: Thu 24 Nov 2005 06:57

Postby Paul » Fri 25 Nov 2005 07:45

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.
Paul
 
Posts: 725
Joined: Thu 28 Oct 2004 14:06


Return to dbExpress driver for Oracle