SQL Syntax UPDATE

classic Classic list List threaded Threaded
13 messages Options
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

SQL Syntax UPDATE

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




--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

On Tue, 2019-07-23 at 16:21 +0200, Robert Großkopf wrote:

> 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

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

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

On Tue, 2019-07-23 at 16:53 +0200, Robert Großkopf wrote:

> 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
> --
> Homepage: http://robert.familiegrosskopf.de
> LibreOffice Community: http://robert.familiegrosskopf.de/map_3
>

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



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

On Tue, 2019-07-23 at 18:08 +0200, Robert Großkopf wrote:

> 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
> --

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Alex Thurgood Alex Thurgood
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

Le 23/07/2019 à 18:37, Harvey Nimmo a écrit :

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

On Tue, 2019-07-23 at 19:03 +0200, Alexander Thurgood wrote:

> Le 23/07/2019 à 18:37, Harvey Nimmo a écrit :
>
> 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
>
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



--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Harvey Nimmo Harvey Nimmo
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

On Tue, 2019-07-23 at 21:02 +0200, Robert Großkopf wrote:

> 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
> --
> Homepage: http://robert.familiegrosskopf.de
> LibreOffice Community: http://robert.familiegrosskopf.de/map_3
>

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


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
steveedmonds steveedmonds
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE



On 24/07/2019 07:49, Harvey Nimmo wrote:
> OpenSUSE has not yet integrated 6.2, so I hope I can use the 6.2
> Handbook without further ambiguities.
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

--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy
Robert Großkopf Robert Großkopf
Reply | Threaded
Open this post in threaded view
|

Re: SQL Syntax UPDATE

... 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
--
Homepage: http://robert.familiegrosskopf.de
LibreOffice Community: http://robert.familiegrosskopf.de/map_3


--
To unsubscribe e-mail to: [hidden email]
Problems? https://www.libreoffice.org/get-help/mailing-lists/how-to-unsubscribe/
Posting guidelines + more: https://wiki.documentfoundation.org/Netiquette
List archive: https://listarchives.libreoffice.org/global/users/
Privacy Policy: https://www.documentfoundation.org/privacy