zdirmysql.pas

控件名称：WINZEOS.ZIP 2002年08月03日 作者：CapellaDevelopment Zeos系列构件套件包含一组可直接存取Linux知名的免费数据库MySQL、Postgre

      if FHandle <> nil then
      begin
        SetAffectedRows(FHandle^.row_count.Data);
        mysql_free_result(FHandle);
        FHandle := nil;
      end else
        SetAffectedRows(TDirMySqlTransact(Transact).Handle.affected_rows.Data);
    end else
      SetAffectedRows(TDirMySqlTransact(Transact).Handle.affected_rows.Data);
  end;
  MonitorList.InvokeEvent(Sql, Transact.Error, Status <> qsCommandOk);

  Result := AffectedRows;
end;

{ Open a query }
procedure TDirMySqlQuery.Open;
begin
  if not Assigned(Transact) or not Transact.Active then Exit;
  inherited Open;

  SetActive(False);
  SetAffectedRows(0);
  FRow := nil;

  if mysql_query(@TDirMySqlTransact(Transact).Handle,
{$IFDEF DELETE_QUERY_SPACES}
    PChar(ClearSpaces(Trim(Sql)))) = 0 then
{$ELSE}
    PChar(Trim(Sql))) = 0 then
{$ENDIF}
  begin
    if StoreResult then
      FHandle := mysql_store_result(@TDirMySqlTransact(Transact).Handle)
    else
      FHandle := mysql_use_result(@TDirMySqlTransact(Transact).Handle);

    if Assigned(FHandle) then
      if FHandle^.field_count <> 0 then
      begin
        FRow := mysql_fetch_row(FHandle);
        SetActive(True);
        SetStatus(qsTuplesOk);
      end
  end;
  First;
  MonitorList.InvokeEvent(Sql, Transact.Error, Status <> qsTuplesOk);
end;

{ Get a rows quantity }
function TDirMySqlQuery.RecordCount: LongInt;
begin
  if FHandle = nil then Result := 0
  else Result := FHandle^.row_count.Data;
end;

{ Get a fields quantity }
function TDirMySqlQuery.FieldCount: Integer;
begin
  if FHandle = nil then Result := 0
  else Result := FHandle^.field_count;
end;

{ Get a field name by it number }
function TDirMySqlQuery.FieldName(FieldNum: Integer): ShortString;
var
  Field: PMYSQL_FIELD;
begin
  Result := '';
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := Field.name;
  end;
end;

{ Get a field size }
function TDirMySqlQuery.FieldSize(FieldNum: Integer): Integer;
var
  Lengths: PLongInt;
begin
  if (FHandle = nil) or (FieldNum >= FieldCount) then
    Result := 0
  else begin
    Lengths := mysql_fetch_lengths(FHandle);
    if Lengths <> nil then
      Result  := PLongInt(LongInt(Lengths) + FieldNum * SizeOf(LongInt))^
    else
      Result := 0;
  end;
end;

{ Get a maxumum field size }
function TDirMySqlQuery.FieldMaxSize(FieldNum: Integer): Integer;
var
  Field: PMYSQL_FIELD;
begin
  Result := 0;
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := Max(Field.max_length, Field.length);
  end;
end;

{ Get a field type }
function TDirMySqlQuery.FieldType(FieldNum: Integer): Integer;
var
  Field: PMYSQL_FIELD;
begin
  Result := 0;
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := Field._type;
  end;
end;

{ Get a field data type }
function TDirMySqlQuery.FieldDataType(FieldNum: Integer): TFieldType;
var
  Field: PMYSQL_FIELD;
begin
  Result := ftUnknown;
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := MySqlToDelphiType(Field._type, Field.flags);
  end;
end;

{ Get a field flags }
function TDirMySqlQuery.FieldFlags(FieldNum: Integer): Integer;
var
  Field: PMYSQL_FIELD;
begin
  Result := 0;
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := Field.flags;
  end;
end;

{ Get a field decimals }
function TDirMySqlQuery.FieldDecimals(FieldNum: Integer): Integer;
var
  Field: PMYSQL_FIELD;
begin
  Result := 0;
  if FHandle <> nil then
  begin
    mysql_field_seek(FHandle, FieldNum);
    Field := mysql_fetch_field(FHandle);
    if Field <> nil then
      Result := Field.decimals;
  end;
end;

{ Get a field value }
function TDirMySqlQuery.Field(FieldNum: Integer): string;
var
  Lengths: PLongInt;
  Length: LongInt;
begin
  Result := '';
  if (FHandle = nil) or (FRow = nil) then
    Exit;
  Lengths := mysql_fetch_lengths(FHandle);
  if Lengths <> nil then
    Length  := PLongInt(LongInt(Lengths)+FieldNum*SizeOf(LongInt))^
  else
    Length := 0;
  Result  := MemPas(FRow[FieldNum], Length);
end;

{ Get a field value's buffer }
function TDirMySqlQuery.FieldBuffer(FieldNum: Integer): PChar;
begin
  Result := nil;
  if (FHandle = nil) or EOF or BOF then Exit;
  Result := FRow[FieldNum];
end;

{ Define if field empty }
function TDirMySqlQuery.FieldIsNull(FieldNum: Integer): Boolean;
begin
  Result := FieldBuffer(FieldNum) = nil;
end;

{ Is end of rows? }
function TDirMySqlQuery.GetEOF: Boolean;
begin
  if StoreResult then
    Result := inherited GetEOF
  else
    Result := (FRow = nil);
end;

{ Go to first row }
procedure TDirMySqlQuery.First;
begin
  inherited First;
  if StoreResult and Assigned(FHandle) then
  begin
    mysql_seek(FHandle, RecNo);
    FRow := mysql_fetch_row(FHandle);
  end;
end;

{ Go to last row }
procedure TDirMySqlQuery.Last;
begin
  inherited Last;
  if StoreResult and Assigned(FHandle) then
  begin
    mysql_seek(FHandle, RecNo);
    FRow := mysql_fetch_row(FHandle);
  end;
end;

{ Go to prior row }
procedure TDirMySqlQuery.Prev;
begin
  inherited Prev;
  if StoreResult and Assigned(FHandle) then
  begin
    mysql_seek(FHandle, RecNo);
    FRow := mysql_fetch_row(FHandle);
  end;
end;

{ Go to next row }
procedure TDirMySqlQuery.Next;
begin
  inherited Next;
  FRow := mysql_fetch_row(FHandle);
end;

{ Go to row with N number }
procedure TDirMySqlQuery.Go(N: Integer);
begin
  inherited Go(N);
  if StoreResult and Assigned(FHandle) then
  begin
    mysql_seek(FHandle, RecNo);
    FRow := mysql_fetch_row(FHandle);
  end;
end;

{ Showes databases }
procedure TDirMySqlQuery.ShowDatabases(DatabaseName: ShortString);
begin
  if Active then Close;
  Sql := 'SHOW DATABASES';
  if DatabaseName <> '' then
    Sql := Sql + ' LIKE "' + DatabaseName + '"';
  Open;
end;

{ Showes tables of the database }
procedure TDirMySqlQuery.ShowTables(TableName: ShortString);
begin
  if Active then Close;
  Sql := 'SHOW TABLES';
  if TableName <> '' then
    Sql := Sql + ' LIKE "' + TableName + '"';
  Open;
end;

{ Showes columns of the table }
procedure TDirMySqlQuery.ShowColumns(TableName, ColumnName: ShortString);
begin
  if Active then Close;
  Sql := 'SHOW COLUMNS';
  if ColumnName <> '' then
    Sql := Sql + ' LIKE "' + ColumnName + '"';
  Sql := Sql + ' FROM ' + TableName;
  Open;
end;

{ Showes indexes of the table }
procedure TDirMySqlQuery.ShowIndexes(TableName: ShortString);
begin
  if Active then Close;
  Sql := 'SHOW INDEX FROM ' + TableName;
  Open;
end;

{ Convert string to sql format }
function TDirMySqlQuery.StringToSql(Value: string): string;
begin
  Result := ZSqlTypes.StringToSql(Value);
end;

{*********** Extra functions implementation ************}

{ Convert mysql field types to delphi field types }
function MySqlToDelphiType(Value: Byte; Flags: Integer): TFieldType;
begin
  case Value of
    FIELD_TYPE_LONGLONG:
{$IFNDEF VER100}
      Result := ftLargeInt;
{$ELSE}
      Result := ftInteger;
{$ENDIF}
    FIELD_TYPE_TINY, FIELD_TYPE_YEAR, FIELD_TYPE_LONG, FIELD_TYPE_SHORT,
    FIELD_TYPE_INT24:
      Result := ftInteger;
    FIELD_TYPE_DECIMAL, FIELD_TYPE_FLOAT, FIELD_TYPE_DOUBLE:
      Result := ftFloat;
    FIELD_TYPE_DATE, FIELD_TYPE_NEWDATE:
      Result := ftDate;
    FIELD_TYPE_TIME:
      Result := ftTime;
    FIELD_TYPE_DATETIME:
      Result := ftDateTime;
    FIELD_TYPE_TINY_BLOB, FIELD_TYPE_MEDIUM_BLOB, FIELD_TYPE_LONG_BLOB,
    FIELD_TYPE_BLOB:
      if (Flags and BINARY_FLAG) = 0 then
        Result := ftMemo
      else Result := ftBlob;
    else
      Result := ftString;
  end;
end;

{ Calc maximum value len of enum type }
{ Value - type definition as 'enum(val1,val2...valN)' }
function EnumMaxLength(Value: string): Integer;
var
  Buffer, Token: string;
begin
  Buffer := Copy(Value, 6, Length(Value)-6);
  Result := 0;
  while Buffer <> '' do
  begin
    Token := StrTok(Buffer, ',');
    DeleteQuotes(Token);
    if Length(Token) > Result then
      Result := Length(Token);
  end;
end;

{ Convert MySql field types to delphi field types }
function MySqlToDelphiTypeDesc(Value: string; var Size, Precision: Integer): TFieldType;
begin
  Size := 0;
  Precision := 0;

{$IFNDEF VER100}
  if StrCmpBegin(Value,'bigint') then
    Result := ftLargeInt
  else
{$ENDIF}
  if (LowerCase(Value) = 'enum(''y'',''n'')')
      or (LowerCase(Value) = 'enum(''n'',''y'')') then
    Result := ftBoolean
  else if (LowerCase(Value) = 'enum(''t'',''f'')')
      or (LowerCase(Value) = 'enum(''f'',''t'')') then
    Result := ftBoolean
  else if StrCmpBegin(Value,'enum') then
  begin
    Result := ftString;
    Size   := EnumMaxLength(Value);
  end else if (Pos('int',Value) > 0) or StrCmpBegin(Value,'year') then
    Result := ftInteger
  else if StrCmpBegin(Value,'double') or StrCmpBegin(Value,'float')
    or StrCmpBegin(Value,'real') or StrCmpBegin(Value,'decimal')
    or StrCmpBegin(Value,'numeric') then
  begin
    Result    := ftFloat;
    Precision := ExtractPrecision(Value);
  end else if StrCmpBegin(Value,'char') then
  begin
    Result := ftString;
    Size   := StrToIntDef(Copy(Value,6,Pos(')',Value)-6),50);
  end else if StrCmpBegin(Value,'varchar') then
  begin
    Result := ftString;
    Size   := StrToIntDef(Copy(Value,9,Pos(')',Value)-9),50);
  end else if Value = 'date' then
    Result := ftDate
  else if Value = 'datetime' then
    Result := ftDateTime
  else if Value = 'time' then
    Result := ftTime
  else if StrCmpBegin(Value,'timestamp') then
    Result := ftDateTime
  else if Pos('blob',Value) > 0 then
    Result := ftBlob
  else if Pos('text',Value) > 0 then
    Result := ftMemo
  else if StrCmpBegin(Value,'set') then
  begin
    Result := ftString;
    Size   := Length(Value) - 4;
  end else
    Result := ftUnknown;
end;

initialization
  MonitorList := TZMonitorList.Create;
finalization
  MonitorList.Free;
end.