We will be migrating from Ask to Discourse on the first week of August, read the details here

Ask Your Question
0

loop over rows in base

asked 2021-01-30 22:31:50 +0200

verwirrt gravatar image

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.)

edit retag flag offensive close merge delete

Comments

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.

Ratslinger gravatar imageRatslinger ( 2021-01-30 22:43:17 +0200 )edit

2 Answers

Sort by » oldest newest most voted
0

answered 2021-02-16 03:45:18 +0200

verwirrt gravatar image

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

edit flag offensive delete link more
0

answered 2021-02-01 08:13:46 +0200

Wanderer gravatar image

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/view...

(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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2021-01-30 22:31:50 +0200

Seen: 81 times

Last updated: Feb 16