OpenOffice.org Forum at OOoForum.orgThe OpenOffice.org Forum
 
 [Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register
 [Profile]   [Log in to check your private messages]   [Log in

[Solved] Define new query with BASIC macro code

 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base
View previous topic :: View next topic  
Author Message
stalbe
Newbie
Newbie


Joined: 07 Jun 2011
Posts: 2

PostPosted: Mon Jun 04, 2012 6:05 am    Post subject: [Solved] Define new query with BASIC macro code Reply with quote

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
Back to top
View user's profile Send private message
Sliderule
Super User
Super User


Joined: 29 May 2004
Posts: 2499
Location: 3rd Rock From The Sun

PostPosted: Mon Jun 04, 2012 7:11 am    Post subject: Reply with quote

Please see the following link.

http://www.oooforum.org/forum/viewtopic.phtml?t=98808

I hope this helps, please be sure to let me / us know.

Sliderule

Thanks to add [Solved] in your first post Title ( edit button ) if your issue has been fixed / resolved.
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Base All times are GMT - 8 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group