Mega Code Archive
Plain MySQL access
<![CDATA[
Title: plain MySQL access
Question: how to access MySQL databases in plain source code using
just library (Windows and Linux)
Answer:
first here is the unit that makes connection with MySQL service:
//------------------------------------------------------------------//
unit qmysql;
interface
uses
{$IFDEF LINUX}
Libc,
{$ENDIF}
{$IFDEF WIN32}
Windows,
{$ENDIF}
SysUtils;
const
{ General Declarations }
MYSQL_ERRMSG_SIZE = 200;
MYSQL_PORT = 3306;
LOCAL_HOST = 'localhost';
NAME_LEN = 64;
PROTOCOL_VERSION = 10;
FRM_VER = 6;
{ Enum Field Types }
FIELD_TYPE_DECIMAL = 0;
FIELD_TYPE_TINY = 1;
FIELD_TYPE_SHORT = 2;
FIELD_TYPE_LONG = 3;
FIELD_TYPE_FLOAT = 4;
FIELD_TYPE_DOUBLE = 5;
FIELD_TYPE_NULL = 6;
FIELD_TYPE_TIMESTAMP = 7;
FIELD_TYPE_LONGLONG = 8;
FIELD_TYPE_INT24 = 9;
FIELD_TYPE_DATE = 10;
FIELD_TYPE_TIME = 11;
FIELD_TYPE_DATETIME = 12;
FIELD_TYPE_YEAR = 13;
FIELD_TYPE_NEWDATE = 14;
FIELD_TYPE_ENUM = 247;
FIELD_TYPE_SET = 248;
FIELD_TYPE_TINY_BLOB = 249;
FIELD_TYPE_MEDIUM_BLOB = 250;
FIELD_TYPE_LONG_BLOB = 251;
FIELD_TYPE_BLOB = 252;
FIELD_TYPE_VAR_STRING = 253;
FIELD_TYPE_STRING = 254;
{ For Compatibility }
FIELD_TYPE_CHAR = FIELD_TYPE_TINY;
FIELD_TYPE_INTERVAL = FIELD_TYPE_ENUM;
{ Field's flags }
NOT_NULL_FLAG = 1; { Field can't be NULL }
PRI_KEY_FLAG = 2; { Field is part of a primary key }
UNIQUE_KEY_FLAG = 4; { Field is part of a unique key }
MULTIPLE_KEY_FLAG = 8; { Field is part of a key }
BLOB_FLAG = 16; { Field is a blob }
UNSIGNED_FLAG = 32; { Field is unsigned }
ZEROFILL_FLAG = 64; { Field is zerofill }
BINARY_FLAG = 128; { Field is binary }
ENUM_FLAG = 256; { Field is an enum }
AUTO_INCREMENT_FLAG = 512; { Field is a autoincrement field }
TIMESTAMP_FLAG = 1024; { Field is a timestamp }
SET_FLAG = 2048; { Field is a set }
NUM_FLAG = 32768; { Field is num (for clients) }
{ Server Administration Refresh Options }
REFRESH_GRANT = 1; { Refresh grant tables }
REFRESH_LOG = 2; { Start on new log file }
REFRESH_TABLES = 4; { close all tables }
REFRESH_HOSTS = 8; { Flush host cache }
REFRESH_STATUS = 16; { Flush status variables }
REFRESH_THREADS = 32; { Flush status variables }
REFRESH_SLAVE = 64; { Reset master info abd restat slave thread }
REFRESH_MASTER = 128; { Remove all bin logs in the index and truncate the index }
REFRESH_READ_LOCK = 16384; { Lock tables for read }
REFRESH_FAST = 32768; { Intern flag }
{ Client Connection Options }
_CLIENT_LONG_PASSWORD = 1; { new more secure passwords }
_CLIENT_FOUND_ROWS = 2; { Found instead of affected rows }
_CLIENT_LONG_FLAG = 4; { Get all column flags }
_CLIENT_CONNECT_WITH_DB = 8; { One can specify db on connect }
_CLIENT_NO_SCHEMA = 16; { Don't allow database.table.column }
_CLIENT_COMPRESS = 32; { Can use compression protcol }
_CLIENT_ODBC = 64; { Odbc client }
_CLIENT_LOCAL_FILES = 128; { Can use LOAD DATA LOCAL }
_CLIENT_IGNORE_SPACE = 256; { Ignore spaces before '(' }
_CLIENT_CHANGE_USER = 512; { Support the mysql_change_user() }
_CLIENT_INTERACTIVE = 1024; { This is an interactive client }
_CLIENT_SSL = 2048; { Switch to SSL after handshake }
_CLIENT_IGNORE_SIGPIPE = 4096; { IGNORE sigpipes }
_CLIENT_TRANSACTIONS = 8196; { Client knows about transactions }
{ Net type }
NET_TYPE_TCPIP = 0;
NET_TYPE_SOCKET = 1;
NET_TYPE_NAMEDPIPE = 2;
{ ****************** Plain API Types definition ***************** }
type
TClientCapabilities = (
CLIENT_LONG_PASSWORD,
CLIENT_FOUND_ROWS,
CLIENT_LONG_FLAG,
CLIENT_CONNECT_WITH_DB,
CLIENT_NO_SCHEMA,
CLIENT_COMPRESS,
CLIENT_ODBC,
CLIENT_LOCAL_FILES,
CLIENT_IGNORE_SPACE
);
TSetClientCapabilities = set of TClientCapabilities;
TRefreshOptions = (
_REFRESH_GRANT,
_REFRESH_LOG,
_REFRESH_TABLES,
_REFRESH_HOSTS,
_REFRESH_FAST
);
TSetRefreshOptions = set of TRefreshOptions;
mysql_status = (
MYSQL_STATUS_READY,
MYSQL_STATUS_GET_RESULT,
MYSQL_STATUS_USE_RESULT
);
mysql_option = (
MYSQL_OPT_CONNECT_TIMEOUT,
MYSQL_OPT_COMPRESS,
MYSQL_OPT_NAMED_PIPE,
MYSQL_INIT_COMMAND,
MYSQL_READ_DEFAULT_FILE,
MYSQL_READ_DEFAULT_GROUP,
MYSQL_SET_CHARSET_DIR,
MYSQL_SET_CHARSET_NAME
);
PUSED_MEM=^USED_MEM;
USED_MEM = packed record
next: PUSED_MEM;
left: Integer;
size: Integer;
end;
PERR_PROC = ^ERR_PROC;
ERR_PROC = procedure;
PMEM_ROOT = ^MEM_ROOT;
MEM_ROOT = packed record
free: PUSED_MEM;
used: PUSED_MEM;
pre_alloc: PUSED_MEM;
min_malloc: Integer;
block_size: Integer;
error_handler: PERR_PROC;
end;
NET = packed record
hPipe: Pointer;
fd: Integer;
fcntl: Integer;
buff: PChar;
buff_end: PChar;
write_pos: PChar;
read_pos: PChar;
last_error: array[01..MYSQL_ERRMSG_SIZE] of Char;
last_errno: Integer;
max_packet: Integer;
timeout: Integer;
pkt_nr: Integer;
error: Char;
return_errno: Byte;
compress: Byte;
no_send_ok: Byte;
remain_in_buf: LongInt;
length: LongInt;
buf_length: LongInt;
where_b: LongInt;
return_status: Pointer;
reading_or_writing: Char;
save_char: Char;
end;
MYSQL_FIELD = record
name: PChar;
table: PChar;
def: PChar;
_type: Byte;
length: Integer;
max_length: Integer;
flags: Integer;
decimals: Integer;
end;
PMYSQL_FIELD = ^MYSQL_FIELD;
MYSQL_FIELD_OFFSET = Cardinal;
MYSQL_ROW = array[00..$ff] of PChar;
PMYSQL_ROW = ^MYSQL_ROW;
PMYSQL_ROWS = ^MYSQL_ROWS;
MYSQL_ROWS = record
next: PMYSQL_ROWS;
data: PMYSQL_ROW;
end;
MYSQL_ROW_OFFSET = PMYSQL_ROWS;
MYSQL_DATA = record
Rows: Int64;
Fields: Cardinal;
Data: PMYSQL_ROWS;
Alloc: MEM_ROOT;
end;
PMYSQL_DATA = ^MYSQL_DATA;
type
_MYSQL_OPTIONS = record
connect_timeout: Integer;
clientFlag: Integer;
compress: Byte;
named_pipe: Byte;
port: Integer;
host: PChar;
init_command: PChar;
user: PChar;
password: PChar;
unix_socket: PChar;
db: PChar;
my_cnf_file: PChar;
my_cnf_group: PChar;
charset_dir: PChar;
charset_name: PChar;
use_ssl: Byte;
ssl_key: PChar;
ssl_cert: PChar;
ssl_ca: PChar;
ssl_capath: PChar;
end;
PMYSQL_OPTIONS = ^_MYSQL_OPTIONS;
MYSQL = record
_net: NET;
connector_fd: PChar;
host: PChar;
user: PChar;
passwd: PChar;
unix_socket: PChar;
server_version: PChar;
host_info: PChar;
info: PChar;
db: PChar;
port: Integer;
client_flag: Integer;
server_capabilities: Integer;
protocol_version: Integer;
field_count: Integer;
server_status: Integer;
thread_id: LongInt;
affected_rows: Int64;
insert_id: Int64;
extra_info: Int64;
packet_length: LongInt;
status: mysql_status;
fields: PMYSQL_FIELD;
field_alloc: MEM_ROOT;
free_me, reconnect: Byte;
options: _mysql_options;
scramble_buff: array[0..8] of Char;
charset: PChar;
server_language: Integer;
end;
PMYSQL = ^MYSQL;
MYSQL_RES = packed record
row_count: Int64;
field_count: Integer;
current_field: Integer;
fields: PMYSQL_FIELD;
data: PMYSQL_DATA;
data_cursor: PMYSQL_ROWS;
field_alloc: MEM_ROOT;
row: PMYSQL_ROW;
current_row: PMYSQL_ROW;
lengths: PLongInt;
handle: PMYSQL;
eof: Byte;
end;
PMYSQL_RES = ^MYSQL_RES;
TModifyType = (MODIFY_INSERT, MODIFY_UPDATE, MODIFY_DELETE);
TQuoteOptions = (QUOTE_STRIP_CR,QUOTE_STRIP_LF);
TQuoteOptionsSet = set of TQuoteOptions;
{ ************** Plain API Function types definition ************* }
Tmysql_debug = procedure(Debug: PChar);
Tmysql_dump_debug_info = function(Handle: PMYSQL): Integer;
Tmysql_init = function(Handle: PMYSQL): PMYSQL;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_connect = function(Handle: PMYSQL; const Host, User, Passwd: PChar):
PMYSQL; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_real_connect = function(Handle: PMYSQL;
const Host, User, Passwd, Db: PChar; Port: Cardinal;
unix_socket: PChar; clientflag: Cardinal): PMYSQL;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_close = procedure(Handle: PMYSQL);
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_query = function(Handle: PMYSQL; const Query: PChar): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_real_query = function(Handle: PMYSQL; const Query: PChar;
len: Integer): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_select_db = function(Handle: PMYSQL; const Db: PChar): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_create_db = function(Handle: PMYSQL; const Db: PChar): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_drop_db = function(Handle: PMYSQL; const Db: PChar): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_shutdown = function(Handle: PMYSQL): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_refresh = function(Handle: PMYSQL; Options: Cardinal): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_kill = function(Handle: PMYSQL; Pid: longint): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_ping = function(Handle: PMYSQL): Integer;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_stat = function(Handle: PMYSQL): PChar;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_options = function(Handle: PMYSQL; Option: mysql_option;
const Arg: PChar): Integer; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_escape_string = function(PTo, PFrom: PChar; Len: Cardinal): Cardinal;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_get_server_info = function(Handle: PMYSQL): PChar;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_get_client_info = function: PChar;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_get_host_info = function(Handle: PMYSQL): PChar;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_get_proto_info = function(Handle: PMYSQL): Cardinal;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_list_dbs = function(Handle: PMYSQL; Wild: PChar): PMYSQL_RES;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_list_tables = function(Handle: PMYSQL; const Wild: PChar): PMYSQL_RES;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_list_fields = function(Handle: PMYSQL; const Table, Wild: PChar):
PMYSQL_RES; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_list_processes = function(Handle: PMYSQL): PMYSQL_RES;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_store_result = function(Handle: PMYSQL): PMYSQL_RES;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_use_result = function(Handle: PMYSQL): PMYSQL_RES;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_free_result = procedure(Result: PMYSQL_RES);
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_fetch_row = function(Result: PMYSQL_RES): PMYSQL_ROW;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_fetch_lengths = function(Result: PMYSQL_RES): PLongInt;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_fetch_field = function(Result: PMYSQL_RES): PMYSQL_FIELD;
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_data_seek = procedure(Result: PMYSQL_RES; Offset: Int64);
{$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_row_seek = function(Result: PMYSQL_RES; Row: MYSQL_ROW_OFFSET):
MYSQL_ROW_OFFSET; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_field_seek = function(Result: PMYSQL_RES; Offset: mysql_field_offset):
mysql_field_offset; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
//start my defines
Tmysql_num_rows = function(Result: PMYSQL_RES):
int64; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_num_fields = function(Result: PMYSQL_RES):
longword; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_fetch_field_direct = function(Result: PMYSQL_RES; FieldNumber: LongWord):
PMYSQL_FIELD; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_error = function(MySQL: PMYSQL):
PChar; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_errno = function(MySQL: PMYSQL):
LongWord; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_thread_id = function(MySQL: PMYSQL):
LongWord; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_character_set_name = function(MySQL: PMYSQL):
PChar; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
Tmysql_change_user = function(MySQL: PMYSQL; const user, passwd, db: PChar):
Byte; {$IFNDEF LINUX} stdcall {$ELSE} cdecl {$ENDIF};
//end my defines
{ ************* Plain API Function variables definition ************ }
var
mysql_debug: Tmysql_debug;
mysql_dump_debug_info: Tmysql_dump_debug_info;
mysql_init: Tmysql_init;
mysql_connect: Tmysql_connect;
mysql_real_connect: Tmysql_real_connect;
mysql_close: Tmysql_close;
mysql_select_db: Tmysql_select_db;
mysql_create_db: Tmysql_create_db;
mysql_drop_db: Tmysql_drop_db;
mysql_query: Tmysql_query;
mysql_real_query: Tmysql_real_query;
mysql_shutdown: Tmysql_shutdown;
mysql_refresh: Tmysql_refresh;
mysql_kill: Tmysql_kill;
mysql_ping: Tmysql_ping;
mysql_stat: Tmysql_stat;
mysql_options: Tmysql_options;
mysql_escape_string: Tmysql_escape_string;
mysql_get_server_info: Tmysql_get_server_info;
mysql_get_client_info: Tmysql_get_client_info;
mysql_get_host_info: Tmysql_get_host_info;
mysql_get_proto_info: Tmysql_get_proto_info;
mysql_list_dbs: Tmysql_list_dbs;
mysql_list_tables: Tmysql_list_tables;
mysql_list_fields: Tmysql_list_fields;
mysql_list_processes: Tmysql_list_processes;
mysql_data_seek: Tmysql_data_seek;
mysql_row_seek: Tmysql_row_seek;
mysql_field_seek: Tmysql_field_seek;
mysql_fetch_row: Tmysql_fetch_row;
mysql_fetch_lengths: Tmysql_fetch_lengths;
mysql_fetch_field: Tmysql_fetch_field;
mysql_store_result: Tmysql_store_result;
mysql_use_result: Tmysql_use_result;
mysql_free_result: Tmysql_free_result;
mysql_num_rows: Tmysql_num_rows;
mysql_num_fields: Tmysql_num_fields;
mysql_fetch_field_direct: Tmysql_fetch_field_direct;
mysql_error: Tmysql_error;
mysql_errno: Tmysql_errno;
mysql_thread_id: Tmysql_thread_id;
mysql_character_set_name: Tmysql_character_set_name;
mysql_change_user: Tmysql_change_user;
var
ModulePath, ModuleName: String;
{$IFDEF LINUX}
ModuleHandle: HMODULE;
{$ENDIF}
{$IFDEF WIN32}
ModuleHandle: THandle;
{$ENDIF}
function LoadModule: Boolean;
function FreeModule: Boolean;
implementation
function GetAddress(ProcName: PChar): Pointer;
begin
Result:=GetProcAddress(ModuleHandle, ProcName);
end;
function LoadModule: Boolean;
begin
ModuleHandle:=LoadLibrary(PChar(ModulePath+ModuleName));
Result:=(ModuleHandle0);
if Result then begin
@mysql_debug := GetAddress('mysql_debug');
@mysql_dump_debug_info := GetAddress('mysql_dump_debug_info');
@mysql_init := GetAddress('mysql_init');
@mysql_connect := GetAddress('mysql_connect');
@mysql_real_connect := GetAddress('mysql_real_connect');
@mysql_close := GetAddress('mysql_close');
@mysql_select_db := GetAddress('mysql_select_db');
@mysql_create_db := GetAddress('mysql_create_db');
@mysql_drop_db := GetAddress('mysql_drop_db');
@mysql_query := GetAddress('mysql_query');
@mysql_real_query := GetAddress('mysql_real_query');
@mysql_shutdown := GetAddress('mysql_shutdown');
@mysql_refresh := GetAddress('mysql_refresh');
@mysql_kill := GetAddress('mysql_kill');
@mysql_ping := GetAddress('mysql_ping');
@mysql_stat := GetAddress('mysql_stat');
@mysql_options := GetAddress('mysql_options');
@mysql_escape_string := GetAddress('mysql_escape_string');
@mysql_get_server_info := GetAddress('mysql_get_server_info');
@mysql_get_client_info := GetAddress('mysql_get_client_info');
@mysql_get_host_info := GetAddress('mysql_get_host_info');
@mysql_get_proto_info := GetAddress('mysql_get_proto_info');
@mysql_list_fields := GetAddress('mysql_list_fields');
@mysql_list_processes := GetAddress('mysql_list_processes');
@mysql_list_dbs := GetAddress('mysql_list_dbs');
@mysql_list_tables := GetAddress('mysql_list_tables');
@mysql_data_seek := GetAddress('mysql_data_seek');
@mysql_row_seek := GetAddress('mysql_row_seek');
@mysql_field_seek := GetAddress('mysql_field_seek');
@mysql_fetch_row := GetAddress('mysql_fetch_row');
@mysql_fetch_lengths := GetAddress('mysql_fetch_lengths');
@mysql_fetch_field := GetAddress('mysql_fetch_field');
@mysql_use_result := GetAddress('mysql_use_result');
@mysql_store_result := GetAddress('mysql_store_result');
@mysql_free_result := GetAddress('mysql_free_result');
@mysql_num_rows := GetAddress('mysql_num_rows');
@mysql_num_fields := GetAddress('mysql_num_fields');
@mysql_fetch_field_direct:= GetAddress('mysql_fetch_field_direct');
@mysql_error := GetAddress('mysql_error');
@mysql_errno := GetAddress('mysql_errno');
@mysql_thread_id := GetAddress('mysql_thread_id');
@mysql_character_set_name:= GetAddress('mysql_character_set_name');
@mysql_change_user := GetAddress('mysql_change_user');
end;
end;
function FreeModule: Boolean;
begin
FreeLibrary(ModuleHandle);
Result:=true;
end;
end.
//------------------------------------------------------------------//
NOTE - unit I got and improved a little from the internet
USING - first of all you must specify library:
win32 function ModulePath:='c:\mysql\'
linux function ModulePath:='/home/user/mysql/'
win32 function ModuleName:='libmysql.dll'
linux function ModuleName:='libmysqlclient.so'
- second you must init module:
win32&linux function LoadModule: Boolean;
- then you can connect and use database
//------------------------------------------------------------------//
now here is Delphi&Kylix unit that I made and it is using previous unit and
it is easier to use:
//------------------------------------------------------------------//
unit sql;
interface
uses
qmysql,
SysUtils,
Classes
;
type
PSQL = PMYSQL;
PSQLRES = PMYSQL_RES;
PSQLROW = PMYSQL_ROW;
PSQLROWS = PMYSQL_ROWS;
PSQLFIELD = PMYSQL_FIELD;
function initmodule(
path,
name: string
): boolean;
function connect(
host,
user,
pwd,
db,
sock: string;
port: integer;
var sql: PSQL
): boolean;
function query(
sql: PSQL;
squery: string;
var rezult: PSQLRES
): integer;
function countrows(
rezult: PSQLRES
): integer;
function countfields(
rezult: PSQLRES
): integer;
function readrow(
rezult: PSQLRES;
index: integer
): TStringList;
function readfield(
rezult: PSQLRES;
row,
field: integer
): string;
function close(
sql: PSQL
): boolean;
function createdb(
sql: PSQL;
db: string
): boolean;
function dropdb(
sql: PSQL;
db: string
): boolean;
function killid(
sql: PSQL;
pid: integer
): boolean;
procedure ping(
sql: PSQL
);
function status(
sql: PSQL
): string;
function serverinfo(
sql: PSQL
): string;
function clientinfo(
sql: PSQL
): string;
function hostinfo(
sql: PSQL
): string;
function protocolinfo(
sql: PSQL
): string;
function readfields(
rezult: PSQLRES
): TStringList;
implementation
function initmodule(
path,
name: string
): boolean;
begin
qmysql.ModulePath:=path;
qmysql.ModuleName:=name;
result:=qmysql.LoadModule;
end;
function connect(
host,
user,
pwd,
db,
sock: string;
port: integer;
var sql: PSQL
): boolean;
var
rez: PSQL;
begin
sql:=qmysql.mysql_init(nil);
rez:=qmysql.mysql_real_connect(
sql,
pchar(host),
pchar(user),
pchar(pwd),
pchar(db),
port,
pchar(sock),
0
);
result:=(reznil);
if not result then sql:=nil;
end;
function query(
sql: PSQL;
squery: string;
var rezult: PSQLRES
): integer;
begin
qmysql.mysql_free_result(rezult);
qmysql.mysql_real_query(sql, pchar(squery), length(squery));
rezult:=qmysql.mysql_store_result(sql);
if rezultnil then
result:=rezult.row_count
else if qmysql.mysql_errno(sql)=0 then result:=0
else result:=-1;
end;
function countrows(
rezult: PSQLRES
): integer;
begin
result:=qmysql.mysql_num_rows(rezult);
end;
function countfields(
rezult: PSQLRES
): integer;
begin
result:=qmysql.mysql_num_fields(rezult);
end;
function readrow(
rezult: PSQLRES;
index: integer
): TStringList;
var
row: PSQLROW;
i: integer;
begin
result:=TStringList.Create;
qmysql.mysql_data_seek(rezult, index);
row:=qmysql.mysql_fetch_row(rezult);
if row=nil then exit;
for i:=0 to rezult.field_count-1 do
result.Add(row[i]);
end;
function readfield(
rezult: PSQLRES;
row,
field: integer
): string;
var
prow: PSQLROW;
begin
result:='';
qmysql.mysql_data_seek(rezult, row);
prow:=qmysql.mysql_fetch_row(rezult);
if prow=nil then exit;
result:=prow[field];
end;
function close(
sql: PSQL
): boolean;
begin
qmysql.mysql_close(sql);
result:=true;
end;
function createdb(
sql: PSQL;
db: string
): boolean;
begin
result:=(qmysql.mysql_create_db(sql, pchar(db))=0);
end;
function dropdb(
sql: PSQL;
db: string
): boolean;
begin
result:=(qmysql.mysql_drop_db(sql, pchar(db))=0);
end;
function killid(
sql: PSQL;
pid: integer
): boolean;
begin
result:=(qmysql.mysql_kill(sql, pid)=0);
end;
procedure ping(
sql: PSQL
);
begin
qmysql.mysql_ping(sql);
end;
function status(
sql: PSQL
): string;
begin
result:=qmysql.mysql_stat(sql);
end;
function serverinfo(
sql: PSQL
): string;
begin
result:=qmysql.mysql_get_server_info(sql);
end;
function clientinfo(
sql: PSQL
): string;
begin
result:=qmysql.mysql_get_client_info();
end;
function hostinfo(
sql: PSQL
): string;
begin
result:=qmysql.mysql_get_host_info(sql);
end;
function protocolinfo(
sql: PSQL
): string;
begin
result:=inttostr(qmysql.mysql_get_proto_info(sql));
end;
function readfields(
rezult: PSQLRES
): TStringList;
var
i: integer;
begin
result:=TStringList.Create;
for i:=0 to rezult.field_count-1 do
result.Add(qmysql.mysql_fetch_field_direct(rezult, i).name);
end;
end.
//------------------------------------------------------------------//
and here is a little demo application (console win32&linux)
//------------------------------------------------------------------//
program test_mysql;
{$APPTYPE CONSOLE}
uses
{$IFDEF WIN32}
Windows,
Messages,
WinSvc,
{$ENDIF}
{$IFDEF LINUX}
Libc,
{$ENDIF}
qmysql,
SysUtils;
var
sql: PMYSQL;
rez: PMYSQL_RES;
connected: Boolean=false;
cmd: String;
{$IFDEF WIN32}
written: Cardinal;
servrun: Boolean=false;
sqh, sch: SC_HANDLE;
ss: _SERVICE_STATUS;
outh: THANDLE;
curold, curinfo: _CONSOLE_CURSOR_INFO;
top, oldcoord: _COORD;
oldcfg: _CONSOLE_SCREEN_BUFFER_INFO;
{$ENDIF}
procedure connect;
var
host, user, password, port {$IFDEF LINUX}, sock {$ENDIF}: String;
begin
writeln('');
writeln(' connecting...');
try
Delete(cmd, 1, Pos(#$20, cmd));
host:=Copy(cmd, 1, Pos(#$20, cmd)-1);
Delete(cmd, 1, Pos(#$20, cmd));
user:=Copy(cmd, 1, Pos(#$20, cmd)-1);
Delete(cmd, 1, Pos(#$20, cmd));
password:=Copy(cmd, 1, Pos(#$20, cmd)-1);
Delete(cmd, 1, Pos(#$20, cmd));
{$IFDEF WIN32}
port:=cmd;
{$ENDIF}
{$IFDEF LINUX}
port:=Copy(cmd, 1, Pos(#$20, cmd)-1);
Delete(cmd, 1, Pos(#$20, cmd));
sock:=cmd;
{$ENDIF}
qmysql.ModulePath:=ExtractFilePath(ExpandFileName(ParamStr(0)));
{$IFDEF WIN32}
qmysql.ModuleName:='libmysql.dll';
{$ENDIF}
{$IFDEF LINUX}
qmysql.ModuleName:='libmysqlclient.so';
{$ENDIF}
if not qmysql.LoadModule then begin
writeln(' error, cannot load MySQL module ', qmysql.ModuleName, #$0d#$0a, ' from ', qmysql.ModulePath);
readln;
halt(255);
end;
sql:=qmysql.mysql_init(nil);
if qmysql.mysql_real_connect(
sql,
PChar(host),
PChar(user),
PChar(password),
'instant',
StrtoIntDef(port, 3306),
{$IFDEF WIN32}
nil,
{$ENDIF}
{$IFDEF LINUX}
PChar(sock),
{$ENDIF}
0
)=nil
then begin
writeln(' cannot connect, please verify arguments');
writeln('');
end
else begin
writeln(' connected to MySQL,');
writeln(' thread id ', qmysql.mysql_thread_id(sql), ',');
writeln(' server info ', qmysql.mysql_get_server_info(sql), ',');
writeln(' client info ', qmysql.mysql_get_client_info, ',');
writeln(' host info ', qmysql.mysql_get_host_info(sql), ',');
writeln(' protocol info ', qmysql.mysql_get_proto_info(sql));
writeln(' charset ', qmysql.mysql_character_set_name(sql));
writeln(' ', LowerCase(qmysql.mysql_stat(sql)));
writeln('');
connected:=true;
end;
except
end;
end;
function EmptyString(Str: String; Max: Integer; Ch: Char): String;
var
i: Integer;
begin
Result:=Str;
if MaxLength(Str) then for i:=1 to Max-Length(Str) do Result:=Result+Ch;
end;
procedure execute(cmd: String);
var
i, fields, rows, rows1: Cardinal;
row: PMYSQL_ROW;
tmpstr: String;
maxs: array[0..4095] of Integer;
err: PChar;
begin
FillChar(maxs, Sizeof(maxs), 0);
qmysql.mysql_real_query(sql, PChar(cmd), Length(cmd));
rez:=qmysql.mysql_store_result(sql);
FillChar(err, Sizeof(err), 0);
err:=qmysql.mysql_error(sql);
writeln('');
if rez=nil then begin
if Length(err)=0 then writeln(' query ok')
else writeln(' ', LowerCase(err));
end
else begin
if rez.row_count=0 then writeln(' empty result')
else begin
writeln('');
fields:=qmysql.mysql_num_fields(rez);
rows:=qmysql.mysql_num_rows(rez);
for i:=0 to fields-1 do
if Length(mysql_fetch_field_direct(rez, i).name)maxs[i] then maxs[i]:=Length(mysql_fetch_field_direct(rez, i).name)+2;
for rows1:=0 to rows-1 do begin
qmysql.mysql_data_seek(rez, rows1);
row:=qmysql.mysql_fetch_row(rez);
for i:=0 to fields-1 do
if Length(row[i])maxs[i] then maxs[i]:=Length(row[i])+2;
end;
tmpstr:='';
for i:=0 to fields-1 do
tmpstr:=tmpstr+'|'+EmptyString(mysql_fetch_field_direct(rez, i).name, maxs[i], ' ');
tmpstr:=tmpstr+'|';
writeln(tmpstr);
tmpstr:='';
for i:=0 to fields-1 do
tmpstr:=tmpstr+'-'+EmptyString('', maxs[i], '-');
tmpstr:=tmpstr+'-';
writeln(tmpstr);
for rows1:=0 to rows-1 do begin
qmysql.mysql_data_seek(rez, rows1);
row:=qmysql.mysql_fetch_row(rez);
tmpstr:='';
for i:=0 to fields-1 do tmpstr:=tmpstr+'|'+EmptyString(row[i], maxs[i], ' ');
tmpstr:=tmpstr+'|';
writeln(tmpstr);
end;
tmpstr:='';
for i:=0 to fields-1 do
tmpstr:=tmpstr+'-'+EmptyString('', maxs[i], '-');
tmpstr:=tmpstr+'-';
writeln(tmpstr);
writeln('');
writeln(' ', rez.row_count, ' result rows');
end;
end;
qmysql.mysql_free_result(rez);
writeln('');
end;
procedure ShowHelp;
begin
writeln('');
writeln(' command available to test_mysql are:');
writeln(' - connect, connects to MySQL with specified parameters');
writeln(' parameters:');
writeln(' - hostname - host to connect to (eg. 193.231.249.1, localhost)');
writeln(' - username - user name to connect with');
writeln(' - password - user password');
writeln(' - port - MySQL socket (default 3306)');
writeln(' - socket - MySQL socket path (linux only)');
writeln(' - disconnect, disconnects current connection');
writeln(' - service, service commands (win32 only)');
writeln(' parameters:');
writeln(' - start - starts MySQL service');
writeln(' - stop - stops MySQL service');
writeln(' - status - retrieves MySQL service status');
writeln(' - chuser, change database user');
writeln(' parameters:');
writeln(' - username - new user name');
writeln(' - password - user password');
writeln(' - help, displays this help screen');
writeln(' - clear, clears display (win32 only)');
writeln(' - about, about MySQL Query');
writeln(' - quit, exits application');
writeln('');
end;
procedure ShowAbout;
begin
writeln('');
writeln(' MySQL query for Linux and Win32');
writeln(' console application');
writeln(' (C) 2003 Ionel Roman');
writeln(' more details at ionelroman@as.ro');
writeln('');
end;
procedure disconnect;
begin
if sqlnil then try qmysql.mysql_close(sql); except end;
connected:=false;
writeln('');
writeln(' disconnected');
writeln('');
end;
procedure Services;
{$IFDEF WIN32}
var
tmpstr: String;
tmp: PChar;
begin
FillChar(tmp, Sizeof(tmp), 0);
tmpstr:=cmd;
Delete(tmpstr, 1, Pos(#$20, tmpstr));
writeln('');
if LowerCase(tmpstr)='start' then begin
write(' starting service...');
if not StartService(
sqh,
0,
tmp
) then writeln(', error')
else begin
writeln(', ok');
servrun:=true;
end;
end else if LowerCase(tmpstr)='stop' then begin
write(' stopping service...');
if not ControlService(
sqh,
SERVICE_CONTROL_STOP,
ss
) then writeln(', error')
else begin
writeln(', ok');
servrun:=false;
end;
end else if LowerCase(tmpstr)='status' then begin
write(' retrieving service status...');
if not QueryServiceStatus(
sqh,
ss
) then writeln(', error')
else begin
writeln(', ok');
write(' type ');
case ss.dwServiceType of
SERVICE_WIN32_OWN_PROCESS : writeln('Win32 service in own process');
SERVICE_WIN32_SHARE_PROCESS : writeln('Win32 service sharing process');
SERVICE_KERNEL_DRIVER : writeln('Windows NT device driver');
SERVICE_FILE_SYSTEM_DRIVER : writeln('Windows NT file system driver');
SERVICE_INTERACTIVE_PROCESS : writeln('Win32 service desktop interact process');
end;
write(' state ');
case ss.dwCurrentState of
SERVICE_STOPPED : writeln('not running');
SERVICE_START_PENDING : writeln('starting');
SERVICE_STOP_PENDING : writeln('stopping');
SERVICE_RUNNING : writeln('running');
SERVICE_CONTINUE_PENDING : writeln('continue pending');
SERVICE_PAUSE_PENDING : writeln('pause pending');
SERVICE_PAUSED : writeln('paused');
end;
end;
end;
writeln('');
{$ENDIF}
{$IFDEF LINUX}
begin
writeln(#$0d#$0a, ' command not available on linux yet', #$0d#$0a);
{$ENDIF}
end;
procedure chuser;
var
usr, pwd: String;
begin
writeln('');
write(' changing user...');
Delete(cmd, 1, Pos(#$20, cmd));
usr:=Copy(cmd, 1, Pos(#$20, cmd)-1);
Delete(cmd, 1, Pos(#$20, cmd));
pwd:=cmd;
if qmysql.mysql_change_user(sql, PChar(usr), PChar(pwd), nil)=0 then writeln(', ok')
else writeln(', error');
writeln('');
end;
procedure clearscr;
{$IFDEF WIN32}
var
null: _COORD;
sb: _CONSOLE_SCREEN_BUFFER_INFO;
{$ENDIF}
begin
{$IFDEF WIN32}
if oldcoord.Y=0 then begin
GetConsoleScreenBufferInfo(outh, sb);
oldcoord.Y:=sb.dwCursorPosition.Y;
end;
null.X:=0;
null.Y:=0;
FillConsoleOutputAttribute(
outh,
0,
MAXDWORD,
null,
written
);
FillConsoleOutputAttribute(
outh,
{FOREGROUND_INTENSITY or }FOREGROUND_BLUE or FOREGROUND_RED or FOREGROUND_GREEN or
{BACKGROUND_INTENSITY or }BACKGROUND_BLUE or BACKGROUND_RED or BACKGROUND_GREEN,
MAXDWORD,
null,
written
);
SetConsoleCursorPosition(outh, null);
{$ENDIF}
{$IFDEF LINUX}
writeln(#$0d#$0a, ' command not available on linux yet', #$0d#$0a);
{$ENDIF}
end;
begin
{$IFDEF WIN32}
outh:=GetStdHandle(STD_OUTPUT_HANDLE);
top.X:=0;
top.Y:=0;
GetConsoleScreenBufferInfo(outh, oldcfg);
FillConsoleOutputAttribute(outh, {BACKGROUND_INTENSITY or }BACKGROUND_RED or BACKGROUND_GREEN or BACKGROUND_BLUE, MAXDWORD, top, written);
SetConsoleTextAttribute(outh, {BACKGROUND_INTENSITY or }BACKGROUND_RED or BACKGROUND_GREEN or BACKGROUND_BLUE);
curinfo.dwSize:=50;
curinfo.bVisible:=true;
GetConsoleCursorInfo(outh, curold);
SetConsoleCursorInfo(outh, curinfo);
ClearScr;
SetConsoleTitle('MySQL query demo for Linux and Win32');
{$ENDIF}
writeln('');
writeln(' MySQL Query Demo for Linux and Win32');
writeln(' v1.0.1 (C) 2003 Ionel Roman');
writeln('');
writeln('');
writeln(' MySQL query console application starting...');
writeln(' type to see available command list');
writeln('');
{$IFDEF WIN32}
sch:=OpenSCManager(
nil,
nil,
SC_MANAGER_ALL_ACCESS
);
sqh:=0;
if sch0 then sqh:=OpenService(
sch,
'MySQL',
SERVICE_ALL_ACCESS
);
if sqh0 then if QueryServiceStatus(
sqh,
ss
) then
if ss.dwCurrentStateSERVICE_RUNNING then begin
writeln('MySQL service is not running,');
writeln('use to start it');
writeln('');
end
else servrun:=true;
{$ENDIF}
repeat
write('cmd: ');
readln(cmd);
if cmd='' then
else begin
if LowerCase(cmd)='quit' then Break;
if LowerCase(cmd)='help' then ShowHelp
else if LowerCase(cmd)='about' then ShowAbout
else if Pos('connect', LowerCase(cmd))=1 then {$IFDEF WIN32}
if servrun then connect
else begin
writeln('');
writeln(' service not running');
writeln(' use ');
writeln(' to start the service');
writeln('');
end
{$ENDIF}
{$IFDEF LINUX}
connect
{$ENDIF}
else if Pos('disconnect', LowerCase(cmd))=1 then disconnect
else if Pos('service', LowerCase(cmd))=1 then services
else if Pos('clear', LowerCase(cmd))=1 then clearscr
else begin
{$IFDEF WIN32}
if not servrun then begin
writeln('');
writeln(' service not running');
writeln(' use ');
writeln(' to start the service');
writeln('');
end;
if not connected then begin
writeln('');
writeln(' not connected');
writeln(' use ');
writeln(' to connect to MySQL');
writeln('');
end;
if servrun and connected then if Pos('chuser', LowerCase(cmd))=1 then chuser
else execute(cmd);
{$ENDIF}
{$IFDEF LINUX}
if not connected then begin
writeln('');
writeln(' not connected');
writeln(' use ');
writeln(' to connect to MySQL');
writeln('');
end;
if connected then if Pos('chuser', LowerCase(cmd))=1 then chuser
else execute(cmd);
{$ENDIF}
end;
end;
until LowerCase(cmd)='quit';
writeln('');
write(' shuting down icsql');
if connected then try qmysql.mysql_close(sql); sleep(200); write('.'); except end;
{$IFDEF WIN32}
CloseServiceHandle(sch);
sleep(200);
write('.');
CloseServiceHandle(sqh);
sleep(200);
write('.');
SetConsoleCursorInfo(outh, curold);
FillConsoleOutputAttribute(outh, oldcfg.wAttributes, MAXDWORD, top, written);
SetConsoleTextAttribute(outh, oldcfg.wAttributes);
{$ENDIF}
writeln(' all done, bye');
{$IFDEF WIN32}
SetConsoleCursorPosition(outh, oldcoord);
{$ENDIF}
end.
//------------------------------------------------------------------//
enjoy all, best regards, Ionel Roman
mail me with improvements at ionelroman@as.ro
]]>