mORMot Open Source

{**
  Set the Wildcards character for WildcardsArray variable.
  Overrride this method if the wildcards character is different in other database
}
procedure TZAbstractDatabaseMetadata.FillWildcards;
begin
  SetLength(WildcardsArray,2);
  WildcardsArray[0]:='_';  //<---- seems to be a trublemaker, no idea how to test it with our tests. See http://zeoslib.sourceforge.net/viewtopic.php?f=40&t=13184
  WildcardsArray[1]:='%';
end;

  procedure TMyRestServer.aet_insert(Ctxt: TSQLRestServerURIContext);
  var
    tmp_query: ISQLDBStatement;
    tmp_record: TSQLUserDefs;
    tmp_fields_JSON: RAWUTF8;
    tmp_fields_SQL: RawUTF8;
    tmp_SQL: RawUTF8;
  begin
    tmp_record := TSQLUserDefs.Create;
    tmp_record.USERNAME := 'test_user';
    tmp_record.USERPASS := 'test_pass';
    tmp_record.REAL_NAME := 'test_real_name';
    tmp_record.REAL_SURNAME := 'test_surname';
    tmp_record.REAL_EMAIL := 'test_email';
    tmp_record.USER_COMPANY := 'test_company';
    tmp_record.USER_DISCIPLINE := 'test_discipline';
    tmp_record.EMAIL_RECIPIENT := 1;

    tmp_fields_JSON := tmp_record.GetJSONValues(true,false,soInsert); //{"USERNAME":"test_user","USERPASS":"test_pass",..}
    tmp_fields_SQL := GetJSONObjectAsSQL(tmp_fields_JSON,False,False); //"(USERNAME,USERPASS,..) VALUES ('test_user','test_pass',..)"
    FormatUTF8('INSERT INTO %%;',['user_defs', tmp_fields_SQL], tmp_SQL);
    //Ctxt.Returns('["' + tmp_SQL + '"]'); //
    try
       tmp_query := fConnection.NewThreadSafeStatementPrepared(tmp_SQL, {ExpectResults=}false, {RaiseExceptionOnError=}true);
       tmp_query.ExecutePrepared;
       tmp_query.ReleaseRows;
       Ctxt.Returns('["DONE"]');
    except
       tmp_query := nil;
    end;
  end; 

aetDBSchema := TSQLModel.Create([TSQLUserDefs], 'root');
VirtualTableExternalMap(aetDBSchema,TSQLUserDefs,fConnection,'mysqltest.user_defs');
aetRestServerDB := TSQLRestServerDB.Create(aetDBSchema, ':memory:', false);
aetRestServerDB.CreateMissingTables();         

1 - TSQLRestServerDB.CreateMissingTables(user_version: cardinal=0; Options: TSQLInitializeTableOptions=[]);
2 - TSQLDataBase.Execute(const aSQL: RawUTF8);
3 - TSQLRequest.Execute(aDB: TSQLite3DB; const aSQL: RawUTF8);
4 - TSQLRequest.Prepare(DB: TSQLite3DB; const SQL: RawUTF8; NoExcept: boolean): integer;
5 - TSQLRequest.Execute;
6 - TSQLRequest.Step: integer;
7 - sqlite3_check(DB: TSQLite3DB; aResult: integer; const SQL: RawUTF8): integer; ----> Creates Exception

SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%';
Step
BEGIN TRANSACTION;
Step
CREATE VIRTUAL TABLE UserDefs USING External(USERNAME TEXT COLLATE SYSTEMNOCASE, USERPASS TEXT COLLATE SYSTEMNOCASE, REAL_NAME TEXT COLLATE SYSTEMNOCASE, REAL_SURNAME TEXT COLLATE SYSTEMNOCASE, REAL_EMAIL TEXT COLLATE SYSTEMNOCASE, USER_COMPANY TEXT COLLATE SYSTEMNOCASE, USER_DISCIPLINE TEXT COLLATE SYSTEMNOCASE, EMAIL_RECIPIENT INTEGER);
Step
Error SQLITE_ERROR (1) [Step] using 3.35.4 - SQL Error: Table 'mysqltest.user\_defs' doesn't exist
Code: 1146 SQL: SHOW INDEX FROM mysqltest.`user\_defs`, extended_errcode=1
ROLLBACK TRANSACTION;
Step
An unhandled exception occurred at $0061B879:
ESQLite3Exception:
  $0061B879
  $0061B2F8
  $0061A6A0
  $0061A70B
  $0061839B
  $004CB33E
  $0040208E

var
   tmp_record: TSQLDriveRecord;
   tmpBatchRest: TSQLRestBatch;

begin
     tmp_record := TSQLDriveRecord.Create;
     tmp_record.Drive := 'C';
     tmp_record.Active := 1;
     tmp_record.Checked := 1;

     tmpBatchRest := TSQLRestBatch.Create(GlobalRestServerDB, TSQLDriveRecord);
     tmpBatchRest.Add(tmp_record, true);
     Ctxt.Returns('["' + IntToStr(GlobalRestServerDB.BatchSend(tmpBatchRest)) + '"]');

     tmpBatchRest.Free;
     tmp_record.Free;
end;

20210330 10412002  +    project1.TMyRestServer(ffffa63271b0).URI GET root/aet_test_mysql_batch in=0 B
20210330 10412002 trace         mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0) BatchSend {"TSQLRestBatch(ffffa448b040)":{"Count":1,"SizeBytes":35}}
20210330 10412002  +            mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0).EngineBatchSend TSQLDriveRecord inlen=36
20210330 10412002 EXC                   EZSQLException ("SQL Error: Duplicate entry 'C' for key 'Drive' \nCode: 1062 SQL: insert into DriveRecord (ID,Drive,Active,Checked) values (?,?,?,?)") [HttpSvr 8888/root THttpSvrResp] at 676fb8 758b74 60d19c 51c540 5189dc 4e0c44 4e2958 4c6aac 4c6bbc 4020ac 4d46c8 4d4118 4da41c 51ed78 655dcc 656e7c 65841c
20210330 10412002 trace                 mORMotSQLite3.TSQLRestServerDB(ffffa630f2f0) EngineBatchSend json=36 B add=1 update=0 delete=0 TSQLDriveRecord