全國最多中醫師線上諮詢網站-台灣中醫網
發文 回覆 瀏覽次數:1155
推到 Plurk!
推到 Facebook!

變 500

 
rick060
高階會員


發表:2
回覆:112
積分:217
註冊:2009-11-17

發送簡訊給我
#1 引用回覆 回覆 發表時間:2011-06-30 08:19:03 IP:60.250.xxx.xxx 未訂閱
這是最近在玩 mysql 用 delphi 做出來的wrapper , delphi 不像 C 可以 static link ,所以 delphi 用起來仍然需要 libmysql.dll
功能很陽春..倒是可以試一試。
基本用法:
[code delphi]
var
pMySQL : MySQLInterface;
begin
try
pMySQL := MySQLInterface.Create;

pMySQL.Host := 'hostIP';//設定連線
pMySQL.User := 'root';
pMySQL.Password := 'root';
pMySQL.DefaultDataBase := 'db';
pMySQL.Port := 3306;
pMySQL.Connect;//連線

pMySQL.SQL := 'select * from table'
pMySQL.Open;

while pMySQL.HasRow do
begin
pMySQL.Fields(0); //column0 的資料
pMySQL.Fields(1); //column1 的資料
pMySQL.Fields(2); //column2 的資料
end;
except on E : Exception do
func_WriteLog( Format('處理錯誤:[%s]',[E.Message]) ,True )
end;
if Assigned(pMySQL) then
pMySQL.Free;
end;
[/code]
至於 prepare 的用法..

[code delphi]
var
pMySQL : MySQLInterface;
pBind : MYSQL_BIND;
dataLen : Cardinal;
inputArray : array[0..127] of Ansichar;
outputArray : array[0..127] of Ansichar;
begin
try
pMySQL := MySQLInterface.Create;
func_InitSQL(pMySQL);

pMySQL.Prepare('select mail_id from quarantine limit 1');
pMySQL.Execute;
ZeroMemory(@pBind,sizeof(pBind));
pBind.buffer := @outputArray;
pBind.buffer_length := sizeof(outputArray);
pBind.length := @dataLen;
pMySQL.BindOutput(@pBind);

while pMySQL.Fetch do
begin
pMySQL.FetchColumn(@pBind,0,0); //column 0 的資料
ShowMessage(outputArray);
end;

pMySQL.Prepare('select mail_id from quarantine where mail_id = ?');

inputArray := 'NjVlXi8jLOXO';
dataLen := StrLen(inputArray);
ZeroMemory(@pBind,sizeof(pBind));
pBind.buffer := @inputArray;
pBind.length := @dataLen;
pBind.buffer_type := Integer(MYSQL_TYPE_STRING);

pMySQL.BindInput(@pBind);
ZeroMemory(@pBind,sizeof(pBind));
pBind.buffer := @outputArray;
pBind.buffer_length := sizeof(outputArray);
pBind.length := @dataLen;
pMySQL.BindOutput(@pBind);

while pMySQL.Fetch do
begin
pMySQL.FetchColumn(@pBind,0,0);
ShowMessage(outputArray);
end;
finally
pMySQL.Free;
end;
end;
[/code]


[code delphi]
unit mysql_wrapper;

interface

uses
Windows,SysUtils,Dialogs,Variants;

const
/// status return codes
MYSQL_NO_DATA = 100;
MYSQL_DATA_TRUNCATED = 101;

type
MYSQL = Pointer;
MYSQL_RES = Pointer;
MYSQL_STMT = Pointer;

type
MYSQL_TYPE = ( MYSQL_TYPE_DECIMAL, MYSQL_TYPE_TINY,
MYSQL_TYPE_SHORT, MYSQL_TYPE_LONG,
MYSQL_TYPE_FLOAT, MYSQL_TYPE_DOUBLE,
MYSQL_TYPE_NULL, MYSQL_TYPE_TIMESTAMP,
MYSQL_TYPE_LONGLONG,MYSQL_TYPE_INT24,
MYSQL_TYPE_DATE, MYSQL_TYPE_TIME,
MYSQL_TYPE_DATETIME, MYSQL_TYPE_YEAR,
MYSQL_TYPE_NEWDATE, MYSQL_TYPE_VARCHAR,
MYSQL_TYPE_BIT,
MYSQL_TYPE_NEWDECIMAL=246,
MYSQL_TYPE_ENUM=247,
MYSQL_TYPE_SET=248,
MYSQL_TYPE_TINY_BLOB=249,
MYSQL_TYPE_MEDIUM_BLOB=250,
MYSQL_TYPE_LONG_BLOB=251,
MYSQL_TYPE_BLOB=252,
MYSQL_TYPE_VAR_STRING=253,
MYSQL_TYPE_STRING=254,
MYSQL_TYPE_GEOMETRY=255,
MAX_NO_FIELD_TYPES);

type
MYSQL_BIND = packed Record
length : PCardinal;
is_null : PAnsiChar;
buffer : Pointer;
error : PAnsiChar;
row_ptr : PByte;
store_param_func : Pointer;
fetch_result : Pointer;
skip_result : Pointer;
buffer_length : Cardinal;
offset : Cardinal;
length_value : Cardinal;
param_number : Cardinal;
pack_length : Cardinal;
buffer_type : Integer;
error_value : AnsiChar;
is_unsinged : AnsiChar;
long_data_used : AnsiChar;
is_null_value : AnsiChar;
extension : Pointer;
end;

type
MySQLInterface = Class
constructor Create;
destructor Destroy; override;

private
FLibraryHandle : THandle; //dll Handle
FMySQLHandle : MYSQL;
FMySQLRes : MYSQL_RES;
FMySQLStmt : MYSQL_STMT;
FfieldHandle : Pointer;
FSQL : AnsiString;
FHost : AnsiString;
FUser : AnsiString;
FPassword : AnsiString;
FDataBase : AnsiString;
FPort : Cardinal;
FAutoCommit : Boolean;

mysql_initPtr : function(pmysql:MYSQL):MYSQL;stdcall;
mysql_closePtr : procedure(pmysql:MYSQL);stdcall;
mysql_real_connectPtr : function(pmysql:MYSQL;host:PAnsiChar;user:PAnsiChar;passwd:PAnsiChar;db:PAnsiChar;port :Cardinal;unix_socket:PAnsiChar;clientflag:Cardinal):MYSQL;stdcall;
mysql_errorPtr : function(pmysql:MYSQL):PAnsiChar;stdcall;
mysql_queryPtr : function(pmysql:MYSQL;stmt_str:PAnsiChar):Integer;stdcall;
mysql_use_resultPtr : function(pmysql:MYSQL):MYSQL_RES;stdcall;
mysql_fetch_rowPtr : function(pres:MYSQL_RES):PPAnsiChar;stdcall;
mysql_free_resultPtr : procedure(pres:MYSQL_RES);stdcall;
mysql_num_fieldsPtr : function(pres:MYSQL_RES):Cardinal;stdcall;
mysql_num_rowsPtr : function(pres:MYSQL_RES):UINT64;stdcall;
mysql_store_resultPtr : function(pmysql:MYSQL):MYSQL_RES;stdcall;
mysql_errnoPtr : function(pmysql:MYSQL):Cardinal;stdcall;
mysql_stmt_initPtr : function(pmysql:MYSQL):MYSQL_STMT;stdcall;
mysql_affected_rowsPtr : function(pmysql:MYSQL):UINT64;stdcall;
mysql_autocommitPtr : function(pmysql:MYSQL;my_bool:WORD):Byte;stdcall;
mysql_commitPtr : function(pmysql:MYSQL):Byte;stdcall;
mysql_rollbackPtr : function(pmysql:MYSQL):Byte;stdcall;
mysql_stmt_preparePtr : function(pstmt:MYSQL_STMT;stmt_str:PAnsiChar;length:Cardinal):Integer;stdcall;
mysql_stmt_bind_paramPtr : function(pstmt:MYSQL_STMT;bind:Pointer):Byte;stdcall;
mysql_stmt_bind_resultPtr : function(pstmt:MYSQL_STMT;bind:Pointer):Byte;stdcall;
mysql_stmt_executePtr : function(pstmt:MYSQL_STMT):Integer;stdcall;
mysql_stmt_fetchPtr : function(pstmt:MYSQL_STMT):Integer;stdcall;
mysql_stmt_closePtr : function(pstmt:MYSQL_STMT):Byte;stdcall;
mysql_stmt_affected_rowsPtr : function(pstmt:MYSQL_STMT):UINT64;stdcall;
mysql_stmt_fetch_columnPtr : function(pstmt:MYSQL_STMT;bind:Pointer;column:Cardinal;offset:Cardinal):Integer;stdcall;

procedure SetSQLText(szSQL:AnsiString);
procedure SetAutoCommit(IsAutoCommit:Boolean);
function dumpError:AnsiString;
function GetErrno : Cardinal;
public
procedure Open;
procedure Exec;
procedure Connect;
procedure Commit;
procedure RollBack;
procedure Execute;
procedure Prepare(szSQL:AnsiString);
procedure BindInput(bind:Pointer);
procedure BindOutput(bind:Pointer);
function Fields(nField:Integer):Variant;
function AffectedRows:UINT64;
function StmtAffectedRows:UINT64;
function HasRow:Boolean;
function Fetch:Boolean;
function FetchColumn(pBind:Pointer;nColumn:Cardinal;nOffset:Cardinal):Variant;

property SQL : AnsiString read FSQL write SetSQLText;
property errno : Cardinal read GetErrno;
property Host : AnsiString read FHost write FHost;
property User : AnsiString read FUser write FUser;
property Password : AnsiString read FPassword write FPassword;
property DefaultDataBase : AnsiString read FDataBase write FDataBase;
property Port : Cardinal read FPort write FPort;
property AutoCommit : Boolean read FAutoCommit write SetAutoCommit;
end;

implementation

{ MySQLInterface }

constructor MySQLInterface.Create;
begin
FLibraryHandle := LoadLibrary( 'libmysql.dll' );
if THandle(FLibraryHandle) = 0 then
raise Exception.Create('LoadLibrary() libmysql.dll Error');

@mysql_initPtr := GetProcAddress(FLibraryHandle, 'mysql_init');
if THandle(@mysql_initPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_init Error');

@mysql_closePtr := GetProcAddress(FLibraryHandle, 'mysql_close');
if THandle(@mysql_closePtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_close Error');

@mysql_real_connectPtr := GetProcAddress(FLibraryHandle, 'mysql_real_connect');
if THandle(@mysql_real_connectPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_real_connect Error');

@mysql_errorPtr := GetProcAddress(FLibraryHandle, 'mysql_error');
if THandle(@mysql_errorPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_error Error');

@mysql_queryPtr := GetProcAddress(FLibraryHandle, 'mysql_query');
if THandle(@mysql_queryPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_query Error');

@mysql_use_resultPtr := GetProcAddress(FLibraryHandle, 'mysql_use_result');
if THandle(@mysql_use_resultPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_use_result Error');

@mysql_fetch_rowPtr := GetProcAddress(FLibraryHandle, 'mysql_fetch_row');
if THandle(@mysql_fetch_rowPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_fetch_row Error');

@mysql_free_resultPtr := GetProcAddress(FLibraryHandle, 'mysql_free_result');
if THandle(@mysql_free_resultPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_free_result Error');

@mysql_num_fieldsPtr := GetProcAddress(FLibraryHandle, 'mysql_num_fields');
if THandle(@mysql_num_fieldsPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_num_fields Error');

@mysql_num_rowsPtr := GetProcAddress(FLibraryHandle, 'mysql_num_rows');
if THandle(@mysql_num_rowsPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_num_rows Error');

@mysql_store_resultPtr := GetProcAddress(FLibraryHandle, 'mysql_num_rows');
if THandle(@mysql_store_resultPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_num_rows Error');

@mysql_errnoPtr := GetProcAddress(FLibraryHandle, 'mysql_errno');
if THandle(@mysql_errnoPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_errno Error');

@mysql_stmt_initPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_init');
if THandle(@mysql_stmt_initPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_init Error');

@mysql_affected_rowsPtr := GetProcAddress(FLibraryHandle, 'mysql_affected_rows');
if THandle(@mysql_affected_rowsPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_affected_rows Error');

@mysql_autocommitPtr := GetProcAddress(FLibraryHandle, 'mysql_autocommit');
if THandle(@mysql_autocommitPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_autocommit Error');

@mysql_commitPtr := GetProcAddress(FLibraryHandle, 'mysql_commit');
if THandle(@mysql_commitPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_commit Error');

@mysql_rollbackPtr := GetProcAddress(FLibraryHandle, 'mysql_rollback');
if THandle(@mysql_rollbackPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_rollback Error');

@mysql_stmt_preparePtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_prepare');
if THandle(@mysql_stmt_preparePtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_prepare Error');

@mysql_stmt_bind_paramPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_bind_param');
if THandle(@mysql_stmt_bind_paramPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_bind_param Error');

@mysql_stmt_executePtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_execute');
if THandle(@mysql_stmt_executePtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_execute Error');

@mysql_stmt_fetchPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_fetch');
if THandle(@mysql_stmt_fetchPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_fetch Error');

@mysql_stmt_closePtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_close');
if THandle(@mysql_stmt_closePtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_close Error');

@mysql_stmt_affected_rowsPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_affected_rows');
if THandle(@mysql_stmt_affected_rowsPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_affected_rows Error');

@mysql_stmt_bind_resultPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_bind_result');
if THandle(@mysql_stmt_bind_resultPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_bind_result Error');

@mysql_stmt_fetch_columnPtr := GetProcAddress(FLibraryHandle, 'mysql_stmt_fetch_column');
if THandle(@mysql_stmt_fetch_columnPtr) = 0 then
raise Exception.Create('GetProcAddress() libmysql.dll -> mysql_stmt_fetch_column Error');

FMySQLHandle := mysql_initPtr(nil);

if FMySQLHandle = nil then
raise Exception.Create('mysql_init() Error');
end;

destructor MySQLInterface.Destroy;
begin
mysql_closePtr(FMySQLHandle);
FreeLibrary( FLibraryHandle );
inherited;
end;

function MySQLInterface.AffectedRows: UINT64;
begin
Result := mysql_affected_rowsPtr(FMySQLHandle);
end;

procedure MySQLInterface.Commit;
begin
if mysql_commitPtr(FMySQLHandle) <> 0 then
raise Exception.Create(dumpError);
end;

procedure MySQLInterface.Connect;
var
pRet : MYSQL;
szErrMsg : AnsiString;
begin
pRet := mysql_real_connectPtr(FMySQLHandle,PAnsiChar(FHost)
,PAnsiChar(FUser),PAnsiChar(FPassword),PAnsiChar(FDatabase)
,FPort,'',0);
if pRet <> FMySQLHandle then
raise Exception.Create(dumpError);
end;

function MySQLInterface.Fetch: Boolean;
var
nRes : Integer;
begin
Result := False;

nRes := mysql_stmt_fetchPtr(FMySQLStmt);

if nRes in [1,MYSQL_NO_DATA,MYSQL_DATA_TRUNCATED] then
mysql_stmt_closePtr(FMySQLStmt);

if nRes = 1 then
raise Exception.Create(dumperror);

if nRes in [0,MYSQL_DATA_TRUNCATED] then
Result := True;
end;

function MySQLInterface.FetchColumn(pBind:Pointer;nColumn:Cardinal;nOffset:Cardinal): Variant;
var
nRes : Integer;
begin
nRes := mysql_stmt_fetch_columnPtr(FMysqlStmt,pBind,nColumn,nOffset);
if nRes <> 0 then
raise Exception.Create(Format('mysql_stmt_fetch_column() error:[%d]',[nRes]));
end;

function MySQLInterface.Fields(nField: Integer): Variant;
var
ptrOffset : Cardinal;
nCount : Integer;
begin
nCount := mysql_num_fieldsPtr(FMySQLRes);
if nField >= nCount then
raise Exception.Create('field index out of range');

Result := AnsiString(PAnsiChar(PInteger(Cardinal(FfieldHandle) (sizeof(Pointer) * nField))^));
end;

procedure MySQLInterface.SetAutoCommit(IsAutoCommit: Boolean);
begin
if mysql_autocommitPtr(FMySQLHandle,Word(IsAutoCommit)) <> 0 then
raise Exception.Create(dumpError);
end;

procedure MySQLInterface.SetSQLText(szSQL:AnsiString);
begin
while HasRow do;
Self.FSQL := szSQL;
end;

function MySQLInterface.StmtAffectedRows: UINT64;
begin
Result := mysql_stmt_affected_rowsPtr(FMySQLStmt);
end;

procedure MySQLInterface.Open;
begin
if mysql_queryPtr(FMySQLHandle,PAnsiChar(FSQL)) <> 0 then
raise Exception.Create(dumpError);

FMySQLRes := mysql_use_resultPtr(FMySQLHandle);
if FMySQLRes = nil then
raise Exception.Create(dumpError);
end;

function MySQLInterface.HasRow: Boolean;
begin
Result := False;

if FMySQLRes = nil then
Exit;

FfieldHandle := mysql_fetch_rowPtr(FMySQLRes);
if FfieldHandle <> nil then
Result := True
else
begin
mysql_free_resultPtr(FMySQLRes);
FMySQLRes := nil;
end;
end;

procedure MySQLInterface.BindInput(bind:Pointer);
var
nRes : Integer;
begin
nRes := mysql_stmt_bind_paramPtr(FMySQLStmt,bind);
if nRes <> 0 then
raise Exception.Create(Format('mysql_stmt_bind_param() error:[%d]',[nRes]));

nRes := mysql_stmt_executePtr(FMySQLStmt);
if nRes <> 0 then
raise Exception.Create(Format('mysql_stmt_execute() error:[%d]',[nRes]));
end;

procedure MySQLInterface.BindOutput(bind: Pointer);
var
nRes : Integer;
begin
nRes := mysql_stmt_bind_resultPtr(FMySQLStmt,bind);
if nRes <> 0 then
raise Exception.Create(Format('mysql_stmt_bind_result() error:[%d]',[nRes]));
end;

function MySQLInterface.dumpError: AnsiString;
begin
Result := Format('mysql Process error:[%s] Errno:[%d]',[mysql_errorPtr(FMySQLHandle),mysql_errnoPtr(FMySQLHandle)]);
end;

function MySQLInterface.GetErrno: Cardinal;
begin
Result := mysql_errnoPtr(FMySQLHandle);
end;

procedure MySQLInterface.Exec;
begin
if mysql_queryPtr(FMySQLHandle,PAnsiChar(FSQL)) <> 0 then
raise Exception.Create(dumpError);
end;

procedure MySQLInterface.Execute;
var
nRes : Integer;
begin
nRes := mysql_stmt_executePtr(FMySQLStmt);
if nRes <> 0 then
raise Exception.Create(Format('mysql_stmt_execute() error:[%d]',[nRes]));
end;

procedure MySQLInterface.Prepare(szSQL: AnsiString);
begin
FSQL := szSQL;

FMySQLStmt := mysql_stmt_initPtr(FMySQLHandle);
if FMySQLStmt = nil then
raise Exception.Create(dumpError);
if mysql_stmt_preparePtr(FMySQLStmt,PAnsiChar(FSQL),Length(FSQL)) <> 0 then
raise Exception.Create(dumpError);
end;

procedure MySQLInterface.RollBack;
begin
if mysql_rollbackPtr(FMySQLHandle) <> 0 then
raise Exception.Create(dumpError);
end;

end.
[/code]
系統時間:2017-12-13 15:29:00
聯絡我們 | Delphi K.Top討論版
本站聲明
1. 本論壇為無營利行為之開放平台,所有文章都是由網友自行張貼,如牽涉到法律糾紛一切與本站無關。
2. 假如網友發表之內容涉及侵權,而損及您的利益,請立即通知版主刪除。
3. 請勿批評中華民國元首及政府或批評各政黨,是藍是綠本站無權干涉,但這裡不是政治性論壇!