mORMot Open Source

deathsfriend

Member

Registered: 2012-04-27

Posts: 6

Questions and Assistance required...

Hi All,

First, loving the framework. I've got it working in a project i'm working on right now and loving it.

However, I have some questions.

1. How can I update the contents of a TableToGrid?

The following code works fine for small databases ( 5 -6 records, tops). It even displays the Grid correctly for large record sets when its first displayed.

But when I try to refresh the Grid with a database containing 65,000 records, it crashes with an EAccessViolation exception;

procedure TfrmMain.RefreshRecords;
var
  rowCount : Integer;
begin

  if not(Assigned(TableToGrid))then
  begin
    Database.Update( Rec );

    rowCount := Database.TableRowCount( TSQLSampleRecord );
    lblRecordCount.Caption := Format ( '%d Records Available', [rowCount] );

    TableToGrid := TSQLTableToGrid.Create( Self.grdData,
                                           Rec.FillTable,
                                           Database );

    Rec.FillTable.OwnerMustFree := False;
  end
  else
  begin
    Database.Update( Rec );

    rowCount := Database.TableRowCount( TSQLSampleRecord );
    lblRecordCount.Caption := Format ( '%d Records Available', [rowCount] );

    if(TableToGrid.Refresh(True))then
        TableToGrid.Refresh();    // << Crashes here
  end;
end;

How do I get around that ?

2. How I can execute an SQL Query against a table - for example, I want to delete all the records where ID < X (Where X is a value the user entered, or the result of a calculation).
Currently I step though the database - there has got to be a faster way.

3. When a database grows, can it be shrunk on the disk ? For example with 66,000 records it grows to 5Mb. Can we Shrink the file back down ?

Cheers

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,589

Website

Re: Questions and Assistance required...

Thanks a lot for your feedback!

1. There was a refresh issue, which has been fixed in the latest version of the trunk.
Try to update your code from http://synopse.info/fossil - see http://synopse.info/fossil/wiki?name=Get+the+source

2. For such queries, you can use directly a SQL statement on the server side.
If you want to be compatible with the security and SOA patterns of the framework, I think the best is to have a dedicated service for such queries.
But ensure that:
- You have set the appropriate user rights check to the Service method: ensure that the connected user has the right to do this;
- If you use caching (a feature of the trunk version 1.16), be sure you flush the cache for the corresponding table;
- This update is called with TSQLRestServerDB.EngineExecuteFmt, as such:

  EngineExecuteFmt('delete from % where ID < ?',[Table.SQLTableName],[IDMax]);

3. You can launch, on the server side, a VACUUM standard statement.
See http://www.sqlite.org/lang_vacuum.html
It will recreate the whole DB content after a massive deletion of rows, for instance.

deathsfriend

Member

Registered: 2012-04-27

Posts: 6

Re: Questions and Assistance required...

Hi,

So I managed to solve the refresh issue with a code update.

However, thats broken one important part.

I was using

SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := true;

To enable the clients to execute SQL statements. However, the latest code update changed AllowRemoteExecute from Boolean to TSQLAllowRemoteExecute = set of (reSQL, reService, reUrlEncodedSQL);

I tried to use the line;

SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := [reSQL];

Do I need to give it full rights ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,589

Website

Re: Questions and Assistance required...

Do I need to give it full rights ?

Two other options are for services.
Be aware that some low-level functions of the framework uses internally services.

So for supervisor rights, it could make sense to add reService!

deathsfriend

Member

Registered: 2012-04-27

Posts: 6

Re: Questions and Assistance required...

Do I need to give it full rights ?

Two other options are for services.
Be aware that some low-level functions of the framework uses internally services.

So for supervisor rights, it could make sense to add reService!

Even using the line;

SUPERVISOR_ACCESS_RIGHTS.AllowRemoteExecute := [reSQL,reService, reUrlEncodedSQL];

doesn't work. The following SQL Statement never runs;

Database.EngineExecuteFmt( 'DELETE FROM % WHERE ID <= ?',
                                  [recTemp.SQLTableName],
                                  [tblTemp.IDColumnHiddenValue(rowCount)] )

It always returns false, and nothing is deleted. This worked previously; so what am I missing here?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,589

Website

Re: Questions and Assistance required...

Could you use the debugger, then step into the EngineExecuteFmt() method - i.e. the EngineExecute() function - and see where its execution is aborted.

deathsfriend

Member

Registered: 2012-04-27

Posts: 6

Re: Questions and Assistance required...

Could you use the debugger, then step into the EngineExecuteFmt() method - i.e. the EngineExecute() function - and see where its execution is aborted.

I fixed the issue just before you posted, and haven't been able to report back with my findings.

Basically, switching out the line

Database.EngineExecuteFmt( 'DELETE FROM % WHERE ID <= ?',
                                  [recTemp.SQLTableName],
                                  [tblTemp.IDColumnHiddenValue(rowCount)] )

for

sqlStr := Format( 'DELETE FROM %s WHERE ID <= %d',[ recTemp.SQLTableName, tblTemp.IDColumnHiddenValue(rowCount)] );
if(Database.EngineExecute( sqlStr ))then

And it magically started working once again (I hate this method of doing it, btw - I'd like to use EngineExecuteFmt).

If I use the EngineExecuteFmt line, it breaks, but EngineExecute is fine. In case it helps, this is a relatively straight forward setup; I have a named pipe server running in a Windows Service, with serveral desktop applications feeding into it. I'm not bothered about user authentication (At this point).

Is there a demo of authentication, and how to use it/manage it ?

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,589

Website

Re: Questions and Assistance required...

This is a weird issue.

I can't understand why EngineExecuteFmt fails, and not EngineExecute.

Is the following code working:
Database.EngineExecuteFmt( 'DELETE FROM % WHERE ID <= %',
                                  [recTemp.SQLTableName,tblTemp.IDColumnHiddenValue(rowCount)],[] )

Perhaps this is a "prepared statement" issue, if the ? parameters are not working as expected.

Did you retrieve the latest version of mORMot from http://synopse.info/fossil/wiki?name=Get+the+source ?
There was an issue with hashing, in some cases, as far as I remember.

deathsfriend

Member

Registered: 2012-04-27

Posts: 6

Re: Questions and Assistance required...

This is a weird issue.

I can't understand why EngineExecuteFmt fails, and not EngineExecute.

Is the following code working:
Database.EngineExecuteFmt( 'DELETE FROM % WHERE ID <= %',
                                  [recTemp.SQLTableName,tblTemp.IDColumnHiddenValue(rowCount)],[] )

Perhaps this is a "prepared statement" issue, if the ? parameters are not working as expected.

Did you retrieve the latest version of mORMot from http://synopse.info/fossil/wiki?name=Get+the+source ?
There was an issue with hashing, in some cases, as far as I remember.

I'm using the latest version; and your code above works. My original code doesn't... the only difference seems to be the ? so it maybe its the hashing.