I've got the following SQL statement:

INSERT INTO TempPODetails
   (ID, DocNum, DocDate, SKU, QTY, Packing, Unit, Unit2,
      ItemSize, CustPartNo, Price, ItemDescription, Sequence)
   SELECT ID, DocNum, DocDate, SKU, QTY, Packing, UNIT, UNIT2,
      ItemSize, CustPartNo, Price, ItemDescription, Sequence
   FROM PODetails
   WHERE DocNum = :DocNum

This works from EDB Manager's SQL statement (replacing :DocNum with an
actual value).

However, when I try to put this same statement in a stored procedure, I get
the following error:

"ElevateDB Error #700 ... Expected VALUES but instead found SELECT"

Why can't I do this in a stored procedure?


--
David Cornelius
CorneliusConcepts.com
custom designed software

David,

BEGIN
DECLARE Result CURSOR FOR Stmt;
PREPARE Stmt FROM
  'INSERT INTO Saetze
  (SaetzeID)
  SELECT RechnungenID from Rechnungen
  WHERE RechnungenID = 1000';
Execute Stmt;
END

works just fine for me (1.09).

Regards Uli

I know how to use cursors, but why should I have to in this case when
INSERT-SELECTs are supposedly valid SQL statements?  They're simpler to
implement, and they work from the SQL statement window in EDB Mgr, why am I
getting the error for the exact same thing in a stored procedure?  Are there
different syntax rules for stored procedures?

The bigger issue is I'm trying to reduce my conversion effort--which has
been huge already for this fairly small application.  I have several query
components on a form to move data back and forth and they consist of 4 or 5
statements in each.  In DBISAM, I could simply execute them all in a string
of commands if they simply had semi-colons at the end of each statement.
But in EDB, I have to put them in a stored procedure and call that instead.
That's not a problem if I can just cut and paste, but if I have to turn them
all into cursor sequences as well, it's one more step I have to take.


--
David Cornelius
CorneliusConcepts.com
custom designed software


"Uli Becker" <test@test.com> wrote in message
news:724154C6-8F3F-4C47-AEEA-2002FAD7EA47@news.elevatesoft.com...
> David,
>
> BEGIN
> DECLARE Result CURSOR FOR Stmt;
> PREPARE Stmt FROM
>   'INSERT INTO Saetze
>   (SaetzeID)
>   SELECT RechnungenID from Rechnungen
>   WHERE RechnungenID = 1000';
> Execute Stmt;
> END
>
> works just fine for me (1.09).
>
> Regards Uli

David


As long as the statements are all simple ones there is a call for scripts - ConvertSQL - hack your .pas & .dfm to alter TEDBQuery to TEDBScript and call ConvertSQL then Prepare then ExecSQL and you should be OK.

Longer terms you make want to convert to SP's but for getting the conversion done I'd use a script.

Roy Lambert [Team Elevate]

So, to execute just one statement, I use TEDBQuery, but for more than one
statement, I need to use TEDBScript and then ConvertSQL/Prepare/ExecSQL?
Why on earth force us to use two different components and then to go to all
that work just to execute more than one statement at a time?  OK, maybe
there's a good reason, and maybe this has been hashed out before here, but
this seems ridiculous!

And why do I have to use different syntax?  Why can't I just chain a bunch
of statements together with semi-colons?  DBISAM can do that.  SQL Server
can do that.  Isn't it common practice in SQL to be able to do that?  Why
does it have to be difficult in EDB?

I'm starting to remember why I didn't convert my application a year ago.

--
David Cornelius
CorneliusConcepts.com
custom designed software


"Roy Lambert" wrote:
> As long as the statements are all simple ones there is a call for
> scripts - ConvertSQL -
> hack your .pas & .dfm to alter TEDBQuery to TEDBScript and call ConvertSQL
> then
> Prepare then ExecSQL and you should be OK.
>
> Longer terms you make want to convert to SP's but for getting the
> conversion done I'd use a script.

David,

<< So, to execute just one statement, I use TEDBQuery, but for more than one
statement, I need to use TEDBScript and then ConvertSQL/Prepare/ExecSQL? Why
on earth force us to use two different components and then to go to all that
work just to execute more than one statement at a time?  OK, maybe there's a
good reason, and maybe this has been hashed out before here, but this seems
ridiculous! >>

You're looking at this from the perspective of someone that only wants to
execute a series of SQL statements.  However, the scripts in EDB do *way*
more than that, which is why they are slightly more complex than with
DBISAM.   For example, with DBISAM you can't perform any conditional
execution of statements, decide which cursor to return from the script,
declare and use varaiables, have parameters to the script, build SQL
statements on the fly, etc.  All of these things you can do with the EDB
scripts.  DBISAM also has issues when dealing with multiple parameterized
statements in a single script.

<< And why do I have to use different syntax?  Why can't I just chain a
bunch of statements together with semi-colons?  DBISAM can do that.  SQL
Server can do that.  Isn't it common practice in SQL to be able to do that?
Why does it have to be difficult in EDB? >>

DBISAM simply did what you can do yourself - just look for the semicolon,
extract the next statement, send it to ElevateDB, and then rinse and repeat.
ElevateDB sends the entire script to the engine/server, where it is executed
entirely within the engine, with all of the performance improvements of such
realized in full.  If you want, I can post the code for a TEDBQueryScript
component that will do what you want.  It's just a simple component that
wraps the TEDBQuery component.

--
Tim Young
Elevate Software
www.elevatesoft.com

David,

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.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:
> You're looking at this from the perspective of someone that only
> wants to execute a series of SQL statements.  However, the scripts in
> EDB do way more than that, which is why they are slightly more
> complex than with DBISAM.   For example, with DBISAM you can't
> perform any conditional execution of statements, decide which cursor
> to return from the script, declare and use varaiables, have
> parameters to the script, build SQL statements on the fly, etc.  All
> of these things you can do with the EDB scripts.  DBISAM also has
> issues when dealing with multiple parameterized statements in a
> single script.

I understand that scripts can do more than just execute SQL statements
and are more powerful than in DBISAM.  And that's great.  But when
converting applications from DBISAM to ElevateDB, I was hoping some of
the differences could be smoothed out.

> DBISAM simply did what you can do yourself - just look for the
> semicolon, extract the next statement, send it to ElevateDB, and then
> rinse and repeat. ElevateDB sends the entire script to the
> engine/server, where it is executed entirely within the engine, with
> all of the performance improvements of such realized in full.  If you
> want, I can post the code for a TEDBQueryScript component that will
> do what you want.  It's just a simple component that wraps the
> TEDBQuery component.

It would've been nice when reading the documentation about the
TEDBQuery component to learn that multiple statements cannot be
executed like they were in DBISAM and perhaps including the
TEDBQueryScript component for compatibility's sake could've helped
smooth the transition.  (Remember, I'm coming to EDB with expectations
stemming from my use of DBISAM.)

It makes a lot of sense to learn TEDBQuery sends the whole batch to the
engine to process at once.  That explains a lot.

But still leaves me wondering why multiple statements cannot be
separated by the EDB engine...

--
David Cornelius
CorneliusConcepts.com
custom designed software

David,

<< I understand that scripts can do more than just execute SQL statements
and are more powerful than in DBISAM.  And that's great.  But when
converting applications from DBISAM to ElevateDB, I was hoping some of the
differences could be smoothed out. >>

They are if you use the TEDBScript.ConvertSQL method.  It will convert any
DBISAM-style script to using the new EXECUTE IMMEDIATE syntax.

<< It would've been nice when reading the documentation about the TEDBQuery
component to learn that multiple statements cannot be executed like they
were in DBISAM and perhaps including the TEDBQueryScript component for
compatibility's sake could've helped smooth the transition.  (Remember, I'm
coming to EDB with expectations
stemming from my use of DBISAM.) >>

This is all covered in the DBISAM migration guide:

http://www.elevatesoft.com/manual?action=mantopic&id=edb1&product=d&version=7&category=2&topic=13

Under "Property, Method, and Event Changes":

SQL: This property only accepts a single SQL statement in ElevateDB. DBISAM
allow for multi-statement scripts.

<< It makes a lot of sense to learn TEDBQuery sends the whole batch to the
engine to process at once.  That explains a lot. >>

I think you mean the TEDBScript component ?

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> This is all covered in the DBISAM migration guide:

Reading through the first time, I was probably in too big of a hurry
and only read about the things that looked, at first glance, really
important.  Perhaps the response to my original post sent me off down
the wrong track.  However, I'm re-reading the part about executing
scripts and realize Uli was right on the money.  And I also realize, as
you said, that it IS right there in the manual.


> << It makes a lot of sense to learn TEDBQuery sends the whole batch
> to the engine to process at once.  That explains a lot. >>
>
> I think you mean the TEDBScript component ?

Uh, right!

--
David Cornelius
CorneliusConcepts.com
custom designed software