Parameter SQL Abfrage in Basic Makro

Datenbanklösungen mit AOO/LO

Moderator: Moderatoren

tobiman26
***
Beiträge: 63
Registriert: Mi, 20.01.2016 15:17
Kontaktdaten:

Parameter SQL Abfrage in Basic Makro

Beitrag von tobiman26 » Di, 26.07.2016 09:27

Hallo liebe Leute

Ich habe folgendes Problem:

Ich habe eine Kunden Datenbank in Base (ca. 1200 Kunden) in der Übersicht ich habe es Kundenverwaltung genannt kann der Inhalt mittels einer Filter Tabelle und einer Parameter Abfrage gefiltert werden. Nun möchte ich den gefilterten Inhalt per Makro in Calc exportieren.

Beim Suchen habe ich Folgendes Makro gefunden:

Code: Alles auswählen

Sub ExportData
   REM Speicherpfad
   sPath = "" 
   REM Dateiname
   sTitle = "Name_" & Date & ".ods"
   REM Name der Abfrage, Tabelle oder SQL-Statement
   'sQueryName = "meineTabelle" 
   sQueryName = "meineAbfrage"
   'sQueryName = "SELECT * FROM ""meineTabelle"""
   
   sURL = ConvertToURL(sPath & "/" & sTitle)
   If FileExists(sURL) Then
      i = MsgBox(">" & sTitle & "<" & Chr(13) & "Datei existiert bereits." & _
                  Chr(13) & Chr(13) & "Überschreiben?",52,"Hinweis")
      If i <> 6 Then
         Exit Sub
      End If
   End If
   oCon = thisComponent.Parent.CurrentController.ActiveConnection
   If oCon.Tables.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,0)
   ElseIf oCon.Queries.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,1)
   Else
      oPrepStatement = oCon.prepareCommand(sQueryName,2)
   End If
   oResult = oPrepStatement.executeQuery
   aColumns = oPrepStatement.Columns.ElementNames
   oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, Array())
   oSheet = oDoc.Sheets(0)
   nColumnCount = UBound(aColumns)
   Dim aLine(nColumnCount)
   Dim aData(0)
   aData(0) = aColumns
   nUpperBoundary = 0
   Do While oResult.Next
      nUpperBoundary = UBound(aData)+1
      ReDim Preserve aData(nUpperBoundary)
      ReDim aLine(nColumnCount)
      For i = 0 To nColumnCount
         aLine(i) = oResult.getString(i+1)
      Next i
      aData(nUpperBoundary) = aLine
   Loop
   oRange = oSheet.getCellRangeByPosition(0,0,nColumnCount,nUpperBoundary)
   oRange.setFormulaArray(aData)
   Dim args(0) as New com.sun.star.beans.PropertyValue
   args(0).Name = "Overwrite"
   args(0).Value = True
   oDoc.StoreAsURL(sURL,args)
   oDoc.close(False)
End Sub

Funktioniert super bei Statischen Abfragen aber sobald es Dynamisch werden soll gibt es Probleme.

Was ich bereits Versucht habe :

Code: Alles auswählen

Abfrage direkt Ansprechen und die Parameter mit liefern
	oform = ThisComponent.drawpage.Forms.Filter
	qTelefon = oform.getString(oform.findcolumn("F_Telefon"))
	qFirma = oform.getString(oform.findcolumn("F_Firma"))
	qName = oform.getString(oform.findcolumn("F_Name"))
	sQueryName = "Parameterabfrage"

führt die Abfrage durch und exportiert aber alle Kunden also setzt er die Parameter auf Null

mein zweiter Ansatz war das SQL Statement hart in den Code zuschreiben also

Code: Alles auswählen

sQueryName = SELECT "Kunde_q"."ID" AS "MAINID", "Firmen"."Branche", "Firmen"."Firma", "Kunde_q"."Titel", "Kunde_q"."Name", "Kunde_q"."Vorname", "Kunde_q"."Email", "Kunde_q"."Ort", "Kunde_q"."Plz", "Kunde_q"."Anschrift","Kunde_q"."Firmennummer" FROM "Firmen", "Kunde_q" WHERE "Firmen"."Firmennummer" = "Kunde_q"."Firmennummer" AND ( LOWER ( "Telefon" ) LIKE LOWER ( '%' || :qTelefon || '%' ) AND LOWER ( "Firma" ) LIKE LOWER ( :qFirma || '%' ) AND LOWER ( "Name" ) LIKE LOWER ( :qName || '%' ) OR LOWER ( "Telefon" ) LIKE LOWER ( '%' || :qTelefon || '%' ) AND LOWER ( "Firma" ) LIKE LOWER ( :qFirma || '%' ) AND :qName IS NULL OR LOWER ( "Firma" ) LIKE LOWER ( :qFirma || '%' ) AND LOWER ( "Name" ) LIKE LOWER ( :qName || '%' ) AND :qTelefon IS NULL OR LOWER ( "Firma" ) LIKE LOWER ( :qFirma || '%' ) AND :qName IS NULL AND :qTelefon IS NULL OR LOWER ( "Telefon" ) LIKE LOWER ( '%' || :qTelefon || '%' ) AND LOWER ( "Name" ) LIKE LOWER ( :qName || '%' ) AND :qFirma IS NULL OR LOWER ( "Telefon" ) LIKE LOWER ( '%' || :qTelefon || '%' ) AND :qName IS NULL AND :qFirma IS NULL OR LOWER ( "Name" ) LIKE LOWER ( :qName || '%' ) AND :qTelefon IS NULL AND :qFirma IS NULL OR :qName IS NULL AND :qTelefon IS NULL AND :qFirma IS NULL )
Liefert mir leider eine Syntax error.
Ich hoffe ihr versteht auf was ich hinaus möchte und könnt mir helfen
Danke im Voraus
Mfg Tobias 8)

Windows 7 64bit + AOO 4.1.2 + LO 5.0.0.0
OpenSUSE 42.1 32 bit + LO 5.0.2.2

RobertG
*******
Beiträge: 1734
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von RobertG » Di, 26.07.2016 10:33

Hallo Tobias,

der Code muss, wie es bei Text üblich ist, in Anführungszeichen stehen:

Code: Alles auswählen

qTelefon = oform.getString(oform.findcolumn("F_Telefon"))
sQueryName = "SELECT ""Kunde_q"".""ID"", ""Firmen"".""Branche"",  ...  FROM ""Firmen"", ""Kunde_q"" WHERE ""Firmen"".""Firmennummer"" = ""Kunde_q"".""Firmennummer"" AND ( LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || "+qTelefon+" || '%' ) ... "
qTelefon ist eine Variable. Da muss die Variable eingetragen werden, die vorher aus dem Formular ausgelesen wird. Deswegen wird zwischendurch die Texteingabe für den SQL-Code unterbrochen.

Eine Frage taucht beim Lesen auf: "Kunde_q" ist doch hoffentlich eine Tabelle, nicht eine Abfrage? Abfragen kennt die Datenbank nicht.

Eine weitere Frage: Warum liest Du nicht direkt in der Abfrage statt der Parameter den Wert aus der Tabelle "Filter" ein? Statt qTelefon also

Code: Alles auswählen

SELECT "F_Telefon" FROM "Filter"
wobei der Datensatz natürlich begrenzt werden muss auf einen Datensatz, also z.B.

Code: Alles auswählen

SELECT "F_Telefon" FROM "Filter" WHERE "ID" = TRUE
Das ergäbe dann bei obigem Code:

Code: Alles auswählen

sQueryName = "SELECT ""Kunde_q"".""ID"", ""Firmen"".""Branche"",  ...  FROM ""Firmen"", ""Kunde_q"" WHERE ""Firmen"".""Firmennummer"" = ""Kunde_q"".""Firmennummer"" AND ( LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = TRUE) || '%' ) ... "
... und das bräuchtest Du auch gar nicht als Code direkt in das Makro einzufügen. Du könntest so eine Abfrage einfach direkt aufrufen.

Gruß

Robert

tobiman26
***
Beiträge: 63
Registriert: Mi, 20.01.2016 15:17
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von tobiman26 » Di, 26.07.2016 11:15

Hallo Robert danke für die schnelle Antwort

Ja anfrage direkt Ansprechen habe ich versucht steht auch oben schon mit drin Problem war nur das Parameter nicht berücksichtigt wurden sondern die Abfrage ausgeführt wurde als wäre qfirma qname und qtelefon IS NULL

Ja das SQL Statement bezieht sich auf Tabelle Kunde_q und Tabelle Firma
Mfg Tobias 8)

Windows 7 64bit + AOO 4.1.2 + LO 5.0.0.0
OpenSUSE 42.1 32 bit + LO 5.0.2.2

tobiman26
***
Beiträge: 63
Registriert: Mi, 20.01.2016 15:17
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von tobiman26 » Di, 26.07.2016 11:45

Ich habe gerade mal alles als Text gemacht wie du gesagt hast verursacht aber eine SQL Exception

Code: Alles auswählen

Sub ExportDataFilter
   REM Speicherpfad
   sPath = "C:\Users\UME_03\Desktop\UMEDatabase\Datenbank" 
   REM Dateiname
   Dim qTitel as String
   qTitel = InputBox("Bitte Namen vergeben", "Speichern","")
   sTitle = qTitle & Date & ".ods"
   REM Name der Abfrage, Tabelle oder SQL-Statement
	oform = ThisComponent.drawpage.Forms.Filter
	qTelefon = oform.getString(oform.findcolumn("F_Telefon"))
	qFirma = oform.getString(oform.findcolumn("F_Firma"))
	qName = oform.getString(oform.findcolumn("F_Name"))
	
   sQueryName = "SELECT ""Kunde_q"".""ID"" AS ""MAINID"", ""Firmen"".""Branche"", ""Firmen"".""Firma"", ""Kunde_q"".""Titel"", ""Kunde_q"".""Name"", ""Kunde_q"".""Vorname"", ""Kunde_q"".""Email"", ""Kunde_q"".""Ort"", ""Kunde_q"".""Plz"", ""Kunde_q"".""Anschrift"",""Kunde_q"".""Firmennummer"" FROM ""Firmen"", ""Kunde_q"" WHERE ""Firmen"".""Firmennummer"" = ""Kunde_q"".""Firmennummer"" AND ( LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || "+qTelefon+" || '%' ) AND LOWER ( ""Firma"" ) LIKE LOWER ( "+qFirma+" || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( "+qName+" || '%' ) OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || "+qTelefon+" || '%' ) AND LOWER ( ""Firma"" ) LIKE LOWER ( "+qFirma+" || '%' ) AND "+qName+" IS NULL OR LOWER ( ""Firma"" ) LIKE LOWER ( "+qFirma+" || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( "+qName+" || '%' ) AND "+qTelefon+" IS NULL OR LOWER ( ""Firma"") LIKE LOWER ( "+qFirma+" || '%' ) AND "+qName+" IS NULL AND "+qTelefon+" IS NULL OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || "+qTelefon+" || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( "+qName+" || '%' ) AND "+qFirma+" IS NULL OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || "+qTelefon+" || '%' ) AND "+qName+" IS NULL AND "+qFirma+" IS NULL OR LOWER ( ""Name"" ) LIKE LOWER ( "+qName+" || '%' ) AND "+qTelefon+" IS NULL AND "+qFirma+" IS NULL OR "+qName+" IS NULL AND "+qTelefon+" IS NULL AND "+qFirma+" IS NULL ))
   
   sURL = ConvertToURL(sPath & "/" & sTitle)
   If FileExists(sURL) Then
      i = MsgBox(">" & sTitle & "<" & Chr(13) & "Datei existiert bereits." & _
                  Chr(13) & Chr(13) & "Überschreiben?",52,"Hinweis")
      If i <> 6 Then
         Exit Sub
      End If
   End If
   oCon = thisComponent.Parent.CurrentController.ActiveConnection
   If oCon.Tables.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,0)
   ElseIf oCon.Queries.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,1)
   Else
      oPrepStatement = oCon.prepareCommand(sQueryName,2)
   End If
   oResult = oPrepStatement.executeQuery
   aColumns = oPrepStatement.Columns.ElementNames
   oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, Array())
   oSheet = oDoc.Sheets(0)
   nColumnCount = UBound(aColumns)
   Dim aLine(nColumnCount)
   Dim aData(0)
   aData(0) = aColumns
   nUpperBoundary = 0
   Do While oResult.Next
      nUpperBoundary = UBound(aData)+1
      ReDim Preserve aData(nUpperBoundary)
      ReDim aLine(nColumnCount)
      For i = 0 To nColumnCount
         aLine(i) = oResult.getString(i+1)
      Next i
      aData(nUpperBoundary) = aLine
   Loop
   oRange = oSheet.getCellRangeByPosition(0,0,nColumnCount,nUpperBoundary)
   oRange.setFormulaArray(aData)
   Dim args(0) as New com.sun.star.beans.PropertyValue
   args(0).Name = "Overwrite"
   args(0).Value = True
   oDoc.StoreAsURL(sURL,args)
   oDoc.close(False)
End Sub
Dateianhänge
SQL.JPG
SQL.JPG (68.78 KiB) 3743 mal betrachtet
Mfg Tobias 8)

Windows 7 64bit + AOO 4.1.2 + LO 5.0.0.0
OpenSUSE 42.1 32 bit + LO 5.0.2.2

tobiman26
***
Beiträge: 63
Registriert: Mi, 20.01.2016 15:17
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von tobiman26 » Di, 26.07.2016 13:57

Robert danke für deine Hilfe zur Selbsthilfe hab jetzt durch Probieren selbst eine Funktionierende Lösung gefunden

Hier die Lösung vielleicht hilft es jemand anderem weiter

Code: Alles auswählen

Sub ExportDataFilter
   REM Speicherpfad   
   sPath = "Pfad"
   REM Dateiname
   'Dim sTitel as String   
   sTitle =  InputBox("Bitte Namen vergeben", "Speichern","")  &  Date & ".ods"
   REM Name der Abfrage, Tabelle oder SQL-Statement
	oform = ThisComponent.drawpage.Forms.Filter
	qTelefon = oform.getString(oform.findcolumn("F_Telefon"))
	qFirma = oform.getString(oform.findcolumn("F_Firma"))
	qName = oform.getString(oform.findcolumn("F_Name"))

   sQueryName ="SELECT ""Kunde_q"".""ID"" AS ""MAINID"", ""Firmen"".""Branche"", ""Firmen"".""Firma"", ""Kunde_q"".""Titel"", ""Kunde_q"".""Name"", ""Kunde_q"".""Vorname"", ""Kunde_q"".""Email"", ""Kunde_q"".""Ort"", ""Kunde_q"".""Plz"", ""Kunde_q"".""Anschrift"",""Kunde_q"".""Firmennummer"" FROM ""Firmen"", ""Kunde_q"" WHERE ""Firmen"".""Firmennummer"" = ""Kunde_q"".""Firmennummer"" AND ( LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND LOWER ( ""Firma"" ) LIKE LOWER ((SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND LOWER ( ""Firma"" ) LIKE LOWER ( (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR LOWER ( ""Firma"" ) LIKE LOWER ( (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR LOWER ( ""Firma"") LIKE LOWER ( (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND(SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL AND (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND LOWER ( ""Name"" ) LIKE LOWER ( (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL AND (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR LOWER ( ""Name"" ) LIKE LOWER ( (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') || '%' ) AND (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL AND (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL OR (SELECT ""F_Name"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL AND (SELECT ""F_Telefon"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL AND (SELECT ""F_Firma"" FROM ""Filter"" WHERE ""ID"" = '0') IS NULL )"
   
   sURL = ConvertToURL(sPath & "/" & sTitle)
   If FileExists(sURL) Then
      i = MsgBox(">" & sTitle & "<" & Chr(13) & "Datei existiert bereits." & _
                  Chr(13) & Chr(13) & "Überschreiben?",52,"Hinweis")
      If i <> 6 Then
         Exit Sub
      End If
   End If
   oCon = thisComponent.Parent.CurrentController.ActiveConnection
   If oCon.Tables.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,0)
   ElseIf oCon.Queries.hasByName(sQueryName) Then
      oPrepStatement = oCon.prepareCommand(sQueryName,1)
   Else
      oPrepStatement = oCon.prepareCommand(sQueryName,2)
   End If
   oResult = oPrepStatement.executeQuery
   aColumns = oPrepStatement.Columns.ElementNames
   oDoc = StarDesktop.loadComponentFromURL("private:factory/scalc","_blank", 0, Array())
   oSheet = oDoc.Sheets(0)
   nColumnCount = UBound(aColumns)
   Dim aLine(nColumnCount)
   Dim aData(0)
   aData(0) = aColumns
   nUpperBoundary = 0
   Do While oResult.Next
      nUpperBoundary = UBound(aData)+1
      ReDim Preserve aData(nUpperBoundary)
      ReDim aLine(nColumnCount)
      For i = 0 To nColumnCount
         aLine(i) = oResult.getString(i+1)
      Next i
      aData(nUpperBoundary) = aLine
   Loop
   oRange = oSheet.getCellRangeByPosition(0,0,nColumnCount,nUpperBoundary)
   oRange.setFormulaArray(aData)
   Dim args(0) as New com.sun.star.beans.PropertyValue
   args(0).Name = "Overwrite"
   args(0).Value = True
   oDoc.StoreAsURL(sURL,args)
   oDoc.close(False)
End Sub
Mfg Tobias 8)

Windows 7 64bit + AOO 4.1.2 + LO 5.0.0.0
OpenSUSE 42.1 32 bit + LO 5.0.2.2

RobertG
*******
Beiträge: 1734
Registriert: Fr, 13.04.2012 19:28
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von RobertG » Di, 26.07.2016 15:15

Hallo Tobias,

in meinem Code war ein Fehler enthalten:

Code: Alles auswählen

qTelefon = oform.getString(oform.findcolumn("F_Telefon"))
sQueryName = "SELECT ""Kunde_q"".""ID"", ""Firmen"".""Branche"",  ...  FROM ""Firmen"", ""Kunde_q"" WHERE ""Firmen"".""Firmennummer"" = ""Kunde_q"".""Firmennummer"" AND ( LOWER ( ""Telefon"" ) LIKE LOWER ( '%' || '"+qTelefon+"' || '%' ) ... "
Die Hochkommata für den Text aus qTelefon fehlten.

In Deinem daraus abgeleiteten Code sind außerdem mehrere '%' und || unten durchgerutscht.

Die Abfrage, die Du jetzt mühsam im Basic-Code da stehen hast, hättest Du natürlich auch direkt als Base-Abfrage erstellen können.

Gruß

Robert

tobiman26
***
Beiträge: 63
Registriert: Mi, 20.01.2016 15:17
Kontaktdaten:

Re: Parameter SQL Abfrage in Basic Makro

Beitrag von tobiman26 » Di, 26.07.2016 15:40

Naja jetz Funktioniert es das ist die Hauptsache wie hässlich das hintenrum aussieht ist dem Nutzer doch egal
Mfg Tobias 8)

Windows 7 64bit + AOO 4.1.2 + LO 5.0.0.0
OpenSUSE 42.1 32 bit + LO 5.0.2.2

Antworten