mysqldrivers.pas

MYSQL 连接控件 MYSQL 连接控件

              +'OPTION,OPTIONALLY,OR,'
              +'ORDER,OUT,OUTER,'
              +'OUTFILE,PRECISION,PRIMARY,'
              +'PRIVILEGES,PROCEDURE,PURGE,'
              +'READ,REAL,REFERENCES,'
              +'REGEXP,RENAME,REPEAT,'
              +'REPLACE,REQUIRE,RESTRICT,'
              +'RETURN,REVOKE,RIGHT,'
              +'RLIKE,SECOND_MICROSECOND,SELECT,'
              +'SENSITIVE,SEPARATOR,SET,'
              +'SHOW,SMALLINT,SOME,'
              +'SONAME,SPATIAL,SPECIFIC,'
              +'SQL,SQLEXCEPTION,SQLSTATE,'
              +'SQLWARNING,SQL_BIG_RESULT,SQL_CALC_FOUND_ROWS,'
              +'SQL_SMALL_RESULT,SQL_TSI_DAY,SQL_TSI_FRAC_SECOND,'
              +'SQL_TSI_HOUR,SQL_TSI_MINUTE,SQL_TSI_MONTH,'
              +'SQL_TSI_QUARTER,SQL_TSI_SECOND,SQL_TSI_WEEK,'
              +'SQL_TSI_YEAR,SSL,STARTING,'
              +'STRAIGHT_JOIN,STRIPED,TABLE,'
              +'TABLES,TERMINATED,TEXT,THEN,'
              +'TIME,TIMESTAMP,TIMESTAMPADD,TIMESTAMPDIFF,TINYBLOB,'
              +'TINYINT,TINYTEXT,TO,'
              +'TRAILING,TRUE,TYPE,UNDO,'
              +'UNION,UNIQUE,UNLOCK,'
              +'UNSIGNED,UPDATE,USAGE,'
              +'USE,USER_RESOURCES,USING,'
              +'UTC_DATE,UTC_TIME,UTC_TIMESTAMP,'
              +'VALUES,VARBINARY,VARCHAR,'
              +'VARCHARACTER,VARYING,WHEN,'
              +'WHERE,WHILE,WITH,'
              +'WRITE,XOR,YEAR_MONTH,'
              +'ZEROFILL,';
end;

destructor TMySQLBase.Destroy;
begin
  if Assigned(FConnection) then Close(Pointer(FConnection));
  if FDriverLoaded then Unload;
  FConnection := nil;
  FDriverLoaded := False;
  FClientVer := 0;
  FServerVer := 0;
  FreeAndNil(FOptions);
  inherited;
end;

procedure TMySQLBase.SetOptions(Value : TStrings);
begin
  FOptions.Assign(Value);
end;

// Loading/Unloading the DLL or Drivers
procedure TMySQLBase.Load;
begin
  if FDriverLoaded then UnLoad;
  FDriverLoaded := True;
  Bind;
end;

procedure TMySQLBase.UnLoad;
begin
	if FDriverLoaded then begin
     FDriverLoaded := False;
     FConnection := nil;
     FClientVer := 0;
  end;
end;

function DeadCall: integer;
begin
  raise Exception.Create('This MySQL API call is not implemented in the library you are using.  Please update your library or contact SciBit, http://www.scibit.com.');
end;

procedure TMySQLBase.BindProc(var Proc: pointer; Handle: integer; const Name: string);
begin
  {$IFDEF MSWINDOWS}
  Proc := GetProcAddress(longword(Handle), PChar(Name));
  {$ELSE}
  Proc := dlsym(pointer(Handle), PChar(Name));
  {$ENDIF}
  if not Assigned(Proc) then Proc := @DeadCall;
end;

procedure TMySQLBase.Bind;
begin
  FClientVer := 0;
end;

// Errors
function TMySQLBase.ErrorNo(Conn: pointer): integer;
begin
  Result := 0;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  Result := mysql_errno(Conn);
end;

function TMySQLBase.ErrorMsg(Conn: pointer): string;
begin
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  Result := mysql_error(Conn);
end;

// Connection functions
function TMySQLBase.Open(const AHost: string='localhost'; const AUser: string='root';const APass: string='';const APort: integer=3306;const AOptions: integer=0; const ADB: string = ''): pointer;
var
  i: integer;
  S: string;
begin
  if not FDriverLoaded then Load;
  Result := mysql_init(nil);
  if not Assigned(Result) then MYSQLError(Self,nil,-1,'Could not initialize mysql driver.');
  try
     if not Assigned(FConnection) then FConnection := Result;
     if FClientVer=0 then begin
        S := mysql_get_client_info;
        FClientVer := StrToIntDef(WordInStr(1,S,'.- '),3)*10000+StrToIntDef(WordInStr(2,S,'.- '),21)*100+StrToIntDef(WordInStr(3,S,'.- ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),0);
     end;
     {$IFDEF MYSQLEMBEDDED}
     // Set global embedded options before connect spaghetticode
     if Self is TMySQLEmbedded then mysql_options(Result,MYSQL_READ_DEFAULT_GROUP,PChar(ParamStr(0)+'_SERVER'));
     {$ENDIF}
     // Set connect timeout specified for all types of drivers
     if FOptions.IndexOfName('connect_timeout')>-1 then begin
        i := StrToIntDef(FOptions.Values['connect_timeout'],60);
        mysql_options(Result,MYSQL_OPT_CONNECT_TIMEOUT,@i);
     end;
     if FOptions.IndexOfName('read_timeout')>-1 then begin
        i := StrToIntDef(FOptions.Values['read_timeout'],300);
        mysql_options(Result,MYSQL_OPT_READ_TIMEOUT,@i);
     end;
     if FOptions.IndexOfName('write_timeout')>-1 then begin
        i := StrToIntDef(FOptions.Values['write_timeout'],300);
        mysql_options(Result,MYSQL_OPT_WRITE_TIMEOUT,@i);
     end;
     if (FOptions.IndexOfName('pipe')>-1) or SameText('pipe',FOptions.Values['protocol']) then begin
        i := 0;
        mysql_options(Result,MYSQL_OPT_NAMED_PIPE,@i);
     end;
     if (FOptions.IndexOfName('autoreconnect')>-1) then begin
        i := StrToIntDef(FOptions.Values['autoreconnect'],1);
        mysql_options(Result,MYSQL_OPT_RECONNECT,@i);
     end;
     {$IFDEF HAVE_SSL}
     if (CLIENT_SSL and AOptions)=CLIENT_SSL then mysql_ssl_set(Result,PChar(FOptions.Values['ssl-key']),PChar(FOptions.Values['ssl-cert']),PChar(FOptions.Values['ssl-ca']),PChar(FOptions.Values['ssl-capath']),PChar(FOptions.Values['ssl-cipher']));
     {$ENDIF}
     if mysql_real_connect(Result, PChar(AHost), PChar(AUser), PChar(APass), PChar(ADB), APort, nil, AOptions) = nil then begin
        MYSQLError(Self,FConnection,-1,'Could not connect to mysql server using:'#13#10+
                           'User: '+AUser+#13#10'Host:'+AHost+#13#10+
                           'Port:'+IntToStr(APort)+#13#10'DB:'+ADB+#13#10+
                           'Please check the following:'#13#10+
                           '> MySQL Server is running on the host and port specified and that you can access it through possible firewalls'#13#10+
                           '> If the server is available and on a remote machine, check that this username (with password) may access it from a remote IP, see MySQL Manual (Security)');
        FConnection := nil;
     end;
     S := ServerInfo(FConnection);
     FServerVer := StrToIntDef(WordInStr(1,S,'.- '),3)*10000+StrToIntDef(WordInStr(2,S,'.- '),21)*100+StrToIntDef(WordInStr(3,S,'.- ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz'),0);
  except
     mysql_close(Result);
     FConnection := nil;
     raise;
  end;
end;

function TMySQLBase.Info: string;
begin
  if not FDriverLoaded then Load;
  Result := ClientInfo;
end;

procedure TMySQLBase.Close(var Conn: pointer);
begin
  if not Assigned(Conn) or not FDriverLoaded then exit;
  mysql_close(Conn);
  if Conn=FConnection then begin
     FConnection := nil;
     Unload;
     FServerVer := 0;
  end;
  Conn := nil;
end;

// Utility connection functions
function TMySQLBase.ClientInfo: string;
begin
  if not FDriverLoaded then Load;
  Result := mysql_get_client_info;
end;

function TMySQLBase.HostInfo(Conn: pointer): string;
begin
  if not Assigned(Conn) then begin Result := 'No server connection'; exit; end;
  if not FDriverLoaded then Load;
  Result := mysql_get_host_info(Conn);
end;

function TMySQLBase.ServerInfo(Conn: pointer): string;
begin
  if not Assigned(Conn) then Result := '-1'
  else if not FDriverLoaded then Result := '-1'
  else Result := mysql_get_server_info(Conn);
end;

function TMySQLBase.ProtoInfo(Conn: pointer): longword;
begin
  Result := 0;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  Result := mysql_get_proto_info(Conn);
end;

function TMySQLBase.Ping(Conn: pointer): boolean;
begin
  Result := False;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_ping(Conn)<>0 then MYSQLError(Self,Conn);
  Result := True;
end;

function TMySQLBase.ThreadID(Conn: pointer): longword;
begin
  Result := 0;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  Result := mysql_thread_id(Conn);
end;

procedure TMySQLBase.Kill(Conn: pointer; const PID: Cardinal);
begin
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_kill(Conn, PID)<>0 then MYSQLError(Self,Conn);
end;

function TMySQLBase.QueryInfo(Conn: pointer): string;
begin
  if not Assigned(Conn) then Result := '-1'
  else if not FDriverLoaded then Result := '-1'
  else Result := mysql_info(Conn);
end;

// Database functions
function TMySQLBase.CreateDatabase(Conn: pointer; const DB: string = ''): boolean;
begin
  Result := False;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_create_db(Conn, PChar(DB))<>0 then MYSQLError(Self,Conn);
  Result := True;
end;

function TMySQLBase.SelectDatabase(Conn: pointer; const DB: string = ''): boolean;
begin
  Result := False;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_select_db(Conn, PChar(DB))<>0 then MYSQLError(Self,Conn);
  Result := True;
end;

function TMySQLBase.DropDatabase(Conn: pointer; const DB: string = ''): boolean;
begin
  Result := False;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_drop_db(Conn, PChar(DB))<>0 then MYSQLError(Self,Conn);
  Result := True;
end;

// Table functions
function TMySQLBase.OpenQuery(Conn: pointer; const SQL: string = ''): pointer;
begin
  Result := nil;
  if not Assigned(Conn) then exit;
  if not FDriverLoaded then Load;
  if mysql_real_query(Conn,PChar(SQL),length(SQL))<>0 then MYSQLError(Self,Conn);
  Result := mysql_use_result(Conn);
end;

procedure TMySQLBase.CloseQuery(var Res: pointer);
begin
  if not Assigned(Res) then exit;
  if not FDriverLoaded then Load;
  mysql_free_result(Res);
  Res := nil;
end;

function TMySQLBase.RetrieveColCount(Res: pointer): longword;
begin
  if not Assigned(Res) then
     Result := 0
  else begin
    if not FDriverLoaded then Load;
    Result := mysql_num_fields(Res);
  end;
end;

function TMySQLBase.RetrieveRowCount(Res: pointer): int64;
begin
  if not Assigned(Res) then
     Result := 0
  else begin
    if not FDriverLoaded then Load;
    Result := mysql_num_rows(Res);
  end;
end;

function TMySQLBase.RetrieveField(Res: pointer): TMYSQL_FIELD;
type
	PMYSQL3_FIELD = ^TMYSQL3_FIELD;
	TMYSQL3_FIELD  = record
		name: pChar;              // Name of column
		table: pChar;             // Table of column if column was a field
		def: pChar;               // Default value (set by mysql_list_fields)
		_type: enum_field_types;  // Type of field. Se mysql_com.h for types
		length: longword;         // Width of column
		max_length: longword;     // Max width of selected set
		flags: longword;          // Div flags
		decimals: longword;       // Number of decimals in field
	end;
  PMYSQL400_FIELD = ^TMYSQL400_FIELD;
  TMYSQL400_FIELD = packed record
    name: pChar;              // Name of column
    table: pChar;             // Table of column if column was a field
    org_table: pChar;		      // Org table name if table was an alias
    db: pChar;      			    // Database for table
    def: pChar;               // Default value (set by mysql_list_fields)
    length: longword;         // Width of column
    max_length: longword;     // Max width of selected set
    flags: longword;          // Div flags
    decimals: longword;       // Number of decimals in field
    _type: enum_field_types;  // Type of field. Se mysql_com.h for types
  end;
  PMYSQL410_FIELD = ^TMYSQL410_FIELD;
  TMYSQL410_FIELD = packed record
    name: pChar;              // Name of column
    org_name: pChar;          // Original column name, if an alias
    table: pChar;             // Table of column if column was a field
    org_table: pChar;		      // Org table name if table was an alias
    db: pChar;      			    // Database for table
    def: pChar;               // Default value (set by mysql_list_fields)
    length: longword;         // Width of column
    max_length: longword;     // Max width of selected set
    name_length: longword;
    org_name_length: longword;
    table_length: longword;
    org_table_length: longword;
    db_length: longword;
    def_length: longword;
    flags: longword;          // Div flags
    decimals: longword;       // Number of decimals in field
    charsetnr: longword;     // Character set
    _type: enum_field_types;  // Type of field. Se mysql_com.h for types
  end;
  PMYSQL411_FIELD = ^TMYSQL411_FIELD;
  TMYSQL411_FIELD = packed record
    name: pChar;              // Name of column
    org_name: pChar;          // Original column name, if an alias
    table: pChar;             // Table of column if column was a field
    org_table: pChar;		      // Org table name if table was an alias
    db: pChar;      			    // Database for table
    catalog: pChar;      			// Catalog for table
    def: pChar;               // Default value (set by mysql_list_fields)
    length: longword;         // Width of column
    max_length: longword;     // Max width of selected set
    name_length: longword;
    org_name_length: longword;
    table_length: longword;
    org_table_length: longword;
    db_length: longword;
    catalog_length: longword;
    def_length: longword;
    flags: longword;          // Div flags
    decimals: longword;       // Number of decimals in field
    charsetnr: longword;     // Character set
    _type: enum_field_types;  // Type of field. Se mysql_com.h for types
  end;

var
  R: pointer;
begin
  if not Assigned(Res) then exit;
  if not FDriverLoaded then Load;
  R := mysql_fetch_field(Res);
  if Assigned(R) then begin
     if ClientVer>=40101 then // MySQL clients 4.1.1 and higher including 5.x's format is the same
        with PMYSQL411_FIELD(R)^ do begin
           Result.name := name;
           Result.org_name := org_name;
           Result.table := table;
           Result.org_table := org_table;