I have a view in SQLite that was used as the basis for a report. The view in SQL is defined as:
select yr,....
FROM
(
select region, strftime('%Y',next_wday) yr
from v_email_dates
)
and the Clarion file is defined as:
annual_summary
FILE,DRIVER('SQLite2'),OWNER(GLO:database_file),NAME('annual_summary'),PRE(ann)
ix_rn KEY(-ann:yr,ann:region),NOCASE
record RECORD
yr SHORT
...
END
END
With the Clarion SQLite driver this did not create a problem, but the SQLite2 driver looks at the yr column in sql (text) and the Clarion column (short) and throws an error:
SELECT ...,A.YR FROM annual_summary A WHERE (A.YR > ?1) OR (A.YR = ?1 AND A.REGION <= ?2 COLLATE NOCASE) ORDER BY A.YR ASC,A.REGION COLLATE NOCASE DESC LIMIT 30 OFFSET ?3 ;
[SQL ]23 rows, 16 columns in result set
[CLAR]SETERROR :133 Unknown Error Posted : 133
[CLAR]SETFILEERROR :133 Field: ANN:YR SQL Type: SQLITE_TEXT(3) Clarion Type: SHORT(2) value: 2021[annual_summary]
It was easy to fix once I looked at the trace file (the error thrown by the program: Record not found was not that useful) – I just threw CAST(… as integer) around the strftime(). However, refusing to convert ‘2021’ to 2021 (or vice versa?) is not very Clarion-like. Not sure if it is only a problem because YR is a bound parameter in the query.
Jon