| View previous topic :: View next topic |
| Author |
Message |
wenze
General User
Joined: 06 Aug 2003
Posts: 7
|
 Posted: Wed Aug 06, 2003 2:59 am Post subject: Get data from database and fill in calc??? |
 |
|
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 |
|
 |
openmind
OOo Enthusiast
Joined: 28 Jun 2003
Posts: 106
Location: Switzerland
|
| Posted: Wed Aug 06, 2003 4:48 am Post subject: |
|
|
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 |
|
|
wenze
General User
Joined: 06 Aug 2003
Posts: 7
|
| Posted: Wed Aug 06, 2003 5:55 am Post subject: just beginning |
|
|
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 |
|
|
openmind
OOo Enthusiast
Joined: 28 Jun 2003
Posts: 106
Location: Switzerland
|
| Posted: Wed Aug 06, 2003 8:24 am Post subject: |
|
|
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 |
|
|
wenze
General User
Joined: 06 Aug 2003
Posts: 7
|
| Posted: Wed Aug 06, 2003 9:17 am Post subject: |
|
|
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 |
|
|
Guest
|
| Posted: Wed Aug 06, 2003 2:42 pm Post subject: |
|
|
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
Joined: 28 Jun 2003
Posts: 106
Location: Switzerland
|
| Posted: Wed Aug 06, 2003 2:45 pm Post subject: |
|
|
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 
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 ... 
(no no, don't believe ) |
|
| Back to top |
|
|
wenze
General User
Joined: 06 Aug 2003
Posts: 7
|
| Posted: Thu Aug 07, 2003 5:35 am Post subject: |
|
|
Thanks all, i am testing later.  |
|
| Back to top |
|
|
openmind
OOo Enthusiast
Joined: 28 Jun 2003
Posts: 106
Location: Switzerland
|
| Posted: Fri Aug 08, 2003 4:44 am Post subject: |
|
|
the example above can't work:
include this line before the loop:
| Code: | | sheet = thisComponent.currentSelection.getSpreadsheet() |
|
|
| Back to top |
|
|
wenze
General User
Joined: 06 Aug 2003
Posts: 7
|
| Posted: Mon Aug 11, 2003 12:58 am Post subject: |
|
|
   It's work !
Thanks openmind |
|
| Back to top |
|
|
OOoBasicNewbie
General User
Joined: 27 Sep 2007
Posts: 8
|
| Posted: Thu Sep 27, 2007 9:04 am Post subject: Help |
|
|
| 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 |
|
|
Mark B
Super User
Joined: 16 Feb 2007
Posts: 852
Location: Lincolnshire, UK
|
|
| Back to top |
|
|
OOoBasicNewbie
General User
Joined: 27 Sep 2007
Posts: 8
|
| Posted: Fri Sep 28, 2007 6:52 am Post subject: |
|
|
| Thanks Mark. Your book may prove to be the biggest help I could ask for. I WILL be purchasing your book. |
|
| Back to top |
|
|
OOoBasicNewbie
General User
Joined: 27 Sep 2007
Posts: 8
|
| Posted: Fri Sep 28, 2007 12:32 pm Post subject: |
|
|
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 |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Fri Sep 28, 2007 1:52 pm Post subject: |
|
|
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 |
|
|
|
