How to scroll through a table in base

[PhilipK]

I am sure this can be done, and I have found hints in various places, but never clear enough to get me off the ground. The problem (schematically) is this.

I have a table called References which gives me links to objects in different publications; some objects may be mentioned in more than one. (The table has this form in order to ensure referential integrity and so that it can be queried both to show what publications refer to any object, and what objects are listed in any publication.) Thus:

Object          Reference

Obj1             Source1
Obj2             Source4
Obj3             Source4
Obj3             Source8
Obj4             Source3

Because LibeOffice reports do not support multiple tables (or multi-record objects), I hope to resolve a consequent layout problem by generating a (temporary) table with a single record for each object, which would take the following form in order to provide the same information:

Object          Reference

Obj1             Source1
Obj2             Source4
Obj3             Source4; Source8
Obj4             Source3

I envisage doing this by scrolling through the References table line by line, storing the field values in temporary variables. Then whenever a line is encountered that has the same value for ‘Object’ as the previous one, the reference values are concatenated; but whenever it is different, the stored values are written to a new record in the temporary table. The pseudo-code might look like this:

        [Connect to source table, set up cursor]
        [Execute SQL to create destination table]
REM Initiate variables
        Previous = 0 
        Ref = ""
        Loop  [Read record]
REM  Check that this is a new object, and not first in table
           If ("Object" != Previous) and (Previous !=0) then
REM  Write preceding record to new table
                [INSERT INTO DestTable(1,2) VALUES (Last, Refs);] 
REM  Reset variables to current record
                Previous = "Object" 
                Refs = "Reference"
REM  If same object as previous record, simply concatenate ref.
            Else
                Refs = Refs & "; " & "Reference"
            Endif
        [Scroll to next record]
        End loop

(And of course there needs to be something to recognise end-of-file and to ensure that the last record is written to the new table.)

[Ha! I am scolded, and rightly, for not providing quite enough information when I was trying to be as concise as possible.] The CONTEXT of this question is using LO Base connected through ODBC to a database in SQLite.

How do I set up a table cursor and cause it to perform this task?

[Ratslinger]

Hello,

As so often happens by many posters (sigh) you have not provided vital information. Here DB and connector type are probably critical.

With HSQLDB embedded it is a matter of setting ResultSetType and then you can move through the result set in different ways. Some DB’s and/or connectors balk at some items like First or 'Last` and may be better to use creation of a RowSet.

To give a direct answer to this question would require a lot of duplicate posting. Instead here are some items of note and some links.

Pitonyak - OOME PDF

Pitonyak - Andrew Base PDF

AOO Result Set

RowSet info & further link

How to display SQL query result

handle runtime error sql macro

Quick test with SQLite3 shows most of a macro SQL execution with ResultSetType set worked. Had problem with getRow and there may be others. May need to use RowSet for some or all.

Edit:

Will also mention in reference to your comment on reports, many things are possible with Report Builder. It may not be as straight forward as one would like. Example of an invoice using multiple tables → Libre Data - multi-level report and comments in this post regarding Jasper Studio → sub-reports

[Ratslinger]

Make sure you go to subsequent pages for AOO post as there is a great deal of information throughout.

[PhilipK]

Thank you for this. Apologies for shortcomings, now rectified I hope. Still not quite used to corresponding in a public forum about successive queries (sorry about that, but I am really appreciating the help) when it seems almost more like a conversation! Lots to read and take in, shall expect to report back in due course.

[PhilipK]

I have [partial] success, which may be of interest to others, and I think it is too long to post as a comment. The pointer to the AOO documentation (which I don’t think I had found before) was really useful. The code I have succeeded in running [with total success after helpful advice below from Ratslinger] is as follows:

SUB ResolveRefs
    	Dim oForm, Conn, ResultA, ResultB as Object
    	Dim QString, sSQL_A, sSQL_B, Refs as String
    	Dim Previous, This  as Integer
    		
REM   Table 'Answer' already exists and is the source table; 'Answer2' is the destination table
        oForm = ThisComponent.Drawpage.forms.getByName("MainForm")
    	Conn = oForm.activeConnection
    	sSQL_A = Conn.createStatement()
    	sSQL_B = Conn.createStatement()
    	QString = "DROP TABLE IF EXISTS 'Answer2' ; "
    	QString = QString & "CREATE TABLE 'Answer2' ('ObjNo' INTEGER, 'Ref' TEXT) ;"
    	sSQL_A.executeUpdate(QString)
    	ResultB = sSQL_B.executeQuery("SELECT ‘Object No.’, ‘Reference’ FROM ‘Answer’;")
REM	Do the next only for the first row of the table
    	ResultB.next()
    	Previous = ResultB.getInt(1)
    	Refs = ResultB.getString(2)
REM	Do the looping for all the remaining rows
    	Do
    		If not ResultB.isFirst() then
    			This = ResultB.getInt(1)
REM	Check that this is a new object; if so, write out stored values
    			If (This <> Previous) then
    				sSQL_A.executeUpdate("INSERT INTO 'Answer2' ('ObjNo','Ref') VALUES ( '" & Previous & "', '" & Refs & "') ; ")
REM	Now reset variables to current record
    				Previous = This
    				Refs = ResultB.getString(2)
    			Else
REM	If same object as previous record, simply concatenate ref.
    				Refs = Refs & "; " & ResultB.getString(2)
    			Endif
    		Endif
    	Loop until (ResultB.next() = FALSE) 
REM	Write out last record
    	sSQL_A.executeQuery("INSERT INTO 'Answer2' ('ObjNo','Ref') VALUES ( '" & Previous & "', '" & Refs & "') ; ")
END SUB

It took me a while to figure out that in my configuration (LO Base communicating with SQLite3 database through ODBC driver by Steve Palm) the cursor markers .isFirst() and .isAfterLast() worked, but .isLast() did not [bug?], and the cursor kept falling off the end of the table. I then realised that .next() is Boolean, and could be used just as well to determine when the cursor has arrived at the last row.

The only remaining problem is that the Create and Insert SQL calls return an error of type com.sun.star.sdbc.SQLException, “The execution of the query doesn’t return a valid result set.” [My mistake in using executeQuery instead of executeUpdate - now corrected above and the problem has disappeared.]

[Ratslinger]

You are attempting to do two different things with a single connection. sSQL is a connection already for the result set you are stepping through. In the middle of this process you are trying to insert a new record with a different statement.

Create and use a different connection such as:

sSQL2 = Conn.createStatement()

then you can run the other statements using this connection while still maintaining the result set processing.

[PhilipK]

That is an interesting observation, but I am not convinced that it is correct. The connection is surely with the named database, not with any individual table: the tables are addressed through the SQL statements. I changed the code but it made no difference to the error message on the Create and Insert SQL statements. (The Select statements executed without complaint.)

[Ratslinger]

Having done similar in the past, I still believe you will have other problems if the two do not use different connections.

As for the error you are getting it is because you are using an incorrect execute statement. You have:

sSQL.executeQuery(

and it should be

sSQL.executeUpdate(

for the Create & Insert statements.

[PhilipK]

Oh ha ha! Yes, of course executeQuery was wrong in those contexts, and that was the problem with an earlier piece of code. Having corrected that and re-tested I did find that as you had suggested, I needed two connections to handle the two tables simultaneously. I have adjusted the code displayed above in case it is useful to others. Apologies for having doubted, and once again much appreciate being set on the right path!