Hi

I have a script - basically the same as posted here by Tim and Roy - which
appears after my signature.

I have my SQL which runs correctly in EDB Manager - also below signature.

I attempt to run the script like this:-

   apmDM.scrCreateTempTable.ParamByName('TableName').AsString :=
sTempFileName; // <<<< Error here <<<<<<<<<
   apmDM.scrCreateTempTable.ParamByName('SQLStatement').AsString :=
memSQL.Text;
   apmDM.scrCreateTempTable.ParamByName('IdxSet').AsString :=
SequenceSelect;
   apmDM.scrCreateTempTable.ExecScript;

.... and I get this error message:-

Project apm.exe raised exception class EDatabaseError with message
'scrCreateTempTable: Parameter 'TableName' not found'.

So I'm obviously not passing the parameters correctly.  I'm assuming that
the ParamByName is looking for something like ":TableName" in the script
just like a DBISAM3 query which I can see is wrong, but can't find any
example in the manual about passing parameters from Delphi into a
TEDBScript.

TIA

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 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
------------------------------------------------------------------------------------------------------------------------------
SELECT L.*, CAST(InternalComment AS CHAR(30)) AS InternalCommentBrief,
      F.Description AS FeeDesc, CAST(StatementMessage AS CHAR(30)) AS
StatementMessageBrief,
      CAST(True AS Boolean) AS Selected,
      CAST(0 AS DECIMAL(19, 2)) AS Balance ,
      CAST(True AS Boolean) AS Managed
FROM APM.Landlords L
LEFT OUTER JOIN APM.Fees F ON F.FeeCode = L.FeeCode
WHERE TRUE AND Hide = False
---------------------------------------------------------------------------------------------------------------------------------

Jeff


Wild guess - you need to prepare the query / script.

It caught me out a few times.

Roy Lambert [Team Elevate]

"Roy Lambert" <roy@lybster.me.uk> wrote in message
news:9443BE6E-3DE3-4B76-A4DB-E4F86D210468@news.elevatesoft.com...
> Jeff
>
>
> Wild guess - you need to prepare the query / script.
>
> It caught me out a few times.
>
> Roy Lambert [Team Elevate]
>

Hi Roy

Thanks that was it.

The manual says:-
-----------------------------------
Preparing the script

By default ElevateDB will automatically prepare a script before it is
executed. However, you may also
manually prepare a script using the TEDBScript Prepare method. Once a script
has been prepared, the
Prepared property will be True. Preparing a script compiles the script,
opens all referenced tables, and
prepares all internal structures for the execution of the script. You should
only need to manually prepare
a script when executing a script that requires parameters.
----------------------------------
Perhaps this:-

"You should only need to manually prepare a script when executing a script
that requires parameters."

should read:-

"You MUST manually prepare a script when executing a script that requires
parameters."
----------------------------------

As an aside, I'm finding it hard to find my way around the manuals anyway -
for instance, despite using the find facility in the PDF's I still can't fin
a list of error codes.  Constantly flipping between the edb2sql.pdf and the
edb2rsdelphiunixe.pdf.

Cheers and thanks again, Roy

Jeff

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

Aha!

I was fooled (not hard!) by the "This page intentionally left blank" on page
550 headed "Appendix A - Error Codes and Messages" - but in fact the error
messages are on page 551 - doh!

Compounded by searching for "#401" as in the error message, but it is
documented as "...(401)" - double duh!

Cheers

Jeff

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

"Jeff Cook" <jeffc@aspect.co.nz> wrote in message
news:06F133E4-E9DC-4617-95D5-3F7641A1E0EC@news.elevatesoft.com...

> As an aside, I'm finding it hard to find my way around the manuals
> anyway - for instance, despite using the find facility in the PDF's I
> still can't fin a list of error codes.  Constantly flipping between the
> edb2sql.pdf and the edb2rsdelphiunixe.pdf.

Jeff


I hate the fact that page numbering gets out of sync in Adobe.

Roy Lambert