Tim Young [Elevate Software] wrote:

> BTW, I noticed that you're using the term "stored procedure" and also
> referring to the scripts in DBISAM.  The two are not the same thing.
> The closest thing to DBISAM's scripts in EDB are the EDB scripts
> (TEDBScript), not the stored procedures, which are stored in the
> actual database.

I think this thread has taught me two things: 1) read the entire manual
carefully before opening my big mouth; 2) the difference between query,
script, and stored procedure, most importantly between query and script.

Thanks for your patience with me.

--
David Cornelius
CorneliusConcepts.com
custom designed software

Now, back to my original question...

I've been reading more, this time through the SQL Manual.  It looks
like each SQL statement defined for EDB is defined again specifically
when putting the statements into a stored procedure, often with subtle
differences.

So while

 INSERT INTO <tabe> SELECT * FROM <other_table>

is fine when executing a single statement, you have to put the SELECT
part in a CURSOR before doing the INSERT part when inside a stored
procedure, -or- you have have to prepare a statement then execute the
statement.  So the only version of INSERT that is valid in a stored
procedure is one that explicitly lists the fields and values.

This leads me to a curiosity question: What is the problem with SELECT
statements in a stored procedure?  Why do they need to be put into a
cursor or prepared and then executed separately?

Thanks,

--
David Cornelius
CorneliusConcepts.com
custom designed software

David,

you don't need to declare a cursor.

This works as well in a stored procedure (or SQL-Script):

BEGIN
Execute Immediate 'INSERT INTO <tabe> SELECT * FROM <other_table>';
END

You have two ways of executing a statement in a dynamic SQL:

1. Prepare for execution and execute as often as required. The advantage is
that the preparation is only made once without any additional overhead.

2. Prepare and execute in one step ( = Execute Immediate). After executing
the statement, the result of the preparation is not preserved. I you execute
the statement once more you have to accept the preparation overhead once
more.

Hope this helps.

Regards Uli

Addendum:

In my last posting I talked only about preparation and immediate execution.
Actually you don't need a cursor at all for executing an insert-statement.
In my first example I just pasted code into a template.

DECLARE Stmt Statement;
Prepare Stmt FROM
  'INSERT INTO <tabe> SELECT * FROM <other_table>';
Execute Stmt;

works as well.

If you have to traverse a collection of rows or retrieving data from the
result you have to declare and use a cursor.

Example:

DECLARE MyCursor CURSOR with return for Stmt;
 Prepare Stmt FROM
  'select * from MyTable';
 OPEN MyCursor;

returns the requested collection of rows.

@Tim: please correct me if I'm wrong...

Regards Uli

My main hang-up is to not use PREPARE, DECLARE, EXECUTE, or EXECUTE
IMMEIDATE.  All I want to do is list the raw SQL statements by
themselves in a stored procedure:

CREATE PROCEDURE <ProcName>
BEGIN
 INSERT INTO <table> SELECT * FROM <other_table>;
END


This I cannot do and it frustrates me.  I don't want to have to say
"EXECUTE IMMEDIATE" and put the real SQL statement in quotes.

The two reasons for this are: 1) for design-time syntax checking, and
2) simplicity.  (OK, a third comes to mind: similarity to the SQL
Server work I do.)

I guess this is just a limitation (or feature depending on your
perspective) of EDB.

--
David Cornelius
CorneliusConcepts.com
custom designed software

 > This I cannot do and it frustrates me.  I don't want to have to say
> "EXECUTE IMMEDIATE" and put the real SQL statement in quotes.

If it frustrates you to write "EXECUTE IMMEDIATE" and some quotes, you
really will have a hard time. I understand that

Uli

 > This I cannot do and it frustrates me.  I don't want to have to say
> "EXECUTE IMMEDIATE" and put the real SQL statement in quotes.

If it frustrates you to write "EXECUTE IMMEDIATE" and some quotes, you
really will have a hard time. I understand that

Uli

Uli

That's a bad stutter you have there


Roy Lambert

David


Whilst I agree with some of your sentiments I'm learning to adapt I've just spent a few minutes (c15) to develop the script below which can execute an arbitrary number of statements. Move the AllSQLs to be a parameter  eg SCRIPT(IN AllSQLs VARCHAR)  and you can store the sql in a stringlist container and simply pass them in. The one potential problem is CREATE/DROP/ALTER table statements and you could wrap the EXECUTE in an exception handler.

The main thing I have against this is the lack of feedback for users but that should be coming (hint hint Tim)

Roy Lambert [Team Elevate]


SCRIPT
BEGIN
DECLARE AllSQLs VARCHAR;
DECLARE Singleton VARCHAR;
DECLARE ChopPos INTEGER;
DECLARE MaxChars INTEGER;  

SET AllSQLs = 'UPDATE USERS SET _Title = ''Fred ''+_Title; UPDATE USERS SET _CheckDate = CURRENT_DATE;';

SET MaxChars = LENGTH(AllSQLs);

WHILE AllSQLs <> '' DO

SET ChopPos = POS(';',AllSQLs);
IF (ChopPos = 0) OR  (ChopPos > LENGTH(AllSQLs)) THEN SET AllSQLs = '';
ELSE
SET Singleton = SUBSTRING(AllSQLs,1,ChopPos-1);
SET AllSQLs = SUBSTRING(AllSQLs,ChopPos+1,MaxChars);

EXECUTE IMMEDIATE Singleton;

END IF;

END WHILE;

END

David,

<< is fine when executing a single statement, you have to put the SELECT
part in a CURSOR before doing the INSERT part when inside a stored
procedure, -or- you have have to prepare a statement then execute the
statement.  So the only version of INSERT that is valid in a stored
procedure is one that explicitly lists the fields and values. >>

Close, but not quite.  What Uli responded with is correct, and I would just
add this:

There are two types of INSERT, UPDATE, and DELETE statements.  There are the
kind that work as dynamic SQL statements (like Uli described), and there are
the SQL/PSM versions that work with SQL/PSM cursors only:

http://www.elevatesoft.com/manual?action=mancat&id=edb1sql&category=13

The two are not interchangeable.  Think of the SQL/PSM INSERT, UPDATE, and
DELETE statements as being the same as the Insert, Edit, and Delete methods
of the TDataSet component (and descendants).  They operate directly on a
cursor at the current row position.

--
Tim Young
Elevate Software
www.elevatesoft.com