Android Tutorial SQL Tutorial

[klaus]

Dieses Tutorial behandelt die SQL Library und ihre Nutzung mit Basic4android.
Es gibt viele allgemeine SQL-Tutorials, die die eigentliche SQL Sprache behandeln. Wenn Sie nicht mit SQL vertraut sind, empfehlen wir, mit einem Lernprogramm wie diesem zu starten.
SQL Introduction leider in Englisch.

Der Sourcecode des Beispielprogramms befindet sich am Ende.
Das Englische Originaltutorial.

Android nutzt SQLite, das eine Open-Source-SQL-Implementierung ist.
Jede Implementierung hat einige Nuancen. Die beiden folgenden Links behandeln wichtige Informationen zu SQLite.
SQLite Syntax: Query Language Understood by SQLite
SQLite-Datentypen: Datatypes In SQLite Version 3

SQL in Basic4android
Der erste Schritt ist, eine Referenz auf die SQL-Library hinzuzufügen. Dies macht man indem man SQL im Library Tab unten rechts im IDE anhakt.
Es gibt zwei Types in dieser Library.
Ein SQL Objekt das den Zugriff auf die Datenbank ermöglicht.
Ein Cursor Objekt das die Bearbeitung der Ergebnisse von Anfragen ermöglicht.

Normalerweise muß man das SQL Objekt als globales Prozessobjekt deklarieren. Auf diese Weise wird es aktiv bleiben, auch wenn die Activity wieder neu erstellt (resumed) wird.

SQLite speichert die Datenbank in einer einzigen Datei.
Wenn wir das SQL Objekt initialisieren geben wir den Ordner und den Namen der Datenbankdatei ein (die erstellt werden kann, wenn nötigt).

Sub Process_Globals
    Dim SQL1 As SQL
End Sub

Sub Globals

End Sub

Sub Activity_Create(FirstTime As Boolean)
    If FirstTime Then
        SQL1.Initialize(File.DirDefaultExternal, "test1.db", True)
    End If
    CreateTables
    FillSimpleData
    LogTable1
    InsertManyRows
    Log("Number of rows = " & SQL1.ExecQuerySingleResult("SELECT count(*) FROM table1"))

    InsertBlob 'stores an image in the database.
    ReadBlob 'load the image from the database and displays it.
End Sub

Das SQL1 Objekt wird nur einmal initialisiert, wenn der Prozess beginnt.
In unserem Fall erstellen wir es in der SD-Karte. Der letzte Parameter (CreateIfNecessary) ist True, also wird die Datei erstellt, wenn sie noch nicht vorhanden ist.

Es gibt drei verschiedene Methoden, die SQL-Anweisungen ausführen.
ExecNonQuery – Führt eine Schreibanweisung durch, ohne Rückgabewert. Zum Beispiel: INSERT, UPDATE or CREATE TABLE.

ExecQuery - Führt eine Anfrageanweisung durch mit einem Cursor Objekt als Rückgabewert das zur Behandlung des Resultates verwendet wird.

ExecQuerySingleResult - Führt eine Anfrageanweisung durch und gibt den Wert der ersten Spalte in der ersten Zeile des Resultates zurück. Diese Methode ist ein Kürzel für die ExecQuery Anweisung und das Lesen mit dem Cursor Objekt.

Wir werden das folgende Beispiel erklären:

Sub CreateTables
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS table1")
    SQL1.ExecNonQuery("DROP TABLE IF EXISTS table2")
    SQL1.ExecNonQuery("CREATE TABLE table1 (col1 TEXT , col2 INTEGER, col3 INTEGER)")
    SQL1.ExecNonQuery("CREATE TABLE table2 (name TEXT, image BLOB)")
End Sub

Der Obige Code löscht zuerst die zwei Tabellen falls sie existieren und erstellt sie wieder neu.

Sub FillSimpleData
    SQL1.ExecNonQuery("INSERT INTO table1 VALUES('abc', 1, 2)")
    SQL1.ExecNonQuery2("INSERT INTO table1 VALUES(?, ?, ?)", Array As Object("def", 3, 4))
End Sub

In diesem Code werden zwei Zeilen hinzugefügt. SQL.ExecNonQuery2 enthält zwei Parameter. Der erste Parameter ist die Anweisung zwischen Anführungszeichen. Die Fragezeichen werden dann durch die Werte des zweiten List Parameters ersetzt. Die Liste kann entweder Zahlen, Strings oder Bytearrays (blobs) enthalten.
Arrays werden implizit in Listen konvertiert deshalb benutzen wir das Schlüsselwort Array um ein Objektarray zu erstellen anstatt eine Liste zu erstellen.

Sub LogTable1
    Dim Cursor1 As Cursor
    Cursor1 = SQL1.ExecQuery("SELECT col1, col2, col3 FROM table1")
    For i = 0 To Cursor1.RowCount - 1
        Cursor1.Position = i
        Log("************************")
        Log(Cursor1.GetString("col1"))
        Log(Cursor1.GetInt("col2"))
        Log(Cursor1.GetInt("col3"))
    Next
    Cursor1.Close
End Sub

Dieser Code verwendet einen Cursor um die beiden Zeilen zu lesen, die zuvor eingefügt wurden. SQL.ExecQuery gibt ein Cursor-Objekt zurück.
Dann durchlaufen wir in der For-Next Schlaufe alle Ergebnisse.
Beachten Sie, daß wir vor dem Lesen der Werte aus dem Cursor dessen Position eingestellt haben (die aktuelle Zeile).

Sub InsertManyRows
    SQL1.BeginTransaction
    Try
        For i = 1 To 500
            SQL1.ExecNonQuery2("INSERT INTO table1 VALUES ('def', ?, ?)", Array As Object(i, i))
        Next
        SQL1.TransactionSuccessful
    Catch
        Log(LastException.Message)
    End Try
    SQL1.EndTransaction
End Sub

Dieser Code ist ein Beispiel für das Hinzufügen vieler Zeilen. Intern wird nach jeder Schreibfunktion ein Schloß erstellt.
Durch die explizite Erstellung der Transaktion mit BeginTransaction wird das Schloß nur einmal erstellt.
Der obige Code, auf einem echten Gerät ausgeführt, dauerte weniger als eine halbe Sekunde.
Ohne den BeginTransaction / EndTransaction Block dauerte es etwa 70 Sekunden.
Ein Transaktionsblock kann auch verwendet werden um sicherzustellen, daß eine Reihe von Änderungen erfolgreich durchgeführt wurde. Entweder werden alle Änderungen vorgenommen oder keine.
Durch den Aufruf SQL.TransactionSuccessful erklären wir die Transaktion als erfolgreich. Wenn wir diese Zeile weglassen würden, würden alle 500 INSERTS ignoriert werden.
Es ist sehr wichtig, am Ende EndTransaction anzufügen.
Ein 'Standard' Transaktionblock sieht wie folgend aus:

SQL1.BeginTransaction
Try
  'Execute the sql statements.
SQL.TransactionSuccessful
Catch
'the transaction will be cancelled
End Try
SQL.EndTransaction

Beachten Sie, daß die Verwendung von Transaktionen nur für Schreiboperationen relevant ist.

Blobs
Die letzten beiden Methoden schreiben eine Bilddatei in die Datenbank und lesen sie dann zurück und setzten das Bild als Hintergrundsbild für die Acivity.

Sub InsertBlob
    'convert the image file to a bytes array
    Dim InputStream1 As InputStream
    InputStream1 = File.OpenInput(File.DirAssets, "smiley.gif")
    Dim OutputStream1 As OutputStream
    OutputStream1.InitializeToBytesArray(1000)
    File.Copy2(InputStream1, OutputStream1)
    Dim Buffer() As Byte 'declares an empty array
    Buffer = OutputStream1.ToBytesArray

    'write the image to the database
    SQL1.ExecNonQuery2("INSERT INTO table2 VALUES('smiley', ?)", Array As Object(Buffer))
End Sub

Hier benutzen wir einen speziellen OutputStream Type, der in ein dynamisches Byte Array schreibt.
File.Copy2 kopiert alle verfügbaren Daten aus dem InputStream in den OutputStream.
Dann wird das Bytes Array in die Datenbank geschrieben.

Sub ReadBlob
    Dim Cursor1 As Cursor
    'Using ExecQuery2 is safer as it escapes special characters automatically.
    'In this case it doesn't really matter.
    Cursor1 = SQL1.ExecQuery2("SELECT image FROM table2 WHERE name = ?", Array As String("smiley"))
    Cursor1.Position = 0
    Dim Buffer() As Byte 'declare an empty byte array
    Buffer = Cursor1.GetBlob("image")
    Dim InputStream1 As InputStream
    InputStream1.InitializeFromBytesArray(Buffer, 0, Buffer.Length)

    Dim Bitmap1 As Bitmap
    Bitmap1.Initialize2(InputStream1)
    InputStream1.Close
    Activity.SetBackgroundImage(Bitmap1)
End Sub

Mit Cursor.GetBlob holen wir das zuvor gespeicherte Bild zurück.
Jetzt benutzen wir einen Buffer und einen InputStream, die das Bild laden.
Und zuletzt wird das Bild als Activity Hintergrunbild gesetzt.

Der Sourcecode des Beispielprogramms :

 

[AFSSoftware]

Also erstmal Danke fürs übersetzen aber ich hab da noch eine Frage:
Ich möchte auf eine SQL Datenbank im Netzwerk zugreifen.
Geht das auch hiermit?

 

[klaus]

Ich, persönlich habe keine Erfahrung mit Netztwerken, aber dieser Thread könnte Dir vieleicht helfen.

Beste Grüsse.

 

[JanPRO]

Hallo,

leider ist das Beispielprojekt nicht mehr verfügbar vielleicht könnte das erneuert werden. Ich habe noch eine Frage:

Dim name As String = "Jan" 'diese Information weiß ich, die restlichen 3 möchte ich über die Datenbank herausbekommen
Dim alter As String
Dim groesse AS String
Dim augenfarbe As String

Dim found As Boolean = False
  SQL1.Initialize(File.DirInternal, "personen", False)
  Dim Cursor1 As Cursor
  Cursor1 = SQL1.ExecQuery("SELECT Name, Alter, Groesse, Gewicht, Augenfarbe FROM Personenverzeichnis")
  For i = 0 To Cursor1.RowCount -1
  Cursor1.Position = i
  If Cursor1.GetString("Name") = name Then
      alter = Cursor1.GetString("Alter") 'die restlichen 3 Informationen abrufen
      groesse = Cursor1.GetString("Groesse")
      augenfarbe = Cursor1.GetString("Augenfarbe")

      found = True
      End If
  Next
  Cursor1.Close

  If found = False Then
  Msgbox("Es wurden keine Informationen zu dieser Person gefunden!","Fehler")
  else
'restliche Informationen anzeigen
  End If

Die Datenbank habe ich mit diesem Programm erstellt. Doch leider wird der Name nicht in der Datenbank gefunden. Die Datenbank befindet sich im Anhang (Dateiendung wurde wegen Upload auf .txt geändert). Was mache ich falsch?

 

[klaus]

Du solltest Dein Project als zip Datei posten das wäre für uns einfacher Dir zu helfen.
Im IDE Menü File / Export As Zip.
Es bleiben leider Fragen offen.
- Bekommst Du eine Fehlermeldung ?
- Befindet sich die Datenbank im File.DirInternal Ordner ?
- Du solltest die Datenbank nur einmal initialisieren.
Aber ohne den restlichen Code zu sehen ist es schwer konkret zu helfen !
Die Datenbank ist in Ordnung, Ich habe sie mit SQLiteViewer getestet, und es funktioniert.
Hast Du dir die Beispiele SQLiteLight three simple SQLite projects in meiner Unterschrift angesehen.
Ich hab den Link zum Projekt im ersten Post erneuert.

 

[JanPRO]

Die Datei befindet sich im File.DirInternal Ordner sie wurde vorher dorthin kopiert (siehe Code), es gibt auch eine Fehlermeldung. Im Anhang befindet sich das Projekt ...

 

[klaus]

Das Problem ist die Spalte 'Alter' !
Alter ist ein reserviertes Wort in SQLite.
Deine Query müsste so sein:
Cursor1 = SQL1.ExecQuery("SELECT Name, 'Alter', Groesse, Gewicht, Augenfarbe FROM Personenverzeichnis")
und dann
alter = Cursor1.GetString("'Alter'")
Die Abfrage kann man auch vereinfachen.
Anstatt der obigen Query könntest Du diese verwenden:
Cursor1 = SQL1.ExecQuery("SELECT * FROM Personenverzeichnis WHERE Name = '" & name & "'")
Dann brauchst Du nicht die ganze Datenbank abzufragen ob der Eintrag existiert.
Du bekommst nur die Einträge mit dem gefragten Namen.

Beiliegend ein geändertes Projekt.

 

[JanPRO]

Danke, funktioniert jetzt alles problemlos


MFG JanPro