Page 1 of 1

SQLite and DBGrid sorting *Solved*

PostPosted: Tue Feb 03, 2015 6:04 pm
by ebear
Hello all,

I have a problem with sorting with clicking on a header column of a DBGrid with a SQLite table, hopefully I'm doing something wrong and it isn't a limitation. Otherwise all my efforts the last few days where are garbage...

Okay in my SQLite table I have only INTEGER and TEXT field types. INTEGER columns are sorted normally as I was used in all my MS-SQL apps, but when I click on a TEXT column header to sort I get the failure:
"Cannot sort on a binary or special field"

Anyone the same problem and hopefully an solution????

Best regards,

Eric

Re: SQLite and DBGrid sorting

PostPosted: Wed Feb 04, 2015 11:52 am
by Neosoft Support
There are different types of text fields and they work differently depending on which database engine you're using. By Text field it sounds like you're using a memo, which is actually a blob field and cannot be sorted by any database engine (as far as I know). In order to sort text you will need to change the field type to Char.

Re: SQLite and DBGrid sorting

PostPosted: Wed Feb 04, 2015 12:07 pm
by ebear
Hello Dave,

The problem is that SQLite has only the flowing datatypes available:

NULL. The value is a NULL value.
INTEGER. The value is a signed integer, stored in 1, 2, 3, 4, 6, or 8 bytes depending on the magnitude of the value.
REAL. The value is a floating point value, stored as an 8-byte IEEE floating point number.
TEXT. The value is a text string, stored using the database encoding (UTF-8, UTF-16BE or UTF-16LE).
BLOB. The value is a blob of data, stored exactly as it was input.

From the I use on the moment only the INTEGER and the TEXT types. For INTEGER is sorting possible trough the Grid header, but at TEXT the "Cannot sort on a binary or special field" failure is showed.
Normally I use server based (MS-SQL or MySQL) databases, but for this project I need a server less database type. I don't want to use Access in this case because of the multi user aspect.
Otherwise everything works fine with SQLite...

Best regards,

Eric

Re: SQLite and DBGrid sorting

PostPosted: Wed Feb 04, 2015 12:42 pm
by Neosoft Support
You could try sorting with dbpExecSQL and see if it makes any difference. For example:

dbpExecSQL "database" "SELECT * FROM tablename ORDER BY fieldname ASC" ""

If that doesn't work then it may be an incompatibility between ADO/ODBC and SQLite.

Re: SQLite and DBGrid sorting

PostPosted: Thu Feb 05, 2015 4:02 pm
by Rasl
You can use fields as varchar , text , nvarchar and char in sqlite

salu2

Re: SQLite and DBGrid sorting *SOLVED*

PostPosted: Thu Feb 05, 2015 5:59 pm
by ebear
Hello Rasl,

You are complete right. I changed TEXT to CHAR and everything seems to work....

Great...

Thanks and best regards,

Eric Beerhorst