Why can my TQuery perform read operations but cannot successfully
execute insert
lazarus / 16-Databases

----------------------------------------------------------------------
akjarjash · 2023-11-21 05:06

project1.lpr
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1; {$mode objfpc}{$H+} uses      {$IFDEF UNIX}      cthreads, 
   {$ENDIF}      {$IFDEF HASAMIGA}      athreads,      {$ENDIF}     
Interfaces, // this includes the LCL widgetset      Forms, unit1     
{ you can add units after this }; {$R *.res} begin     
RequireDerivedFormResource:=True;      Application.Scaled:=True;     
Application.Initialize;                       
Application.CreateForm(TForm1, Form1);      Application.Run;end. 

Unit1.lfm
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
Form1: TForm1  Left = 452  Height = 227  Top = 151  Width = 608 
Caption = 'Form1'  ClientHeight = 227  ClientWidth = 608  OnCreate =
FormCreate  object DBGrid1: TDBGrid    Left = 16    Height = 208   
Top = 8    Width = 400    Color = clWindow    Columns = <         
item        Title.Caption = '编号'        FieldName = 'id'      end   
    item        Title.Caption = '姓名'        FieldName = 'name'     
end          item        Title.Caption = '年龄'        FieldName =
'age'      end>    DataSource = DataSource1    Options = [dgEditing,
dgTitles, dgIndicator, dgColumnResize, dgColumnMove, dgColLines,
dgRowLines, dgTabs, dgAlwaysShowSelection, dgConfirmDelete,
dgCancelOnExit, dgDisplayMemoText]    TabOrder = 0  end  object
Label1: TLabel    Left = 432    Height = 17    Top = 15    Width = 36 
  Caption = '姓名：'  end  object Label2: TLabel    Left = 432    Height
= 17    Top = 50    Width = 36    Caption = '年龄：'  end  object
EditUserName: TEdit    Left = 486    Height = 25    Top = 11    Width
= 98    TabOrder = 1  end  object EditUserAge: TEdit    Left = 486   
Height = 25    Top = 46    Width = 96    TabOrder = 2  end  object
Button1: TButton    Left = 432    Height = 25    Top = 88    Width =
75    Caption = '新增'    TabOrder = 3    OnClick = Button1Click  end 
object Button2: TButton    Left = 512    Height = 25    Top = 88   
Width = 75    Caption = '更新'    TabOrder = 4  end  object Button3:
TButton    Left = 432    Height = 25    Top = 128    Width = 75   
Caption = '查询'    TabOrder = 5  end  object Button4: TButton    Left =
512    Height = 25    Top = 128    Width = 75    Caption = '编辑'   
TabOrder = 6  end  object SQLite3Connection1: TSQLite3Connection   
Connected = True    LoginPrompt = False    DatabaseName =
'C:\Users\akjarjash\Desktop\student.db'    KeepConnection = True   
Transaction = SQLTransaction1    AlwaysUseBigint = False    Left =
424    Top = 168  end  object SQLQuery1: TSQLQuery    IndexName =
'DEFAULT_ORDER'    MaxIndexesCount = 4    FieldDefs = <          item 
      Name = 'id'        DataType = ftAutoInc        Precision = -1  
  end          item        Name = 'name'        DataType = ftMemo    
  Precision = -1      end          item        Name = 'age'       
DataType = ftInteger        Precision = -1      end>    Active = True 
  Database = SQLite3Connection1    Transaction = SQLTransaction1   
SQL.Strings = (      'select'      '  *'      'from t_students'    ) 
 InsertSQL.Strings = (      'insert into t_students'      '  (name,
age'      '  )'      'values'      '  (:name, :age'      '  )'    )  
UpdateSQL.Strings = (      'update t_students'      'set'      '  name
= :name, age = :age'      'where'      '   (id = :OLD_id)'      '  ' 
 )    DeleteSQL.Strings = (      'delete from t_students'     
'where'      '   (id = :OLD_id)'      '  '    )    RefreshSQL.Strings
= (      'select'      '  *'      'from t_students'      'where'     
'   (id = :id)'      '  '    )    Params = <>    Macros = <>    Left =
536    Top = 168  end  object SQLTransaction1: TSQLTransaction   
Active = True    Database = SQLite3Connection1    Left = 480    Top =
168  end  object DataSource1: TDataSource    DataSet = SQLQuery1   
Left = 592    Top = 168  endend 

Unit1.pas
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses      Classes, SysUtils,
SQLite3Conn, SQLDB, DB, Forms, Controls, Graphics,                   
   Dialogs, DBGrids, StdCtrls; type                         { TForm1
}       TForm1 = class(TForm)                                        
      Button1: TButton;                                              
Button2: TButton;                                               
Button3: TButton;                                               
Button4: TButton;                                               
DataSource1: TDataSource;                                            
  DBGrid1: TDBGrid;                                               
EditUserName: TEdit;                                               
EditUserAge: TEdit;                                               
Label1: TLabel;                                                Label2:
TLabel;                                               
SQLite3Connection1: TSQLite3Connection;                              
                SQLQuery1: TSQLQuery;                                
              SQLTransaction1: TSQLTransaction;                      
                        procedure Button1Click(Sender: TObject);     
procedure FormCreate(Sender: TObject);      private       public     
end; var      Form1: TForm1; implementation {$R *.lfm} { TForm1
} procedure TForm1.FormCreate(Sender: TObject);begin  //
执行初始化设定工作end; procedure TForm1.Button1Click(Sender: TObject);begin  //
检查用户是否输入相关数据  if Trim(self.EditUserName.Text) = '' then  begin   
 ShowMessage('学生姓名数据为必填数据!');     self.EditUserName.Text:='';   
 self.EditUserName.SetFocus;     Exit;        end;  if
Trim(self.EditUserAge.Text) = '' then  begin   
 ShowMessage('学生姓名数据为必填数据!');     self.EditUserAge.Text:='';   
 self.EditUserAge.SetFocus;     Exit;        end;  // 检查数据库连接对象  if
not self.SQLite3Connection1.Connected then  begin   
self.SQLite3Connection1.Open;        end;        // 检查数据库事务  if not
self.SQLTransaction1.Active then  begin   
 self.SQLTransaction1.StartTransaction;        end;  // 进行新增操作前异常处理 
try        // 检查查询对象的激活状态    if not self.SQLQuery1.Active then   
begin       self.SQLQuery1.Active:=True;        end;    // 设置数据库新增标记 
 self.SQLQuery1.Append;    // 设置插入语句参数    self.SQLQuery1.FieldByName('
name').AsString:=Trim(self.EditUserName.Text);    self.SQLQuery1.Field
ByName('age').AsInteger:=StrToInt(self.EditUserAge.Text);    //
执行新增操作    self.SQLQuery1.Post;    // 进行事务提交   
self.SQLTransaction1.Commit;    // 提示用户信息    ShowMessage('恭喜，新增成功!'); 
  // 处理编辑框文本    self.EditUserName.Text:='';   
self.EditUserAge.Text:='';    // 刷新数据源参数    self.SQLQuery1.Close;   
self.SQLQuery1.Active:=True;    self.SQLQuery1.Open;   
self.DataSource1.DataSet.Refresh;        except    on E:Exception do 
 begin      self.SQLTransaction1.Rollback;     
ShowMessage('抱歉，系统异常('+E.Message+')，新增失败!');                end;     
 end;    end;end.

----------------------------------------------------------------------
Zvoni · 2023-11-21 07:39

"ApplyUpdates" missing? resp. sqoAutoApplyUpdates not set in
TSQLQuery.Options?

----------------------------------------------------------------------
akjarjash · 2023-11-21 07:57

I tested it again, and it still doesn't work. Could you provide me
with a complete example, similar to the one in the attachment
interface, for performing CRUD operations on an SQLite3 database table
using buttons?
To facilitate the work, you can use the following prompts through
ChatGPT (PS: Although the code generated by ChatGPT in my tests
achieves the functionality, I would like to have some template code to
speed up the process):


Please provide a complete Lazarus code example that meets the
following requirements:
01) Please complete a code example that performs CRUD operations on
TDBGrid (Student Basic Information Management System). You can add any
controls as needed, and the database should use sqlite3. The database
file path is: /Users/akjarjash/Downloads/student.db. The database has
a table called t_students with fields: id (auto-increment), name
(text), age (integer).
02) The code example files you should output include: `project1.lpr`,
`Unit1.lfm`, `Unit1.pas`.
03) Do not omit any file or any content of any file.
04) Note: You need to provide the actual content of the Unit1.lfm
file.
05) Note: Avoid referencing the Windows unit in the 'uses' clause of
the instance (this is important).
06) Note: In the given Unit1.lfm file, for the Form window, you only
need to set the following properties and the events you need:
``` 
Left = 249
Height = 240
Top = 147
Width = 320
Caption = 'Form1'
ClientHeight = 240
ClientWidth = 320
LCLVersion = '3.99.0.0'
```

----------------------------------------------------------------------
Handoko · 2023-11-21 08:45

For adding new record you do not have you use TQuery, I usually use
ExecuteDirect. You can see my demo:
https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.h
tml#msg496461

----------------------------------------------------------------------
Zvoni · 2023-11-21 08:46

Quote from: akjarjash on November 21, 2023, 08:57:33 amI tested it
again, and it still doesn't work. Could you provide me with a complete
example, similar to the one in the attachment interface, for
performing CRUD operations on an SQLite3 database table using buttons?

I'm the wrong person to ask that.
I don't use "Append", "Post" and whatever else.
I use SQL-Statements. Period

----------------------------------------------------------------------
akjarjash · 2023-11-22 08:32

Haha, thank you for your response. With the help of ChatGPT-4 and your
advice on "ApplyUpdates," I have resolved the issue and completed a
small example. :-)

- SQL
   database：SQLite3  student.db
   Code: SQL  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i    CREATE
TABLE student_info (       id INTEGER PRIMARY KEY AUTOINCREMENT,     
 name TEXT,       age INTEGER   );  
- project1.lpr
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i program
project1; {$mode objfpc}{$H+} uses      {$IFDEF UNIX}      cthreads, 
   {$ENDIF}      {$IFDEF HASAMIGA}      athreads,      {$ENDIF}     
Interfaces, // this includes the LCL widgetset      Forms, unit1     
{ you can add units after this }; {$R *.res} begin     
RequireDerivedFormResource:=True;      Application.Scaled:=True;     
Application.Initialize;                       
Application.CreateForm(TForm1, Form1);      Application.Run;end. 

- Unit1.lfm
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i object
Form1: TForm1  Left = 249  Height = 297  Top = 149  Width = 352 
Caption = '学生基本信息管理系统'  ClientHeight = 297  ClientWidth = 352 
LCLVersion = '3.99.0.0'  OnCreate = FormCreate  object DBGrid1:
TDBGrid    Left = 8    Height = 184    Top = 8    Width = 331    Color
= clWindow    Columns = <          item        Title.Caption = '编号'  
    FieldName = 'id'      end          item        Title.Caption =
'姓名'        FieldName = 'name'      end          item       
Title.Caption = '年龄'        FieldName = 'age'      end>    DataSource
= DataSource1    Options = [dgEditing, dgTitles, dgIndicator,
dgColumnResize, dgColumnMove, dgColLines, dgRowLines, dgTabs,
dgAlwaysShowSelection, dgConfirmDelete, dgCancelOnExit,
dgDisplayMemoText]    TabOrder = 0    OnCellClick = DBGrid1CellClick 
end  object Label1: TLabel    Left = 10    Height = 16    Top = 208  
Width = 33    Caption = '姓名：'  end  object EditUserName: TEdit    Left
= 64    Height = 22    Top = 205    Width = 80    TabOrder = 1  end 
object Label2: TLabel    Left = 10    Height = 16    Top = 236   
Width = 33    Caption = '年龄：'  end  object EditUserAge: TEdit    Left
= 64    Height = 22    Top = 233    Width = 80    TabOrder = 2  end 
object Button1: TButton    Left = 170    Height = 25    Top = 204   
Width = 75    Caption = '新增'    TabOrder = 3    OnClick =
Button1Click  end  object Button2: TButton    Left = 264    Height =
25    Top = 204    Width = 75    Caption = '更新'    TabOrder = 4   
OnClick = Button2Click  end  object Button3: TButton    Left = 170   
Height = 25    Top = 232    Width = 75    Caption = '查询'    TabOrder =
5    OnClick = Button3Click  end  object Button4: TButton    Left =
264    Height = 25    Top = 232    Width = 75    Caption = '删除'   
TabOrder = 6    OnClick = Button4Click  end  object Button5: TButton 
 Left = 10    Height = 25    Top = 264    Width = 326    Caption =
'刷新数据'    TabOrder = 7    OnClick = Button5Click  end  object
SQLite3Connection1: TSQLite3Connection    Connected = False   
LoginPrompt = False    DatabaseName =
'/Users/akjarjash/Downloads/student.db'    KeepConnection = True   
Transaction = SQLTransaction1    AlwaysUseBigint = False    Left = 32 
  Top = 64  end  object SQLTransaction1: TSQLTransaction    Active =
False    Database = SQLite3Connection1    Left = 112    Top = 64  end 
object SQLQuery1: TSQLQuery    IndexName = 'DEFAULT_ORDER'   
MaxIndexesCount = 4    FieldDefs = <          item        Name = 'id' 
      DataType = ftAutoInc        Precision = -1      end         
item        Name = 'name'        DataType = ftMemo        Precision =
-1      end          item        Name = 'age'        DataType =
ftInteger        Precision = -1      end>    Database =
SQLite3Connection1    Transaction = SQLTransaction1    SQL.Strings =
(      'SELECT * FROM t_students'    )    Params = <>    Macros = <> 
 Left = 32    Top = 136  end  object DataSource1: TDataSource   
DataSet = SQLQuery1    Left = 112    Top = 136  endend 

- Unit1.pas
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i unit
Unit1; {$mode objfpc}{$H+} interface uses  Classes, SysUtils, Forms,
Controls, Graphics, Dialogs, DBGrids, StdCtrls,  SQLite3Conn, SQLDB,
DB; type   { TForm1 }   TForm1 = class(TForm)    Button1: TButton;   
Button2: TButton;    Button3: TButton;    Button4: TButton;          
    Button5: TButton;    DataSource1: TDataSource;    DBGrid1:
TDBGrid;    EditUserName: TEdit;    EditUserAge: TEdit;    Label1:
TLabel;    Label2: TLabel;    SQLite3Connection1: TSQLite3Connection; 
  SQLQuery1: TSQLQuery;    SQLTransaction1: TSQLTransaction;   
procedure Button1Click(Sender: TObject);                procedure
Button2Click(Sender: TObject);                procedure
Button3Click(Sender: TObject);                procedure
Button4Click(Sender: TObject);                procedure
Button5Click(Sender: TObject);                procedure
DBGrid1CellClick(Column: TColumn);    procedure FormCreate(Sender:
TObject);  private   public   end; var  Form1:
TForm1; implementation {$R *.lfm} { TForm1 } procedure
TForm1.FormCreate(Sender: TObject);begin  // 进行查询数据的事务处理  try    //
进行相关连接操作配置        self.SQLite3Connection1.Connected:=True;       
self.SQLTransaction1.Active:=True;       
self.SQLQuery1.Active:=True;    // 进行数据查询操作         
self.SQLQuery1.Open;  except    on E:Exception do    begin     
ShowMessage('抱歉，系统异常('+E.Message+')，查询失败!');    end;       
end;end; procedure TForm1.Button1Click(Sender: TObject);begin  //
进行新增前的数据检测工作  if Trim(self.EditUserName.Text) = '' then  begin   
self.EditUserName.Text:='';    ShowMessage('用户姓名数据为必填数据!');   
self.EditUserName.SetFocus;    Exit;      end;  if
Trim(self.EditUserAge.Text) = '' then  begin   
self.EditUserAge.Text:='';    ShowMessage('用户姓名数据为必填数据!');   
self.EditUserAge.SetFocus;    Exit;  end;  // 进行新增操作的事务处理  try    //
进行新增操作前的相关配置信息    self.SQLite3Connection1.Connected := True;   
self.SQLTransaction1.Active := True;    self.SQLQuery1.Active :=
True;    // 设置新增操作标记    self.SQLQuery1.Append;    // 设置相关新增字段   
self.SQLQuery1.FieldByName('name').AsString :=
Trim(self.EditUserName.Text);   
self.SQLQuery1.FieldByName('age').AsInteger :=
StrToInt(Trim(self.EditUserAge.Text));    // 将新增的字段缓存起来   
self.SQLQuery1.Post;    // 真实应用新增操作    self.SQLQuery1.ApplyUpdates;  
// 提交缓存的新增记录到数据库中并保持事务继续打开    self.SQLTransaction1.CommitRetaining;  
// 提示用户新增结果    ShowMessage('恭喜，新增成功!');  except    on E:Exception do 
 begin      // 执行数据库回滚操作      self.SQLTransaction1.Rollback;      //
提示用户新增结果      ShowMessage('抱歉，新增失败!');    end;  end; end; procedure
TForm1.Button2Click(Sender: TObject);begin        // 进行更新操作前的异常处理操作 
try                // 进行新增操作前的相关设置操作   
self.SQLite3Connection1.Connected:=True;   
self.SQLTransaction1.Active:=True;    self.SQLQuery1.Active:=True;   
// 设置当前的模式为编辑状态    self.SQLQuery1.Edit;    // 设置要编辑的字段的新的值    self.SQL
Query1.FieldByName('name').AsString:=Trim(self.EditUserName.Text);   
self.SQLQuery1.FieldByName('age').AsInteger:=StrToInt(Trim(self.EditUs
erAge.Text));    // 将欲进行更新的数据缓存起来    self.SQLQuery1.Post;    //
将缓存的数据应用到数据库中    self.SQLQuery1.ApplyUpdates;    // 进行事物提交操作   
self.SQLTransaction1.CommitRetaining;    // 提示用户信息   
ShowMessage('恭喜，更新成功!');    // 清空编辑框数据    self.EditUserName.Text:=''; 
  self.EditUserAge.Text:='';        except    on E:Exception do   
begin      ShowMessage('抱歉，系统异常('+E.Message+')，更新失败!');    end;      
end;end; procedure TForm1.Button3Click(Sender: TObject);var 
SQLQueryStr : String;begin        // 检测编辑框中是否存在查询数据  if
(Trim(self.EditUserName.Text) = '') and
(Trim(self.EditUserAge.Text)='') then  begin   
ShowMessage('名称和姓名编辑框必须至少填写一项数据才能进行查询操作!');    Exit;        end;  //
设置原始SQL查询语句  SQLQueryStr := 'SELECT * FROM t_students WHERE 1=1 '; 
self.SQLQuery1.SQL.Text:=SQLQueryStr;  // 判断是否需要附加名称字段  if
Trim(self.EditUserName.Text) <> '' then  begin        SQLQueryStr :=
SQLQueryStr + 'AND name LIKE :name ';   
self.SQLQuery1.SQL.Text:=SQLQueryStr;   
self.SQLQuery1.ParamByName('name').AsString:='%' +
Trim(self.EditUserName.Text) + '%';        end;  // 判断是否需要附加年龄字段  if
Trim(self.EditUserAge.Text) <> '' then  begin      SQLQueryStr :=
SQLQueryStr + ' AND age = :age ';     
self.SQLQuery1.SQL.Text:=SQLQueryStr;      self.SQLQuery1.ParamByName(
'age').AsInteger:=StrToInt(self.EditUserAge.Text);        end;  //
进行查询操作前的异常处理  try    // 进行查询前的设置操作   
self.SQLite3Connection1.Connected:=True;   
self.SQLTransaction1.Active:=True;    self.SQLQuery1.Active:=True;   
// 设置查询的SQL语句操作    self.SQLQuery1.SQL.Text:=SQLQueryStr;    // 进行查询操作 
  self.SQLQuery1.Close;    self.SQLQuery1.Open;    // 提示用户   
ShowMessage('恭喜，查询成功!');        except    on E:Exception do    begin 
   ShowMessage('抱歉，系统异常('+E.Message+')，查询失败!');    end;       
end;end; procedure TForm1.Button4Click(Sender: TObject);begin       
// 让用户确认是否进行删除操作  if MessageDlg('确认删除', '是否确定要执行删除操作？',
mtConfirmation, [mbYes, mbNo], 0) = mrNo then  begin   
 ShowMessage('您取消了删除操作!');     Exit;        end;  // 进行删除异常处理操作  try 
 // 进行删除前的配置操作    self.SQLite3Connection1.Connected:=True;   
self.SQLTransaction1.Active:=True;    self.SQLQuery1.Active:=True;   
// 设置删除状态标记    self.SQLQuery1.Delete;    // 提交删除操作的事务   
self.SQLTransaction1.CommitRetaining;    // 提示用户删除操作   
ShowMessage('恭喜，删除成功!');        except    on E:Exception do    begin 
   ShowMessage('抱歉，系统异常('+E.Message+')，删除失败!');    end;       
end;end; procedure TForm1.Button5Click(Sender: TObject);begin  //
进行查询数据的事务处理  try    // 进行相关连接操作配置       
self.SQLite3Connection1.Connected:=True;       
self.SQLTransaction1.Active:=True;       
self.SQLQuery1.Active:=True;    // 设置查询语句   
self.SQLQuery1.SQL.Text:='SELECT * FROM t_students';    // 进行数据查询操作  
self.SQLQuery1.Close;          self.SQLQuery1.Open;  except    on
E:Exception do    begin     
ShowMessage('抱歉，系统异常('+E.Message+')，查询失败!');    end;       
end;end;  procedure TForm1.DBGrid1CellClick(Column: TColumn);begin   
  
self.EditUserName.Text:=self.SQLQuery1.FieldByName('name').AsString; 
self.EditUserAge.Text:=self.SQLQuery1.FieldByName('age').AsString;end;
 end.

----------------------------------------------------------------------
akjarjash · 2023-11-22 08:36

Quote from: Handoko on November 21, 2023, 09:45:20 amFor adding new
record you do not have you use TQuery, I usually use ExecuteDirect.
You can see my demo:
https://forum.lazarus.freepascal.org/index.php/topic,65185.msg496461.h
tml#msg496461

Thank you for your response and efforts. After several attempts, I
have successfully created an example using native Lazarus for SQLite3
CRUD operations. The native approach was indeed a bit cumbersome, but
I managed to discover some tricks, and it turned out well. I will test
the example you recommended when I have the time.