Topic: Few questions

Hi !

First I would thank you for your excellent components - great work !  smile

I am using SQlite (pass) for the first time - real rookie wink

1. how do I disable error log window ? I would like to catch database exceptions manually. What is the easiest way to do it ?
2. I am doing massive inserts. I've tried to post (commit) after each insert, then i've tried to post at the end of inserts, but there is no noticeable speed difference. Any thoughts ? (btw. I am not using SQL statements, but standard methods - insert, post, cancel etc.)

Thanks in advance. I'll probably come back here with new questions -  hope I won't bother you too much.

Zabukowski

p.s. using Delphi 2009

Last edited by zabukowski (2010-11-05 11:59:14)

Re: Few questions

Hi Zabukowski,

There is no property to disable the LogError Dialog. I will think about it in next release.
As a quick fix, you can easily switch back to classic exception handler if you change the code in SqlitePassDatabase.inc

procedure TSqlitePassDatabaseError.RaiseException(const Msg: String; const ErrorCode: Integer = -1;  Component: TComponent = nil; VerboseLevel: TSqlitePassVerboseLevel = vlLogAndShow);
begin
 { Track multi errors ? }
 if FTrackerErrorCount <> 0
   then VerboseLevel := vlLog;

 { Log, show or both ? }
 Case VerboseLevel of
      vlLog        : LogError(Msg, [], ErrorCode, Component);
      vlShow       : ShowErrorDialog(Msg);
      vlLogAndShow : begin
                     LogError(Msg, [], ErrorCode, Component);
                     ShowErrorDialog;
                     end;
      end;
Abort;
end;

with

procedure TSqlitePassDatabaseError.RaiseException(const Msg: String; const ErrorCode: Integer = -1;  Component: TComponent = nil; VerboseLevel: TSqlitePassVerboseLevel = vlLogAndShow);
begin
 { Track multi errors ? }
 if FTrackerErrorCount <> 0
   then VerboseLevel := vlLog;

 { Log, show or both ? }
 Case VerboseLevel of
      vlLog        : LogError(Msg, [], ErrorCode, Component);
      vlShow       :  raise Exception.Create(Msg);
      vlLogAndShow : begin
                     LogError(Msg, [], ErrorCode, Component);
                     raise Exception.Create(Msg);
                     end;
      end;
end;

Zabukowski wrote

I am doing massive inserts. I've tried to post (commit) after each insert, then i've tried to post at the end of inserts, but there is no noticeable speed difference. Any thoughts ? (btw. I am not using SQL statements, but standard methods - insert, post, cancel etc.)

Try
SqlitePassDatabase1.Engine.Transaction.Start;
....
your code
....
SqlitePassDatabase1.Engine.Transaction.Commit;


You are welcome if you want to send your code, bug reports...and your overall feeling about the components.

Thanks
Luc

Re: Few questions

Thanks for your quick answers.

I've changed code as you suggested and it works smile

btw. how can I determine which database error occured (errorcode ?) within standard exception handler ?

Regarding mass inserts using transaction start/commit ...

WOW, that's what I call speed big_smile Unbelievable ! I guess this nontransactional mode inserting is REALLY slow !


Another one:

I've created database with SQLite Expert (.db3). Then I changed extension to .db. Suddenly some database fields were missing when using database components within Delphi. Dataset component is showing only 2 of 6 fields. If I change extension back to .db3, everything is ok again. Is this normal ?

Thanks again !

Zabukowski

Re: Few questions

And another few issues, sorry sad

1. Trying to empty table ...

I found that emptytable function finds dataset type dtSQLSelect and not dtTable as expected. So, nothing is really deleted.
How do I create dtTable type ?

2. 'Dataset name' property is not reloaded with delphi project !? Is this ok ?

edit:
There is something strange going on.

I've removed following 2 lines

   if (DatasetName <> '') and not FInInternalSetDatasetType
      then DatasetName := '';

from procedure

Procedure TSqlitePassDataset.NotifySQLChanged(Value: TSqlitePassSQLChange);

Delete is now working. But still, if I reload project, dataset name is empty again. I have to choose table again. If I don't, delete is not successful.

edit:

If I set datasetname realtime in code, everything works (i put everything back, as it was). Looks like there is a problem with a datasetname property in design-mode only.

Thanks
Zabukowski

Last edited by zabukowski (2010-11-05 18:12:52)

Re: Few questions

Hi,

how can I determine which database error occured (errorcode ?) within standard exception handler ?

1 - If SqlitePassDatabase.Options.LogErrors = True then you can use the SqlitePassDatabase.DatabaseError.CurrentError.Code to retrieve the last Sqlite Error Code.
You can find more information in SqlitePassDbo (TSqlitePassDatabaseErrorItem and TSqlitePassDatabaseError)

2 - If not, Trap the ErrorCode in :
procedure TSqlitePassDatabaseError.RaiseException(const Msg: String; const ErrorCode: Integer = -1;  Component: TComponent = nil; VerboseLevel: TSqlitePassVerboseLevel = vlLogAndShow);
If ErrorCode was sent back by sqlite, it should be there.

3 - Finally, (Not the easiest way), you can directly use the Sqlite DLL functions (in SqlitePassApi_v3.pas)
SqliteDbv3_errcode:function(db:pointer):integer; cdecl;
SqliteDbv3_errmsg:function(db:pointer):pAnsiChar; cdecl;
SqliteDbv3_errmsg16:function(db:pointer):pWideChar; cdecl;
but you need to provide a handle/pointer on the current db. You will loose the advantage of the wrapper !


I've created database with SQLite Expert (.db3). Then I changed extension to .db.

This is the normal behavior :

function TSqlitePassDatabase.DatabaseTypeFromFileName(FileName: String): TSqlitePassDatabaseType;
begin
FileName := Lowercase(FileName);
      if (AnsiPos('.kexi',  FileName)) > 0 then Result := dbtKexi
 else if (AnsiPos('.s3db',  FileName)) > 0 then Result := dbtSqliteAdmin
 else if (AnsiPos('.db3',   FileName)) > 0 then Result := dbtSqliteExpert
 else if (AnsiPos('.s3fpc', FileName)) > 0 then Result := dbtSqlite4Fpc
 else if (AnsiPos('.sp3',   FileName)) > 0 then Result := dbtSqlitePass
 else Result := dbtUnknown;
end;

but you can always override the default setting :
Fill in the database property
set connected to false
set MyDb.DatabaseType := dbtSqliteExpert
set connected to true


I found that emptytable function finds dataset type dtSQLSelect

The code is :

procedure TSqlitePassDataset.EmptyTable;
begin
  if DatasetType = dtTable then
     begin
     FDatabase.Engine.ExecSQL('DELETE FROM "' + DatasetName + '";');
     Refresh;
     end;
end;

so, the DatasetName property is not a valid table name or your SQL Statement is not recognised as an existing table in the current database.

I check the other issues. Please, send a sample of your data if you want me to test it.

Thanks

Re: Few questions

Thanks !!

but you can always override the default setting :
Fill in the database property
set connected to false
set MyDb.DatabaseType := dbtSqliteExpert
set connected to true

I did that without success. Nevermind, I guess i did something wrong ... I'll leave extension as it is for now, no big deal.

I've discovered another thing, which I can't resolve.

I use 'locate' function to find a record with exact key value. But, if key value starts with a number, record is not found (key type is char(32)), if it starts with a letter it is normally found.

Is there something i am doing wrong ? I saw from sources that locate is using filters. Is there a function to find a record using index like FindKey in TTable ?

edit:
I will send you my database in the evening !

Thanks again
Zabukowski

Last edited by zabukowski (2010-11-06 13:35:18)

Re: Few questions

Hi Francis,

1 ------------
Yesterday, I came up with the same analysis. I have to change the way the parser deals with quoted strings.
Here is a temporarely and quick fix up (not enough time right now)

  Tips : when you want locate a KeyValue containing spaces and starting with a number, in a string field,
     the KeyValue must be between brackets [] in order to be parsed as a single statement i.e.
     MyDataset.Locate('StringField', '45 NEWSPAPERS', []) always returns FALSE
     MyDataset.Locate('StringField', '[45 NEWSPAPERS]', []) returns TRUE

Procedure ProcessNumber;
  var
  CharSet: Set of Char;
  begin
  if Char(FNestingCharStack[FNestingLevel]) = '['
     then ProcessIdentifier
     else
  begin
  if Char(FNestingCharStack[FNestingLevel]) in ['''', '"', '#']
     then begin
          { We have DateTime, Date or Time ? }
          CharSet := ['0'..'9', '.', '-', '/', ':', ' '];
          NewToken.TokenType := ttDateTime;
          end
     else begin
          { We have number ? }
          CharSet := ['0'..'9', '.'];
          NewToken.TokenType := ttNumber;
          end;
  While (Char(CurrentChar^) in CharSet) do Inc(CurrentChar);
  end;
  end;

Think to move the ProcessIdentifier proc above the ProcessNumber proc

2 --------------
DatasetName problem at designtime

procedure TSqlitePassDataset.SQLChanged(Sender: TObject);
begin
  NotifySQLChanged(scProcess);

  if (DatasetName <> '') and not (FInInternalSetDatasetType or (csLoading in ComponentState))
     then DatasetName := '';

  if FDatasetName = '' then
     begin
     FDatasetType := dtUnknown;
     ProcessSQLText;
     end;

  if FParamCheck
     then UpdateParamsList;
end;

Should be ok.

Looking forward further testing

Thanks.
Luc