stalbe
Newbie
Joined: 07 Jun 2011
Posts: 2
|
 Posted: Mon Jun 04, 2012 6:05 am Post subject: [Solved] Define new query with BASIC macro code |
 |
|
I wanted to create a STARBASIC macro:
| Code: | | Sub CreateQuery(name as String, code as String) |
If the query already exists, it should be overwritten.
Example: | Code: | | CreateQuery("Query1", "SELECT * FROM Table1") |
Solution
This solution has been tested on OpenOffice 3.4. The code is put together from various sources to help others see how a database with tables and queries can be restored. Exported queries and table data are read back into a new database should the original database file crash.
| Code: | Sub Main
' To illustrate usage
Dim dbName as String
Dim queryName as String
Dim strSQL as String
Dim sFilename as String
dbName = "New Database"
queryName = "Query1"
strSQL = "SELECT * FROM Keys"
sFilename = "C:\Users\...\DatabaseQueries.txt"
'CreateQuery_01()
'CreateQuery_02(dbName, queryName, strSQL)
'CreateQuery_03(queryName, strSQL)
CreateQueryFromFile(sFilename)
End Sub
Sub ExportAllQueries()
' This subroutine can be used to dump queries in full text.
Dim oCon As Object, oDataSource As Object
Dim sFilename As String
Dim nFd As Integer, i As Integer
Dim oQueryDefinitions As Object
Dim oQuery As Object
oDataSource = ThisComponent.DataSource
oCon = oDataSource.getConnection("","")
oQueryDefinitions = oDataSource.getQueryDefinitions()
sFilename = "C:\Users\...\DatabaseQueries.txt"
nFd = FreeFile
Open sFilename For Output As #nFd
For i = 0 To oQueryDefinitions.Count() - 1
oQuery = oQueryDefinitions(i)
Print #nFd, "=== " & oQuery.Name & " ==="
Print #nFd, oQuery.Command
Print #nFd,
Next i
Close #nFd
oCon = nothing
oDataSource = nothing
oQueryDefinitions = nothing
oQuery = nothing
End Sub
Sub ExecuteSQLfromFile()
' This subroutine can be used to read SQL commands from a file,
' such as commands created by SQL command SCRIPT 'filePath.txt'
Dim oStatement As Object
Dim oDoc As Object
Dim sFilename As String
Dim nFd As Integer
Dim sLine As String
oDoc = ThisComponent
if IsNull(oDoc.CurrentController.ActiveConnection) then
oDoc.CurrentController.connect
endif
sFilename = "C:\Users\...\SQLfromSCRIPTcommand.txt"
nFd = FreeFile
Open sFilename For Input As #nFd
oStatement = oDoc.CurrentController.ActiveConnection.createStatement()
While not eof(#nFd)
Line Input #nFd, sLine
oStatement.execute(sLine)
wend
Close #nFd
oStatement = nothing
oDoc = nothing
End Sub
Sub CreateQuery_01()
' Raw version.
Dim dbName as String
Dim queryName as String
Dim strSQL as String
Dim oDBContext As Object
Dim oDataSource As Object
Dim oQueryDefinitions As Object
Dim oQueryObject As Object
dbName = "New Database"
queryName = "Query1"
strSQL = "SELECT * FROM Keys"
oDBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDBContext.getByName(dbName)
oQueryDefinitions = oDataSource.getQueryDefinitions()
oQueryObject = createUnoService("com.sun.star.sdb.QueryDefinition")
oQueryObject.Command = strSQL
if oQueryDefinitions.hasByName(queryName) then
oQueryDefinitions.removeByName(queryName, oQueryObject)
end if
oQueryDefinitions.insertByName(queryName, oQueryObject)
oDBContext = nothing
oDataSource = nothing
oQueryDefinitions = nothing
oQueryObject = nothing
End Sub
Sub CreateQuery_02(dbName as String, queryName as String, strSQL as String)
' Allows to specify database, query name and SQL string.
Dim oDBContext As Object
Dim oDataSource As Object
Dim oQueryDefinitions As Object
Dim oQueryObject As Object
oDBContext = createUnoService("com.sun.star.sdb.DatabaseContext")
oDataSource = oDBContext.getByName(dbName)
oQueryDefinitions = oDataSource.getQueryDefinitions()
oQueryObject = createUnoService("com.sun.star.sdb.QueryDefinition")
oQueryObject.Command = strSQL
if oQueryDefinitions.hasByName(queryName) then
oQueryDefinitions.removeByName(queryName, oQueryObject)
end if
oQueryDefinitions.insertByName(queryName, oQueryObject)
oDBContext = nothing
oDataSource = nothing
oQueryDefinitions = nothing
oQueryObject = nothing
End Sub
Sub CreateQuery_03(queryName as String, strSQL as String)
' For creation in the current database.
Dim oDataSource As Object
Dim oQueryDefinitions As Object
Dim oQueryObject As Object
oDataSource = ThisComponent.DataSource
oQueryDefinitions = oDataSource.getQueryDefinitions()
oQueryObject = createUnoService("com.sun.star.sdb.QueryDefinition")
oQueryObject.Command = strSQL
if oQueryDefinitions.hasByName(queryName) then
oQueryDefinitions.removeByName(queryName, oQueryObject)
end if
oQueryDefinitions.insertByName(queryName, oQueryObject)
oDBContext = nothing
oDataSource = nothing
oQueryDefinitions = nothing
oQueryObject = nothing
End Sub
Sub CreateQueryFromFile(filePath as String)
' Reading back the exported queries in a file into the database.
Dim queryName as String
Dim strSQL as String
Dim sFilename As String
Dim nFd As Integer
Dim sLine As String
Dim oDataSource As Object
Dim oQueryDefinitions As Object
Dim oQueryObject As Object
Dim flag as Boolean
oDataSource = ThisComponent.DataSource
oQueryDefinitions = oDataSource.getQueryDefinitions()
oQueryObject = createUnoService("com.sun.star.sdb.QueryDefinition")
nFd = FreeFile
Open filePath For Input As #nFd
flag = false
While not eof(#nFd)
Line Input #nFd, sLine
'For i = 0 To oQueryDefinitions.Count() - 1
'oQuery = oQueryDefinitions(i)
if flag then
strSQL = sLine
createQuery_03(queryName, strSQL)
flag = false
queryName = ""
elseif left(sLine, 4) = "=== " then
queryName = Mid(sLine, 5, Len(sLine) - 8)
flag = true
end if
wend
Close #nFd
oDataSource = nothing
oQueryDefinitions = nothing
oQueryObject = nothing
End Sub |
Last edited by stalbe on Mon Jun 04, 2012 9:07 am; edited 3 times in total |
|
