Subject : Append data to a query

I am porting a DBISAM 4 application to ElevateDB. There may well be 2
problems.

Here is the code which has problems with ElevateDB.

        SourceDataMod.Source.Open;
        SourceDataMod.Source.First;
        {TargetDataMod.qryBrowseSource.SuppressAutoIncValues := True; XE2}

        WHILE NOT SourceDataMod.Source.EOF DO BEGIN
          TargetDataMod.qryBrowseSource.Append;      <================
          FOR iIterate := 0 TO (SourceDataMod.Source.FieldCount - 1) DO

TargetDataMod.qryBrowseSource.Fields[iIterate].Assign(SourceDataMod.Source.Fields[iIterate]);

          TargetDataMod.qryBrowseSource.Post;
          SourceDataMod.Source.Next;
        END;

Problem 1)

  When I run my program I get an exception at the line with Append in
it. See the line with an arrow. "EDatabaseError with message
'qryBrowseSource: Cannot modify a read-only dataset".

If I place the following line of code in the line above the WHILE statement

        ShowMessage('qryBrowseSource ReadOnly=' +
                    IsTrueStr(TargetDataMod.qryBrowseSource.ReadOnly));

I see qryBrowseSource ReadOnly=T

So far this error makes sense as qryBrowseSource is apparently read only.

So going back a step to where the qryBrowseSource is created :-

  WITH DataModule.qryBrowseSource DO BEGIN
      Close;
      SQL.Clear;

      SQL.Add(  blah blah );

      ShowMessage('qryBrowseSource ReadOnly=' +
        IsTrueStr(ReadOnly)); {I see qryBrowseSource ReadOnly=F}
      Open;
      ShowMessage('qryBrowseSource ReadOnly=' +
        IsTrueStr(ReadOnly)); {I see qryBrowseSource ReadOnly=T}

  END;

So it seems this is the point where there is a problem. How do I make
qryBrowseSource ReadOnly = False?

Problem 2)

  I have had to comment out the line :-

  TargetDataMod.qryBrowseSource.SuppressAutoIncValues := True;

because SuppressAutoIncValues is not supported in ElevateDB.

I do not want the one generated field to be changed when the record is
Appended.
Hopefully this field will not be changed?

Regards,
  Peter Evans

On 4/09/2013 12:04 PM, Peter Evans wrote:

>
> Problem 2)

>
> I do not want the one generated field to be changed when the record is
> Appended.
> Hopefully this field will not be changed?
>

I think I can now answer my own question for Problem 2.
The documention for ElevateDB says "AutoInc Fields  ...  The way autoinc
fields work now is that they will auto-increment if a value is not
specified for the field before the Post operation (field is NULL), and
will leave any existing value alone if one is already specified before
the Post operation".

Regards,
  Peter Evans

Peter

Problem 1


Forget ReadOnly (OK it can play a part but for now it can be igniored)- check for wether its sensitive or not (TEDBQuery.Sensitive). Jargon has changed ElevateDB no longer talks about canned or live result sets but about insensitive and sensitive. You can't edit an insensitive result set - end of story!

You can ask for a result set to be sensitive by setting the TEDBQuery.ResquestSensitive to True (by default its False), BUT you may not get what you want anyway

I forget the exact rules but if the result set is formed using JOINs its probably going to end up insensitive (ie canned).

If you need to edit an insensitive result set then you'll need to transfer the data into an in-memory or temporary table and use that.

Roy Lambert [Team Elevate]

On 4/09/2013 7:16 PM, Roy Lambert wrote:
>
> Problem 1
>
>
> Forget ReadOnly (OK it can play a part but for now it can be igniored)
>
> If you need to edit an insensitive result set then you'll need to transfer the data into an in-memory or temporary table and use that.
>

I am still struggling with this Problem.

To recap - I run a query, then I read a table row by row, and add each
row to the query.

This used to work in DBISAM. However under ElevateDB I get the error
'qryBrowseSource: Cannot modify a read-only dataset'.

I tried :
  RequestSensitive := True

Oh dear, each row from the table got added into the table referenced by
the query, and not added to the query. This lead to a Duplicate key
error in the table!

In the web page 'Result Set Cursor Sensitivity' it mentions that this is
what will happen. So it seems I cannot set RequestSensitive = True.

Regards,
  Peter Evans

Peter

>I am still struggling with this Problem.

Post the sql for the query.


Roy Lambert [Team Elevate]

Peter,

> This used to work in DBISAM. However under ElevateDB I get the error
> 'qryBrowseSource: Cannot modify a read-only dataset'.

That's correct. In DBISAM you were able to add records to a "readonly"
table. In EDB *NOT*.

> I tried :
>    RequestSensitive := True
>
> Oh dear, each row from the table got added into the table referenced by
> the query, and not added to the query. This lead to a Duplicate key
> error in the table!

That's normal. By using a sensitive query referencing the same table,
the query tries to add records to the table with a key that already exists.

I would be interesting to know why you are doing that.

Uli

On 5/09/2013 5:51 PM, Uli Becker wrote:

>
> That's normal. By using a sensitive query referencing the same table,
> the query tries to add records to the table with a key that already exists.
>
> I would be interesting to know why you are doing that.
>

Well, I was following a suggestion that Roy made...

Regards,
  Peter Evans

On 5/09/2013 5:46 PM, Roy Lambert wrote:
> Peter
>
>> I am still struggling with this Problem.

What I was doing under DBISAM was :-
1) run a query against a table in a database.
2) then I read a table from a completely different database
adding each row to 1).

Then pass the query 1) on to the next stage of my program.

Note - The input tables have the same structure.

Perhaps I have to do the following :-
1) run a query against a table in a database.
2) create a temporary table.
3) add each row from 1) into 2).
4) somehow do a Union of 2) and the table in the completely different
database.
5) Close 1).
6) Select * from 4) into 1).

Then pass the query 1) on to the next stage of my program.

Regards,
  Peter Evans

Peter

>I am still struggling with this Problem.
>
>What I was doing under DBISAM was :-
>1) run a query against a table in a database.
>2) then I read a table from a completely different database
>adding each row to 1).
>
>Then pass the query 1) on to the next stage of my program.
>
>Note - The input tables have the same structure.
>
>Perhaps I have to do the following :-
>1) run a query against a table in a database.
>2) create a temporary table.
>3) add each row from 1) into 2).
>4) somehow do a Union of 2) and the table in the completely different
>database.
>5) Close 1).
>6) Select * from 4) into 1).
>
>Then pass the query 1) on to the next stage of my program.

I was going to shout SHOW YOUR SQL CODE but looking at what you've written I think you want, in DBISAM, terms a canned query. Those no longer exist in ElevateDB. I have a couple of ideas as to which way you need to go, but since I can't see your app or requirements I'll make a few comments.

Yes you want either a temporary table or an in-memory table. I go for the latter because 1) temporary tables are self cleaning unless there's a crash. In-menory table always clean themselves up 2) DBISAM didn't have temporary tables and 3) gut feel that in-memory ought to be a lot faster (not necessarily true).

To create either in ElevateDB use the CREATE TABLE AS syntax. There's no need to do steps 1,2 &3 they can all be done in one step. Tim gave me a great script for creating an in-memory table from the sql select statement. If you can't find it on these ngs and you want it let me know and I'll post it again.

Depending on what the different database is you have a number of options. If its another ElevateDB database then you may be able to add it into the same session. If so you should be able to use a UNION ALL to add the rows in as part of the select statement creating the temporary table. IE you may be able to get everything into one SQL statement.

We need a bit more info here.

Once you have your table you can do pretty much what you want to it, its just another table. Depending on the programs flow I'd probably suggest a temporary table since then you don't have to bother with an in-memory database which makes things a bit simpler.

Roy Lambert [Team Elevate]

On 5/09/2013 9:15 PM, Roy Lambert wrote:

>
> To create either in ElevateDB use the CREATE TABLE AS syntax. There's no need to do steps 1,2 &3 they can all be

done in one step. Tim gave me a great script for creating an in-memory
table from the sql select statement.

If you can't find it on these ngs and you want it let me know and I'll
post it again.

I decided to go the path of an in-memory table. Also to combine Steps 1,
2 and 3 as you suggested.

Code it like :-

    WITH DataModule.qryBrowseMem DO BEGIN
      Close;
      SQL.Clear;
      SQL.Add('CREATE TABLE "ReposTemp" AS' +
              ' SELECT * FROM "' + DatabaseStrRepos + '"."REPOS"'   +
              '   WHERE Standard = True' +
              ' WITH DATA');
      Open;
    END;

I get the error message :-

  401 The table or view ReposTemp does not exist in the schema Default.

On the data module I have the component TEDBQuery (Name = qryBrowseMem)
which points to the
data source TDataSource which in turn points to the data set
TEDBTable which is ReposTemp.

Note - I do have other memory tables. Those all work. For each of them I
CREATE the table the usual way. The above is the first time I have tried
CREATE TABLE AS.

To my mind, yes, the table ReposTemp does not exist because the SQL is
trying to create it. To me the error message is meaningless.

So table ReposTemp should be in the memory database. So table Repos
should be in the disc database. Yes it is on disc and I can see its
contents using ElevateDB Manager Unicode.

I know the error message is telling me something, but at this stage I
just don't see it.

I have tried running similar CREATE TABLE AS in the ElevateDB Manager
Unicode and that code can create a table on disc.

Does the CREATE TABLE AS only work to create a disc table and not an
in-memory table?

I think I do need to look at the Script you offered as it might be what
I need.

Regards,
  Peter Evans