Ask Your Question
0

Base → LO Base import as SQL Update

asked 2019-12-05 11:05:27 +0200

cbg.jahn gravatar image

updated 2019-12-05 11:07:06 +0200

Dear LO Base team:

The procedure of importing data from a spreadsheet into a LO Base table works as SQL Insert statement, as shown below in the LO Help forum. i.e., for adding data rows of NEW, DISTINCT primary keys into an existing / defined SQL table.

Importing and exporting data in Base
http://help.libreoffice.org/Common/Im...

Is there a procedure working as SQL Update statement? i.e., for updating existing rows (EXISTING primary keys) in a SQL table?

Sincerely,
Ray

[business case]

Business operation always copes with dynamic data.

Client POs undergo frequent revisions of delivery date, purchase quantity, colors, etc. Client issues revision of multiple POs (batch mode) in spreadsheet. A partially automated procedure of data import for SQL Update, similar to that for SQL Insert above, is crucial for the business case of LO Base.

edit retag flag offensive close merge delete

Comments

Hi, RatSlinger:

Thanks for 12-06 feedback.

Forms mandate manual input effort, row by row. Forms are not suited for batch import. Any guidance is appreciated on the following items of LO scripting.

Script examples (Basic, BeanShell, etc.) to reuse the code for SQL Insert?
Is the following script planning feasible?

Sincerely,
Ray

[script planning]

Two sections of JDBC code are needed per SQL Update syntax:

Update <.. table ..> Set <.. tuple value ..> Where <.. match of primary keys ..>

(1). Set (columns) = (values) → assignment, reuse code for SQL Insert.
(2). Where (keys) = (targets) → comparison, new code.

[tuple comparison]

www.sql-workbench.eu/dbms_comparison....
none = Derby, Firebird, MS SQL Server,
full = HSQLDB, PostgreSQL, IBM DB2,
partial = Sqlite (no In), H2 (no In), MariaDB (no Between), MySQL (no Between), Oracle (only =),

cbg.jahn gravatar imagecbg.jahn ( 2019-12-10 03:38:07 +0200 )edit

12-10 repost as a comment, by deleting answer and repasting as comment, per 12-09 advice by RatSlinger. The forum tools did not work.
(1) repost as a question comment.
(2) repost as a comment under older questions.

cbg.jahn gravatar imagecbg.jahn ( 2019-12-10 03:59:26 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

answered 2019-12-05 17:54:33 +0200

Ratslinger gravatar image

Hello,

There is no easy process to "update" table records in Base from Calc. You would need to do this via a form in Calc or if not using a form write specific macro(s) for the process.

edit flag offensive delete link more

Comments

In response to your additional comments posted as an answer, it would appear that the scripting be placed in the Calc file and not in Base. This seems to be where the data is coming from. For code used in a similar process see this post -> Calc BASIC + Firebird : How to add flush ?

It is just executing SQL through a macro.

Ratslinger gravatar imageRatslinger ( 2019-12-06 18:43:33 +0200 )edit

Thanks for the link to template JDBC code in LO Basic.

cbg.jahn gravatar imagecbg.jahn ( 2019-12-09 03:02:36 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-12-05 11:05:27 +0200

Seen: 135 times

Last updated: Dec 05 '19