unit TDBUnit;
interface
uses Uni, UniProvider, MySQLUniProvider, Codes, SysUtils, Forms, MemData,
Data.DB, DBAccess, Classes, DADump, UniDump,
vcl.Dialogs;
Type
TDBMySQL =
class
private
FMySQlConnection: TUniConnection;
FMySQLProvider: TUniProvider;
FMyQuery_Ticket: TUniQuery;
FMyDataSource_Ticket: TUniDataSource;
private
procedure TicketAfterScroll(DataSet: TDataSet);
procedure TicketAfterInsert (DataSet: TDataSet);
//Die nachstehende Procedure möchte ich im Create der BeforeUpdateExecute-Ereignis der FMyQuery_Ticket zuweisen
procedure TicketBeforeUpdate(Sender: TDataSet;
var StatementTypes: TStatementTypes;
var Params: TDAParams);
procedure Query_Ticket_SQL;
procedure Query_Ticket_Insert;
procedure Query_Ticket_Update;
public
constructor create;
property MyConnection: TUniConnection
read FMySQLConnection
write FMySQLConnection;
property MyProvider: TUniProvider
read FMySQLProvider
write FMySQLProvider;
property DSTicket: TUniDataSource
read FMyDataSource_Ticket
write FMyDataSource_Ticket;
public //Procedure
procedure Connect(Connection: TUniConnection; SQL_Verzeichnis: string='
SQL.set'; Server:
string ='
';
User:
string ='
'; Passwort:
string ='
'; Database:
string ='
'; Provider:
string ='
'; Port:
string ='
0');
procedure ExecQuery (
query: TUniQuery;
var Cols: TCols;
var Rows: TRows; AddRows: integer);
procedure Disconnect (Connection: TUniConnection);
end;
var MySQL: TDBMySQL;
implementation
procedure TDBMySQL.TicketAfterInsert (DataSet: TDataSet);
begin
DataSet.FieldByName('
ticket_schliessung').Clear;
DataSet.FieldByName('
ticket_eroeffnung').AsDateTime:=now();
DataSet.FieldByName('
status_id').AsInteger:=1;
DataSet.FieldByName('
computer_id').AsString:=FMyDataSource_Ticket.DataSet.FieldByName('
computer_id').AsString;
DataSet.FieldByName('
deleted').AsBoolean:=false;
end;
procedure TDBMySQL.TicketAfterScroll(DataSet: TDataSet);
begin
FMyQuery_Kommentare.ParamByName('
ticket_id').AsInteger:=FMyQuery_Ticket.FieldByName('
ticket_id').AsInteger;
FMyQuery_Kommentare.Active:=true;
FMyQuery_Kommentare.Refresh;
end;
procedure TDBMySQL.KommentarAfterInsert(DataSet: TDataSet);
begin
DataSet.FieldByName('
ticket_id').AsInteger:=FMyQuery_Ticket.FieldByName('
ticket_id').AsInteger;
DataSet.FieldByName('
user_id').AsInteger:=2;
DataSet.FieldByName('
info_user').AsBoolean:=FMyDataSource_Kommentare.DataSet.FieldByName('
info_user').AsBoolean;
DataSet.FieldByName('
kommentar').AsString:=FMyDataSource_Kommentare.DataSet.FieldByName('
kommentar').AsString;
DataSet.FieldByName('
deleted').AsBoolean:=false;
end;
//Hier sind die Einzelheiten der Query, die dem BeforeUpdateExecute-Ereignis zugewiesen werden soll
procedure TDBMySQL.TicketBeforeUpdate(Sender: TDataSet;
var StatementTypes: TStatementTypes;
var Params: TDAParams);
begin
(Sender
as TDataSet).FieldByName('
ticket_thema').AsString:=FMyDataSource_Ticket.DataSet.FieldByName('
ticket_thema').AsString;
(Sender
as TDataSet).FieldByName('
kategorie_id').AsInteger:=FMyDataSource_Ticket.DataSet.FieldByName('
kategorie_id').AsInteger;
(Sender
as TDataSet).FieldByName('
ticket_eroeffnung').AsDateTime:=FMyDataSource_Ticket.DataSet.FieldByName('
ticket_eroeffnung').AsDateTime;
(Sender
as TDataSet).FieldByName('
ticket_schliessung').AsDateTime:=FMyDataSource_Ticket.DataSet.FieldByName('
ticket_schliessung').AsDateTime;
(Sender
as TDataSet).FieldByName('
status_id').AsInteger:=FMyDataSource_Ticket.DataSet.FieldByName('
status_id').AsInteger;
(Sender
as TDataSet).FieldByName('
computer_id').AsString:=FMyDataSource_Ticket.DataSet.FieldByName('
computer_id').AsString;
(Sender
as TDataSet).FieldByName('
ticket_thema').AsString:=FMyDataSource_Ticket.DataSet.FieldByName('
ticket_thema').AsString;
(Sender
as TDataSet).FieldByName('
deleted').AsBoolean:=false;
end;
procedure TDBMySQL.Query_Ticket_SQL;
begin
FMyQuery_Ticket.SQL.Add('
select t.ticket_id, '+
'
t.ticket_thema, '+
'
t.kategorie_id, '+
'
k.kategorie_name, '+
'
t.ticket_eroeffnung, '+
'
t.ticket_schliessung, '+
'
t.status_id, '+
'
s.status_text, '+
'
t.computer_id '+
'
from ticket as t '+
'
left join kategorie as k on k.kategorie_id=t.kategorie_id '+
'
left join status as s on s.status_id=t.status_id '+
'
where (t.computer_id= :computer_id '+
'
or t.user_computer= :cmputer )'+
'
and t.deleted = false '+
'
order by t.ticket_id');
FMyQuery_Ticket.ParamByName('
computer_id').AsString:=GetComputerName;
FMyQuery_Ticket.Active:=true;
end;
procedure TDBMySQL.Query_Ticket_Insert;
begin
FMyQuery_Ticket.SQLInsert.Add('
INSERT INTO ticket '+
'
(ticket_id, ticket_thema, kategorie_id, '+
'
ticket_eroeffnung, ticket_schliessung, '+
'
status_id, computer_id, deleted)' +
'
VALUES '+
'
(:ticket_id, :ticket_thema, :kategorie_id, '+
'
SYSDATE(), :ticket_schliessung, '+
'
1, :computer_id, :deleted)')
end;
procedure TDBMySQL.Query_Ticket_Update;
begin
FMyQuery_Ticket.SQLUpdate.Add('
update ticket set '+
'
ticket_thema= :ticket_thema, '+
'
kategorie_id= :kategorie_id, '+
'
ticket_eroeffnung= :eroeffnung, '+
'
ticket_schliessung= :schliessung, '+
'
status_id= :status_id, '+
'
computer_id= :computer_id, '+
'
deleted= :deleted '+
'
where ticket_id= :ticket_id');
end;
constructor TDBMySQL.create;
begin
//Connection und allgemeine Querys
FMySQLConnection:= TUniConnection.Create(
nil);
FMySQLProvider:= TMySQLUniProvider.Create(
nil);
//Spezifische Querys
FMyQuery_Ticket:= TUniQuery.Create(
nil);
//DataSources für die spezifischen Querys
FMyDataSource_Ticket:= TUniDataSource.Create(
nil);
//Verbindung der Connection und Zuweisung zu den allgemeinen Querys
Connect(FMySQLConnection);
//Zuweisung der Connection zu den spezifischen Querys
FMyQuery_Ticket.Connection:= FMySQLConnection;
//Zuweisung der spezifischen Querys als DataSet zu den DataSources
FMyDataSource_Ticket.DataSet:= FMyQuery_Ticket;
//Connection-Options
FMySQLConnection.Options.LocalFailover:= true;
FMySQLConnection.OnConnectionLost:=LostConnection;
//Querys ausführen
Query_Ticket_SQL;
//Object-Procedure
FMyQuery_Ticket.AfterScroll:=TicketAfterScroll;
FMyQuery_Ticket.AfterInsert:=TicketAfterInsert;
FMyQuery_Ticket.BeforeUpdateExecute:=Query_Ticket_Update;
//Hier kommt der Fehler
end;
{=============================================================================}
procedure TDBMySQL.Connect (Connection: TUniConnection; SQL_Verzeichnis: string='
SQL.set'; Server:
string ='
';
User:
string ='
'; Passwort:
string ='
'; Database:
string ='
'; Provider:
string ='
'; Port:
string ='
0');
var Datei: TextFile;
MyString:
String;
begin
if (Server='
')
and (User='
')
and (Passwort='
')
and (Database='
')
and (Provider='
')
and (Port='
0')
then begin
AssignFile(Datei, SQL_Verzeichnis);
Reset(Datei);
try
ReadLn(Datei, MyString);
Connection.Server := copy(MyString, pos('
:', MyString)+1, length(MyString));
ReadLn(Datei, MyString);
Connection.Username := copy(MyString, pos('
:', MyString)+1, length(MyString));
ReadLn(Datei, MyString);
Connection.Password := copy(MyString, pos('
:', MyString)+1, length(MyString));
ReadLn(Datei, MyString);
Connection.Database := copy(MyString, pos('
:', MyString)+1, length(MyString));
ReadLn(Datei, MyString);
Connection.ProviderName := copy(MyString, pos('
:', MyString)+1, length(MyString));
ReadLn(Datei, MyString);
Connection.Port := strtoint(copy(MyString, pos('
:', MyString)+1, length(MyString)));
finally
CloseFile(Datei);
end;
end
else begin
Connection.Server := Server;
Connection.Username := User;
Connection.Password := Passwort;
Connection.Database := Database;
Connection.ProviderName := Provider;
Connection.Port := StrToInt(Port);
end;
Connection.Connected:=true;
end;
{=============================================================================}
procedure TDBMySQL.ExecQuery (
query: TUniQuery;
var Cols: TCols;
var Rows: TRows; AddRows: integer);
var I, J: integer;
begin
Query.SQL.Text:=LowerCase(
Query.SQL.Text);
//Query.Active:=true;
Query.Open;
SetLength(Cols,0);
SetLength(Rows,0, 0);
SetLength(Cols,
Query.FieldCount);
SetLength(Rows,
Query.FieldCount,
Query.RecordCount+AddRows);
//+1 für FloatFooter
for J:=0
to Query.FieldCount -1
do begin
Cols[J]:=
Query.Fields.Fields[J].FieldName;
end;
for I:=0
to Query.RecordCount -1
do begin
for J:=0
to Query.FieldCount -1
do begin
Rows[J,I]:=
Query.Fields.Fields[J].AsString;
end;
query.Next;
end;
end;
{=============================================================================}
procedure TDBMySQL.Disconnect (Connection: TUniConnection);
begin
Connection.Connected:=false;
end;
{=============================================================================}
initialization
MySQL := TDBMySQL.Create;
finalization
if MySQL <>
nil then begin
MySQL.Disconnect(
MySQL.MyConnection);
MySQL.Free;
end;
end.