SQLite query issue

[thebert]

Hello,

I'm attempting to query an sqlite database with the sqlite provider and keep getting zero records returned for the table in question. I've tracked the problem down via lovely trial & error AGAIN and have determined that the named field in the sql statement is causing the issue.

Here's a code snippet from the sql statement:

CASE WHEN QUOTE_ID IN (SELECT PARENT_QID FROM QUOTES WHERE PARENT_QID IS NOT NULL) THEN ''Yes'''
WHEN QUOTE_ID NOT IN (SELECT PARENT_QID FROM QUOTES WHERE PARENT_QID IS NOT NULL) THEN ''No'''
END AS HAS_CHILD

The TUniQuery used for this query has the HAS_CHILD field entered into the fields editor, TStringField, fieldKind = fkData, size=3. If I remove HAS_CHILD from the fields editor then the query works but I no longer have access to the HAS_CHILD field which is NOT a valid workaround. The exact same code works correctly with MSSQL 2005 using the ODBC provider. Please confirm this is a bug in unidac.

Terry

[AlexP]

Hello,

We cannot reproduce the problem. Please send us the full query text and the scripts for creating the tables.

[thebert]

sql statements.

create table quotes (quote_id char(8), client_id char(15), parent_qid char(8));
create unique index [quotes.default] on quotes([quote_id]);
insert into quotes (quote_id, client_id) values('00000003', '000000000000001');

query used:

SELECT QUOTE_ID, CLIENT_ID,
CASE WHEN QUOTE_ID IN (SELECT PARENT_QID FROM QUOTES WHERE PARENT_QID IS NOT NULL) THEN "Yes"
WHEN QUOTE_ID NOT IN (SELECT PARENT_QID FROM QUOTES WHERE PARENT_QID IS NOT NULL) THEN "No"
END AS HAS_CHILD
FROM QUOTES
WHERE CLIENT_ID = :ClientID

fields editor of TUniQuery:

add quote_id - TStringField, client_id - TStringField, has_child - TStringField.

I created a small stub application to illustrate the error. I can send it to you if you wish. I'm getting an error in the stub application, "Type mismatch for field 'has_child', expecting: String actual: Memo. If I switch has_child from a TStringField to a TMemoField then the query works as expected. The problem with this change is that the query does NOT work with SQL Server. We need UniDac to work with SQL Server, SQLite & Paradox(ODBC) in order to satisfy our needs. If this is not possible then we will be forced to look elsewhere.

Terry

[AlexP]

Hello,

When using the CASE construction,aggregate functions, etc., SQLite returns field type as Unknown, since it cannot define the result field type, therefore we map such fields as TMemoField. To solve the problem, you can use explicit type mapping in your query or use the Data Type Mapping technology in order to explicitly specify the type, to which such fields should be mapped for DataSet

[thebert]

Your Data Type Mapping technology only allows mapping to physical fields in the database. The fields I'm experiencing troubles with are not physically in the database. I have tried casting the result prior to the AS clause without success.
The only success I have had is adding the TMemoField or TStringField definition at runtime for SQLite & MSSQL respectively.

Terry

[AlexP]

Hello,

Data Type Mapping works with all fields, that participate in the query, independently on whether a field is present in the table or not. For correct work of Data Type Mapping in design-time, you should firstly remove all fields, then set mapping rules for the needed fields, and then recreate the fields.