Support Forums - View Thread

View Thread

The following is the text of the current message along with any replies.

Messages 1 to 3 of 3 total

Thu, Nov 21 2013 6:03 PM	Permanent Link
Jeff Cook Aspect Systems Ltd	Hi With the following script, which is blatantly plagiarised from this very newsgroup, I would think that I could run it repeatedly with the same TableName parameter but it comes up with an error:- ----------------------------------------------------------- Project apm.exe raised exception class EEDBException with message 'ElevateDB Error #400 The temporary table Temp00001015 already exists in the schema Default'. ----------------------------------------------------------- .... which, to me, means that the DROP TABLE is failing. I have closed the script, unprepared it, closed the table ... and can't think of anything else to do. Here is my Delphi code:- memSQL.Lines.Strings[12] := RangeSelect; << this is why I'm re-running the script - user changed parameters apmDM.scrCreateTempTable.Close; apmDM.scrCreateTempTable.UnPrepare; tblPropertyExport.Close; apmDM.scrCreateTempTable.Prepare; apmDM.scrCreateTempTable.ParamByName('TableName').AsString := sTempFileName; apmDM.scrCreateTempTable.ParamByName('SQLStatement').AsString := memSQL.Text; apmDM.scrCreateTempTable.ParamByName('IdxSet').AsString := SequenceSelect; Cheers Jeff -- 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 TEMPORARY 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
Thu, Nov 21 2013 9:13 PM	Permanent Link
Jeff Cook Aspect Systems Ltd	Found it myself - (pats self on back!) > PREPARE InfoStmt FROM 'SELECT * FROM Information.Tables WHERE Name=?'; Should be PREPARE InfoStmt FROM 'SELECT * FROM Information.TemporaryTables WHERE Name=?'; Somewhat obvious when you know! - thinks "Have to get to grips with this 'Information.' stuff!" Cheers Jeff -- Jeff Cook Aspect Systems Ltd www.aspect.co.nz
Fri, Nov 22 2013 3:45 AM	Permanent Link
Roy Lambert NLH Associates Team Elevate	Jeff Have a pat on the head from me to go with the pat on the back from yourself. Roy Lambert