SQL Syntax UPDATE

users
#1

I'm running LO Version: 6.1.3.2 on OpenSUSE LEAP 15.1 and have run into
a snag with a query I am trying to construct.

The SQL editor is rejecting this UPDATE query that, I believe, obeys
the standard SQL syntax.

UPDATE 'Adressensammlung'.'Adressen' SET 'Adressen'.'Print_flag' = TRUE
WHERE 'Adressen'.`Xmas` = TRUE;

I'm no SQL expert but I cannot for the life of me understand what is
wrong with that command.

Must I understand that LO Base is not supporting the standard SQL
syntax?

Cheers
Harvey

#2

Hi Harvey,

The SQL editor is rejecting this UPDATE query that, I believe, obeys
the standard SQL syntax.

UPDATE 'Adressensammlung'.'Adressen' SET 'Adressen'.'Print_flag' = TRUE
WHERE 'Adressen'.`Xmas` = TRUE;

I'm no SQL expert but I cannot for the life of me understand what is
wrong with that command.

You have to mask tablenames and fieldnames with ", if you are using the
internal databases.

UPDATE "Adressensammlung"."Adressen" SET "Adressen"."Print_flag" = TRUE
WHERE "Adressen"."Xmas" = TRUE;

Could be you are using MySQL/MariaDB (I see there is a database-name in
the code). So you could also mask databasename, tablename and fieldname
with `.

UPDATE `Adressensammlung`.`Adressen` SET `Adressen`.`Print_flag` = TRUE
WHERE `Adressen`.`Xmas` = TRUE;

Everything, which is masked with ', will be detected as text, not as a
name of the database, table or field.

Regards

Robert

#3

Hallo Robert,

nice to hear from you again!

even with
UPDATE "Adressensammlung"."Adressen" SET "Adressen"."print_flag" = TRUE
WHERE "Adressen"."Xmas" = TRUE;

I'm getting "Syntax error, unexpected $end, expecting BETWEEN or IN or
SQL_TOKEN_LIKE"

Or with
UPDATE "Adressensammlung"."Adressen" SET "Print_flag" = FALSE;

I'm getting "Can not issue data manipulation statements with
executeQuery()."

Cheers
Harvey

#4

Hi Harvey,

I'm getting "Can not issue data manipulation statements with
executeQuery()."

Where do you start the update?

Tools > SQL?

Or is there a macro working? Then you have to run executeUpdate() instead.

Which database-connection do you use?

Regards

Robert

#5

Hallo Robert,

I am not using macros (yet...hmm). I create the queries with the GUI in
SQL view.
I execute using the 'Run Query (F5)' function from the editor. The SQL
text is not allowed to be stored with the syntax error in the one case.
In the case of the shorter command, the SQL text is stored as a query
and, it seems, could be run from the GUI, if it weren't for the
executeUpdate() error.

The database itself is backend mariadb connectect via JDBC.

Cheers
Harvey

#6

Hi Harvey,

I am not using macros (yet...hmm). I create the queries with the GUI in
SQL view.
I execute using the 'Run Query (F5)' function from the editor. The SQL
text is not allowed to be stored with the syntax error in the one case.
In the case of the shorter command, the SQL text is stored as a query
and, it seems, could be run from the GUI, if it weren't for the
executeUpdate() error.

The query-editor will only work with queries, not with updates or
inserts. You have to start the command through
Tools > SQL
in the Base-file.

The database itself is backend mariadb connectect via JDBC.

So you have to use
UPDATE `Adressensammlung`.`Adressen` SET `Adressen`.`Print_flag` = TRUE
WHERE `Adressen`.`Xmas` = TRUE;

Regards

Robert

#7

Hi Robert,

Thanks a lot, that works fine!

A bit disappointing though. Is there no possibility for storing those
kinds of SQL statements, so that they could be used again?
I have a few in mind, and it seems a bit primitive to have to create a
clipboard type of text file for that kind of thing.

Cheers
Harvey

#8

Hi Harvey,

A bit disappointing though. Is there no possibility for storing those
kinds of SQL statements, so that they could be used again?
I have a few in mind, and it seems a bit primitive to have to create a
clipboard type of text file for that kind of thing.

As yet, and as Robert has indicated, nothing in the Base UI allows you
to save UPDATE queries and run them from the SQL Query UI. It has
unfortunately always been this way, since StarOffice (so that's a very
long time for a seemingly critically missing piece of functionality).

No one has undertaken to add the required functionality to the UI, or
internal LO parser (which would allow for graphical UI manipulation of
update statements).

The only remaining possibility then is via macro, which can be stored in
the ODB file, and triggered for example, via a button press on a form,
or a form reload, or some other action that can be used as the event
trigger.

Alex

#9

Hallo Alex,

yes, thanks for that suggestion. I was 'kinda feared' you might suggest
that. I think I would need some very solid documentation to go that
way. What flummexes me most there is the multitude of methods available
that house the relatively modest functions I need.

I remember using VBA from my days with MSAccess, but our LO/Linux world
has such an 'embarrassment of riches' I don't quite know where to
start. You could say I'm a bit intimidated by the idea. Any suggestions
where I might start?

Cheers
Harvey

#10

Hi Harvey,

I remember using VBA from my days with MSAccess, but our LO/Linux world
has such an 'embarrassment of riches' I don't quite know where to
start. You could say I'm a bit intimidated by the idea. Any suggestions
where I might start?

I see your mailaccount is from *.de (Germany). So could be you
understand the German descriptions of the Base-Handbuch. You could find
the newest versions on my homepage. There are also example-databases
where you could find parts of macro-code.

You could also find older versions, which are translated into English:
http://robert.familiegrosskopf.de/lo_hb_en/
... but my English might be a littel bit too funny ...

Regards

Robert

#11

Thanks, Robert. I am at home in both languages, so to speak, so there
is no problem there.

As I see it, your handbook is limited to Basic, i.e. no Java, no
python, so that solves the first problem (I might have tried to venture
into python).

OpenSUSE has not yet integrated 6.2, so I hope I can use the 6.2
Handbook without further ambiguities. I know that I was waiting for the
ODBC connection to be set up in 6.2, but the connection issue is
solved, thanks to your help!

In short, I'll give it a try! Many thanks again. No doubt I'll be in
touch again!

Cheers
Harvey

#12

Hi Harvey.
I use the repositories below as 6.1 has some bugs blocking my use, fixed
in 6.2.
https://download.opensuse.org/repositories/LibreOffice:/6.2/openSUSE_Leap_15.1/
https://download.opensuse.org/repositories/LibreOffice:/6.2/openSUSE_Leap_15.0/

A German mirror too;
https://ftp.gwdg.de/pub/opensuse/repositories/LibreOffice:/6.2/openSUSE_Leap_15.0/
etc.

steve

#13

... and there is no problem to use the *.rpm directly from LibreOffice.
I have installed more than 15 different LO-versions for testing parallel
in a folder here - also LO 6.3.0.2 ...

My System: OpenSUSE 15 64bit rpm Linux.

Regards

Robert