LazarusでMysqlに接続する
１，接続
２，読み込み（Select)
３，挿入（Insert）
４，アップデート（Update）
５，削除（Delete）
ポイント
TMySQL80Connection
TSQLQuery
TSQLTransaction
を、使用する。
以前の投稿を、参考にして、
procedure TForm1.Button1Click(Sender: TObject);
var
StrText:string;
begin
if not MySQL80Connection1.Connected then MySQL80Connection1.Open;
if MySQL80Connection1.Connected then begin
SQLQuery1.DataBase := MySQL80Connection1;
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;
MySQL80Connection1.Close();
//Button1.caption:='漢字を表示';
end;
procedure TForm1.Button2Click(Sender: TObject);
var
i:integer;
s:string;
begin
if not MySQL80Connection1.Connected then MySQL80Connection1.Open;
if MySQL80Connection1.Connected then begin
SQLQuery1.DataBase := MySQL80Connection1;
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;
MySQL80Connection1.Close;
end;
end;
procedure TForm1.Button3Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL80Connection1.Connected then MySQL80Connection1.Open;
if MySQL80Connection1.Connected then begin
SQLQuery1.DataBase := MySQL80Connection1;
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())';
MySQL80Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
Button2Click(Sender);
MySQL80Connection1.Close;
end;
procedure TForm1.Button4Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL80Connection1.Connected then MySQL80Connection1.Open;
if MySQL80Connection1.Connected then begin
SQLQuery1.DataBase := MySQL80Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:='update m_product set p_code="'+edit6.Text+'" where p_name = "'+edit5.Text+'"';
MySQL80Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
Button2Click(Sender);
MySQL80Connection1.Close;
end;
procedure TForm1.Button5Click(Sender: TObject);
var
i:integer;
s,sql:string;
begin
if not MySQL80Connection1.Connected then MySQL80Connection1.Open;
if MySQL80Connection1.Connected then begin
SQLQuery1.DataBase := MySQL80Connection1;
if not SQLTransaction1.Active then SQLTransaction1.StartTransaction;
sql:='delete from m_product where p_name = "'+edit7.Text+'"';
MySQL80Connection1.ExecuteDirect(sql);
SQLTransaction1.Commit;
end;
SQLQuery1.Close;
edit8.Text:=sql;
Button2Click(Sender);
MySQL80Connection1.Close;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
MySQL80Connection1:= TMySQL80Connection.Create(nil);
MySQL80Connection1.HostName := 'xxx.xxx.xxx.xxx';
MySQL80Connection1.CharSet:='utf8mb4';
MySQL80Connection1.UserName := 'xxxx';
MySQL80Connection1.Password := 'xxxx';
MySQL80Connection1.DatabaseName:='xxxx';
SQLQuery1:= TSQLQuery.Create(nil);
SQLTransaction1:= TSQLTransaction.Create(nil);
SQLTransaction1.SQLConnection:=MySQL80Connection1;
StringGrid1.RowCount:=1;
StringGrid1.ColCount:=3;
<pre><code> StringGrid1.Cells[1,0]:='商品名';
StringGrid1.Cells[2,0]:='商品コード';</code></pre>
button1.Caption:='search';
button2.Caption:='select';
button3.Caption:='insert';
button4.Caption:='update';
button5.Caption:='detale';
button6.Caption:='maketable';
end;
MySQL57Connection1
can not load default Mysql library(“libmysqlclient.so18” or “libmysqlclient.so”).check your installation
で、エラーになり
MySQL80Connection1
で、行ったら、上手く行った
サンプルのデータベースは
|
1 2 3 4 5 6 7 8 9 10 11 |
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 ; |
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
;
