[Kexi] SQL-Queries in kexi

[Jarosław Staniek]

Christian Thanner said the following, On 2006-11-05 19:41:

> Based on the cars-example I have tried very simple queries just to see 
> how kexi works, although not very successfully so far. I have tried for 
> example:
> 
> SELECT owner, model FROM cars ORDER BY owner - the parser tells me, that 
> it's an incorrect query with a syntax error near "BY".

Christian, thanks for trying Kexi. This ORDER query works for Kexi 1.1.0 or newer.

> SELECT tbl_artikel.d_artikel, tbl_costs.amount FROM tbl_costs, 
> tbl_artikel WHERE tbl_artikel.c_artikel = tbl_costs.c_artikel GROUP BY 
> tbl_artikel.d_artikel - which tells me, that there is a syntax error 
> near "GROUP", which I don't really understand.

The syntax looks of course correctly. GROUP BY does not currently work in Kexi 
GUI. So, any such case where Kexi does not support a given SQL feature is be 
reported to users as "incorrect", even while it's correct SQL. Sorry but the 
application cannot predict something is correct but unimplemented :)

Not having GROUP BY also implies totals are not supported.
GROUP BY support is a must-have feature for 2.0 version planned for next year.

> and I don't know where else to look, which is why I'm 
> hoping to find some answers here.

This mailing list is right place for you. Consider subscribing here
  http://kexi-project.org/wiki/wikiview/index.php?Support#Mailing_Lists

> I would be really thankful for any information on specialities about 
> queries (with SQL - although no general SQL tutorials) in kexi.

This is missibg part of the Kexi Handbook indeed, however so far (1.1.x) the 
supported queries are of type SELECT (i.e. no DML/DDL) with ORDER BY and WHERE 
support. The WHERE should be used instead of JOINS for now until JOIN is 
supported.

The news (soon to be announced for the forthcoming 1.1.1 version) are 
parametrized queries (like in MS Access). For example this query will display 
  an "Enter name" window asking you for a name and then will display all 
persons for this name:

SELECT name, surname FROM persons WHERE name = [Enter name]

Of course, if anyone is determined enough, it's possible to use native 
queriries outside of Kexi, either with .kexi files (ksqlite shell command) or 
MySQL/PostgreSQL shells. Note that any modification (e.g. adding a column to a 
table) will not be visible by Kexi though.

-- 
regards / pozdrawiam, Jaroslaw Staniek
  Sponsored by OpenOffice Polska (http://www.openoffice.com.pl/en) to work on
  Kexi & KOffice: http://www.kexi-project.org, http://www.koffice.org
  KDE3 & KDE4 Libraries for MS Windows: http://kdelibs.com, http://www.kde.org