You cannot execute more than one DDL sentences (ending with in a
Statement. You need to "convert into script" and execute as Script.
But this has some frustratring problems:

1. Convert into script simply put inside quotes and execute inmediate
all text until it finds a ; When creating a trigger or procedure,
"convert into script" gets crazy and makes unusable code.
2. When complex DDL statemtes are converted into scripts, editor
treats them as quoted text, then keyboard shorcuts don't work as
spected. And syntax highlight does nothing, yet it's a quoted string,
not code.

I don't know there is a solution, but is very hard to work with large
and complex database migration in such circunstances.

And, after executing some large scripts, with user error+fix+rerun,
EDBMgr hangs. Today in a 5 hours work session, tree times.  EDBMgr is
still unstable, with hard to reproduce errors. But errors are there.


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago


I know exactly where you're coming from, and I never had that many scripts. DBISAM as far as scripts went simply took each statement and executed them in turn. What I did, as an interim was use a form that was already displaying sql progress to split apart the scripts and execute each in turn. You could do something similar as an aid whilst migrating. I've added the relevant code below. The form had a TEDBQuery on it and I set the session and database before running the job.

For triggers you're better off learning the new script language - its much more powerful than simply using multiple sql statements.


Roy Lambert [Team Elevate]


procedure TDoSQLForm.DoTheJob(const Script: string);
var
Cntr: integer;
DropTable: string;
SQLChunk: string;
begin
//Engine.SessionList[Swap.SessionName].ProgressTimeInterval := 100;
Screen.Cursor := crSQLWait;
try
 Update;
 Swap.Close;
 Swap.SQL.Clear;
 Cntr := 1;
 SQLChunk := SubFld(Script, ';', Cntr);
 while Trim(SQLChunk) <> '' do begin
  Swap.SQL.Text := SQLChunk;
  if UpperCase(Copy(Swap.SQL.Text, 1, 10)) = 'DROP TABLE' then begin
   DropTable := Copy(Swap.SQL.Text, 12, Maxint);
   if DropTable[Length(DropTable)] = ';' then Delete(DropTable, Length(DropTable), 1);
   DropTable := StringReplace(DropTable, '"', '', [rfReplaceAll]);
   if 0 = Pos('.', DropTable) then begin
    if IsTableThere(Swap.SessionName, Swap.DatabaseName, DropTable) then Swap.ExecSQL;
   end else begin
    if IsTableThere(Swap.SessionName, SubFld(DropTable, '.', 1), SubFld(DropTable, '.', 2)) then Swap.ExecSQL;
   end;
  end else Swap.ExecSQL;
  Swap.Close;
  Swap.SQL.Clear;
  Progress.Position := 0;
  inc(Cntr);
  SQLChunk := SubFld(Script, ';', Cntr);
 end;
// end;
 if CloseOnFinish then Swap.Close;
finally
 Screen.Cursor := crDefault;
end;
end;

Roy,

Thanks for you code. I wrote something similar to executing a bunch of
scripts in the Paradox era. I'll go for it.


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago,

<< But this has some frustratring problems: >>

I'm sorry, but that's just the way that things work.  DBISAM couldn't do
half of the things that you can do in a script with ElevateDB, so I think
that you haven't quite appreciated the power that is available with the
ElevateDB scripts.  They are actual scripts, not just a series of SQL
statements.  DBISAM didn't even execute them all on the ElevateDB Server at
the same time, and you certainly couldn't debug them.

<< And, after executing some large scripts, with user error+fix+rerun,
EDBMgr hangs. Today in a 5 hours work session, tree times.  EDBMgr is still
unstable, with hard to reproduce errors. But errors are there. >>

Are you sure that you're not getting an error while running the script, and
then thinking that the manager is hung when in reality it is just waiting on
you to resume the script execution ?  By default, the script execution will
pause when an error is encountered, and you must hit F9 or the Continue
button to resume execution.

The EDB Manager is very stable at this point.  There is one issue
outstanding, and that is executing scripts with OUT parameters that cause a
parameter dialog to be displayed after script execution.  Other than that,
things are perfectly fine.  If you have a specific example of a problem,
then let me know, but general comments like the above are not particularly
helpful.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>I'm sorry, but that's just the way that things work.  DBISAM couldn't do
>half of the things that you can do in a script with ElevateDB, so I think
>that you haven't quite appreciated the power that is available with the
>ElevateDB scripts.  They are actual scripts, not just a series of SQL
>statements.  DBISAM didn't even execute them all on the ElevateDB Server at
>the same time, and you certainly couldn't debug them.
The fact is EDB is faster, a true C/S system. My C/S projects in
DBISAM running over a xDSL were almost unusable, while EBD is faster
enought that my first EDB project was developed completely in a xDSL
remote server. Thats great!

In the cons part, we veteran DBSISAM programmers want best of both
worlds and this is not 100%  possible.

>The EDB Manager is very stable at this point.  
>but general comments like the above are not particularly
>helpful.

You are right. When I'll be able to reproduce suchs errors I'll
document them to you fix them. If not, there are no errors (this is I
say to my users


Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago

>In the cons part, we veteran DBSISAM programmers want best of both
>worlds and this is not 100% possible.

That was/is my biggest problem. Tim did such a great job with DBISAM I miss a lot of its nice features.

Roy Lambert

Roy,

My be the issue is when I embraced EDB didn't known what exactly means
"SQL 2003 compliant"

Tiago Ameller
tiago put_an-a-_in_a_circle sistemasc.net
Sistema, S.C.

Tiago


>"SQL 2003 compliant"

It can mean anything from "it follows some of the standard" to "does everything the standard says and nothing else" to those plus "does some extra bits as well"

Roy Lambert

Roy,

<< It can mean anything from "it follows some of the standard" to "does
everything the standard says and nothing else" to those plus "does some
extra bits as well" >>

No, it means exactly what it says, and any deviations are detailed in every
section of the SQL manual.  If you have an issue with the documentation on
this, then please point it out.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


My comment was not a specific reference to ElevateDB but a general comment relating to all the "compliant" software out there.

Roy Lambert