Source language: Translate to:

SQLite and DBGrid sorting *Solved*

Questions about our Advanced Database plug-in

Moderator: Neosoft Support

SQLite and DBGrid sorting *Solved*

Postby ebear » Tue Feb 03, 2015 6:04 pm

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
Last edited by ebear on Thu Feb 05, 2015 6:00 pm, edited 1 time in total.
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: SQLite and DBGrid sorting

Postby Neosoft Support » Wed Feb 04, 2015 11:52 am

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: SQLite and DBGrid sorting

Postby ebear » Wed Feb 04, 2015 12:07 pm

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
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic

Re: SQLite and DBGrid sorting

Postby Neosoft Support » Wed Feb 04, 2015 12:42 pm

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.
NeoSoft Support
Neosoft Support
NeoSoft Team
 
Posts: 5602
Joined: Thu Mar 31, 2005 10:48 pm
Location: Oregon, USA

Re: SQLite and DBGrid sorting

Postby Rasl » Thu Feb 05, 2015 4:02 pm

You can use fields as varchar , text , nvarchar and char in sqlite

salu2
Rasl
 
Posts: 129
Joined: Sat Apr 02, 2005 8:25 am
Location: Buenos Aires, Argentina

Re: SQLite and DBGrid sorting *SOLVED*

Postby ebear » Thu Feb 05, 2015 5:59 pm

Hello Rasl,

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

Great...

Thanks and best regards,

Eric Beerhorst
http://www.lunarsoftware.eu - Lunar Software ERP database Development. Manufacturing processes, Document management, QMS, Stock management. Standalone solutions or connected to Helios Orange and other ERP systems.
User avatar
ebear
 
Posts: 270
Joined: Wed Jan 18, 2012 3:12 am
Location: Vrchlabi - Czech republic


Return to NeoBookDBPro

Who is online

Users browsing this forum: No registered users and 1 guest

cron