Parametreret query fra Calc til Base

[Rislev]

Jeg er ved at blive lidt desperat i min kamp med Oo v2.0.

Jeg kan fint definere en fast query i base der bringer fx. en middelværdi over i en celle i calc. Men jeg vil gerne have at nogle af parametrene i denne query er afhængige af det kaldende regneark.
Med andre ord, eksempelvis: Jeg vil gerne have en middelværdi i $B$2 af de værdier i min database der ligger mellem datoerne i $A$1 og $A$2.
Hvis jeg skriver min SQL sætning som ------ WHERE ("DATE" > :Start AND "DATE" < :Slut) får jeg en dialog hvilket jeg ikke vil have. Er der nogen der prøvet dette før?

[lapont]

Jeg er ved at blive lidt desperat i min kamp med Oo v2.0.

Jeg kan fint definere en fast query i base der bringer fx. en middelværdi over i en celle i calc. Men jeg vil gerne have at nogle af parametrene i denne query er afhængige af det kaldende regneark.
Med andre ord, eksempelvis: Jeg vil gerne have en middelværdi i $B$2 af de værdier i min database der ligger mellem datoerne i $A$1 og $A$2.
Hvis jeg skriver min SQL sætning som ------ WHERE ("DATE" > :Start AND "DATE" < :Slut) får jeg en dialog hvilket jeg ikke vil have. Er der nogen der prøvet dette før?

Jeg har prøvet at skrive makroer, som henter nogle værdier fra celler i et regneark og bruger disse værdier i en SQL-sætning, som skrives i en makro. Resultatet af SQL-query'en sættes ind i andre celler i arket.
På denne måde kan du i hvert fald løse dit problem. Det kræver bare, at du skriver et par makroer.
Jeg ved ikke, om der findes en enklere metode, men hører gerne om en sådan, hvis nogen ved noget.

lapont

[lapont]

Jeg kan fint definere en fast query i base der bringer fx. en middelværdi over i en celle i calc. Men jeg vil gerne have at nogle af parametrene i denne query er afhængige af det kaldende regneark.
Med andre ord, eksempelvis: Jeg vil gerne have en middelværdi i $B$2 af de værdier i min database der ligger mellem datoerne i $A$1 og $A$2.
Hvis jeg skriver min SQL sætning som ------ WHERE ("DATE" > :Start AND "DATE" < :Slut) får jeg en dialog hvilket jeg ikke vil have. Er der nogen der prøvet dette før?

Mit tidligere svar på dette spørgsmål var lidt uhåndgribeligt, så her er en mere fyldestgørende forklaring:

Først et par antagelser: Din database hedder 'RislevsData' og den indeholder en tabel, som hedder 'transaktioner'. Denne tabel indeholder bl.a. felterne 'dato' og 'beloeb'.

Du skal skrive følgende makroer:

--------------------------------------------------------------------
Function SqlForbindelse() As Object
DatabaseContext = createUnoService("com.sun.star.sdb.DatabaseContext")
DataSource = DatabaseContext.getByName("RislevsData")
InteractionHandler = createUnoService("com.sun.star.sdb.InteractionHandler")
Connection = DataSource.ConnectWithCompletion(InteractionHandler)
SqlForbindelse = Connection.createStatement()
End Function

Function DatoOmform(dato AS Date) As String
aar$ = Year(dato)
mnd$ = Month(dato)
dag$ = Day(dato)
DatoOmform = aar$ + "-" + mnd$ + "-" + dag$
end Function

Function Gennemsnit(dag1 As Date, dag2 As Date) As Double
Statement = SqlForbindelse()
'Her konstrueres sql-sætningen som en streng
sql = "SELECT AVG(`beloeb` ) "
sql = sql + " FROM `transaktioner` "
sql = sql + " WHERE ( `dato` >= '" + DatoOmform(dag1)
sql = sql + "' AND `dato` <= '" + DatoOmform(dag2) + "')
'Afkommenter næste linie, hvis du vil se din sql-sætning på skærmen
'MsgBox(sql)
ResultSet = Statement.executeQuery(sql)
If Not IsNull(ResultSet) Then
while ResultSet.next
Gennemsnit=ResultSet.getString(1)
wend
End If
End Function
---------------------------------------------------------

På dit regneark har du datoerne dag1 og dag2 stående i felterne A1 og A2.
I feltet B2 skal du indsætte fomlen: =Gennemsnit(A1;A2)
Herefter vil middelværdien blive vist i feltet B2

Lidt supplerende forklaring:
Funktionen DatoOmform er nødvendig for mig fordi calc sender datoer som 'dd-mm-åååå' (også selv om feltet er formateret til at vise data anderledes!) men min MySQL database håndterer datoer som 'åååå-mm-dd'.
Vær opmærksom på anførselstegnene ' rundt om datoerne i sql-sætningen.
Funktionen SqlForbindelse() udfører al det hokus-pokus som skal til for at få et objekt, som kan udføre sql-forespørgsler. I mit eksempel forbinder den sig altid til databasen RislevsData. Hvis du gør databasenavnet til en parameter i funktionskaldet, kan funktionen bruges til alle databaser, som kan ses under 'rediger->vis-datakilder'

Hvis du ikke har brugt makroer før, så er her lidt vejledning:
Makroeditoren åbnes med:
Funktioner->Makroer->Administrer makroer->OpenOffice.org Basic
I venstre side af det vindue, som kommer frem, står bl.a. navnet på det aktive dokument. Under dette (foldes ud og ind med det lille plus/minus) findes 'Standard'. Klik på dette 'Standard' og derefter på knappen 'Ny(t)' til højre i vinduet. Accepter navnet 'Module1'. Nu kommer maroeditoren frem med en automatisk genereret SUB Main(). Slet den og tilføj i stedet de tre funktioner, som er vist ovenfor.

Lapont

[Rislev]

Tak. Ja det var lidt mere håndgribeligt så det vil jeg prøve. Jeg har gennemført "øvelsen" i Excel, men det irriterer mig for jeg var sikker på at det kunne lade sig gøre i Oo.

Jeg lader høre fra mig når jeg har prøvet det.

Atter en gang tak.

[Rislev]

Hej igen,

Nu har jeg prøvet funktionen og det virker jo fortræffeligt, selv via en ODBC link til en meget extern database. Man skal bare huske ODBCs "sjove" dataformat.
Du skriver at SqlForbindelse udfører alt hokus-pokus med forbindelsen til databasen. Ved du hvor man kan finde en forklaring på de enkelte funktioner? InteractionHandler, fx, lyder som sort snak for mig.

Mvh

[lapont]

Du skriver at SqlForbindelse udfører alt hokus-pokus med forbindelsen til databasen. Ved du hvor man kan finde en forklaring på de enkelte funktioner? InteractionHandler, fx, lyder som sort snak for mig.

Mvh

Jeg må tilstå, at jeg ikke har den dybe indsigt i de funktioner, som kaldes i den funktion, som jeg har kaldt SqlForbindelse. Det er en 'remse', der skal lires af for at få et objekt med en metode til udførelse af SQL-sætninger. Jeg har bare samlet dem i en enkelt funktion.

På siden http://docs.sun.com/app/docs/coll/1263.1 er der link til dokumentet: "StarOffice 8 Programming Guide for BASIC". Der kan du læse om Data Sources, Queries, Database Access og meget mere.
Det er det bedste, jeg kender.

Lapont