Apache OpenOffice Community Forum

I hope I am in the right place as I have googled extensively and experimented a lot but I am beginning to thing this might be a bug; before yelling bug I thought I'd ask here.

Scene:

SQLite3 database as external source
A number of views exist in the db
the odb file doesn't seem to recognise the data type of the columns in the view even when CAST

Example:

in the SQLite db I have:

CREATE VIEW "J13" AS
SELECT
CAST (avg("U") * 7.0 AS NUMERIC) as "u",
CAST (count(*) AS INT) as "days"
FROM dr
WHERE Date >= "2010-01-13"

but for reasons I cannot work out in the odb the number columns appear as Field Type Text [wvarchar] which they clearly are not. I am not able to change the field type to anything sensible (anything numeric would probably do).

I can work around this using macros, etc. when I drag the data into calc but don't think I should have to.

Before anyone starts on the "SQLite is loose on definition" thing: look at the CASTs, they are clear, it is the odb that is getting it wrong.

Hopefully someone can help or provide further information before I yell bug.

TIA

following up own post with a workaround rather than a solution; a mod is free to change the Subject: to match.

What I've ended up doing is creating a query in the OOo .odb for each of my views in the underlying SQLite database and then setting the columns to the appropriate formats (with some columns a * 1.0 has been necessary to allow the format to be set to numeric and the .odb still insists on seeing ISO-8601 dates as text fields for reasons I can't fathom).

I'm not particularly happy that I'm having to do this from a neatness / redundancy POV but since the views are essentially condensed there isn't much overhead as the SQLite db is still doing the real work.

I leave this posting here in case anyone else comes across a similar conundrum.

I faced similar data type to column format transformation issues in Base and Calc, when using SQLite3 via an ODBC connection. The data type seems to be recognized and represented just fine in Base, but when viewing/editing the Format of a column in Base, all columns were defined as Category "Text", Format / "@" (instead of integer, timestamp, etc...). Causing issues in Calc where columns with numbers can't be used in formulas, graphs, etc, because they are internally recognized as "Text".

In my case, problem was solved by changing the "BigInt" ODBC connection property from "Yes" to "No".

Wm_o_o_o wrote:...and the .odb still insists on seeing ISO-8601 dates as text fields for reasons I can't fathom

SQLite is severely limited in terms of 'data types.'

"SQLite does not have a storage class set aside for storing dates and/or times. Instead, the built-in Date And Time Functions of SQLite are capable of storing dates and times as TEXT, REAL, or INTEGER values:

• TEXT as ISO8601 strings ("YYYY-MM-DD HH:MM:SS.SSS").
  REAL as Julian day numbers, the number of days since noon in Greenwich on November 24, 4714 B.C. according to the proleptic Gregorian calendar.
  INTEGER as Unix Time, the number of seconds since 1970-01-01 00:00:00 UTC."

So Base (actually your ODBC driver) is properly interpreting the dates as 'text' fields in your case. This should be sufficient for simple sorting and/or searches on the ISO8601 strings. But if you're interested in utilizing more advanced SQL functions such as 'date math,' or utilizing Base Forms (Calendar Control) to input Dates, then I would recommend migrating your data to a more full-featured database engine.