[Solved] Database Range Source

Creating a macro - Writing a Script - Using the API

[Solved] Database Range Source

Postby nomen » Tue Oct 31, 2017 5:57 pm

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   Expand viewCollapse view
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.
Attachments
DefineDatabaserange.odt
(38.71 KiB) Downloaded 15 times
Last edited by nomen on Tue Nov 14, 2017 8:40 pm, edited 1 time in total.
OpenOffice 3.2 on PCLinuxOS
nomen
 
Posts: 6
Joined: Tue Nov 06, 2012 6:52 pm

Re: Database Range Source

Postby Villeroy » Tue Oct 31, 2017 6:13 pm

Use MRI :!:
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database Range Source

Postby Jurassic Pork » Wed Nov 01, 2017 2:03 am

hello,
you can use the descriptor object to get the infos. Example :
Code: Select all   Expand viewCollapse view
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
OpenOffice 4.1.3 , LibreOffice 5.2.7.2 on Windows 7 / LibreOffice 5.4.2.2 on Ubuntu 16.04
User avatar
Jurassic Pork
 
Posts: 5
Joined: Wed Oct 25, 2017 7:55 am
Location: France

Re: Database Range Source

Postby Villeroy » Wed Nov 01, 2017 3:33 am

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   Expand viewCollapse view
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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Database Range Source

Postby nomen » Wed Nov 01, 2017 2:20 pm

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.
OpenOffice 3.2 on PCLinuxOS
nomen
 
Posts: 6
Joined: Tue Nov 06, 2012 6:52 pm

Re: [Solved] Database Range Source

Postby Villeroy » Sat Nov 25, 2017 5:05 pm

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: https://forum.openoffice.org/en/forum/d ... 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   Expand viewCollapse view
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 https://forum.openoffice.org/en/forum/v ... 14#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.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24650
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to Macros and UNO API

Who is online

Users browsing this forum: No registered users and 11 guests