ＬａｚａｒｕｓでMysqlに接続する
１，接続
２，読み込み（Select)
３，挿入（Insert）
４，アップデート（Update）
５，削除（Delete）
ポイント
TMySQL55Connection
TSQLQuery
TSQLTransaction
を、使用する。
プログラム
設定
uses Classes, SysUtils, FileUtil, Forms, Controls, Graphics, Dialogs, StdCtrls, Grids , mysql55conn // 追加 , sqldb // 追加 , db // 追加 , ComCtrls // 追加 ;
本体
procedure TForm1.FormCreate(Sender: TObject);
begin
MySQL55Connection1:= TMySQL55Connection.Create(nil);
MySQL55Connection1.HostName := 'xxx.xxx.xxx.xxx';
MySQL55Connection1.CharSet:='utf8mb4';
MySQL55Connection1.UserName := 'xxxx';
MySQL55Connection1.Password := 'xxx';
MySQL55Connection1.DatabaseName:='xxxxx';
SQLQuery1:= TSQLQuery.Create(nil);
SQLTransaction1:= TSQLTransaction.Create(nil);
SQLTransaction1.SQLConnection:=MySQL55Connection1;
button1.Caption:='search';
button2.Caption:='select';
button3.Caption:='insert';
button4.Caption:='update';
button5.Caption:='detale';
button6.Caption:='maketable';
StringGrid1.RowCount:=1;
StringGrid1.ColCount:=3;
StringGrid1.Cells[1,0]:='商品名';
StringGrid1.Cells[2,0]:='商品コード';
end;
procedure TForm1.Button1Click(Sender: TObject);
var
StrText:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
SQLQuery1.SQL.Text := 'select p_name from m_product where p_name="'+edit1.text+'" and p_code = "'+edit2.text+'" ';
SQLQuery1.Open;
if SQLQuery1.EOF then begin
StrText :='商品と商品コードが一致しません';
MessageDlg(StrText, mtInformation, [mbYes], 0);
end
else begin
StrText :='商品と商品コードが一致しました';
MessageDlg(StrText, mtInformation, [mbYes], 0);
end;
SQLQuery1.Close;
end;
MySQL55Connection1.Close();
end;
procedure TForm1.Button2Click(Sender: TObject);
var
i:integer;
s:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
SQLQuery1.SQL.Text := 'select p_name,p_code from m_product ';
SQLQuery1.Open;
SQLQuery1.Open;
i:=1;
while not SQLQuery1.EOF do
begin
if i+1>StringGrid1.RowCount then StringGrid1.RowCount:=StringGrid1.RowCount+1;
// s:= SQLQuery1.Fields[0].AsString;
// StringGrid1.Cells[i+1,0]:=SQLQuery1.Fields[0].AsString;
StringGrid1.Cells[1,i]:=SQLQuery1.FieldByName('p_name').AsString;
StringGrid1.Cells[2,i]:=SQLQuery1.FieldByName('p_code').AsString;
i:=i+1;
SQLQuery1.Next;
end;
StringGrid1.RowCount:=i;
SQLQuery1.Close;
MySQL55Connection1.Close;
end;
end;
procedure TForm1.Button3Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:='INSERT into m_product (p_name,p_code,update_date,create_date) VALUES';
sql:=sql+'("'+edit3.Text+'","'+edit4.Text+'",now(),now())';
MySQL55Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
Button2Click(Sender);
MySQL55Connection1.Close;
end;
procedure TForm1.Button4Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:='update m_product set p_code="'+edit6.Text+'" where p_name = "'+edit5.Text+'"';
MySQL55Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
Button2Click(Sender);
MySQL55Connection1.Close;
end;
procedure TForm1.Button5Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:='delete from m_product where p_name = "'+edit7.Text+'"';
MySQL55Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
edit8.Text:=sql;
Button2Click(Sender);
MySQL55Connection1.Close;
end;
procedure TForm1.Button6Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL55Connection1.Connected then MySQL55Connection1.Open;
if MySQL55Connection1.Connected then begin
SQLQuery1.DataBase := MySQL55Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:=' CREATE TABLE IF NOT EXISTS m_product ( ';
sql:=sql+'product_id INT(11) NOT NULL AUTO_INCREMENT,';
sql:=sql+'p_name VARCHAR(20) NOT NULL,';
sql:=sql+'p_code VARCHAR(50) NOT NULL,';
sql:=sql+'create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,';
sql:=sql+'update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,';
sql:=sql+'PRIMARY KEY ("product_id"))COLLATE="utf8mb4_general_ci" ENGINE=InnoDB ';
MySQL55Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
edit8.Text:=sql;
MySQL55Connection1.Close;
end;
サンプルのDB
CREATE TABLE m_product (
product_id INT(11) NOT NULL AUTO_INCREMENT,
p_name VARCHAR(20) NOT NULL,
p_code VARCHAR(50) NOT NULL,
create_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
update_date TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY ('product_id')
)
COLLATE='utf8mb4_general_ci'
ENGINE=InnoDB
;
SQLインジェクションは、未対応、修正が必要
参考
//http://www.366service.com/jp/qa/679747ee836bb2082ae2df224d6839ed