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.