Page 1 of 1

[Solved] Database Range Source

Posted: Tue Oct 31, 2017 5:57 pm
by nomen
I have been working on a macro in a Calc file that will update database ranges/links to a Base database. This is the macro as it stands now.

Code: Select all

Sub RefreshDBRanges
	Dim oDBRangesEnum as Object
	Dim oNext as Object
	
oDBRangesEnum = thisComponent.DatabaseRanges.createEnumeration()
    
    	While oDBRangesEnum.hasMoreElements()
    	    	oNext = oDBRangesEnum.nextElement()
oNext.refresh()
Wend
    
MsgBox ("Update Complete", MB_OK, "UPDATE COMPLETE")  
    
End Sub
I have it mostly complete, but would like to provide information messages. I would like to display the Source information as shown near the bottom of the Define Database Range window in the attached file. I have used Xray to try to see if this Source is available, but cannot find it.

Is this available to a macro program? If I am missing it, could someone provide some pointers or code sample to get me started?

Thanks in advance.

Re: Database Range Source

Posted: Tue Oct 31, 2017 6:13 pm
by Villeroy
Use MRI :!:

Re: Database Range Source

Posted: Wed Nov 01, 2017 2:03 am
by Jurassic Pork
hello,
you can use the descriptor object to get the infos. Example :

Code: Select all

Sub RefreshDBRanges
   Dim oDBRangesEnum as Object
   Dim oNext as Object
   Dim Descriptor as Object 
   
oDBRangesEnum = thisComponent.DatabaseRanges.createEnumeration()  
While oDBRangesEnum.hasMoreElements()
              oNext = oDBRangesEnum.nextElement()
              Descriptor = oNext.getImportDescriptor() 
              ' Descriptor 0:DataBaseName  1:SourceType  2:SourceObject
              MsgBox ("Source : "  & Descriptor(0).Value & "/" &_
              Descriptor(2).Value & " - Type : " & Descriptor(1).Value )
              oNext.refresh()
Wend
    
MsgBox ("Update Complete", MB_OK, "UPDATE COMPLETE")  
End Sub
Friendly, J.P

Re: Database Range Source

Posted: Wed Nov 01, 2017 3:33 am
by Villeroy
The following Basic code has been recorded using MRI.
Activate your document with import ranges.
menu:Tools>Add-onls>Mri...
You get a window which shows all document properties on the first tab and the methods on the second tab.
REM Comments in the code added by me describing what I did

Code: Select all

Sub Snippet
  Dim oDatabaseRanges As Variant
  Dim oObj1 As Variant
  Dim oImportDescriptor As Variant
  Dim aPropertyValue As New com.sun.star.beans.PropertyValue
  Dim sName As String
  Dim oValue As Variant
  Dim aPropertyValue2 As New com.sun.star.beans.PropertyValue
  Dim sName2 As String
  Dim oValue2 As Variant
  Dim aPropertyValue3 As New com.sun.star.beans.PropertyValue
  Dim sName3 As String
  Dim oValue3 As Variant
  Dim aPropertyValue4 As New com.sun.star.beans.PropertyValue
  Dim sName4 As String
  Dim oValue4 As Variant
REM double-click property DatabaseRanges
  oDatabaseRanges = ThisComponent.DatabaseRanges
REM double-click method getByIndex and choose 0
  oObj1 = oDatabaseRanges.getByIndex(0)
REM double-click getImportDescriptor
  oImportDescriptor = oObj1.getImportDescriptor()
REM the import descriptor is an array of 4 property values

REM double-click each property value, the name and the value
  aPropertyValue = oImportDescriptor(0)
  sName = aPropertyValue.Name
  oValue = aPropertyValue.Value

  aPropertyValue2 = oImportDescriptor(2)
  sName2 = aPropertyValue2.Name
  oValue2 = aPropertyValue2.Value
  
  aPropertyValue3 = oImportDescriptor(1)
  sName3 = aPropertyValue3.Name
  oValue3 = aPropertyValue3.Value
  
  aPropertyValue4 = oImportDescriptor(3)
  sName4 = aPropertyValue4.Name
  oValue4 = aPropertyValue4.Value
  
End Sub
Finally hit Ctrl+H in order to show the code pane.
The code is also availlable in Python, C#, C++ and Java via Tools>Code in the MRI-menue.

Re: Database Range Source

Posted: Wed Nov 01, 2017 2:20 pm
by nomen
It looks like I did not dig deep enough into the details. These ideas give me the start I was looking for. Thanks for the replies.

Re: [Solved] Database Range Source

Posted: Sat Nov 25, 2017 5:05 pm
by Villeroy
Meanwhile you may have found out that the ImportDescriptor of a database range is a read-only pseudo-property. There is only one method getImportDescriptor but no setImportDescriptor. If you search this forum for getImportDescriptor you will find an example document of mine: download/file.php?id=248 demonstrating how to set the import descriptor. Actually, you have to call method doImport on a cell range object.

Anyhow, the below setImportDescriptor seems to be a good enough wrapper.
test_setImportDescriptor passes an existing database range "Import1" and some parameter query from the "Bibliography" dBase which is shipped with the office.

setImportDescriptor takes 5 arguments
1) An existing database range. This may be a dummy range consisting of one cell. It will adjust automatically to the size of the imported record set.
2) Registered data source name of a Base document
3) One of http://www.openoffice.org/api/docs/comm ... tMode.html The named DataImportMode values represent integers from 0 [NONE] to 3 [QUERY]. NONE removes any database link from the database range.
4) The actual content which is either a table name, a query name or full SQL string.
5) Boolean value if the given SQL string should be parsed by the office suite or not. The latter (not parsed) is the same as "direct SQL mode" in Base.

Code: Select all

Sub test_setImportDescriptor()
sql = "SELECT ""Identifier"", ""Type"", ""Author"", ""Pages"", ""Publisher"", ""Title"", ""Year"", ""ISBN"" FROM ""biblio"" WHERE ""Identifier"" = :Enter_Identifier"
' print sql
' inputbox "","",sql
dbr = ThisComponent.DatabaseRanges.getByName("Import1")
setImportDescriptor dbr, "Bibliography", com.sun.star.sheet.DataImportMode.SQL, sql, True
End Sub

Sub setImportDescriptor(oDBR, sDB, iType, sContent, bParsed)
Dim dsc(3) as new com.sun.star.beans.PropertyValue
	dsc(0).Name = "DatabaseName" : dsc(0).Value = sDB
	dsc(1).Name = "SourceType" : dsc(1).Value = iType
	dsc(2).Name = "SourceObject" : dsc(2).Value = sContent
	dsc(3).Name = "IsNative" : dsc(3).Value = bParsed
	oDBR.getReferredCells.doImport(dsc)
End Sub
In viewtopic.php?f=39&t=91314#p432634 you find an example document where I used this method to build a database import range linked to a parameter query with the SQL stored within the spreadsheet. This is not possible to do in the GUI because the GUI lets you pick predefined database objects (table or query) but not an SQL string.