Hi

I have a script ( apmDM.scrCreateTempTable) that starts off like this:-

----------------
SCRIPT (IN SQLStatement VARCHAR, IN TableName VARCHAR, IN IdxSet VARCHAR)
BEGIN
----------------

In Delphi, I say:-

----------------------------
   apmDM.scrCreateTempTable.ParamByName('SQLStatement').AsString := 'SELECT
....'; // actual SQL below my sig
   apmDM.scrCreateTempTable.ParamByName('TableName').AsString :=
sTempTableName;
   apmDM.scrCreateTempTable.ExecScript;
------------------------

.... and I get the following error message:-

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

I am using the same script elsewhere and it works fine (familiar to all as
it has been posted here many times, but pasted below just in case).

Can't see what is wrong, so hoping that someone can point out the obvious


Cheers

Jeff

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

SELECT MR.ReadingDate,
      MR.Reading,
      CAST(0 AS INTEGER) AS Usage,
      ST.TransType,
      MR.TransNo,
      MR.ReadingType, CAST(0 AS INTEGER) AS Days,
      CAST(0 AS INTEGER) AS UsagePerDay
FROM DB0000.MeterReadings MR
LEFT OUTER JOIN DB0000.SupplierTrans ST ON ST.TransNo = MR.TransNo
WHERE MeterID = 25
ORDER BY  ReadingDate

--------------------------------------------------------------------

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

OK, ignore me!

I've repeated the same mistake - If I PREPARE the script first, then I can set the parameters.  (I think Roy has told me this before!)

No complaining, but can't see why I have to prepare a script with parameters but not a query.  Probably some "Tim" technical reason - not intuitive enough for me, I'll just have to learn from my errors

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

On 12/11/2013 5:38 PM, Jeff Cook wrote:
> No complaining, but can't see why I have to prepare a script with parameters but not a query.  Probably some "Tim" technical reason - not intuitive enough for me, I'll just have to learn from my errors
>
Don't know if Tim has explained why but docs do mention it. Under
"Executing Script" it says

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

Raul

"Raul" <raul@removethis.raul.ca> wrote in message
news:7ECDF08E-6911-4AAA-A52A-C6D838A40D70@news.elevatesoft.com...

> Don't know if Tim has explained why but docs do mention it. Under
> "Executing Script" it says
>
> "...  You should only need to manually prepare a script when executing a
> script that requires parameters."
>

Accepted that it is in the manual but none of the error messages make me
think:- "Hmmm... better go and read up about preparing scripts!"

A case of easy when you know how (and haven't forgotten).

Cheers

Jeff

On 12/11/2013 8:10 PM, Jeff Cook wrote:
> Accepted that it is in the manual but none of the error messages make me
> think:- "Hmmm... better go and read up about preparing scripts!"

Agreed. It's just a notch above "one of those things you just have to
know".

I would be curious to get Tim to comment on this though since nothing
else AFAIK requires preparation  - queries can benefit from manual prep
it in terms of opening tables etc but if you don't then EDB still will
auto prepare and unprepare and everything just works (maybe slower but
still).

Maybe he can even remove the requirement - or change error message to
something more meaningful.

Raul

Raul,

<< I would be curious to get Tim to comment on this though since nothing
else AFAIK requires preparation  - queries can benefit from manual prep it
in terms of opening tables etc but if you don't then EDB still will auto
prepare and unprepare and everything just works (maybe slower but still). >>

I'll have a look at this again before 2.15.  It's been on the list of things
to address for a while.  There are some minor differences in how the
parameters are handled for stored procedures/scripts vs. queries, but I
think that they can be resolved.

Tim Young
Elevate Software
www.elevatesoft.com