ZEOS & Oracle

[Mike.Cornflake]

G'day,

Not sure where to post this question.  I'm using ZEOS lib 7.1.0-alfa, Laz 1.0/FPC 2.6.0, Win XP 64 bit.  However, I've been seeing this for at least the past two years on both 32bit and 64 bit machines.  First saw it with ZEOS lib 6.6.

Connecting to Oracle 11.2.0.1.0

Components in use TZConnection (protocol set to either Oracle or Oracle-9i - no change either way) and TZQuery.

When I fire off the following sql

Code: [Select]

Select Count(*) As "Count"
From RandomTable
If the result is more than around 30, I get a 1E2 in my return value.  Same goes for any sort of calculated field, and when I say calculated, I mean calculated server side, not client side.

My workaround for the past few years has been to write my SQL as

Code: [Select]

Select Count(*) || '' As "Count"
From RandomTable
Sure, I get my value back as a string, but at least I get the correct value. 

Now - I'm not in a position where I can predefine my Query.Fields, and I suspect this is the key reason why I'm getting the return values in scientific notation.  My fields are being dynamically created by TZQuery when execute the SQL.  I suspect if I'd predefined the field as an Integer, it would all work.

This is only really for a hobby app, which is why I've been living with this for so long :-)

Finally, my question:   Has anyone else seen this behaviour, and if so, how did you workaround/resolve?

And another question:  Anyone idea the correct place for me to post this?

[taazz]

1) what is the field type that has the problem?
2) where do you see the scientific notation?

There are a number of ways to by pass that with the simplest being to cast the result of the count(*) to a data type that does not have that problem eg int64.

[Mike.Cornflake]

G'day,

Interesting.  After investigation, the resultant datatype is ftFloat.

I'm seeing the scientific notation in the returned data.   I just display the data in a bog standard TDBDataGrid or TDBMemo.

As for casting as a datatype that doesn't exhibit the problem, that's exactly what my workaround for the past years has been.  The || '' bit in the second SQL basically takes the return value and adds an empty string to the end, converting it to a string. 

And hmm.  After investigation, if I call .AsString on the field, I get the correct value.  It's TDBGrid and TDBMemo that are displaying the data as scientific notation.

My app is basically an SQL Browser, where I let the user type in whatever SQL's they want.  So I'm not in a position where I can easily determine the expected data type for each field.

Ahh, here's a more useful SQL that exhibits the problem without me needing to upload any databases. 

Code: [Select]

Select 10000
From Dual


The code for this would look something like (Excluding the DBGrid and Datasource creation...)

Code: [Select]

Var
  oQuery : TZQuery;
  oConnection : TZConnection;
Begin
  oConnection := TZConnection.Create(Application.Mainform);
  oConnection.Protocol := 'oracle';

  oConnection.User := FSchema; // String values configured by user
  oConnection.Database := FDatabase;
  oConnection.Password := FPassword;

  oConnection.Connected := True;

  oQuery := TZQuery.Create(Application.MainForm);
  oQuery.Connection := oConnection;
  oQuery.ReadOnly := True;

  oQuery.SQL.Text := 'Select 10000 from dual';

  oQuery.Open;

  lblTest.Caption := oQuery.Fields[0].AsString; // Displays 1E4 in a TDBGrid, but this correctly displays 10000

  oQuery.Close;

  oQuery.SQL.Text := 'Select 10000 || '''''' from dual';

  oQuery.Open;

  lblTest.Caption := oQuery.Fields[0].AsString; // Correctly displays 10000 in both DBGrid and the label

  oQuery.Close;

  oQuery.Free;

  oConnection.Connected := False
  oConnection.Free;
End;

So my question becomes easier.  Is there anyway I can force a TDBGrid or TMemo to display ftFloat fields in non-scientific notation?

Many thanks

Mike

[taazz]

set the float column's displayformat property to something along the lines of #,###.###;-#,###.###;
this should solve the problem. you can also set the ongettext event of a tfield so a method that will convert the float to a string avoiding the scientific notation.

[Mike.Cornflake]

G'day,

Excellent, I'll investigate both options.

Many thanks for the assist, much appreciated.

Mike

[Mike.Cornflake]

Came across this while searching the Zeos bug tracker for a different issue.  Seems I wasn't the only one with this issue.  Same solution posted there as recommended here.

http://sourceforge.net/p/zeoslib/tickets/22/