[libreoffice-users] Re: Update SQL does nothing using "Run SQL directly" button

Am 04.10.2012 01:27, David S. Crampton wrote:
> Register1a is a table with field "Trans-Date" of type TIMESTAMP. Field
> "Trans-Date-Year" is either TIMESTAMP or INTERGER. I have tried with both.
>
> Update "Register1a" Set "Register1a.Trans-Date-Year" =
> YEAR("Register1a.Trans-Date");
>
> Looks like is should be a very simple SQL statement. It does absolutely
> nothing.
>
> The statement won't run with the F5 key (the "run" button). It gives "The
> given command is not a SELECT statement".
>
> I'm trying to apply SQL learned elsewhere, mostly in MicroBloat Access, and
> I'm getting nowhere.
>
> Help will be appreciated.
>
> David

Hello,

This is fundamental. It is a shame that this mailing list is unable to
answer this simple question properly and in depth after all the lengthy
topics on Base and Base documentation.

menu:Edit>"Run SQL directly" (or the SQL toggle button on the tool bar)
is the equivalent of the "pass-through query" in MS Access. The normal
operation mode is a "parsed query" where Base handles the query string.

Whenever you want to use backend specific functions (e.g. MySQL
GROUP_CONCAT), the specific SQL syntax of the backend or one of the many
things that are not (properly) implemented in Base, you can mark the
query as "direct SQL". Base will ignore the query string and pass it
over to the underlying database engine waiting for a record set or some
error message in return.

A frequent issue with HSQLDB is the UNION statement which requires
direct mode:
> SELECT "Date", "Text", "Number" FROM "Table A"
> UNION ALL
> SELECT "Date", "Text", "Number" FROM "Table B"

Direct SQL is not the solution to all problems.
-- The returned record set is always read-only.
-- Parameter queries and nested queries are Base features. No backend
can deal with that.
-- Pairs of forms and subforms require two parsed queries, otherwise the
subform ignores the binding to its parent.

Hope this helps,
A.S.


-- 
For unsubscribe instructions e-mail to: users+help@global.libreoffice.org
Problems? http://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: http://wiki.documentfoundation.org/Netiquette
List archive: http://listarchives.libreoffice.org/global/users/
All messages sent to this list will be publicly archived and cannot be deleted