Hi Team,
Working in EDBMgr, I thought the following should work..
{sql}
CREATE VIEW "DB1.Parts" AS
select * from DB2.Parts
WITHOUT CHECK OPTION
VERSION 1.00
{sql}
It 'prepares' OK but I get the following error on Execute..
"ElevateDB Error #401 The table or view Parts does not exist in the schema Default"

What am I doing wrong?  Or, for that matter, can I do this?

Regards & TIA,
Ian

Ian


To check things out I altered your statement a bit

First using database DBiWorkflow

{sql}
CREATE VIEW "DBiWorkflow.PartsX" AS
select * from DBiWorkflow.Parts
WITHOUT CHECK OPTION
VERSION 1.00
{sql}

ran the query from and << ElevateDB Error #406 Invalid view identifier 'DBiWorkflow.PartsX' in the schema Default>>

This I would expect because you're not allowed database modifiers in DDL queries

next

{sql}
CREATE VIEW "PartsX" AS
select * from DBiWorkflow.Parts
WITHOUT CHECK OPTION
VERSION 1.00
{sql}

That works

OK run in database DBiHistory and <<ElevateDB Error #401 The table or view Parts does not exist in the schema Default>>


since the query "select * from DBiWorkflow.Parts" is valid and runs fine in the database DBiHistory I think its a bug. However, since I haven't read the SQL spec I may well be wrong. I suggest reporting to Tim unless someone points out that it is what the spec says.

Roy Lambert

And that's a No!  
I checked with Tim too.
"Unfortunately, no.  You cannot have any references to another database in any database objects because doing so would make the catalog unable to be loaded if the other database isn't available or present."

Sigh!  Back to the drawing board.

Regards & Tks,
Ian

Ian


This is where you need to cheat - I'll knock up an example but it will take a while because I have cudgel my memory into co-operating

Essentially what you do is create a temporary table in the target database using the definition stored in the information database for the desired database and then populate it. The other alternative is to just use a standard query. Do you need to edit the data and have it get back to the source? If so that gets a bit trickier.


Roy Lambert

Ian


Not as hard as I thought

SCRIPT
(IN TblName VARCHAR)
BEGIN
DECLARE Grabber VARCHAR DEFAULT 'SELECT Name,CreateSQL FROM DBiWorkflow.Information.Tables WHERE Name = ''XX''';
DECLARE Transfer VARCHAR DEFAULT 'INSERT INTO XX SELECT * FROM DBiWorkFlow.XX';
DECLARE DBCursor CURSOR FOR DBStmt;
DECLARE Maker VARCHAR DEFAULT '';

PREPARE DBStmt FROM REPLACE('XX' WITH TblName IN Grabber);

OPEN DBCursor;
FETCH FIRST FROM DBCursor ('CreateSQL') INTO Maker;
CLOSE DBCursor;

EXECUTE IMMEDIATE REPLACE('CREATE TABLE' WITH 'CREATE TEMPORARY TABLE' IN Maker);
EXECUTE IMMEDIATE REPLACE('XX' WITH TblName IN Transfer);

END


May need a bit more work depending on what you want to do with it.

Roy Lambert

Hi Roy,
Thank you.  I think.
I have no idea what it is doing.
Let's assume I need a view of the DBiWorkflow.Company table in DBiArchive.
I figure I put Company in place of XX but what is TblName?
What does this leave me with to use?
No editing is involved, all read only for reporting.

Regards,
Ian

OK.  Sussed it out enough to apparently get it working in EDBMgr.
I can select records from the Temp tables but they do not appear as a table under the Database.
Thus they can't be 'seen' in my program.

Ian

As you probably figured out TblName is the name of the table you want.


The table is created as a temporary table (means it will disappear when the app is closed). Those don't show in EDBManager's list of tables but are accessible simply by referring to them.

Do you have a small demo app that shows what you're trying to do? Are you, for example and guessing from your post, trying to use then as a lookup table? Since you quote Company are you trying to show the company name in AJobTickets?

Roy Lambert

Hi Roy,
Within Delphi code it isn't an issue,  I can refer to either database.
It is for Reporting, the report engine can only address/use one database.
As the selected Live data records will move to the Archive when they run the Archive app, I have decided to copy/update the additional relevant tables, Company, Parts, Customers & Accounts from Live to Archive each time the Archive app is run.  They will just sit there for any time they need to be interrogated by the Report engine.

Tks for your efforts,
Ian

Ian


Couldn't you just use queries in that case? You can have persistent fields, and a query can point to the other database. The only thing you might need and can't have is indices.

Roy Lambert