Datenbankverbindung per Makro

[sg1985]

Hallo zusammen,

ich habe bereits im Forum herumgesucht, aber es ist mir leider noch immer nicht ganz klar, wie ich ein Makro schreiben kann, dass die
Verbindung zur DB herstellt und auch noch Select Befehle ausführt.
Die Datenquelle ist bereits angemeldet im Open Office, da ich bis jetzt mit dem Datenpilot gearbeitet hab.

Hat jemand einen Link für mich oder noch besser eine Makrovorlage?

Danke schon mal für eure Hilfe,
lg

[komma4]

Code: Alles auswählen

' Datenbankverbindung
' Kontext holen
oDatenbankKontext = CreateUnoService ( "com.sun.star.sdb.DatabaseContext" )
' Datenquelle auswählen
' Name der registrierten Datenquelle 
oDatenquelle = oDatenbankKontext.getByName( "base_mysqlDB" )

' 
oDatenquelle.setLoginTimeout( 10 ) 
' Verbindung zur DB herstellen
' 
If Not oDatenquelle.IsPasswordRequired Then
	oVerbindung = oDatenquelle.getConnection( , )
Else
	oInteractionHandler = createUnoService( "com.sun.star.sdb.InteractionHandler" )
	oVerbindung = oDatenquelle.connectWithCompletion( oInteractionHandler ) 
End If 

' Verbindung nicht geklappt
If IsNull( oVerbindung ) Then 
   msgbox "keine Verbindung zur DB (user/passwort falsch?)"
   exit sub
End If

' Abfrage
sSQL = "SELECT * FROM t091_rechnung as r ;"

' Statement erzeugen
oStatement = oVerbindung.createStatement
' Abfrage absetzen
oResultSet = oStatement.executeQuery( sSQL )

' Ergebnis
While oResultSet.Next()
  sWert1 = oResultSet.getString(1)
[...]

Weitere Beispiele : siehe http://www.pitonyak.org/database/


Hilft das weiter?

[sg1985]

hat mir sehr geholfen, danke!

allerdings funktioniert die sql abfrage nicht, ich hab auch einfach ein "select * from tabelle" gemacht,
bekomm dann aber immer einen fehler wegen dem "while", dachte mir zuerst dass vielleicht ein "end while" fehlt,
aber das hat auch nichts gebracht.

hast du eine idee was da falsch sein könnte?

[sg1985]

Nachtrag:

der Fehler lautet "Unerwartetes Symbol: End Sub"

das "End Sub" steht gleich nach der Zeile "sWert1 = oResultSet.getString(1)"

[komma4]

...da fehlt das Ende der WHILE-Schleife for dem Ende des SUBs: WEnd

[sg1985]

danke, jetzt gehts. eine frage hätte ich noch:

ich lass mir nun das ergebnis der sql abfrage durch den beobachter im makro editor anzeigen.
was mich wundert ist, dass ich eigentlich ganze datensätze bzw mehrere spalten raus bekommen müsst da ich mit "select *" abfrage (also zb ID+Vorname+Nachname),
allersdings gibt er mir immer nur die ID aus, und das in zufälliger reihenfolge bzw manche doppelt.

was muss ich machen, dass mir alle vollständigen datensätze des ergebnisses der abfrage ausgegeben werden?

[komma4]

Das kann der Betrachter nicht.

Nehme Xray zur Untersuchung Deiner Objekte.


P.S.: Die Reihenfolge ist abhängig von der physischen Speicherung. Sortiere durch eine ORDER BY-Klausel in der SQL-Abfrage, wenn Du bswp. nach ID aufsteigend die Ausgabe möchtest.

[sg1985]

hm, das mit dem Anzeigen der Daten ist mir noch unklar:
Ich brauche das Ergebnis der Abfrage in einer calc Datei, bzw habe ich eine Schaltfläche durch die das Makro ausgeführt wird und sobald
man auf diese Schaltfläche klickt, sollte das Ergebnis des Select Befehls in der calc Datei erscheinen.

Ich weiß zwar wie man das Ergebnis einer Abfrage die man durch den SQL Editor von Open Office erstellt hat ins calc bekommt (durch den Datenpilot oder
die Datenansicht mittels F4), aber wie bekommt man das Ergebnis einer Abfrage in einem Makro in eine calc Tabelle?

lg

[komma4]

...indem Du jede Spalte eines Ergebnis-resultsets in eine Zelle einer Calc-Datei schreibst.

[komma4]

...etwas ausführlicher:



Das folgende Codebeispiel setzt eine Abfrage (gegen Oracle) ab und schreibt das Ergebnis in ein Datenarray und setzt dieses schlussendlich in eine neue Calc-Datei.

Code: Alles auswählen

Sub base_readOracle
' Java 1.6.0.1

GlobalScope.BasicLibraries.LoadLibrary( "Tools" )

' RowSet auf Oracle: NAMEN GROSS SCHREIBEN ! 
sSQL = " SELECT * FROM Tabelle ORDER BY tab_ID  "

' Kontext holen
Dim oDBKontext 
oDBKontext = CreateUnoService ( "com.sun.star.sdb.DatabaseContext" )

If IsNull( oDBKontext ) Then
	msgbox "kein DB Kontext" 
	exit sub
End If

' Datenquelle auswählen
' name of OOo registered data source
oDatenquelle = oDBKontext.getByName( "base_oracleDB" )
 ' Verbindung zur DB herstellen
If Not oDatenquelle.IsPasswordRequired Then
	oVerbindung = oDatenquelle.getConnection( , )
Else
	oInteractionHandler = createUnoService( "com.sun.star.sdb.InteractionHandler" )
	oVerbindung = oDatenquelle.connectWithCompletion( oInteractionHandler ) 
End If 


oRowSet = createUnoService("com.sun.star.sdb.RowSet")
oRowSet.ActiveConnection = oVerbindung
oRowSet.setPropertyValue( "Command", sSQL ) 
oRowSet.execute()


' wenn ungleich 0: Anzahl feststellen
If oRowSet.getPropertyValue( "RowCount" )  Then

' Drew Jensen Sat Aug 19, 2006 4:49 pm
' http://www.oooforum.org/forum/viewtopic.phtml?t=41470
	oRowSet.Next
    bkMark = oRowSet.getBookMark()
    oRowSet.Last
    oRowSet.moveToBookmark( bkMark )
Else
	Msgbox "Keine Zeilen erhalten  - beende das Makro"
	Exit Sub
End If

' Zeilen- und Spaltenanzahl
iAnzahlZeilen = oRowSet.getPropertyValue( "RowCount" )  
iAnzahlSpalten = oRowSet.Columns.getCount()

' __________________________________________________________________
' neues Calc Dokument
' im Hintergrund erstellen
' Bereich zum Füllen holen
oDeskNeu = _
 createUnoService( "com.sun.star.frame.Desktop" )

Dim OpenProperties(1) as new com.sun.star.beans.PropertyValue 
OpenProperties(0).Name = "Hidden"
OpenProperties(0).Value = True
 
sNeuURL = _
 "private:factory/scalc"
oNeuDok = _
 oDeskNeu.loadComponentFromURL( sNeuURL, "_blank" , 0 , OpenProperties() )
oBlatt = _
 oNeuDok.Sheets.getByIndex( 0 ) 

oBereich = _
 oBlatt.getCellRangeByPosition( 0, 0, iAnzahlSpalten - 1 , iAnzahlZeilen ) 
' __________________________________________________________________



ReDim aDatenzeile( iAnzahlSpalten - 1 )

oDaten = _
 oBereich.getDataArray()

' Spaltenbeschriftung ermitteln
for i = 0 to oRowSet.getColumns().getCount() - 1
 aDatenZeile( i ) = oRowSet.getColumns().getByIndex( i ).Name
next i 
' Spaltenüberschrift setzen
oDaten( 0 ) = aDatenZeile() 

ReDim aSpaltenTyp( iAnzahlSpalten - 1 ) as String
' Spaltentypen ermitteln
for i = 0 to oRowSet.getColumns.getCount() - 1
	aSpaltenTyp( i ) =  oRowSet.getColumns.getByIndex( i ).TypeName
next

' Datenzeilen besetzen
for zz = _
	1  to _
	UBound( oDaten() )  

	aDatenZeile() = oDaten( zz )
	' zz: ZählerZeilen

	' Spalten-/Zellen-Zähler
	for zc = _
		0 to _
		iAnzahlSpalten - 1 
		
'
		Select Case aSpaltenTyp( zc ) 
			Case "NUMBER" 
				aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
			Case "VARCHAR2"
				aDatenZeile( zc ) = oRowSet.getString( zc + 1 )
			CASE "DATE"				
				oDatum = _
					oRowSet.getTimestamp( zc + 1 )
				sDatum = _
					oDatum.Year & _
					"-" & _
					Format( oDatum.Month, "00" ) & _
					"-" & _
					Format( oDatum.Day, "00" ) & _
					" " & _
					Format( oDatum.Hours, "00" ) & _
					":" & _
					Format( oDatum.Minutes, "00" ) & _
					":" & _
					Format( oDatum.Seconds, "00" )
'				print "a" 
'				aDatenZeile( zc ) = oRowSet.getDate( zc + 1 )
				aDatenZeile( zc ) = oRowSet.getString( zc + 1 )			
		End Select

		
	next ' Zellen

'	xray aDatenZeile()

	' 
	oRowSet.next()
next ' Zeile

' Daten setzen
oBereich.setDataArray( oDaten() ) 
' __________________________________________________________________


' Dokument anzeigen 
oNeuDok.getCurrentController().getFrame().getContainerWindow().Visible =  TRUE 

End Sub

Damit kommst Du zurecht?

[sg1985]

ehrlich gesagt komm ich nicht gut zu Recht damit, es wirkt ein bisschen kompliziert und ich würde die Abfrage gerne per
MySQL absetzen, gibt es da keine einfachere Möglichkeit vom Makro aus auf die Zellen eines Calc Sheets zuzugreifen und die einfach zu
befüllen?

lg

[komma4]

es wirkt ein bisschen kompliziert

Ja, das tut es: es wirkt kompliziert.
Gehe die Anweisungen einzeln durch (lese die spärlichen Kommentare!)


Natürlich kannst Du auch eine einzelne Zelle setzen (Beispiele finden sich in der BASIC-Abteilung des Forums)....


Mein Beispiel sollte bei Dir laufen, wenn Du "nur" zwei Stellen anpasst: die SQL-Abfrage und den Namen der registrierten Datenquelle.
Vorteil des Makrobeispiels: dynamische Auswertung der Anzahl von Zeilen und Spalten, dynamisches Reagieren auf den Spalten-Datentyp, Erstellen des Dokuments mit Datenarray und im Hintergrund.
Das Arbeiten mit dem Datenarray beschleunigt den Vorgang bei vielen Daten (mehreren Dutzend/hunderte Datensätze).

[sg1985]

ich hab dein Makro ausprobiert mit meinem Select Befehl und erhalte als Ergebnis im calc Dokument aber leider nur die Namen der Spalten der Tabelle, aber keine
Datensätze.

Mein select Befehl schaut so aus: SELECT * from staff;
...und ich erhalte als Ergebnis eben nur die Namen der Spalten welche die Tabelle beinhaltet (s_id, s_name usw)

ein weiteres Problem (weswegen ich konkrete Zellen befüllen wollte) ist, dass es wenn es fertig ist, so ausschauen soll, dass ich auf einem Dokument
3 Listboxen habe, dort etwas selektiere, die Schaltfläche anklicke welche das Makro auslöst und dann im selben Dokument das Ergebnis erhalte (also darf das Ergebnis
erst ein paar Zellen unterhalb der Listbox erscheinen).
Aber davon bin ich momentan sowieso noch weit entfernt, da ich es nicht schaffe die Listboxen durch einen Select Befehl mit Personennamen zu befüllen

[komma4]

nur die Namen der Spalten der Tabelle, aber keine Datensätze.

Die Aufstellung der Datentypen der Spalten ist nicht vollständig, ergänze mal folgendermassen:

Code: Alles auswählen

          Select Case aSpaltenTyp( zc )

             Case "NUMBER", "INTEGER"
                aDatenZeile( zc ) = oRowSet.getInt( zc + 1 )
             Case "VARCHAR2" , "VARCHAR"

Klappt es nun?

[sg1985]

Ich habe es nun folgendermaßen gelöst:

Code: Alles auswählen

sub ListBox_fuellen

' Datenbankverbindung
' Kontext holen
oDatenbankKontext = CreateUnoService ( "com.sun.star.sdb.DatabaseContext" )
' Datenquelle auswählen
' Name der registrierten Datenquelle
oDatenquelle = oDatenbankKontext.getByName( "db_test3" )

'
oDatenquelle.setLoginTimeout( 10 )
' Verbindung zur DB herstellen
'
If Not oDatenquelle.IsPasswordRequired Then
   oVerbindung = oDatenquelle.getConnection("","")
Else
   oInteractionHandler = createUnoService( "com.sun.star.sdb.InteractionHandler" )
   oVerbindung = oDatenquelle.connectWithCompletion( oInteractionHandler )
End If

' Verbindung nicht geklappt
If IsNull( oVerbindung ) Then
   msgbox "keine Verbindung zur DB (user/passwort falsch?)"
   exit sub
End If


' Abfrage
sSQL = "SELECT s_lname from staff order by s_lname;"

' Statement erzeugen
oStatement = oVerbindung.createStatement
' Abfrage absetzen
oResultSet = oStatement.executeQuery( sSQL )

' Ergebnis
While oResultSet.Next()
  'msgbox oResultSet.getString(1)
  s = oResultSet.getString(1) '1. Spalte in Tabelle
 
  Dim oListBox as Object, aListe(), oForm as Object
  oForm = thisComponent.sheets(0).Drawpage.Forms(0)
  oListBox = oForm.getByName("ListBox")
 
   aListe = oListBox.StringItemList
   n = UBound(aListe())
   redim preserve aListe(n + 1)
   aListe (n+1) = s
   oListBox.StringItemList = aListe
end sub

in der while Schleife speicher ich alle Ergebnisse der Abfrage in die Listbox, wobei ich immer den bereits drin stehenden String
auslese und den neu dazu gekommenen anhänge.
Ich hab sonst keine Möglichkeit gefunden, nicht nur einen String des Ergebnisses der Abfrage rein zu speichern in die Listbox, sondern alle.

Allerdings habe ich bei dieser Lösung folgendes Problem:
Ich habe eine leere Listbox und klick auf den Button um das Makro auszuführen, er speichert mir wunderbar das Ergebnis in die Listbox. Ändert
sich aber nun in der Datenbank etwas, so muss ich das Makro mit dem Select Befehl erneut ausführen um den Inhalt der Listbox aktuell zu halten bzw neu zu
befüllen.
Da ich jedesmal vorm reinspeichern den Inhalt der Listbox auslese, und zusätzlich den neuen rein speicher, hab ich nun doppelte Ergebnisse drinnen stehen.

Ich hoffe mein Problem ist nachvollziehbar Kann mir da jemand helfen oder eine bessere Lösung sagen, um alle Strings des Ergebnisses der Abfrage
in die Listbox zu speichern?

lg