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

Get data from database and fill in calc???
Goto page 1, 2  Next
 
Post new topic   Reply to topic    OOoForum.org Forum Index -> OpenOffice.org Macros and API
View previous topic :: View next topic  
Author Message
wenze
General User
General User


Joined: 06 Aug 2003
Posts: 7

PostPosted: Wed Aug 06, 2003 2:59 am    Post subject: Get data from database and fill in calc??? Reply with quote

Hello,
how can i get in OpenOffice Basic data from database and fill in calc?
My Sql-Statement run.



Thanks

Joerg
Code:
Sub SQLTest2_Messagebox


Dim DatabaseContext As Object
Dim DataSource As Object
Dim Connection As Object
Dim InteractionHandler as Object
Dim Statement As Object
Dim ResultSet As Object
Dim Ergebnis As String

   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   DataSource = DatabaseContext.getByName("ewois.log")
If Not DataSource.IsPasswordRequired Then
Connection = DataSource.GetConnection("","")
Else
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
End If
Statement = Connection.createStatement()
ResultSet = Statement.executeQuery("SELECT c1,c2,c3,c4,c5 from ewois_2003_4")
If Not IsNull(ResultSet) Then
While ResultSet.next

Ergebnis = Ergebnis & ResultSet.GetString(1) & " : " & ResultSet.GetString(2)&+ _
 " : " & ResultSet.GetString(3)& " : " & ResultSet.GetString(4) & Chr(10)


MsgBox (Ergebnis, 20)
Wend
End If

End Sub
Back to top
View user's profile Send private message
openmind
OOo Enthusiast
OOo Enthusiast


Joined: 28 Jun 2003
Posts: 106
Location: Switzerland

PostPosted: Wed Aug 06, 2003 4:48 am    Post subject: Reply with quote

While you are able to access the database you just need to have code for puttin values in clas cells:

Code:
 Sub populateCellWithData
 
    Dim sheet as Object
    Dim cell as Object

    ' spreadsheet taken from selection
    sheet = thisComponent.currentSelection.getSpreadsheet()
    ' cell on position 0,0
    cell = sheet.getCellByPosition( 0, 0 )
 
    ' insert text
    cell.String = "my new text in cell 0,0"
    ' insert number
    cell.Value = 2
 
 End Sub
Back to top
View user's profile Send private message
wenze
General User
General User


Joined: 06 Aug 2003
Posts: 7

PostPosted: Wed Aug 06, 2003 5:55 am    Post subject: just beginning Reply with quote

Thanks, but i have more values. What can i do? Look:

20030402142521 ewois gesamt ok 2610 ms
20030402143521 ewois gesamt ok 2453 ms
20030402144521 ewois gesamt ok 2430 ms
20030402145521 ewois gesamt ok 2439 ms
20030402150533 ewois gesamt ok 5754 ms
20030402151521 ewois gesamt ok 2579 ms
20030402152521 ewois gesamt ok 2460 ms
20030402153521 ewois gesamt ok 2536 ms
20030402154522 ewois gesamt ok 2722 ms


openmind wrote:

Code:
 Sub populateCellWithData
 
    Dim sheet as Object
    Dim cell as Object

    ' spreadsheet taken from selection
    sheet = thisComponent.currentSelection.getSpreadsheet()
    ' cell on position 0,0
    cell = sheet.getCellByPosition( 0, 0 )
 
    ' insert text
    cell.String = "my new text in cell 0,0"
    ' insert number
    cell.Value = 2
 
 End Sub
Back to top
View user's profile Send private message
openmind
OOo Enthusiast
OOo Enthusiast


Joined: 28 Jun 2003
Posts: 106
Location: Switzerland

PostPosted: Wed Aug 06, 2003 8:24 am    Post subject: Reply with quote

IF you dont concatenate your db values you can do something like this:

Code:
Sub populateCellWithData
 
    Dim sheet as Object
    Dim cell as Object
    sheet = thisComponent.currentSelection.getSpreadsheet()

    ' your database code here

   While ResultSet.next

           cell = sheet.getCellByPosition( 0, 0 )
           cell.String = ResultSet.GetString(1)

           cell = sheet.getCellByPosition( 1, 0 )
           cell.String = ResultSet.GetString(2)

           cell = sheet.getCellByPosition( 2, 0 )
           cell.String = ResultSet.GetString(3)
      ' ...

   Wend
 
 End Sub


i hope I understood your problem.
Back to top
View user's profile Send private message
wenze
General User
General User


Joined: 06 Aug 2003
Posts: 7

PostPosted: Wed Aug 06, 2003 9:17 am    Post subject: Reply with quote

Shocked

My db values is concatenate. What i meen is, how can i get 6 column from my sql-query in 6 column from calc?

Sample:

my db :
20030402142521| ewois||gesamt| ok| 2610| ms
20030402143521| ewois| gesamt| ok| 2453| ms

new table:
A1|A2| A3| A4| A5| A6
20030402142521| ewois| gesamt| ok| 2610| ms
20030402143521| ewois| gesamt| ok| 2453| ms

I must for every column one sql-query again generate?

Joerg
Back to top
View user's profile Send private message
Guest






PostPosted: Wed Aug 06, 2003 2:42 pm    Post subject: Reply with quote

REM import query from data source in calc sheet
Sub ImportQuery (sDBName as string,sQueryName as string,oTopLeftCell as object)
Dim mImportQuery(2)as new com.sun.star.beans.PropertyValue
mImportQuery(0).Name ="DatabaseName"
mImportQuery(0).Value = sDBName
mImportQuery(1).Name = "SourceType"
mImportQuery(1).Value = com.sun.star.sheet.DataImportMode.QUERY
mImportQuery(2).Name = "SourceObject"
mImportQuery(2).Value = sQueryName
oTopLeftCell.doImport(mImportQuery())
End Sub
[/quote]
Back to top
openmind
OOo Enthusiast
OOo Enthusiast


Joined: 28 Jun 2003
Posts: 106
Location: Switzerland

PostPosted: Wed Aug 06, 2003 2:45 pm    Post subject: Reply with quote

I can't see the troubles here...

maybe helpful: http://docs.sun.com/db/doc/817-1826-10

Quote:
My db values is concatenate. What i meen is, how can i get 6 column from my sql-query in 6 column from calc?


I can't see a reason to concatenate all the db values just to face the problem of splitting them again Wink

Just merge your code with mine. I did it as follows but didn't test it out.


Code:
Sub SQLTest2_Messagebox
 
   Dim DatabaseContext As Object
   Dim DataSource As Object
   Dim Connection As Object
   Dim InteractionHandler as Object
   Dim Statement As Object
   Dim ResultSet As Object
   Dim Ergebnis As String
 
   DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
   DataSource = DatabaseContext.getByName("ewois.log")
   If Not DataSource.IsPasswordRequired Then
      Connection = DataSource.GetConnection("","")
   Else
      InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
      Connection = DataSource.ConnectWithCompletion(InteractionHandler)
   End If

   Statement = Connection.createStatement()
   ResultSet = Statement.executeQuery("SELECT c1,c2,c3,c4,c5 from ewois_2003_4")

   Dim counter as Integer
   counter = 0

   If Not IsNull(ResultSet) Then
      While ResultSet.next
 
            cell = sheet.getCellByPosition( 0, counter )
                 cell.String = ResultSet.GetString(1)
 
                 cell = sheet.getCellByPosition( 1, counter )
                 cell.String = ResultSet.GetString(2)
 
                 cell = sheet.getCellByPosition( 2, counter )
                 cell.String = ResultSet.GetString(3)
 
                 cell = sheet.getCellByPosition( 3, counter )
                 cell.String = ResultSet.GetString(4)
 
                 cell = sheet.getCellByPosition( 4, counter )
                 cell.String = ResultSet.GetString(5)
 
                 cell = sheet.getCellByPosition( 5, counter )
                 cell.String = ResultSet.GetString(6)
         
         counter = counter + 1
      Wend
   End If 
End Sub



Quote:
must for every column one sql-query again generate?


Reboot your system after every query to be sure ... Twisted Evil
(no no, don't believe ) Wink
Back to top
View user's profile Send private message
wenze
General User
General User


Joined: 06 Aug 2003
Posts: 7

PostPosted: Thu Aug 07, 2003 5:35 am    Post subject: Reply with quote

Thanks all, i am testing later. Embarassed
Back to top
View user's profile Send private message
openmind
OOo Enthusiast
OOo Enthusiast


Joined: 28 Jun 2003
Posts: 106
Location: Switzerland

PostPosted: Fri Aug 08, 2003 4:44 am    Post subject: Reply with quote

the example above can't work:

include this line before the loop:

Code:
sheet = thisComponent.currentSelection.getSpreadsheet()
Back to top
View user's profile Send private message
wenze
General User
General User


Joined: 06 Aug 2003
Posts: 7

PostPosted: Mon Aug 11, 2003 12:58 am    Post subject: Reply with quote

Shocked Smile Laughing Very Happy It's work !

Thanks openmind
Back to top
View user's profile Send private message
OOoBasicNewbie
General User
General User


Joined: 27 Sep 2007
Posts: 8

PostPosted: Thu Sep 27, 2007 9:04 am    Post subject: Help Reply with quote

I am new to OOoBasic. I have multiple VBA macros that I need to convert to OOoBasic. I have searched EVERYWHERE for a way to import data from MS Access DB. I currently use an SQL statement to query the DB, but cannot find a way to do this in OOoBasic Code in OOo Calc. Can anyone help??????
Back to top
View user's profile Send private message
Mark B
Super User
Super User


Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK

PostPosted: Fri Sep 28, 2007 6:04 am    Post subject: Reply with quote

Hi

I've got a chapter that you can download and which will explain how to do what you want. Go to http://www.packtpub.com/openoffice-ooobasic-calc-automation/book and then click on 'Download Chapter 6: Working with Databases'

Mark
_________________
Mark B's Articles
Back to top
View user's profile Send private message Send e-mail Visit poster's website MSN Messenger
OOoBasicNewbie
General User
General User


Joined: 27 Sep 2007
Posts: 8

PostPosted: Fri Sep 28, 2007 6:52 am    Post subject: Reply with quote

Thanks Mark. Your book may prove to be the biggest help I could ask for. I WILL be purchasing your book.
Back to top
View user's profile Send private message
OOoBasicNewbie
General User
General User


Joined: 27 Sep 2007
Posts: 8

PostPosted: Fri Sep 28, 2007 12:32 pm    Post subject: Reply with quote

Ok, I have read through Chapter 6 of your book Mark. Everything works except one thing. I have a SQL statement that I am sending to MS Access. I have tested the statement directly in MS Acces and it works, but for some reason when I put the statement in OOoBasic I get a SQLException error. The OOoBasic code is this:
osql = "SELECT DISTINCTROW VendorList.VendorId, ProductTable.ProdID, Category.CategoryID, VenProdTable.VendorProductName, VenProdTable.VendorProductCode FROM ProductTable INNER JOIN (VendorList INNER JOIN ((Category INNER JOIN SpecialAddins ON Category.CategoryID=SpecialAddins.Category) INNER JOIN VenProdTable ON (SpecialAddins.VendorID=VenProdTable.VenID) AND (SpecialAddins.Category=VenProdTable.AddinCategory)) ON VendorList.VendorId=VenProdTable.VenID) ON ProductTable.ProdID=VenProdTable.ProdID WHERE VenID=7;"

Can you give me some insight? Is this statement not valid in OOoBasic?
Back to top
View user's profile Send private message
Villeroy
Super User
Super User


Joined: 04 Oct 2004
Posts: 10106
Location: Germany

PostPosted: Fri Sep 28, 2007 1:52 pm    Post subject: Reply with quote

I just wonder why you don't drag a datasource object into the spreadsheet. This used to work in OOo1.x as well (this thread started in 2003).

Quote:

Can you give me some insight? Is this statement not valid in OOoBasic?

The (in-)validity of your query has nothing to do with Basic. Try to get it working in the database document, using native SQL mode.
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 Macros and API All times are GMT - 8 Hours
Goto page 1, 2  Next
Page 1 of 2

 
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