[yunqa.de] Re: Strange data types returned by DISQLiteUNIDirQuery ?
- From: Delphi Inspiration <delphi@xxxxxxxx>
- To: yunqa@xxxxxxxxxxxxx
- Date: Mon, 22 Oct 2012 11:32:04 +0200
On 04.10.2012 13:13, H M wrote:
> I use DISQLiteDataBase--> DISQLiteUNIDirQuery --> DataSetProvider -->
> ClientDataSet --> DataSource --> DBgrid
>
> I store UK money amounts as integers, ie the number of pennies, and then
> convert to pounds and pence in the SQL using a calculation (eg
> 'TheAmount *1.0/100'). Then I use a dbgrid to show the amount, using the
> ClientDataSet field editor to set the DisplayFormat to £#,##0.00
>
> The problem is that DISQLiteUNIDirQuery does not seem consistant in the
> data types its returns for similar SQL, so sometimes the money amount
> comes back as a TDSQLite3WideStringField type and so there is no
> DisplayFormat property available in the ClientDataSet and the amount
> displays incorrectly.
We have worked on the problem via private e-mail. I had access to some
example data and this additional user information:
> I added all the fields in the clientdataset (via Add All ), simply to
> see what types the queries were returning for the calculated money
> fields of OrderValue ,invoicedTotal, StillToBeInvoiced, OrderItemPrice
> and OrderLineTotal. They all have similar calculations and should all
> return float. Therfore the clientdataset should have a displayformat.
>
> What I get returned is
>
> OrderValue :TWideStringField
> invoicedTotal:TWideStringField
> StillToBeInvoiced:TWideStringField
> OrderItemPrice :TFloatField
> OrderLineTotal :TFloatField
>
> So I am unable to format the first three fields as money
At last, here is the solution:
The problem arises because your query uses a parameter which is unbound
at design time. Hence the query does not return any result rows and
TDISQLite3UniDirQuery is unable to determine the column types.
This is due to the fact that SQLite does not assign fixed types to its
table columns. Instead, each cell may have its own, different data type.
Thus TDISQLite3UniDirQuery must sometimes "guess" a column's data type
if it cannot base it on real values.
We have two options here:
1st Option:
When designing your forms, always use a data / query combination which
returns at least a single row of data with the right data type. Please
note that NULL values won't work, the actual type values are
indispensable. Unfortunately, this is not always possible.
2nd Option:
Add all fields and edit the form's text source. Press ALT+F12 to enter
form editing mode. Then locate the respective field definitions and
change them from TDISQLite3WideStringField to TFloatField.
Unfortunately, this does not work if you do it for the TClientDataSet
only. You must also add the same fields and field types to the base
TDISQLite3UniDirQuery. In fact, it should be sufficient to add fields to
TDISQLite3UniDirQuery only and not to TClientDataSet because
TClientDataSet will pick them from there at run-time anyway.
After you've applied all changes ALT+F12 to return to the form's design
vies. The Object Inspector should now display all TFloatField properties
including Currency and EditFormat.
On compilation, Delphi might warn you about field type incompatibilities
and ask to correct them. I allowed the change and all worked well (with
Delphi XE3).
Ralf
_______________________________________________
Delphi Inspiration mailing list
yunqa@xxxxxxxxxxxxx
//www.freelists.org/list/yunqa
Other related posts:
