如何使用Open Office Base创建更新查询? [英] How to create an update query with Open Office Base?

问题描述

我基本上想在Open Office Base上创建一个更新查询(与ACCESS女士相同).

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

推荐答案

Base通常不使用更新查询(但请参见下文).相反，执行更新命令的最简单方法是转到工具"->"SQL".输入类似于以下内容的内容，然后按Execute:

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

另一种方法是使用宏运行命令.这是使用Basic的示例:

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.

在某些情况下，可以 创建更新查询.我无法将其与默认的内置HSQLDB 1.8引擎一起使用，但可与MYSQL一起使用.

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. 在查询"部分中，Create Query in SQL View
2. 单击工具栏按钮以Run SQL Command directly.
3. 输入如下命令:

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;

1. 按 F5 可以运行查询.

1. Hit F5 to run the query.

它给出了错误The data content could not be loaded，但是它仍然执行更新并更改数据.要消除该错误，该命令需要返回一个值.例如，我在MYSQL中创建了此存储过程:

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 ;

然后，LibreOffice Base中的此查询将修改数据而不会出现任何错误:

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

CALL update_val(2,'HHH')

另请参阅:

• 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/zh-CN/question/32700/how-to-create-an-update-query-in-base-sql/
• 可以从LibreOffice Base修改表条目吗? a>

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

这篇关于如何使用Open Office Base创建更新查询?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！