8
Classes, SysUtils,sqldb,db,dynlibs,
11
{$DEFINE TConnectionName:=TMySQL50Connection}
12
{$DEFINE TTransactionName:=TMySQL50Transaction}
13
{$DEFINE TCursorName:=TMySQL50Cursor}
17
{$DEFINE TConnectionName:=TMySQL41Connection}
18
{$DEFINE TTransactionName:=TMySQL41Transaction}
19
{$DEFINE TCursorName:=TMySQL41Cursor}
21
{$IFDEF mysql4} // temporary backwards compatibility for Lazarus
23
{$DEFINE TConnectionName:=TMySQLConnection}
24
{$DEFINE TTransactionName:=TMySQLTransaction}
25
{$DEFINE TCursorName:=TMySQLCursor}
28
{$DEFINE TConnectionName:=TMySQL40Connection}
29
{$DEFINE TTransactionName:=TMySQL40Transaction}
30
{$DEFINE TCursorName:=TMySQL40Cursor}
36
TTransactionName = Class(TSQLHandle)
40
TCursorName = Class(TSQLCursor)
43
FRes: PMYSQL_RES; { Record pointer }
49
ParamBinding : TParamBinding;
50
ParamReplaceString : String;
51
MapDSRowToMSQLRow : array of integer;
54
TConnectionName = class (TSQLConnection)
60
FDidConnect : Boolean;
61
function GetClientInfo: string;
62
function GetServerStatus: String;
63
procedure ConnectMySQL(var HMySQL : PMySQL;H,U,P : pchar);
65
function StrToStatementType(s : string) : TStatementType; override;
66
Procedure ConnectToServer; virtual;
67
Procedure SelectDatabase; virtual;
68
function MySQLDataType(AType: enum_field_types; ASize, ADecimals: Integer; var NewType: TFieldType; var NewSize: Integer): Boolean;
69
function MySQLWriteData(AType: enum_field_types;ASize: Integer; AFieldType: TFieldType;Source, Dest: PChar): Boolean;
70
// SQLConnection methods
71
procedure DoInternalConnect; override;
72
procedure DoInternalDisconnect; override;
73
function GetHandle : pointer; override;
75
function GetAsSQLText(Field : TField) : string; overload; virtual;
76
function GetAsSQLText(Param : TParam) : string; overload; virtual;
78
Function AllocateCursorHandle : TSQLCursor; override;
79
Procedure DeAllocateCursorHandle(var cursor : TSQLCursor); override;
80
Function AllocateTransactionHandle : TSQLHandle; override;
82
procedure PrepareStatement(cursor: TSQLCursor;ATransaction : TSQLTransaction;buf : string; AParams : TParams); override;
83
procedure UnPrepareStatement(cursor:TSQLCursor); override;
84
procedure FreeFldBuffers(cursor : TSQLCursor); override;
85
procedure Execute(cursor: TSQLCursor;atransaction:tSQLtransaction;AParams : TParams); override;
86
procedure AddFieldDefs(cursor: TSQLCursor; FieldDefs : TfieldDefs); override;
87
function Fetch(cursor : TSQLCursor) : boolean; override;
88
function LoadField(cursor : TSQLCursor;FieldDef : TfieldDef;buffer : pointer) : boolean; override;
89
function GetTransactionHandle(trans : TSQLHandle): pointer; override;
90
function Commit(trans : TSQLHandle) : boolean; override;
91
function RollBack(trans : TSQLHandle) : boolean; override;
92
function StartdbTransaction(trans : TSQLHandle; AParams : string) : boolean; override;
93
procedure CommitRetaining(trans : TSQLHandle); override;
94
procedure RollBackRetaining(trans : TSQLHandle); override;
95
procedure UpdateIndexDefs(var IndexDefs : TIndexDefs;TableName : string); override;
98
Property ServerInfo : String Read FServerInfo;
99
Property HostInfo : String Read FHostInfo;
100
property ClientInfo: string read GetClientInfo;
101
property ServerStatus : String read GetServerStatus;
103
property Dialect : integer read FDialect write FDialect;
104
property DatabaseName;
106
property KeepConnection;
107
property LoginPrompt;
112
EMySQLError = Class(Exception);
121
SErrServerConnectFailed = 'Server connect failed.';
122
SErrDatabaseSelectFailed = 'failed to select database: %s';
123
SErrDatabaseCreate = 'Failed to create database: %s';
124
SErrDatabaseDrop = 'Failed to drop database: %s';
125
SErrNoData = 'No data for record';
126
SErrExecuting = 'Error executing query: %s';
127
SErrFetchingdata = 'Error fetching row data: %s';
128
SErrGettingResult = 'Error getting result set: %s';
129
SErrNoQueryResult = 'No result from query.';
130
SErrNotversion50 = 'TMySQL50Connection can not work with the installed MySQL client version (%s).';
131
SErrNotversion41 = 'TMySQL41Connection can not work with the installed MySQL client version (%s).';
132
SErrNotversion40 = 'TMySQL40Connection can not work with the installed MySQL client version (%s).';
134
Procedure MySQlError(R : PMySQL;Msg: String;Comp : TComponent);
142
MySQLMsg:=Strpas(mysql_error(R));
143
DatabaseErrorFmt(Msg,[MySQLMsg],Comp);
146
DatabaseError(Msg,Comp);
149
function TConnectionName.StrToStatementType(s : string) : TStatementType;
153
if s = 'show' then exit(stSelect);
154
result := inherited StrToStatementType(s);
158
function TConnectionName.GetClientInfo: string;
164
// To make it possible to call this if there's no connection yet
165
B:=(MysqlLibraryHandle=Nilhandle);
169
Result:=strpas(mysql_get_client_info());
176
function TConnectionName.GetServerStatus: String;
179
Result := mysql_stat(FMYSQL);
182
procedure TConnectionName.ConnectMySQL(var HMySQL : PMySQL;H,U,P : pchar);
185
HMySQL := mysql_init(HMySQL);
186
HMySQL:=mysql_real_connect(HMySQL,PChar(H),PChar(U),Pchar(P),Nil,0,Nil,0);
188
MySQlError(Nil,SErrServerConnectFailed,Self);
191
function TConnectionName.GetAsSQLText(Field : TField) : string;
196
if (not assigned(field)) or field.IsNull then Result := 'Null'
197
else if field.DataType = ftString then
199
Getmem(esc_str,sizeof(field.asstring)*2+1);
200
mysql_real_escape_string(FMySQL,esc_str,pchar(field.asstring),length(field.asstring));
201
Result := '''' + esc_str + '''';
204
else Result := inherited GetAsSqlText(field);
207
function TConnectionName.GetAsSQLText(Param: TParam) : string;
212
if (not assigned(param)) or param.IsNull then Result := 'Null'
213
else if param.DataType = ftString then
215
Getmem(esc_str,sizeof(param.asstring)*2+1);
216
mysql_real_escape_string(FMySQL,esc_str,pchar(param.asstring),length(param.asstring));
217
Result := '''' + esc_str + '''';
220
else Result := inherited GetAsSqlText(Param);
224
procedure TConnectionName.ConnectToServer;
233
ConnectMySQL(FMySQL,pchar(H),pchar(U),pchar(P));
234
FServerInfo := strpas(mysql_get_server_info(FMYSQL));
235
FHostInfo := strpas(mysql_get_host_info(FMYSQL));
238
procedure TConnectionName.SelectDatabase;
240
if mysql_select_db(FMySQL,pchar(DatabaseName))<>0 then
241
MySQLError(FMySQL,SErrDatabaseSelectFailed,Self);
244
procedure TConnectionName.DoInternalConnect;
246
FDidConnect:=(MySQLLibraryHandle=NilHandle);
250
if copy(strpas(mysql_get_client_info()),1,3)<>'5.0' then
251
Raise EInOutError.CreateFmt(SErrNotversion50,[strpas(mysql_get_client_info())]);
254
if copy(strpas(mysql_get_client_info()),1,3)<>'4.1' then
255
Raise EInOutError.CreateFmt(SErrNotversion41,[strpas(mysql_get_client_info())]);
257
if copy(strpas(mysql_get_client_info()),1,3)<>'4.0' then
258
Raise EInOutError.CreateFmt(SErrNotversion40,[strpas(mysql_get_client_info())]);
261
inherited DoInternalConnect;
266
procedure TConnectionName.DoInternalDisconnect;
268
inherited DoInternalDisconnect;
275
function TConnectionName.GetHandle: pointer;
280
function TConnectionName.AllocateCursorHandle: TSQLCursor;
282
Result:=TCursorName.Create;
285
Procedure TConnectionName.DeAllocateCursorHandle(var cursor : TSQLCursor);
291
function TConnectionName.AllocateTransactionHandle: TSQLHandle;
293
// Result:=TTransactionName.Create;
297
procedure TConnectionName.PrepareStatement(cursor: TSQLCursor;
298
ATransaction: TSQLTransaction; buf: string;AParams : TParams);
300
// if assigned(AParams) and (AParams.count > 0) then
301
// DatabaseError('Parameters (not) yet supported for the MySQL SqlDB connection.',self);
302
With Cursor as TCursorName do
305
if assigned(AParams) and (AParams.count > 0) then
306
FStatement := AParams.ParseSQL(FStatement,false,psSimulated,paramBinding,ParamReplaceString);
307
if FStatementType=stSelect then
309
ConnectMySQL(FQMySQL,FMySQL^.host,FMySQL^.user,FMySQL^.passwd);
310
if mysql_select_db(FQMySQL,pchar(DatabaseName))<>0 then
311
MySQLError(FQMySQL,SErrDatabaseSelectFailed,Self);
315
procedure TConnectionName.UnPrepareStatement(cursor: TSQLCursor);
317
With Cursor as TCursorName do
319
mysql_close(FQMySQL);
324
procedure TConnectionName.FreeFldBuffers(cursor: TSQLCursor);
330
C:=Cursor as TCursorName;
331
if c.FStatementType=stSelect then
333
if (c.FQMySQL <> Nil) then
335
mysql_close(c.FQMySQL);
338
If (C.FRes<>Nil) then
340
Mysql_free_result(C.FRes);
343
SetLength(c.MapDSRowToMSQLRow,0);
346
procedure TConnectionName.Execute(cursor: TSQLCursor;
347
atransaction: tSQLtransaction;AParams : TParams);
354
C:=Cursor as TCursorName;
357
if Assigned(AParams) and (AParams.count > 0) then
358
for i := 0 to AParams.count -1 do
359
C.FStatement := stringreplace(C.FStatement,C.ParamReplaceString+inttostr(AParams[i].Index+1),GetAsSQLText(AParams[i]),[rfReplaceAll,rfIgnoreCase]);
360
if mysql_query(c.FQMySQL,Pchar(C.FStatement))<>0 then
361
MySQLError(c.FQMYSQL,Format(SErrExecuting,[StrPas(mysql_error(c.FQMySQL))]),Self)
364
C.RowsAffected := mysql_affected_rows(c.FQMYSQL);
365
C.LastInsertID := mysql_insert_id(c.FQMYSQL);
367
C.FRes:=mysql_use_result(c.FQMySQL);
372
function TConnectionName.MySQLDataType(AType: enum_field_types; ASize, ADecimals: Integer;
373
var NewType: TFieldType; var NewSize: Integer): Boolean;
377
FIELD_TYPE_TINY, FIELD_TYPE_SHORT, FIELD_TYPE_LONG, FIELD_TYPE_LONGLONG,
380
NewType := ftInteger;
384
FIELD_TYPE_NEWDECIMAL,
386
FIELD_TYPE_DECIMAL: if ADecimals < 5 then
396
FIELD_TYPE_FLOAT, FIELD_TYPE_DOUBLE:
401
FIELD_TYPE_TIMESTAMP, FIELD_TYPE_DATETIME:
403
NewType := ftDateTime;
416
FIELD_TYPE_VAR_STRING, FIELD_TYPE_STRING, FIELD_TYPE_ENUM, FIELD_TYPE_SET:
426
procedure TConnectionName.AddFieldDefs(cursor: TSQLCursor;
427
FieldDefs: TfieldDefs);
437
// Writeln('MySQL: Adding fielddefs');
438
C:=(Cursor as TCursorName);
441
// Writeln('res is nil');
442
MySQLError(c.FQMySQL,SErrNoQueryResult,Self);
444
// Writeln('MySQL: have result');
445
FC:=mysql_num_fields(C.FRes);
446
SetLength(c.MapDSRowToMSQLRow,FC);
451
field := mysql_fetch_field_direct(C.FRES, I);
452
// Writeln('MySQL: creating fielddef ',I+1);
454
if MySQLDataType(field^.ftype, field^.length, field^.decimals, DFT, DFS) then
456
TFieldDef.Create(FieldDefs, field^.name, DFT, DFS, False, TF);
457
c.MapDSRowToMSQLRow[TF-1] := I;
461
// Writeln('MySQL: Finished adding fielddefs');
464
function TConnectionName.Fetch(cursor: TSQLCursor): boolean;
470
C:=Cursor as TCursorName;
471
C.Row:=MySQL_Fetch_row(C.FRes);
472
Result:=(C.Row<>Nil);
475
function TConnectionName.LoadField(cursor : TSQLCursor;
476
FieldDef : TfieldDef;buffer : pointer) : boolean;
484
// Writeln('LoadFieldsFromBuffer');
485
C:=Cursor as TCursorName;
488
// Writeln('LoadFieldsFromBuffer: row=nil');
489
MySQLError(c.FQMySQL,SErrFetchingData,Self);
493
inc(Row,c.MapDSRowToMSQLRow[FieldDef.FieldNo-1]);
494
field := mysql_fetch_field_direct(C.FRES, c.MapDSRowToMSQLRow[FieldDef.FieldNo-1]);
496
Result := MySQLWriteData(field^.ftype, field^.length, FieldDef.DataType, Row^, Buffer);
499
function InternalStrToFloat(S: string): Extended;
507
for I := 1 to Length(S) do
509
if not (S[I] in ['0'..'9', '+', '-', 'E', 'e']) then
510
Tmp := Tmp + DecimalSeparator
514
Result := StrToFloat(Tmp);
517
function InternalStrToCurrency(S: string): Extended;
525
for I := 1 to Length(S) do
527
if not (S[I] in ['0'..'9', '+', '-', 'E', 'e']) then
528
Tmp := Tmp + DecimalSeparator
532
Result := StrToCurr(Tmp);
535
function InternalStrToDate(S: string): TDateTime;
541
EY := StrToInt(Copy(S,1,4));
542
EM := StrToInt(Copy(S,6,2));
543
ED := StrToInt(Copy(S,9,2));
544
if (EY = 0) or (EM = 0) or (ED = 0) then
547
Result:=EncodeDate(EY, EM, ED);
550
function InternalStrToDateTime(S: string): TDateTime;
557
EY := StrToInt(Copy(S, 1, 4));
558
EM := StrToInt(Copy(S, 6, 2));
559
ED := StrToInt(Copy(S, 9, 2));
560
EH := StrToInt(Copy(S, 12, 2));
561
EN := StrToInt(Copy(S, 15, 2));
562
ES := StrToInt(Copy(S, 18, 2));
563
if (EY = 0) or (EM = 0) or (ED = 0) then
566
Result := EncodeDate(EY, EM, ED);
567
Result := Result + EncodeTime(EH, EN, ES, 0);
570
function InternalStrToTime(S: string): TDateTime;
576
EH := StrToInt(Copy(S, 1, 2));
577
EM := StrToInt(Copy(S, 4, 2));
578
ES := StrToInt(Copy(S, 7, 2));
579
Result := EncodeTime(EH, EM, ES, 0);
582
function InternalStrToTimeStamp(S: string): TDateTime;
590
EY := StrToInt(Copy(S, 1, 4));
591
EM := StrToInt(Copy(S, 6, 2));
592
ED := StrToInt(Copy(S, 9, 2));
593
EH := StrToInt(Copy(S, 12, 2));
594
EN := StrToInt(Copy(S, 15, 2));
595
ES := StrToInt(Copy(S, 18, 2));
597
EY := StrToInt(Copy(S, 1, 4));
598
EM := StrToInt(Copy(S, 5, 2));
599
ED := StrToInt(Copy(S, 7, 2));
600
EH := StrToInt(Copy(S, 9, 2));
601
EN := StrToInt(Copy(S, 11, 2));
602
ES := StrToInt(Copy(S, 13, 2));
604
if (EY = 0) or (EM = 0) or (ED = 0) then
607
Result := EncodeDate(EY, EM, ED);
608
Result := Result + EncodeTime(EH, EN, ES, 0);;
611
function TConnectionName.MySQLWriteData(AType: enum_field_types;ASize: Integer; AFieldType: TFieldType;Source, Dest: PChar): Boolean;
626
FIELD_TYPE_TINY, FIELD_TYPE_SHORT, FIELD_TYPE_LONG,
633
Move(VI, Dest^, SizeOf(Integer));
638
VI := StrToInt64(Src)
641
Move(VI, Dest^, SizeOf(LargeInt));
644
FIELD_TYPE_NEWDECIMAL,
646
FIELD_TYPE_DECIMAL, FIELD_TYPE_FLOAT, FIELD_TYPE_DOUBLE:
647
if AFieldType = ftBCD then
649
VC := InternalStrToCurrency(Src);
650
Move(VC, Dest^, SizeOf(Currency));
655
VF := InternalStrToFloat(Src)
658
Move(VF, Dest^, SizeOf(Double));
660
FIELD_TYPE_TIMESTAMP:
663
VD := InternalStrToTimeStamp(Src)
666
Move(VD, Dest^, SizeOf(TDateTime));
671
VD := InternalStrToDateTime(Src)
674
Move(VD, Dest^, SizeOf(TDateTime));
679
VD := InternalStrToDate(Src)
682
Move(VD, Dest^, SizeOf(TDateTime));
687
VD := InternalStrToTime(Src)
690
Move(VD, Dest^, SizeOf(TDateTime));
692
FIELD_TYPE_VAR_STRING, FIELD_TYPE_STRING, FIELD_TYPE_ENUM, FIELD_TYPE_SET:
694
{ Write('Moving string of size ',asize,' : ');
704
Move(Source^, Dest^, ASize)
712
procedure TConnectionName.UpdateIndexDefs(var IndexDefs : TIndexDefs;TableName : string);
717
if not assigned(Transaction) then
718
DatabaseError(SErrConnTransactionnSet);
720
qry := tsqlquery.Create(nil);
721
qry.transaction := Transaction;
722
qry.database := Self;
727
sql.add('show index from ' + TableName);
731
while not qry.eof do with IndexDefs.AddIndexDef do
733
Name := trim(qry.fieldbyname('Key_name').asstring);
734
Fields := trim(qry.fieldbyname('Column_name').asstring);
735
If Name = 'PRIMARY' then options := options + [ixPrimary];
736
If qry.fieldbyname('Non_unique').asinteger = 0 then options := options + [ixUnique];
738
{ while (name = qry.fields[0].asstring) and (not qry.eof) do
740
Fields := Fields + ';' + trim(qry.Fields[2].asstring);
749
function TConnectionName.GetTransactionHandle(trans: TSQLHandle): pointer;
754
function TConnectionName.Commit(trans: TSQLHandle): boolean;
759
function TConnectionName.RollBack(trans: TSQLHandle): boolean;
764
function TConnectionName.StartdbTransaction(trans: TSQLHandle; AParams : string): boolean;
769
procedure TConnectionName.CommitRetaining(trans: TSQLHandle);
774
procedure TConnectionName.RollBackRetaining(trans: TSQLHandle);