DatabaseColumnType issue

[Jordi]

I'm using SQLITE engine of PB.

If I send a request with this query:

Code: Select all

SELECT date FROM table

DatabaseColumnType returns for the first column (0) after DatabaseQuery: 2, this is right because "date" field is a date as its name means.

But with this query:

Code: Select all

SELECT MIN(date) FROM table

DatabaseColumnType returns for the first column (0) after DatabaseQuery: 0, and 0 is an unknown type for PB.

Anyone can help me? I suppose this has a reason, but I have not found it looking in the forum, pb help, sql help.

Thanks!

[pdwyer]

This might help

http://www.sqlite.org/datatype3.html
http://www.sqlite.org/lang_createtable.html

Many types are "supported" but don't exist in actuality, VARCHAR is just TEXT renamed for support reasons. You can query the table itself and see what SQL created it, the type might be just TEXT as there is no dedicated DATE type.

maybe something like that is tripping the PB proc up

[Jordi]

Thanks pdwyer.

Yes, GetDatabaseString runs ok and I can get the date, but the problem is when type of column is not defined on compiling time, when you don't know the query, when query is definied on runtime.

Any idea how get the type easily? Thks.

[pdwyer]

A PB person should probably answer, I think this needs to have a bit of extra logic added for sqllite to get the underlying type

[Jordi]

Thanks Paul.

I hope there is a solution for this issue because I have only one idea to solve this myself, it is parsing the query instance... too much hard for me.

[the.weavster]

Even if you call select * from sqlite_master where type = 'table' and parse the response you wont necessarily get the answer because declaring a datatype in a create table... statement is optional with SQLite.

[Jordi]

Perhaps a "tricky" way to solve by the moment the issue:

Code: Select all

SELECT MIN(date), typeof(MIN(date)) FROM table

With the above code the second column has the type of the first so I have to parse the columns queried between SELECT and FROM and add the same number of columns for type or save the original query to do a previous query getting the types of columns involved in the query, adding typeof() to any field between select and from.

Any better idea?