How to create an update query with Open Office Bas

Question

I want to create basically an update query on Open Office Base (the same way with Ms ACCESS).

Answer 1

Base does not typically use update queries (but see below). Instead, the easiest way to do an update command is to go to Tools -> SQL. Enter something similar to the following, then press Execute:

UPDATE "Table1" SET "Value" = 'BBB' WHERE ID = 0

The other way is to run the command with a macro. Here is an example using Basic:

Sub UpdateSQL
    REM Run an SQL command on a table in LibreOffice Base
    Context = CreateUnoService("com.sun.star.sdb.DatabaseContext")
    databaseURLOrRegisteredName = "file:///C:/Users/JimStandard/Desktop/New Database.odb"
    Db = Context.getByName(databaseURLOrRegisteredName )
    Conn = Db.getConnection("","") 'username & password pair - HSQL default blank

    Stmt = Conn.createStatement()
    'strSQL = "INSERT INTO ""Table1"" (ID,""Value"") VALUES (3,'DDD')"
    strSQL = "UPDATE ""Table1"" SET ""Value"" = 'CCC' WHERE ID = 0"
    Stmt.executeUpdate(strSQL)

    Conn.close()
End Sub

Note that the data can also be modified with a form or by editing the table directly.

Under some circumstances it is possible to create an update query. I couldn't get this to work with the default built-in HSQLDB 1.8 engine, but it worked with MYSQL.

1. In the Queries section, Create Query in SQL View
2. Click the toolbar button to Run SQL Command directly.
3. Enter a command like the following:

    update mytable set mycolumn = 'This is some text.' where ID = 59;

4. Hit F5 to run the query.

It gives an error that The data content could not be loaded, but it still performs the update and changes the data. To get rid of the error, the command needs to return a value. For example, I created this stored procedure in MYSQL:

DELIMITER $$
CREATE PROCEDURE update_val
(
   IN id_in INT,
   IN newval_in VARCHAR(100)
)
BEGIN
    UPDATE test_table SET value = newval_in WHERE id = id_in;
    SELECT id, value FROM test_table WHERE id = id_in;
END
$$
DELIMITER ;

Then this query in LibreOffice Base modifies the data without giving any errors:

CALL update_val(2,'HHH')

See also:

• https://forum.openoffice.org/en/forum/viewtopic.php?f=5&t=75763
• https://forum.openoffice.org/en/forum/viewtopic.php?f=61&t=6655
• https://ask.libreoffice.org/en/question/32700/how-to-create-an-update-query-in-base-sql/
• Modifying table entries from LibreOffice Base, possible?