sqlitetable3u.pas

定时器for timer for ic chip

  Stmt: TSQLiteStmt;
  NextSQLStatement: PAnsiChar;
  iStepResult: integer;
  ptr: pointer;
  iNumBytes: integer;
  thisBlobValue: TMemoryStream;
  thisStringValue: pWideString;
  thisDoubleValue: pDouble;
  thisIntValue: pInt64;
  thisColType: pInteger;
  I: integer;
  DeclaredColType: PAnsiChar;
  ActualColType: integer;
  ptrValue: PAnsiChar;
Begin
  Try
    self.fRowCount := 0;
    self.fColCount := 0;
    //if there are several SQL statements in SQL, NextSQLStatment points to the
    //beginning of the next one. Prepare only prepares the first SQL statement.
    If Sqlite3_Prepare(DB.fDB, UTF8(SQL), -1, Stmt, NextSQLStatement) <> SQLITE_OK Then
      DB.RaiseError('Error executing SQL', SQL);
    If (Stmt = Nil) Then
      DB.RaiseError('Could not prepare SQL statement', SQL);
    iStepResult := Sqlite3_step(Stmt);
    While (iStepResult <> SQLITE_DONE) Do
    Begin
      Case iStepResult Of
        SQLITE_ROW:
          Begin
            Inc(fRowCount);
            If (fRowCount = 1) Then
            Begin
              //get data types
              fCols := TTntStringList.Create;
              fColTypes := TList.Create;
              fColCount := SQLite3_Column_Count(Stmt);
              For I := 0 To Pred(fColCount) Do
                fCols.Add(WideUpperCase(UnUTF8(Sqlite3_Column_Name(Stmt, I))));
              For I := 0 To Pred(fColCount) Do
              Begin
                New(thisColType);
                DeclaredColType := Sqlite3_Column_DeclType(Stmt, I);
                If DeclaredColType = Nil Then
                  thisColType^ := Sqlite3_Column_Type(Stmt, I) //use the actual column type instead
                  //seems to be needed for last_insert_rowid
                Else
                  If (DeclaredColType = 'INTEGER') Or (DeclaredColType = 'BOOLEAN') Then
                    thisColType^ := SQLITE_INTEGER
                  Else
                    If (DeclaredColType = 'NUMERIC') Or
                      (DeclaredColType = 'FLOAT') Or
                      (DeclaredColType = 'DOUBLE') Or
                      (DeclaredColType = 'REAL') Then
                      thisColType^ := SQLITE_FLOAT
                    Else
                      If DeclaredColType = 'BLOB' Then
                        thisColType^ := SQLITE_BLOB
                      Else
                        thisColType^ := SQLITE_TEXT;
                fColTypes.Add(thisColType);
              End;
              fResults := TList.Create;
            End;

            //get column values
            For I := 0 To Pred(ColCount) Do
            Begin
              ActualColType := Sqlite3_Column_Type(Stmt, I);
              If (ActualColType = SQLITE_NULL) Then
                fResults.Add(Nil)
              Else
                If pInteger(fColTypes[I])^ = SQLITE_INTEGER Then
                Begin
                  New(thisIntValue);
                  thisIntValue^ := Sqlite3_Column_Int64(Stmt, I);
                  fResults.Add(thisIntValue);
                End
                Else
                  If pInteger(fColTypes[I])^ = SQLITE_FLOAT Then
                  Begin
                    New(thisDoubleValue);
                    thisDoubleValue^ := Sqlite3_Column_Double(Stmt, I);
                    fResults.Add(thisDoubleValue);
                  End
                  Else
                    If pInteger(fColTypes[I])^ = SQLITE_BLOB Then
                    Begin
                      iNumBytes := Sqlite3_Column_Bytes(Stmt, I);
                      If iNumBytes = 0 Then
                        thisBlobValue := Nil
                      Else
                      Begin
                        thisBlobValue := TMemoryStream.Create;
                        thisBlobValue.position := 0;
                        ptr := Sqlite3_Column_Blob(Stmt, I);
                        thisBlobValue.writebuffer(ptr^, iNumBytes);
                      End;
                      fResults.Add(thisBlobValue);
                    End
                    Else
                    Begin
                      New(thisStringValue);
                      ptrValue := Sqlite3_Column_Text(Stmt, I);
                      setString(thisStringValue^, ptrValue, StrLen(ptrValue));
                      fResults.Add(thisStringValue);
                    End;
            End;
          End;
        SQLITE_BUSY:
          Raise ESQLiteException.CreateFmt('Could not prepare SQL statement',
            [SQL, 'SQLite is Busy']);
      Else
        DB.RaiseError('Could not retrieve data', SQL);
      End;
      iStepResult := SQLite3_Step(Stmt);
    End;
    fRow := 0;
  Finally
    If Assigned(Stmt) Then
      Sqlite3_Finalize(Stmt);
  End;
End;

//..............................................................................

Destructor TSQLiteTable.Destroy;
Var
  I: cardinal;
  iColNo: integer;
Begin
  If Assigned(fResults) Then
  Begin
    For I := 0 To fResults.Count - 1 Do
    Begin
      //check for blob type
      iColNo := (I Mod fColCount);
      Case pInteger(self.fColTypes[iColNo])^ Of
        SQLITE_BLOB:
          TMemoryStream(fResults[I]).Free;
        SQLITE_TEXT:
          If fResults[I] <> Nil Then
          Begin
            setString(WideString(fResults[I]^), Nil, 0);
            Dispose(fResults[I]);
          End;
      Else
        Dispose(fResults[I]);
      End;
    End;
    fResults.Free;
  End;
  If Assigned(fCols) Then
    fCols.Free;
  If Assigned(fColTypes) Then
    For I := 0 To fColTypes.Count - 1 Do
      Dispose(fColTypes[I]);
  fColTypes.Free;
  Inherited;
End;

//..............................................................................

Function TSQLiteTable.GetColumns(I: integer): WideString;
Begin
  Result := fCols[I];
End;

//..............................................................................

Function TSQLiteTable.GetCountResult: integer;
Begin
  If Not Eof Then
    Result := StrToInt(Fields[0])
  Else
    Result := 0;
End;

Function TSQLiteTable.GetCount: integer;
Begin
  Result := fRowCount;
End;

//..............................................................................

Function TSQLiteTable.GetEOF: boolean;
Begin
  Result := fRow >= fRowCount;
End;

Function TSQLiteTable.GetBOF: boolean;
Begin
  Result := fRow <= 0;
End;

//..............................................................................

Function TSQLiteTable.GetFieldByName(FieldName: WideString): WideString;
Begin
  Result := GetFields(self.GetFieldIndex(FieldName));
End;

Function TSQLiteTable.GetFieldIndex(FieldName: WideString): integer;
Begin

  If (fCols = Nil) Then
  Begin
    Raise ESQLiteException.Create('Field ' + FieldName + ' Not found. Empty dataset');
    Exit;
  End;

  If (fCols.Count = 0) Then
  Begin
    Raise ESQLiteException.Create('Field ' + FieldName + ' Not found. Empty dataset');
    Exit;
  End;

  Result := fCols.IndexOf(AnsiUpperCase(FieldName));

  If (Result < 0) Then
  Begin
    Raise ESQLiteException.Create('Field not found in dataset: ' + FieldName)
  End;

End;

//..............................................................................

Function TSQLiteTable.GetFields(I: cardinal): WideString;
Var
  thisvalue: pWideString;
  thistype: integer;
Begin
  Result := '';
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  //integer types are not stored in the resultset
  //as WideStrings, so they should be retrieved using the type-specific
  //methods
  thistype := pInteger(self.fColTypes[I])^;

  Case thistype Of
    SQLITE_TEXT:
      Begin
        thisvalue := self.fResults[(self.fRow * self.fColCount) + I];
        If (thisvalue <> Nil) Then
          //Result := thisvalue^
          Result := UnUTF8(thisvalue^)
        Else
          Result := '';
      End;
    SQLITE_INTEGER:
      Result := IntToStr(self.FieldAsInteger(I));
    SQLITE_FLOAT:
      Result := FloatToStr(self.FieldAsDouble(I));
    SQLITE_BLOB:
      Result := self.FieldAsBlobText(I);
  Else
    Result := '';
  End;
End;

Function TSQLiteTable.FieldAsBlob(I: cardinal): TMemoryStream;
Begin
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  If (self.fResults[(self.fRow * self.fColCount) + I] = Nil) Then
    Result := Nil
  Else
    If pInteger(self.fColTypes[I])^ = SQLITE_BLOB Then
      Result := TMemoryStream(self.fResults[(self.fRow * self.fColCount) + I])
    Else
      Raise ESQLiteException.Create('Not a Blob field');
End;

Function TSQLiteTable.FieldAsBlobText(I: cardinal): WideString;
Var
  MemStream: TMemoryStream;
  Buffer: PAnsiChar;
Begin
  Result := '';
  MemStream := self.FieldAsBlob(I);
  If MemStream <> Nil Then
    If MemStream.size > 0 Then
    Begin
      MemStream.position := 0;
      Buffer := StrAlloc(MemStream.size + 1);
      MemStream.readbuffer(Buffer[0], MemStream.size);
      (Buffer + MemStream.size)^ := Chr(0);
      setString(Result, Buffer, MemStream.size);
      StrDispose(Buffer);
      Result := UnUTF8(Result);
    End;
End;

Function TSQLiteTable.FieldAsInteger(I: cardinal): int64;
Begin
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  If (self.fResults[(self.fRow * self.fColCount) + I] = Nil) Then
    Result := 0
  Else
    If pInteger(self.fColTypes[I])^ = SQLITE_INTEGER Then
      Result := pInt64(self.fResults[(self.fRow * self.fColCount) + I])^
    Else
      If pInteger(self.fColTypes[I])^ = SQLITE_FLOAT Then
        Result := Trunc(StrToFloat(pString(self.fResults[(self.fRow * self.fColCount) + I])^))
      Else
        Raise ESQLiteException.Create('Not an integer or numeric field');
End;

Function TSQLiteTable.FieldAsDouble(I: cardinal): double;
Begin
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  If (self.fResults[(self.fRow * self.fColCount) + I] = Nil) Then
    Result := 0
  Else
    If pInteger(self.fColTypes[I])^ = SQLITE_INTEGER Then
      Result := pInt64(self.fResults[(self.fRow * self.fColCount) + I])^
    Else
      If pInteger(self.fColTypes[I])^ = SQLITE_FLOAT Then
        Result := pDouble(self.fResults[(self.fRow * self.fColCount) + I])^
      Else
        Raise ESQLiteException.Create('Not an integer or numeric field');
End;

Function TSQLiteTable.FieldAsString(I: cardinal): WideString;
Begin
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  If (self.fResults[(self.fRow * self.fColCount) + I] = Nil) Then
    Result := ''
  Else
    Result := self.GetFields(I);
End;

Function TSQLiteTable.FieldIsNull(I: cardinal): boolean;
Var
  thisvalue: pointer;
Begin
  If Eof Then
    Raise ESQLiteException.Create('Table is at End of File');
  thisvalue := self.fResults[(self.fRow * self.fColCount) + I];
  Result := (thisvalue = Nil);
End;

//..............................................................................

Function TSQLiteTable.Next: boolean;
Begin
  Result := False;
  If Not Eof Then
  Begin
    Inc(fRow);
    Result := True;
  End;
End;

Function TSQLiteTable.Previous: boolean;
Begin
  Result := False;
  If Not BOF Then
  Begin
    Dec(fRow);
    Result := True;
  End;
End;

Function TSQLiteTable.MoveFirst: boolean;
Begin
  Result := False;
  If self.fRowCount > 0 Then
  Begin
    fRow := 0;
    Result := True;
  End;
End;

Function TSQLiteTable.MoveLast: boolean;
Begin
  Result := False;
  If self.fRowCount > 0 Then
  Begin
    fRow := fRowCount - 1;
    Result := True;
  End;
End;

End.