OpenOffice.info

Code: Alles auswählen

'Annäherungssuche an die "Start-PLZ" (optimale Einstellung: 500er-Schritte bei 50.000 Einträgen/500ms)
Sub precisionCount()
	For i = 500 To 52000
		oResultPLZ.absolute(i)
		If textGeoPLZ.text >= oResultPLZ.getString(1) Then
			nPrecisionCount = i
		Else Exit Sub
		End If
		i = i + 499
	Next i
End Sub

Code: Alles auswählen

oStatement = oConnection.createStatement
sSQL = "UPDATE meineTabelle SET meineSpalte = 'neuerWert'"
REM oder auch sSql = "INSERT INTO meineTabelle (""meineSpalte1"",""meineSpalte2"") VALUES ('Wert1','Wert2')"
oStatement.executeUpdate(sSQL)

Code: Alles auswählen

RowSet = createUnoService("com.sun.star.sdb.RowSet")
RowSet.DataSourceName = "geoPersonality"
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
sSQLCommand = "SELECT Unternehmen, Zusatz, Anschrift, PLZ, Ort, Land, Bundesland, Geschlecht, Titel, Vorname, Nachname, "
sSQLCommand = sSQLCommand & "Position, Tel1, Tel2, Tel3, Fax, eMail, Web, Tags, Anmerkungen, Erstellungsdatum, Änderungsdatum "
sSQLCommand = sSQLCommand & "FROM kvpm.kvpm_adresses"
RowSet.Command = sSQLCommand
RowSet.Execute
RowSet.Next
nColumnIndex = RowSet.findColumn("Anmerkungen") REM Spaltenindex der Spalte "Anmerkungen" finden
RowSet.updateString(nColumnIndex,"meineAnmerkung") REM Den String des RowSet an der aktuellen Cursorposition in der Spalte "Anmerkungen" mit "meineAnmerkung" überschreiben
RowSet.updateRow REM Die Änderung in die Datenbank schreiben

Code: Alles auswählen

oResultGeo.Execute
oResultGeo.next()
(...)
Do While oResultGeo.Next
(...)

Code: Alles auswählen

Function CalculateDistance(nGeoLat as Double, nGeoLon as Double) as Integer
	On Error Resume Next
	Dim F as Double, G as Double, L as Double
	Dim S as Double, C as Double, W as Double
	Dim D as Double, R as Double
	Dim H1 as Double, H2 as Double

	F = (nLatitudeStart+nGeoLat) / 2
	G = (nLatitudeStart-nGeoLat) / 2
	If G = 0 Then G = 0.000000000001
	L = (nLongitudeStart-nGeoLon) / 2
	If L = 0 Then L = 0.000000000001

	S = (sin(G))^2 * (cos(l))^2 + (cos(F))^2 * (sin(l))^2
	C = (cos(G))^2 * (cos(l))^2 + (sin(F))^2 * (sin(l))^2
	W = ArcTan((sqr(S/C)))
	D = 2 * W * constA
	R = Sqr(S*C) / W
	H1 = (3 * R - 1) / (2 * C)
	H2 = (3 * R + 1) / (2 * S)
	S = D * (1 + constF*H1*(sin(F))^2 * (cos(G))^2 - constF *H2 * (cos(F))^2 * (sin(G))^2)
	
	CalculateDistance = S
End Function

Function ArcTan(x as Double) as Double
	ArcTan = (x + 0.43157974*x^3)/(1 + 0.76443945*x^2 + 0.05831938*x^4)
End Function

Code: Alles auswählen

OPTION EXPLICIT

Sub geoCoords()
	Dim nGeoLatStart, nGeoLonStart, nGeoLatTarget, nGeoLonTarget as Double
	Dim nLatitudeStart, nLongitudeStart, nLatitudeTarget, nLongitudeTarget as Double
	Dim nDistance, nMaxDistance
	Dim sEntry, sListSpace
	Dim startTime, endTime, countTime, nHits
	
	startTime = GetSystemTicks 'Abrufen der Systemzeit für den Funktions-Counter
	nHits = 0
	nMaxDistance = 6000
	
	oResultGeo.Command = "SELECT Unternehmen, Zusatz, Anschrift, PLZ, Ort, Land, Bundesland, Latitude, Longitude, Geschlecht, Titel, Vorname, Nachname, Position, Tel1, Tel2, Tel3, Fax, eMail, Web, Tags, Anmerkungen, Erstellungsdatum, Änderungsdatum FROM geopersonality.adresses"
	oResultGeo.Execute
	oResultGeo.next()
	
	nGeoLatStart = 50.11222 'Frankfurt
	nGeoLonStart = 08.68194
	'nGeoLatTarget = 52.52222 'Berlin
	'nGeoLonTarget = 13.29750
	
	nLatitudeStart = nGeoLatStart / 180 * Pi
	nLongitudeStart = nGeoLonStart / 180 * Pi
	
	Do While oResultGeo.Next
		If oResultGeo.getDouble(8) <> 0 Then 'Wenn Feld "PLZ" nicht leer,...
			'... Abrufen der Koordinaten aus dem ResultSet
			nGeoLatTarget = oResultGeo.getDouble(8)
			nGeoLonTarget = oResultGeo.getDouble(9)
			'Vorbereitung der Koordinaten für die Arkuskosinus-Funktion
			nLatitudeTarget = nGeoLatTarget / 180 * Pi
			nLongitudeTarget = nGeoLonTarget / 180 * Pi
			nDistance = ArcCos(Sin(nLatitudeStart) * Sin(nLatitudeTarget) + Cos(nLatitudeStart) * Cos(nLatitudeTarget) * Cos(nLongitudeTarget - nLongitudeStart)) * 6378.137
			If nDistance <= nMaxDistance Then
				nDistance = Left(nDistance, Instr(nDistance,",") + 1) 'Die Entfernungsangabe (nDistance) bis auf eine Stelle nach dem Komma abschneiden
				'Vervollständigung von "nDistance" mit Leerzeichen, um in der Listbox eine gleichförmige Ansicht in Spalten zu gewährleisten
				If Len(nDistance) = 3 Then
					sListSpace = "      "
				ElseIf Len(nDistance) = 4 Then
					sListSpace = "    "
				ElseIf Len(nDistance) = 5 Then
					sListSpace = "  "
				End If
				
				sEntry = "[→" & sListSpace & nDistance & " Km] " & oResultGeo.getString(4) & " " & oResultGeo.getString(5)
				If oResultGeo.getString(19) <> "" Then sEntry = sEntry & "    → " & oResultGeo.getString(19)
				
				'Übertragung in das Listbox-Element
				listGeoResults.additem(sEntry, 0)
				
				nHits = nHits + 1 'Aktualisierung der Ergebnis-Anzeige
			End If
		End If
	Loop
	
	'listGeoResults.removeitems(0,1) 'Einen Eintrag ab Eintrag 1(0) löschen oder...
	'EmptyListBox(listGeoResults)

	endTime = GetSystemTicks 'Erneutes Abrufen der Systemzeit für den Funktions-Counter
	countTime = (endTime - startTime)/1000 'Differenz zwischen den Counter-Zeiten berechnen
	'If Instr(countTime,"-") > 0 Then
	'	countTime = replaceString(countTime,"",",")
	'	countTime = replaceString(countTime,"","E")
	'End If
	textGeoInfo.text = nHits & " Ergebnisse (" & countTime & " Sekunden)"
End Sub

'Experimentelle Arkussinus-Funktion für OpenOffice.org-Basic
Function ArcSin(x)
	Dim nNumer, nDenom, nFrac, nResult, nNewTerm
	nNumer = 1
	nDenom = 2
	nFrac = 1
	nResult = x

	For i = 1 To 100
		nFrac = nFrac * nNumer / nDenom
		nNumer = nDenom + 1
		nDenom = nNumer + 1

		nNewTerm = nFrac * ((x^nNumer)/nNumer)
		nResult = nResult + nNewTerm

		If nNewTerm < 1.0E-60 Then
			Exit For
		EndIf
	Next i

	ArcSin = nResult
End Function

'Experimentelle Arkuskosinus-Funktion für OpenOffice.org-Basic
Function ArcCos(x)
	ArcCos = ArcSin(Sqr(1 - x^2))
End Function

Code: Alles auswählen

Sub dbOpen()

Dim sSQLCommand as String
RowSet = createUnoService("com.sun.star.sdb.RowSet")
RowSet.DataSourceName = "geoPersonality"
RowSet.CommandType = com.sun.star.sdb.CommandType.COMMAND
sSQLCommand = "SELECT Unternehmen, Zusatz, Anschrift, PLZ, Ort, Land, Bundesland, Geschlecht, Titel, Vorname, Nachname, "
sSQLCommand = sSQLCommand & "Position, Tel1, Tel2, Tel3, Fax, eMail, Web, Tags, Anmerkungen, Erstellungsdatum, Änderungsdatum "
sSQLCommand = sSQLCommand & "FROM kvpm.kvpm_adresses"
RowSet.Command = sSQLCommand
RowSet.Execute
If RowSet.Last Then
	resultRefresh	
End If

End Sub

Code: Alles auswählen

ResultSet = Statement.executeQuery(...)
If Not IsNull(ResultSet) Then (...)

Code: Alles auswählen

OPTION EXPLICIT

'Deklarationen für die MySQL-Datenbankanbindung
Public DatabaseContext As Object
Public DataSource As Object
Public Connection As Object
Public InteractionHandler as Object
Public Statement As Object
Public ResultSet As Object

Sub dbOpen()
	'... Initialisierung des Datenbankobjektes und Herstellen der MySQL-Verbindung
	DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
	DataSource = DatabaseContext.getByName("geoPersonality")
	
	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()
	Statement.ResultSetConcurrency = com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE
	Statement.setPropertyValue("ResultSetConcurrency", 1008) 
	Statement.ResultSetType = com.sun.star.sdbc.ResultSetType.SCROLL_SENSITIVE
	ResultSet = Statement.executeQuery("SELECT Unternehmen, Zusatz, Anschrift, PLZ, Ort, Land, Bundesland, Geschlecht, Titel, Vorname, Nachname, Position, Tel1, Tel2, Tel3, Fax, eMail, Web, Tags, Anmerkungen, Erstellungsdatum, Änderungsdatum FROM kvpm.kvpm_adresses")

	If Not IsNull(ResultSet) Then
		ResultSet.last()
		call resultRefresh()
	End If
End Sub

Sub dbFirst()
	ResultSet.first()
	call resultRefresh()
End Sub

Sub dbPrevious()
	If ResultSet.isFirst() = 0 Then
		ResultSet.previous()
		call resultRefresh()
	End If
End Sub

Sub dbSave()
	ResultSet.getString(1, txt_zeitung.text)
	ResultSet.getString(2, txt_verlag.text)
	ResultSet.getString(3, txt_anschrift.text)
	ResultSet.getString(4, txt_plz.text)
	ResultSet.getString(5, txt_ort.text)
	ResultSet.getString(6, txt_land.text)
	ResultSet.getString(7, txt_bundesland.text)
	ResultSet.getString(8, txt_geschlecht.text)
	ResultSet.getString(9, txt_titel.text)
	ResultSet.getString(10, txt_kontaktperson.text)
	ResultSet.getString(11, txt_kontaktperson2.text)
	ResultSet.getString(12, txt_position.text)
	ResultSet.getString(13, txt_telefon.text)
	ResultSet.getString(14, txt_tel2.text)
	
	ResultSet.getString(16, txt_fax.text)
	ResultSet.getString(17, txt_email.text)
	ResultSet.getString(18, txt_web.text)
	ResultSet.getString(19, txt_tags.text)
	ResultSet.getString(20, txt_anmerkungen.text)
	
	ResultSet.updateRow()
End Sub

Sub dbNext()
	If ResultSet.isLast() = 0 Then
		ResultSet.next()
		call resultRefresh()
	End If
End Sub

Sub dbWarning_del()
	If MsgBox("Eintrag wirklich löschen?",16 + 1,"Datenbank-Meldung...") = 1 Then
		call dbDelete()
	end if
End Sub

Sub dbDelete()
	
	MsgBox("Datensatz wurde gelöscht.",64,"Datenbank-Meldung...")
End Sub

Sub resultRefresh()
	str_zeitung = ResultSet.getString(1)
	str_verlag = ResultSet.getString(2)
	str_anschrift = ResultSet.getString(3)
	str_plz = ResultSet.getString(4)
	str_ort = ResultSet.getString(5)
	str_land = ResultSet.getString(6)
	str_bundesland = ResultSet.getString(7)
	str_geschlecht = ResultSet.getString(8)
	str_titel = ResultSet.getString(9)
	str_kontaktperson = ResultSet.getString(10)
	str_kontaktperson2 = ResultSet.getString(11)
	str_position = ResultSet.getString(12)
	str_telefon = ResultSet.getString(13)
	str_tel2 = ResultSet.getString(14)
	
	str_fax = ResultSet.getString(16)
	str_email = ResultSet.getString(17)
	str_web = ResultSet.getString(18)
	str_tags = ResultSet.getString(19)
	str_anmerkungen = ResultSet.getString(20)
	
	call actuallyText()
End Sub