sdmysql.pas

SQLDirect Component Library is a light-weight Borland Database Engine replacement for Borland Delphi

  myodbc_remove_escape:
  	procedure(mysql: PMYSQL; name: TSDCharPtr); stdcall;
  mysql_thread_safe:
  	function: Integer; stdcall;

//  Set up and bring down the server; to ensure that applications will
//  work when linked against either the standard client library or the
//  embedded server library, these functions should be called.
  mysql_server_init:
    	function(argc: Integer; argv, groups: Pointer): Integer; stdcall;
  mysql_server_end:
    	procedure; stdcall;
//  Set up and bring down a thread; these function should be called
//  for each thread in an application which opens at least one MySQL
//  connection.  All uses of the connection(s) should be between these function calls.
  mysql_thread_init:
    	function: TMyBool; stdcall;
  mysql_thread_end:
    	procedure; stdcall;
{$ENDIF}

{
    NOTE, TMYSQL_V?, TMYSQL_RES_V?, TMYSQL_OPTIONS_V?, TMYSQL_FIELD_V? have
  different versions for MySQL versions 3 and 4
}

type
  ESDMySQLError = class(ESDEngineError);

{ TIMyDatabase }
  TIMyConnInfo= packed record
    ServerType: Byte;
    MySQLPtr:	PMYSQL;			// pointer to TMYSQL structure for communication with the MySQL
  end;

  TIMyDatabase = class(TISqlDatabase)
  private
    FHandle: TSDPtr;
    FIsEnableMoney: Boolean;
    FIsClientVer3: Boolean;

    procedure Check(mysql: PMYSQL);
    procedure AllocHandle;
    procedure FreeHandle;
    procedure ExecCmd(const SQL: string; CheckRslt: Boolean);
    function GetMySQLPtr: PMYSQL;
    procedure SetDefaultOptions;
  protected
    function GetHandle: TSDPtr; override;

    procedure DoConnect(const sRemoteDatabase, sUserName, sPassword: string); override;
    procedure DoDisconnect(Force: Boolean); override;
    procedure DoCommit; override;
    procedure DoRollback; override;
    procedure DoStartTransaction; override;
    
    procedure SetAutoCommitOption(Value: Boolean); override;
    procedure SetHandle(AHandle: TSDPtr); override;
  public
    constructor Create(ADbParams: TStrings); override;
    destructor Destroy; override;
    function CreateSqlCommand: TISqlCommand; override;

    function GetAutoIncSQL: string; override;    
    function GetClientVersion: LongInt; override;
    function GetServerVersion: LongInt; override;
    function GetVersionString: string; override;
    function GetSchemaInfo(ASchemaType: TSDSchemaType; AObjectName: string): TISqlCommand; override;

    procedure SetTransIsolation(Value: TISqlTransIsolation); override;
    function TestConnected: Boolean; override;    

    property MySQLPtr: PMYSQL read GetMySQLPtr;
    property IsClientVer3: Boolean read FIsClientVer3;
    property IsEnableMoney: Boolean read FIsEnableMoney;
  end;

{ TIMyCommand }
  TIMyCommand = class(TISqlCommand)
  private
    FHandle: PSDCursor;
    FRow: PMYSQL_ROW;		// current row
    FFieldLens: PLongInt;	// field lengths of the current row

    FStmt,			// w/o bind data
    FBindStmt: string;		// with bind parameter data, it's used to check whether the statement was executed
    FRowsAffected: TMyInt64;	// number of rows affected by the last query
    FAutoIncID: TMyInt64;       // AutoInc primary key from Insert query
    FFirstCalcFieldIdx: Integer;// first field, which is not bound. The following fields are not corresponded actual database fields

    function GetIsOpened: Boolean;
    function GetSqlDatabase: TIMyDatabase;
    function CnvtDateTime2SqlString(Value: TDateTime): string;
    function CnvtFloat2SqlString(Value: Double): string;
    function CnvtVarBytes2EscapeString(Value: string): string;
    function GetFieldValue(Index: Integer): TSDCharPtr;
    procedure InternalQBindParams;
    procedure InternalQExecute;
  protected
    procedure Check(Status: TSDEResult);

    function FieldDataType(ExtDataType: Integer): TFieldType; override;
    function NativeDataType(FieldType: TFieldType): Integer; override;
    function NativeDataSize(FieldType: TFieldType): Word; override;
    function RequiredCnvtFieldType(FieldType: TFieldType): Boolean; override;

    procedure DoPrepare(Value: string); override;
    procedure DoExecDirect(Value: string); override;
    procedure DoExecute; override;
    procedure BindParamsBuffer; override;
    function GetHandle: PSDCursor; override;
    procedure GetFieldDescs(Descs: TSDFieldDescList); override;
    procedure InitParamList; override;
    procedure SetFieldsBuffer; override;

    property IsOpened: Boolean read GetIsOpened;
    property SqlDatabase: TIMyDatabase read GetSqlDatabase;
  public
    constructor Create(ASqlDatabase: TISqlDatabase); override;
    destructor Destroy; override;
 	// command interface
    procedure CloseResultSet; override;
    procedure Disconnect(Force: Boolean); override;
    function GetAutoIncValue: Integer; override;
    function GetRowsAffected: Integer; override;
    function ResultSetExists: Boolean; override;
	// cursor interface
    function FetchNextRow: Boolean; override;
    function GetCnvtFieldData(AFieldDesc: TSDFieldDesc; Buffer: TSDPtr; BufSize: Integer): Boolean; override;
    procedure GetOutputParams; override;

    function ReadBlob(AFieldDesc: TSDFieldDesc; var BlobData: TSDBlobData): Longint; override;    
  end;

{ TIMyCommandShowTables }
  TIMyCommandShowTables = class(TIMyCommand)
  private
    FTableNames: string;
    FIsSysTables: Boolean;
    FCatNameFieldIdx,
    FSchNameFieldIdx,
    FTblTypeFieldIdx: Integer;
  protected
    procedure DoExecDirect(Value: string); override;
    procedure GetFieldDescs(Descs: TSDFieldDescList); override;
  public
    constructor Create(ASqlDatabase: TISqlDatabase); override;
    function FetchNextRow: Boolean; override;
    property TableNames: string read FTableNames write FTableNames;
    property IsSysTables: Boolean read FIsSysTables write FIsSysTables;
  end;

{ TIMyCommandShowColumns }
  TIMyCommandShowColumns = class(TIMyCommand)
  private
    FTableName: string;
    FCatNameFieldIdx,
    FSchNameFieldIdx,
    FTblNameFieldIdx,
    FColLenFieldIdx,
    FColPrecFieldIdx,
    FColScaleFieldIdx,
    FColNullFieldIdx: Integer;
  protected
    procedure DoExecDirect(Value: string); override;
    procedure GetFieldDescs(Descs: TSDFieldDescList); override;
  public
    constructor Create(ASqlDatabase: TISqlDatabase); override;
    function FetchNextRow: Boolean; override;
    property TableName: string read FTableName write FTableName;
  end;

{ TIMyCommandShowIndexes }
  TIMyCommandShowIndexes = class(TIMyCommand)
  private
    FTableName: string;
    FCatNameFieldIdx,
    FSchNameFieldIdx,
    FIdxTypeFieldIdx,
    FIdxFilterFieldIdx: Integer;
  protected
    procedure DoExecDirect(Value: string); override;
    procedure GetFieldDescs(Descs: TSDFieldDescList); override;
  public
    constructor Create(ASqlDatabase: TISqlDatabase); override;
    function FetchNextRow: Boolean; override;
    property TableName: string read FTableName write FTableName;
  end;

const
  DefSqlApiDLL	= 'libmysql.dll';

var
  SqlApiDLL: string;

{*******************************************************************************
		Load/Unload Sql-library
*******************************************************************************}
procedure LoadSqlLib;
procedure FreeSqlLib;

function InitSqlDatabase(ADbParams: TStrings): TISqlDatabase;

{$IFDEF SD_CLR}

[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_num_rows')]
function mysql_num_rows(res: PMYSQL_RES): TMyInt64; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_num_fields')]
function mysql_num_fields(res: PMYSQL_RES): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_eof')]
function mysql_eof(res: PMYSQL_RES): TMyBool; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_fetch_field_direct')]
function mysql_fetch_field_direct(res: PMYSQL_RES; fieldnr: Integer): PMYSQL_FIELD; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_fetch_fields')]
function mysql_fetch_fields(res: PMYSQL_RES): PMYSQL_FIELD_V3; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_row_tell')]
function mysql_row_tell(res: PMYSQL_RES): PMYSQL_ROWS; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_field_tell')]
function mysql_field_tell(res: PMYSQL_RES): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_field_count')]
function mysql_field_count(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_affected_rows')]
function mysql_affected_rows(mysql: PMYSQL): TMyInt64; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_insert_id')]
function mysql_insert_id(mysql: PMYSQL): TMyInt64; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_errno')]
function mysql_errno(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_error')]
function mysql_error(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_info')]
function mysql_info(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_thread_id')]
function mysql_thread_id(mysql: PMYSQL): LongInt; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_character_set_name')]
function mysql_character_set_name(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_init')]
function mysql_init(mysql: PMYSQL): PMYSQL; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_ssl_set')]
function mysql_ssl_set(mysql: PMYSQL; const key, cert, ca, capath: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_ssl_cipher')]
function mysql_ssl_cipher(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_ssl_clear')]
function mysql_ssl_clear(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_connect')]
function mysql_connect(mysql: PMYSQL; const host, user, passwd: TSDCharPtr): PMYSQL; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_change_user')]
function mysql_change_user(mysql: PMYSQL; const user, passwd, db: TSDCharPtr): TMyBool; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_real_connect')]
function mysql_real_connect(mysql: PMYSQL; const host, user, passwd, db: TSDCharPtr;
		port: Integer; const unix_socket: TSDCharPtr; clientflag: Integer): PMYSQL; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_close')]
procedure mysql_close(sock: PMYSQL); external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_select_db')]
function mysql_select_db(mysql: PMYSQL; const db: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_query')]
function mysql_query(mysql: PMYSQL; const q: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_send_query')]
function mysql_send_query(mysql: PMYSQL; const q: TSDCharPtr; length: Integer): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_read_query_result')]
function mysql_read_query_result(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_real_query')]
function mysql_real_query(mysql: PMYSQL; const q: TSDCharPtr; length: Integer): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_create_db')]
function mysql_create_db(mysql: PMYSQL; const DB: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_drop_db')]
function mysql_drop_db(mysql: PMYSQL; const DB: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_shutdown')]
function mysql_shutdown(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_dump_debug_info')]
function mysql_dump_debug_info(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_refresh')]
function mysql_refresh(mysql: PMYSQL; refresh_options: Integer): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_kill')]
function mysql_kill(mysql: PMYSQL; pid: Integer): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_ping')]
function mysql_ping(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_stat')]
function mysql_stat(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_get_server_info')]
function mysql_get_server_info(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_get_client_info')]
function mysql_get_client_info: TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_get_host_info')]
function mysql_get_host_info(mysql: PMYSQL): TSDCharPtr; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_get_proto_info')]
function mysql_get_proto_info(mysql: PMYSQL): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_list_dbs')]
function mysql_list_dbs(mysql: PMYSQL; const wild: TSDCharPtr): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_list_tables')]
function mysql_list_tables(mysql: PMYSQL; const wild: TSDCharPtr): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_list_fields')]
function mysql_list_fields(mysql: PMYSQL; const table, wild: TSDCharPtr): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_list_processes')]
function mysql_list_processes(mysql: PMYSQL): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_store_result')]
function mysql_store_result(mysql: PMYSQL): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_use_result')]
function mysql_use_result(mysql: PMYSQL): PMYSQL_RES; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_options')]
function mysql_options(mysql: PMYSQL; option: TMySqlOption; const arg: TSDCharPtr): Integer; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_free_result')]
procedure mysql_free_result(res: PMYSQL_RES); external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_data_seek')]
procedure mysql_data_seek(res: PMYSQL_RES; offset: TMyInt64); external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_row_seek')]
function mysql_row_seek(res: PMYSQL_RES; Row: TMYSQL_ROW_OFFSET): TMYSQL_ROW_OFFSET; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_field_seek')]
function mysql_field_seek(res: PMYSQL_RES; offset: TMYSQL_FIELD_OFFSET): TMYSQL_FIELD_OFFSET; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_fetch_row')]
function mysql_fetch_row(res: PMYSQL_RES): PMYSQL_ROW; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_fetch_lengths')]
function mysql_fetch_lengths(res: PMYSQL_RES): PLongInt; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_fetch_field')]
function mysql_fetch_field(res: PMYSQL_RES): PMYSQL_FIELD_V3; external;
[DllImport(DefSqlApiDLL, CharSet = CharSet.Ansi, SetLastError = True, EntryPoint = 'mysql_escape_string')]
function mysql_escape_string(szTo: TSDCharPtr; const szFrom: TSDCharPtr; from_length: LongInt): LongInt; external;