problem data type COUNT SQLSERVER

[erik78]

Hello,

I'm a software engineer and my company want an application that must be work correctly on Oracle and SQLServer databases (with total transparency).

For this reason, My company buy our DBExpress products for Oracle and SQL Server (dbexpoda.dll / dbexpsda.dll).

In some cases, the applicattion need to create a calculated data field on TSQLClientDataSet, and then is obligatory to create the all fields on the dataset with the respective type.

The problem is when create a field to store the result for a COUNT clausule, the field is type ftFloat when using Oracle database and is type ftInteger when using SQL Server database. Is possible to resolve this problem, and that the type will be the same?

I’m sending a sample where you can see how to create the field and how to send the query.

function CreateFieldToDataSet (DataSet: TDataSet; Name: string; DataCalc: TFieldKind; FieldType: TFieldType): TField;
var
Field: TField;

begin
Field := nil;
case FieldType of
ftInteger: Field := TIntegerField.create(nil);
ftFloat: Field := TFloatField.Create(nil);
end;

Field.FieldName := Name;
Field.FieldKind := DataCalc;
Field.SetFieldType(FieldType);
Field.DataSet := DataSet;
Result := Field;

end;

The sentence to get the field value (over TSQLClientDataSet) is:

SELECT COUNT(*) AS TOTAL FROM TEST

Thanks

[Antaeus]

Try to set the IntegerPrecision connection option for the DbxOda driver to 38. For more information refer to the Readme.html file in the DbxOda installation directory.

[erik78]

Try to set the IntegerPrecision connection option for the DbxOda driver to 38. For more information refer to the Readme.html file in the DbxOda installation directory.

Hello to everybody,

I check this solution, but the problem isn't resolve it.

Because, on this solution, the driver of Oracle still retrieves COUNT as float. And, on SQLSERVER, the returned type is Integer.
I want to solution that or SQLSEVER return float type on COUNT clausule or ORACLE return integer type on COUNT clausule.

[Antaeus]

Please, describe how you perform the setup of this option. Also specify the exact version of your IDE and the exact version of DbxOda.

[erik78]

Please, describe how you perform the setup of this option. Also specify the exact version of your IDE and the exact version of DbxOda.

Hello,

the exact versions are there:

- IDE: Delphi 7.0 (Build 4.453)
- Dbexpoda.dll: 2.50.5.0

And the exact setup that I use is this:

FSQLConnection.ConnectionName := 'Oracle Net (Core Lab)';
FSQLConnection.DriverName := 'Oracle Net (Core Lab)';
FSQLConnection.GetDriverFunc := 'getSQLDriverORANET';
FSQLConnection.LibraryName := 'dbexpoda.dll';
FSQLConnection.VendorLib := 'dbexpoda.dll';
FSQLConnection.Params.Add('BlobSize=-1');
FSQLConnection.Params.Add('LocaleCode=0000');
FSQLConnection.Params.Add('EnableBCD=False');
FSQLConnection.Params.Add('Oracle TransIsolation=ReadCommited');
FSQLConnection.Params.Add('DataBase=' + ADBConnectionString);
FSQLConnection.Params.Add('User_Name=' + ADBUser);
FSQLConnection.Params.Add('Password=' + ADBPassword);
FSQLConnection.Params.Add('IntegerPrecision=38');

[Antaeus]

You should set the IntegerPrecision option value after connect in this way:

Code: Select all

const
  coIntegerPrecision = TSQLConnectionOption(205); //integer
. . .
  FSQLConnection.SQLConnection.SetOption(coIntegerPrecision, Integer(10));

[erik78]

You should set the IntegerPrecision option value after connect in this way:

Code: Select all

const
  coIntegerPrecision = TSQLConnectionOption(205); //integer
. . .
  FSQLConnection.SQLConnection.SetOption(coIntegerPrecision, Integer(10));

Hello,

I use this line of code in my selects, but the is not resove it. The returned type is float. I need a soltution that the returned type will be integer/number.

Thanks.

[Plash]

To create correct fields at design-time you should use TCRSQLConnection component instead of TSQLConnection component. Add parameter 'IntegerPrecision=38' to Params property of TCRSQLConnection at design-time.

[erik78]

To create correct fields at design-time you should use TCRSQLConnection component instead of TSQLConnection component. Add parameter 'IntegerPrecision=38' to Params property of TCRSQLConnection at design-time.

It’s already a TCRSQLConnection. I’m creating the object as follows:
FSQLConnection := TCRSQLConnection.Create(nil);

[Plash]

Please check what type of field is created at design-time using Field Editor if you place TCRSQLConnection component on the form, add the IntegerPrecision parameter, and then add a field for COUNT().