I want to loop over the rows in a base table, select rows that have a specified value in one field, modify (multiply by a factor) other fields in the selected rows, and return the updated rows to the table. Can someone tell me where I can find an example of how something like this could be done in a macro? (I have considerable experience in Basic and C++, but none in the LibreOffice Basic variant, and no experience with SQL.)
Hello,
There is no specific example for what you want based upon the provided information. Too many variants involved. It is many questions requiring many answers and possibly variations depending upon circumstances. Sounds as if much can be done through SQL but if recurring probably needs to be done through SQL in a macro.
Need more specific information - Specific LO version; OS; database used; more specifics on what you are doing; a sample would help.
At first: Usually you don’t loop through in SQL,
you describe the task, and the database decides how to solve this.
UPDATE table
SET OtherField = OtherField * factor
WHERE OneField = ASpecifiedValue
or as invented example
UPDATE employee
SET income = income * 0.8
WHERE country = ‘UK’
To understand how this is called from BASIC
you may check the code-sections in
https://www.libreoffice-forum.de/viewtopic.php?f=10&t=33231
(The topic there is not to expect a result-set from update, therefore to use the method executeUpdate instead of executeQuery)
The general concept is always:
get a connection to the database
Generate a String with the sql-command
Send it to the database
Check the results…
J.
Thank you. Starting with your hint I was able to do what I wanted. A simplified version of what I ended with appears below. There were complications. Because I use mixed upper and lower case for the names of the fields in the data base, they apparently have to appear in quotes in the SQL command, and in BASIC to enter a single double-quote in a string, you have to enter a pair of double-quotes, so the command construction looks pretty ugly. In addition, I needed to apply the adjustment to several fields in the records, so I did that in a loop.
Option Explicit
Sub Main
DIM rcrdType AS STRING
DIM factor AS STRING
DIM msg AS STRING
DIM msgResponse AS integer
DIM oStatement AS OBJECT
DIM command AS STRING
DIM i AS INTEGER
DIM varList(4) AS STRING
varList() = Array( “”,“Var1”, “Var2”, “Var3”, “Var4”)
oStatement = ThisComponent.CurrentController.ActiveConnection.createStatement()
Retry:
rcrdType = InputBox(“Record type?”,“Record type entry”)
rcrdType = UCase(rcrdType)
factor = Val( InputBox("Adjustment Factor? ", “Adjustment factor entry”) )
msg = "Record type: " + rcrdType + chr$(13) + _
"Adjustment factor: " + factor + chr$(13) + chr$(13) + “Entries OK?”
msgResponse = MsgBox (msg, 3)
if (msgResponse = 2) then end ’ Cancel
if (msgResponse = 7) then goto Retry ’ Not OK
for i=1 to 4
var = varList(i)
command = "UPDATE ""dBFile"" SET """ + var + """="""+var+"""*" + factor + _
" WHERE ""Type""='" + rcrdType +"'"
oStatement.execute(command)
next i
End Sub