SQL Error in a macro. Needs Help!

[Renel]

From a macro, I want to insert the text contained in a variable named sHLink in a column of a table of a form for a record in use using an SQL command.
.
The TActors table of a form named Actors contains a column named Link_X and a column named IDActor.
.
In the form, there is a field named fmt-IDActor associated with the IDActor data field and a field named txt-HLink associated with the Link_X data field.
.
The current record ID was previously placed in a variable named sRecordID. The sHLink variable has been initialized and contains String type information.
.
The following error is generated:
BASIC runtime error.
Property or method not found: ExecuteSQL.
.
oDb = com.sun.star.sdbc.drivers.OConnectionWrapper
sSQL = expected value
.
Could someone help me with this?
.
Here is the code:

Sub InsertText
	Dim sName as String
	Dim sRecordID as Integer
	...
	There was some work done before this section appeared
	...

	' New variables
	Dim oDb as Object
	Dim sSQL as String
	Dim sHLink as String

	' I want to make sure the database is available
	oDb = ThisDatabaseDocument.CurrentController.ActiveConnection

	' sHLink content
	sHLink = "There is some text here combined with this String variable" & sName

	' SQL command content ' Try this one
	sSQL = "UPDATE TActors SET Link_X = " & sHLink & " WHERE IDActor = " & sRecordID
    ' And try this one
	sSQL = "UPDATE ""TActors "" SET ""Link_X"" = " & sHLink & " WHERE ""IDActor"" = " & sRecordID

	oDb.ExecuteSQL(sSQL) ' ERROR Property or method not found: ExecuteSQL
End Sub

[RobertG]

Seems the code isn’t complete:

oSQL_Statement = oDb.createStatement()
oSQL_Statement.executeUpdate(sSQL)

There are 3 possible methods: executeQuery (for reading content of table), executeUpdate (for Insert, Update or Delete) and execute (Query, Insert, Update or Delete - could also give feedback for returning values after Insert).

[Renel]

Your code was what I was missing.
.
I only had to rewrite the SQL command this way to avoid an error.
.
sSQL = "UPDATE ““TActors “” SET ''Link_X”” = ’ " & sHLink & " ’ WHERE ““IDActor”” = " & sRecordID
.
Thanks very much @RobertG!

[Villeroy]

Alternative approach with parameter query:

  oActiveConnection = oCurrentController.ActiveConnection
  oObj1 = oActiveConnection.prepareCommand("DELETE FROM ""D"" WHERE ""ID"" = ?", 2)  
  oObj1.setLong(1, 230)
  oObj2 = oObj1.executeUpdate()```
The ActiveConnection prepares a statement with one positional parameter.
Method setLong takes a positional parameter index (1-based) and a value. Here I delete the row with ID=230. The same works with many parameters of all types (setString, setDouble, setDate, setTime etc.)

[Renel]

@Villeroy, your alternative seems to be more concise compared to the code I’m currently using which works great thanks to @RobertG.
.
In the approach you propose, is there a difference between preparing the SQL command by placing it in a variable and using this variable as a parameter of prepareCommand and using the command directly as a parameter of prepareCommand.
.
I understand that the ? will match a value that will be used by **setLong()**. For example, if the SQL command contained more than one field like text1, text2, the setLong() command could be formulated like this: setLong(1, text1) or **setLong(2, text2)** depending on the needs of the update, is that correct?
.
To help me understand, could you give me a little more detail on the use of these values and their correspondence:
WHERE ""ID"" =?", 2 and **setLong(1, 230)**

[Villeroy]

https://api.libreoffice.org/docs/idl/ref/interfacecom_1_1sun_1_1star_1_1sdbc_1_1XParameters.html
https://api.libreoffice.org/docs/idl/ref/namespacecom_1_1sun_1_1star_1_1sdb_1_1CommandType.html

The 2 is c.s.s.sdb.CommandType SQL

[Renel]

Alright, I need some time to explore the links you suggested and explore the possibilities it offers. What’s really interesting with this is that there isn’t just one way to get a result. Also, the better one understands how APIs work and interact with a database, the more the possibilities grow. Exciting!

Thanks!

[Villeroy]

[Tutorial] Introduction into object inspection with MRI
https://www.pitonyak.org/database/

[Renel]

This tool looks really interesting. I will proceed to install this. Thank you for the link!

[RobertG]

Two example from German Base Handbuch:

 DIM oSQL_Statement AS OBJECT
 DIM stSql AS STRING
 stSql = "UPDATE ""Verfasser"" SET ""Nachname"" = ?, ""Vorname"" = ? WHERE ""ID"" = ?"
 oSQL_Statement = oConnection.prepareStatement(stSql)
 oSQL_Statement.setString(1, oTextfeld1.Text)
 oSQL_Statement.setString(2, oTextfeld2.Text)
 oSQL_Statement.setLong(3, oZahlenfeld1.Value)
 iResult = oSQL_Statement.executeUpdate

and

 stSql = "UPDATE ""Person"" SET ""Name"" = ? WHERE ""ID"" = ?"
 oSQL_Statement = oConnection.prepareStatement(stSql)
 oSQL_Statement.setString(1, "Bill")
 oSQL_Statement.setLong(2, 1)
 oSQL_Statement.addBatch()
 oSQL_Statement.setString(1, "Michaela")
 oSQL_Statement.setLong(2, 2)
 oSQL_Statement.addBatch()
 oSQL_Statement.executeBatch()

[Renel]

Okay! @RobertG, the examples you suggested to me lead me to believe that if I have a table with 5 columns or fields, I could act on this table via this type of commands considering that the “?” acts as an index for the columns or existing fields in the table.

So, depending on the data type associated with the field, I can put the column 3 or 5 depending on the definition of the SQL command. Plus, I can do this for multiple records at once. Is it correct?

[RobertG]

It’s correct so.