Tim

Any chance of the above making it into the DDL for the official release. If not what syntax should be used for pure SQL table maintenance?

Roy Lambert

Roy,

<< Any chance of the above making it into the DDL for the official release.
If not what syntax should be used for pure SQL table maintenance? >>

If you want to determine whether a table exists, then you should query the
information schema for the database.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


Can you show me a pure sql example please?  Your cdcollector uses mixed sql and Pascal.

Roy Lambert

Hi Roy,

From the soon to be released suite of EDB sample applications:

function TfrmMain.SampleTableExists(ATableName: string): Boolean;
begin
 Result := False;

 with MetadataQuery do
 begin
   if SampleDatabaseExists then
   begin
     Close;
       Engine.Active := False;

     DatabaseName := 'Sample';

     SQL.Text := 'SELECT * FROM Information.Tables WHERE Name = ' +
AnsiQuotedStr(ATableName,'''');
     RequestSensitive:=True;
     Open;

     Result := (RecordCount <> 0);

     Close;
   end;
 end;
end;

function TfrmMain.SampleDatabaseExists: Boolean;
begin
 try
   with MetadataQuery do
   begin
     Close;

     DatabaseName := 'Configuration';

     SQL.Text := 'SELECT * FROM Databases WHERE Name = ' +
AnsiQuotedStr('Sample','''');
     RequestSensitive:=True;
     Open;

     Result := (RecordCount <> 0);

     Close;
   end;
 except
   Result := False;
 end;
end;


--
Best regards

Steve

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:2126A02F-1F9A-49F9-ADB0-387E6F22F0FF@news.elevatesoft.com...
> Tim
>
>
> Can you show me a pure sql example please? Your cdcollector uses mixed sql
> and Pascal.
>
> Roy Lambert
>

Steve


That's a copy of Tim's code slightly restructured and mixes SQL and Pascal. What I'm looking for is pure SQL code.

Roy Lambert

Hi Roy,

> That's a copy of Tim's code slightly restructured and mixes SQL and
> Pascal. What I'm looking for is pure SQL code.

Sorry, misunderstood Hmm, .. have to leave that one to Tim

--
Best regards

Steve

"Roy Lambert" <roy.lambert@skynet.co.uk> wrote in message
news:11F7410C-D56E-4209-9A2F-5A871451EB8B@news.elevatesoft.com...
> Steve
>
>
> That's a copy of Tim's code slightly restructured and mixes SQL and
> Pascal. What I'm looking for is pure SQL code.
>
> Roy Lambert
>

Roy,

<< Can you show me a pure sql example please? Your cdcollector uses mixed
sql and Pascal. >>

You need to use a stored procedure for that.  Do you still want it or is
this just a academic issue ?  The reason that I ask is that I really need to
watch what I spend my time on right now, specifically with proving examples
here when I should be working on them in the actual manuals.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim

>You need to use a stored procedure for that. Do you still want it or is
>this just a academic issue ? The reason that I ask is that I really need to
>watch what I spend my time on right now, specifically with proving examples
>here when I should be working on them in the actual manuals.

I'm not 100% sure what you're asking - if its the example needs stored procedures and do I want that - I can wait - if its do I want IF EXISTS/NOT EXITS then YES I'd like it. But I'd need scripts as well.

I can work round it but at the moment I'm just creating a monster SQL script to check/create tables. Its a lot easier to just create the script and run it rather than do tests for each table and then create/run the script, but not enough easier to make me say "deliver or stuff it".

The lack of scripts and IF EXISTS/NOT EXITS will make it a bit more difficult (how much I won't know until I've got some experience in) to send out a bit of SQL to add a new table/modify an existing table(s).

Roy Lambert

Roy,

<< I'm not 100% sure what you're asking - if its the example needs stored
procedures and do I want that - I can wait - if its do I want IF EXISTS/NOT
EXITS then YES I'd like it. But I'd need scripts as well. >>

Sorry about that.  What I was asking is - if what you're asking for is
dynamic SQL script support, then I won't bother explaining how to do what
you want in a stored procedure because that's the only way to do a script
right now.

<< I can work round it but at the moment I'm just creating a monster SQL
script to check/create tables. Its a lot easier to just create the script
and run it rather than do tests for each table and then create/run the
script, but not enough easier to make me say "deliver or stuff it". >>

Oh, I understand.  I had to convert a lot of SQL scripts in our automated
test framework for DBISAM into straight SQL statements for testing with EDB.
It was a PIA, to be sure.  However, at this point I don't want to just throw
the same old DBISAM scripting back into EDB without making sure that I can't
design in something better with proper branching, looping, variables, etc.

<< The lack of scripts and IF EXISTS/NOT EXITS will make it a bit more
difficult (how much I won't know until I've got some experience in) to send
out a bit of SQL to add a new table/modify an existing table(s). >>

To a small degree, yes.  However, it's not like DBISAM's scripting was that
rich to begin with.  True, you could test for the existence of certain
things.  But, it certainly didn't allow for testing of the existence of
multiple objects as one condition or anything more complex than the simple
EXISTS/NOT EXISTS test.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< But, it certainly didn't allow for testing of the existence of multiple
objects as one condition or anything more complex than the simple EXISTS/NOT
EXISTS test. >>

IMO, the IF EXISTS clause is a damned good/convenient DDL extension. I
wouldn't be surprised to se this (so far) proprietary extension go into the
SQL standard, since it's a convenient short hand for querying the metadata -
no matter how the system tables are implemented at the vendor level.

Ole Willy Tuv