I am experimenting with datasnap (never used it before) and have run into a strange issue. Maybe I am doing it wrong, I don't know. I have placed a Clientdataset3 on my form. Linked it to SQLConnection1 (on the same form) Which connects to datasnap server. I have also linked the Clientdataset3 to the datasetprovider (which allows command text) on the server side that Is linked to the table I want to insert into. However when I run:
procedure TForm3.AdvGlowButton1Click(Sender: TObject);
begin
clientdataset3.CommandText:='insert into "MY_TABLE" (twit) values (:A)';
clientdataset3.Params.ParamByName('A').AsString := cxmemo1.Lines.Text;
clientdataset3.Execute;
end;
I get "Remote error: no such table:insert"
What am I doing wrong ? Database is SQLite via DBX, using XE6.
If you are populating your CDS using a SELECT statement, you don't need to go through the exercise of sending a bespoke INSERT statement.
You should be able to just call
ClientDataSet3.Insert;
// populate fields here
ClientDataSet3.Post;
Followed by a call to ClientDataSet3.ApplyUpdates.
Similarly, you can do a DELETE just by calling ClientDataSet3.Delete.
The construction of necessary INSERT, DELETE and UPDATE statements is handled by the CDS's provider. However I'm not intending to suggest that you can't do an INSERT the way you're trying - it should work fine.
I can't tell what's going wrong with your INSERT from here, so instead, here's some code which works for me (including CREATE TABLE, etc statements) so you can maybe "spot the difference." I'm using XE6 on Win7 64-bit and v.3.8.5.0 of sqlite3.dll dated June 4, 2014.
The sample code provides 3 ways (all checked and working) of doing an insert, two using custom INSERT statements and the third using the default insert behaviour of a CDS, which can be invoked in code (CDS1.Insert) and by clicking the '+' button on the DBNavigator. The default CDS insert behaviour requires special handling: Although the ID column on the server is defined as Autoinc, getting the autoinc value while doing a CDS Insert is problematic, so the code uses the method described here:
http://edn.embarcadero.com/article/20847
of generating a temporary, negative, ID value which is replaced during the CDS ApplyUpdates process. Note from the DFM that the measure referred to in the Errata section of the link regarding the ProviderFlags in the SqlQuery ID field is necessary because the ID column value on the server is a 64-bit Integer.
type
TDataOperation = (doCreateTable, doDropTable, doInsert, doInsertUsingParams, doSelect);
TForm3 = class(TForm)
[...]
{ private declarations }
ID : Int64;
function NextID : Int64;
[...]
end;
implementation
{$R *.dfm}
const
scCreateTable = 'CREATE TABLE [MATable2] ([ID] INTEGER NOT NULL '
+ #13#10 + 'PRIMARY KEY AUTOINCREMENT, [AName] VARCHAR(20), '
+ #13#10 + ' [AMemo] MEMO)';
scDropTable =
'DROP TABLE [MATable2]';
scInsert1 =
'INSERT INTO [MATable2] (AName, AMemo) VALUES(''a'', ''A memo'')';
scSelect =
'SELECT * FROM [MATable2]';
scInsertUsingParams =
'INSERT INTO [MATable2] (AName, AMemo) VALUES(:AName, :AMemo)';
procedure TForm3.PerformTableOperation(Operation : TDataOperation);
var
Param : TParam;
begin
if {(Operation in [toCreate, toDrop]) and} CDS1.Active then
CDS1.Close;
case Operation of
doCreateTable : begin
CDS1.CommandText := scCreateTable;
CDS1.Execute;
PerformTableOperation(doSelect);
end;
doDropTable : begin
CDS1.CommandText := scDropTable;
CDS1.Execute;
end;
doSelect : begin
CDS1.CommandText := scSelect;
CDS1.Open;
end;
doInsert : begin
CDS1.CommandText := scInsert1;
CDS1.Execute;
PerformTableOperation(doSelect);
end;
doInsertUsingParams : begin
CDS1.CommandText := scInsertUsingParams;
// CDS1.FetchParams;
CDS1.Params.ParamByName('AName').AsString:= 'bcdef';
CDS1.Params.ParamByName('AMemo').AsString := 'memo b';
CDS1.Execute;
CDS1.Params.Clear;
PerformTableOperation(doSelect);
end;
end;
if CDS1.Active then // it won't be after a toDrop
CDS1.ApplyUpdates(-1);
end;
procedure TForm3.OpenConnection;
begin
SqlConnection1.Open;
end;
procedure TForm3.btnCreateClick(Sender: TObject);
begin
PerformTableOperation(doCreateTable);
end;
[etc ...]
procedure TForm3.btnReopenClick(Sender: TObject);
begin
CDS1.Close;
PerformTableOperation(doSelect);
end;
procedure TForm3.btnSelectClick(Sender: TObject);
begin
PerformTableOperation(doSelect);
end;
procedure TForm3.CDS1AfterDelete(DataSet: TDataSet);
begin
CDS1.ApplyUpdates(-1);
end;
procedure TForm3.CDS1AfterPost(DataSet: TDataSet);
begin
CDS1.ApplyUpdates(-1);
end;
procedure TForm3.CDS1NewRecord(DataSet: TDataSet);
begin
CDS1.FieldByName('ID').AsInteger := NextID;
end;
function TForm3.NextID: Int64;
begin
Dec(ID);
Result := ID;
end;
procedure TForm3.FormCreate(Sender: TObject);
begin
OpenConnection;
end;
end.
And here's a partial DFM to minimize the need for guessing how my DB components are set up.
object Form3: TForm3
[...]
object DBGrid1: TDBGrid
Left = 8
Top = 8
Width = 456
Height = 193
DataSource = DataSource1
TabOrder = 0
TitleFont.Charset = DEFAULT_CHARSET
TitleFont.Color = clWindowText
TitleFont.Height = -11
TitleFont.Name = 'Tahoma'
TitleFont.Style = []
Columns = <
item
Expanded = False
FieldName = 'ID'
Visible = True
end
item
Expanded = False
FieldName = 'AName'
Visible = True
end
item
Expanded = False
FieldName = 'AMemo'
Visible = True
end>
end
[...]
object DBNavigator1: TDBNavigator
Left = 16
Top = 216
Width = 240
Height = 25
DataSource = DataSource1
TabOrder = 6
end
object DBMemo1: TDBMemo
Left = 207
Top = 259
Width = 185
Height = 74
DataField = 'AMemo'
DataSource = DataSource1
TabOrder = 7
end
object DBEdit1: TDBEdit
Left = 24
Top = 264
Width = 121
Height = 21
DataField = 'AName'
DataSource = DataSource1
TabOrder = 8
end
object SQLConnection1: TSQLConnection
ConnectionName = 'SQLITECONNECTION'
DriverName = 'Sqlite'
LoginPrompt = False
Params.Strings = (
'DriverName=Sqlite'
'Database=D:\delphi\xe6\sqlite\matestdb.sqlite')
Connected = True
Left = 40
Top = 16
end
object SQLQuery1: TSQLQuery
MaxBlobSize = 1
Params = <>
SQL.Strings = (
'select * from [matable2]')
SQLConnection = SQLConnection1
Left = 128
Top = 16
object SQLQuery1ID: TLargeintField
FieldName = 'ID'
ProviderFlags = [pfInWhere, pfInKey]
end
object SQLQuery1AName: TWideStringField
FieldName = 'AName'
end
object SQLQuery1AMemo: TWideMemoField
FieldName = 'AMemo'
BlobType = ftWideMemo
Size = 1
end
end
object DataSetProvider1: TDataSetProvider
DataSet = SQLQuery1
Options = [poAllowCommandText, poUseQuoteChar]
UpdateMode = upWhereKeyOnly
Left = 216
Top = 16
end
object CDS1: TClientDataSet
Aggregates = <>
CommandText = 'select * from MATable2'
Params = <>
ProviderName = 'DataSetProvider1'
BeforeInsert = CDS1BeforeInsert
AfterInsert = CDS1AfterInsert
BeforePost = CDS1BeforePost
AfterPost = CDS1AfterPost
AfterDelete = CDS1AfterDelete
OnNewRecord = CDS1NewRecord
AfterApplyUpdates = CDS1AfterApplyUpdates
Left = 288
Top = 16
object CDS1ID: TLargeintField
FieldName = 'ID'
end
object CDS1AName: TWideStringField
FieldName = 'AName'
end
object CDS1AMemo: TWideMemoField
FieldName = 'AMemo'
BlobType = ftWideMemo
Size = 1
end
end
object DataSource1: TDataSource
DataSet = CDS1
Left = 344
Top = 16
end
end