Hi

I am converting an app from DBISAM3 to ElevateDB and I am stuck already on
the first SQL executed.

The actual SQL used is modified in code - putting in JOIN and WHERE
conditons and ORDER BY according to user selections.  I have simplified the
script and tried it out in EDBMgr - so what I show below might seem a bit
pointless ... but if I can't get the simplified version going what hope
is there

The problem seems to be that the dropping of the TEMPORARY table doesn't
seem to work as I get this message:-

ElevateDB Error #400 An error occurred with the statement at line 17 and
column 21 (The temporary table TmpLandlords already exists in the schema
Default)

.... it is saying "in schema  Default" - where is that?  Should I be able to
see that in EDBMgr?

Here is the Script:-
-------------------------------------------------------------------
SCRIPT
BEGIN

 DECLARE TmpCursor CURSOR WITH RETURN FOR Stmt;
 DECLARE InfoCursor SENSITIVE CURSOR FOR InfoStmt;
 PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'

/* Drop the table if it exists */
 OPEN InfoCursor USING 'TmpLandlords';
 IF (ROWCOUNT(InfoCursor) > 0) THEN
   EXECUTE IMMEDIATE 'DROP TABLE TmpLandlords';
 END IF
;

/* this select will have a WHERE constructed in code to select one or ALL
portfolios */
 EXECUTE IMMEDIATE 'CREATE TEMPORARY TABLE TmpLandlords AS     <<<<<<<<<<<
line 17
                   (SELECT DISTINCT LandlordCode
                    FROM Landlords L) WITH DATA'
 ;
 EXECUTE IMMEDIATE 'SELECT L.LandlordCode,
                           SortKey,
                           DayTimePhone,
                           AfterHoursPhone,
                           ContactName,
                           LastName,
                           FirstName, EMail, InternalCommentIsWarning
                    FROM Landlords L
                    JOIN TempLandlords T ON T.LandlordCode = L.LandlordCode
                    ORDER BY SortKey'  /* ORDER BY will be made in code */
;

END
-------------------------------------------------------------------

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Got it!

This statement ...

>  PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'

.... should be

PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE
Name=?'

Now I just have to work out how to write the script making my Temporary
table a memory table.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

Jeff


Glad to see you solved your first problem. The second one can be solved by running the query in the memory database (ie set the databasename property for the component to whatever your in-memory database is) and referencing the appropriate disk based database as part of your select statement.

Now I'm going to be wicked - go and look through these very newsgroups and you'll see I've posted a number of times a script that Tim gave me that takes a piece of SQL (ie you can create it programmatically) as a parameter and will create an in-memory table for you. I modified it to allow creation of an index as well.

I'll be kindish - the subject to look for is Temporary Table Script Questions


Roy Lambert [Team Elevate]

Hi Roy

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:CFAB2FDE-75E0-419D-805A-F5092E25A563@news.elevatesoft.com...

> Glad to see you solved your first problem. The second one can be solved by
> running the query in the memory database (ie set the databasename property
> for the component to whatever your in-memory database is) and referencing
> the appropriate disk based database as part of your select statement.

Thanks for that - don't think that I would picked that up - I'm working in
my "real" database, but the script has to work in the memory database and
cross reference to the real one.  Strikes as being slightly back to front
thinking - but I don't want to spark a new "NULL <> ''" debate

> Now I'm going to be wicked - go and look through these very newsgroups and
> you'll see I've posted a number of times a script that Tim gave me that
> takes a piece of SQL (ie you can create it programmatically) as a
> parameter and will create an in-memory table for you. I modified it to
> allow creation of an index as well.
>
> I'll be kindish - the subject to look for is Temporary Table Script
> Questions

AHA! I did read that post as part of my trawling through the newsgroups but
at the time I didn't understand scripting enough to realise it had my
answers and I don't see it as directly referencing my memory table problem.

So, I think:-

1.  I'll need to set the DatabaseName to the memory database  (? I'll have
to find out how to do that)
2.  "'SELECT * FROM Information.Tables WHERE Name=?';"  will need to change
to Information.TemporaryTables.
3.  The script has 4 parameters - I can see how to pass the SQLStatement,
TableName and IdxSet,  but how do I pass the "?" parameter - or should that
be  Name= "' +TableName + '" instead of  Name=?';

Lots to learn about ElevateDB - it's a bit different form DBISAM3, isn't it


Many thanks Roy

Cheers

Jeff

>
>
> Roy Lambert [Team Elevate]
>--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz
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

Jeff


>Thanks for that - don't think that I would picked that up - I'm working in
>my "real" database, but the script has to work in the memory database and
>cross reference to the real one. Strikes as being slightly back to front
>thinking

Not really, it takes a bit of getting your head round but the way to think of it is that the script is more interested in where you're creating the table than where the definition is coming from. It makes still more sense if you think of creating a table full stop. The script/query has to know where to create it and the way to tell it is to specify the database its working in.

>- but I don't want to spark a new "NULL <> ''" debate

Don't understand


>So, I think:-
>
>1. I'll need to set the DatabaseName to the memory database (? I'll have
>to find out how to do that)

On the component you're using. Ah the light bulb just went on. Its different to DBISAM in-memory databases are just the same as disk based ones now. You need to create an in-memory database yourself (mine surprisingly is called Memory) and just set the script/query component's DatabaseName to that database's DatabaseName.

>2. "'SELECT * FROM Information.Tables WHERE Name=?';" will need to change
>to Information.TemporaryTables.

Yup

>3. The script has 4 parameters - I can see how to pass the SQLStatement,
>TableName and IdxSet, but how do I pass the "?" parameter - or should that
>be Name= "' +TableName + '" instead of Name=?';

Its this bit
PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?';
OPEN InfoCursor USING TableName; <<<<<<<<<<<<< NOTICE this says replace the ? with TableName

>
>Lots to learn about ElevateDB - it's a bit different form DBISAM3, isn't it
>

And it was different from DBISAM4 as well!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Roy Lambert [Team Elevate]

Thanks Roy

This conversation about EDB scripts has help me understand how things work.
Not saying I won't be back for more, but at least I understand some of the
basics.

Cheers

Jeff

--
Jeff Cook
Aspect Systems Ltd
www.aspect.co.nz

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:FA4BE264-D543-42D0-9BD5-00A437518BAC@news.elevatesoft.com...