Peter

> 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;

You do know that using "with" is selling your soul to Satan

>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.

I think this is where your problem is. You're trying to create a table but the query you're using to do it is trying to point to the table you're creating which doesn't exist yet.

>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.

Its quite correct because you've told the query to use a datasource

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

See above

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

But in EDBManager you can't tell the query to use a datasource.

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

Nope. I do both.

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

All you need to do is remove the datasource reference from qryBrowseMem, make sure the database qryBrowseMem is pointing at is the in-memory one and you're in business. You'll need to hook up TEDBTable to ReposTemp afterwards

The script is below my sig

Roy Lambert [Team Elevate]


SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN     
DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;

PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName;

IF (ROWCOUNT(InfoCursor) > 0) THEN
 EXECUTE IMMEDIATE 'DROP TABLE "'+TableName+'"';
END IF;

CLOSE InfoCursor;

PREPARE ResultStmt FROM 'CREATE TABLE "'+TableName+'" AS '+SQLStatement+' WITH DATA';
EXECUTE ResultStmt;

IF IdxSet IS NOT NULL THEN
 PREPARE ResultStmt FROM 'CREATE INDEX Idx ON "'+TableName+'"('+IdxSet+')';
 EXECUTE  ResultStmt;
END IF;
END

On 7/09/2013 5:57 PM, Roy Lambert wrote:

>
> You do know that using "with" is selling your soul to Satan

Possibly, must be a lost cause.


>
> Its quite correct because you've told the query to use a datasource

Yes, this lead looks promising.



>
> But in EDBManager you can't tell the query to use a datasource.


Yep, but at least I could one CREATE TABLE AS to work!


>
> SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)

Thanks for script.

Will try your suggestions and reply.

Regards,
  Peter Evans

On 7/09/2013 6:24 PM, Peter Evans wrote:

>
> Will try your suggestions and reply.

Success at last. What I did was to place a TEDBScript component on my
data module. I call it edbscriptCreateTableAs. I also set the database
name.

Then I wrote a procedure which has the following in it :-

    DataModule.edbscriptCreateTableAs.SQL.Clear;

    DataModule.edbscriptCreateTableAs.SQL.Add(
      'SCRIPT (IN SQLStatement VARCHAR,' +
      '        IN TableName VARCHAR,' +
      '        IN IdxSet VARCHAR)' +
      ' BEGIN' +
      '  DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;' +
      '  DECLARE ResultCursor SENSITIVE CURSOR FOR ResultStmt;' +
      '  PREPARE InfoStmt FROM ''SELECT * FROM Information.Tables WHERE
Name=?'';' +
      '  OPEN InfoCursor USING TableName;' +
      '  IF (ROWCOUNT(InfoCursor) > 0) THEN' +
      '    EXECUTE IMMEDIATE ''DROP TABLE "'' + TableName + ''"'';' +
      '  END IF;' +
      '  CLOSE InfoCursor;' +
      '  PREPARE ResultStmt FROM ''CREATE TABLE "'' + TableName + ''"
AS '' + SQLStatement + '' WITH DATA'';' +
      '  EXECUTE ResultStmt;' +
      '  IF IdxSet IS NOT NULL THEN' +
      '    PREPARE ResultStmt FROM ''CREATE INDEX Idx ON "'' +
TableName + ''"('' + IdxSet + '')'';' +
      '    EXECUTE ResultStmt;' +
      '  END IF;' +
      ' END'
      );

    DataModule.edbscriptCreateTableAs.Prepare;


DataModule.edbscriptCreateTableAs.ParamByName('SQLStatement').AsString :=
      'SELECT * FROM "' + DatabaseStr + '"."REPOS"' +
      '  WHERE Standard = True';

    DataModule.edbscriptCreateTableAs.ParamByName('TableName').AsString
:= 'ReposTemp';

    DataModule.edbscriptCreateTableAs.ExecScript;

So thanks again Roy for the script.
I have included the script to help others wanting to know how to set
parameters and call a script.

There is one fly in the ointment though. I will get to that in a moment.

After calling the procedure, and then appending the data I want to
append to table ReposTemp, and then running a query to get the data from
ReposTemp to the query, I want to drop the table. Then I display stuff
on the screen. What appears on the screen is correct.

So what is the fly in the ointment? It is dropping the table ReposTemp.

I get the error 'ElevateDB Error #300 Cannot lock the table ReposTemp in
the schema Default for exclusive access'.

I even put in two lines to and make sure the table was closed or whatever.

  TargetDataMod.edbscriptCreateTableAs.UnPrepare; {free resources}
  TargetDataMod.ReposTemp.Close;
  if TableLowUtil.TableExists(TargetDataMod.DBTempMem, 'ReposTemp') then
      TableLowUtil.TableDrop(TargetDataMod.DBTempMem, 'ReposTemp');

But even putting in the two line to UnPrepare and Close did not help.

When the TableDrop routine is called it gets the error.

What do I have to do to the table so it can be Dropped?

Regards,
  Peter Evans

Peter


>After calling the procedure, and then appending the data I want to
>append to table ReposTemp, and then running a query to get the data from
>ReposTemp to the query, I want to drop the table. Then I display stuff
>on the screen. What appears on the screen is correct.

This could be your problem. You're using ReposTemp in whatever this query is called and until that query is closed (and probably unprepared) you can't drop the table.

Roy Lambert [Team Elevate]

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

>
> This could be your problem. You're using ReposTemp in whatever this query is called and until that query is closed

I have now coded :-

  TargetDataMod.qryBrowse.UnPrepare;
  TargetDataMod.ReposTemp.Close;

On my limited tests these lines enable the dropping of the table without
an exception.

Can I dare to hope these routines are now working? Thanks Roy for your
assistance.

Regards,
  Peter Evans

Peter


>Can I dare to hope these routines are now working?

No!

What you can hope is that the Great Electron will deign to listen to your supplications and grant that your code will work THIS TIME.

The Great Electron may decide at any time for ITs own inscrutable reasons to prevent your code from working at all, or to insert any bug IT so chooses to do.

Well that's what seems to happen to me anyway.

Roy

Roy Lambert wrote:

> What you can hope is that the Great Electron will deign to listen to
> your supplications and grant that your code will work THIS TIME.


+1 "The Great Electron"

--
Michael Riley
GySgt USMC (Ret)
www.zilchworks.com

On 10/09/2013 12:10 AM, Roy Lambert wrote:

>
> What you can hope is that the Great Electron will deign to listen to your supplications

Oh, you gave me a fright as I first read what you wrote at "What you can
hope is that the Great Election will deign to listen to your
supplications". You see that on the weekend we had a Great Election here...

Regards,
  Peter Evans

Peter


My sympathies. I'm still waiting for an election where an option on the ballot paper is "take them out, stand against a wall and shoot them"

I know there are some good MPs (or whatever the Oz term is for them) but I'd be willing to risk the collateral damage to get rid of the "normal" ones.

Roy Lambert

Peter,

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

First mistake....just kidding, Roy.

Sorry I got to this thread late, but Roy handled things just fine, as
always.

Tim Young
Elevate Software
www.elevatesoft.com