#1 Re: mORMot 1 » Error 16 - Execute SQL via services Fied Default » 2014-03-25 13:22:43

Sample database "NorthWind.db" is a SQLite database, how to open the "MS SQL" Engine?. With the "S qlite 3" Engine all table data can be displayed unless the "Order Details", because there is a default Field.
Database accessed by "Sqlite Manager" Add-ons Mozilla Firefox there is no problem.

#2 Re: mORMot 1 » Error 16 - Execute SQL via services Fied Default » 2014-03-25 12:41:59

I run the example "16 - Execute SQL via services".
I copy the sample database "NorthWind.db" and the program "Server" then "Client" is executed.
When the "Client" runs, the form filled in as follows:
Egine: Sqlite 3                  Server    :  NorthWind.db:              User      :                 Open
                                     Database : NorthWind.db              Password :

Select * from Order Details

                                           Select * from: Order Details                                    Execute

After Execute error occurs because there is a default, to another Table no problem as to: Orders, Customers, Employees.

#3 mORMot 1 » Error 16 - Execute SQL via services Fied Default » 2014-03-25 08:32:09

Tohang
Replies: 5

CREATE TABLE [Order Details] ([OrderID] INTEGER, [ProductID] INTEGER NOT NULL, [UnitPrice] REAL NOT NULL Default 0, [Quantity] INTEGER NOT NULL Default 1, [Discount] REAL NOT NULL Default 0, PRIMARY KEY ( [OrderID], [ProductID]))

20140325 14031617 srvr      POST root/RemoteSQL.Execute/8 ERROR=500 (Exception ESQLite3Exception: near "Order": syntax error)

Why erorr when Field added "Default 1"

#4 Re: mORMot 1 » SynSQLite3 and Delphi 5 compilation » 2012-09-07 07:54:51

His writing Geogle Translare translation, sorry my english bases can not.

a.) function FormatUTF8(Format: RawUTF8; const Args: array of const): RawUTF8; overload;
     begin
      Result:=FormatUTF8(PUTF8Char(Format),Args);
    end;
   
b.) function FormatUTF8(Format: PUTF8Char; const Args: array of const): RawUTF8;
    ....

procedure TSQLDataBase.GetFieldNames(var Names: TRawUTF8DynArray; const TableName: RawUTF8);
....
      R.Prepare(fDB,FormatUTF8('PRAGMA table_info(%);',[TableName]));
....
end;

Dephi 7 does not need the function of a.)

Dephi 5 requires the function of a.), String constants are considered RawUTF8, AnsiString. If the function b.) Used parameters must PUTF8Char.
  eg ss :: PUTF8Char
   ss: = 'PRAGMA table_info (%)';
   R.Prepare (FDB, FormatUTF8 (ss, [TableName]));
   
I have a way to overcome the disadvantages of Delphi 5 problem parameters "array of const". If this trick can be easy to be added.

SynCommonsd.pas
TTextWriter = class
......   
    procedure Add(Format: PWinAnsiChar; const Values: array of const;Escape: TTextWriterKind=twNone); overload;
    .....
    property Stream: TStream read fStream write fStream;
end;
{$ifdef DELPHI5OROLDER} { array of const is buggy in Delphi 5 sad }
procedure TextWriterAdd(oSelf:TTextWriter;Format: PWinAnsiChar; const Values: array of const;Escape: TTextWriterKind=twNone);//TTextWriter.Add
type
{$endif DELPHI5OROLDER}

TSynLog = class(TObject, ISynLog)
.....
{$ifndef DELPHI5OROLDER}
....
    procedure Log(Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArgs: array of const;
      aInstance: TObject=nil); overload;
{$endif}
    /// same as Log(Level,TextFmt,[]) but with one RawUTF8 parameter
    procedure Log(Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArg: RawUTF8;
      aInstance: TObject=nil); overload;
    /// same as Log(Level,TextFmt,[]) but with one Int64 parameter
    procedure Log(Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArg: Int64; 
      aInstance: TObject=nil); overload;
....
    property FileName: TFileName read fFileName;
  end;

TSynLogFile = class(TMemoryMapText)
....
   property LogProcCount: integer read fLogProcCurrentCount;
end;
{$ifdef DELPHI5OROLDER} { array of const is buggy in Delphi 5 sad } //thg
procedure SynLogLogInternal(oSelf:TSynLog;Level: TSynLogInfo; TextFmt: PWinAnsiChar;const TextArgs: array of const; Instance: TObject);//TSynLog.LogInternal
procedure SynLogLog(oSelf:TSynLog;Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArgs: array of const;aInstance: TObject=nil);//TSynLog.Log
{$endif DELPHI5OROLDER}

{$ifndef DELPHI5OROLDER}
procedure TTextWriter.Add(Format: PWinAnsiChar; const Values: array of const;
  Escape: TTextWriterKind=twNone);
  ....
end;
{$endif}
 
{$ifdef DELPHI5OROLDER} { array of const is buggy in Delphi 5 sad }  //thg
procedure TextWriterAdd(oSelf:TTextWriter;Format: PWinAnsiChar; const Values: array of const;Escape: TTextWriterKind=twNone);
var ValuesIndex: integer;
begin // we put const char > #127 as #??? -> asiatic MBCS codepage OK
  if not Assigned(oSelf) then Exit;if Format=nil then exit;ValuesIndex := 0;
  repeat
    repeat
      case ord(Format^) of
      0: exit;
      13, 164: oSelf.AddCR; // CR,¤ -> add CR,LF
      167: if oSelf.B^=',' then dec(oSelf.B); // §
      ord('$'),ord('%'),163,181: break; // $,%,£,µ
      else
        if oSelf.B>=oSelf.BEnd then
          oSelf.FlushInc^ := Format^ else begin
          oSelf.B[1] := Format^;
          inc(oSelf.B);
        end;
      end;
      inc(Format);
    until false;
    // add next value as text
    if ValuesIndex<=high(Values) then // missing value will display nothing
    case ord(Format^) of
    ord('%'): with Values[ValuesIndex] do
       case Vtype of
         vtInteger:      oSelf.Add(VInteger);
         vtBoolean:      oSelf.AddU(byte(VBoolean));
         vtChar:         oSelf.Add(@VChar,1,Escape);
         vtExtended:     oSelf.Add(VExtended^);
         vtString:       oSelf.Add(@VString^[1],ord(VString^[0]),Escape);
         vtPointer:      oSelf.AddPointer(PtrUInt(VPointer));
         vtPChar:        oSelf.Add(PUTF8Char(VPChar),Escape);
         vtObject:
           if VObject<>nil then begin
             oSelf.AddShort(PShortString(PPointer(PPtrInt(VObject)^+vmtClassName)^)^);
             oSelf.Add('(');
             if VObject.InheritsFrom(Exception) then
               with Exception(VObject) do
               {$ifdef UNICODE}AddW{$else} { no longer D5} oSelf.Add{$endif}(
                 pointer(Message),length(Message),Escape) else
               oSelf.AddPointer(PtrUInt(VObject));
             oSelf.Add(')');
           end;
         vtClass:
           if VClass<>nil then
             oSelf.AddShort(PShortString(PPointer(PtrInt(VClass)+vmtClassName)^)^);
         vtWideChar:
           oSelf.AddW(@VWideChar,1,Escape);
         vtPWideChar:
           oSelf.AddW(pointer(VPWideChar),StrLenW(VPWideChar),Escape);
         vtAnsiString:
           oSelf.Add(VAnsiString,Escape); // expect RawUTF8
         vtCurrency:
           oSelf.AddCurr64(VInt64);
         vtWideString:
           if VWideString<>nil then
             oSelf.AddW(VWideString,length(WideString(VWideString)),Escape);
         vtInt64:
           oSelf.Add(VInt64^);
{$ifdef UNICODE}  //no longer D5
         vtUnicodeString:
           if VUnicodeString<>nil then // convert to UTF-8
             AddW(VUnicodeString,length(UnicodeString(VUnicodeString)),Escape);
{$endif} end;
    ord('$'): with Values[ValuesIndex] do
           if Vtype=vtInteger then oSelf.Add2(VInteger);
    163: with Values[ValuesIndex] do // £
           if Vtype=vtInteger then oSelf.Add4(VInteger);
    181: with Values[ValuesIndex] do // µ
           if Vtype=vtInteger then oSelf.Add3(VInteger);
    end;
    inc(Format);
    inc(ValuesIndex);
  until false;
end;
{$endif DELPHI5OROLDER}

procedure TSynLog.Log(Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArg: RawUTF8;
  aInstance: TObject=nil);
begin
  if (self<>nil) and (Level in fFamily.fLevel) then
{$IFDEF DELPHI5OROLDER}  //thg
    SynLogLogInternal(self,Level,TextFmt,[TextArg],aInstance);
{$ELSE}
   LogInternal(Level,TextFmt,[TextArg],aInstance);
{$endif}
end;

procedure TSynLog.Log(Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArg: Int64;
  aInstance: TObject=nil);
begin
  if (self<>nil) and (Level in fFamily.fLevel) then
{$IFDEF DELPHI5OROLDER}  //thg
    SynLogLogInternal(self,Level,TextFmt,[TextArg],aInstance);
{$ELSE}
   LogInternal(Level,TextFmt,[TextArg],aInstance);
{$endif}
end;

{$ifndef DELPHI5OROLDER}
procedure TSynLog.LogInternal(Level: TSynLogInfo; TextFmt: PWinAnsiChar;const TextArgs: array of const; Instance: TObject);
...
end;
{$endif}

{$ifdef DELPHI5OROLDER} { array of const is buggy in Delphi 5 sad }  //thg
procedure SynLogLogInternal(oSelf:TSynLog;Level: TSynLogInfo; TextFmt: PWinAnsiChar;const TextArgs: array of const; Instance: TObject);
begin
  if not Assigned(oSelf) then Exit;oSelf.LogHeaderLock(Level);
  if Instance<>nil then oSelf.fWriter.AddInstancePointer(Instance,' ');
  TextWriterAdd(oSelf.fWriter,TextFmt,TextArgs,twOnSameLine);
  oSelf.LogTrailerUnLock(Level);
end;

procedure SynLogLog(oSelf:TSynLog;Level: TSynLogInfo; TextFmt: PWinAnsiChar; const TextArgs: array of const;aInstance: TObject=nil);
begin
  if (oSelf<>nil) and (Level in oSelf.fFamily.fLevel) then
    SynLogLogInternal(oSelf,Level,TextFmt,TextArgs,aInstance);
end;
{$endif DELPHI5OROLDER}

Application in "SynSQLite3.pas" can be done as an example:
function TSQLDataBase.DBOpen: integer;
...
  ss:PWinAnsiChar;//thg
begin
  ...
     ss:='open("%") failed';//thg
     fLog.Log(sllError,ss,utf8,self);
  ...
  {$ifdef DELPHI5OROLDER} //thg
     SynLogLog(fLog,sllDB,'open("%") with handle=%',[utf8,Cyph.Handle],self); //thg
  {$else}
     fLog.Log(sllDB,'open("%") with handle=%',[utf8,Cyph.Handle],self);  //thg
  {$endif}
  ....
    ss:='Handle reused for %';//thg
    if i>=0 then fLog.Log(sllError,ss,utf8) else begin //thg
  ....
end;


Testing Program

program SQLITED5;

uses SynCommonsd5,SynSQLite3D5;

var oDB:TSQLDataBase;
begin
    with SynSQLite3Log.Family do begin
      Level := LOG_VERBOSE;AutoFlushTimeOut := 10;HighResolutionTimeStamp := true;
    end;
    oDB:=TSQLDataBase.Create('E:\SynSQLite\SQLite3\Nwind.db');
    oDB.Execute('Select * from customers');
    oDB.DBClose;
end.


Log Results Program
E:\SynSQLite\SQLite3\SQLITED5.exe 0.0.0.0 (2012-09-07 14:41:30)
Host=TOHANGP User=Owner CPU=2*0-6-3851 OS=2.3=5.1.2600 Wow64=0 Freq=2599990000
TSynLog 1.17 2012-09-07T14:41:28

00000000000002BE  +    0041A584
0000000002090AE8 DB        TSQLDatabase(00990DBC) open("E:\SynSQLite\SQLite3\Nwind.db") with handle=1968
00000000020A9700  -    0041A93B  00.013.172
00000000020AC890  +    TSQLDatabase(00990DBC).0041A3C4
00000000020AD5EB SQL       Select * from customers
0000000003235B7E  -    TSQLDatabase(00990DBC).0041A431  00.007.072
0000000003237A1D  +    0041A4A6
0000000003263892  -    0041A51E  00.000.069

#5 mORMot 1 » SynSQLite3 and Delphi 5 compilation » 2012-09-02 15:10:32

Tohang
Replies: 3

I try to compile Delphi 5 SynSQLite3 degan and edit soursce program. I've tested and works well as follows:

program SQLITED5;

uses
   WINDOWS,SynCommonsd5,SynSQLite3D5;

var S:string; Res:Integer;oLite:TSQLRequest;nHDB:TSQLite3DB;
begin
  Res:=sqlite3_open('G:\SynSQLite\SQLite3\Nwind.db',nHDB);
  if Res=SQLITE_OK then begin
     oLite.Prepare(nHDB,'Select * from customers');
     oLite.Step;
     S:=oLite.FieldUTF8(1);
     MessageBox(0,PChar(S), 'Customer Name', MB_ICONWARNING or MB_OK);
     sqlite3_close(nHDB)
  end;
end.

SynSQLite3.pas
  ....
type
{$ifdef DELPHI5OROLDER}
{$A+} // bcc32 default alignment is 4 bytes
{$else}
{$A4} // bcc32 default alignment is 4 bytes
{$endif}
  ....
 
function TSQLDataBase.LastInsertRowID: Int64;
begin
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('LastInsertRowID=%d',[result]),self);
{$else}
      fLog.Log(sllDB,'LastInsertRowID=%',result,self);
{$endif}
  ....
end;

function TSQLDataBase.LastChangeCount: integer;
begin
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('LastChangeCount=%d',[result]),self);
{$else}
      fLog.Log(sllDB,'LastChangeCount=%',result,self);
{$endif}
  ....
end;

procedure TSQLDataBase.GetTableNames(var Names: TRawUTF8DynArray);
begin // SQL statement taken from official SQLite3 FAQ
  SetLength(Names,Execute(SQL_GET_TABLE_NAMES,Names));
{$ifdef DELPHI5OROLDER}
     // fLog.Log(sllDebug,'TableNames',TypeInfo(TRawUTF8DynArray),Names,self);
{$else}
      fLog.Log(sllDebug,'TableNames',TypeInfo(TRawUTF8DynArray),Names,self);
{$endif}
end;

function TSQLDataBase.DBOpen: integer;
var utf8: RawUTF8;
    i: integer;
    Cyph: TSQLCypher;
begin
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('open(%s) failed',[utf8]),self);
{$else}
    fLog.Log(sllError,'open("%") failed',utf8,self);
{$endif}
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('open(%s) with handle=%d',[utf8,Cyph.Handle]),self);
{$else}
   fLog.Log(sllDB,'open("%") with handle=%',[utf8,Cyph.Handle],self);
{$endif}
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllError,FormatUTF8('Handle reused for %s',[utf8]))
{$else}
      fLog.Log(sllError,'Handle reused for %',utf8)
{$endif}
  ....
end;

procedure TSQLDataBase.SetBusyTimeout(const ms: Integer);
begin
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('SetBusyTimeout=%d',[ms]),self);
{$else}
  fLog.Log(sllDB,'SetBusyTimeout=%',ms,self);
{$endif}
  ....
end;

procedure TSQLDataBase.RegisterSQLFunction(aFunction: TSQLDataBaseSQLFunction);
var i: integer;
begin
  ....
{$ifdef DELPHI5OROLDER}
      fLog.Log(sllDB,FormatUTF8('RegisterSQLFunction=%s',[aFunction.FunctionName]),self);
{$else}
  fLog.Log(sllDB,'RegisterSQLFunction "%"',aFunction.FunctionName,self);
{$endif}
  ....
end;

function TSQLDataBaseSQLFunction.CreateFunction(DB: TSQLite3DB): Integer;
begin
  ....
    {$ifdef WITHLOG}
    if result<>SQLITE_OK then
{$ifdef DELPHI5OROLDER}
      SynSQLite3Log.Add.Log(sllError,FormatUTF8('register SQL function %s() failed',[FunctionName]),self);
{$else}
     SynSQLite3Log.Add.Log(sllError,'register SQL function %() failed',FunctionName,self);
{$endif}
    {$endif}
  ....
end;

SynCommons.pas
{$ifdef DELPHI5OROLDER}
function FormatUTF8(const Fmt: string; const Args: array of const): RawUTF8; overload;
{$endif}

{$ifdef DELPHI5OROLDER}
function FormatUTF8(const Fmt: String; const Args: array of const): RawUTF8; overload;
begin
  Result:=StringToUTF8(Format(Fmt,Args));
end;
{$endif}

#6 Source Code repository » Compatibility Windows, Linux, Mac, Delphi, Free Pascal, Delphi XE2 » 2012-02-12 11:52:36

Tohang
Replies: 6

Why not use an existing function in the SysUtils or System.Sysutils for reading and writing process to obtain compatibility.

Is "mORMot database Framework" designed only for Windows?

SynSQLite3.WinWrite SetFilePointer why not FileSeek written, WriteFile with FileWrite.
SynSQLite3.WinRead written SetFilePointer why not FileSeek, ReadFile with FileRead

Have not made for 64 bit based sqite3.obj to use Free Pascal or Delphi XE2?

Sorry my english very poor, thanks in advance.

#7 Re: mORMot 1 » How do I access more than 64 tables in one database? » 2011-03-17 02:06:43

If the field already set the maximum number of 64, whether the number of record definitions can not be dynamically allocated?
Because the method dynamically allocating AddTable done SetLength (fTables, i +1);

#8 mORMot 1 » How do I access more than 64 tables in one database? » 2011-03-16 10:52:21

Tohang
Replies: 12

What is the maximum TSQLModel Record in formation, whether the 64 definitions of records?
if N>sizeof(SUPERVISOR_ACCESS_RIGHTS.Get)*8
What if more than 64 tables in the Database:
result: = TSQLModel.Create ([TSQLSampleRecord,...]);

I am interested to try, but I'm still confused user. Thank you for your guidance.

Board footer

Powered by FluxBB